עיצוב מותנה בגרפים

באחת הקבוצות נשאלה שאלה מעניינת:

אני רוצה ליצור גרף התקדמות כזה:

שישנה את צבעו לירוק, כאשר נעבור את ה – 90%:

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

ועכשיו לעבודה

נתחיל מהגדרות ראשוניות:
בתא A2 נכתוב את ערך ההתקדמות הנוכחי, למשל, 70%
בתא C2 נכתוב את ערך הסף, שעל פיו יקבע איזה גרף יוצג:

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

חישוב הסדרה הראשונה

הנוסחה עבור התא הראשון היא

=IF(A2<=C2,A2,NA())

והנוסחה להשלמה ל 100% היא:

=1-A5

כלומר, אם הערך שנמצא ב A2 קטן או שווה לערך הסף, תציג את הערך שקיים ב A2, ואם לא, תציג בתא #N/A
וזה בעצם הטריק – מכיוון שגרפים לא מציגים ערכי #N/A, אנחנו בעצם מציגים בכל רגע נתון רק את הסדרה הרלוונטית!
אותו הדבר נעשה עבור הסדרה השנייה, רק שהפעם נבדוק אם הערך גדול מהסף,
והנה 4 הנוסחאות:

אפשר לראות שכאשר מדובר בערך שקטן או שווה לערך הסף, נקבל ערכים מספריים עבור הסדרה הראשונה וערכי #N/A עבור סדרה השנייה,
ואילו כשמדובר בערך שגבוה מערך הסף הסדרה הראשונה תקבל #N/A והסדרה השנייה תקבל ערכים מספריים:

זהו, עכשיו אפשר ליצור את הגרף, כפי שלמדנו במאמר הזה:

ועכשיו לעיצובים:

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

ומה נקבל?

גרף שמציג את ההתקדמות בצבע אחד בלבד:
אם הערך בתא A1 גבוה או שווה לערך הסף החלק הצבעוני יהיה ירוק
אם נמוך או שווה – הוא יוצג באדום

וכל זה מתעדכן אוטומטית בהתאם לערך שהוזן.