فرمول های آرایه ای در اکسل و کاربرد شگفت انگیز آنان در اکسل

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

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

برای تبدیل کردن فرمول خود به فرمول آرایه ای ، در انتهای فرمول باید کلیدهای زیر را فشار دهیم:CTRL+SHIFT+ENTER

همان طور که در فرمول بالا مشاهده می نمایید ، پس از زدن کلیدهای CTRL+SHIFT+ENTER در ابتدا و انتهای فرمول علامت براکت ظاهر می شود. و نمایانگر این هستکه ما از فرمول آرایه ای در اکسل استفاده کرده ایم.

جهت پیاده سازی این مثال مراحل زیر را با هم انجام می دهیم.

قدم اول: فرمول =Max( را وارد می کنیم.

قدم دوم: در این مرحله محدوده ای از سلول ها را از محدوده ای دیگر کسر می کنیم.

قدم سوم : در این مرحله به جای فشردن کلید Enter ، کلیدهای "کنترل + شیفت + اینتر" را فشار دهید تا فرمول به حالت آرایه ای تبدیل شود.حال فرمول شما باید به صورت زیر درآمده باشد:

قدم چهارم : حال می خواهیم فرمول آرایه ای را اجرا و تجزیه و تحلیل نماییم. برای این کار به انتهای فرمول رفته و کلید F9 را فشار دهید.آرایه ای از اعداد را در داخل پرانتز به صورت زیر مشاهده می نمایید:

حال برای این که فرمول دوباره به حالت اول برگردد ، کلیدهای Ctrl + Z را می زنیم.

خُب !! شاید سوالی در ذهنتان پیش بیاید که این کار را می توانیم به طور معمول انجام دهیم. کاملا درست هست. شما می توانید این کار را بدون نوشتن فرمول های آرایه ای در اکسل انجام دهید ، اما نیاز به یک ستون اضافه و کمکی در این مثال دارید. تا تفریق دو ستون Open و Close را انجام دهد و سپس با تابع Max ، بزرگترین نتیجه را در خروجی نمایش دهیم.

مثال دوم از فرمول های آرایه ای در اکسل :

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

قدم اول: فرمول Sum( را وارد می کنیم.

قدم دوم: انتخاب محدوده داده ها با عنوان Units Sold ، سپس وارد کردن علامت ضربدر و انتخاب محدوده داده های Unit Price

قدم سوم: وارد کردن کلیدهای Ctrl+Shift+Enter

حال فرمول شما باید به صورت زیر درآمده باشد:

قدم چهارم: حال کلید F9 را می زنیم . البته یا در انتهای فرمول قرار می گیریم و سپس این کلید را می زنیم و یا فرمول را انتخاب کرده (کاملا) و سپس کلید F9 را می زنیم. مانند زیر:

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

این دومین مثالی بود که با هم بررسی کردیم.

مثال سوم از فرمول های آرایه ای در اکسل :

در این مثال می خواهیم از تابع Lookup استفاده نماییم که شباهت زیادی به Vlookup دارد. در صورتی که با Vlookup کار نکرده اید پیشنهاد می کنیم حتما ویدئو دانلود رایگان جستجوی پیشرفته در اکسل را مشاهده نمایید.

تابع Lookup مقداری را در محدوده ای از داده ها جستجو کرده و ستونی متناظر با مقدار پیدا شده را برمی گرداند.

فرقی که تابع Lookup با Vlookup دارد این هست که آرگومان چهارم در Vlookup در تابع Lookup به صورت اتوماتیک True در نظر گرفته شده است. این پارامتر همان سنجش براساس مقدار تقریبی می باشد.

نکته: دقت نمایید که مقدار result_vector در تابع Lookup باید به صورت مرتب شده باشد.

داده ها به صورت زیر می باشند: