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

By Mirko Peters · 2026-11-12

מציאת כפילויות ב-Excel באמצעות עיצוב מותנה, דגלי COUNTIF, UNIQUE, Power Query ובדיקות בין גיליונות, וכן דרכים לזהות התאמות קרובות לפני מחיקה.

Automatically translated with AI from the original post. Read the English original

כדי למצוא כפילויות ב-Excel בלי למחוק דבר, בחרו את הנתונים והשתמשו ב-בית > עיצוב מותנה > כללי הדגשת תאים > ערכים כפולים. Excel יצבע כל ערך שמופיע יותר מפעם אחת. אם תעדיפו מספרים במקום צבעים, הוסיפו עמודת עזר עם נוסחת COUNTIF. מאמר זה מיועד למשתמשי Excel שירשו רשימות לא מסודרות ורוצים לראות את הבעיה לפני שמתקנים אותה. תקבלו את שיטת ההדגשה, נוסחאות ספירה וסימון, דרך להציג רק את הערכים החוזרים, אפשרות של Power Query, בדיקות בין גיליונות ותוכנית לטיפול בהתאמות קרובות.

עיקרי הדברים

• הדרך המהירה ביותר למצוא כפילויות ב-Excel היא Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. היא צובעת ערכים חוזרים ולא מוחקת דבר.
• עמודת עזר עם COUNTIF מראה כמה פעמים מופיע כל ערך, ועם COUNTIFS אפשר לבדוק כמה עמודות יחד.
• COUNTIF מתעלם מאותיות גדולות וקטנות ומתייחס ל-? ול-* כתווי כלליים, ולכן הספירה עלולה להיות שונה ממה שציפיתם.
• כדי להשוות בין שני גיליונות, ספרו כל ערך מול הרשימה השנייה באמצעות COUNTIF, ופתחו את שתי חוברות העבודה אם הרשימה השנייה נמצאת בקובץ אחר.
• התאמות קרובות, כמו Jon ו-John, לא יופיעו ככפילויות. נקו רווחים ואותיות גדולות/קטנות קודם, ואחר כך בדקו את השאר בעין או בעזרת התוסף Fuzzy Lookup.

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

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

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

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

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

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

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

ספירה וסימון של כפילויות עם COUNTIF

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

סימון כל חזרה

נניח שהערכים שלך נמצאים ב-A2:A100. בתא B2 הקלידו:

=COUNTIF($A$2:$A$100,A2)

גררו את הנוסחה למטה. התוצאה 1 אומרת שהערך ייחודי. התוצאה 2 אומרת שהוא מופיע פעמיים, וכך הלאה. כל עותק של ערך חוזר מקבל את אותו מספר, כולל הראשון. כדי להמיר את הספירה ל-TRUE או FALSE, הוסיפו השוואה: =COUNTIF($A$2:$A$100,A2)>1.

סימון רק העותקים המאוחרים

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

=COUNTIF(A$2:A2,A2)>1

הטווח A$2:A2 גדל ככל שמעתיקים את הנוסחה למטה. בפעם הראשונה שערך מופיע תקבלו FALSE. כל הופעה מאוחרת תקבל TRUE. זה תואם למה שפקודת Remove Duplicates של Excel עושה, שכן היא שומרת את המופע הראשון.

בדיקת כמה עמודות עם COUNTIFS

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

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

תוצאה גדולה מ-1 אומרת שהצירוף מופיע יותר מפעם אחת. הוסיפו זוגות נוספים לעמודות נוספות. אל תנסו לבנות את אותה בדיקה על ידי חיבור הטווחים בתוך COUNTIF, כמו ב-COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF מצפה לטווח אמיתי כארגומנט הראשון, ולכן הנוסחה הזו לא עושה את מה שנראה שהיא עושה. COUNTIFS הוא הפתרון הנקי.

מה ש-COUNTIF עושה ומפתיע אנשים

דף ה-COUNTIF של מיקרוסופט מפרט כמה התנהגויות שחשובות כשמחפשים כפילויות:

• התאמת טקסט אינה רגישה לאותיות רישיות. "apples" ו-"APPLES" נספרים כאותו ערך. Excel מתייחס אליהם ככפילויות כאן.
• התווים ? ו-* פועלים כתווי כלליים. אם הנתונים שלכם מכילים אותם, למשל בקודי מוצר, COUNTIF עלול להתאים ליותר ממה שציפיתם. הציבו טילדה לפני התו כדי לחפש אותו באופן מילולי.
• COUNTIF מחזיר תוצאות שגויות למחרוזות ארוכות מ-255 תווים.
• רווחים מובילים או נגררים, שילוב של גרשיים ישרים ומעוגלים ותווים שאינם מודפסים יכולים כולם לשבש את הספירה. מיקרוסופט מציעה להשתמש בפונקציות TRIM ו-CLEAN כדי לנקות את הנתונים קודם.

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

סינון וסקירה

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

הצגת ערכים כפולים בלבד

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

ב-Excel for Microsoft 365, 2024 ו-2021, הפונקציה UNIQUE יכולה לבצע את העבודה. לפי דף ה-UNIQUE של מיקרוסופט, =UNIQUE(array,[by_col],[exactly_once]) מחזירה את הערכים הייחודיים מטווח, והגדרת exactly_once ל-TRUE מחזירה רק את הערכים שמופיעים פעם אחת. לכן =UNIQUE(A2:A100) נותנת כל ערך פעם אחת, ו-=UNIQUE(A2:A100,,TRUE) נותנת את הערכים שאינם כפולים כלל. לערכים החוזרים בלבד, שלבו את UNIQUE עם FILTER ו-COUNTIF:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

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

בגרסאות ישנות יותר, השתמשו בעמודת עזר. הקלידו =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") וגררו למטה. העמודה תציג עכשיו כל ערך חוזר ותשאיר תאים ריקים לשאר. סננו את התאים הריקים. תראו כל ערך חוזר בכל פעם שהוא מופיע, וזה מספיק לסקירה.

עם Advanced Filter אפשר להעתיק רק את הרשומות הייחודיות למיקום חדש. עברו אל Data > Advanced, בחרו ב-Copy to another location, הזינו תא בתיבה Copy to וסמנו את Unique records only. השוואת הרשימה הזו למקור תראה מה חזר על עצמו.

שימוש ב-Power Query כדי להשאיר רק כפילויות

אם הנתונים שלכם גדולים או מגיעים מדי שבוע, תנו ל-Power Query לאתר את הכפילויות. התיעוד של Microsoft ל-Power Query מתאר פקודה בשם Keep duplicates, ההפך של Remove duplicates. היא מסננת את הטבלה לשורות שהערכים בעמודות שנבחרו שלהן מופיעים יותר מפעם אחת, ובניגוד ל-Remove duplicates היא מחזירה את כל השורות התואמות, לא אחת לכל ערך.

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

1. בחרו תא בתוך הנתונים ובחרו Query > Edit, או טענו אותם עם Data > From Table/Range.
2. לחצו על כותרת עמודה כדי לבחור אותה. החזיקו Shift או Ctrl כדי לבחור עמודות נוספות.
3. הפעילו את הפקודה. השלבים של Microsoft להסרה משתמשים ב-Home > Remove Rows > Remove Duplicates. חפשו את הפקודה התואמת Keep Duplicates בין פקודות השורות האחרות בכרטיסייה Home, ובדקו את התצוגה המקדימה.

Microsoft מציינת גם ש-Power Query רגישה לאותיות רישיות, כך ש-"Apple" ו-"apple" הם ערכים שונים שם, בניגוד ל-COUNTIF. זהו הבדל ממשי בין שתי השיטות. אם המטרה שלכם היא בדיקה שבועית חוזרת, כדאי להחליט על התנהגות אחת ולהישאר איתה, למשל על ידי המרת העמודה לאותיות גדולות מראש.

איתור כפילויות בין שני גיליונות או רשימות

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

1. הניחו את שתי הרשימות בגיליונות נפרדים, למשל Sheet1 ו-Sheet2, כשהערכים בעמודה A.
2. בגיליון Sheet1, בתא B2, הזינו =COUNTIF(Sheet2!A:A,A2)>0.
3. גררו את הנוסחה כלפי מטה. TRUE פירושו שהערך מופיע גם ב-Sheet2.
4. סננו לפי TRUE כדי לראות את החפיפה, או לפי FALSE כדי לראות מה חדש.

לשתי עמודות שחייבות להתאים יחד, השתמשו ב-COUNTIFS עם זוג לכל עמודה והטווחים של הגיליון השני.

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

התאמות קרובות: Jon Smith ו-John Smith

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

שלב ראשון: הסרת הבדלים מדומים. השתמשו ב-TRIM כדי להסיר רווחים מיותרים וב-CLEAN כדי להסיר תווים שאינם ניתנים להדפסה, ובנו את הבדיקה על הערכים הנקיים. בעמודת עזר, =TRIM(A2) מציג את הטקסט הנקי. ספרו כפילויות על העמודה הזו, לא על המקורית. מכיוון ש-COUNTIF מתעלמת מאותיות רישיות, אין צורך לשנות אותיות שם. ב-Power Query, שבה האותיות חשובות, המירו את העמודה לאותיות גדולות או קטנות.

שלב שני: בדקו מה נשאר. מיינו את הרשימה הנקייה לפי סדר אלפביתי וקראו את השורות הסמוכות זו לזו. התאמות קרובות, כמו "Jon Smith" ו-"John Smith", נוטות להגיע זו ליד זו, כך שאדם יכול לזהות אותן. ברשימות גדולות יותר, Microsoft Research פרסמה את התוסף Fuzzy Lookup add-in for Excel. הוא יכול לאתר שורות כפולות משוערות בתוך טבלה אחת, או להתאים שורות דומות בין שתי טבלאות, ונותן לכל התאמה ציון דמיון. לפני שאתם מתכננים סביבו, בדקו את הדרישות: דף ההורדה מציין Windows, עם Excel 2007, 2010, 2013 ו-2016, וגרסה משנת 2020. ייתכן שהתוסף לא יותקן בסביבת Microsoft 365 עדכנית, והוא אינו זמין ב-Mac.

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

טעויות נפוצות

• בדיקת עמודה אחת כשהכפילות מוגדרת לפי שתיים. שם משפחה חוזר לבדו אינו רשומה כפולה.
• הסתמכות על ספירות של COUNTIF בנתונים עם רווחים מיותרים או תווים לא תקינים. נקו קודם את הנתונים באמצעות TRIM ו-CLEAN.
• שכחה שש-COUNTIF אינו מבחין בין אותיות רשיות לקטנות, בעוד ש-Power Query כן מבחין, ואז קבלת תוצאות שונות משני הכלים.
• שימוש ב-COUNTIF($A$2:$A$100,A2)>1 ומחיקת כל שורה שמסומנת ב-TRUE. הנוסחה מסמנת גם את העותק הראשון, כך שתמחקו את כל העותקים. השתמשו בטווח מתרחב כדי לסמן רק את העותקים המאוחרים יותר.
• חיפוש בעמודות שלמות אחר מחרוזות ארוכות מ-255 תווים, שבהן COUNTIF נותן תשובות שגויות.
• מחיקה מיד אחרי הסימון. העתיקו את הגיליון קודם, שכן Microsoft מציינת שכפילויות שהוסרו נמחקות לצמיתות.

נושאים קשורים לחקירה

• הסירו את הכפילויות שמצאתם. הפקודה Remove Duplicates בלשונית Data שומרת את ההתאמה הראשונה ומוחקת את השאר. מיינו קודם כדי לשלוט בשורה שתישאר.
• שמרו על הרשומה הטובה ביותר, לא על הראשונה. מיינו לפי תאריך או ערך, כך שהשורה הרצויה תופיע ראשונה, ואז סמנו את העותקים המאוחרים יותר באמצעות COUNTIF עם טווח מתרחב.
• השוו רשימות בין קבצים. Power Query יכול לאחד מספר חוברות עבודה לטבלה אחת, כך שתוכלו לבדוק אותן יחד.
• מנעו כפילויות חדשות. אימות נתונים עם נוסחה מותאמת אישית יכול לחסום ערך שכבר קיים. נסו זאת בגיליון בדיקה.
• שמרו נתיב ביקורת. השאירו את עמודת העזר בעותק של הגיליון, כך שתוכלו להראות בהמשך מה סומן ומדוע.

הצעד הבא

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