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

[µo°Ý] ½Ð°Ý°ÊºA¸ê®Æ¤¤¡A¦p¦ó¶×Á`¬Û¦P­Èªº¼Æ¦r

[µo°Ý] ½Ð°Ý°ÊºA¸ê®Æ¤¤¡A¦p¦ó¶×Á`¬Û¦P­Èªº¼Æ¦r

½Ð±Ð¤@¤U¦U¦ì«e½ú
¦pªG¨C¦¸¶K¤J¸ê®ÆªíªºÄæ¦ì³£¤£¦P
¥u¦³¦³½æ³o­Ó°Ó«~®É
Äæ¦ì¤~·|¥X²{
©Ò¥H¥i¯à¤µ¤Ñ¶K¤Jªº¸ê®Æªí¤¤
§Ú­nªºÄæ¦ì¡]¤»¤»¤j¶¶«O¶O¡^¬OD4
©ú¤Ñ¶K¤Jªº¸ê®Æªí¤¤
§Ú­nªºÄæ¦ì(¤»¤»¤j¶¶«O¶O)Åܦ¨¬OE5
¹³³o¼Ëªº¸ê®Æ¤¤
¥u¦³¦WºÙ¬Û¦P
§Ú¸Ó¦p¦óÂ^¨ú¸ê®Æ©O¡H

§Ú¥i¯àªí¹Fªº¤£¬O«Ü²M·¡
¦bªþ¥ó Book1.rar (6.25 KB) ¤¤§Ú¦³´£¥X¨â°ÝÃD¡]¥Î¡H¨Óªí¥Ü·Qª¾¹DªºÄæ¦ì¤½¦¡¡^
¥i¥H½Ð¦U¦ì¤j¤jÀ°§Ú¬Ý¬Ý¶Ü¡H
¥ýÁ¹L¦U¦ì«e½ú¤F

ªþ¥ó¦p¥k¡G

¥~¦æ¤H¬Ý¤£¥X§A­nªº¬O¤°»ò?´£°Ý­nÅý¤H¤@¬Ý´NÀ´,©Ò¥H­n¦b???ªº¦a¤è¶ñ¤W¤â¤uªºµª®×.¨Ã¸ÑÄÀ¦p¦ó¨Óªº.

TOP

¦^´_ 2# ANGELA

¬Oªº
©êºp¡A§Ú·Q¤F«Ü¤[ÁÙ¬O¤£ª¾¸Ó¦p¦óªí¹F¥X§Úªº·N«ä
­«ÂI´N¬O
"¸ê®Æ"¤u§@ªí ¤¤ªº¸ê®Æ¬O·|Åܰʪº
§Ú¦p¦ó¨C¦¸³£¯à¦b"²Î­p"¤u§@ªí¤¤ Â^¨ú¨ì"¸ê®Æ"¤u§@ªí¤¤§Ú·Q­nªº¼Æ­È©O¡H
§Úª¾¹D¥i¥H¥ý«Ø¥ß¦WºÙ©w¸q
¦A§Q¥Î INDIRECT(A) INDIRECT(B)
¨Ó¦^¶Ç§Ú·Q­nªº¸ê®Æ
¦ý°ÝÃD¬O
1. ©m¦WA ªº¸ê®Æ ¥]§t¤FA2¡ãM52
     ©m¦WB ªº¸ê®Æ  ¥]§t¤FA55¡ãJ100
     ³o¼Ë¤@¨Ó §Ú´NµLªk©w¸q¦WºÙ¤F
2."¸ê®Æ"¤u§@ªí¤¤ªº¸ê®Æ
     Äæ¦ì±`·|ÅÜ°Ê
      ¦³½æªº°Ó«~¦WºÙ¤~·|¥X²{
      ¨S¦³½æªº°Ó«~¦WºÙÄæ¦ì´N¤£·|¥X²{
      ³o¼Ë¤@¨Ó
     §Ú´N§ó¤£ª¾­n¦p¦ó¨ú±o®æ¤º ¡H ªº¸ê®Æ¤F
©Ò¥H·Q¸ò¦U¦ì«e½ú¨D±Ï
¬O§_¯à½Ð¦U¦ìÀ°¦£
±N¡H¤º¶ñ¤W¤½¦¡
±Ð¾É¤p§Ì¦p¦ó¥Î¨ç¼Æ±o¨ì¡H¤ºªº¸ê®Æ
¦A¦¸«ô°U¦U¦ì¤F¡ã

TOP

¦^´_ 3# hsien33

¦¹ºØªí®æ¤w¸g¤£¯à¦¨¬°³æ¤@¸ê®Æ®w¨Ï¥Î
¸Ñ¨M¤èªk¦³2
1.±NÄæ¦ì³]¸m¦¨¬Û¦PÄæ¦ì(¦p¤U¹Ï)

2.±N¨C­Ó«È¤á¤À¶}­pºâ¡A¦A±N¦U­Ó­pºâµ²ªG¥[Á`(¦p¤U¹Ï)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

·PÁÂHsieh «e½úªº¦^ÂÐ
°w¹ï±z»¡ªº²Ä¤@ºØ¤è¦¡
¥Ñ©ó§Ú¬O±q¨t²Î¤¤©Ô¥Xªº³øªí¨ú±o¸ê®Æ
¤£¥u¦³©m¦WA¡B©m¦WB¨â­Ó
¦Ó¬O¦³20¨Ó­Ó
©Ò¥H¦pªG¤@¤@§ïªí®æ
§Ú·Q·|«D±`¯Ó®É¶¡
¦Ü©ó±z´£¨ìªº²Ä¤GºØ¤è¦¡
§Úı±o¬Û·í¥i¦æ
¥u¬O¦h¤@¹D¥\¤Ò¦Ó¤w
¦ý¬O¤p§Ì§Ú­è¶}©l±µÄ²¨ç¼Æ
²Ä¤GºØ¤è¦¡¥i¥H½Ð«e½ú«üÂI§Ú¸Ó¦p¦ó¼g³o­Ó¨ç¼Æ¶Ü¡H
­«ÂI¤]¬O¥d¦b
°ÊºA½d³ò¤¤§Ú¸Ó¦p¦ó¨ú±o¨C­Ó©m¦W¸Ì  
«È¤áªº   «O¶O¤p­pÄæ¦ì¸ê®Æ
¦A¦¸Á¹L«e½ú¤F

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-9-29 22:44 ½s¿è

¦^´_ 5# hsien33
©w¸q¤U¦C¦WºÙ
A=INDEX(¸ê®Æ!$A:$A,MATCH(²Î­p!$B$2,¸ê®Æ!$A:$A,0)+3,1)
B=OFFSET(A,MATCH("¤p­p",A:¸ê®Æ!$A$65536,0)-2,)
P=MATCH("«O¶O¤p­p",OFFSET(A,-2,,,256),0)
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)

¤U¦CÀx¦s®æ¿é¤J¤½¦¡
²Î­p!B2¥Î¨Ó¿é¤J©m¦W
²Î­p!B4¥H¤U¿é¤J»È¦æ¦WºÙ
²Î­p!C4=SUMPRODUCT((Rng=B4)*Rng1)   ¦V¤U½Æ»s

    ¤À°Ï¥[Á`.rar (9 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

«ö·Ó±zªº§@ªk
¯uªº¥i¥H¨Ï¥Î­C¡ã
¥i¬O·í§Ú¦b"¸ê®Æ"¤u§@ªí¤¤¶K¤J·sªº©m¦WC¸ê®Æ®É
¤S¤£¦æ¤F
§Ú²q¬O¦]¬°©m¦WA¡B©m¦WB§Ú³£§â³\¦hÄæ¦ì§R°£«á¤~§â¸ê®Æ©ñ¤W¨Ó
©m¦WC´Nª½±µ¶K¤W
©Ò¥H¤~¤£¦æ
¥i¥H½Ð«e½ú¦A³Â·Ð¤@¤U
¬°§Ú¤j¬ù¸ÑÄÀ¤@¤U
A=INDEX(¸ê®Æ!$A$A,MATCH(²Î­p!$B$2,¸ê®Æ!$A$A,0)+3,1)
B=OFFSET(A,MATCH("¤p­p",A:¸ê®Æ!$A$65536,0)-2,)
P=MATCH("«O¶O¤p­p",OFFSET(A,-2,,,256),0)
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)
=SUMPRODUCT((Rng=B4)*Rng1)
ªº·N«ä
§Úª¾¹D³o¼Ëªº½Ð¨D«Ü¤£§´
¦ý¬O   §Ú¬Ý¤F¤@­Ó¤p®É
ÁÙ¬O¬Ý¤£¥X­Ó©Ò¥HµM
¥u¦n´£¥X³oµL²zªº­n¨D
³Â·Ð«e½ú¤F

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-9-30 18:08 ½s¿è

