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

[¤À¨É] ©w¸q¦WºÙªº°g«ä

[¤À¨É] ©w¸q¦WºÙªº°g«ä

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-9-3 21:19 ½s¿è

ªñ¨Óµo²{«Ü¦h°ÝÃD¦b©ó©w¸q¦WºÙªºÆ[©À
©w¸q¦WºÙªº¥Î³~«Ü¼s¡AÁ|¤Z¤½¦¡ªºÂ²¤Æ¡B¸ó¤u§@ªíÅçÃÒ¡B±ø¥ó®æ¦¡¤Æ¡B°ÊºA½d³òªº¨ú±oµ¥µ¥
¥Î¨ì©w¸q¦WºÙªº¦a¤è«Ü¼s¡A¦ý¬O¦b§@©w¸q¦WºÙ®É¸g±`µLªk¹F¨ì¦Û¤v·Q­nªºµª®×¡C
¦b¦¹´N¥»¤H¾Ç²ß¸gÅç»P¤j®a¤À¨É¡A­Y¦³¿ù»~©Î¤£¨¬¤§³BÁٽЦU¦ì«e½ú¤£§[«ü±Ð¡A§ó§Æ±æ¯à©ß¿j¤Þ¥É¡A
½Ð¦U¦ì¥ý¶i´£¥X¸gÅç»P¤j®a¤À¨É¡C
¾Ç²ß©w¸q¦WºÙ¡AÀx¦s®æ°Ñ·ÓªºÆ[©À¥²¶·½T¹ê«Ø¥ß¡C
¬Æ»ò¬O°Ñ·Ó?°Ñ·Ó²³æªº»¡´N¬OÀx¦s®æ»PÀx¦s®æ¤§¶¡ªº¹ïÀ³Ãö«Y
EXCEL»¡©ú¤¤¦³¤@¬qµ²ºc¤Æ°Ñ·Óªº¤¸¥ó
¦pªþ¥ó°Ñ·Ó.doc °Ñ·Ó.rar (43.48 KB)
³o¬q»¡©úÄÄ­z¤u§@ªíªº¸ê®Æªí¦U­Ó¤¸¥ó·N¸q(³o³¡¤À½Ð¦Û¦æ¤U¸üÀɮשÎEXCEL»¡©ú¥HÃöÁä¦r"°Ñ·Ó"·j´M)
¤º¦³Â²³æ½d¨Ò½Ð¾\Ū«á­Y¦³¤£©ú¤§³B¦A´£¥X°Q½×
»{ÃѸê®Æªíµ²ºc«á¡A§Ú­Ì´N¶}©l¨Ó°Q½×µ´¹ï°Ñ·Ó»P¬Û¹ï°Ñ·Ó
³o¦b¤@¯ë³]©w¤½¦¡©Î©w¸q¦WºÙ®É¬O·¥¬°­«­nªºÆ[©À
¬Û«H¤j®a³£ª¾¹D¦b¤½¦¡¿é¤J®É­Y¥[¤J$³o®É¦¹Àx¦s®æ¦ì§}§Y¬°µ´¹ï°Ñ·Ó¦ì§}
¦p¦bB1¿é¤J¤½¦¡=$A$1
·í§A±NB1¤½¦¡¦V¤U©ì¦²½Æ»s®É¡A§A·|µo²{B2¡AB3¡AB4....±o¨ìªº¤½¦¡¤@¼Ë¬O=$A$1
³o´N¬O»¡¦bBÄ椽¦¡¤¤¦¹A1¬O¤£ÀHµÛBÄæ¦C¦ì§ïÅܦӧïÅÜ¡A¥L©l²×³£·|±o¨ìA1Àx¦s®æªº­È
­Y¦bB1¿é¤J¤½¦¡=$A1¦V¤U©ì¦²½Æ»s®É¡A§A·|µo²{B2¡AB3¡AB4....±o¨ìªº¤½¦¡¤@¼Ë¬O=$A2¡A=$A3¡A=$A4....
¤]´N¬O»¡ÀHµÛ¦C¦ìªº¼W¥[¨ä°Ñ·Ó¨ìAÄ檺¦C¦ì¤]¸òµÛ¼W¥[

­Y¦bB1¿é¤J¤½¦¡=$A1¦V¥k©ì¦²½Æ»s®É¡A§A·|µo²{B1¡AC1¡AD1....±o¨ìªº¤½¦¡¤@¼Ë¬O=$A1¡A=$A1¡A=$A1....
¤]´N¬O»¡ÀHµÛÄæ¦ìªº¼W¥[¨ä°Ñ·Ó¨ìAÄ檺Äæ¦ì¬O¤£Åܪº

©Ò¥H¥i¨£=$A1³o¤½¦¡¤£½×¦V¥k©Î¦V¤U½Æ»s¡A¹ï¨ìªºÄæ¦ì¬O¤£Åܪº¥Ã»·¬OAÄæ
¦ý¬O¦C¦ì·|¦]¬°¤½¦¡©Ò¦b¦ì¸m¦Ó§ïÅÜ¡A©Ò¥H$A1ªí¬O¹ïAÄæ¬Oµ´¹ï°Ñ·Ó¦ý¦C¦ì¦]¤½¦¡©Ò¦b¦ì¸m¤£¦P¦Ó§ïÅÜ©Ò¥H¦C¦ì¬O¬Û¹ï°Ñ·Ó

¦³¤F°Ñ·ÓªºÆ[©À¤Îµ²ºc¤Æ°Ñ·Ó¤¸¥óªº°ò¦¤F¸Ñ¡A¬Û«H¹ï©ó©w¸q¦WºÙªº·N¸q·|¦³¤@ÂI°ò¥»Æ[©À¤F¡A¸Û¦p¦Ñ®L»Pªü¦N©Ò»¡©w¸qºØÃþ¤Î¨Ï¥Î¹ï¶H¤£³ÓªTÁ|¡A¤p§Ì©Ò¾Ç¦³­­´N°w¹ï¥Ø«e±`¥Î¨ìªºÀx¦s®æª«¥óªº©w¸q¥[¥H»¡©ú¡A¦]¬°³oÃþªº©w¸q¥Ø«e¦b¨Ï¥Î¤W³Ì¬°¼sªx¡A¥Ñ¨ä¬O¥Î¦bÅçÃÒ¥H¤Î±ø¥ó®æ¦¡¤Æªº³]©w·¥¨ä­«­n¡A¦]¬°ÅçÃÒ¤½¦¡¤Î±ø¥ó®æ¦¡¤Æ¤½¦¡¬O¤£¤¹³\¸ó¤u§@ªí°Ñ·Óªº¡A©Ò¥H¦b¤U³o¨Ç¤½¦¡®É¬°¤F­n°Ñ·Ó¤£¦P¤u§@ªí®É´N¥²¶·¨Ï¥Î©w¸q¦WºÙ¨Ó¹F¦¨¡A¦³®É­ÔÅçÃÒ±ø¥ó¤Î®æ¦¡±ø¥ó¤S¬O¤@½d³òÀx¦s®æ¡A¨ú¨M©ó¥t¤@½d³òÀx¦s®æªº­pºâµ²ªG¨Ó§PÂ_¨äÅÞ¿è­È¡A³o®Éªº°Ñ·Ó¨ä¹ê¬OÅܰʪº¡A¥H¤U´N¥H´X­Ó¹ê¨Ò¨Ó¸ÑÄÀ©w¸q¦WºÙ¤¤ªº°Ñ·ÓÃö«Y¡A¥H¤è«K¤j®a¹ïÅçÃÒ¤½¦¡»P®æ¦¡±ø¥ó¤½¦¡ªº¤F¸Ñ¡C
¥H³Ì±`³Q°Ý¨ìªº¤G¶¥¼hÅçÃÒ¿ï³æ¬°¨Ò:
§Ú¦bSheet1ªºAÄæ¿é¤J²Ä¤@¶¥¼h²M³æ¤º®eA1:A3¤À§O¿é¤JA¡AB¡AC¨Ã©w¸q¸Ó½d³ò¦WºÙ¬°List_1¡A°µ¬°²Ä¤G¼h²M³æªº¨Ì¾Ú
B1:D1¿é¤J¥Ò        ¤A        ¤þ
B2:E2¿é¤J¤l        ¤¡        ±G        ¥f
B3:F3¿é¤Jª÷        »È        »É        ÅK        ¿ü
°µ¬°²Ä¤G¼h²M³æ
H1°µ¬°¿é¤J²Ä¤@¼h²M³æªº¦ì¸m¡A¨äÅçÃÒ²M³æ¤½¦¡´N¬O=List_1
I1°µ¬°¿é¤J²Ä¤G¼h²M³æªº¦ì¸m¡A¦]¬°I1ªº²M³æ¨Ó·½§Ú­Ì¬O­n¨Ì¾ÚH1©Ò¿ï¤º®e¨Ó°Ñ·Ó¨ìA1:F3¡Aªº­þ¤@¦C¸ê®Æ°µ¬°²M³æ¤º®e©Ò¥H³o®É­Ô¦pªG­n§â¤½¦¡¤@¦¸¼g¦bI1ªºÅçÃÒ²M³æ¤½¦¡´N¦¨¤F
=OFFSET($A$1,MATCH($H$1,$A:$A,0)-1,1,,COUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0))))
³o¼Ë¤¾ªøªº¤½¦¡¤é«á­n­×¥¿©Î¾\Ū·|¥O¤H²´ªá¼º¶Ã¡A³o®É­Ô§Ú­Ì¦pªG§â³o¤½¦¡¤À¸Ñ¦¨´X³¡¤À¡A±N³o´X³¡¥÷³£©w¸q¦¨¤@­Ó¦WºÙ¡A¨º»ò¤é«á§Ú­Ì­n­×¥¿©Î¾\Ū®É´N»´¦Ó©öÁ|¤F¡A¦Ó°_³o¼Ëªº¤½¦¡¦pªG­n¦b¨ä¥L¤u§@ªí·í¦¨ÅçÃÒ²M³æ¤½¦¡®É¡A±N·|¥X²{¤£±o°Ñ·Ó¨ä¥L¤u§@ªíªº¿ù»~¡A©Ò¥H©w¸q¦WºÙ¬O¥²¶·¥B¹ê¥Îªº¡A²{¦b§Ú­Ì¥ý¥H³æ¬°H1°Ñ·Ó©Ò°µªºIÄæ²M³æ¤º®e¤½¦¡§@©w¸q¡A¤]´N¬O»¡¦bIÄæ©Ò¦³¦s¦s®æ³£¬O¨Ì·ÓH1¤º®e°µ¬°°Ñ¦Ò¡A¤£¦]¦C¦ì¤£¦P¦Ó°Ñ·Ó¤£¦PªºHÄæ¦ìÀx¦s®æ¡A­º¥ý§Ú­Ì·Q¨ìªº¬O:²M³æ½d³ò¬O¤@Àx¦s®æ½d³ò¡A¨º»ò¦³­þ¨Ç¨ç¼Æªº¶Ç¦^­È¬OÀx¦s®æ½d³ò©O?²Ä¤@­Óª½±µ·Q¨ìªº´N¬OINDIRECT©MOFFSET³o2­Ó¨ç¼Æ¡A¨º»ò³o2­Ó¨ç¼Æ­n¶Ç¦^½d³ò®É´N¥²¶·±o¨ì°ò·ÇÀx¦s®æ¡B¦ì²¾¡BÄæ¦C¤j¤p³o3­Ó­«­nªº°Ñ¼Æ­È¡A©Ò¥H§Ú­Ì´N·Q¿ìªk§â¤W­zªº¤½¦¡¤À¸Ñ¥X§Ú­Ì·Q­nªº­È°µ¦¨©w¸q,¦A±N°Ñ¼Æ±a¤J¨ç¼Æ§Y¥i±o¨ì§Ú­Ì·Q­nªºªF¦è¤F
¥H¤W¤½¦¡¬O§Ú·Q¨ì¥ÎOFFSET¨ç¼Æ¨Ó¶Ç¦^¨ä½d³ò¡A
OFFSET(reference,rows,cols,height,width)¶Ç¦^¤@Àx¦s®æ½d³ò
reference´N¬O°ò·ÇÂI
rows´N¬O¦ì²¾¦C¼Æ
cols´N¬O¦ì²¾Äæ¼Æ
height´N¬O½d³òÁ`¦C¼Æ
width´N¬O½d³òÁ`Äæ¼Æ
©Ò¥H
=OFFSET($A$1,MATCH($H$1,$A:$A,0)-1,1,,COUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0))))¤¤
reference´N¬O$A$1
rows´N¬OMATCH($H$1,$A:$A,0)-1
cols´N¬O1
height¬Ù²¤¥Nªí¹w³]­È1
width´N¬OCOUNTA(INDIRECT("B"&MATCH($H$1,$A:$A,0)&":F"&MATCH($H$1,$A:$A,0)))
·í¤¤rows¸òwidthªº¤½¦¡¤¤¦³¤@­Ó¬Û¦Pªº¦a¤èMATCH($H$1,$A:$A,0)
³o´N¬O¦bAÄ椤§ä¨ìH1ªº¦ì¸m
¨º»ò§Ú­Ì´N§â³o¦@¦Pªº¤½¦¡©w¸q¤@­Ó¦WºÙ
©Ò¥Hrr=MATCH(Sheet1!$H$1,Sheet1!$A:$A,0)
§A¦b¥ô¤@Àx¦s®æ¿é¤J¤½¦¡=rr±o¨ìªº­È±N¬OH1¦bAÄ椤ªº¦C¸¹
©Ò¥H¦A§ârr±a¤JOFFSETªº¤½¦¡
´N±o¨ì=OFFSET($A$1,rr-1,1,,COUNTA(INDIRECT("B"&rr&":F"&rr)))
§â³o¤½¦¡©w¸q¦WºÙ¬°List
§A±N·|µo²{¤£ºÞ§A¦bSheet1ªº­þ¤@­ÓÀx¦s®æ°µÅçÃÒ²M³æ¤½¦¡=List
¥L³£±N·|¬O¥HSheet1ªºH1°µ§PÂ_¨Ì¾Ú¨Ó§ä¨ì²Ä´X¦C°µ¬°²M³æ¤º®e¡A
¦pÀɮפG¶¥¿ï³æ_1 ¤G¶¥¿ï³æ_1.rar (143.53 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

¥»©«³Ì«á¥Ñ HUNGCHILIN ©ó 2010-8-10 00:01 ½s¿è

¦Ó©w¸q¥i¤À¦¨
ÁôÂûP«DÁôÂÃ
¨Ì¶}±Ò»P¨Ï¥ÎSHEETÁÙ¥i¦A¤À¦¨¦Û°Ê±Ò¥Î»P«D¦Û°Ê±Ò¥Î¨âºØ
¨Ì¨Ï¥Î¤¤SHEETÁÙ¥i¦A¤À¦¨¨p¥Î»P¤½¥Î
¨Ì¨Ï¥Î¤¤°Ï°ìÁÙ¥i¦A¤À¦¨µ´¹ï¬Û¹ï»P¦@¥Î¤TºØ

¦U¤è­±³£¥i¦A§ó²`¤J¬ã¨s
·|¦³§ó¤jªº¦¬Àò

¤W­z³o¤å¦bºë­^µo¹L¤@¨Ç
¦b4.0§»ªí±M©«#20©«¤¤
http://www.officefans.net/cdb/viewthread.php?tid=59862&extra=&page=1
¥H«e4.0ªí¹ï©w¸q«D±`¨Ì¿à

TOP

Åý¦WºÙªð¦^·í«e¤u§@ªíªº¤@­Ó§Þ¥©

¥»©«³Ì«á¥Ñ et5323 ©ó 2010-9-20 16:57 ½s¿è

¦bSheet1ùØ©w¸q¤@­Ó¦WºÙ¡Grng¡A¤Þ¥Î¦ì¸m¡G= $A$1¡A¨º麽¦b§Oªº¤u§@ªíùبϥγo­Ó¦WºÙ¡G=rng¡Aªð¦^ªºÁÙ¬OSheet1ùØA1ªº­È¡A¦Ó¤£¬O·í«e¤u§@ªíùØA1ªº­È¡C³oùئ³­Ó§Þ¥©¡G¤Þ¥Î¦ì¸m¡G= !$A$1¡A§Y¦b«e­±¥[­Ó¡§!¡¨¡A«h¦WºÙªð¦^ªº¬O·í«e¤u§@ªíùØA1ªº­È¡C¦³¨â­Ó°ÝÃD­nª`·N¡G
1¡D³oºØ¤è¦¡¤£¯à¥Î¦b¤u§@ªíªº¤½¦¡ùØ­±¡A¥u¯à¥Î¦b¦WºÙùØ¡F
2¡D¨ã¤S¡§©ö¥¢©Ê¡¨¡A§YA1¦³ÅܤơA¦WºÙrng¤£·|¤Ï¬M¥X¨Ó¡C¸Ñ¨M¿ìªk¥[T(NOW())¡G=!$A$1&T(NOW())¡A³o®Érngªð¦^ªº¬O¦r²Å¦ê¡A­Y­nÂà´«¬°¼Æ­È¡G= Value(!$A$1&T(NOW()))

Book1.rar (4.34 KB)

1

µû¤À¤H¼Æ

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-9-23 13:47 ½s¿è

¦^´_ 1# et5323
³o´N¬O©Ò¿×ªº¤½¥Î©w¸q
«Ü¦nªº¤ß±o¤À¨É
¥Ît(now())¨Ï¦¨¬°©ö¥¢©Ê¤½¦¡
³o©Û¥Î±o«Ü§®
·PÁ¤À¨É
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

«¢«¢¡A¨ü±Ð¤F¡I

TOP

¦n§Þ¥©
¦hÁ et5323 ¥S´£¨Ñ¤À¨É !!!
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¾Ç²ß¤F
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

«Ü¤£¿ùªºidea
³o½g³Ì¦³»ù­Èªº³¡¥÷¦b T(NOW()) ³o­Ó¨ç¼ÆÀ³¥Î¦b©w¸q
ÁÂÁÂ

TOP

¬Ý°_¨Ó¦ü¥G¦³ÂI½ÆÂø...
¦ý¬O¤S¦h¾Ç²ß¨ì¤F·sªºªF¦è

TOP

§¹¥þ¤£À´¡A§V¤O¾Ç²ß¤¤~~~

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD