התקשרו אלינו: 052-2928949
ימים א'-ה' בין השעות 9:00-17:00

התקשרו אלינו: 052-2928949 א-ה 9:00-17:00

גרף דינמי ב-365

אקסל 365

גרף דינמי ב-365

אקסל 365

כיצד ניצור גרף דינמי
באמצעות פונקציות המערך באקסל 365

לפנינו טבלת נתוני עובדים בשם EmpList, שמכילה את שם העובד, המחלקה והשכר:

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

שלב ראשון – יצירת רשימה ייחודית של המחלקות שקיימות בטבלה

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

=UNIQUE(EmpList[מחלקה])

וזו התוצאה שהתקבלה:

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

שלב שני – אימות נתונים

בשלב הקודם יצאנו רשימת מחלקות דינאמית, שמשתנה עם שינוי בטבלת הנתונים.
כעת נשתמש בה כבסיס לאימות נתונים, ממנו נוכל לבחור את שם המחלקה שאותה נרצה לראות בגרף.
בכרטיסיית נתונים בחרתי ב'אימות נתונים' > 'רשימה' והפניתי אל הטווח שמכיל את שמות המחלקות.
מכיוון שמדובר בנוסחת מערך, אני מפנה אל התא הראשון בלבד ומוסיפה סולמית, כדי לציין לאקסל שצריך לקחת את כל הטווח
גם אם תסמנו ידנית את הטווח, כאשר תגיעו לשורה האחרונה, הטווח שעד עכשיו הופיע בסגנון $N$8:$N$11, יוצג עם הסולמית בסופו, כפי שניתן לראות בתמונה:

שלב שלישי – יצירת הטבלה עליה יתבסס הגרף

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

=FILTER(EmpList[עובד],EmpList[מחלקה]=G5)

כלומר – מתוך טבלת העובדים, הצג את כל הנתונים בעמודת העובדים שהערך בעמודת המחלקה שווה לערך שנבחר בתא G5, שם נמצא האימות:

אם אבחר מחלקה אחרת, רשימת העובדים תשתנה בהתאם:

בשלב הזה נוסיף את המשכורות, ולצורך כך אשתמש בפונקציה XLOOKUP שהיא הגרסה המשופרת של VLOOKUP:

=XLOOKUP(N18#,EmpList[עובד],EmpList[משכורת])

וזו התוצאה:

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

שלב רביעי – יצירת הגרף

בדרך כלל, כדי ליצור גרף יש לעמוד בתוך הטבלה ולבחור ב'הוספה' > 'גרף' (או ללחוץ על קיצור המקשים Alt+F1 כדי ליצור את גרף ברירת המחדל):

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

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

על פניו, הכל בסדר…

אז בחרתי את המחלקה, ואני רואה את הגרף
אבל מה קורה אם אבחר עכשיו את מחלקה 2, שיש בה רק שלושה עובדים?

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

טוב, הבנו, יש בעיה. אז מה הפתרון?

הפתרון הוא קודם כל בהבנה שגרפים אפשר לבסס על טווחים בגיליון או על שמות (Named Ranged), ובמקרה שלנו – זה בדיוק מה שאנחנו צריכים לעשות.
אז בשלב הראשון אצור שם לרשימת העובדים בטבלת הביניים ושם נוסף למשכורות.
אסמן את הטווח שבו נמצאת רשימת המחלקות שנוצרה על פי רשימת העובדים הדינאמית
ובכרטיסיית 'נוסחאות' אבחר ב'הגדר שם'.

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

אותו דבר אעשה לרשימת המשכורות:

עכשיו אפשר ללחוץ לחיצה ימנית על הגרף > 'בחר נתונים' ונחליף את ההפניה הישירה, לטווחים עם השמות שיצרנו:

בצד שמאל של המסך, מעל לרשימת השמות, אלחץ על 'ערוך':

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

לסיום נלחץ על 'אישור'.

ועכשיו אחזור על הפעולה עבור המשכורות:
אלחץ על 'ערוך' בחלק הימני של החלונית, ('ערכי מקרא'),
אמחק את ההפניה לטווח ואבחר במקומה את השם שנתתי לטווח הדינאמי של המשכורות ואאשר:

ועכשיו, כל שינוי בשם המחלקה, ישנה לי גם את טבלת העובדים, וגם את הגרף שמבוסס עליו:

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

דילוג לתוכן