حذف کلمات تکراری در اکسل – از صفر تا صد

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

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

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

تعریف تابع جدید برای حذف کلمات تکراری در اکسل

پس از شناسایی داده های تکراری در اکسل، برای حذف آن‌ها می‌توانید کد زیر به نام «RemoveDupeWords» را بنویسید و تابع جدیدی تعریف کنید.

1Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String
2  Dim dictionary As Object
3  Dim x, part
4
5  Set dictionary = CreateObject("Scripting.Dictionary")
6  dictionary.CompareMode = vbTextCompare
7  For Each x In Split(text, delimiter)
8    part = Trim(x)
9    If part <> "" And Not dictionary.Exists(part) Then
10      dictionary.Add part, Nothing
11    End If
12  Next
13
14  If dictionary.Count > 0 Then
15    RemoveDupeWords = Join(dictionary.keys, delimiter)
16  Else
17    RemoveDupeWords = ""
18  End If
19
20  Set dictionary = Nothing
21End Function

آموزش افزودن کد تابع جدید به اکسل

برای وارد کردن کد بالا و ساخت تابع در اکسل، ابتدا دکمه‌های Alt+F11   را روی کیبورد فشار دهید تا پنجره مربوط به کدنویسی VBA باز شود. سپس در نوار سمت چپ روی گزینه «ThisWorkbook» راست‌کلیک کرده و گزینه‌های «Insert > Module» را به‌ترتیب انتخاب کنید. حالا کد موردنظر را در پنجره ماژول بنویسید.

در نهایت پنجره را ببندید و به صفحه اصلی اکسل برگردید.

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

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

1RemoveDupeWords(text, [delimiter])

آرگومان‌های این تابع به شرح زیر هستند‌:

• Text   (ضروری): این آرگومان، رشته یا سلولی است که می‌خواهید کلمات تکراری درون آن را خذف کنید.
• Delimiter   (اختیاری): این آرگومان برای تعیین نوع جداکننده متن تکراری به کار می‌رود. اگر آن را ننویسید، فاصله را به عنوان جداکننده در نظر می‌گیرد.

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

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

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

برای انجام این کار، درون کادر فرمول، علامت «مساوی» (=) را قرار داده و سپس نام تابع را بنویسید. خواهید دید که این تابع در لیست پیشنهادهای اکسل نشان داده می‌شود. روی آن دوبار کلیک کنید تا به طور کامل نوشته شود. بعد آرگومان‌ها را در جای خود قرار دهید، پرانتز را ببندید و دکمه «Enter» را بزنید.

به عنوان نمونه، برای حذف کردن کلمات تکراری در سلول «A2» که با علامت «خط فاصله» (-) از هم جدا شده‌اند، فرمول را به شکل زیر می‌نویسیم.

1=RemoveDupeWords(A2,"-")

همان‌طور که در تصویر زیر می‌بینید، تمام کلمات تکراری حذف شده‌اند.

توجه داشته باشید که اگر برخی از کلمات تکراری با علامت دیگری مانند «مثبت» (+) از هم جدا شده باشند، حذف نخواهند شد. مثلا اگر همین فرمول را برای سلول‌های دیگری بنویسیم، نتیجه تغییر می‌کند.

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

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

نحوه استفاده از ماکروی VBA برای حذف کلمات تکراری در چند سلول اکسل به طور همزمان

یک روش برای حذف داده‌های تکراری درون چندین سلول به طور همزمان این است که تابع RemoveDupeWords را از داخل یک ماکروی VBA فراخوانی کنید. در این صورت، پارامتر Delimiter به صورت «هارد کد» (Hard Code) تبدیل می‌شود و امکان تغییر آن وجود ندارد. به همین دلیل هر بار که جداکننده عوض می‌شود باید کد ماکرو را نیز به صورت دستی تغییر دهید.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

به جای آن می‌توانید کدهای مختلفی برای هریک از جداکننده‌های رایج مثل فاصله، ویرگول یا «کاما» (,) بنویسید و نام‌های مختلف اما معناداری مانند «RemoveDupesDelimSpace» که برای جداکننده فاصله است، برای آن‌ها انتخاب کنید.

نهایتا کد ماکرو شبیه به نمونه زیر خواهد شد.

1Public Sub RemoveDupeWords2()
2  Dim cell As Range
3  For Each cell In Application.Selection
4    cell.Value = RemoveDupeWords(cell.Value, " ")
5  Next
6End Sub

در کد بالا، جداکننده یک فاصله در نظر گرفته شده است. برای استفاده از سایر علائم کافی است آن‌ها را به جای عبارت " "   بنویسید. مثلا برای قرار دادن علامت ویرگول، این قسمت از کد را به شکل زیر می‌نویسیم.

1cell.Value = RemoveDupeWords(cell.Value, "، ")

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

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

برای شروع کار ابتدا کد موردنظر را در صفحه کدنویسی VBA وارد کنید. سپس محدوده سلولی موردنظر برای حذف کلمات تکراری را برگزینید. حالا دکمه‌های Alt+F8   را فشار دهید تا پنجره مربوط به ماکرو باز شود. سپس از بین لیست موجود، ماکروی «RemoveDupeWords2» را برگزینید و دکمه «Run» را فشار دهید.

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

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

آموزش حذف حروف اضافه در یک سلول اکسل

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

روش ساخت تابع جدید برای حذف حروف تکراری در سلول اکسل

برای حذف کردن حروف تکراری موجود در سلول اکسل و نگه داشتن یکی از آن‌ها، می‌توانید از کد زیر برای نوشتن تابعی به نام «RemoveDuplicateCharacters» کمک بگیرید.

1Function RemoveDuplicateCharacters(inputText As String) As String
2    Dim uniqueChars As String
3    Dim i As Integer
4
5    uniqueChars = ""
6    For i = 1 To Len(inputText)
7        If InStr(uniqueChars, Mid(inputText, i, 1)) = 0 Then
8            uniqueChars = uniqueChars & Mid(inputText, i, 1)
9        End If
10    Next i
11
12    RemoveDuplicateCharacters = uniqueChars
13End Function

برای انجام این کار نیز کافی است دکمه‌های Alt+F11 را بزنید، در محیط VBA، ماژول جدیدی بسازید و کد را در آن جایگذاری کنید.

فرمول تابع RemoveDupeChars

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

1=RemoveDuplicateCharacters(text)

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

نحوه کار با تابع RemoveDupeChars

این تابع نیز درست مانند تابع RemoveDupeWords کار می‌کند و در ادامه با ذکر یک مثال بیشتر با نحوه عملکرد آن آشنا خواهیم شد. به عنوان نمونه برای حذف کردن حروف اضافه موجود در ستون A که از سلول A2 شروع می‌شوند باید فرمول مذکور را به شکل زیر به کار ببرید.

1=RemoveDuplicateCharacters(A2)

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

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

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

شما می‌توانید تابع RemoveDuplicateCharacters را نیز درست مانند تابع RemoveDupeWords از درون ماکرو فراخوانی کنید. کافی است کد زیر را مانند آموزش قسمت‌های قبلی در ماژول مربوطه قرار دهید.

1Public Sub RemoveDuplicateCharacters2()
2Dim cell As Range
3For Each cell In Application.Selection
4cell.Value = RemoveDuplicateCharacters(cell.Value)
5Next
6End Sub

از آن‌جایی که تابع سفارشی که در قسمت قبل ساختیم، از هیچ جداکننده‌ای استفاده نمی‌کند، امکان انجام تغییرات مشابه در این کد نیز وجود ندارد.

همچنین توجه داشته باشید برای اینکه ماکرو به درستی کار کند، کد تابع سفارشی و کد ماکرو باید درون یک ماژول نوشته شده باشند.

آموزش کار با ماکروی حذف حروف تکراری در سلول اکسل

پس از وارد کردن کد، برای اجرای ماکرو ابتدا محدوده سلولی موردنظر برای حذف حروف اضافه را انتخاب کنید و سپس دکمه‌های Alt+F8 را روی کیبورد بزنید تا کادر مربوط به ماکروها باز شود. در این مرحله ماکروی RemoveDuplicateCharacters2 را از لیست برگزینید و دکمه Run را بزنید.

استفاده از ترکیب تابع MID و Find برای حذف کلمات تکراری در اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

تابع MID برای استخراج زیرمجموعه‌ای از حروف یا اعداد از درون یک رشته متنی کاربرد دارد. تابع Find نیز برای یافتن موقعیت حروف درون یک رشته به کار می‌رود. با نوشتن فرمولی ترکیبی مانند نمونه زیر می‌توانید کلمات اضافی را از سلول‌های اکسل حذف کنید.

1=MID(A1&" ",1,FIND(" ",A1&" "))&
2 MID(SUBSTITUTE(A1&" ",MID(A1&" ",1,FIND(" ",A1&" ")),"")&" ",1,
3 FIND(" ",SUBSTITUTE(A1&" ",MID(A1&" ",1,FIND(" ",A1&" "))&" ","")))

نحوه کارکرد فرمول ترکیبی توابع Find و MID

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

1MID(A1&" ",1,FIND(" ",A1&" ")) 

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

1A1&" " 

• این بخش فاصله‌ای اضافه به انتهای کلمه موجود در سلول اضافه می‌کند تا اطمینان حاصل شود که آخرین کلمه به درستی شناسایی شده است (در صورتی که هیچ فاصله‌ای در انتهای متن وجود نداشته باشد).

1FIND(" ",A1&" ") 

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

1MID(A1&" ",1,FIND(" ",A1&" ")) 

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

1MID(SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" ")),"")&" ",1, FIND(" ",SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" "))&" ","")))

• این قسمت برای استخراج کلمه دوم به کار می‌رود و عملکرد آن به شرح زیر است:

1MID(A1&" ",1,FIND(" ",A1&" ")) 

• همان‌طور که در مرحله قبل گفتیم، اولین کلمه را پیدا می‌کند.

1SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" ")),"") 

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

1SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" ")),"")&" " 

• این کد فاصله‌ای اضافه به انتهای متن اصلاح شده می‌افزاید تا اطمینان حاصل شود که کلمه آخر به درستی شناسایی شده است.

1FIND(" ",SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" "))&" ","")) 

• موقعیت اولین فاصله را در متن اصلاح شده پیدا می‌کند (پس از حذف اولین کلمه). این موقعیت، انتهای دومین کلمه را مشخص می‌کند.

1MID(SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" ")),"")&" ",1, FIND(" ",SUBSTITUTE(A1&" ", MID(A1&" ",1,FIND(" ",A1&" "))&" ","")))

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

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

سخن پایانی

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

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