Excel-计算平均时不包含空白储存格(SUMPRODUCT,ISBL

有网友问到以下图左的 Excel 资料清单中，若是要将编号为奇数／偶数的项目，别离掏出计较其均匀，该若何处置？此中有部门贮存格的内容为空缺。

计较均匀时，若是贮存格的内容为空缺，理应不併入计较。而 AVERAGE 函数，也是会将贮存格为空缺贮存格者不列入均匀。

先拔取贮存格A1:B20，按一下 Ctrl+Shfit+F3 键，界说名称：编号、数值。

【毛病成果】

贮存格E2：=SUMPRODUCT((MOD(编号,2)=1)*数值)/SUMPRODUCT(–(MOD(编号,2)=1))

MOD(编号,2)=1：判定编号除以 2 的余数是不是为 1 (该数为奇数)，获得一个 TRUE／FALSE 的阵列。

SUMPRODUCT((MOD(编号,2)=1)*数值)：计较编号为奇数者的数值总和。

SUMPRODUCT(–(MOD(编号,2)=1))：藉由「–」运算，将计较成果为 TRUE／FALSE 的阵列转换为 1／0 的阵列。

贮存格E3：=SUMPRODUCT((MOD(编号,2)=0)*数值)/SUMPRODUCT(–(MOD(编号,2)=0))

MOD(编号,2)=0：判定编号除以 2 的余数是不是为 0 (该数为偶数)，获得一个 TRUE／FALSE 的阵列。

可是以上的公式，会将贮存格B4和贮存格B13这二个空缺贮存格的内容视为 0，在计较均匀值时会发生毛病。

【准确成果】

为了将空缺贮存格不在计较均匀时併入计较，将公式稍做点窜：

贮存格E4：=SUMPRODUCT((MOD(编号,2)=1)*数值)/SUMPRODUCT((NOT(ISBLANK(数值)))*(MOD(编号,2)=1))

NOT(ISBLANK(数值))：透过 ISBLANK 函数来判定数值阵列中是不是为空缺贮存格，获得一个 TRUE／FALSE 阵列，再藉由 NOT 函数将成果转换为 FALSE／TRUE 阵列。

贮存格E5：=SUMPRODUCT((MOD(编号,2)=0)*数值)/SUMPRODUCT((NOT(ISBLANK(数值)))*(MOD(编号,2)=0))

创作者先容

vincent

北京拓展公司

学不完．教不断．用不尽

• 全站分类：不设分类
• 小我分类：课本资料
• 此分类上一篇： Excel-关于DATEDIF函数
• 此分类下一篇： Excel-计较比来几天的均匀(OFFSET)
• 上一篇： Excel-关于DATEDIF函数
• 下一篇： Excel-计较比来几天的均匀(OFFSET)

汗青上的今天

• 2016: Word-设定每一个章节题目显示在页首中
• 2015: IFTTT利用：手电机量在低值时主动把WiFi关失落
• 2015: 快速移除以Google帐户透过第三方认证保持的利用程式
• 2015: 利用Google云端印表机让本电机脑的文件在另外一部电脑的印表机中列印
• 2010: Excel-利用多层下拉式清单布局输入资料(进阶)
• 2010: Excel-利用多层下拉式清单布局输入资料(根本)
• 2009: Google的片子搜索
• 2009: 将Google图书中的册本嵌入部落格中

▲top