✅ חישוב ממוצע משוקלל ב- Excel (שימוש בנוסחאות)

תוכן העניינים

חישוב ממוצע משוקלל ב- Excel

כאשר אנו מחשבים ממוצע פשוט של קבוצת ערכים נתונה, ההנחה היא שלכל הערכים יש משקל או חשיבות שווים.

לדוגמה, אם אתה מופיע לבחינות ולכל הבחינות משקל דומה, הממוצע של הציונים שלך יהיה גם הממוצע המשוקלל של הציונים שלך.

עם זאת, בחיים האמיתיים, זה כמעט לא המקרה.

חלק מהמשימות תמיד חשובות יותר מהאחרות. חלק מהמבחנים חשובים יותר מהאחרים.

וזה המקום שבו ממוצע משוקלל נכנס לתמונה.

להלן הגדרת ספר הלימוד של ממוצע משוקלל:

כעת נראה כיצד לחשב את הממוצע המשוקלל ב- Excel.

חישוב ממוצע משוקלל ב- Excel

במדריך זה תלמד כיצד לחשב את הממוצע המשוקלל ב- Excel:

שימוש בפונקציית SUMPRODUCT.
שימוש בפונקציית SUM.

אז בואו נתחיל.

חישוב ממוצע משוקלל ב- Excel - פונקציית SUMPRODUCT

יכולים להיות תרחישים שונים שבהם עליך לחשב את הממוצע המשוקלל. להלן שלושה מצבים שונים בהם תוכל להשתמש בפונקציית SUMPRODUCT לחישוב ממוצע משוקלל ב- Excel

להלן שלושה מצבים שונים בהם תוכל להשתמש בפונקציית SUMPRODUCT לחישוב ממוצע משוקלל ב- Excel

דוגמה 1 - כאשר המשקלים מסתכמים ב -100%

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

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

לדוגמה, חידון, במשקל של 10% נושא משקל כפול מהמשקל לעומת מטלה, אך רבע מהמשקל בהשוואה לבחינה.

במקרה כזה, תוכל להשתמש בפונקציית SUMPRODUCT כדי לקבל את הממוצע המשוקלל של הציון.

להלן הנוסחה שתיתן לך את הממוצע המשוקלל ב- Excel:

= SUMPRODUCT (B2: B8, C2: C8)

כך פועלת נוסחה זו: פונקציית SUMPRODUCT של Excel מכפילה את האלמנט הראשון של המערך הראשון עם האלמנט הראשון של המערך השני. ואז הוא מכפיל את האלמנט השני של המערך הראשון עם האלמנט השני של המערך השני. וכולי…

ולבסוף, הוא מוסיף את כל הערכים הללו.

להלן דוגמה להבהרה.

דוגמה 2 - כאשר המשקלים אינם מסתכמים עד 100%

במקרה לעיל, המשקלים הוקצו בצורה כזו שהסכום הוסיף עד 100%. אבל בתרחישי חיים אמיתיים, זה לא תמיד יכול להיות כך.

בואו נסתכל על אותה דוגמה עם משקלים שונים.

במקרה הנ"ל המשקלים מסתכמים בכ- 200%.

אם אשתמש באותה נוסחת SUMPRODUCT, זה ייתן לי את התוצאה הלא נכונה.

בתוצאה הנ"ל הכפלתי את כל המשקולות והיא מחזירה את הערך הממוצע המשוקלל כ- 153.2. עכשיו אנחנו יודעים שתלמיד לא יכול לקבל יותר מ -100 מתוך 100, לא משנה כמה הוא/היא מבריק.

הסיבה לכך היא שהמשקולות אינן מסתכמות ב -100%.

להלן הנוסחה שתסדר את זה:

= SUMPRODUCT (B2: B8, C2: C8)/SUM (C2: C8)

בנוסחה שלעיל, התוצאה SUMPRODUCT מחולקת בסיכום כל המשקולות. מכאן, שלא משנה מה, המשקל תמיד יוסיף עד 100%.

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

דוגמה 3 - כאשר יש צורך לחשב את המשקולות

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

נניח שאתה מוכר שלושה סוגים שונים של מוצרים כאמור להלן:

אתה יכול לחשב את המחיר הממוצע המשוקלל למוצר באמצעות הפונקציה SUMPRODUCT. להלן הנוסחה בה תוכל להשתמש:

= SUMPRODUCT (B2: B4, C2: C4)/SUM (B2: B4)

חלוקת התוצאה SUMPRODUCT עם סכום הכמויות מוודא שהמשקלים (במקרה זה כמויות) מסתכמים ב -100%.

חישוב ממוצע משוקלל ב- Excel - פונקציית SUM

בעוד שפונקציית SUMPRODUCT היא הדרך הטובה ביותר לחשב את הממוצע המשוקלל ב- Excel, תוכל גם להשתמש בפונקציית SUM.

כדי לחשב את הממוצע המשוקלל באמצעות הפונקציה SUM, עליך להכפיל כל אלמנט, עם חשיבותו המוקצבת באחוזים.

שימוש באותו מערך נתונים:

הנה הנוסחה שתתן לך את התוצאה הנכונה:

= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)

יש להשתמש בשיטה זו כאשר יש לך כמה פריטים. אך כשיש לך פריטים ומשקולות רבים, שיטה זו עלולה להיות מסורבלת ונטייה לשגיאות. יש דרך קצרה וטובה יותר לעשות זאת באמצעות פונקציית SUM.

בהמשך לאותו מערך נתונים, הנה הנוסחה הקצרה שתתן לך את הממוצע המשוקלל באמצעות הפונקציה SUM:

= SUM (B2: B8*C2: C8)

הטריק בעת שימוש בנוסחה זו הוא להשתמש ב- Control + Shift + Enter, במקום להשתמש רק ב- Enter. מכיוון שפונקציית SUM אינה יכולה להתמודד עם מערכים, עליך להשתמש ב- Control + Shift + Enter.