EXCEL Function Part II Chapter 5 —— 公式中的王者，数组公式

---

typora-copy-images-to: EXCEL FUNCTION PART II

Chapter 5 公式中的王者——数组公式

数组与数组公式

• 数组由多数据组成，每个数据称为数组的元素
• EXCEL公式中的数组分类
  • 区域数组——由单元格组成的数组，如B2：B5
  • 常量数组——如{1;2;3;4;5}or{“a”;“b”;“c”;“d”}

= SUM({1;2;3;4;5})
# 上述公式只有1个参数
# 如果某个元素是文本类型，要将其写在英文半角双引号中
{1;"Excel";"2";"Home";3}

• 将区域数组转为常量数组——按 “F9”

• 数组的维数

  • 多个单元格组成一维数组，一行或一列
  • 多行多列为二维数组
  • 多个二维数组组成三位数组

• 常量数组中元素的分隔符

  • 行数组用逗号
  • 列数组用分号
  • 多行多列既有分号也有逗号

• 数组公式的特殊之处——公式中单个数据或单元格的位置，用数组参与公式

• 数组公式可以返回数量不定的结果，因此需要在计算结果前事先选中结果返回的单元格区域

• 数组公式最外层总有一对大括号，并非输入的，而是自动加入的

输入和编辑数组公式

• 输入数组公式时，应该按下<Ctrl+Shift+Enter>三键
• 多单元格数组公式，返回结果包括多个数据，在输入数组公式前，应先选中与公式返回结果行列数相同的单元格后再输入公式
• 编辑或删除数组公式，必须选中比公式占用单元格较小的区域编辑公式

数组公式的优势

• 减少公式的录入量

#求1到100的自然数和
{ = SUM(ROW(A1:A100))}
# 减少公式的录入量,不需要占用其他单元格

# 将数据保留两位小数后再求和
= SUM(ROUND(A2:A10),2)
# 数组公式是由多个具有某种共同特征的普通公式的组合

• 保护公式完整不被破坏，不能更改数组的某一部分，如果需要删除或者删除修改多单元格数组公式，不需先选中所有占用的单元格
• 提高公式的运算效率，有效节省批量多重计算的时间，较少占用内存

公式中的数组运算规则

• 公式处理数组的两种方式
  • 作为整体统一计算和汇总
  • 对公式中数组包含的数据分别进行计算

• 行列数相同的数组运算，对数组与数组之间的运算，应尽量保证参与计算的数组行列数相同
• 单一数据与数组进行运算，EXCEL依然能完成计算
• 单列数组与单行数组的运算，返回的是一个多行多列的二维数组

• 一维数组与二维数组的运算
  • 如果让单列的一维数组与二维数组正常运算，应保证两个数组行数相同
  • 如果让单行的数组与一个二维数组进行运算，应保证两个数组的列数相同

• 注意点：返回数组的行列数，一定与二维数组相同
• 数组在计算时的自动扩展，有观点认为，EXCEL自动将参与计算的数组进行扩展，使其变成为行列数相等的两个数组间的运算
• 行列数不等的两个数组的运算，会返回错误
• 注意点：只有单个数据、单列数组或单行数组在运算时才能扩展
• 有效元素为两个数组中对应元素的计算结果，否则为错误值
• 剔除错误值之后的运算

认识ROW & COLUMN 函数

= ROW()
# 返回参数中单元格的行号
= ROW(B2)
# 返回B2单元格的行号，也就是2
= ROW(B2:C5)
# 返回4个数值的数组{2;3;4;5}

• 注意点：ROW函数返回的结果是一个数组
• 关于ROW函数的几个疑问
  • = ROW(1:3) 表示返回由1到3行各行行号组成的数组
  • ROW(A1) & ROW(1:1) 的区别在于删除A1单元格是否会对函数的计算结果产生影响
  • ROW函数与ROWS函数的区别
    • ROW返回的是区域行号组成的数组
    • ROWS函数返回的是参数中区域的行数，是常量

= COLUMN()
# 返回公式所在单元格的列号
= COLUMN(F:F)
# 返回F列的列号
= COLUMN(A:F)
# 返回A：F各列的列号

• COLUMNS函数与COLUMN函数的区别

  • COLUMNS 返回的列数，不是数组
  • COLUMN 返回的是列号，是数组

• 巧用ROW函数构造序列辅助解题

  • 用公式构造等差数列，= ROW(5:5)
  • 借助等差数列拆分字符

• 让每个姓名重复2次，构造“1,1,2,2,3,3…”的数列

• 其它的等差数列构建法

• 用TRANSPOSE函数对数组进行转置
• COLUMN 函数也可以构建数组序列，不过，COLUMN构造的是一行数组

= TRUNC(ROW(B:G)/2)
# 行，11,22,33......
# ROW更有优势，直观
= TRANSPOSE(TRUNC(ROW(2:7)/2))
# 行数组转化为列数组

用数组公式按条件求和

• 求所有商品的销售总额（不使用辅助列）

• 求7月份所有商品的销售总额

• 借助算术运算合并求和条件与数据

• 按多条件求商品销售额，无论是单条件求和还是多条件求和，用来解决的数组公式结构上都是相同的，都是用SUM函数求多个数组的乘积

• 总结：

# 条件求和的问题公式：
= SUM((条件1区域=条件1)*（条件2区域=条件2）*（条件n区域=条件n)*求和区域）
#交换公式中各个数组的顺序，并不会影响公式的计算结果

使用数组公式按条件计数

• 根据考试成绩统计及格人数

= SUM(--(B2:B10>=60)) 
# 逻辑值转换成了数值

• 根据考试成绩统计双科及格人数

= SUM((B2:B10>=60)*(C2:C10>=60))
# 都返回逻辑值数组

• 求1到100自然数中能被3整除的数据个数
  • COUNTIF &COUNT 函数的第1参数只能是单元格引用，不能是公式返回结果

= SUM(--MOD(ROW(1:100),3)=0))
# 生成数组
# 求余数
# 返回逻辑值
# 转换为数值
#求和

#求1到100的自然数中能同时被3和5整除的数据个数
= SUM((MOD(ROW(1:100),3)=0)*(MOD(ROW(1:100),5)=0))

用数组公式查询和筛选数据

• 让VLOOKUP函数也能逆向查询
  • 使用VLOOKUP函数时，应保证数据列表中的查找值位于返回值的左侧，否则不能完成查询任务

• 用VLOOKUP函数按多条件查询数据

• 使用INDEX & MATCH 函数按多条件查询数据（替代VLOOKUP）

• 筛选满足条件的数据
  • 删选“第一组”所有人员的信息

{= INDEX(A:A,SMALL(IF($A$1:$A$20="第1组",ROW($1:$20),2^20),ROW(1:1)))&""}
# 计算IF函数第1参数是否为“第一组”，返回逻辑数组
# TRUE，显示行号
# FALSE，返回2^20数值
# SMALL函数求一组数据中第几个最小数据
# ROW（1:1）用于指定要返回第1参数中第几个最小值

• 多条件筛选人员信息
  

用数组公式计算和处理日期

• 求2015年8月包含几个星期一

= SUM(--WEEKDAY(DATE(2015,8,ROW(1:31),2)=1))
# WEEKDAY 的参数2，表示返回的星期一的数值为1，否则为2
# 返回逻辑值数组，转化为数值后求和

• 求今天是今年的第几天

其他常见的计算问题

• 统计单元格区域中不重复的数据个数

• 求字符串中指定字符的个数

# 截取到字符串的各个字符后
= MID(A2,ROW(1:12),1)
# 第一参数是A2
# 第二参数是数组

# 条件计数
= SUM(--(MID(A2,ROW(1:99),1)="E"))

• 求字符串中包含的数字个数

= SUM(--ISNUMBER(-MID(A2,ROW(1:99),1)))
or = COUNT(-MID(A2,ROW(1:99),1))