
توابع جدید اکسل ۲۰۲۱
با انتشار نسخه جدید اکسل و اضافه شدن توابع جدید اکسل ۲۰۲۱، فرمول نویسی آرایه ای مورد توجه زیادی قرار گرفته. چرا که توابعی تحت عنوان 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! نمایش داده شده.
شکل ۱- توابع جدید اکسل ۲۰۲۱- خطای #SPILL
استفاده از محدوده SPILL Range در فرمول نویسی
برای اینکه بتونیم از محدوده SPILL در فرمول نویسی استفاده کنیم کافیه علامت # رو قبل از اولین سلول یعنی جایی که فرمول رومی نویسیم قرار بدیم. مثلا فرض کنید میخواهیم ببینیم در مثال بالا، چند مورد صنعت پیدا شده. کافیه بنویسیم:
=Counta(F2#)/4
وقتی این فرمول رو مینویسیم، تابع کل محدوده SPILL رو در نظر میگیره (مطابق شکل ۲). برای اینکه تعداد داده های پیدا شده رو ببینیم، کافیه تقسیم بر ۴ (چهار ستون داده) کنیم.
شکل ۲- توابع جدید اکسل ۲۰۲۱- شمردن داده های محدوده
مزایای توابع Dynamic Array
بدون شک توابع داینامیک از بهترین تغییرات اکسل در سالیان گذشته بوده و مثل هر تغییری نقاط ضعف و قوتی داره که البته در مجموع نقاط قوتش خیلی بیشتر از نقاط ضعف اون هست.
- این توابع بسیار قدرتمند و ساده هستن و نمیخواد مدام در نظر بگیریم که این تابع منطق آرایه رو ساپورت میکنه یا نه. کافیه فرمول رو بنوسیم، اگه جواب در یک آرایه بود خودش نشون میده.
- یکی دیگه از مزایای خوب این توابع اینه که براحتی با توابع دیگه ترکیب میشن و نتایج بسیار کاربردی و حرفه ای خواهند داشت.
- همچنین بحث آدرس دهی $ در این توابع مطرح نیست چون در یک سلول نوشته میشن و درگ نمیکنیم.
محدودیت های توابع Dynamic Array
این توابع خیلی کاربردی و عالی هستن ولی نقطه ضعف هایی هم دارن مثلا:
- نتایج این توابع رو نمیتونیم با استفاده از ابزار Sort مرتب کنیم. البته مشکل بزرگی نیست خیلی راحت میتونیم این کار رو با استفاده از تابع Sort انجام بدیم.
- بخشی از SPILL Range رو نمیتونیم حذف کنیم. البته این هم مسئله پیچیده ای نیست با توابع متنوعی میتونیم نتیجه این توابع رو محدود کنیم و یا انتخاب کنیم که کدوم قسمت ها نمایش داده بشن . (این نکات رو در اپدیت دوره نینجا ارائه کردیم)
- این توابع در Table کار نمیکنن. یعنی اگر یک Spill Range رو تبدیل به Table کنیم، تبدیل میشه اما توابع به خطای #SPILL! تبدیل میشن.
- داده های خروجی این توابع در پاورکوئری قابل استفاده نیستن.
در این مقاله سعی کردیم نگاهی به توابع جدید اکسل ۲۰۲۱ (که جزء توابع Dynamic Array هستند) داشته باشیم و منطق این توابع که در ورژن ۲۰۲۱ اضافه شدن رو بررسی کنیم. پس اگر هنوز ورژن ۲۰۲۱ رو نصب نکردید حتما این کار و انجام بدید و از قدرت این توابع حسابی بهره ببرید.
سلام وقت بخیر
من چند تا شیط مختلف برای حقوق هرماه پرسنل دارم و یک فیش حقوق طراحی کردم
لطفا راهنمایی بفرمایید چه فرمولی باید در قسمت فیش حقوق بنویسم که فقط ماه کارکرد که عوض بشه مبالغ همون ماه رو نشونم بده
درود بر شما
با ترکیب 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 رو . تعیین کنید
با سلام چگونه می توان تعداد اعداد یا متن های تکرار شده در چندین شیت را یکجا پیدا کرد؟
مثلا اگه کلمه احمد در شیت یک دو بار و در شیت دو سه بار تکرار شده است این تکرار که پنج تا می شود در یک شیت این عدد را بدست آورد؟
درورد، برای انجام این کار می بایست کد وی بی نوشته بشه
کد زیر زیر را در یک ماژول قرارداده و اجرا کنید:
با سلام و عرض احترام
آفرین و هزاران آفرین بر شمادو زوج نخبه.
سپاس…
تشکر جناب شیوانی
لطف دارید