כיצד להשוות בין שתי עמודות ב-Excel ולהסיר כפילויות (הדגשה, הצבע, הזזה)

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

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

תאר לעצמך שיש לנו 2 עמודות עם שמות של אנשים - 5 שמות לכל עמודה A ו-3 שמות בעמודה B. עליך להשוות את השמות בשתי העמודות הללו ולמצוא כפילויות. כפי שאתה מבין, מדובר בנתונים פיקטיביים, הנלקחים לדוגמא בלבד. בטבלאות אמיתיות, אנו מתמודדים עם אלפי או אפילו עשרות אלפי רשומות.

אפשרות א ': שתי העמודות נמצאות באותו גיליון. לדוגמה, עמודה A ועמודה B.

אפשרות ב ': העמודות נמצאות על גיליונות שונים. לדוגמה, עמודה A על הגיליון Sheet2 ועמודה A על הגיליון Sheet3.

ל-Excel 2013, 2010 ו-2007 יש כלי מובנה הסר כפילויות (הסר כפילויות) אבל הוא חסר אונים במצב זה מכיוון שהוא לא יכול להשוות נתונים ב-2 עמודות. יתר על כן, זה יכול להסיר רק כפילויות. אין אפשרויות אחרות כגון הדגשה או שינוי צבעים. ונקודה!

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

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

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

אפשרות א': שתי העמודות נמצאות באותו גיליון

1. בתא הריק הראשון (בדוגמה שלנו, זהו תא C1), אנו כותבים את הנוסחה הבאה:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   בנוסחה שלנו A1 זהו התא הראשון של העמודה הראשונה שאנו הולכים להשוות. $B$1 и $B$10000 אלו הכתובות של התא הראשון והאחרון של העמודה השנייה, איתם נבצע את ההשוואה. שימו לב להפניות המוחלטות - לפני אותיות העמודות ומספרי השורות יש סימן דולר ($). אני משתמש בהפניות מוחלטות כך שכתובות התא נשארות זהות בעת העתקת נוסחאות.

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

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

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

2. כעת נעתיק את הנוסחה שלנו לכל התאים בעמודה C, עד לשורה התחתונה, המכילה את הנתונים בעמודה A. לשם כך, הזז את מצביע העכבר לפינה הימנית התחתונה של התא C1, המצביע יקבל צורה של כובע שחור, כפי שמוצג בתמונה למטה:לחץ והחזק את לחצן העכבר השמאלי וגרור את גבול המסגרת כלפי מטה, תוך הדגשת כל התאים שבהם ברצונך להוסיף את הנוסחה. כאשר כל התאים הדרושים נבחרו, שחרר את לחצן העכבר:

   

טיפ: בטבלאות גדולות, העתקת הנוסחה תהיה מהירה יותר אם תשתמש בקיצורי מקלדת. סמן תא C1 ולחץ Ctrl + C (כדי להעתיק את הנוסחה ללוח), ואז לחץ Ctrl + Shift + End (כדי לבחור את כל התאים הלא ריקים בעמודה C) ולבסוף הקש Ctrl + V (כדי להכניס את הנוסחה לכל התאים שנבחרו).

3. נהדר, כעת כל הערכים הכפולים מסומנים כ"לשכפל":

אפשרות ב': שתי עמודות נמצאות על גיליונות שונים (בחוברות עבודה שונות)

1. בתא הראשון של העמודה הריקה הראשונה בגליון העבודה Sheet2 (במקרה שלנו זה עמודה B) הזן את הנוסחה הבאה:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   כאן Sheet3 הוא שם הגיליון עליו ממוקמת העמודה השנייה, ו $A$1:$A$10000 הן כתובות התא מהמקום הראשון עד האחרון בעמודה השנייה הזו.

2. העתק את הנוסחה לכל התאים בעמודה B (זהה לאפשרות א').
3. אנחנו מקבלים את התוצאה הזו:

עיבוד של כפילויות שנמצאו

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

הצג רק שורות כפולות בעמודה A

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

לחץ לחיצה ימנית ובחר מתפריט ההקשר הַכנָסָה (לְהַכנִיס):

תן שמות לעמודות, לדוגמה, "שם"וגם"לְשַׁכְפֵּל?» לאחר מכן פתח את הכרטיסייה נתונים (נתונים) ולחץ סינון (לְסַנֵן):

לאחר מכן לחץ על החץ האפור הקטן שליד "לְשַׁכְפֵּל?« כדי לפתוח את תפריט הסינון; בטל את הסימון של כל הפריטים ברשימה זו מלבד לשכפל, ולחץ על OK.

זה הכל, עכשיו אתה רואה רק את הרכיבים האלה של העמודה А, אשר משוכפלים בעמודה В. יש רק שני תאים כאלה בטבלת האימונים שלנו, אבל כפי שהבנתם, בפועל יהיו הרבה יותר מהם.

כדי להציג שוב את כל שורות העמודה А, לחץ על סמל המסנן בעמודה В, שנראה כעת כמו משפך עם חץ קטן, ובחר בחר הכל (בחר הכל). או שאתה יכול לעשות את אותו הדבר דרך רצועת הכלים על ידי לחיצה נתונים (נתונים) > בחר & סינון (מיין וסנן) > נקה (נקה) כפי שמוצג בצילום המסך למטה:

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

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

במקרה זה, סנן את הכפילויות כפי שמוצג לעיל, בחר את כל התאים המסוננים ולחץ Ctrl + 1כדי לפתוח את תיבת הדו-שיח עיצוב תאים (פורמט תא). כדוגמה, בואו נשנה את צבע המילוי של תאים בשורות עם כפילויות לצהוב בהיר. כמובן, אתה יכול לשנות את צבע המילוי עם הכלי למלא (צבע מילוי). עמוד הבית (בית) אבל יתרון בתיבת הדו-שיח עיצוב תאים (Cell Format) בכך שאתה יכול להגדיר את כל אפשרויות העיצוב בו זמנית.

עכשיו אתה בהחלט לא תפספס אף תאים עם כפילויות:

הסרת ערכים כפולים מהעמודה הראשונה

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

אם 2 העמודות שאתה משווה נמצאות על גיליונות שונים, כלומר בטבלאות שונות, לחץ לחיצה ימנית על הטווח שנבחר ובחר מחק שורה (הסר שורה):

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

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

1. סנן את הטבלה כדי להציג רק ערכים כפולים ובחר את התאים האלה. לחץ לחיצה ימנית עליהם ובחר מתפריט ההקשר נקה תוכן (תוכן ברור).
2. נקה את המסנן.
3. בחר את כל התאים בעמודה А, החל מהתא A1 עד לתחתית המכיל את הנתונים.
4. לחץ על נתונים (נתונים) ולחץ מיין א' עד ת' (מיין מא' עד ת'). בתיבת הדו-שיח שנפתחת, בחר המשך עם הבחירה הנוכחית (מיין בתוך הבחירה שצוינה) ולחץ על הכפתור שחור (מִיוּן):
5. מחק את העמודה עם הנוסחה, לא תצטרך אותה יותר, מעכשיו יש לך רק ערכים ייחודיים.
6. זהו, עכשיו הטור А מכיל רק נתונים ייחודיים שאינם בעמודה В:

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