史上最牛查找函數(shù)組合 Index + Match

1、史史上上最最牛牛查查找找函函數(shù)數(shù)組組合合 Index + Match 一一看看就就會會! (老老搭搭檔檔，新新感感覺覺)通通用用公公式式 =INDEX(#1,MATCH(#2,#3,#4),MATCH(#5,#6,#7)在列中返回某個單元格水果北京水果北京蘋果40蘋果40香蕉38香蕉38檸檬15檸檬15橙子25橙子25梨子40梨子40參數(shù) - 行3水果橙子計算結果15計算結果25在行中返回某個單元格水果北京上海廣州水果北京上海廣州蘋果404142蘋果404142參數(shù) - 列2地區(qū)上海計算結果41計算結果414141在區(qū)域中返回某個單元格水果北京上海廣州水果北京上海廣州蘋果404142蘋果404

2、142香蕉383940香蕉383940檸檬151617檸檬151617橙子252627橙子252627梨子404142梨子404142參數(shù) - 行3水果檸檬參數(shù) - 列2地區(qū)上海計算結果16計算結果16通用公式 =INDEX(#1,MATCH(#2,#3,#4),MATCH(#5,#6,#7)多條件下 在區(qū)域中 返回某個單元格水果等級北京上海廣州蘋果A404142蘋果B383940檸檬A151617檸檬B252627檸檬C404142水果檸檬等級B地區(qū)上海計算結果262626返回某個單元格 在 多區(qū)域返回某個單元格 在 多區(qū)域水果北京上海廣州水果北京上海廣州蘋果404142蘋果404142香蕉

3、383940香蕉383940檸檬151617檸檬151617橙子252627橙子252627梨子404142梨子404142干果北京上海廣州干果北京上海廣州杏仁101112杏仁101112腰果161718腰果161718花生202122花生202122胡桃121314胡桃121314瓜子161218瓜子161218參數(shù) - 區(qū)域1產(chǎn)品類別水果參數(shù) - 行3產(chǎn)品名稱香蕉參數(shù) - 列2地區(qū)上海計算結果16計算結果39返回單行或單列返回單行或單列水果北京上海廣州合計水果北京上海廣州合計蘋果404142123蘋果404142123香蕉383940117香蕉383940117檸檬15161748檸檬15

4、161748橙子25262778橙子25262778梨子404142123梨子404142123合計158163168489合計158163168489參數(shù) - 行0水果0參數(shù) - 列2地區(qū)上海計算結果41計算結果413939161626264141結果求和163結果求和163返回單行返回單行水果北京上海廣州合計水果北京上海廣州合計蘋果404142123蘋果404142123香蕉383940117香蕉383940117檸檬15161748檸檬15161748橙子25262778橙子25262778梨子404142123梨子404142123合計158163168489合/p>

5、9參數(shù) - 行2水果檸檬參數(shù) - 列0地區(qū)0計算結果383940計算結果151617結果求和117結果求和48返回區(qū)域返回區(qū)域水果北京上海廣州合計水果北京上海廣州合計蘋果404142123蘋果404142123香蕉383940117香蕉383940117檸檬15161748檸檬15161748橙子25262778橙子25262778梨子404142123梨子404142123合計158163168489合計158163168489起點終點起點終點參數(shù) - 行2參數(shù) - 行4水果香蕉水果橙子參數(shù) - 列2參數(shù) - 列3地區(qū)上海地區(qū)廣州=INDEX($C$5:$E$9,C13,C14):INDEX

6、($C$5:$E$9,F13,F14)=INDEX($C$5:$E$9,C13,C14):INDEX($C$5:$E$9,F13,F14)計算結果393939計算結果393939393939393939393939393939393939393939393939393939結果求和165結果求和165返回 單行 單列 的 交叉點水果北京上海廣州合計蘋果404142123香蕉383940117檸檬15161748橙子25262778梨子404142123合計158163168489參數(shù) - 行03參數(shù) - 列20交叉點16記記錄錄篩篩選選 Select * Where *出出庫庫單單序序號號業(yè)業(yè)

7、務務員員地地區(qū)區(qū)產(chǎn)產(chǎn)品品數(shù)數(shù)量量1D001北京M011002D001北京M022003D001上海M033004D002上海U01305D002上海U02406D002北京U03507D002北京U0415008D002上海U0525009D002上海U063500條條件件業(yè)業(yè)務務員員D002地地區(qū)區(qū)上海結結果果序序號號業(yè)業(yè)務務員員地地區(qū)區(qū)產(chǎn)產(chǎn)品品數(shù)數(shù)量量1D002上海U01302D002上海U02403D002上海U0525004D002上海U0635005#NUM!#NUM!#NUM!#NUM!返返回回 多多列列 (根根據(jù)據(jù)日日期期求求和和)=SUM(INDEX($D4:$O4,MATCH

8、($C$3,$D$3:$O$3,0):O4)代碼2009/1/102009/1/122009/1/112009/1/102009/1/92009/1/82009/1/72009/1/62009/1/52009/1/42009/1/32009/1/22009/1/1KF.005422,973121314151617215,726207,0520111220KF.004654,76714151617181900011122654,564KF.00373416171819202122646222KF.0021,376181920216462344646444KF.00190,964206465555

9、5555000090,7440GS.034663,11464655555555553,6463,646346346346654,564GS.0331,317,583266465764657573,6463,646654,564346654,5640GS.0321,274285959595959003463463460GS.030252326363636363000000現(xiàn)在我們想達到這個結果.當我們在C2單元格,輸入2009.5時,在C3以下單元格,公式,可否顯示.D:O區(qū)域中對應的和.例;C2單元格輸入 2009-01-05C3 = K3:O3 數(shù)據(jù)求和 207185C2單元格輸入 2009

10、-01-12C3 = D3:O3 數(shù)據(jù)求和 422998也也就就是是說說,=C2日日期期的的區(qū)區(qū)域域數(shù)數(shù)據(jù)據(jù)和和物物料料需需求求計計劃劃(MRP) 計計算算 - Sumif + Index + Match1.以完成品生產(chǎn)計劃、BOM、零部件期初庫存、零部件入庫計劃為基礎資料，按日期分析零部件的盈缺2.范例簡單，僅為拋磚引玉1.成成品品 - 生生產(chǎn)產(chǎn)計計劃劃完成品NullNull2010/1/12010/1/22010/1/32010/1/42010/1/5男士自行車2,000 2,500 3,000 3,500 4,000 女士自行車3,500 4,500 4,000 2,000 4,500

11、合計5,500 7,000 7,000 5,500 8,500 2.BOM (產(chǎn)產(chǎn)成成品品 零零部部件件 對對照照表表) - 引引用用成成品品生生產(chǎn)產(chǎn)計計劃劃中中的的生生產(chǎn)產(chǎn)數(shù)數(shù)量量 * 用用量量完成品零部件用量2010/1/12010/1/22010/1/32010/1/42010/1/5男士自行車車架(男)12,000 2,500 3,000 3,500 4,000 男士自行車車把12,000 2,500 3,000 3,500 4,000 男士自行車車輪24,000 5,000 6,000 7,000 8,000 女士自行車車架(女)13,500 4,500 4,000 2,000 4,

12、500 女士自行車車把13,500 4,500 4,000 2,000 4,500 女士自行車車輪27,000 9,000 8,000 4,000 9,000 合計22,000 28,000 28,000 22,000 34,000 3.零零部部件件 入入庫庫 計計劃劃 - 根根據(jù)據(jù) 在在途途材材料料 和和 采采購購訂訂單單 制制作作零部件Null2009/12/312010/1/12010/1/22010/1/32010/1/42010/1/5車架(男)7,500 2,000 車架(女)12,000 2,000 車把19,500 4,000 車輪74,000 8,000 合計113,000

13、0 0 16,000 0 0 4.零零部部件件 庫庫存存、需需求求、盈盈缺缺分分析析TRUE=SUMIF($C$15:$C$22,$B37,INDEX($E$15:$I$22,0,MATCH(I$35,$E$14:$I$14,0)零部件Null2009/12/312010/1/12010/1/12010/1/12010/1/22010/1/22010/1/22010/1/32010/1/32010/1/32010/1/42010/1/42010/1/42010/1/52010/1/52010/1/5庫存到貨需求庫存到貨需求庫存到貨需求庫存到貨需求庫存到貨需求庫存車架(男)7,500- 2,0005,500 - 2,5003,000 00002880000#,#0 3,0002,000 - 3,500 短缺-1,500 - 4,000 短缺-5,500 車架(女)12,000- 3,5008,500 - 4,5004,000 00002880000#,#0 4,0002,000 - 2,000 零庫存0 - 4,500 短缺-4,500 車把19,500- 5,50014,000 - 7,0007,000 00005760000#,#0 7,0004,000 - 5,500 短缺-1,500 - 8,500 短