自定义函数
CKLARGEGROUPBY · 大范围分组汇总
更新于 1 小时前 · 阅读 1
CKLARGEGROUPBY · 大范围分组汇总
| 【模块定位】CKLARGEGROUPBY 先按维度聚合求和、再按大小排名，返回指定名次的数据 —— 相当于「带条件的 SUMIFS ＋ 排名」。 功能路径：【报表管理】-【报表平台】- 工作表展示区（单元格公式栏输入） 界面类型：单元格级公式（聚合 ＋ 排名） 前置条件：① 已明确维度列与求和列；② 数据范围已确定；③ 知道要取第几名。 |
一、公式长什么样
公式就是「函数名 ＋ 括号里的一串位置」，从左到右依次是：
=CKLARGEGROUPBY(维度区域, 求和区域, 第几大, 返回类型, [条件区域1, 条件1, 条件2, …])
括号里每个位置分别管什么，先看一眼：
| 位置 | 叫什么 | 这一格放什么 |
| 第 1 个 | 维度区域 | 用来分组的那一列（例：客商） |
| 第 2 个 | 求和区域 | 要加总的那一列（例：余额） |
| 第 3 个 | 第几大 | 要第几名；写 1 就是第 1 名 |
| 第 4 个 | 返回类型 | 0＝返回金额、1＝返回名称 |
| 第 5 个起 | 条件区域 / 条件 | 可选，成对出现；加了就只统计命中的那些行 |
一句话理解整条公式：拿第 1 列分组 → 对第 2 列求和 → 排好名次 → 按第 3 个位置取第几名 → 按第 4 个位置决定给你名字还是金额。
图 1 示例表长这样：左边是维度列（客商）、右边是求和列（余额），公式写在结果区
二、每个位置怎么填
| 位置 | 含义 | 填什么 | 示例 | 取数范围 |
| 维度区域 | 按谁来分组 | 那一列的单元格区域 | $D$9:$D$29 | 取 D 列第 9～29 行（客商） |
| 求和区域 | 加总哪一个数 | 那一列的单元格区域 | $E$9:$E$29 | 取 E 列第 9～29 行（余额） |
| 第几大 | 要第几名 | 数字；做整列前 N 名用 ROW(A1) 下拉 | 1 → 第 1 名 | —（决定取第几名） |
| 返回类型 | 给你名字还是金额 | 0 或 1 | 1 → 返回客商名 | — |
| 条件区域 / 条件 | 只统计其中的一部分 | 条件列的区域 ＋ 条件值，成对出现 | $F$9:$F$29, "单项计提" | F 列第 9～29 行等于「单项计提」的行 |
⚠️ 两个区域的行号要对齐：维度、求和、条件三段的起止行必须落在同一批数据上（示例都是第 9～29 行），否则结果会整体错位。
💡 先聚合、后排名：同一个客商出现多次会先相加（A 公司 100 ＋ A 公司 80 ＝ 180），再拿这个合计数去和别的客商比大小。
三、注意事项
- 返回类型最容易填错：
0是金额、1是名称，不填默认0—— 想显示客商名却出来一串数字，多半是这里。 - 按「求和后」的大小排序：源数据一改，名次和结果都会跟着变。
- 维度列 / 求和列一旦选错，排名会整体偏移，先确认口径再写公式。
- 条件区域与条件必须成对，缺一不可；不加条件就按全部数据聚合。
- 行号不要写死：数据增加时把区域一起放大（
$D$9:$D$29→$D$9:$D$40），否则新增行不进统计。
四、场景实操
场景：想知道「单项计提」口径下，前 5 大客商是谁、各多少钱。
| 步 | 做什么 | 公式 / 取值 |
| 1 | 先确认三列的位置：维度列＝D 列客商、求和列＝E 列余额、条件列＝F 列计提方式 | — |
| 2 | 在结果区第一格取名称（返回类型 1） | =CKLARGEGROUPBY($D$9:$D$29, $E$9:$E$29, ROW(A1), 1) |
| 3 | 旁边一格取金额（返回类型 0） | =CKLARGEGROUPBY($D$9:$D$29, $E$9:$E$29, ROW(A1), 0) |
| 4 | 两列一起向下填充 5 行 | 自动变成第 1～5 名 |
| 5 | 只想看「单项计提」，把条件接在最后，两列都加上 | …, ROW(A1), 1, $F$9:$F$29, "单项计提") |
这条公式的取数范围：把 D 列第 9～29 行（客商）和 E 列第 9～29 行（余额）成组读进来，只保留 F 列第 9～29 行等于「单项计提」的行；按客商汇总余额、降序排名，返回第 N 名的名称与金额。
图 2 实操结果：左侧是维度/求和/条件三列，右侧用 ROW(A1) 做序号，向下填充即得 1、2、3…
💬 评论（0）
还没有评论，来抢沙发～