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

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

بچه‌ها تابع SUMPRODUCT یک تابع آرایه‌ای است 🙂 مطالعه مقاله

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

با نام و یاد خداوند بخشنده و مهربان در این مقاله آموزشی قصد داریم پیرامون نحوه فرمول نویسی از نوع آرایه ای در اکسل اطلاعات مفیدی را خدمت شما عزیزان و دنبال کنندگان محترم سایت آموزشی اکسل لرن ارائه کنیم. قبل از اینکه اقدام به آموزش فرمول نویسی از نوع آرایه ای کنیم می بایست ابتدا با مفهوم آرایه شما را آشنا کنیم. با ما تا پایان این مقاله آموزشی همراه باشید.

آرایه چیست؟

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

به مثال زیر دقت کنید:

آرایه چیست؟
آرایه چیست؟

یک فرمول ساده که از ضرب دو مقدار عددی است همانند تصویر زیر خواهیم داشت:

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

آن را به سایر سلول های جدول تعمیم خواهیم داد:

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

در ادامه با استفاده از تابع MAX بزرگترین عددی که خروجی این محاسبات است را استخراج خواهیم نمود:

آرایه چیست؟
آرایه چیست؟

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

ابتدا یکایک سلول های A2:A4 را در سلول های B2:B4 بصورت نظیر به نظیر با یکدگیر ضرب نمودیم:

آرایه چیست؟
آرایه چیست؟

سپس نتیجه ی این ضرب با تابع MAX استخراج نمودیم:

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

حال تصور کنید تمام محتویات داخل مخلوط کن قرار گرفته است، کلید ترکیبی Ctrl + Shift + Enter را برای روشن کردن این مخلوط کن فشار دهید:

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

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

به مثالی دیگر دقت کنید:

آرایه چیست؟
آرایه چیست؟

در مثال فوق ما ابتدا یکایک سلول های A2:A4 را در B2:B4 ضرب نمودیم و سپس نتیجه نهایی آنها را با استفاده از تابع SUM با یکدیگر جمع کردیم.

فرمول نویسی آرایه ای عملیات فوق بصورت زیر خواهد بود:

آرایه چیست؟
آرایه چیست؟

البته فراموش نکنید که محاسبات آرایه ای با Ctrl + Shift + Enter امکان پذیر است.

به مثال دیگری دقت کنید:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

در مثال فوق قصد داریم دومین فروش بزرگ فروشنده Jaxon را استخراج کنیم لذا همانند تصویر زیر ابتدا نیاز به یک شرط داریم تا تنها فروش های این فروشنده را بصورت مجزا در یک ستون کمکی داشته باشیم:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

حال از آنجایی که تمام فروش های فروشنده Jaxon را بصورت مجزا استخراج نمودیم تنها کافی است که از تابع Large برای استخراج دومین فروش بزرگ این فروشنده همانند تصویر زیر استفاده کنیم:

 

آرایه نویسی در اکسل
آرایه نویسی در اکسل

به همین راحتی توانستیم دومین فروش بزرگ فروشنده Jaxon را که برابر با عدد 86 است استخراج کنیم.

حال اجازه دهید عملیات فوق را با فرمول نویسی آرایه ای انجام دهیم. شرطی برای محدوده A2:A11 تعریف خواهیم نمود که اگر برابر با Jaxon بود، نتیجه ی آن سلول نظیر آن در محدوده B2:B11 باشد. لذا خواهیم داشت:

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

حال در ادامه از تابع LARGE برای تعیین دومین مقدار بزرگ این خروجی استفاده نمودیم:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

با زدن کلیدهای ترکیبی Ctrl + Shift + Enter خروجی ما بصورت زیر خواهد بود:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

آیا مایل هستین پشت پرده این محاسبات عجیب را با یکدیگر مشاهده کنیم؟

ابتدا قسمت شرطی فرمول آرایه را انتخاب و سپس کلید F9 کیبورد را فشار دهید تا خروجی این بخش قابل مشاهده باشد:

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

همانطور که در تصویر فوق نیز مشاهده می کنید، خروجی آرایه ای تابع شرطی IF مقادیر عددی 15,0,0,0,0,96,86,0,0,0 است که قطعا دومین عدد بزرگ این بخش عدد 86 خواهد بود که توسط تابع LARGE استخراج و به عنوان خروجی نمایش داده می شود.

با درنظر گرفتن مثال قبل قصد داریم اینبار میانگین فروش مقادیر بیشتر از عدد 50 را محاسبه کنیم لذا ابتدا می بایست همانند مثال قبل آنها را در یک ستون کمکی بصورت مجزا تفکیک کنیم:

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

حال محاسبه میانگین کار ساده ای خواهد بود:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

در ادامه فرمول آرایه ای این بخش را مشاهده خواهید نمود که خروجی یکسانی به دنبال خواهد داشت:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

با مشاهده خروجی های تابع IF در این اقدام آرایه ای می توانید آن را همانند تصویر زیر انتخاب:

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

و کلید F9 کیبورد را فشار دهید:

آرایه نویسی در اکسل
آرایه نویسی در اکسل

حال دیگر تحلیل میانگین اعداد 75,96 و 86 کار ساده تری خواهد بود.

مثال شماره #1 - فرمول‌نویسی آرایه‌ای در اکسل
مثال شماره #2 - فرمول‌نویسی آرایه‌ای در اکسل
مثال شماره #3 - فرمول‌نویسی آرایه‌ای در اکسل

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

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

پیشنهاد ما به شما