לְהִצטַיֵן

בדוק עיצוב מותנה עם נוסחאות דמה

Test Conditional Formatting With Dummy Formulas

התחלה מהירה | דוגמאות | פתרון בעיות | הַדְרָכָה

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





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

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





  1. ערוך את הכלל
  2. ערוך את הנוסחה בעזרת 'הניחוש הטוב ביותר' שלך
  3. שמור את הכלל כדי לראות מה קורה
  4. חזור על הצורך

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

למרבה המזל, יש פתרון קל: נוסחאות דמה.



דרך טובה יותר - בדוק עם נוסחאות דמה

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

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

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

להצטיין למצוא ערכים ייחודיים בעמודה

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

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

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

דוגמא 1 - נוסחה פשוטה

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

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

בעיה - הדגש ערכים מעל 100 עם כלל עיצוב מותנה

יש לנו מספיק מקום מימין, אז נוסיף לשם את נוסחאות הדמה שלנו. בתא H4, הוסף את הנוסחה הראשונה. במקרה זה, אנו רוצים להשתמש ב:

 
=B4>100

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

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

העתק נוסחאות לרוחב ולמטה

שימו לב שאנו מקבלים ערך TRUE או FALSE בכל תא. אם נבדוק כמה הפניות, תוכל לראות שכל נוסחה מעריכה תא בנתונים, ביחס ל- B4. כל ההפניות ל- B4 השתנו מאז B4 הוזנה ככתובת יחסית.

בדיקת הפניות לנוסחה

בדיקת הפניות - כל נוסחה מתייחסת לתא ביחס ל- B4

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

נוסחאות דמה מציגות TRUE היכן ייושם העיצוב
שים לב שערכי TRUE מסמנים נכון את הערכים> 100 בנתונים (מודגשים ידנית)

כיצד לתקן מעברי עמודים ב - Excel

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

ראשית, העתק את הנוסחה בתא השמאלי העליון של נוסחאות הדמה - זהו H4 במקרה זה.

העתק את הנוסחה הראשונה בסט הדמה

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

בחר את הנתונים והתחל כלל עיצוב מותנה חדש

הנתונים נבחרו - שימו לב שהתא הפעיל הוא B4

הדבק את הנוסחה בתיבה, וקבע את הפורמט.

נוסחת דמה מודבקת, הכלל מוכן לשמירה

מוכן לשמור את הכלל החדש

הַצלָחָה! כל התאים עם ערכים מעל 100 מודגשים:

פורמט התנאי הסופי, עם נוסחות דמה שהוסרו

עיצוב מותנה סופי מוחל עם נוסחה, עם נוסחות דמה מוסרות.

דוגמא 2 - נוסחה מסובכת יותר

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

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

בעיה - הדגש משימות עם עדיפות של

צריך להדגיש את כל השורות עם עדיפות של 'A'

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

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

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

 
=B5='A'

לאחר שאני מעתיק את הנוסחאות לרוחב ולמטה, זה מה שיש לנו:

נוסחאות דמה - נסיון ראשון

לא הולך לעבודה - רק ערכים בעמודה B יסומנו (הצללה כתומה מוחלת באופן ידני)

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

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

 
=$B5='A'

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

נוסחאות דמה - ניסיון שני - עובד!

כשהעמודה נעולה, אנו מקבלים שורה שלמה של TRUE כאשר העדיפות היא 'A' (הצללה כתומה מוחלת באופן ידני)

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

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

הנתונים נבחרו - התא הפעיל הוא B5

הנתונים נבחרו ומוכנים ליצירת כלל חדש (הערה התא הפעיל הוא B5)

כיצד לשמור על השורה העליונה גלויה ב- Excel

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

נוסחה מודבקת, כלל חדש מוכן לשמירה

פורמולה מודבקת מ- G5

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

פורמט אחרון - שורות מודגשות, נוסחות דמה הוסרו

עיצוב מותנה עובד כצפוי (נוסחות דמה הוסרו)

סיכום

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

ראה נוסחאות עיצוב מותנות נוספות כאן מחבר דייב בראנס קבצים מצורפים קוֹבֶץ בדוק דוגמא לכלל CF1.xlsx קוֹבֶץ בדוק כלל CF לדוגמא 2. xlsx


^