حذف موارد تکراری در یک سلول اکسل؛ دو روش کاربردی برای راحتی شما
تو کار با اکسل، حذف موارد تکراری یه کار خیلی رایجه. معمولا ما سراغ حذف موارد تکراری از یه بازه سلولی میریم، ولی خب، شایدم یه وقتایی بخوایم همین کار رو فقط واسه یه سلول خاص انجام بدیم.
مثلا تو مثال پایین، یه لیستی داریم که تو هر سلولش کلمه های تکراری نوشته شده. حالا میخوایم این کلمه های تکراری رو پاک کنیم و فقط هموناییکه یه بار اومدن رو نگه داریم.
اگه از نسخه جدید اکسل که تابعهای باحال مثل UNIQUE و TEXTSPLIT داره استفاده میکنی، خیلی راحت میتونی این کار رو با یه فرمول ساده انجام بدی. ولی اگه این توابع رو تو اکسلت نداری، جای نگرانی نیست! تو این مقاله یه روش با VBA برات توضیح میدم.
حالا بریم سراغ این دو تا روش.
فرمول حذف موارد تکراری در یک سلول
تو Excel یه عالمه تابع باحال جدید اومده که کار آدم رو خیلی راحت می کنه. اگه از اکسل تو آفیس ۳۶۵ استفاده می کنی، میتونی از این تابع های جدید برای پاک کردن اطلاعات تکراری تو یه سلول استفاده کنی.
مثلا تو جدول زیر، می خوام اسم های منطقه، جنس محصول، یا اسم افراد تکراری رو که تو سلول های A2 تا A4 هستن پاک کنم.
تمام این آیتمها با یه کاما و یه فاصله از هم جدا شدن. نکته اینجاست که ما میتونیم از همین ویژگی برای جدا کردن تکتک آیتمها و بعد حذف موارد تکراری استفاده کنیم.
پایین یه فرمول کاربردی برای این کار نوشته شده:
این فرمول رو میشه به این شکل توضیح داد:
- TEXTJOIN: این تابع سلولهای مختلف رو با یه جداکننده (تو این مثال، کاما و فاصله) به هم وصل میکنه.
- ,: این قسمت، جداکنندهای هست که بین آیتمها قرار میگیره.
- TRUE: این گزینه باعث میشه که خروجی فرمول، یه رشتهی متنی باشه (اگه اینجا FALSE بذاریم، خروجی یه آرایه میشه).
- UNIQUE: این تابع مقادیر تکراری رو از لیست حذف میکنه.
- TEXTSPLIT: این تابع یه رشتهی متنی رو بر اساس یه جداکننده (تو این مثال، کاما و فاصله) به لیستی از زیرمجموعهها تقسیم میکنه.
- A2: این سلولی هست که حاوی لیست جدا شده با کاما و فاصله است.
این فرمول باحال یه ترفنده تو Excel به اسم TEXTSPLIT داره کار میکنه. با TEXTSPLIT میتونیم متن یه سلول رو بر اساس یه جداکننده خاص، به چند خط جدا تو همون ستون تقسیم کنیم. تو این مثال، جداکنندهمون یه کاما (،) به همراه یه فاصله است.
بعدش نتیجهی TEXTSPLIT رو میفرستیم سراغ یه فرمول دیگه به اسم UNIQUE. این فرمول لیست رو میگیره و فقط مقادیر تکراری رو ازش حذف میکنه و یه لیست تمیز بهمون تحویل میده.
حالا نوبت فرمول TEXTJOIN میرسه. این فرمول لیست تمیز رو برمیداره و دوباره با همون جداکنندهی قبلی (که تو مثال ما، کاما و فاصله بود) بهم میچسبونه و یه خروجی تمیز بهمون میده. تو خروجی نهایی، دیگه خبری از کپیهای اضافی نیست.
یه نکتهی مهم موقع استفاده از این فرمول اینه که حواستون باشه جداکنندهتون همیشه یه شکل باشه. مثلا تو مثال ما، همهی موارد با یه کاما و فاصله از هم جدا شده بودن.
اما اگه جداکنندهها قاطی باشن، یعنی تو بعضی جاها فاصله باشه و تو بعضی جاها نباشه، میتونین از فرمول پایین استفاده کنین. این فرمول از یه ترفندهی دیگه به اسم TRIM استفاده میکنه که فاصلههای اضافی اول و آخر متن رو پاک میکنه.
فرمول جدید این شکلی میشه:
این فرمول به حروف بزرگ و کوچک حساس نیست، یعنی کلماتی مثل “US” و “us” رو به عنوان یک کلمه در نظر میگیره و اونارو تکراری در نظر میگیره.
موارد تکراری رو با چند جداکننده حذف کن
اگه تو یه خونه (سلول) چندتا چیز مختلف داری که با چند مدل جدا کننده از هم جدا شدن، بازم میتونی از همون فرمولی که قبلا گفتیم استفاده کنی، فقط یه کوچولو باید تغییرش بدی.
پایین یه مجموعه داده (دیتاست) داریم که میخوایم مقادیر تکراری رو از داخل همون خونه (سلول) پاک کنیم.
تو این متن انگلیسی یه سری جداکنندههای مختلف مثل “کاما” (,)، “خط عمودی” (|), “خط تیره” (-) و “نقطه ویرگول” (;) به کار رفته.
این متن یه فرمول رو معرفی میکنه که میتونه از این جداکنندهها برای ترکیب کردن یه سری مقادیر استفاده کنه. فرمول به این صورته:
توضیح فرمول
- TEXTJOIN: این تابع یه سری مقادیر رو با یه جداکنندهی خاص به هم وصل میکنه.
- “, “: این جداکنندهی مورد استفاده تو این فرموله. میتونی هر جداکنندهی دیگهای که دوست داری بذاری.
- TRUE: این گزینه باعث میشه که اگه یه مقدار تکراری تو لیست باشه، فقط یه بار تو خروجی نمایش داده بشه.
- UNIQUE: این تابع مقادیر تکراری تو لیست رو حذف میکنه.
- TRIM: این تابع فضای خالی قبل و بعد از هر مقدار تو لیست رو حذف میکنه.
- TEXTSPLIT: این تابع یه متن رو با یه سری جداکنندههای مختلف به یه لیست از مقادیر تبدیل میکنه.
- A2: این سلوله که شامل متنیه که میخوایم جداکنندهها رو توش اعمال کنیم.
- {“,”,”-“,”|”,”;”}: این لیست شامل تمام جداکنندههایی هست که میخوایم تو فرمول استفاده بشه.
این فرمول توی سلولها دنبال چهار علامت خاص میگرده. وقتی این علامتها رو پیدا میکنه، با استفاده از اونها محتوای سلول رو به قسمتهای کوچیکتر تقسیم میکنه.
عملکرد سفارشی VBA برای حذف موارد تکراری در یک سلول
اگه به این قابلیتهای جدید اکسل دسترسی ندارین، میتونین یه تابع دلخواه خودتون با استفاده از VBA بسازین. به این توابع، توابع تعریفشده توسط کاربر (UDF) گفته میشه.
پایین یه کد VBA برای ساخت این تابع رو میبینین:
Function DeDupCells(cellRef As Range, delimiter As String) As String
Dim cellValue As String
Dim valueArray As Variant
Dim uniqueValues As Collection
Dim result As String
Dim i As Integer
‘ Get the cell value
cellValue = cellRef.Value
‘ Split the cell value into an array
valueArray = Split(cellValue, delimiter)
‘ Initialize the collection for unique values
Set uniqueValues = New Collection
‘ Loop through the array and add unique values to the collection
On Error Resume Next
For i = LBound(valueArray) To UBound(valueArray)
uniqueValues.Add Trim(valueArray(i)), CStr(Trim(valueArray(i)))
Next i
On Error GoTo 0
‘ Construct the result string from the unique values collection
For i = 1 To uniqueValues.Count
result = result & uniqueValues(i) & delimiter
Next i
‘ Remove the trailing delimiter
If Len(result) > 0 Then
result = Left(result, Len(result) – Len(delimiter))
End If
‘ Output the result
DeDupCells = result
End Function
برای استفاده از این تابع VBA، باید کد VBA رو در یه ماژول تو ویرایشگر VB تو فایل اکسل خودت بذاری.
جای کد VBA رو کجا بذاریم؟
بذار با یه راه ساده بهت بگم چطوری از این کدهای سفارشی VBA تو یه صفحه اکسل استفاده کنی:
اول کلیدهای ترکیبی Alt + F11 رو با هم فشار بده تا وارد محیط ویرایش Visual Basic for Applications (یا همون ویرایشگر VBA) بشی. یه راه دیگه هم اینه که بری سراغ تب Developer و بعدش روی آیکون Visual Basic کلیک کنی. اینجوری ویرایشگر VBA باز میشه.
حالا که ویرایشگر VBA رو باز کردی، برو تو منوی بالا و روی Insert کلیک کن، بعدش هم گزینه Module رو انتخاب کن. با این کار یه ماژول جدید تو فایل اکسل درست میشه که میتونی کدهای تابع دلخواهت رو با VBA توش بنویسی.
تو یه پنجرهی جدید تو VBA، کد بالا رو کپی و پیست کن.
ویرایشگر ویژوال بیسیک رو ببند!
حالا بریم سراغ استفاده از تابع شخصیسازهمون تو صفحهی گستردهات (ورکشیت)
بعد از اینکه مراحل بالا رو انجام دادی، میتونی از این تابعی که خودت درست کردی، مثل هر تابع دیگهای تو خونههای صفحهگستردهات استفاده کنی.
مثلا من میتونم از فرمول زیر برای حذف موارد تکراری تو یه خونه استفاده کنم:
تو این مقاله، دوتا روش باحال یادتون دادم که چطوری با فرمول تو اکسل، اطلاعات تکراری رو از یه سلول پاک کنید. اگه از نسخههای جدید اکسل که امکانات بیشتری داره استفاده میکنین، مثل توابع منحصر به فرد (UNIQUE) و تقسیم متن (TEXTSPLIT)، میتونین از روش اول که با یه فرمول ترکیبی از این توابعه استفاده کنین.
اگه به این توابع دسترسی ندارین، میتونین از روش دوم استفاده کنین. تو این روش یادتون دادم که چطوری یه تابع دلخواه خودتون رو با VBA بسازین.
امیدوارم این مقاله به دردتون خورده باشه.
اگه نظری یا پیشنهادی دارین، حتما تو قسمت کامنتها بهم بگین.
__ تکنو دات مرجع اخبار تکنولوژی __