iT邦幫忙

0

EXCEL 中VLOOKUP後是否可以做到不同儲存格累計減少(上課剩餘堂數)

請教Excel高手們
目前想用Excel製作一個扣課的查詢
如下表
https://ithelp.ithome.com.tw/upload/images/20181116/20113427qqD2veYwRy.png

用VLOOKUP當我輸入姓名後可以看到我想看到的資訊(EX:E2-J2儲存格)
他所購買的堂數加總後(F2+I2)是總共堂數(D2)
但想要可以呈現我總共堂數多少堂後的剩餘堂數(C2、C4)
同個學生(EX:Sandy)的堂數可以一直累計減少到0為止
是否有公式可以帶入呢?

或者有其他程式可以解決嗎?
謝謝!!!

11/19 更新
https://ithelp.ithome.com.tw/upload/images/20181119/20113427imvv2SOx1T.png

https://ithelp.ithome.com.tw/upload/images/20181119/20113427H1xdFkQBIb.png
當B4輸入名字後,會從"會員資料"工作表中抓取購買資料及顯示"目前上課堂數"、"剩餘堂數"
相關公式如下:
C4=MAX(E4-SUMIFS(U$2:U4,B$2:B4,B4,A$2:A4,"<="&A4),0)
D4=COUNTIF(B4:B202,B4)
E4=G4+J4

希望四位老師的工作表可以同步顯示剩餘堂數,假設今天sandy有24堂課,11/24上Duke的課,11/25上Hank的課,現在剩下22堂

看更多先前的討論...收起先前的討論...
看不明白。
你的VLOOKUP函數要寫在哪一格?姓名要輸入在哪一格?
sandyaya iT邦新手 5 級 ‧ 2018-11-16 20:46:49 檢舉
我的函數要寫在C這一排,當我在B2輸入Sandy時會在C2顯示剩餘堂數47,之後我在B4再次輸入Sandy時會顯示剩餘堂數46
sandyaya iT邦新手 5 級 ‧ 2018-11-16 20:51:11 檢舉
我在想是否另一個做法是我寫一個函數去搜尋這整張出現相同名稱的儲存格後,看他出現幾次,用D(總共堂數)相減,C(剩餘堂數)這邊整張表單同步顯示某位到目前為止的剩餘堂數
sandyaya iT邦新手 5 級 ‧ 2018-11-16 20:55:38 檢舉
另一個作法用COUNTIF可以呈現,但呈現的就不是一個一個減少,而是到目前為止累計出現
D4到K4都不用函數嗎?
sandyaya iT邦新手 5 級 ‧ 2018-11-16 22:59:40 檢舉
D4到K4的部分已經設好vlookup從另一張表單抓資料
意思是說,只要A4與B4有值,D4到K4就會自動出現。是嗎?
sandyaya iT邦新手 5 級 ‧ 2018-11-17 17:32:07 檢舉
只要B4有值,D4到K4就會自動出現
所以,按照你的設計,因為B2與B4相同,所以D4就會與D2相同。是嗎?
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
Luke
iT邦研究生 5 級 ‧ 2018-11-16 16:59:38

看不明白/images/emoticon/emoticon67.gif

VLOOKUP
https://ithelp.ithome.com.tw/questions/10088299

VLOOKUP語法: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value:
在表格陣列的第一欄中搜尋的數值Lookup_value 可以是數值,也可以是參照位址。

Table_array:
兩欄以上的資料。
請使用參照位址來指向某個範圍或範圍名稱。

col_index_num:
table_array 中的欄號;相符的值將從該欄中傳回。
如果 col_index_num 引數值為 1,則傳回 table_array 第一欄中的值;
如果 col_index_num 引數值為 2,則傳回 table_array 第二欄中的值,依此類推。

range_lookup:
一個邏輯值,用來指定 VLOOKUP 應該要尋找完全符合還是部分符合的值:
如果此引數值為 TRUE 或被省略了,則傳回完全符合或部分符合的值。

VLOOKUP( '參照位址' , B:C , 1 ,true)

0
海綿寶寶
iT邦大神 1 級 ‧ 2018-11-17 17:12:15
C4=IF(ISERROR(LOOKUP(2,1/(B$1:B3=B4),C$1:C3)-K4),D4-K4,LOOKUP(2,1/(B$1:B3=B4),C$1:C3)-K4)

1.可往下複製至C欄其他儲存格
2.每個人名的第一筆資料(C2)不可用此公式,其他均可使用
3.未考慮小於零的結果

sandyaya iT邦新手 5 級 ‧ 2018-11-17 17:34:40 檢舉

但無法確定每個人名在哪個時候輸入

修正公式,增加錯誤判斷,看看合不合用

0
paicheng0111
iT邦大師 5 級 ‧ 2018-11-18 10:29:02
  1. C4寫下
    =max(d4-sumifs(K$2:K4,B$2:B4,B4,A$2:A4,"<="&A4),0)
    
  2. 按下enter

往下往上拉都可以,拉到C2儲存格也可以。

看更多先前的回應...收起先前的回應...
sandyaya iT邦新手 5 級 ‧ 2018-11-19 01:40:14 檢舉

謝謝您!!!可以使用~
方便請教另一個問題若我有四個這個表單(4個老師每人一個表單)
但學生今天跟a老師上課,明天跟b老師
在“剩餘堂數”這格當中是否可以即時更新?

要看實例才知道,可能可以。

sandyaya iT邦新手 5 級 ‧ 2018-11-19 15:21:44 檢舉

您好,我有更新一下資訊,看是否資料足夠
四個老師的表單都是一樣,只是名稱不同
謝謝您!

假設今天sandy有24堂課,11/18上Duke的課,11/19上Hank的課,現在剩下22堂

你貼的Duke表,沒看到Sandy在11/18的紀錄,應該是做不到了。

sandyaya iT邦新手 5 級 ‧ 2018-11-19 19:46:30 檢舉

我解釋錯了,舉例是11/24sandy是上duke的課,11/25是上hank的課,11/26是上may的課,剩餘課堂數是可以四張表單連動的

11/24sandy是上duke的課,11/25是上hank的課,11/26是上may的課

可是你的圖片是duke表,其中U4:U6都是1,表示sandy連續三天都上duke的課。
你的圖與你的陳述矛盾。

sandyaya iT邦新手 5 級 ‧ 2018-11-19 23:54:44 檢舉

我有更新圖片了,U的部分呈現的是"是否有上課",會呈現O或1(方便統計老師當月的總上課堂數)
D的部分是到學生目前為止所上的"總"上課數,函數是目前表單的=COUNTIF(B4:B202,B4)

D的部分是到學生目前為止所上的"總"上課數,函數是目前表單的=COUNTIF(B4:B202,B4)

依照你所用的函數,上面這段敘述應該要調整為:
D的部分是到學生目前為止在該老師所上的"總"上課數
因為,你的函數並沒有去其他老師的表抓資料。

我要發表回答

立即登入回答