تابع UNIQUE در اکسل – استخراج داده های غیرتکراری و ترکیب با FILTER

تابع UNIQUE یکی از توابع آرایه‌ای پویا در اکسل است که برای استخراج داده‌های یکتا یا غیر تکراری از محدوده‌ مشخصی در جدول کاربرد دارد. این تابع که در نسخه جدید ۲۰۲۱ به بعد اکسل کار می‌کند، برای انواع داده‌ها مانند متن، عدد و تاریخ قابل استفاده است. به این شکل می‌توانیم فهرستی مرتب و فیلتر شده بدون داده‌های تکراری داشته باشیم. برای آشنایی بیشتر با تابع UNIQUE در اکسل در این مطلب از مجله فرادرس روش فرمول‌نویسی ساده با آن و جدا کردن شرطی داده‌های غیرتکراری با تابع FILTER را همراه مثال‌های مختلف یاد می‌گیریم.

فرمول کلی تابع UNIQUE در اکسل

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

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

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

• Array (آرگومان اجباری): محدوده یا آرایه‌ای که می‌خواهیم داده‌های غیرتکراری را از آن استخراج کنیم.
• By_col  (آرگومان اختیاری): یک مقدار منطقی که نشان می‌دهد داده‌ها به چه شکل با هم مقایسه شوند. با نوشتن عبارت «TRUE» تابع داده‌ها را به‌صورت ستون به ستون مقایسه می‌کند. در صورت خالی گذاشتن این آرگومان یا نوشتن عبارت «FALSE» نیز داده‌ها به شکل سطر به سطر مقایسه می‌شوند.
• exactly_once (آرگومان اختیاری): این آرگومان نیز یک عبارت منطقی است که نشان می‌دهد چه نوع داده‌ای غیرتکراری محسوب می‌شود. در حالت پیش‌فرض که مقداری برای آن نمی‌نویسیم، تابع به شکل خودکار تمام داده‌های متفاوت نسبت به یکدیگر را صرف‌نظر از تعداد تکرار آن‌ها، به‌عنوان داده یکتا در نظر می‌گیرد. به عبارت دیگر خروجی فرمول فهرستی از تمام داده‌هایی است که حداقل یک‌بار تکرار شده‌اند. این حالت در مورد نوشتن عبارت «FALSE» نیز به همین شکل است. اما اگر عبارت «TRUE» را بنویسیم، خروجی فرمول فقط داده‌هایی است که فقط یک‌بار در محدوده نمایش داده شده‌اند.

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

مثال فرمول نویسی ساده با تابع UNIQUE در اکسل

برای آشنایی بیشتر با کاربرد تابع UNIQUE در اکسل ابتدا پنج نوع فرمول‌نویسی ساده زیر را توضیح می‌دهیم.

1. جدا کردن داده‌های قرار گرفته در یک سطر
2. جدا کردن داده‌های قرار گرفته در یک ستون
3. جدا کردن داده‌ها فقط با یک بار تکرار
4. جدا کردن داده‌های قرار گرفته در چند ستون
5. جدا کردن داده‌ها و ادغام آن‌ها در یک سلول

در ادامه بحث هر یک از این موارد را همراه مثال توضیح می‌دهیم.

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

کلیک کنید

۱. جدا کردن داده‌های غیر تکراری قرار گرفته در سطر

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

برای جدا کردن داده‌های غیرتکراری از فهرست اسامی ابتدا فرمول =UNIQUE(B2,B8) را در سلول D2 می‌نویسیم.

از آنجا که مقایسه اسامی به شکل سطر به سطر است، نیاز به نوشتن آرگومان by_col نیست. همچنین چون می‌خواهیم اسامی که حداقل یک‌بار در فهرست تکرار شده‌اند را تفکیک کنیم،‌ آرگومان exactly_once را نیز نمی‌نویسیم. بعد از نوشتن فرمول در سلول D2 و فشار دادن دکمه«ENTER»، اکسل به‌صورت خودکار بقیه اسامی غیرتکراری را در سلول‌های دیگر می‌نویسد.

۲. جدا کردن داده‌های غیرتکراری قرار گرفته در ستون

اما حالت دوم فرمول‌نویسی ساده با تابع UNIQUE زمانی است که مانند تصویر زیر داده‌ها در چند ستون قرار داشته باشند.

در این مورد به‌جای آرگومان by_col عبارت «TRUE» را می‌نویسیم تا داده‌ها به شکل ستون به ستون مقایسه شوند.

بعد از نوشتن فرمول در سلول و فشار دادن دکمه «ENTER» خروجی در ستون‌های بعدی به‌صورت خودکار نوشته می‌شوند.

۳. جدا کردن داده‌ها با یک بار تکرار

اگر بخواهیم داده‌هایی که فقط یک بار تکرار شده‌اند را به‌دست آوریم، به‌جای آرگومان سوم فرمول عبارت «TRUE» می‌نویسیم و آرگومان دوم را خالی می‌گذاریم.

به‌عنوان مثال در فهرست اسامی زیر برای جدا کردن اسم افرادی که فقط یک بار برنده شده‌اند، فرمول =UNIQUE(B2:B8, ,TRUE) را می‌نویسیم.

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

۴. جدا کردن داده‌های غیرتکراری قرار گرفته در چند ستون

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

به‌عنوان مثال در جدول زیر نام «علی ابراهیمی» سه بار تکرار شده است. اگر بخواهیم بدون در نظر گرفتن رشته ورزشی، فقط اسم برندگان را جدا کنیم، با انتخاب محدوده A2:B6 فرمول زیر را می‌نویسیم.

=UNIQUE(A2:B6)

بنابراین نتیجه به شکل تصویر زیر در می‌آید.

اما برای جدا کردن اسامی برندگان همراه با رشته ورزشی، محدوده انتخابی در فرمول را A2:C6 در نظر می‌گیریم. نتیجه نهایی به شکل تصویر زیر است.

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

۵. جدا کردن داده‌های غیرتکراری و ادغام آن‌ها در یک سلول

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

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

=UNIQUE(A2:A6&" "&B2:B6)

نتیجه نهایی به شکل تصویر زیر درمی‌آید.

البته برای ادغام حرفه‌ای متن دو سلول در اکسل از تابع TEXTJOIN هم می‌توانیم استفاده کنیم که نیاز به فرمول‌نویسی در سطح پیشرفته دارد. در مطلب زیر نکات فرمول‌نویسی با این تابع را توضیح داده‌ایم.

یادگیری مهارت فرمول‌نویسی مقدماتی تا پیشرفته اکسل همراه فرادرس

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

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

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش رایگان ساخت فرمول‌‌های پیچیده در اکسل در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس

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

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل مقدماتی تا پیشرفته در فرادرس

 فرمول‌نویسی ترکیبی تابع UNIQUE و تابع FILTER

تابع FILTER ابزاری برای دسته‌بندی داده‌ها بر اساس یک یا چند شرط تعریف شده است. ترکیب آن با تابع UNIQUE کاربردهای زیر را در حذف داده‌های تکراری دارد.

1. جدا کردن داده‌ها با یک شرط خاص
2. جدا کردن داده‌ها با برقراری هم‌زمان چند شرط
3. جدا کردن داده‌ها با برقراری فقط یک شرط
4. جدا کردن داده‌ها با حذف سلول‌های خالی
5. جلوگیری از خطا هنگام فرمول‌نویسی شرطی
6. جدا کردن داده‌ها از یک ستون خاص

هر یک از این موارد نوعی فرمول‌نویسی پیشرفته در اکسل است که همراه مثال آن‌ها را توضیح می‌دهیم.

فیلم آموزش ساخت فرمول‌ های پیچیده در اکسل در فرادرس

کلیک کنید

۱. جدا کردن داده‌های غیرتکراری با یک شرط خاص

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

UNIQUE(FILTER(array, criteria_range = criteria))

که در آن هر یک از آرگومان‌ها به‌صورت زیر تعریف می‌شوند.

• array : محدوده مورد نظر در جدول که می‌خواهیم داده‌های تکراری از آن حذف شوند.
• criteria_range : محدوده‌ای که شرط را روی آن اعمال می‌کنیم.
• criteria : شرط دلخواه که برای فیلتر کردن تعریف می‌کنیم.

به‌عنوان مثال در جدول زیر می‌خواهیم اسامی غیرتکراری برندگان در رشته ورزشی «بسکتبال» را جدا کنیم.

برای این کار فرمول زیر را می‌نویسیم.

=UNIQUE(FILTER(A2:B6, C2:C6=F1))

در این فرمول A2:B6 بخشی از جدول است که می‌خواهیم اسامی غیرتکراری را از آن جدا کنیم. همچنین محدوده C2:C6 نیز قسمتی است که شرط مربوط به رشته ورزشی روی آن اعمال می‌شود.

بنابراین نتیجه نهایی به شکل تصویر زیر در می‌آید.

۲. جدا کردن داده‌های غیرتکراری با برقراری هم‌زمان چند شرط

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

=UNIQUE(FILTER(array, (criteria_range1 = criteria1) * (criteria_range2 = criteria2)))

در این فرمول بسته به تعداد شرط‌ها آرگومان criteria_rangen = criterian را اضافه می‌کنیم. علامت * نیز نقش عملگر «AND» را بازی می‌کند.

به‌عنوان مثال در جدول زیر اسامی برندگان چند رشته ورزشی بر حسب سن آن‌ها را داریم.

برای جدا کردن اسامی غیرتکراری افراد کمتر از ۱۸ سال که در رشته بسکتبال برنده شده‌اند، فرمول زیر را می‌نویسیم.

=UNIQUE(FILTER(A2:B6,(C2:C6=G1)*(D2:D6<G2)))

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

۳. جدا کردن داده‌های غیرتکراری با برقراری یکی از شرط‌ها

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

=UNIQUE(FILTER(array, (criteria_range1 = criteria1) + (criteria_range2 = criteria2)))

در این فرمول علامت «+» نقش عملگر «OR» را دارد.

به‌عنوان مثال برای جدا کردن اسامی افرادی که در یکی از رشته‌های ورزشی «فوتبال» یا «بسکتبال» برنده شده اند، فرمول را به شکل زیر می‌نویسیم.

=UNIQUE(FILTER(A2:B8, (C2:C8=G1) + (C2:C8=G2)))

بنابراین نتیجه مانند تصویر زیر خواهد بود.

۴. جدا کردن داده‌های غیرتکراری با حذف سلول‌های خالی

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

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

=UNIQUE(FILTER(range, range<>""))

در این فرمول تابع FILTER در آرگومان range<>"" فقط سلول‌هایی را برمی‌گرداند که خالی نباشند.

به‌عنوان مثال در جدول نمونه برای حذف عدد صفر فرمول زیر را می‌نویسیم.

=UNIQUE(FILTER(B2:B10, B2:B10<>""))

بنابراین مانند تصویر زیر در نهایت داده‌های غیرتکراری بدون در نظر گرفتن سلول خالی بازگردانده می‌شوند.

۵. جلوگیری از خطا هنگام جدا کردن شرطی داده‌ها

بیشتر موارد مشکلی در کار با تابع UNIQUE همراه تابع FILTER نداریم. اما اگر فرمول داده‌ تکراری را پیدا نکند، پیام خطای #CALC!  را برمی‌گرداند. برای رفع این خطا از تابع IFERROR همراه تابع UNIQUE و تابع FILTER استفاده می‌کنیم.

به‌عنوان مثال در جدول تصویر زیر داده‌ای با شرط‌های تعریف شده در فرمول وجود ندارد و پیام خطای #CALC!  را می‌بینیم.

بنابراین برای رفع مشکل، فرمول زیر را می‌نویسیم.

=IFERROR(UNIQUE(FILTER(A2:B8, (C2:C8=G1) * (C2:C8=G2))), "داده‌ای پیدا نشد")

در این فرمول اگر تابع UNIQUE داده‌ای را پیدا نکند، تابع IFERROR خروجی «داده‌ای پیدا نشد» را نشان می‌دهد.

برای آشنایی بیشتر با تابع IFERROR پیشنهاد می‌کنیم فیلم آموزش رایگان استفاده از فرمول شرطی با تابع IFERROR در فرادرس را مشاهده کنید.

۶. جدا کردن داده‌های غیر تکراری از یک ستون خاص

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

=UNIQUE(FILTER(range,{1,2,3,..})

در این فرمول نام ستون‌های مورد نظر را در تابع FILTER می‌نویسیم. آرگومان‌ range هم محدوده‌ قرارگیری این ستون‌ها هستند.

به‌عنوان مثال در جدول زیر می‌خواهیم اسامی برندگان را بر اساس داده‌های ستون A و C جدا کنیم به شکلی که ستون اول خروجی مربوط به «نام» و ستون دوم «رشته ورزشی» باشد. برای این کار فرمول زیر را می‌نویسیم.

=UNIQUE(FILTER(A2:C8,{1,0,1}))

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

برای آشنایی کامل‌تر با تابع FILTER در اکسل، پیشنهاد می‌کنیم مطلب زیر از مجله فرادرس را مطالعه کنید:

پیام‌های خطا هنگام کار با تابع UNIQUE در اکسل

به‌جز پیام خطای #CALC! که در مثال مربوط به فرمول‌نویسی ترکیبی تابع UNIQUE و تابع FILTER توضیح دادیم، سه خطای دیگر نیز ممکن است هنگام کار با این تابع پیش آید.

پیام خطای ?NAME#

این پیام نشان می‌دهد که تابع UNIQUE در نسخه فعلی اکسل پشتیبانی نمی‌شود. البته معمولا زمانی‌که فایل حاوی تابع را در نسخه‌های قدیمی اکسل باز می‌کنیم، به این پیام خطا برمی‌خوریم. گاهی اوقات هم اگر نام تابع را از نظر املایی به‌درستی ننویسیم اکسل پیام #NAME? را نشان می‌دهد.

پیام خطای SPILL#

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

همچنین لازم است به این موضوع توجه کنیم که تابع UNIQUE در جدول‌هایی که از بخش «Table» رسم می‌شوند، کار نمی‌کند. در این حالت نیز اگر تابع را در این نوع جدول قرار دهیم، اکسل پیام خطای #SPILL را نشان می‌دهد.

پیام خطای REF#

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

جمع‌بندی پایانی

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