學不完．教不停．用不盡

(研習練習範例)根據學生請假清單，製作各種統計資訊。

首先，定義名稱，選取儲存格B3:D35，按 Ctrl+Shift+F3 鍵，定義名稱：學生、假別、時數。

1. 計算請假人數

儲存格F4：=COUNTA(UNIQUE(學生))

UNIQUE(學生)：利用 UNIQUE 函數列出不重覆的學生清單。

再利用 COUNTA 函數計算上式陣列的數量。

2. 計算請假人次

儲存格F6：=COUNTA(學生)

利用 COUNTA 函數計算學生陣列的數量。

3. 計算公假時數

儲存格F8：=SUMPRODUCT((假別=F7)*時數)

4. 計算事假時數

儲存格F10：=SUMPRODUCT((假別=F9)*時數)

5. 計算病假時數

儲存格F12：=SUMPRODUCT((假別=F11)*時數)

6. 不重覆的列出請假學清單

儲存格G4：=SORT(UNIQUE(學生))

UNIQUE(學生)：利用 UNIQUE 函數列出不重覆的學生清單。

再利用 SORT 函數予以排序(由小至大)。

7. 計算每位學生的請假時數

儲存格H4：=SUMPRODUCT((學生=G4)*時數)

複製儲存格H4，貼至儲存格H4:H18。

8. 計算每位學生的各種假別的請假時數

儲存格I4：=SUMPRODUCT((學生=$G4)*(假別=I$3)*時數)

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

【參考資料】

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

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

https://support.microsoft.com/zh-tw/office/sort-函數-ccc8-492f-953d-c20e8e44b86c

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

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

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

https://support.microsoft.com/zh-tw/office/counta-函數-7dc98875-d5c1-46f1-9a82-53f3219e2509

在學校裡打分數不是一件容易的事，要做到客觀也不容易。有時候使用評語會比打分數來的好一些。如何利用一些固定的評語詞彙，快速為學生評語？

觀察下圖，如何利用勾選方式，即可獲得一串評語？

選取儲存格B3:J3，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，定義名稱：評語。

儲存格B4：=TEXTJOIN(",",TRUE,IF(C4:J4="V",評語,""))

如果是 Excel 2021 以前的版本，則輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

儲存格B4：{=TEXTJOIN(",",TRUE,IF(C4:J4="V",評語,""))}

(1) IF(C4:J4="V",評語,"")

在陣列中判斷儲存格C4:J4中如果是「V」，則傳回評語對應的內容，否則傳回空字串。

(2) TEXTJOIN(",",TRUE,IF(C4:J4="V",評語,""))

利用 TEXTJOIN 函數將陣列裡的字串予以串接，並且設定忽略空字串。

如果你是 Excel 2021 版，也可以使用 FILTER 函數：

儲存格B4：=TEXTJOIN(",",,FILTER(評語,C4:J4="V"))

【參考資料】

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

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

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

在 Excel 裡，如果是一個很大的數值，則通常是預設以科學記號表示法來呈現。

例如，8.9778E+14，如果調整小數點可以看到其完整的數值，例如，8.97777023524654E+14。在使用時要注意什麼？

如果將數值設定儲存格格式為：0，則所有的指數(科學記號表示)都會以原數值表示。

在自訂格式中填入「?」或「#」或「0」，其實都可以完整的顯示所有的數字。

儲存格B17：=A17&""

如果將儲存格內容和一個空字串串接，則也會顯示完整的數值內容。但是此時看到的數字其實是「文字」。雖然是文字格式，但是執行數值運算也是可以的。

儲存格B24：=TEXT(A24,"0")

以 TEXT 函數為儲存格A24設定格式「0」，結果和數值設定格式相同。雖然是文字格式，但是執行數值運算也是可以的。

如下圖，在 Excel 的工作表裡，如何產生多組相同人員名單的亂數？

例如，人員：甲,乙,丙,丁,戊，要產生多組不同的亂數排列。

【設計與解析】

1. 定義名稱

區段：=OFFSET(工作表1!$C$4,INT((ROW(工作表1!1:1)-1)/5)*5,0,5,1)

(1) INT((ROW(工作表1!1:1)-1)/5)*5

當公式向下複製時，會產生 0,0,0,0,0,1,1,1,1,1,2,2,…。

(2) 在 OFFSET 函數中，以儲存格C4為起點，產生儲存格範圍。

儲存格C4:C8,C4:C8,C4:C8,C4:C8,C4:C8→儲存格C9:C13,C9:C13,C9:C13,C9:C13,C9:C13→儲存格C14:C18,C14:C18,…。

2. 設計公式

儲存格B4：=CHOOSE(RANK(C4,區段),"甲","乙","丙","丁","戊")

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

(1) 利用 RANK 函數判斷C欄中的儲存格內容在區段中的排名。

(2) 利用 CHOOSE 函數依第(1)式的傳回值對應一個人員名字。

你可以依此應用在每週亂數排班，參考下圖：(每按一次F9鍵，就會亂數一次)

【參考資料】

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

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

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

https://support.microsoft.com/zh-tw/office/rank-函數-6a2fc49d-1831-4a03-9d8c-c279cf99f723

在現行的會考成績有「等級標示：A++、A+、A、B++、B+、B、C，對應點數：7、6、5、4、3、2、1」的換算。當取得一個學生的會考成績報表，試著來進一步處理。

1. 計算每個人的總點數

先為所有資料定義：

名稱：班級，儲存格範圍：A4:A350

名稱：等級標示，儲存格範圍：K4:K10

名稱：點數，儲存格範圍：L4:L10

名稱：資料，儲存格範圍：A4:H350

儲存格H4：=SUMPRODUCT((D4:H4=等級標示)*點數)

複製儲存格H4，貼至儲存格H4:H350。

(1) 判斷符合條件「D4:H4=等級標示」者，傳回 TRUE／FALSE 陣列。

(2) (D4:H4=等級標示)*點數，運算時 TRUE／FALSE 陣列會轉換為 1／0 陣列。

(3) 最後加總 1／0 陣列的總和即為所求。

2. 列出所有科目各等級的數量

儲存格L18：=SUMPRODUCT((班級=$K18)*(SUBSTITUTE(資料,L$17,"")<>資料))

複製儲存格L18，貼至儲存格L18:R32。

(1) 條件一：(班級=$K18)

判斷是為儲存格K18所指的班級，傳回 TRUE／FALSE 陣列。

(2) 條件二：(SUBSTITUTE(資料,L$17,"")<>資料)

利用 SUBSTITUTE 函數將資料內容置換儲存格L17的內容為空字串，如果兩者不相等，表示資料內容有包含儲存格L17的內容，傳回 TRUE／FALSE 陣列。

(3) ((班級=$K18)*(SUBSTITUTE(資料,L$17,"")<>資料)

將二個條件執行「*」運算，相當於執行邏輯 AND 運算，並且會將 TRUE／FALSE 陣列轉換為 1／0 陣列再加總。

3. 列出各班共有幾個A

儲存格K9：=SUMPRODUCT((班級=K9)*(SUBSTITUTE(資料,"A","")<>資料))

計算原理同「2. 列出所有科目各等級的數量」。

4. 列出各班「國文A++」清單

儲存格K12：=FILTER(班級,國文="A++")

儲存格L12：=FILTER(姓名,國文="A++")

儲存格M12：=FILTER(總點數,國文="A++")

利用 FILTER 函數執行篩選工作。

5. 列出各班「英文A, A+, A++」清單

儲存格K12：=FILTER(班級,LEFT(英文,1)="A")

儲存格L12：=FILTER(姓名,LEFT(英文,1)="A")

儲存格M12：=FILTER(總點數,LEFT(英文,1)="A")

利用 FILTER 函數執行篩選工作，並以 LEFT 函數判斷英文欄位的第1個字元是否為「A」。

【參考資料】

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 2021 版之後，如果你的公式中有陣列的表示式，則公式會自動溢出至陣列範圍的儲存格。以下要用製作九九乘法表來當為範例，以比較之間的差異。

1. 非陣列公式，利用欄和列已輸入的數值 1~9。

儲存格B4：=$A4*B$3

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

2. 陣列公式，利用 ROW 和 COLUMN 自動產生數值 1~9。

儲存格B4：=ROW(1:9)*COLUMN(A:I)

在儲存格B4輸入的公式會自動溢出至儲存格B4:J12。

在公式中「1:9」和「A:I」都是陣列表示。

3. 非陣列公式，利用欄和列已輸入的數值 1~9。

儲存格A3：=ROW(A1)&"X"&COLUMN(A1)&"="&ROW(A1)*COLUMN(A1)

複製儲存格B3，貼至儲存格B3:I11。

4. 陣列公式，利用 ROW 和 COLUMN 自動產生數值 1~9。

儲存格A3：=ROW(1:9)&"X"&COLUMN(A:I)&"="&ROW(1:9)*COLUMN(A:I)

在儲存格A3輸入公式會自動溢出至儲存格A3:I11。

在公式中「1:9」和「A:I」都是陣列表示。

【延伸學習】

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

Excel-FILTER和OFFSET的動態陣列

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

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

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

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

本篇以研習報名表的Google表單填答結果來練習後續的資料處理。

參考下圖，除了姓名欄位以外，共有四場研習報名，各有一欄參加與否的資訊、一欄便當的資訊。

【設計與解析】

選取儲存格A3:I28，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義各欄位名稱。

1.各課程參加人數

儲存格L4：=SUMPRODUCT((INDIRECT("課程"&K4)="參加")*1)

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

(1) 利用 INDIRECT 函數將字串「"課程"&K4」(本例：課程A)轉換為儲存格範圍。

(2) INDIRECT("課程"&K4)="參加"：條件判斷傳回 TRUE／FALSE 陣列。

(3) INDIRECT("課程"&K4)="參加")*1：將 TRUE／FALSE 陣列轉換為 1／0 陣列。

(4) 利用 SUMPRODUCT 函數將 1／0 陣列加總。

同理：

(1) 參加者便當葷

儲存格M4：=SUMPRODUCT((INDIRECT("便當"&K4)="葷")*1)

(2) 參加者便當素

儲存格N4：=SUMPRODUCT((INDIRECT("便當"&K4)="素")*1)

2. 各課程參加者名單

儲存格L9：=FILTER(姓名,INDIRECT(K8&K9)="參加")

(1) 利用 INDIRECT 函數將字串「K8&K9」(本例：課程A)轉換為儲存格範圍。

(2) 利用 FILTER 函數依條件「INDIRECT(K8&K9)="參加")」篩選並列出符合的「姓名」。

同理，各課程參加者便當資訊：

儲存格M9：=FILTER(INDIRECT("便當"&K9),INDIRECT(K8&K9)="參加")

3. 各課程參加者共報名幾場次

儲存格N9：=SUMPRODUCT((姓名=L9)*($B$4:$I$28="參加"))

利用雙條件：

條件一：姓名=L9

條件二：$B$4:$I$28="參加"

在 SUMPRODUCT 函數將兩者都符合者的數量加總，即為所求。

【參考資料】

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

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

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

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

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

轉眼間，又來到的期末時刻，又是老師的夢魘諺！

有老師想要依多種作業繳交狀況和加權以計算總分，該如何處理？

如下圖中，學生作業繳交狀況有：未交、遲交、完成，三種狀況，分別對應分數：未交(0分)、遲交(70分)、完成(100分)。每個作業也有其加權的份量(如下圖)。

【設計與解析】

1. 將儲存格C3:L4，定義名稱：加權。

2. 設計公式

儲存格B5：=SUMPRODUCT(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權)/SUM(加權)

複製儲存格B5，貼至儲存格B5:B24。

(1) SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)

利用 SWITCH 函數傳回「未交、遲交、完成」所對應的分數「0、70、100」。

(2) SUMPRODUCT(第(1)式*加權)

計算各週第(1)式的傳回值乘以加權的分數加總。

(3) 第(2)式/SUM(加權)

把第(2)式的傳回值除以加權總和，即為加權後的實際分數。

如果改用 SUM 函數：

則公式改為：

儲存格B5：=SUM(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權/SUM(加權))

如果是Excel 2021 以前版本，則輸入完成要按 Ctrl+Shift+Enter 鍵。

儲存格B5：{=SUM(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權/SUM(加權))}

複製儲存格B5，貼至儲存格B5:B24。

假如，你也無法使用 SWITCH 函數，則可以改用 VLOOKUP 函數：

儲存格B5：=SUMPRODUCT(VLOOKUP(C5:L5,{"完成",100;"遲交",70;"未交",0},2,FALSE)*加權)/SUM(加權)

複製儲存格B5，貼至儲存格B5:B24。

【參考資料】

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

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

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

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

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

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

一個 YouTube 影片那裡最精彩、最吸引人再次欣賞，要如何判斷？

如何知道一個教學影片，那裡被學生重覆觀看最多次？

反之，如何知道那些片段不被喜歡而直接被跳過？

現在的教學講求要對學生學習進行觀察和統計、分析學生學習行為，所以如果有數據可供參考當作指標，教學可以得到回饋和驗證。

YouTube 上提供的一個數據，可供參考。只要將滑鼠移到影片中的時間長條上，你會看到一些高低起伏的波狀，這是顯示被重覆播放的次數。

你可以找到重播次數最高的時間點：

如果是教師，以此可以檢視自己的教學影片，那裡被重覆觀看，或是學生較多人不懂之處，或是教學吸引學生再次重看的地方。

如果是學生，以此看看別人都是在那些地方重覆觀看，或是用來直接跳到最精彩的地方。

反之，一個影片中最少被重播或跳過的地方，或許也值得參考。

每個影片都有其特點，網友大眾也是各取所好，因此高高低低起伏的數據，代表影片不可能處處是高潮，總有些地方是迭起。

【延伸閱讀】

YouTube-免工具取得影片中的文字稿

YouTube-讓影片重覆播放某個片段

在教學過程或生活中，YouTube 影片常常扮影重要的角色。在 YouTube 的影片裡，除了會有製作者製作的字幕，也可以由機器自動產生翻譯的文字。字幕隨著影片顯示，也隨著影片消失。

有時學生學習或是聽演講的影片，需要的是一段的文字(一個區間的文字)，該如何滿足這樣的需求？

在 YouTube 中有提供這樣的功能，讓你免工具即可以取得文字稿：

在 YoutTube 影片下方的選單中選取：顯示轉錄稿。

在視窗右側的轉錄稿，文字左側還有時間標示：

你可以在選單中切換為不顯示：

選取這些文字稿，可以複製出來利用。

【延伸閱讀】

YouTube-利用重播次數資訊知道大眾最喜歡或重覆欣賞的片段

YouTube-讓影片重覆播放某個片段

老師問到：在教學簡報裡有許多的投影片，如果要轉換成 PDF 檔給學生，又必須有部分投影片不能給學生，例如：題目的答案等，該如何處理較方便。

你如果想要將檔案另存新檔，再刪除不要的投影片後，再另存成 PDF 檔，當然也可以也。

有沒有更簡單的方式呢？

以下投影片中，藍色字的4張投影片不想出現在 PDF 檔中，該如何處理？

方法很簡單！只要將不想放入 PDF 檔的投影片設定為「隱藏投影片」，再另存新檔為 PDF 檔即可。

PDF 檔裡不會出現隱藏的投影片。

隨著切換投影片的「顯示／隱藏」，即可控制那些投影片會出現在簡報輸出的 PDF 檔中了。這樣的處理方式，原始的投影片內容都可以保留，而設定的動作也非常簡單方便。

有時候在 YouTube 上欣賞一些串接歌曲影片時，如果對某一段影片想要重覆欣賞。

或是，老師們在自己的教學影片上，為每個小單元可以獨立出來當作單元教材，讓學生重覆檢視。

有什麼做法可以達成？

如果你在影片上按右鍵，再選取「循環播放」，則會整個影片重覆的播放。如果只想要某個片段重覆，我試過這樣的方式。

選取：剪輯片段

這本來是用來剪輯片段用的，只要拖曳頭尾片段，維持目前狀態，影片就會在頭尾之間重覆播放。

選好後點選：分享剪輯片段。

再複製網址：

使用該網圵即可重覆觀看了。預設是不斷的重播。

【延伸閱讀】

YouTube-免工具取得影片中的文字稿

