حذف تکرار در اکسل

لیست بدون تکرار در اکسل – حذف داده های تکراری در دو ستون اکسل – یافتن داده های تکراری در اکسل

یکی از چالش هایی که کاربران رد زمان کار با نرم افزار اکسل با آن رو به رو میشوند وجود داده های تکراری در سلول های اکسل میباشد. اگر پروژه اکسل کوچک باشد شاید پیدا کردن داده های تکراری در اکسل کار راحتی باشد اما ایجاد لیست بدون تکرار در اکسل در پروژه های بزرگ با داده های فراوان یک چالش بزرگ محسوب میشود. در این مقاله میخواهیم روش های مختلف حذف تکرار در اکسل را بررسی کنیم و بهترین روش ها را به شما آموزش دهیم.

اگر سوالی در هر بخش از آموزش حذف داده های تکراری در اکسل داشتید میتوانید در قسمت نظرات بیان کنید تا اساتید اکسل ما در اسرع وقت پاسخ شما را بدهند. همچنین اگر میخواهید صفر تا صد اکسل را به صورت کاربردی و متاسب با نیاز بازار کار یاد بگیرید میتوانید به صفحه آموزش اکسل مراجعه کنید و فیلم های آموزشی رایگان قرار داده شده در این صفحه را مشاهده کنید.

حذف تکرار در اکسل
اولین کاری که باید برای حذف تکرار در اکسل انجام دهید و مهمترین و اصلی ترین کار در این مورد یافتن داده های تکراری در اکسل میباشد. اولین قدم برای حذف تکرار در ردیف ها و سلول های اکسل پیدا کردن داده های تکراری میباشد.

مایکروسافت اکسل یک برنامه کاربردی است که به طور گسترده در امور اداری و حسابداری مورد استفاده قرار می گیرد اما این نرم افزار در مورد حذف داده های تکراری کمی کاربر را گیج کرده است.

حذف موارد تکراری در اکسل یک کار رایج برای افرادی است که روی مجموعه داده های بزرگ در اکسل کار می کنند. هنگامی که جداول مختلف اکسل را ترکیب می کنید، یا زمانی که افراد زیادی به یک سند اکسل دسترسی دارند، ممکن است در نهایت با داده های تکراری زیادی در صفحه گسترده خود مواجه شوند.

هرچه مجموعه داده بزرگتر باشد، شانس مواجهه با رکوردهای تکراری بیشتر است. اگر آنها را به درستی شناسایی و مدیریت نکنید، می تواند مشکل ساز باشد.

روش های مختلفی برای این کار وجود دارد که در این مقاله تمام روش ها را مورد بررسی قرار میدهیم.

حذف تکرار در اکسل با گزینه Remove Duplicates
اکسل یک ابزار داخلی دارد که به حذف ورودی های مکرر در مجموعه داده شما کمک می کند.مراحلی که باید برای حذف داده های تکراری در اکسل انجام شود را در قسمت زیر بررسی کرده ایم.

ابتدا روی هر سلول یا محدوده خاصی در مجموعه داده ای که می خواهید موارد تکراری را از آن حذف کنید، کلیک کنید. اگر روی یک سلول کلیک کنید، اکسل به طور خودکار محدوده را در مرحله بعد برای شما تعیین می کند.
حذف تکرار در اکسل با گزینه Remove Duplicates
سپس گزینه Remove Duplicates را پیدا کرده و آن را انتخاب کنید.
تب DATA ← بخش Data Tools← انتخاب Remove Duplicates
حذف تکرار در اکسل با گزینه Remove Duplicates
یک پنجره جدید ظاهر می شود، همانطور که در تصویر زیر نشان داده شده است. می‌توانید ستون‌هایی را که می‌خواهید مقایسه کنید انتخاب کنید و داده‌های تکراری را بررسی کنید.
در صورتی که داده‌های شما شامل سرصفحه‌های ستون است، گزینه My data has headers را انتخاب کرده و روی OK کلیک کنید.
با بررسی گزینه header، ردیف اول برای حذف مقادیر تکراری در نظر گرفته نمی شود.

حذف تکرار در اکسل با گزینه Remove Duplicates
اکسل اکنون ردیف های تکراری را حذف می کند و یک پنجره جدید نمایش می دهد. این پنجره خلاصه ای از تعداد مقادیر تکراری که پیدا و حذف شده اند همراه با تعداد مقادیر منحصر به فرد در جدول اکسل را نشان می دهد.
حذف تکرار در اکسل با گزینه Remove Duplicates
همانطور که مشاهده کردید رکوردهای تکراری اکسل حذف می شوند.

حذف تکرار در اکسل با گزینه Remove Duplicates
روش های دیگری نیز برای حذف داده های تکراری در اکسل وجود دارد که در ادامه مقاله این روش ها را به شما آموزش میدهیم.

حذف داده های تکراری در اکسل با Advanced Filter
گزینه Advanced Filter در نرم افزار اکسل به شما کمک می کند مقادیر تکراری را فیلتر کرده و مقادیر منحصر به فرد را در مکان دیگری کپی کنید. برای اطلاع از نحوه عملکرد گزینه Advanced Filter به مراحل زیر توجه کنید و به ترتیب این مراحل را انجام دهید.

ابتدا روی سلول یا محدوده ای در مجموعه داده ای که می خواهید موارد تکراری را از آن حذف کنید، کلیک کنید. اگر روی یک سلول کلیک کنید، اکسل به طور خودکار محدوده را با کلیک بر روی Advanced Filter تعیین می کند.
حذف داده های تکراری در اکسل با Advanced Filter
گزینه Advanced Filter را پیدا کنید.
تب DATA → قسمت Sort & Filter → Advanced انتخاب و روی آن کلیک کنید.

حذف داده های تکراری در اکسل با Advanced Filter
یک پنجره برای شما باز میشود که این پنجره شامل لیستی از گزینه های فیلتر پیشرفته است.
برای کپی کردن مقادیر منحصر به فرد در مکان دیگری، گزینه “Copy to another location” را انتخاب کنید.
محدوده رکوردهای خود را در قسمت “List Range” بررسی کنید و مطمئن شوید که محدوده ای است که شما مشخص کرده اید.
در فیلد «Copy to:»، محدوده‌ای را وارد کنید که مقادیر منحصربه‌فرد حاصل باید در آنجا کپی شوند.
کادر «Unique records only / فقط رکوردهای منحصر به فرد» را علامت بزنید. این مرحله مهم ترین بخش است که باید برای حذف داده های تکراری در اکسل انجام دهید.
روی OK کلیک کنید.
حذف داده های تکراری در اکسل با Advanced Filter
در این مثال مقادیر منحصر به فرد در سلول G1 کپی می شود.
حذف داده های تکراری در اکسل با Advanced Filter
حذف داده های تکراری در اکسل با فرمول
از دیگر روش هایی که میتوانید برای حذف تکرار در اکسل از آن استفاده کنید بهره گیری از فرمول ها و توابع میباشد.

ما از یک مثال ساده شامل ستون‌ها برای درک بهتر این مسئله استفاده خواهیم کرد که در این مثال ستون های نوع ورزش، نام ورزشکار و مدال کسب شده برای نشان دادن این رویکرد.

حذف داده های تکراری در اکسل با فرمول
این روش شامل ترکیب ستون ها با استفاده از فرمول اکسل و یافتن تعداد تکراری ها است. سپس مقادیر تکراری (آنهایی که تعداد آنها بیشتر از 1 است) را فیلتر می کنیم.

بیایید ستون های A، B و C را با استفاده از عملگر الحاق “&” ترکیب کنیم. بنابراین، فرمول اکسل به صورت زیر خواهد بود:
A2&B2&C2=

این فرمول در سلول D2 وارد می شود و سپس در تمام ردیف ها کپی می شود.

حذف داده های تکراری در اکسل با فرمول
اکنون، برای یافتن موارد تکراری در ستون D به ستون دیگری به نام «Count» نیاز داریم. بنابراین، از تابع COUNTIF در سلول E2 استفاده می‌کنیم. فرمول به صورت زیر خواهد بود:
COUNTIF($D$2:D2,D2)=

این فرمول به شمارش تعداد دفعات هر مقدار در ستون D کمک می کند.

آموزش حذف داده های تکراری در اکسل با فرمول
اگر مقدار “Count” برابر با 1 باشد، فقط یک بار ظاهر شده است و منحصر به فرد است. اگر مقدار “2” یا بیشتر باشد، یک مقدار تکراری در نظر گرفته می شود.

حالا با انتخاب گزینه Filter یک فیلتر به ستون Count اضافه کنید.
این قابلیت را در تب DATA → قسمت Sort & Filter → Filter پیدا خواهید کرد.

روی Filter بالای ستون E کلیک کنید. “1” را انتخاب کنید تا فقط مقادیر منحصر به فرد را حفظ کنید و موارد تکراری را حذف کنید.

آموزش حذف داده های تکراری در اکسل با فرمول
با کلیک بر روی OK، مقادیر تکراری از جدول حذف خواهند شد. می‌توانید این رکوردهای منحصربه‌فرد حاصل را کپی کرده و در جای دیگری جای‌گذاری کنید.

آموزش حذف داده های تکراری در اکسل با فرمول
لیست بدون تکرار در اکسل با ابزار Power Query
از دیگر راه هایی که میتوان بری حذف تکرار در اکسل از آن استفاده کرد ابزار Power Query میباشد. نرم افزار اکسل قابلیت های زیادی را برای حذف تکراری ها ارائه میدهد. اگر میخواهید با همه کاربرد ها و قابلیت های اکسل آشنا شوید حتما مقاله اکسل چیست را مطالعه کنید.

Power Query در اکسل به شما امکان می دهد داده ها را از منابع مختلف وارد کنید، داده های خود را فیلتر و تغییر دهید. این ابزار حذف موارد تکراری در اکسل را بدون دردسر حذف می کند.

یک سلول یا محدوده را انتخاب کنید و سپس مسیر زیر را طی کنید:
Data Tab → Get & Transform Data → From Table/Range
لیست بدون تکرار در اکسل با ابزار Power Query
با کلیک کردن، یک پنجره برای ایجاد جدول power query خواهید دید. مطمئن شوید که محدوده مقادیر به درستی مشخص شده است. روی OK کلیک کنید.
لیست بدون تکرار در اکسل با ابزار Power Query
پنجره ویرایشگر Power Query زیر ظاهر می شود.

لیست بدون تکرار در اکسل با ابزار Power Query
حالا شما برای داشتن لیست بدون تکرار در اکسل دو گزینه دارید. می توانید موارد تکراری را بر اساس موارد زیر حذف کنید:

یک یا چند ستون
کل جدول
برای حذف موارد تکراری بر اساس یک یا چند ستون، روی عنوان ستون خاص کلیک راست کنید. شما می توانید بیش از یک ستون را با استفاده از دکمه CTRL انتخاب کنید و بر اساس آن موارد تکراری را حذف کنید.

لیست بدون تکرار در اکسل با ابزار Power Query
برای حذف رکوردهای تکراری بر اساس کل جدول، روی دکمه موجود در گوشه سمت چپ بالای پیش نمایش داده ها کلیک کنید و سپس گزینه “Remove Duplicates” را انتخاب کنید. به این ترتیب داده ها از مقادیر تکراری پاک می شوند.

با کلیک بر روی گزینه “Close & Load” داده ها در صفحه گسترده شما بارگیری می شوند.

حذف داده های تکراری اکسل با گزینه Remove Duplicates
در اکسل، ویژگی Remove Duplicate می تواند به شما کمک کند تا همه تکراری ها را حذف کنید و یا به دلخواه یک مقدار تکراری را نگه دارید.

لیست داده های مورد نیاز خود را انتخاب کنید و روی Data > Remove Duplicates کلیک کنید.
حذف داده های تکراری اکسل با گزینه Remove Duplicates
سپس در پنجره Remove Duplicates، نام ستونی را که می‌خواهید موارد تکراری را حذف کنید، علامت بزنید و اگر داده‌های شما در هدر قرار دارند و نمیخواهید داده های تکراری هدر را در نظر بگیرد گزینه My data has headers را نیز تیک بزنید.
حذف داده های تکراری اکسل با گزینه Remove Duplicates
روی OK کلیک کنید. و یک پنجره ظاهر می شود که به شما می گوید چند مورد تکراری حذف شده است، فقط آن را ببندید. و اکنون همه موارد تکراری حذف شده اند اما فقط یکی را در لیست نگه دارید.
حذف داده های تکراری اکسل با گزینه Remove Duplicates
حذف تکرار در اکسل با کد VBA
اگر به VBA در اکسل آشنایی دارید در اینجا می توانم یک کد VBA را برای حل این کار معرفی کنم.

اگر با نحوه ذخیره فایل های ماکرو و کد های vba در اکسل آشنایی ندارید پیشنهاد میکنیم حتما مقاله ذخیره ماکرو در اکسل را مطالعه کنید.

کلیدهای Alt + F11 را به طور همزمان فشار دهید تا پنجره Microsoft Visual Basic برای برنامه ها باز شود.
روی Insert > Module کلیک کنید و در زیر VBA در پنجره جدید Module قرار دهید.
()Sub RemoveAllDeplicateButOne
Dim LR As Long: LR = Range(“A” & Rows.Count).End(xlUp).Row
ActiveSheet.Range(“A1:A” & LR).RemoveDuplicates Columns:=1, Header:=Name
End Sub
کلید F5 را فشار دهید یا روی Run کلیک کنید تا VBA اجرا شود و موارد تکراری در لیست ستون A حذف شده اند اما یکی را نگه دارید.

نکته: در VBA بالا، A نشان دهنده ستون A، و A1: A نشان دهنده محدوده لیست است، شما می توانید آنها را در صورت نیاز تغییر دهید.