سبد خرید
0

سبد خرید شما خالی است.

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

توابع جدید اکسل 2019
۴.۷/۵ - (۳ امتیاز)

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

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

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

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

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

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

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

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

=CONCAT(A1:C1)

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

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

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

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

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

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

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

تابع Textjoin- اتصال داده های موجود در یک محدوده با جداکننده مشخص

شکل ۲- تابع 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 تو در تو

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

تابع SWITCH

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

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

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

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

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

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

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

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

تابع Switch در اکسل 2019

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

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

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

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

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

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

 تابع Maxifs/Minifs

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

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

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

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

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

 

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

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

توابع Maxifs/Minifs

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

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

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

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

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

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

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

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • emami6599 21 آذر 1398 / 5:27 ب.ظ

    سلام. بخشید ۲ تا سوال داشتم نمیدونستم کجا مطرح کنم. یکی اینکه برای لیست حضور و غیاب دانش آموزان میخوام هر سه بار تاخیر دانش آموز یک غیبت حساب بشه. مثلا یک شخصی ۱۱ بار تاخیر در کلاس داشته ۳ بار غیبت محاسبه کنه و به همین ترتیب.آیا راهی داره؟ سوال دیگه اینکه وقتی عددی رو که رند نیست (مثلا ۲.۷۵) از فرمت سل و تنظیمات decimal places روی صفر قرار بدم، عدد رند میشه اما تبدیل به ۳ میشه. راهی نداره با رند شدن عدد تبدیل به ۲ بشه و اصلا اون قسمت اعشار رو حساب نکنه؟

    • حسنا خاکزاد 23 آذر 1398 / 10:38 ق.ظ

      درود بر شما
      سوال اول:
      تعداد تاخیر ها رو با هر تابعی بشمارید مثلا countif و بعد از تابع quotient استفاده کنید. این تابع خارج قسمت تقسیم رو میده. مثلا جواب فرمول زیر میشه ۳

      سوال دوم رو هم در این مقاله مطالعه کنید: دقت کنید، فرمت سل فقط ظاهر عدد رو گرد میکنه اونم با توجه به قواعد ریاضی

      https://excelpedia.net/number-rounding/

      • emami6599 23 آذر 1398 / 1:48 ب.ظ

        خیلی ممنون از لطفتون

  • ramezani 31 مرداد 1398 / 10:00 ب.ظ

    سلام…
    کار تابع SWITCH را تقریبا تابع choose هم انجام میدهد درسته؟…… اگر درسته چه تفاوتی بین این دو خواهد بود؟

    • سامان چراغی 1 شهریور 1398 / 9:01 ق.ظ

      بله تقریبا شبیه هم هستند اما تفاوت:
      در تابع Choose آرگومان اول فقط باید عدد باشد اما در تابع Switch متن هم به عنوان ورودی می تواند تعیین کند که کدام آرگومان تابع Switch برگردانده شود.
      البته که با ترکیب تابع Choose با IF میشه این کار رو انجام داد ولی با تابع Switch این کار راحت تر انجام میشه.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان