index+match+sumproduct¿z¿ï
- ©«¤l
- 4
- ¥DÃD
- 5
- ºëµØ
- 0
- ¿n¤À
- 6
- ÂI¦W
- 0
- §@·~¨t²Î
- window7
- ³nÅ骩¥»
- office2016
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2018-10-27
- ³Ì«áµn¿ý
- 2019-5-17
|
index+match+sumproduct¿z¿ï
½Ð¦U¦ìexcel°ª¤â¨ó§U¦^µª, §Ú§Æ±æ¥Ñ¹Ïªí1¨Ï¥Îindex+match¿z¿ïred, green, blue,
¦A¨Ï¥Îsumproduct¤ñ¹ï¹Ïªí1¤Î¹Ïªí2pºâ¥X¦UÓÁ`¦X, ¦ýmatch¥u¯à¿z¿ï¥Xx, µLªk¿z¿ï¥Xx1, ¦p¦óק綠¦¡, ¨Ï¨ä¯à¦P®Éºâ¥Xx¤Îx1ªº¥[Á`, ÁÂÁÂ
¹Ïªí1
x x1 y z
red red green blue
¹Ïªí2 ¹Ïªí3
A B C A B C
1 x z 1 2 2
2 x y z 2 4 7 5
3 x y z 3 5 2 8
4 x y x1 4 3 4 5
5 x y x1 5 5 4 2
6 z y x1 6 6 6 6
7 z y x1 7 2 8 8
8 z y x1 8 5 3 5
9 z y 9 1 4
10 z 10 6
pºâµ²ªG
red =SUMPRODUCT(($D$8:$F$17=INDEX($M$7:$P$8,1,MATCH(M12,$M$8:$P$8,0)))*1,$I$8:$K$17)=19
green =SUMPRODUCT(($D$8:$F$17=INDEX($M$7:$P$8,1,MATCH(M13,$M$8:$P$8,0)))*1,$I$8:$K$17)=38
blue =SUMPRODUCT(($D$8:$F$17=INDEX($M$7:$P$8,1,MATCH(M14,$M$8:$P$8,0)))*1,$I$8:$K$17)=35
red¥[Á`À³¬°45, ¦ýmatch¥u¯à¿z¿ï¥Xx, µLªk¿z¿ï¥Xx1, ¦p¦óק綠¦¡, ¨Ï¨ä¯à¦P®Éºâ¥Xx¤Îx1ªº¥[Á` |
|
|
|
|
|
|