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

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

במקרה שאתה מעדיף לקרוא הוראה בכתב, להלן ההדרכה.

עיצוב מותנה מאפשר לך לעצב תא (או טווח תאים) על סמך הערך שבו.

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

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

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

לחץ כאן להורדת קובץ הדוגמה והמשך.

הדגש שורות המבוססות על קריטריוני טקסט

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

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

1. בחר את מערך הנתונים כולו (A2: F17 בדוגמה זו).
2. לחץ על הכרטיסייה דף הבית.
3. בקבוצה סגנונות, לחץ על עיצוב מותנה.
4. לחץ על 'כללים חדשים'.
5. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
6. בשדה הנוסחה, הזן את הנוסחה הבאה: = $ C2 = "בוב"
7. לחץ על הלחצן 'פורמט'.
8. בתיבת הדו -שיח שנפתחת, הגדר את הצבע שבו ברצונך שהשורה תודגש.
9. לחץ על אישור.

זה ידגיש את כל השורות שבהן שמו של נציג המכירות הוא 'בוב'.

לחץ כאן להורדת קובץ הדוגמה והמשך.

איך זה עובד?

עיצוב מותנה בודק בכל תא את התנאי שציינו, כלומר = $ C2 = "בוב"

אז כאשר הוא מנתח כל תא בשורה A2, הוא יבדוק אם לתא C2 יש את השם בוב או לא. אם כן, התא הזה מודגש, אחרת לא.

שים לב שהטריק כאן הוא להשתמש בסימן דולר ($) לפני אלפבית העמודה ($ C1). על ידי כך, נעלנו את העמודה להיות תמיד C. כך שגם כאשר תא A2 נבדק לגבי הנוסחה, הוא יבדוק את C2, וכאשר A3 נבדק לגבי המצב, הוא יבדוק את C3.

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

הדגש שורות בהתבסס על קריטריונים של מספרים

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

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

נניח שיש לי את אותם הנתונים (כפי שמוצג להלן), ואני רוצה להדגיש את כל השורות שבהן הכמות היא יותר מ -15.

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

1. בחר את מערך הנתונים כולו (A2: F17 בדוגמה זו).
2. לחץ על הכרטיסייה דף הבית.
3. בקבוצה סגנונות, לחץ על עיצוב מותנה.
4. לחץ על 'כללים חדשים'.
5. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
6. בשדה הנוסחה, הזן את הנוסחה הבאה: = $ D2> = 15
7. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע שבו ברצונך שהשורה תודגש.
8. לחץ על אישור.

פעולה זו תדגיש את כל השורות בהן הכמות גדולה או שווה ל -15.

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

לדוגמה, אם ברצונך להדגיש את כל השורות שבהן התאריך הוא לאחר 10 ביולי 2020-2022, תוכל להשתמש בנוסחת התאריך שלהלן:

= $ A2> DATE (2018,7,10)

הדגשת שורות המבוססות על קריטריונים מרובים (AND/OR)

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

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

1. בחר את מערך הנתונים כולו (A2: F17 בדוגמה זו).
2. לחץ על הכרטיסייה דף הבית.
3. בקבוצה סגנונות, לחץ על עיצוב מותנה.
4. לחץ על 'כללים חדשים'.
5. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
6. בשדה הנוסחה, הזן את הנוסחה הבאה: = AND ($ C2 = "בוב", $ D2> 10)
7. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע שבו ברצונך שהשורה תודגש.
8. לחץ על אישור.

בדוגמה זו, רק השורות האלה מודגשות במקום בו מתקיימים שני התנאים (הדבר נעשה באמצעות נוסחת AND).

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

= או ($ C2 = "בוב", $ D2> 15)

לחץ כאן להורדת קובץ הדוגמה והמשך.

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

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

לדוגמה, ייתכן שתרצה להדגיש את כל השורות שבהן הכמות היא יותר מ -20 בירוק והכמות שבהן היא יותר מ -15 (אך פחות מ -20) בכתום.

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

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

1. בחר את מערך הנתונים כולו (A2: F17 בדוגמה זו).
2. לחץ על הכרטיסייה דף הבית.
3. בקבוצה סגנונות, לחץ על עיצוב מותנה.
4. לחץ על 'כללים חדשים'.
5. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
6. בשדה הנוסחה, הזן את הנוסחה הבאה: = $ D2> 15
7. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע כתום.
8. לחץ על אישור.
9. בתיבת הדו -שיח 'מנהל כללים בעיצוב מותנה', לחץ על 'כלל חדש'.
10. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
11. בשדה הנוסחה, הזן את הנוסחה הבאה: = $ D2> 20
12. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע לירוק.
13. לחץ על אישור.
14. לחץ על החל (או אישור).

השלבים לעיל יהפכו את כל השורות עם הכמות ליותר מ -20 בירוק ולאלה עם יותר מ -15 (אך פחות מ -20 בכתום).

הבנת סדר הכללים:

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

בדוגמה לעיל, מצב הצבע הירוק נמצא מעל מצב הצבע הכתום.

אם זה הפוך, כל השורות היו בצבע כתום בלבד.

למה?

כי שורה שבה הכמות היא יותר מ -20 (נניח 23) עונה על התנאים שלנו (= $ D2> 15 וגם = $ D2> 20). ומכיוון שמצב הכתום נמצא למעלה, הוא מקבל העדפה.

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

לחץ כאן להורדת קובץ הדוגמה והמשך.

הדגש שורות היכן שכל תא ריק

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

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

1. בחר את מערך הנתונים כולו (A2: F17 בדוגמה זו).
2. לחץ על הכרטיסייה דף הבית.
3. בקבוצה סגנונות, לחץ על עיצוב מותנה.
4. לחץ על 'כללים חדשים'.
5. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
6. בשדה הנוסחה, הזן את הנוסחה הבאה: = COUNTIF ($ A2: $ F2, ””)> 0
7. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע כתום.
8. לחץ על אישור.

הנוסחה הנ"ל סופרת את מספר התאים הריקים. אם התוצאה היא יותר מ -0, פירוש הדבר שיש תאים ריקים בשורה זו.

אם כל אחד מהתאים ריק, הוא מדגיש את כל השורה.

הדגש שורות בהתבסס על בחירה נפתחת

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

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

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

להלן השלבים ליצירת זה:

1. צור רשימה נפתחת בתא A2. כאן השתמשתי בשמות של נציג המכירות כדי ליצור את הרשימה הנפתחת. להלן מדריך מפורט כיצד ליצור רשימה נפתחת ב- Excel.
2. בחר את מערך הנתונים כולו (C2: H17 בדוגמה זו).
3. לחץ על הכרטיסייה דף הבית.
4. בקבוצה סגנונות, לחץ על עיצוב מותנה.
5. לחץ על 'כללים חדשים'.
6. בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
7. בשדה הנוסחה, הזן את הנוסחה הבאה: = $ E2 = $ A $ 2
8. לחץ על הלחצן 'פורמט'. בתיבת הדו -שיח שנפתחת, הגדר את הצבע כתום.
9. לחץ על אישור.

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

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