互動式報表設計：用下拉選單和核取方塊讓同一張報表自己換內容

信義店長要看信義，板橋店長要看板橋。我以前的做法是每間店複製一個工作表，改一次公式就要改三次，新開一間店再複製一次。

這篇把它做成一張報表：讀的人自己選門市、勾要不要含外送、挑要看品項還是通路，結果自己換。資料是三間店 9 月的銷量，放在 A1:D13，共 12 筆：

整張報表我分成三層，讀的人只碰第一層：

← 左右滑動看完整的圖，點圖可放大

圖：控制格是輸入，資料層是公式，展示層是結果，三層放在同一頁但各佔一塊。

資料與計算分層的想法在 三層架構 講過，這篇直接拿來用。

控制面板：三個格子，三種控制

控制格放在資料右邊，F1:G3：

圖：讀的人只會動 G1、G2、G3 這三格。

• G1 門市：下拉選單，選項是「全部」「信義」「板橋」「中和」，手打在 L2:L5，用「來自範圍」指過去。「全部」那個選項的處理與選項自動長出來的寫法，在 FILTER 實戰 講過，設定下拉選單的操作則見 資料驗證實戰。
• G2 含外送：核取方塊，選取 G2 後點「插入」>「核取方塊」。勾起來的值是 TRUE，沒勾是 FALSE。
• G3 分組：下拉選單，選項直接手打「品項,通路」。

兩種控制我是這樣分的：要在幾個名稱裡挑一個，用下拉選單。只有開和關兩種狀態，用核取方塊，因為它的值本身就是 TRUE 或 FALSE，可以直接放進 IF，用下拉選單選「是」「否」還得多寫一道 G2="是"。

這三格跟 動態資料設計 的「設定」工作表長得很像，差在改的人不同。設定是做表的人調，控制格是看表的人調，所以我放在報表旁邊，不藏到別頁。

資料層：把控制格組成 QUERY

先手算一個狀態。選信義、不含外送，店內有三筆：蘋果紅茶 87、青茶 52、烏龍奶茶 64。要讓 QUERY 算出這個，就得把控制格的選擇組成一條查詢字串。

我把字串拆成三格，一格一件事，出錯時看得到哪一步歪了。G5 放條件：

TEXTJOIN 的第二個參數 TRUE 是略過空字串，所以兩個條件都不需要時，G5 就是空的。G1 選信義、G2 沒勾時，G5 的結果是：

A = '信義' and C = '店內'

G6 放要分組的欄位字母，這個等一下在「切換檢視」那節講，現在先知道分組選品項時它的結果是 B。G7 放完整的查詢字串：

G7 的結果是：

select B, sum(D) where A = '信義' and C = '店內' group by B order by sum(D) desc label sum(D) '杯數'

最後 F9 把字串交給 QUERY：

來看看結果，F9:G12：

跟手算的三筆一樣。把 G1 改成「全部」、G2 勾起來，G5 變成空字串，G7 就沒有 where，結果是三間店全部加總：

三個數字加起來是 527，等於 D 欄 12 筆全部加總。

字串拼接我自己很容易少一個引號，所以我習慣先在 G7 看字串長相，確定對了再交給 QUERY。QUERY 本身的語法在 QUERY 基礎 講過。

只要列明細：FILTER 版

如果報表要的是明細，不用加總，換成 FILTER 比較直接，放在 I9：

用 + 當 OR、逗號當 AND 的寫法，在 FILTER 實戰 有整理，這邊不重講。G1 信義、G2 沒勾的結果，溢出到 I9:L11：

我的分法很單純：要加總、要分組，用 QUERY；只要把符合的列列出來，用 FILTER。QUERY 的字串要自己拼，比 FILTER 多一道工，所以沒有加總需求我就不用它。

切換檢視：一個下拉選單換分組欄位

G3 選「通路」時，同一張報表要改成按店內、外送加總。做法是只換 QUERY 字串裡的欄位字母，其他都不動。G6 這樣寫：

← 左右滑動看完整的圖，點圖可放大

圖：下拉選單只決定欄位字母，後面的 QUERY 是同一條。

