Excel הפך את חישוב הממוצע של תאים מרובים למשימה קלה מאוד - פשוט השתמש בפונקציה מְמוּצָע (מְמוּצָע). אבל מה אם ערכים מסוימים נושאים משקל יותר מאחרים? לדוגמה, בקורסים רבים, למבחנים יש משקל רב יותר מאשר מטלות. עבור מקרים כאלה, יש צורך לחשב ממוצע משוקלל.
לאקסל אין פונקציה לחישוב הממוצע המשוקלל, אבל יש פונקציה שתעשה עבורכם את רוב העבודה: SUMPRODUCT (מוצר SUM). וגם אם מעולם לא השתמשת בתכונה זו לפני כן, בסוף מאמר זה תשתמש בה כמו מקצוען. השיטה בה אנו משתמשים פועלת בכל גרסה של אקסל כמו גם בגיליונות אלקטרוניים אחרים כגון Google Sheets.
אנחנו מכינים את השולחן
אם אתה מתכוון לחשב ממוצע משוקלל, תצטרך לפחות שתי עמודות. העמודה הראשונה (עמודה B בדוגמה שלנו) מכילה את הציונים עבור כל מטלה או מבחן. העמודה השנייה (עמודה C) מכילה את המשקולות. משקל רב יותר פירושו השפעה רבה יותר של המשימה או המבחן על הציון הסופי.
כדי להבין מהו משקל, אתה יכול לחשוב על זה כאחוז מהציון הסופי שלך. למעשה, זה לא המקרה, שכן במקרה זה המשקולות אמורות להסתכם ב-100%. הנוסחה שננתח בשיעור זה תחשב הכל נכון ואינה תלויה בכמות שאליה מסתכמות המשקולות.
אנחנו נכנסים לנוסחה
כעת, כשהטבלה שלנו מוכנה, אנו מוסיפים את הנוסחה לתא B10 (כל תא ריק יתאים). כמו בכל נוסחה אחרת באקסל, אנחנו מתחילים בסימן שווה (=).
החלק הראשון של הנוסחה שלנו הוא הפונקציה SUMPRODUCT (מוצר SUM). טיעונים חייבים להיות מוקפים בסוגריים, אז אנחנו פותחים אותם:
=СУММПРОИЗВ(
=SUMPRODUCT(
לאחר מכן, הוסף את הארגומנטים של הפונקציה. SUMPRODUCT (SUMPRODUCT) יכולים להיות ארגומנטים מרובים, אבל בדרך כלל משתמשים בשניים. בדוגמה שלנו, הארגומנט הראשון יהיה טווח של תאים. B2:B9א שמכיל את הציונים.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
הארגומנט השני יהיה טווח של תאים C2:C9, המכיל את המשקולות. יש להפריד בין הארגומנטים הללו באמצעות פסיק (פסיק). כשהכל מוכן, סוגרים את הסוגריים:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
כעת נוסיף את החלק השני של הנוסחה שלנו, אשר יחלק את התוצאה המחושבת לפי הפונקציה SUMPRODUCT (SUMPRODUCT) לפי סכום המשקולות. נדון בהמשך מדוע זה חשוב.
כדי לבצע את פעולת החלוקה, נמשיך את הנוסחה שכבר הוכנסה עם הסמל / (אלכסון ישר), ולאחר מכן כתוב את הפונקציה SUM (סְכוּם):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
לתפקוד SUM (SUM) נציין רק ארגומנט אחד - טווח של תאים C2:C9. אל תשכח לסגור את הסוגריים לאחר הזנת הטיעון:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
מוּכָן! לאחר לחיצה על המקש זן, אקסל יחשב את הממוצע המשוקלל. בדוגמה שלנו, התוצאה הסופית תהיה 83,6.
איך זה עובד
בואו נפרק כל חלק של הנוסחה, החל מהפונקציה SUMPRODUCT (SUMPRODUCT) כדי להבין איך זה עובד. פוּנקצִיָה SUMPRODUCT (SUMPRODUCT) מחשב את המכפלה של הניקוד של כל פריט ומשקלו, ולאחר מכן מסכם את כל המוצרים המתקבלים. במילים אחרות, הפונקציה מוצאת את סכום המוצרים, ומכאן השם. אז בשביל מטלות 1 תכפילו 85 ב-5, ועבור המבחן תכפילו 83 ב-25.
אם אתה תוהה מדוע עלינו להכפיל את הערכים בחלק הראשון, דמיינו שככל שמשקל המשימה גדול יותר, כך עלינו לשקול את הציון עבורה יותר פעמים. לדוגמה, משימה 2 נספר 5 פעמים ו בחינה סופית – 45 פעמים. בגלל זה בחינה סופית יש השפעה רבה יותר על הציון הסופי.
לשם השוואה, בעת חישוב הממוצע האריתמטי הרגיל, כל ערך נלקח בחשבון רק פעם אחת, כלומר לכל הערכים יש משקל שווה.
אם אתה יכול להסתכל מתחת למכסה המנוע של פונקציה SUMPRODUCT (SUMPRODUCT), ראינו שלמעשה היא מאמינה בזה:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
למזלנו, אנחנו לא צריכים לכתוב נוסחה כל כך ארוכה כי SUMPRODUCT (SUMPRODUCT) עושה את כל זה באופן אוטומטי.
פונקציה בפני עצמה SUMPRODUCT (SUMPRODUCT) מחזירה לנו מספר עצום - 10450. בשלב זה, החלק השני של הנוסחה נכנס לפעולה: /SUM(C2:C9) or /SUM(C2:C9), המחזיר את התוצאה לטווח הרגיל של הציונים, נותן את התשובה 83,6.
החלק השני של הנוסחה חשוב מאוד מכיוון שמאפשר לך לתקן חישובים באופן אוטומטי. זוכרים שמשקולות לא חייבות להסתכם ב-100%? כל זאת הודות לחלק השני של הנוסחה. לדוגמה, אם נעלה ערך משקל אחד או יותר, החלק השני של הנוסחה פשוט יחלק בערך הגדול יותר, שוב יגרום לתשובה הנכונה. או שאנחנו יכולים להקטין את המשקולות בהרבה, למשל על ידי ציון ערכים כמו 0,5, 2,5, 3 or 4,5, והנוסחה עדיין תעבוד כהלכה. זה נהדר, נכון?