PlaidXL 自定义函数
PlaidXL 为 Excel 添加了一些函数,可将 PlaidCloud 数据直接读入单元格。在单元格中输入 =PLAIDXL.,Excel 的自动完成会列出这些函数,并附有每个参数的简短说明。任务窗格的 Formulas 选项卡也可以为您编写公式;请参阅从任务窗格插入公式。
| 分组 | 函数 | 用途 |
|---|---|---|
| 列表 | LIST_PROJECTS、TABLE.TBL_LIST、TABLE.GET_COLUMNS、DIMENSION.DIM_LIST |
查看可用于构建的内容 |
| 表 | TABLE.GET_TABLE |
将整个表读入一个区域 |
| 层次结构 | DIMENSION.GET_DIMENSION、DRILL |
将维度的节点布局为报表行 |
| 值 | CELL、CELLS |
为任意节点组合计算汇总值 |
| 指标 | METRIC、METRIC_BREAKDOWN |
读取受治理的业务指标,与仪表板显示的一致 |
| 构建块 | REPR.TABLE_REPR、REPR.MEASURE、REPR.MEASURES、REPR.MEASURE_TYPE、REPR.DIMENSION_REPR、REPR.DRILL_KEY |
描述 CELL、CELLS 和 DRILL 所用的表、度量、聚合方式、节点和下钻深度 |
构建报表展示了这些函数如何配合使用。
函数的工作方式
Section titled “函数的工作方式”- 先登录。 这些函数使用您的 PlaidCloud 登录信息和权限读取数据,因此请在使用之前从任务窗格登录。
- 从工作簿的项目读取。 每个函数都从任务窗格中与工作簿关联的项目读取。
GET_TABLE、TBL_LIST和DIM_LIST还可以接受可选的项目 ID,以便在不更改关联的情况下读取其他项目。每个工作簿都有各自的项目,因此两个已打开的工作簿可以读取不同的项目。 - 结果会溢出。 返回列表或表的函数会填充其下方(或右侧)的单元格。请为结果留出空间;如果有内容阻挡,Excel 会显示
#SPILL!错误。 - 重新计算以获取更改。 当某个参数更改时,PlaidXL 公式会重新获取数据。要获取 PlaidCloud 中的新数据,或在任务窗格中关联的其他项目,请使用功能区的 刷新 菜单:Recalculate PlaidXL formulas 会重新计算工作簿中的所有 PlaidXL 公式,Refresh this sheet 和 Refresh selection 会重新计算它们所涵盖的公式。Refresh all tables 刷新的是已检索的表,而不是公式。请参阅刷新。
- 使用准确的名称。 输入表、列、维度和节点名称时,请与 PlaidCloud 中显示的完全一致。
方括号中的参数为可选参数。
LIST_PROJECTS
Section titled “LIST_PROJECTS”=PLAIDXL.LIST_PROJECTS([horizontal])
列出您可以打开的项目名称,按名称排序。
horizontal:设为TRUE时横向排成一行。默认为FALSE,即一列。
TBL_LIST
Section titled “TBL_LIST”=PLAIDXL.TABLE.TBL_LIST([project_id], [horizontal])
列出项目中的表名称,按名称排序。
project_id:要读取的项目。默认为工作簿的项目。horizontal:设为TRUE时横向排成一行。默认为FALSE,即一列。
GET_COLUMNS
Section titled “GET_COLUMNS”=PLAIDXL.TABLE.GET_COLUMNS(table_name, [column_filter], [orientation])
列出所选项目中某个表的列名称。
table_name:该表在 PlaidCloud 中的名称。column_filter:数据类型(如"text"),仅列出该类型的列。省略则列出所有列。orientation:"vertical"(默认)表示一列,"horizontal"表示一行。
DIM_LIST
Section titled “DIM_LIST”=PLAIDXL.DIMENSION.DIM_LIST([project_id], [horizontal])
列出项目中的维度名称。
project_id:要读取的项目。默认为工作簿的项目。horizontal:设为TRUE时横向排成一行。默认为FALSE,即一列。
GET_TABLE
Section titled “GET_TABLE”=PLAIDXL.TABLE.GET_TABLE(table_name, [project_id], [limit])
返回表的行及标题行,从公式所在单元格开始溢出。空值以空白单元格返回。
table_name:该表在 PlaidCloud 中的名称。project_id:要读取的项目。默认为工作簿的项目。limit:返回的最大行数。默认为 1,048,575,即工作表在标题行下方可容纳的最大行数。
GET_TABLE 会返回所有列。若要选择列或筛选行,请改为在任务窗格中检索该表。
GET_DIMENSION
Section titled “GET_DIMENSION”=PLAIDXL.DIMENSION.GET_DIMENSION(dimension_name, start_node, [hierarchy], [orientation], [generation_limit])
布局维度的节点,从 start_node 开始,向下遍历其后代。每一代比其父级多缩进四个空格,Excel 大纲分组可让您折叠和展开每一级。
dimension_name:维度的名称,或其在项目中的路径。start_node:起始节点,例如维度的根节点。hierarchy:要沿用的备用层次结构。默认为"main"。orientation:"vertical"(默认)表示将节点向下列在一列中,"horizontal"表示横向列在一行中。generation_limit:返回的代数,start_node算作第一代:2返回起始节点及其子节点。省略则返回所有代。
=PLAIDXL.DRILL(dimension_name, start_node, drill_key, [hierarchy], [exclude], [expand], [orientation])
返回 start_node 以及 drill_key 所要求的其下节点,按代缩进,并分组到 Excel 大纲中。
dimension_name:维度的名称,或其在项目中的路径。start_node:下钻的起始节点。留空("")则从维度的顶层节点开始。维度本身视为下钻键的第 0 代且不会列出,因此顶层节点是第 1 代:>[1]仅列出顶层节点,>[0]则不列出任何内容。drill_key:下钻的范围,来自DRILL_KEY:所有后代、仅叶节点、若干层级,或一段代的范围。hierarchy:要沿用的备用层次结构。默认为"main"。exclude:要排除的节点及其下的所有内容:可以是一个节点、一个列表或一个单元格区域。起始节点永远不会被排除。expand:即使超出drill_key所要求的深度也要显示其子节点的节点:可以是一个节点、一个列表或一个单元格区域。只有下钻显示出的节点才会展开,因此要展开某个孙节点,还需列出它的父节点。orientation:"vertical"(默认)表示将节点向下列在一列中,"horizontal"表示横向列在一行中,不缩进并按列分组。任何其他值都会产生错误。
exclude 和 expand 接受文本或数字形式的节点 ID;区域中的空单元格和错误会被忽略。将参数留空("")即可跳过,例如 =PLAIDXL.DRILL("Period", "FY2026", PLAIDXL.REPR.DRILL_KEY("1 level"), "main", "", "", "horizontal")。
例如,=PLAIDXL.DRILL("Region", "Total", PLAIDXL.REPR.DRILL_KEY("leaves")) 会列出 总计,随后是其下的每个叶区域。
下钻命令会为您编写和编辑 DRILL 公式:选择一个节点后即可向下钻取、向上钻取、保留或移除节点。
CELL 和 CELLS 计算一个度量(表的数值列),并汇总到您在一个或多个维度中选择的节点。可将每次调用视为报表中的一个交叉点:EMEA 在 FY2026 的 收入。
您使用下面的构建块函数(通常放在各自的单元格中)来描述这个交叉点,然后以任意顺序将这些单元格传给 CELL 或 CELLS:
| 参数 | 构建方式 | 是否必需 |
|---|---|---|
| 要读取的表 | TABLE_REPR |
恰好一个 |
| 要聚合的列 | CELL 使用 MEASURE,CELLS 使用 MEASURES 或多个 MEASURE 单元格 |
是 |
| 聚合方式 | MEASURE_TYPE |
否,默认为 sum |
| 对某列的筛选器 | DIMENSION_REPR |
至少一个,每列一个 |
每个筛选器都指定一个 表的列 和一个值。当该列在 PlaidCloud 中关联到某个维度时,该值是一个节点,筛选器会包含在维度的主层次结构(或您指定的层次结构)中汇总到该节点的所有内容:在关联到地理维度的 Region 列上,节点 EMEA 涵盖其下的每个国家。在未关联到维度的列上,筛选器按值精确匹配。
本页的示例使用的表,其 Region 和 Period 列已关联到维度。请使用您自己表中的列名称。
=PLAIDXL.CELL(table, measure, [measure_type], node, [node], ...)
返回一个数字:对落在您传入的每个节点之下的行聚合得到的度量。
A1 =PLAIDXL.REPR.TABLE_REPR("Sales")A2 =PLAIDXL.REPR.MEASURE("revenue")A3 =PLAIDXL.REPR.MEASURE_TYPE("sum")A4 =PLAIDXL.REPR.DIMENSION_REPR("Region", "EMEA")A5 =PLAIDXL.REPR.DIMENSION_REPR("Period", "FY2026")B1 =PLAIDXL.CELL(A1, A2, A3, A4, A5)B1 显示 EMEA 在 FY2026 的总收入。
在 CELL 上输入数字即可将其写入 PlaidCloud。请参阅将值输入 PlaidCloud。
包含大量 CELL 公式的工作表计算很快:一起计算的 CELL 公式会合并为一次请求发送到 PlaidCloud,每次最多 10,000 个,并且 PlaidXL 会将表的列详细信息复用 30 秒,而不是为每个单元格重新获取。如果某个值无法计算,单元格会显示 #VALUE!,将鼠标悬停其上会显示 PlaidCloud 报告的原因,例如筛选列不在表中,或节点没有成员。CELLS 会为整个结果显示第一个原因。
没有匹配行时,sum 为 0。其他任何合并方式(mean、median、min、max、std 或 var)都没有值可给:CELL 显示 #VALUE!,悬停时显示 No value for 以及聚合方式、成员和度量,而 CELLS 元素则留空。AVERAGE 等 Excel 函数会跳过空白的 CELLS 元素,说明 给出的原因相同。
=PLAIDXL.CELLS(table, measures, [measure_type], node, [node], ...)
与 CELL 类似,但一次处理多个度量。每个度量返回一个数字,按您列出的顺序向下排成一列。
D1:F1 revenue | cost | unitsD2 =PLAIDXL.REPR.MEASURES(D1:F1)B2 =PLAIDXL.CELLS(A1, D2#, A3, A4, A5)D2# 传入 MEASURES 的整个溢出结果。B2:B4 显示 EMEA 在 FY2026 的收入、成本和数量。
METRIC 和 METRIC_BREAKDOWN 从工作簿的项目读取指标定义:即像 Net Revenue 这样的业务数字,其计算方式已在 PlaidCloud 中约定并记录。该值与仪表板显示的一致,并在您自己的行访问权限下计算。功能区上的 Metric… 会列出项目的指标及其当前值,并为您编写公式;请参阅插入指标。
筛选器是 DIMENSION_REPR 值,与 CELL 相同,区别在于第一个参数指定的是指标可按其细分的列。节点包含其下的所有内容。可传入单个筛选器,或一个区域或列表;空筛选器会被忽略。一个公式可以使用来自多个备用层次结构的筛选器,并且可以将备用层次结构的成员与其他维度的成员组合使用。
METRIC
Section titled “METRIC”=PLAIDXL.METRIC(name, [filter], ...)
返回一个数字:满足您传入的每个筛选器的行所对应的指标。
name:指标的名称、其同义词之一或其 ID。不区分大小写。filter:DIMENSION_REPR筛选器。可按需传入多个。
=PLAIDXL.METRIC("Net Revenue", PLAIDXL.REPR.DIMENSION_REPR("Region", "EMEA"))没有匹配行时,METRIC 返回 0;指标没有值时,单元格为空白。
METRIC_BREAKDOWN
Section titled “METRIC_BREAKDOWN”=PLAIDXL.METRIC_BREAKDOWN(name, breakdown, [filter], ...)
返回按一列拆分的指标:共两列,breakdown 的每个成员与其值并排,按值从大到小排序,不含标题行。
name:指标的名称、其同义词之一或其 ID。breakdown:用于细分指标的列,必须是该指标可按其细分的列。filter:DIMENSION_REPR筛选器。可按需传入多个。
=PLAIDXL.METRIC_BREAKDOWN("Net Revenue", "Region")没有值的成员排在最后,值为空白。没有匹配行时,返回一行:空成员和 0。
无法计算的指标会显示 #VALUE!;将鼠标悬停在单元格上可查看原因:
| 消息 | 操作 |
|---|---|
| 未知指标或未知列 | 检查名称。有相近匹配时,消息会在 Did you mean 之后列出。 |
| 名称匹配多个指标 | 使用指标的全名或其 ID。 |
| Your PlaidCloud role doesn’t allow reading metrics in this project. | 请向项目所有者申请访问权限。 |
| This PlaidCloud workspace doesn’t serve metrics to Excel yet. | 请管理员更新该工作区。 |
| PlaidCloud is busy; try again in 若干秒 | PlaidXL 已重试过。请在给出的时间之后重新计算。 |
| Choose a project in the PlaidXL pane first. | 将工作簿关联到项目。 |
| 此 PlaidCloud 工作区暂时无法将备用层次结构与其他成员组合使用。请联系管理员更新工作区。 | 请联系管理员更新工作区。同一次重新计算中的其他单元格仍会返回结果。 |
这些函数本身不读取数据。每个函数返回一小段文本,用于描述计算的某一部分,可直接传给 CELL、CELLS 或 DRILL。请将它们放在被引用的单元格中,这样一处更改(例如换一个期间)就会更新所有使用它们的公式。
TABLE_REPR
Section titled “TABLE_REPR”=PLAIDXL.REPR.TABLE_REPR(table_name)
CELL 和 CELLS 读取的表,按其在所选项目中的名称指定。
MEASURE
Section titled “MEASURE”=PLAIDXL.REPR.MEASURE(column_name)
CELL 要聚合的数值列。
MEASURES
Section titled “MEASURES”=PLAIDXL.REPR.MEASURES(column_names)
供 CELLS 使用的多个度量列,来自包含列名称的单元格区域。结果每列溢出一个单元格;使用溢出运算符(如 D2#)将其传给 CELLS。
MEASURE_TYPE
Section titled “MEASURE_TYPE”=PLAIDXL.REPR.MEASURE_TYPE(agg_function)
度量的聚合方式。agg_function 为下列之一,使用小写:
| 值 | 返回 |
|---|---|
sum |
总和(未传入度量类型时的默认值) |
mean |
平均值 |
median |
中位数 |
min |
最小值 |
max |
最大值 |
std |
标准差 |
var |
方差 |
DIMENSION_REPR
Section titled “DIMENSION_REPR”=PLAIDXL.REPR.DIMENSION_REPR(dimension_name, start_node, [hierarchy])
用于 CELL、CELLS、METRIC 和 METRIC_BREAKDOWN 的筛选器。每个要筛选的列传入一个。
dimension_name:表中某列 的名称,即保存维度值的列,例如"Region"。必须与列名称完全一致;不在表中的列会返回错误。start_node:要汇总到的节点,例如"EMEA"。对于未关联到维度的列,则为要匹配的值。hierarchy:用于汇总该节点的备用层次结构。默认为"main"。
引用某个单元格作为节点即可构建网格:=PLAIDXL.REPR.DIMENSION_REPR("Region", $A7)。前导和尾随空格会被忽略,因此可以指向 DRILL 布局出的带缩进的节点名称。
DRILL_KEY
Section titled “DRILL_KEY”=PLAIDXL.REPR.DRILL_KEY(d_key)
DRILL 在其起始节点之下深入的范围。d_key 是纯文本,可以是文字形式,也可以是写出的下钻键:
d_key |
下钻到 |
|---|---|
"all" |
所有后代 |
"leaves" |
仅叶节点,即其下没有任何节点的节点 |
"1 level"、"2 levels"…… |
向下指定的代数 |
">[...]" |
写出的下钻键,见下表 |
数字请用阿拉伯数字书写:"2 levels",而不是 "two levels"。开头的 drill 会被忽略,因此 "drill 2 levels" 同样有效。
写出的键从起始节点向下计算代数,起始节点为第 0 代,始终包含在内。负数从每个分支的叶节点向上计数,因此不规则层次结构也能按预期工作。
| 键 | 包含 |
|---|---|
>[*] 或 >[:] |
每个节点 |
>[] 或 >[-1] |
仅叶节点 |
>[n] |
起始节点及其下 n 代。>[0] 仅为起始节点。 |
>[a:] |
起始节点,然后是第 a 代及其下的所有内容 |
>[a:b] |
起始节点,然后是第 a 代到第 b - 1 代 |
>[0:-k] |
起始节点以及其下至少有 k 代的每个节点,因此每个分支的最后 k 代会被省略。>[0:-1] 是除叶节点外的每个节点。 |
>[a:-k] |
同上,但从第 a 代开始 |
对于层次结构 A,其子节点为 B、C 和 D,其中 B 有 B1 和 B2,C 有 C1:
| 键 | 结果 |
|---|---|
>[1] |
A、B、C、D |
>[2] |
A、B、B1、B2、C、C1、D |
>[] |
A、B1、B2、C1、D |
>[0:-1] |
A、B、C |
>[2:] |
A、B1、B2、C1 |
节点按层次结构顺序列出,每个节点位于其自己的父节点之下,Excel 大纲分组也遵循相同的顺序。键中的空格会被忽略。不符合这些形式之一的键会返回错误,并列出有效的形式。
从任务窗格插入公式
Section titled “从任务窗格插入公式”任务窗格的 Formulas 选项卡可为您编写公式。可从任务窗格、功能区上的 Formula…,或 PlaidXL 右键菜单中的 Insert formula here… 打开它。在 What do you want in the cell? 下选择其中一项:
- A whole table(
GET_TABLE):选择或输入表,还可以选择性地设置最大行数。 - One number(
CELL):选择或输入表,选择 Column to total 和 How to combine the values。 - A list of dimension nodes(
DRILL):选择或输入维度和起始节点,选择下钻的范围,还可以选择 Alternate hierarchy、要排除的节点和要展开的节点。行 会预览下钻列出的节点。请参阅自定义下钻。 - A business metric(
METRIC):请参阅插入指标。
Formula 预览会在您填写字段时显示结果。点击 Insert in active cell(或按 Ctrl+Enter)将其放入所选单元格,或点击 复制 自行粘贴。在 Tables 选项卡上选择表,或在 Dimensions 选项卡上选择维度,会填入对应的字段。
点击功能区 插入 组中的 Metric…,或在 Formulas 选项卡上选择 A business metric。
- 在 Metric 中输入指标名称或同义词的一部分,例如
revenue。每个匹配项都会显示其当前值和说明。 - 使用方向键或点击来选择指标。
- 可选:添加筛选器:选择 Dimension,输入 Node,然后点击 添加筛选器。
- 可选:在 Breakdown by (optional) 下选择一列,以编写
METRIC_BREAKDOWN而不是单个数字。 - 点击 Insert in active cell,或点击 复制。