贊助廠商

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

延續這兩篇:

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

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

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

Excel-動態計算最近日期大於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筆來計算平均。

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

 

【參考資料】

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

 

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

文章標籤

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

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

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

【設計與解析】

最近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)

其中必須使用絶對參照。

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

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

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

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

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

【延伸閱讀】

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

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

【參考資料】

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

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

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

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

文章標籤

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

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

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

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

Excel-動態計算最近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

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

文章標籤

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

Excel-依月份判斷季別的各種作法

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

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

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

文章標籤

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

Excel-篩選循列顯示的資料再循欄顯示

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

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

Excel-篩選循列顯示的資料再循欄顯示

【設計與解析】

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

 

1. 使用 Excel 2021 公式

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

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

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

Excel-篩選循列顯示的資料再循欄顯示

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

Excel-篩選循列顯示的資料再循欄顯示

 

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

 

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

文章標籤

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

Excel-成績依性別、組別排名

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


【成績依性別排名】

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)


【成績依組別排名】

Excel-成績依性別、組別排名

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


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

文章標籤

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

Excel-列出指定次數最佳成績的平均

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

 

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

Excel-列出指定次數最佳成績的平均

選取儲存格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次)

 

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

Excel-列出指定次數最佳成績的平均

儲存格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個成績的平均。

【參考資料】

FILTER 函數參考微軟提供的說明網頁:
【延伸學習】
 

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

文章標籤

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

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

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

1. 使用 Excel 2021 版的 FILTER 函數

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

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

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

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

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

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

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

 

2. 使用傳統陣列公式

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

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

 

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

文章標籤

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

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

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

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

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

【設計與解析】

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

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

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

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

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

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

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

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

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

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

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」是這個工作表的名稱)

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

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儲存格範圍。

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

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

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

【參考資料】

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

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

文章標籤

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

依據這二篇的做法:

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

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

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

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

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

1. 設計互評填答表單

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

完成的填答表單如下:

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

接著,讓 Excel 協助分數的計算:

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

3. 填入分數計算公式。

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

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

4. 計算小組分數

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

(1) 計算總分

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

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

(2) 計算平均

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

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

 

【延伸應用】

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

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

2. 複製這個連結網址。

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

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

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

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

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

6. 選取最後一個層級。

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

7. 選取:表單回應1,並點選:載入。

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

目前結果如下:

Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

仿照前面的說明,稍微修改公式:

儲存格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-利用Google表單讓學生依評分量表實施分組互評並計算分數

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

【延伸閱讀】

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

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

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

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

Excel-多選題計分

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

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

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

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

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

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

文章標籤

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

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

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

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

 

【設計與解析】

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

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

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

1. 列出已填名單

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

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

2. 列出未填名單

座號/儲存格E5:=FILTER(座號,內容="")

姓名/儲存格E5:=FILTER(姓名,內容="")

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

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

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

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

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

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

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

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

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

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

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

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-下拉式選單顯示未選項目

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

文章標籤

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

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

 

在 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. 對店名取唯一值

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

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

儲存格G4:=UNIQUE(店名)

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

公式裡,共有三個參數:

第2個參數,TRUE:比較欄的唯一值;FALSE:比較列的唯一值。

第3個參數,TRUE:傳回只出現一次者;FALSE:傳回唯一值。

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

 

2. 依店名計算銷售總額

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

先篩選不重覆店名

儲存格G4:=UNIQUE(店名)

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

接著依店名計算銷售總額

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

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

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

 

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

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

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

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

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

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

 

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

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

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

 

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

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

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

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

 

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

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

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

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

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

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

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

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

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

 

6. 篩選只出現一次者

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

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

藉由控制第三個參數,TRUE:傳回只出現一次者;FALSE:傳回唯一值。

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

 

【延伸閱讀】

 Excel-2021版新增函數的使用

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

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

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

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

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

文章標籤

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

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

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

在 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. 單一欄位排序(依店名遞增排序)

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

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

儲存格H4:=SORT(店名)

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

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

 

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

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

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

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

在此,中文字遞增排序:筆劃由少至多;中文字遞減排序:筆劃由多至少。

 

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

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

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

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

在此,中文字遞增排序:筆劃由少至多;中文字遞減排序:筆劃由多至少。

此公式,相當於手動自訂排序如下:

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

 

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

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

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

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

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

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

此公式,相當於手動自訂排序如下:

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

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

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

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

 

【延伸學習】

  Excel-2021版新增函數的使用

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

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

  Excel-亂數重排座位

 

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

文章標籤

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

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

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

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

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

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

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

 

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

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

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

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

(2) 篩選店名

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

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

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

(3) 篩選代碼

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

(4) 篩選銷售額

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

 

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

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

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

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

 

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

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

(1) 篩選店名

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

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

(2) 篩選代碼

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

(3) 篩選銷售額

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

 

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

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

(1) 篩選店名

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

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

(2) 篩選代碼

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

(3) 篩選銷售額

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

 

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

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

(1) 篩選店名

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

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

(2) 篩選代碼

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

(3) 篩選銷售額

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

 

6. 手動進階篩選

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

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

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

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

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

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

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

 

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

 

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

 Excel-進階篩選

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

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

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

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

 Excel-資料篩選-6

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

 

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

文章標籤

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

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

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

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

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

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

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

行動裝置和電腦螢幕共享-LetsView

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

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

行動裝置和電腦螢幕共享-LetsView

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

行動裝置和電腦螢幕共享-LetsView

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

(1) 手機螢幕投影

(2) 電腦桌面投影

行動裝置和電腦螢幕共享-LetsView

 

1. 手機投影至電腦

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

行動裝置和電腦螢幕共享-LetsView 行動裝置和電腦螢幕共享-LetsView

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

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

 

2. 電腦投影至手機

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

行動裝置和電腦螢幕共享-LetsView Screenshot_20220604-125351

按下「允許」:

行動裝置和電腦螢幕共享-LetsView

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

行動裝置和電腦螢幕共享-LetsView

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

Screenshot_20220604-145855

你可以在螢幕上劃記:

行動裝置和電腦螢幕共享-LetsView

也可以輸入文字到電腦:

行動裝置和電腦螢幕共享-LetsView

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

文章標籤

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

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

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

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

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

【設計與解析】

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

1. 計算平日時數

(1) SUMPRODUCT 函數

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

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

image

條件: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)。


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

文章標籤

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

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

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

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

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

【設計與解析】

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

定義名稱:星期,

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

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

1. 有輔助欄位

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

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

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

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


2. 沒有輔助欄位

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

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

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

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

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

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

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

文章標籤

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

Excel-亂數重排座位

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

參考下圖,如何亂數重排座位?

Excel-亂數重排座位

【設計與解析】

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以前版本),則可以利用以下的公式:

Excel-亂數重排座位

儲存格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 鍵,即可重排不同的座位。


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

文章標籤

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

Excel-FILTER和OFFSET的動態陣列

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

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

Excel-FILTER和OFFSET的動態陣列

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

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

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

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

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

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

Excel-FILTER和OFFSET的動態陣列

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

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

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

Excel-FILTER和OFFSET的動態陣列

 

【延伸學習】

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

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

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

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

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

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

 

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

文章標籤

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

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

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

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

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

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

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

MP3 的聲音檔做法相同:

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

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

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

你可以接著看這幾篇:

使用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的升級功能(Google Workspace for Education:Teaching and Learning Upgrade)

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

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

 

 

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

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