報表範例

表格結構分類：按照表格資料的組織結構分類

清單報表

分組報表

交叉報表

表單報表

報表佈局分類：按照報表中各表格的佈局分類

分片報表

分欄報表

迴圈擴展報表

報表功能分類範例：按照報表自身功能分類

分頁報表

多Sheet報表

圖形報表

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML1b70538.PNG$

填報報表

聯動報表

欄頭排序報表

資料來源分類範例：按照資料來源分類

資料來源類型

參考範例

同資料來源

本章節中的清單報表、分組報表、交叉報表等都屬於此類

跨資料來源資料表間資料無關係

試算表-資料來源分類

跨資料來源資料表間資料關聯關係

試算表-資料來源分類

7.1 試算表-表格結構分類

表格是報表的根本，通常一個表格即成一張報表，按照表格的結構對報表進行劃分，可以分為：清單報表、分組報表、交叉報表和表單報表。

7.1.1 試算表-清單報表

報表簡介

清單表是以清單結構呈現資料的一類報表。

作用：用於記錄資料清單。

適用場景：適用資料記錄、查詢的情況。

報表結構：清單表主要有“欄頭+資料區”和“列頭+資料區”兩種結構，如下圖：

範例效果

下表是最常見的一類清單表：

資料要求

定義如下資料集進行清單表設定。

分析設定

該表比較簡單明瞭，具體的分析及實現詳情如下：

設定內容

分析

方法或步驟

欄頭

有兩種方式設定欄頭欄位：

1、手動輸入欄頭。

2、系統自動取得資料集欄位名稱設定欄頭，並設定欄頭樣式。

系統自動取得欄頭名稱的方法如下：

在“資料集面板”中通過Ctrl或Shift鍵依次批次選中欄位，拖拉這些欄位到列欄頭第一個儲存格處（此處為儲存格B3），彈出右鍵功能表，在右鍵功能表中選擇插入名稱>從左到右。

注：該選擇方式必須保證資料集輸出欄位順序與目前報表欄頭欄位順序一致。 2、欄頭樣式：字體：粗體；字型大小：11；字體顏色：白色；填充色：#2F5597；置中顯示，調整適當欄寬。

綁定欄位

將資料集欄位與試算表儲存格進行綁定。

在“資料集面板”相應的資料集下通過Ctrl或Shift鍵選擇欄位，拖拉到儲存格B4，彈出右鍵功能表，選擇從左到右。

資料區

設定綁定欄位各列以清單式顯示，設定方法有兩種：

1、“頁面設定>報表類型”中的“清單報表”

2、“屬性面板>擴展面板”或工具列中的“列表”

方法1：“頁面設定>報表類型”中的“清單報表”

方法2：“屬性面板>擴展面板”或工具列中的“列表”

表格樣式

設定表格中內容的字體、大小、顏色、置中對齊和間隔行色。

字體：粗體；大小：10；並設定置中顯示。

間隔行色通過Excel的“條件”設定選項進行設定：

規則詳情如下：

標題

標題直接在試算表中輸入標題名稱，設定字體、大小、顏色、置中對齊。

儲存格區域“B2:L2”輸入標題：供應商資訊清單。

標題字體：粗體粗體；字型大小：12；字體顏色：#203764；置中顯示；

完成以上設定後，儲存即可。

注意事項

1、通過“頁面設定>報表類型”中的“清單報表”和“屬性面板>擴展面板”或工具列中的“清單”項實現的清單表，其區別為：“頁面設定>報表類型”中的“清單報表”不支援多sheet報表。

7.1.2 試算表-分組報表

報表簡介

分組報表是以分組表結構呈現資料的一類報表。

作用：用於記錄資料清單。

適用場景：適用資料記錄、查詢的情況。

報表結構：分組報表主要由有“欄頭+分組區+資料區”和“列頭+分組區+資料區”兩種結構，如下圖：

範例效果

下表是最常見的一類分組報表：

資料要求

定義如下資料集進行分組報表設定。

分析設定

該表結構簡潔，資料清晰，具體的分析及實現詳情如下：

設定內容

分析

方法或步驟

欄頭

有兩種方式設定欄頭欄位：

1、手動輸入欄頭。

2、系統自動取得資料集欄位名稱設定欄頭，並設定欄頭樣式。

1、系統自動取得欄頭名稱的方法如下：

在“資料集面板”中通過Ctrl或Shift鍵依次批次選中欄位，拖拉這些欄位到欄頭第一個儲存格處（此處為儲存格C3），彈出右鍵功能表，選擇插入名稱>從左到右。

注：該選擇方式必須保證資料集輸出欄位與目前報表欄頭欄位順序一致。

2、欄頭樣式：字體：粗體；字型大小：11；字體顏色：白色；填充色：#2F5597。

綁定欄位

將資料集欄位與試算表儲存格進行綁定。

在“資料集面板”相應的資料集下通過Ctrl或Shift鍵選擇欄位，拖拉到儲存格C4，彈出右鍵功能表，選擇從左到右。

分組區

設定欄位“銷售區域”、“銷售城市”、“產品”逐層分組。

通過系統預設的父儲存格設定即實現逐層分組，因此目前報表設定不需要自訂父儲存格。

選中儲存格C4、D4、E4，在工具列設定：擴展方向為“從上到下”，資料設定為“分組”：

資料區

設定欄位“銷售額”、“銷售量”中的資料進行匯總合計顯示。

選中儲存格F4、G4，在工具列設定：擴展方向為“不擴展”，資料設定為“匯總”，匯總為“求和”：

表格樣式

分別設定表格中分組區和資料區的填充色。

分組區背景色：選中儲存格C4、D4、E4，設定填充色為#F2F2F2；

資料區背景色：選中儲存格F4、G4，設定填充色為#FAFAFA；

所有資料置中顯示；

邊框：白色，2px；

標題

標題直接在試算表中輸入標題名稱，設定字體、大小、顏色、置中對齊。

儲存格區域“C2:G2“輸入標題：各區銷售情況統計；

標題字體：粗體粗體；字型大小：12；字體顏色：#203764；置中顯示；

完成以上設定後，儲存即可。

7.1.3 試算表-交叉報表

報表簡介

交叉報表是分組報表的一種特殊情況，列欄都呈現分組結構，交叉區域呈現資料的一類報表。

作用：用於多維度統計資料。

適用場景：適用資料統計的情況。

報表結構：交叉報表主要由有“欄組+列組+交叉資料區”一種結構，如下圖：

範例效果

下表是最常見的一類交叉報表：

資料要求

定義如下資料集進行分組報表設定。

分析設定

該表結構簡潔，資料清晰，具體的分析及實現詳情如下：

設定內容

分析

方法或步驟

欄頭綁定欄位

通過拖拉欄位到儲存格，將維度欄位綁定在欄頭。

多個維度逐層分組，因此預設父儲存格設定即可。

1、在“資料集面板”相應的資料集下拖拉欄位“產品類別”、“產品名稱”到儲存格D2、D3；設定“從左到右擴展，分組“；

2、欄頭樣式：字體：粗體；字型大小：10；字體顏色：黑色；填充色：#DAEEF3；置中顯示。

列頭綁定欄位

通過拖拉欄位到儲存格，將維度欄位綁定在列頭。

多個維度逐層分組，因此預設父儲存格設定即可。

1、在“資料集面板”相應的資料集下拖拉欄位“銷售區域”、“銷售城市”到儲存格B4、C4；設定“從上至下擴展，分組“；

2、列頭樣式：字體：粗體；字型大小：10；字體顏色：黑色；填充色：#DAEEF3。

資料區

拖拉數值欄位（量值欄位）進行匯總合計顯示。

交叉資料區採用預設父儲存格設定即可，其資料計算是根據預設上父儲存格和預設左父儲存格進行分組匯總。

1、在“資料集面板”相應的資料集下拖拉欄位“銷售額”到儲存格D4；

2、選中儲存格D4，在工具列設定：擴展方向為“不擴展”，資料設定為“匯總”，匯總為“求和”：

3、資料區樣式：字體：粗體；字型大小：10；字體顏色：黑色；填充色：#FFEB9C；

交叉表頭設計

主要有兩種方法進行設定：

1、利用Excel的工具。

2、利用Synapse的工具。

這兩種方式的對比請參見注意事項。

1、利用Excel的工具設計：通過插入功能表下的 形狀 > 文字、斜線 工具進行設計：

其中，文字不帶邊框。

設計介面如下：

瀏覽效果如下：

2、利用Synapse的工具設計：通過Synapse中的格式>斜線工具進行設計：

設計介面如下：

其中，列頭名稱寫在分隔線前，欄頭名稱寫在分隔線後。

瀏覽效果如下：

3、交叉表頭填充色：#FFEB9C；

警示設定

設定銷售資料中，異常資料的警示顯示。

1、設定入口：Excel的常用功能表下的工具條件格式>突出顯示儲存格規則：

2、“1<=銷售資料且<=200”的警示設定：

表格樣式

主要是表格邊框線條的設定。

邊框顏色：灰色；邊框大小：2px；線型：實線；

標題

標題直接在試算表中輸入標題名稱，設定字體、大小、顏色、置中對齊。

儲存格“B1:D1“輸入標題：各區產品銷售情況。

標題字體：粗體粗體；字型大小：12；字體顏色：#203764；置左顯示；

完成以上設定後，儲存即可。

注意事項

1、交叉表頭兩種實現方式的對比：

Excel工具實現的交叉表頭，樣式上更靈活些，可以設定斜線的顏色、字體大小顏色等，有所見即所得的效果，但操作上會複雜些。
Synapse工具實現的交叉表頭，操作更便捷些，但格式固定。

7.1.4 試算表-表單報表

報表簡介

表單報表是業務應用比較強的一類報表，沒有列欄資料沒有規律。

作用：用於資料錄入、資料查詢。

適用場景：適用資料錄入、查詢的情況。

報表結構：表單報表沒有固定表結構，根據實際業務需要進行規劃。

範例效果

下表是比較具有代表性的一類表單報表：

資料要求

該範例中，求職人員是通過單選的參數進行確定的，因此定義的資料集需要包含求職人的參數。

通過分析表格樣式，我們發現求職人的家庭關係是目前表單中的一個子表，因此我們建議通過兩個資料集來取得相關資料資訊：

資料集分類

資料集設計

資料集效果

取得求職人的基本資訊

由於目前資料集欄位過多，只截取部分資料結果，發現這些基礎資訊只記錄在一條資料中。

取得求職人的家庭關係資訊

分析設定

該表各資料資訊呈分散式排列，且都是圍繞求職人，即“姓名”的內容擴展的資料資訊，由於該表實現的基礎資料是一列資料記錄，因此只需要保持預設的父儲存格即可。

具體的實現方法如下：

設定內容

分析

方法或步驟

定義表格樣式

該類型的表格樣式都有範本，或者比較固定，我們可以先在報表設計器中將表格樣式設計好，留下資料空白即可。

先設計好表格樣式：

該表格樣式設計比較多，使用者可以按照自己的審美對文字、邊框、填充色進行設定。

欄位綁定及設定

由於資訊是分佈排列，因此需要分別拖拉所有欄位，並且進行擴展屬性、附屬屬性的設定。

各欄位的綁定情況如欄位綁定及設定表

欄位綁定及設定表

報表資訊欄位

對應資料集綁定的欄位

綁定的儲存格

姓名

基本資訊：f_name

性別

基本資訊：gender

出生日期

基本資訊：birth_date

戶籍

基本資訊：hukou

民族

基本資訊：mengzu

身高

基本資訊：height

婚姻狀況

基本資訊：huny

宗教

基本資訊：zongj

最高學歷

基本資訊：xueli

畢業學校

基本資訊：byxx

合併儲存格C9-G9

專業

基本資訊：major

合併儲存格F9-G9

手機號碼

基本資訊：tel

D10

父母電話

基本資訊：無欄位

家庭電話

基本資訊：hp

D11

E-mail

基本資訊：email

合併儲存格F11-G11

普通話能力

基本資訊：pthnl

C12

英語能力

基本資訊：yynl

E12

韓語能力

基本資訊：hynl

G12

目前所在地

基本資訊：suozd

合併儲存格C13-G13

從事業務種類

基本資訊：csywzl

合併儲存格C14-G14

興趣/愛好

基本資訊：xinqu

合併儲存格C15-G15

專長

基本資訊：zhuanc

合併儲存格C16-G16

家庭狀況-關係

家庭關係：ganxi

C18

家庭狀況-姓名

家庭關係：f_name

D18

家庭狀況-年齡

家庭關係：age

E18

家庭狀況-職業

家庭關係：zhiwu

F18

家庭狀況-職位

家庭關係：無欄位

家庭狀況-備註

家庭關係：無欄位

教育背景及描述

基本資訊：jybj

合併儲存格C21-H21

工作經歷及描述

基本資訊：gzjl

合併儲存格C24-H26

業績及獎勵

基本資訊：無欄位

資格證

基本資訊：無欄位

自我評價

基本資訊：zwpj

合併儲存格B36-H36

以上所有綁定欄位的“擴展屬性”設定為：從上到下，分組擴展；附屬屬性設定為：預設父儲存格；字體樣式：粗體，10號。

完成以上設定後，儲存即可。

注意事項

1、表單報表通常在回寫填入的業務場景中也多有運用，需要瞭解回寫詳情請參見填報報表章節。

2、表單報表在業務應用中不斷延伸，如：分欄報表、迴圈擴展報表。

7.2 試算表-報表佈局分類

資料統計時，有時候一個表格並不能完整、準確的表達資料資訊，此時我們可以通過在報表中設定多個報表來進行展示。

當報表中包含或隱含多個表格時，按照表格的佈局，可以分為：分片報表、分欄報表和迴圈擴展報表。

7.2.1 試算表-分片報表

報表簡介

分片報表是在報表中通過一個或多個表格呈現單源或多來源資料的報表種類。

作用：用於多維度統計資料。

適用場景：適用多來源資料呈現。

報表佈局：分片報表的表格佈局沒有規律，根據實際業務需求進行規劃。

範例效果

以下列出了分片報表的幾種範例效果：

範例

分析說明圖

分析說明

範例1：同表格中資料左右分片

該分片報表，雖然展示的是一個表格，但是隱含了兩個表格的資訊：分別按照區域和月份統計不同產品類別下的產品銷售額情況。

由於統計的都是相同的物件“產品”，且產品資訊在該報表中位於列頭位置，在產品的基礎上再分別綜合區域和月份進行資料統計，表格結構基本一致，屬於交叉分組類，因此我們將區域和月份的資料按照不同的資料區塊處理，且以左右分片的形式呈現，加以一些樣式設計，便得到目前範例效果。

該範例的實現詳情請參見“同表格中資料左右分片”。

範例2：同表格中資料上下分片

該分片報表，形式上是一個表格，但是也顯示了兩個資料區塊的內容：從時間角度和從區域角度進行的銷售資料統計，呈上下分片無縫銜接的形式，這兩個資料區塊共用欄頭，加上表格樣式設計實現範例效果。

該範例的實現詳情請參見“同表格中資料上下分片”。

範例3：不同表格左右分片

該分片報表，以兩個獨立表格的形式呈現不同維度的銷售資料。

該範例的實現詳情請參見“不同表格左右分片“。

資料要求

分片報表有不同的資料區塊，這些資料可以來源同一個資料結果集，也可以來源不同的資料結果集。

本章節我們以來源同一個資料集進行設定示範，來源不同的資料集的分片報表範例詳情請參見多資料集報表章節。

下表顯示的是同資料集的資料結果：

分析設定

三種不同類型的分片報表實現的詳情如下。

