יצירת ממוצע נתונים מגיליונות אקסל שונים

[Menor]

יצירת ממוצע נתונים מגיליונות אקסל שונים

שלום לכולם,

אני משתמש באקסל 2010 וWIN10.

יש לי קובץ אקסל עם מספר גיליונות. בכל גיליון יש טבלה עם שמות ובטור ליד נתונים מספריים חודשיים. סדר השמות בטבלה יכול להשתנות כל חודש ויכולים גם להתווסף אנשים חדשים. אני רוצה ליצור גיליון שלישי ("ממוצעים") שיעשה ממוצע רב חודשי של הנתונים עבור כל אחד מהשמות.

אני לא כל כך מתמצא בנושא. ניסיתי לעבוד עם VLOOKUP אבל לא הלך....

אשמח מאד לעזרה!

אני מצרף קובץ לדוגמא.

תודה רבה!

 

[iyyi]

אפשר לעשות זאת כך

הכן רשימה של גיליונות הנתונים (בדוגמה המצורפת זה בתאים E2:E2) ותן להם את השם Months.
בתא C3 כתוב את נוסחת המערך:
=AVERAGE(SUMIF(INDIRECT("'"&Months&"'!B:B"),B3,INDIRECT("'"&Months&"'!C:C")))

יש לסיים את הכנסתה בצירוף המקשים Ctrl+Shift+Enter.
שים לב שחישוב הממוצע מתייחס לשם שאינו מופיע בגיליון כאילו הוא מופיע עם נתון 0 (אפס)
מצורף קובץ דוגמה

 

[Menor]

יש כמה בעיות

קודם כל תודה רבה!

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

דבר שני - לא לגמרי הבנתי את עניין ה months. בקובץ שצירפת רשום פשוט חודש 1, חודש 2). אני מניח שזה קשור גם להוספת גיליונות חדשים כי אני לא מצליח לעשות את זה.

צירפתי קובץ חדש לדוגמא.

שוב תודה

 

[Menor]

סליחה, אני רואה שכבר התייחסת לעניין האפסים

אבל יש איזו דרך לפתור את זה?

 

[iyyi]

תשובות לבעיות

1. שים לב שלתחום התאים המכיל את שמות הגיליונות צריך לתת שם. אני נתתי את השם Months לתאים E2:E3. כאשר הוספת בתא E4 את "חודש 3" היית צריך להכיל את השם גם עליו. אתה יכול לעשות זאת ע"י הקשת צירוף המקשים Ctrl+F3 ולעדכן את טווח התאים של השם.
2. ניתן לטפל בתאים ריקים. "המחיר" הוא הכפלת אורך הנוסחה. במקרה כזה נוסחת המערך תהיה:
   =AVERAGE(IF(SUMIF(INDIRECT("'"&Months&"'!B:B"),B3,INDIRECT("'"&Months&"'!C:C"))<>0,SUMIF(INDIRECT("'"&Months&"'!B:B"),B3,INDIRECT("'"&Months&"'!C:C"))))

יש לסיים את הכנסת הנוסחה בצירוף המקשים Ctrl+Shift+Enter

בקובץ המצורף שאול לא מופיע ב"חודש 1", ב"חודש 2" רשום לו 66 וב"חודש 3" רשום לו 64. חישוב הממוצע הוא 65.
נשארה עדיין בעיה אחת אפשרית. מה קורה אם למישהו רשום הנתון 0 (אפס) בעמודה C. במקרה כזה אני מניח שכן נרצה שהנתון יכלל בחישוב הממוצע.
אולי מישהו יציע פתרון לכך, אבל עד אז ניתן לרשום במקום 0 ערך מאוד קטן (למשל 0.0001) ובעיצוב התאים להציג מספר ספרות אחרי הנקודה שיגרום לכך שלא נראה את התוספת.

מצורף קובץ הדגמה.

 

[Menor]

הבנתי! עובד מצויין! תודה