תרשים לפי תא נבחר

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

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

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

יישום זה קל מאוד - אתה צריך רק שתי נוסחאות ומקרו אחד זעיר ב-3 שורות.

שלב 1. מספר קו נוכחי

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

שלב 2. קישור לכותרת

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

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

שלב 3. קישור לנתונים

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

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

שלב 4. החלפת קישורים בתרשים

כעת בחר את כותרת הטבלה ואת השורה הראשונה עם הנתונים (טווח) ובנה תרשים המבוסס עליהם באמצעות הוספה - תרשימים (הוסף - תרשימים). אם תבחר שורה עם נתונים בתרשים, הפונקציה תוצג בשורת הנוסחאות שׁוּרָה (סִדרָה) היא פונקציה מיוחדת שבה Excel משתמש באופן אוטומטי בעת יצירת תרשים כלשהו כדי להתייחס לנתונים והתוויות המקוריים:

בואו נחליף בזהירות את הארגומנט הראשון (חתימה) והשלישי (נתונים) בפונקציה זו בשמות הטווחים שלנו משלבים 2 ו-3:

התרשים יתחיל להציג נתוני מכירות מהשורה הנוכחית.

שלב 5. מאקרו חישוב מחדש

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

לחץ לחיצה ימנית על לשונית גיליון הנתונים ובחר בפקודה מָקוֹר (קוד מקור). בחלון שנפתח, הזן את הקוד של מטפל המאקרו עבור אירוע שינוי הבחירה:

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

שלב 6. הדגשת הקו הנוכחי

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

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

זהו – פשוט ויפה, נכון?

הערות

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

  =IF(CELL(“row”)<4,IF(CELL("row")>4,CELL(“row”)))
  

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