snare wrote:
因為您沒有改範圍順...(恕刪)
你好，我使用你的公式可以運作，
但現在碰到退貨時，進貨數量為負值，
而我只想得到最後進貨數量為正值的數據，
有辦法修改嗎？
snare wrote:
當資料量超多，數千筆時，請參考7樓高手
把Sheet1!$B:$B、Sheet1!$A:$A、Sheet1!$C:$C，定義成“動態範圍”
就像這樣=LOOKUP(1,0/(名稱=$A2),日期)，執行效率會比較好
或使用6樓高手的陣列公式
其實我前面用的陣列公式也可套用動態範圍，加快處理速度，
但因目前沒有電腦可用，測試不方便，所以懶得加進去！
（已將陣列公式略做修改，加入動態範圍，請回六樓參考）
此外，發現提問者對於 Excel 並不熟悉，
所以較不建議使用複雜的做法，
最好是以一、兩個公式就能解決，不用另外定義名稱，
日後如因資料增修，需修改公式時也會比較容易！
My Interior Knowledge is Extraordinaire
Mystique Hsiao wrote:
此外，發現提問者對於 Excel 並不熟悉，
所以較不建議使用複雜的做法...(恕刪)
100% 同意
gan978a wrote:
而我只想得到最後進貨數量為正值的數據...(恕刪)
(由下往上找，跳過所有相同產品名稱但數量為負的值，找出最後一筆為正的值)
sheet2 b2
=LOOKUP(1,0/((Sheet1!B:B=A2)*(Sheet1!C:C>0)),Sheet1!A:A)
sheet2 c2
=LOOKUP(1,0/((Sheet1!B:B=A2)*(Sheet1!C:C>0)),Sheet1!C:C)
如果資料量多，建議改成動態範圍，提升計算效率
想要用定義方式，請參考7樓
=LOOKUP(1,0/((名稱=A2)*(數量>0)),日期)
=LOOKUP(1,0/((名稱=A2)*(數量>0)),數量)
要加在公式內請把 Sheet1!A:A、Sheet1!B:B、Sheet1!C:C
用以下3個取代
INDIRECT("sheet1!A1:A"& COUNTA(Sheet1!A:A))
INDIRECT("sheet1!B1:B"& COUNTA(Sheet1!A:A))
INDIRECT("sheet1!C1:C"& COUNTA(Sheet1!A:A))
snare wrote:
我的intel I7...(恕刪)
因為在進出貨新增資料時電腦實在太頓了，所以我改成7樓定義名稱的作法，
而由於我一開始沒想到，上傳的圖和我實際的EXCEL檔的欄位不一樣
所以自己修改的很頭大，
先附上我實際的欄位圖
而以下是我改的定義名稱，借用7樓的解釋
在工作表
先選取E2格後，再新增定義名稱
到貨日期 =OFFSET(進出貨明細!$A$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
商品名稱 =OFFSET(進出貨明細!$C$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
數量 =OFFSET(進出貨明細!$F$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
上面動態定義資料範圍(統一以日期資料數量來定範圍)
最後進貨日 =MAX(IF(商品名稱=工作表!$B1,到貨日期))
計算符合商品名稱中最大日期(最後日期)
然後在
B2格輸入 =IF(最後進貨日>0,最後進貨日,"")
C2格輸入 =SUMIFS(數量,商品名稱,B2,到貨日期,E2)
輸入完後按 Ctrl + Shift + Enter
這樣子在新增進貨資料時，電腦就不會頓了！
但是這個函數會包含"退貨時進貨數量為負值"
請問如果我只要"進貨數量為正值"的要怎麼修改？
gan978a wrote:
但是這個函數會包含"退貨時進貨數量為負值"
請問如果我只要"進貨數量為正值"的要怎麼修改？
...(恕刪)
snare wrote:
(由下往上找，跳過所有相同產品名稱但數量為負的值，找出最後一筆為正的值)
sheet2 b2
=LOOKUP(1,0/((Sheet1!B:B=A2)*(Sheet1!C:C>0)),Sheet1!A:A)
sheet2 c2
=LOOKUP(1,0/((Sheet1!B:B=A2)*(Sheet1!C:C>0)),Sheet1!C:C)
如果資料量多，建議改成動態範圍，提升計算效率
想要用定義方式，請參考7樓
=LOOKUP(1,0/((名稱=A2)*(數量>0)),日期)
=LOOKUP(1,0/((名稱=A2)*(數量>0)),數量)
要加在公式內請把 Sheet1!A:A、Sheet1!B:B、Sheet1!C:C
用以下3個取代
INDIRECT("sheet1!A1:A"& COUNTA(Sheet1!A:A))
INDIRECT("sheet1!B1:B"& COUNTA(Sheet1!A:A))
INDIRECT("sheet1!C1:C"& COUNTA(Sheet1!A:A))
...(恕刪)
我不是回答了嗎??
如果您定義的名稱是用這3個
到貨日期 =OFFSET(進出貨明細!$A$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
商品名稱 =OFFSET(進出貨明細!$C$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
數量 =OFFSET(進出貨明細!$F$1,1,0,COUNTA(進出貨明細!$A:$A)-1)
根據您新的表格，a2=>要改成b2
公式照抄就可以了，其它往下拉
最後進貨日期(跳過數量負值取數量正值的日期)
儲存格E2
=LOOKUP(1,0/((商品名稱=b2)*(數量>0)),到貨日期)
最後進貨數量(跳過負值取正值)
儲存格F2
=LOOKUP(1,0/((商品名稱=b2)*(數量>0)),數量)
gan978a wrote:
而由於我一開始沒想到，
上傳的圖和我實際的EXCEL檔的欄位不一樣
所以自己修改的很頭大
建議先將公式中的各函數意義及用法弄清楚，
再來考慮自己修改函數內的參數內容，
否則只會愈改愈糟！
gan978a wrote:
然後在
B2格輸入 =IF(最後進貨日>0,最後進貨日,"")
C2格輸入 =SUMIFS(數量,商品名稱,B2,到貨日期,E2)
輸入完後按 Ctrl + Shift + Enter
紅字組合鍵是專門用於陣列公式，
除非有特別說明，否則一般公式直接以 Enter 結束即可，
兩種情況不可混用，否則無法得到正確結果！
My Interior Knowledge is Extraordinaire
內文搜尋
從 APP 打開
X