同表格中資料左右分片

設定內容

設定方法

效果

設定表格中的固定內容：

按區域統計

按月份統計

1、在儲存格D3中輸入“按區域統計”；

2、在儲存格F3中輸入“按月份統計”；

3、設定樣式：字體“粗體粗體”、字型大小“10”、字體顏色“#2F5597”，置中顯示；

設定交叉表頭

1、合併儲存格“B3：C4”；

2、選擇該合併儲存格，通過Synapse工具列的 格式>斜線，設定交叉表頭：

2、在“斜線”視窗的文字方塊中輸入如下內容：

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTMLe19f66.PNG$

3、設定樣式：字體“粗體粗體”、字型大小“12”、字體顏色“#2F5597”，置中顯示；

拖拉欄位

1、欄位“產品目錄名稱”拖拉到儲存格B5；

2、欄位“產品名稱”拖拉到儲存格C5；

3、欄位“銷售區域”拖拉到儲存格D4；

4、欄位“月”拖拉到儲存格F4；

5、欄位“銷售額”拖拉兩次，分別到儲存格D5和F5；

6、設定樣式：

- 儲存格B5：字體“粗體”，字型大小“10”，字體顏色“#2F5597”，置中顯示；
- 儲存格D4、F4：字體“粗體”，字型大小“10”，字體顏色“白色”，填充色“#2F5597”，置中顯示；
- 儲存格C5、D5、F5：字體“粗體”，字型大小“10”，字體顏色“黑色”，置中顯示；

定義相關屬性：擴展屬性、資料設定、附屬屬性

1、儲存格B5、C5：從上到下擴展，分組顯示，父儲存格預設；

2、單元D4、F4：從左到右擴展，分組顯示，父儲存格預設；

3、儲存格D5、F5：不擴展，匯總求和顯示，父儲存格預設；

設定表格邊框，列高欄寬調整

1、按照範例設定邊框，邊框顏色為“#2F5597”，並調整列高欄寬；

2、設計標題；

完成以上設定後，儲存即可。

同表格中資料上下分片

設定內容

設定方法

效果

設定欄頭

1、儲存格D1中輸入“銷售額（元）”；

2、儲存格E1中輸入“銷售量”；

3、設定樣式：字體“粗體粗體”，字型大小“10”，字體顏色“白色”，填充色“#2F5597”，置中顯示；

設定交叉表頭

1、合併儲存格“B1：C1”；

2、選擇該合併儲存格，通過Synapse工具列的格式>斜線，設定交叉表頭：

3、設定樣式：字體“粗體”，字型大小“10”，字體顏色“白色”，填充色“#2F5597”，置左顯示；

拖拉欄位

1、欄位“年”拖拉到儲存格B2；

2、欄位“月”拖拉到儲存格C2；

3、合併儲存格“B3：C3”，欄位“銷售區域”拖拉到該合併儲存格；

4、欄位“銷售額”拖拉兩次，分別到儲存格D2、E2；

5、欄位“銷售量”拖拉兩次，分別到儲存格D3、E3；

6、設定樣式：

- 字體“粗體”，字型大小“10”，字體顏色“黑色”，置中顯示；
- 儲存格“B2：E2”填充色：#CFE3E7；
- 儲存格“B3：E3”填充色：#E2F0D9；

定義相關屬性：擴展屬性、資料設定、附屬屬性

1、儲存格D2、D3、E2、E3：不擴展，匯總求和顯示，父儲存格預設；；

2、其它儲存格不需要修改，保持系統預設即可；

設定表格邊框，列高欄寬調整

1、按照範例設定邊框，邊框顏色為“#D9D9D9”，並調整列高欄寬；

完成以上設定後，儲存即可。

不同表格左右分片

左右分片的報表，由於左右兩側的表格存在共同的表格行，因此為了避免相互的影響，可以通過“資料覆蓋”或“資料區域”的方式來實現。

設定內容

設定方法

效果

設定欄頭

1、在右側資料集面板中對應的資料集下通過Ctrl或Shift鍵批次選擇欄位“銷售區域”、“銷售額”、“銷售量”，並拖拉到儲存格B2，在彈出的功能表中選擇插入名稱>從左到右；

2、再批次選擇欄位“年”、“月”、“銷售額”、“銷售量”，並拖拉到儲存格G2，在彈出的功能表中選擇插入名稱>從左到右；

3、設定樣式：

- 字體“粗體”，字型大小“10”，字體顏色“白色”，置中顯示；
- 儲存格區域“B2：D2”填充色：#66ABB3；
- 儲存格區域“G2：J2”填充色：#A9D18E；

拖拉欄位

1、在資料集下通過Ctrl或Shift鍵批次選擇欄位“銷售區域”、“銷售額”、“銷售量”，並拖拉到儲存格B3，在彈出的功能表中選擇從左到右；

2、批次選擇欄位“年”、“月”、“銷售額”、“銷售量”，並拖拉到儲存格G2，在彈出的功能表中選擇從左到右；

3、設定樣式：

- 字體“粗體”，字型大小“10”，字體顏色“白色”，置中顯示；
- 儲存格區域“B3：D3”填充色：#CFE3E7；
- 儲存格區域“G3：J3”填充色：#E2F0D9；

設定相關屬性：擴展屬性、資料設定、附屬屬性

1、儲存格C3、D3、I3、J3：不擴展，匯總求和顯示，父儲存格預設；

2、儲存格G3：左父儲存格“無”，其它設定選項保持預設；

2、其它儲存格不需要修改，保持系統預設即可；

注：由於兩個表格間沒有資料關係，因此儲存格G3應該把預設項改成“無”，以避免G3儲存格隨其預設左父儲存格B3而擴展，導致資料有誤。

設定資料填充方式

注：由於左右兩個表格的欄位行、分組等完全不同，但兩個表格又有共用的行，若選用“插入”式資料擴展方式，左側表格的擴展會影響右側表格效果，因此：

方案一修改資料填充方式為“覆蓋”，保證表格行是固定的，不是擴展的，然後再將資料填充到這些固定的行中；

方案二在資料填充方式為“插入”時，通過指定資料區域，在將左右兩側表格的資料區間的影響清除，以保證資料擴展效果正確。

方案一：通過資料覆蓋方式實現的具體操作

1、分別選中儲存格B3、G3，設定資料填充方式為：覆蓋；

2、其它儲存格的資料擴展方式不需要修改，保持系統預設即可；

方案二：通過定義資料區域實現的具體操作

1、在Synapse工具列中按一下區域，彈出“資料區設定”視窗；

2、按一下增加按鈕，彈出“選擇儲存格”視窗；

3、滑鼠在報表設計器中批次選擇儲存格“B3：D3"；

4、按照步驟2和3批次選擇儲存格“G3：J3"；

5、按一下確定後，返回“資料區設定”視窗，按一下該視窗中的確定按鈕即可。

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML136ff2c.PNG$

覆蓋設定：

設定表格邊框，列高欄寬調整

1、按照範例設定邊框，邊框顏色為“白色”，並調整列高欄寬；

完成以上設定後，儲存即可。

7.2.2 試算表-分欄報表

報表簡介

分欄報表是在報表中以分欄的形式組織多個表格佈局的一類報表。我們也可以稱之為名片式報表，每個報表佔據的空間很少，用於呈現關鍵資料資訊。

作用：用於資料的查詢和呈現。

適用場景：適用資料量比較少需要多欄呈現資料的情況。

報表佈局：分欄報表的表格為多欄均衡排列，每個表格結構一致。

範例效果

下圖顯示了分欄範例效果：

資料要求

分欄報表是基於父儲存格擴展原理而實現的一種報表形式，因此通常在資料集中定義一個識別字，用來確定分欄的數量和效果。

本範例的資料集要求如下：

分析設定

該分欄報表的實現詳情如下：

設定內容

設定方法

效果

設定表格樣式

1、在儲存格C2中輸入“電話：”；

2、在儲存格C3中輸入“傳真：”；

3、在儲存格B4中輸入“地址：”；

4、設定樣式：字體“粗體粗體”，字型大小“10”，表格填充色為“#FF2CC”，邊框為“#BFBFBF、2px”；

拖拉欄位

1、合併儲存格“B2：B3”，並拖拉欄位“客戶名稱”到該合併儲存格；

2、拖拉欄位“電話”到儲存格D2；

3、拖拉欄位“傳真”到儲存格D3；

4、合併儲存格“C4：D4”，並拖拉欄位“地址”到該儲存格；

設定相關屬性：擴展屬性、附屬屬性

1、設定儲存格B2：從左到右擴展，其它項預設；

2、儲存格D2、D3、C4：若設定從左到右擴展，則設定左父儲存格為“B2”，若設定從上到下擴展，則設定上父儲存格為“B2”；

3、儲存格C2、C3、B4：設定上父儲存格為“B2”，左父儲存格預設；

4、儲存格E2、B5：父儲存格預設即可。該項設定用於實現每個表格間的間隔；

設定分欄標識

1、拖拉欄位“分欄標識”到儲存格A2；

此時儲存格B2預設以A2為父儲存格，可以實現按照A2的內容進行分欄呈現。 2、隱藏A2儲存格，儲存後得到範例效果。

分欄效果延展

下表列出含有不同分欄標識的資料集而顯示的不同分欄效果：

資料集

效果

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML17b24ca.PNG$

7.2.3 試算表-迴圈擴展報表

報表簡介

迴圈擴展報表是按照某個指定的欄位自動迴圈擴展出多個表格的一類報表。

作用：用於資料查詢。

適用場景：適用查詢物件描述資料比較多的情況，通過一個表格呈現該物件的所有資料。

報表佈局：多個表格呈上下式段落佈局，每個表格結構一致。

範例效果

下面演示了循環擴展報表效果：

資料要求

這類型的報表通常用資料集查出明細資料或者直接使用資料庫中的明細資料。

實現方法

設定內容

設定方法

效果

設定表格固定內容

1、儲存格A1中輸入“員工資訊”，其樣式：字體“粗體粗體”，字型大小“11”，字體顏色“白色”；

2、儲存格輸入相應的內容：A2輸入“員工姓名”，A3輸入“年齡”，A4輸入“出生日期”，C2輸入“員工ID”，C3輸入“電話”，C4輸入“入職日期”，E2輸入“性別”，E3輸入“Email”，E4輸入“位址”，其樣式：字體“粗體粗體”，字型大小“10”，字體顏色“黑色”；

拖拉欄位

1、從右側資料集下拖拉欄位到相應的儲存格：“姓名”到儲存格B2，“年齡”到儲存格B3，“雇員生日”到儲存格B4，“雇員編號”到儲存格D2，“家庭電話”到儲存格D3，“入職日期”到儲存格D4，“性別”到儲存格F2，“郵箱”到儲存格F3，“雇員地址”到儲存格F4；

設定附屬屬性：自訂父儲存格

1、儲存格D2左父儲存格和上父儲存格都設定“無”；

2、表格中除D2之外的其它儲存格，設定附屬屬性：上父儲存格為D2或左父儲存格為D2；

3、儲存格區域“A5：F5”設定附屬屬性：上父儲存格為D2或左父儲存格為D2；

步驟2和3的設定可以實現，隨著員工擴展增加一名，就會增加一個資訊條記錄這名員工的資訊。

表格樣式

1、儲存格區域“A1: F1”的填充色：#66ABC5；

2、其它固定欄位所在單元的填充色：#CFE3E7；

3、“員工資訊”字體樣式：字體“粗體”，字型大小“11”，字體顏色“白色”，置左顯示；

4、其它儲存格字體樣式：字體“粗體”，字型大小“9”，字體顏色“黑色”，置中顯示；

3、邊框：顏色“#66ABB3”，大小“1px”；

4、調整欄寬列高；

完成報表設定，儲存即可。

7.3 試算表-報表功能分類

試算表實現了報表在資料分析或業務應用中的常見功能，主要分類有：分頁報表、多sheet報表、圖形報表、填報報表、聯動報表、欄頭排序報表。

7.3.1 試算表-分頁報表

報表簡介

分頁報表是以分頁的形式呈現報表資料的一種報表功能。

作用：用於將資料以列分頁呈現。

適用場景：適用資料量較大的報表。

功能入口

系統實現試算表分頁的方法：頁面設定+頁面規格設定。

分頁功能入口及設定介面如下：

功能入口

設定介面

適用情況

頁面設定

頁面設定-分組報表：Synapse工具列

該視窗設定詳情請參見電子表格-頁面設定內容。

適用於“資料設定”為“分組”或“清單”類的報表，詳情參見分組狀態內容：

頁面設定-清單報表：Synapse工具列

該視窗設定詳情請參見電子表格-頁面設定內容。只適用於固定欄頭型的報表，一旦設定了清單報表方式的分頁，系統會強制將目前報表定義為清單報表，有固定欄寬列高，不允許多Sheet呈現、附屬屬性和擴展屬性設定。

頁面規格

Excel的“頁面設定”功能表中的工具：紙張方向、紙張大小

必須與“頁面設定“結合使用。

分頁及效果

下面，我們將對清單報表和分組報表為例來演示分頁設定及效果：

分頁設定

分頁效果

清單狀態

前端效果：

方法1：清單報表分頁方式

1、在Synapse工具列中選擇頁面設定 > 報表類型>清單報表，進行如下設定：

此方式定義的分頁，每次切換至下頁時進行資料讀取。

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML1b70538.PNG$

清單狀態

方法2：分組報表分頁方式

1、在Synapse工具列中選擇頁面設定>報表類型>分組報表，進行如下設定：

2、在Excel的“頁面設定”通過紙張方向、紙張大小確定頁面：設定A4紙大小，縱向的方向。

分組狀態

前端效果：

只能通過“分組報表分頁方式”實現。然後，通過Excel的“紙張方向和紙張大小“確定頁面。

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML1c00dd7.PNG$

注意事項

由於欄分頁通常會導致表格樣式不完整，實際業務中只有極特殊的情況才需要用到欄分頁。因此本章節未對欄分頁進行詳說。

7.3.2 試算表-多sheet報表

功能簡介

多sheet報表是指試算表支援在Excel上的多個sheet中設計報表並呈現的一種報表功能。

作用：用於資料多sheet呈現。

適用場景：適用需要多sheet呈現的報表。

範例效果

下面將清單報表、分組報表的範例為例演示多sheet呈現效果：

分析設定

以上效果是基於清單報表和分組報表而實現的多sheet呈現：

1、開啟試算表設計器，新建一個sheet，兩個sheet分別命名為：清單報表、分組報表。

2、按照前面章節的報表範例建立清單報表和分組報表。

3、儲存以上設定即可。

注意事項

1、只有通過“清單”方式建立的清單表才支援sheet呈現。

7.3.3 試算表-圖形報表

功能簡介

圖形報表是指通過圖形輔助呈現資料的一種報表功能。

作用：用於資料統計、形象呈現。

適用場景：適用資料呈現的業務需求。

範例效果

試算表支援ECharts圖形和Excel的圖形兩類圖形輔助呈現資料，範例效果如下：

上圖範例中，直條圖是通過ECharts圖形實現，圓環圖是通過Excel圖形實現。

分析設定

該圖形報表的實現詳情如下：

設定內容

設定方法

效果

設定資料表格

1、儲存格B2、C2輸入文字“銷售區域”、“銷售額”；

2、從右側資料集面板中拖拉欄位“銷售區域”、“銷售額”分別到儲存格B3、C3；

3、儲存格C2設定：“不擴展”、“匯總”、“求和”的屬性，其它儲存格屬性保持系統預設；

4、設定樣式：欄頭填充色、邊框，置中顯示，字體；

設計ECharts圖形

1、選中儲存格區域“B2：C3”，按一下Synapse工具列中的圖形，彈出“插入圖表”視窗。

2、選擇ECharts作圖，進行如下設定：

3、在“ECharts作圖”分頁中選擇標題，設定標題如下：

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML1d68445.PNG$

4、設定X、Y軸防溢出，並且取消X、Y軸名稱顯示狀態。

設計Excel圖形

1、選中儲存格區域“B2：C3”，按一下Synapse工具列中的圖形，彈出“插入圖表”視窗。

2、選擇Excel作圖，選擇圓環圖即可：

3、選中Excel圖形元件，在Excel的“設計”功能表中選擇增加圖表元素>圖表標題，定義圓環圖標題。

拖拉ECharts圖形元件、Excel圖形元件，進行位置調整後，儲存即可。

7.3.4 試算表-填報報表

功能簡介

填報報表是指使用者在報表端可以直接增加、刪除資料記錄的一種報表功能，同時填報的資料亦能用於資料查詢。

作用：用於在報表端進行資料補錄或回寫。

適用場景：適用資料補錄或回寫填報的業務需求。

實現原理

填報，我們也可以說是回寫，是指使用者從瀏覽端中錄入資料，然後儲存到指定資料庫的表中。我們將儲存這些錄入資料的庫稱為回寫資料庫，將表稱為回寫表。

在定義好瀏覽端錄入表與回寫資料庫中回寫表的關聯關係（包含主鍵）後，按照如下規則進行回寫：

當該資料以主鍵為搜尋條件在回寫表中不存在時，通過“insert"語句將該資料寫入回寫表中。例如：insert into 表名（欄位1，欄位2，欄位3） values（欄位1的值、欄位2的值，欄位3的值）……Where 主鍵欄位1=值1，主鍵欄位2=值2……
當該資料以主鍵為搜尋條件在回寫表中已經存在，通過“update”語句將最新資料更新到回寫表中。例如：update 表名 set 欄位1=欄位1的值，欄位2=欄位2的值……Where主鍵欄位1=值1，主鍵欄位2=值2……

範例效果

下面以一個表單回寫填報為例演示效果：

分析設定

該報表功能的實現詳情如下：

設定內容

設定方法

效果

設定填報表格樣式

1、儲存格B2輸入“填報人：”，儲存格D2輸入“填報日期”，設定樣式：字體“粗體”，字型大小“11”，字體顏色“黑色”，置左顯示；

2、儲存格B3輸入“姓名：”，儲存格B4輸入“手機：”，儲存格B5輸入“性別：”，儲存格B6輸入“部門：”，儲存格D3輸入“年齡：”，儲存格D4輸入“郵箱：”，儲存格D5輸入“入職年限：”，儲存格D6輸入“薪資（年薪）：”，儲存格D7輸入“檔案名稱：”，設定樣式：字體“粗體”，字型大小“11”，字體顏色“黑色”，置中顯示；

3、調整列寬、欄高，選擇儲存格區域“B3：E7”設定邊框顏色為“#305496”，邊框大小為“1px”；

4、設定標題

設定填報資訊

1、在儲存格C2中輸入Synapse函數：=SSR_GetCurrentUserAlias()

2、在儲存格E2中輸入Excel函數：=TODAY()

注：函數詳情請參見試算表-計算函數章節。

填報人：

填報日期：

連接回寫資料庫、回寫表

1、通過關係資料來源連接功能，連接回寫資料庫：

設定回寫規則

回寫規則實現的是將填報報表中的欄位與回寫表中的欄位建立對應關係。

1、在Synapse工具列中按一下回寫規則，彈出“回寫規則設定”視窗，按照如下要求進行回寫規則設定：

設填報屬性

填報屬性的設定用於實現在瀏覽端報表的錄入以及控制錄入內容的規範。

儲存格C3、E7

儲存格C4、E4

手機和郵箱也是“單行文字”，但是需要設定規則運算式來規範輸入的內容：

儲存格C5、C6、E5、E6

性別、入職年限、部門、薪資（年薪）四項通過綁定參數實現在瀏覽端中選擇輸入值：

其中“性別”參數控制項是“平鋪勾選面板”類型，其它三個參數是“下拉清單”類型。

儲存格C7

儲存格E3

“儲存格填報屬性”視窗的各設定選項詳情請參見試算表-填報屬性章節。

完成以上設定儲存即可。

回寫應用

填報回寫是試算表很強大的一項功能，以上的範例只是呈現了該功能最基本的部分，除此之外填報回寫在實際業務中有很豐富的應用。

7.3.6 試算表-聯動報表

功能簡介

聯動報表是指報表中各表格的跳轉鑽入以及圖形表格間的互動效果。

作用：用於資料聯動、跳轉分析。

適用場景：適用資料聯動的業務需求。

範例效果

Synapse通過基於表格的“儲存格傳值”功能和圖形的“資料傳值”功能實現多種類型的聯動效果：

聯動類型

效果

實現說明

表格到ECharts圖形聯動

基於“儲存格傳值”功能實現

ECharts圖形到表格聯動

基於“資料傳值設定”功能實現

ECharts圖形間聯動

基於“資料傳值設定”功能實現

Excel圖形間聯動

基於“資料傳值”功能實現

表格跳轉

基於“跳轉規則”功能實現

實現方法

下面詳細介紹表格到ECharts圖形的聯動實現方法：

設定內容

設定方法

靜態表格樣式

1、登錄報表設計器，在Sheet1中設計靜態表格樣式：字體“粗體”，標題字型大小是“12”，“選擇類型”的字型大小是“10”；

設定表格

1、在Synapse中定義如下資料集：

2、在報表設計器中基於該查詢在Sheet1中設定表格：

表格樣式：

欄頭：字體“粗體”，字型大小“11”，字體顏色“白色”，填充色“#2F5597";
資料列：字體“粗體”，字型大小“10”，字體顏色“黑色”；

設定圓餅圖

1、在Synapse中定義如下資料集：

2、在報表設計器中基於該查詢在Sheet2中設定如下表格：

3、在Sheet2中基於步驟2設定的表格：選擇儲存格區域“B2：C3”，按一下Synapse工具列的圖形按鈕，彈出的視窗中選擇ECharts作圖，按照下圖進行設定：

圖例不顯示。

設定系列內容：

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML2859db5.PNG$

設定進階內容：

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML2862796.PNG$

4、圖形儲存後，將Sheet2中的圓餅圖移到Sheet1中：選中圓餅圖，在右鍵功能表中選擇移動或複製：

5、彈出“移動或複製”視窗，選擇“Sheet1”，確定即可。

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML27691c8.PNG$

設定聯動

1、返回到Sheet1，拖拉圓餅圖位置，以及選中圖形邊框的四角錨點確定圖形大小。

2、選中儲存格C5，在其右鍵功能表中選擇儲存格傳值，彈出“儲存格傳值設定”視窗，設定將表格中的“產品類別”值傳遞給“選擇類別”參數及儲存格：

3、確定儲存以上設定。

其它設定

1、隱藏參數：在Sheet1的Synapse工具列中選擇參數排版按鈕，彈出“參數面板佈局”視窗，從上方的面板中將需要隱藏的參數拖拉到最下方面板即可。

2、隱藏Sheet2。

局部更新設定

選中圓餅圖，單擊工具欄中的頁面設定，選擇局部更新即可。

完成以上所有操作後，儲存該試算表，即實現表格到圖形的聯動效果。

7.3.7 試算表-欄頭排序報表

功能簡介

欄頭排序報表是指在報表在瀏覽狀態下，支援欄位排序即按一下欄頭欄位實現排序。

注：實現欄位排序的前提：被點擊的欄頭儲存格不允許是合併儲存格。

1、支援排序功能的報表類型有：清單式報表、分組報表、分片報表。

2、分片報表，只適用於左右分片且各片間沒有資料關係的情況，則分別排序，一片不會影響其它片。

