2017年7月31日 星期一

Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)

網友想要在 Excel 中找出每一年各個月的第一個星期六是那一天,該如何處理?
參考以下的二個範例,基本上要使用 WEEKDAY 函數。

1. 每個月第1個星期六的日期
Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)
【公式設計與解析】
儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),17)
複製儲存格B3,貼至儲存格B3:M3。
先來看看 WEEKDAY 函數的傳回值:
儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),17)
複製儲存格B4,貼至儲存格B4:M4。
在 WEEKDAY 函數中使用參數『17』,表示傳回的數字1~7對應星期日~星期六。
將 8 減掉 WEEKDAY 的傳回值,即為所求。

2. 求每個月第1個星期三的日期
Excel-找出一年中每個月的第一個星期六(WEEKDAY,DATE)
【公式設計與解析】
儲存格B3=8-WEEKDAY(DATE($A3,COLUMN(A:A),1),14)
複製儲存格B3,貼至儲存格B3:M3。
儲存格B4=WEEKDAY(DATE($A3,COLUMN(A:A),1),14)
複製儲存格B4,貼至儲存格B4:M4。
如果要找每個月的第一個星期三,則只要將 WEEKDAY 的參數指定為『14』,即表示傳回的數字1~7對應星期四~星期三。將 8 減掉 WEEKDAY 的傳回值,即為所求。

根據以上兩個例子,不難發現,要求每個月第一個星期幾和參數的對應關係:
星期日:11
星期一:12
星期二:13
星期三:14
星期四:15
星期五:16
星期六:17

2017年7月29日 星期六

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

網友問到:在 Excel 中的一個資料表,想要求得每一欄中,不為 0 的第 1, 2 個並且予以加總。該如何處理?
參考下圖,每一欄都有數個連續內容為 0 的儲存格,如何求得不為 0 的第 1, 2 個並且予以加總?
Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

【公式設計與解析】
儲存格A19:
=SUM(OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1))
複製儲存格A19,貼至儲存格A19:G19。
(1) (A1:A18=0)*ROW(A1:A18)
在 SUMPRODUCT 函數中傳回符合條件 A1:A18=0 者的列號。ROW 函數可以傳回儲存格列號。
(2) MAX((A1:A18=0)*ROW(A1:A18))
利用 MAX 函數取得不為 0 者儲存格列號中的最大值。
(3) SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18)))
利用 SUMPRODUCT 函數可以使用陣列運算。
(4) OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1)
透過 OFFSET 函數,以儲存格A1為起點,位移至不為 0 的第 1 個儲存格,再取高度為 2、寬度為 1 的儲存格範圍。本例傳回儲存格A15:A16。
(5) 最後再透過 SUM 函數予以加總,即為所求。
Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

2017年7月26日 星期三

使用Google表單的檔案上傳功能讓學生繳交作業檔案

老師們在暑假中可沒有閒著呢!
許多老師積極的參與研習、備課,為新學期做足準備。以往讓學生以 Email 方式繳交作業,又受限於 Email 的檔案附件大小問題。現在你使用 Google 表單就可以做為學生繳交作業檔案的平台了。
最近 Google 表單已開放任何使用者,都可以在表單上使用「檔案上傳」的功能了!
當你選取了新增:檔案上傳,可以指定允許上傳的檔案類型、檔案數量上限和檔案大小上限。
使用Google表單的檔案上傳功能讓學生繳交作業檔案
學生上傳檔案前必須先登入 Google 帳戶才能使用:
使用Google表單的檔案上傳功能讓學生繳交作業檔案
學生點選「新增檔案」:
使用Google表單的檔案上傳功能讓學生繳交作業檔案
學生可以由自己的雲端硬碟挑選檔案,也可以點選「從您的電腦中選取檔案」來上傳作業的檔案。一次可以上傳的檔案數量和檔案格式,依你在表單的設定為準。
使用Google表單的檔案上傳功能讓學生繳交作業檔案
按下「提交」,才完成作業繳交。
使用Google表單的檔案上傳功能讓學生繳交作業檔案
你可以看到學生繳交作業的檔案名稱(檔案名稱+學生姓名):
使用Google表單的檔案上傳功能讓學生繳交作業檔案
如果開啟表單回覆記錄的試算表,作業檔案的網址也被集合在一個儲存格中。(你不用特別去處理這些檔案的網址)
使用Google表單的檔案上傳功能讓學生繳交作業檔案
到雲端硬碟中,你會發現 Goolge 已自動為你將學生上傳的作業檔案放在一個資料夾中,而且檔案標題已都依照檔名+姓名來命名了。
使用Google表單的檔案上傳功能讓學生繳交作業檔案
注意:這個方式很方便的搜集了學生的作業,但也同樣會吃掉你的雲碟容量。如果你使用 G Suite,就沒有檔案容量的限制。

Excel-在進階篩選中使用公式運算

在 Excel 中使用一般篩選功能,是常見的篩選工具,大多數都可以滿足想要的篩選結果。但是如果要一般篩選以外的篩選功能,還是得靠「進階篩選」了。
【例1】如何篩選生日月份是9月~12月的人?
在以下圖中,有一個生日的欄位。
Excel-在進階篩選中使用公式運算
如果你使用一般篩選,Excel 會自動辨識這是日期欄位,並且提供篩選的選項中即有「年、月、日」的選項。所以,你只要勾選「九月、十月、十一月、十二月」即可。
Excel-在進階篩選中使用公式運算
結果如下:
Excel-在進階篩選中使用公式運算
Excel 還有提供其他日期篩選的功能可以使用:
Excel-在進階篩選中使用公式運算
如何使用進階篩選來篩選生日月份是9月~12月的人?(參考下圖)
Excel-在進階篩選中使用公式運算
做法如下:
1. 在儲存格G2中輸入一個欄位標題(自訂,不要使用和原欄位相同的名稱)
2. 在儲存格G3中輸入公式:=MONTH(C2)>=9
該公式是想要利用 MONTH 函數找出生日的月份,利用「>=9 」條件找出9月~12月者。儲存格C2是生日欄位中的一個儲存格,其結果為 TRUE,是因為生日 2001/10/20 符合9月~12月者。
Excel-在進階篩選中使用公式運算
結果如下:
Excel-在進階篩選中使用公式運算

【例2】找出9月~12月的女生
如果使用一般篩選,先篩選生日為9月~12月者,再篩選性別為女者。這兩次篩選動作,相當於兩個條件執行邏輯 AND 運算。
Excel-在進階篩選中使用公式運算
如果使用進階篩選(做法如下圖),當兩個條件寫在同一列中,表示兩個條件執行邏輯 AND 運算。
Excel-在進階篩選中使用公式運算

【例3】找出9月~12月的女生和1月~4月的男生
參考下圖,儲存格G2:H3為條件設定,當條件寫在不同列中,表示兩個條件執行邏輯 OR運算。而同當同時要執行 AND 運算和 OR 運算時,會先執行 AND 運算,再執行 OR運算。
先輸入以下公式:
儲存格G3:=MONTH(C2)>=9
儲存格G4:=MONTH(C2)<=4
相當於執行條件:(儲存格G2 AND 儲存格H2) OR (儲存格G3 AND 儲存格 H3)
Excel-在進階篩選中使用公式運算

【例4】找出國文及格的女生和英文及格的男生
你可以如下圖的做法:
Excel-在進階篩選中使用公式運算
也可以這樣做,將所有的條件全寫在同一個儲存格:
1. 在儲存格G2中輸入一個欄位標題(自訂,不要使用和原欄位相同的名稱)
2. 輸入公式:=(B2="女")*(D2>=60)+(B2="男")*(E2>=60)
公式中的『*』運算子相當於執行邏輯 AND 運算;『+』運算子相當於執行邏輯 OR 運算。
Excel-在進階篩選中使用公式運算

2017年7月21日 星期五

Excel-列出非空白項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。
繼前二篇文章:
本篇要分別以陣列公和非陣列公式來處理將項目清單中的空白予以忽略,重新列出有資料的項目。
Excel-列出非空白項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
【公式設計與解析】
先選取儲存格A1:A18,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目。
1. 陣列公式
儲存格C2:
{=IFERROR(OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格C2,貼至儲存格C2:D18。
(1) IF(項目<>"",ROW(項目),"")
判斷項目陣列中具有空白的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))
將第(1)式傳回的列號陣列,利用 SMALL 函數由小至大取出其列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。
(4) IFERROR(第(3)式,"")
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

2. 非陣列公式
儲存格C2:
=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((項目<>"")*ROW(項目),
ROW(1:1)+COUNTIF(項目,"")))-1,0),"")
複製儲存格C2,貼至儲存格C2:D18。
(1) (項目<>"")*ROW(項目)
判斷項目陣列中具有空白的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,"")
COUNTIF(項目,""):計算在項目欄位中共有幾個空白儲存格。
將第(1)式傳回的列號陣列,利用 SMALL 函數由小至大取出其列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) SUMPRODUCT(SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,""))
在 SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。
(4) OFFSET($A$1,第(3)式-1,0)
最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。
(5) IFERROR(第(4)式,"")
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

2017年7月20日 星期四

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

網友常問的問題:在 Excel 的資料清單中,如何挑選重複/不重覆的項目?本篇要以陣列公式和非陣列公式二種方式來處理。

【列出不重覆的項目】
在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選不重複的項目?
Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。
在輔助欄位輸入公式,儲存格B2:=COUNTIF(項目,A2),複製儲存格B2,貼至儲存格B2:B23。

1. 陣列公式
儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(項目,項目)=1,
ROW(項目),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:D20。
(1) IF(COUNTIF(項目,項目)=1,ROW(項目),"")
在陣列公式中找出項目陣列重覆個數為 1 者(表示未重覆項目)的列號。
(2) SMALL(第(1)式,ROW(1:1))
將第(1)式傳回的列號陣列,利用 SMALL 函數由小至大取出其列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。
(4) IFERROR(第(3)式,"")
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

2. 非陣列公式
儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")
複製儲存格D2,貼至儲存格D2:D20。
(1) (重覆數=1)*ROW(項目)
在 SUMPRODUCT 函數中找出重覆數為 1 的列號。
(2) SMALL((重覆數=1)*ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1"))
COUNTIF(重覆數,">1"):計算在重覆數欄位中共有幾個大於 1 的儲存格。
本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。
(3) SUMPRODUCT(第(2)式)
在 SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。
(4) OFFSET($A$1,第(3)式-1,0)
最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

【列出重覆的項目】
在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選重複的項目?
Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。
在輔助欄位輸入公式,儲存格B2=COUNTIF($A$2:A2,A2),複製儲存格B2,貼至儲存格B2:B23。

1. 陣列公式
儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(重覆數=2,ROW(項目),""),
ROW(1:1))-1,0),"")}
這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:D20。
參考列出非重覆的項目的1.陣列公式,其差異為將「COUNTIF(項目,項目)=1」,更新為「重覆數=2」。

2. 非陣列公式
儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")
複製儲存格D2,貼至儲存格D2:D20。
參考列出非重覆的項目的2.非陣列公式。

Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

網友常問的問題:在 Excel 的資料清單中,如何挑選勾選/不勾選的項目?本篇要以陣列公式和非陣列公式二種方式來處理。

【列出篩選已勾選的項目】
在下圖中,A欄是資料清單,在B欄中有以『V』標示為勾選的項目,如何以公式來篩選這些『V』的項目?
Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:清單、勾選。
1. 陣列公式
儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),
ROW(1:1))-1,0),"")}
這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:D20。
(1) IF(勾選="V",ROW(清單),"")
在勾選陣列中判斷具有「V」的儲存格,傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))
將第(1)式傳回的列號陣列,利用 SMALL 函數由小至大取出其列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,SMALL(IF(勾選="V",ROW(清單),""),ROW(1:1))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

2. 非陣列公式
儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選<>"")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))-1,0),"")
複製儲存格D2,貼至儲存格D2:D20。
(1) (勾選<>"")*ROW(勾選)
在 SUMPRODUCT 函數中找出不具有「V」的列號。
(2) SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,""))
COUNTIF(勾選,""):計算在勾選欄位中共有幾個空白(非「V」儲存格)。
本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。
(3) SUMPRODUCT(SMALL((勾選<>"")*ROW(勾選),ROW(1:1)+COUNTIF(勾選,"")))
在 SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。
(4) OFFSET($A$1,第(3)式-1,0)
最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

【列出篩選未勾選的項目】
在下圖中,A欄是資料清單,在B欄中有以『V』標示為勾選的項目,如何以公式來篩選這些『V』以外的項目?
Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:清單、勾選。
1. 陣列公式
儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(勾選<>"V",ROW(清單),""),
ROW(1:1))-1,0),"")}
這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:D20。
參考列出篩選已勾選的項目的1.陣列公式,其差異為將「勾選="V"」,更新為「勾選<>"V"」。

2. 非陣列公式
儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((勾選="")*
ROW(勾選),ROW(1:1)+COUNTIF(勾選,"V")))-1,0),"")
複製儲存格D2,貼至儲存格D2:D20。
參考列出篩選已勾選的項目的2.非陣列公式,其差異為將「COUNTIF(勾選,"")」更新為「COUNTIF(勾選,"V")」。

2017年7月17日 星期一

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

國中教育會考早已結束,會考成績也已成為進入高中的重要評比項目。現在,高中已經放榜,各個學校(國中和高中)也已陸續拿到學生的會考成績。如何藉由 Excel 來處理這些成績呢?
(以下圖為範例的相關操作說明,重點是在練習 Excel 的各種功能,而不強調會考成績要如何運用。)
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

【範例一:計算總點數】
會考成績是以「A++、A+、A、B++、B+、B、C」來標示,若要換算成點數,可參考下圖左。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
根據儲存格J1:K8的表格中,有各個等級標示對應的點數,要將五個科目所對應的點數予以加總。以下提供有三種做法可以求得總點數:
1. 使用VLOOKUP函數
儲存格H2:
=VLOOKUP(C2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(D2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(E2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(F2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(G2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
2. 使用VLOOKUP函數+資料表
儲存格H2:
=VLOOKUP(C2,$J$2:$K$8,2,FALSE)+VLOOKUP(D2,$J$2:$K$8,2,FALSE)
+VLOOKUP(E2,$J$2:$K$8,2,FALSE)+VLOOKUP(F2,$J$2:$K$8,2,FALSE)
+VLOOKUP(G2,$J$2:$K$8,2,FALSE)
3. 使用陣列公式
選取儲存格J1:K8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:等級標示、點數。
儲存格H2:{=SUM((等級標示=C2:G2)*點數)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格H2,往下各列貼上,即可求得每位學生換算得的總點數。
結語:
第 1 種方式直接用定數的觀念,在 VLOOKUP 函數中求得等級標示所對應的點數,所以公式較為冗長。而第 2 種方式將定數改為儲存格中的變數,所以縮短了公式長度。第 3 種方式透過定義名稱和陣列觀念,公式顯得較為簡短,但是思考上較有難度。

【範例二:依成績排序位】
假設:要將全體學生依「總點數→國文→數學→英文→社會→自然」的成績高低來排序。
首先,必須自訂排序的順序:A++>A+>A>B++>B+>B>C。參考以下步驟:
1. 選取[檔案/選項]選項,並點選[進階]標籤,按下[編輯自訂清單]按鈕。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
2. 在[選項]對話框的「自訂清單」標籤下:
(1) 在[匯入清單來源]中選取儲存格J2:J8
(2) 按一下[匯入]按鈕,在[清單項目]方塊中會列出A++、A+、...、C。
(3) 按一下[新增]按鈕,在[自訂清單]方塊中會列出A++、A+、...、C。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
3. 進行排序。
1. 選取[常用/編輯]功能表中的「自訂排序」,開啟[排序]對話框,勾選「我的資料有標題」項目。
2. 設定第1個排序層級:欄→總點數/排序對象→值/順序→最大到最小。
3. 按一下[新增層級]按鈕,設定:欄→總點數/排序對象→值/順序→自訂清單。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
4. 在[自訂清單]對話框中選取已定義好的「A++、A+、...、C」清單。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
結果如下:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
5.仿步驟3和4,依序完成 「數學→英文→社會→自然」等排序設定。設定結果如下:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
完成的排序結果:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

【範例三:成績分佈】
以國文科為例,想要知道A++、A+、A、B++、B+、B、C的人數分佈。(如下圖)
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
本例要使用樞紐分析表,並且資料中需要一個不會重覆的值(例如:准考證號碼)
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
1. 執行插入這個資料表的樞紐分析表。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
2. 在樞紐分析表欄位設定中,設定:列→班級、性別;欄→國文;值:計數-准考證。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
初步的結果如下:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
3. 點選一個班級的儲存格,再選取「作用中欄位/欄位設定」。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
4. 在[欄位設定]對話框中的「小計與篩選」標籤下,將小計設定為:無。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
5. 在[版面配置與列印]標籤下,選取「以列表方式顯示項目標籤」選項,再勾選「重複項目標籤」項目。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
結果如下:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
例如,你想知道305班A++的二個男生是誰,只要點選二下數字2即可。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
如果你想列出所有國文A++者,只要在數字16上點選二下即可。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
結果如下:
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

【範例四:計算各班成績A的個數】
以下來練計算各班各科成績A的個數和5A的個數,其中欄I是輔助欄位。
Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)
本例建立一個輔助欄位:I欄,用以計算每個學生成績有幾個A。
儲存格I2:=SUMPRODUCT(1*(LEFT(C2:G2,1)="A"))
複製儲存格I2,往下各列貼上。
接著,定義「班級、性別、A個數」三個名稱。
1. 計算A的個數
儲存格M2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*A個數)
如果你將所有學生成績的儲存格範圍定義為:成績,也可以使用以下公式,不需藉用輔助欄位即可計算出結果。
儲存格M2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*(LEFT(成績,1)="A"))

2. 計算5A的個數
儲存格N2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*(A個數=5))

檢視其他文章

好康東東