توابع جدید اکسل ۲۰۱۹

توابع جدید در اکسل ۲۰۱۹

مایکروسافت در نسخه جدیدی که از آفیس منتشر کرده از جمله ویژگی های جدید اکسل ۲۰۱۹ توابع جدید کاربردی هست که در راستای بهبود فرمول نویسی اضافه شدند، سعی کرده یک سری از کاستی ها رو در فرمول نویسی با استفاده از توابع جدید در اکسل ۲۰۱۹ جبران کنه. برخی توابع موجود رو بهبود بخشیده و چند تابع جدید هم اضافه کرده. در این مطلب در مورد توابع جدید اکسل ۲۰۱۹ صحبت میکنیم.یادتون باشه بهترین راه تسلط به این توابع تمرین و تکرار بسیار زیاد است.

در ادامه با این توابع، ساختار و کاربدر آنها آشنا میشیم.

توجه داشته باشید توابعی که در این مقاله معرفی میشن، قبلا از زمان نسخه ۲۰۱۶، البته در آفیس ۳۶۵ وجود داشتن که الان به نسخه اصلی ۲۰۱۹ اضافه شدن.

توابع جدید اکسل ۲۰۱۹ : تابع CONCAT

تابع CONCAT مشابه تابع Concatenate هست. در واقع این تابع برای متصل کردن یک سری رشته، کلمه، عدد و … (بدون جداکننده) به هم استفاده میشه. ویژگی این تابع اینه که میتونه یک محدوده رو بصورت یکجا قبول کنه و نیازی به تخصیص جداگانه اجزای عبارت نیست. آرگومان تابع CONCAT عبارتند از:

Text: این تابع حداقل یک آرگومان اجباری داره که محدوده داده هایی هست که قراره به هم متصل بشن.

مثال: میخواهیم داده های موجود در سلول های A1:C1 رو به هم وصل کنیم. شکل ۱

=CONCAT(A1:C1)

شکل ۱- تابع Concat -اتصال اجزای مختلف بدون جدا کننده مشخص

توابع جدید اکسل ۲۰۱۹ : تابع TextJoin

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

Delimiter: جدا کننده ای که باید بین اجزای جدا از هم قرار بگیره

Ignore_empty: این آرگومان مشخص میکنه که سلول های خالی موجود در محدوده مورد نظر، در نظر گرفته بن یا نه. True سلول های خالی رو در نظر نمیگیره. False سلول های خالی رو در نظر میگیره.

Text: محدوده شامل داده های مورد نظر (میتونه پیوسته یا ناپیوسته باشه)

شکل ۲- تابع Textjoin- اتصال داده های موجود در یک محدوده با جداکننده مشخص از ویژگی های جدید اکسل ۲۰۱۹

توابع جدید اکسل ۲۰۱۹ : تابع IFS

همونطور که میدونیم یکی از پرکاربردترین مسائلی که در حین کار با اکسل باهاش برخورد میکنیم، مسائل مربوط به شروط چندگانه و IF های تودرتو هست. منطق IF تو در تو یا Nested IF در مقاله مربوطه تشریح شده. حالا میخوایم کاربرد تابع IFS در حل این مسائل رو ببینیم. تابع IFS برای بررسی شرط های چندگانه به اکسل اضافه شده که آرگومان هاش رو در ادامه میبینیم:

Logical Test1: شرط منطقی که باید بررسی بشه و خروجی True/False داره

Value IF True1: نتیجه ای که باید به ازای برقرار بودن شرط نمایش داده بشه.

ساختار کلی IFS به اینصورت هست و اولین شرطی که برقرار بشه، خروجی تابع خواهد بود.

=IFS (test1, value1, [test2, value2], …)

مثال: فرض کند میخواهیم عددی رو رتبه بندی کنیم. اگر کوچکتر از ۵۰ باشه، ضعیف، بین ۵۰ تا ۷۵ باشه، متوسط و بیشتر از ۷۵، خوب رو نمایش بده. شکل ۳.

=IFS (A1<50,”ضعیف”,A1<=75,”متوسط”,A1>75,”خوب”)

همونطور که در فرمول نمایش داده شده، هر شرط با خروجی مرتبط به تابع تخصیص داده شده و تابع اولین شرطی که True بشه رو به عنوان خروجی نشون میده.

شکل ۳- تابع IFS-بررسی شرط های چندگانه با منطق IF تو در تو

تابع SWITCH

تابع Switch تقریبا مشابه ساختار Select Case در VBA عمل میکنه. در این تابع میتونیم با اعداد مختلف، خروجی های مختلفی بگیریم.

آرگومان های این تابع عبارتند از:

Expression:  شرطی که با مقادیر VALUE تطبیق داده میشه. میتونه مقار ثابت، سلول و فرمول دیگه ای باشه که قرار هست با سایر مقادیر مقایسه بشه.

Value1/result1: عدد مورد نظر و خروجی مورد نظر برای عدد Value1

Value2/result2: دومین عدد مورد نظر و خروجی مورد نظر برای عدد Value2 (اختیاری)

Default: آرگومان اختیاری که در صورتی که داده با هیچ یک از Value ها همخوان نبود، نمایش داده میشه.

این تابع تا ۱۲۷ حالت رو میتونه محاسبه کنه. مثلا به ازای عدد ۱، مقدار ضعیف. به ازای عدد ۲ ، متوسط و به ازای عدد ۳، مقدار خوب نمایش داده بشه. (شکل ۴ مشاهده شود)

=SWITCH (A1,1 ,”خوب”,۳,”متوسط”,۲, “ضعیف”)

شکل ۴- تابع Switch یکی از توابع جدید در اکسل ۲۰۱۹

اگر آرگومان آخر رو تخصیص بدیم، فرمول بصورت زیر خواهد بود:

=SWITCH (A1,1 ,”خوب”,۳,”متوسط”,۲, “ضعیف”,”??”)

در این فرمول اگر عددی غیر از ۱، ۲ و ۳  در سلول A1 وارد بشه، ?? نمایش داده میشه. اگر آرگومان default رو تعیین نکنیم و عددی غیر از ۱، ۲ و ۳ وارد کنیم، خطای NA خواهیم داشت.

اما تفاوت این تابع با IFS چیه؟

این تابع مشابه تابع IFS امکان بررسی چند شرط رو فراهم میکنه ولی اولین تفاوتی که نسبت به IF داره اینه که شرط فقط یک بار همون اول نوشته میشه و نیازی نیست تکرار بشه. دومین تفاوت اینه که تابع Switch فقط مقدار قطعی  و مشخص رو میتونه مقایسه کنه. یعنی نمیتونه عملگرهای > و < رو در محاسبات دخیل کنه.

 تابع Maxifs/Minifs

این دو تابع منطقی مشابه توابع Sumif و Countif دارن. یعنی مینیمم/ماکزیمم یک محدوده رو به شرط/شرط های مختلفی محاسبه میکنن. آرگومان های این تابع عبارتنداز:

Max_Range: محدوده ای که قرار هست بیشترین/کمترین مقدار آن مشخص بشه.

Criteria_Range1: محدوده ای که شرط در اون محدوده قرار میگیره.

Criteria1: شرط مورد نظر.

مثال: میخواهیم بیشترین مقدار فروش محصول ۲ رو حساب کنیم. شکل ۵.

=MAXIFS (B2:B13, A2:A13,D2)

شکل ۵- توابع Maxifs/Minifs یکی از توابع جدید در اکسل ۲۰۱۹

تا حالا که این تابع رو نداشتیم چطور می نیمم یا مازیمم شرطی محاسبه میکردیم؟

دو روش برای حل ای موضوع وجود داره.

1. توابع دیتابیس، یعنی Dmax و یا Dmin
2. از فرمول نویسی آرایه ای برای محاسبه می نیمم/ ماکزیمم شرطی استفاده میکردیم با این ساختار:

=MAX(IF(range=criteria, max range , “”))

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

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