«فرمول نویسی آرایه ای» (Array Formula) روشی سریع و قدرتمند در اکسل برای انجام همزمان چند محاسبه‌ پیچیده روی تعداد زیادی از مقادیر جدول فقط در قالب یک فرمول است. این نوع فرمول‌نویسی باعث ساده‌تر شدن انجام کار و پایین آمدن احتمال خطا در نوشتن فرمول می‌شود. در نسخه‌های جدید اکسل فرمول‌نویسی آرایه‌ای به‌صورت خودکار انجام می‌گیرد، اما در نسخه‌های قدیمی‌تر نیاز به استفاده از ترفندهایی خاص برای این کار داریم. در این مطلب از مجله فرادرس بعد از توضیح کامل فرمول نویسی آرایه ای در اکسل و کاربردهای آن، با انواع فرمول‌های آرایه‌ای، ثابت‌ها و عملگرهای آرایه‌ای همراه مثال آشنا می‌شویم.

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

«آرایه» (Array) در اکسل مجموعه‌ای از چند متن، عدد یا عبارت‌های منطقی هستند که در یک سطر، ستون از جدول یا ترکیبی از هر دو قرار می‌گیرند. به‌عنوان مثال، قرار گرفتن هر یک از این مقادیر در محدوده سلول A1:A5  یا B2:E2  یک آرایه را تشکیل می‌دهد. فرمول‌های آرایه‌ای نیز نوعی فرمول در اکسل هستند که برخلاف فرمول‌های معمولی روی یک یا چند آرایه عمل می‌کنند. این فرمول‌ها به‌جای انجام محاسبه روی یک مقدار یا یک سلول اکسل، همزمان چند مقدار را پردازش می‌کنند. با فرمول نویسی آرایه ای در اکسل می‌توانیم محاسبات را فقط با یک فرمول و بدون نیاز به کپی کردن آن در چند سلول انجام دهیم.

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

فرمول‌نویسی آرایه‌ای در محاسبات پیچیده و تعداد داده‌های زیاد جدول اکسل بسیار مناسب‌تر، ساده‌تر و سریع‌تر از فرمول‌نویسی معمولی است. یک فرمول آرایه‌ای می‌تواند جایگزین چندین فرمول معمول اکسل شود. همچنین کاربرد این روش به‌خصوص در موارد زیر کمک‌کننده و حتی تنها روش فرمول‌نویسی به‌حساب می‌آید.

• محاسبه مجموع N عدد بزرگ‌تر یا کوچک‌تر در یک محدوده سلول (به‌عنوان مثال به‌دست آوردن مجموع سه عدد بزرگ‌تر یک محدوده از سلول‌ها در جدول اکسل)
• محاسبه مجموع Nمین ردیف یا ستون (به‌عنوان مثال به‌دست آوردن مجموع اعداد سومین ردیف جدول اکسل)
• شمارش تعداد کل کاراکترها یا کاراکترهای مشخص در یک محدوده از جدول اکسل

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

روش فرمول نویسی آرایه ای در اکسل

برای فرمول نویسی آرایه ای در اکسل کافی است بعد از نوشتن فرمول به‌شکل معمولی آن، در انتهای کار به‌صورت همزمان سه کلید CTRL + SHIFT + ENTER  را فشار دهیم. البته در نسخه‌های اکسل ۲۰۲۱ و ۳۶۵، نیاز به فشردن این سه کلید نیست و فرمول‌نویسی آرایه‌ای به شکل خودکار با فشردن دکمه «ENTER» هم اجرا می‌شود. به عبارتی، آرایه نویسی در نسخه‌های جدید اکسل حالت «دینامیک» یا «پویا» دارد.

برای درک بهتر کار با یک مثال ساده شروع می‌کنیم. در جدول داده‌های زیر اطلاعات فرضی مربوط به قیمت چند کالا و تعداد فروش آن‌ها قرار دارد.

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

۱. در یک سلول خالی دلخواه مانند C7 فرمول =SUM(B2:B6*C2:C6) را وارد می‌کنیم.

۲. کلیدهای CTRL + SHIFT + ENTER را همزمان فشار می‌دهیم. با این کار فرمول داخل آکولاد قرار می‌گیرد که نشان‌دهنده یک فرمول آرایه‌ای است. در این حالت ابتدا فرمول مقادیر هر ردیف از آرایه در سلول‌های B2 تا C6 را ضرب می‌کند و در انتها، مجموع فروش کل را به عنوان خروجی نهایی نشان می‌دهد.

در نسخه‌های ۲۰۱۹ به قبل اکسل اگر بعد از نوشتن فرمول، دکمه «ENTER» را فشار دهیم، مبلغ فروش فقط یک محصول نمایش داده می‌شود. بنابراین برای به‌دست آوردن کل مبلغ فروش لازم است در هر ردیف یک فرمول جداگانه مانند =B2*C2 را تعریف کنیم و بعد از کپی کردن فرمول در ردیف‌های دیگر، مجموع کل را محاسبه کنیم. اما با فرمول‌ نویسی آرایه ای در اکسل همه این محاسبات همزمان و در یک مرحله با نوشتن فقط یک فرمول انجام می‌گیرد. به این شکل علاوه بر بالاتر رفتن سرعت انجام کار، احتمال خطا هم پایین‌تر خواهد بود.

نکات مهم برای جلوگیری از خطا در آرایه‌نویسی اکسل

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

1. بعد از فشردن همزمان کلیدهای ترکیبی، اکسل به‌صورت خودکار فرمول را داخل علامت آکولاد {‌} قرار می‌دهد.
2. برای آرایه‌نویسی در اکسل نمی‌توانیم آکولادها را به شکل دستی در فرمول تایپ کنیم.
3. در صورت ویرایش فرمول آرایه‌ای، نرم‌افزار اکسل آکولادها را حذف می‌کند. بنابراین در انتها لازم است دوباره سه کلید ترکیبی را فشار دهیم.
4. در نسخه‌های ۲۰۱۹ به قبل اکسل اگر از کلیدهای ترکیبی استفاده نکنیم، فرمول مانند یک فرمول معمولی عمل می‌کند و فقط اولین مقدار را در محاسبات خود در نظر می‌گیرد. به همین دلیل در برخی از مطالب فرمول آرایه‌ای به نام «فرمول CSE» هم شناخته می‌شود که مخفف سه حرف اول کلیدهای CTRL + SHIFT + ENTER است.

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

فرمول‌های آرایه‌ای در اکسل به دو دسته کلی زیر تقسیم‌بندی می‌شوند.

1. «فرمول‌های آرایه‌ای تک سلولی» ( Single Cell Array Formula)
2. «فرمول‌های آرایه‌ای چند سلولی» (Multi Cell Array Formula)

در نوع تک سلولی فرمول فقط در یک سلول قرار می‌گیرد، اما در نوع دوم نتیجه یک فرمول آرایه‌ای در محدوده ای از سلول‌ها وارد می‌شود. فرمول‌نویسی آرایه‌ای با بعضی توابع در اکسل مانند تابع TRANSPOSE، تابع TREND، تابع FREQUENCY و تابع LINEST به‌طور خاص فقط در دسته دوم تعریف می‌شود. یعنی با توجه به ویژگی این توابع، نتیجه محاسبات فرمول‌نویسی آرایه‌ای مجموعه‌ای از مقادیر است که در چند سلول از جدول قرار می‌گیرد. اما خروجی سایر توابع مانند تابع SUM، تابع AVERAGE، تابع AGGREGATE، تابع MAX و تابع MIN بعد از فشردن همزمان سه کلید ترکیبی فقط در یک سلول نمایش داده می‌شود.

برای درک بهتر مفهوم هر یک از این توابع پیشنهاد می‌کنیم درس دوم و سوم از فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل در فرادرس را مشاهده کنید.

چند مثال از انواع فرمول‌های آرایه‌ای اکسل

برای آشنایی بیشتر استفاده از انواع فرمول‌های آرایه‌ای اکسل، چند مثال مختلف را بررسی می‌کنیم.

مثال ۱. فرمول‌ آرایه‌ای تک سلولی

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

در صورت استفاده از فرمول‌نویسی معمولی برای انجام محاسبات، لازم است سه مرحله زیر را انجام دهیم.

۱. یک ستون کمکی (مثلا ستون D) به جدول اضافه می‌کنیم.

۲. فرمولی برای محاسبه حداکثر تغییر در میزان فروش بین دو ماه، به‌عنوان مثال =C2-B2 ، را در هر ردیف می‌نویسیم.

۳. با نوشتن یک فرمول دیگر و استفاده از تابع MAX بیشترین مقدار تغییر فروش محصول را بین این دو ماه محاسبه می‌کنیم.

اما در فرمول‌نویسی آرایه‌ای دیگر نیازی به ستون کمکی و چند مرحله فرمول‌نویسی جداگانه نداریم. زیرا در این فرمول نتایج محاسبات میانی در حافظه آن ذخیره می‌شود. بنابراین کافی است فرمول =MAX(C2:C6-B2:B6) را در یک سلول دلخواه مانند F2 بنویسیم و سپس به‌صورت همزمان سه کلید Ctrl + Shift + Enter را فشار دهیم.

نتیجه محاسبه به شکل تصویر زیر خواهد بود.

مثال ۲. فرمول‌ آرایه‌ای چند سلولی با توابع ساده اکسل

فرض می‌کنیم در جدول داده‌های زیر می‌خواهیم میزان مالیات بر ارزش افزوده هر محصول را محاسبه کنیم.

برای این کار کافی است فرمول =B2:B6 * C2:C6 * 0.1 را در اولین سلول از ستون مالیات یعنی D2 وارد کنیم. سپس سلول‌های خالی باقیمانده ستون یعنی D3 تا D6 را انتخاب می کنیم و همزمان کلیدهای Ctrl + Shift + Enter را فشار می‌دهیم. به این شکل محاسبه به‌صورت خودکار در بقیه سلول‌ها انجام می‌گیرد. همان‌طور که قبل از این گفته شد، در نسخه‌های جدید اکسل نیازی به استفاده از سه کلید ترکیبی نداریم.

مثال ۳. فرمول آرایه‌ای چند سلولی با توابع آرایه‌ای اکسل

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

حال جدول فرضی زیر را در نظر می‌گیریم.

برای جابجا کردن سطر و ستون‌ها با تابع TRANSPOSE و فرمول‌نویسی آرایه‌ای مراحل زیر را انجام می‌دهیم.

۱. محدوده‌ای از سلول‌های خالی را برای نمایش خروجی تابع TRANSPOSE انتخاب می‌کنیم. در این حالت تعداد سطرها و ستون‌های انتخاب شده با توجه به جدول اصلی و عملکرد معکوس کردن تابع است. به این معنا که در مثال مورد نظر، جدول اولیه شش سطر و چهار ستون دارد. بنابراین در سلول‌های خالی جدید چهار سطر و سه ستون را انتخاب می‌کنیم.

۲. برای ورود به حالت ویرایش جدول دکمه F2 را فشار می‌دهیم.

۳. فرمول =TRANSPOSE($A$1:$D$6) را در نوار فرمول‌نویسی اکسل وارد می‌کنیم و سه کلید Ctrl + Shift + Enter را همزمان فشار می‌دهیم.

نتیجه به شکل تصویر زیر در می‌آید.

اگر در این حالت فرمول‌نویسی معمولی بدون کلیدهای ترکیبی را استفاده کنیم، تابع TRANSPOSE فقط مقدار سلول A1 را برمی‌گرداند و بقیه سلول‌ها خالی می‌مانند.

نکات مهم در فرمول‌نویسی آرایه‌ای چند سلولی

برای انجام صحیح محاسبات در فرمول‌نویسی آرایه‌ای چند سلولی لازم است نکات زیر را رعایت کنیم.

• قبل از وارد کردن فرمول، محدوده‌ نمایش نهایی سلول‌ها را انتخاب می‌کنیم.
• برای حذف فرمول آرایه‌ای، ابتدا تمام سلول‌های حاوی فرمول را انتخاب می‌کنیم، سپس دکمهDelete را می‌زنیم.
• محتوای یک سلول تک در فرمول آرایه‌ای قابل ویرایش یا جابجایی نیست. همچنین نمی‌توانیم در آن محدوده سلول جدیدی را اضافه یا حذف کنیم. در صورت انجام این کار اکسل پیام خطای «You cannot change part of an array» را نمایش می‌دهد.
• برای کم کردن تعداد سلول‌های فرمول بعد از پایان محاسبه، ابتدا فرمول قبلی را پاک می‌کنیم و سپس فرمول جدید را در سلول‌ها می‌نویسیم.
• برای توسعه فرمول آرایه‌ای به تعداد سلول‌های بیشتر، ابتدا سلول‌های فعلی را به همراه سلول‌های جدید انتخاب می‌کنیم. سپس بقیه مراحل را انجام می‌دهیم.
• فرمول‌های آرایه‌ای چند سلولی در جدول‌های تعریف شده از مسیر Insert>Table در نوار ابزار اکسل قابل استفاده نیستند.
• محدوده خروجی فرمول آرایه‌ای کاملا با آرایه نمایش داده شده در فرمول برابر است. در صورت بزرگ‌تر بودن خروجی فرمول از محدوده انتخابی، مقادیر اضافی نمایش داده نمی‌شوند. همچنین در صورت کوچک‌تر بودن خروجی فرمول نسبت به محدوده انتخاب نیز با پیام خطای #N/A روبرو می‌شویم.
• اگر تعداد داده‌های خروجی فرمول آرایه‌ای متغیر باشد، برای جلوگیری از پیام خطا و محاسبه درست بهتر است آن را در محدوده‌ای وارد کنیم که برابر یا بزرگ‌تر از بیشترین اندازه خروجی باشد. سپس با استفاده از تابع IFERROR در فرمول، خطاهای احتمالی را حذف می‌کنیم.

آموزش فرمول نویسی آرایه ای در اکسل همراه با فرادرس

فرمول نویسی آرایه ای در اکسل چیزی بیشتر از فشردن همزمان سه کلید ترکیبی است. زیرا فقط در صورت درک کامل فایده این روش در ساده‌تر کردن فرمول‌نویسی و کاهش خطای محاسباتی ضرورت کاربرد آن‌ها مشخص می‌شود. از آنجا که در این نوع فرمول‌نویسی توابع مختلف به شکل‌های ساده و پیچیده قابل استفاده هستند، یادگیری کامل کار با توابع اکسل، ابزارها و ترفندهای آن اهمیت بالایی دارد. بنابراین علاقه‌مندان به یادگیری کامل مفاهیم می‌توانند فیلم‌های آموزشی منتخب زیر در فرادرس را به‌عنوان نقطه شروع حرکت در این مسیر در نظر بگیرند.

همچنین در دو مجموعه آموزش زیر امکان انتخاب‌های بیشتری بر حسب نیاز برای علاقه‌مندان وجود دارد.

ثابت‌های آرایه‌ای در اکسل

«ثابت‌های آرایه‌ای» (Array Constants) در اکسل مجموعه‌ای از مقادیر ثابت در فرمول اکسل هستند که حتی هنگام کپی کردن فرمول به سلول‌های دیگر تغییر نمی‌کنند. این مقادیر به شکل عدد صحیح یا اعشاری، متن و مقادیر منطقی «True» یا «False» در اکسل تعریف می‌شوند. مواردی مانند تاریخ، فرمول‌، توابع، آرایه‌ها، نام‌های تعریف شده، ارجاع به سلول یا محدوده‌ای از جدول اکسل در دسته ثابت‌های آرایه‌ای قرار نمی‌گیرند.

سه نوع ثابت آرایه‌ای به شرح زیر در اکسل وجود دارد.

• ثابت آرایه‌ای افقی
• ثابت آرایه‌ای عمودی
• ثابت آرایه‌ای دو بعدی

هر یک از این ثابت‌ها، تعریف و نحوه استفاده متفاوتی در فرمول نویسی آرایه ای در اکسل دارند که در ادامه در مورد آن‌ها توضیح می‌دهیم.

ثابت آرایه‌ای افقی

«ثابت آرایه‌ای افقی» (Horizontal Array Constant) مجموعه‌ای از مقادیر غیر قابل تغییر هستند که در یک ردیف از جدول اکسل قرار می‌گیرند. برای وارد کردن این نوع آرایه در اکسل مراحل زیر را انجام می‌دهیم.

• ابتدا به اندازه تعداد مقادیر آرایه، چند سلول خالی (به‌عنوان مثال چهار سلول) را انتخاب می‌کنیم.
• در نوار فرمول عبارت متناسب با مقادیر را داخل آکولاد می‌نویسیم و هر مقدار را با علامت ویرگول , جدا می‌کنیم.
• کلیدهای Ctrl + Shift + Enter را فشار می‌دهیم. در این صورت یک علامت آکولاد اضافی در اطراف آکولاد اول قرار می‌گیرد.

ثابت آرایه‌ای عمودی

«ثابت آرایه‌ای عمودی» (Vertical Array Constant) مجموعه‌ای از مقادیر غیر قابل تغییر هستند که در یک ستون از جدول اکسل قرار می‌گیرند. مراحل وارد کردن این نوع آرایه در اکسل مانند ثابت افقی است، با این تفاوت که به‌جای علامت ویرگول از علامت نقطه ویرگول ; برای جدا کردن مقادیر استفاده می‌کنیم. نمونه‌ای از ثابت‌های آرایه‌ای عمودی در تصویر زیر مشخص هستند.

ثابت آرایه‌ای دو بعدی

«ثابت آرایه‌ای دو بعدی» (Two-Dimensional Array Constant) مجموعه‌ای از مقادیر غیر قابل تغییر هستند که به‌صورت افقی و عمودی در جدول اکسل قرار می‌گیرند. برای وارد کردن ثابت آرایه‌ای دو بعدی در اکسل، مقادیر افقی با علامت ویرگول و مقادیر عمودی با علامت نقطه ویرگول از یکدیگر جدا می‌شوند. در تصویر زیر نمونه‌ای از این آرایه مشخص است.

چند مثال از کاربرد ثابت‌های آرایه‌ای در اکسل

ممکن است درک این مفاهیم در ابتدا مشکل به‌نظر برسد، اما در ادامه با تعریف چند مثال کاربرد جذاب این ثابت‌ها را یاد می‌گیریم.

مثال ۱. محاسبه مجموع N عدد بزرگ‌تر یا کوچک‌تر در یک محدوده سلول

در جدول فرضی زیر می‌خواهیم مجموع سه عدد بزرگ‌تر در محدوده سلول‌های A1 تا A7 را پیدا کنیم. از آنجا که اعداد جدول در یک ستون قرار دارند، بنابراین از ثابت آرایه‌ای عمودی استفاده می‌کنیم. همچنین با توجه به تعداد اعداد مورد نیاز (سه عدد) شکل کلی این ثابت آرایه‌ای {1;2;3} خواهد بود. بنابراین برای محاسبه مجموع سه عدد بزرگ‌تر در این جدول با استفاده از تابع LARGE و تابع SUM فرمول به شکل زیر در می‌آید.

=SUM(LARGE(range, {1;2;3}))

بعد از فشردن همزمان کلیدهای Ctrl + Shift + Enter نتیجه به‌ شکل زیر خواهد بود.

به همین ترتیب برای محاسبه مجموع سه عدد کوچک‌تر جدول فرمول =SUM(SMALL(range, {1;2;3})) را می‌نویسیم.

البته با این نوع فرمول‌نویسی می‌توانیم محاسبات مختلفی را با سایر توابع اکسل نیز انجام دهیم. به‌عنوان مثال برای محاسبه میانگین سه عدد بزرگ‌تر یا کوچک‌تر جدول دو فرمول زیر با تابع AVERAGE را می‌نویسیم.

=AVERAGE(LARGE(range, {1;2;3}))

در مطلب زیر از مجله فرادرس نکات لازم برای فرمول‌نویسی با تابع AVERAGE را توضیح داده‌ایم.

مثال ۲. شمارش سلول‌ها همراه با چند شرط

فرض می‌کنیم فهرستی از سفارش‌های مشتریان و فروشندگان یک محصول مشخص را مطابق جدول زیر داریم.

برای دانستن اینکه یک فروشنده مشخص، محصول معینی را چند بار فروخته است، استفاده از تابع COUNTIFS ساده‌ترین راه خواهد بود.

اما اگر شرط دوم یعنی تعداد محصولات معین را از یک عدد بیشتر کنیم، استفاده از این تابع پیچیده و زمان‌بر است. به‌همین دلیل برای سریع‌تر کردن محاسبه ترکیبی از تابع COUNTIFS، تابع SUM و یک ثابت آرایه‌ای را در قالب فرمول کلی زیر استفاده می‌کنیم.

=SUM(COUNTIFS(range1, "criteria1", range2, {"criteria1", "criteria2"}))

در این فرمول عبارت قرار گرفته در آکولاد یعنی {“criteria1”, “criteria2”} ثابت‌های آرایه‌ای افقی هستند و مقادیری بدون تغییر دارند.

حال برای محاسبه اینکه فروشنده‌ای به نام «علی» دو محصول «سیب» و «لیمو» را چند بار فروخته است، فرمول به‌صورت زیر در می‌آید.

=SUM(COUNTIFS(C2:C9, "علی", B2:B9, {"سیب", "لیمو"}))

در این فرمول ابتدا تابع COUNIFS در دو مرحله جداگانه، تعداد محصول «سیب» و «لیمو» فروخته شده توسط «علی» را محاسبه می‌کند. بعد از مشخص شدن مقدار عددی، تابع SUM مجموع آن‌ها را به‌عنوان خروجی مطابق تصویر زیر نمایش می‌دهد.

ترفندهای کار با ثابت‌های آرایه‌ای

هنگام استفاده از ثابت‌های آرایه‌ای اجرای ترفندهای زیر بسیار کمک می‌کند.

نام‌گذاری ثابت‌های آرایه‌ای

برخی مواقع ممکن است یک ثابت‌ آرایه‌ای مشخص را به تعداد دفعات بالا در فرمول‌های مختلف استفاده کنیم. در این حالت بهترین کار برای جلوگیری از تعریف هر باره ثابت آرایه‌ای، نام‌‌گذاری و ذخیره کردن آن در اکسل است. به این شکل ثابت آرایه‌ای در هر زمان به‌راحتی در دسترس خواهد بود. البته این کار علاوه بر افزایش خوانایی فرمول، خطا در فرمول‌نویسی را نیز کاهش می‌دهد.

نام‌گذاری ثابت آرایه‌ای در اکسل در مراحل زیر انجام می‌گیرد.

۱. در زبانه «Formulas» و بخش «Defined Names» روی گزینه «Define Name» کلیک می‌کنیم. البته این کار با استفاده از کلید میانبر Ctrl + F3 و کلیک روی دکمه «New» هم قابل انجام است.

۲. در قسمت «Name» نام دلخواه خود را وارد می‌کنیم. سپس در قسمت «Refers to» مقدار ثابت آرایه‌ای را با گذاشتن علامت = در ابتدا و سپس آکولاد {} وارد می‌کنیم.

۳. روی دکمه «Ok» کلیک می‌کنیم تا ثابت آرایه‌ای ذخیره شود.

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

جلوگیری از ایجاد خطا

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

• استفاده از جداکننده صحیح بین مقادیر آرایه به‌عنوان مثال ویرگول برای آرایه افقی یا نقطه ویرگول برای آرایه عمودی را بررسی کنیم.
• اگر مقادیر ثابت آرایه‌ای از نوع متن است، قرار داشتن صحیح هر یک از متن‌ها داخل گیومه را بررسی کنیم.
• یکسان بودن تعداد سلول‌های انتخاب شده در صفحه اکسل را با تعداد اجزای آرایه بررسی کنیم. در این حالت اگر تعداد سلول‌های انتخاب شده بیشتر از اجزای آرایه باشد، در سلول‌های اضافی پیام خطای #N/A را می‌بینیم. همچنین اگر تعداد سلول کمتری را انتخاب کنیم، فقط یک بخش از آرایه در آن نمایش داده خواهد شد.

استفاده از عملگرهای آرایه‌ای برای فرمول نویسی آرایه ای در اکسل

در اکسل از سه عملگر AND ،OR و «دو منفی» برای فرمول‌نویسی آرایه‌ای استفاده می‌کنیم. عملگرهای آرایه‌ای AND و OR برقراری شرط‌های مختلف در فرمول را هدایت می‌کنند. از عملگر AND در فرمول‌ نویسی آرایه ای در اکسل با علامت * زمانی استفاده می‌کنیم که در خروجی فرمول نیاز به برقراری تمام شروط داشته باشیم. به‌همین ترتیب عملگر OR با علامت + در زمان نیاز به برقراری تنها یک شرط کاربرد دارد. عملگر دو منفی — هم وظیفه تبدیل عبارت‌های منطقی به اعداد معادل آن‌ها را در فرمول به‌عهده دارد.

در ادامه بحث هر یک از این موارد را با مثال توضیح می‌دهیم.

فرمول‌ نویسی آرایه ای در اکسل با عملگر AND

در جدول زیر میزان فروش افراد مختلف بر حسب محصول آورده شده است.

حال اگر بخواهیم مجموع فروش محصول «سیب» فروشنده‌ای به نام «علی» را محاسبه کنیم، فرمول به شکل زیر در می‌آید.

=SUM((A2:A9="علی") * (B2:B9="سیب") * (C2:C9))

البته می‌توانیم با استفاده از تابع IF نیز فرمول را به شکل زیر بنویسیم.

=SUM(IF(((A2:A9="علی") * (B2:B9="سیب")) * (C2:C9)))

در این فرمول‌ها تنها در صورت درست بودن هر دو شرط یعنی پیدا شدن فروشنده‌ای به‌نام «علی» در سلول‌های A2 تا A9 و محصول «سیب» در سلول‌های B2 تا B9 مجموع فروش را به‌عنوان نتیجه محاسبه می‌بینیم. همچنین علامت * نماینده عملگر AND است.

فرمول نویسی آرایه ای در اکسل با عملگر OR

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

=SUM(IF(((A2:A9="علی") + (B2:B9="سیب")), (C2:C9)))

بنابراین نتیجه محاسبه به‌شکل زیر خواهد بود.

در این فرمول اگر حداقل یکی از شرط‌ها برقرار باشد، عملگر OR عبارت «TRUE» یا مقدار «۱» را بر می‌گرداند. اما اگر همه شرط‌ها نادرست باشند، مقدار «صفر» خواهد بود. سپس تابع IF در صورت تشخیص درست بودن مقدار شرط، دستور قرار دادن آن در مجموع نهایی را به تابع SUM می‌دهد.

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

فرمول نویسی آرایه ای در اکسل با عملگر دو منفی

«عملگر دو منفی» یا «دابل منفی» (Double Unray Operator) یک عملگر خاص در فرمول‌نویسی آرایه ای اکسل است که مانند نام خود با دو علامت منفی پشت سر هم — در فرمول نشان داده می‌شود. کار اصلی این عملگر تبدیل کردن عبارت‌های منطقی «TRUE» و «FALSE» در نتیجه فرمول به مقادیر عددی معادل آن‌ها یعنی «۱» و «۰» است. برای درک بهتر موضوع یک مثال از کاربرد این عملگر را بررسی می‌کنیم.

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

=SUM(--(MONTH(A2:A7)=D2))

و نتیجه نهایی به‌صورت زیر خواهد بود.

در این فرمول ابتدا تابع MONTH شماره ماه همه تاریخ‌های قرار گرفته در سلول A2 تا A7 را به شکل یک آرایه باز می‌گرداند. سپس این آرایه را با مقدار عددی سلول D2 مقایسه می‌کند. نتیجه حاصل، آرایه‌ای شامل مقادیر منطقی «TRUE» و «FALSE» است. با انتخاب بخشی از فرمول و فشردن دکمه F9 می‌توانیم این آرایه را در صفحه اکسل مشاهده کنیم.

در این مرحله، عملگر دو منفی هر یک از مقادیر منطقی تولید شده را به عدد معادل آن‌ها یعنی «۰» و «۱» تبدیل می‌کند. در نهایت تابع SUM با جمع کردن این مقادیر عددی، نتیجه نهایی فرمول را نشان می‌دهد. اگر از عملگر — در فرمول استفاده نکنیم، تابع SUM نمی‌تواند مقدار عددی مربوط به عبارت‌های «TRUE» و «FALSE» را تشخیص دهد و پیام خطای محاسبه خواهیم داشت.

جمع‌بندی پایانی

فرمول‌‌های آرایه‌ای روشی سریع برای انجام محاسبه‌های پیچیده و تعداد زیاد داده در اکسل هستند. در این روش می‌توانیم با استفاده از انواع توابع، ثابت‌ها و عملگرهای آرایه‌ای محاسباتی جذاب انجام دهیم که در حالت عادی قابل انجام نیست. در این مطلب از مجله فرادرس نحوه فرمول نویسی آرایه ای در اکسل به دو روش تک سلولی و چند سلولی را یاد گرفتیم. همچنین در مثال‌های مختلف با عملگرهای آرایه‌ای AND و OR و سه نوع ثابت آرایه‌ای آشنا شدیم. نکات مهم و قابل توجه بیان شده در هر بخش از مطلب راهنمای خوبی برای جلوگیری از پیام‌های خطا و اشتباه محاسباتی است. با این حال تکمیل فرایند آموزشی مهارت فرمول‌نویسی در اکسل با توابع مسیر ضروری برای استفاده بهتر از تمام قابلیت‌های آن خواهد بود.