3、數值和分類欄是可以進行多欄排序的，表示目前成員相同的資料在進行排序。

4、排序情況：第一次點擊某欄頭則為按該欄位資料升冪排序，第二次點擊該欄頭則為按該欄位資料降冪排序，第三次點擊該欄頭則為不排序。

5、多欄數值時，可以按Ctrl鍵進行多欄排序，且根據選擇的順序選擇優先順序執行。

作用：用於報表瀏覽時進行排序操作。

適用場景：適用試算表排序瀏覽情況。

功能入口

點擊工具列裡的頁面設定按鈕，在彈出頁面勾選允許點擊欄頭排序。

演示效果

下面呈現的是在試算表中點擊欄頭實現排序的效果：

分析設定

各類型報表列排序的實現詳情如下：

報表類型

要求

效果

清單報表

針對清單式的清單報表，即：欄位的擴展屬性為“列表，從上到下”。

分組報表

按一下非分組欄位，實現的是組內排序。

數值和分類欄是可以進行多欄排序的，表示目前成員相同的資料在進行排序。

分片報表

只適用於左右分片且各片間沒有資料關係的情況，則分別排序，一片不會影響其它片。

注：必須通過“覆蓋“方式實現的分片報表才支援欄頭排序。

7.4 試算表-資料來源分類

試算表是支援同來源資料和跨來源資料的。以上章節中我們演示的大部分範例都是同來源資料，因此本章節不再對來源於同來源資料的試算表進行說明，只演示跨來源資料的試算表。

7.4.1 試算表-多資料集報表

報表簡介

多資料集報表基於試算表支援跨資料集查詢基礎上實現的資料查詢和呈現的一類報表。

作用：用於跨資料集查詢資料。

適用場景：適用多資料集的分片報表，跨資料集查詢的報表。

報表佈局：多資料集報表的表格佈局沒有規律，根據實際業務需求進行規劃。

範例效果

在分片報表範例中，我們列出了常用的幾種分片佈局，但是只基於同一個結果集的，本章節，我們將基於多個資料集來介紹分片報表的實現。

報表範例效果及報表說明請參見”分片報表-範例效果“內容。

資料要求

本章節的分片報表效果，基於如下兩個資料集：

項目

說明

資料集1：查詢不同區域的產品銷售情況

資料集2：查詢不同時間的產品銷售情況

分析設定

範例

分析

同表格中資料左右分片

基於兩個資料集實現該報表效果，形式上是分片報表，但是這個報表的資料是有關聯關係的，因此：該報表的實現需要通過“過濾”功能將兩個資料結果進行關聯，才能查詢出正確的資料效果。實現詳情請參見範例：分片報表-同表格中資料左右分片。

同表格中資料上下分片

實現方法同“分片報表-同表格中資料上下分片“一致，只是在欄位拖拉環節時，是分別從兩個資料集中拖拉相關欄位。

由於該報表的資料片並沒有關係，因此不需要進行資料關聯。

不同表格左右分片

實現方法同“分片報表-不同表格左右分片“一致，只是在欄位拖拉環節時，是分別從兩個資料集中拖拉相關欄位。

由於該報表的資料片並沒有關係，因此不需要進行資料關聯。

同表格中資料左右分片

“同表格中資料左右分片”範例的實現詳情如下：

設定內容

設定方法

設定表格中的固定內容：

按區域統計；

按月份統計；

1、在儲存格D3中輸入“按區域統計”；

2、在儲存格F3中輸入“按月份統計”；

3、設定樣式：字體“粗體”、字型大小“10”、字體顏色“#2F5597”，置中顯示；

設定交叉表頭

1、合併儲存格“B3：C4”；

2、選擇該合併儲存格，通過Synapse工具列的格式>斜線，設定交叉表頭：

3、在“斜線”視窗的文字方塊中輸入如下內容：

$C:\Users\Administrator\AppData\Local\Temp\SNAGHTML3409e15.PNG$

4、設定樣式：字體“粗體”、字型大小“12”、字體顏色“#2F5597”，置中顯示；

拖拉欄位

1、拖拉資料集1欄位：“產品類別”拖拉到儲存格B5；“產品名稱”拖拉到儲存格C5；“銷售區域”拖拉到儲存格D4；“銷售額”拖拉到儲存格D5；

2、拖拉資料集2欄位：“月”拖拉到儲存格F4；“銷售額”拖拉到儲存格F5；

3、設定樣式：

儲存格B5：字體“粗體”，字型大小“10”，字體顏色“#2F5597”，置中顯示；
儲存格D4、F4：字體“粗體”，字型大小“10”，字體顏色“白色”，填充色“#2F5597”，置中顯示；
儲存格C5、D5、F5：字體“粗體”，字型大小“10”，字體顏色“黑色”，置中顯示；

定義相關屬性：擴展屬性、資料設定、附屬屬性

1、儲存格B5、C5：從上到下擴展，分組顯示，父儲存格預設；

2、單元D4、F4：從左到右擴展，分組顯示，父儲存格預設；

3、儲存格D5、F5：不擴展，匯總求和顯示，父儲存格預設；

設定兩個資料集的關聯關係

1、選中儲存格F5，按一下Synapse工具過濾，彈出“儲存格屬性”視窗。

2、選擇過濾分頁，設定兩個資料集的“產品類別”和“產品名稱”分別的關聯關係。

過濾各設定選項詳情請參見試算表-過濾章節。

設定表格邊框，列高欄寬調整

1、按照範例設定邊框，邊框顏色為“#2F5597”，並調整列高欄寬；

2、設計標題；

注意事項

選擇設定過濾條件的目標儲存格的要求：1、儲存格必須綁定欄位；2、根據資料過濾的生效範圍進行選取確定。如本範例中：我們是想過濾出按月份統計的銷售額，是想對銷售額進行過濾，因此我們是選擇儲存格F5進行過濾條件設定。設定原則請參見試算表-過濾章節。

7.5 試算表-報表計算範例

7.5.1 試算表-小計/合計

範例效果

在試算表報表中增加“小計、合計“。

如下圖，以“發貨城市”為單位，計算小計；最終計算所有“發貨區域”的合計。

實現步驟

說明

1、新建試算表

1）設定試算表的靜態表格樣式如圖：

2）拖拉欄位。將欄位拖拉到相應儲存格，並設定擴展屬性。

2、設定小計

在C4儲存格中輸入公式"=sum(C3)"，如圖：

3、設定合計

在C5儲存格中輸入公式"=sum(C3)"，如圖：

4、儲存查看

預覽效果如圖：

7.5.2 試算表-占比

範例效果

本例在試算表中實現各月銷售量在全年銷售總量中所占的比重，如下圖所示：

實現步驟

說明

1、新建試算表

1）設定試算表的靜態表格樣式如圖：

2）拖拉欄位。將欄位拖拉到相應儲存格。

2、計算匯總資料

計算全年的銷售總量。在B4輸入Excel公式“=SUM(B3)”，如圖：

3、計算占比

1）在C3儲存格輸入公式“=B3/B4”，如圖：

2）在C4儲存格輸入“100%”，如圖：

3）選中C3-C4儲存格，右鍵功能表選擇設定儲存格格式，彈出“設定儲存格格式”介面，切換到“數值”分頁，分類選擇“百分比”，小數位數為“2”，如圖：

4、儲存查看

預覽效果如圖：

7.5.3 試算表-差異值

範例效果

本例在試算表中實現擴展欄位的差異值，差異值=銷售量最大值-銷售量最小值，如下圖所示：

實現步驟

說明

1、新建試算表

1）設定試算表的靜態表格樣式如圖：

2）拖拉欄位。將欄位拖拉到相應儲存格。

2、設定排序

設定月份按照銷售量的升冪順序排序。

選中A3儲存格，在工具列點擊屬性按鈕，彈出“儲存格屬性”介面，切換到“其他”分頁，同一分組內排列順序選擇“升冪”，排序儲存格選擇“B3”，如圖：

3、設定差值

差值=銷售量最大值-銷售量最小值。

在B4儲存格中，輸入公式“=INDEX(B3,COUNTA(B3))-INDEX(B3,1)”，如圖：

公式說明如下：

1）COUNTA（）：計算區域中非空白儲存格的個數。

2）INDEX（array, row_num, [column_num])：在給定的儲存格區域中，返回特定列欄交叉處儲存格的值。

array：儲存格區域或陣列常數。
row_num：陣列中某列的列序號，函數從該列返回數值。
column_num：陣列中某欄的欄序號，函數從該欄返回數值。

4、儲存查看

預覽效果如圖：

7.5.4 試算表-前期比

前期比，表示連續2個單位週期（比如連續兩月）內的量的變化比。

其計算公式為：前期比=本期值/上期值x100%。

範例效果

計算銷售量的前期比，從下圖可以看出：

2月的上期值為1月的本期值“6006”；

2月的前期比為2月的本期值/2月的上期值*100%，即5247/6006*100%。

實現步驟

說明

1、新建試算表

建立的試算表如圖：

2、設定上期值

計算銷售量的上期值。

在C3儲存格輸入公式“=SSR_GetCell(B3,A3,-1)”，如圖：

關於“SSR_GetCell”公式，詳情請參考SSR_GetCell。

3、設定前期比

1）在D3儲存格中輸入公式“=B3/C3*100%"，如圖：

2）選中D3儲存格，右鍵功能表選擇設定儲存格格式，彈出“設定儲存格格式”介面，切換到“數值”分頁，分類選擇“百分比”，小數位數為“2”，如圖：

4、儲存查看

預覽效果如圖：

7.5.5 試算表-同期值/同期比

同期值是指上一年中目前時間點的資料值。

同期比是指目前時間資料值與上一年該時間點資料值的百分比。

範例效果

從下圖可以看出：

1）2016年01月到05月的同期值和同期比為空值，這是因為沒有取到對應同期值的資料。

2）2017年對應月的同期值為2016年對應月銷售量的值。如：2017年3月的同期值是2016年銷售量的值“2167”。

3）2017年對應月的同期比為目前月銷售量與目前月同期值的比值。如：2017年3月的同期比為“3529/2167*100%=162.85%”。

實現步驟

說明

1、新建試算表

建立的試算表如下：

2、設定同期值

在D3儲存格輸入公式“=VLOOKUP(B3,SSR_GetSubCells(B3:C3,SSR_GetCell(A3,A3,-1)),2,FALSE)”，如圖：

公式說明如下：

1）VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])：搜尋表區域首欄滿足條件的元素，確定待檢索儲存格在區域中的列序號，再進一步返回選定儲存格的值。預設情況下，表是以升冪排序的。

lookup_value：在資料表第一欄查找的值。
table_array：查找的範圍。

table_array的第一欄必須是lookup_value所在的欄。

col_index_num：返回的值在查找範圍的第幾欄。如col_index_num=1，返回table_array第一欄中的數值；col_index_num=2，返回table_array第二欄中的數值，以此類推。

注：col_index_num的值小於1或者大於table_array 的欄數都會導致vlookup函數返回錯誤值。

range_lookup： TRUE——近似配對：table_array的首欄中的值必須以升冪排序。 FALSE——精確配對：VLOOKUP只能查找精確配對項。

2）SSR_GetSubCells：詳情請參考SSR_GetSubCells。

3）SSR_GetCell：詳情請參考SSR_GetCell。

3、設定同期比

1）在E3儲存格輸入公式“=C3/D3*100%”，如圖：

2）選中E3儲存格，右鍵功能表選擇設定儲存格格式，彈出“設定儲存格格式”介面，切換到“數值”分頁，分類選擇“百分比”，小數位數為“2”，如圖：

4、儲存查看

預覽效果如圖：

7.5.6 試算表-累計

累計是指在同一年度中從首個月份到目前月份資料值的和。

範例效果

從下圖可以看出：

1）2016年1月份的值是2540；2月份的值是2540+2132=4672；3月份的值是2540+2132+1770=6442；以此類推。

2）2017年1月份的值是3466，不再與2016年12月求和。

實現步驟

說明

1、新建試算表

建立的試算表如圖：

2、設定累計

在D3儲存格中輸入公式“=SSR_GetCell(D3,B3,-1)+C3”，如圖：

關於“SSR_GetCell”公式，詳情請參考SSR_GetCell。

3、儲存查看

預覽效果如圖：

7.5.7 試算表-排序

7.5.8.1 試算表-總排序

範例效果

實現銷售量資料的總排名，如圖：

實現步驟

1、新建試算表

建立的試算表如圖：

2、設定排名

在D3儲存格輸入公式“=RANK(C3,SSR_GetSubCells(C3))"，如圖：

公式說明如下：

1）RANK(number,ref,[order])：返回某數位在一欄數位中相對於其他數值的大小排名。

• number：需要求排名的儲存格。

注：求排名的儲存格的欄位必須為數值型。

• ref：排名的參照數值區域。

• order：

為“0”或省略：類比降冪儲存的清單來對數位進行排序。

為“1”：類比升冪儲存的清單來對數位進行排序。

注：“RANK"公式對重複數的排位相同，但重複數的存在將影響後續數值的排位。例如，在一欄按升冪排列的整數中，假設整數10出現兩次，其排位為5，則11的排位為7。

2）SSR_GetSubCells：關於“SSR_GetSubCells”公式，詳情請參考SSR_GetSubCells。

3、儲存查看

預覽效果如圖：

7.5.8.2 試算表-分組排序

範例效果

實現分組內的排名。從下圖可以看出：

排名為：“發貨城市”對應“銷售量”在“發貨區域”內的排名。例如，“長春”對應的銷售量為2293，在“東北”這個區域中的銷售量排名為2。

實現步驟

1、新建試算表

建立的試算表如圖：

2、設定維內排序

在D3儲存格中輸入公式“=RANK(C3,SSR_GetSubCells(C3,A3))”，如圖：

關於"RANK”公式，詳情請參考試算表-總排序。

關於“SSR_GetSubCells”公式，詳情請參考SSR_GetSubCells。

3、儲存查看

預覽效果如圖：

7.5.8.3 試算表-多欄位維內排序

範例效果

實現先根據“發貨區域”進行升冪排序，再根據“發貨城市”進行分組升冪排序。如圖：

實現步驟

1、新建試算表

建立的試算表如圖：

2、設定排名

在D3儲存格輸入公式“=RANK(C3,SSR_GetSubCells(C3))"，如圖：

公式說明如下：

1）RANK(number,ref,[order])：返回某數位在一欄數位中相對於其他數值的大小排名。

• number：需要求排名的儲存格。

注：求排名的儲存格的欄位必須為數值型。

• ref：排名的參照數值區域。

• order：

為“0”或省略：類比降冪儲存的清單來對數位進行排序。

為“1”：類比升冪儲存的清單來對數位進行排序。

2）SSR_GetSubCells：關於“SSR_GetSubCells”公式，詳情請參考SSR_GetSubCells。

3、儲存查看

預覽效果如圖：

7.5.8.2 試算表-分組排序

範例效果

實現分組內的排名。從下圖可以看出：

排名為：“發貨城市”對應“銷售量”在“發貨區域”內的排名。例如，“長春”對應的銷售量為2293，在“東北”這個區域中的銷售量排名為2。

實現步驟

1、新建試算表

建立的試算表如圖：

2、設定維內排序

在D3儲存格中輸入公式“=RANK(C3,SSR_GetSubCells(C3,A3))”，如圖：

關於"RANK”公式，詳情請參考試算表-總排序。

關於“SSR_GetSubCells”公式，詳情請參考SSR_GetSubCells。

3、儲存查看

預覽效果如圖：

7.5.8.3 試算表-多欄位維內排序

範例效果

