有网友问到：在 Excel 中的一个资料表(以下图左)，若何挑选出职员为甲及非空缺日期的资料，而且统计其数值的和？

若是你不想手动利用挑选东西，则可以藉助公式来挑选，可是必需颠末「阵列公式」的处置。

【準备工作】

拔取贮存格B1:D17，按 Ctrl+Shift+F3 键，拔取「顶端列」，界说名称：职员、数值、日期。

【公式设计】

1. 挑选：职员为甲

(1) 找出合适的列数

贮存格F3：｛=SMALL(IF(职员="甲",ROW(职员),999),ROW(1:1))-1｝

这是阵列公式，输入完成要按 Ctrl+Shift+Enter 键，Excel 会主动加上｛｝。

此中参数「999」是随便指定一个较年夜的数值罢了。

(2) 按照合适的列数找出合适的内容

贮存格G3：｛=OFFSET(B$1,SMALL(IF(职员="甲",ROW(职员),999)-1,
ROW(1:1)),,)｝

这是阵列公式，输入完成要按 Ctrl+Shift+Enter 键，Excel 会主动加上｛｝。

複製贮存格G3，贴至贮存格G3:I3。複製贮存格F3:I3，贴至贮存格F3:I8。

(3)计较挑选后的总和

贮存格H1：=SUMPRODUCT((职员="甲")*数值)

2. 挑选：非空缺日期

(1) 找出合适的列数

贮存格F12：｛=SMALL(IF(日期<>"",ROW(日期),999),ROW(1:1))-1｝

这是阵列公式，输入完成要按 Ctrl+Shift+Enter 键，Excel 会主动加上｛｝。

(2) 按照合适的列数找出合适的内容

贮存格G12：｛=OFFSET(B$1,SMALL(IF(日期<>"",ROW(日期),999)-1,ROW(1:1)),,)｝

这是阵列公式，输入完成要按 Ctrl+Shift+Enter 键，Excel 会主动加上｛｝。

複製贮存格G12，贴至贮存格G12:I12。複製贮存格F12:I12，贴至贮存格F23:I23。

(3)计较挑选后的总和

贮存格H10：=SUMPRODUCT((日期<>"")*数值)

3. 调剂日期格局

读者若是实做时就会发此刻 OFFSET 函数挑选日期的成果若是是空缺贮存格，则会以「0」显示，该若何将 0 显示为空缺呢？

为了统筹正常日期要显示为月二码、日二码，所以将所有日期的贮存格数值格局设定为：「mm/dd;;」

创作者先容 vincent

学不完．教不断．用不尽

• 全站分类：不设分类
• 小我分类：课本资料
• 此分类上一篇： Excel-用公式更改日期格局(TEXT)
• 此分类下一篇： Excel-「名称」的界说及利用操练
• 上一篇： IFTTT-DO和IF利用(记实本身生气的时候／提示本身不生气)
• 下一篇： Excel-「名称」的界说及利用操练

汗青上的今天

• 2017: Windows-若何快速开启桌面档案地点的资料夹
• 2016: 利用Google文件共笔协作省时又省事
• 2016: Excel-利用多变北京拓展公司数、多前提查询(LOOKUP,OFFSET,MATCH)
• 2015: IFTTT-DO和IF利用(记实本身生气的时候／提示本身不生气)
• 2014: Excel-查表公式(INDEX,MATCH,OFFSET,VLOOKUP)
• 2013: Excel-让日期只显示年和月
• 2013: Excel-列出指定区间的所有礼拜几的日期(WEEKDAY)
• 2011: Excel-计较到本日的月报表
• 2011: Excel-成立含表格的页首页尾
• 2009: 诺贝尔奖得主的座右铭
• 2009: 风致教育
• 2009: Adobe Reader 9.1.0版更新程式

▲top