M365Con 2027 – כנס אונליין חינמי · 11–22 Jan 2027 · 120 sessions→ m365con.net

בלוג · Mirko Peters

איך מסירים כפילויות ב-Excel: פקודה, נוסחאות, Power Query

By · · Updated

איך מסירים כפילויות ב-Excel: משתמשים ב-Data, Remove Duplicates, או שומרים על הנתונים המקוריים עם UNIQUE, נוסחאות ו-Power Query. שלבים, מגבלות וטעויות נפוצות.

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

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

עיקרי הדברים

  • בחרו את הנתונים, פתחו את Data, לאחר מכן Remove Duplicates, סמנו את העמודות שמגדירות כפילות ולחצו על OK. Excel מוחק את השורה כולה ושומר את ההתאמה הראשונה.
  • המחיקה היא לצמיתות, לכן העתיקו קודם את הנתונים לגיליון אחר. Ctrl+Z פועל רק עד שמבצעים פעולה אחרת.
  • Excel משווה את מה שמוצג בתא, ולכן 3/8/2006 ו-Mar 8, 2006 נחשבים לערכים שונים. נקו את הפורמטים לפני שמתחילים.
  • אם אתם רוצים לבדוק כפילויות לפני המחיקה, סמנו אותן בעיצוב מותנה או הוסיפו עמודת עזר עם COUNTIF שמסמנת אותן.
  • UNIQUE ו-Power Query מספקים רשימה נקייה בלי לגעת בנתוני המקור, וה-Power Query מחיל את הניקוי מחדש בכל רענון.

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

הסרת כפילויות באמצעות הפקודה בכרטיסייה Data

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

  1. גבו את הנתונים. העתיקו את הטווח לגיליון או לחוברת אחרת. Microsoft אומרת זאת בבירור: הכפילויות נמחקות לצמיתות.
  2. בחרו את הטווח. לחצו על כל תא בנתונים, או בטבלה של Excel, או בחרו את הטווח בעצמכם. רק הטווח או הטבלה שנבחרו ישתנו. תאים מחוצה להם נשארים במקומם.
  3. פתחו את הפקודה. עברו אל Data ובחרו ב-Remove Duplicates בקבוצה Data Tools.
  4. בחרו את העמודות. בתיבת הדו-שיח סמנו את העמודות ש-Excel צריך להשוות. אפשר להשתמש ב-Select All או ב-Unselect All. אם השורה הראשונה מכילה שמות עמודות, השאירו מסומן את My data has headers.
  5. בחרו OK. Excel מציג הודעה עם מספר הערכים הכפולים שהוסרו ומספר הערכים הייחודיים שנותרו.

שימו לב לשלב 4, כי הוא מבלבל אנשים. שורה נחשבת לכפילות רק כאשר כל העמודות שסימנתם תואמות. קחו רשימה עם ID, Name ו-Price. אם סימנתם את ID ואת Name אבל לא את Price, שתי שורות עם אותו ID ושם ייחשבו לכפילות גם אם המחירים שונים, וExcel ימחק את כל השורה השנייה, כולל המחיר. אם אתם רוצים לשמור כל שורה שבה משהו שונה, סמנו את כל העמודות. אם ה-ID לבדו מגדיר רשומה, סמנו רק את ה-ID, וכדאי לדעת מה אתם זורקים.

לחצו על Ctrl+Z מיד אחרי הפקודה אם התוצאה נראית לא נכונה. אל תסתמכו על כך מאוחר יותר.

איזו שורה נשארת: מיינו קודם

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

מה נחשב זהה

Excel משווה את מה שמוצג בתא, לא את מה שמאוחסן בו. לפי הדוגמה של Microsoft, 3/8/2006 ו-Mar 8, 2006 מתפרשים כערכים שונים, אף שמדובר באותו תאריך. כנ"ל לגבי מספרים שעוצבו בצורה שונה או טקסט עם רווח מיותר בסופו. המאמר על איתור והסרת כפילויות מציין גם שהספירות ש-Excel מציג לאחר מכן עשויות לכלול תאים ריקים או רווחים.

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

מתי הפקודה מסרבת לפעול

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

בדקו את הכפילויות לפני שמוחקים

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

סימון כפילויות באמצעות עיצוב מותנה

  1. בחרו את התאים שברצונכם לבדוק.
  2. בכרטיסייה Home, פתחו את Conditional Formatting, לאחר מכן Highlight Cells Rules, ולבסוף Duplicate Values.
  3. בחרו סגנון הדגשה ולחצו על OK.

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

סימון שורות כפולות באמצעות עמודת עזר

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

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

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

כדי לבדוק כמה עמודות בבת אחת, בנו עמודת מפתח שמחברת אותן: =A2&"|"&B2&"|"&C2. הריצו את COUNTIF על המפתח הזה. הוסיפו מפריד כמו קו אנכי בין השדות. בלעדיו, "12" ו-"34" יחד עם "1" ו-"234" יתקבלו שניהם כ-"1234" ייראו כאותה רשומה.

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

קבלו רשימה ללא כפילויות ושמרו על המקור

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

הפונקציה UNIQUE

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

  • array הוא הטווח לבדיקה. אם הוא כולל כמה עמודות, UNIQUE משווה שורות שלמות.
  • by_col הוא TRUE אם רוצים להשוות עמודות במקום שורות. השמיטו אותו להשוואת שורות הרגילה.
  • exactly_once הוא TRUE אם רוצים רק את השורות שמופיעות פעם אחת בלבד בנתונים. אם משמיטים אותו, מקבלים כל שורה נפרדת.

