使用試算表

2 使用試算表

本章節主要講述試算表，在Excel中可以滿足各種複雜格式的監管報表、內部管理報表的需求；支援交叉統計報表、不規則報表、原始憑證報表等各種複雜格式的報表。

2.1 快速建立分析查詢

說明

在建立試算表報表資源之前，需要先建立分析查詢，將需要使用的欄位進行輸出。後續的試算表報表可使用查詢輸出的欄位進行報表設計，並通過分析查詢從業務庫中查詢出資料進行呈現。

2.1.1 建立分析查詢

我們基於資料來源基礎設定中建立的“northwind”關聯式資料來源來建立分析查詢，具體操作如下：

1.打開平台管理，在資源設計節點或其目錄節點下右鍵選擇新建查詢>視覺化查詢，跳出"選擇資料來源"視窗。

2.在跳出的“選擇資料來源”視窗中，切換到“資料來源”頁籤下，選擇“DEMO2016”關聯式資料來源

3.在資料來源的“產品類別（categories）”表下找到“產品類別（categoryName）”欄位，拖拉到“欄位”區中；

在“產品（products）”表下找到“產品名稱（productname）”欄位，拖拉到“欄位”區中；

在“訂單明細（orderdetails）”表下找到“單價（unitprice）”欄位、“數量（quantity）”欄位和“訂單編號（orderid）”欄位，拖拉到“欄位”區中。如下：

4.在視覺化查詢工具列上點擊預覽資料按鈕。則可以在跳出的對話方塊中預覽該視覺化查詢所查詢出的資料。

如下：

5.在視覺化查詢上工具列上按一下儲存按鈕，跳出"儲存視覺化查詢"對話方塊。

6.將視覺化查詢儲存到“Demo資料來源”目錄下，命名為"產品銷售數據集"，如下：

視覺化查詢詳細資訊請參見：視覺化查詢章節。

2.1.2 帶參數的分析查詢

有些時候，需要在查詢中使用參數，對資料進行篩選。如下，切換上方的參數值後，資料可相應變化。

此類查詢的實現，需要先定義參數。

2.1.2.1 定義參數

下面，我們將建立一個參數，命名為“產品目錄名稱”，後續將其使用到查詢中，對產品目錄資料進行過濾。具體步驟如下：

1.在“平台管理”介面左側的資源樹中，找到“公共設定=>參數定義”目錄。

2.在該目錄或子目錄下右鍵=>新建=>參數。

3.在跳出的"資料來源選擇”視窗中，選擇據源基礎設定中建立的“DEMO2016”資料來源，進入“參數編輯”介面

4.在“參數編輯”視窗中，輸入參數名稱為“產品目錄名稱”，資料類型選擇“字串”，控制項類型選擇“下拉列表”。如下圖：

5.點擊“下一步”，根據需要設定參數的備選值和預設值設定。備選值是指該參數可選擇的值；預設值是指該參數使用時，預設選擇的值。如下圖：

6.點擊“下一步”，無論是實際值還是顯示值，都設定為“產品類別”。如下：

7.點擊儲存按鈕，儲存目前新建的參數。如下：

接下來在查詢中使用該參數。

說明

關於參數設定的詳細說明請參見參數定義。

2.1.2.2 在查詢中使用參數

建立好參數後，在查詢中可以使用該參數，建立起欄位與該參數的過濾關係。步驟如下：

1.在“資源設計”下找到之前建立的“產品銷售資料集”，按兩下打開

2.找到“DEMO2016”資料來源下“產品類別（categories）”表中的“產品類別（categoryname）”欄位，將其拖拉到查詢的“條件區”中，如下：

3.在條件區“產品類別”後面輸入"="，如下：

4.從左側“參數定義”目錄下找到之前建立的“產品目錄名稱”參數，拖拉到條件區的“=”，後面，使得”產品類別”表中的“產品類別”欄位與參數“產品目錄名稱”進行等式運算。如下：

5.點擊查詢工具列上的“預覽資料”按鈕，資料預覽如下：

6.點擊工具列上的“儲存”按鈕，儲存視覺化查詢即可。

2.2 快速建立試算表

試算表

試算表是指基於資料集（視覺化查詢、SQL查詢、原生SQL查詢和儲存過程查詢等），在Excel工具中建立的、格式和計算複雜的一類業務報表。它的優勢在於完全基於Excel建立報表，簡單易學；可以實現跨資料來源和跨分析查詢的報表；可以設計出結構複雜的各類報表樣式。

外掛程式安裝

試算表報表是在Excel中建立的報表，需要在開發機器上安裝Excel外掛程式。

（1）在開發及其客戶的開發機器上準備好Excel環境，目前支援的Excel版本有Microsoft Office Excel 2010、2013以及更新的Excel版本

（2）執行Synapse的試算表外掛程式安裝程式，進行外掛程式安裝。

功能入口

安裝好Synapse Excel使用者端外掛程式後，打開EXCEL程式，在檔案功能表列上會出現Synapse頁籤，如下圖所示：

操作介面

2.2.1 建立試算表

接下來，我們將以下面試算表為例，介紹建立試算表報表的完整步驟。

2.2.1.1 建立流程

在Synapse中建立並瀏覽試算表的流程如下：

其中，定義關聯式資料來源和設定資料基礎請參考資料來源基礎設定；建立分析主題請參考建立分析主題，建立分析查詢請參考建立分析查詢。

2.2.1.2 建立步驟

2.2.1.2.1 定義關聯式資料來源和資料設定

參考資料來源基礎設定，建立關聯式資料來源連接上資料庫，並且進行基礎設定。如下：

2.2.1.2.2 建立分析主題

按照業務需要選擇是否建立分析主題，若需要，可參考：建立分析主題進行建立，本範例中不涉及分析主題的建立。

2.2.1.2.3 建立分析查詢

參考建立分析查詢，實現視覺化查詢“產品銷售資料集”，該資料集中輸出試算表所需的“產品類別”、“產品名稱”、“單價”、“數量”和“訂單編號”欄位。如下：

2.2.1.2.4 安裝使用者端

下載Synapse Excel外掛程式安裝程式，點擊SynapseExcelAddin.exe檔進行安裝，安裝前需關閉所有的Excel檔。

第一次安裝後，如果伺服器版本更新，則打開Excel檔後試算表使用者端會自動跳出更新提示，可以根據提示安裝更新。

2.2.1.2.5 登錄使用者端

安裝好使用者端後，打開Excel，在Excel檔功能表列上會出現Synapse頁籤。在Synapse頁籤工具列上點擊設定按鈕，則會跳出"伺服器設定"對話方塊，根據實際情況填寫需連接的產品伺服器位址、使用者名、密碼後，點擊確定按鈕，則完成伺服器設定。

伺服器設定完成後，點擊工具列上按鈕登錄，如下圖：

登錄成功後登錄按鈕會變成登出按鈕，並在右側出現資料集面板，如下圖：

2.2.1.2.6 定義試算表

實現以上試算表效果，請執行下列操作：

（1）建立靜態表格樣式

在一個空白的Excel工作表中，先設計好如下的靜態表格樣式。

包括：將第一行的B、C、D、E、F列合併，在儲存格中輸入“產品銷售分析報表”，並將字體調大；

將B列和C列的第2和第3行合併，在儲存格中輸入“產品資訊”；

將D列的第2和第3行合併，在儲存格中輸入“銷售量"等等

（2）拖拉欄位

從右側資料集面板下找到上面步驟建立的視覺化查詢"產品銷售資料集"，並打開。如下圖：

從視覺化查詢"產品銷售資料集"分別拖拉"產品目錄名稱"、"產品名稱"、"數量"、"單價"和"訂單編號"到儲存格B4、C4、D4、E4、F4。如下：

（3）設定欄位屬性。

選中“數量”欄位所在的D4儲存格，設定其展開屬性"不展開"，資料設定為"匯總"，匯總方式為"求和"，使數量進行匯總計算。如下圖：

選中"單價"欄位所在的儲存格E4。設定其展開屬性"不展開"，資料設定為"匯總"，匯總方式為"平均值"。如下圖：

選中"訂單編號"欄位所在的儲存格F4。設定其展開屬性"不展開"，資料設定為"匯總"，匯總方式為"計數"。如下圖：

（4）設定單元格格式。

選中D4、E4、F4儲存格，右鍵選擇設定單元格格式，如下圖：

在跳出的對話方塊中選擇數數值型別，並設定小數位數為2。

（5）美化調整

設定儲存格的背景顏色、邊框顏色、字體和列高欄寬等細節。如下：

（6）發佈試算表。

點擊工具列上發佈按鈕，點另發佈會跳出"儲存文件"對話方塊，根據需要選擇儲存路徑和名稱，儲存此試算表。

(7)打開、瀏覽試算表

可以直接在Excel中點擊“預覽”按鈕瀏覽試算表

也可以在瀏覽器中登錄Synapse，在前一步驟中儲存的目錄下找到該試算表，按兩下打開，進行瀏覽。

預覽效果如下：

試算表報表詳細資訊請參見：試算表章節。

2.2.2 帶參數的試算表

如果需要在試算表中添加參數，對資料進行動態篩選，如下：

可按如下步驟實現：

1.參考快速建立分析查詢中的“帶參數的查詢”，建立一個參數，並在查詢中使用。如下：

2.按照前面試算表的建立流程，使用步驟1中的查詢，建立試算表即可。

當試算表所引用的查詢中使用了參數時，該試算表上也會同樣使用參數，並在報表介面上呈現參數。

2.3 建立綜合儀表板

2.3.1 說明

使用試算表可以建立各式各樣複雜樣式的綜合儀表板。如下：

本文件以下面的範例，示範如何建立此類互動式儀表板。如下：該儀表板頁面呈現的是產品的銷售資料分析，存在區域維度，可切換。呈現的內容是產品目錄匯總的直條圖和兩個明細資料的圓餅圖、表格。

通過切換“選擇區域”位置的下拉列表，可以切換報表中匯總直條圖、明細玫瑰圓餅圖和明細表格的資料。同時在匯總直條圖上點擊柱子，可同時更新明細玫瑰圓餅圖和明細表格，並且明細玫瑰圓餅圖和明細表格的資料，會變化為對應產品目錄的資料。

2.3.2 設計

1.呈現主題確定

本範例呈現的主題是產品銷售資料分析，對產品目錄、產品名稱的資料進行分析、以及明細資料的查看，並且可以切換區域維度，對不同區域下的產品銷售資料進行分析。

2.呈現形式和佈局確定

作為儀表板，強調的是資料視覺化，以各種圖形，增強資料的可讀性。結合呈現主題和表格、圖形（包括直條圖、圓餅圖、折線圖、聯合圖等等）的特點，設計如下：

（1）產品目錄匯總資料，維度為產品目錄、指標為銷售量。呈現形式為直條圖

（2）產品明細1，維度為產品名稱、指標為銷售量。呈現形式為圓餅圖。

（3）產品明細2，呈現資料包括訂單的明細資料，包含產品名稱、訂單編號、每一訂單的銷售量、折扣和單價。呈現形式為表格。

通過直條圖、圓餅圖和表格結合的形式，增強儀表板頁面的美觀效果和資料的可讀性。

對儀表板頁面進行整體佈局，如下：

3.資料交互效果確定

由於該儀表板頁面中存在匯總資料和明細資料，同時維度中“產品目錄”和“產品名稱”是父項與子項的關係。可實現內部的直條圖聯動圓餅圖和明細表格的效果。

（需要注意的是，圓餅圖和明細表格在建立時，其查詢需要添加產品目錄相關的參數，以便接收直條圖傳遞過來的產品目錄值並更新資料）

下面我們就開始來建立這個儀表板頁面。

2.3.3 實現

1.建立參數

參考快速建立分析查詢中的“帶參數的查詢”部分，建立兩個下拉列表參數。分別是“產品目錄名稱”參數，和“選擇區域”參數。

其中“產品目錄名稱”參數的備選值和預設值定義如下：

“選擇區域”參數的備選值和預設值定義如下：

2.建立分析查詢

參考快速建立分析查詢，針對儀表板頁面上的三個元素（直條圖、圓餅圖和表格），分別建立3個視覺化查詢，命名為“直條圖查詢”、“圓餅圖查詢”和“表格查詢”。

（1）其中直條圖查詢的輸出欄位是“產品類別”表中的“產品目錄名稱”欄位、“訂單明細”表中的“數量”欄位。條件中設定“訂單”表中的“發貨區域”欄位等於參數“選擇區域”。

預覽如下：

（2）圓餅圖查詢中輸出的欄位是“產品”表中的“產品名稱”欄位、“訂單明細”表中的“數量”欄位。條件中設定兩個條件，分別是“訂單”表中的“發貨區域”欄位等於參數“選擇區域”，以及”產品類別”表中的“產品目錄名稱”欄位等於參數“產品目錄名稱”，兩個條件之間的關係是and的關係。

預覽如下：

（3）表格查詢中輸出的欄位是“產品”表中的“產品名稱”欄位、“訂單明細”表中的“訂單編號”、“單價”、“數量”和“折扣”欄位。條件中設定兩個條件，分別是“訂單”表中的“發貨區域”欄位等於參數“選擇區域”，以及”產品類別”表中的“產品目錄名稱”欄位等於參數“產品目錄名稱”，兩個條件之間的關係是and的關係。

預覽如下：

3.建立試算表

在Excel中登錄Synapse，並新建一個空白的試算表，設定4個sheet，分別命名為“呈現”、“直條圖”、“圓餅圖”和“表格”。我們會在後面三個sheet中分別製作好直條圖、圓餅圖和表格，並最終移動到“展示”sheet中進行呈現

4.直條圖製作

（1）切換到“直條圖”sheet中，在A1儲存格中輸入“產品目錄”，B1儲存格中輸入“銷售量”，並從右側資料集面板中找到“直條圖查詢”，將查詢中的“產品目錄名稱”欄位，拖拉到A2儲存格，將“數量”欄位拖拉到B2儲存格

（2）選中“數量”所在的B2儲存格，設定其展開方向為“不展開”，資料設定為“匯總”，匯總為“求和”。

（3）選中A1：B2儲存格，在工具列“Synapse”頁籤下，找到“圖形”功能表，點擊

（4）在跳出的圖形設定介面中，點擊“直條圖”，選擇“普通直條圖”

（5）勾選“產品目錄”為“分類（X）軸”，“銷售量”為“指標（Y）軸”，如下：

（6）點擊確定，在Excel中會建立一個圖形，如下：

5.圓餅圖製作

（1）同理，按照直條圖的製作方式，切換到“圓餅圖”sheet中，使用視覺化查詢“圓餅圖查詢”中的欄位，建立“南丁格爾玫瑰圖”（選擇圖形時，需要選擇圓餅圖下的“南丁格爾玫瑰圖”），如下：

（2）切換到“圖例”這個頁籤下，設定圖例顯示在圖表右側

（3）點擊確定，如下：

6.表格製作

（1）切換到“表格”Sheet中，設定如下靜態表格樣式

（2）從右側資料集面板中找到“表格查詢”中的“產品名稱”、“訂單編號”、“單價”、“數量”和“折扣”欄位，分別拖拉到A3-E3儲存格中。如下：

（3）選擇A3-E3，設定“展開方向”為“從上到下”，資料設定為“列表”

（4）選中F3儲存格，工具列切換到“檢視”頁籤，點擊“凍結窗口”>“凍結分割窗格”。由於表格的資料很多，通過這個設定選項，使得資料滾動時表頭固定不動。

凍結窗格是Excel本身的功能，關於該功能更多內容請參考微軟相關說明。

（5）設定表格的背景顏色、邊框顏色、字體、置中、列高等細節，如下：

7.呈現頁面製作

（1）切換到“呈現”sheet頁，縮小A列欄寬，合併B4-H4儲存格，輸入“產品銷售匯總直條圖”。並設定背景顏色、字體顏色和字體大小。如下：

（2）切換到“直條圖”sheet，在直條圖上右鍵->移動或複製

（3）在跳出的視窗中，將直條圖移動到“呈現”Sheet中

（4）則該圖形會被移動到“呈現”sheet中，將該圖形位置和寬度進行調整，與B4-H4對齊，並縮小圖形的高度。如下：

（5）同理，將B7-H17儲存格合併，並輸入產品數據明細占比圓餅圖，調整背景顏色、字體顏色、字體大小等

並將“圓餅圖”sheet中的圓餅圖移動到該位置下，調整圖形寬度和位置，與B17-H17對其，並縮小圖形高度

（7）切換到“表格”sheet中，選擇A1-E3儲存格，複製

（8）切換到“呈現”Sheet中，右鍵>選擇性貼上>連結的圖片。通過這樣的操作，使用Excel的照相機功能，將表格照到“呈現”sheet中。注意：如果是WPS，則直接使用工具列上的“照相機”功能即可

貼上後，效果如下：

（9）調整貼上過來的“圖片”的位置和寬、高，注意高度與直條圖和圓餅圖整體高度對齊

（10）此時，可點擊發佈該試算表報表，並預覽效果如下：

8.參數控制項設定

（1）在B2儲存格中輸入“選擇區域：”並設定字體顏色。如下：

（2）點擊工具列“Synapse”頁籤下的“下拉列表”控制項，在設定控制項格式視窗中，設定“備選值”為參數“選擇區域”、“影響目標”為參數“選擇區域”

（3）點擊確定，會插入一個下拉列表控制項。調整其位置和長度，如下：

（4）在控制項上右鍵=>設定，進入設定介面。

（5）切換到“外觀”頁籤下，對其外觀進行如下設定：

（6）點擊確定，如下：

9.美化細節調整

接下來需要對該儀表板頁面做細節美化和調整。主要是對直條圖和圓餅圖的設定，如下：

（1）直條圖呈現時，位置有點偏移。可以對直條圖做如下調整：在直條圖上右鍵>設定，（2）進入圖形設定介面切換到“基本上設定”，調整一下直條圖的邊距，使得直條圖在直條圖區域中位置置中，寬度合適

（3）同理，在圓餅圖上右鍵>設定，進入圖形設定介面。點擊“指標（Y）軸”欄位後的設定按鈕，進入序列設定介面

（4）設定圓心位置為說明30%，垂直50%，使得圓餅圖與圖例的距離稍遠，並且垂直置中

（5）還可以切換到“進階”選項，對直條圖和圓餅圖設定主題，通過主題設定，可切換圖形的呈現效果

本範例中將直條圖和圓餅圖的圖形設定均設定為“shine”

（6）再對報表整體做一下細節調整，比如列高、欄寬等等。最終效果如下：

預覽如下：

10.資料傳值設定

接下來設定點擊直條圖，聯動圓餅圖和表格。

（1）在直條圖上右鍵>設定，進入圖形設定介面。切換到“資料傳值設定”頁籤。

（2）在“參數傳值設定”中勾選“參數傳值”，設定將直條圖中的“產品目錄”欄位的“真實值”傳遞給報表中的“產品目錄名稱”參數

（3）由於“產品目錄名稱”參數不會直接顯示在報表中，他的值是通過上面傳遞而得到的。因此需要隱藏“產品目錄名稱”參數。點擊工具列上的“參數排版”

（4）將“產品目錄名稱”參數拖動到下方隱藏區域中，將該參數從報表的呈現介面隱藏

（5）由於報表內的元素聯動，不應該更新整個報表，因此需要設定“局部更新”，使得參數值變化時，只有相關的資料和圖形變化，而不是整個報表的重新渲染載入。

點擊工具列上“Synapse”頁籤下的“頁面設定”，“切換參數更新設定”勾選“局部更新”

（6）由於報表中有表格存在，上述步驟中局部更新，有可能造成表格格式的錯亂，因此要設定貼上的表格部分不允許局部更新。

在“呈現”sheet中，選中貼上的表格圖片，右鍵=>允許局部更新，點擊“取消局部更新”。

注意：“使用局部更新”圖示為反灰狀態，表示該映射的表格是允許局部更新的；“使用局部更新”前的圖示為，表示該映射的表格是不允許局部更新的狀態。

11.最後，隱藏後面的三個sheet，發佈預覽如下