צפה בוידאו - חפש והדגש נתונים באמצעות עיצוב מותנה
אם אתה עובד עם מערכי נתונים גדולים, ייתכן שיהיה צורך ליצור פונקציונליות חיפוש המאפשרת לך להדגיש במהירות תאים/שורות עבור המונח שחיפשת.
אמנם אין דרך ישירה לעשות זאת ב- Excel, אך תוכל ליצור פונקציונליות חיפוש באמצעות עיצוב מותנה.
לדוגמה, נניח שיש לך מערך נתונים כפי שמוצג להלן (בתמונה). יש לו עמודות עבור שם המוצר, נציג המכירות והמדינה.
כעת תוכל להשתמש בעיצוב מותנה כדי לחפש מילת מפתח (על ידי הזנתה בתא C2) ולהדגיש את כל התאים המכילים את מילת המפתח הזו.
משהו כפי שמוצג להלן (כאשר אני מזין את שם הפריט בתא B2 ולוחץ על Enter, השורה כולה מודגשת):
במדריך זה אראה לך כיצד ליצור חיפוש זה ולהדגיש פונקציונליות ב- Excel.
בהמשך ההדרכה, נתקדם מעט ונראה כיצד להפוך אותה לדינאמית (כך שהיא תדגיש בזמן שאתה מקליד בתיבת החיפוש).
לחץ כאן להורדת קובץ הדוגמה ועקוב אחרי.
חפש והדגיש תאים תואמים
בחלק הזה. אני אראה לך כיצד לחפש ולהדגיש רק את התאים התואמים במערך הנתונים.
משהו כפי שמוצג להלן:
להלן השלבים לחיפוש והדגשת כל התאים המכילים את הטקסט התואם:
- בחר את מערך הנתונים שבו ברצונך להחיל עיצוב מותנה (A4: F19 בדוגמה זו).
- לחץ על הכרטיסייה דף הבית.
- בקבוצה סגנונות, לחץ על עיצוב מותנה.
- באפשרויות הנפתחות, לחץ על כלל חדש.
- בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על האפשרות 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
- הזן את הנוסחה הבאה: = A4 = $ B $ 1
- לחץ על כפתור 'עיצוב …'.
- ציין את העיצוב (כדי להדגיש תאים התואמים את מילת המפתח שחיפשת).
- לחץ על אישור.
כעת הקלד כל דבר בתא B1 והקש Enter. הוא ידגיש את התאים התואמים במערך הנתונים המכילים את מילת המפתח ב- B1.
איך זה עובד?
עיצוב מותנה יוחל בכל פעם שהנוסחה המצוינת בו מחזירה TRUE.
בדוגמה שלמעלה, אנו בודקים כל תא באמצעות הנוסחה = A4 = $ B $ 1
עיצוב מותנה בודק כל תא ומאמת אותו התוכן בתא זהה לזה שבתא B1. אם זהה, הנוסחה מחזירה TRUE והתא מודגש. אם זה לא אותו דבר, הנוסחה מחזירה שקר ושום דבר לא קורה.
לחץ כאן להורדת קובץ הדוגמה ועקוב אחרי.
חפש והדגיש שורות עם נתונים תואמים
אם ברצונך להדגיש את כל השורה במקום רק את התאים התואמים, תוכל לעשות זאת על ידי שיפור הנוסחה מעט.
להלן דוגמה שבה כל השורה מודגשת אם סוג המוצר תואם את אחד בתא B1.
להלן השלבים לחיפוש והדגשת כל השורה:
- בחר את מערך הנתונים שבו ברצונך להחיל עיצוב מותנה (A4: F19 בדוגמה זו).
- לחץ על הכרטיסייה דף הבית.
- בקבוצה סגנונות, לחץ על עיצוב מותנה.
- באפשרויות הנפתחות, לחץ על כלל חדש.
- בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על האפשרות 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
- הזן את הנוסחה הבאה: = $ B4 = $ B $ 1
- לחץ על כפתור 'עיצוב …'.
- ציין את העיצוב (כדי להדגיש תאים התואמים את מילת המפתח שחיפשת).
- לחץ על אישור.
השלבים שלעיל יחפשו את הפריט שצוין במערך הנתונים, ואם הוא ימצא את הפריט התואם, הוא יסמן את השורה כולה.
שים לב שזה יבדוק רק את עמודת הפריט. אם תזין כאן שם נציג מכירות, זה לא יעבוד. אם אתה רוצה שזה יעבוד בשם נציג המכירות, עליך לשנות את הנוסחה ל- = $ C4 = $ B $ 1
הערה: הסיבה שהוא מדגיש את כל השורה ולא רק את התא התואם היא שהשתמשנו בסימן $ לפני הפניה לעמודה ($ B4). כעת כאשר עיצוב מותנה מנתח תאים בשורה, הוא בודק אם הערך בעמודה B של שורה זו שווה לערך בתא B1. כך שגם כאשר הוא מנתח A4 או B4 או C4 וכן הלאה, הוא בודק את ערך B4 בלבד (כפי שנעולנו עמודה B באמצעות סימן הדולר).
תוכל לקרוא עוד על הפניות מוחלטות, יחסיות ומעורבות כאן.
שורות חיפוש והדגשה (מבוסס על התאמה חלקית)
במקרים מסוימים, ייתכן שתרצה להדגיש שורות המבוססות על התאמה חלקית.
לדוגמה, אם יש לך פריטים כגון White Board, Green Board ו- Gray Board, ואתה רוצה להדגיש את כל אלה על סמך המילה Board, תוכל לעשות זאת באמצעות פונקציית SEARCH.
משהו כפי שמוצג להלן:
להלן השלבים לביצוע פעולה זו:
- בחר את מערך הנתונים שבו ברצונך להחיל עיצוב מותנה (A4: F19 בדוגמה זו).
- לחץ על הכרטיסייה דף הבית.
- בקבוצה סגנונות, לחץ על עיצוב מותנה.
- באפשרויות הנפתחות, לחץ על כלל חדש.
- בתיבת הדו -שיח 'כלל עיצוב חדש', לחץ על האפשרות 'השתמש בנוסחה כדי לקבוע אילו תאים לעצב'.
- הזן את הנוסחה הבאה: = AND ($ B $ 1 ””, ISNUMBER (SEARCH ($ B $ 1, $ B4)))
- לחץ על כפתור 'עיצוב …'.
- ציין את העיצוב (כדי להדגיש תאים התואמים את מילת המפתח שחיפשת).
- לחץ על אישור.
איך זה עובד?
- פונקציית SEARCH מחפשת את מחרוזת החיפוש/מילת המפתח בכל התאים בשורה. היא מחזירה שגיאה אם מילת המפתח לחיפוש לא נמצאה ומחזירה מספר אם היא מוצאת התאמה.
- הפונקציה ISNUMBER ממירה את השגיאה ל- FALSE ואת הערכים המספריים ל- TRUE.
- הפונקציה AND בודקת תנאי נוסף - שתא C2 לא צריך להיות ריק.
אז עכשיו, בכל פעם שאתה מקליד מילת מפתח בתא B1 ולחץ על Enter, הוא מדגיש את כל השורות המכילות את התאים המכילים את מילת המפתח הזו.
טיפ בונוס: אם ברצונך להפוך את חיפוש האותיות לרגיש, השתמש בפונקציית FIND במקום ב- SEARCH.
לחץ כאן להורדת קובץ הדוגמה ועקוב אחרי.
פונקציונליות חיפוש והדגשה דינמית (דגשים תוך כדי הקלדה)
באמצעות אותם טריקי עיצוב מותנה המכוסים למעלה, אתה יכול גם לקחת את זה צעד קדימה ולהפוך אותו לדינאמי.
לדוגמה, תוכל ליצור סרגל חיפוש שבו הנתונים המתאימים מודגשים תוך כדי הקלדת סרגל החיפוש.
משהו כפי שמוצג להלן:
ניתן לעשות זאת באמצעות פקדי ActiveX ויכול להיות פונקציונליות טובה לשימוש בעת יצירת דוחות או לוחות מחוונים.
להלן סרטון שבו אני מראה כיצד ליצור זאת:
האם מצאת שהדרכה שימושית? ספר לי את דעתך בסעיף ההערות.
אולי תאהב גם את מדריכי האקסל הבאים:
- מסנן Excel דינמי - מחלץ נתונים בזמן ההקלדה.
- צור רשימה נפתחת עם הצעת חיפוש.
- יצירת מפת חום ב- Excel.
- הדגש שורות בהתבסס על ערך תא ב- Excel.
- הדגש את השורה והעמודה הפעילה בטווח נתונים ב- Excel.
- כיצד להדגיש תאים ריקים ב- Excel.
