فرمول نویسی آرایه ای در اکسل
فرمول نویسی آرایه ای فرمول نویسی حرفه ای
همونطور که قبلا گفته شد، فرمول نویسی حرفه ای اصولی داره که حتما باید با این اصول آشنا و به اونها مسلط باشید. بعد از تسلط به این اصول و شناخت توابع مهم و کاربردی، نوبت میرسه به فرمول نویسی آرایه ای در اکسل. این نوع فرمول نویسی منطق و اصول خاص خودشو داره. با تسلط به این مفهوم و منطق، میشه گفت شما تبدیل به یک فرمول نویسی حرفه ای در اکسل شدید و میتونید درصد بیشتری از مسائل رو حل کنید.
در این مقاله به تشریح مفهوم فرمول نویسی آرایه ای در اکسل می پردازیم.
منطق فرمول نویسی آرایه ای در اکسل به این صورت هست که یک سری محاسبات رو در درون خودش انجام میشه و نیاز به سلول های کمکی رو از بین میبره. نکته مهم این نوع فرمول نویسی این هست که بعد از نوشتن فرمول، بجای Enter، باید Ctrl+Shift+Enter زده بشه در غیر اینصورت با خطا مواجه میشه.
فرمول نویسی آرایه ای به دو بخش تقسیم میشه:
بخش اول: توابعی که ذاتا آرایه ای هستن
برخی توابع در اکسل وجود دارن که ذاتا آرایه ای هستن. چند تابعی که بیشتر نسبتا بیشتر مورد استفاده قرا میگیرن رو در ادامه معرفی میکنم:
MMult: تابع ضرب ماتریس (تعداد سطر ماتریس اول باید با تعداد ستون ماتریس دوم برابر باشه)
Minverse: تابع معکوس کردن ماتریس (ماتریس حتما باید مربعی باشه)
Frequency: تابع محاسبه فراوانی
هر کدوم از این توابع شرایط استفاده خاصی دارن که در مقالات جداگانه به آنها پرداخته شده است.
این توابع عموما به این صورت کار میکنند که اول باید محدوده جواب آنها انتخاب بشه و بعد فرمول نویسی انجام بشه. و این دسته توابع چون یک مجموعه جواب دارن، امکان حذف یک سلول از محدوده جواب وجود نداره و اگر اقدام به حذف کنیم با این خطا روبرو می شیم.
بخش دوم: ترکیب توابع معمولی با منطق آرایه ای
این بخش تابع خاص و ویژه ای نداره. استفاده از همون توابع معمولی اکسل مثل Sum, Index,small و … اما با منطق آرایه ای هست. تسلط به این بخش قطعا شما رو به سمت درک بهتر توابع، فرمول نویسی در اکسل و حرفه ای شدن پیش می بره.
یک مثال ساده از فرمول نویسی آرایه ای میزنم برای اینکه منطق و تفاوتش با فمرول نویسی معمولی رو متوجه بشید.
همونطور که میدونید تابع Sumproduct از ورژن ۲۰۰۷ به بعد اضافه شده و قبل از اون همچین تابعی وجود نداشته. تابع Sumproduct اول داده ها رو نظیر به نظیر در هم ضرب میکنه و بعد با هم جمع میکنه. حالا فرض کنید تعداد فروش و مبلغ مربوط به هر کدوم رو داریم. حالا میخوایم مجموع فروش رو محاسبه کنیم. با ۳ روش این کار رو انجام میدیم:
روش اول: تابع Sumproduct
شکل ۱- محاسبه مجموع فروش با تابع Sumproduct
روش دوم: فرمول نویسی معمولی
اگر بخوایم بدون استفاده از تابع Sumproduct مجموع فروش رو حساب کنیم، باید یک ستون کمکی داشته باشیم که اول داده ها رو در هم ضرب کنه و بعد با هم جمع بزنه.
شکل ۲- محاسبه مجموع فروش با ستون کمکی
روش سوم: فرمول نویسی آرایه ای
حالا میخوایم بدون ستون کمکی و بدون تابع استفاده از تابع Sumproduct این مجموع رو حساب کنیم.
شکل ۳- محاسبه مجموع فروش بصورت آرایه ای
همونطور که در شکل ۳ می بینید، مجموع فروش رو با استفاده از تابع Sum و بدون ستون کمکی و بصورت آرایه ای محاسبه کردیم. نحوه عملکرد این تابع رو در تصویر زیر می بینید:
همونطور که میبینید، عملیات ضرب نظیر به نظیر داده ها در یک آرایه و در دل خود فرمول انجام میشه. بعد عملیات جمع روی داده های ضرب شده انجام میشه. در واقع تابع Sumproduct منطق آرایه ای داره و طی دو مرحله محاسبات رو انجام میده و معادل فرمول (Sum(A2:A14*B2:B14 عمل میکنه.
نشانه ظاهری فرمول های آرایه ای علامت { } هست که نباید تایپ بشه. بلکه به محض زدن کلید ترکیبی Ctrl+Shift+Enter این علامت ابتدا و انتهای فرمول اضافه میشه.
با درک منطق و مفهوم فرمول نویسی آرایه ای در اکسل ، میتونید مطالعه مباحث فرمول نویسی آرایه ای در اکسل رو شروع کنید. بسیار مبحث مفصلی هست و نیاز به مطالعه و تمرین و فکر زیاد داره تا بتونید کاملا مسلط بشید. اگر میخواید در اکسل حرفه ای بشید حتما روی این مبحث وقت بذارید.
دانلود فایل اکسل این آموزش
برای دانلود فایل اکسل این آموزش روی لینک زیر کلیک کنید.
یک سوال داشتم ، اگر یک سری داده داشته باشم ، ۱۰۰ ستون داشته باشه ، بعد سه تا سه تا باید اینا باهم جمع بشند، چطور میتونم فرمول بنویسم که نخوام برای هر سه تا دسته یک فرمول جداگانه بنویسم
با ترکیب offset و ساخت اعداد (اگه ۲۰۲۱ دارید تابع sequence) اگه نه با column / row و ضریب ۳
میتونید انجام بددی
سلام
یک سوال داشتم چطور یک ستون رو به صورت اتوماتیک بنویسم که درایه های ان
(۱)aتا a(1000)باشد
درود
راه های مختلف داره
یکیش اینه:
=”a(“&row(A1)&”)”
عرض سلام ودرود خدمت استاد ارجمند خانم خاکزاد عزیز…..
جسارت نباشه،تو بخش فرمول نویسی آرایه ای که تو مقالات سایت ارایه شده وهمینطور فایل ویدیویی در محصولاتی که خریدم ،تعریف ضرب دوماتریس فکرکنم اشتباه تعریف شده..در ضرب دوماتریس باید تعداد ستونهای ماتریس اول با تعداد سطرهای ماتریس دوم برابر باشه…در مقالات مربوطه گفته شده:
( MMult: تابع ضرب ماتریس (تعداد سطر ماتریس اول باید با تعداد ستون ماتریس دوم برابر باشه)
درود بر شما
ممنون از شما
بله دقیقا درسته
در واقع منظور خروجی بوده (که محدوده خروجی مد نظر باید تعداد سطر اولی و تعداد ستون دومی باشه)
اما چشم اصلاح میشه
سلام و روز بخیر سرکار خانم
جدولی دارم که مربوط به مرور حقوق پرسنل در ماههای مختلف با سرستون های زیاد که شامل ستون “سال/ماه” – ستون “جمع کل مزایا” یا “مأموریت” و …برای هر کدام از پرسنل است در هر سال/ماه مشخصی است
اگر بخواهم جمع کلی مقادیر بعنوان مثال ” جمع کل حق اولاد ” یا “حقوق پایه ” برای تمامی پرسنل در یک “سال/ماه” مشخص رو بدست بیارم از چه دستوراتی استفاده باید بشه؟
ممنون میشم راهنمایی بفرمایید.
تشکر
درود بر شما
خیلی بستگی به نحوه چیدمان داده ها داره
اما بصورت کلی احتمالا با sumif نتیجه میگیرید
با سلام وا حترام
من در اکسل یک فرمول sumifs نوشتم و بعد از ران شدن هیچ خطایی را نمیدهد فقط اعداد را با هم جمع نزده و صفر نشان مdدهد در صورتیکه مثلا طبق فرمول جمع اعداد می بایست ۵۰۰ شود. سپاسگزار خواهم شد راهنمایی فرمایید .
سلام
اول چک کنید شرط رو درست نوشتید، دوم مطمئن بشید که حتما همه اعداد به صورت عدد ثبت شده باشند نه متن (برای تست این قضیه، چند سلول رو انتخاب کنید و ببینید که اطلاعات مربوط به جمع و میانگین در قسمت Status Bar نشان داده میشود یا خیر. اگر نشان نمیدهد یعنی اعداد شما صحیح وارد نشده اند)
سلام خسته نباشید
من یک لیست از نفرات و نمره ها رو دارم و میخوام رتبه بندی نفراتی که بالاترین نمره رو میگیرند همیشه بصورت خودکار انجام بشه که مشکلی خوردم که ظاهرا با آرایه کردن فرمول حل میشه. مثلا این جدول رو دارم:
نام نمره
علی ۴۰
حسن ۳۰
محمد ۴۰
محسن ۵۲
با فرمول Large کلیه نمرات به ترتیب مرتب میشه
با ترکیب فرمول MATCH+INDEX هم نفری که نمره متعلق به اون هست پیدا میشه
فقط مشکل اینجاست که نفراتی که نمره مشابه دارند، نام اولین نفری که پیدامیکنه رو برای نفر بعدی و بعدی ها هم تکرار میکنه!
یعنی بعد از استفاده از فرمول، نتیجه اینطوری میاد:
محسن ۵۲
علی ۴۰
علی ۴۰
حسن ۳۰
یعنی اسم محمد رو که نمره ۴۰ داره نمیاره. اگر امکان داره یکی از اساتید فرمول رو اصلاح کنند:
=INDEX($A$2:B13,MATCH(F3,$B$2:B15,0),1)
باتشکر
درود
بله تکرار میکنه چون ذات تابع match همینه
برای این کار باید یک مقدار خیلی کم، در حد ۰.۰۰۰۰۰۱ به نمره هاتون اضافه کنید که منحصر بفرد باشه. مثلا row()*0.000001
هم اثری روی مقدار نمره نداره هم منحصر بفرده و میتونید جستجو کنید
اما این نمره ها معدل نفرات هست و من در تعیین شون نقش ندارم و ممکنه واقعا دو سه نفر دقیقا نمره یکسانی بگیرند!! با آرایه یا IF چطور میشه درستش کرد؟
توضیحی که دادم رو دقت نکردید
من نگفتم نباید یکی باشه
خیلی وقت ها پیش میاد که اعداد مشابه باشن
عرض کردم باید ترتیبی بدید که اعداد از هم تفکیک بشن. اونم با اضافه کردن یک مقدار خیلی خیلی کوچیک که در ارزش اون مقدار تغییری ایجاد نکنه. فقط فرمول بتونه تفکیک کنه.
یا اینکه در یک ستون کمکی رتبه بندی انجام بدید با rank و برای رتبه های یکسان با countif داده ها رو اضافه کنید به رتبه که تفکیک بشن از هم. بعد رتبه ها رو شروع کنید vlookup کردن. اینطوری رتبه ها ۱ تا n شما جستجو میشن و مقادیر بصورت مرتب چیده میشن.
=rank(A1,$A$1:$A$30)+countif($A$1:A1,A1)
سلام. چگونه پس از انتخاب از دو کامبو باکس که ابعاد ماتریس را مشخص میکنند می توان ماتریس ایجاد شود . که در ادامه بتوانیم عملیاتی روی ماتریس انجام دهیم
سلام
اگر منظورتون از ایجاد ماتریس:
اگر ایجاد Table با ابعادی که انتخاب شده هست، باید از VBA استفاده بشه.
اگر تعریف محدوده ای نامگذاری شده با ابعاد انتخابی هست، هم با VBA میشه و هم با استفاده از فرمول نویسی در Name
سلام
خسته نباشید
یک فایل اکسل دارم با ۲ شیت که برای هر دو شیت ستون اول شماره درخواست،ستون دوم کد کالا و ستون سوم تعداد درخواست می باشد که ممکن است برای یک شماره درخواست یکسان چندین کد مختلف با تعداد متفاوت ثبت شده باشد
سوال:
اگر بخوام از شیت a ردیفی که شماره درخواست ۱ با کد ۱۰۰ ثبت شده مقدار درخواست را از شماره درخواست ۱ با کد ۱۰۰ از شیت b بردارم چیکار کنم
این را مدنظر داشته باشید احتمال این که برای مثال درخواست ۱ شامل چند کد در ردیفهای مختلف باشد هست
ممنون
سلام
اگر فقط قصد شمردن تعداد ردیف های مورد نظر در شیت B هست از تابع Countifs استفاده کنید.
اگر قصد دارید کل ردیف های شیت B رو ببینید (یکجوری فیلتر حساب میشه) بهتره از Pivot Table استفاده کنید.
عالی بود. مرسی