ساختن و برش آرایه: SEQUENCE، CHOOSECOLS، VSTACK، HSTACK، TAKE و DROP

آرایه را مثل خمیر شکل دهید

وقتی نتیجه‌ی FILTER یا UNIQUE را دارید، اغلب لازم است چند ستونش را جدا کنید، چند جدول را زیر هم بگذارید یا فقط ۱۰ ردیف اول را نشان دهید. Excel 365 مجموعه‌ای از توابع «شکل‌دهی آرایه» دارد که کار چند ستون کمکی یا حتی بخشی از Power Query را انجام می‌دهند.

SEQUENCE در عمل

شماره‌ی ردیف برای خروجی یک FILTER در G2:
=SEQUENCE(ROWS(G2#))

سی روز از تاریخ B1:
=SEQUENCE(30, 1, B1)

جدول ضرب 10×10:
=SEQUENCE(10) * SEQUENCE(1, 10)

داده‌ی آزمایشی: 100 مبلغ تصادفی بین 5 تا 50 میلیون:
=RANDARRAY(100, 1, 5E6, 5E7, TRUE)

ادغام شیت‌های ماهانه با VSTACK

اگر هر ماه در یک شیت با ساختار یکسان است، VSTACK با ارجاع سه‌بعدی همه را زیر هم می‌چیند. چون محدوده‌ها معمولاً ردیف خالی دارند، با FILTER آن‌ها را حذف کنید:

=LET(
  d, VSTACK(فروردین:اسفند!A2:F500),
  FILTER(d, CHOOSECOLS(d, 1) <> "")
)

فقط ستون‌های تاریخ، نماینده و مبلغ (ستون‌های 1، 2 و 6):
=CHOOSECOLS(G2#, 1, 2, 6)

ده فاکتور بزرگ (مرتب نزولی روی ستون 6):
=TAKE(SORT(G2#, 6, -1), 10)

حذف ردیف اول (سرستون) از یک محدوده:
=DROP(A1:F500, 1)

افزودن سرستون به یک گزارش پویا:
=VSTACK({"نماینده","فروش"}, HSTACK(K2#, L2#))

این روش برای گزارش‌های سبک و فایل‌های کوچک عالی است. برای ده‌ها فایل جداگانه یا صدها هزار سطر، Power Query (فصل ۵) پایدارتر و سریع‌تر است.

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

• اگر آرایه‌هایی با عرض متفاوت را VSTACK کنید، جاهای خالی با #N/A پر می‌شوند؛ کل فرمول را در IFNA(…, "") بپیچید.
• VSTACK سه‌بعدی (فروردین:اسفند) هر شیتی را که بین دو شیت ابتدا و انتها قرار گیرد شامل می‌شود؛ با ترفند شیت‌های خالی Start و End از درس ۱۰ ترکیبش کنید.
• TAKE با عدد منفی از انتها می‌گیرد: TAKE(G2#, -5) پنج ردیف آخر، یعنی پنج فروش اخیر اگر داده بر اساس تاریخ مرتب باشد.
• TOCOL(B2:M50, 1) یک جدول ماه در نماینده را به یک ستون تبدیل می‌کند و خالی‌ها را کنار می‌گذارد؛ ترکیبش با یک SEQUENCE یک Unpivot سریع فرمولی می‌سازد.
• RANDARRAY و RAND با هر محاسبه عوض می‌شوند؛ برای ثابت کردن داده‌ی آزمایشی، Copy و Paste Values کنید.