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

owenchen0911

¤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§ÚªºÅ޿褣¹ï¶Ü??

¤U¸ü (14.63 KB)

2016-6-27 16:30

owenchen0911

­è´ú¸Õ

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???

p212

¦^´_ 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

owenchen0911

¦^´_  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¦¸ÁÂÁÂ

KCC

¦^´_ 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¤@¨Ç

owenchen0911

¦^´_  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