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

¸óªí¨Ì²Å¦X±ø¥ó¿z¿ï.

¸óªí¨Ì²Å¦X±ø¥ó¿z¿ï.

"¿z¿ï"¤u§@ªí¡A­Y¦bB2¿é¤J10305¡A§Æ±æA¡GHÄæ¥h§ì"²Î­p"¤u§@ªí¤º²Å¦XB2ªºA¡GHÄæ¡A¨Ã¦b³Ì«á¤@ÄæÅã¥Ü¦X­p

QUESTION.zip (52.59 KB)

joyce

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-18 12:06 ½s¿è

¦^´_ 1# leiru
¡u¿z¿ï¡v¤u§@ªíÀx¦s®æA4¿é¤J°}¦C¤½¦¡
for Excel 2007
=IFERROR(INDEX(²Î­p!A$2:A$61,SMALL(IF(--LEFT(²Î­p!$A$2:$A$61,5)=$B$1,ROW(²Î­p!$A$2:$A$61)-1,4^8),ROW(1:1))),"")
for Excel 2003
=IF(ISERROR(SMALL(IF(--LEFT(²Î­p!$A$2:$A$61,5)=$B$1,ROW(²Î­p!$A$2:$A$61)-1,4^8),ROW(1:1))),"",INDEX(²Î­p!A$2:A$61,SMALL(IF(--LEFT(²Î­p!$A$2:$A$61,5)=$B$1,ROW(²Î­p!$A$2:$A$61)-1,4^8),ROW(1:1))))
¦V¥k¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I
µù¡G¨Ì½d¨Ò´£¨Ñ¡u¿z¿ï¡v¤u§@ªí¨ä¿é¤J«ü©w­È©óÀx¦s®æB1¡A¦Ó«DÀx¦s®æB2¡C

TOP

¦^´_ 2# p212


    ÁÂÁ¦^ÂСA¥t¥~¦³´£¨ì¡A²Å¦X±ø¥óªº¸ê®Æ³Ì«á¤@¦CaÄ椺Åã¥Ü"¦X­p"¨Ã¦bGÄ椺§â¥þ³¡¼Æ¦r¬Û¥[°_¨Ó¡C
ÁÂÁÂ
joyce

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-18 17:17 ½s¿è

¦^´_ 3# leiru
¡u¿z¿ï¡v¤u§@ªí¤§Àx¦s®æG4­×§ï¦¨ °}¦C¤½¦¡
=IF((A4="")*(A3=""),"",IF((A4="")*(A3<>""),"¦X­p¡G"&SUM($G$3:G3),INDEX(²Î­p!G$2:G$61,SMALL(IF(--LEFT(²Î­p!$A$2:$A$61,5)=$B$1,ROW(²Î­p!$A$2:$A$61)-1,4^8),ROW(1:1)))))
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 4# p212


    ÁÂÁ±z
¥t¡A¦A½Ð°Ý¡A¦X­p¨ºÀx¦s®æ¦p¦ó±N¼Æ­È³]©w¤d¤À¦ì¡C
joyce

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-18 18:22 ½s¿è

¦^´_ 5# leiru
±ý±N¡u¿z¿ï¡v¤u§@ªí¤§Àx¦s®æG4ªº¼Æ­È³]©w¡u¤d¤À¦ì¡v¡A½Ð­×§ï¦¨°}¦C¤½¦¡¡G
=IF((A4="")*(A3=""),"",IF((A4="")*(A3<>""),"¦X­p¡G"&TEXT(SUM($G$3:G3),"#,000"),INDEX(²Î­p!G$2:G$61,SMALL(IF(--LEFT(²Î­p!$A$2:$A$61,5)=$B$1,ROW(²Î­p!$A$2:$A$61)-1,4^8),ROW(1:1)))))
½Ð°Ñ¦Ò¡I

TOP

¦A½Ð°Ý¡G
²Î­p¤u§@ªí¤ºªºL2¤ÎL3¦p¦ó¨D¥X­È¡C¡]¨ÌAÄæ¨D¥X¨Óªº¡^

QUESTION.zip (65.27 KB)

joyce

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-21 12:07 ½s¿è

¦^´_ 7# leiru
²Î­p¤u§@ªíÀx¦s®æL2¿é¤J°}¦C¤½¦¡
for Excel 2007
=IFERROR(INDEX(²Î­p!A$2:A$61,LARGE(IF(--LEFT(²Î­p!$B$2:$B$61,5)=$K2,ROW(²Î­p!$B$2:$B$61)-1,FALSE),1)),"")
for Excel 2003
=IF(ISERROR(LARGE(IF(--LEFT(²Î­p!$B$2:$B$61,5)=$K2,ROW(²Î­p!$B$2:$B$61)-1,FALSE),1)),"",INDEX(²Î­p!A$2:A$61,LARGE(IF(--LEFT(²Î­p!$B$2:$B$61,5)=$K2,ROW(²Î­p!$B$2:$B$61)-1,FALSE),1)))
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 8# p212


    ÁÂÁ±zªº¦^µª....:)
joyce

TOP

¦^´_ 8# p212


    ¤£¦n·N«ä¡A¦A½Ð±Ð¡A¿z¿ï¤u§@ªí¤ºªºL2Àx¦s®æ¬O§_¥i°ÊºAªº¨Ì²Î­p¤u§@ªí¤ºªºBÄæ°µ¤U©Ô¦¡¿ï³æ¡A¦]²Î­p¤u§@ªí¤ºªºBÄæ·|¦AÄ~Äò¤@ª½·s¼W¤é´Á¤U¥h¡C

·PÁ±z¼ö¤ß¦^ÂÐ
joyce

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD