1051 |
Excel上課剩餘堂數統計-IF、IFERROR、MINIFS函數 |
||||
示範檔 |
無 |
範例檔 |
1050.XLSX |
結果檔 |
1050F.XLSX |
在重陽街的小吃街上只見豬八戒低頭沈思的走著走著,忽然看到對面唐三藏輕輕柔柔的走來。
這時,唐三藏詢問豬八戒:「八戒,你怎麼了一個人在沈思什麼呀!是不是我們線上佛學教育課程招生不良呀!」
豬八戒驚醒之下,慢慢道出:「師傅,招生太好了,只是我用Excel作了一份課表明細,但要如何顯示目前所有學員的剩餘節數,苦思無解該如何是好?」
A工作表
B工作表
用VLOOKUP當我在B工作表B欄輸入姓名後可以看到我想看到抓取A工作表內容的資訊(D2-J2儲存格)。
他所購買的堂數加總後(F欄+I欄)是總共堂數減掉J欄等於C欄剩餘堂數,J欄則是自動判別若B欄有資料時,輸入1,否則空白,同個學生(EX:張三丰)的堂數可以一直累計減少到0為止,是否有公式可以帶入呢?
唐三藏:「八戒,其實這對許多初學者來說是一大難題,因為會用到IF、IFERROR、MINIFS三個函數的組合。」
點取C2儲存格輸入公式「=IF(IFERROR(MINIFS($C$1:C1,$B$1:B1,B2),)>0,MINIFS($C$1:C1, $B$1:B1,B2)-J2,E2+H2-J2)」後,按ENTER鍵完成輸入,並將公式複製到C3儲存格。
以後每增加一筆時,只要將上一筆的C:J欄向下複製即可。
【公式解說】
1 MINIFS($C$1:C1,$B$1:B1,B2) 傳回B2儲存格同名字的學生到目前為止剩餘的堂數。
2 IFERROR(MINIFS($C$1:C1,$B$1:B1,B2),) 如果1的計算發生錯誤時表示為記錄起頭則不輸入資料。
3 IF(IFERROR(MINIFS($C$1:C1,$B$1:B1,B2),)>0,MINIFS($C$1:C1, $B$1:B1,B2)-J2,E2+H2-J2) 如果2統計出數值,則以其統計的數值減掉本次上課MINIFS($C$1:C1, $B$1:B1,B2)-J2,否則表示其第一次上課,則將其購買堂數相加減掉本次上課E2+H2-J2。
豬八戒:「哇!師傅您好棒喔!」
唐三藏:「那是當然的。」
留言列表