مقادیر تکراری در دادههای شما میتواند مشکل بزرگی باشند! ممکن است موجب خطاهای بزرگی شوند و موجب تحلیل نادرستی از دادههایتان شوند.
اما پیدا کردن و حذف دادههای تکراری در اکسل واقعاً ساده است.
در این مطلب، میخواهیم به بررسی 7 روش متفاوت برای پیدا کردن و حذف مقادیر تکراری از دادههایتان بپردازیم.
دادهی تکراری چیست؟
مقادیر تکراری زمانی اتفاق میافتند که یک مقدار یا مجموعهای از مقادیر مشابه در دادههایتان ظاهر شود.
برای مجموعهای از دادهها، میتوانید مقادیر تکراری را به روشهای مختلف تعریف کنید.
در مثال فوق، مجموعهای ساده از دادهها با 3 ستون تحت عناوین Make، Model و Year برای لیست ماشینها آمده است.
- تصویر اول تمامی تکراریها را تنها بر اساس ستون اول Make هایلایت کرده است.
- تصویر دوم تمامی تکراریها را بر اساس ستون اول و دوم Make و Model هایلایت کرده است. تعداد نتایج، یک تکراری کمتر است.
- تصویر دوم تمامی تکراریها را بر اساس تمامی ستونهای جدول هایلایت کرده است. تعداد نتایج، باز تعداد کمتری مقادیر تکراری است.
نتایج مقادیر تکراری بر اساس یک ستون یا کل جدول میتواند بسیار متفاوت باشد. همیشه باید هوشیار باشد چه نسخهای را میخواهید و اکسل چه کاری انجام میدهد.
پیدا کردن و حذف دادههای تکراری با فرمان Remove Duplicates
حذف مقادیر تکراری در دادهها عمل بسیار رایجی است. برای همین یک فرمان اختصاصی در ریبون برای آن وجود دارد.
سلولی را درون دادههایتان که میخواهید تکراریهایشان را حذف کنید، انتخاب کنید و به زبانهی Data رفته و و بر روی فرمان Remove Duplicates کلیک کنید.
اکسل تمامی مجموعهی دادهها را انتخاب خواهد کرد و پنجرهی Remove Duplicates باز خواهد شد.
- اگر دادههایتان حاول عناوین ستون در سطر اول است لازم است اکسل را مطلع کنید. اگر تیک داشته باشد، سطر اول دادههایتان به هنگام پیدا کردن و حذف مقادیر تکراری در نظر گرفته نخواهد شد.
- باید انتخاب کنید کدام ستونها برای تعیین تکراریها استفاده شود. در بالا دکمههای مفید Select All و Unselect All وجود دارند که میتوانید از آنها زمانی که لیست بلندی از ستونها در دادههایتان دارید، استفاده کنید.
وقتی OK را میزنید، اکسل تمام مقادیر تکراری یافتشده را حذف خواهد کرد و به شما خلاصهای از تعداد مقادیر حذفشده و باقیمانده ارائه خواهد کرد.
این فرمان جزئیات دادههایتان را تغییر خواهد داد بنابراین بهتر است این فرمان را بر روی کپیِ دادههایتان انجام دهید تا دادههای اصلی دستنخورده باقی بماند.
در این ویدئو نحوهی حذف دادههای تکراری با فرمان Remove Duplicates در حالتی که تنها یک ستون داریم، آموزش داده شدهاست.
پیدا کردن و حذف دادههای تکراری با فرمان Advanced Filter
راه دیگری برای خلاص شدن از شر مقادیر تکراری در دادههایتان از طریق ریبون وجود دارد. این راه استفاده از Advanced Filter است.
سلولی را درون دادههایتان انتخاب کنید و به زبانهی Data رفته و و بر روی فرمان Advanced filter کلیک کنید.
با این کار پنجرهی Advanced Filter باز خواهد شد.
- میتوایند Filter the list in place یا Copy to another location را انتخاب کنید. Filter the list in place سطرهای حاوی تکراریها را مخفی میکند در حالی که Copy to another location از دادههایتان یک کپی ایجاد میکند.
- اکسل محدودهی دادهها را حدس میزند، اما میتوانید آن را از List range تغییر دهید. Criteria range میتواند خالی باشد و فیلد Copy to باید پر شود اگر گزینهی Copy to another location انتخاب شده باشد.
- تیک جعبهی Unique records only را بزنید.
OK را بزنید مقادیر تکراری برایتان حذف خواهد شد.
Advanced filter میتوانید گزینهی خوبی برای خلاص شدن از شر مقدار تکراری و در همان حال ایجاد یک کپی از دادههایتان باشد. اما Advanced filter این کار را تنها میتواند بر روی تمام جدولتان انجام دهد.
پیدا کردن و حذف دادههای تکراری با پیوتتیبل
پیوتتیبل تنها برای تجزیهوتحلیل دادههایتان است؛ درست است؟
در واقع شما میتوانید از آن برای حذف دادههای تکراری هم استفاده کنید!
در واقع در این روش مقادیر تکراری از دادههایتان حذف نمیشود، بلکه از پیوتتیبل برای نمایش مقادیر منحصربهفردِ مجموعهی دادهها استفاده میشود.
ابتدا یک پیوتتیبل از دادههایتان ایجاد کنید. سلولی را درون دادههایتان و یا کل محدودهی دادههایتان انتخاب کنید و به زبانهی Insert رفته و PivotTable را انتخاب کنید. در پنجرهی PivotTable بازشده OK را بزنید.
در پیوتتیبل خالی تمام فیلدها را به ناحیهی Rows پیوتتیبل اضافه کنید.
حال میبایست نمای نتیجهی پیوتتیبل را تغییر دهید تا در قالب جدول قرار بگیرد. با انتخاب پیوتتیبل، به زبانهی Design رفته و Report Layout را انتخاب کنید. دو گزینه در اینجا موجود است که باید آن را تغییر بدهید.
- گزینهی Show in Tabular Form را انتخاب کنید.
- گزینهی Repeat All Item Labels را انتخاب کنید.
همچنین لازم است تمامی subtotalها را از پیوتتیبل حذف کنید. به زبانهی Design رفته ← Subtotals را انتخاب کنید ← Do Not Show Subtotals را انتخاب کنید.
اکنون شما یک پیوتتیبل دارید که شبیه یک مجموعه جدولی از دادهها است!
پیوتتیبل تنها مقادیر منحصربهفرد آیتمها را در ناحیهی Rows فهرست میکند، بنابراین به طور خودکار همهی تکراریهای دادههایتان را حذف خواهد کرد.
پیدا کردن و حذف دادههای تکراری با پاورکوئری
پاورکوئری کلاً با تغییر شکل دادهها سروکار دارد، بنابراین میتوانید مطمئن باشید قابلیت پیدا کردن و حذف مقادیر تکراری را داراست.
جدول مقادیری را که میخواهید تکراریهایش را حذف کنید انتخاب کنید ← به زبانهی Data رفته ← کوئری From Table/Range را انتخاب کنید.
حذف تکراریها بر اساس یک یا چند ستون
با پاورکوئری، میتوانید تکراریها را بر اساس یک یا چند ستون در جدول حذف کنید.
کافی است انتخاب کنید تکراریها بر اساس کدام ستونها باید حذف شوند. میتوانید کلید Ctrl را نگه دارید تا چندین ستون را انتخاب کنید.
بر روی عنوان ستون انتخابشده کلیکراست کنید و Remove Duplicates را از منو انتخاب کنید.
از این طریق نیز میتوانید این عمل را انجام دهید:
Home ➜ Remove Rows ➜ Remove Duplicates
= Table.Distinct(#"Previous Step", {"Make", "Model"})
اگر به فرمولی که ایجاد شدهاست نگاه کنید، از تابع Table.Distinct استفاده شده که پارامتر دوم به ستونهای استفادهشده ارجاع میکند.
حذف تکراریها بر اساس کل جدول
برای حذف تکراریها بر اساس کل جدول، باید همهی ستونهای جدول را انتخاب کرده سپس تکراریها را حذف کنید. اما روش سریعتری نیز وجود دارد که نیاز به انتخاب همهی ستونها ندارد.
دکمهای در بالا گوشهی چپ پیشنمایش دادهها با امکان انتخاب فرامین و اعمال آن بر کل جدول موجود است.
بر روی این دکمهی جدول بالا گوشهی چپ کلیک کنید ← حال Remove Duplicates را انتخاب کنید.
= Table.Distinct(#"Previous Step")
اگر به فرمولی که ایجاد شدهاست نگاه کنید، همان تابع Table.Distinct است بدون هیچ پارامتر دومی. بدون پارامتر دوم، تابع بر روی تمام جدول عمل میکند.
نگه داشتن تکراریها بر اساس یک ستون تنها یا کل جدول
در پاورکوئری، فرمانی هم برای نگه داشتن تکراریها برای ستونهای انتخابشده یا کل جدول وجود دارد.
همان گامهای حذف تکراریها را انجام داده، اما از فرمان Keep Rows به جای Keep Duplicates استفاده کنید. این کار همهی دادههایی را که مقدار تکراری دارند نمایش خواهد داد.
پیدا کردن و حذف دادههای تکراری با فرمول
میتوانید از یک فرمول برای یافتن مقادیر تکراری در دادههایتان کمک بگیرید.
ابتدا لازم است یک ستون کمکی اضافه کنید که دادهها را از هر ستونی که میخواهید تعریف تکراریها بر اساس آن باشد، با هم ادغام کند.
= [@Make] & [@Model] & [@Year]
فرمول فوق هر سه ستون را در یک ستون ادغام میکند. برای این کار از عملگر & برای الحاق هر ستون استفاده شده.
= TEXTJOIN("", FALSE , CarList[@[Make]:[Year]])
اگر لیست بلندی از ستونها برای ادغام دارد، به جای آن میتوانید از فرمول فوق استفاده کنید. از این راه میتوانید به آسانی به همهی ستونها همچون یک محدودهی منفرد ارجاع کنید.
حال لازم است ستون دیگری را برای شمارش مقادیر تکراری اضافه کنید. از این ستون بعداً برای فیلتر سطرهای دادههایی که یک بار بیشتر ظاهر شدهاند، استفاده خواهد شد.
= COUNTIFS($E$3:E3, E3)
فرمول فوق را از ابتدا تا آخر ستون کپی کرده تا تعداد دفعاتی را که مقدار جاری در لیست مقادیر بالا ظاهر شدهاست، شمارش شود.
اگر تعداد 1 باشد، یعنی این اولین باری است که این مقدار در میان دادهها ظاهر شدهاست و آن را در مجموعه مقادیر منحصربهفرد نگه دارید. اگر تعداد 2 یا بیشتر باشد یعنی مقدار قبلاً در دادهها ظاهر شده و تکراری است و میتواند حذف شود.
به لیست دادههایتان فیلتر اضافه کنید.
- به زبانهی Data رفته و فرمان Filter را انتخاب کنید.
- یا میانبر صفحهکلید Ctrl+Shift+L را استفاده کنید.
حال میتوانید ستون تعداد (Count) را فیلتر کنید. انتخاب فیلتر 1 تمام مقادیر منحصربهفرد را تولید کرده و تکراریها را حذف میکند.
میتوانید سلولهای قابل رؤیت را از نتایج فیلتر انتخاب کنید و هر جا خواستید کپی و پیست کنید. برای انتخاب تنها سلولهای قایل رؤیت از میانبر صفحهکلید Alt+; استفاده کنید.
پیدا کردن و حذف دادههای تکراری با کاندیشنال فرمتینگ
با کاندیشنال فرمتینگ راهی برای هایلایت کردن مقادیر تکراری در دادههایتان وجود دارد.
همانند روش فرمول، باید یک ستون کمکی برای ادغام دادههای ستونها اضافه کنید. کاندیشنال فرمتینگ با دادهها در سطرها کار نمیکند، بنابراین اگر بخواهید تکراریها را بر اساس بیشتر از یک ستون شناسایی کنید، به این ستون ادغامی نیاز خواهید داشت.
حال لازم است ستون دادههای ادغامشده را انتخاب کنید.
برای ایجاد کاندیشنال فرمتینگ این مسیر را دنبال کنید: زبانهی Home ← انتخاب Conditional Formatting ← انتخاب Highlight Cells Rules ← انتخاب Duplicate Values
با این کار پنجرهی Duplicate Values باز خواهد شد.
- میتوانید یکی از مقادیر Duplicate (تکراری) یا Unique (منحصربهفرد) را انتخاب کنید.
- همچنین می توانید برای هایلات کردن مقادیر از میان مجموعهای از فرمتهای سلول ازپیشتعریفشده یکی را انتخاب کنید یا فرمت دلخواه خود را ایجاد کنید.
توجه: روش قبلی برای پیدا کردن و حذف تکراریها اولین وقوع داده را به عنوان یک بار تکرار در نظر میگرفت و آن را دستنخورده رها میکرد. اما این روش اولین وقوع را هم هایلایت خواهد کرد و هیچ تمییزی قائل نمیشود.
با مقادیر هایلایتشده، حال میتوانید با استفاده از گزینهی فیلتر بر اساس رنگ مقادیر تکراری و یا منحصربهفرد را فیلتر کنید. ابتدا به دادههایتان فیلتر اضافه کنید. به زبانهی Data رفته و فرمان Filter را انتخاب کنید یا میانبر صفحهکلید Ctrl+Shift+L را استفاده کنید.
- بر روی سوئیچ (toggle) کلیک کنید.
- Filter by Color را از منو انتخاب کنید.
- از رنگ استفادهشده در conditional formatting برای انتخاب مقادیر تکراری و یا از No Fill برای انتخاب مقادیر منحصربهفرد برای فیلتر کردن استفاده کنید.
حال میتوانید فقط سلولهای قابل رؤیت را انتخاب کنید با میانبر صفحهکلید Alt+;.
پیدا کردن و حذف دادههای تکراری با استفاده از VBA
یک فرمان توکار در VBA برای حذف تکراریها در بین اشیای لیست وجود دارد.
Sub RemoveDuplicates()
Dim DuplicateValues As Range
Set DuplicateValues = ActiveSheet.ListObjects("CarList").Range
DuplicateValues.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub
پروسیجر فوق تکراریها را از یک جدول اکسل با نام CarList حذف خواهد کرد.
Columns:=Array(1, 2, 3)
قسمت فوق از پروسیجر تنظیم میکند کدام ستونها اساس تشخیص تکراریها باشد. در این مورد تمام جدول خواهد بود زیرا هر سه ستون لیست شدهاند.
Header:=xlYes
قسمت فوق از پروسیجر به اکسل میگوید اولین سطر در لیست ما حاوی عناوین ستون است.
قبل از اجرای این کد VBA، یک کپی از دادههایتان ایجاد کنید، چرا که اگر کد را اجرا کنید دیگر قابل برگشت نخواهد بود.
ویدئوی آموزشی
در ویدئوی زیر نحوهی حذف دادههای تکراری به دو روش (استفاده از فرمول و کاندیشنال فرمتینگ) در حالتی که تنها یک ستون داریم، آموزش داده شدهاست.
نتیجهگیری
مقادیر تکراری در دادههایتان میتواند مانعی بزرگ برای مجموعهای از دادههای تمیز و خوانا باشد.
خوشبختانه روشهای بسیاری در اکسل وجود دارد تا به راحتی آن مقادیر تکراری مزاحم را حذف کنید.
روش شما برای حذف تکراریها چیست؟
خیلی ممنون از مطالب مفیدتان
خیلییییییی عالییی بود.
واقعا ممنون از آموزش خوبتون. خدا قوت.
درود بر شما
با سلام و درود
عالی بود
سپاسگزارم
بسیار عالی ممنونم
سلام
وقت بخیر
ممنون از وقتی که میذارید
مهندس اگه بخوام رکورد تکراری را با اصل رکوردپاک کنم باید چکارکنم ؟
به عنوان مثال یه بانک اطلاعاتی دارم صدتا شماره موبایل توش هست ، ده تا شماره دیگه از فایل دیگه میارم کپی میکنم ، میخوام شماره های تکراری که از فایل جدید آوردم با شماره ای که روی فایل اصلی بوده هردو با هم پاک بشن
سلام. برای این کار باید ستون را انتخاب کرده با استفادە از کاندیشنال فرمتیگ گزینه Highlight Cells Rules سپس Duplicate Values را انتخاب کنید. در ادامه ستون را بر اساس رنگ سلول فیلتر کنید و موارد فیلترشده را حذف کنید.
سلام وقتتون بخیر
ما مثلا ۱۰۰۰ تا ردیف تاریخ داریم که به صورت تکراری پشت سر هم قرار دارن به طور مثال ۱۳۹۹/۰۶/۲۴ – ۱۳۹۹/۰۶/۲۴ وقتی میخوایم تکراری هارو حذف کنیم همه روشها اولی رو نگه میداره مابقی رو حذف میکنه،آیا روشی وجود داره که آخری رو نگه داره و قبلیارو حذف کنه؟ ممنون میشم راهنماییم کنید.
درود. شما باید دادههایتان را معکوس کنید (برای این کار یک ستون شماره ردیف ایجاد کنید و آن را از بزرگ به کوچک سورت کنید). حال با زدن Remove Duplicate آخرین مورد نگه داشتهشده و بقیه حذف میشوند.
سلام ممنون از اموزشهای عالی و با جزئیات و نکات مهم و تشکر مجدد از زحمات شما
سلام ممنون از آموزش های عالی و با جزئیاتتان . خدا قوت
عالی بود
سپاسگزارم
عالی بود
درود بر شما.
سلام بسیار مفید و کاربردی بود ممنونم