شناسایی و حذف داده‌های تکراری در اکسل

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

آموزش تابع MATCH در اکسل

با استفاده از تابع MATCH در اکسل، می‌توانیم جایگاه یک مقدار را در یک محدوده مشخص تعیین کنیم. ساختار(Syntax) این تابع بصورت زیر است:

=MATCH(lookup_value, lookup_array, [match_type])

1	=MATCH(lookup_value, lookup_array, [match_type])

توضیحات ساختار(Syntax) تابع MATCH به شرح زیر می‌باشد:

آرگومان lookup_value: مقداری که قرار است مورد جستجو قرار گیرد.
آرگومان lookup_array: آرگومان lookup_value در این محدوده جستجو می‌شود.
آرگومان math_type: نوع مطابقت را بررسی می‌کند که از سه مقدار عددی -1، 0 و 1 پشتیبانی می‌کند. از آنجاییکه ما در جستجوها عینا به دنبال تعیین جایگاه عامل جستجو یعنی مقدار lookup_value هستیم، این بخش از تابع را روی عدد 0 تنظیم می‌کنیم.

به منظور درک بهتر به مثالی که در ادامه آورده شده است دقت نمایید:

با توجه به تصویر فوق و نتیجه تابع MATCH، میوه oranges در جایگاه دوم، میوه apples در جایگاه سوم و میوه pears درجایگاه چهارم محدوده A2:A5 قرار گرفته است. تصویر زیر نیز مصداق این ادعا است:

حال فرض کنید، میوه oranges دو مرتبه تکرار شده باشد:

برخورد تابع MATCH با داده‌های تکراری در اکسل

نکته‌ای که تابع MATCH در این جستجو ملاک قرار می‌دهد، تعیین جایگاه مقدار اول جستجو خواهد بود! نه مقداری که پس از آن درج گردیده است. جهت درک بیشتر می‌توانید به تصویر زیر توجه کنید:

نکته وجود داده تکراری در تابع MATCH اکسل

در نظر گرفتن همین نکته برای این مقاله آموزشی کفایت می‌کند. حال اگر قصد کسب اطلاعات بیشتر پیرامون تابع MATCH در اکسل را دارید پیشنهاد می‌کنم مقاله ترکیب توابع MATCH و INDEX را مطالعه نمایید.

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

به جدولی از محصولات میوه که در ادامه آورده شده است، دقت کنید:

در جدول فوق محصولات Bananas و Oranges تکراری هستند زیرا این میوه‌ها دو مرتبه آورده شده اند. در تصویر زیر نیز این اختلاف به خوبی قابل مشاهده است:

حال ما قصد داریم این داده‌های تکراری را با استفاده از ماکرونویسی در اکسل حذف نماییم. در ابتدای این بخش شما ماکروی کامل شناسایی و حذف سلول‌های تکراری را مشاهده خواهید نمود که در ادامه مقاله به توضیح هریک از بخش‌های این ماکرو خواهیم نمود (دانلود کدهای ماکرو):

Sub FindDuplicates()
    Dim lastRow As Long
    Dim i As Long
    Dim MatchFound As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastRow
        MatchFound = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)

        If i <> MatchFound Then
                Cells(i, 1).ClearContents
                Cells(i, 2).ClearContents
        End If
    Next
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Sub FindDuplicates()

Dim lastRow As Long

Dim i As Long

Dim MatchFound As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow

MatchFound = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)

If i <> MatchFound Then

Cells(i, 1).ClearContents

Cells(i, 2).ClearContents

End If

به منظور استفاده از ماکروی فوق آن را ابتدا کپی(Ctrl + C) و سپس با زدن کلیدهای ترکیبی Alt + F11 همانند تصویر زیر وارد محیط VBE اکسل شوید و از سربرگ Insert اقدام به ایجاد یک ماژول(Module) نمایید:

در پایان نیز ماکروی کپی شده را به محتوای ماژول الحاق (CTRL + V) و برای اجرای آن از کلیدمیانبر F5 استفاده نمایید:

تشریح ماکروی شناسایی و حذف داده‌های تکراری در اکسل

در گام نخست می‌بایست آخرین سلول پر را جهت بررسی یکایک داده‌های موجود در این جدول پیدا کنیم، از این رو خواهیم داشت:

در ماکروی فوق، شماره سطر آخرین سلول پر از ستون A در متغیر LastRow قرار خواهد گرفت. حال نیاز به یک حلقه داریم که از اولین سلول تا آخرین سلول پر از ستون A را مورد بررسی قرار دهد (آموزش حلقه For i در ماکرونویسی اکسل):

در ادامه با استفاده از تابع MATCH نیاز بررسی جایگاه هریک از سلول‌های ستون A طی هرگردش حلقه For خواهیم داشت:

نحوه استفاده از تابع MATCH در ماکرونویسی اکسل

حال اگر جایگاه سلولی با ترتیب شمارش گردش حلقه For هم‌خوانی نداشت، عملا تکراری محسوب شده و می‌بایست حذف گردد:

به منظور درک بهتر اجازه بدین جایگاه هریک از سلول‌های ستون A را با استفاده از تابع MATCH استخراج کنیم:

با تعمیم دادن فرمول فوق خواهیم داشت:

کاملا طبیعی است که جایگاه هریک از سلول‌های غیرتکراری ستون A به ترتیب 1، 2، 3 و … باشد. حال اگر داده‌ای تکراری در این فی مابین قرار گیرد، طبق صحبت‌هایی که در ابتدا سرفصل شد، تابع MATCH جایگاه اولین مقدار آن را برمی‌گرداند! به همین خاطر یک اختلال در ترتیب شمارش ایجاد می‌گردد. دقت کنید:

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

با اجرا کردن ماکروی فوق خروجی نهایی ما بصورت زیر خواهد بود:

نمایش خروجی نهایی شناسایی داده های تکراری

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