در این پست روش کار فرمول های آرایه ای در اکسل را با ذکر چند مثال خدمت شما عزیزان تقدیم می کنم.
ابتدا یک مثال را بدون استفاده از فرمول های آرایه ای انجام داده و سپس همین مثال را با استفاده از فرمول های آرایه ای انجام می دهیم.
بدون استفاده از فرمول های آرایه ای
با توجه به داده های جدول زیر، بدون استفاده از فرمول های آرایه ای، مراحل زیر را برای پیدا کردن بیشترین پیشرفت در نمرات طی می کنیم.
- در گام اول، باید میزان پیشرفت برای هر دانشجو را محاسبه کنیم
فرمول های آرایه ای
- در گام دوم با استفاده از تابع Max بیشترین پیشرفت را پیدا می کنیم.
با استفاده از فرمول های آرایه ای
با استفاده از فرمول های آرایه ای نیازی نیست که ستون کمکی D را ایجاد کنیم. اکسل می تواند این محدوده را در حافظه خود ذخیره کند. محدوده ذخیره شده در حافظه اکسل ثابت آرایه نامیده می شود.
- از قبل می دانیم که می توانیم با استفاده از فرمول زیر پیشرفت دانش آموز اول را پیدا کنیم
- برای پیدا کردن بیشترین پیشرفت ، از تابع MAX استفاده می کنیم ،در فرمول بالا C2 را با C2: C6 و B2 را با B2: B6 جایگزین می کنیم.
- در پایان به جای Enter کلیدهای ترکیبی Ctrl+Shift+Enter را فشار می دهیم.
توجه: همانطور که در نوار فرمول می بینید، در ابتدا و انتهای فرمول علائم { } ظاهر شده اند که نشان دهنده فرمول آرایه ای هستند. این علائم را خودتان تایپ نکنید.
در واقع هنگام فشردن کلیدهای ترکیبی Ctrl+Shift+Enter در پایان نوشتن فرمول، این علامت ها ایجاد می شوند و هنگام ویرایش فرمول نیز ناپدید می شوند.
توضیح: دامنه (ثابت آرایه ای) در حافظه اکسل ذخیره می شود نه در یک محدوده. ثابت آرایه ای به شرح زیر است:
{19;33;63;48;13}
این ثابت آرایه ای به عنوان آرگومان (ورودی) برای تابع MAX استفاده می شود و نتیجه 63 را بر می گرداند.
کلید F9 (نمایش نتیجه قسمت انتخاب شده در فرمول)
هنگام کار با فرمول های آرایه ، می توانید خودتان به این ثابت های آرایه نگاهی بیندازید.
- ناحیه C2: C6-B2: B6 را در فرمول انتخاب کنید
- کلید F9 را فشار دهید
- نتیجه به شکل ثابت آرایه ای زیر ظاهر می گردد.
{19;33;63;48;13}
تذکر: اگر کلید F9 نتیجه را نمایش نداد از کلیدهای ترکیبی زیر استفاده کنید
Ctrl+Alt+F9
عناصر موجود در یک آرایه افقی با سمیکولون از هم جدا می شوند.
مثال دوم
شمارش تعداد سلول های دارای خطا
این مثال نحوه ایجاد فرمول آرایه ای را نشان می دهد که تعداد خطاها را در یک محدوده شمارش می کند.
- برای بررسی خطا از توابع IF و ISERROR استفاده می کنیم.
توضیح: اگر خطایی پیدا شود ، تابع مقدار 1 را برمی گرداند. در غیر این صورت ، یک رشته خالی را برمی گرداند.
- برای شمارش خطاها تابع COUNT را به فرمول مرحله اول اضافه می کنیم و محدوده A1 را با A1: C3 جایگزین می کنیم.
- کلیدهای ترکیبی Ctrl+Shift+Enter را فشار می دهیم
برای مطالعه بیشتر در مورد توابع شمارشی اکسل اینجا را کلیک کنید
توجه: همانطور که قبلاً در مثال اول بیان کردیم در نوار فرمول در ابتدا و انتهای فرمول علائم { } ظاهر شده اند که نشان دهنده فرمول آرایه ای هستند.
توضیح: دامنه (ثابت آرایه) ایجاد شده توسط تابع IF در حافظه اکسل ذخیره می شود ، نه در یک محدوده. ثابت آرایه به شرح زیر است:
{1,””,1;””,””,””;””,””,1}
برای دیدن ثابت آرایه ای در نوار فرمول ورودی تابع IF را انتخاب کرده و کلید F9 را فشار دهید. (در صورت عدم کارکرد کلید F9 از کلیدهای ترکیبی Ctrl+Alt+F9 استفاده کنید)
این ثابت آرایه به عنوان آرگومان برای تابع COUNT استفاده می شود و نتیجه 3 را بر می گرداند.
برای شمارش خطاهای مشخص ، از تابع COUNTIF استفاده می کنیم. به عنوان مثال تعداد سلولهایی را که حاوی # DIV / 0 هستند را با استفاده از تابع COUNTIF محاسبه می کنیم.
شمارش خطا با استفاده از توابع شرطی
برای مطالعه سایر توابع آرایه ای به اینجا بروید
ثبت ديدگاه