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

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

داده‌ی تکراری چیست

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

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

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

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

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

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

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

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

پیشنهاد ویژه : نمونه سوالات عملی اکسل با جواب pdf

حذف تکراری ها در اکسل با Remove Duplicates

برای این کار ابتدا سلول مورد نظر که قصد دارید داده های تکراری آن را حذف کنید، انتخاب کرده و سپس وارد زبانه Data شوید و بر روی فرمان Remove Duplicates کلیک کنید.

حال برای شما یک پنجره جدید باز می شود که می توانید آن را در عکس زیر مشاهده کنید شما می توانید ستون هایی که قصد دارید مقایسه کنید انتخاب کرده و داده های تکراری موجود در آن را بررسی کنید. در صورتی که داده های شما شامل سرسفحه های ستون می باشد، می بایست روی گزینه My data has headers ضربه بزنید و روی OK کلیک کنید.

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

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

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

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

پیدا کردن و حذف داده‌های تکراری با فرمان Advanced Filter

یکی از ساده ترین روش ها برای حذف اسامی تکراری در اکسل استفاده از Advanced Filter می باشد که کار با آن بسیار ساده است.

Advanced Filter به شما کمک می کند تا بتوانید مقادری تکراری را فیلتر کرده و مقادیر غیرتکراری را به مکان دیگری کپی کنید.

برای حذف duplicate در اکسل به روش Advanced Filter باید طبق مراحل زیر عمل کنید:

در ابتدا روی سلول مورد نظر یا داده ای که قصد دارید موارد تکراری آن را حذف کنید کلیک کنید. در صورتی که روی یک سلول کلیک کنید، نرم افزار اکسل به صورت خودکار محدوده را با کلیک بر روی Advanced Filter تعیین می کند.

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

در این قسمت برای شما یک پنجره باز می شود که در این پنجره لیستی از گزینه های فیلتر پیشرفته وجود دارد. حال برای کپی کردن مقادیر غیرتکراری به مکانی دیگر، باید روی گزینه Copy to another location ضربه بزنید. محدوده رکوردهای خود را در قسمت List Rangeبررسی کرده و اطمینان حاصل کنید که محدوده ای است که شما آن را انتخاب کرده اید.

حال در بخش Copy to: محدوده ای را باید وارد کنید که مقادیر منحصر به فرد باید در آن قسمت کپی کنید. سپس در کادر «Unique records only / تنها رکوردهای منحصر به فرد» را علامت بزنید. توجه داشته باشید که این مرحله از مهم ترین مراحل برای حذف رکورد تکراری در اکسل می باشد. روی گزینه OK کلیک کنید.

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

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

یکی دیگر از روش هایی که برای حذف تکرار در اکسل به کار می رود، با استفاده از فرمول و توابع می باشد. ما در این قسمت از پیدا کردن و حذف داده‌های تکراری اکسل با فرمول یک مثال ساده برای درک بهتر شما از این مسئله آورده ایم که در این مثال ستون های نوع ورزش، نام ورزشکار و مدال کسب شده برای نشان دادن این رویکرد تعیین شده اند.

این روش شامل ترکیب کردن ستون ها توسط فرمول اکسل و پیدا کردن تعداد تکراری ها می باشد. در ادامه مقادیر تکراری، آن هایی که تعدادشان بیشتر از ۱ است را فیلتر کنید.

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

A2&B2&C2= این فرمول در سلول D2 وارد می شود و سپس در تمام ردیف ها کپی می شود.

با مراجعه به صفحه تبدیل تاریخ شمسی به میلادی در اکسل می توانید با فرمول تبدیل تاریخ شمسی به میلادی در اکسل ۲۰۱۶ و دیگر نسخه ها آشنا شوید.

حال برای شناسایی سلول های تکراری در ستون D به ستون دیگری به نام Count نیاز داریم. به همین دلیل از تابع COUNTIF در سلول E2 باید استفاده کنید که فرمول به صورت زیر خواهد بود:

COUNTIF($D$2:D2,D2)= این فرمول به شمارش تعداد دفعات هر مقدار در ستون D کمک می کند.

در صورتی که مقدار “Count” برابر با ۱ باشد، فقط یک بار ظاهر شده است و منحصر به فرد خواهد بود. اما اگر مقدار “۲” یا بیشتر باشد، بعنوان یک مقدار تکراری محسوب می شود.

در این قسمت با انتخاب گزینه Filter یک فیلتر به ستون Count اضافه کنید. این قابلیت را در تب DATA → قسمت Sort & Filter → Filter پیدا خواهید کرد. سپس روی Filter بالای ستون E کلیک کنید. “۱” را انتخاب نمایید تا فقط مقادیر منحصر به فرد را حفظ کنید و موارد تکراری را حذف کنید.

در این قسمت با کلیک بر روی گزینه ok اقدام به استخراج داده های تکراری در اکسل نمایید.

حذف سلولهای تکراری در اکسل با ابزار Power Query

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

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

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

Data Tab → Get & Transform Data → From Table/Range

نحوه اضافه کردن ستون و ردیف یکی از رایج ترین مسائلی است که ممکن است در هنگام کار با نرم افزار پرکاربرد اکسل با آن روبرو شوید. برای رفع این مشکل پیشنهاد می کنیم به صفحه اضافه کردن ستون و ردیف در اکسل مراجعه کنید.

حال با کلیک، یک پنجره برای ساخت جدول power query مشاهده خواهید کرد. در این مرحله اطمینان پیدا کنید که محدوده مقادیر به درستی تعیین شده است. سپس روی ok کلیک کنید.

با کلیک بر روی ok پنجره ویرایشگر Power Query که در قسمت زیر مشاهده می کنید برای شما باز می شود.

پیشنهاد ویژه : راست چین و چپ چین کردن اکسل

در این قسمت شما برای داشتن یک لیست غیرتکراری در اکسل، دو گزینه پیش رو خواهید داشت.

• یک یا چند ستون
• کل جدول

برای حذف داده های تکراری در یک ستون اکسل یا چند ستون، روی عنوان ستون مورد نظر کلیک راست کنید. شما می توانید برای انتخاب بیش از ۱ ستون، از دکمه CTRL استفاده کنید و طبق آن موارد به حذف داپلیکیت در اکسل بپردازید.

برای حذف ستون های تکراری در اکسل بر اساس کل جدول، می بایست روی دکمه سمت چپ در بالای پیش نمایش داده ها کلیک کرده و گزینه Remove Duplicates را انتخاب کنید. با این کار داده های تکراری حذف خواهند شد. حال با کلیک بر روی گزینه Close & Load داده ها در صفحه گسترده شما بارگیری خواهند شد.

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

پیدا کردن و حذف داده‌های تکراری با فرمان Remove Duplicates

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

برای این کار می بایست لیست داده های مورد نیاز خود را انتخاب کرده و روی Data > Remove Duplicates کلیک کنید.

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

یکی از قابلیت های مهم نرم افزار اکسل، قفل کردن یا فریز کردن سلول های اکسل می باشد که برای  برای ثابت کردن سطرها یا ستون‌ ها در داده ها مورد استفاده قرار می گیرد. اگر می خواهید با نحوه قفل کردن سلول ها در اکسل آشنا شوید مقاله فریز کردن در اکسل را مطالعه کنید.

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

اگر می خواهید فایل های پی دی اف خود را بدون بههم ریختگی به فایل اکسل تبدیل کنید اما نمی دانید چگونه باید این کار را انجام دهید، مطالعه مقاله تبدیل پی دی اف به اکسل می تواند بهترین راهنمای شما باشد.

حذف تکرار در اکسل با کد 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 نشان دهنده محدوده لیست می باشد و شما می توانید آن ها را در صورت لزوم تغییر دهید.

پیشنهاد ویژه : ساخت جدول در اکسل

شمارش داده های تکراری در اکسل

جالب است بدانید که یکی از روش های شمارش داده های تکراری در اکسل با استفاده از VBA امکان پذیر است.

پارامترهای قابل تنظیم به صوزت زیر می باشند:

محدوده خروجی: محدوده خروجی را باید توسط تغییر سلول مرجع (“E5”) در کد VBA انتخاب کنید.

محدوده داده ها: محدوده ای را که قصد دارید داده های تکراری را بررسی کنید با تغییر محدوده مرجع (“B5:B9”) در کد VBA، انتخاب نمایید.

مقدار تکراری: سلولی که مقداری را می گیرد که می خواهید تعداد دفعات وقوع آن را در محدودی خاصی مورد بررسی قرار دهید، با تغییر سلول مرجع (“D5”) در کد VBA یا با تغییر مقدار در سلول (“D5”)، انتخاب کنید.

یکی از مشکلاتی که کاربران ایرانی در انجام پروژه های مختلف با آن روبرو هستند، فارسی کردن اعداد در اکسل می باشد که با مطالعه مقاله فارسی با کردن اعداد در اکسل می توانید این مشکل را برای همیشه رفع کنید.

پیدا کردن و حذف داده‌های تکراری با پیوت‌تیبل

یکی دیگر از روش هایی که می تواند به شما در حذف داده های تکراری در دو ستون اکسل کمک کند، استفاده از PIVOT TABLE می باشد.

برای استفاده از این روش می توانید در ابتدا داده های را به PIVOT تبدیل کنید. در زبانه PIVOT TABLE، گزینه INSERT را بزنید و جدول مورد نظر را انتخاب کنید. حال از شما می پرسد، قصد دارید نتیجه را کجا بگذارید؟ به طور مثال شما می توانید بگویید که در همین شیت قصد دارید نگه دارید.

تصور کنید که به طور مثال در کنار داده های قبلی در سلول H11 بگذارید و OK را بزنید.

هتگامی که پیوت را ایجاد می کنیم PRODUCT را در قسمت ROWS می آوریم که اینجا مشاهده خواهید کرد که PRODUCT ها را به صورت یونیک آورده است.

حال در این مرحله می توانید پنجره PIVOT TABLE FIELDS را ببندید و علاوه بر این می توانید جمع کل (GRAND TOTAL) را هم کلیک راست کنید تا نمایش داده نشود.

رنگی شدن سلول های تکراری در اکسل

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

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

سپس در قسمت نوار ابزار بالای صفحه روی تب Home و سپس روی Conditional Formatting ضربه بزنید. حال در منوی کرکره ای که مشاهده می کنید، Highlight Cells Rules‌ را انتخاب کرده و سپس گزینه Duplicate Values را انتخاب نمایید که به معنی مقادیر تکراری است.

در این مرحله از رنگی شدن سلول های تکراری در اکسل، پنجره ای جهت انتخاب کردن روش هایلایت کردن سلول‌ ها و همچنین نوع سلول‌ ها برای شما نمایش داده می شود. برای رنگی کردن سلول های تکراری در اکسل، می توانید از منوی کرکره ای، گزینه Duplicate را انتخاب کنید و از منوی کرکره ای دوم یکی از انواع هایلایت ها را نیز انتخاب کنید.

همانطور که مشاهده خواهید کرد در حالت پیش فرض رنگ پس‌زمینه‌ی سلول قرمز کم‌ رنگ و رنگ متن درون سلول، قرمز تیره می باشد. شما می توانید از رنگ های زرد و سبز نیز با استفاده از گزینه های بعدی استفاده کنید.

با استفاده از Light Red Fill تنها پس‌ زمینه‌ سلول قرمز کم‌ رنگ می‌شود.

با استفاده از Red Text می‌ توانید تنها متن را قرمز کنید و سلول رنگ پیش‌ فرض را حفظ می‌ کند.

با استفاده از Red Border مرز یا خط دور سلول قرمز می‌شود و درون آن هیچگونه تغییری نمی کند.

جمع بندی

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

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

سوالات متداول