用 Grist 汇总表分组统计并钻取原始记录

汇总表是 Grist 的数据分析基础。它与电子表格的透视表、数据库的 GROUP BY 思路相近:先根据字段把原始记录分成组,再计算整张表或每一组的数量、金额、平均值等指标。你可以按状态或优先级统计项目,按年份、季度和类别汇总银行交易,也可以同时按职位、性别、部门分组。

本文依据 Grist 官方帮助中心《Summary Tables》全文整理。原文未注明个人作者;来源与维护方为 Grist。核对日期:2026-10-05。在线帮助未固定产品版本,界面文字可能随版本变化。

原始员工记录按职位分组,汇总行通过 group 引用组内记录;选择汇总行后联动明细和更细的图表,Detach 后成为不自动新增分组的独立表。
图 1:汇总、钻取和脱离汇总的关系。本文原创技术示意图,并非 Grist 界面截图。

创建汇总表:先决定按什么分组

点击 Add New,选择 Add Page 或 Add Widget to Page。在组件选择器中,找到要汇总的表,点击表名旁的求和图标。接着在 Group by 中选择一个或多个分组字段。

例如,源表 Employees 记录员工信息。选择 Position 后,每个不同职位会在汇总表中对应一行;选择 Position 和 Gender,则每一种“职位与性别”的组合对应一行。如果不按任何字段分组,就可以把所有记录放在同一组中,计算整表统计。

分组字段决定有哪些组,不是要计算哪些数值。若目标是“各职位的平均工资”,应把职位选为分组字段,随后使用公式计算工资。把工资也选入 Group by,会继续按工资值拆分记录,得到另一种统计粒度。

可以把每个汇总行理解成一个桶:状态字段决定“Active”“Completed”“NotStarted”等桶,原始项目按状态进入相应桶,公式再计算每个桶中的项目数与总成本。更改源记录的状态,记录所属的组也随之变化。

按年、季度、月份或周汇总

对于 Date 或 DateTime 列,通常需要先在源表增加一个辅助列,从日期中提取要统计的时间周期。添加列时,选择 Date helpers…,再选择日期列以及年、季度、月或周等周期。之后,把辅助列作为汇总表的 Group by 字段。

这样,周期的定义保留在源数据旁边,汇总表只负责分组与计算。若需要自定义日期处理,可继续参考官方的 Working with dates 文档。编者提醒:做跨年度月报时,要确认辅助列区分了年份,避免把不同年份的同一个月份误并为一组;这是使用数据前的语义检查,不是本次执行结果。

理解自动生成的公式和 $group

创建汇总表后,选定的分组字段会成为列,其余列都是公式列。Grist 会自动增加 count,表示当前组有多少条原始记录;对于源表中的数值列,它通常创建同名汇总列并默认求和。

以按职位汇总 Employees 为例,组件标题可能显示为 EMPLOYEES [by Position]。可以点击标题改名。选中 count 或 AnnualPay 单元格并按 Enter,就能查看公式:

# count 列
len($group)

# AnnualPay 列
SUM($group.AnnualPay)

$group 本身也是一列,只是默认隐藏。每个单元格保存当前汇总行所代表的原始记录集合。取消隐藏后,界面会把它显示成包含数字行 ID 的 Python 列表。

对熟悉 Python 的读者来说,$group 是一个可迭代的记录集合;$group.A 是取得所有记录的 A 列值的简写,含义类似 [r.A for r in $group]。因此,汇总公式不是只能进行固定的“求和”:它可以访问组内每条记录,也可以引用汇总表本身的其他列。

自动生成不等于业务含义正确。例如把小时工资 PayRate 相加通常没有意义。你可以用列菜单或 Alt + Minus 删除不需要的汇总列,或把公式改为平均值。汇总表允许修改现有公式、添加公式列,但不允许添加普通的非公式列。

统计目标 原文公式 解释
平均值 AVERAGE($group.PayRate) 计算本组工资率的平均值。
由合计与数量计算平均值 SUM($group.PayRate) / $count 当分母的记录范围与实际要平均的值一致时使用。
标准差 STDEV($group.PayRate) 描述组内值的离散程度。
最大值与最小值 MAX($group.PayRate)、MIN($group.PayRate) 查看组内数值范围。
满足条件的金额合计 SUM(r.AnnualPay for r in $group if r.EmploymentStatus == "Active") 只对在职员工求和。
加权平均 AVERAGE_WEIGHTED(zip($group.Life_Expectancy, $group.Population)) 把预期寿命与人口一一配对,以人口作为权重。

