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 的儲存格的智慧物件會顯示其結果。

另請參閱