2016年10月29日 星期六

Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)

網友問到:在 Excel 的資料清單中,如何用公式篩選符合條件者?
參考下圖左,是一個『日期、編號、評語』的清單,現在要根據一個『編號』值,篩選出符合該編號的資料內容(日期和評語),該如何處理?
Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)
【公式設計與解析】
選取儲存格B1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。
儲存格E3:
{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格EE3,貼至儲存格E3:E14。
(1) IF(編號=$F$1,ROW(編號),999)
在陣列公式中判斷若是編號範圍的儲存格內容和儲存格F1相同者,傳回其列號陣列,若不是,則傳回「999」(這只是很大的一個數)。
(2) SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))
利用 SMALL 函數依序取出列號陣列中的第1, 2, 3, ... 較小值。當公式向下複製時,其中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,第(2)式-1,0)
將列號代入 OFFSET 函數,即可查詢到對應的儲存格內容。

同理,儲存格F3:
{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(2:2))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F3,貼至儲存格F3:F14。

2016年10月26日 星期三

Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)

讀者提問:下圖是 Excel 的資料表,如果在綠色區域中的S1~S8欄位中,根據藍色區域中的 S 對照 T 來列出橙色區域中的value。
例如:第2列中的S8位在T3欄位,查表得到T3=0.8,將其填入儲存格E2。
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
儲存格E2:=IFERROR(OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0),"")
複製儲存格E2,貼至儲存格E2:L9。
(1) MATCH(E$1,$A2:$D2,0)
找出儲存格E1位於儲存格A2:D2範圍中的那個位置。
(2) OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0)
根據第(1)傳回的位置利用 OFFSET 函數傳回對應的儲存格內容。
(3) IFERROR(第(2)式,"")
若第(2)式的傳回值是錯誤訊息,則顯示空白。

若是原始資料如下安排:(儲存格A1:D1的內容與上圖不同)
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
公式調整如下:
儲存格E2:=IFERROR(OFFSET($A$1,0,MATCH(E$2,$A3:$D3,0)-1),"")
複製儲存格E2,貼至儲存格E2:L9。

若是原始資料按排如下:(儲存格A1:D1的內容做了調整)
Excel-由兩個表格中查詢對應的結果(MATCH,OFFSET,VLOOKUP)
儲存格E2:=IFERROR(VLOOKUP(OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1),
$A$12:$B$15,2,FALSE),"")
複製儲存格E2,貼至儲存格E2:L9。
(1) OFFSET($A$1,0,MATCH(E$1,$A2:$D2,0)-1)
找出儲存格E1內容所對應T1~T4中的那一個。
(2) VLOOKUP(第(1)式,$A$12:$B$15,2,FALSE)
根據第(1)式傳回的結果,查詢紅色區域中所對照的value。

2016年10月23日 星期日

Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

網友問到一個很實用的問題:在 Excel 的資料表中,有部分欄位缺漏資料,如何挑出這些缺漏的記錄,以方便後續處理?
在下圖左的資料清單中含有四個欄位,其中姓名沒有缺漏,而性別、生日、餐食等有部分缺漏,在此要以「進階篩選工具」來挑出含有空白內容的記錄。
Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)
做法很簡單,參考下圖左儲存格F1:I4的內容,請先輸入:
儲存格G2:『<=""』;儲存格H3:『<=""』;儲存格I4:『<=""』。
再進入「進階篩選」(選取[資料/排序與篩選]功能表區中的「篩選」),設定:
資料範圍:$A$1:$D$23;準則範圍:$F$1:$I$4;複製到:$F$7:$I$7。
Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)
如果你有興趣使用公式來執行這個操作,請

儲存格F2:{=OFFSET($A$1,SMALL(IF(($B$2:$B$23="")+($C$2:$C$23="")+
($D$2:$D$23=""),ROW($A$2:$A$23),999)-1,ROW(1:1)),COLUMN(A:A)-1)}
複製儲存格F2,貼至儲存格F2:I23。
公式中的『+』運算,相當於執行邏輯 OR 運算。
Excel-如何列出資料清單中任一個欄位有空白者(進階篩選)

2016年10月22日 星期六

Excel-不重覆的排列組合(公式,樞紐分析表)

