۳ روش مختلف برای پیدا کردن داده های تکراری در اکسل
سلام . به سافت پلاس و به مطلب دیگری در مورد آموزش اکسل خوش آمدید . امروز می خواهیم در مورد اصطلاحی به اسم داپلیکیت در اکسل یا به عبارتی در مورد پیدا کردن داده های تکراری در اکسل و روش های مختلف مربوط به این کار با هم صحبت کنیم .
مطمئنا شما هم با من هم عقیده هستید که کار کردن با داده های تکراری می تواند در خیلی از موارد برای ما درد سرهایی را ایجاد کند .
البته تا وقتی که حجم داده های ما محدود است – مثلا کمتر از ۱۰ مورد و ۲۰ مورد و یا حالا کمی کمتر و یا بیشتر – شاید کار کردن با این داده های تکراری و یا به اصطلاح داده های داپلیکیت در اکسل و اینکه بخواهیم آنها را پیدا و حذف کنیم چندان درد سری برای ما ایجاد نکند .
اما وقتی که حجم اطلاعات زیاد می شود مطمئنا قضیه دیگر به این سادگی ها نخواهد بود .
خوب برای اینکه بتوانیم این داده های تکراری را پیدا کنیم چه راههایی به ذهن تان می رسد .
اینکه بخواهید خودتان دست به کار شوید و این داده ها را پیدا کنید یک راه حل است؛ اما نه راه حل ایده آلی است و نه اینکه همیشه و همه جا جوابگو ست .
پس راهکار بهتر چیست ؟
بهترین راهکار این است که همه کار ها را به اکسل بسپارید .اکسل برای یافتن داده های تکراری راه حل های بی نظیری فراهم کرده است .
فهرست محتوا
اکسل برای یافتن داده ها و اطلاعات تکراری راه حل های بی نظیری را برای ما فراهم کرده است .
کافی که ما با یک یا همه این راهکارها آشنا شویم و در انتها بنا بر نیاز و سلیقه خود یکی از آنها را به کار گیریم تا به راحتی هر چه تمامتر این اطلاعات تکراری برای ما پیدا شوند .
امروز و در مطلب تحت عنوان جستجو و پیدا کردن داده های تکراری در اکسل می خواهیم با هم به بررسی این راهها بپردازیم .
من علیرضا شهرآئینی هستم و سعی می کنم تمامی آنچه را که برای این کار نیاز دارید را به ساده ترین شکل ممکن به شما آموزش دهم .
پس از شما دعوت می کنم که تا انتهای این آموزش همراه من باشید .
معرفی روش های پیدا کردن داده های تکراری در اکسل
توی اکسل وقتی که صحبت از داده تکراری می کنیم در حقیقت 4 تا دغدغه وجود دارد که باید برای هر کدام از انها به دنبال یک راهکاری مناسبی باشیم .
دیاگرام زیر این دغدغه ها در خصوص داده ها و اطلاعات تکراری در اکسل را نشان می دهند .
برای انجام هر کدام از این کارها روش های خاصی وجود دارد .
اما اجازه بدهید کار خودمان را با آشنایی با روش های پیدا کردن داده های تکراری توی اکسل شروع کنیم .
بیایید با این مثال پیش برویم .
فایل اکسلی را در نظر بگیرید که شامل نام های ایرانی مورد استفاده برای نامگذاری پسران است .
بنابر هر دلیلی قرار نیست که نام افراد در این لیست بصورت تکراری آورده شود .
به عنوان مثال به جدول زیر توجه کنید .
ما در این جدول لیستی از اسامی پسرانه ایرانی را فراهم آورده ایم قاعدتا نیاز نیست که در این جدول اسامی به صورت تکراری وارد شوند.
اما من به شما می گویم که در این جدول تعدادی از اسامی به صورت تکراری وجود دارند
آیا شما می توانید این اسامی را در یک نگاه پیدا نموده و آنها را مشخص کنید؟
مسلما پاسخ شما منفی خواهد بود جرا که برای پیدا کردن تک تک اسامی که به صورت تکرار هستند حداقل چندین و چند دقیقه زمان لازم است
امری که برای اکسل به سادگی و در کسری از دقیقه امکان پذیر است. اما چه جوری ؟ در ادامه خواهید خواند .
اما چه روش هایی برای پیدا کردن داده های تکراری توی اکسل وجود دارد ؟
برای پیدا کردن داده های تکراری یا در اصطلاح همان Duplicate Values اکسل امکانات و قابلیت های مختلفی را در اختیار شما قرار می دهد .
برای اینکه بهتر با این قابلیت ها و امکانات آشنا شویم یک دسته بندی را روی آنها اعمال می کنیم .
در دیاگرام زیر می توانید بهتر این دسته بندی را مشاهده کنید .
پس متوجه شدید که سه تا روش مختلف برای یافتن اطلاعات تکراری در اکسل وجود دارد .
از اینجا به بعد کاری که می خواهیم بکنیم این است که به سراغ تک تک این روش ها رفته و با زیر و بم کار با هر کدام از آنها آشنا شویم .
پس در ادامه من را همراهی کنید تا با هر کدام از آنها بهتر و بیشتر آشنا شویم .
روش اول : پیدا کردن سلول های تکراری با استفاده از فرمت شرطی
اول از امکانات موجود در خود نرم افزار اکسل شروع می کنیم.
توی اکسل یک قابلیتی داریم به اسم Conditional Formatting یا در اصطلاح فرمت های شرطی .
با کاندیشنال فرمتینگ کارهای خیلی زیادی می شود در داخل اکسل انجام داد . که یکی از این کارها می تواند پیدا کردن داده های تکراری در اکسل باشد .
اما چطوری می شود این کار را کرد ؟
با استفاده از فرمت های شرطی در اکسل کارهای زیادی می شود انجام داد که اتفاقا یکی از همین کارها مربوط به پیدا کردن مقادیر تکراری در اکسل است .
برای انجام این کار مراحل زیر را دنبال نمایید.
اول محدوده سلول های مورد نظر خودتان را که می خواهید در آنها دنبال داده های تکراری بگیرید را به حالت انتخاب در بیاورید .
بعد از سربرگ home گزینه Conditional Formatting را انتخاب نمایید
از زیر مجموعه های مربوط به آن، Highlight Cell Rule را انتخاب کنید.
از بین گزینه هایی که برای شما به نمایش در می آید گزینه Duplicate Value را انتخاب کنید.
با انجام این مراحل کادری به نام Duplicate Value برای شما باز خواهد شد
در این کادر دو تا لیست کشویی وجود دارد که آیتم های مختلفی را می توانید انتخاب نمایید
در اینجا به بررسی آیتم های موجود در هر کدام از این دو کمبو باکس می پردازیم.
درکمبو باکس سمت چپ دو گزینه وجود دارد.
گزینه اول :Duplicate
با انتخاب این گزینه اکسل تمامی آیتم هایی را که به صورت تکرار هستند برای شما هایلایت خواهد کرد.
گزینه دوم :Unique
عملکرد این گزینه دقیقاً برعکس گزینه قبلی است به گونه ای که آیتم هایی را برای شما پیدا می کند که حالت غیر تکراری دارند.
پس ما در کمبو باکس اول تعیین می کنیم که آیا به دنبال اطلاعات تکراری هستیم یا مواردی که غیر
تکراری هستند.
ما اینجا دنبال پیدا کردن سلول هایی هستیم که دارای اطلاعات تکراری هستند پس گزینه مورد نظر را انتخاب می کنیم و کار را ادامه می دهیم
یک سری تنظیمات در کمبو باکس سمت راست وجود دارد که در حقیقت به اکسل می گوییم که اطلاعات تکراری را به چه صورت برای ما از بقیه اطلاعات متمایز کند .
لیست و عملکرد هر کدام از این تنظیمات به قرار زیر خواهد بود.
گزینه Light Red fill with Dark Red Text
انتخاب این گزینه موجب می شود رنگ پس زمینه سلول قرمز کم رنگ و رنگ متن مربوط به آن قرمز پررنگ شود.
گزینه Light Yellow fill with Dark Yellow Text
انتخاب این گزینه موجب می شود رنگ پس زمینه سلول زرد کم رنگ و رنگ متن مربوط به آن زرد پررنگ شود.
گزینه Light Green fill with Dark Green Text
انتخاب این گزینه موجب می شود رنگ پس زمینه سلول سبز کم رنگ و رنگ متن مربوط به آن سبز پررنگ شود.
گزینه Light Fill
انتخاب این گزینه موجب می شود رنگ پس زمینه سلول قرمز کم رنگ شود .
گزینه Red Text
انتخاب این گزینه موجب می شود رنگ متن سلول قرمز رنگ شود .
گزینه Red Border
انتخاب این گزینه موجب می شود رنگ کادر دور تا دور سلول قرمز رنگ شود .
گزینه Costum format
با انتخاب این گزینه می توانید فرمت های مورد نظر خود را در رنگ نوشته ، رنگ سلول و … را بنا بر سلیقه خود تعریف نمایید .
برای مشاهده نحوه عملکرد گزینه های معرفی شده در بالا فقط کافیست که محدوده اطلاعات مورد نظر خود
را به حالت انتخاب در آورید
حالا گزینه مربوط بهDuplicate Value را انتخاب نمایید.
به محض باز شدن کد مربوطه شما ملاحظه خواهید نمود دیتا هایی که به صورت تکراری هستند از بقیه جدول شما متمایز شده اند
برای مشاهده عملکرد هر کدام از گزینه های بالا می توانید آنها را انتخاب نموده عملکرد آنها را مشاهده نمایید.
روش دوم : استخراج داده های تکراری با استفاده از تابع
در مثال قبلی اکسل سلولهایی را که دارای مقادیر تکراری بودند را برای ما پیدا و فقط خود آن سلول ها را برای ما متمایز می کرد.
اما در این ترفند برای پیدا کردن داده های داپلیکیت در اکسل می خواهیم کاری کنیم که اگر یک سلول دارای مقادیر تکراری باشد کل ردیف مربوط به آن سلول برای ما متمایز شود.
برای این منظور می توانید از گزینه New Rule استفاده کنید.
مراحل انجام این کار به قرار زیر است:
ابتدا جدول اطلاعات زیر را در نظر بگیرید
– ابتدا کل محدوده اطلاعات مورد نظر خود را به حالت انتخاب در آورید (از A2 تا C5)
– گزینه موسوم به Conditional Formatting را باز نمایید.
– گزینه New Rule را انتخاب کنید
بعد از انجام این کار پنجره ای تحت عنوان New Formatting Rule برای شما باز خواهد شد
– در پنجره مذکور بر روی آیتم مشخص شده در تصویر زیر کلیک نموده تا انتخاب گردد
سپس در کادر که برای شما به نمایش در می آید فرمول زیر را وارد کنید.
COUNTIF($A$2:$C$5;$A2)>1=
– در مرحله بعد بر روی دکمهFormat کلیک نمایید در پنجره ای که برای شما باز می شود بسته به نیاز خود می توانید فرمتی را به روی سلولهای مورد نظر خود تعریف کنید.
ما در اینجا تعیین نموده ایم که رنگ زمینه سلول های مورد نظر به رنگ انتخابی ما تغییر پیدا کند.
کافیست تمامی پنجره هایی که برای شما باز شده اند را تایید نماید تا به محیط اکسل برگردید
ملاحظه خواهید کرد که دو ردیف از سلول ها که دارای اطلاعات تکراری و یکسان هستند برای شما به صورت هایلایت درآمده اند.
تمامی مواردی که در بالا به آن پرداخته شد وقتی کاربرد دارد که لیست اطلاعات شما از قبل تکمیل شده باشد .
اما گاهی نیاز پیدا می کنید که تنظیماتی را در اکسل انجام دهید که از همان بدو ورود اطلاعات مانع از درج اطلاعات بصورت تکراری در اکسل شوید .
اگر می خواهید بدانید که چگونه این امر در اکسل انجام پذیر است می توانید از مطلب موجود در لینک زیر استفاده کنید .
مطلب مرتبط : ممانعت از ورود داده تکراری در اکسل .
روش سوم : یافتن داده های تکراری به کمک کدهای VBA
اول ببینیم که می خواهیم چه کاری انجام دهیم :
- ابتدا یک دکمه را درون شیت مورد نظر خود که حاوی اطلاعات مورد نظر تان است و می خواهیم مقادیر تکراری را در آن پیدا کنیم قرار می دهیم .
- در قدم بعدی یک روال حاوی کدهای VBA را ایجاد می کنیم که قرار است کار پیدا کردن مقادیر تکراری را برای ما بصورت خودکار انجام دهد .
Sub MyDuplicate()
Dim myCell As Range
Dim myrange As Range
Set myrange = Range("A2:C20")
For Each myCell In myrange
If WorksheetFunction.CountIf(myrange, myCell.Value) > 1 Then
myCell.Interior.Color = RGB(125, 85, 21)
Else
If WorksheetFunction.CountIf(myrange, myCell.Value) = 1 Then
myCell.Interior.Color = RGB(255, 255, 255)
End If
End If
Next
End Sub
در گام آخر باید یک دکمه را درون شیت خود قرار دهیم و کدهای بالا را به آن اختصاص دهیم .
اینکه چگونه می توانیم یک دکمه را به محیط اکسل اضافه کنیم را قبلا در آموزش هایی که با این دکمه ها سر و کار داشتیم آموزش داده ایم برای نمونه می توانید به آدرس زیر مراجعه کنید و مشابه همان کار برای اضافه کردن دکمه انجام دهید .
مطلب مرتبط : افزودن متن مورد نظر به سلول ها در اکسل
بعد از اینکه دکمه خود را ایجاد کردید بر روی آن دابل کلیک نمایید
با این کار وارد محیط کد نویسی دکمه مربوطه می شوید .
اکنون در روال On-Click فقط نام روالی را که در مرحله قبل ذخیره کردیم درج کنید .
بعد از انجام تمامی این مراحل می توانید به محیط کاری اکسل برگردید .
اگر بر روی دکمه مورد نظر کلیک کنید نتیجه کار را همانند تصویر زیر می بینید .
در تصویر بالا کاملا مشخص است که فقط اسامی که در یک ردیف تکرار شده اند برای ما مشخص می شوند .
اما اگر یک ردیف خالی از اسامی تکراری باشد بطور خودکار رنگ پس زمینه آن به حالت قبل بر می گردد .
آموزش مربوط به نحوه استفاده از این کدها را در ویدئوی همین بخش می توانید مشاهده کنید .
به انتهای آموزش مربوط به یافتن داده های تکراری در اکسل رسیدیم .
از اینکه تا انتهای این آموزش من را همراهی کردید از شما سپاسگذارم .
خوشحال می شوم اگر شما هم پیشنهادی در مورد بهبود کیفیت آموزش مربوط به یافتن داده های تکراری در اکسل دارید با من در میان بگذارید .
امیدوارم که باز هم شما را در سافت پلاس ببینم .
مطالب زیر را حتما مطالعه کنید
Xlookup در اکسل- تابعی با کلی قابلیت های کاربردی
تابع Hlookup در اکسل💥نکات و ترفندهای کار با آن +ویدئو
تابع فیلتر در اکسل 💥به همراه یک مثال کاربردی
تابع سفارشی در اکسل ؛ چی هست؟ چطور آن را ایجاد کنیم ؟
تابع countif در اکسل💚راهنمای کامل کار با آن
زوم کردن در سلول های اکسل + ۳ روش مختلف
10 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام علیکم: من می خواهم درستون اعدادجستجو کند واگرموردی یافت درستن سمت چپ مثلا:عددیک رابزنداگرراهنمایی بفرماوید متشکرم
با سلام
اگر بخواهیم لیستی را که به اسم ایتم های مختلف داریم (سرتیتر مختلف) و بخواهیم داخل یک جدول مقادیر تکراری رو لیست کنه و با اسم آیتم های انها داخل یک سلول بیاره از چه کدی و یا از چه قسمتی باید استفاده کنیم
الگوی تیک صعودی… گارد صعودی آماده رشد… از صف فروش به صف خرید… سهم رانتی…
آسیاتک آپ وکار مداران
ثفارس اعتلا سصوفی آپ
آپ بزاگرس آسیاتک ثفارس
مثلا در جدول بالا (مربوط به بورس هست): ایتم هایی تکراری رو جداگانه در یک سلول با سرتیترش بیاره
اگر کسی می تونه کمک کنه به ادرس ایمیل من جوابتونو ارسال کنین
pouyacafe@yahoo.com
سلام من وقتی از روش اول استفاده میکنم مثلا ۹۰۰٫۰۰۰ را با ۳۹۰۰٫۰۰۰ رو رنگی میکنه یعنی ۹۰۰٫۰۰۰ این ها رو باهم ارتباط میده ولی در اصل این ها باهم فرق دارن چیکارکنم که اینجوری نشون نده لطفا راهنمایی کنید
سلام. احتمالا داده های مربوط به ستون مورد نظر بصورت text معرفی شده است نه number. در اینصورت میتوان از روش زیر استفاده کرد:
۱٫ در صورت امکان داده ها بصورت عدد تعریف شود
۲٫ در دستور شرط برای رنگ بندی فرمول نوشته شود A1=900000=
امیدوارم مفید واقع شود
خیلی عالی بود.
برای کارهای ما که نیاز به تعداد بالایی لیست قطعات و پروژه ها هست نیاز بود تا موارد تکراری حذف بشه ولی چون به صورت عادی نمی شد با این روش خیلی راحت کارم راه افتاد.
سلام خوشحالم که براتون کاربردی و مفید واقع شده
سلام و درود. ممنونم از مقاله جامعه و خوبی که به اشتراک گذاشتین. کمک بزری برای من بود.
سلام
خیلی ممنونم.
استفاده کردم.
به هنگامی که مقادیر تکراری را پیدا کریم موقعی که بخواهیم رنگ سطر و ستون به حالت اولیه بازگردد و از حالت رنگی خارج شود چه باید کرد؟
برای این کار می شود دو تا دکمه طراحی کرد برای دکمه اول می شود کدهای مربوط به هایلایت کردن و برای دکمه دوم کدهای مربوط به برگشت به رنگ اولیه را قرار داد