Excel-根据双条件来摘要资料(SUMPRODUCT,OFFSET,LARGE)
头几天的二篇 Excel 文章,都是利用「阵列公式」来处置:
Excel-应用双前提从资料表摘要资料(OFFSET,INDIRECT,SUMPRODCUT)
Excel-藉由界说名称转换表格来摘要资料(阵列公式,OFFSET,INDIRECT)
此次不要利用阵列公式,而是利用 SUMPRODUCT 函数来运算。鄙人图右的上下二个摘要表中,上半部是根据一个前提来挑选资料,下半部是根据二个前提来挑选资料。请自行对比以上二篇文章比力其差别。
【公式设计与解析】
为了便利申明,先界说名称。拔取贮存格A1:E25,按 Ctrl+Shift+F3 键,勾选「顶端列」,界说名称:日期、客编、品名、规格、数目。
利用 SUMPRODUCT 函数可以不消利用「阵列公式」,有些人对阵列公式望而生畏或是一之半解没法活用。
(1) 根据一个前提来挑选资料
为便利申明利用一个辅助栏位(K栏)。
贮存格K3:=SUMPRODUCT(LARGE((客编=$H$1)*ROW(日期),ROW(1:1)))-1
客编=$H$1:在 SUMPRODUCT 函数中的「客编」阵列中,判定是和贮存格H1不异,传回 TRUE/FALSE 阵列。
(客编=$H$1)*ROW(日期):传回合适「客编」阵列中是和贮存格H1不异者的列号(以日期阵列来获得列号)所构成的阵列。
LARGE((客编=$H$1)*ROW(日期),ROW(1:1)):操纵ROW(1:1)来依序「由年夜到小」获得上式中列号阵列的数值。ROW(1:1)=1向下複製时会发生ROW(2:2)=2→ROW(3:3)=3→...。
贮存格G3:=IFERROR(OFFSET($A$1,K3,,,),"")
贮存格H3:=IFERROR(OFFSET($C$1,K3,,,),"")
贮存格I3:=IFERROR(OFFSET($D$1,K3,,,),"")
贮存格J3:=IFERROR(OFFSET($E$1,K3,,,),"")
複製贮存格G3:K3,贴至贮存格G3:K13。
(2) 根据二个前提来挑选资料
贮存格K17:
=SUMPRODUCT(LARGE((客编=$H$15)*(品名=$J$15)*ROW(日期),ROW(1:1)))-1
不雅察上式,利用 (客编=$H$15)*(品名=$J$15) 来挑选双前提的成果。
贮存格G17:=IFERROR(OFFSET($A$1,K17,,,),"")
贮存格H17:=IFERROR(OFFSET($D$1,K17,,,),"")
贮存格I17:=IFERROR(OFFSET($E$1,K17,,,),"")
複製贮存格G17:K17,贴至贮存格G17:K25。
【思虑一下】
若何根据三个前提、四个前提来挑选资料?
创作者先容 vincent- 全站分类:不设分类
- 小我分类:课本资料
- 此分类上一篇: Excel-显示较多位数的费氏数列(Fibonacci number)(RIGHT,LEFT,INT)
- 此分类下一篇: Excel-在VLOOKUP函数中利用双前提来查询(INDIRECT)
- 上一篇: 若何从舆图上查询地址的门商标码(Google,Here)
- 下一篇: Google日曆输出为PDF档以便利书面利用
汗青上的今天
- 2015: 若何从舆图上查询地址的门商标码(Google,Here)
- 2015: Excel-显示较多位数的费氏数列(Fibonacci number)(RIGHT,LEFT,INT)
- 2014: Excel-在日期区间数列中小计分年分月的人数(SUMPRODUCT)
- 2013: Excel-掏出数值中的每位数
- 2012: 步履装配Google舆图供给新功能-单手指缩放
- 2012: Word-利用F8键拔取文字
- 2011: Excel-发生某年度固定礼拜几的随机日期
- 2011: Excel-找出每一年的第一个礼拜几是那一天
- 2010: Google推出透明度陈述
- 2009: 检视文件的metadata
- 2北京拓展公司009: 移除Office文件中的隐私资讯
- 2008: 校园 BBS 站相干法令题目研究
- 2008: 校园网站法令案例
- 2008: 资讯素养与伦理讲授资本
- 2008: 七十二句话令你年夜智若愚
- 2008: 反向补缀
- 2008: 自编国小一至六年级生字簿及生字语词簿