达人之路·《高效办公应用宝典公式·函数与VBA》

达人之路·《高效办公应用宝典公式·函数与VBA》_4.1 数组公式的应用

【摘要】本书摘自《高效办公应用宝典公式·函数与VBA》一书中第4章，第1节，创客诚品编著。

04 数组公式的应用

本章概述

多数人都知道Excel公式，却很少听说数组公式，那么什么是数组公式呢?数组公式和普通公式相比有哪些不同之处呢?数组公式可以认为是Excel 对公式和数组的一种扩充，换句话说，就是Excel公式在以数组为参数时的一种应用。使用数组公式可以提高公式的计算效率。本章将详细介绍数组公式的相关应用。

核心知识点一

什么是数组

快速输入数组公式

达人之路·《高效办公应用宝典公式·函数与VBA》_4.1 数组公式的应用

数组之间的运算规则

修改和删除数组公式

4.1 什么是数组

在Excel中，数组是由一个或多个元素按照行列排列方式组成的集合。数据元素可以是数值、文本、日期、错误值、逻辑值等。数组又分为常量数组、区域数组和内存数组三类。

①常量数组

常量数组由常量数据组成，数组常量必须放置在大括号“{}”中。不同列的数值需要使用半角逗号“,”来分隔，不同行的数值使用分号“;”来分隔。数组常量不能使用单元格引用、长度不等的行或列、公式或特殊符号(如$、%、括号等)。数组常量可以是整数、小数或科学计数格式的数值。当数组常量是文本时必须添加半角双引号。例如下左图中使用的数组公式“=SUM(B2: E2*(0.02,0.03,0.04,0.05})”,其中“{0.02,0.03,0.04,0.05}”部分就是常量数组。

②区域数组

区域数组即公式中对单元格区域的引用。例如下右图“=FREQUENCY(B2:B9,D2:D5)” 公式中的B2:B9和D2:D5都是区域数组。

③内存数组

内存数组相对于常量数组和区域数组比较难理解。内存数组存在于内存之中，它不反映在工作表的单元格中，也不直接反映在单元格公式中，内存数组不在人们的视觉范围内，用户看不见它。内存数组是通过公式计算返回的结果在内存中临时构成，并且可以作为一个整体直接嵌入其他公式中继续参与计算的数组。

4.2 快速输入数组公式

在Excel中利用数组公式可以对一组或多组数据进行同时计算，并返回一个或多个计算结果。数组公式输入完成后必须按Ctrl+Shift+Enter组合键才能返回计算结果。下面以实际应用介绍数组公式的输入方法。

①使用数组公式进行多项计算

选中单元格D2, 输入公式“=SUM((B2:B7>0)*B2:B7)”, 按下Ctrl+Shift+Enter组合键，单元格中随即计算出B2:B7单元格区域中所有大于0的数值的总和。

本例中如果在输入完公式后直接按Enter键进行计算，公式将返回另外一个结果，很明显这个结果并不正确，如右图所示。

这是由于输入的是一个数组公式，必须要以Ctrl+Shift+Enter组合键来执行计算。数组公式的运算规律和普通公式的运算规律不同。

使用“公式求值”功能对这两个公式进行分步计算便可以清楚地了解到这两个公式的运算过程。

数组公式“=SUM((B2:B7>0)*B2:B7)” 先执行(B2:B7>0)部分的运算，得出B2>0,B3<0,B4>0,B5<0,B6<0,B7>0 这6个比较运算，由这6 个比较运算返回由6个逻辑值组成的数组{TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}, 然后用这个数组与B2:B7单元格中的数值相乘，得到乘积数组{8000,0,5800,0,0,12000}。最后对乘积数组进行求和，得到最终结果为“25800”。

使用Enter 键返回的公式结果为“8000”,这是因为公式采用了隐含交叉的引用形式。本例是在第2行中输入的公式，第2行与B2:B7单元格区域隐含交叉的单元格是B2, 所以公式中B2:B7 的真正返回值是单元格B2中的数值“8000”。8000>0返回的逻辑值为TRUE, 公式最终变为 “=SUM(TRUE*8000)”。所以SUM函数最终只对8000进行求和，公式返回结果就是8000。