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

[µo°Ý] ¦p¦ó­pºâ¯B°Ê¦C¼Æ½d³òªº¥­§¡­È

[µo°Ý] ¦p¦ó­pºâ¯B°Ê¦C¼Æ½d³òªº¥­§¡­È

¥»©«³Ì«á¥Ñ jsleee ©ó 2013-1-15 21:59 ½s¿è

½Ð±Ð¥ý¶i
    ¦pªG§Úªº¤u§@ªí¦pªþ¥ó©Ò¥Ü¡A
    ²Ä¤@Ä欰¶µ¥Ø¡A²Ä¤GÄ榳¼Æ­È¡A§Ú·Q­n¦b²Ä¤TÄæ¶À¦â°Ï°ì
    ®I¤J¤½¦¡­pºâ²Ä¤GÄ檺¼Æ­Èªº¥­§¡
    ¦ý ¨C­Ó¶µ¥Øªº¦C¼Æ¥i¯à¨C¤@¦¸³£¤£¤@¼Ë...
    ´«¥y¸Ü»¡¡A¥Ø«eAAªº¦C¼Æ¦³4¦C¡A
    ¦ý¤U¤@¦¸AA¥i¯àÅܦ¨6¦C©Î¨ä¥Lªº¦C¼Æ
    ¨ä¥L¶µ¥Ø¤]¬O¤@¼Ë¡A¦C¼Æ³£·|¯B°Ê
    ¤£ª¾¹D³o¼Ëªº¨ç¼Æ¦p¦ó¼g¡HÁÙ¬O¥u¯à¥ÎVBA¡H
    §Úªº§xÂZ¬O ¤£ª¾¥Î¬Æ»ò¨ç¼Æ¨Ó¾÷°Ê§PÂ_ ©Ò»Ýªº½d³ò¦C¼Æ...
    ³Â·Ð ¥ý¶i­Ì«üÂI°g¬z¡AÁÂÁÂ

JS

¯B°Ê¦C¼Æ­pºâ.rar (5.92 KB)

j14
=IF(C14="","",PRODUCT(OFFSET(I14,,,IF(ISNA(MATCH("*",C15:C$99,)),COUNT(E14:E$99),MATCH("*",C15:C$99,)))))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 14# zz5151353

·PÁ zz¤j¤jªº¨ó§U¡A§Ú¦A¦n¦n¬ã¨s¬ã¨s

JS

TOP

¤½¦¡¤w³]°ÊºA¤F,¥t¥~¼W¥[¤@ VBA  ³t«×¸ò®Ä²v³£¥þ­±´£¤É,½Ð°Ñ¦Òªþ¤WªºÀÉ®×

¯B°Ê¦C¼Æ­pºâ.rar (9.28 KB)

300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 12# Hsieh

·PÁ ¶W¯Åª©¥D ´£¨Ñªº­×¥¿
¤w­×¥¿¨Ã´ú¸Õ¥¿±`¡A·P®¦

JS

TOP

¦^´_ 11# jsleee
y¤½¦¡ =OFFSET(´ú¸Õ!$C$14,,,COUNTA(´ú¸Õ!$E$14:$E$65536)+1,)
¦C¦ìÀ³¸Ó­n¥Îµ´¹ï°Ñ·Ó¡A§_«h·í¦WºÙ¨Ï¥Î©ó¤½¦¡¤º®É
·|¦]¬°¨î§ïÅܦӧïÅÜ­pºâªº°_©l¦ì¸m
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 10# Hsieh

ÁÂÁ ¶W¯Åª©¥D ªº«üÂI
§Ú¸ÕµÛ¨Ì·Ó ¶W¯Åª©¥D ªº«ü¥Ü¶i¦æ x©w¸q½d³ò¤½¦¡ªº­×§ï¡Aµ²ªGÁÙ¬O¦³°ÝÃD
±µµÛ§Úµo²{ ¥u­n±N yªº©w¸q½d³ò¤½¦¡­×§ï¡A·f°t ¶W¯Åª©¥D ©Ò´£¨Ñªº x ­×§ï¡Aµ²ªG´NOK
­×§ï«e y¤½¦¡ =OFFSET(´ú¸Õ!$C$14,,,COUNTA(´ú¸Õ!$E14:$E$65536)+1,)
­×§ï«á y¤½¦¡ =OFFSET(´ú¸Õ!$C$14,,,COUNTA(´ú¸Õ!$E14:$E$65536)+ROW(´ú¸Õ!E14),)

¦]¬°¹ï¤½¦¡¨Ã¨S¦³§¹¥þ¤F¸Ñ³z´ú¡A©Ò¥H¤]¤£¤Óª¾¨ä©Ò¥HµM
ªþ¤W­×§ï«áªº´ú¸Õµ²ªG¥¿±`ÀɮסA¦pªG¦³¥ô¦ó¸É¥R»¡©ú¡A½Ð¤£§[´£¥X¡AÁÂÁÂ

JS

´ú¸Õ2.zip (30.11 KB)

TOP

¦^´_ 9# jsleee
¦]¬°¸ê®Æ½d³ò¨Ã«D±qE1¶}©l
©Ò¥HROWS(y)¨Ã¤£·|¬O50
©w¸q¦WºÙx¤½¦¡§ï¬°
=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=ROWS(y)+ROW(y)-1),ROW(y),""))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh

«D±`ÁÂÁ ¶W¯Åª©¥D ­@¤ßªº»¡©ú¡AµL©` ·M¶wªº§ÚÁÙ¬O¤@ª¾¥b¸Ñ
§Ú¹Á¸Õ®M¥Î³o¼Ëªº§@ªk¦bªþ¥óªº ´ú¸Õ Àɮפ¤
¦ý¥u¦³´X¦Cªº¼Æ¾Ú¬O¥¿½Tªº¡A¤§«áªº¼Æ¾Ú´N¥X°ÝÃD...

ªþ¥ó¤¤ ªº JÄæ ¬O§Ú­nªºµ²ªG¡A
                  JÄæ=IÄæ¼Æ­È¬Û­¼ªºµ²ªG¡A¬Û­¼ªº¨Ì¾Ú¬O®Ú¾Ú CÄ檺¶µ¥Ø
                  KÄæ «h¬OÅçºâ
¨ä¤¤ ²Ä14¦C~30¦C µ²ªG³£ÁÙ¥¿±`¡A¦ý±q 31¦C¶}©l¡A¼Æ¾Ú´N¥X²{°ÝÃD...

¯à§_¦b³Ò·Ð ¶W¯Åª©¥D ¨ó§U½T»{¤@¤U§Úªº°ÝÃD¬O¥X¦b­þ¸Ì¡H
·P¿E¤£ºÉ

JS

´ú¸Õ.zip (20.38 KB)

TOP

¦^´_ 7# jsleee
½d¨Òªº°µªk¬°°t¦X¸ê®Æ¶qÅÜ°Ê
x¦WºÙªº¤½¦¡§ï¬°=FREQUENCY(ROW(y),IF((y<>"")+(ROW(y)=ROWS(y)),ROW(y),""))

x¦WºÙªº¥Î·N¦b¶Ç¦^¨C­Ó¤ÀÃþ¸ê®Æ¶q
¥HÀɮ׸ê®Æ¨Ó»¡¡A·|¶Ç¦^°}¦C{1,4,2,6,3,5}
©Ò¥H­Y¥HAÄ欰«DªÅ®æªº¬Û¹ïBÄæ¦ì¸m¡A¦V¤UÂX®iªº¸ê®Æ§@¥­§¡´N¬O¦U¤À²Õªº¥­§¡

¥HC1¦Ó¨¥¡AA1¶µ¥Øªº¸ê®Æ¶q¬Oxªº²Ä2­Ó­È=4
©Ò¥HOFFSET(B1,,,INDEX(x,COUNTA($A$1:A1)+1))
¤¤½d³òªº¦C¼Æ¬OINDEX(x,COUNTA($A$1:A1)+1)
´N¬O­pºâAÄæ«DªÅ®æªº¼Æ¶q¥[1¨Ó«ü©wx°}¦C¤¤ªº§Ç¦C¼Æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD