اگر ورژن 2021 آفیس را نصب کرده باشید، جتما با توابع UNIQE و FILTER آشنا شده‌اید(اگر هم نشده‌اید، مهم نیست هم‌اکنون در حال آشنا شدن هستید)، این دو تابع قدرتمند و توانای آفیس بسیار کاربردی و مفید هستند، جلوگیری از تکرار داده، جمع‌آوری داده‌هایی که یک صفت مشترک دارند و ...، اما اگر این نسخه یا ورژن را نداشته باشید چه؟ محرومیت از این دو تابع در عمل کار را برایتان سخت می‌کند، شاید تا حدودی بتوانید با استفاده از توابع IF،  LOOKUP، INDEX و MATCH کار خود را راه اندازی کنید، اما تقریبا غیرممکن است مشابه UNIQE را با این توابع ساخت، در ادامه نخست به تعریف و کاربرد این توابع خواهم پرداخت و پس از آن روش‌های مختلف برای تولید این توابع در ورژن های پایین‌تر آفیس را خواهم گفت.

UNIQU

تابع UNIQUE در اکسل یک تابع آرایه داینامیکی(Dynamic Array Function) است که در Excel 365 و Excel 2021 معرفی شد. این تابع برای استخراج مقادیر یکتا (Unique Values) از یک محدوده یا آرایه استفاده میشود. در ادامه به توضیح کامل این تابع می‌پردازیم:

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

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

اگر دقت کنید کشور IRAN دو بار در لیست ظاهر شده، لذا لیست نام کشور‌ها ظاهر می‌گردد ولی نام IRAN فقط یکبار در آن ظاهر می‌شود. اگر دستور زیر را در سلول B2 بنویسید:

=UNIQUE(A2:A6,FALSE,TRUE)

چون نام کشور ایران(IRAN) تنها نام تکراری در لیست است لذا بجز ایران بقیه را نمایش می‌دهد.

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

=UNIQUE(A2:B7)

دستور بالا مقدار زیر را برمی‌گرداند

اگر دقت کنید دو ردیف کشور ایران(IRAN) تکراری نمی‌باشند زیرا یک ردیف(IRAN و 30) است و آن یکی(IRAN و 25) لذا تکراری نیست. دقت داشته باشید تابع فوق نسبت به حروف بزرگ و کوچک حساس نیست(یعنی USA=usa می‌باشد), این تابع را می‌توانید با دستور SORT هم استفاده کنید.

=SORT(UNIQUE(A2:A6,FALSE,TRUE))

اگر دسترسی به آفیس ورژن 2021 ندارید می‌توانید از فرمول‌نویسی استفاده کنید(با اینکه نتیجه حاصل می‌گردد ولی چندان مطلوب نیست) در زیر نمونه قابل زیر را داریم(این کد را در سلول B2 بنویسید و تا B6 ادامه‌دهید:

=IFERROR(INDEX($A$2:$A$6,IF(COUNTIF($A$2:$A2,A2)=1,ROWS($A$2:$A2),-1)),"")

عیب‌های این روش عبارتند از:

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

روش دیگر استفاده از VBA هست که خود نیاز به مطلبی جداگانه دارد.

FILTER

این تابع برای فیلتر کردن داده‌ها بر اساس یک یا چند شرط استفاده می‌شود و نتایج را به‌صورت پویا در سلول‌های مجاور نمایش می‌دهد. در ادامه به توضیح کامل این تابع می‌پردازیم: 

=FILTER(array, include, [if_empty])

• array: محدوده یا آرایهای که میخواهید فیلتر شود.
• include: شرط فیلتر کردن (یک آرایه بولی (TRUE/FALSE) که اندازه آن با سطرها یا ستونهای array برابر است).
• [if_empty] (اختیاری): مقداری که در صورت عدم وجود نتیجه منطبق با شرط نمایش داده می‌شود.

=FILTER(A2:A6,A2:A6="IRAN")

فقط ردیف‌هایی که ایران دارد را می‌آورد.

=FILTER(A2:B6, (A2:A6="IRAN") * (B2:B6>25))

این دستور تنها IRAN با مقدار بیش از 25 را می‌آورد(علامت * همانند عملگر AND کار می‌کند، زیرا هر دو مقایسه بالا (A2:A6="IRAN") و (B2:B6>25) دو آرایه با مقادیر true و یا false  برمی‌گرداند، سپس عناصر این دو آرایه در هم ضرب‌شده و می‌دانیم در اکسل فقط true*true = true و در بقیه حالت‌ها نتیجهfalse  است ).

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