התוצאה מתעדכנת כשהמקור משתנה. אם המקור הוא Excel Table ומשתמשים בהפניות מובנות (structured references), טווח ה-spill מתכווץ ומתרחב ככל שמוסיפים או מסירים שורות. לרשימה ממוינת עטפו את הפונקציה: =SORT(UNIQUE(A2:A100)).

זוג הארגומנטים השני חשוב יותר ממה שרבים חושבים. =UNIQUE(A2:A100,,TRUE) מחזירה את הערכים שמופיעים בדיוק פעם אחת, וזו שאלה אחרת לגמרי מ"הסרת העותקים הנוספים". שאלו את עצמכם איזו מהן אתם צריכים.

מגבלה אחת: גרסאות Excel ישנות יותר (2019 ומוקדמות יותר) אינן כוללות את UNIQUE. דף התמיכה של Microsoft מפרט רק את הגרסאות שהוזכרו לעיל.

מסנן מתקדם (Advanced Filter)

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

  1. בחרו את הנתונים.
  2. עברו אל Data > Advanced בקבוצה Sort & Filter.
  3. בחרו העתקה למיקום אחר.
  4. הזינו תא בתיבה Copy to וסמנו את Unique records only.
  5. לחצו על OK.

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

תבנית הנוסחה הישנה

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

טיפול בהתאמות חלקיות והבדלי רישיות

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

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

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

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

אוטומציה: Power Query ו-VBA

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

Power Query

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

  1. טענו את הנתונים לעורך Power Query. אם הם כבר נמצאים בגיליון, בחרו תא בנתונים ולחצו על Query > Edit, או השתמשו ב-Data > Get Data כדי לטעון מקור חדש.
  2. לחצו על כותרת העמודה כדי לבחור עמודה. החזיקו Shift או Ctrl כדי לבחור עוד עמודות. העמודות אינן חייבות להיות צמודות זו לזו.
  3. עברו אל דף הבית > הסרת שורות > הסרת כפילויות.
  4. סגרו וטענו את התוצאה.

המדריך של Microsoft בנושא שמירה או הסרה של שורות כפולות ב-Power Query מפרט את השלבים. כדאי להכיר את ההנחיות שלהם לגבי כפילויות: העמודות שנבחרו קובעות מה נחשב לכפילות, ו-Power Query אינו מבטיח שישמר השורה הראשונה בקבוצה. אם חשוב לכם איזו שורה נשארת, אל תסתמכו על מיון בלבד. Microsoft מפנה להנחיות "Preserve sort" במאמר הבעיות הנפוצות של Power Query. אפשרות קשורה, Keep Duplicates, עושה את ההפך: היא משאירה רק שורות שהערכים שלהן מופיעים יותר מפעם אחת, וזו דרך מהירה לבדוק רשימה.

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

מאקרו VBA

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

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

השורה משווה את שתי העמודות הראשונות בטווח A1:C100 ומתייחסת לשורה הראשונה ככותרת. כדי להשתמש בה, לחצו על Alt+F11, הוסיפו מודול, הדביקו את השורה בתוך Sub, ושמרו את הקובץ כחוברת עבודה עם תמיכה במאקרו (.xlsm). הריצו תחילה על עותק. מאקרו מוחק באופן סופי בדיוק כמו הפקודה, ו-Ctrl+Z לא יחזיר את השורות. ייתכן שצוות ה-IT שלכם חוסם מאקרו, ולכן בדקו זאת לפני שאתם בונים משהו שהעמיתים שלכם לא יוכלו להפעיל. אם תרצו ללמוד יותר על השפה, בדף VBA מרוכז חומר קשור.

Excel לאינטרנט ולנייד

המאמרים של Microsoft על הסרת כפילויות מפרטים רק את גרסאות שולחן העבודה של Excel (Microsoft 365, 2024, 2021, 2019 ו-2016), ואינם מזכירים את האינטרנט או אפליקציות הנייד. Microsoft אינה מתארת את הפקודה Remove Duplicates עבור הגרסאות האלה, ולכן אל תבנו תהליך עליה. מה שכן מתועד על ידי Microsoft לגרסאות האינטרנט והנייד היא הפונקציה UNIQUE. השתמשו בה כדי לקבל רשימה נקייה במכשירים האלה, ובצעו את המחיקה הסופית בשולחן העבודה.

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

  • הרצת הפקודה ללא גיבוי. לאחר סגירת הקובץ השורות אבודות לתמיד.
  • סימון העמודות הלא נכונות. השמטת עמודה משנה אילו שורות נחשבות לכפולות, והשורה כולה נמחקת, לא רק התא החוזר.
  • שכחה למיין קודם, ואז תהיתה מדוע הרשומה הישנה נשארה במקום החדשה.
  • השוואת תאים שנראים זהים אך מעוצבים אחרת, כמו תאריכים הכתובים בשתי דרכים או טקסט עם רווחים בסוף.
  • ניסיון להריץ את הפקודה על נתונים עם קבוצות (outline) או סיכומי ביניים. הסירו אותם קודם.
  • התייחסות ל-UNIQUE עם exactly_once שמוגדר ל-TRUE כדרך למחוק את העודפים. הפונקציה מחזירה רק ערכים שמופיעים פעם אחת, ולכן ערכים החוזרים על עצמם נעלמים מהתוצאה לחלוטין.

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

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

הצעד הבא

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

מקורות

Entities in this article

Microsoft Excel Power Query Visual Basic for Applications (VBA)

Related articles

RSS