עיצוב מותנה ב-Excel: כללים, נוסחאות ודוגמאות
By Mirko Peters · · Updated
עיצוב מותנה ב-Excel צובע תאים לפי כלל. למדו על הדגשת תאים, ראשון/אחרון, סרגלי נתונים, סולמות צבעים, מערכי סמלים, נוסחאות, סדר הכללים וניקוי הכללים.
Automatically translated with AI from the original post. Read the English original
עיצוב מותנה ב-Excel משנה את מראה התא, כלומר צבע המילוי, הגופן או סמל, בהתאם לתוכן שבו. בוחרים את התאים, עוברים לכרטיסייה בית, בוחרים עיצוב מותנה ומשתמשים בכלל כגון גדול מ- או ערכים כפולים. כאשר הנתונים משתנים, העיצוב משתנה איתם. מאמר זה מיועד למשתמשי Excel שבונים דוחות, מעקבים או רשימות, ורוצים שתאריכים שחלף מועדם, כפילויות או התוצאות המובילות יבלטו, בלי לבדוק כל שורה ידנית. תלמדו על סוגי הכללים המובנים, איך כותבים כלל מבוסס נוסחה, איך פועל סדר הכללים, וכיצד מנקים ומפתרים בעיות בכללים.
עיקרי הדברים
- בחרו את התאים והקליקו על Conditional Formatting (עיצוב מותנה) בכרטיסייה Home (בית). ניתן גם להשתמש בכפתור Quick Analysis (ניתוח מהיר) או במקש הקיצור Ctrl+Q.
- Highlight Cells Rules (כללי הדגשת תאים) ו-Top/Bottom Rules (כללי עליון/תחתון) מכסים רוב הצרכים היומיומיים: Greater Than (גדול מ-), Between (בין), Text that Contains (טקסט שמכיל), Duplicate Values (ערכים כפולים), Above Average (מעל הממוצע).
- לוגיקה משלכם, בחרו New Rule (כלל חדש), ואז Use a formula to determine which cells to format (השתמש בנוסחה כדי לקבוע אילו תאים יעוצבו). הנוסחה חייבת להתחיל ב-= ולהחזיר TRUE או FALSE.
- הכללים פועלים מלמעלה למטה בתיבת Manage Rules (נהל כללים). כאשר שני כללים מתנגשים, הכלל שגבוה יותר גובר, ו-Stop If True (עצור אם מתקיים) עוצר את הבדיקה.
- עיצוב מותנה לא יכול להפנות לחוברות עבודה אחרות, ותנאי טקסט מוגבלים ל-255 תווים.
המקור לרוב הפרטים כאן הוא דף התמיכה של Microsoft בנושא שימוש בעיצוב מותנה להדגשת מידע ב-Excel. כאשר אני מוסיף משהו שאינו מופיע בדף הזה, אני מציין זאת.
כיצד מחילים עיצוב מותנה ב-Excel
Microsoft מציינת שאפשר להחיל עיצוב מותנה על טווח שנבחר, על טווח בעל שם או על טבלת Excel. ב-Excel for Windows זה פועל גם על דוחות PivotTable. יש שתי דרכים להתחיל.
מהסרט.
- בחרו את התאים.
- בכרטיסייה בית, בחרו עיצוב מותנה.
- הצביעו על סוג כלל, בחרו כלל ופעלו לפי תיבת הדו-שיח שנפתחת. ברוב הכללים היא מבקשת ערך או טקסט, וגם עיצוב.
באמצעות ניתוח מהיר.
- בחרו את הנתונים שלכם. כפתור Quick Analysis (ניתוח מהיר) קטן מופיע בפינה הימנית התחתונה של הבחירה.
- לחצו על הלחצן, או הקישו Ctrl+Q.
- בכרטיסייה עיצוב, הצביעו על אפשרות כדי לראות תצוגה מקדימה, ואז לחצו עליה. אם מופיעה תיבת הדו-שיח טקסט שמכיל, הזינו את הטקסט ולחצו על אישור.
האפשרויות בכרטיסייה זו תלויות בנתונים. בחירה שמכילה טקסט בלבד מציעה אפשרויות כגון טקסט, כפילות, ייחודי, שווה ל- וניקוי. בחירה מספרית מציעה סרגלי נתונים, צבעים, מערכי סמלים, גדול מ-, 10% הראשונים וניקוי. זו הדרך המהירה ביותר לנסות מראה לפני שמחליטים עליו.
התפריט בלשונית הבית הוא ארגז הכלים האמיתי, וכל שאר המאמר עוקב אחריו: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, New Rule, Manage Rules ו-Clear Rules. אם תרצו רקע על התכונה עצמה, עיינו בדף עיצוב מותנה.
הדגשת תאים בעזרת כללים פשוטים
Highlight Cells Rules הם הכללים המהירים. Microsoft מציעה כללים כמו Between, Equal To, Text that Contains, A Date Occurring ו-Duplicate Values (Greater Than ו-Less Than נמצאים באותו תפריט). כל אחד מהם שואל שאלה פשוטה על תא, ומדגיש את התאים שבהם התשובה היא כן.
שימושים נפוצים:
- Greater Than בעמודת מכירות, כדי לסמן כל ערך שחורג מיעד.
- Between להצגת ערכים בטווח מקובל, ואותו כלל על מחוץ לטווח אם אתם מעוניינים בערכים החריגים.
- Equal To לערך מדויק, למשל קוד סטטוס או ציון של 100.
- Text that Contains לסימון כל תא שמכיל מילה כמו "late" או שם מוצר. גם מילים חלקיות יתאימו, כי הכלל מחפש את הטקסט בכל מקום בתא.
- A Date Occurring לסימון תאריכים שנופלים בתקופה נבחרת. Excel מציע ערכים מוגדרים מראש בתיבת הדו-שיח, לכן בחרו באחד מהם במקום להקליד תאריך ידנית.
- Duplicate Values לסימון כפילויות ברשימה. זו הבדיקה המהירה ביותר לפני שבונים טבלת ציר או חיפוש.
Top/Bottom Rules פועלים על דירוג: Top 10 Items, Bottom 10 %, Above Average ו-Below Average. המגבלות של Microsoft לכללים אלה: המספר העליון או התחתון נע בין 1 ל-1000, או בין 1 ל-100 כאשר משתמשים באחוזים, והאחוזונים נעים בין 0 ל-100 (מקלידים את המספר ללא סימן אחוז).
דוגמה קטנה תמחיש זאת. נניח שעמודה C מכילה ערכי הזמנות. בחרו את C2 עד השורה האחרונה, בחרו Highlight Cells Rules > Greater Than, הזינו את היעד והגדירו מילוי ירוק. הוסיפו Less Than עם ערך רצפה ומילוי כתום על אותם תאים. כעת ההזמנות החזקות והחלשות בולטות, והאמצעיות נשארות רגילות. שנו מספר והצבעים יזוזו בהתאם.
סרגלי נתונים, סולמות צבע וקבוצות סמלים
שלושת אלה מראים כיצד ערכים משתווים בתוך טווח, ולא האם הם עוברים מבחן. כך מתארים את התכונות הללו בדפי Microsoft:
- Data Bars מציבים פס בכל תא. פס ארוך יותר פירושו ערך גדול יותר. אפשר לבחור מילוי מדורג או מלא, להציג מסגרות, להציג פסים בלבד ולעצב ערכים שליליים.
- Color Scales משתמשים בשני צבעים או בשלושה. בסולם דו-צבעוני צבע אחד מסמן ערכים גבוהים והשני מסמן ערכים נמוכים. סולם תלת-צבעוני מוסיף צבע אמצעי.
- Icon Sets משתמשים בשלוש עד חמש קטגוריות, וכל סמל מכסה טווח של ערכים. אפשר להפוך את סדר הסמלים, להציג סמלים בלבד, או להסתיר את הסמל לתאים מסוימים באמצעות No Cell Icon.
השלבים זהים לשלושתם: בחרו את התאים, עברו אל Home > Conditional Formatting, הצביעו על הסוג ולחצו על סגנון. בדף של Microsoft על סרגלי נתונים, סולמות צבע וקבוצות סמלים מופיעה טיפ מעשי: הרחבת עמודה שיש בה סרגלי נתונים מקלה על זיהוי ההפרשים בין הערכים. אפשר לשלב קבוצות סמלים עם עיצובים מותנים אחרים.
השתמשו בהם בזהירות. עמודה מלאה בסולמות תלת-צבעוניים מעל מילויים וסמלים קשה לקריאה. סימן אחד לכל עמודה היא הרגל טובה. גם צבע לבדו לא צריך לשאת את המשמעות, שכן יש קוראים שאינם מבחינים בין אדום לירוק. סמלים או עמודת טקסט עוזרים בכך, ואני מוסיף אחד כזה לכל דוח שמיועד לקהל רחב.
למגבלות של סולמות צבע, Microsoft קובעת שהמינימום חייב להיות קטן מהמקסימום, ושנקודת האמצע מוגדרת כברירת מחדל ל-50 אחוזים.
בנו כלל משלכם עם נוסחה
כאשר הכללים המובנים אינם מספיקים, השתמשו ב-כלל חדש. הפעולה פותחת את תיבת הדו-שיח עיצוב חדש של כלל, שבה בוחרים באפשרות השתמש בנוסחה כדי לקבוע אילו תאים לעצב. לפי ההנחיות של Microsoft, הנוסחה חייבת להתחיל ב-= ולהחזיר TRUE או FALSE. התאים שבהם הנוסחה מחזירה TRUE יקבלו את העיצוב. בדקו תחילה שהנוסחה אינה מניבה שגיאות, ואם שגיאות עלולות להופיע, השתמשו בפונקציות IS או ב-IFERROR.
כתבו את הנוסחה עבור התא הראשון בטווח שנבחר. אם הטווח מתחיל ב-B2, כתבו את הנוסחה כאילו אתם מתייחסים ל-B2, ו-Excel יחיל את אותה לוגיקה על שאר התאים. זה החלק שבו אנשים נתקעים, ולכן לסימן הדולר יש משמעות: $B2 נועל את העמודה ומאפשר לשורה לזוז, וכך מעצבים שורה שלמה על בסיס עמודה אחת.
הדוגמאות של Microsoft מראות מה אפשרי:
- תאריכים שפגים.
=B2<TODAY()למילוי אדום של תגים שפג תוקפם, ו-=B2<TODAY()+60למילוי צהוב של תגים שפגים תוך 60 יום. כלל האדום חייב להופיע גבוה יותר ברשימה מכלל הצהוב, אחרת כלל הצהוב יתפוס גם את התגים שפג תוקפם. - שורות מתחלפות.
=MOD(ROW(),2)=1מצבע כל שורה בעלת מספר אי-זוגי. - כפילויות.
=COUNTIF($A$2:$A$400,A2)>1מסמן כפילויות בטווח A2:A400. - שני תנאים. הדוגמה התקציבית של Microsoft משתמשת ב-
ANDוב-ORכדי לצבוע תוצאה בירוק כאשר גם המקדמה וגם התשלום החודשי נכנסים לתקציב, ובאדום כאשר אחד מהם אינו נכנס.
שימו לב לתבנית בדוגמת COUNTIF: הטווח מוחלט ($A$2:$A$400) והתא הנבדק יחסי (A2). כך נראים רוב כללי הנוסחאות השימושיים.
אם הכלל אינו נראה כפועל, בדקו שהנוסחה מחזירה TRUE עבור התא הראשון, ושכתבתם אותה עבור התא השמאלי-עליון של הבחירה. כלל שנכתב עבור התא הלא נכון מתעתק בשורה אחת ונראה אקראי.
דוגמה מעשית: מעקב משימות
הנה הגדרה שאפשר להעתיק תוך חמש דקות. בעמודה A נמצאים שמות המשימות, בעמודה B תאריכי היעד, בעמודה C סטטוס כמו Open או Done, ובעמודה D שעות העבודה.
- בחרו את B2 עד השורה האחרונה. הוסיפו כלל נוסחה עם
=B2<TODAY()ומילוי אדום. כלל זה מסמן כל מה שעבר את תאריך היעד. - באותם תאים הוסיפו
=B2<TODAY()+7עם מילוי צהוב, והזיזו את הכלל האדום מעליו ב-Manage Rules. כעת משימות שפג תוקפן מסומנות באדום, משימות שתאריכן קרוב ב-שבוע מסומנות בצהוב, ושאר המשימות נשארות ללא צבע. - בחרו את כל הטבלה, מ-A2 עד D100, והוסיפו כלל נוסחה כגון
=$C2="Done"עם גופן אפור. סימן הדולר נועל את העמודה, ולכן השורה כולה נצבעת באפור כאשר הסטטוס הוא Done. הנוסחה הזו היא תבנית משלי ולא אחת מהדוגמאות של Microsoft, אבל היא פועלת לפי אותם כללים: היא מתחילה בסימן שווה ומחזירה TRUE או FALSE. - בחרו מ-D2 והחילו Data Bars כך שהמשימות הכבדות ביותר יהיו בולטות לעין.
שני דברים עלולים להשתבש. אם כלל האדום נמצא מתחת לכלל הצהוב, משימות שפג תוקפן ייראו צהובות, מפני ששני הכללים מתקיימים והכלל הגבוה יותר גובר. אם כלל ה-Done נמצא בראש הרשימה ואתם מגדירים Stop If True, צבעי התאריכים של המשימות שהושלמו ייעלמו, וזה ככל הנראה מה שתרצו. מומלץ לנסות על עותק לפני כן ולשנות את הסדר כדי לראות את ההשפעה.
שליטה בסדר הכללים
פתחו עיצוב מותנה > Manage Rules כדי לראות את Conditional Formatting Rules Manager. Microsoft מסבירה כיצד הוא פועל:
- הכללים מוערכים מלמעלה למטה, וכללים חדשים מתווספים לראש הרשימה כברירת מחדל. Move Up ו-Move Down משנים את הסדר.
- כאשר הכללים אינם מתנגשים, כולם חלים. לדוגמה, כלל אחד יכול להגדיר גופן מודגש וכלל אחר מילוי אדום.
- כאשר הכללים מתנגשים, הכלל שמעליו גובר.
- עיצוב מותנה שמתקיים גובר על עיצוב ידני באותם תאים. אם תמחקו את הכלל, העיצוב הידני יחזור.
- Stop If True עוצר את ההערכה לאחר שכלל מתקיים. לפי Microsoft, האפשרות מחקה את התנהגותן של גרסאות Excel ישנות יותר. אי אפשר להגדיר אותה לכללי סרגל נתונים, סולם צבעים או קבוצת סמלים.
אם אתם רואים צבע שלא ציפיתם לו, מנהל הכללים הוא המקום לבדוק.
העתקה, חיפוש וניקוי של עיצוב מותנה
- העתקה לתאים אחרים. השתמשו ב-Format Painter (מברשת עיצוב) בכרטיסייה Home (בית). לחצו עליו פעמיים כדי לצבוע כמה אזורים, והקישו Esc כדי לעצור. אם הכלל משתמש בנוסחה, בדקו את ההפניות לאחר מכן.
- איתור תאים מעוצבים. השתמשו ב-Find & Select > Conditional Formatting, או ב-Go To Special > Conditional formats.
- ניקוי כללים. בחרו תאים, ואז Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, או Clear Rules from Entire Sheet.
מגבלה אחת לפי Microsoft: הדבקת תאים מעוצבים למופע אחר של Excel אינה מעבירה את הכלל, לכן העתיקו בתוך מופע אחד אם אתם צריכים שהכללים יישמרו.
מגבלות וסיבות שבגללן כלל עלול לא לפעול
- חוברת עבודה אחרת. עיצוב מותנה אינו יכול להפנות לחוברות עבודה חיצוניות. הביאו קודם את הנתונים לתוך הקובץ.
- PivotTables. באזור Values של PivotTable אי אפשר לעצב לפי טקסט, לפי תאריך או לפי ערכים ייחודיים וכפולים. שם פועלים רק כללים מבוססי מספרים.
- טקסט ארוך. קריטריוני טקסט מוגבלים ל-255 תווים.
- סדר. כלל שמופיע גבוה יותר גובר על כלל נמוך יותר. העלו את הכלל הספציפי יותר.
- מילוי ידני. אם לתא יש מילוי ידני והכלל מתקיים, הכלל גובר. אם התוצאה נראית שגויה, בדקו אם הכלל אכן מתקיים.
- Excel for the web. הדף שהשתמשתי בו אינו מפרט שלבים או מגבלות ספציפיים לגרסת הרשת. לא הייתי מבטיח שכל סוגי הכללים מתנהגים אותו דבר בדפדפן. בדקו על עותק.
טעויות נפוצות
- כתיבת הנוסחה לתא הלא נכון, כך שההדגשה נופלת שורה אחת הצידה.
- שימוש בהפניות מוחלטות (
$A$1) כשנדרשה הפניה יחסית, וצביעה של הכול או של כלום. - הערמת כללים על כללים עד שאף אחד לא יודע למה תא אדום. פתחו את Manage Rules ונקו.
- הדבקת תאים מעוצבים לחלון Excel אחר וציפייה שהכללים יגיעו איתם. לפי Microsoft, הם לא מגיעים.
- הסתמכות על אדום וירוק בלבד.
- החלת כלל על עמודה שלמה כשרק 200 שורות צריכות אותו.
נושאים קשורים לחקירה
- הדגישו כפילויות לפני שאתם מסירים אותן. כלל Duplicate Values הוא בדיקה טובה לפני ניקוי רשימה.
- רשימות נפתחות וכללים יחד. סמנו שורה לפי הסטטוס שמישהו בוחר מתוך רשימה.
- עיצוב מותנה ב-PivotTables. באזור Values פועלים רק כללים מבוססי מספרים, לכן תכננו את הפריסה בהתאם.
- לוחות בקרה. שילוב של סרגלי נתונים, אייקונים ו-slicers הופך דוח לקריא במבט אחד.
- ביצועים בגיליונות גדולים. שמרו על טווחים מצומצמים ומספר כללים קטן. הדף של Microsoft אינו מפרסם מספרים, לכן בדקו עם הנתונים שלכם.
הצעד הבא
פתחו גיליון אמיתי והחילו כלל אחד עם Highlight Cells Rules. אחר כך נסו כלל נוסחה כגון =B2<TODAY() על עמודת תאריכים, ופתחו את Manage Rules כדי לראות איך הסדר פועל. שמרו על עיצוב מותנה פשוט ב-Excel: סימון אחד ברור לכל עמודה עדיף על קשת צבעים.