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

خبر خوب اینه که توی نسخه 2021 میتونید این تابع رو پیدا کنید.

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

تابع UNIQUE اکسل

تابع UNIQUE در اکسل مقادیر منحصر به فرد را از ک محدوده یا آرایه برمی گرداند. این تابع با همه نوع داده ای کار میکند: متن، اعداد، تاریخ، زمان و..

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

ساختار این تابع به شکل زیر است:

UNIQUE(array, [by_col], [exactly_once])

Array: اجباری- محدوده یا آرایه ای که قرار است از آن مقادیر منحصر به فرد برگردانده شود.

By_col: اختیاری- یک مقدار منطقی که نشان می دهد داده ها چگونه مقایسه شوند: true: داده ها را در یک ستون مقایسه می­کند. False یا خالی: داده ها را به صورت ردیفی مقایسه می کند.

Exactly-once: اختیاری- یک مقدار منطقی که مشخص می کند چه مقادیری منحصر به فرد در نظر گرفته شوند:

True: مقادیری را برمیگرداند دکه فقط یک بار اتفاق افتاده باشند.

False یا omitted (پیش فرض)- تمام مقادیر متفاوت را در یک محدوده یا آرایه برمیگرداند.

فرمول های UNIQUE مقدماتی اکسل

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

هدف این است که اسم های منحصر به فرد را در محدوده B2:B10 استخراج کنیم. برای این کار، فرمول زیر را در سلول D2 وارد میکنیم:

=UNIQUE(B2:B10)

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

هنگامی که کلید enter را میزنید، اکسل اولین نام پیدا شده را در سلول D2 وارد میکند و نام دیگر را در سلول های زیرین می ریزد. در نتیجه، شما تمام مقادیر منحصر به فرد را در یک ستون خواهید داشت:

در صورتی که داده های شما از ستون B2 تا I2 باشد، آرگومان دوم را روی true تنظیم کنید تا ستون ها با یکدیگر مقایسه شوند:

=UNIQUE(B2:I2,TRUE)

فرمول بالا را در سلول B4 بنویسید، enter را فشار دهید، و نتایج به صورت افقی در سلول های سمت راست وارد میشوند. بنابراین، مقادیر منحصر به فرد را در یک ردیف خواهید داشت:

تابع UNIQUE- نکات

این تابع یک تابع جدید است و باید مانند سایر توابع آرایه پویا با ویژگی های آن آشنا باشید:

• اگر این تابع نتیجه نهایی را برگرداند (یعنی این تابع به عنوان آرگومان تابع دیگری استفاده نشده باشد.) اکسل به صورت پویا محدوده ای به اندازه مناسب برای نشان دادن نتایج ایجاد می کند. فرمول فقط باید در یک سلول وارد شود. مهم است که شما به اندازه کافی سلول خالی در پایین یا سمت راست سلولی که در آن فرمول را وارد میکنید، داشته باشید؛ در غیر اینصورت خطای #SPILL رخ می­دهد.
• اگر داده ها تغییر کنند، نتایج به صورت خودکار به روز میشوند. با این حال اگر ورودی های جدید خارج از آرایه ارجاع داده شده وارد شوند، در فرمول وارد نخواهند شد، مگر اینکه مرجع آرایه را تغییر دهید. اگر می خواهید آرایه به صورت خودکار پاسخگوی تغییر اندازه دامنه داده ها باشد، محدوده را به یک جدول اکسل تبدیل کنید و از مراجع ساختار یافته استفاده کنید، یا یک دامنه با نام پویا ایجاد کنید.
• آرایه های پویا بین دو فایل مختلف اکسل زمانی درست کار میکنند که هر دو فایل اکسل باز باشند. اگر فایل منبع بسته باشد، و در فایل دیگر از فرمول unique استفاده کنید، خطای #REF را برمیگرداند.

چگونگی پیدا کردن مقادیر منحصر به فرد – مثال های فرمولی

مثال های زیر کاربردهای تابع unique در اکسل را نشان می­دهد. ایده اصلی استخراج مقادیر منحصر به فرد یا حذف مواردی که کپی هستند به ساده ترین شکل ممکن است. بریم با دیدن مثال ها این تابع رو بهتر یاد بگیریم.

استخراج مقادیری که فقط یک بار اتفاق افتاده اند

هدف استخراج مقادیری است که در یک محدوده مشخص یک بار اتفاق افتاده باشند، پس آرگومان سوم تابع unique رو روی true تنظیم کن.

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

=UNIQUE(B2:B10,,TRUE)

B2:B10 محدوده منبع است و آرگومان دوم (by_col) روی flase تنظیم شده است (یا میتوانید این آرگومان را حذف کنید، کاری که ما کردیم) زیرا داده ها در ردیف ها سازماندهی شده اند.

استخراج مقادیری که بیش از یک بار اتفاق افتاده اند.

اگر برعکس هدف قبلی را میخواهید دنبال کنید، یعنی به دنبال مقادیری هستید که بیش از یک بار در یک محدوده مشخص ظاهر شده اند، از تابع unique در اکسل به همراه توابع FILTER و COUNTIF استفاده کنید:

UNIQUE(FILTER(range, COUNTIF(range, range)>1))

به عنوان مثال برای استخراج نام های مختلف که در محدوده B2:B10 بیش از یک بار وجود دارند، می­توانید از فرمول زیر استفاده کنید:

=UNIQUE(FILTER(B2:B10, COUNTIF(B2:B10, B2:B10)>1))

نحوه کار این فرمول:

در قلب فرمول، تابع FILTER ورودی های تکراری را بر اساس تعداد وقوع، که توسط عملکرد COUNTIF برگردانده شده، فیلتر میکند. در مثال ما نتیجه COUNTIF این آرایه شمارش خواهد بود:

{4;1;3;4;4;1;3;4;3}

علمیات مقایسه ای (1<) آرایه فوق را به مقادیر true و false تغییر می دهد، که true مواردی را نشان می دهد که بیش از یک بار تکرار شده باشند:

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}

این آرایه به جای آرگومان include در تابع FILTER قرار میگیرد و به تابع میگوید کدام مقادیر در آرایه نتیجه ظاهر شود:

{“Andrew”;”David”;”Andrew”;”Andrew”;”David”;”Andrew”;”David”}

همانطور که مشاهده میکنید فقط مقادیر مربوط به true بافی مانده اند.

آرایه بالا به جای آرگومان array تابع UNIQUE قرار می­گیرد و بعد از حذف کردن موارد تکراری نتیجه نهایی را نشان می­دهد:

{“Andrew”;”David”}

نکته: به همین ترتیب میتوانید مقادیر منحصر به فردی که بیش از دوبار (2<)، بیش از سه بار (3<) و …اتفاق افتاده اند؛ فیلتر کنید. برای این منظور فقط در فرمول بالا عدد یک را به عدد مورد نظر تغییر دهید و تمااام.

پیدا کردن مقادیر منحصر به فرد در چندین ستون (ردیف)

در شرایطی که می­خواهید دو یا چند ستون را مقایسه کنید و مقادیر منحصر به فردی را که در آنها وجود دارد برگردانید، تمام ستون های هدف را در آرگومان array قرار دهید.

به عنوان مثال برای برگرداندن نام منحصر به فرد (ستون A) و نام خانوادگی (ستون B) از برندگان، این فرمول را در E2 وارد کنید:

=UNIQUE(A2:B10)

با فشردن کلید enter نتایج زیر به دست می آید:

برای بدست آوردن مقادیر منحصر به فرد در ردیف ها، به عنوان مثال مقایسه مقادیر ستون های A، B و C از این فرمول استفاده کنید:

=UNIQUE(A2:C10)

شگفت انگیزه، مگه نه ؟ 😊

مقادیر منحصر به فردی که بر اساس حروف الفبا مرتب شده اند

معمولاٌ چگونه در اکسل بر اساس حروف الفبا مرتب میکنید؟ درست است، با استفاده از ویژگی مرتب سازی داخلی  (inbuilt sort) یا فیلتر (Filter). مشکل استفاده از این ویژگی ها، این است که هر بار که داده هایتان تغییر کند باید مرتب سازی را مجددا انجام دهید، زیرا برخلاف فرمول های اکسل که با هر تغییر در صفحه اکسل به صورت خودکار مجدداً محاسبه می­شوند، ویژگی ها باید دوباره به صورت دستی اعتمال شوند.

با معرفی توابع آرایه ای پویا، این مشکل از بین رفته است! برای این کار کافیست تابع unique را داخل تابع sort قرار دهید. مانند زیر:

SORT(UNIQUE(array))

به عنوان مثال برای استخراج مقادیر منحصر به فرد ستون های A تا C و مرتب کردن آنها از A تا Z، از این فرمول استفاده کنید:

=SORT(UNIQUE(A2:C10))

دو مثال را با هم مقایسه کنید. در مثال دوم ما به راحتی میتوانیم بفهمیم که Andrew و David در دو مسابقه مختلف برنده شده اند.

نکته: در این مثال، مقادیر موجود در ستون اول (first name) از A به Z مرتب کرده ایم. این مورد را به صورت پیش فرض تابع SORT انجام می­دهد و آرگومان های sort_index و sort_order در فرمول ما حذف شده اند. اگر می خواهید نتایج را بر اساس ستون دیگری یا به شکل دیگری (مثلا از Z به A یا از بالاترین به کوچکترین) مرتب کنید، آرگومان های دوم و سوم تابع sort را به گونه ای که در آموزش تابع sort گفته شده است، تنظیم کنید.

پیدا کردن مقادیر منحصر به فرد از چندین ستون و پیوند دادن آنها در یک سلول

هنگام جستجو کردن در چندین ستون، تابع UNIQUE در اکسل به صورت پیش فرض، هر مقدار را در سلول جداگانه برمی­گرداند. شاید شما دوست داشته باشید نتایج در یک سلول نمایش داده شوند، چاره چیست؟

برای رسیدن به این هدف، به جای اینکه از مرجع کل محدوده استفاده کنید، از علامت & برای پیوند دادن ستون ها استفاده کنید و جدا کننده دلخواه خود را در این بین قرار دهید.

به عنوان مثال، ما میخواهیم نام های موجود در A2:A10 و نام های خانوادگی B2:B10 را با هم ادغام کنیم و مقادیر را با یک فاصله (” “) از هم جدا کنیم:

=UNIQUE(A2:A10&" "&B2:B10)

به این ترتیب در یک ستون لیستی از نام کامل (نام و نام خانوادگی) خواهیم داشت: