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

[µo°Ý] sumif¦p¦ó¥[Á`°ÊºA¦ì¸m

[µo°Ý] sumif¦p¦ó¥[Á`°ÊºA¦ì¸m

sumif(°ÊºArange,±ø¥ó,sum_range)
§Ú¨Ï¥Î³oºØ¤è¦¡¼g
°ÊºA½d³ò¥i¥H§ì¨ì§Ú·Q­nªº°Ï¶ô
¦ýsum_range«o¥Ã»·³£§ì¦P¼Ëªº¦ì¸m
¨ä¤¤°ÊºA½d³ò§Ú¥t¥~©w¸q¤@­Ó¦WºÙ
¤ñ¦paaa
¤½¦¡Åܦ¨
sumif(aaa,"*"&"¤p­p",sum_range)
­n«ç¼Ë¤~¯àÅýsum_range§ì¨ú°ÊºA½d³òaaa¤¤ªº¸ê®Æ?

¬Ý°_¨Ó¬O¤½¦¡¦³°ÝÃDsumif(aaa,"*""¤p­p",sum_range)
""¥h°£§Y¥i.

TOP

©êºp...¤½¦¡ªº³¡¥÷µ§»~¤F
§Úªº¼gªk¬Osumif(aaa,"*"&"¤p­p",sum_range)
ÁöµM¦³ÂIµe³D²K¨¬...¦ý¤½¦¡³¡¥÷¤´¥i°õ¦æ

§Ú¬O¦bA¤À­¶¶K¤W¤@¨Ç¥¼¾ã²z¹Lªºdata
¨C¤Ñ³£·|©¹¤U¶K¤W·s¸ê®Æ(¤£Âл\)
¦A³]©w¤@®æ©ñ¤Wkey word(¤é´Á)
µM«á¦Û¤v³]°Ê°ÊºA½d³ò¥h§ä´Mkey word«áÂê©w»Ý­n¾ã²zªº¸ê®Æ
³Ì«á­n¥h§ä´M¨Ã¥[Á`§Ú©Ò»Ýªº³¡¥÷
sumifªºrange§Ú¥i¥Hª½±µ©ñ¦Û¤v©w¸qªº¦WºÙ
¦ýsum_range¥u»Ý­n¥[Á`¨ä¤¤¤@¦C²Å¦X±ø¥óªº¼Æ­È
¦pªG¤£¬O°ÊºA½d³ò¨Ï¥Îsheet2!$AK:$AK§Y¥i
³o¸Ì¨Ï¥Î³o¤è¦¡«h·|¥þ³£¥[Á`³Ì¤W¤èªº¸ê®Æ¦Ó«D°ÊºA½d³ò¤ºªº¸ê®Æ

¤£¤Ó¦nªí¹F...¦pªG¦³»Ý­n
§Ú¦A¶Ã°µ­Ó¤u§@ªí¨Ó¸ÑÄÀ¦n¤F

TOP

¤½¦¡¬Ý¤£¥X©Ò¥HµM,ÁÙ¬O¤W¶ÇÀɮקa.

TOP

©w¸q¦WºÙ AAA=Sheet1!$A:$A
©w¸q¦WºÙ BBB=OFFSET($B$1,ROW(AAA)-1,,COUNTA(AAA))
sumif(AAA,"*¤p­p",BBB)
­Y­n¥[Á`ªºÄæ¦ì¦bAKªº¸Ü±N $B$1§ï¦¨ $AK$1¡A
¨S¨Æ´N¨Ó³}³}

TOP

¦pªGª½±µ§âOFFSET($B$1,ROW(AAA)-1,,COUNTA(AAA))¼g¨ìsumif¤½¦¡¸Ì¤]¥i¥H¶Ü
¦]¬°§Ú¦³«Ü¦hºØ«¬¦¡­n¥[Á`
¦pªG¥þ³£­n©w¸q¦WºÙ...¨º·|¥X²{«Ü¦hºØ¦WºÙ

TOP


¥H¤W¬O¥Ü·N¹Ï
§Ú°ÊºA½d³ò¦b¤£¦PªºÀx¦s®æ¥i¥H§ä¨ì1¡B2¡B3ªº½d³ò
sum_range°²³]¦³3ºØ¸ê®Æ­n°µ¥[Á`
¥H¦¹¥Ü·N¹Ï¦Ó¨¥§Ú¥²»Ý±o¥X9­Ó­È

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2011-6-13 23:16): ½Ð¤W¶ÇÀÉ®×¥H«K¤F¸Ñ¬O§_¦³¿ìªkÅý©w¸q¶q´î¤Ö ¹ÏÀɽЪ½±µ¤W¶Ç

©êºp...¸É¤W¥Ü·N¹Ï


¨Ï¥Îsumif(AAA,"*¤p­p",BBB)³oºØ¤è¦¡§Ú¦³°µ¥X¨Ó
¥u¬O³o¼Ë¤@¨Ó§Ú­n©w¸q¤j¬ù10­Ó¦WºÙ
¤èªk¦³ÂI²Â...©Ò¥H·Q°Ý°Ý¬Ý¦³¤°»ò¼gªk¥i¥H¨ú¥N

TOP

²q¨Ó²q¥h§ì¤£¨ìÄo³B.§A»¡¤£²M·¡,¤j®a¬Ý¤£©ú¥Õ.¨S¸ê®Æ¯uªº¨SªkÀ°¤W§A.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2011-6-14 17:22): ¤p¾Ç¥Í¤]¯à¤W¶Çªþ¥ó

©êºp...¦]¬°¬O¤½¥qªºªF¦è
§Ú­n¦^®a­×§ï¤@¥÷Ãþ¦üªºªF¦è¦A¤W¶Ç
³o´Á¶¡§Ú¶¶«K­n·Q¿ìªk¦^¤å¼W¥[¦Û¤vªº¿n¤À(²{¦b¤£¯à¤W¶Çªþ¥ó)
¥ýÁÂÁ¤j®aªºÀ°¦£

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD