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

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

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

 

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

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

 

برای تبدیل کردن فرمول خود به فرمول آرایه ای ، در انتهای فرمول باید کلیدهای زیر را فشار دهیم: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 باید به صورت مرتب شده باشد.

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

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

 

قدم اول:  تابع Sum( را وارد می کنیم.  =SUM(

قدم دوم:  سپس تابع Lookup را به صورت تودرتو وارد می کنیم.  =SUM(LOOKUP(

قدم سوم:  انتخاب Lookup Values که معادل ستون Sales می باشد.

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

 

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

قدم چهارم : انتخاب محدوده Lookup Range در ستون Sale 

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

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

قدم پنجم :  انتخاب Result Range در ستون Bonus     

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

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

قدم ششم:  در انتها کلید های Ctrl+Shift+Enter را می زنیم.فرمول به صورت زیر در خواهد آمد:

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

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

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

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

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

 

 

اگر این مقاله برای شما مفید بوده ، می توانید از "دوره آموزش فرمول نویسی پیشرفته" با کلیک بر روی تصویر زیر دیدن نمایید:

 

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

 

همچنین می توانید از ویدئوی آموزشی "فرمول نویسی آرایه ای و سه بعدی در اکسل" که نکات بسیار مهمی در آن می باشد دیدن نمایید:(با کلیک روی تصویر زیر)

 

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

 

تنها 10 ثانیه تا دریافت آموزش ارزشمند در ایمیل خود فاصله دارید!
آموزش پیشرفته داینامیک کردن نمودار با چکباکس ها در اکسل
لطفا پس از پر کردن فرم زیر ایمیل خود را چک نمایید و آموزش را دریافت کنید.
لطفا نام خود را وارد نمایید:
لطفا ایمیل خود را وارد نمایید:
شماره موبایل خود را وارد نمایید:
اطلاعات شما در سایت هدف آموزش کاملا محرمانه محافظت خواهد شد.
تنها 10 ثانیه تا دریافت آموزش ارزشمند در ایمیل خود فاصله دارید!
آموزش کامبوباکس ها و ترکیب آنها با نمودار در اکسل
لطفا پس از پر کردن فرم زیر ایمیل خود را چک نمایید و آموزش را دریافت کنید.
لطفا نام خود را وارد نمایید:
لطفا ایمیل خود را وارد نمایید:
شماره موبایل خود را وارد نمایید:
اطلاعات شما در سایت هدف آموزش کاملا محرمانه محافظت خواهد شد.