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

[sinabed1393]

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

در جدول زیر فهرست و یک مثال ساده از این توابع را می­توانید ببیند:

نام تابع

مثال

CONCAT

 =CONCAT(A1:A10)

TEXTJOIN

 =TEXTJOIN("-", TRUE, A1:A10)

MAXIFS

 =MAXIFS(B:B,A:A,"Terhan")

MINIFS

 =MINIFS(B:B,A:A,"Terhan")

IFS

 =IFS(A1=1,"بهار",A1=2,"تابستان",A1=3,"پاییز",A1=4,"زمستان")

SWITCH

 =SWITCH(A1, 1,"فروش", 2,"خرید", "نامشخص")

 

تابع CONCAT و تابع TEXTJOIN

تا به حال در اکسل برای چسباندن (ترکیب، ادغام) نوشته­‌ها از تابع CONCATENATE و یا عملگر & استفاده می­کردیم. مشکل اینجا بود که اگر لازم بود متن 5 سلول کنار هم را به هم بچسبانیم، باید آدرس آن 5 سلول را دقیقا تایپ میکردیم. مثلا می­‌نوشتیم:

و تایپ این فرمول­های طولانی کلافه کننده بود و نمی­توانستیم مانند تابع SUM ، آدرس محدوده بدهیم، یعنی فرمول زیر خطا می­داد:
=CONCATENATE(A1:A5)
با تابع جدید CONCAT اینکار به سادگی شدنی است یعنی دیگر لازم نیست آدرس تک تک سلو­ل­ها را جداگانه بنویسیم و کافی است که آدرس محدوده را بدهیم. و اکسل متن تمامی این سلول­ها را بهم می­چسباند و نتیجه را به ما می­دهد.
البته است که بخواهیم متن­ها با یک علامت فاصله از هم جدا شوند در ضمن اگر سلولی خالی بود، نادیده گرفته شود که اینکار با تابع TEXTJOIN قابل انجام است:

توجه: دیگر لزومی به استفاده از تابع قدیمی CONCATENATE نیست.
تابع MAXIFS و MINIFS

در اکسل2007  اضافه شدن توابع SUMIFS و COUNTIFS واقعا کارها را ساده کرد اما جای خالی توابع MAXIFS و MINIFS واقعا خالی بود و مجبور بودیم که از توابع برداری و یا تکنیک­های دیگر جای خالی این توابع را پر کنیم. خوب حالا در 2019 این دو تابع اضافه شده­اند. از آنجایی که این توابع دقیقا شبیه SUMIFS هستند، به نظرم توضیح دادن آنها لزومی ندارد و فقط یک مثال ساده می­زنم:

تابع IFS و SWITCH

وقتی که تابع IF ما چندین حالت را باید بررسی می­کرد، ناگزیر بودیم که IF های تو در تو بنویسیم. یعنی در داخل یک تابع IF از یک تابع دیگر  IF استفاده کنیم و کار کمی سخت بود. زیرا باید چندین بار IF را می­نوشتیم و همینطور مراقب تعداد پرانتزها و .. بودیم.

مثال 1) فرض کنید که در ستون B فروش کالاها نوشته شده است. با توجه به شرح زیر قرار است که امتیاز دهی به تیم فروش انجام شود:

[list]
[*]اگر فروش بیش از 200  است، 13 امتیاز

[*]اگر فروش بیش از 150 است، 10 امتیاز

[*]اگر فروش بیش از 100 است، 4 امتیاز

[*]وگرنه (فروش کمتر یا مساوی 100)،  2 امتیاز
[/list]

این مثال در نسخه‌­های قدیمی به شکل زیر باید حل می­شد:

=IF( B2>200, 13, IF(B2>150, 10, IF(B2>100, 4, 2)))
حال این فرمول در اکسل 2019 به سادگی زیر و بدون IF های تو در تو نوشته می­شود:

=IFS(B2>200, 13, B2>150, 10, B2>100, 4, B2<=100, 2)

تابع SWITCH شبیه دستور SELECT-CASE در برنامه نویسی است و بسیار نوشتن شرطها را ساده و کوتاه می­کند.

بگذارید مثال ساده­ای را بزنم. فرض کنید که در یک لیست واحد ارز به صورت کدهایی نوشته شده­اند و شما می­خواهید که آن کدها را نام آن ارز با شرح جدول زیر تبدیل کنید:

با IF های تو در تو این فرمول باید به شکل زیر نوشته می­شد:

=IF( A1=1 , "IRR" , IF(A1=4 , "EUR" , IF(A1=9 , "USD" , "OTHERS")))

