FineBI常用函数实战：聚合粒度、空值与DEF同环比排查

上周有人在群里问我，FineBI 里算同比到底该用DATEDIF还是DEF，我翻出自己那份记了快两年的函数笔记，发现最早一条写的是“SUM和明细字段别混着写”，旁边画了个大大的感叹号。那份笔记现在有六七十条，按聚合、文本、日期、逻辑、分析函数分了类，每条后面都跟着一行“踩坑备注”。这篇就把这份笔记里最常用的那部分整理出来，顺带把每条函数背后的口径逻辑和实操细节讲清楚。

这是一份给“能看懂字段、但写公式就发怵”的同学准备的常用函数记录，也是一份给已经在写公式、却经常被“数字对不上”折磨的从业者的排查清单。里面不会有函数大全式的罗列，只会保留真正在报表里高频出现的那几十个，以及它们最容易翻车的地方。FineBI 的公式体系跟 SQL 思路很像但并不完全一致，很多坑不是语法错，而是粒度错、空值错、口径错，语法检查一路绿灯，数字出来是另一回事。

1. 先说清楚这份记录是怎么攒出来的

1.1 一个让我彻底重视函数的下午

最早接手的一个销售看板，指标很简单：销售额、订单数、客户数、客单价。四个指标写完只花了半小时，验收时业务方指着客单价说“这个数不对，比我自己算的高”。我一看公式：SUM(销售额)/COUNTD(订单编号)，逻辑上没毛病。问题出在数据集里有一批“退货单”金额是负数，销售额被冲减了，但订单编号还在，分母没跟着减，客单价自然偏高。改法很简单，把分母换成COUNTD(IF(销售额>0, 订单编号, NULL))，数字立刻对上了。

这件事之后我养成了一个习惯：每写一个函数，先在纸上写清楚它“数的是什么、不数什么”。SUM不数空值但数负数，COUNT数非空行，COUNTD数非空去重值，AVG的分母是非空行数而不是总行数。这些规则单看都是常识，凑在一个公式里互相嵌套的时候，就特别容易出错。所以我那份笔记的每一条后面，都强制要求自己写一行“不包含什么”。

1.2 这份记录适合谁、怎么用

如果你刚开始用 FineBI 做自助数据集和仪表板，建议按“聚合 → 逻辑 → 日期 → 文本 → DEF”的顺序看，前四类是日常八成场景的覆盖，DEF 属于进阶，等你遇到“占比”“排名”“同环比”“去重后求和”这类问题时再回头翻。如果你已经能独立写公式，可以直接跳到第 3、7、8 章，那里集中了粒度、空值和排查的内容，是我认为最容易出问题也最省时间的部分。

需要提前说明一点：FineBI 不同版本之间，个别函数的参数顺序、单位缩写、返回值类型会有微调，比如日期差函数里“月”的单位到底是大写M还是小写m，不同版本文档写法不完全一致。我的处理办法是，写的时候盯着公式编辑器左侧的语法提示，它会把你当前版本支持的参数格式直接列出来，比翻文档快得多。下面所有写法都是我在实际版本里跑通的，但你在自己环境里用之前，还是建议先用一行两行数据验一下。

2. 函数体系先摸清：六类函数各管什么

2.1 六类函数的分工与选择顺序

我把 FineBI 里常用的函数按“它解决什么问题”分成六类，这个分类跟官方文档的排布不完全一样，但更贴近写报表时的思考顺序：

选择顺序上，我遵循一个原则：能用聚合解决的，不用 DEF；能用简单IF解决的，不嵌套三层；能在数据集里算的，不放到组件里算。原因很实际，越靠前的写法越容易被人看懂，也越容易在换人维护的时候不出事。一个用DEF写出来的占比公式，三个月后自己都得盯着看半分钟，而一个SUM(IF(...))/SUM(IF(...))的结构，谁接手都能秒懂。

2.2 公式编辑器里三个容易被忽略的规则

第一个规则是聚合函数自动带分组。在 FineBI 里写SUM(销售额)，它不是一个全局总和，而是跟着组件当前的维度走。你拖了“门店”进来，它就是每个门店的销售额；你把“门店”换成“月份”，它自动变成每月销售额。很多人以为是函数变了，其实是维度变了。理解这一点，就不会再问“为什么同一个公式在两个组件里结果不一样”。

第二个规则是明细字段混进聚合公式会改变分组。比如你写销售额 / SUM(销售额)想算占比，语法上可能过得去，但销售额是个明细字段，它会把每一行都当成一个独立分组，结果要么全是 1，要么行数暴涨。正确写法是SUM(销售额) / DEF(SUM(销售额))，或者用聚合套聚合的方式把分母固定住。这条我在第 3 章会展开。

第三个规则是空值参与运算会传染。NULL + 1还是NULL，NULL > 100还是NULL，IF判断里NULL既不等于真也不等于假。所以任何涉及“可能为空”的字段，都要先用ISNULL兜一层。这个习惯能挡掉后面八成的诡异问题，尤其是从数据库同步过来的宽表，外键关联不上就是空，业务上叫“未分类”，公式里叫“炸弹”。

3. 聚合函数：粒度对了数字才对

3.1 五个高频聚合函数的边界

SUM是最没脾气的，但它有两个特性要记住：空值不参与计算，负数照算不误。所以算“销售总额”时如果有退货冲减，结果是净额，这是对的；但如果业务方要的是“毛销售额”，就得写成SUM(IF(金额>0, 金额, 0))。

AVG的坑在分母。它除以的是非空行数，不是总行数。如果某个门店某天没有数据，这一行可能压根不存在，也可能存在但值为空，两种情况算出来的平均值完全不同。做“日均销售额”的时候，我一般不用AVG，而是用SUM(销售额)/COUNTD(日期)显式指定分母，这样口径写在明面上，谁都能复核。

COUNT数非空行数，COUNTD数非空去重值，MAX和MIN除了求极值，还经常被拿来“取最新状态”，比如MAX(更新时间)找到每个客户最近一次下单时间。MEDIAN用得少但在处理金额分布时很好用，平均值容易被大客户拉高，中位数更能反映普通客户的真实水平，做客户分层阈值的时候我会两个都看一眼。

3.2 去重计数 COUNTD 的三个真实坑

第一个坑是空值。COUNTD(客户编号)不会把空值算进去，这通常是对的，但如果你要统计“包含未识别客户的总记录数”，就得用COUNT而不是COUNTD。

第二个坑是性能。COUNTD底层要做去重，数据量上到千万行级别时，明显比SUM慢。我做过一个对比，同一个明细表，SUM秒出，COUNTD要等几秒到十几秒。所以如果一个指标能用“标记位求和”替代，我会优先替代，比如“是否下单”这个 0/1 字段，SUM(是否下单)和COUNTD(IF(是否下单=1, 订单号, NULL))在语义上接近，但前者快得多——前提是“一笔订单一行”这个粒度成立，如果存在一单多行，SUM就会重复计数，这时候只能老老实实用COUNTD。

第三个坑是条件去重。统计“复购客户数”，写法是COUNTD(IF(订单数>=2, 客户编号, NULL))，关键在于不满足条件时返回NULL而不是0或空字符串。返回0的话，所有客户编号都会被计成一个“编号为 0 的客户”，结果永远多 1。

3.3 聚合与明细混用的报错根源

前面提过销售额 / SUM(销售额)这种写法的问题，这里展开说清楚。FineBI 的计算字段大致分两种：聚合计算和明细计算。聚合计算返回的是“按当前维度汇总后的一个值”，明细计算返回的是“每一行一个值”。两者混在一个公式里，引擎必须做一次隐式转换，转换规则是“把明细字段也加入分组维度”，于是你本来想看“每个门店的占比”，结果变成了“每一行占它自己的 100%”。

正确的占比写法有三种，我按推荐顺序列一下：

写法一（推荐，结构最清晰）： SUM(销售额) / DEF(SUM(销售额)) 写法二（维度固定时更稳）： SUM(销售额) / SUM(DEF(SUM(销售额), [门店])) 写法三（总量不变的场景，最简单）： SUM(销售额) / SUM(销售额) 配合"快速计算-占比"

