贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201707 (8)

瀏覽方式: 標題列表 簡短摘要

老師們在暑假中可沒有閒著呢!

許多老師積極的參與研習、備課,為新學期做足準備。以往讓學生以 Email 方式繳交作業,又受限於 Email 的檔案附件大小問題。現在你使用 Google 表單就可以做為學生繳交作業檔案的平台了。

最近 Google 表單已開放任何使用者,都可以在表單上使用「檔案上傳」的功能了!

當你選取了新增:檔案上傳,可以指定允許上傳的檔案類型、檔案數量上限和檔案大小上限。

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

學生上傳檔案前必須先登入 Google 帳戶才能使用:

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

學生點選「新增檔案」:

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

學生可以由自己的雲端硬碟挑選檔案,也可以點選「從您的電腦中選取檔案」來上傳作業的檔案。一次可以上傳的檔案數量和檔案格式,依你在表單的設定為準。

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

按下「提交」,才完成作業繳交。

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

你可以看到學生繳交作業的檔案名稱(檔案名稱+學生姓名):

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

如果開啟表單回覆記錄的試算表,作業檔案的網址也被集合在一個儲存格中。(你不用特別去處理這些檔案的網址)

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

到雲端硬碟中,你會發現 Goolge 已自動為你將學生上傳的作業檔案放在一個資料夾中,而且檔案標題已都依照檔名+姓名來命名了。

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

注意:這個方式很方便的搜集了學生的作業,但也同樣會吃掉你的雲碟容量。如果你使用 G Suite,就沒有檔案容量的限制。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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-在進階篩選中使用公式運算

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。

繼前二篇文章:

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

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

本篇要分別以陣列公和非陣列公式來處理將項目清單中的空白予以忽略,重新列出有資料的項目。

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 函數傳回錯誤訊息者顯示為空白。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友常問的問題:在 Excel 的資料清單中,如何挑選勾選/不勾選的項目?本篇要以陣列公式和非陣列公式二種方式來處理。(在 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")」。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

國中教育會考早已結束,會考成績也已成為進入高中的重要評比項目。現在,高中已經放榜,各個學校(國中和高中)也已陸續拿到學生的會考成績。如何藉由 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))

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到 Excel 的問題:參考下圖,如何在G欄和H欄之間插入一欄時,不會改變公式中相對的儲存格位置?

下圖中,目前起始值有6個月的目內容,之後會不斷的插入一個月,報表會不斷的往右延伸。例如:在插入七月的資料後,近6月的平均自動計算2~7月的平均;而前1月數值,也自動對應到新插入的H欄內容。

Excel-插入一欄後不改變公式中相對的儲存格位置(OFFSET)

【公式設計與解析】

這個題目,很適合使用 OFFSET 函數來設計。

1. 儲存格I2:=AVERAGE(OFFSET($I2,0,-2,1,-6))

複製儲存格I2,貼至儲存格I2:I6。

OFFSET($I2,0,-2,1,-6) 公式中的參數「-2、-6」分別對應圖中的「-2、-6」。

在 OFFSET 函數中以儲存格I2起始,-2 表示往左相對 2 欄/-6 表示往左寬度 6 欄的位置。

因為插入一欄後,原公式會自動變為:AVERAGE(OFFSET($J2,0,-2,1,-6))

其中儲存格I2會自動變為儲存格J2,而其他定數的部分都不會被改變。


2. 儲存格L2:=OFFSET($L2,0,-5)

複製儲存格L2,貼至儲存格L2:L6。

OFFSET($L2,0,-5) 公式中的參數「-5」對應圖中的「-5」。

在 OFFSET 函數中以儲存格L2起始,-5 表示往左相對 5 欄的位置。

因為插入一欄後,原公式會自動變為:=OFFSET($M2,0,-5)

其中儲存格L2會自動變為儲存格M2,而其他定數的部分都不會被改變。

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到 Excel 的問題:在一個含有類別和數值的清單中(如下圖),如何列出各個類別中最後一個數值?

在下圖中共有「甲、乙、丙、丁、戊、己」共六個類別,如何找出對應類別的最後一個?

Excel-找出數列中每類別的最後一個項目(OFFSET,ROW,陣列公式)

【公式設計與解析】

選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、數值。

儲存格E2:{=OFFSET($B$1,MAX(IF((類別=D2),ROW(類別),FALSE))-1,0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter  鍵,Excel 會自動加上「{}」。

(1) IF((類別=D2),ROW(類別),FALSE)

在類別陣列中找到和儲存格D2相同者,傳回其列號(ROW函數用以顯示儲存格列號),否則傳回 FALSE。

(2) MAX(IF((類別=D2),ROW(類別),FALSE))

根據公式(1),傳回列號的最大值(MAX函數),即為各類別的最後一個。

(3) OFFSET($B$1,MAX(IF((類別=D2),ROW(類別),FALSE))-1,0)

將第(2)式傳回的列號,帶入 OFFSET 函數取得對應的儲存格位址內容。


如果你的每個類別資料不是連續排列,原先的公式仍是適用的。

Excel-找出數列中每類別的最後一個項目(OFFSET,ROW,陣列公式)

文章標籤

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