כיצד ליצור טווח תרשים דינמי ב- Excel

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

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

מהו טווח תרשים דינמי?

טווח תרשים דינאמי הוא טווח נתונים המתעדכן אוטומטית בעת שינוי מקור הנתונים.

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

להלן דוגמה לתרשים המשתמש בטווח תרשימים דינאמי.

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

כיצד ליצור טווח תרשים דינמי ב- Excel?

ישנן שתי דרכים ליצור טווח תרשים דינאמי ב- Excel:

• שימוש בטבלת אקסל
• שימוש בנוסחאות

ברוב המקרים, שימוש בטבלת Excel היא הדרך הטובה ביותר ליצור טווחים דינאמיים ב- Excel.

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

לחץ כאן להורדת קובץ הדוגמה.

שימוש בטבלת אקסל

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

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

טיפ למקצוענים: כדי להמיר מגוון תאים לטבלת Excel, בחר את התאים והשתמש בקיצור המקשים - Control + T (החזק את מקש Ctrl ולחץ על מקש T).

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

כעת, עלינו להשתמש בטבלת Excel זו בעת יצירת התרשימים.

להלן השלבים המדויקים ליצירת תרשים קווים דינאמי באמצעות טבלת Excel:

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

זהו זה!

השלבים שלעיל יוסיפו תרשים קווים שיתעדכן אוטומטית כאשר תוסיף נתונים נוספים לטבלת Excel.

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

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

שימוש בנוסחאות אקסל

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

עם זאת, אם אינך יכול להשתמש בטבלת Excel מסיבה כלשהי (אולי אם אתה משתמש ב- Excel 2003), יש דרך נוספת (מעט מסובכת) ליצור טווחי תרשימים דינמיים באמצעות נוסחאות Excel וטווחים עם שם.

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

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

1. צור שני טווחים בעלי שם דינמי באמצעות נוסחת OFFSET (אחד לכל אחד בעמודה 'ערכים' ו'חודשים '). הוספה/מחיקה של נקודת נתונים תעדכן אוטומטית את הטווחים האלה.
2. הכנס תרשים שמשתמש בטווחים הנקובים כמקור נתונים.

תן לי להסביר כל שלב בפירוט עכשיו.

שלב 1 - יצירת טווחי שמות דינמיים

להלן השלבים ליצירת טווחי שמות דינמיים:

• עבור לכרטיסייה 'נוסחאות'.
• לחץ על 'מנהל שמות'.
• בתיבת הדו -שיח מנהל שמות, ציין את השם בשם ChartValues והזן את הנוסחה הבאה בהתייחס לחלק: = OFFSET (Formula! $ B $ 2 ,,, COUNTIF (Formula! $ B $ 2: $ B $ 100, ””))
• לחץ על אישור.
• בתיבת הדו -שיח מנהל שמות, לחץ על חדש.
• בתיבת הדו -שיח מנהל שמות, ציין את השם בשם ChartMonths והזן את הנוסחה הבאה בהתייחס לחלק: = OFFSET (Formula! $ A $ 2 ,,, COUNTIF (Formula! $ A $ 2: $ A $ 100, ””))
• לחץ על אישור.
• לחץ על סגור.

השלבים שלעיל יצרו שני טווחים בשם בחוברת העבודה - ChartValue ו- ChartMonth (אלה מתייחסים לערכים וטווח החודשים במערך הנתונים בהתאמה).

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

הקסם נעשה על ידי הפונקציה OFFSET כאן.

בנוסחת הטווח בשם 'ChartValue', ציינו את B2 כנקודת הייחוס. נוסחת OFFSET מתחילה שם ומתרחבת לכסות את כל התאים המלאים בעמודה.

אותו היגיון פועל גם בנוסחת הטווח בשם ChartMonth.

שלב 2 - צור תרשים באמצעות טווחי שמות אלה

כל שעליך לעשות הוא להוסיף תרשים שישתמש בטווחים הנקובים כמקור הנתונים.

להלן השלבים להוספת תרשים ושימוש בטווחי תרשים דינמיים:

• עבור לכרטיסייה הוספה.
• לחץ על 'הכנס קו או תרשים שטח' והכנס את תרשים 'קו עם סמנים'. פעולה זו תוסיף את התרשים בגליון העבודה.
• כשהתרשים נבחר, עבור לכרטיסייה עיצוב.
• לחץ על בחר נתונים.
• בתיבת הדו -שיח 'בחר מקור נתונים', לחץ על הלחצן הוסף ב'ערכים מקראיים (סדרות) '.
• בשדה ערך סדרה, הזן = Formula! ChartValues ​​(שים לב שאתה צריך לציין את שם גליון העבודה לפני הטווח בשם כדי שזה יעבוד).
• לחץ על אישור.
• לחץ על כפתור העריכה ב'תוויות ציר אופקיות (קטגוריות) '.
• בתיבת הדו -שיח 'תוויות ציר', הזן = Formula! ChartMonths
• לחץ על אישור.

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

כמה דברים שחשוב לדעת בעת שימוש בטווחים בשם עם תרשימים:

• לא אמורים להיות תאים ריקים בנתוני התרשים. אם קיים ריק, טווח בשם לא יתייחס למערך הנתונים הנכון (מכיוון שהספירה הכוללת תוביל לכך שהוא יתייחס למספר פחות של תאים).
• עליך לעקוב אחר מוסכמת השמות בעת שימוש בשם הגיליון במקור התרשים. לדוגמה, אם שם הגיליון הוא מילה אחת, כגון Formula, תוכל להשתמש = Formula! ChartValue. אבל אם יש יותר ממילה אחת, כגון טבלת נוסחה, עליך להשתמש = 'תרשים נוסחה'! ChartValue.