编者校核:空值、错误值、样本数量和权重是否合理,会影响统计解释。不要默认 SUM(...)/$count 与忽略空值的平均函数在所有数据上都等价,也不要把零权重或错误值悄悄当作正常结果。本次只对公式与原文进行静态核对,没有在 Grist 中执行这些公式。

调整分组字段,理解哪些单元格不能编辑

汇总表的分组列来自源表。不能直接修改其中的分组值,也不能直接更改分组列的数据类型等设置;这些值和设置反映的是源表。如果源表出现了新的分组值,汇总表会自动增加相应行。

若想从“按职位”改成“按职位与部门”,点击汇总组件右上角的三点菜单,打开 Data selection,再点击侧栏的 Edit Data Selection。在与创建时相同的组件选择器中,取消旧的分组字段、选中新的字段,最后点击 Save。

这里改变的是分组定义。若只是想把某名员工归到其他职位,应修改源表中的员工记录,而不是试图编辑汇总行。

通过汇总行钻取明细

汇总表可以作为其他组件的选择器。在同一页面中已有按 Position 汇总的员工表时,添加一个显示未汇总 Employees 数据的表格组件,然后在 SELECT BY 中选择这个汇总组件。此后点击某个职位,明细表就显示该职位的员工。

也可以联动一个更细的汇总。例如,先有“按职位”的概览,再新增“按职位和性别”的汇总,并让前者控制后者。选中一个职位后,就能看到该职位内部按性别拆分的结果。新组件的分组字段需要同时包含 Position 与 Gender,否则它并不具备所需的关联粒度。

这种布局可以保留上下文:左侧看到哪些组规模大,右侧检查该组由哪些记录组成。它能帮助解释统计值,但联动筛选本身不是访问控制。敏感数据仍需由文档的权限设置保护。更多联动方式见官方 Linking widgets。

让图表使用汇总数据

汇总表也是图表的数据源,并可与其他组件形成动态图表。沿用上述示例,添加一个按 Position 和 Gender 分组的 Chart 组件,选择 Bar Chart,把 Gender 和 AnnualPay 设为 Visible Series。让它由职位汇总组件控制,点击职位即可查看对应分组的薪酬指标。

原文中需特别核对的地方:文章在图表段落把该图解释为“平均工资”,但前面说明数值列默认使用 SUM。若你的 AnnualPay 仍是 SUM($group.AnnualPay),图表展示的就是总工资,不能称作平均工资。要比较平均工资,应先把该汇总列明确改成 AVERAGE($group.AnnualPay),并将列名和图表标题写成平均工资。这一说明是编者对原文统计语义的补充,不是 Grist 自动替你完成的转换。

Detach:把分组结果变成独立表

有时,职位不只是一个员工字段,还需要维护岗位描述、工伤保险代码等资料。每个职位一行的独立 Positions 表更适合这类用途。

先新建按 Position 分组的员工汇总页,在右侧面板的 Data 标签中点击 Detach。Grist 会将其转为一张新数据表,行保持不变,并自动取一个类似 Table1 的名称。把表改名为 Positions 后,就可以添加所需的普通数据列。

已有计算列会保留;此前针对各职位计算的内容,仍可在新表中计算。因此 Detach 不等同于“把所有公式都冻结成静态数值”。真正关键的变化是:独立表不会自动接纳新分组值。今后源表出现一个从未见过的职位,仍连接源表的汇总会自动出现新行,已经脱离的 Positions 表则不会。

选择前应想清楚目标:持续随源数据更新的统计适合保留汇总关系;需要人工维护属性的职位维度适合独立表,并为新增职位安排维护流程。删除列或脱离汇总前,建议保留文档副本,便于核对与回退。

来源与校核说明

原文:Grist Help Center,Summary Tables。原文无个人署名、未明确发表日期;未推定作者姓名或首发时间。软件开源许可证不在本文中被用来替代文章授权。

本文涵盖原文全部技术章节,保留关键公式和界面名称,另标出统计语义及权限方面的编者提示。配图为原创示意图。未登录或修改 Grist 文档,未执行公式、未进行真实投递或产品功能测试;静态审查未发现硬编码秘密、外部命令执行或注入构造,但这不等于软件或文档配置不存在其他风险。

原始版权与许可

原文 Copyright 2022 Grist Labs Inc.;Grist 帮助文档官方许可为 CC BY-SA 4.0。本中文翻译整理及原创示意图同样按 CC BY-SA 4.0 提供;改动包括翻译、结构整理、明确标示的公式与图表说明。不表示 Grist 为改动背书。

Copyright 2022 Grist Labs Inc.

Creative Commons Attribution-ShareAlike 4.0 International Public
License

By exercising the Licensed Rights (defined below), You accept and agree
to be bound by the terms and conditions of this Creative Commons
Attribution-ShareAlike 4.0 International Public License ("Public
License"). To the extent this Public License may be interpreted as a
contract, You are granted the Licensed Rights in consideration of Your
acceptance of these terms and conditions, and the Licensor grants You
such rights in consideration of benefits the Licensor receives from
making the Licensed Material available under these terms and
conditions.


Section 1 -- Definitions.

  a. Adapted Material means material subject to Copyright and Similar
     Rights that is derived from or based upon the Licensed Material
     and in which the Licensed Material is translated, altered,
     arranged, transformed, or otherwise modified in a manner requiring
     permission under the Copyright and Similar Rights held by the
     Licensor. For purposes of this Public License, where the Licensed
     Material is a musical work, performance, or sound recording,
     Adapted Material is always produced where the Licensed Material is
     synched in timed relation with a moving image.

  b. Adapter's License means the license You apply to Your Copyright
     and Similar Rights in Your contributions to Adapted Material in
     accordance with the terms and conditions of this Public License.

  c. BY-SA Compatible License means a license listed at
     creativecommons.org/compatiblelicenses, approved by Creative
     Commons as essentially the equivalent of this Public License.

  d. Copyright and Similar Rights means copyright and/or similar rights
     closely related to copyright including, without limitation,
     performance, broadcast, sound recording, and Sui Generis Database
     Rights, without regard to how the rights are labeled or
     categorized. For purposes of this Public License, the rights
     specified in Section 2(b)(1)-(2) are not Copyright and Similar
     Rights.

  e. Effective Technological Measures means those measures that, in the
     absence of proper authority, may not be circumvented under laws
     fulfilling obligations under Article 11 of the WIPO Copyright
     Treaty adopted on December 20, 1996, and/or similar international
     agreements.

  f. Exceptions and Limitations means fair use, fair dealing, and/or
     any other exception or limitation to Copyright and Similar Rights
     that applies to Your use of the Licensed Material.

  g. License Elements means the license attributes listed in the name
     of a Creative Commons Public License. The License Elements of this
     Public License are Attribution and ShareAlike.

  h. Licensed Material means the artistic or literary work, database,
     or other material to which the Licensor applied this Public
     License.

  i. Licensed Rights means the rights granted to You subject to the
     terms and conditions of this Public License, which are limited to
     all Copyright and Similar Rights that apply to Your use of the
     Licensed Material and that the Licensor has authority to license.

  j. Licensor means the individual(s) or entity(ies) granting rights
     under this Public License.

  k. Share means to provide material to the public by any means or
     process that requires permission under the Licensed Rights, such
     as reproduction, public display, public performance, distribution,
     dissemination, communication, or importation, and to make material
     available to the public including in ways that members of the
     public may access the material from a place and at a time
     individually chosen by them.

  l. Sui Generis Database Rights means rights other than copyright
     resulting from Directive 96/9/EC of the European Parliament and of
     the Council of 11 March 1996 on the legal protection of databases,
     as amended and/or succeeded, as well as other essentially
     equivalent rights anywhere in the world.

  m. You means the individual or entity exercising the Licensed Rights
     under this Public License. Your has a corresponding meaning.


Section 2 -- Scope.

  a. License grant.

       1. Subject to the terms and conditions of this Public License,
          the Licensor hereby grants You a worldwide, royalty-free,
          non-sublicensable, non-exclusive, irrevocable license to
          exercise the Licensed Rights in the Licensed Material to:

            a. reproduce and Share the Licensed Material, in whole or
               in part; and

            b. produce, reproduce, and Share Adapted Material.

       2. Exceptions and Limitations. For the avoidance of doubt, where
          Exceptions and Limitations apply to Your use, this Public
          License does not apply, and You do not need to comply with
          its terms and conditions.

       3. Term. The term of this Public License is specified in Section
          6(a).

       4. Media and formats; technical modifications allowed. The
          Licensor authorizes You to exercise the Licensed Rights in
          all media and formats whether now known or hereafter created,
          and to make technical modifications necessary to do so. The
          Licensor waives and/or agrees not to assert any right or
          authority to forbid You from making technical modifications
          necessary to exercise the Licensed Rights, including
          technical modifications necessary to circumvent Effective
          Technological Measures. For purposes of this Public License,
          simply making modifications authorized by this Section 2(a)
          (4) never produces Adapted Material.

       5. Downstream recipients.

            a. Offer from the Licensor -- Licensed Material. Every
               recipient of the Licensed Material automatically
               receives an offer from the Licensor to exercise the
               Licensed Rights under the terms and conditions of this
               Public License.

            b. Additional offer from the Licensor -- Adapted Material.
               Every recipient of Adapted Material from You
               automatically receives an offer from the Licensor to
               exercise the Licensed Rights in the Adapted Material
               under the conditions of the Adapter's License You apply.

            c. No downstream restrictions. You may not offer or impose
               any additional or different terms or conditions on, or
               apply any Effective Technological Measures to, the
               Licensed Material if doing so restricts exercise of the
               Licensed Rights by any recipient of the Licensed
               Material.

       6. No endorsement. Nothing in this Public License constitutes or
          may be construed as permission to assert or imply that You
          are, or that Your use of the Licensed Material is, connected
          with, or sponsored, endorsed, or granted official status by,
          the Licensor or others designated to receive attribution as
          provided in Section 3(a)(1)(A)(i).

  b. Other rights.

       1. Moral rights, such as the right of integrity, are not
          licensed under this Public License, nor are publicity,
          privacy, and/or other similar personality rights; however, to
          the extent possible, the Licensor waives and/or agrees not to
          assert any such rights held by the Licensor to the limited
          extent necessary to allow You to exercise the Licensed
          Rights, but not otherwise.

       2. Patent and trademark rights are not licensed under this
          Public License.

       3. To the extent possible, the Licensor waives any right to
          collect royalties from You for the exercise of the Licensed
          Rights, whether directly or through a collecting society
          under any voluntary or waivable statutory or compulsory
          licensing scheme. In all other cases the Licensor expressly
          reserves any right to collect such royalties.


Section 3 -- License Conditions.

Your exercise of the Licensed Rights is expressly made subject to the
following conditions.

  a. Attribution.

       1. If You Share the Licensed Material (including in modified
          form), You must:

            a. retain the following if it is supplied by the Licensor
               with the Licensed Material:

                 i. identification of the creator(s) of the Licensed
                    Material and any others designated to receive
                    attribution, in any reasonable manner requested by
                    the Licensor (including by pseudonym if
                    designated);

                ii. a copyright notice;

               iii. a notice that refers to this Public License;

                iv. a notice that refers to the disclaimer of
                    warranties;

                 v. a URI or hyperlink to the Licensed Material to the
                    extent reasonably practicable;

            b. indicate if You modified the Licensed Material and
               retain an indication of any previous modifications; and

            c. indicate the Licensed Material is licensed under this
               Public License, and include the text of, or the URI or
               hyperlink to, this Public License.

       2. You may satisfy the conditions in Section 3(a)(1) in any
          reasonable manner based on the medium, means, and context in
          which You Share the Licensed Material. For example, it may be
          reasonable to satisfy the conditions by providing a URI or
          hyperlink to a resource that includes the required
          information.

       3. If requested by the Licensor, You must remove any of the
          information required by Section 3(a)(1)(A) to the extent
          reasonably practicable.

  b. ShareAlike.

     In addition to the conditions in Section 3(a), if You Share
     Adapted Material You produce, the following conditions also apply.

       1. The Adapter's License You apply must be a Creative Commons
          license with the same License Elements, this version or
          later, or a BY-SA Compatible License.

       2. You must include the text of, or the URI or hyperlink to, the
          Adapter's License You apply. You may satisfy this condition
          in any reasonable manner based on the medium, means, and
          context in which You Share Adapted Material.

       3. You may not offer or impose any additional or different terms
          or conditions on, or apply any Effective Technological
          Measures to, Adapted Material that restrict exercise of the
          rights granted under the Adapter's License You apply.


Section 4 -- Sui Generis Database Rights.

Where the Licensed Rights include Sui Generis Database Rights that
apply to Your use of the Licensed Material:

  a. for the avoidance of doubt, Section 2(a)(1) grants You the right
     to extract, reuse, reproduce, and Share all or a substantial
     portion of the contents of the database;

  b. if You include all or a substantial portion of the database
     contents in a database in which You have Sui Generis Database
     Rights, then the database in which You have Sui Generis Database
     Rights (but not its individual contents) is Adapted Material,

     including for purposes of Section 3(b); and
  c. You must comply with the conditions in Section 3(a) if You Share
     all or a substantial portion of the contents of the database.

For the avoidance of doubt, this Section 4 supplements and does not
replace Your obligations under this Public License where the Licensed
Rights include other Copyright and Similar Rights.


Section 5 -- Disclaimer of Warranties and Limitation of Liability.

  a. UNLESS OTHERWISE SEPARATELY UNDERTAKEN BY THE LICENSOR, TO THE
     EXTENT POSSIBLE, THE LICENSOR OFFERS THE LICENSED MATERIAL AS-IS
     AND AS-AVAILABLE, AND MAKES NO REPRESENTATIONS OR WARRANTIES OF
     ANY KIND CONCERNING THE LICENSED MATERIAL, WHETHER EXPRESS,
     IMPLIED, STATUTORY, OR OTHER. THIS INCLUDES, WITHOUT LIMITATION,
     WARRANTIES OF TITLE, MERCHANTABILITY, FITNESS FOR A PARTICULAR
     PURPOSE, NON-INFRINGEMENT, ABSENCE OF LATENT OR OTHER DEFECTS,
     ACCURACY, OR THE PRESENCE OR ABSENCE OF ERRORS, WHETHER OR NOT
     KNOWN OR DISCOVERABLE. WHERE DISCLAIMERS OF WARRANTIES ARE NOT
     ALLOWED IN FULL OR IN PART, THIS DISCLAIMER MAY NOT APPLY TO YOU.

  b. TO THE EXTENT POSSIBLE, IN NO EVENT WILL THE LICENSOR BE LIABLE
     TO YOU ON ANY LEGAL THEORY (INCLUDING, WITHOUT LIMITATION,
     NEGLIGENCE) OR OTHERWISE FOR ANY DIRECT, SPECIAL, INDIRECT,
     INCIDENTAL, CONSEQUENTIAL, PUNITIVE, EXEMPLARY, OR OTHER LOSSES,
     COSTS, EXPENSES, OR DAMAGES ARISING OUT OF THIS PUBLIC LICENSE OR
     USE OF THE LICENSED MATERIAL, EVEN IF THE LICENSOR HAS BEEN
     ADVISED OF THE POSSIBILITY OF SUCH LOSSES, COSTS, EXPENSES, OR
     DAMAGES. WHERE A LIMITATION OF LIABILITY IS NOT ALLOWED IN FULL OR
     IN PART, THIS LIMITATION MAY NOT APPLY TO YOU.

  c. The disclaimer of warranties and limitation of liability provided
     above shall be interpreted in a manner that, to the extent
     possible, most closely approximates an absolute disclaimer and
     waiver of all liability.


Section 6 -- Term and Termination.

  a. This Public License applies for the term of the Copyright and
     Similar Rights licensed here. However, if You fail to comply with
     this Public License, then Your rights under this Public License
     terminate automatically.

  b. Where Your right to use the Licensed Material has terminated under
     Section 6(a), it reinstates:

       1. automatically as of the date the violation is cured, provided
          it is cured within 30 days of Your discovery of the
          violation; or

       2. upon express reinstatement by the Licensor.

     For the avoidance of doubt, this Section 6(b) does not affect any
     right the Licensor may have to seek remedies for Your violations
     of this Public License.

  c. For the avoidance of doubt, the Licensor may also offer the
     Licensed Material under separate terms or conditions or stop
     distributing the Licensed Material at any time; however, doing so
     will not terminate this Public License.

  d. Sections 1, 5, 6, 7, and 8 survive termination of this Public
     License.


Section 7 -- Other Terms and Conditions.

  a. The Licensor shall not be bound by any additional or different
     terms or conditions communicated by You unless expressly agreed.

  b. Any arrangements, understandings, or agreements regarding the
     Licensed Material not stated herein are separate from and
     independent of the terms and conditions of this Public License.


Section 8 -- Interpretation.

  a. For the avoidance of doubt, this Public License does not, and
     shall not be interpreted to, reduce, limit, restrict, or impose
     conditions on any use of the Licensed Material that could lawfully
     be made without permission under this Public License.

  b. To the extent possible, if any provision of this Public License is
     deemed unenforceable, it shall be automatically reformed to the
     minimum extent necessary to make it enforceable. If the provision
     cannot be reformed, it shall be severed from this Public License
     without affecting the enforceability of the remaining terms and
     conditions.

  c. No term or condition of this Public License will be waived and no
     failure to comply consented to unless expressly agreed to by the
     Licensor.

  d. Nothing in this Public License constitutes or may be interpreted
     as a limitation upon, or waiver of, any privileges and immunities
     that apply to the Licensor or You, including from the legal
     processes of any jurisdiction or authority.
© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容