עיצוב מותנה באקסל: המדריך האולטימטיבי עם דוגמאות

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

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

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

שימוש בעיצוב מותנה ב- Excel (דוגמאות)

במדריך זה אראה לכם שבע דוגמאות מדהימות לשימוש בעיצוב מותנה ב- Excel:

• זיהוי כפילויות במהירות באמצעות עיצוב מותנה ב- Excel.
• הדגש תאים עם ערך גבוה יותר/פחות ממספר נתונים.
• הדגשת ערכים עליונים/תחתונים של 10 (או 10%) במערך נתונים.
• הדגשת שגיאות/ריקים באמצעות עיצוב מותנה ב- Excel.
• יצירת מפות חום באמצעות עיצוב מותנה ב- Excel.
• הדגש כל שורה/טור ע 'באמצעות עיצוב מותנה.
• חפש והדגש באמצעות עיצוב מותנה ב- Excel.

1. זיהוי כפילויות במהירות

ניתן להשתמש בעיצוב מותנה ב- Excel לזיהוי כפילויות במערך נתונים.

כך תוכל לעשות זאת:

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

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

ראה גם: המדריך האולטימטיבי לאיתור והסרה של כפילויות ב- Excel.

2. הדגש תאים עם ערך גבוה יותר/פחות ממספר

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

להלן השלבים לביצוע פעולה זו:

• בחר את מערך הנתונים כולו.
• עבור לדף הבית -> עיצוב מותנה -> הדגשת כללי תאים -> יותר מאשר … / פחות מאשר …
• בהתבסס על האפשרות שתבחר (גדולה מ- או פחות ממנה) תיפתח תיבת דו -שיח. נניח, אתה בוחר באפשרות 'גדול יותר'. בתיבת הדו -שיח, הזן את המספר בשדה משמאל. הכוונה היא להדגיש תאים שיש להם מספר גדול מהמספר שצוין.

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

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

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

3. הדגשת למעלה/תחתון 10 (או 10%)

עיצוב מותנה ב- Excel יכול לזהות במהירות את 10 הפריטים המובילים או את 10% המובילים מערך נתונים. זה יכול להיות מועיל במצבים שבהם אתה רוצה לראות במהירות את המועמדים המובילים לפי ציונים או ערכי עסקה מובילים בנתוני המכירות.

באופן דומה, תוכל גם לזהות במהירות את 10 הפריטים התחתונים או 10% התחתונים במערך נתונים.

להלן השלבים לביצוע פעולה זו:

• בחר את מערך הנתונים כולו.
• עבור לדף הבית -> עיצוב מותנה -> כללים עליונים / תחתונים -> 10 הפריטים העליונים (או %) / 10 הפריטים התחתונים (או %).
• בהתבסס על מה שאתה בוחר, הוא יפתח את תיבת הדו -שיח. נניח שבחרת את 10 הפריטים המובילים, ואז היא תפתח תיבת דו -שיח כפי שמוצג להלן:
• ציין את הפורמט שיש להחיל על התאים העונים על התנאי באמצעות התפריט הנפתח מימין. ישנם כמה פורמטים קיימים שתוכל להשתמש בהם, או ציין פורמט משלך באמצעות האפשרות פורמט מותאם אישית.
• לחץ על אישור.

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

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

להלן כמה דוגמאות לאופן שבו העיצוב המותנה יפעל:

4. הדגשת שגיאות/ריקים

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

עיצוב מותנה ב- Excel יכול לעזור לך לזהות במהירות ולהדגיש תאים שיש בהם שגיאות או שהם ריקים.

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

מערך נתונים זה כולל תא ריק (A4) ושגיאות (A5 ו- A6).

להלן שלבים להדגשת התאים ריקים או שיש בהם שגיאות:

• בחר את מערך הנתונים שבו ברצונך להדגיש תאים ריקים ותאים עם שגיאות.
• עבור לדף הבית -> עיצוב מותנה -> כלל חדש.
• בתיבת הדו -שיח כלל עיצוב חדש בחר השתמש בנוסחה כדי לקבוע אילו תאים לעצב.
• הזן את הנוסחה הבאה בשדה בקטע 'ערוך את תיאור הכלל':
  = או (ISBLANK (A1), ISERROR (A1))
  • הנוסחה לעיל בודקת את כל התאים לשני תנאים - האם היא ריקה או לא והאם יש לה שגיאה או לא. אם אחד מהתנאים הוא TRUE, הוא מחזיר TRUE.
• הגדר את הפורמט שברצונך להחיל על התאים ריקים או שיש בהם שגיאות. לשם כך, לחץ על כפתור העיצוב. הוא יפתח את תיבת הדו -שיח 'עיצוב תאים', שם תוכל לציין את הפורמט.
• לחץ על אישור.