G1 選板橋、G2 勾起來、G3 選通路，結果是：

板橋店內 71 + 38 = 109，外送 19 + 27 = 46。想多一種檢視，在 SWITCH 多加一組「名稱, 欄位」就好。

同一個下拉，更新明細與彙總兩份查詢

方格子 QUERY 與儲存格連動提過，一個選單可以控制多個查詢。這個練習請另開一張空白的新分頁，用少量資料做。

把下方資料貼到 A1，H1 填「控制門市」、H2 先填 O'茶。P1:P4 依序填「選單來源」「全部」「O'茶」「信義」，把 H2 設為來自 P2:P4 的下拉選單：

門市	品項	杯數
O'茶	紅茶	10
信義	紅茶	20
O'茶	青茶	5
信義	青茶	30
	紅茶	99

J1 放明細，留空 J:L 讓結果展開：

J12 放彙總，仍讀同一個 H2：

這裡沒有把 H2 內容拼進查詢文字，而是在陣列多加一個布林欄；Col4 = true 比較布林值，不是字串 'TRUE'。因此 O'茶 的單引號只是資料，不會提早關閉查詢文字。

「全部」在這裡明訂為有填門市的資料，所以沒有門市的 99 不納入；這是報表的業務規則，不是 QUERY 自動判斷那一列有錯。缺門市的資料要另外追蹤補齊。

建立下拉後換三個選項，明細與彙總都會跟著更新，結果如上表；清空 H2 後兩處都顯示「請選門市」。選「全部」時，四筆明細不會混入缺門市的 99。

來源固定到第 6 列，是為了讓練習範圍看得清楚；正式表擴充時，三欄資料與布林欄必須維持相同列數。查無門市的情況也不一定丟錯，若要提示「無符合資料」，另外先算命中數；更多單選／多選與特殊字元的邊界在 QUERY 動態多選。

這個兩份查詢的練習放在獨立分頁就好。下一節的圖表與 Slicer 回到前面 12 筆、527 杯的原始報表。

圖表跟著結果走

圖表的資料範圍框 F9:G12，也就是 QUERY 的輸出。控制格一改，結果換了，圖表跟著換，不需要再動圖表。

圖：圖表讀的是 QUERY 的輸出，不是原始資料。

圖：改一格，表和圖一起換。

分組換成通路時，結果只有 2 筆；換回品項則有 3 筆。框選圖表來源時，要能容納兩種結果，別為了縮掉空白格就固定改成 F9:G11，否則第三個品項會漏掉。空白範圍的呈現還要看圖表設定，切換分組後直接檢查標籤與數值，見 圖表完全指南 的互動式圖表一節。

用 Slicer 帶動透視表與圖表

切片器（Slicer）在繁體中文介面叫「篩選器控制項」。如果門市、通路已經是固定的篩選欄位，我會先試這個做法，讓同一份資料的透視表與圖表一起換，不必先拼 QUERY 字串。

下面沿用 A1:D13 的 12 筆銷量。這次我要做「各門市杯數」透視表，再讓門市和通路各有一個控制項。

先放好透視表與圖表

1. 選取 A1:D13，建立資料透視表，放在同一張工作表的 A22。列放「門市」，值放「杯數」，匯總依據選 SUM。
2. 如果要畫各門市占比，關閉透視表列的總計，再用透視表的門市與杯數欄建立圓餅圖。不要把總和也當成一間門市畫進去。
3. 選取來源 A1:D13，點「資料」>「新增篩選器控制項」。在右側把資料範圍設為 A1:D13，資料欄選「門市」，確認「套用到資料透視表」已勾選。

← 左右滑動看完整的圖，點圖可放大

圖：控制項的來源是原始銷量，不是透視表輸出的兩欄。

我把透視表放在第 22 列，是因為控制項篩掉來源資料時，對應的整列會隱藏。如果透視表放在來源右邊、剛好跟它同列，報表本身也可能跟著看不見。來源下方的報表記得留在 A1:D13 範圍外。

門市與通路一起篩

