Excel Trump 知識+ 知識名人

關於部落格
分享 Excel 功能、公式、函數、密技、線上教學...
  • 1237024

    累積人氣

  • 122

    今日人氣

    0

    訂閱人氣

Excel 尋找符合條件再以該條件所找到的資料計算

如果我需要收尋兩個條件後再找出對應的值(功能類似Vlookup,但vlookup只能收尋一個條件"因為只提供了一個lookup value欄位")如A欄代表"料號",B欄代表"供應商",C欄是數量,同一個料號因會有不同的供應商,所以不一定只出現一次,A欄的料號會依不同月份而會變化;我另外有一sheet(第二sheet)設定每個料號的基本資料,我要做的事是收尋與第二個sheet同料號且同廠商的第一個sheet的資料(即第一個sheet的A及B欄)並傳回第一個sheet的C欄值到第二個sheet的指定位置,這樣如何做?








■ 範例檔下載:http://www.funp.net/7708435
為了降低閱讀公式的困難度,因此筆者將全部工作表放在同一張工作表。若有需要放在 Sheet2 工作表,可使用「剪下\貼上」之方法,將 G:Y 欄資料貼至 Sheet2 工作表中。

筆者共提出四個方法,都可辦到:
■ 方法一:使用 VLOOKUP 函數且增加輔助欄
1. 原來資料範圍先插入一欄,以目前資料來說,就是插入 A 欄
2. A2 公式 =B2&C2
3. 將 A2 儲存格公式往下複製到 A8 儲存格
4. 同樣地,在 J3 輸入公式 =H3&I3
5. 將 G3 儲存格公式複製到 G9 儲存格
6. J3 輸入公式 =VLOOKUP(G3,$A$2:$D$8,4,0)
7. 將 J3 儲存格公式往下複製到 J9 儲存格
完成

■ 方法二:使用 VLOOKUP 函數且不增加輔助欄(陣列公式)
O3 =VLOOKUP(M3&N3,IF({1,0},$B$2:$B$8&$C$2:$C$8,$D$2:$D$8),2,0)

■ 方法三:使用 LOOKUP 函數
U3 =LOOKUP(2,1/(($B$2:$B$8=S3)*($C$2:$C$8=T3)),$D$2:$D$8)

■ 方法四:使用 INDEX, MATCH 函數(陣列公式)
Y3 =INDEX($D$2:$D$8,MATCH(W3&X3,$B$2:$B$8&$C$2:$C$8,0))

■ 何謂陣列公式?
1.陣列公式:針對一或多組值執行多個計算,然後傳回單一結果或多個結果的公式。
2.陣列公式與一般公式不同的地方在於,一般公式輸入完成時是按 Enter 按鍵,而陣列公式完成時需要同時按下 Ctrl + Shift + Enter 。
3.陣列公式括在大括弧 { } 中,且藉由按 CTRL + SHIFT + ENTER 輸入。或將筆者提供之公式複製到空白工作表後,請選取儲存格。按 F2,然後按 CTRL + SHIFT + ENTER。
4.如果不是輸入陣列公式,系統會傳回錯誤 #VALUE! 訊息或傳回不正確的計算結果。

■ 複製公式:
上述所有公式,都可以往下複製

相簿設定
標籤設定
相簿狀態