excel中使用index和match解决城市水果销售问题？
excel\u67e5\u8be2\u95ee\u9898\uff0cindex\u548cmatch\u5d4c\u5957\u95ee\u9898\u3002\u594b\u6597\u4e24\u5929\uff0c\u516c\u5f0f\u7ec8\u4e8e\u7f16\u51fa\u6765\uff0c\u4e0d\u8fc7\u6765\u665a\u54af\uff0c\u56de\u7b54\u5df2\u88ab\u91c7\u7eb3\u3002
L1\u5355\u5143\u683c\u8f93\u5165\u4ee5\u4e0b\u516c\u5f0f\uff1a
=INDEX($A$2:$A$9,MATCH(K1,OFFSET($B$2:$B$9,0,MATCH(J1,$B$1:$E$1,0)-1),0)+IF(COUNTIF(INDIRECT("K"&MATCH(J1,$J$1:$J$20,0)&":K"&COUNTIF($J$1:J1,J1)+MATCH(J1,$J$1:$J$20,0)-1),K1)>1,IFERROR(MATCH(K1,INDIRECT(LEFT(CELL("address",OFFSET($B$2:$B$9,0,MATCH(J1,$B$1:$E$1,0)-1)),3)&MATCH(K1,OFFSET($B$2:$B$9,0,MATCH(J1,$B$1:$E$1,0)-1),0)+2&":"&LEFT(CELL("address",OFFSET($B$2:$B$9,0,MATCH(J1,$B$1:$E$1,0)-1)),3)&"9"),0),0),0))
\u516c\u5f0f\u4e0b\u62c9\u5373\u53ef\u3002
\u6548\u679c\u56fe\uff1a
=IF(P6>50,IF(COUNTIF(B6:O6,P6)=1,INDEX($B$2:$N$2, MATCH(P6, B6:O6, 0)-1),),"--")
在E3单元格输入以下公式，然后向下填充公式=INDEX(单价!$A$2:$F$10,MATCH(C3,单价!A$2:A$10,0),MATCH(B3,单价!$2:$2,0))
表示：在单价表数据区域，以城市名称在第2行中精确匹配，返回第几列；以品种在A列精确匹配，返回第几行；交叉位置即是符合双重条件的单价。
E3公式：=INDEX(单价!$B$3:$F$10, MATCH($C3, 单价!$A$3:$A$10, 0), MATCH($B3, 单价!B$2:F$2, 0))
下拉即可
E3=INDEX(单价!$B$3:$F$10,MATCH($C3,单价!$A$3:$A$10,0),MATCH($B3,单价!$B$2:$F$2,0))
下拉
水果销售统计工作表E3单元格公式：
=VLOOKUP(C3,单价!A:F,MATCH(B3,单价!$B$2:$F$2,0)+1)
向下拖拽。
e3中输入=index(单价!$b$3:$f$10,match(b3,单价!$b$2:$f$2,0),match(c3,单价!$a$3:$a$10,0),向下复制
绛旓細??= VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 璁╂垜浠浣跨敤姝ゅ嚱鏁颁粠杩欎釜灏忔暟鎹簱涓煡鎵句竴浜涘笺 瑕佸湪SKU鍒椾腑鏌ユ壘鈥 G23鈥濆苟杩斿洖鍏舵弿杩,鎴戜滑灏嗕娇鐢: = VLOOKUP(鈥 G23鈥,$ A $ 3:$ C $ 7,2,FALSE) 瑕佸湪SKU涓煡鎵锯 G23鈥濆苟杩斿洖鍏朵环鏍,鎴戜滑灏嗕娇鐢: = VLOOKUP(鈥 G23鈥,$ A...
绛旓細鍙互浣跨敤浠ヤ笅鍏紡鏉ュ疄鐜颁綘鐨勯渶姹傦細=INDEX(A14:A17,MATCH(MAX(B14:B17),B14:B17,0))杩欎釜鍏紡鐨勪綔鐢ㄦ槸锛浣跨敤MAX鍑芥暟鏉ユ煡鎵綛鍒椾腑鐨勬渶澶у硷紱浣跨敤MATCH鍑芥暟鍦˙鍒椾腑鏌ユ壘鏈澶у兼墍鍦ㄧ殑琛屾暟锛浣跨敤INDEX鍑芥暟鍦ˋ鍒椾腑鏌ユ壘瀵瑰簲鐨勫崟鍏冩牸銆傛渶缁堬紝璇ュ叕寮忓皢杩斿洖A14鍗曞厓鏍间腑鐨勫尖渕鈥濄傛敞鎰忎簨椤癸細璇风‘淇濆湪杩愯姝ゅ叕...
绛旓細excle甯哥敤鍏紡涓銆佹眰瀛楃涓蹭腑鏌愬瓧绗﹀嚭鐜扮殑娆℃暟锛氫緥锛氭眰A1鍗曞厓鏍间腑瀛楃"a"鍑虹幇鐨勬鏁帮細 =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) 浜屻佸浣曞湪涓嶅悓宸ヤ綔钖勪箣闂村鍒跺畯锛 1銆佹墦寮鍚湁瀹忕殑宸ヤ綔钖勶紝鐐光滃伐鍏/瀹(M)鈥︹濓紝閫変腑浣犵殑瀹忥紝鐐光滅紪杈戔濓紝杩欐牱灏辫皟鍑轰簡VB缂栬緫鍣ㄧ晫闈 2銆佺偣鈥滄枃浠/瀵煎嚭鏂囦欢鈥...
绛旓細1銆佸琛ㄦ牸涓殑鏁版嵁锛鐢鍑芥暟鏌ユ壘鍊肩彮鐨勪汉鍛樸2銆佸湪鍗曞厓鏍艰緭鍏ュ嚱鏁 =INDEX(C29:G37,MATCH(J29,B29:B37,0),MATCH(I29,C28:G28,0))3銆佸嚱鏁 =INDEX(C29:G37,MATCH(J29,B29:B37,0),MATCH(I29,C28:G28,0))锛 鍙傛暟C29:G37—— 鏌ユ壘鍖哄煙 4銆佸嚱鏁 =INDEX(C29:G37,MATCH(J...
绛旓細1. 鍦ㄦ墦寮鐨Excel琛ㄦ牸涓紝鎯宠鍦ㄨ緭鍏ョ殑鍗曞厓鏍煎墠闈㈡壒閲忔坊鍔犲瓧姣峂銆2. 鍦ㄥ彸渚х殑鍗曞厓鏍间腑杈撳叆 ="M"&D3 锛屽洖杞︺(D3涓哄墠闈㈢殑鍗曞厓鏍硷紝鏍规嵁瀹為檯鎯呭喌杈撳叆)3. 鎺ヤ笅鏉ワ紝鍚戜笅濉厖鍚屾牱鐨勫叕寮忋4.灏嗏 娣诲姞瀛楁瘝鍚庣殑鍗曞厓鏍 鈥濆叏閮ㄩ変腑銆傝缁嗚浠ヤ笅鍥剧墖:鎿嶄綔鍥剧墖 ...
绛旓細鍋氫簡涓ず渚嬶紝鍏紡鏄細=INDEX($B$3:$K$6,MATCH($M3,$A$3:$A$6,0),MATCH($N$1&$N$2,$B$1:$K$1&$B$2:$K$2,0))濡備笅鍥撅細M:Q鏄尮閰嶇殑鏁版嵁锛$绗﹀彿闇瑕佹敞鎰忥紝濡傛灉寰椾笉鍒版纭粨鏋滐紝灏辨槸鏌ユ壘鐨勪綅缃紝鎴栬呰寖鍥村彉鍔ㄤ簡銆傝缁嗙殑闇瑕佷綘鑷繁鍘昏瘯涓涓嬩簡銆
绛旓細浣跨敤鏍煎紡:INDEX(array,row_num,column_num) 鍙傛暟璇存槑:Array浠ｈ〃鍗曞厓鏍煎尯鍩熸垨鏁扮粍甯搁噺;Row_num琛ㄧず鎸囧畾鐨勮搴忓彿(濡傛灉鐪佺暐row_num,鍒欏繀椤绘湁 column_num);Column_num琛ㄧず鎸囧畾鐨勫垪搴忓彿(濡傛灉鐪佺暐column_num,鍒欏繀椤绘湁 row_num)銆 搴旂敤涓句緥:濡傚浘3鎵绀,鍦‵8鍗曞厓鏍间腑杈撳叆鍏紡:=INDEX(A1:D11,4,3),纭鍚庡垯鏄剧ず鍑篈1鑷...
绛旓細excel鍖归厤涓や釜琛ㄦ牸鐩稿悓鏁版嵁鐨勬柟娉曞涓嬶細鍒╃敤VLOOKUP鍑芥暟 銆俈LOOKUP鍑芥暟鏄Excel涓鐢ㄤ簬鍦ㄤ竴涓〃鏍间腑鏌ユ壘鎸囧畾鍊肩殑鍑芥暟銆傚彲浠ヤ娇鐢╒LOOKUP鍑芥暟灏嗕竴涓〃鏍间腑鐨勬暟鎹笌鍙︿竴涓〃鏍间腑鐨勬暟鎹繘琛屽尮閰嶃鍒╃敤INDEX-MATCH鍑芥暟缁勫悎 銆侷NDEX-MATCH鍑芥暟缁勫悎鏄疎xcel涓洿鐏垫椿鍜屽己澶х殑鍖归厤鍑芥暟銆傚畠浣跨敤INDEX鍑芥暟浠庝竴涓...
绛旓細3. 浣跨敤绗﹀彿浠ｆ浛鏂囧瓧鍗曚綅锛氬湪绉戝鐮旂┒鍜屽伐绋嬮鍩熶腑锛屼负浜嗚妭鐪佺┖闂村拰鎻愰珮鍙鎬э紝缁忓父浣跨敤绗﹀彿鏉ヤ唬鏇挎枃瀛楀崟浣嶃備緥濡傦紝绫崇敤“m”琛ㄧず锛岀鐢“s”琛ㄧず锛屽崈鍏嬬敤“kg”琛ㄧず銆傝繖浜涚鍙烽氬父鐩存帴鍐欏湪鐩稿簲鐨勬暟鎹悗闈㈡垨琛ㄥご涓傛爣娉ㄥ崟浣嶆椂闇瑕佹敞鎰忕殑鏄紝鍗曚綅搴旇涓庢暟鎹殑鎬ц川鍜岃〃鏍...
绛旓細H鍒楄緭鍏=INDEX($C$3:$C$8,MATCH(LARGE(IF($B$3:$B$8=G3,$D$3:$D$8,0),MOD(ROW(),3)+1),$D$3:$D$8,))I鍒楄緭鍏=INDEX($D$3:$D$8,MATCH(LARGE(IF($B$3:$B$8=G3,$D$3:$D$8,0),MOD(ROW(),3)+1),$D$3:$D$8,))浠ヤ笂鍧囦负鏁扮粍鍏紡锛屼互ctrl+shift+enter...