1. 點門市控制項的篩選圖示，用「依值篩選」只保留「信義」，套用後看透視表與圖表。
2. 再新增一個控制項，資料欄選「通路」，來源仍然是同一個 A1:D13，並勾選「套用到資料透視表」。
3. 通路只保留「店內」。這時保留的是「門市為信義，而且通路為店內」的資料。
4. 在門市控制項重新全選並套用，通路先保留店內，看看剩下的條件有沒有繼續生效。

用上面這份資料，兩個控制項的組合結果是：

兩個控制項會一起限制資料，清掉其中一個不會清掉另一個。圖表接的是這張透視表，所以同樣跟著變；兩個條件只剩信義店內時，圓餅圖也只剩信義這一片。

畫面篩了，公式會跟著算嗎？

這邊最容易看錯。我同時在來源範圍外放了兩條公式：

控制項切成信義、店內後，透視表是 203，這兩條仍然是 527。要讓公式只算信義店內，就得把條件寫進公式，例如：

結果是 203。它符合的原因是公式自己寫了這兩個條件；接著清掉控制項，也不會把這條公式改成算全部。

所以我會看報表要怎麼互動：

• 透視表已經做好，只想讓人切門市、切通路，連同圖表一起看：用 Slicer。
• 需要讓公式讀取使用者的選擇、加核取方塊，或切換分組欄位：用上面的控制格加 QUERY／FILTER。

自己的選擇與大家的預設畫面

依 Google 官方說明，一般篩選選擇是私人的，也不會自動儲存成共用預設。不要以為自己選了信義，其他人打開就一定先看到信義。

想固定初始畫面，先設好條件，再開控制項右側的「更多」選單，選「將目前的篩選器設為預設值」。兩個控制項要各自設定；想預設看全部，就先各自全選、套用，再儲存預設。官方說明也區分了權限：新增或移除控制項需要編輯權限，可存取試算表的人則可以調整篩選。

我把門市「信義」與通路「店內」各自設為預設後，先把門市改成全選，透視表變成 431；重新載入頁面，兩個控制項都恢復只選一項，透視表也回到 203。其他人打開時看到的是你存的預設值；沒有存成預設值的選擇不會公開，也不會儲存。

只有一部分跟著變時

先核對來源，不要急著重做圖表：

• 兩個控制項與透視表是不是同一個來源範圍？本例都用 A1:D13。若一個只框到第 12 列，就沒有套在相同的資料範圍上。
• 控制項是否勾選「套用到資料透視表」？圖表是否真的指向這張透視表？若接的是另一份 QUERY 結果，它會繼續依自己的公式計算。
• 新資料是否加在第 14 列、已經超出 A1:D13？更新來源範圍時，透視表與每個控制項都要一起核對。報表放在第 22 列的這個練習版不要直接改成整欄，否則會把下方的報表也框進來源。

來源會持續新增的正式報表，我會先把原始資料與展示區分開。

讓看的人只動控制格

• 控制格的預設值放「全部」並勾起含外送，打開報表時就有完整內容，不會是一張空的。
• G1 的下拉選單設成「拒絕輸入」，打錯字就進不來，不會讓 QUERY 的 where 找不到門市。
• 把控制格以外的範圍用保護鎖起來，只留 G1:G3 可以編輯，作法見 保護試算表。
• 篩選結果是空的時候，FILTER 要用 IFNA 接住，寫法在 FILTER 實戰 的「沒有符合的時候」。

什麼時候不要做成互動

主管每週只看信義一間店，就不要做。一個控制格看起來只是多一個選單，但它讓組合數乘上去：這張報表門市有 4 種選項、含外送 2 種、分組 2 種，一共 4 × 2 × 2 = 16 種狀態，每一種都得能正常算出東西。每多一個只有兩種選項的控制格，要驗的組合就變成兩倍。

資料會一直新增的話，原始資料另放一張工作表、報表放另一張，會比現在這樣擺在同一頁好維護，見 三層架構。

練習

控制格設好之後，讀的人什麼公式都不用看，只要選就好。想把互動做在圖表上，下一步看 圖表完全指南。我是喜特先生，Mr. Sheet，我們下個教學見！