Methodox Wiki
产品文档
简体中文
English 简体中文 繁體中文
Methodox 首页

表格、名称、数据透视表和 SQL

Formula 与 Excel 一样，将数据保存在表和命名区域中，用数据透视表对其进行汇总，此外还可以用 SQL 查询数据。本页介绍的所有内容都以 Excel 可读取的形式保存在 .xlsx 中，只有 SQL 例外：Excel 会保留它，但不会运行它。

表

选择一块带有标题行的数据，然后选择 Insert ▸ Table（Ctrl+T 或 Ctrl+L）。表有一个名称（Table1，可在 Name Manager 或 Navigator 中重命名）、一种样式，并且在公式中可以按名称引用其列：=SUM(Sales[Revenue])，或在表的某一行中使用 =[@Units]*[@Price]。

表会自动扩展。在表下方的行或旁边的列中输入内容，表就会把该行或该列纳入其中；计算列中的公式会向下填充，汇总行保持在底部，图表、结构化引用、条件格式和数据验证也会随之调整。这次扩展是一个撤消步骤；Insert ▸ Undo Table AutoExpansion 只撤消扩展本身，Insert ▸ Expand Tables Automatically 可关闭自动扩展。

要手动更改表的大小，可拖动表右下角的小手柄，或选择 Insert ▸ Resize Table 并在工作表上选取新的区域。汇总、计算列、引用和图表都会随之调整，且作为一个撤消步骤。

Insert 菜单还可以切换表的 Header Row、Totals Row、Banded Rows、Banded Columns 以及首列和末列的强调显示；Convert Table to Range 可将表恢复为普通单元格。Format ▸ Table Style 用于选择表的外观。

名称

名称可以代表一个单元格、一个区域或一个值：=SUM(Q1Sales) 比 =SUM(B2:B4) 更易读。

  • 创建名称：选择单元格，在 Name Box（名称框）中输入名称并按 Enter；或者使用 Insert ▸ Named Ranges（即 Name Manager）。
  • Name Manager 列出每个名称和表及其引用的内容，并可创建、重命名、重新定义和删除它们。它在你工作时保持打开，每次更改都会立即生效并作为一个撤消步骤；Refers to 旁边的按钮可将其折叠为一个横条，以便你在工作表上选择新的区域；重命名一个名称时，会改写使用它的公式和其他名称，而不会让它们变成 #NAME?。
  • 根据所选内容重新定义：选择新的单元格，打开 Name Box 的列表，选择 Redefine as ▸ 该名称；或者在 Navigator 中右键单击该名称。
  • Go To（F5 或 Ctrl+G）可跳转到名称、表、地址或任意单元格引用。

Navigator

View ▸ Navigator 列出工作簿中的工作表、名称（及其引用的内容）、表（及其列）、图表、数据透视表和已保存的 SQL 查询。单击某个条目即可跳转到该处；右键单击可将其重命名、重新定义或删除，或选择 Query with SQL。顶部的筛选框可缩小列表范围。

Sales analysis 工作簿旁边的 Navigator：四个工作表、两个名称、三个表和一个图表，每项都标明其位置

数据透视表

数据透视表按你放入其行、列、值和筛选器中的字段汇总一个表，并且保持实时更新：Refresh（Alt+F5）或 Refresh All（Ctrl+Alt+F5）会根据其数据源重新计算。

  1. 单击数据内部，然后选择 Insert ▸ PivotTable。选择将其放在新工作表上，还是放在现有工作表的某个单元格处。源区域和放置位置各有一个按钮，可让你在工作表上选取。
  2. 侧边栏中会打开 PivotTable Fields 面板。将字段拖入 Filters、Columns、Rows 和 Values，或勾选它们。
  3. Data ▸ PivotTable ▸ Value Field Settings 用于选择汇总方式（求和、计数、平均值等）及其数字格式。

Data ▸ PivotTable 菜单还可以排序、筛选、移除字段、选择布局（压缩、大纲或表格形式）、切换总计和分类汇总、更改数据源以及删除数据透视表。数据透视表的报表筛选器、行标签和列标签以及字段标题上都带有下拉按钮，每个按钮都会打开一个列表，其中包含排序、搜索框以及每个项目对应的复选框。

=GETPIVOTDATA() 从数据透视表中读取一个值。在 Excel 中创建的数据透视表打开后是实时数据透视表，刷新后得到的单元格与 Excel 显示的相同；在 Formula 中创建的数据透视表也可以在 Excel 中打开和刷新。

Orders 表的数据透视表，Product 作为报表筛选器，各地区作为行

Examples\Formula\PivotTable.xlsx 中有一年的订单数据，由两个实时数据透视表汇总：你可以修改某个订单后刷新，或在 PivotTable Fields 面板中拖动字段。

尚未支持：数据透视图、日期和数字分组、计算字段和切片器。

SQL Workbench

Data ▸ SQL Workbench（Ctrl+Shift+Q）用 SQL 查询工作簿。每个表、命名区域和工作表都是可供查询的表，列在左侧并显示其行数；如果某个工作表与某个表同名，则该工作表会在名称后加上 _sheet 列出。输入语句后按 Ctrl+Enter 或 F5 运行，也可以用 Query builder 按钮构建语句，它会写出所显示的 SQL。Cancel 可停止长时间运行的语句；在列表中右键单击某个表，可插入其名称或选择其所有行。

select Region, count(*) as Orders, sum(Units) as Units, sum(Revenue) as Revenue
from Sales
group by Region
order by Revenue desc

Sales analysis 工作簿下方的 SQL Workbench：左侧是表，上方是语句，下方是其四行结果

工作台只读取数据：SQL 本身从不更改任何单元格。语句上方的按钮可将结果发送到别处，每次都作为一个撤消步骤：Copy、Save as new sheet、Save as new sheet with a table、Insert at the selection 和 Export CSV。Save View 将语句作为命名查询保存在工作簿中，Navigator 和工作台的 Views 列表会显示它，SQL.VIEW 和 SQL.STORED 可按名称调用它。History 列表保存在此窗口中运行过的语句。所用的 SQL 是 SQLite 的 SQL。

脚本和程序无需此窗口即可访问同一层；参见自动化。要从工作簿之外的数据库引入表，请使用 Data Source。

SQL.VIEW

工作台只复制一次结果。SQL.VIEW 则让结果保持最新：它是一个以查询结果为值的公式，结果溢出到下方和右侧的单元格中，并且与其他公式一样，每当查询读取的单元格发生变化时都会重新计算。

=SQL.VIEW("select Region, count(*) as Orders, sum(Sales) as Total from Orders group by Region order by Total desc")

溢出区域的第一行是列名；=SQL.VIEW(sql, FALSE) 会省略列名。文本也可以是从工作台保存的查询的名称。SQL 出错时显示 #VALUE!，查询没有返回任何行时显示 #CALC!。Excel 会保留该公式并显示其最后的值，但无法重新计算它。

Report 工作表，其 A2 是基于 Orders 表的 SQL.VIEW 公式，溢出为一个三列的汇总

已保存的查询和 SQL.STORED

SQL.VIEW 适合一行 SQL，但对于更长的查询就不太方便了。因此，可以在工作台中把查询写好，用 Save View 以某个名称保存，然后在单元格中按该名称调用它：

=SQL.STORED("SalesFor", F1, 50)

已保存的查询按 SQLite 的方式命名其接收的值：:region、@region、$region 或 ?1。查询名称之后的各个值按照它们在查询文本中首次出现的顺序传给这些参数，因此对于

select Region, Product, sum(Units) as Units
from Sales
where Region = :region and Units >= :least
group by Region, Product

以 SalesFor 保存后，=SQL.STORED("SalesFor", F1, 50) 从 F1 获取地区，并以 50 作为最小值。同一名称使用两次时视为同一个值。结果连同其标题行一起溢出，与 SQL.VIEW 的结果相同。

  • 值是绑定的，绝不会被写入 SQL 中，因此无论单元格中有什么内容，都无法改变语句的行为。
  • 省略的值为 NULL；传入的值多于查询接收的数量时为 #VALUE!；名称不对应任何查询时为 #NAME?。
  • 当所传入的单元格发生变化、查询读取的表发生变化，或已保存的查询本身被编辑或该编辑被撤消时，单元格都会重新计算。

在工作台中，命名了参数的语句旁边会显示一个 Parameter values 框：按相同顺序输入各个值，以逗号分隔（East, 50；文本可以不加引号或加引号，TRUE 和 FALSE，留空表示 NULL），Run 会像单元格那样使用这些值。Views 列表在每个已保存查询的名称旁边显示其参数，顺序与单元格传入它们的顺序一致。

带参数的已保存查询是一个过程而非视图：其他查询无法从中查询，而对其名称使用 SQL.VIEW 时，会以所有参数均为 NULL 的方式运行它。SQL 函数是 Formula 的功能；在 Impulse 和 Expo 中，链接到包含 SQL.STORED 的单元格的智能对象会显示其结果。

另请参阅