עיצוב מותנה עם נוסחה

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

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

מה הלוגיקה של העיצוב המותנה?

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

רגע לפני שמתחילים…

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

אז מה יש לנו

מטריצה שמציגה את סך המשכורות בחברה, מפולחות על פי עיר מגורים ואגף:

אפשרות בחירה באמצעות אימות נתונים עבור עיר המגורים והאגף:

שלב ראשון – סימון טווח הנתונים

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

שלב שני – בחירה בעיצוב מותנה

בכרטיסיית 'בית' נבחר ב'עיצוב מותנה' > 'כלל חדש':

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

=$A2:$A7=$G$1

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

לסיום נלחץ על אישור, וזו התוצאה שהתקבלה:

שלב שלישי – עיצוב מותנה של העמודות:

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

=B$1:D$1=$G$2

נאשר, וזו התוצאה שהתקבלה:

לבדיקה, נשנה את הערכים בתאי העזר, ונראה איך העיצוב המותנה משתנה בהתאם: