یافتن داده های تکراری در اکسل از طریق کدهای VBA
یافتن داده های تکراری در اکسل موضوع مطلبی بود که در مطلب مجزایی به آن پرداختیم .
در مطلب قبلی توانستیم با استفاده از امکاناتی که خود اکسل برای ما فراهم آورده بود مقادیر تکراری را پیدا کنیم .
بعد از پیدا کردن این مقادیر نیز تغییرات مورد نظر خود را در فرمت و شکل ظاهری داده های مربوطه ایجاد نمودیم .
اگر این مطلب را مطالعه نکرده اید توصیه می کنم که حتما از طریق لینک زیر آن را مطالعه کنید .
مطلب مرتبط : جست و جوی داده های تکراری در اکسل -قسمت اول
و اما امروز و در مطلب یافتن داده های تکراری در اکسل به دنبال چه هستیم .
امروز می خواهیم همان کاری را که با امکانات اکسل به انجام رساندیم این بار با استفاده از کدهای VBA انجام دهیم .
یعنی نتیجه و خروجی نهایی کار ما با در شکل و محتوای کلی تفاوتی با روش های قبلی نخواهد داشت .
ممکن است که این سوال برای شما پیش بیاید که وقتی قرار نیست نتیجه جدیدی حاصل شود پس چرا باید این روش را یاد بگیریم ?
یافتن داده های تکراری در اکسل از طریق کدهای VBA
ما این کار را در مورد یافتن اطلاعات تکراری در چهار سطح انجام می دهیم .
- هایلایت سلول حاوی اطلاعات تکراری در یک سطر
- هایلایت سلول حاوی اطلاعات تکراری در یک ستون
- هایلایت مقادیر تکراری در یک محدوده انتخابی
- هایلایت مقادیر تکراری در کل محدوده اطلاعات
اما قبل از هر چیز جدول اطلاعات مورد نظر خود را ایجاد می کنیم .
من برای این منظور از جدول اطلاعات زیر بهره خواهم برد .
هایلایت سلول حاوی اطلاعات تکراری در یک سطر
در این روش می خواهیم به گو نه ای عمل کنیم که اگر در یک ردیف از سلول ها مقداری تکراری پیدا شد اکسل سلول مورد نظر را بطور خودکار برای ما بصورت هایلایت درآورد.
در گام نخست می خواهیم از اکسل بخواهیم یک به یک ردیف ها را کنترل کند .
در حین این کار مقادیر سلول های هر ردیف را با هم مقایسه کند.
اگر در یک ردیف دو یا چند سلول دارای مقادیر تکراری بودند آنها را برای ما هایلایت نماید .
نکته مهم اینکه قرار است سلول های هر ردیف با هم مقایسه شوند و نه با کل ردیف های دیگر .
این به این معنی است که اگر یک سلول در ردیف اول دارای نام “آرش ” بود فقط در صورتی این نام برای ما بعنوان تکرار هایلایت می شود که سلول دیگری در همان ردیف دارای این نام باشد .
و اگر در سایر ردیف ها سلولی دارای این نام بود بعنوان تکرار شناخته نخواهد شد .
اکنون برویم به سراغ کدهای مورد نظر خودمان
شروع کد نویسی VBA
در اولین قدم نیاز به تعریف چند متغیر داریم تا بتوانیم داده هایی را در انها ذخیره کنیم .
برای این منظور داریم
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
بسیار خوب متغیر های مورد نظر را تعریف کردیم .
اکنون به سراغ مرحله بعد می رویم .
حال می خواهیم بدانیم که اطلاعات موجود در شیت ما دارای چند سطر و ستون است .
برای این منظور از کدهای زیر استفاده می کنیم .
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
در کدهای بالا متغیر my Row تعداد سطرها و متغیر myCol تعداد ستون ها را شمارش و در خود ذخیره می کننند .
بعد از انجام این کار به سراغ مرحله بعد می رویم .
در گام بعدی می خواهیم با استفاده از کدهای خود کنترل کنیم که چه سلول هایی در یک سطر دارای مقادیر تکراری هستند .
این کار به راحتی توسط کدهای فوق برای ما انجام می شود .
اما آیا باز هم می خواهید بیشتر و جزئی تر با این خطوط کد و معنا و مفهوم آنها آشنا شوید .
بسیار خوب اما ابتدا اجازه دهید همه این خطوط کد را در یک جا جمع کنیم .
برای این کار من آنها را داخل روالی به نام My- duplicate قرار می دهم
سپس آنها را در داخل یک ماژول در اکسل ذخیره می کنم .
بسیار خوب حالا اگر می خواهید یادگیری خود را در خصوص نحوه عملکرد این کدها بیشتر کنید ویدیوی زیر را نگاه کنید .
گام نهایی قرار دادن یک دکمه در شیت کاری .
آماده اید ادامه مطلب رو با هم پیگیری کنیم .
بسیار خوب پس پیش بسوی یادگیری یک مطلب جدید .
هایلایت سلول حاوی اطلاعات تکراری در یک ستون
در مرحله قبل ما سلول ها را بطور ردیفی مورد مقایسه قرار می دادیم .
فرض بر این بود که اگر در یک ردیف دو یا چند سلول تکراری بودند برای ما هایلایت شوند.
اکنون مشابه همین کار را می خواهیم برای ستون های خود اعمال کنیم .
مراحل انجام این کار دقیقا مشابه روش قبلی است .
تنها تفاوت در کدهایی است که باید از آنها استفاده کنیم .
این کدها نیز عینا مشابه کدهای قلبی هستند و فقط اندکی تغییرات در آنها اعمال می کنیم .
برای این کار از کدهای زیر استفاده می کنیم .
Sub Col_Duplicate()
'اول تعریف متغیرها
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
'شمارش تعداد سطرو ستون های دارای اطلاعات
myRow = Range(Cells(1, 1),Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
'جستجوی ستون ها برای مقادیر تکراری
For i = 1 To myRow
Set myRange = Range(Cells(1, i), Cells(myRow, i))
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
Next
End Sub
عملکرد این کدها و نحوه استفاده از آنها دقیقا مشابه کدهای قبلی خواهد بود .
یافتن مقادیر تکراری در یک محدوده انتخابی
در این روش دست کاربر را بازتر نموده و اختیار کامل را به وی می دهیم .
به گونه ای که از کاربر می خواهیم یک محدوده از اطلاعات را انتخاب نماید .
در عوض ما نیز کاری می کنیم که اکسل مقادیر تکراری را برای وی پیدا کند .
روش انجام این کار به قرار زیر است .
- ابتدا کدهای زیر را کپی نموده و در داخل یک ماژول ذخیره نمایید .
Sub Duplicatebyselect()
Dim myRange As Range
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
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 فرم خود قرار دهید .
برای مشاهده نتیجه کار ابتدا محدوده مورد نظر خود را انتخاب نموده و بر روی دکمه مربوطه کلیک کنید .
نتیجه را در زیر می توانید مشاهده نمایید .
آنچه که تا به اینجا به آن پرداختیم شامل موارد زیر است :
- یافتن مقادیر تکراری در یک سطر
- یافتن مقادیر تکراری در یک ستون
- یافتن مقادیر تکراری در یک محدوده انتخابی .
آخرین آیتمی که به آن می پردازیم مربوط به پیدا کردن مقادیر تکراری در کل اطلاعات است .
یافتن مقادیر تکراری در کل محدوده
برای این منظور هم می توانیم از کدهای زیر استفاده کنیم .
Sub Duplicatein All()
Dim myRange As Range
Dim i As Integer
Dim j As Integer
Dim myCell As Range
set myRange = Range("نام محدوده مورد نظر ")S
for Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next
End Sub
برای استفاده از این کدها فقط باید نام محدوده اطلاعات خود را در خط شماره ۶ از این کدها وارد نمایید .
در اینجا بحث در خصوص نحوه یافتن داده های تکراری در اکسل به پایان می رسد .
امیدورام که برای شما مثمر ثمر بوده باشد .
خوشحال می شویم با ارائه نظرات خود در خصوص این مطلب و در خصوص تمامی مطالب این سایت ما را در بهبود این سایت یاری دهید .
مطالب زیر را حتما مطالعه کنید
تابع سفارشی در اکسل ؛ چی هست؟ چطور آن را ایجاد کنیم ؟
تابع countif در اکسل💚راهنمای کامل کار با آن
زوم کردن در سلول های اکسل + ۳ روش مختلف
چند فرمت سفارشی برای اعداد که هر حسابداری باید بداند.
من چطور یک نمودار دایره ای ساده را بصورت حرفه ای نمایش میدهم ؟
تابع Column و Row در اکسل | دو تابع ساده اما کاربردی
6 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام وقت بخیر
ضمن تشکر و قدردانی برای مطالب وب سایت
لطفا در صورت امکان کد VBA برای هایلایت کردن اولین تکرار در یک لیست اکسل
بصورتی که اگر در لیست چندین تکرار وجود داشته باشه از هر مورد فقط یکی هایلایت بشه
با تشکر از شما
به زودی اموزشی در همین زمینه در سایت قرار خواهد گرفت
با تشکر
امکانش هست فیلمی ازش بزارید
سلام در اولین بروز رسانی ویدئوی آموزشی تهیه و در سایت قرار می گیرد
سلام و تشکر از آموزش بسیار خوبتان
لطفا در صورت امکان کد های جلوگیری از ورود اطلاعات تکراری را هم بنویسید در روش های دیگر وقتی در سلول تایپ می کنیم می توان پیام خطا داد اما به چسباندن داده ها پیام خطا نمی دهد ممنون می شوم راهنمایی بفرمائید
با تشکر
سلام حتما راجع به این مورد در آپدیت های بعدی صحبت خواهیم کرد