הוספת עיצוב מותנה ב-Excel מאפשרת לך להחיל אפשרויות עיצוב שונות על תא, או טווח של תאים, העומדים בתנאים ספציפיים שהגדרת. הגדרת תנאים כאלה יכולה לעזור לארגן את הגיליון האלקטרוני שלך ולהקל על הסריקה. אפשרויות העיצוב שבהן תוכל להשתמש כוללות שינויים בצבעי גופן ורקע, סגנונות גופנים, גבולות תאים והוספת עיצוב מספרים לנתונים.
Excel כולל אפשרויות מובנות עבור תנאים נפוצים כגון מציאת מספרים שגדולים או קטנים מערך מסוים או מציאת מספרים שהם מעל או מתחת לערך הממוצע. בנוסף לאפשרויות שנקבעו מראש, תוכל גם ליצור כללי עיצוב מותנה מותאמים אישית באמצעות נוסחאות Excel.
הוראות אלה חלות על Excel 2019, 2016, 2013, 2010 ו-Excel עבור Microsoft 365.
החלת תנאים מרובים ב-Excel
ניתן להחיל יותר מכלל אחד על אותם נתונים כדי לבדוק תנאים שונים. לדוגמה, ייתכן שלנתוני תקציב מוגדרים תנאים המחילים שינויי עיצוב כאשר מגיעים לרמות מסוימות של הוצאה, כגון 50%, 75% ו-100%, מהתקציב הכולל.
בנסיבות כאלה, Excel קובע תחילה אם הכללים השונים מתנגשים, ואם כן, התוכנית פועלת לפי סדר עדיפות קבוע כדי לקבוע איזה כלל עיצוב מותנה להחיל על הנתונים.
מציאת נתונים שעולים על 25% ועלייה של 50%
בדוגמה הבאה, שני כללי עיצוב מותנה מותאמים אישית יוחלו על הטווח של תאים B2 עד B5.
- הכלל הראשון בודק אם הנתונים ב- תאים A2:A5 גדולים מהערך המתאים ב- B2:B5 ב- יותר מ-25%.
- הכלל השני בודק אם אותם נתונים ב- A2:A5 חורגים מהערך המתאים ב- B2:B5 ביותר מ- 50%.
כפי שניתן לראות בתמונה למעלה, אם אחד מהתנאים לעיל מתקיים, צבע הרקע של התא או התאים בטווח B1:B4 ישתנה.
- עבור נתונים שבהם ההבדל הוא יותר מ-25%, צבע הרקע של התא ישתנה לירוק.
- אם ההפרש גדול מ-50%, צבע הרקע של התא ישתנה לאדום.
הכללים ששימשו לביצוע משימה זו יוזנו באמצעות תיבת הדו-שיח New Formatting Rule. התחל בהזנת הנתונים לדוגמה לתוך תאים A1 עד C5 כפי שניתן לראות בתמונה למעלה.
בחלק האחרון של המדריך נוסיף נוסחאות לתאים C2:C4 המציגות את ההפרש המדויק באחוזים בין הערכים בתאים A2:A5 ו- B2:B5; זה יאפשר לנו לבדוק את הדיוק של כללי העיצוב המותנה.
הגדרת כללי עיצוב מותנה
ראשית, נחיל עיצוב מותנה כדי למצוא עלייה משמעותית של 25 אחוזים או יותר.
הפונקציה תיראה כך:
=(A2-B2)/A2>25%
- הדגש תאים B2 ל- B5 בגיליון העבודה.
- לחץ על כרטיסיית הבית של ribbon.
- לחץ על הסמל פורמט מותנה ב- ribbon כדי לפתוח את התפריט הנפתח.
- בחר New Rule כדי לפתוח את תיבת הדו-שיח New Formatting Rule.
- תחת בחר סוג כלל, לחץ על האפשרות האחרונה: השתמש בנוסחה כדי לקבוע אילו תאים לעצב.
- הקלד את נוסחה המצוינת למעלה ברווח שמתחת ל- פורמט ערכים שבהם הנוסחה הזו נכונה:
- לחץ על הלחצן Format כדי לפתוח את תיבת הדו-שיח. לחץ על הכרטיסייה מילוי ובחר צבע.
- לחץ על OK כדי לסגור את תיבות הדו-שיח ולחזור לגיליון העבודה.
- צבע הרקע של תאים B3 ו- B5 אמור להשתנות לצבע שבחרת.
עכשיו, נחיל עיצוב מותנה כדי למצוא עלייה של 50 אחוזים או יותר. הנוסחה תיראה כך:
- חזור על חמשת השלבים הראשונים למעלה.
- הקלד את נוסחה המסופק למעלה ברווח מתחת פורמט ערכים שבהם הנוסחה הזו נכונה:
- לחץ על הלחצן Format כדי לפתוח את תיבת הדו-שיח. לחץ על הכרטיסייה Fill ובחר צבע שונה מזה שעשית בקבוצת השלבים הקודמת.
- לחץ על OK כדי לסגור את תיבות הדו-שיח ולחזור לגיליון העבודה.
צבע הרקע של תא B3 צריך להישאר זהה, מה שמציין שההפרש באחוזים בין המספרים ב- תאים A3 ו- B3 גדול מ-25 אחוזים אך פחות או שווה ל-50 אחוזים. צבע הרקע של תא B5 אמור להשתנות לצבע החדש שבחרת, מה שמציין שההפרש באחוזים בין המספרים ב-תאים A5 ו- B5 גדול מ-50 אחוזים.
בדיקת כללי עיצוב מותנה
כדי לוודא שכללי העיצוב המותנה שהוזנו נכונים, נוכל להזין נוסחאות לתאים C2:C5 שיחשב את ההפרש המדויק באחוזים בין המספרים בטווחיםA2:A5 ו-B2:B5.
הנוסחה בתא C2 נראית כך:
=(A2-B2)/A2
- לחץ על תא C2 כדי להפוך אותו לתא הפעיל.
- הקלד את הנוסחה לעיל והקש על המקש Enter במקלדת.
- התשובה 10% צריכה להופיע ב- תא C2, מה שמציין שהמספר ב- תא A2 גדול ב-10% מהמספר ב- תא B2.
- ייתכן שיהיה צורך לשנות את העיצוב ב- תא C2 כדי להציג את התשובה באחוזים.
- השתמש ב- המילוי כדי להעתיק את הנוסחה מ- תא C2 ל- תאים C3 אל C5.
- התשובות עבור תאים C3 עד C5 צריכות להיות 30%, 25% ו-60%.
התשובות בתאים אלה מראות שכללי העיצוב המותנה מדויקים מכיוון שההבדל בין תאים A3 ו- B3 גדול מ-25 אחוזים, וההבדל בין תאים A5 ו- B5 גדול מ-50 אחוזים.
Cell B4 לא שינה צבע מכיוון שההבדל בין תאים A4 ו- B4 שווה 25 אחוז, וכלל העיצוב המותנה שלנו ציין שנדרש אחוז גדול מ-25 אחוז כדי שצבע הרקע ישתנה.
סדר קדימות עבור עיצוב מותנה
כאשר אתה מחיל כללים מרובים על אותו טווח נתונים, Excel קובע תחילה אם הכללים מתנגשים. כללים סותרים הם אלה שבהם לא ניתן להחיל את שתי אפשרויות העיצוב על אותם נתונים.
בדוגמה שלנו, הכללים מתנגשים מכיוון ששניהם משתמשים באותה אפשרות עיצוב - שינוי צבע תא הרקע.
במצב שבו הכלל השני נכון (ההבדל בערך הוא יותר מ-50 אחוז בין שני תאים), אז הכלל הראשון (ההבדל בערך גדול מ-25 אחוז) נכון גם הוא.
מכיוון שלתא אין שני רקעים בצבעים שונים בו-זמנית, Excel צריך לדעת איזה כלל עיצוב מותנה עליו להחיל.
סדר העדיפויות של Excel קובע שהכלל שנמצא גבוה יותר ברשימה בתיבת הדו-שיח Conditional Formatting Rules Manager מוחל תחילה.
כפי שמוצג בתמונה למעלה, הכלל השני בשימוש במדריך זה גבוה יותר ברשימה, ולכן יש לו עדיפות על הכלל הראשון. כתוצאה מכך, צבע הרקע של תא B5 הוא ירוק.
כברירת מחדל, כללים חדשים עוברים לראש הרשימה; כדי לשנות את הסדר, השתמש בלחצני החצים למעלה ולמטה בתיבת הדו-שיח.
יישום כללים לא סותרים
אם שני כללי עיצוב מותנה או יותר אינם מתנגשים, שניהם מיושמים כאשר התנאי שכל כלל בודק מתקיים.
אם כלל העיצוב המותנה הראשון בדוגמה שלנו עיצב את טווח התאים B2:B5 עם גבול כתום במקום צבע רקע כתום, שני כללי העיצוב המותנה לא יהיו התנגשות מכיוון שניתן להחיל את שני הפורמטים מבלי להפריע לשני.
עיצוב מותנה לעומת עיצוב רגיל
במקרה של התנגשויות בין כללי עיצוב מותנה ואפשרויות עיצוב מיושמות ידנית, כלל העיצוב המותנה תמיד מקבל עדיפות ויוחל במקום כל אפשרויות עיצוב שנוספו ידנית.
