نسخه 3 افزونه تقویم شمسی با امکانات بیشتر منتشر شد!!! (برای مشاهده اینجا کلیک کن)
سبد خرید
0

محصولی در سبد خرید نیست.

بازگشت به فروشگاه
تمام دسته ها
  • تمام دسته ها
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • Power BI
  • Power Pivot
  • Power Query
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

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

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

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

مهم ترین ویژگی این توابع و کلا فرمول نویسی آرایه ای در نسخه ۲۰۲۱ اینه که دیگه برای ثبت فرمول آرایه ای نیازی به ترکیب Ctrl+Shift+Enter نیست و اکسل خودش متوجه میشه که باید منطق آرایه رو بکار بگیره. جذاب نیست؟ ?

در همه نسخه های قبلی اکسل در یک سلول یک فرمول میزدیم و نتیجه رو توی همون سلول میدیدیم. حالا در نسخه ۲۰۲۱ در یک سلول یک فرمول می نویسیم و تا هر تعداد سلولی که مورد نیاز است، جواب رو نمایش میده. یعنی در یک سلول یک فرمول میزنیم و ممکنه تا ۱۰۰ سلول رو برای نمایش جواب نیاز داشته باشه. برای اینکه این موضوع رو بهتر متوجه بشیم مثال زیر رو ببینیم:

در تصویر بالا میبینیم که در سلول B2 فرمول A2:A5*B1:E1 رو تایپ میکنیم و نتیجه در محدوده مورد نظر نمایش داده میشه. در واقع بدون انتخاب محدوده و فقط با تایپ فرمول در یک سلول، محدوده مورد نیاز با نتایج فرمول پر شد.

به این عمل که با یک فرمول تعدادی سلول پر بشه، SPILL گفته میشه و به محدوده ای که پر شده SPILL Range گفته میشه.

نکته مهم اینه که این آپدیت جدید، تنها یک شیوه محاسبات نیست بلکه یک تغییر خیلی بزرگ روی موتور محاسباتی اکسل هست. با Dynamic Arrays هم یک سری توابع جدید به اکسل اضافه شدند و توابع فعلی سریع تر و موثرتر کار میکنن. و این توابع جایگزین شیوه قدیمی فرمول نویسی آرایه ای هست. (دونستن منطق فرمول نویسی آرایه ای به درک بهتر و استفاده تریکبی این توابع جدید کمک میکنن)

لیست توابع DynamicArray عبارتند از:

تابع Unique: یک لیست بدون تکرار از یک محدوده استخراج میکند.

تابع Filter: داده های یک بانک اطلاعاتی رو بر اساس شرایط مختلف فیلتر میکند.

تابع Sort: داده های یک دیتابیس رو بر اساس ترتیب دلخواه مرتب میکند.

تابع SortBy: داده های یک دیتابیس رو بر اساس داده های یک ستون دیگه مرتب میکند.

تابع Randarray: عدد تصادفی (اعشاری و غیراعشاری) و در بازه دلخواه تولید میکند.

تابع Sequence: لیستی از اعداد با فواصل و آرایش دلخواه تولید میکند.

دو تا تابع دیگه هم هست که البته جزو گروه Dynamic Array نیستن اما بسیار کاربردی و حرفه ای هستن و با Dynamic Array ها قابل ترکیب هستن. این دو تابع XLOOKUP, XMATCh هستن.

تابع XLOOKUP میتونه جایگزین همه توابع Lookup, Vlookup, Hlookup باشه و جستجو رو در حالت های مختلف و بصورت افقی و عمودی انجام میده.

تابع XMATCH هم میتونه جایگزین تابع Match باشه و جستجو رو بصورت حرفه ای تر و کاربردی ترانجام میده.

توابع جدید اکسل ۲۰۲۱ و خطای جدید #SPILL!

همونطور که گفتیم وقتی یک فرمول رو در یک سلول مینویسیم و نتیجه در تعداد سلول دلخواه نمایش داده میشه، عمل SPILL انجام شده. حالا اگر به هر دلیلی، در مسیر این محدوده SPILL مانعی وجود داشته باشه، و فرمول نتونه نتایج رو در سلول های مورد نیاز نمایش بده، خطای #SPILL! نمایش داده میشه. مثلا در شکل شماره ۱ تابع Filter نیاز به محدوده F2:I6 داره تا بتونه نتیجه تابع رو نمایش بده و داده های مربوط به صادرات رو لیست کنه. اما چون در این محدوده و در سلولH4 مانع (کلمه اکسل پدیا) وجود داره ، خطای #SPILL! نمایش داده شده.

توابع جدید اکسل 2021-خطای #SPILL

شکل ۱- توابع جدید اکسل ۲۰۲۱- خطای #SPILL

استفاده از محدوده SPILL Range در فرمول نویسی

برای اینکه بتونیم از محدوده SPILL در فرمول نویسی استفاده کنیم کافیه علامت # رو قبل از اولین سلول یعنی جایی که فرمول رومی نویسیم قرار بدیم. مثلا فرض کنید میخواهیم ببینیم در مثال بالا، چند مورد صنعت پیدا شده. کافیه بنویسیم:

=Counta(F2#)/4

وقتی این فرمول رو مینویسیم، تابع کل محدوده SPILL رو در نظر میگیره (مطابق شکل ۲). برای اینکه تعداد داده های پیدا شده رو ببینیم، کافیه تقسیم بر ۴ (چهار ستون داده) کنیم.

توابع 2021-شمردن داده های محدوده

شکل ۲- توابع جدید اکسل ۲۰۲۱- شمردن داده های محدوده

مزایای توابع Dynamic Array

بدون شک توابع داینامیک از بهترین تغییرات اکسل در سالیان گذشته بوده و مثل هر تغییری نقاط ضعف و قوتی داره که البته در مجموع نقاط قوتش خیلی بیشتر از نقاط ضعف اون هست.

  • این توابع بسیار قدرتمند و ساده هستن و نمیخواد مدام در نظر بگیریم که این تابع منطق آرایه رو ساپورت میکنه یا نه. کافیه فرمول رو بنوسیم، اگه جواب در یک آرایه بود خودش نشون میده.
  • یکی دیگه از مزایای خوب این توابع اینه که براحتی با توابع دیگه ترکیب میشن و نتایج بسیار کاربردی و حرفه ای خواهند داشت.
  • همچنین بحث آدرس دهی $ در این توابع مطرح نیست چون در یک سلول نوشته میشن و درگ نمیکنیم.

محدودیت های توابع Dynamic Array

این توابع خیلی کاربردی و عالی هستن ولی نقطه ضعف هایی هم دارن مثلا:

  • نتایج این توابع رو نمیتونیم با استفاده از ابزار Sort مرتب کنیم. البته مشکل بزرگی نیست خیلی راحت میتونیم این کار رو با استفاده از تابع Sort انجام بدیم.
  • بخشی از SPILL Range رو نمیتونیم حذف کنیم. البته این هم مسئله پیچیده ای نیست با توابع متنوعی میتونیم نتیجه این توابع رو محدود کنیم و یا انتخاب کنیم که کدوم قسمت ها نمایش داده بشن . (این نکات رو در اپدیت دوره نینجا ارائه کردیم)
  • این توابع در Table کار نمیکنن. یعنی اگر یک Spill Range رو تبدیل به Table کنیم، تبدیل میشه اما توابع به خطای #SPILL! تبدیل میشن.
  • داده های خروجی این توابع در پاورکوئری قابل استفاده نیستن.

در این مقاله سعی کردیم نگاهی به توابع جدید اکسل ۲۰۲۱ (که جزء توابع Dynamic Array هستند) داشته باشیم و منطق این توابع که در ورژن ۲۰۲۱ اضافه شدن رو بررسی کنیم. پس اگر هنوز ورژن ۲۰۲۱ رو نصب نکردید حتما این کار و انجام بدید و از قدرت این توابع حسابی بهره ببرید.

134

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

دیدگاه کاربران
  • محمد ۲۰ مهر ۱۴۰۲ / ۱۱:۰۳ ق٫ظ

    سلام وقت بخیر
    من چند تا شیط مختلف برای حقوق هرماه پرسنل دارم و یک فیش حقوق طراحی کردم
    لطفا راهنمایی بفرمایید چه فرمولی باید در قسمت فیش حقوق بنویسم که فقط ماه کارکرد که عوض بشه مبالغ همون ماه رو نشونم بده

    • آواتار
      حسنا خاکزاد ۲۱ مهر ۱۴۰۲ / ۶:۵۲ ب٫ظ

      درود بر شما
      با ترکیب indirect و تابع address میتونید محل جستجو رو با توجه به اسم شیت جابجا کنید

  • مهدی کهنسال ۱۸ اردیبهشت ۱۴۰۲ / ۹:۱۲ ق٫ظ

    سلام و درود خانم خاکزاد ممنون از محبتتون شما کاملا منظور منو فهمیدید ولی فرمول فوق از ستونهای بالا دارای اسم مشابه باشند یعنی دو تا ستون با عنوان C فقط اولین ستون رو جمع میزنه
    در کل من دونبال به تابع مستقل بودم گفتم شاید تو نسخه های جدید افیس فرمول جدیدی مثل xlookup اضافه شده باشه به هر حال ممنون بابت همه چیز اگر راه بهتری وجود داشت ممنون میشم خبر اطلاع بدید

    • آواتار
      حسنا خاکزاد ۱۸ اردیبهشت ۱۴۰۲ / ۱۱:۵۵ ق٫ظ

      درود
      تابع خاصی که بنظرم نمیرسه ولی خب راه حل آرایه ای به این شکل میتونه باشه:
      آرایه ای در قبل از ۲۰۲۱ با ctrl shift enter ثبت میشه. پرانتزها رو هم جدی بگیرید

  • مهدی کهنسال ۱۷ اردیبهشت ۱۴۰۲ / ۱:۴۶ ب٫ظ

    a b c
    حسن ۵ ۶ ۷
    حسین ۴ ۳ ۶
    محمد ۲ ۱ ۶
    حسن ۶ ۵ ۴

    سلام فکر کنید اطلاعات بالا ستون ها و ردیفهای که یک جدول هست
    با چه تابعی میتونم جمع اعدادی که در ستون اسامی حسن هستن و در ردیف b قرار دارند بدون انتخواب ستون b فقط بدست بیارم
    یعنی شرط برای ستون و ردیف اختیاری و متغیر باشه

    • آواتار
      حسنا خاکزاد ۱۷ اردیبهشت ۱۴۰۲ / ۴:۵۵ ب٫ظ

      درود
      منظورتون sumif هست که ستون جمع زدنش متغیره؟ گاهی a, b یا c هست؟
      اگر اینه با Index میتونید محدوده sumrange رو متغیر کنید به این شکل:
      یعنی در اینجا ۵ و ۶ که مربوط به حسن هست جمع زده بشه

  • علی ۲۳ اسفند ۱۴۰۱ / ۶:۵۵ ب٫ظ

    سلام وقت بخیر من اکسل ۲۰۱۳ دارم بنابر دلایلی نمیتونم اپدیتش کنم میخوام یه متنی که به صورت زیر هست رو از قسمت نقطه هاش جدا کنم در ضمن تعداد کاراکترها میتونه متفاوت باشه لطف میکنید راهنمایی بفرمایید

    askjjgfdb.s1-45vg.tghjk.fg2345.xcf23g

    • آواتار
      حسنا خاکزاد ۳ فروردین ۱۴۰۲ / ۱۰:۴۸ ب٫ظ

      درود
      از ابزار text to column استفاده کنید و delimiter رو . تعیین کنید

  • آواتار
    امیرحسین ۳۰ شهریور ۱۴۰۱ / ۴:۲۳ ب٫ظ

    با سلام چگونه می توان تعداد اعداد یا متن های تکرار شده در چندین شیت را یکجا پیدا کرد؟
    مثلا اگه کلمه احمد در شیت یک دو بار و در شیت دو سه بار تکرار شده است این تکرار که پنج تا می شود در یک شیت این عدد را بدست آورد؟

    • سامان چراغی ۱۴ اسفند ۱۴۰۱ / ۹:۴۹ ق٫ظ

      درورد، برای انجام این کار می بایست کد وی بی نوشته بشه
      کد زیر زیر را در یک ماژول قرارداده و اجرا کنید:

  • محمد شیوانی ۲۷ مرداد ۱۴۰۱ / ۱۰:۲۸ ق٫ظ

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

    • سامان چراغی ۲۹ مرداد ۱۴۰۱ / ۸:۱۹ ق٫ظ

      تشکر جناب شیوانی
      لطف دارید

ارسال دیدگاه

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

توسط
تومان