آشنایی با تابع MAKEARRAY در اکسل

تابع MAKEARRAY یکی از توابع جدید اکسل (Microsoft 365 / Excel for the web) است که به شما اجازه می‌دهد یک آرایه (ماتریس) را به‌صورت داینامیک و با استفاده از یک تابع LAMBDA تولید کنید. به جای پر کردن سلول‌ها به‌صورت دستی یا نوشتن فرمول‌های تکراری، تابع MAKEARRAY براساس تعداد سطر و ستون و یک LAMBDA که مقدار هر خانه را محاسبه می‌کند، کل آرایه را تولید می‌کند.

قالب کلی و پارامترها

قالب کلی تابع به این شکل است:

=MAKEARRAY(rows, cols, lambda(row, col))

توضیح پارامترها:

• rows: تعداد سطرهای آرایه خروجی (عدد کامل مثبت).
• cols: تعداد ستون‌های آرایه خروجی.
• lambda(row, col): تابع LAMBDA که برای هر خانه آرایه مقدار را برمی‌گرداند. آرگومان‌ها row و col شماره سطر و ستون (از 1 شروع) هستند.

مثال پایه — جدول ضرب 10×10

مثالی متداول برای درک عملکرد MAKEARRAY تولید جدول ضرب است:

=MAKEARRAY(10, 10, LAMBDA(r, c, r * c))

این فرمول یک آرایه 10 در 10 ایجاد می‌کند که مقدار هر خانه برابر ضرب شماره سطر در شماره ستون است (مثلاً خانه ردیف 3 ستون 4 مقدار 12 خواهد داشت). اگر فرمول را در یک سلول وارد کنید، آرایه به صورت Spill در محدوده بعدی نمایش داده خواهد شد.

مثال پیشرفته — تولید جدول با سرستون و سرسطر

می‌خواهیم جدول ضرب را با سطر و ستون عنوان‌دار (0 تا 10) تولید کنیم. راهکار با استفاده از IF درون LAMBDA:

=MAKEARRAY(11, 11, LAMBDA(r, c,
  IF(r=1,
     IF(c=1, "", c-1),    /* ردیف اول: عنوان ستون */     IF(c=1, r-1, (r-1)*(c-1)) /* ستون اول: عنوان سطر، بقیه: ضرب */  )
))

در این فرمول:

• ردیف 1 و ستون 1 خانه خالی می‌شود.
• ردیف 1 (سطر عناوین) شماره ستون‌ها را نمایش می‌دهد.
• ستون 1 (ستون عناوین) شماره سطرها را نمایش می‌دهد.
• در بقیه خانه‌ها مقدار ضرب سطر-ستون چاپ می‌شود.

ترکیب MAKEARRAY با توابع دیگر — مثال تقویم ماه

با MAKEARRAY می‌توان ساختارهایی مثل تقویم ماهانه را هم تولید کرد. فرض کنید می‌خواهیم تقویم یک ماه با شروع روز هفته و تعداد روزها بسازیم (نمایش روزهای ماه در ماتریس 6×7):

=LET(
  startDay, 3,   /* فرضاً چهارشنبه = 3 (از 1 تا 7) */  daysInMonth, 30,
  MAKEARRAY(6, 7, LAMBDA(r, c,
    dayNum := (r-1)*7 + c - startDay + 1,
    IF(AND(dayNum>=1, dayNum<=daysInMonth), dayNum, "")
  ))
)

توضیح: با LET ابتدا متغیرهای startDay و daysInMonth تعریف شده‌اند تا محاسبه خواناتر شود. سپس برای هر خانه عدد روز محاسبه و تنها اگر بین 1 تا تعداد روزهای ماه بود نمایش داده می‌شود.

موارد استفاده عملی و مزایا

• تولید داده‌های نمونه یا تستی (mock data) بدون نوشتن فرمول تکراری.
• ساخت داشبوردها و جداول محاسباتی داینامیک که اندازه آن‌ها با پارامترها تغییر می‌کند.
• جایگزینی VBA برای بسیاری از تولیدهای ماتریسی ساده.
• قابلیت ترکیب با XLOOKUP، INDEX، FILTER و توابع داینامیک دیگر برای ساخته شدن جداول پویا.

نکات عملکردی و بهترین روش‌ها (Expert Tips)

• MAKEARRAY در Excel 365 و نسخه‌های پشتیبانی‌کننده توابع داینامیک کار می‌کند؛ در نسخه‌های قدیمی قابل استفاده نیست.
• برای جلوگیری از محاسبات غیرضروری از LET داخل LAMBDA استفاده کنید تا مقادیر میانی تنها یک‌بار محاسبه شوند.
• از توابع بسیار ولتایل (مثل RAND یا NOW) درون MAKEARRAY با احتیاط استفاده کنید — می‌تواند باعث ری‌کالکول مداوم و کندی شود.
• اگر آرایه بزرگ است، دقت کنید که تولید و نمایش آن حافظه و سرعت را مصرف می‌کند؛ بهتر است اندازه را محدود کنید یا محاسبات را به بخش‌های کوچک‌تر تقسیم کنید.

مثال ترکیبی با XLOOKUP — ماتریس ارزش با برچسب‌ها

فرض کنید دو لیست عنوان (ردیف‌ها و ستون‌ها) دارید و می‌خواهید ماتریسی تولید کنید که مقدار هر خانه بر اساس جستجوی ترکیبی تعیین شود:

=MAKEARRAY(ROWS(RowLabels), COLUMNS(ColLabels),
  LAMBDA(r, c,
    XLOOKUP(INDEX(RowLabels, r) & "|" & INDEX(ColLabels, c), Keys, Values, "")
  )
)

در این سناریو:

• RowLabels و ColLabels محدوده‌هایی هستند که برچسب‌ها را نگه می‌دارند.
• Keys و Values جدولی از کلیدهای ترکیبی و مقدار متناظر را نگه می‌دارند (مثلاً “ProductA|Region1”).
• برای هر خانه ترکیب برچسب‌ها جستجو شده و مقدار متناظر برگردانده می‌شود.

مقایسه با روش‌های سنتی

اشکال‌زدایی و نکات عملی

• اگر آرایه نمایش داده نمی‌شود، بررسی کنید فرمول در یک سلول وارد شده و فضای کافی برای Spill وجود داشته باشد.
• خطاهای داخل LAMBDA را با ارزیابی مقدار r و c تست کنید (مثلاً با نمایش r & “,” & c برای دیدن مقادیر).
• برای مقادیر محاسباتی بزرگ ابتدا با نسخه کوچک‌تر تست کنید تا از عملکرد مناسب مطمئن شوید.

جمع‌بندی

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

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

ارسال