حال با تابع SWITCH دیگر لازم نیست که چندین با آدرس A1 و همینطور IF را بنویسیم و خواهیم داشت:

=SWITCH(A1 , 1,"IRR",  4,"EUR",  9,"USD"  , "OTHERS")

نکته: این مساله را با VLOOKUP هم می­توان حل کرد که البته مستلزم ساخت جدول پایه در اکسل است و احتمال می­دهم که کندتر از تابع SWITCH باشد.

توابع جدید در نسخه‌­های قدیمی

یک سوال مهم! اگر از توابع جدید در فایلی استفاده کنید و سپس آن فایل را برای همکار خود که اکسل 2010 دارد، ارسال نمایید چه خواهد شد.
خوشبختانه اکسل همواره نتیجه محاسبات را در فایل ذخیره می­کند و به همین دلیل اگر همکار شما که اکسل قدیمی تری دارد، فایل را باز کند، آخرین نتیجه محاسبات را می­بینید و می­تواند آنها را PasteValue کند.
اما اگر او سلول­هایی که در فرمول استفاده شده­اند را ویرایش کند، فرمول خطای #NAME خواهد شد.

در سلول E1 فرمول =TEXTJOIN("-",TRUE,A1:C1) نوشته شده است.

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

حال اگر کاربر یکی از سلول­های A1:C1 را تغییر دهد، اکسل 2010 نمی­تواند فرمول را آپدیت کند و خطای #NAME در سلول نمایش داده می­شود:

چارت­های جدید در اکسل 2019

خوشبختانه در اکسل 2019 همدو چارت جدید داریم به نام­های Map  و Funnel 

Map Chart

از این به بعد فروش را بر اساس استان­های ایران به سادگی یک کلیک در اکسل خواهیم داشت:

در این چارت با نکته­‌های زیر برخورد کردم:

[list]
[*]خراسان به صورت یک استان است و نه سه استان جداگانه.

[*]می­توانیم نام استان­ها را فارسی بنویسیم اما برخی از نام­ها مانند "آذرباییجان غربی" توسط نقشه تشخیص داده نشد.

[*]بهتر است که هدر جدول انگلیسی باشد در غیر اینصورت گاهی تشخیص نمی­دهد.

[*]نام های انگلیسی را باید از روی نقشه Bing بیابید.

[*]نام شهرها را نمی‌پذیرد و نام استان / کشور را قبول می­کند.

[*]برای رسم اولیه نقشه اتصال به اینترنت لازم است.

[*]اگر نقشه نتواند همه محدوده­ها را تشخیص دهد، به شما پیغامی می­دهد تا متوجه شوید.

[*]این نقشه دارای تنظیمات خاص خودش است (تصویر زیر)
[/list]

Funnel Chart

این چارت به ما مراحل انجام یک کار (پروسه) را به ما نمایش می­دهد.

مثال)  واحد منابع انسانی 100 روزمه را بررسی کرده­اند و با 70 نفر آنها تماس گرفتند برای مصاحبه و 46 نفر برای مصاحبه اول آمدند و از آنها 20 نفر برای مصاحبه فنی رفتند و سرانجام 7 نفر واجد شرایط استخدام در شرکت ما بودند.

امکان Deselectکردن سلول­های انتخاب شده

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

بله، تاکنون این کار در اکسل امکان پذیر نبود، اما اکنون با کلید CTRL می­توانیم به سادگی اینکار را انجام دهیم. یعنی سلول­هایی را از انتخاب خارج کنیم و یا انتخاب کنیم.

 

تنظیمات پیش فرض Pivot Tables

اکنون می­توانیم به اکسل بگوییم که چیدمان و تنظیمات Pivot Tableهای ما را به صورت پیش فرض، چگونه در نظر بگیرد و دیگر لازم نیست پس از ایجاد هر Pivot Table، یکبار این تنظیمات را برای آن انجام دهیم.

 این تنظیمات از طریق مسیر زیر قابل دسترسی و تغییر است.

Excel Options --> Data --> Edit Default Layout

 

حذف Get External Data

در تب Data تمام فرمان­های گروه Get External Data که در تصویر زیر می­بینید، حذف شده­اند.

و جای آنها PowerQuery (Get & Transform)  اشغال کرده است که البته کاملا موضوع منطقی و درستی است. زیرا هر آنچه با گزینه­های Get External Data تا کنون قابل انجام بود را PowerQuery بسیار کامل­تر و قوی­تر، سریعتر و هوشمندتر می­تواند انجام دهد.

[Arashbakhshi]

آقای عابد تاثیر نرم افزار جسی ریدر در اکسل چیه ؟