آرایه‌های پویا: FILTER، SORT، SORTBY، UNIQUE، عملگر # و خطای #SPILL!

یک فرمول، یک جدول کامل

در Excel 365 و 2021، فرمولی که چند مقدار برمی‌گرداند نتیجه‌اش را در سلول‌های مجاور «می‌ریزد» (Spill). محدوده‌ی نتیجه با یک کادر آبی مشخص می‌شود و فقط سلول اول فرمول دارد. این تغییر، شیوه‌ی ساختن گزارش را عوض کرده است: به‌جای کپی فرمول در صدها سطر، یک فرمول می‌نویسید که خودش بزرگ و کوچک می‌شود.

چهار تابع اصلی

=FILTER(آرایه, شرط, [اگر_خالی])
=SORT(آرایه, [ستون_مرتب‌سازی], [1 صعودی / -1 نزولی], [by_col])
=SORTBY(آرایه, آرایه_معیار1, ترتیب1, [آرایه_معیار2, ترتیب2] …)
=UNIQUE(آرایه, [by_col], [exactly_once])

فروش‌های استان کاشان بالای 100 میلیون:
=FILTER(Sales, (Sales[Province]="کاشان")*(Sales[Amount]>=1E8), "موردی نیست")

فروش‌های تهران یا قم، مرتب بر اساس مبلغ نزولی:
=SORT(FILTER(Sales, (Sales[Province]="تهران")+(Sales[Province]="قم")), 6, -1)

فهرست یکتای نمایندگان به ترتیب الفبا:
=SORT(UNIQUE(Sales[Rep]))

مشتریانی که فقط یک بار خرید کرده‌اند:
=UNIQUE(Sales[Customer], , TRUE)

عملگر # برای ارجاع به کل spill

اگر در G2 فرمول =SORT(UNIQUE(Sales[Rep])) باشد، G2# یعنی «کل محدوده‌ی ریخته‌شده از G2»، هر اندازه که باشد. با آن یک گزارش خلاصه‌ی خودکار می‌سازید:

G2:  =SORT(UNIQUE(Sales[Rep]))
H2:  =SUMIFS(Sales[Amount], Sales[Rep], G2#)          ← فروش هر نماینده
I2:  =H2#/SUM(H2#)                                     ← سهم درصدی
J2:  =ROWS(G2#)                                        ← تعداد نمایندگان

همان گزارش مرتب بر اساس فروش در یک فرمول:
=LET(reps, UNIQUE(Sales[Rep]), tot, SUMIFS(Sales[Amount], Sales[Rep], reps),
     SORTBY(HSTACK(reps, tot), tot, -1))

خطای #SPILL! و علت‌هایش

نکته‌هایی که کمتر کسی می‌داند

• در Data Validation می‌توانید منبع لیست را =$G$2# بگذارید؛ لیست کشویی با اضافه شدن نماینده‌ی جدید خودکار بزرگ می‌شود (فصل ۵).
• علامت @ که گاهی اکسل جلوی فرمول‌های قدیمی می‌گذارد (=@A:A) «تقاطع ضمنی» است: یعنی فقط مقدار هم‌سطر را بگیر، نه کل آرایه. این همان رفتار نسخه‌های قدیمی است که برای سازگاری حفظ شده.
• UNIQUE به بزرگی و کوچکی حروف لاتین حساس نیست، اما «ی» عربی و فارسی را دو مقدار جدا می‌داند؛ پاک‌سازی درس ۱۳ را قبل از UNIQUE انجام دهید.
• FILTER اگر هیچ ردیفی پیدا نکند و آرگومان سوم نداشته باشد #CALC! می‌دهد؛ همیشه یک پیام یا "" برایش بگذارید.
• نمودارها و فرمول‌های دیگر به محدوده‌ی spill با # به‌صورت پویا متصل می‌مانند؛ اما Conditional Formatting را باید روی یک محدوده‌ی بزرگ‌تر تعریف کنید و با فرمول =G2<>"" محدود کنید.