فرمول نویسی آرایه ای در اکسل
همونطور که قبلا گفته شد، فرمول نویسی حرفه ای اصولی داره که حتما باید با این اصول آشنا و به اونها مسلط باشید. بعد از تسلط به این اصول و شناخت توابع مهم و کاربردی، نوبت میرسه به فرمول نویسی آرایه ای در اکسل. این نوع فرمول نویسی منطق و اصول خاص خودشو داره. با تسلط به این مفهوم و منطق، میشه گفت شما تبدیل به یک فرمول نویسی حرفه ای در اکسل شدید و میتونید درصد بیشتری از مسائل رو حل کنید.
در این مقاله به تشریح مفهوم فرمول نویسی آرایه ای در اکسل می پردازیم.
منطق فرمول نویسی آرایه ای در اکسل به این صورت هست که یک سری محاسبات رو در درون خودش انجام میشه و نیاز به سلول های کمکی رو از بین میبره. نکته مهم این نوع فرمول نویسی این هست که بعد از نوشتن فرمول، بجای Enter، باید Ctrl+Shift+Enter زده بشه در غیر اینصورت با خطا مواجه میشه.
فرمول نویسی آرایه ای به دو بخش تقسیم میشه:
بخش اول: توابعی که ذاتا آرایه ای هستن
برخی توابع در اکسل وجود دارن که ذاتا آرایه ای هستن. چند تابعی که بیشتر نسبتا بیشتر مورد استفاده قرا میگیرن رو در ادامه معرفی میکنم:
MMult: تابع ضرب ماتریس (تعداد سطر ماتریس اول باید با تعداد ستون ماتریس دوم برابر باشه)
Minverse: تابع معکوس کردن ماتریس (ماتریس حتما باید مربعی باشه)
Frequency: تابع محاسبه فراوانی
هر کدوم از این توابع شرایط استفاده خاصی دارن که در مقالات جداگانه به آنها پرداخته شده است.
این توابع عموما به این صورت کار میکنند که اول باید محدوده جواب آنها انتخاب بشه و بعد فرمول نویسی انجام بشه. و این دسته توابع چون یک مجموعه جواب دارن، امکان حذف یک سلول از محدوده جواب وجود نداره و اگر اقدام به حذف کنیم با این خطا روبرو می شیم.
بخش دوم: ترکیب توابع معمولی با منطق آرایه ای
این بخش تابع خاص و ویژه ای نداره. استفاده از همون توابع معمولی اکسل مثل Sum, Index,small و … اما با منطق آرایه ای هست. تسلط به این بخش قطعا شما رو به سمت درک بهتر توابع، فرمول نویسی در اکسل و حرفه ای شدن پیش می بره.
یک مثال ساده از فرمول نویسی آرایه ای میزنم برای اینکه منطق و تفاوتش با فمرول نویسی معمولی رو متوجه بشید.
همونطور که میدونید تابع Sumproduct از ورژن 2007 به بعد اضافه شده و قبل از اون همچین تابعی وجود نداشته. تابع Sumproduct اول داده ها رو نظیر به نظیر در هم ضرب میکنه و بعد با هم جمع میکنه. حالا فرض کنید تعداد فروش و مبلغ مربوط به هر کدوم رو داریم. حالا میخوایم مجموع فروش رو محاسبه کنیم. با 3 روش این کار رو انجام میدیم:
روش اول: تابع Sumproduct
شکل 1- محاسبه مجموع فروش با تابع Sumproduct
روش دوم: فرمول نویسی معمولی
اگر بخوایم بدون استفاده از تابع Sumproduct مجموع فروش رو حساب کنیم، باید یک ستون کمکی داشته باشیم که اول داده ها رو در هم ضرب کنه و بعد با هم جمع بزنه.
شکل 2- محاسبه مجموع فروش با ستون کمکی
روش سوم: فرمول نویسی آرایه ای
حالا میخوایم بدون ستون کمکی و بدون تابع استفاده از تابع Sumproduct این مجموع رو حساب کنیم.
شکل 3- محاسبه مجموع فروش بصورت آرایه ای
همونطور که در شکل 3 می بینید، مجموع فروش رو با استفاده از تابع Sum و بدون ستون کمکی و بصورت آرایه ای محاسبه کردیم. نحوه عملکرد این تابع رو در تصویر زیر می بینید:
همونطور که میبینید، عملیات ضرب نظیر به نظیر داده ها در یک آرایه و در دل خود فرمول انجام میشه. بعد عملیات جمع روی داده های ضرب شده انجام میشه. در واقع تابع Sumproduct منطق آرایه ای داره و طی دو مرحله محاسبات رو انجام میده و معادل فرمول (Sum(A2:A14*B2:B14 عمل میکنه.
نشانه ظاهری فرمول های آرایه ای علامت { } هست که نباید تایپ بشه. بلکه به محض زدن کلید ترکیبی Ctrl+Shift+Enter این علامت ابتدا و انتهای فرمول اضافه میشه.
با درک منطق و مفهوم فرمول نویسی آرایه ای در اکسل ، میتونید مطالعه مباحث فرمول نویسی آرایه ای در اکسل رو شروع کنید. بسیار مبحث مفصلی هست و نیاز به مطالعه و تمرین و فکر زیاد داره تا بتونید کاملا مسلط بشید. اگر میخواید در اکسل حرفه ای بشید حتما روی این مبحث وقت بذارید.
دانلود فایل اکسل این آموزش
برای دانلود فایل اکسل این آموزش روی لینک زیر کلیک کنید.
ممکن است همچنین دوست داشته باشید
آموزش کار با تابع Replace
تابع Mid و چند کاربرد در اکسل
9 نظر
سلام خسته نباشید
من یک لیست از نفرات و نمره ها رو دارم و میخوام رتبه بندی نفراتی که بالاترین نمره رو میگیرند همیشه بصورت خودکار انجام بشه که مشکلی خوردم که ظاهرا با آرایه کردن فرمول حل میشه. مثلا این جدول رو دارم:
نام نمره
علی 40
حسن 30
محمد 40
محسن 52
با فرمول Large کلیه نمرات به ترتیب مرتب میشه
با ترکیب فرمول MATCH+INDEX هم نفری که نمره متعلق به اون هست پیدا میشه
فقط مشکل اینجاست که نفراتی که نمره مشابه دارند، نام اولین نفری که پیدامیکنه رو برای نفر بعدی و بعدی ها هم تکرار میکنه!
یعنی بعد از استفاده از فرمول، نتیجه اینطوری میاد:
محسن 52
علی 40
علی 40
حسن 30
یعنی اسم محمد رو که نمره 40 داره نمیاره. اگر امکان داره یکی از اساتید فرمول رو اصلاح کنند:
=INDEX($A$2:B13,MATCH(F3,$B$2:B15,0),1)
باتشکر
درود
بله تکرار میکنه چون ذات تابع match همینه
برای این کار باید یک مقدار خیلی کم، در حد 0.000001 به نمره هاتون اضافه کنید که منحصر بفرد باشه. مثلا row()*0.000001
هم اثری روی مقدار نمره نداره هم منحصر بفرده و میتونید جستجو کنید
اما این نمره ها معدل نفرات هست و من در تعیین شون نقش ندارم و ممکنه واقعا دو سه نفر دقیقا نمره یکسانی بگیرند!! با آرایه یا IF چطور میشه درستش کرد؟
توضیحی که دادم رو دقت نکردید
من نگفتم نباید یکی باشه
خیلی وقت ها پیش میاد که اعداد مشابه باشن
عرض کردم باید ترتیبی بدید که اعداد از هم تفکیک بشن. اونم با اضافه کردن یک مقدار خیلی خیلی کوچیک که در ارزش اون مقدار تغییری ایجاد نکنه. فقط فرمول بتونه تفکیک کنه.
یا اینکه در یک ستون کمکی رتبه بندی انجام بدید با rank و برای رتبه های یکسان با countif داده ها رو اضافه کنید به رتبه که تفکیک بشن از هم. بعد رتبه ها رو شروع کنید vlookup کردن. اینطوری رتبه ها 1 تا n شما جستجو میشن و مقادیر بصورت مرتب چیده میشن.
=rank(A1,$A$1:$A$30)+countif($A$1:A1,A1)
سلام. چگونه پس از انتخاب از دو کامبو باکس که ابعاد ماتریس را مشخص میکنند می توان ماتریس ایجاد شود . که در ادامه بتوانیم عملیاتی روی ماتریس انجام دهیم
سلام
اگر منظورتون از ایجاد ماتریس:
اگر ایجاد Table با ابعادی که انتخاب شده هست، باید از VBA استفاده بشه.
اگر تعریف محدوده ای نامگذاری شده با ابعاد انتخابی هست، هم با VBA میشه و هم با استفاده از فرمول نویسی در Name
سلام
خسته نباشید
یک فایل اکسل دارم با 2 شیت که برای هر دو شیت ستون اول شماره درخواست،ستون دوم کد کالا و ستون سوم تعداد درخواست می باشد که ممکن است برای یک شماره درخواست یکسان چندین کد مختلف با تعداد متفاوت ثبت شده باشد
سوال:
اگر بخوام از شیت a ردیفی که شماره درخواست 1 با کد 100 ثبت شده مقدار درخواست را از شماره درخواست 1 با کد 100 از شیت b بردارم چیکار کنم
این را مدنظر داشته باشید احتمال این که برای مثال درخواست 1 شامل چند کد در ردیفهای مختلف باشد هست
ممنون
سلام
اگر فقط قصد شمردن تعداد ردیف های مورد نظر در شیت B هست از تابع Countifs استفاده کنید.
اگر قصد دارید کل ردیف های شیت B رو ببینید (یکجوری فیلتر حساب میشه) بهتره از Pivot Table استفاده کنید.
عالی بود. مرسی