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

[µo°Ý] EXCEL ­«ºâÀx¦s®æ

[µo°Ý] EXCEL ­«ºâÀx¦s®æ

§Ú°µ¤F¤@­ÓÀɮצ³¤G­Ó¤u§@ªí¡]DATA¤Î¦¬¤Jªí¡^
¦¬¤Jªíªº³¡¥÷¸ê®Æ³sµ²DATA
¨ä¹ê³o­ìÀɤ£¬O§Ú°µªº
§Ú¥u¬O·í®É(¤j·§10¦~«e)ı±o³o­Ó«Ü¦n¥Î´N®³¨Ó­×§ï
¦ý¬O­ìÀɧڤ]§Ë¥á¤F
­×§ï«á¤@ª½Ä±±o«Ü¤£¶¶¡A¾ã­ÓÀÉ®×"¶]«ÜºC"
¨C¦¸¿é¤Jª÷ÃB´N¥X²{"­«ºâÀx¦s®æ"
µM«á­n¤@ª½ºâ¨ì100%
¥­§¡³£­nµ¥¤W­Ó¤Q´X¬íÄÁ¡A¦pªG­n¿é¤J«Ü¦hª÷ÃB´N­n¤@ª½µ¥
§Ú¦³¸Õ¹L¤â°Ê¸Õºâ¡A¥þ³¡¥´¦A§¹¦A«öF9¡Aµ¥¥¦¶]§¹¤j·§­nµ¥3¤ÀÄÁ@@

¦]¬°§Ú­×§ïªºEXCELªíµLªk¤W¶Ç¨ì³o­µ¸Ì¨Ó
©Ò¥H§Ú§â¤½¦¡¶K¨ì³o¤W­±¨Ó
¦¬¤Jªí¸Ì©w¸q¸Ì°µ¤F¤G­Ó¨ç¼Æ
=OFFSET(DATA!$B$1,MATCH(Á~¸ê¦¬¤J!D4,DATA!$A$2:$A$971,0),,COUNTIF(DATA!$A$2:$A$971,Á~¸ê¦¬¤J!D4))
=DATA!$C$2:$C$8

§Ú¤£ª¾¹D¬O¤£¬O¦³¿ìªk¥i¥HÅý¥¦³B²z³t«×¶¶ºZ¤@ÂI¡H

¦^´_ 1# jochun

³æ´N³o¼Ë¬Ý¤£¥X¨Ó¡A¾ã­Ó¸ê®Æªí¤¤ÁÙ¦³­þ¨Ç¨ç¼Æ¹Bºâ¡H

TOP

¥»©«³Ì«á¥Ñ jochun ©ó 2013-4-21 14:27 ½s¿è

¦^´_ 2# Bodhidharma


²{¦b¥Îªº³o¬O§Ú­×§ï¹L²Ä¤G­Óª©
§Ú­è­èµo²{§Ú¤§«e¨º­Óª©¸ÌªºVBA PROJECT¸ÌÁÙ¦³«Ü¦hªº¤§«eªºÀÉ®×
¥i¬O«o§R¤£±¼
¦ý¨ä¹ê¦b¤§«e¨ºª©®É´N¤w¸g¬O¶]«ÜºC¤F
¤£¾å±o¬O¤£¬O¦]¬°³o¼Ë©Ò¥H¤~¶]³o»òºC
°ò¥»¤WÀ³¸Ó¬O¨S¦³¨ä¥L¨ç¼Æ¤F

111.jpg (38.87 KB)

111.jpg

TOP

¦^´_ 3# jochun
¦bVBAµøµ¡¤¤©Ò¬Ý¨ìªº¤u§@ªí¡A´N¬OÀɮפºÁÙ¦s¦b³o¨Ç¤u§@ªí
³o¸Ì­±¥ú¬Ý¤u§@ªí¦WºÙ´N¥iª¾¹D·|¥Î¨ì¤£¤Öªº¤½¦¡
¦V­«½Æ­Èªº­pºâ³q±`·|¦³«Ü¦h»²§UÄæ©Î¬O°}¦C¤½¦¡
©Ò¥H¨S¦³Àɮ׬O¤£¥i¯à°µ¨ì¥ô¦ó§ïµ½ªº«Øij
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


§Ú­è­èµo²{§Ú§â³o¨Ç¤u§@ªíÁôÂäF
§R°£«á¬O¦n¤F
¦ý¥t¤@­ÓÀÉ®×
´N¬O²Ä¤G­Óª©¥»ÁÙ¬O¤£¦æ
¦]¬°²Ä¤G­Óª©¥»ªº¤u§@ªí«Ü¦h«Ü¦h
©Ò¥H¦pªG§Ú·Q­nÅý¥¦¶]ªº¶¶ºZ¬O¤£¬O´N¥²¶·­n¤À¶}¨Ó°µ¡A¤£­n¥þ³£¶ë¦b¤@­Ó¬¡­¶Ã¯¸Ì¡H

TOP

§Ú¦A½Ð°Ý¤@¤U¡A§Úªº¤u§@ªí¸Ì¥H«e¦³°µ¥¨¶°
«á¨Ó§Ú§R±¼¤F¡A§Ú¤]¶i¥hVBA¸Ì§âµ{¦¡½X³£§R±¼¤F
¦ý¬O§Ú¶}±Ò®ÉÁÙ¬O¤@¼Ë·|¥X²{¬O§_¶}±Ò¥¨¶°
©Ò¥H³o¬O¤£¬Oªí¥Ü§ÚÁÙ¬O¦³¼ç¦b¨S¦³¨Ï¥Îªº¥¨¶°¨S¦³§R°£¨ì¡H
§Ú­n«ç»òª¾¹D¬O­þ¨Ç¡H

TOP

¦^´_ 6# jochun
½M¤lºN¶H¡A¶}±ÒÀÉ®×·|°Ý¬O§_¶}±Ò¥¨¶°¡Aªí¥ÜÀɮפºÁÙ¦s¦bµ{¦¡½X¨S¿ù
§Aªº°ÝÃD¦b¨S¦³Àɮתº±¡ªp¤U¡AÀ³¸Ó¬OµLªk±o¨ì¸Ñµª(´Nºâ¦³Àɮ׳£¤£¤@©w¯à°÷§ïµ½³t«×)
«Øij±NÀÉ®×À£ÁY¤W¶Ç¡A¤~¯à¹ï§Aªº°ÝÃD¦³©ÒÀ°§U
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh

§Ú¤@ª½·QµÛ¤£¯à¤W¶ÇEXCEL
§Ñ¤F¤]¥i¥H§Ë¦¨À£ÁYÀÉ^^"
§Ú²{¦b¦b°µ¨â­ÓÀÉ®×
ªþ¥óTESTªº°ÝÃD¬O¶}±Ò®É·|¥X²{¥¨¶°ªº°ÝÃD
ACCªº³o­ÓÀɮ׬O«ÜºC

ªá¤F¤@­Ó¤U¤È+¤@­Ó±ß¤W
¨Ì·Ó±zªº»¡©ú¡A§Ú²×©ó§â¤@¨Ç½ÆÂøªº¤½¦¡³£§R§¹¤F
©Ò¥H­«ºâÀx¦s®æ«ÜºCªº°ÝÃD¤]¸Ñ¨M¤F
¦ý¬OACCªº³o­ÓÀÉ®×ÁÙ¬O«D±`«D±`ºC
¨ä¹ê³o­Ó°ÝÃD¦s¦b¤F¤@¦~¦h¤F
¤]¦]¬°¦p¦¹§Ú³£¤£·Q¶}±Ò³o­ÓÀÉ®×
¤§«e¨SªÅ³B²z¡A²{¦b¤~¦³¤@ÂI®É¶¡·Q¦n¦nªº¸Ñ¨M¥¦
§Ú²q·Q¦³¨â­Ó­ì¦]¡G
1. ¸ê®Æµ§¼Æ¤Ó¦h¡H¡Ð¡Ð¦pªG¬O³o­Ó°ÝÃD«Ü³Â·Ð¡A¦]¬°§Ú¤£·Q¤À¤u§@ªí¨Ó°µ¡A³o¼Ë§Ú¦b°µ¸ê®Æ«Ü¤£¦n°µ
2. §Ú¦³¨Ï¥Î¡u³]©w®æ¦¡¤Æ±ø¥ó¡vªº¥\¯à¡H¡Ð¡Ð¦pªG¬O³o­Ó°ÝÃD¡A¬O§_¦³¨ä¥L¥\¯à¥i¥H´À¥N¡H

TEST.rar (7.08 KB)

¦³¥¨¶°

ACC.rar (43.45 KB)

³t«×«ÜºC

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-4-21 23:10 ½s¿è

¦^´_ 8# jochun
¥¨¶°¦s¦bªº°ÝÃD¦p°Êµe±N©Ò¦³¼Ò²Õ§R°£¤~¬O¤£§t¥ô¦ó¥¨¶°
play.gif
ACC.xls³t«×ºC¬O¦]¬°§Aªº©w¸q¦WºÙ«Ü¦h¡A³o¨Ç©w¸q¦pªG¥H§A¥Ø«eÀɮפº®e®Ú¥»³£¥Î¤£¤W
¦]¬°§AªºÀÉ®×¥u¬O¸ê®ÆªºÀx¦s¨ÃµL¥ô¦ó­pºâ
§â©w¸q¦WºÙ¥þ³¡§R°£À³¸Ó´N¤£·|©ìºC³t«×¤F
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


    ÁÂÁ§A
§Ú­è­è§âµL¥Îªº©w¸q³£§R°£¥u³Ñ¤U¤T²Õ¡Gsend¡þ¶µ¥Ø¡þÃþ§O
ÁÙ¬O¤@¼Ë¡C¡C¡C
¬O¦]¬°¡u¶µ¥Ø¡þÃþ§O¡v³o¨â­Óªº©w¸q¤Ó½ÆÂø¶Ü¡H

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD