Excel超级函数(基于Microsoft 365整理)

Excel超级函数实战指南：高效数据处理技巧 Microsoft 365的Excel超级函数帮你轻松搞定复杂任务！重点介绍PIVOTB函数，它能按指定字段分组、聚合、排序和筛选数据。此外，XLOOKUP精准匹配，TEXT灵活格式化，SUMPRODUCT多条件计算，AGGREGATE和SUBTOTAL智能汇总掌握这些函数，数据整理快人一步！

Excel超级函数 (基于Microsoft 365整理)

• SUBTOTAL
  • 語法：SUBTOTAL(function_num,ref1,[ref2],...)
  • 11种动态功能参数（function_num）
    • 参数：1-11包含隐藏的数据进行计算，101-111忽略隐藏数据进行计算
    • 1 & 101
      • AVERAGE
        • 平均值
    • 2 & 102
      • COUNT
        • 数值计数
    • 3 & 103
      • COUNTA
        • 非空值计数
    • 4 & 104
      • MAX
        • 最大值
    • 5 & 105
      • MIN
        • 最小值
    • 6 & 106
      • PRODUCT
        • 乘积
    • 7 & 107
      • STDEV
        • 标准偏差
    • 8 & 108
      • STDEVP
        • 总体标准偏差
    • 9 & 109
      • SUM
        • 求和
    • 10 & 110
      • VAR
        • 方差
    • 11 & 111
      • VARP
        • 总体方差
  • Ref1
    • 必填
      • 您要取得小計值的第一個具名範圍或參照
  • Ref2,...
    • 选填
      • 第 2 個到第 254 個要計算小計的具名範圍或參照
• AGGREGATE
  • 語法：AGGREGATE(function_num, options, ref1, [ref2], …)
  • 19种动态功能参数（function_num）
    • 必填
      • 1 到 19 之间数字指定要用函数
      • 1
        • AVERAGE
      • 2
        • COUNT
      • 3
        • COUNTA
      • 4
        • MAX
      • 5
        • MIN
      • 6
        • PRODUCT
      • 7
        • STDEV.S
      • 8
        • STDEV.P
      • 9
        • SUM
      • 10
        • VAR.S
      • 11
        • VAR.P
      • 12
        • MEDIAN
          • 中值
      • 13
        • MODE.SNGL
          • 众数
      • 14
        • LARGE
          • 第K个最大值
            • 必填
      • 15
        • SMALL
          • 第K个最小值
            • 必填
      • 16
        • PERCENTILE.INC
          • K百分点值(含0与1)
            • 必填
      • 17
        • QUARTILE.INC
          • 四分位值(含0与1)
            • 必填
      • 18
        • PERCENTILE.EXC
          • K百分点值(不含0与1)
            • 必填
      • 19
        • QUARTILE.EXC
          • 四分位值(不含0与1)
            • 必填
  • 8种应用场景参数（options）
    • 必填
      • 0-7之间数字指定一个数值，决定在函数的计算区域内要忽略哪些值
      • 0 或省略
        • 忽略嵌套 SUBTOTAL 和 AGGREGATE 函数
      • 1
        • 忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数
      • 2
        • 忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数
      • 3
        • 忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数
      • 4
        • 忽略空值
      • 5
        • 忽略隐藏行
      • 6
        • 忽略错误值
      • 7
        • 忽略隐藏行和错误值
  • Ref1
    • 选填
      • 函数的第一个数值参数，这些函数具有要计算聚合值的多个数值参数
  • Ref2,...
    • 选填
      • 要计算聚合值的 2 至 253 个数值参数
• SUMPRODUCT
  • 求相应范围或数组的乘积的总和。 默认运算是乘法，但也可以加法、减法和除法
  • 语法：SUMPRODUCT (array1， [array2]， [array3]， ...)
  • array1
    • 必需
      • 其相应元素需要进行相乘并求和的第一个数组参数。
  • [array2]、[array3],...
    • 选填
      • 2 到 255 个数组参数，其相应元素需要进行相乘并求和。
  • 可应用多条件数组求乘积总和
    • 单条件乘积求合
      • SUMPRODUCT((条件区域=条件)*数据区域1*数据区域2)
    • 多条件乘积求合
      • SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)*数据区域1*数据区域2)
    • 单分组乘积排名次
      • SUMPRODUC((条件区域1>=条件1)*数据区域1*数据区域2)
    • 多分组乘积排名次
      • SUMPRODUC((条件区域1>=条件1)*数据区域1)*(条件区域2=条件2)*数据区域2
• TEXT
  • 语法：TEXT(value, format_text)
  • value
    • 必填
    • 是要转换的数值
  • format_text
    • 必填
    • 是用于指定数字格式的文本
    • 应用1
      • 一个公式实现三种功能，如同时显示“￥20.00元”
      • 公式为：=TEXT(数据区域,"￥0元")
    • 应用2
      • 处理日期类问题
        • 将日期显示完整的年月日，同时显示出是星期几
        • 公式：=TEXT(数据区域,"e年mm月dd日 aaaa")
    • 应用3
      • 情况显示不同结果
        • 通过“公式=TEXT(数据区域，判断条件1，结论1，判断条件2，结论2），就可以用TEXT实现判断
        • TEXT(B2,"[>=80]优秀;[>=60]一般;不合格")：=TEXT(A1-2000,"超额0元;还差0元;刚好"
        • 公式：=TEXT(A1,"[>=80]优秀;[>=60]一般;不合格")
    • 应用4
      • 金额变成大写
        • 将金额变成大写，并且只保留整数部分
        • 公式为：=TEXT(D2,"[DBNum2]0元")
• XLOOKUP
  • 语法：=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value
    • 必填
    • 要搜索的值 *如果省略，XLOOKUP 将返回在 lookup_array 中找到的空白单元格。
  • lookup_array
    • 必需
    • 要搜索的数组或区域
  • return_array
    • 必需
    • 要返回的数组或区域
  • [if_not_found]
    • 选填
      • 如果未找到有效的匹配项，则返回你提供的 [if_not_found] 文本。
      • 如果未找到有效的匹配项，并且缺少 [if_not_found]，则返回 #N/A 。
  • [match_mode]
    • 选填
    • 指定匹配类型
      • 0 -
        • 完全匹配；如果未找到，则返回 #N/A。 这是默认选项
      • -1 -
        • 完全匹配；如果没有找到，则返回下一个较小的项
      • 1 -
        • 完全匹配；如果没有找到，则返回下一个较大的项
      • 2 -
        • 通配符匹配；其中 *, ? 和 ~ 有特殊含义
  • [search_mode]
    • 选填
    • 指定要使用的搜索模式
      • 1 -
        • 从第一项开始执行搜索。 这是默认选项。
      • -1 -
        • 从最后一项开始执行反向搜索
      • 2 -
        • 执行依赖于 lookup_array 按升序排序二进制搜索。 如果未排序，将返回无效结果
      • - 2 -
        • 执行依赖于 lookup_array 按降序排序二进制搜索，如果未排序，将返回无效结果
• PIVOTBY
  • 根据指定的行列字段，对数据进行分组、聚合、排序、筛选
  • 语法：=PIVOTBY (row_fields，col_fields，values，function，[field_headers]，[row_total_depth]，[row_sort_order]，[col_total_depth]，[col_sort_order]，[filter_array]，[relative_to])
  • row_fields
    • 必填
      • 需要统计的行字段数据区域
  • col_fields
    • 必填
      • 需要统计的列字段数据区域
  • values
    • 必填
      • 需要统计的值字段数据区域
  • function
    • 必填
      • 统计方式
        • 分类汇总的计算方式是什么
        • SUM
        • PERCENTOF
        • AVERAGE
        • MEDIAN
        • COUNT
        • COUNTA
        • MAX
        • MIN
        • PRODUCT
        • ARRAYTOTEXT
        • CONCAT
        • STDEV.S
        • STDEV.P
        • VAR.S
        • VAR.P
        • MODE.SNGL
        • LAMBDA
          • 提供矢量向量
  • [field_headers]
    • 选填
      • 一个数字，指定 row_fields、col_fields 和 值是否具有标头，以及是否应在结果中返回字段标头
      • 缺失：自动
      • 0：否
      • 1：是且不显示
      • 2：否，但生成
      • 3：是并显示
  • [row_total_depth]
    • 选填
      • 行标题是否需要包含总计
      • 缺失：自动
      • 0： No Totals
      • 1： Grand Totals
      • 2： Grand and Subtotals
      • -1： Grand Totals at Top
      • -2： Grand total and Subtotals at Top
  • [row_sort_order]
    • 选填
      • 行字段排序方式
        • 1：升序
        • -1：降序
  • [col_total_depth]
    • 选填
      • 列标题是否需要包含总计
      • 缺失：自动
      • 0： No Totals
      • 1： Grand Totals
      • 2： Grand and Subtotals
      • -1： Grand Totals at Top
      • -2： Grand total and Subtotals at Top
  • [col_sort_order]
    • 选填
      • 列字段排序方式
        • 1：升序
        • -1：降序
  • [filter_array]。
    • 选填
      • 是否需要进行筛选
      • 一个面向列的 1D 布尔值数组，指示是否应考虑相应的数据行
  • [relative_to]
    • 选填
      • 使用需要两个参数的聚合函数时，relative_to 控制向聚合函数的第二个参数提供哪些值。 这通常在为函数提供PERCENTOF时使用
      • 0：列汇总 (默认)
      • 1：行总计
      • 2：总计
      • 3：父
      • 总计4：父行总计
• 根据指定的字段对数据查找
• 统计时，若无法识别文本类数据时，统计区域前加"--"