[發問] 比對數量，自動套取符合大於領取數的資料???

帖子: 134
主題: 27
精華: 0
積分: 168
點名: 0
作業系統: WINXP
軟體版本: EXCEL 2003
閱讀權限: 20
性別: 男
來自: 雲林
註冊時間: 2010-10-6
最後登錄: 2018-5-13

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2013-6-19 16:22 | 只看該作者

[發問] 比對數量，自動套取符合大於領取數的資料???

附件中有庫存&領取明細工作表，原是使用vlookup公式於領取明細中輸入料號時會自動代入庫位

但因該料號庫位有多筆，常發生比對第一筆數量小於領取數

1.公式如何設定自動判定尋找符合大於領取數的庫位

2.如果領取數大於明細中該料加總時，自動於D欄位秀出加總數量，並於庫位秀出第一筆庫位資料

以上二需求懇請各位大大賜教，先在此謝謝各位了^^

Book32.rar (2.28 KB)

樓主熱帖

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

2^#

發表於 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欄位秀出加總數量

看不懂，是要秀什麼的加總數量？

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

3^#

發表於 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),"")

複製代碼

以上都是整列引用，最好適資料量改為固定範圍，或是使用動態範圍

帖子: 134
主題: 27
精華: 0
積分: 168
點名: 0
作業系統: WINXP
軟體版本: EXCEL 2003
閱讀權限: 20
性別: 男
來自: 雲林
註冊時間: 2010-10-6
最後登錄: 2018-5-13

論壇贊助勳章

4^#

發表於 2013-6-19 22:18 | 只看該作者

感謝Bodhidharma兄回覆，但小弟按你C2及D2的公式代入卻沒法秀出正確的資料，再次附上公式的檔案，請再幫忙看看，感謝你^^

Book32.rar (2.43 KB)

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

5^#

發表於 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在查找數量不大於所有該項目庫存的總和的時候，不是本來就應該是空白的嗎？還是要顯示什麼東西？

帖子: 134
主題: 27
精華: 0
積分: 168
點名: 0
作業系統: WINXP
軟體版本: EXCEL 2003
閱讀權限: 20
性別: 男
來自: 雲林
註冊時間: 2010-10-6
最後登錄: 2018-5-13

論壇贊助勳章

6^#

發表於 2013-6-20 09:08 | 只看該作者

感謝Bodhidharma兄，用整列的不知何原因，秀出的會是錯誤訊息，改以範圍後公式就可套出了，修改好的附件，再次感謝...^^

Book32.rar (2.6 KB)

帖子: 134
主題: 27
精華: 0
積分: 168
點名: 0
作業系統: WINXP
軟體版本: EXCEL 2003
閱讀權限: 20
性別: 男
來自: 雲林
註冊時間: 2010-10-6
最後登錄: 2018-5-13

論壇贊助勳章

7^#

發表於 2013-6-21 10:50 | 只看該作者

奇怪了,按公式套到自己使用的報表,卻又無法跑出庫位資料,可否請Bodhidharma兄稍微解釋一下公式,否則只是公式自行更換,仍不明怎麼套出來正確資料,在此先感謝了^^

帖子: 134
主題: 27
精華: 0
積分: 168
點名: 0
作業系統: WINXP
軟體版本: EXCEL 2003
閱讀權限: 20
性別: 男
來自: 雲林
註冊時間: 2010-10-6
最後登錄: 2018-5-13

論壇贊助勳章

8^#

發表於 2013-6-21 10:51 | 只看該作者

奇怪了,按公式套到自己使用的報表,卻又無法跑出庫位資料

可否請Bodhidharma兄稍微解釋一下公式,否則公式自行更換,仍不明怎麼套出來正確資料

在此先感謝了^^

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

9^#

發表於 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))回傳第一個