實現先根據“發貨區域”進行升冪排序，再根據“發貨城市”進行分組升冪排序。如圖：

實現步驟

1、新建試算表

1）設定試算表的靜態表格樣式如圖：

2）拖拉欄位。將欄位拖拉到相應儲存格，並設定擴展屬性。

2、設定小計

在C4儲存格中輸入公式“=SUM(C3)”，如下圖：

3、設定父儲存格

計算每個發貨區域的小計值。

將A4、C4儲存格的左父儲存格設定為“A3”，如圖：

報表資料如圖：

4、設定排序

1）選中A3儲存格，點擊工具列的屬性按鈕，彈出“儲存格屬性”視窗，切換到“其他”分頁，同一分組內排列順序選擇“升冪”，選擇排序儲存格“C4”，如圖：

2）選中B3儲存格，點擊工具列的屬性按鈕，彈出“儲存格屬性”視窗，切換到“其他”分頁，同一分組內排列順序選擇“升冪”，選擇排序儲存格“C3”，如圖：

5、隱藏小計

選中小計所在行，右鍵功能表選擇隱藏，如圖：

6、儲存查看

預覽效果如圖：

7.5.8.4 試算表-按照指定資料排序

範例效果

實現擴展欄位按照自己編寫的順序排序顯示。

如下圖，“發貨區域”擴展欄位按照編寫的順序（華北、華南、華中、華東、東北、西北、西南）排序顯示。

實現步驟

1、新建試算表

1）設定試算表的靜態表格樣式如圖：

2）拖拉欄位。將欄位拖拉到相應儲存格。

2、設定公式

通過A3和A12的對比，取得到B12銷售量的值。

1）在B3儲存格中輸入公式“=VLOOKUP(A3,SSR_GetSubCells(A12:B12),2,0)”，如圖：

關於“VLOOKUP”公式，詳情請參考試算表-同期值/同期比。

關於“SSR_GetSubCells”公式，詳情請參考SSR_GetSubCells。

通過“華北”與“A12”的對比，取得到“華北的銷售量”，如圖：

2）在B4儲存格中輸入公式“=VLOOKUP(A4,SSR_GetSubCells(A12:B12),2,0)”，如圖：

3）以此類推，對B5、B6、B7、B8、B9儲存格同樣設定公式，如圖：

3、隱藏資料

將第11和第12行隱藏。

選中第11和12行，右鍵功能表選擇隱藏，如圖：

4、儲存查看

預覽效果如圖：

7.5.8.5 試算表-順序倒序排序

範例效果

在試算表中對序號進行倒序顯示，如圖：

實現步驟

1、新建試算表

建立的試算表如圖：

2、設定序號

在C3儲存格中輸入公式“=COUNTA(SSR_GetSubCells(A3))-SSR_GetIndex(A3)”，如圖：

公式說明如下：

1）COUNTA（）：計算區域中非白空儲存格的個數。

2）SSR_GetSubCells：詳情請參考SSR_GetSubCells。

3）SSR_GetIndex：詳情請參考SSR_GetIndex。

公式“=COUNTA(SSR_GetSubCells(A3))-SSR_GetIndex(A3)”：表示先計算A3儲存格的資料總數，然後將總數減去目前資料在表格區域中的列數。

注：如果“序號”欄放在“發貨區域”欄的左側，則需指定序號公式所在的儲存格的左父儲存格為“發貨區域”資料欄欄位所在的儲存格。

3、儲存查看

預覽效果如圖：

7.6 試算表-擴展應用範例

本章節主要介紹的是試算表在專案中的一些特殊擴展應用範例。

7.6.1 試算表-分析報告

報表簡介

分析報告是指通過試算表在一個sheet頁中呈現多個圖形和報表，輕鬆實現儀錶盤的效果。

作用：用於多角度多形式的呈現資料。

適用場景：適用大螢幕設定、彙報總結。

報表佈局：分析報告沒有特定佈局，根據實際業務需求進行規劃。

範例效果

下圖顯示了一個分析報告的範例效果：

分析設定

該分析報告的實現詳情如下：

佈局規劃

分析報告建議最好先規劃好佈局，可以在紙上或者利用其它工具進行規劃。

定義資料集

在Synapse的資料集管理介面中定義如下輸出欄位和參數的資料集：

設定標題及標題

在試算表設計器的Sheet1中進行如下設定：

1、設定標題：合併儲存格“A1：R3”，輸入文字“S公司2016年區域業績年報”，設定樣式：填充色“#2F75B5"，字體“粗體”，字型大小“14”，字體顏色“白色”，置左顯示，標題列高“26.5”；

2、設定背景：選擇儲存格“A4：R34”，設定填充色“#F2F2F2#”；

設定匯總資訊

在試算表設計器的Sheet1中進行如下設定：

1、合併儲存格“B5：I7”，輸入“=SSR_GetParamDisplayValue("產品類別")”，設定樣式：填充色“#66ABB3”，字體“粗體”，字型大小“16”，字體顏色“白色”，置中顯示；

2、合併儲存格“B8：D11”，輸入“=Sheet2!B3&"銷售總額"”，設定樣式：填充色“白色”，字體“粗體”，字型大小“14”，字體顏色“#6F6AA7”，置中顯示；

3、合併儲存格“E8：G11”，輸入“=SUM(Sheet2!F3)”，設定樣式：填充色“白色”，字體“粗體”，字型大小“36”，字體顏色“#92D050”，置中顯示；

4、合併儲存格“H8：I11”，輸入“萬元”，設定樣式：填充色“白色”，字體“粗體”，字型大小“28”，字體顏色“##92D050”，置左顯示；

設定表格

在試算表設計器的Sheet2中進行如下設定：

1、定義固定欄頭，設定樣式：填充色“#66ABB3”，字體“粗體”，字型大小“10”，字體顏色“白色”，置中顯示；

2、從右側資料集下拖拉相應的欄位到欄頭下，“銷售額”和“銷售量”設定“不擴展-匯總-求和”的屬性，設定樣式：設定填充色，字體“粗體”，字型大小“9”，置中顯示；

3、利用Excel的“複製-貼上選項-連結的圖片”功能實現將元件化的表格移動到Sheet1中：

1）選中儲存格區域“B2：G3”，在右鍵功能表中選擇複製；

2）切換到Sheet1介面，任意儲存格右鍵功能表中選擇貼上選項>連結的圖片（如下圖），然後選擇該表格元件通過四角錨點將元件大小與儲存格區域“B13：I 33”對齊。

Sheet2中的效果：

Sheet1中的效果：

設定瀑布圖/圓餅圖

在試算表設計器的Sheet2中進行如下設定：

1、選中儲存格區域“B2：G3”，按一下Synapse工具列上的圖形，開啟“插入圖表”視窗，選擇瀑布圖/圓餅圖，以“產品名稱”為分類，以“銷售額”為指標：

2、完成圖形設定後，將圖形移動到Sheet1中：

1）分別選中兩個圖形元件，在右鍵功能表中選擇移動或複製；

2）彈出“移動或複製”視窗，選擇“Sheet1”。

在試算表設計器的Sheet1中進行如下設定：

1、定義兩個圖形標題列：合併儲存格區域“K5：Q6”，輸入“銷售資料分析”，合併儲存格區域“K20：Q21”，輸入“銷售占比分析“，設定樣式：列高“10”，填充色“#2F75B5”，字體“粗體”，字型大小“11”，字體顏色“白色”，置中顯示；

2、分別拖拉瀑布圖和圓餅圖，調整位置。

設定下拉清單控制項

設定“更新”和“匯出”按鈕，操作詳情請參見控制項-下拉清單章節。

設定按鈕控制項

設定“更新”和“匯出”按鈕，操作詳情請參見控制項-按鈕章節。

其它設定

調整列高、欄寬;圖片底色為白色；隱藏Sheet2。

Previous報表設計 Next使用者管理

Last updated 5 years ago

Was this helpful?