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

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

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

¥»©«³Ì«á¥Ñ jj369963 ©ó 2012-4-10 09:23 ½s¿è

Dear¤j¤j¡G

¨Ó´M¨D¨ó§U»P¾Ç²ß¡C§Ú¦³¤@¨Ç¸ê®Æ¤£ª¾¦p¦ó³B²z¡A·Q½Ð±Ð¤j®a¡C¤£¦n·N«ä¡A§ÚªºÅÞ¿è»Pªí¹F¯à¤O¤£¦n¡A¦p¦³»¡©ú¤£¨Î¡AÅwªï¦^ÂСAÁÂÁ¡C
¨Ï¥Î¥ô¦ó¤èªk¬Ò¥i(¦]¬°§Ú¥Ø«e¤£ª¾¦p¦ó¾ã²z)¡A¦ý§Æ±æ¾ã²z¦¨"«D¼Ð·Ç¤Æ"sheet,¬ÛÃö»¡©ú¡A°Ñ¦ÒªþÀÉ¡A¤£ª¾¬O§_¸ê®Æ¦h¡A©Ò¥H¶}±Òexcel­nµ¥¤@¨Ç®É¶¡¡C

§Ú¥ý±q"­ì©l¸ê®Æsheet"¤@­Ó¤@­ÓºCºC§ä¥XÅܬÛ(¦C1)ªí®æªº¦ì¸m,¦A¥Î¤â°Ê¤è¦¡¡A±avlookup¹ïÀ³¼Æ­È¡A¦ý¬O³o¼Ë«ÜºC¡A¦]¬°­nºCºC¥Î¦×²´§äªí®æ¡C
¹ïÀ³§¹«á¡A¾ã²z¦¨¹³¤W­±¤@¼Ë¡A¤§«á¦AºCºC¥Î¦×²´§ä¥XÅܬۦì¸m¡A¥ÎÂà¸m¤èªk¶K¨ì"«D¼Ð·Ç¤Æ"sheet
¦]¬°¦³200¦h­Ó³o¼ËªºÀɮסA¥Î¦×²´´M§ä¡A¤S´î¤U¶K¤W¡A©ö¥X¿ù
©Ò¥H·Q½Ð±Ð¬O§_¥i¥H§Ö³tªº§â¸ê®Æ¾ã²z¦¨"«D¼Ð·Ç¤Æ"sheet

¥Ø«eªº°ÝÃD¬O¦p§Ö³t©w¦ì¥X­ì©l¸ê®Æ©Ò»Ý­nªºªí®æ
§Ú¥u»Ý­nCoefficientsaªí®æ¸ÌªºUnstandardized Coefficients¡A§Ú§â´X­Ó¥Î¶À©³¼Ð¥X¨Ó¡A¨Ãª¾¹D¥¦ªº±ø¥Ø¦WºÙ¡A¹³¬OA11_1_0¡A¨Ãª¾¹D¨ä©³¤Uªº¦¸Åܬ۪º«Dªí·Ç¤Æ«Y¼Æ¬O¦h¤Ö¡A¾ã²z¥X"«D¼Ð·Ç¤Æ"ªºsheet¡A¬O§Ú©Ò»Ý(¤£­­©w¥ô¦ó¤èªk)¡C

§Ñ°O»¡©ú¦pªG¦b¦P¤@±ø¥Ø·j´M¨ì¡A¦h­Ó¬Û¦PÅܬۡA¨ä¤À¼Æ¶·°µ¥­§¡

¦]¬°¬OEXCEl·s¤â¡A§Æ±æ¨D§U¤j¤j­Ì¡A¤]¥i¥H¶¶«K¾Ç²ß¡A´£¤É¦Û¤v¡A¦A¦¸«ôÁ¡C

example0409.rar (778.55 KB)

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ jj369963 ©ó 2012-4-10 01:09 ½s¿è

¦^´_ 2# Hsieh


    ¤£¦n·N«ä¤j¤j¡G

