【函中201401】第7讲D组作业提交贴

danny-fei

1、作业统一贴公式(需要过程的直接录屏或者写明步骤).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.
4. 作业时间截止：10/15日08：00

lmze2000

第1题：

1. =IF(WEEKDAY(B4)>4,8-WEEKDAY(B4),-WEEKDAY(B4)+1)+B4

复制代码

第2题：

1. =41640+MATCH(C14,WEEKNUM(41640+ROW(1:365)),)+2-WEEKDAY(41640+MATCH(C14,WEEKNUM(41640+ROW(1:365)),))

复制代码

1. =41630+C14*7+MOD(41637+C14*7,7)

复制代码

第3题：

1. =SUM(--(WEEKDAY(MONTH(NOW())&ROW(1:31),2)=6))

复制代码

1. =SUM(--(MOD(ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))),7)=0))

复制代码

第4题：

1. =TEXT(MONTH(B25)+(DAY(B25)=25),"[>12]!1")

复制代码

第5题：
第1问

1. =MONTH(B33&1)

复制代码

1. =MATCH(B33,TEXT(ROW($1:$12)&"-1","mmm"),)

复制代码

第2问：

1. =TEXT(LEN(2^MONTH(B33&1)),"第[dbnum1]0季度")

复制代码

木偶fenzi

公式老长，，哎
1

1. =B4+IF(MOD(B4,7)>4,8-MOD(B4,7),-MOD(B4,7)+1)

复制代码

1. =IF(MOD(B4,7)>4,B4-MOD(B4,7)+8,B4-MOD(B4,7)+1)

复制代码

1. =IF(WEEKDAY(B4)>4,B4-WEEKDAY(B4)+8,B4-WEEKDAY(B4)+1)

复制代码

2

1. =TEXT(--(B14&"-1")+c14*6+WEEKDAY(--(B14&"-1")+c14*6),"m/d")

复制代码

1. =TEXT(--(B14&"-1")+c14*6+MOD(--(B14&"-1")+c14*6,7),"m/d")

复制代码

1. =TEXT(DATE(B14,1,1)+c14*6+WEEKDAY(DATE(B14,1,1)+c14*6),"m/d")

复制代码

1. =TEXT(DATE(B14,1,1)+c14*6+MOD(DATE(B14,1,1)+c14*6,7),"m/d")

复制代码

3

1. =INT((WEEKDAY(--TEXT(NOW(),"e-m")-7)+EOMONTH(NOW(),0)-TEXT(NOW(),"e-m"))/7)

复制代码

1. 
   

复制代码

数组

1. =SUM(N(MOD(ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))),7)=0))

复制代码

1. =SUM(N(WEEKDAY(ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))))=7))

复制代码

1. =SUM(N(MOD(ROW(INDIRECT(--TEXT(NOW(),"e-m")&":"&EOMONTH(NOW(),0))),7)=0))

复制代码

1. =SUM(N(MOD(ROW(INDIRECT(--(MONTH(NOW())&"-1")&":"&EOMONTH(NOW(),0))),7)=0))

复制代码

1. =SUM(N(WEEKDAY(ROW(INDIRECT(--TEXT(NOW(),"e-m")&":"&EOMONTH(NOW(),0))))=7))

复制代码

1. =SUM(N(WEEKDAY(ROW(INDIRECT(----(MONTH(NOW())&"-1")&":"&EOMONTH(NOW(),0))))=7))

复制代码

1. =IF(DAY(B25)>24,MONTH(DATE(1,MONTH(B25)+1,1)),MONTH(B25))

复制代码

1. =TEXT(--IF(DAY(B25)>24,MONTH(B25)+1,MONTH(B25))&"-1","m")

复制代码

5

1. =MONTH(B33&1)

复制代码

1. =TEXT(LEN(2^MONTH(B33&1)),"第0季度")

复制代码

1. =VLOOKUP(B33,TEXT(DATE(1,ROW($1:$12),1),{"mmm","m"}),2,)

复制代码

1. =VLOOKUP(B33,TEXT(--(ROW($1:$12)&"-1"),{"mmm","m"}),2,)

复制代码

1. =LOOKUP(,0/(B33=TEXT(DATE(1,ROW($1:$12),1),"mmm")),ROW($1:$12))

复制代码

1. ="第"&LEN(2^VLOOKUP(B33,TEXT(--(ROW($1:$12)&"-1"),{"mmm","m"}),2,))&"季度"

复制代码

1. ="第"&LEN(2^LOOKUP(,0/(B33=TEXT(--(ROW($1:$12)&"-1"),"mmm")),ROW($1:$12)))&"季度"

复制代码

1. ="第"&LEN(2^MONTH(--(F33&"-1")))&"季度"

复制代码

1. =TEXT(LEN(2^MONTH(--(F33&"-1"))),"第0季度")

复制代码

Tank008

1、=IF(WEEKDAY(B4,2)=7,B4,IF(WEEKDAY(B4,2)>=4,B4+(7-WEEKDAY(B4,2)),B4-WEEKDAY(B4,2)))

2、=TEXT(DATE(B14,1,1)+(C14-1)*7-(WEEKDAY(DATE(B14,1,1),2)-1),"m/d")

3、=SUMPRODUCT(--(WEEKDAY(EOMONTH(TODAY(),-1)+ROW(INDIRECT("1:"&DAY(EOMONTH(TODAY(),0)))),2)=6))

4、=IF(DAY(B25)-25<0,MONTH(B25),MONTH(DATE(YEAR(B25),MONTH(B25)+1,1)))

5、数字月份：C33=MATCH(B33,TEXT(ROW($1:$12)*30,"mmm"),0)
     季度处理：D33="第"&TEXT(LEN(2^C33),"[dbnum1]")&"季度"

yuan1987

1、=B4-MOD(B4-1,7)+7*(WEEKDAY(B4,2)>3)
2、=TEXT(B14&"-1-1","yyyy-mm-dd")+C14*7-6-WEEKDAY(B14&"/1/1",2)
3、=NETWORKDAYS.INTL(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0),"1111101")
4、=IF(IF(DAY(B25)>=25,MONTH(B25)+1,MONTH(B25))-12>0,IF(DAY(B25)>=25,MONTH(B25)+1,MONTH(B25))-12,IF(DAY(B25)>=25,MONTH(B25)+1,MONTH(B25)))
5、
=MONTH(B33&1)
="第"&NUMBERSTRING(ROUNDUP(C33/3,),1)&"季度"
去出差了，没赶上听课。

天棋

第一题：=B4+7*(WEEKDAY(B4)>4)-MOD(B4-1,7)
第二题：=DATE(B14,1,1)+MATCH(1,0/(MOD(DATE(B14,1,ROW(INDIRECT("1:"&C14*7-6))),7)=2))-1
              =DATE(B14,1,C14*7-5)-MOD(DATE(B14,1,),7)
第三题：=SUM(N(MOD((TEXT(ROW(1:65536),"eM")=TEXT(TODAY(),"eM"))*ROW(1:65536),7)=1))
第四题：=MONTH(DATE(,MONTH(B25)+(DAY(B25)>24),)+1)
第五题：1.1=TEXT(B33&1,"M")
              1.2=TEXT((TEXT(B33&1,"M")+5)/3,"第aaa季度")

tgy0451

1、=B4+IF(WEEKDAY(B4,2)>3,7-WEEKDAY(B4,2),-WEEKDAY(B4,2))
2、=DATE(B14,1,1)+7*(C14-1)-WEEKDAY(DATE(B14,1,1)+7*(C14-1),2)+1
3、=IF(MONTH(TEXT(NOW(),"e-m")+6-WEEKDAY(TEXT(NOW(),"e-m"),2)+28)=MONTH(NOW()),5,4)
4、=MONTH(B25)+(DAY(B25)>24)-12*(MONTH(B25)+(DAY(B25)>24)>12)
5、="第"&NUMBERSTRING(INT(C33/3.1)+1,1)&"季度"

C.Deny

D08:C.Deny上交第七讲作业
1.=TEXT(IF(WEEKDAY(B4,2)>=4,B4+(7-WEEKDAY(B4,2)),B4-WEEKDAY(B4,2)),"yyyy/m/d")下拉
2.=TEXT(MAX(FLOOR(DATE(B14,1,0),7)+7*C14-5,B14&-1),"m/d")下拉
3.=4+COUNT(IF(MOD(TEXT(TODAY(),"e-m")&-{29,30,31},7)=0,))
4.=MONTH(B25+(DAY(B25)>24)*7)
5.数字月份=MONTH(B33&-1)
季度=TEXT(CEILING(C33,3)/3,"[dbnum1]第0季度")

李建军

1，=IF(WEEKDAY(B4,2)>=4,7-WEEKDAY(B4,2),-WEEKDAY(B4,2))+B4
2，=DATE(B14,1,C14*7-4-WEEKDAY(B14&"/1/1"))
3，=NETWORKDAYS.INTL(TEXT(NOW(),"e-m"),EOMONTH(NOW(),0),"1111101")
4，=MONTH(B25+7)
5.1，=LOOKUP(,0/({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}=B33),{1;2;3;4;5;6;7;8;9;10;11;12})
5.2，=TEXT(MONTH(E33*10),"第[DBNum1]#季度")