写法一里，DEF(SUM(销售额))不带维度参数，表示“在整个数据集范围内求总和”，作为分母固定不动，分子跟着组件维度变化，占比就出来了。写法二适合你只想在门店这个层级上算占比、不想被其他筛选维度干扰的场景。写法三是很多人不知道的省事办法，FineBI 的快速计算里直接有“占比”选项，能点就别写公式。

注意：任何百分比指标算完之后，记得在组件里设置显示格式为百分比并保留一位小数，别让它显示成0.3456789，业务方看到长串小数会本能地不信任这个数。

4. 文本与脏数据处理：报表可信度的第一道闸门

4.1 截取与定位的组合拳

现实里的编码字段基本都是“拼接型”的，比如门店编码SH001-华东-直营，你要拆出区域。这时候靠LEFT和RIGHT硬切是不行的，因为每段长度不一样，得用FIND定位分隔符再配合MID截取。

取区域（第二段）： MID(门店编码, FIND(门店编码, "-") + 1, FIND(门店编码, "-", FIND(门店编码, "-") + 1) - FIND(门店编码, "-") - 1)

上面这行写法能跑，但可读性极差，维护的人看到会骂人。我的建议是分两步走：先在自助数据集里加一个“第一分隔符位置”的辅助字段，再基于它算第二段，虽然多一列，但三个月后还能看懂。如果分隔符结构固定是三段，用SPLIT会舒服很多，直接按分隔符切开取第几段就行，具体参数顺序看编辑器提示。

4.2 替换、去空格与不可见字符

脏数据里最隐蔽的是空格。用户从别处复制过来的名称，前后可能带普通空格、全角空格、换行符，肉眼看不出来，但分组的时候华东和华东会被当成两个不同的值，报表里就出现两个“华东”，一个有数一个没数。

标准处理是TRIM去首尾空格，但TRIM对全角空格和换行符不一定管用，我会再加一层SUBSTITUTE把常见脏字符替换掉：

SUBSTITUTE(SUBSTITUTE(TRIM(区域名称), " ", ""), "\n", "")

替换类函数里，REPLACE是按位置换，SUBSTITUTE是按内容换，两个别搞混。脱敏场景我用REPLACE比较多，比如手机号中间四位：LEFT(手机号,3) + "****" + RIGHT(手机号,4)，用+拼接比CONCATENATE更短，FineBI 里两种都支持。

4.3 类型转换与大小写统一

从数据库同步过来的字段，类型经常不是你想要的。数字被存成文本，排序就变成字典序，10会排在9前面；日期被存成文本，DATEDIF直接报错。这时候要用TOSTRING或DATEVALUE之类的转换函数统一类型，具体函数名各版本略有差异，在编辑器里输入TO一般就能看到候选列表。

大小写统一用LOWER或UPPER，看着不起眼，但在做关联匹配的时候特别重要。我有一次做客户表关联，两边一个是ABC一个是abc，关联不上，查了半小时才发现是大小写问题。从那以后，凡是文本型主键做关联，我都会在数据集层面先统一一次大小写再去掉首尾空格，宁可多算一个字段，也不在组件里反复排查。

提示：文本函数尽量在自助数据集阶段算完，不要在组件里对明细字段做文本运算。组件里的计算是每次查询都重算的，数据集里算完是物化的，性能差好几倍。

5. 日期函数：口径对齐比函数本身更重要

5.1 基准日与日期构造

TODAY()返回当天日期，NOW()返回当前时间戳，这两个函数最常用于“距今”类指标。但要注意，TODAY()是跟随系统时间的，如果报表是自动刷新后发出去看的，第二天打开数据就变了，这在做月报的时候会引起困惑。我的做法是，凡是需要固定基准日的报表，都建一个“基准日”参数，默认值取TODAY()，但允许手动改，这样复盘历史数据的时候不会乱。

构造日期用DATE(年, 月, 日)。它有个很好用的特性，月或日超出正常范围时会自动进位，比如DATE(2024, 13, 1)会得到 2025 年 1 月 1 日。这个特性拿来找月末特别方便：

当月最后一天： DATEDELTA(DATE(YEAR(日期), MONTH(日期) + 1, 1), -1, "d") 当月第一天： DATE(YEAR(日期), MONTH(日期), 1)

比用DAYSOFMONTH之类的函数更直白，也不依赖版本支持。

5.2 DATEDIF 与 DATEDELTA 的区别

这两个函数名字像，用途完全不同，我见过不少人用混。DATEDIF(开始日期, 结束日期, 单位)算的是两个日期之间的差值，返回一个数字；DATEDELTA(日期, 偏移量, 单位)算的是日期加减偏移量之后得到的新日期，返回一个日期。

算账龄（天）： DATEDIF(下单日期, TODAY(), "d") 算7天前的日期： DATEDELTA(TODAY(), -7, "d")

单位缩写是个高频出错点。年用y，月用M，日用小写d，小时h，分钟m。注意月和分钟在某些版本里是大小写区分的，月大写M、分钟小写m，写错一个字母结果差 12 倍。我现在的习惯是，写完日期差公式先用一行边界数据验一下，比如算一个跨年的日期差，看结果对不对。

DATEDIF还有一个特性值得记：它算“整月”时是向下取整的。1 月 31 日到 2 月 28 日，按"M"算出来是 0 个月，不到一个月。做账龄分档的时候如果不注意，会把一批“快到期”的客户算成“未到期”。处理办法是改用天数分档，或者自己定义分档规则，别直接依赖整月差值。

5.3 周、月、季度口径的对齐

业务方说“本周”，可能是周一到周日，也可能是周日到周六；说“本月”，可能是自然月，也可能是财月。这类分歧没有技术解法，只能事先问清楚。技术上要做的是，把口径固定成字段，而不是每次写公式现算。

我的做法是在数据集里加三个字段：年月、年周、年季。年月用YEAR(日期)*100 + MONTH(日期)生成一个整数，排序天然正确，比字符串拼接的2024-03更好用；年周用周数函数生成，注意年初那几天可能被算到上一年的最后一周，具体归属规则看函数参数；年季用YEAR(日期)*10 + CEILING(MONTH(日期)/3.0, 0)生成，注意除完要向上取整，MONTH/3直接整除会把 3 月算成第 1 季度。

这些字段一次算好放在数据集里，后面所有组件都复用，口径就不会在各处漂移。

5.4 同比环比的标准写法

同环比是问得最多的。FineBI 的图表里有快速计算可以直接出“同比增长率”“环比增长率”，能用快速计算就别手写，省事且不易错。但快速计算有个限制：它对维度的顺序有要求，维度一多就可能算错层级。

需要精确控制的时候，我用DEF手工构造上期值：

本期销售额： SUM(销售额) 上年同期销售额： DEF(SUM(销售额), [门店], [年月]) 配合日期维度偏移 环比增长率： (SUM(销售额) - 上期值) / 上期值

实际写的时候，更稳妥的路子是在数据集里先把“上年同月”这个字段算出来，然后用它作为维度做一次自关联或者用DEF按它聚合。这样虽然多一步，但每一层的口径都是显式的，出错能定位到具体哪一步。我吃过亏，一个用快速计算做出来的同比看板，换了个维度组合之后数字整体偏大，查了半天发现是比较基准被算成了“上一个有数据的月份”而不是“上一个月”，中间断月导致基准错位。

注意：算增长率一定要处理分母为 0 和分母为空的情况，否则报表里会出现无穷大或者空白。标准兜底写法是IF(ISNULL(上期值) OR 上期值 = 0, NULL, (本期 - 上期) / 上期)。

6. 逻辑函数与空值：让报表不翻车

6.1 IF 嵌套与分支写法取舍

IF(条件, 真值, 假值)是最基础也最常用的。两三层嵌套还好，超过四层就开始难读了，括号对不上是常事。这时候有两个选择：换成多分支的判断函数（不同版本叫法不一，编辑器里输入IF一般会带出候选），或者干脆拆成多个计算字段，每个字段负责一层判断，最后再拼。

