网友问到一个 Excel 的题目:
按照前一篇文章:Excel-根据日期区间列出各月职员清单(OFFSET,ROW,COLUMN,阵列公式),若是日期有跨年时,该若何处置?
本例以2015年7月~12月、2016年1月~6为週期,来列出各月学员的清单。
【公式设计与解析】
先拔取贮存格A1:C23,按 Ctrl+Shift+F3 键,勾选「顶端列」,界说名称:学员、起头、竣事。
贮存格E3:
{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(起头),MONTH(起头),1)<=
DATE(2015,COLUMN(G:G),1))*(DATE(YEAR(竣事),MONTH(竣事),1)>=
DATE(2015,COLUMN(G:G),1)),ROW(学员),""),ROW(1:1))-1,0,1,1),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动发生「{}」。
前提一:DATE(YEAR(起头),MONTH(起头),1)<=DATE(2015,COLUMN(G:G),1)
DATE(YEAR(起头),MONTH(起头),1):操纵 DATE 函数找出起头的日期阵列中各月的第1天。
DATE(2015,COLUMN(G:G),1):操纵 DATE 函数找出 2015 年7月的第1天。
COLUMN(G:G)=7,向右複製公式时,COLUMN(G:G)=7→COLUMN(H:H)=8→...COLUMN(L:L)=12。
前提二:DATE(YEAR(竣事),MONTH(竣事),1)>=DATE(2015,COLUMN(G:G),1)
複製贮存格E3,贴至贮存格E3:J24。
贮存格K3:
{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(起头),MONTH(起头),1)<=
DATE(2016,COLUMN(A:A),1))*(DATE(YEAR(竣事),MONTH(竣事),1)>=
DATE(2016,COLUMN(A:A),1)),ROW(学员),""),ROW(1:1))-1,0,1,1),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动发生「{}」。
前提一:DATE(YEAR(起头),MONTH(起头),1)<=DATE(2016,COLUMN(A:A),1)
DATE(2016,COLUMN(A:A),1):操纵 DATE 函数找出 2016 年1月的第1天。
COLUMN(A:A)=1,向右複製公式时,COLUMN(A:A)=1→COLUMN(B:B)=2→...COLUMN(F:F)=6。
前提二:DATE(YEAR(竣事),MONTH(竣事),1)>=DATE(2016,COLUMN(A:A),1)
複製贮存格E3,贴至贮存格K3:P24。
具体申明,可参考前一篇文章:Excel-根据日期区间列出各月职员清单(OFFSET,ROW,COLUMN,阵列公式)。
创作者先容 vincent 北京拓展公司- 全站分类:不设分类
- 小我分类:课本资料
- 此分类上一篇: Excel-将西元年日期加上年代日(TEXT,YEAR,MONTH,DAY)
- 此分类下一篇: Excel-在一个数值区间入彀算含有某个数字的个数(SUBSTITUTE,ROW)
- 上一篇: Excel-将西元年日期加上年代日(TEXT,YEAR,MONTH,DAY)
- 下一篇: Excel-在一个数值区间入彀算含有某个数字的个数(SUBSTITUTE,ROW)
汗青上的今天
- 2015: Line供给了1GB的云端空间(keep)当为小我记事空间
- 2014: Excel-在两个资料表中查询(VLOOKUP)
- 2014: Word/Excel-移转主动校订内容到另外一个版本(或另外一部电脑)
- 2013: 撷取PowerPoint简报中多张投影片中的文字
- 2012: Microsoft Visual Basic 6.0 Service Pack 6 平安彙总更新
- 2012: Google-多重帐号登入
- 2011: Excel-点窜为固定长度的编号
- 2011: Windows 7-在档案总管中利用挑选功能2