עיצוב מותנה עם נוסחאות (10 דוגמאות)

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

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

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

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

להלן תוצאת הכלל שהוחל על הטווח B4: F8 בגיליון אלקטרוני זה:

להלן הנוסחה המדויקת בה נעשה שימוש:

 
= OR (B4='apple',B4='kiwi',B4='lime')

התחלה מהירה

תוכל ליצור כלל עיצוב מותנה המבוסס על נוסחה בארבעה שלבים פשוטים:

1. בחר את התאים שברצונך לעצב.

2. צור כלל עיצוב מותנה, ובחר באפשרות נוסחה

3. הזן נוסחה המחזירה TRUE או FALSE.

4. הגדר אפשרויות עיצוב ושמור את הכלל.

ה פונקציית ISODD מחזירה TRUE רק למספרים אי -זוגיים, ומפעילה את הכלל:

excel תואמים מספר קריטריונים ממערכים שונים

וִידֵאוֹ: כיצד ליישם עיצוב מותנה באמצעות נוסחה

היגיון בנוסחה

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

להחיל תבנית מספר חשבונאית ב- Excel

 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

הנוסחאות שלעיל כולן מחזירות TRUE או FALSE, כך שהן פועלות בצורה מושלמת כמניע לעיצוב מותנה.

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

דוגמאות לנוסחאות

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

הדגש הזמנות מטקסס

כדי להדגיש שורות המייצגות הזמנות מטקסס (מקוצר TX), השתמש בנוסחה שנועלת את ההפניה לעמודה F:

 
=$F5='TX'

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

וִידֵאוֹ: כיצד להדגיש שורות עם עיצוב מותנה

הדגש תאריכים ב -30 הימים הבאים

כדי להדגיש תאריכים המתרחשים ב -30 הימים הבאים, אנו זקוקים לנוסחה ש (1) מוודאת שתאריכים יהיו בעתיד ו (2) מוודאים שהתאריכים יהיו 30 יום או פחות מהיום. אחת הדרכים לעשות זאת היא להשתמש ב- ותפקוד ביחד עם ה פונקציה NOW ככה:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

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

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

הדגש את הבדלי העמודות

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

 
=$B4$C4

ראה גם: גרסה של נוסחה זו המשתמשת בפונקציה EXACT לביצוע השוואה תלויית רישיות .

הדגש ערכים חסרים

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

 
= COUNTIF (list,B5)=0

נוסחה זו פשוט בודקת כל ערך רשימה א כנגד ערכים בטווח 'הרשימה' בשם (D5: D10). כאשר הספירה היא אפס, הנוסחה מחזירה TRUE ומפעילה את הכלל, המדגיש ערכים ב- רשימה א שחסרים מ רשימה ב .

וִידֵאוֹ: כיצד למצוא ערכים חסרים באמצעות COUNTIF

הדגש נכסים עם 3+ חדרי שינה מתחת ל -350 אלף דולר

כדי למצוא נכסים ברשימה זו הכוללים לפחות 3 חדרי שינה אך הם פחות מ -300,000 $, תוכל להשתמש בנוסחה המבוססת על הפונקציה AND:

 
= AND ($C5<350000,$D5>=3)

סימני הדולר ($) נועלים את ההתייחסות לעמודות C ו- D, ו- ותפקוד משמש כדי לוודא ששני התנאים נכונים. בשורות שבהן הפונקציה AND מחזירה TRUE, העיצוב המותנה מוחל:

הדגש ערכים עליונים (דוגמה דינמית)

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

הנוסחה המשמשת לכלל זה היא:

איך להיפטר מחוצה דרך ב- Excel

 
=B4>= LARGE (data,input)

כאשר 'נתונים' הם הטווח B4: G11, ו- 'input' הוא הטווח F2 בשם. לדף זה יש פרטים והסבר מלא .

תרשימי גאנט

תאמין או לא, אתה יכול אפילו להשתמש בנוסחאות ליצירת תרשימי Gantt פשוטים עם עיצוב מותנה כמו זה:

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

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

מאמר זה מסביר את הנוסחה לסורגים , ו מאמר זה מסביר את הנוסחה להצללת סוף שבוע .

תיבת חיפוש פשוטה

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

הנוסחה בה נעשה שימוש היא:

 
= ISNUMBER ( SEARCH ($F,B2))

לפרטים נוספים והסבר מלא ראו:

• מאמר: כיצד להדגיש תאים המכילים טקסט ספציפי
• מאמר: כיצד להדגיש שורות המכילות טקסט ספציפי
• וִידֵאוֹ: כיצד לבנות תיבת חיפוש להדגשת נתונים

פתרון תקלות

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

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

נוסחאות דמה

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

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

וִידֵאוֹ: בדוק עיצוב מותנה עם נוסחאות דמה

מגבלות

ישנן כמה מגבלות הכרוכות בעיצוב מותנה המבוסס על נוסחה:

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

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

משאבי נוסחה נוספים של CF

• יותר מ -30 דוגמאות לעיצוב מותנה
• הדרכת וידאו עם דפי עבודה לתרגול