標題:
[發問]
比對數量，自動套取符合大於領取數的資料???
[打印本頁]
作者:
p6703
時間:
2013-6-19 16:22
標題:
比對數量，自動套取符合大於領取數的資料???
附件中有庫存&領取明細工作表，原是使用vlookup公式於領取明細中輸入料號時會自動代入庫位
但因該料號庫位有多筆，常發生比對第一筆數量小於領取數
1.公式如何設定自動判定尋找符合大於領取數的庫位
2.如果領取數大於明細中該料加總時，自動於D欄位秀出加總數量，並於庫位秀出第一筆庫位資料
以上二需求懇請各位大大賜教，先在此謝謝各位了^^
[attach]15261[/attach]
作者:
Bodhidharma
時間:
2013-6-19 17:28
本帖最後由 Bodhidharma 於 2013-6-19 17:30 編輯
回復
1#
p6703
找第一個符合的話 C2陣列公式：
=INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>=B2),0))
複製代碼
自動於D欄位秀出加總數量
看不懂，是要秀什麼的加總數量？
作者:
Bodhidharma
時間:
2013-6-19 17:36
回復
2#
Bodhidharma
是這樣嗎？
C2陣列公式(CTRL+SHIFT+ENTER輸入)：
=IF(ISNA(INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0))),INDEX(庫存!C:C,MATCH(A2,庫存!A:A)))
複製代碼
D2一般公式：
=IF(B2>SUMPRODUCT(--(庫存!A:A=A2),庫存!B:B),"A料總庫存僅有"&SUMPRODUCT(--(庫存!A:A=A2),庫存!B:B),"")
複製代碼
以上都是整列引用，最好適資料量改為固定範圍，或是使用動態範圍
作者:
p6703
時間:
2013-6-19 22:18
感謝Bodhidharma兄回覆，但小弟按你C2及D2的公式代入卻沒法秀出正確的資料，再次附上公式的檔案，請再幫忙看看，感謝你^^
[attach]15264[/attach]
作者:
Bodhidharma
時間:
2013-6-20 01:29
本帖最後由 Bodhidharma 於 2013-6-20 01:31 編輯
回復
4#
p6703
抱歉，C2公式不知道為什麼少了一段，應該是陣列公式：
=IF(ISNA(INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0))),INDEX(庫存!C:C,MATCH(A2,庫存!A:A)),INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0)))
複製代碼
另外D2在查找數量不大於所有該項目庫存的總和的時候，不是本來就應該是空白的嗎？還是要顯示什麼東西？
作者:
p6703
時間:
2013-6-20 09:08
感謝Bodhidharma兄，用整列的不知何原因，秀出的會是錯誤訊息，改以範圍後公式就可套出了，修改好的附件，再次感謝...^^
[attach]15266[/attach]
作者:
p6703
時間:
2013-6-21 10:50
奇怪了,按公式套到自己使用的報表,卻又無法跑出庫位資料,可否請Bodhidharma兄稍微解釋一下公式,否則只是公式自行更換,仍不明怎麼套出來正確資料,在此先感謝了^^
作者:
p6703
時間:
2013-6-21 10:51
奇怪了,按公式套到自己使用的報表,卻又無法跑出庫位資料
可否請Bodhidharma兄稍微解釋一下公式,否則公式自行更換,仍不明怎麼套出來正確資料
在此先感謝了^^
作者:
Bodhidharma
時間:
2013-6-21 11:52
回復
7#
p6703
=IF(ISNA(INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0))),INDEX(庫存!C:C,MATCH(A2,庫存!A:A)),INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0)))
複製代碼
重點是
INDEX(庫存!C:C,MATCH(1,(庫存!A:A=A2)*(庫存!B:B>B2),0))
複製代碼
match的部分，(庫存!A:A=A2)*(庫存!B:B>B2)會形成一個1跟0的陣列，兩個都符合就是1，其中一個不符合就是0
因此match(1,{1,0之陣列},0)就會回傳第一個符合的列數
再用INDEX把那個列數叫出來，即是想要的答案
若match不到(即ISNA(....)，)，則以INDEX(庫存!C:C,MATCH(A2,庫存!A:A))回傳第一個
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