在 Excel 中,在下圖中有類別和項目的清單,要如何才能產生不重覆的排列組合結果(參考下圖右)?
在下圖左中,有類別:甲、乙、丙、丁,項目:忠、孝、仁、愛,要產生其不重覆的排列組合結果,該如何處理?本篇將利用二種方法來處理。
Excel-不重覆的排列組合(公式,樞紐分析表

1. 使用公式
(1) 類別欄位
儲存格D2:=OFFSET($A$2,INT((ROW(1:1)-1)/4),0)
INT((ROW(1:1):當公式向下複製時產生「0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3」。
(2) 項目欄位
儲存格E2:=OFFSET($B$2,MOD(ROW(1:1)-1,4),0)
MOD(ROW(1:1)-1,4):當公式向下複製時產生「0,1,2,3,0,1,2,3,0,1,2,3,0,1,2,3」
複製儲存格D2:E2,貼至儲存格D2:E18。

2. 使用樞紐分析表工具
如果你不喜歡使用公式來處理,也可以透過「樞紐分析表」工具來自動產生。
你可以將類別清單和項目清單,放在相同或不同的工作表中。
Excel-不重覆的排列組合(公式,樞紐分析表 image
並選取[檔案/選項]功能,在[進階]標籤下找到「編輯自訂清單」按鈕,按一下這個按鈕以新增自訂清單。
Excel-不重覆的排列組合(公式,樞紐分析表
在自訂清單中,新增:忠、孝、仁、愛。
Excel-不重覆的排列組合(公式,樞紐分析表
回到類別清單中,建立樞紐分析表,勾選:新增此資料至資料模型。
Excel-不重覆的排列組合(公式,樞紐分析表
接著,對項目清單執行上述的動作。
Excel-不重覆的排列組合(公式,樞紐分析表
在樞紐分析表的「欄位清單」方塊中切換到「所有」標籤下,將兩個範圍都勾選,並且將「類別」插入「列」中,再將「項目」插入「列」中。(注意:項目要在類別之下)
Excel-不重覆的排列組合(公式,樞紐分析表
接著,選取列標籤中的一個儲存格(使其成為作用中欄位),按一下功能表中的「欄位設定」。然後在[欄位設定]對話框中的「版面配置與列」標籤下,勾選:以列表方式顯示項目標籤,並勾選:重複項目標籤。
Excel-不重覆的排列組合(公式,樞紐分析表
再切到「小計與篩選」標籤下,在[小計]區中勾選「無」。
Excel-不重覆的排列組合(公式,樞紐分析表
目前結果如下圖:(類別和項目目前尚未排序)。接著要執行正確排序的動作。
先在「列標籤」下拉式清單中選取「更多排序選項」:
Excel-不重覆的排列組合(公式,樞紐分析表
在[排序(類別)]對話框中,選取排序選項:遞增(類別),然後按一下「更多選項」按鈕。
Excel-不重覆的排列組合(公式,樞紐分析表
選取自訂排序順序:甲,乙,丙,列, ...。
Excel-不重覆的排列組合(公式,樞紐分析表
接著選取[項目]欄位中的一個儲存格,再選取[資料/排序],並在[排序(項目)]對話框中選取「遞增:項目」,並按一下「更多選項」按鈕。
Excel-不重覆的排列組合(公式,樞紐分析表
選取自訂排序順序:忠,孝,仁,愛。
Excel-不重覆的排列組合(公式,樞紐分析表
結果如下,即為所求。可以複製到其他位置使用了。
Excel-不重覆的排列組合(公式,樞紐分析表

【延伸練習】
你能運用上述的公式或是樞紐分析表工具,產生三個變項的所有排列組合?(如下圖)
Excel-不重覆的排列組合(公式,樞紐分析表

2016年10月20日 星期四

Excel-巢狀IF函數練習(NOT,ISBLANK)

網友問到一個 Excel 問題,雖然是不難,但是邏輯判斷工作對某些人而言,卻是會造成一些困擾,而不知如何下公式。例如:
網友原題目:
總共有A, B, C三個欄位,如果在A欄位key入『Y』或者『N』,如果是『N』的話,C欄位直接顯示『-』 ,如果是『Y』的話,C欄位會判讀B欄位有無輸入任何的符號或數值,如果有的會顯示『1』,沒有的話顯示『2』。
我將其翻譯為:
1. 若 A 是『N』,則 C 是『-』。
2. 若 A 是『Y』,則:
(1) 若 B 不是空白,則 C 是『1』。
(2) 若 B 是空白,則 C 是『2』。
如此,是否有助於你轉換為公式?
Excel-巢狀IF函數練習

【公式設計與解析】
儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(NOT(ISBLANK(B2)),1,2),"輸入錯誤"))
複製存格C2,貼至儲存格C2:C7。
公式利用 3 個 IF 函數形成巢狀結構,而其中 ISBLANK(B2) 用以判斷儲存格B2是否為空白,並傳回 TRUE/FALSE 值。透過 NOT 函數將傳回值 TRUE/FALSE 值轉換為 FALSE/TRUE 值。
不論你輸入的是『Y、y』,Excel 視為二者相同。同理,『N、n』亦是。
你也可以將公式簡化為:
儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(NOT(B2=""),1,2),"輸入錯誤"))
利用『""』(空字串),代替 ISBLANK 函數。
再簡化為:
儲存格C2:=IF(A2="N","-",IF(A2="Y",IF(B2<>"",1,2),"輸入錯誤"))
利用『<>』(不等於),來取代 NOT 函數的作用。

2016年10月16日 星期日

Excel-調整非數值的日期格式(MID)

網友問到的 Excel 問題:如下圖A欄的日期是非數值的日期,無法以數值格式設定方式來調整格式(例如:dd/mm/yyyy→mm/dd/yyyy),該如何才能調整?
下圖中的A欄格式:dd/mm/yyyy,想要調整為C欄:mm/dd/yyyy,該如何處理?
Excel-調整非數值的日期格式(MID)
【公式設計與解析】
該例中的日期格式是固定的,均為:日2碼/月2碼/年4碼,所以可以直接用 MID 函數取出想要的部分。
儲存格C2:=MID(A2,4,3)&MID(A2,1,3)&MID(A2,7,4)
(1) MID(A2,4,3):取出儲存格C2中的1至3碼。
(2) MID(A2,1,3):取出儲存格C2中的4至6碼。
(3) MID(A2,7,4):取出儲存格C2中的7至10碼。
將(1)(2)(3)式之間以『&』運算子串接即可。
相同公式亦可套用在A欄格式:mm/dd/yyyy,調整為C欄:dd/mm/yyyy。
Excel-調整非數值的日期格式(MID)

Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

先前的文章:
參考下圖,已經可以由回應結果中看到有一個欄位是『分數』欄位,不用再自行計算分數。但是,如果你「修改了正確的答案,或是修改了各題的得分」,該如何修正分數?
本篇文章要來練習如果要自行判斷得分,該如何處理?
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
1. 先下載回應結果為試算表格式。
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
2. 開啟這個 Excel 檔,並複製作答結果(儲存格C2:G9)。(參考下圖)
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
3. 在一個新的 Excel 檔案中貼上,位置安排如下圖(儲存格B5:F12)。
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
4. 在儲存格B2:G2,分別填入各題的標準答案。在儲存格C2:G2,填入各題的占分。
(可以在開放給他人填寫表單時,先行輸入一次正確答案)
5. 輸入公式:
儲存格G5:=SUMPRODUCT((B5:F5=$B$2:$F$2)*$B$3:$F$3)
複製儲存格G5,貼至儲存格G5:G12。
如果你的分數想要以比例來表示,參考下圖,公式也要調整:
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
儲存格G5:=SUMPRODUCT((B5:F5=$B$2:$F$2)*$B$3:$F$3*100)
公式中的參數『100』,乃指滿分為 100分。
最後,如果你想要「修改正確的答案,或是修改各題的得分」,只要在儲存格B2:F3中修改,得分隨即改變。
取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

2016年10月15日 星期六

Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)

每隔一段時間,就有老師會問到在實務上會遇到的問題:如何運用 Excel 來產生隨機座位表?
例如下圖中,每按一次 F9 鍵,就可以產生一個隨機座位表,該如何處理?
Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)

【公式設計與解析】
觀察I欄、J欄、K欄,除了座號和姓名之外,在I欄中多了一個亂數欄位,其儲存格內容:『=RAND()』。
假設學生有42個,選取儲存格I1:I43,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:亂數。
儲存格A4:=OFFSET($J$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)
複製儲存格A4,貼至儲存格A4:G4。複製儲存格A4:G4,再貼至下方對應的各列位置。
(1) INT(((ROW(A4)-4)/3)*7)
●式子中的參數『4』,乃因為儲存格A4是第 4 列。
●式子中的參數『7』,乃因為每一列有 7 個座位。
●式子中的參數『3』,乃因為每 3 列一組。
當公式向下複製時會產生儲存格A4=1、儲存格A7=8、儲存格A10=15、...。
(2) INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)
當公式向右複製時,COLUMN(A4)=1→COLUMN(B4)2→ ... →COLUMN(G4)=7。
當公式向右複製時,第(2)式會產生 1, 2, 3, 4, 5, 6, 7。
當公式向下複製時,第 7 列會依序產生 8, 9, 10, 11, 12, 13, 14。
當公式向下複製時,第 10 列會依序產生 15, 16, 17, 18, 19, 20, 14。
(3) SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4))
在 SMALL 函數中利用第(2)式的傳回值取得亂數陣列中的第 1, 2,3, ..., 43 個較小值。
(4) MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)),亂數,0)
利用第(3)式傳回的亂數值,透過 MATCH 函數找到位於亂數欄位(I欄)的位置(傳回列號)。
(5) OFFSET($J$1,第(4)式,0)
由第(4)傳回的列號,代入 OFFSET 函數找到對應的J欄中的儲存格內容。
同理:
儲存格A5:=OFFSET($K$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)

2016年10月13日 星期四

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

網友問到的 Excel 的問題:如下圖,有一個日期和服務的清單列表,其中是三種服務的記錄,如何根據這個服務清單(下圖左),轉換為個別三個服務的日期清單(下圖右)
Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)
【公式設計與解析】
首先,選取儲存格A1:D27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、A服務、B服務、C服務。
接著輸入公式,儲存格F2:
{=OFFSET($A$1,SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F2,貼至儲存格F2:H22。
(1) INDIRECT(F$1)
利用 INDIRECT 函數將儲存格F1的內容轉換為儲存格位址。例如:儲存格F1(「A服務」),轉換為儲存格B2:B27。(先前已定義名稱範圍)
(2) IF(INDIRECT(F$1)="V",ROW(日期),999)
在陣列公式中,判斷在儲存格範圍內的儲存格內容是否為「V」,若是,則傳回日期陣列的列號(利用 ROW 函數),若否,則傳回『999』。(這只是一個很大的數字,只要比儲存格範圍最大值大即可。)
(3) SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))
利用第(2)式所傳回的日期陣列,利用 SMALL 函數由小到大,依序取出日期對應的列號。(當公式向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。)
(4) OFFSET($A$1,第(3)式-1,0)
根據第(3)式取得的日期列號,代入 OFFSET 函數,即可找出對應的A欄內容(日期)。

或許,你的資料清單長成下圖這樣:
Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)
【公式設計與解析】
首先,選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、服務。
接著輸入公式,儲存格D2:
{=OFFSET($A$1,SMALL(IF(服務=D$1,ROW(日期),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格D2,貼至儲存格D2:F22。
公式原理同上。

Excel-查表反推欄列標題(OFFSET,MATCH)

網友問到 Excel 的問題:如下圖,如何找出每個項目最高價的公司名稱?
觀察下圖,不同項目都有三個公司的標價,其中F欄標示出最高價,要從這二個資訊反推最高價的公司,該如何處理?
Excel-查表反推欄列標題(OFFSET,MATCH)
【公式設計與解析】
這個問題的概念像是要由表格內容反推欄或列的標題。
儲存格G2:=OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)
(1) MATCH(F2,C2:E2,0)
先由儲存格F2內容透過 MATCH 函數查到儲存格F2是在儲存格C2:E2中的那個位置(傳回第幾個)。本例傳回『3』(35在32,27,35中的第3個)。
(2) OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)
將第(1)代入 OFFSET 函數找出由儲存格C1開始的第n個位置的內容。本例將3代入,得到『丙公司』。(=OFFSET(C1,0,2))
複製儲存格G2,貼至儲存格G2:G11。
如果,你想略F欄的「最高價」(參考下圖),也可以修改公式如下:
儲存格F2:=OFFSET($C$1,0,MATCH(MAX(C2:E2),C2:E2,0)-1)
複製儲存格F2,貼至儲存格F2:F11。
image

2016年10月11日 星期二

Excel-增加資料時不用修改公式(INDIRECT)

網友問到:在 Excel 的工作表中,如果已寫好了公式,如何因應可能增加資料而造成公式要跟著調整?
例如:下圖中要計算B欄清單中的數量總和,目前有16筆資料。如果增加一筆時,如何能不用修改公式,即可正確運算?
Excel-增加資料時不用修改公式(INDIRECT)
如果你使用公式:
儲存格E2:=SUM(B2:B17)
當新增一筆資料時,公式仍維持『=SUM(B2:B17)』,當然結果也不會有所調整。
因此,改用下列公式:
儲存格E5:=SUM(INDIRECT("B2:B"& COUNTA(B:B)))
COUNTA(B:B):計算B欄中有數值的儲存格個數。
INDIRECT("B2:B"& COUNTA(B:B)):利用 INDIRECT 函數將字串轉換為儲存格位址。如此,儲存格範圍即隨有資料的儲存格個數而變了。
Excel-增加資料時不用修改公式(INDIRECT)

2016年10月10日 星期一

Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)

網友想問:在 Excel 中如果有兩個固定間隔時間的清單,該如何找出兩者之間所有時間重疊者?
在下圖中:
條件A:在 9:00~13:00 中每間隔 3 分鐘的時間清單。
條件B:在 9:00~13:00 中每間隔 5 分鐘的時間清單。
想要找出條件A和條件B時間重疊者,如下圖右(D欄)。
Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)

【公式設計與解析】
1. 產生由 9:00 開始間隔 3 分鐘的時間清單
儲存格A3:=9*1/24
儲存格A4:=A3+3/(24*60)
複製儲存格A4,往下各列貼上。

2. 產生由 9:00 開始間隔 5 分鐘的時間清單
儲存格B3:=9*1/24
儲存格B4:=B3+5/(24*60)
複製儲存格B4,往下各列貼上。

3. 產生重疊時間的清單
選取儲存格A2:B83(有資料的儲存格),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:條件A、條件B。
儲存格D3:{=OFFSET($B$3,SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),
999),ROW(1:1))-3,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
(1) COUNTIF(條件A,條件B)
在陣列公式中,計算條件B中每一項在條件A清單中的數量。(如果傳回 1,表示重疊;如果傳回 0,表示沒有重疊。)
(2) IF(COUNTIF(條件A,條件B),ROW(條件B),999)
在陣列公式中,若第(1)式的傳回值為 1(表示重疊),則傳回重疊者儲存格的列號;若第(1)式的傳回值為 0(表示沒有重疊),則傳回「999」(這只是一個任意很大的數值)。
(3) SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),999),ROW(1:1))
在陣列公式中,利用 SMALL 函數利用 ROW(1:1)=1,找出傳回的列號中的最小值。若公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,即可依序找出列號中第 1, 2, 3, ... 小值的列號。
(4) OFFSET($B$3,第(3)式-3,0)
在陣列公式中,利用第 (3) 式的傳回值,代入第(4)式的 OFFSET 函數,即可找出對應的儲存格內容。

Excel-樞紐分析報表格式變化練習

最近開始鼓勵學校同事在處理各類報表時能善用 Excel 的樞紐分析工具,因此提供以下的多個練習範例。
基本表:
Excel-樞紐分析報表格式變化練習

《練習一》
試練習呈現下列表格格式,該如何設定?
Excel-樞紐分析報表格式變化練習
在各區或中置入欄位名稱:
「欄」區域:『產品』,「列」區:『機型』,「值」區域:『銷售額』。
Excel-樞紐分析報表格式變化練習
點選儲存格A3、儲存格B3、儲存格A4,可以直接修改三個名稱:
Excel-樞紐分析報表格式變化練習

《練習二》
試練習呈現下列表格格式,該如何設定?
Excel-樞紐分析報表格式變化練習
在各區或中置入欄位名稱:
「列」區:先置入『產品』,再置入『機型』,「值」區域:『銷售額』。
Excel-樞紐分析報表格式變化練習
在儲存格A3上按一下,將『列標籤』修改為『產品機型』;
在儲存格B3上按二下,在自訂名稱中修改『加總-銷售額』為『銷售總額』。
Excel-樞紐分析報表格式變化練習
修改如下:
Excel-樞紐分析報表格式變化練習
選取儲存格A4(或任一產品名稱),再按一下[分析/作用中欄位]功能表區中的「欄位設定」。
在[小計與篩選]標籤下,將小計勾選為『無』:
Excel-樞紐分析報表格式變化練習
在[版面配置與列印]標籤下,選取『以列表方式顯示標籤』,勾選『重覆項目標籤』。
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習

《練習三》
試練習呈現下列表格格式,該如何設定?
Excel-樞紐分析報表格式變化練習

《練習四》
試練習呈現下列表格格式,該如何設定?(每個業務員依各產品類別予以銷售額小計,並依每個業務員的銷售額由大到小排列)
Excel-樞紐分析報表格式變化練習
先產生如下的表格格式,在一個儲存格上按右鍵,選取[值的顯示方式/總計百分比]選項。
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習
在[總計]欄位的任一儲存格上按右鍵,選取[排序/從最大到最小排序]選項:
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習

《練習五》
試練習呈現下列表格格式,該如何設定?(分季/分月統計銷售額)
Excel-樞紐分析報表格式變化練習
在各區或中置入欄位名稱:
「列」區:先置入『日期』,再置入『店名』,「值」區域:『銷售額』(標籤改名:銷售總額)。
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習
選取任一個銷售日期,在儲存格上按一下右鍵,選取「組成群組」:
Excel-樞紐分析報表格式變化練習
分別選取「月」和「季」:
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習
最後,選取儲存格A5(季別中的一個),按一下[分析/作用中欄位]功能表區「欄位設定」。在[小計與篩選]標籤下的[小計]區中選取「自動」。
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習

《練習六》
試練習呈現下列表格格式,該如何設定?(第二季的銷售前三名各月統計表,分店依銷售額由大到小排序)
Excel-樞紐分析報表格式變化練習
延續《練習五》的結果,按一下儲存格B3(分店)的下拉式清單,再選取「值篩選/前10項」選項:
Excel-樞紐分析報表格式變化練習
調整為「最前 3 項」(藉由銷售總額):
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習
按一下儲存格A4(銷售日期)的下拉式清單,再選取「日期篩選/週期中的所有日期」選項,再選取「第二季」:
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習
按一下儲存格B3(分店)的下拉式清單,選取「更多排序選項」:
Excel-樞紐分析報表格式變化練習
選取「遞減(Z到A)方式」,再選取下拉式清單中的「銷售總額」:
Excel-樞紐分析報表格式變化練習
結果如下:
Excel-樞紐分析報表格式變化練習

《練習七》
(1) 試自行練習呈現下列表格格式:店名和產品的樞紐分析,店名和產品分別依銷售數量由大至小排列。
Excel-樞紐分析報表格式變化練習
(2) 試自行練習呈現下列表格格式:機型和月份的樞紐分析,機型和月份分別依銷售數量由大至小排列。
Excel-樞紐分析報表格式變化練習
(3) 試自行練習呈現下列表格格式:產品和店名的樞紐分析,分別顯示各季的銷售數量,且產品和分店分別依銷售數量由大至小排列。
Excel-樞紐分析報表格式變化練習

《練習八》
試練習呈現下列表格格式:
報表版面配罝/壓縮模式:
Excel-樞紐分析報表格式變化練習
報表版面配罝/大綱模式:
Excel-樞紐分析報表格式變化練習
報表版面配罝/列表模式:
Excel-樞紐分析報表格式變化練習

檢視其他文章

好康東東