我更倾向拆字段。比如客户分层，与其写一个巨大的嵌套IF一次分出五档，不如先算“近12个月消费额”“近12个月订单数”两个字段，再用一个较短的IF组合出分层。拆开之后每一层都能单独拖到表里验证，出错范围小，别人接手也能看懂逻辑。

分层打标的模板我一般是这么写的：

IF(ISNULL(消费额), "未消费", IF(消费额 >= 10000, "高价值", IF(消费额 >= 3000, "中价值", IF(消费额 >= 500, "低价值", "潜力"))))

注意最外层先把空值处理掉，否则NULL >= 10000返回空，整个分层结果就是空，这类客户在报表里会直接消失，看数的人以为没这批人。

6.2 零除、空值与隐式转换

零除是报表里最常见的报错来源。SUM(金额)/SUM(数量)，某个维度组合下数量是 0，结果要么报错要么显示异常值。标准处理是分母兜底：

IF(SUM(数量) = 0 OR ISNULL(SUM(数量)), NULL, SUM(金额) / SUM(数量))

返回NULL而不是0，是有讲究的。返回0会被当成“这个品类的单价是 0”，参与后续平均值计算时把整体拉低；返回NULL表示“没有数据”，在图表里显示为空缺，看数的人知道这是缺口而不是真实值。这个区别在做汇总分析时影响很大。

隐式转换的坑在数值和文本混用。有些字段在数据库里是varchar，存的是"1,234"这种带千分位的字符串，直接SUM会报错或者算出奇怪结果。得先用替换函数去掉逗号，再转成数值。我一般会在数据集里专门做一个“清洗后数值”字段，命名上带前缀区分，避免后面误用原始字段。

6.3 AND、OR 与条件组合

AND和OR组合多个条件时，一定要加括号明确优先级。A AND B OR C在不同引擎里的结合顺序可能不同，虽然大多遵循AND优先，但写清楚总没坏处。我的写法是把每组条件都括起来：(A AND B) OR (C AND D)。

NOT用得少，一般用<>或者!=更直观。ISNULL是必须记住的，它在IF里配合使用，是空值处理的主力。另外有个细节，ISNULL只判断空，不判断空字符串。文本字段里""和NULL是两回事，从 Excel 导入的数据经常是空字符串，得用LEN(TRIM(字段)) = 0来判断。这个区别我调了两回才记住。

7. DEF 函数族：跨粒度计算的主力

7.1 DEF 到底在做什么

一句话概括：DEF让你在当前的聚合粒度之外，另外指定一套维度重新算一遍聚合。它等价于 SQL 里的子查询或者窗口函数，但在 FineBI 里写起来更短。

语法结构大致是DEF(聚合表达式, 维度1, 维度2, ...)。比如DEF(SUM(销售额), [客户])，意思是“不管当前组件按什么维度分组，都按客户这个维度重新算一遍销售额总和”。每个明细行拿到的是它所属客户的总销售额，拿这个值再和当前行做除法，占比就出来了。

理解DEF的关键是分清两个粒度：组件粒度（你拖进图表的维度）和DEF 粒度（你写在函数里的维度）。两者可以完全不同，这正是它有价值的地方。没有DEF的时候，想做“明细行对比所属分组汇总”只能靠自关联，写起来麻烦且容易重复。

7.2 DEF_ADD 与 DEF_SUB 的使用场景

DEF_ADD是在当前维度基础上追加维度，DEF_SUB是减少维度。这两个在写多层占比的时候特别顺手。

举个例子，组件里拖了“区域”和“城市”两个维度，你想算“每个城市销售额占所属区域的比例”。用DEF_SUB的思路是，在当前粒度基础上把“城市”这个维度去掉，剩下“区域”粒度，得到区域总额：

城市占区域比例： SUM(销售额) / DEF_SUB(SUM(销售额), [城市])

反过来，如果组件里只有“区域”，你想算“该区域占全国的比例”，用DEF_ADD把“全国”这个更高层级补进来，或者直接用不带维度的DEF(SUM(销售额))拿全局总额。实际用下来，DEF_SUB的场景比DEF_ADD多，因为大部分占比计算是“往下看往上算”。

7.3 去重、排名与 TopN 的写法