¦^´_ 7# hsien33
§R°£Àx¦s®æ·|³y¦¨©w¸q°Ñ·Ó¿ù»~§ï¥ÎINDIRECT«ü©w½d³ò
A=INDEX(INDIRECT("¸ê®Æ!$A:$A"),MATCH(²Î­p!$B$2,INDIRECT("¸ê®Æ!$A:$A"),0)+3,1)   §ä¨ì¸Ó©m¦Wªº²Ä¤@¦C¸ê®Æ¦ì¸m
B=OFFSET(A,MATCH("¤p­p",A:INDIRECT("¸ê®Æ!$A$65536"),0)-2,)          ¸Ó©m¦W¸ê®Æ½d³ò²Ä¤@¦C¸ê®Æ¦ì¸m¦V¤U§ä¨ì²Ä¤@­Ó"¤p­p"¦V¤W1®æ(¸ê®Æ³Ì«á¤@µ§¦ì¸m)  
P=MATCH("«O¶O¤p­p",OFFSET(A,-2,,,256),0)    ¸Ó©m¦W¸ê®Æ½d³ò¼ÐÃD¬°"«O¶O¤p­p"ªº¦ì¸m
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)   AÄæ¸ê®Æ½d³ò
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)   "«O¶O¤p­p"Äæ¦ì¸ê®Æ½d³ò

¤£¹L§Ú»{¬°­n±N¸ê®Æ¾ã²z¦¨¦p¤U¹Ïªº¸ê®Æ®w¦A¨Ó­pºâ·|¤ñ¸û¦n

A=INDEX(INDIRECT("¸ê®Æ!$A:$A"),MATCH(¤u§@ªí1!$A3,INDIRECT("¸ê®Æ!$A:$A"),0)+3,1)   §ä¨ì¸Ó©m¦Wªº²Ä¤@¦C¸ê®Æ¦ì¸m
B=OFFSET(A,MATCH("¤p­p",A:INDIRECT("¸ê®Æ!$A$65536"),0)-2,)     ¸Ó©m¦W¸ê®Æ½d³ò²Ä¤@¦C¸ê®Æ¦ì¸m¦V¤U§ä¨ì²Ä¤@­Ó"¤p­p"¦V¤W1®æ(¸ê®Æ³Ì«á¤@µ§¦ì¸m)  
P=MATCH(¤u§@ªí1!C$1,OFFSET(A,-2,,,256),0)+IF(¤u§@ªí1!C$1="´«ºâ«O¶O¤p­p",0,MOD(COLUMN()-3,3))    ¸Ó©m¦W¸ê®Æ½d³ò¼ÐÃD¬°"«O¶O¤p­p"ªº¦ì¸m
Rng=OFFSET(A,,,ROW(B)-ROW(A)+1,)   AÄæ¸ê®Æ½d³ò
Rng1=OFFSET(A,,P-1,ROW(B)-ROW(A)+1,)   "«O¶O¤p­p"Äæ¦ì¸ê®Æ½d³ò
¤À°Ï¥[Á`.rar (11.89 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh
·PÁ«e½úªº¦^ÂÐ
¤£¹L§ÚÁÙ¬O¨S§¹¥þÀ´
§Ú²{¦b¥¿¦b§V¤O¦Û­×¤¤
µ¥¹L´X¤Ñ
¦A¨Ó¦n¦n¬ã¨s«e½úªº¤½¦¡
§Ú·Q¨ì¨º®É­Ô
À³¸Ó´N¤ñ¸û¯à´£¥X¤£À´ªº¦a¤è¨Ó½Ð±Ð«e½ú¤F
²{¦b¬O¤Ó¦h¤£À´¤F...   >.<

TOP

¦^´_ 8# Hsieh

¶W·PÁª©¤jªº
³o°ÝÃD§xÂZ§Ú¶W¤[¤F
³o­Ó§«ô³£¦b¬ãŪExecl
Á`ºâ¬ÝªºÀ´¤@¨Çª©¤jªº¤½¦¡¤F
§Ú§â¥¦©î¸Ñ¦¨«Ü¦h­Ó¤½¦¡
¤@­Ó¤@­Ó¥h¬ã¨s
²×©ó¤j³¡¤À¬ÝÀ´¤F
¯u¨ØªAª©¤j¯à°÷ÁA¸Ñ³o»ò½ÆÂøªº¤½¦¡
Áٯଡ¥Î¥¦
±z¯u¯«ªü
§Ú²{¦b½Æ»s±zªº¤½¦¡
´N¯à¬d¸ß¨ä¥LÄæ¦ìªº¸ê®Æ
§Ú·Q
µ¥§Ú¦h½m²ß´X¦¸±zªº¤½¦¡
À³¸Ó·|Àò¯q¨}¦h¡ã
Á`¤§
¸U¤À·PÁ±z¡I¡I¡I:D

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD