2019年1月15日 星期二

Excel-從已繳交清單中找出繳交作業學生的成績(SUMPRODUCT)

如果要將已繳交的學生顯示成績,未繳交的學生標示「X」,該如何處理?
Excel-從已繳交清單中找出繳交作業學生的成績(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:B300,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、座號、成績。
儲存格F2:
=IF(SUMPRODUCT((班級=F$1)*(座號=$E2)),INDEX(成績,
SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))-1,0),"X")
公式:INDEX(成績,SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))-1,0)
(1) SUMPRODUCT((班級=F$1)*(座號=$E2)*ROW(成績))
(班級=F$1)*(座號=$E2)*ROW(成績):在SUMPRODUCT函數中找出符合條件:班級=F$1和座號=$E2的成績列號。
(2) INDEX(成績,第(1)式-1,0)
利用 INDEX 函數,以查表方式根據第(1)式傳回的列號,顯示對應的儲存格內容。

2019年1月12日 星期六

Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

老師們讓學生繳交作業時是透過Google表單上傳資料,Google表單也會在Google試算表中儲存學生的繳交資訊,包含班級和座號等。如果老師教授的班級較多,要在短時間內找出尚未繳交作業的學生,一筆一筆的核對,會造成老師的負擔,有沒有較快的方式?
參考下圖,如果取得了班級和座號的資訊,可以利用這個欄位,在一個班級和座號矩陣中可以尚未繳交的班級、座號上做標記。
Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

【公式設計與解析】
假設共有五個班級,每個班級20個學生,假設學生可能會重覆繳交。
1. 先選取儲存格A1:B300(為何比所有學生總和多?因為學生可能會重覆繳交。),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、座號。
2. 在儲存格E1:J21中,建立班級、座號的矩陣。
3. 輸入公式,儲存格F2:=IF(SUMPRODUCT((班級=F$1)*(座號=$E2)),"","X")
在 SUMPRODUCT 函數使用兩個條件:「班級=F$1」和=座號=$E2」。其中「*」運算子相當於執行邏輯 AND 運算
(1) 若完全符合條件者會傳回數值 1。
(2) 若同一學生繳交多次,則會傳回大於 1 的數值。
(3) 若學生未繳交,則會傳回 0。
最後,利用這個傳回值,只要是大於或等於 1 者表示有繳交,顯示空字串;若是未繳交者,則顯示「X」記號。
相同的操作和公式,可以套用在 Google 試算表中,請自行練習囉!
Excel-從已繳交清單中找出尚未繳交作業的學生(SUMPRODUCT)

2019年1月1日 星期二

Excel-每天遞增存1元,各月分別存多少錢?(ROW,INDIRECT,N,DATE)

今天看到媒體報導了熱血青年儲存零錢由一元開始,每天遞增一元,整年下來也存了六萬多元。只是,剛開始很輕鬆,到了後面每天要存的金額就有些壓力了。
以下就來看看每個月要負擔多少錢?(以2019年為例)第1個月存496元,第12個月要存10,850元,的確落差太大。如果可以定額每月存5,566元,應該也是不錯的好方法。以下來看看各月要存的金額如何求得?
Excel-每天遞增存1元,各月分別存多少錢?(ROW,INDIRECT,N,DATE)

【公式設計與解析】
1. 計算全年金額
儲存格E17:{=SUM(ROW(1:365))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
在陣列公式中,ROW(1:365)代表 1~365 的數值。利用 SUM 函數予以加總,在陣列公式中等同 1+2+….+364+365。

2. 計算各月金額
儲存格H2:
{=SUM(ROW(INDIRECT(DATE(2019,ROW(1:1),1)-N(DATE(2019,1,1))+1&":"&
DATE(2019,ROW(1:1)+1,1)-N(DATE(2019,1,1)))))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格H2,貼至儲存格H2:H13。
(1) N(DATE(2019,1,1))
計算 2019年1月1日所代表的數值。(本例為:43466)
(2) DATE(2019,ROW(1:1),1)-N(DATE(2019,1,1))+1
計算各月第 1 天的數值。
(3) DATE(2019,ROW(1:1)+1,1)-N(DATE(2019,1,1))
計算各月最後 1 天的數值。
(4) INDIRECT(第(2)式&":"&第(3)式)
利用 INDIRECT 函數將第(2)式和第(3)式的傳回值組合用以轉換為儲存格範圍。
(5) ROW(INDIRECT(第(2)式&":"&第(3)式))
利用 ROW 函數將第(4)式傳回的儲存格範圍轉換為數值範圍。
(6) SUM(ROW(INDIRECT(第(2)式&":"&第(3)式)))
最後利用 SUM 函數求得數值範圍的總和。

檢視其他文章

好康東東