贊助廠商

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

Excel-學生請假時數統計(UNIQUE,SORT,SUMPRODUCT)

首先,定義名稱,選取儲存格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。

Excel-學生請假時數統計(UNIQUE,SORT,SUMPRODUCT)

【參考資料】

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

Excel-利用勾選以組織評語字串(TEXTJOIN)

選取儲存格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-利用勾選以組織評語字串(TEXTJOIN)

如果你是 Excel 2021 版,也可以使用 FILTER 函數:

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

【參考資料】

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

學不完.教不停.用不盡文章列表

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

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

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

Excel-數值不以科學記號(指數)表示

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

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

Excel-數值不以科學記號(指數)表示

儲存格B17:=A17&""

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

Excel-數值不以科學記號(指數)表示

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

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

Excel-數值不以科學記號(指數)表示

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

Excel-亂數排列多組的人員名單(OFFSET,CHOOSE)

【設計與解析】

1. 定義名稱

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

Excel-亂數排列多組的人員名單(OFFSET,CHOOSE)

(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鍵,就會亂數一次)

Excel-亂數排列多組的人員名單(OFFSET,CHOOSE)

【參考資料】

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

 

1. 計算每個人的總點數

Excel-會考成績處理(FILTER,SUMPRODUCT)

先為所有資料定義:

名稱:班級,儲存格範圍: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. 列出所有科目各等級的數量

Excel-會考成績處理(FILTER,SUMPRODUCT)

儲存格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

Excel-會考成績處理(FILTER,SUMPRODUCT)

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

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

 

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

Excel-會考成績處理(FILTER,SUMPRODUCT)

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

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

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

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

 

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

Excel-會考成績處理(FILTER,SUMPRODUCT)

儲存格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

學不完.教不停.用不盡文章列表

文章標籤

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

在 Excel 2021 版之後,如果你的公式中有陣列的表示式,則公式會自動溢出至陣列範圍的儲存格。以下要用製作九九乘法表來當為範例,以比較之間的差異。

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

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

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

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

 

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

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

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

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

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

 

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

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

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

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

 

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

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

儲存格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-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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 函數參考微軟提供的說明網頁:

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

Excel-依多種作業繳交狀況和加權以計算總分

【設計與解析】

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

Excel-依多種作業繳交狀況和加權以計算總分

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 函數:

Excel-依多種作業繳交狀況和加權以計算總分

則公式改為:

儲存格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 函數:

Excel-依多種作業繳交狀況和加權以計算總分

儲存格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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

【延伸閱讀】

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

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

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

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

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

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

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

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

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

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

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

【延伸閱讀】

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

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

有沒有更簡單的方式呢?

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

PowerPoint-如何在簡報轉換為PDF檔時不會出現指定的投影片

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

PowerPoint-如何在簡報轉換為PDF檔時不會出現指定的投影片

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

PowerPoint-如何在簡報轉換為PDF檔時不會出現指定的投影片

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

 

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

有什麼做法可以達成?

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

選取:剪輯片段

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

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

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

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

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

再複製網址:

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

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

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

【延伸閱讀】

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

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

這是母畫面的樣子:

Google Meet-利用子母畫面讓你邊開會邊做其他事

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

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

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

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

Google Meet-利用子母畫面讓你邊開會邊做其他事

學不完.教不停.用不盡文章列表

文章標籤

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

Excel-解析SUMPRODUCT函數

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

語法:=SUMPRODUCT (array1, [array2], [array3], ...)

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

 

Excel-解析SUMPRODUCT函數

選取儲存格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組必須相同維度、列裡的欄數要相同。

 

Excel-解析SUMPRODUCT函數

(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))

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

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

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

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

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

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

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

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

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

 

Excel-解析SUMPRODUCT函數

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

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

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

條件一:性別="女"

條件二:數值>10

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

 

Excel-解析SUMPRODUCT函數

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

(1) 計算加權平均

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

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

 

Excel-解析SUMPRODUCT函數

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

(1) 計算各國中的人數

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

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

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

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

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

 

Excel-解析SUMPRODUCT函數

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

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

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

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

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

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

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

 

Excel-解析SUMPRODUCT函數

(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

學不完.教不停.用不盡文章列表

文章標籤

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

Excel-解析OFFSET函數

本篇要介紹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-解析OFFSET函數

 

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

Excel-解析OFFSET函數

 

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

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

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

Excel-解析OFFSET函數

 

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

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

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

Excel-解析OFFSET函數

 

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

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

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

Excel-解析OFFSET函數

 

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

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

Excel-解析OFFSET函數

 

 

【參考資料】

​ 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,使用名稱)

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

PowerPoint-設計左右半部對稱文字

參考以下的做法:

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

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

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

PowerPoint-設計左右半部對稱文字

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

5. 複製右半部。

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

PowerPoint-設計左右半部對稱文字

拉開左、右半部:

PowerPoint-設計左右半部對稱文字

左右半部設定不同色彩:

PowerPoint-設計左右半部對稱文字

 

學不完.教不停.用不盡文章列表

文章標籤

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

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

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) 檔案必須開啟才能抓取儲存格內容

學不完.教不停.用不盡文章列表

文章標籤

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

Excel-列出一年裡的所有星期幾日期

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

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

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年裡所有日期的陣列。

Excel-列出一年裡的所有星期幾日期

 

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

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

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

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

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

Excel-列出一年裡的所有星期幾日期

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

 

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

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

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

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

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

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

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

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

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

 Excel-利用FILTER函數模糊篩選

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

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

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

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

【參考資料】

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

 

【篩選:楊梅國中】

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

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

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

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

 

【篩選:仁*國中】

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

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

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

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

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

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

 

【篩選:新屋區】

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

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

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

或是

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

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

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

 

【篩選:八月出生】

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

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

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

以此做為篩選的條件。

 

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

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

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

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

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

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

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

 

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

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

儲存格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-篩選資料並轉置資料

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

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

 Excel-利用FILTER函數模糊篩選

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

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

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

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用

【參考資料】

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

學不完.教不停.用不盡文章列表

文章標籤

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

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

PowerPoint-建立任意形狀的梯形

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

PowerPoint-建立任意形狀的梯形`

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

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

PowerPoint-建立任意形狀的梯形

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

PowerPoint-建立任意形狀的梯形 PowerPoint-建立任意形狀的梯形

 

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

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

PowerPoint-建立任意形狀的梯形

 

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

PowerPoint-建立任意形狀的梯形

學不完.教不停.用不盡文章列表

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