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

[µo°Ý] ¸ê®Æ³B²z°ÝÃD¡A¨D§U¦U¦ì¥ý¶i­Ìªº½ç±Ð

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-12 00:31 ½s¿è

¦^´_ 1# jj369963

³o°ÝÃD­«ÂI¦b©ó§ä¥X½d³ò
­º¥ý­n¥ý§â§Aªº¥N¤½¦¡¤u§@ªí²Ä¤@¦Cªº¸ê®Æ°µ¤@¤U§R°£¤£¥i¨£¦r¤¸
1.¿ï¨ú²Ä¤@¦C
2.½s¿è/´M§ä¨ú¥N
3.¦b´M§ä¥Ø¼Ð¤¤¥Halt+¼Æ¦rÁä32¿é¤J¬°´M§ä¥Ø¼Ð
4.«ö¤U¥þ³¡¨ú¥N
±µ¤U¨Ó©w¸q4­Ó¦WºÙ
x=MATCH("a. Dependent Variable: "&¥N¤½¦¡!A$1,­ì©l¸ê®Æ!$A:$A,0)
y=MATCH("b. Dependent Variable: "&¥N¤½¦¡!A$1,­ì©l¸ê®Æ!$A:$A,0)
k=x-y+1
rng=OFFSET(­ì©l¸ê®Æ!$A$1,y+4,1,k-6,3)
§¹¦¨³]¸m³o4­Ó¦WºÙ«á
¥N¤½¦¡!C2¤½¦¡
=VLOOKUP($B2,rng,2,0)
¦V¤U¦V¥k½Æ»s
«D¼Ð·Ç¤Æ!D4¤½¦¡
=IF(ISERROR(MATCH($C4,¥N¤½¦¡!$1:$1,0)),"",INDEX(¥N¤½¦¡!$A$1:$AI$91,MATCH(«D¼Ð·Ç¤Æ!D$2,¥N¤½¦¡!$B:$B,0),MATCH($C4,¥N¤½¦¡!$1:$1,0)))
¦V¤U¦V¥k½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# jj369963


   ¦b©w¸q4­Ó¦WºÙ®É§@¥ÎÀx¦s®æ¥²¶·¿ï¨ú¤½¦¡¤Æ!C2Àx¦s®æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-11 23:48 ½s¿è

¦^´_ 5# jj369963

ºâ±ø¥ó¥­§¡­È¡A2003ª©¥»¤½¦¡­nªø¤@ÂI
¥N¤½¦¡!C2¤½¦¡
=SUMPRODUCT((INDEX(rng,,1)=$B2)*(INDEX(rng,,2)))/SUMPRODUCT((INDEX(rng,,1)=$B2)*1)

