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

®ø°£Àx¦s®æ#VALUE!

®ø°£Àx¦s®æ#VALUE!

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-5-17 14:29 ½s¿è

¦U¦ì¤j®a¦n
                  °}¦C¤½¦¡{=VALUE(IF(COLUMN()-5<=COUNTIF(Sheet1!$P$2:$P$1000,$B5),INDIRECT("Sheet1!R"&SMALL(IF(Sheet1!$P$2:$P$1000=$B5,ROW(2:1000)),INT(COLUMN()-5))&"C"&"17",0),""))}
              ¥X²{#VALUE!¥ÎISERROR¦p¦ó¼g¤½¦¡Àx¦s®æ¤è¯à¤£¥X²{#VALUE!¡A°Ñ¦Ò¨ä¥L»¡©ú¦Û¤v¸Õ¤F«Ü¤[µLªk§¹¥þ¦¨½Ð¤j·|¨Ó¨ó§UÁÂÁ¡C

¯à¤W¶Ç¦³°ÝÃDªºÀɮ׶Ü?

TOP

¤W¶ÇªþÀɽХý¶i¨ó§UÁÂÁ¡C

Book1.rar (7.72 KB)

TOP

¥»©«³Ì«á¥Ñ zz5151353 ©ó 2010-5-17 16:34 ½s¿è

F5 ½Æ»s¦¹¤½¦¡©¹¥k¤Î¤U¶K

{=INDEX(Sheet1!$Q:$Q,SMALL(IF(Sheet1!$P$2:$P$46=$B5,ROW(Sheet1!$P$2:$P$46),4^8),COLUMN(A$1)))&""}

¥t¥~ SHEET1!$P$2:$P$46 ¥i¥H³]°ÊºA
©w¸q¤@¦WºÙ ZZ
¤½¦¡ =OFFSET(Sheet1!$P$2,,,COUNTA(Sheet1!$P$2:$P$65536))

¦]¦¹¤½¦¡¥i¥H²¤Æ
{=INDEX(Sheet1!$Q:$Q,SMALL(IF(ZZ=$B5,ROW(ZZ),4^8),COLUMN(A$1)))&""}


Z5 ½Æ»s¦¹¤½¦¡©¹¤U¶K

{=MAX(IF(ISNUMBER(--F5:Y5),--F5:Y5))}

TOP

ÁÂÁÂzz5151353 ª©¥D       ±a¤J±z´£¨Ñªº¤½¦¡¡A¤@ ¤ÁOK¤F  ¡C    ¥i§_½Ð±Ð¨ä¤¤¬õ¦â{=INDEX(Sheet1!$QQ,SMALL(IF(ZZ=$B5,ROW(ZZ),4^8),COLUMN(A$1)))&""}
              {=MAX(IF(ISNUMBER(--F5:Y5),--F5:Y5))}¬O¤°»ò·N¸qÁÂÁ±z¡C

TOP

¥»©«³Ì«á¥Ñ zz5151353 ©ó 2010-5-19 12:50 ½s¿è
ÁÂÁÂzz5151353 ª©¥D       ±a¤J±z´£¨Ñªº¤½¦¡¡A¤@ ¤ÁOK¤F  ¡C    ¥i§_½Ð±Ð¨ä¤¤¬õ¦â{=INDEX(Sheet1!$QQ,SMA ...
y663258 µoªí©ó 2010-5-17 23:13


4^8    ¦b excel2003ª©¥»©Î¥H¤U¬O³Ì¤j row , 65536
½Ð¬Ý hsieh ª©¥D¾ã²zªº¦nªF¦è  http://forum.twbts.com/viewthread.php?tid=36&extra=page%3D1


--    ·N«ä¬O±j¨î±q¤å¦r§ÎºAªº¼Æ¦rÂà´«¦¨¯u¥¿ªº¯Â¼Æ¦r,¦P®É¤]¥i¥H³o¼Ëªí¥Ü *1 ( +0¡B-0¡B*1¡B/1¡Bvalue¡]¡^¨Ó¹ê²{¡C)

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD