An Excel فرمول آرایه فرمولی است که محاسباتی را بر روی مقادیر یک یا چند آرایه به جای یک مقدار داده واحد انجام می دهد. در برنامه‌های صفحه‌گسترده، آرایه یک محدوده یا یک سری از مقادیر داده مرتبط است که معمولاً در سلولهای مجاور یک کاربرگ قرار دارند.
این دستورالعمل‌ها برای Excel 2019، 2016، 2013، 2010 و Excel برای Microsoft 365 اعمال می‌شود.
فرمول های آرایه چیست؟
فرمول های آرایه مشابه فرمول های معمولی هستند. آنها:
- شروع با علامت مساوی (=)
- از نحوی مشابه فرمول های معمولی استفاده کنید
- از همان عملگرهای ریاضی استفاده کنید
- از همان ترتیب عملیات پیروی کنید
اما، فرمول های آرایه با مهاربندهای مجعد احاطه شده اند { }. و شما نمی توانید فقط آنها را تایپ کنید. شما باید آنها را با فشار دادن کلیدهای Ctrl ، Shift و Enter پس از وارد کردن فرمول در یک فرمول اضافه کنید. سلول یا سلول به همین دلیل، فرمول آرایه گاهی در اکسل فرمول CSE نامیده می شود.
هر زمان که فرمول آرایه ای را ویرایش می کنید، بریس های فرفری ناپدید می شوند. برای بازگرداندن آنها، دوباره کلیدهای Ctrl ، Shift و Enter را فشار دهید.
دو نوع اصلی فرمول آرایه وجود دارد:
- فرمول های آرایه تک سلولی که چندین محاسبات را در یک سلول کاربرگ انجام می دهد
- فرمول های آرایه چند سلولی که در بیش از یک سلول کاربرگ هستند
چگونه یک فرمول آرایه ایجاد کنیم
- فرمول را در یک سلول وارد کنید.
- کلیدهای Ctrl و Shift روی صفحه کلید را نگه دارید.
- برای ایجاد فرمول آرایه کلید Enter را فشار داده و رها کنید.
- کلیدهای Ctrl و Shift را رها کنید.
- اگر به درستی انجام شود، بریس های فرفری فرمول را احاطه می کنند.
فرمول های آرایه سلولی تک سلولی
یک فرمول آرایه سلولی از تابعی استفاده می کند، مانند SUM ، AVERAGE ، یا COUNT ، برای ترکیب خروجی یک فرمول آرایه چند سلولی در یک مقدار واحد در یک سلول واحد. در زیر یک مثال آمده است:
{=SUM(A1:A2B1:B2)}
فرمول بالا حاصلضرب A1B1 و A2B2 را با هم جمع می کند و سپس یک نتیجه را در یک تک سلولی در کاربرگ راه دیگری برای ارائه این فرمول این است:
=(A1B1)+(A2B2)
فرمول های آرایه چند سلولی
همانطور که از نام آنها پیداست، فرمول های آرایه چند سلولی در چندین سلول کاربرگ هستند و یک آرایه را به عنوان پاسخ برمی گردانند. به عبارت دیگر، فرمول یکسان در دو یا چند خانه است و در هر سلول پاسخ‌های متفاوتی را برمی‌گرداند.
هر کپی، یا نمونه ای از فرمول آرایه، در هر سلولی که در آن ساکن است، محاسبه یکسانی را انجام می دهد، اما هر کدام از داده های متفاوتی استفاده می کنند. بنابراین، هر کدام نتایج متفاوتی را به همراه دارد. مثالی از فرمول آرایه سلولی چندگانه این است:
{=A1:A2B1:B2}
اگر فرمول آرایه بالا در سلول های C1 و C2 در یک کاربرگ باشد، نتایج به شرح زیر خواهد بود:
- داده های A1 در داده های B1 ضرب می شود و نتایج در سلول C1 ظاهر می شود..
- داده های A2 در داده های B2 ضرب می شود و نتایج در سلول C2 ظاهر می شود..
فرمول‌های آرایه و توابع اکسل
می توانید از بسیاری از توابع داخلی اکسل استفاده کنید، مانند SUM ، AVERAGE ، و COUNT ، در یک فرمول آرایه. چند توابع نیز وجود دارد، مانند TRANSPOSE ، که همیشه باید یک فرمول آرایه باشد تا به درستی کار کند. (تابع TRANSPOSE داده ها را از یک ردیف در یک ستون یا برعکس کپی می کند.)
همچنین می توانید سودمندی بسیاری از توابع مانند INDEX و MATCH یا MAX را افزایش دهیدو IF با استفاده از آنها با هم در یک فرمول آرایه.
ایجاد فرمول آرایه سلولی ساده
فرمول‌های آرایه تک سلولی معمولاً ابتدا یک محاسبه چند سلولی انجام می‌دهند و سپس از تابعی مانند AVERAGE یا SUM برای ترکیب استفاده می‌کنند. خروجی آرایه به یک نتیجه واحد.
نادیده گرفتن مقادیر خطا هنگام یافتن داده
این فرمول آرایه از توابع AVERAGE ، IF و و ISNUMBER برای پیدا کردن مقدار متوسط برای داده‌های موجود در حالی که مقادیر خطا را نادیده می‌گیرید، مانند DIV/0! و NAME?
شمار سلولهای داده
از توابع SUM و IF در فرمول آرایه برای شمارش سلول های داده ای که یکی از چندین شرط را دارند استفاده کنید. این تکنیک با استفاده از تابع COUNTIFS اکسل متفاوت است، که لازم است قبل از شمارش سلول، همه شرایط تنظیم شده رعایت شوند.
بزرگترین عدد مثبت یا منفی را بیابید
این مثال تابع MAX و تابع IF را در فرمول آرایه ای ترکیب می کند که بزرگترین یا حداکثر مقدار را برای محدوده ای از داده ها زمانی که معیارهای خاصی را برآورده می کنند. در اینجا، بزرگترین مقدار نشان دهنده کندترین زمان است.
کوچکترین عدد مثبت یا منفی را پیدا کنید
مشابه مثال بالا، می توانید توابع MIN و IF را در یک فرمول آرایه ترکیب کنید تا کوچکترین یا حداقل مقدار را پیدا کنید. برای طیف وسیعی از داده ها زمانی که معیارهای خاصی را برآورده می کند.
مقدار میانی یا میانه را بیابید
عملکرد MEDIAN در اکسل مقدار وسط را برای لیستی از داده ها پیدا می کند. با ترکیب آن با تابع IF در یک فرمول آرایه، می توانید مقدار میانی را برای گروه های مختلف داده های مرتبط پیدا کنید.
یک فرمول جستجو با چند معیار ایجاد کنید
این فرمول آرایه شامل تودرتو کردن توابع MATCH و INDEX برای یافتن اطلاعات خاص در پایگاه داده است.
یک فرمول جستجوی چپ ایجاد کنید
VLOOKUP معمولاً فقط داده هایی را جستجو می کند که در ستون های سمت راست قرار دارند، اما با ترکیب آن با تابع CHOOSE ، شما می تواند یک فرمول جستجوی چپ ایجاد کند که ستون های داده را در سمت چپ آرگومان Lookup_value جستجو کند.
هنگامی که آرایه‌ای را به‌عنوان آرگومان برای تابعی وارد می‌کنید که معمولاً حاوی تنها یک مقدار یا مرجع سلول است، به جای استفاده از Ctrl+Shift+Enter می‌توانید مستقیماً در پرانتزها تایپ کنید.ترکیب کلید، مانند مثال بالا.