¥i¥H½Ð±z¥Ü½d¶Ü¡H

¥Ø«e¤£À´ªº¦a¤è¦³
2.©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)
¦]¬°§Ú¤£ª¾¹D©Ò¿×ªº©w¸q¦WºÙ¥Î³~¡A¤Î±q­þ­Ó¥\¯à¦C¥h°µ¡A­n¦b­þ¤@­ÓÀx¦s®æ°µ©O¡H

©Ò¥H¥i¥H¥Ü½d¡A©Î¬O¸ÑÄÀ¶Ü¡H¤£¦n·N«ä¦A¦h³Â·Ð¡A¤£¹L¤]§Æ±æ±q¦hµo°Ý¤Î¦h¾Þ§@¤W¥i¥HÅý¦Û¤v§ó¶i¨B¡A¦A¦¸·P¿E¡C

ªþÀɬ°¸g¹L¶Wª©ªº¨£¸Ñ©Ò°µ¡A¦ý¬O¥»¤H¥i¯à¸û²Â¡A©Ò¥H°µ¥X¨Ó¦ü¥G¤£¤@¼Ë¡A©Ò¥H·Q½Ð°Ý¬O­þ¨BÆJ¥X¿ù©O¡H¦A·Ð½Ð¦^µª¡AÁÂÁÂ

3.

example0409§ó·s.rar (818.2 KB)

TOP

¦^´_ 3# jj369963


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

TOP

¦^´_ 4# Hsieh


    ÁÂÁ¦^µª¡A§Ú¦A¸Õ¸Õ¡C¥t¥~¦]¬°§Ñ°O»¡©ú

§Ñ°O»¡©ú¦pªG¦b¦P¤@ªí®æ¸Ì¹ïÀ³ªº¡A±ø¥Ø¸Ì·j´M¨ì¡A¦h­Ó¬Û¦PÅܬۡA¨ä¤À¼Æ¶·°µ¥­§¡¡A³o­n«ç¼Ëµ²¦Xvlookup¤Îaverage©O¡H§Ú¤]¤£½T©w¦pªG·j¨ìvlookup¬O§_¥i¥H·j¨ì2­Ó¬Û¦P­È¡A¦]¬°ÅܬۢwS_17¸Ñ¨M½ÆÂø°ÝÃD¡A¦³2­Ó­È¡÷©Ò¥H¶··j´M¹ïÀ³ªºªº2­Ó¦b°µ¥­§¡

¦b³Â·Ð¤j¤j¡AÁÂÁÂ

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

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

¦^´_ 6# Hsieh

Dear ¶Wª©¤j¤j:

    ¤£¦n·N«ä¡AÁÂÁ±zªº¦^µª¡C¤£¹L¦]¬°°Q½×¹L«á¡AÅܦ¨­nºâ(½T©w³o¬O³Ì«á¤ÀªRµ¦²¤¤F¡A¦½ÃC)

¦b¦P¤@ªí®æ¸Ì¹ïÀ³ªº¡A±ø¥Ø¸Ì·j´M¨ì¡A¦h­Ó¬Û¦PÅܬۡA¨ä¤À¼Æ¶·°µ"¥[Á`"¡AÅܬۢwS_17¸Ñ¨M½ÆÂø°ÝÃD¡A¦³2­Ó­È¡÷©Ò¥H¶··j´M¹ïÀ³ªºªº2­Ó¦b°µ"¥[Á`" ©Ò¥H­É§U°Ñ¦Ò¶Wª©¤½¦¡¡A§ï¦¨ ¥N¤½¦¡!C2¤½¦¡=SUMPRODUCT((INDEX(rng,,1)=$B2)*(INDEX(rng,,2)))

¥t¥~¦³­ÓºÃ°Ý¡A¦pªþÀÉ«D¼Ð·Ç¤Æsheet¸Ì
A11_1_0¡BA11_2_0¡BA11_3_2¡BA12_1_1¡BA12_5_2¡BA12_5_3¡BA12_5_7¡BA12_5_8¡BA11_1¡BA11_2¬O¿ù»~­È¡A±qMÄæ A11_3¶}©l¤~¦³­È¡A¦ý¬OMÄ檺­ÈÀ³¸Ó¬OÄÝ©óAÄæ A11_1_0¡A¤]´N¬O¥¦¸õ±¼10­ÓÄæ¦ì¡A¦ý§Ú¤£ª¾¹D¬°¤°»ò¡C§Ú²q¬O¦brng¤Wªº³]©w¡A¦ý§Ú¬Ý¤£À´¨ä·N¸q(¦½ÃC§Ú¦Û¤v­n¦h¥[ªo)

¥t¥~½Ð°Ý¤j¤j¬O§_¥i¥Hµ¹¤©¬ÛÃö«ØijÃö©ó¾Ç²ß"©w¸q¦WºÙ"³o³¡¥÷¡A²`ı¨ä¥\¯à¹ê¦b±j¤j¡A¥»¥H¬°¥u¬O§@¦WºÙ½d³ò¤Wªº©R¦W¡A¦ý¥Î©ó¤½¦¡«o¤dÅܸU¤Æ¡C

¦¹¥~¦pªG¥i¯àªº¸Ü¡A¦pªG§Ú·Q±q°Ñ¦Ò¸ê®Æ¸Ìªºªí®æ¡A§ì¥X§Úªº±ø¥ØA11_1_0¡BA11_2_0¡BA11_3_2¡BA12_1_1¡BA12_5_2¡BA12_5_3¡BA12_5_7¡BA12_5_8¡BA11_1¡BA11_2¡A©ñ¸m¥N¤½¦¡sheetªº¦C1¡A¦Ó¤£¬O¦pÀɮפ¤¥Î¤âkey¡A¬O§_¦³¿ìªk©O¡H´N¨Æ¥ý©w¦ì¥X±ø¥Ø¦bªí®æªº¦ì¸m¡A¦A§ì¥X¨ä¤å¦r¡C
¦A¦¸«ôÁ¤ηP¿E¡C
³Â·Ð¤F

example0412§ó·s.rar (813.78 KB)

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

¦^´_ 8# Hsieh

Dear ¤j¤j¡G

¤£¦n·N«ä¡C§Ú»¡¿ù¤F¡A¯u¬O©êºp¡C¬O¦b¥N¤½¦¡sheet¦³¿ù»~

¦pªþÀÉ¥N¤½¦¡sheet
A11_1_0¡BA11_2_0¡BA11_3_2¡BA12_1_1¡BA12_5_2¡BA12_5_3¡BA12_5_7¡BA12_5_8¡BA11_1¡BA11_2¬O¿ù»~­È¡A±qMÄæ A11_3¶}©l¤~¦³­È¡A¦ý¬OMÄ檺­ÈÀ³¸Ó¬OÄÝ©óAÄæ A11_1_0¡A¤]´N¬O¥¦¸õ±¼10­ÓÄæ¦ì¡C


¦¹¥~¦pªG¥i¯àªº¸Ü¡A¦pªG§Ú·Q±q°Ñ¦Ò¸ê®Æ¸Ìªºªí®æ¡A§ì¥X§Úªº±ø¥ØA11_1_0¡BA11_2_0¡BA11_3_2¡BA12_1_1¡BA12_5_2¡BA12_5_3¡BA12_5_7¡BA12_5_8¡BA11_1¡BA11_2¡A©ñ¸m¥N¤½¦¡sheetªº¦C1¡A¦Ó¤£¬O¦pÀɮפ¤¥Î¤âkey¡A¬O§_¦³¿ìªk©O¡H´N¬O¥ý©w¦ì¥X±ø¥Ø¦bªí®æªº¦ì¸m¡A¦A§ì¥X¨ä¤å¦r±ø¥Ø¡C
¦A¦¸«ôÁ¤ηP¿E¡C
³Â·Ð¤F

example0412-1§ó·s.rar (824.31 KB)

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

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD