[201401函中]E组第七讲作业提交贴

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

hsl215

一、

1. =(WEEKDAY(B4)>4)*7-WEEKDAY(B4)+B4+1
   
2. =(WEEKDAY(B4,2)>3)*7-WEEKDAY(B4,2)+B4

复制代码

二、

1. =DATE(B14,1,C14*7-4-WEEKDAY(B14&"/1/1"))
   
2. =DATE(B14,1,C14*7-5-WEEKDAY(B14&"/1/1",2))

复制代码

三、前两个通用，第3个适用07以上版本

1. =COUNT(1/(MOD(TEXT(NOW(),"e-m")&-ROW(1:31),7)=0))
   
2. =COUNT(1/(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&"："&DATE(YEAR(NOW()),MONTH(NOW())+1,))),7)=0))
   
3. =(DAY(EOMONTH(NOW(),0))>35-WEEKDAY(--TEXT(NOW(),"e-m")))+4
   

复制代码

四、第一个通用，第二个07以上版本适用

1. =MONTH(DAY(DATE(YEAR(B25),MONTH(B25)+1,))-24+B25)
   
2. =MONTH(DAY(EOMONTH(B25,0))-24+B25)

复制代码

增加一个新公式

1. =MONTH(EOMONTH(B25,DAY(B25)/25))

复制代码

再增加一个取巧公式

1. =MONTH((B25)+DAY(B25)/3.5)

复制代码

五、
1、

1. =MONTH(B33&1)
   
2. =TEXT(B33&1,"m")

复制代码

2、

1. =TEXT((MONTH(B33&1)+5)/3,"第aaa季度")
   
2. =TEXT((MONTH(B33&1)+1)/3,"[dbnum1]第#季度")
   
3. =TEXT(LEN(2^MONTH(B33&1)),"[dbnum1]第#季度")
   
4. =TEXT(MONTH(B33&1)/3+0.2,"[dbnum1]第#季度")

复制代码

sinowonder

1、=B4-IF(WEEKDAY(B4,2)<4,WEEKDAY(B4,2),WEEKDAY(B4,2)-7)
2、=(B14&-1)-WEEKDAY(B14&-1,2)-6+C14*7
3、{=SUM((WEEKDAY(MONTH(NOW())&-1+ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0))))-1,2)=6)*1)}
     {=SUM(1*(MONTH((EOMONTH(NOW(),-1)-WEEKDAY(MONTH(NOW())&-1,2)+ROW(1:5)*7))=MONTH(NOW())))}
4、=MONTH(IF(DAY(B25)<=24,MONTH(B25),MONTH(B25)+1)&-1)
5、=TEXT(LEN(2^C33),"第0[dbnum1]季度")

meililin

第一题

1. =TEXT(IF(WEEKDAY(B4,2)>3,B4+7-WEEKDAY(B4,2),B4-WEEKDAY(B4,2)),"e/m/d")

复制代码

1. =B4-WEEKDAY(B4,2)+7*(WEEKDAY(B4,2)>3)

复制代码

第二题

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

复制代码

第三题
方法1：头+中+尾三段判断计算（不忍直视的长度）

1. =(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)<7)+INT((DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,))-(7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)+1))/7)+(MOD((DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,))-(7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1),2)+1)),7)=6)

复制代码

方法2：数组公式，构造一个当前月份星期几的数组，然后判断是否等于星期六，最后求和即为当前月份周六的个数

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

复制代码

第四题

1. =IF(DAY(B25)<25,MONTH(B25),MONTH(DATE(YEAR(B25),MONTH(B25),)+32))

复制代码

1. =--TEXT((DAY(B25)>24)+MONTH(B25),"[>12]!1;0")

复制代码

第五题
数字月份

1. =MONTH(TEXT("1-"&B33,"e-m-d"))

复制代码

季度

1. =TEXT(LEN(2^MONTH(TEXT("1-"&B33,"e-m-d"))),"第[DBNum1][$-804]0季度")

复制代码

XMergeD

1

1. =(MOD(B4-1,7)>3)*7-MOD(B4-1,7)+B4

复制代码

2

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

复制代码

3

1. =NETWORKDAYS.INTL(TEXT(NOW(),"e-m"),EOMONTH(now(),0),"1111101")

复制代码

4

1. =MONTH((DAY(B25)>24)*7+B25)

复制代码

5

1. =MONTH(B33&-1)

复制代码

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

复制代码

ANDY0393

题1：

1. =B4+TEXT(WEEKDAY(B4,2),"[>3]7;!0")-WEEKDAY(B4,2)

复制代码

题2：

1. 数组公式
   
2. =MAX((WEEKNUM((B14&"-1-1")+ROW(1:366)-1,2)=C14)*(WEEKDAY((B14&"-1-1")+ROW(1:366)-1)=2)*(B14&"-1-1")+ROW(1:366)-1)

复制代码

题3：

1. 数组公式
   
2. =COUNT(0/(WEEKDAY(TODAY()-DAY(NOW())+ROW(1:31))=7)*(MONTH(TODAY()-DAY(TODAY())+ROW(1:31))=MONTH(NOW())))

复制代码

1. 数组公式=COUNT(0/(MONTH(7-WEEKDAY(MONTH(NOW())&"-1")+DATEVALUE(MONTH(NOW())&"-1")+7*{0,1,2,3,4})=MONTH(NOW())))

复制代码

题4：

1. =MONTH(MONTH(B25)+(DAY(B25)>24)&"-1")

复制代码

1. =MOD(MONTH(B25-24),12)+1

复制代码

题5：
数字月份

1. 数组公式=MATCH(B33,TEXT(ROW($1:$12)&"-1","mmm"),)
   
2. 取巧公式
   
3. =CEILING(FIND(B33,PHONETIC(B$33:B$44))/3,1)

复制代码

季度

1. =TEXT(CEILING(C33/3,1),"第[dbnum1]0季度")
   
2. =TEXT(ROUNDUP(C33/3,),"第[dbnum1]0季度")

复制代码

十五的月亮

1. 1、=B4+IF((7-WEEKDAY(B4,2))>WEEKDAY(B4,2),-WEEKDAY(B4,2),7-WEEKDAY(B4,2))
   
2. 2、=WEEKNUM(DATE(B14,1,7),11)+C14*7-14
   
3. 3、=SUM(N(WEEKDAY(EOMONTH(NOW(),-1)+ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))),2)=6))
   
4. 4、=MONTH(EOMONTH(B25,IF(DAY(B25)>24,1,0)))
   
5. 5、=TEXT(LEN(2^C33),"第[DBNum1][$-804]G/通用格式季度")

复制代码

76868850

1、
2、=TEXT(DATE(B14,1,C14*7-4)+MOD(DATE(B14,1,),7),"m/d;@")
3、=DAY(EOMONTH(TODAY(),0))-NETWORKDAYS.INTL(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0),17)
4、=IF(DAY(B25)>24,--TEXT(EDATE(B25,1),"mm"),MONTH(B25))
5、="第"&NUMBERSTRING(INT((MONTH(--(B33&1))+2)/3),3)&"季度"

yqnh

E11：yqnh上交第七课作业，请学委老师指导指点

1. 1、=TEXT(ROUND((B4-1)/7,)*7+1,"e-m-d")
   
2. 2、=TEXT(DATE(B14,1,C14*7)-11+WEEKDAY(DATE(B14,1,1),2),"m/d")
   
3. 3-1、=INT((WEEKDAY(EOMONTH(NOW(),-1)+1,2)+DAY(EOMONTH(NOW(),0)))/7)
   
4. 3-2、=SUM(N(MOD(ROW(INDIRECT(EOMONTH(NOW(),-1)+1&":"&EOMONTH(NOW(),0))),7)=0))（数组）
   
5. 4、=MOD(MONTH(B25)+N(DAY(B25)>24)-1,12)+1
   
6. 5-1、月份=MONTH(--(B33&-1))
   
7. 5-2、季度=TEXT(LEN(2^C33),"第[dbnum1]0季度")
   

复制代码

一杯清荼

题一：
       =B4+(WEEKDAY(B4,2)>3)*7-WEEKDAY(B4,2)
       =B4+(WEEKDAY(B4)>4)*7-WEEKDAY(B4)+1

题二：
      =TEXT((B14&-1)+7*C14-WEEKDAY(B14&-1,2)-6,"m/d")
      =DATE(B14,1,C14*7)-WEEKDAY(B14&-1,2)-5
题三：
      =COUNT(1/(MOD(TEXT(NOW(),"e-m")&-ROW(1:31),7)=0))
题四:
      =MONTH(MONTH(B25)+(DAY(B25)>24)&-1)
      =MONTH(EOMONTH(B25,(DAY(B25)>24)*1))

题五：
      数字月份
     =MONTH(B33&-1)
     =TEXT(B33&"-1","m")
     季度
     =TEXT(LEN(2^MONTH(B33&0)),"第[dbnum1]0季度")
     =TEXT(ROUNDUP(MONTH(B33&0)/3,),"第[dbnum1]0季度")
     =TEXT(INT(MONTH(B33&0)/3+0.7),"第[dbnum1]0季度")