זה ידגיש באופן מיידי את כל התאים ריקים או שיש בהם שגיאות.

הערה: אינך צריך להשתמש בכל הטווח A1: A7 בנוסחה בעיצוב מותנה. הנוסחה הנ"ל משתמשת רק ב- A1. כאשר אתה מחיל נוסחה זו על כל הטווח, Excel בודק תא אחד בכל פעם ומתאים את ההפניה. לדוגמה, כאשר הוא בודק את A1, הוא משתמש בנוסחה = OR (ISBLANK (A1), ISERROR (A1)). כאשר הוא בודק את תא A2, הוא משתמש בנוסחה = OR (ISBLANK (A2), ISERROR (A2)). הוא מתאים את ההפניה באופן אוטומטי (מכיוון שמדובר בהפניות יחסיות) בהתאם לתא המנותח. אז אתה לא צריך לכתוב נוסחה נפרדת לכל תא. Excel חכם מספיק כדי לשנות את הפניה לתא מעצמו 🙂

ראה גם: שימוש ב- IFERROR ו- ISERROR לטיפול בשגיאות ב- Excel.

5. יצירת מפות חום

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

משהו כפי שמוצג להלן:

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

להלן השלבים ליצירת מפות חום באמצעות עיצוב מותנה ב- Excel.

• בחר את מערך הנתונים.
• עבור לדף הבית -> עיצוב מותנה -> סולם צבעים, ובחר באחת מסכימות הצבעים.

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

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

6. סמן כל שורה/טור אחר

ייתכן שתרצה להדגיש שורות חלופיות כדי להגדיל את הקריאות של הנתונים.

אלה נקראים קווי זברה ויכולים להיות מועילים במיוחד אם אתה מדפיס את הנתונים.

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

דרך נוספת היא שימוש בעיצוב מותנה.

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

להלן השלבים להדגשת שורות חלופיות באמצעות עיצוב מותנה ב- Excel.

• בחר את מערך הנתונים. בדוגמה למעלה, בחר A2: C13 (שאינו כולל את הכותרת). אם ברצונך לכלול גם את הכותרת, בחר את מערך הנתונים כולו.
• פתח את תיבת הדו-שיח עיצוב מותנה (בית-> עיצוב מותנה-> כלל חדש) [קיצור מקשים - Alt + O + D].
• בתיבת הדו -שיח, בחר 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
• הזן את הנוסחה הבאה בשדה בקטע 'ערוך את תיאור הכלל':
  = ISODD (ROW ())

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

זהו זה! השורות החלופיות במערך הנתונים יסומנו.

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

• הדגש שורות שוות חלופיות: = ISEVEN (ROW ())
• הדגש שורות הוספת חלופיות: = ISODD (ROW ())
• הדגש כל שורה שלישית: = MOD (ROW (), 3) = 0

7. חפש והדגש נתונים באמצעות עיצוב מותנה

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

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

להלן השלבים ליצירת פונקציונליות זו של חיפוש והדגשה:

• בחר את מערך הנתונים.
• עבור לדף הבית -> עיצוב מותנה -> כלל חדש (קיצור מקשים - Alt + O + D).
• בתיבת הדו -שיח כלל עיצוב חדש, בחר באפשרות 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
• הזן את הנוסחה הבאה בשדה בקטע 'ערוך את תיאור הכלל':
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• הגדר את הפורמט שברצונך להחיל על התאים ריקים או שיש בהם שגיאות. לשם כך, לחץ על כפתור העיצוב. הוא יפתח את תיבת הדו -שיח 'עיצוב תאים', שם תוכל לציין את הפורמט.
• לחץ על אישור.

זהו זה! כעת כאשר אתה מזין משהו בתא C2 ולחץ על enter, הוא יסמן את כל התאים התואמים.

איך זה עובד?

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

הנוסחה תחזיר TRUE לתא כאשר מתקיימים שני תנאים:

• תא C2 אינו ריק.
• התוכן של תא C2 תואם בדיוק את תוכן התא במערך הנתונים.

מכאן שכל התאים המכילים את הטקסט יפן מודגשים.

הורד את קובץ הדוגמה

אתה יכול להשתמש באותה לוגיקה כדי ליצור וריאציות כגון:

• סמן את כל השורה במקום תא.
• הדגש גם כאשר יש התאמה חלקית.
• הדגש את התאים/השורות בעת ההקלדה (דינמי) [אתה הולך לאהוב את הטריק הזה :)].

כיצד להסיר עיצוב מותנה ב- Excel

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

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

כדי להסיר עיצוב מותנה:

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

דברים שחשוב לדעת על עיצוב מותנה ב- Excel

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