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

[µo°Ý] ½Ð±Ð__INDIRECT¨ç¼Æ¨Ï¥Î¤èªk

[µo°Ý] ½Ð±Ð__INDIRECT¨ç¼Æ¨Ï¥Î¤èªk

½Ð±Ð¦U¦ì«e½ú
=SUM('1:31'!H5)  µ{¦¡ ¥i¥H¥[Á`

¦ý¬O§ï¦¨=SUM(INDIRECT("'"&B5&":"&C5&"'!H5"))

«o¥X²{#ERF!   B5=1     C5=31
¼Ó¥D¼ö©«
¤j®a¦n

¦^´_ 1# axen0520


    ¥ßÅé°Ñ·Ó¥u¯à¨Ï¥Î
SUM¡AAVERAGE ¡AAVERAGEA¡ACOUNT ¡ACOUNTA ¡AMAX¡AMAXA ¡AMIN ¡AMINA ¡APRODUCT ¡ASTDEV¡ASTDEVA¡ASTDEVP¡ASTDEVPA¡AVAR¡AVARA¡AVARP ¡AVARPA  
³o¨Ç¨ç¼Æ
¤]´N¬O»¡INDIRECT¨ç¼ÆµLªk³]©w¥ßÅé°Ñ·Ó
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


    ¤°»ò¥s ¥ßÅé°Ñ·Ó ªü  ¤£¤ÓÀ´­C
¤j®a¦n

TOP

°Ñ·Ó¨ì1­Ó¥H¤W(¤£§t1­Ó)¤u§@ªí¬Û¦PÀx¦s®æ½d³ò©Î³æ¤@Àx¦s®æ
´N¥s°µ¥ßÅé°Ñ·Ó¡A¥ßÅé°Ñ·Ó§Î¦¡¦p¤U:
¶}©l¤u§@ªí:µ²§ô¤u§@ªí!Àx¦s®æ°Ñ·Ó
¨Ò¦p:¬¡­¶Ã¯¤¤¦³5­Ó³sÄò¤u§@ªí
¦WºÙ¤À§O¬°:Sheet1¡ASheet2¡ASheet3¡ASheet4¡ASheet5
­n¦b²Ä5­Ó¤u§@ªí(Sheet5)¤¤­pºâSheet1¨ìSheet4³o4­Ó¤u§@ªíªºA1Àx¦s®æ¥[Á`
¤½¦¡=SUM(Sheet1:Sheet4!A1)
¦]¬°¦¹ºØ°Ñ·Ó¬O¤À§O¦s¦b¤£¦P¤u§@ªí¤¤ªºÀx¦s®æ
¨C­Ó¤u§@ªí¥iµø¬°¤@­Ó¥­­±¡A¦h­Ó¤u§@ªí§Y¬°¦h­Ó¥­­±¡A©Ò¥HºÙ¬°¥ßÅé°Ñ·Ó
½Ð°Ñ¾\
    Microsoft½u¤W»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¸Õ¸Õ³o­Ó:
{=SUM(N(INDIRECT(ROW(INDIRECT(B5&":"&C5))&"!H5")))}

©Î¬O

=SUMPRODUCT(N(INDIRECT(ROW(INDIRECT(B5&":"&C5))&"!H5")))
1

µû¤À¤H¼Æ

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-12-20 12:29 ½s¿è

¶}²´¬É¤F
·Q¤£¨ì¯à¬ð¯}°}¦C¤½¦¡»P»P¤u§@ªíÅܼƪº­­¨î
¾Ç²ß¤F!
¥u¬O³o¼Ëªº¤èªk¥u¾A¦X³sÄò¥H¼Æ¦r½s¸¹ªº¤u§@ªí¦WºÙ
¹ê½è¤W¤w¸g¤£¬O¯u¥¿¥ßÅé°Ñ·Ó
§Q¥ÎªÅ¥Õ°_¡B¨´¤u§@ªí¨ÓÅܰʽd³ò
play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# handmuch


    ÁÂÁ ³o­Ó¤½¦¡¥i¥H ¥i¥H²Å¦X§Úªº»Ý¨D

    SUMPRODUCT(N(INDIRECT(ROW(INDIRECT(B5&":"&C5))&"!H5")))
¤j®a¦n

TOP

¦^´_ 6# Hsieh


·PÁª©¥D ¸Ô²Ó ¹Ï¤å±Ð¾Ç   §ÚÀ´¤F
¤j®a¦n

TOP

¦^´_ 6# Hsieh

INDIRECT¡BOFFSET¨Ï¥ÎN()¡BT()­°ºûªº¤èªk¥i¥H³B²z³oÃþ°ÝÃD

¥H«eªºµªÃD¥i§@°Ñ¦Ò
https://tw.knowledge.yahoo.com/q ... n?qid=1011072907608
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# handmuch


  ½Ð°Ý¤@¤U..

¸Õ¸Õ³o­Ó:
{=SUM(N(INDIRECT(ROW(INDIRECT(B5&":"&C5))&"!H5")))}

©Î¬O

=SUMPRODUCT(N(INDIRECT(ROW(INDIRECT(B5&":"&C5))&"!H5")))

n¬O¦³¤°»ò¥Î©O... ½Ð°Ý¥i¤£¥i¥H¸Ñ»¡¤@¤U¶Ü..

¥ýÁÂÁ¤j¤j¦^µª~

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD