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

فرمول نویسی آرایه ای فرمول نویسی حرفه ای

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

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

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

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

بخش اول: توابعی که ذاتا آرایه ای هستن

برخی توابع در اکسل وجود دارن که ذاتا آرایه ای هستن. چند تابعی که بیشتر نسبتا بیشتر مورد استفاده قرا میگیرن رو در ادامه معرفی میکنم:

MMult: تابع ضرب ماتریس (تعداد سطر ماتریس اول باید با تعداد ستون ماتریس دوم برابر باشه)

Minverse: تابع معکوس کردن ماتریس (ماتریس حتما باید مربعی باشه)

Transpose: تابع ترانهاده کردن

Frequency: تابع محاسبه فراوانی

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

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

بخش دوم: ترکیب توابع معمولی با منطق آرایه ای

این بخش تابع خاص و ویژه ای نداره. استفاده از همون توابع معمولی اکسل مثل Sum, Index,small و … اما با منطق آرایه ای هست. تسلط به این بخش قطعا شما رو به سمت درک بهتر توابع، فرمول نویسی در اکسل و حرفه ای شدن پیش می بره.

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

همونطور که میدونید تابع Sumproduct از ورژن ۲۰۰۷ به بعد اضافه شده و قبل از اون همچین تابعی وجود نداشته. تابع Sumproduct اول داده ها رو نظیر به نظیر در هم ضرب میکنه و بعد با هم جمع میکنه. حالا فرض کنید تعداد فروش و مبلغ مربوط به هر کدوم رو داریم. حالا میخوایم مجموع فروش رو محاسبه کنیم. با ۳ روش این کار رو انجام میدیم:

روش اول: تابع Sumproduct

شکل ۱- محاسبه مجموع فروش با تابع Sumproduct

روش دوم: فرمول نویسی معمولی

اگر بخوایم بدون استفاده از تابع Sumproduct مجموع فروش رو حساب کنیم، باید یک ستون کمکی داشته باشیم که اول داده ها رو در هم ضرب کنه و بعد با هم جمع بزنه.

شکل ۲- محاسبه مجموع فروش با ستون کمکی

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

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

شکل ۳- محاسبه مجموع فروش بصورت آرایه ای

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

همونطور که میبینید، عملیات ضرب نظیر به نظیر داده ها در یک آرایه و در دل خود فرمول انجام میشه. بعد عملیات جمع روی داده های ضرب شده انجام میشه. در واقع تابع Sumproduct  منطق آرایه ای داره و طی دو مرحله محاسبات رو انجام میده و معادل فرمول (Sum(A2:A14*B2:B14 عمل میکنه.

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

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

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

[esd id=”4″ link=”https://dl.dropboxusercontent.com/s/mi2emasbtudzl59/Array%20Formula.xlsx?dl=0″ subject=”لینک دانلود فایل فرمول نویسی آرایه ای” btntext=”دریافت فایل” type=”mail_type” theme=”theme_d” mailer_group=”default” position=”right” icon=”fa fa-download” description=”برای دانلود فایل کلیک کنید”]