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

۴.۷/۵ - (۱۰ امتیاز)

آنچه در این مقاله میخوانید:

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

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

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

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

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

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

با انتشار نسخه جدید اکسل و اضافه شدن توابع جدید اکسل ۲۰۲۱، فرمول نویسی آرایه ای مورد توجه زیادی قرار گرفته. چرا که توابعی تحت عنوان 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 هستند) داشته باشیم و منطق این توابع که در ورژن ۲۰۲۱ اضافه شدن رو بررسی کنیم. پس اگر هنوز ورژن ۲۰۲۱ رو نصب نکردید حتما این کار و انجام بدید و از قدرت این توابع حسابی بهره ببرید.

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

پاسخ

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

Sub CountDuplicates()
Dim ws As Worksheet, wss As Worksheet
Dim cell As Range
Dim rng As Range
Dim str As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Set rng = Application.InputBox("Select range:", "Count duplicates", Type:=8)
For Each wss In ThisWorkbook.Worksheets
    For Each cell In wss.Range(rng.Address)
        If cell <> "" Then
            If Not dict.exists(cell.Value) Then
                dict.Add cell.Value, 1
            Else
                dict.Item(cell.Value) = dict.Item(cell.Value) + 1
            End If
        End If
    Next cell
Next wss
Set ws = ThisWorkbook.Sheets.Add
ws.Name = "Duplicates"
ws.Range("A1").Value = "Value"
ws.Range("B1").Value = "Count"
For Each str In dict.keys
    ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = str
    ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1, 0).Value = dict.Item(str)
Next str
End Sub

۱۰

۱۱

۱۲

۱۳

۱۴

۱۵

۱۶

۱۷

۱۸

۱۹

۲۰

۲۱

۲۲

۲۳

۲۴

۲۵

۲۶

۲۷

۲۸

Sub CountDuplicates()

Dim ws As Worksheet, wss As Worksheet

Dim cell As Range

Dim rng As Range

Dim str As Variant

Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

Set rng = Application.InputBox("Select range:", "Count duplicates", Type:=۸)

For Each wss In ThisWorkbook.Worksheets

For Each cell In wss.Range(rng.Address)

If cell <> "" Then

If Not dict.exists(cell.Value) Then

dict.Add cell.Value, ۱

Else

dict.Item(cell.Value) = dict.Item(cell.Value) + ۱

End If

Next cell

Next wss

Set ws = ThisWorkbook.Sheets.Add

ws.Name = "Duplicates"

ws.Range("A1").Value = "Value"

ws.Range("B1").Value = "Count"

For Each str In dict.keys

ws.Cells(ws.Rows.Count, ۱).End(xlUp).Offset(۱, ۰).Value = str

ws.Cells(ws.Rows.Count, ۲).End(xlUp).Offset(۱, ۰).Value = dict.Item(str)

Next str

End Sub

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

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

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

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

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

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

سامان چراغی 134