לְהִצטַיֵן

ממוצע משוקלל

Weighted Average

נוסחת Excel: ממוצע משוקללנוסחה כללית | _+_ | סיכום

לחישוב ממוצע משוקלל, ניתן להשתמש ב- SUMPRODUCT לתפקד יחד עם פונקציית SUM . בדוגמה המוצגת, הנוסחה ב- G5, שהועתקה למטה, היא:





= SUMPRODUCT (weights,values)/ SUM (weights)

איפה משקולות האם ה טווח בשם I5: K5.

הֶסבֵּר

ממוצע משוקלל, המכונה גם ממוצע משוקלל, הוא ממוצע שבו ערכים מסוימים סופרים יותר מאחרים. במילים אחרות, לערכים מסוימים יש משקל רב יותר. אנו יכולים לחשב ממוצע משוקלל על ידי הכפלת הערכים לממוצע במשקלים תואמים, ולאחר מכן נחלק את סכום התוצאות בסכום המשקולות. ב- Excel ניתן לייצג זאת באמצעות הנוסחה הגנרית שלהלן, כאשר משקלים וערכים הם טווחי תאים:





איך לכתוב תואר באקסל
 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

בדף העבודה המוצג, ציונים עבור 3 בדיקות מופיעים בעמודות C עד E, ומשקלים נמצאים ב- טווח בשם משקולות (I5: K5). הנוסחה בתא G5 היא:

 
= SUMPRODUCT (weights,values)/ SUM (weights)

בעבודה מבפנים החוצה, אנו משתמשים תחילה בפונקציית SUMPRODUCT כדי להכפיל משקלים בציונים תואמים ולסכם את התוצאה:



 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

SUMPRODUCT מכפיל תחילה את האלמנטים המתאימים של שני המערכים יחד, ואז מחזיר את סכום המוצר:

 
= SUMPRODUCT (weights,C5:E5) // returns 88.25

התוצאה מחולקת לאחר מכן בסכום המשקולות:

 
= SUMPRODUCT ({0.25,0.25,0.5},{90,83,90}) = SUMPRODUCT ({22.5,20.75,45}) =88.25

כאשר הנוסחה מועתקת מטה G, הטווח בשם משקולות I5: K5 אינו משתנה מכיוון שהוא מתנהג כמו התייחסות מוחלטת . עם זאת, הציונים ב- C5: E5, נכנסו כ- התייחסות יחסית , עדכון בכל שורה חדשה. התוצאה היא ממוצע משוקלל לכל שם ברשימה כפי שמוצג. הממוצע בעמודה F מחושב לעיון רק עם הפונקציה AVERAGE :

 
=88.25/ SUM (weights) =88.25/ SUM ({0.25,0.25,0.5}) =88.25/1 =88.25

משקלים שאינם מסתכמים ב -1

בדוגמה זו, המשקולות מוגדרים לצירוף עד 1, כך שהמחלק הוא תמיד 1, והתוצאה היא הערך שהוחזר על ידי SUMPRODUCT. עם זאת, תכונה נחמדה של הנוסחה היא שהמשקלים לא צריכים להוסיף עד 1.

לדוגמה, נוכל להשתמש במשקל של 1 עבור שתי הבדיקות הראשונות ובמשקל של 2 לגמר (מכיוון שהגמר חשוב פי שניים) והממוצע המשוקלל יהיה זהה:

ממוצע משוקלל של Excel עם משקלים מותאמים אישית

כמה תרשימים יכולים להכיל גיליון תרשים

בתא G5, הנוסחה נפתרת כך:

 
= AVERAGE (C5:E5)

הערה: הערכים בסדים מתולתלים {} לעיל הם טווחים המתבטאים כ מערכים .

הטמעת משקולות

הפונקציה SUMPRODUCT דורשת ממדי מערך להיות תואמים. אם המידות אינן תואמות, SUMPRODUCT יחזיר שגיאת #VALUE. בדוגמה להלן המשקולות זהות לדוגמה המקורית, אך הן מופיעות ב- אֲנָכִי טווח:

ממוצע משוקלל של Excel עם TRANSPOSE

כדי לחשב ממוצע משוקלל עם אותה נוסחה, עלינו 'להפוך' את המשקולות למערך אופקי עם פונקציית TRANSPOSE ככה:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights) = SUMPRODUCT ({1,1,2},{90,83,90})/ SUM (1,1,2) = SUMPRODUCT ({90,83,180})/ SUM (1,1,2) =353/4 =88.25

לאחר הפעלת TRANSPOSE, המערך האנכי:

 
= SUMPRODUCT ( TRANSPOSE (weights),C5:E5)/ SUM (weights)

הופך להיות:

 
= TRANSPOSE ({0.250.250.5}) // vertical array

ומנקודה זו, הנוסחה מתנהגת כמו קודם.

קרא עוד: מערכים אנכיים ואופקיים .

קבצים מצורפים קוֹבֶץ ממוצע משוקלל exceljet. xlsx הסופר דייב בראנס


^