发布时间:2017-10-01 作者:admin 点击量:
在 Excel 中有一个天天会增添三笔资料的报表(以下图左),若何按照分歧产物,发生依礼拜和月份统计的报表(累计下载和均匀校阅阅兵)(参考下图右)?
由于天天城市增添三笔资料,所以资料的範围其实不固定。
【準备工作】
起首要定四个名称,以便简化公式的複杂度。因为天天都有三笔资料插手,所以资料的範围不固定,是以以OFFSET函数来界说资料範围。在名称办理员中做以下的界说:
(1) 产物:=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$A:$A)-1,)
(2) 日期:=OFFSET(工作表1!$B$2,,,COUNTA(工作表1!$A:$A)-1,)
(3) 校阅阅兵数:=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$A:$A)-1,)
(4) 下载数:=OFFSET(工作表1!$D$2,,,COUNTA(工作表1!$A:$A)-1,)
公式中以 COUNTA 函数来抓取今朝在 A 栏中有几多笔资料。
【计较累计下载 - 利用 SUMPRODUCT 函数】
贮存格G2:=SUMPRODUCT(--(产物=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下载数)
複製贮存格G2,贴至贮存格G2:I8。
此中 WEEKDAY 函数中的参数 2,乃界说数字 1(礼拜一) 至 7(礼拜日):
ROW(1:1)=1,往下複製时会主动变成ROW(2:2)=2 –> ROW(3:3)=3 –> … –> ROW(7:7)=7。
操纵 SUMPRODUCT 函数获得「合适产物名称的 True/False阵列、合适礼拜几的 True/False阵列、下载数」来运算乘积和。
SUMPRODUCT 函数中利用「--」,是为了将 True/False 阵列转换为 1/0 阵列,才能以数值计较。
SUMPRODUCT(--(产物=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下载数) 也能够写成:
SUMPRODUCT((产物=G$10)*(WEEKDAY(日期,2)=ROW(1:1))*下载数)
【计较累计下载 - 利用阵列公式】
贮存格G2:{=SUM(IF(产物=G$10,IF(WEEKDAY(日期,2)=ROW(1:1),下载数,FALSE),FAL北京拓展公司SE))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键。
複製贮存格G2,贴至贮存格G2:I8。
IF 函数中的 FALSE 参数在此例中利用「0」或「空缺」所得的成果是一样的。
由以上「计较累计下载」的例子,来自行操练「计较均匀校阅阅兵」。
【计较均匀校阅阅兵 - 利用 SUMPRODUCT 函数】
贮存格G11:=SUMPRODUCT((产物=G$10)*(MONTH(日期)=ROW(9:9))*校阅阅兵数)/SUMPRODUCT((产物=G$10)*(MONTH(日期)=ROW(9:9)))
複製贮存格G11,贴至贮存格G11:I13。
公式中透过 MONTH 函数来求得日期代表的月份,由于标题问题要求9,10,11月,所以判定其即是ROW(9:9)=9。往下複製时可以发生10,11。
【计较均匀校阅阅兵 - 利用阵列公式】
贮存格G11:{=AVERAGE(IF(产物=G$10,IF(MONTH(日期)=ROW(9:9),校阅阅兵数,FALSE),FALSE))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键。
複製贮存格G11,贴至贮存格G11:I13。
【弥补申明】
相干函数申明,请参阅微软网站。
SUMPRODUCT:weioffice.microsoft.af/zh-tuozhan/excel-help/HP010342935.aspx
SUMPRODUCT:传回各阵列中所有对应元素乘积的总和。 | 语法:SUMPRODUCT(array1, [array2], [array3], ...) array1:要求对应元素乘积和的第一个阵列引数。 array2, array3,...:要求对应元素乘积和的第 2 个到第 255 个阵列引数。 注:各阵列必需有不异的维度(不异的列数,不异的栏数)。不然会传回毛病值#VALUE!。而且会将所有非数值资料的阵列元素当做0来处置。 |
联系我们
contact us地址:北京市西红门理想商业街3号楼6层
电话:4000-288-501
点击图标在线留言,我们会及时回复