YouTube-利用重播次數資訊知道大眾最喜歡或重覆欣賞的片段

開視訊會議時，最常做的就是一心二用。所以如果你有雙螢幕，可就方便多了。因為一個螢幕開會，一個螢幕做其他事。但是如果只有一個螢幕時，就不那麼方便了。

你會想要同時開二個會議？想要工作時顯示二個會議子畫面？

現在，Google Meet 推出了「子母畫面」功能，讓你在開會時，可以切換到其他桌面或應用程式時，你能同時看到會議內容。不用雙螢幕即可多工作業了。

只要在會議的設定選單中，點選：開啟子母畫面。

子畫面即會脫離瀏覽器而單獨存在，所以可以移動到任意位置。

你可以在編輯PowerPoint簡報時，仍能看到會議的內容。會議子畫面會浮在最上層，並不會被其他應用程式所覆蓋。

在子畫面中，可以切換音訊、視訊和掛斷電話。子畫面是可以任意改變大小的，而點選：返回分頁，即可結束子母畫面功能。

這是母畫面的樣子：

按下返回分頁，可以結束子畫面。

如果你想要以Chrome瀏覽器來利用子母畫面功能來同時來開二個會議，恐怕你要失望了。因為 Google Chrome 同時只會有一個子畫面，也就是說你在第二個會議切換至子母畫面時，第一個會議的子畫面就會消失。

但是如果你一個會議以 Chrome 瀏覽器登入、一個會議以 Edge 瀏覽器登入，你的桌面上就可以有二個會議子畫面了。

最後提醒：開會或上課使用遠距，還是要專心，不要做別的事啊！

在本教學網站裡，使用最多的函數就屬 SUMPRODUCT 函數，其函數意義是用來：計算乘績和。

語法：=SUMPRODUCT (array1， [array2]， [array3]， ...)

各陣列引數必須有相同的維度 (相同的列數、相同的欄數)， 否則 SUMPRODUCT 函數會傳回 #VALUE! 錯誤值。

選取儲存格A3:B9，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：A組、B組。

選取儲存格A11:H12，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，定義名稱：C組、D組。

(1) 直式資料

儲存格I4：=SUMPRODUCT(A組,B組)

儲存格I4：=SUMPRODUCT(A組*B組)

以上二個式子略有不同，但是結果相同。A組和B組必須相同維度、欄裡的列數要相同。

(2) 横式資料

儲存格I12：=SUMPRODUCT(C組,D組)

儲存格I12：=SUMPRODUCT(C組*D組)

以上二個式子略有不同，但是結果相同。C組和D組必須相同維度、列裡的欄數要相同。

(1) 計算A組大於5的總和

儲存格I4：=SUMPRODUCT(A組*(A組>5))

條件一：A組>5，會傳回 TRUE／FALSE 陣列。

公式中的「*」，相當於執行邏輯 AND 運算，會將 TRUE／FALSE 陣列轉換為 1／0 陣列。

(2) 計算C組大於5的總和

儲存格I12：=SUMPRODUCT(C組*(C組>5))

(1) 二個矩陣乘積和

儲存格I4：=SUMPRODUCT(A3:C7*D8:F12)

2個矩陣相乘，欄和列的數量相同。

(1) 二個矩陣乘積和

儲存格I4：=SUMPRODUCT(A3:C3*D8:F12)

2個矩陣相乘，欄數相同和列數不相同。「;」區隔不同列，「,」區隔不同欄。

(1) 二個矩陣乘積和

儲存格I4：=SUMPRODUCT(A3:A7*D8:F12)

2個矩陣相乘，列數相同和欄數不相同。

選取儲存格A3:B16，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、數值。

(1) 計算女生數值大於10的總和

儲存格H5：=SUMPRODUCT(數值*(性別="女")*(數值>10))

條件一：性別="女"

條件二：數值>10

公式中的「*」，相當於執行邏輯 AND 運算，會將 TRUE／FALSE 陣列轉換為 1／0 陣列。

選取儲存格D3:G100，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，定義名稱：加權。

(1) 計算加權平均

儲存格H5：=SUMPRODUCT(E5:G5*加權)/SUM(加權)

利用 SUMPRODUCT(E5:G5*加權) 計算加權分數，再除以加權的總和，即為加權平均。

選取儲存格A3:G100，按 Ctrl+Shift+F3 鍵，勾選「最左欄」，定義名稱：班級、座號、性別、國中、國文、英文、數學。

(1) 計算各國中的人數

儲存格J4：=SUMPRODUCT((國中=I4)*1)

(2) 計算各國中裡女生的人數

儲存格J15：=SUMPRODUCT((國中=I15)*(性別="女"))

(3) 計算各國中裡國文大於或等於60的人數

儲存格J26：=SUMPRODUCT((國中=I26)*(國文>=60))

(1) 計算各國中裡女生英文及格人數

儲存格J4：=SUMPRODUCT((國中=I4)*(性別="女")*(英文>=60))

利用三個條件傳回的 TRUE／FALSE 陣列，再以「*」運算執行邏輯 AND 運算。並將 TRUE／FALSE 陣列轉換為 1／0 陣列再加總。

(2) 計算各國中裡女生、男生人數

儲存格J15：=SUMPRODUCT((國中=$I15)*(性別=J$14))

(3) 計算各班級中各國中人數

儲存格J26：=SUMPRODUCT((國中=$I26)*(班級=J$25))

(1) 計算各國中裡國文及格或英文及格的人數

儲存格J4：=SUMPRODUCT((國中=I4)*((國文>=60)+(英文>=60)))

在公式中裡「+」，相當於執行邏輯 OR 運算，並將傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列再加總。

(2) 計算各國中裡國文,英文,數學總分超過200的人數

儲存格J15：=SUMPRODUCT((國中=I15)*(國文+英文+數學>200))

在公式中裡「+」，相當於執行加法運算，並將傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列再加總。

【參考資料】

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

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

【延伸閱讀】

​ 本教學網站和 SUMPRODUCT 函數相關的網頁：

https://isvincent.pixnet.net/blog/search/SUMPRODUCT

本篇要介紹Excel裡很重要的函數，關於 OFFSET 函數的語法和用法。

OFFSET 函數主要是以一個參考點(儲存格)，再給予相對位移位置和高、寬度資訊，來定義一個儲存格範圍。

語法：OFFSET(reference, rows, cols, [height], [width])

參考下圖，例如，儲存格G8：=OFFSET(A4,4,2,10,3)，結果為儲存格C8:E17。

reference：儲存格A4 (由A4為起點)

rows：4 (第5列，由0開始)

cols：2 (第3欄，由0開始)

height：10 (高度10列)

width：3 (寬度3欄)

Excel 2021 有別於先前的版本，只要一個儲存格輸入資料，公式會溢出至其他陣列範圍的儲存格。

如果公式中的高度和寬度為「０」，表示參照一個儲存格。

儲存格G8：=OFFSET(A4,4,3)，結果為儲存格D8。

【reference：儲存格A4】【rows：4】【cols：3】【height：0】【width：0】

在 OFFSET 函數中的參數可以為負值。

儲存格G8：=OFFSET(A4,0,0,6,3)，結果為儲存格A4:D9。

【reference：儲存格A4】【rows：0】【cols：0】【height：6】【width：3】

在 OFFSET 函數中的參數可以為負值。

儲存格G8：=OFFSET(A16,-3,1,8,3)，結果為儲存格B13:D20。

【reference：儲存格A16】【rows：-3】【cols：1】【height：8】【width：3】

儲存格G8：=OFFSET(E22,-2,-1,-12,-3)，結果為儲存格B9:D20。

【reference：儲存格E22】【rows：-2】【cols：-1】【height：-12】【width：-3】

【參考資料】

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

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

【OFFSET應用延伸閱讀】

 Excel-FILTER和OFFSET的動態陣列​

​ Excel-多欄轉換為單欄

​ Excel-取出對角裡的儲存格內容

​ Excel-亂數重排座位

​ Excel-設定圖表只顯示指定起迄月份的資料(動態圖表)

​ Excel-根據起迄日期列出姓名清單

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

​ Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

​ Excel-查表練習(MATCH, OFFSET)

​ Excel-Offset函數應用

​ Excel-累加運算(OFFSET)

​ Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)

​ Excel-查表練習(INDEX,MATCH,OFFSET)

​ Excel-一列多欄轉多列一欄(OFFSET,INT,MOD,ROW)

​ Excel-以欄列交叉對照查表(OFFSET,MATCH)

​ Excel-用公式將資料轉置(OFFSET)

​ Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)

在 PowerPoint 中如何製作如下圖的文字效果？

「萊爾富、王品、光泉、貢茶、春水堂、鼎泰豐」等這些文字有個共同的特點：左右對稱。命名的時候，不知是故意還是巧合？

參考以下的做法：

1. 輸入這些文字，並設定直書。

2. 如下圖，新增矩形方塊。

3. 選取文字再選取矩形，並設定「合併圖案／減去」。

4. 刪掉每個文字的左半部。

5. 複製右半部。

6. 設定複製部分「水平翻轉」。

拉開左、右半部：

左右半部設定不同色彩：

網友問到：在 Excel 中如果參照到另一個檔案的內容，如何因應檔案名稱會更改，希望仍能讀到資料，該如何處理？

以上圖為例，在儲存格的B4:B6，要分別讀取DATA1.xlsx、DATA2.xlsx、DATA3.xlsx中的儲存格A1。公式設計：

儲存格B4：='V:\Google Downloads\[DATA1.xlsx]工作表1'!$A$1

其中「V:\Google Downloads\」是路徑，DATA1.xlsx是活頁簿，工作表1是工作表，是儲存格$A$1。

在此，路徑和檔名都是以「定數」形式來呈現。但是因為檔案名稱會變動(更名)，所以儲存格中的公式會讀不到正確的內容。因此，可以將路徑和檔案名稱改為「變數」來設計。

儲存格B12：=INDIRECT("'"&$C$10&"["&A12&".xlsx]工作表1'!$A$1")

(1) 以「"'"&$C$10&"["&A12&".xlsx]工作表1'!$A$1"」字串組成儲存格的絶對位置。

(2) 以INDIRECT(第(1)式)將儲存格的絶對位置字串轉換為路徑。

在此要注意：

第1種方式：

(1) 會因為檔案名稱修改而抓不到資料

(2) 檔案不須開啟也能抓取儲存格內容

第 2 種方式：

(1) 將路徑和檔名設定為變數方便修改

(2) 檔案必須開啟才能抓取儲存格內容

網友根據這篇：Excel-列出指定星期幾的日期

問到：如何列出一年裡的所有指定星期幾日期？

【設計與解析】

本例指定列出一年裡的所有星期五日期。

1. 列出一年所有日期

先定義名稱，日期：=ROW(INDIRECT(DATE(A$3,1,1)&":"&DATE(A$3,12,31)))

(1) DATE(A$3,1,1)

依儲存格A3指定年的第一天，傳回一個數字(44562)。

(2) DATE(A$3,12,31))

依儲存格A3指定年的最後一天，傳回一個數字(44926)。

(3) INDIRECT(第(1)式&":"&第(2)式))

將第(1)式和第(2)式組成數字區間，透過 INDIRECT 函數轉換為可用的位址(44562:44926)。

(4) ROW(第(3)式

例如，傳回 ROW(44562:44926)。這是用以代表2022年裡所有日期的陣列。

2. 列出一年裡所有星期五

儲存格A4：=FILTER(日期,WEEKDAY(日期,2)=5)

公式會自動溢出至其他儲存格。

(1) WEEKDAY(日期,2)=5

在 WEEKDAY 函數中利用參數「2」，取傳回值為5者。

利用 FILTER 函數來篩選第(1)式傳回值為「5」者。

【延伸閱讀：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版新增函數的使用

【參考資料】

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

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

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

先前的文章：Excel-利用FILTER函數模糊篩選，提到了模糊篩選，本篇要繼續探討各種做法。因為在 FILTER 函數無法使用萬用字元「*」，因此，要以模擬方式來設計，以達到篩選的效果。

【篩選：楊梅國中】

選取儲存格A3:F120，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：班級、姓名、生日、性別、國中、地址。

儲存格I4：=FILTER(姓名,國中="楊梅國中")

在「國中」欄位中篩選合於「條件：楊梅國中」者。

【篩選：仁*國中】

儲存格I4：=FILTER(國中,(LEFT(國中,1)="仁")*(RIGHT(國中,2)="國中"))

因為要模擬「*」萬用字元的概念，在公式中以雙條件來設計：

條件一：LEFT(國中,1)="仁"

條件二：RIGHT(國中,2)="國中"

即表示在國中欄位中要篩選合於「仁」開頭，而「國中」結尾者。

【篩選：新屋區】

儲存格I4：=FILTER(地址,SUBSTITUTE(地址,"新屋區","")<>地址)

在 SUBSTITUTE 函數中，將地址裡的「新屋區」置換為空字串，若和原地址不相同者，表示地址中含有「新屋區」。

或是

儲存格I4：=FILTER(地址,IFERROR(FIND("新屋區",地址),0)>0)

本例以 FIND 函數來判斷在「地址」欄位中是否含有「新屋區」，若有，則傳回一個數字(位置)，若無，則傳回錯誤訊息。所以，再以 IFERROR 函數將錯誤訊息轉為「0」。

因此，只要判斷傳回值是大於 0 者，表示地址中含有「新屋區」。

【篩選：八月出生】

儲存格I4=FILTER(姓名,MID(生日,4,2)="08")

利用 MID 函數取出「姓名」欄位中第 4 碼起始的 2 碼文字，判斷是否為「08」。

以此做為篩選的條件。

【篩選：八月出生的女生】

儲存格I4：=FILTER(姓名,(MID(生日,4,2)="08")*(性別="女"))

在此以雙條件來判斷是否為 8 月出生的女生。

條件一：MID(生日,4,2)="08"，判斷生日的月份是否為「08」。

條件二：性別="女"，判斷性別是否為「女」。

以這二個條件執行邏輯AND運算，做為篩選的條件。

【篩選：93年出生住中壢區】

儲存格I4：=FILTER(姓名,(MID(生日,1,2)="93")*(SUBSTITUTE(地址,"中壢區","")<>地址))

條件一：MID(生日,1,2)="93"，以 MID 函數判斷第1,2碼是否為「93」。

條件二：SUBSTITUTE(地址,"中壢區","")<>地址，置換地址中的「中壢區」，如果和原地址不相同，則表示地址中含有「中壢區」。

以這二個條件執行邏輯AND運算，做為篩選的條件。

或是

儲存格I4：=FILTER(姓名,(MID(生日,1,2)="93")*(IFERROR(FIND("中壢區",地址),0)))

條件一：MID(生日,1,2)="93"，以 MID 函數判斷第1,2碼是否為「93」。

條件二：IFERROR(FIND("中壢區",地址),0)，以 FIND 函數判斷「地址」是否包含「中壢區」。

以這二個條件執行邏輯AND運算，做為篩選的條件。

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

Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

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

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

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

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

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

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

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

 Excel-利用FILTER函數模糊篩選

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

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

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

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

【參考資料】

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

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

https://support.microsoft.com/zh-tw/office/substitute-函數-6434944e-a904-4336-a9b0-1e58df3bc332

FIND,FINDB 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/find-findb-函數-c7912941-af2a-4bdf-a553-d0d89b0a0628

LEFT,LEFTB 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/left-leftb-函數-9203d2d2-7960-479b-84c6-1ea52b99640c

RIGHT,RIGHTB 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/right-rightb-函數-240267ee-9afa-4639-a02b-f19e1786cf2f

MID,MIDB 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/mid-midb-函數-d5f9e25c-d7d6-472e-b568-4ecb12433028

在 PowerPoint 中，如何建立任意形狀的梯形

如果你使用圖案裡的「梯形」物件，則只能建立對稱的梯形，也就是移動調整控制點來改變梯形的形狀。

提供以下三種方式可以建立任意的梯形。

1. 在「繪圖」功能表裡，點選：筆跡轉換圖形。

劃出一個大約的梯形(下圖左)，會自動轉換為直線的梯形。

2. 利用「編輯端點」修改矩形。

在建立的矩形上編輯端點，但要注意，如果操作到白點，線條會由直線變曲線。

3. 建立二個物件(例如：三角形和矩形)，再利用合併圖案的「交集」。