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

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

¦^´_ 10# Hsieh


    Dear ¤j¤j¡G

ÁÂÁ±z¦^µª¡A¤£¹L¦]¬°§Ú·Q¦h¤F¸Ñ±zªº¤½¦¡(Åý¦Û¤v¦h¾Ç²ß)¡A©Ò¥H¤S°µ´ú¸Õ¡A±N¸ê®Æ­×§ï¦pªþÀÉ¡A¤@¼Ë¥N¤J¤j¤j´£¨Ñªº¤½¦¡¨£¸Ñ¡C
¦ýµo²{·|¥X²{¿ù»~­È¡C©Ò¥H·Q°Ý¦pªG­×§ï¦¨ªþÀÉ¡A¨º¤S¸Ó¦p¦ó§ï¤½¦¡©O¡H
¥Ø«e¤£À´¦¹
t=SMALL(IF(ISNUMBER(FIND("a. Dependent Variable: ",­ì©l¸ê®Æ!$A$1:$A$16854)),ROW(¥N¤½¦¡!$1:$16854),""),COLUMN(¥N¤½¦¡!A$1))

¦Ó¥B¦ì¬°¦ó¬O ­ì©l¸ê®Æ!$A$1:$A$16854)),ROW(¥N¤½¦¡!$1:$16854  ¤£À´¬°¤°»ò¨ì 16854©O¡H


¦A¦¸«ôÁ»P½ç±Ð

example0413.rar (56.99 KB)

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

¦^´_ 12# Hsieh


    Dear ¤j¤j¡G

«D±`·PÁ§Aªº¸Ô²ÓÁ¿¸Ñ¡A·|¦A§V¤O¬ã¨s¬ÛÃöÀ³¥Î¡C

¦A¦¸«ôÁ»P·P®¦

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD