توابع جدید در اکسل 2019

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

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

توجه داشته باشید توابعی که در این مقاله معرفی میشن، قبلا از زمان نسخه 2016، البته در آفیس 365 وجود داشتن که الان به نسخه اصلی 2019 اضافه شدن.

تابع CONCAT

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

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

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

=CONCAT(A1:C1)

تابع TextJoin

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

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

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

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

تابع IFS

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

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

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

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

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

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

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

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

تابع SWITCH

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

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

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

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

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

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

این تابع تا 127 حالت رو میتونه محاسبه کنه. مثلا به ازای عدد 1، مقدار ضعیف. به ازای عدد 2 ، متوسط و به ازای عدد 3، مقدار خوب نمایش داده بشه. (شکل 4 مشاهده شود)

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

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

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

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

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

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

 تابع Maxifs/Minifs

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

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

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

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

نکته:
در صورتی که بیش از یک شرط داشتیم، مشابه تابع SUMIFS در ادامه سه آرگومان اول، criteria_range/criteria های بعدی رو وارد میکنیم.

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

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

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

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

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

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

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

سایر مزایای اضافه شده به اکسل 2019:

بهبود در انتخاب سلول ها:

همونطور که میدونیم در اکسل برای انتخاب چند سلول میتونیم کلید Control رو نگهداریم و سلول هامون رو انتخاب کنیم. حالا اگر سلولی به اشتباه به انتخابمون اضافه میکردیم باید مجدداً شروعت میکردیم به انتخاب سلول ها. یه قابلیت مفید که در اکسل 2019 اضافه شده اینه که در صورتی که شما سلولی رو اشتباه انتخاب کردید میتونید اون رو از حالت انتخاب دربیارید بدون اینکه نیاز باشه تمام سلول های قبلی رو از ابتدا انتخاب کنید.

بهبود قابلیت AUTOCOMPLETE:

در نسخه های قبلی اکسل هنگامی که میخواستید یه تابع رو بنویسید باید حتماً ابتدای اسم اون رو درست مینوشتید تا در لیست AUTOCOMPLETE نمایش داده بشه. اما در اکسل 2019 هر بخشی از اسم تابع رو بنویسید اون تابع در لیست نمایش داده میشه. مثلاً اگر کلمه DAY رو در بخش فرمول نویسی بنویسید تابع WEEKDAY هم در لیست نمایش داده میشه در صورتی که در اکسل های قبلی این تابع فقط وقتی نماش داده میشد که شما حروف اول اون یعنی WEEK رو تایپ کنید.

عدم نمایش خطا در هنگام ذخیره فایل CSV:

در اکسل 2019 دیگه هنگام ذخیره فایل CSV خطای آزار دهنده  “This file may contain features that are not compatible with CSV…” نمایش داده نمیشه.

امکان ذخیره فایل CSV با فرمت UNICODE:

این یکی از قابلیت های مهمیه که برای ما فارسی زبان ها اضافه شده. در اکسل های قبل اگر فایلتون رو با فرمت CSV ذخیره میکردید، کاراکترهای فارسی به صورت “?” ذخیره میشد که عملاً امکان ذخیره فایل با فرمت CSV برای فایل های فارسی وجو د نداشت. اما در این نسخه از اکسل میتونید با انتخاب گزینه “CSV UTF-8 (Comma delimited)” در پنجره SAVE AS، فایلتون رو با فرمت CSV ذخیره کنید بدون اینکه برای کاراکترهای فارسی مشکلی پیش بیاد.

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