學不完．教不停．用不盡

延續這兩篇：

Excel-動態計算最近n天且小於m筆的平均

Excel-動態計算最近N天且數值大於M的平均

如果要動態計算最近日期大於M的N筆數值平均，該如何處理？

【設計與解析】

定義名稱：DATA，參照位址：儲存格B4:B400。

儲存格D6：=AVERAGE(INDEX(DATA,LARGE(IF(DATA>$D$4,ROW(DATA),""),ROW(INDIRECT("1:"&E4)))-3))

這是陣列公式，Excel 2021 以前版本者，要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

儲存格D6：{=AVERAGE(INDEX(DATA,LARGE(IF(DATA>$D$4,ROW(DATA),""),ROW(INDIRECT("1:"&E4)))-3))}

(1) ROW(INDIRECT("1:"&E4))=ROW(1:10)

儲存格E4為指定的筆數(N)，指定1:N筆

(2) LARGE(IF(DATA>$D$4,ROW(DATA),""),第(1)式))

傳回大於M的最近N筆的列號

=INDEX(DATA,第(2)式-3)

根據列號找出對應的數值

=AVERAGE(INDEX(DATA,第(2)式-3))

計算找到的數值加以平均

當在最末位置增加一筆時，公式自動判斷並取最近日期大於500的10筆來計算平均。

【參考資料】

INDEX 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/index-函數-a5dcf0dd-996d-40a4-a822-b56b061328bd

INDIRECT 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/indirect-函數-474b3a3a-8a26-4f44-b491-92b6306fa261

LARGE 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/large-函數-3af0af19-1190-42bb-bb8b-01672ec00a64

ROW 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/row-函數-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

【設計與解析】

最近N筆的儲存格範圍：=OFFSET(B4,COUNT(A4:A400)-1,0,-1*D4,1)

將其定義命稱，命名為：DATA

參照：=OFFSET($B$4,COUNT($A$4:$A$400)-1,0,-1*$D$4,1)

其中必須使用絶對參照。

計算最近N筆且內容小於M的平均：

儲存格D6：=SUMPRODUCT((DATA>E4)*DATA)/SUMPRODUCT((DATA>E4)*1)

如果在資料最後再增加內容，則會動態取最近N筆且內容大於M的平均。

【延伸閱讀】

​ Excel-動態計算最近日期大於M的N筆數值平均

​ Excel-動態計算最近n天且小於m筆的平均

【參考資料】

​ OFFSET 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/offset-函數-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

 COUNT 函數參考微軟提供的說明網頁：

網友根據這篇：Excel-動態計算最近幾天的平均(OFFSET)

問到：如果要動態計算最近n天且小於m筆的平均該如何設計？

【設計與解析】

選取儲存格A4:A39，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

1. 最近m筆(m=10)

儲存格D6：=AVERAGE(OFFSET($B$4,COUNT(日期)-1,0,-1*D4,1))

因為日期是由遠而近(由上而下)排列，所以：

(1) COUNT(日期)

計算全部日期的數量。

(2) OFFSET($B$4,COUNT(日期)-1,0,-1*D4,1)

利用「-1*D4」，取得日期陣列的最後m筆。

2. 最近n天且小於m筆(n=30、m=10)

儲存格D26：=AVERAGE(OFFSET($B$4,COUNT(日期)-1,0,-1*MIN(D24,COUNTIF(日期,">="&(TODAY()-E24))),1))

利用和1.相同的原理。

MIN(D24,COUNTIF(日期,">="&(TODAY()-E24))，其中：

最近m天：=儲存格D24

小於n筆：=COUNTIF(日期,">="&(TODAY()-E24)

取儲存格D24和COUNTIF(日期,">="&(TODAY()-E24)的最小值。

【延伸閱讀】

​ Excel-動態計算最近日期大於M的N筆數值平均

​ Excel-動態計算最近N天且數值大於M的平均

【參考資料】

​ OFFSET 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/offset-函數-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

​ COUNTIF 函數參考微軟提供的說明網頁：

ttps://support.microsoft.com/zh-tw/office/countif-函數-e0de10c6-f885-4e71-abb4-1f464816df34

在 Excel 裡如果要根據月份來判斷季別的作法有好幾種，本篇要來比較各種作法，也做為了解各個函數的差異和特性。

【IF函數】

儲存格B4：=IF(A4<4,"春",IF(A4<7,"夏",IF(A4<10,"秋","冬")))

月份：0，結果：春(錯誤)；月份：13，結果：冬(錯誤)。

【IFS函數】

儲存格B7：=IFS(A7<4,"春",A7<7,"夏",A7<10,"秋",A7<13,"冬")

月份：0，結果：春(錯誤)；月份：13，結果：#N/A(錯誤誤息)。

【SWITCH函數】

儲存格B10：=SWITCH(A10,1,"春",2,"春",3,"春",4,"夏",5,"夏",6,"夏",7,"秋",8,"秋",9,"秋",10,"冬",11,"冬",12,"冬","無此月")

月份：0，結果：無此月(正確)；月份：13，結果：無此月(正確)。

在 SWITCH 函數中可以指定沒有對應值時，要顯示的結果。

【CHOOSE函數】

儲存格B13=CHOOSE(A13,"春","春","春","夏","夏","夏","秋","秋","秋","冬","冬","冬")

月份：0，結果：#VALUE(錯誤訊息)；月份：13，結果：#VALUE(錯誤訊息)。

【VLOOKUP函數】

儲存格B16：=VLOOKUP(A16,{0,"春";3,"夏";6,"秋";9,"冬"},2,TRUE)

月份：0，結果：春(錯誤)；月份：13，結果：冬(錯誤)。

在 VLOOKUP 函數用到陣列的表示，由「｛｝」組成，注意其中：「，」用以分隔資料欄、「；」用以分隔資料列。

【參考資料】

IF 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/if-函數-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

IFS 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/ifs-函數-36329a26-37b2-467c-972b-4a39bd951d45

SWITCH 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/switch-函數-47ab33c0-28ce-4530-8a45-d532ec4aa25e

CHOOSE 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/choose-函數-fc5c184f-cb62-4ec7-a46e-38653b98f5bc

VLOOKUP 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/vlookup-函數-0bbc8083-26fe-4963-8ab8-93a18ad188a1

網友根據這篇：Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

問到，如果篩選結果要循欄顯示，該如何處理？

【設計與解析】

選取儲存格B3:D18，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：戶號、姓名、資料。

1. 使用 Excel 2021 公式

儲存格F7：=INDEX(FILTER(資料,戶號=$F$4),COLUMN(A:A))

複製儲存格F7，貼至儲存格F7:J7。

FILTER(資料,戶號=$F$4)公式產生如下陣列：

再透過 INDEX 函數和 COLUMN 函數在公式向右複製時，分欄取出對應的儲存格內容。

2. 使用傳統陣列公式

儲存格F7：{=IFERROR(OFFSET($D$3,SMALL(IF($B$4:$B$18=$F$4,ROW($B$4:$B$18),""),COLUMN(A:A))-3,0),"")}

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

複製儲存格F7，貼至儲存格F7:J7。

詳細說明參考：Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(1) IF($B$4:$B$18=$F$4,ROW($B$4:$B$18),"")

在陣列公式中判斷儲存格F4的內容是否和儲存格B4:B18中的每一個儲存格的內容相符，若是，則傳回該儲存格的列號；否則傳回空字串。在陣列公式中，ROW($B$4:$B$18)＝{4,5,6, ... , 16,17,18}。　

(2) SMALL(第(1)式,COLUMN(A:A))

利用 SMALL 函數找出第(1)式傳回的列號之最小值。其中，ROW(1:1)向右複製時，COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。可以分別取出第 1,2,3,… 較小值。

(3) OFFSET($D$3,第(2)式-3,0)

將第(2)式依序取出的列號，置入 OFFSET 函數中，得到對應的儲存格內容。

「-3」是因為資料來源中的第一筆資料是由第4列開始。

(4) IFERROR(第(3)式,"")

因為第(3)式當公式向下複製時，有可能傳回錯誤訊，所以利用 IFERROR 函數將錯誤訊息以空字串顯示。

【參考資料】

FILTER 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

OFFSET 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/offset-函數-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

在 Excel 裡有一個成績清單，可能還有性別、組別等資訊，如何將成績依性別或組別來排多？

【成績依性別排名】

選取儲存格C3:D28，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、成績。

儲存格F4：=COUNT(FILTER(成績,(性別=C4)*(成績>=D4)))

複製儲存格F4，貼至儲存格F4:28。

在 FILTER 函數中，利用雙條件「(性別=C4)*(成績>=D4)」來篩選。

再以 COUNT 函數計算個數，該個數即為名次。

共分為「男、女」分別排序。

因為 FILTER 函數要 Excel 2021 版以上才有，所以提供另一種作法：

儲存格F4：=COUNTIFS(性別,C4,成績,">="&D4)

【成績依組別排名】

選取儲存格C3:D28，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：組別、成績。

儲存格F4：=COUNT(FILTER(成績,(組別=C4)*(成績>=D4)))

複製儲存格F4，貼至儲存格F4:28。

在 FILTER 函數中，利用雙條件「(組別=C4)*(成績>=D4)」來篩選。

再以 COUNT 函數計算個數，該個數即為名次。

共分為「第1組、第2組、第3組、第4組、第5組」分別排序。

因為 FILTER 函數要 Excel 2021 版以上才有，所以提供另一種作法：

儲存格F4：=COUNTIFS(組別,C4,成績,">="&D4)

FILTER 函數參考微軟提供的說明網頁：

COUNT 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/count-函數-a59cd7fc-b623-4d93-87a4-d23bf411294c

由於 Excel 提供了新的函數與陣列處理的新方式，所以本篇要利用 FILTER 函數來幫助教師計算成績。

【列出二次考試成績進步的名單】

選取儲存格A4:D24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：座號、姓名、第1次、第2次。

(1) 列出座號

儲存格F5：=FILTER(座號,第2次>第1次)

利用 FILTER  函數列出符合條件「第2次>第1次」者。

只要在儲存格F5中輸入公式，其餘儲存格會自動溢出公式。

(2) 列出姓名

儲存格G5：=FILTER(姓名,第2次>第1次)

(3) 列出第1次成績

儲存格H5：=FILTER(第1次,第2次>第1次)

(4) 列出第2次成績

座號  儲存格I5：=FILTER(第2次,第2次>第1次)

【列出指定次數最佳成績的平均】

儲存格M4：=AVERAGE(FILTER(C4:L4,C4:L4>SMALL(C4:L4,3)))

複製儲存格M4，貼至儲存格M4:M23。

(1) SMALL(C4:L4,3)

利用 SMALL 函數找出座號1同學由小至大排第3的成績。

(2) FILTER(C4:L4,C4:L4>SMALL(C4:L4,3))

在 FILTER 函數中以篩選符合條件者：C4:L4>SMALL(C4:L4,3)

篩選大於由小至大排第3者，即由大至少的前7名。

(3) AVERAGE(FILTER(C4:L4,C4:L4>SMALL(C4:L4,3)))

最後以 AVERAGE 函數計算這7個成績的平均。

【參考資料】

如下圖，如何根據起日和迄日，列出姓名清單？以下提供二種方式供參考。

1. 使用 Excel 2021 版的 FILTER 函數

選取儲存格A4:C25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、上班起、上班迄。

儲存格D4：=FILTER(姓名,(D$3>=上班起)*(D$3<=上班迄))

複製儲存格D4，貼至儲存格D4:L4。

利用雙條件執行邏輯 AND 運算，再取出符合條件的姓名：

條件一：D$3>=上班起

條件二：D$3<=上班迄

2. 使用傳統陣列公式

儲存格D4：

{=IFERROR(INDEX(姓名,SMALL(IF((D$3>=上班起)*(D$3<=上班迄),ROW(姓名),""),ROW(1:1))-3),"")}

複製儲存格D4，貼至儲存格D4:KL25。

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

(1) IF((D$3>=上班起)*(D$3<=上班迄),ROW(姓名),"")

利用雙條件執行邏輯 AND 運算，

條件一：D$3>=上班起

條件二：D$3<=上班迄

將符合者傳回姓名欄位的列號，否則傳回空字串。

(2) SMALL(第(1)式,,ROW(1:1))

利用 SMALL 函數於第(1)式的傳回值依序由小至大取出對應的列號。

ROW(1:1)=1，公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) INDEX(姓名,第(2)式-3)

將第(2)式的傳回值代入 INDEX 函數取得姓名欄位中對應的儲存格內容。

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數將第(3)傳回的錯誤訊息以空字串顯示。

【參考資料】

FILTER 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

INDEX 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/index-函數-a5dcf0dd-996d-40a4-a822-b56b061328bd

OFFSET 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/offset-函數-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

​ SMALL 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/small-函數-17da8222-7c82-42b2-961b-14c45384df07

ROW 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/row-函數-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

如果想要依指定起、訖的月份來顯示統計圖表，該如何處理？

在下圖中，指定起始月份和終止月份，圖表只會顯示該區間的統計資料。

【設計與解析】

選取儲存格A3:A29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

1. 依指定起迄月份找出資料的起始列和終止列

起始列：=MIN(IF(MONTH(日期)>=E3,ROW(日期),""))

終止列：=MAX(IF(MONTH(日期)<=E4,ROW(日期),""))

利用 MIN 和 MAX 函數找出日期的月份大於起始月份的最值和小於終止月份的最大值。

以起始月：4、終止月：8 為例，日期區間在第 10 列和第 20 列之間。

觀察新增的圖表在公式列裡的內容，你可以發現其由日期和數值兩個欄位的內容組成。

既然要做成動態的圖表，就必須以公式來取代這兩個固定儲存格範圍。

2. 以 OFFSET 函數定義儲存格範圍的名稱

以 OFFSET 函數來取代統計圖表要使用的資料區間，並且將其設定為「名稱」。

show1：=OFFSET(DATA!$A$3,DATA!$M$3-3,0,DATA!$M$4-DATA!$M$3+1,1)

用以取代原儲存格範圍A4:A29。

show2：=OFFSET(DATA!$B$3,DATA!$M$3-3,0,DATA!$M$4-DATA!$M$3+1,1)

用以取代原儲存格範圍B4:B29。

公式受到儲存格E5(起始列)和儲存格E6(終止列)的控制，所以可以動態顯示圖表。

因為要用在名稱的定義上，所以公式中都採用「絶對位址」。(其中「DATA」是這個工作表的名稱)

3. 置換統計圖上的公式

原公式：

=SERIES(工作表2!$B$3,工作表2!$A$4:$A$29,工作表2!$B$4:$B$29,1)

置換為：

=SERIES(DATA!$B$3,圖表.xlsx!show1,圖表.xlsx!show2,1)

其中「圖表」是這個檔案的名稱，圖表.xlsx!show1表示「圖表」檔案中的show1儲存格範圍。

現在，只要設定起始月份和終止月份，圖表即只會顯示該月份區間的統計資料。

【參考資料】

 OFFSET 函數參考微軟提供的說明網頁：

依據這二篇的做法：

Excel-依類別成績代碼轉換為分數(SUMPRODUCT)

Google Classroom-使用評分量表對學生作業評分

如果要讓學依評分量表實施分組互評該如何設計？

假設在 Google Classroom 中以新增表單方式來讓學生填答互評分數。

假設依下表做為評量規準(範例)：

1. 設計互評填答表單

(1) 以下拉式選單讓學生選擇組別。

2. 以「單選方格」設計評分填答

完成的填答表單如下：

學生重覆以依填入各組的評分：

最後會搜集到如下的表單：

接著，讓 Excel 協助分數的計算：

1. 將 Google 試算表複製而來的填答結果貼在資料區中。

2. 在評量分數對照表中填入你定義的分數。

3. 填入分數計算公式。

儲存格F3：=SUMPRODUCT((C3:E3=$H$3:$H$8)*$I$3:$K$8)

複製儲存格F3，貼至儲存格F3:F22。

4. 計算小組分數

選取儲存格B3:F22，這是資料範圍，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：組別、分數。

(1) 計算總分

儲存格I12：=SUMPRODUCT((組別=H12)*分數)

複製儲存格I12，貼至儲存格I12:I18。

(2) 計算平均

儲存格J12：=SUMPRODUCT((組別=H12)*分數)/SUMPRODUCT((組別=H12)*1)

複製儲存格J12，貼至儲存格J12:J18。

【延伸應用】

如果你有興趣以連結大數據方式來處理Google表單，可以參考以下的做法。

1. 將學生填答結果的 Google 試算表的共用設定為「知道連結的使用者」。

2. 複製這個連結網址。

3. 修改這個連結的內容，並複製修改後的連結。

原始：XXXX/edit?usp=sharing
改為：XXXX/export?format=xlsx

4. 在 Excel 的「資料／取得及轉換資料」功能表中選取「從Web」。

5. 在 URL 方塊中貼上步驟3取得的修改後的連結。

6. 選取最後一個層級。

7. 選取：表單回應1，並點選：載入。

目前結果如下：

只要有新的作答結果，只要按一下「全部重新整理」，即可代入新的資料。

仿照前面的說明，稍微修改公式：

儲存格G2：=SUMPRODUCT((D2:F2=$I$3:$I$8)*$J$3:$L$8)

複製儲存格G2，貼至儲存格G2:G21。

儲存格I12：=SUMPRODUCT((表單回應_1[組別]=I12)*表單回應_1[成績])

複製儲存格J12，貼至儲存格J12:J18。

儲存格J12：=SUMPRODUCT((表單回應_1[組別]=I12)*表單回應_1[成績])/SUMPRODUCT((表單回應_1[組別]=I12)*1)

複製儲存格K12，貼至儲存格K12:K18。

從此，只要重新整理資料，即可自動重算。

【延伸閱讀】

Excel-依類別成績代碼轉換為分數(SUMPRODUCT)

Google Classroom-使用評分量表對學生作業評分

Google表單設計測驗的單選題、複選題、多選題

Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數

Excel-多選題計分

Excel-設計Google表單測驗多選題並以Excel計算分數

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

用Google表單來設計線上測驗卷並且評分，還可以獲得測驗結果的統計分析

Google Classroom 的問題和測驗作業功能

實施遠距教學多元評量讓同儕互評(Google表單+Excel)

當你在 Excel 裡，要製作下拉式清單，如果想要已列出的項目，就不要再列出該選項，應該如何處理？

【設計與解析】

先從以下的方式著手觀察：

選取儲存格A4:C26，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：座號、姓名。

1. 列出已填名單

座號／儲存格E5：=FILTER(座號,內容<>"")

姓名／儲存格E5：=FILTER(姓名,內容<>"")

２. 列出未填名單

座號／儲存格E5：=FILTER(座號,內容="")

姓名／儲存格E5：=FILTER(姓名,內容="")

有了以上的經驗，現在來著手實作下拉式選單只顯示未被選的項目。

由下圖中先定義儲存格名稱，人員、資料、狀態。

(1) 設定狀態，已列：1、未列：0。

儲存格D5：=COUNTIF(人員,C5)

複製儲存格D5，貼至儲存格D5:D26。

(2) 未填名單

儲存格E5：=FILTER(資料,狀態<>1)

(3) 定義未填儲存格範圍

=OFFSET(E5,0,0,COUNTA(未填),1)

利用 COUNTA 函數計算在未填欄位中的個數。

由 OFFSET 函數產生對應的儲存格範圍。

(4) 選取儲存格A5:A26，設定資料驗證。

在資料驗證中設定準則：

儲存格內允許：清單

來源：=OFFSET($E$5,0,0,COUNTA(未填),1)

在下拉式清單中選取一個選項，下一個儲存格就會少了這個選項。

換一種做法來產生未填名單：

1. 首先，在原有資料之下，複製一份下拉式清單區。

儲存格C27：=A5，複製儲存格C27，貼至儲存格C27:C48。

2. 將「資料」名稱重新定義範圍C5:C48。

3. 利用公式：

儲存格D5：=UNIQUE(資料,,TRUE)

其中第3個參數：TRUE，可以指定篩選只出現一次者。

【參考資料】

UNIQUE 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/unique-函數-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

FILTER 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

【延伸學習】

 Excel-在下拉式選單中選取日期

 Excel-製作隨輸入字元列出可選項目的下拉式清單

 Excel-下拉式清單選取月份列出該月日期

 Excel-設計二層的下拉式選單

 Excel-使用多層下拉式清單結構輸入資料(基礎)

 Excel-使用多層下拉式清單結構輸入資料(進階)

 Excel-選取月份／日期／時間(多層下拉式清單)

 Excel-下拉式選單顯示未選項目

在 Excel 2021 以上版本加入了 UNIQUE 函數，對於資料篩選「唯一值」的處理很有幫助。

在 Excel 裡，以往「篩選唯一值」都要透過複雜的公式，而且要使用陣列公式才能完成。如果想要使用工具來處理，當然也可以利用將儲存格範圍轉變成表格，再「移除重覆項」。由於使用工具的方式，無法因為原始資料改變而隨之改變篩選結果。

現在，有了 UNIQUE 函數，以前複雜的公式加上陣列公式運算的情形已大為改善了。對於學習 Excel 來處理工作的人，實有如魚得水、如虎添翼的好工具。

UNIQUE 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/unique-%E5%87%BD%E6%95%B8-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

1. 對店名取唯一值

選取儲存格A3:E27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、店名、產品、代碼、銷售額。

儲存格G4：=UNIQUE(店名)

只要在儲存格G4輸入公式，按下 Enter 鍵後，公式會自動溢出至其他儲存格陣列範圍。

公式裡，共有三個參數：

第2個參數，TRUE：比較欄的唯一值；FALSE：比較列的唯一值。

第3個參數，TRUE：傳回只出現一次者；FALSE：傳回唯一值。

2. 依店名計算銷售總額

先篩選不重覆店名

儲存格G4：=UNIQUE(店名)

在儲存格G4輸入公式，按下 Enter 鍵後，公式會自動溢出至其他儲存格陣列範圍。

接著依店名計算銷售總額

儲存格I4：=SUMPRODUCT((店名=G4)*銷售額)

複製儲存格I4，貼至儲存格I4:I10。

在 SUMPRODUCT 函數中依條件「店名=G4」傳回的 TRUE／FALSE 陣列，再乘以銷售額，可得銷售總額。

3. 排序不重覆代碼並計算銷售總額

先對篩選不重覆代碼並且加以排序

儲存格H4：=SORT(UNIQUE(代碼))

利用 UNIQUE 函數取不重覆值，再用 SORT 函數加以排序。

在儲存格H4輸入公式，按下 Enter 鍵後，公式會自動溢出至其他儲存格陣列範圍。

儲存格I4：=SUMPRODUCT((代碼=H4)*銷售額)

複製儲存格I4，貼至儲存格I4:I15。

在 SUMPRODUCT 函數中依條件「代碼=H4」傳回的 TRUE／FALSE 陣列，再乘以銷售額，可得銷售總額。

4. 取店名和產品組合的唯一值

儲存格H4：=UNIQUE(店名&產品)

以「&」將取店名和產品串接，再以 UNIQUE 函數取不重覆值。

5. 取店名和產品組合的唯一值並且排序再計算銷售總額

先篩選不重覆的店名和產品組合，再進行排序。

儲存格H4：=SORT(UNIQUE(店名&產品))

在儲存格H4輸入公式，按下 Enter 鍵後，公式會自動溢出至其他儲存格陣列範圍。

利用 UNIQUE 函數取不重覆值，再用 SOQT 函數加以排序。

儲存格I4：=SUMPRODUCT((店名&產品=H5)*銷售額)

複製儲存格I4，貼至儲存格I4:I19。

在 SUMPRODUCT 函數中依條件「店名&產品=H5」傳回的 TRUE／FALSE 陣列，再乘以銷售額，可得銷售總額。

6. 篩選只出現一次者

儲存格G3：=SORT(UNIQUE(店名&產品,,TRUE))

藉由控制第三個參數，TRUE：傳回只出現一次者；FALSE：傳回唯一值。

利用 TRUE／FALSE，可以區隔雖然有出現但是只出現一次者，或是出現一次以上者。

【延伸閱讀】

 Excel-2021版新增函數的使用

 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)

 Excel-列出不重覆的姓氏並依筆劃由小至大排列

 Excel-下拉式選單顯示未選項目

在 Excel 2021 以上版本加入了 SORT 和 SORTBY 函數，對於資料排序的處理，幫助很大。

在 Excel 裡，以往「排序」可以使用自訂排序工具來處理，由於使用工具的方式，無法因為原始資料改變而隨之改變篩選結果。因此，使用者也會想要使用公式運算來處理。

現在，有了 SORT 和 SORTBY 函數，以前複雜的公式加上陣列公式運算的情形已大為改善了。對於學習 Excel 來處理工作的人，實有如魚得水、如虎添翼的好工具。

SORT 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/sort-%E5%87%BD%E6%95%B8-22f63bd0-ccc8-492f-953d-c20e8e44b86c

SORTBY 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/sortby-%E5%87%BD%E6%95%B8-cd2d7a62-1b93-435c-b561-d6a35134f28f

1. 單一欄位排序(依店名遞增排序)

選取儲存格A3:E27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、店名、產品、代碼、銷售額。

儲存格H4：=SORT(店名)

此公式會對「店名」欄位排序，只要在第一個儲存格輸入公式，公式會自動溢出至其他儲存格。公式結果是以預設的遞增順序來排序。

2. 單一欄位排序(依店名遞減排序)

儲存格H4：=SORT(店名,,-1)

第3個參數指定排序的方向：1為遞增排序，-1為遞減排序。

在此，中文字遞增排序：筆劃由少至多；中文字遞減排序：筆劃由多至少。

3. 對所有欄位排序(依店名遞減排序)

儲存格G4：=SORT(A4:E27,2,-1)

公式是指以第2欄(店名)作為排序依據，參數-1指遞減排序。

在此，中文字遞增排序：筆劃由少至多；中文字遞減排序：筆劃由多至少。

此公式，相當於手動自訂排序如下：

4. 對所有欄位排序(依產品遞減排序、依代碼遞增排序)

儲存格G4：=SORTBY(A4:E27,產品,-1,代碼,1)

只要在第一個儲存格輸入公式，公式會自動溢出至其他儲存格。公式結果是以預設的遞增順序來排序。

公式會以先以「產品」欄位遞減排序，若相同者，再以「代碼」欄位遞增排序。

此公式，相當於手動自訂排序如下：

手動排序的操作和以 SORT 函數、SORTBY 函數來操作，其差別在於：

(1) 原始資料變動，SORT 函數、SORTBY 函數結果會隨之變動。

(2) 原始資料變動，手動排序必須重新操作才能隨之變動。

【延伸學習】

  Excel-2021版新增函數的使用

  Excel-利用SORT和SORTBY函數進行排序

  Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

  Excel-亂數重排座位

在 Excel 2021 以上版本加入了 FILTER 函數，對於資料篩選的處理幫助很大。

在 Excel 裡，以往「篩選」可以使用一般篩選和進階選等工具來處理，由於使用工具的方式，無法因為原始資料改變而隨之改變篩選結果。因此，使用者也會想要使用公式運算來處理。

現在，有了 FILTER 函數，以前複雜的公式加上陣列公式運算的情形已大為改善了。對於學習 Excel 來處理工作的人，實有如魚得水、如虎添翼的好工具。

FILTER 函數可以參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/filter-函數-f4f7cb66-82eb-4767-8f7c-4877ad80c759

1. 以單一條件篩選(指定篩選欄位)

(1) 定義儲存格範圍的名稱

選取儲存格A3:E27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、店名、產品、代碼、銷售額。

(2) 篩選店名

儲存格G4：=FILTER(店名,產品="果汁機")

只要在儲存格G4輸入公式，按下 Enter 鍵後，公式會自動溢出至其他儲存格陣列範圍。

(3) 篩選代碼

儲存格H4：=FILTER(代碼,產品="果汁機")

(4) 篩選銷售額

儲存格I4：=FILTER(銷售額,產品="果汁機")

2. 以單一條件篩選(未指定篩選欄位)

儲存格G4：=FILTER(A4:E27,產品="果汁機")

因為沒有指定顯示的欄位，所以只要在第一個儲存格輸入公式，公式會自動溢出至其他儲存格。

3. 以多個條件 AND 邏輯運算篩選(指定篩選欄位)

(1) 篩選店名

儲存格G4：=FILTER(店名,(產品="果汁機")*(代碼="FM15"))

公式中，兩個條件中的「*」，相當於執行邏輯 AND 運算。如果想要更多的條件，只要依此方式，以括弧含括條件，再以「*」連結運算即可。

(2) 篩選代碼

儲存格H4：=FILTER(代碼,(產品="果汁機")*(代碼="FM15"))

(3) 篩選銷售額

儲存格I4：=FILTER(銷售額,(產品="果汁機")*(代碼="FM15"))

4. 以多個條件 OR 邏輯運算篩選(指定篩選欄位)

(1) 篩選店名

儲存格G4：=FILTER(店名,(產品="果汁機")+(代碼="FM15"))

公式中，兩個條件中的「+」，相當於執行邏輯 OR 運算。如果想要更多的條件，只要依此方式，以括弧含括條件，再以「+」連結運算即可。

(2) 篩選代碼

儲存格H4：=FILTER(代碼,(產品="果汁機")+(代碼="FM15"))

(3) 篩選銷售額

儲存格I4：=FILTER(銷售額,(產品="果汁機")+(代碼="FM15"))

5. 以多個條件 AND邏輯 和 OR 邏輯運算篩選(指定篩選欄位)

(1) 篩選店名

儲存格G4：=FILTER(店名,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

公式中，如果同時有「*」和「+」，因為邏輯 AND 運算優先於邏輯 OR 運算。所以會先執行「*」運算，再執行「+」運算。

(2) 篩選代碼

儲存格H4：=FILTER(代碼,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

(3) 篩選銷售額

儲存格I4：=FILTER(銷售額,(產品="果汁機")*(銷售額>5000)+(代碼="FM15"))

6. 手動進階篩選

如果你是以手動的進階篩選來執行，結果如下圖：

手動進階篩選的操作和以FILTER函數來操作，其差別在於：

(1) 原始資料變動，FILTER函數結果會隨之變動。

(2) 原始資料變動，手動進階篩選必須重新操作才能隨之變動。

當你想要執行含有 AND 邏輯和 OR 邏輯運算時，在進階篩選裡必須將 AND 邏輯運算者放在同一列，要將 OR 邏輯運算者放在不同列。

【延伸閱讀：FILTER函數篩選應用】

Excel-以FILTER函數進行模糊篩選

Excel-下拉式清單選取月份列出該月日期

 Excel-根據單條件和雙條件篩選資料(FILTER)

 Excel-列出指定星期幾的日期

 Excel-單一欄位篩選與跨欄位篩選

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-由資料清單中篩選一組

 Excel-利用FILTER函數模糊篩選

 Excel-從日期清單中區別平日和假日計算總和

 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

【延伸閱讀：手動進階篩選應用】

 Excel-進階篩選

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

 Excel-進階篩選解析(AND,OR運算)

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

 Excel-研習練習範例(篩選)

 Excel-資料篩選-6

 Excel-使用進階篩選功能來移除重覆的資料

在生活中或工作上，電腦和行動裝置都是使用者重要的伙伴，所以現在人們常會需要將手機、平板投影到電腦上，而有些工具也能反向將電腦投影到行動裝置上。

在學校裡，教師也常需要利用一些行動裝置上的 App 來教學或示範，而將手機內容投影到電腦，也是愈來愈頻繁。尤其最近遠距教學時，也有許多老師會問到類似的問題，螢幕共享軟體也是必備的App。

除了先前這篇所提的方式：Windows 10-手機畫面投影到電腦上

透過手機投影至電腦，例如：手機可以用來當作實物投影機。

而將電腦桌面投影至手機，例如：可以利用手機來切換正在電腦播放的簡報投影片。

我在電腦裡也有使用，例如：LetsView：https://letsview.com/

首先，在電腦和手機中裝好軟體，並且在讓電腦和手機連在相同的WiFi，並且兩者都將軟體打開。

在電腦中會顯示本機名稱和 WiFi 的 SSID。

在手機中會顯示 WiFi 和 IP 地址，並且會在設備連結中顯示所連結的電腦。

在手機點選電腦設備(本例：LetsView[Administrator])，會顯示：

(1) 手機螢幕投影

(2) 電腦桌面投影

1. 手機投影至電腦

請點選：手機螢幕投影，再點選開關聲音。電腦上即可看到手機上的完整投影。

此時，如果手機上在播放 YouTube 的影片，在電腦也可以顯示相同內容，並且發出聲音。

如果點選手機上的 App，再點選：斷開投影，即可結束投影。

2. 電腦投影至手機

請點選：電腦桌面投影。顯示：請在電腦授權手機獲取電螢幕。

按下「允許」：

電腦桌面即可出現在手機上了：

例如：電腦桌面正在播放簡報，你可以透過手機來控制下一頁。

你可以在螢幕上劃記：

也可以輸入文字到電腦：

在 Excel 裡有一個日期清單，包含日期和時數二個欄位，如何從日期清單中區別平日和假日計算總和？

參考下圖，以下利用 SUMPRODUCT 函數和 Excel 2021 版以上的 FILTER 函數來計算。

【設計與解析】

選取儲存格A3:E34，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、時數。

1. 計算平日時數

(1) SUMPRODUCT 函數

儲存格I4：=SUMPRODUCT((WEEKDAY(日期,2)<6)*時數)

在 WEEKDAY 函數中利用參數「2」，傳回星期一至星期日對 1 到 7。

條件：WEEKDAY(日期,2)<6，符合者傳回 TRUE，反之傳回 FALSE。

(WEEKDAY(日期,2)<6)*時數：傳回符合條件者的時數。

再後再予以加總。

(2) FILTER 函數

儲存格I4：=SUM(FILTER(時數,WEEKDAY(日期,2)<6))

利用 FILTER 函數篩選符合條件的時數，再由 SUM 函數予以加總。

2. 計算假日時數

(1) SUMPRODUCT 函數

儲存格I5：=SUMPRODUCT((WEEKDAY(日期,2)>5)*時數)

原理同1-(1)。

(2) FILTER 函數

儲存格I5：=SUM(FILTER(時數,WEEKDAY(日期,2)>5))

原理同1-(2)。

延續這篇：Excel-下拉式清單選取月份列出該月日期

如果要在一個日期清單中(包含日期和數值欄位)，如何依指定的星期幾來列出日期？

【設計與解析】

選取儲存A4:B100，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、數值。

定義名稱：星期，

參照到：=MATCH(工作表1!$E$4,{"星期一","星期二","星期三","星期四","星期五","星期六","星期日"},0)

1. 有輔助欄位

儲存格F4：=FILTER(日期,輔助=E4)

只需在儲存格F4輸入公式，公式會自動向下溢出。

儲存格G4：=FILTER(數值,輔助=E4)

只需在儲存格G4輸入公式，公式會自動向下溢出。

2. 沒有輔助欄位

儲存格F4：=FILTER(日期,WEEKDAY(日期,2)=星期)

使用參數「2」，傳回值 1~7，對應至星期一~星期日。

只需在儲存格F4輸入公式，公式會自動向下溢出。

儲存格G4：=FILTER(數值,WEEKDAY(日期,2)=星期)

只需在儲存格F4輸入公式，公式會自動向下溢出。

很常被學校的老師問到要隨機亂數排座位的問題，如果是以 Excel 2021 以上版本來看，公式變得很簡單。

參考下圖，如何亂數重排座位？

【設計與解析】

1. 設計輔助欄位

儲存格D3：=RAND()

利用 RAND 函數，為每個姓名產生亂數。

2. 定義名稱

選取儲存格B3:C39，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、亂數。

3. 重排姓名

儲存格E4：=SORTBY(姓名,亂數)

複製儲存格D3，貼至儲存格D3:D39。

利用 SORTBY 函數將「姓名」欄位依「亂數」欄位排序。

4. 姓名填入座位表

儲存格H10：=OFFSET($E$4,(COLUMN(A:A)-1)*6,0,6,1)

複製儲存格H10，貼至儲存格H10:M10。

(COLUMN(A:A)-1)*6中的「6」是指每排有6個座位。

公式中的第2個6，是指共有6排。

在儲存格H10中以 OFFSET 函數取得間隔 6 個儲存格為一個範圍。

公式會自動溢出至儲存格H10:H14。

5. 每按一次 F9 鍵，即可重排不同的座位。

如果你的 Excel 還是舊版(2021以前版本)，則可以利用以下的公式：

儲存格D3：=RAND()

複製儲存格D3，貼至儲存格D3:D39。

儲存格E4：=INDEX(姓名,RANK(C4,亂數,0))

複製儲存格E4，貼至儲存格E4:E39。

儲存格H10：=OFFSET($E$4,(ROW(1:1)-1)*6+MOD(COLUMN(A:A)-1,6),0)

複製儲存格H10，貼至儲存格H10:M15。

如果不想利用輔助欄位，也可以直接輸入公式：

儲存格：=INDEX(姓名,RANK(OFFSET($C$4,(ROW(1:1)-1)*6+MOD(COLUMN(A:A)-1,6),0),亂數,0))

H15複製儲存格H15，貼至儲存格H15:M20。

每按一次 F9 鍵，即可重排不同的座位。

在 Excel 2021 版之後使用動態陣列，公式會自動溢出，剛開始使用時，有些使用者不習慣，但也慢慢發現其優點。

以下用 FILTER 函數 和 OFFSET 函數來分別取出表格的一部分為例。

選取儲存格A7:C26，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義：名次、人員、數值。

利用 FILTER 函數使用動態陣列，只要在一個儲存格填入公式，公式會溢出至其他儲存格。

儲存格F8：=FILTER(名次,名次<=E8)

儲存格G8：=FILTER(人員,名次<=E8)

儲存格H8：=FILTER(數值,名次<=E8)

本例中，公式中含有陣列(1X4)，而要在三個儲存格中撰寫。

如果使用 OFFSET 函數使用動態陣列，則只要在儲存格F19輸入公式，，公式會溢出至其他儲存格。

儲存格F19：=OFFSET(A7,1,0,E19,3)

本例中，公式中含有陣列(3X4)，而且只要在一個儲存格中撰寫。

【延伸學習】

Excel-陣列的使用(比較2021版和先前的版本)

Excel-以製作九九乘法表說明陣列和非陣列公式

Excel-輸入具陣列形式的公式

Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

Excel-使用ARRAYTOTEXT函數取得陣列文字

Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

在遠距教學時，「聲音」往往是個大問題，通常你會使用瀏覽器來連線會議，所以如果你能在瀏覽器裡產生聲音，就能讓遠端的參與者聽到聲音。

所以如果你要播放的影片已放在 YouTube，或是聲音檔已放在 YouTube Music 上，可以在瀏覽器上播放，然後再分享該頁面。

由於 Google Chrome 或 Microsoft Edge 可以用來播放影片檔或聲音檔，所以如果你的影片檔或聲音檔是放在檔案總管裡，則可以直接將影片拖曳至 Google Chrome 或 Microsoft Edge 的頁面裡，再分享該分頁。

MP3 的聲音檔做法相同：

如果將多個聲音或影片檔，以相同方式在瀏覽器中不同的分頁裡，當在會議中分享時，可以直接點選改分享的分頁。

你可以接著看這幾篇：

使用Google Hangouts Meet進行同步教學(操作說明)

讓Google Meet能播放電腦的音效(MP3、YouTube)

Google Meet可以在Gmail中直接發起和加入會議

在Google Classroom的每個課程中可以設定專屬的Meet連結

利用Google Classroom管理學生和課程(教學)

Google Classroom-在作業中新增檔案或建立文件的各種作法解析

Google Meet-讓與會者能聽到播放YouTube影片的聲音

Google Classroom-解決學生先於教師進入Meet會議，造成教師失去分享螢幕畫面和傳送即時通訊訊息控制權的問題

Google Meet-使用工具整合網頁當作桌面(以Classroomscreen為例)

Google Meet-將音樂檔上傳至YouTube Music，可以使用分享分頁方式播放給學生聽

Google Meet-新增Jamboard時要設定使用權限

Google Meet-通話品質不佳時，檢查網路穩定性和系統負載

Google Meet-在Windows 10中操作遠距教學時善用虛擬桌面減少畫面混亂

Google Meet-在課程或會議中只分享聲音和字幕

Google Meet-讓上課過程和錄影的影片有字幕

Slido-遠距教學和學生互動、投票、測驗的好工具

Google Meet-遠距教學時利用共享文件和檔案以減少學生分享螢幕畫面

Google Meet-主辦人可以新增共同主辦人

Google Meet-使用夥伴模式來消除回音

Google Meet結合觸屏應用在混成教學

Google-如何在實體教學和遠距教學時讓學生線上填答

Google Meet-如何讓分享的影片檔和聲音檔順利播出聲音

還可以試試：

PowerPoint-錄製非同步教學的簡報影片

Google Meet、OBS+YouTube-以直播進行同步教學

Google Meet-觀課教師不進入學生課程會議來觀課

體驗Google Meet的升級功能(Ｇoogle Workspace for Education:Teaching and Learning Upgrade)

你以為視訊會議軟體只能用來視訊會議和教學嗎？

還可這樣：Google Meet-配合學校現有設備讓視訊會議軟體有加值功能