DEF组合COUNTD能做“每个客户的去重商品数”这类指标：DEF(COUNTD(商品编号), [客户])。这类指标如果不用DEF，在明细粒度上直接写COUNTD会得到全局去重数，不是每个客户各自的。

排名用DEF加上排名函数，或者用组件自带的排序功能。我倾向后者，因为排名涉及“并列怎么处理”“空值排第几”这些细节，函数参数的默认行为不一定符合预期，在图表里点排序更直观。真要用函数算，记得先明确“按降序排、并列取相同名次还是跳过名次”。

TopN 场景我一般不用公式，直接用组件的筛选功能，设置“按销售额取前 10”。公式实现 TopN 需要在DEF里嵌套排名判断，可读性差，而且筛选条件一变就得改公式。

提示：DEF本质上是一次额外的聚合计算，数据量大时会明显拖慢查询。我的经验是，明细行数在百万级以内可以放心用，超过千万行就要评估一下，能提前在数据集里算好落表的，就不要放在组件里实时算。

8. 报错与数字对不上的排查路径

8.1 常见报错对照表

8.2 数字对不上的四步排查法

我的固定排查顺序是：先查行数，再查关联，再查粒度，最后查空值。

第一步查行数。把明细表拖到最细粒度，看总行数是多少，和数据库里COUNT(*)对一下。行数对不上，后面的都不用查了，问题在数据接入环节。

第二步查关联。如果用了多表关联，先单独看每张表的行数和主键去重数，判断关联键是不是唯一。一对多关联后行数会膨胀，这是“数字偏大”的头号原因。我遇到过一次，客户表和订单表关联，客户表里同一个客户有三条地址记录，关联后订单翻了三倍，销售额自然虚高。

第三步查粒度。把指标拖到不同维度组合下看，看哪个组合开始出现异常。如果全局汇总是对的、拆到明细就错，基本可以确定是粒度问题，去检查公式里有没有明细字段混进聚合。

第四步查空值。把待查字段单独拿出来COUNT一下，和总行数比，看缺多少。缺失的部分是不是被IF分支吞掉了，是不是让SUM少算了。这一步最容易被跳过，但我统计下来，至少三成的“数字对不上”最后都归到空值上。

9. 让公式更好维护的几个习惯

写公式这件事，写出来只是开始，维护才是成本大头。我在几个项目里慢慢养成了几个习惯，成本很低但收益明显。

第一个是命名带语义。计算字段别叫“新建公式1”“指标A”，就叫“销售额_净额”“客户数_去重_近12月”。名字长一点没关系，搜索的时候能搜到、看名字知道口径，比什么都强。我现在的命名规则是“指标_口径_时间范围_特殊说明”，四段式，不够就省略后面几段。

第二个是把复杂公式拆成字段链。一个五层嵌套的公式，拆成三个中间字段，每层单测一次，最后拼起来。中间字段可以隐藏掉不展示，但保留在数据集里，后面改口径的时候改一层就行，不用推倒重来。

第三个是记录口径来源。每个容易引起歧义的指标，在描述里写清楚“含不含退货”“按哪个时间字段”“去重按什么键”。这话写给自己看是备忘，写给别人看是防扯皮。

第四个是优先用组件自带能力。占比、排名、同环比、累计，这些 FineBI 的快速计算里基本都有，点两下就能出，还自带格式处理。自己写公式的唯一理由是快速计算口径不满足，比如需要自定义分组或者跨层级计算。别为了“显得专业”去手写，维护成本是自己的。

最后一个习惯是定期回头看。我那份函数笔记每季度翻一次，把三个月没用过的划掉，把新踩的坑补进去。函数常用的其实就那么二十来个，剩下的都是为了解决某个特定问题临时学的，不常用的记了也忘，不如保持笔记精简，只留高频和易错的那部分。

这套东西没有多高深，都是被一个个报错和一次次对数逼出来的。真要说有什么心得，就是每次写完公式别急着发布，先用两三个边界值验一下：空值、零值、负数。这三个值能过，八成场景就没问题了。剩下两成，等它出问题的时候你自然会想起来该查哪里，因为排查路径已经刻在脑子里了。