example0409§ó·s.rar (817.71 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# jj369963

¥N¤½¦¡!C2
=SUMIF(INDEX(rng,,1),$B2,INDEX(rng,,2))
«D¼Ð·Ç¤Æ!D4(¼Ó¤W¿ù»~¤w§ó¥¿)
=IF(ISERROR(MATCH($C4,¥N¤½¦¡!$1:$1,0)),"",INDEX(¥N¤½¦¡!$A$1:$AI$91,MATCH(«D¼Ð·Ç¤Æ!D$2,¥N¤½¦¡!$B:$B,0),MATCH($C4,¥N¤½¦¡!$1:$1,0)))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# jj369963


    ¿ï¨ú¥N¤½¦¡!C1·s¼W
©w¸q¦WºÙt
=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",­ì©l¸ê®Æ!$A$1:$A$16854)),ROW(¥N¤½¦¡!$1:$16854),""),COLUMN(¥N¤½¦¡!A$1))
©w¸q¦WºÙs
=SUMPRODUCT(ISNUMBER(FIND("a. Dependent Variable: ",­ì©l¸ê®Æ!$A$1:$A$16854))*1)
©w¸q¦WºÙx
=MATCH("a. Dependent Variable: "&¥N¤½¦¡!C$1,­ì©l¸ê®Æ!$A:$A,0)
©w¸q¦WºÙy
=MATCH("b. Dependent Variable: "&¥N¤½¦¡!C$1,­ì©l¸ê®Æ!$A:$A,0)
©w¸q¦WºÙk
=x-y+1
©w¸q¦WºÙrng
=OFFSET(­ì©l¸ê®Æ!$A$1,y+4,1,k-6,3)

******¥H¤U¬°¤½¦¡******

¥N¤½¦¡!C1¤½¦¡
=IF(COLUMN(A$1)>s,"",SUBSTITUTE(INDIRECT("­ì©l¸ê®Æ!A"&t),"a. Dependent Variable: ",""))
¦V¥k½Æ»s

¥N¤½¦¡!C2¤½¦¡
=SUMIF(INDEX(rng,,1),$B2,INDEX(rng,,2))
¦V¥k¦V¤U½Æ»s

«D¼Ð·Ç¤Æ!D4¤½¦¡
=IF(ISERROR(MATCH($C4,¥N¤½¦¡!$1:$1,0)),"",INDEX(¥N¤½¦¡!$A$1:$AI$91,MATCH(«D¼Ð·Ç¤Æ!D$2,¥N¤½¦¡!$B:$B,0),MATCH($C4,¥N¤½¦¡!$1:$1,0)))
¦V¥k¦V¤U½Æ»s
example0412-1§ó·s.zip (1.9 MB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-13 22:27 ½s¿è

¦^´_ 11# jj369963

16854¬O¦]¬°§A­ì¨Óªº¸ê®Æ¥u¨ìA16854
­Y­n¸Ñ¨M¸ê®Æ¶qÅÜ°Ê¥i¼W¥[¤@­Ó§PÂ_¸ê®Æ§Àªº¦WºÙ
©w¸q¦WºÙ®É§@¥ÎÀx¦s®æªº¦ì¸m·|¼vÅT¨ì¬Û¹ï°Ñ·ÓªºÄæ¦C¦ì
©Ò¥H­n¯S§Oª`·N©Ò¦bÀx¦s®æ¦ì¸m
h¦WºÙ(¨ú±o­ì©l¸ê®ÆªºAÄæ¸ê®Æ§À)
=LOOKUP("ùÕ",­ì©l¸ê®Æ!$A$2:$A$65536,ROW(­ì©l¸ê®Æ!$2:$65536))
rng_1¦WºÙ(¨ú±o­ì©l¸ê®Æ½d³ò)
=INDIRECT("­ì©l¸ê®Æ!A1:A"&h)
s¦WºÙ(­pºâ­ì©l¸ê®Æ!AÄ椺¦³´X­Ó¥N¸¹)
=SUMPRODUCT(ISNUMBER(FIND("a. Dependent Variable: ",rng_1))*1)
*********
¿ï¨ú¥N¤½¦¡!B1(¦]¬°¦WºÙt­n±qBÄæ¦V¥k±q1¶}©l»¼¼W¡A©Ò¥HSMALLªº²Ä2¤Þ¼Æ¨Ï¥ÎCOLUMN(¥N¤½¦¡!A$1)³òÄæ¬Û¹ï¡B¦Cµ´¹ï°Ñ·Ó¡A¬G§@¥ÎÀx¦s®æ¥²¶·¦ì©óBÄæ)
t¦WºÙ(¨ú±o²Ä´X­Ó¥N½X©Ò¦b¦ì¸m)
=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",rng_1)),ROW(rng_1),""),COLUMN(¥N¤½¦¡!A$1))
x¦WºÙ(¥N½X¸ê®Æµ²§ôÂI)
=MATCH("a. Dependent Variable: "&¥N¤½¦¡!B$1,­ì©l¸ê®Æ!$A:$A,0)
y¦WºÙ(¥N½X¸ê®Æ°_ÂI)
=MATCH("b. Dependent Variable: "&¥N¤½¦¡!B$1,­ì©l¸ê®Æ!$A:$A,0)
k¦WºÙ(¥N½X½d³ò¦C­z)
=x-y+1
rng¦WºÙ(¥N½X¹ê»Ú¸ê®Æ½d³ò)
=OFFSET(­ì©l¸ê®Æ!$A$1,y+4,1,k-6,3)
******************
¥N¤½¦¡!B2¤½¦¡
=SUMIF(INDEX(rng,,1),$A2,INDEX(rng,,2))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD