Board logo

標題: SUMPRODUCT多條件用法 [打印本頁]
作者: PJChen    時間: 2017-6-27 07:43     標題: SUMPRODUCT多條件用法

1. Quotation 工作表 A欄=匯總工作表 列1
2. Quotation 工作表 C欄=匯總工作表 列4
3. Quotation 工作表 列2=匯總工作表 A欄
4. 希望用公式帶出Quotation 工作表 Q~AN欄所有數據

請教先進們：
我用去做,為何都帶不出來數值,或者有其他函數可用?  [attach]27383[/attach]
作者: zz5151353    時間: 2017-6-27 07:57

C7 這意思嗎？
=SUMPRODUCT((C$4=Quotation!$C$4:$C$50)*(Quotation!$A$4:$A$50=C$1)*ISNUMBER(FIND(Quotation!$Q$2:$AN$2,$A7))*Quotation!$Q$4:$AN$50)
作者: PJChen    時間: 2017-6-27 12:27

回復 2# zz5151353

C7
=SUMPRODUCT((C$4=Quotation!$C$4:$C$50)*(Quotation!$A$4:$A$50=C$1)*ISNUMBER(FIND(Quotation!$Q$2:$AN$2,$A7))*Quotation!$Q$4:$AN$50)

太感謝了，就是這樣！請問大大2個問題：
1. 紅字的部份有時放前面，有時在後面，在意義上有沒有什麼不同？
2. 如果我把公式套用在C6就不成立，因為它是"文字",請問這種情形要怎麼改公式？有沒有文字及數字可通用的？
作者: zz5151353    時間: 2017-6-27 12:42

問題1 , 是絕對跟相對引用問題，簡單說那裡要鎖定就放個 $ 號,依據拖拉方向

問題2
強制文字形態數字轉換成純數字  --  , *1

C6
=VLOOKUP(C$1,Quotation!$A:$F,6,)
作者: PJChen    時間: 2017-6-27 13:01

回復 4# zz5151353

大大,

1. 您的意思是說純文字無法用sumproduct只能用VLOOKUP ?不過因為我常要用到多個條件的功能，所以才會想到用sumproduct,而vlookup只能一個條件.

2. 我把資料key好,發現許多錯誤，可以幫我看一下嗎？我用黃色的區塊是正確的. [attach]27391[/attach]
作者: zz5151353    時間: 2017-6-27 13:33

本帖最後由 zz5151353 於 2017-6-27 13:40 編輯

C21 下拉及右拉，注意提供黃色區域數據跟實際位置有部份是不同位置的
=SUMPRODUCT((C$4=Quotation!$C$4:$C$500)*(Quotation!$A$4:$A$500=C$1)*(Quotation!$B$4:$B$500=C$2)*(Quotation!$Q$2:$AO$2=$A21)*Quotation!$Q$4:$AO$500)
作者: PJChen    時間: 2017-6-27 16:14

回復 6# zz5151353

謝謝你的提醒,現在完全正確了^^
作者: PJChen    時間: 2017-6-28 13:06

回復 6# zz5151353

請問大大,
另一個檔用雷同的方法,在派車單工作表中為何文字部分用您說的強制轉文字(強制文字形態數字轉換成純數字  --  , *1),還是不行！ [attach]27405[/attach]
作者: ML089    時間: 2017-6-28 18:23

B3 =INDEX(派車!$A:$N,MATCH($A$2,派車!B:B,),MATCH($A3,派車!$1:$1,))

公式下拉
作者: PJChen    時間: 2017-6-28 20:13

回復 9# ML089

請問大大，
我要是同一張訂單,同一天派車2次但不同貨運公司,資料會帶不出來，這要如何修正公式？ [attach]27406[/attach]
作者: ML089    時間: 2017-6-28 20:41

本帖最後由 ML089 於 2017-6-29 06:58 編輯

回復 10# PJChen

最好 一張派車執行一項任務，這樣派車單號就是資料庫的KEY(索引號碼)容易查詢。

若是 派車單號 有多筆任務，放資料時又是歸檔再一起，查詢也不困難，就是 先查詢第一筆位置再加1列就是

查詢第一筆
B3 =INDEX(派車!$A:$Q,MATCH($A$2,派車!B:B,),MATCH($A3,派車!$1:$1,))

查詢第二筆
B3 =INDEX(派車!$A:$Q,MATCH($A$2,派車!B:B,)+1,MATCH($A3,派車!$1:$1,))

查詢第三筆
B3 =INDEX(派車!$A:$Q,MATCH($A$2,派車!B:B,)+2,MATCH($A3,派車!$1:$1,))   

若要防止沒有第二筆或第三筆，可以使用 COUNTIF 來計算有幾筆
B3 =IF(COLUMN(A1)>COUNTIF(派車!$B:$B,$A$2),"",INDEX(派車!$A:$Q,MATCH($A$2,派車!$B:$B,)+COLUMN(A1)-1,MATCH($A3,派車!$1:$1,)))
右拉 (第一筆、第二筆、...)
下拉
作者: PJChen    時間: 2017-6-28 22:07

回復 11# ML089

謝謝！也行，這也是一個方法.
作者: zz5151353    時間: 2017-6-29 08:17

派車單工作表
B3 下拖拉
=VLOOKUP($A$2,派車!$B:$O,MATCH($A3,派車!$B$1:$O$1,),)
作者: PJChen    時間: 2017-6-29 11:09

回復 13# zz5151353

謝謝你,這公式也可以用,只是都無法解決同一張訂單,同一天派車2次但不同貨運公司,資料會帶不出來的問題.
作者: PJChen    時間: 2017-7-4 13:19

回復 11# ML089

請大大再指導下,為什麼類似的用法,在這個檔案中，卻帶不出結果？

[attach]27426[/attach]
作者: ML089    時間: 2017-7-4 16:02

回復 15# PJChen

B2儲存格
=INEDX(庫存!$A:$AH,MATCH($A2,庫存!$F:$F,),MATCH(B$1,庫存!$1:$1,))
修改為
=INDEX(庫存!$A:$AH,MATCH($A2,庫存!$F:$F,),MATCH(B$1,庫存!$1:$1,))
作者: aer    時間: 2017-7-4 16:02

回復 15# PJChen

B2公式函數名稱鍵錯為 inedx，修正為 index 後，公式即可正常運作

B3=VLOOKUP($A3,庫存!$F:$R,MATCH(B$1,庫存!$F$1:$R$1,),)
作者: PJChen    時間: 2017-7-4 18:37

回復 17# aer
回復 16# ML089

我看了半天也沒看出錯誤在哪！...感謝了.
作者: 平心靜氣    時間: 2017-7-14 04:07

本帖最後由 平心靜氣 於 2017-7-14 04:08 編輯

請教 15# PJChen
LAMP附加檔,欄位名稱上方的...+...-，類似小計的縮放是如何做出來的??
此功能對工作上,顯示含有大量資料的表單，還滿實用的。



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)