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

averageifs ¦P¤@­ÓÄæ¦ì¡A¤£¦P­È

averageifs ¦P¤@­ÓÄæ¦ì¡A¤£¦P­È

¤j®a¤È¦w¡A·Q½Ð±Ð­Ó°ÝÃD
§Ú·Q²Î­p¥­§¡­È¡A©Ò¥H¥Îaverageifs¨Ó­pºâ

§Ú·Q¥h­pºâ¬P´Á¤@ ~ ¤Gªº¥­§¡­È,©Ò¥H§Ú¤½¦¡³o¼Ë¼g

=AVERAGEIFS(M4:M33,B4:B33,"¬P´Á¤@",B4:B33,"¬P´Á¤G")

¥i«o¤£¦æ¡A¥X²{#div/0!

­Y®³±¼¬P´Á¤Gªº³¡¤À¡A«o¥i¥Hºâ¥X¡A¬O§ÚªºÅ޿褣¹ï¶Ü??

data.png
2016-6-27 16:30

­è´ú¸Õ
  1. =AVERAGEIFS($M$4:$M$33,$B$4:$B$33,"<>¬P´Á¤@",$B$4:$B$33,"<>¬P´Á¤G",$B$4:$B$33,"<>¬P´Á¤T",$B$4:$B$33,"<>¬P´Á¥|")
½Æ»s¥N½X
¥i¥Hºâ¥X¤­ ~ ¤éªº¥­§¡¡A¦ý¬°¤°»ò¥Î=«o¤£¯à©O???

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2016-6-28 09:57 ½s¿è

¦^´_ 1# owenchen0911
°²³]¡u¤é´Á¡v¸ê®Æ¦ì©óAÄæ¡A¡u¬P´Á¡v¸ê®Æ¦ì©óBÄæ¡A¡uªM¼Æ¡v¸ê®Æ¦ì©óCÄæ
1.»s§@°ÊºA½d³ò
(1)©w¸q¦WºÙ¡u¤é´Á¡v¡A°Ñ·Ó¨ì¿é¤J   =OFFSET(¤u§@ªí1!$A$2,,,COUNT(¤u§@ªí1!$A:$A))
(2)©w¸q¦WºÙ¡u¬P´Á¡v¡A°Ñ·Ó¨ì¿é¤J   =OFFSET(¤u§@ªí1!$B$2,,,COUNT(¤u§@ªí1!$A:$A))
(3)©w¸q¦WºÙ¡uªM¼Æ¡v¡A°Ñ·Ó¨ì¿é¤J   =OFFSET(¤u§@ªí1!$C$2,,,COUNT(¤u§@ªí1!$A:$A))

2.«ü©w±ø¥ó¨D¥­§¡­È¡A¦p1#­pºâ¡u¬P´Á¤@ ~ ¤Gªº¥­§¡­È¡v¡G
=ROUND(SUMPRODUCT((¬P´Á={"¬P´Á¤@","¬P´Á¤G"})*ªM¼Æ)/SUMPRODUCT((¬P´Á={"¬P´Á¤@","¬P´Á¤G"})*1),0)

½Ð°Ñ¦Ò¡I

TOP

¦^´_  owenchen0911
°²³]¡u¤é´Á¡v¸ê®Æ¦ì©óAÄæ¡A¡u¬P´Á¡v¸ê®Æ¦ì©óBÄæ¡A¡uªM¼Æ¡v¸ê®Æ¦ì©óCÄæ
1.»s§@°ÊºA½d ...
p212 µoªí©ó 2016-6-28 09:55




ÁÂÁ¤j¤jªºÀ°¦£¡A¦ý¥i½Ð°Ý¤@¤U¬°¤°»ò§Úªº¼gªk¤£¯à©O??

¦A¦¸ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ KCC ©ó 2016-6-28 10:37 ½s¿è

¦^´_ 4# owenchen0911


    §A¨S·d²M·¡¨ç¼Æªº¥Îªk
#1
=AVERAGEIFS(M4:M33,B4:B33,"¬P´Á¤@",B4:B33,"¬P´Á¤G") ¬O¨D ¦P®É¬O¬P´Á¤@¤]¬O¬P´Á¤G¡A©Ò¥H·íµM¬O¨S¥b­Ó³y¦¨0°£¿ù»~

µ¹¤@­Ó¤½¦¡°Ñ¦Ò
=SUM(SUMIFS(M4:M33,B4:B33,{"¬P´Á¤@","¬P´Á¤G"}))/SUM(COUNTIFS(B4:B33,{"¬P´Á¤@","¬P´Á¤G"}))

#2

=AVERAGEIFS($M$4:$M$33,$B$4:$B$33,"<>¬P´Á¤@",$B$4:$B$33,"<>¬P´Á¤G",$B$4:$B$33,"<>¬P´Á¤T",$B$4:$B$33,"<>¬P´Á¥|")

¨D¦P®É¤£µ¥©ó¬P´Á¤@¡B¬P´Á¤G¡B¬P´Á¤T¡B¬P´Á¥|¡A©Ò¥H·|§ä¥X ¬P´Á¤­¡B¬P´Á¤»¡B¬P´Á¤Ñ ªº­È¨D¥­§¡

-----------
µ¹­Ó«Øij¡A°Ý°ÝÃD¯à´£¨ÑªþÀÉ¥[¤W¾A·íªº»¡©ú·|¦n¤@¨Ç

TOP

¦^´_  owenchen0911


    §A¨S·d²M·¡¨ç¼Æªº¥Îªk
#1
=AVERAGEIFS(M4:M33,B4:B33,"¬P´Á¤@",B4:B33,"¬P ...
KCC µoªí©ó 2016-6-28 10:28




ÁÂÁ¨â¦ì¤j¤jªº«üÂI

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD