WikiHow זה מלמד אותך כיצד להשתמש בעיצוב מותנה כדי להשוות את כל התאים בשתי רשימות נפרדות בגיליון אלקטרוני של Excel, ולסמן את התאים המופיעים בשתי הרשימות. תכונה זו זמינה רק בגרסת שולחן העבודה של Excel, והאפליקציה לנייד אינה תומכת בה.
צעדים
שיטה 1 מתוך 2: שימוש בנוסחת CountIf
שלב 1. פתח את הגיליון האלקטרוני של Excel שברצונך לערוך
מצא את קובץ הגיליון האלקטרוני עם הרשימות שברצונך להשוות ולחץ פעמיים על הקובץ כדי לפתוח אותו ב- Microsoft Excel.
שלב 2. בחר את הרשימה הראשונה שלך
לחץ על התא הראשון ברשימה הראשונה שלך וגרור את העכבר עד לתא האחרון של הרשימה כדי לבחור את כל טווח הנתונים.
שלב 3. לחץ על הכרטיסייה נוסחאות שבסרגל הכלים
תוכל למצוא כרטיסייה זו מעל סרגל הכלים בחלק העליון של הגיליון האלקטרוני שלך. הוא יפתח את כלי הנוסחה שלך על סרגל הכלים.
שלב 4. לחץ על הגדר שם בסרגל הכלים
אתה יכול למצוא אפשרות זו באמצע סרט "הנוסחאות". הוא יפתח חלון מוקפץ חדש ויאפשר לך לתת שם לרשימה שלך.
שלב 5. הקלד List1 בשדה שם
לחץ על שדה הטקסט בראש החלון המוקפץ והזן כאן שם רשימה.
- מאוחר יותר תוכל להשתמש בשם זה כדי להכניס את הרשימה לנוסחת ההשוואה.
- לחלופין, תוכל לתת לרשימה שלך שם אחר כאן. לדוגמה, אם זו רשימת מיקומים, תוכל לקרוא לה בשם "מיקומים 1" או "רשימת מיקום".
שלב 6. לחץ על אישור בחלון המוקפץ
פעולה זו תאשר את פעולתך ותקבל את שם הרשימה שלך.
שלב 7. תן שם לרשימה השנייה שלך כ- List2
בצע את אותם השלבים כמו הרשימה הראשונה, ותן לרשימה השנייה שלך שם. זה יאפשר לך להשתמש במהירות ברשימה השנייה הזו בנוסחת ההשוואה שלך מאוחר יותר.
אתה יכול לתת לרשימה כל שם שאתה רוצה. הקפד לזכור או לרשום את השם שאתה נותן לכל אחת מהרשימות שלך כאן
שלב 8. בחר את הרשימה הראשונה שלך
לחץ על התא הראשון ברשימה הראשונה וגרור כלפי מטה כדי לבחור את כל טווח הנתונים.
ודא שהרשימה הראשונה שלך נבחרה לפני שתתחיל בהגדרת העיצוב המותנה
שלב 9. לחץ על הכרטיסייה דף הבית בסרגל הכלים
זוהי הכרטיסייה הראשונה בפינה השמאלית העליונה של סרגל הכלים. זה יפתח את כלי הגיליון האלקטרוניים הבסיסיים שלך בסרגל הכלים.
שלב 10. לחץ על עיצוב מותנה בסרגל הכלים
אפשרות זו נראית כמו סמל גיליון אלקטרוני זעיר עם כמה תאים מודגשים באדום וכחול. הוא יפתח תפריט נפתח של כל אפשרויות העיצוב שלך.
שלב 11. לחץ על כלל חדש בתפריט הנפתח
הוא יפתח חלון מוקפץ חדש ויאפשר לך להגדיר באופן ידני כלל עיצוב חדש לטווח שנבחר.
שלב 12. בחר באפשרות "השתמש בנוסחה כדי לקבוע אילו תאים לעצב"
אפשרות זו תאפשר לך להקליד באופן ידני נוסחת עיצוב כדי להשוות בין שתי הרשימות שלך.
- עַל חלונות, תמצא אותו בתחתית רשימת הכללים בתיבה "בחר סוג כלל".
- עַל מק, בחר קלַאסִי בתפריט הנפתח "סגנון" בחלק העליון של המוקפץ. לאחר מכן, מצא אפשרות זו בתפריט הנפתח השני מתחת לתפריט סגנון.
שלב 13. לחץ על שדה הנוסחה בחלון המוקפץ
תוכל להזין כאן כל נוסחת אקסל תקפה כדי להגדיר כלל עיצוב מותנה.
שלב 14. הקלד = countif (List2, A1) = 1 בשורת הנוסחאות
נוסחה זו תסרוק את שתי הרשימות שלך ותסמן את כל התאים ברשימה הראשונה שלך המופיעים גם ברשימה השנייה.
- החלף את A1 בנוסחה במספר התא הראשון של הרשימה הראשונה שלך.
- לדוגמה, אם התא הראשון של הרשימה הראשונה שלך הוא תא D5, אז הנוסחה שלך תיראה = countif (List2, D5) = 1.
- אם נתת שם אחר לרשימה השנייה שלך, הקפד להחליף את List2 בנוסחה בשם האמיתי של הרשימה שלך.
- לחלופין, שנה את הנוסחה ל- = countif (List2, A1) = 0 אם ברצונך לסמן את התאים אל מופיעים ברשימה השנייה.
שלב 15. הקלד = countif (List1, B1) = 1 בשורת הנוסחאות (אופציונלי)
אם אתה רוצה למצוא ולסמן את התאים שב- שְׁנִיָה ברשימה המופיעה גם ברשימה הראשונה, השתמש בנוסחה זו במקום בראשונה.
החלף את List1 בשם הרשימה הראשונה שלך ו- B1 בתא הראשון של הרשימה השנייה
שלב 16. בחר פורמט מותאם אישית לסימון התאים (אופציונלי)
אתה יכול לבחור צבע מילוי רקע מותאם אישית וסגנונות גופנים שונים כדי לסמן את התאים שהנוסחה שלך מוצאת.
- עַל חלונות, לחץ על פוּרמָט כפתור בפינה השמאלית התחתונה של החלון המוקפץ. באפשרותך לבחור צבע רקע בכרטיסייה "מילוי", וסגנונות גופנים בכרטיסייה "גופן".
- עַל מק, בחר תבנית מוגדרת מראש בתפריט הנפתח "פורמט עם" בתחתית. אתה יכול גם לבחור פורמט מותאם אישית כאן לבחירה ידנית של מילוי רקע וסגנונות גופנים.
שלב 17. לחץ על אישור בחלון המוקפץ
פעולה זו תאשר ותיישם את נוסחת ההשוואה שלך. כל התאים ברשימה הראשונה שלך המופיעים גם ברשימה השנייה יסומנו בצבע ובגופן שבחרת.
- לדוגמה, אם תבחר מילוי אדום בהיר עם טקסט אדום כהה, כל התאים החוזרים יפנו לצבע זה ברשימה הראשונה שלך.
- אם אתה משתמש בנוסחה השנייה למעלה, עיצוב מותנה יסמן את התאים החוזרים ברשימה השנייה במקום הראשון.
שיטה 2 מתוך 2: שימוש בנוסחת VLookup
שלב 1. פתח את הגיליון האלקטרוני של Excel
מצא את קובץ ה- Excel עם הרשימות שברצונך להשוות ולחץ פעמיים על שם הקובץ או הסמל כדי לפתוח את הגיליון האלקטרוני ב- Microsoft Excel.
שלב 2. לחץ על התא הריק לצד הפריט הראשון ברשימה השנייה שלך
מצא את הרשימה השנייה שלך בגיליון האלקטרוני ולחץ על התא הריק שליד פריט הרשימה הראשון למעלה.
- תוכל להכניס את נוסחת VLookup שלך כאן.
- לחלופין, באפשרותך לבחור כל תא ריק בגיליון האלקטרוני שלך. תא זה רק יקל עליך לראות את ההשוואה לצד הרשימה השנייה שלך.
שלב 3. הקלד = vlookup (בתא הריק
נוסחת VLookup תאפשר לך להשוות בין כל הפריטים בשתי רשימות נפרדות ולראות אם ערך הוא ערך חוזר או חדש.
אין לסגור את סוגר הנוסחה עד להשלמת הנוסחה
שלב 4. בחר את הפריט הראשון ברשימה השנייה שלך
מבלי לסגור את סוגריים הנוסחה, לחץ על הפריט הראשון ברשימה השנייה שלך. זה יכניס את התא הראשון של הרשימה השנייה שלך לנוסחה.
שלב 5. הקלד a, פסיק בנוסחה
לאחר בחירת התא הראשון ברשימה השנייה, הקלד פסיק בנוסחה. לאחר מכן תוכל לבחור את טווח ההשוואה שלך.
שלב 6. החזק ובחר את כל הרשימה הראשונה שלך
זה יכניס את טווח התאים של הרשימה הראשונה שלך לחלק השני של נוסחת VLookup.
זה יאפשר לך לחפש ברשימה הראשונה את הפריט שנבחר מהרשימה השנייה שלך (הפריט הראשון בראש הרשימה השנייה), ולחזור אם מדובר בחזרה חוזרת או חדשה
שלב 7. הקלד a, פסיק בנוסחה
פעולה זו תנעל את טווח ההשוואה בנוסחה שלך.
שלב 8. הקלד 1 בנוסחה אחרי הפסיק
מספר זה מייצג את מספר אינדקס העמודות שלך. היא תנחה את נוסחת VLookup לחפש בעמודה הרשימה בפועל במקום בעמודה אחרת לידה.
אם אתה רוצה שהנוסחה שלך תחזיר את הערך מהעמודה שליד הרשימה הראשונה שלך, הקלד 2 כאן
שלב 9. הקלד a, פסיק בנוסחה
פעולה זו תנעל את מספר אינדקס העמודות שלך (1) בנוסחת VLookup.
שלב 10. הקלד FALSE בנוסחה
פעולה זו תחפש ברשימה התאמה מדויקת של פריט החיפוש שנבחר (הפריט הראשון בראש הרשימה השנייה) במקום התאמות משוערות.
- במקום FALSE אתה יכול להשתמש ב- 0, זה בדיוק אותו הדבר.
- לחלופין, תוכל להקליד TRUE או 1 אם ברצונך לחפש התאמה משוערת.
שלב 11. הקלד) בסוף כדי לסגור את הנוסחה
כעת תוכל להריץ את הנוסחה ולראות אם פריט החיפוש שנבחר ברשימה השנייה שלך הוא ערך חוזר או חדש.
לדוגמה, אם הרשימה השנייה שלך מתחילה ב- B1, והרשימה הראשונה שלך עוברת מהתאים A1 ל- A5, הנוסחה שלך תיראה כך = vlookup (B1, $ A $ 1: $ A $ 5, 1, false).
שלב 12. הקש ↵ Enter אוֹ חזור על המקלדת.
פעולה זו תריץ את הנוסחה ותחפש ברשימה הראשונה שלך את הפריט הראשון מהרשימה השנייה.
- אם זהו ערך חוזר, תראה אותו ערך מודפס שוב בתא הנוסחה.
- אם זהו ערך חדש, תראה " #לא"מודפס כאן.
- לדוגמה, אם אתה מחפש ברשימה הראשונה את "ג'ון" ועכשיו רואה את "ג'ון" בתא הנוסחה, זהו ערך חוזר שמופיע בשתי הרשימות. אם אתה רואה את "#N/A", זהו ערך חדש ברשימה השנייה.
שלב 13. בחר את תא הנוסחה שלך
לאחר הפעלת הנוסחה וראית את התוצאות שלך עבור פריט הרשימה הראשון, לחץ על תא הנוסחה כדי לבחור אותה.
שלב 14. לחץ וגרור את הנקודה הירוקה בפינה השמאלית התחתונה של התא
זה ירחיב את תא הנוסחה לאורך הרשימה, ויחיל את הנוסחה על כל פריט רשימה ברשימה השנייה שלך.
- כך תוכל להשוות כל פריט ברשימה השנייה שלך לרשימה הראשונה כולה.
- פעולה זו תחפש ברשימה הראשונה שלך כל פריט ברשימה השנייה בנפרד, ותציג את התוצאה ליד כל תא בנפרד.
- אם אתה רוצה לראות סמן אחר לערכים חדשים במקום "#N/A", השתמש בנוסחה הבאה: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "New Value"). פעולה זו תדפיס "ערך חדש" לערכים חדשים במקום "#לא רלוונטי".
