שימוש בפורמולות עבור עיצוב מותנה ב- Excel

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

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

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

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

01 מתוך 04

החלת כללים מרובים ב - Excel

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

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

מציאת נתונים העולים על 25% ועליות של 50%

בדוגמה הבאה, שני כללי התאמה המותאמים אישית יחולו על טווח תאים B2 ל B5.

• הכלל הראשון בודק אם הנתונים תאים A2 ל A5 גדול מהערך המקביל B2 ל B5 על ידי יותר מ -25%.
• הכלל השני בודק אם הנתונים זהה A2: A5 ערך עולה על הערך המקביל B2: B5 על ידי יותר מ -50%.

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

• לקבלת נתונים שבהם ההפרש גדול מ -25%, צבע הרקע של התא ישתנה לירוק.
• אם ההבדל גדול מ -50% צבע הרקע התא ישתנה לאדום.

הכללים המשמשים לביצוע משימה זו ייכנסו לשימוש בתבנית המותנית כלל עיצוב חדש תיבת דיאלוג. התחל על ידי הזנת נתוני המדגם לתאים A1 עד C5 כפי שמוצג בתמונה לעיל

בתוך ה צעד שלישי של המדריך יוסיף נוסחאות לתאים C2: C4 המראים את ההבדל המדויק באחוזים בין הערכים בתאים A2: A5 ו B2: B5 על מנת לבדוק את הדיוק של כללי העיצוב המותנה.

02 מתוך 04

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

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

עיצוב מותנה כדי למצוא יותר מ 25 אחוז גידול

= (A2-B2) / A2> 25%

1. הדגש תאים B2 ל B5 בגליון העבודה.
2. הקלק על ה הכרטיסייה דף הבית של ה סרט.
3. הקלק על ה עיצוב מותנה סמל ב סרט כדי לפתוח את התפריט הנפתח.
4. בחר חוק חדש לפתוח את כלל עיצוב חדש תיבת דיאלוג.
5. מ ה סגנון: בחר את קלאסי אפשרות.
6. בתפריט הנפתח השני, לחץ על האפשרות האחרונההשתמש בנוסחה כדי לקבוע אילו תאים לעצב.
7. הקלד את ה נוסחה בתנאי לעיל לתוך שטח בתנאי.
8. הקלק על ה פורמט עם: הנפתח ולאחר מכן לבחור את אור אדום מלא עם טקסט אדום כהה אפשרות.
9. לחץ על בסדר כדי לסגור את תיבות הדו-שיח ולחזור לגליון העבודה.
10. בשלב זה, את צבע הרקע של תאים B3 ו B5 צריך להיות ירוק.

עיצוב מותנה למצוא יותר מ 50 אחוז גידול

= (A2-B2) / A2> 50%

הדגש תאים B2 ל B5 בגליון העבודה.

1. הקלק על ה הכרטיסייה דף הבית של ה סרט.
2. הקלק על ה עיצוב מותנה סמל ב סרט כדי לפתוח את התפריט הנפתח.
3. בחר חוק חדש לפתוח את כלל עיצוב חדש תיבת דיאלוג.
4. מ ה סגנון: בחר את קלאסי אפשרות.
5. בתפריט הנפתח השני, לחץ על האפשרות האחרונההשתמש בנוסחה כדי לקבוע אילו תאים לעצב.
6. הקלד את ה נוסחה בתנאי לעיל לתוך שטח בתנאי.
7. הקלק על ה פורמט עם: הנפתח ולאחר מכן לבחור את אור ירוק מילוי עם טקסט ירוק כהה אפשרות.
8. לחץ על בסדר כדי לסגור את תיבות הדו-שיח ולחזור לגליון העבודה.

צבע הרקע של תא B3 עדיין צריך להיות ירוק המציין כי ההבדל באחוזים בין מספרים ב תאים A3 ו B3 הוא גבוה מ -25% אך פחות או שווה ל -50%. צבע הרקע של תא B5 צריך להשתנות לאדום המציין כי ההפרש באחוזים בין המספרים תאים A5 ו B5 גדול מ -50%.

03 מתוך 04

בדיקת כללי עיצוב מותנה

כדי לבדוק שכללי הפורמט המותנים שהוזנו נכונים, אנו יכולים להזין נוסחאות לתאים C2: C5 כי יחשב את ההבדל המדויק אחוז בין המספרים בטווחים A2: A5 ו B2: B5.

= (A2-B2) / A2

1. לחץ על תא C2 כדי להפוך אותו לתא הפעיל.
2. הקלד את הנוסחה לעיל ולחץ על להיכנס מפתח במקלדת.
3. התשובה צריכה להיות 10% תא C2, המציין את המספר ב תא A2 הוא 10% יותר מאשר מספר ב B2 B2.
4. ייתכן שיהיה צורך לשנות את העיצוב תא C2 כדי להציג את התשובה כאחוז.
5. להשתמש ב ידית מילוי להעתיק את הנוסחה מ תא C2 ל תאים C3 ל C5.
6. התשובות עבור תאים C3 ל C5 צריך להיות 30%, 25% ו -60%.

התשובות בתאים אלה מראות כי חוקי העיצוב המותנים שנוצרו נכונים מאז ההבדל בין תאים A3 ו B3 הוא גדול מ -25% ואת ההבדל בין תאים A5 ו B5 גדול מ -50%.

תא B4 לא שינו צבע כי ההבדל בין תאים A4 ו B4 שווה 25 אחוזים, ואת כלל העיצוב המותנה שלנו צוין כי אחוז גדול מ 25 אחוזים נדרש צבע רקע כדי לשנות את הירוק.

04 מתוך 04

סדר עדיפות עבור עיצוב מותנה

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

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

במצב שבו הכלל השני נכון (ההפרש בערכו גדול מ -50% בין שני תאים), אזי הכלל הראשון (ההבדל בשווי גדול מ -25%) נכון גם כן.

סדר העדיפויות של Excel

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

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

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

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

החלת כללים שאינם סותרים

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

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

כתוצאה, תא B5 יהיה גם גבול ירוק צבע רקע אדום, שכן ההבדל בין המספרים פנימה תאים A5 ו B5 הוא גדול מ 25 ו -50 אחוזים.

עיצוב מותנה לעומת עיצוב רגיל

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

אם צבע רקע צהוב הוחל תחילה תאים B2 ל B5 בדוגמה, לאחר הוספת כללי הפורמט המותנה, רק תאים B2 ו B4 יישאר צהוב. בגלל הכללים עיצוב מותנה נכנס לחול על תאים B3 ו B5, צבעי הרקע שלהם ישתנו מצהוב לירוק ואדום בהתאמה.