ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

²Å¦X2­Ó±ø¥ó

²Å¦X2­Ó±ø¥ó

¦P®É²Å¦X2¶µ¸ê®Æ.rar (7.3 KB) ½Ð°Ý¦U¦ì¥ý¶i~­n¦P®É²Å¦X2­ÓÀx¦s®æªº±ø¥ó«á~Åã¥Ü»Ý­nÄæ¦ìªº¸ê®Æ~(¦pªþ¥óÀÉ)~ª¦¤F³\¦h¤å~¸Õ¹LIF,MATCHµ¥¨ç¼Æ~¤´µM¨Sªk¸Ñ¨M~·Ð½Ð¦U¦ì¥ý¶i«ü¾É~

¥»©«³Ì«á¥Ñ register313 ©ó 2012-6-4 16:43 ½s¿è

¦^´_ 1# ®Ü¦â

office 2003
K2=IF(ISERROR(MATCH($I2&$J2,$A$2:$A$9&$B$2:$B$9,0)),"",OFFSET($C$2:$D$9,MATCH($I2&$J2,$A$2:$A$9&$B$2:$B$9,0)-1,COLUMN()-11))
°}¦C¤½¦¡

K2=IF(ISERROR(LOOKUP(2,1/(($A$2:$A$9=$I2)*($B$2:$B$9=$J2)),$C$2:$C$9)),"",LOOKUP(2,1/(($A$2:$A$9=$I2)*($B$2:$B$9=$J2)),$C$2:$C$9))
L2=IF(ISERROR(LOOKUP(2,1/(($A$2:$A$9=$I2)*($B$2:$B$9=$J2)),$C$2:$C$9)),"",LOOKUP(2,1/(($A$2:$A$9=$I2)*($B$2:$B$9=$J2)),$C$2:$C$9))

TOP

¬d

¥»©«³Ì«á¥Ñ ®Ü¦â ©ó 2012-6-6 15:56 ½s¿è

¦^´_ 2# register313


    ÁÂÁÂ¥ý¶i«ü¾É¡A´N¤½¦¡¤º®e
K2=IF(ISERROR(MATCH($I2&$J2,$A$2:$A$9&$B$2:$B$9,0)),"",OFFSET($C$2:$D$9,MATCH($I2&$J2,$A$2:$A$9&$B$2:$B$9,0)-1,COLUMN()-11))¤¤ªº¡i-1,COLUMN()-11))¡j¤Î¡iLOOKUP(2,1/(($A$2:$A$9=$I2)*($B$2:$B$9=$J2)),$C$2:$C$9)¡j¥Nªíªº·N«ä¡A·PÁÂ¥ý¶i¦A¦¸«ü¾É¡C¤p§Ì¥t§ï¦¨¸ó­¶¬d¸ß¸ê®Æ~µ²ªG¥X²{¿ù»~
k=INDEX(Sheet2!$A$2:$E$9,IF(ISERROR(LOOKUP(2,1/((Sheet2!$A$2:$A$9=$J2)*(Sheet2!$C$2:$C$9=$K2)),Sheet2!D2:D9)),"",LOOKUP(2,1/((Sheet2!$A$2:$A$9=$J2)*(Sheet2!$C$2:$C$9=$K2)),Sheet2!$D$2:$D$9)))~½Ð¥ý¶i«ü¾É­n¦p¦ó§ó§ï~·PÁÂ~

TOP

¦^´_ 3# ®Ü¦â

¨Ï¥ÎÀx¦s®æªº¦ì¸m¬O«Ü­«­nªº
§Ú¥ÎSHEET2¥h¬dSHEET1
SHEET2 C2=IF(ISERROR(MATCH($A2&$B2,Sheet1!$A2:$A9&Sheet1!$B$2:$B$9,0)),"",OFFSET(Sheet1!$C$2:$D$9,MATCH($A2&$B2,Sheet1!$A$2:$A$9&Sheet1!$B$2:$B$9,0)-1,COLUMN()-3))   °}¦C¤½¦¡
¦P®É²Å¦X2¶µ¸ê®Æ.rar (4.31 KB)

TOP

·PÁÂ¥ý¶i«ü¾É~²Å¦X¤p§Ì¦b¤u§@¤Wªº»Ý¨D~·P¿E~

TOP

¬Ý¨ì¾Çªø­Ìªº°Q½×
¦p¼Y¤­¨½¶³¤¤
¬Ý¼Ë¤l§Úªº¥\¤OÁÙ¦b¥®¨à¶¥¬q
FreeMan

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD