آرایه‌ها در اکسل؛ راهنمای کاربردی فرمول‌نویسی مدرن

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

آرایه یعنی چه؟

آرایه مجموعه‌ای مرتب از مقادیر است. هر محدوده‌ی سلولی در اکسل در واقع یک آرایه است: یک ستون، آرایه‌ی عمودی؛ یک سطر، آرایه‌ی افقی؛ و یک بلوک، آرایه‌ی دوبعدی. در فرمول‌ها، آرایه درون آکولاد نوشته می‌شود:

{10,20,30}        // افقی (جداکننده ستون: کاما)
{10;20;30}        // عمودی (جداکننده سطر: نقطه‌ویرگول)
{1,2,3;4,5,6}     // دوبعدی: ۲ سطر و ۳ ستون

برای تمرین، هر کدام از عبارت‌های زیر را در یک سلول بنویسید و نتیجه را ببینید:

={1,2,3}*10
={1;2;3}+{10;20;30}
={1,2,3;4,5,6}^2

فرمول آرایه‌ای چیست؟

فرمول آرایه‌ای فرمولی است که به‌جای یک مقدار، روی مجموعه‌ای از مقادیر کار می‌کند. فرض کنید ستون B قیمت فروش و ستون C بهای تمام‌شده‌ی ۷ محصول است و می‌خواهید بیشترین سود یک واحد را پیدا کنید:

=MAX(B2:B8-C2:C8)

اکسل ابتدا هفت تفریق را هم‌زمان انجام می‌دهد و آرایه‌ای هفت‌عضوی می‌سازد، سپس این آرایه را به MAX می‌دهد. به این مرحله‌ی میانی «عملیات آرایه‌ای» می‌گویند. نکته‌ی کلیدی این است که اکسل می‌تواند محاسبه را روی کل یک محدوده انجام دهد، نه فقط روی تک‌تک سلول‌ها.

دوران Ctrl+Shift+Enter

در نسخه‌های قدیمی، چنین فرمولی را باید با Ctrl+Shift+Enter ثبت می‌کردید تا اکسل دور آن آکولاد بگذارد. فراموش‌کردن این کار گاهی بدون هیچ هشداری نتیجه‌ی غلط می‌داد. در Microsoft 365 و اکسل ۲۰۲۱ به بعد این دردسر تمام شده و کافی است Enter بزنید. فرمول‌های قدیمی هنوز کار می‌کنند.

آرایه‌های پویا و «سرریز» (Spill)

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

=UNIQUE(A2:A100)

فهرست مقادیر یکتا از همان سلول به پایین پخش می‌شود. محدوده‌ی پرشده را «محدوده‌ی سرریز» (Spill Range) می‌نامند و با تغییر داده‌ها خودش بزرگ یا کوچک می‌شود. با انتخاب سلول فرمول، کادر آبی دور کل محدوده دیده می‌شود.

ارجاع به محدوده‌ی سرریز

اگر فرمول بالا در سلول E2 باشد، با علامت # می‌توان به کل نتیجه اشاره کرد، بدون اینکه نگران اندازه‌اش باشید:

=COUNTA(E2#)   // تعداد مقادیر یکتا

توابع قدیمی هم سرریز می‌کنند

این رفتار فقط مخصوص توابع جدید نیست و کل موتور محاسباتی اکسل را شامل می‌شود:

=LEN(A2:A6)                       // طول متن هر پنج سلول
=VLOOKUP("کد-105",A2:D50,{2,3,4},0)  // سه ستون در یک فرمول

در مثال دوم، {2,3,4} یک «ثابت آرایه‌ای» است که شماره ستون‌های موردنظر را یک‌جا می‌دهد.

چند نمونه‌ی کاربردی

مرتب‌سازی و فیلتر

=SORT(A2:C20,3,-1)                        // مرتب‌سازی نزولی بر اساس ستون سوم
=FILTER(A2:D20,D2:D20="تحویل شد","موردی نیست")

درون FILTER، عبارت D2:D20="تحویل شد" آرایه‌ای از درست/نادرست می‌سازد و فقط ردیف‌های «درست» برگردانده می‌شوند.

زنجیره‌کردن توابع

=SORT(UNIQUE(A2:A100))

خروجی هر تابع، ورودی تابع بیرونی می‌شود؛ مثل خط تولید. هر تابع یک کار ساده انجام می‌دهد و ترکیبشان مسئله‌ی بزرگ‌تر را حل می‌کند.

شرط «و» و «یا» با ضرب و جمع

=FILTER(A2:D50,(B2:B50="تهران")*(D2:D50>1000),"یافت نشد")   // و
=FILTER(A2:D50,(B2:B50="تهران")+(B2:B50="شیراز"),"یافت نشد")   // یا

درست/نادرست در عملیات ریاضی به ۱ و ۰ تبدیل می‌شود. ضرب یعنی هر دو شرط برقرار باشد و جمع یعنی دست‌کم یکی.

انتخاب تصادفی چند مورد

=TAKE(SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21))),3)

نام‌ها با اعداد تصادفی مرتب (بُر) می‌شوند و سه مورد اول برداشته می‌شود. توجه کنید RANDARRAY با هر تغییر در برگه دوباره محاسبه می‌شود.

محدودیت مهم: همه‌چیز آرایه نمی‌پذیرد

توابع خانواده‌ی IFS (مثل COUNTIFS و SUMIFS) برای آرگومان‌های محدوده، سلول واقعی می‌خواهند و آرایه‌ی حاصل از تابع دیگر را قبول نمی‌کنند. مثلاً این فرمول خطا می‌دهد:

=COUNTIFS(YEAR(B2:B100),2026)   // خطا

راه‌حل، استفاده از SUMPRODUCT است:

=SUMPRODUCT(--(YEAR(B2:B100)=2026))

علامت -- مقادیر درست/نادرست را به ۱ و ۰ تبدیل می‌کند تا جمع‌پذیر شوند.

چطور آرایه‌ها را «ببینیم»؟

وقتی فرمول پیچیده می‌شود، دیدن نتیجه‌ی مراحل میانی بهترین راه اشکال‌یابی است:

• پیش‌نمایش با انتخاب (Microsoft 365): در حالت ویرایش، بخشی از فرمول را انتخاب کنید تا نتیجه‌اش در یک راهنما نمایش داده شود. فرمول تغییری نمی‌کند.
• کلید F9: بخشی از فرمول را انتخاب و F9 بزنید تا با مقدار محاسبه‌شده جایگزین شود. برای برگشت حتماً Esc بزنید، نه Enter.
• Evaluate Formula: از تب Formulas، فرمول را قدم‌به‌قدم ارزیابی کنید.
• ARRAYTOTEXT: آرایه را به متن تبدیل می‌کند تا همیشه روی برگه دیده شود.

توابع جدید مرتبط با آرایه

جمع‌بندی

• هر محدوده در اکسل یک آرایه است و آرایه‌ها با آکولاد نوشته می‌شوند.
• در اکسل جدید دیگر به Ctrl+Shift+Enter نیازی نیست.
• فرمولی که چند مقدار برگرداند، خودکار سرریز می‌شود و با # می‌توان به آن ارجاع داد.
• توابع را می‌توان زنجیروار ترکیب کرد.
• برای فهم فرمول‌های پیچیده از F9 یا پیش‌نمایش انتخابی استفاده کنید.