LAMBDA عملکرد فوق العاده جدید اکسل است

در حال حاضر، مایکروسافت اکسل تقریباً پانصد عملکرد کاربرگ را از طریق دکمه پنجره Function Wizard در دسترس دارد fx در نوار فرمول این یک مجموعه بسیار مناسب است، اما، با این وجود، تقریباً هر کاربر دیر یا زود با موقعیتی مواجه می شود که در آن این لیست شامل عملکرد مورد نیاز او نیست - فقط به این دلیل که در اکسل نیست.

تا به حال، تنها راه حل این مشکل ماکروها بود، یعنی نوشتن تابع تعریف شده توسط کاربر (UDF = User Defined Function) در ویژوال بیسیک، که نیاز به مهارت های برنامه نویسی مناسب دارد و در بعضی مواقع اصلاً آسان نیست. با این حال، با آخرین به‌روزرسانی‌های آفیس 365، وضعیت به سمت بهتر شدن تغییر کرده است - یک تابع ویژه "wrapper" به اکسل اضافه شده است. لامبدا. با کمک آن، کار ایجاد توابع خود اکنون به راحتی و زیبایی حل شده است.

بیایید به اصل استفاده از آن در مثال زیر نگاه کنیم.

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

مرحله 1. فرمول را بنویسید

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

مرحله 2. بسته بندی در LAMBDA و آزمایش

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

=LAMBDA(متغیر 1; متغیر 2؛ ... متغیرN ; اصطلاح)

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

در مورد ما، تنها یک متغیر وجود خواهد داشت - تاریخی که برای آن عدد سه ماهه را محاسبه می کنیم. بیایید متغیر مربوط به آن را صدا کنیم، مثلاً d. سپس فرمول خود را در یک تابع قرار می دهیم لامبدا و با جایگزین کردن آدرس سلول اصلی A2 با نام متغیر ساختگی، دریافت می کنیم:

لطفاً توجه داشته باشید که پس از چنین تبدیلی، فرمول ما (در واقع درست است!) شروع به ایجاد خطا کرد، زیرا اکنون تاریخ اصلی از سلول A2 به آن منتقل نمی شود. برای تست و اطمینان، می توانید آرگومان هایی را با اضافه کردن آنها بعد از تابع به آن ارسال کنید لامبدا داخل پرانتز:

مرحله 3. یک نام ایجاد کنید

حالا برای بخش آسان و سرگرم کننده. باز میکنیم مدیر نام برگ فرمول (فرمول ها - مدیر نام) و با دکمه یک نام جدید ایجاد کنید ساختن (ایجاد کردن). یک نام برای تابع آینده خود بیابید و وارد کنید (به عنوان مثال، نومکوارتلا) و در میدان ارتباط دادن (ارجاع) با دقت از نوار فرمول کپی کنید و تابع ما را Paste کنید لامبدا، فقط بدون آخرین آرگومان (A2):

همه چيز. پس از کلیک بر روی OK تابع ایجاد شده را می توان در هر سلول در هر صفحه از این کتاب کار استفاده کرد:

در کتاب های دیگر استفاده کنید

LAMBDA و آرایه های پویا

توابع سفارشی ایجاد شده با یک تابع لامبدا با موفقیت از کار با آرایه های پویا جدید و عملکردهای آنها پشتیبانی می کند (FILTER, یونیک, GRADE) در سال 2020 به مایکروسافت اکسل اضافه شد.

فرض کنید می‌خواهیم یک تابع تعریف‌شده توسط کاربر جدید ایجاد کنیم که دو لیست را با هم مقایسه کرده و تفاوت بین آنها را برگرداند - آن عناصر از لیست اول که در لیست دوم نیستند. کار زندگی است، اینطور نیست؟ قبلاً برای این کار از هر دو تابع a la استفاده می کردند VPR (VLOOKUP)، یا PivotTables یا Query های Power Query. اکنون می توانید با یک فرمول انجام دهید:

در نسخه انگلیسی این خواهد بود:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

در اینجا تابع COUNTIF تعداد دفعات هر عنصر از لیست اول را در لیست دوم و سپس تابع را می شمارد FILTER فقط کسانی از آنها را انتخاب می کند که این اتفاقات را نداشته اند. با پیچیدن این ساختار در لامبدا و ایجاد یک محدوده با نام بر اساس آن با یک نام، برای مثال، توزیع جستجو - یک تابع راحت دریافت می کنیم که نتیجه مقایسه دو لیست را در قالب یک آرایه پویا برمی گرداند:

اگر داده های منبع جداول معمولی نیستند، اما "هوشمند" هستند، عملکرد ما نیز بدون مشکل از پس آن بر می آید:

مثال دیگر تقسیم پویا متن با تبدیل آن به XML و سپس تجزیه سلول به سلول با استفاده از تابع FILTER.XML است که اخیرا آن را تجزیه کردیم. برای اینکه این فرمول پیچیده را هر بار به صورت دستی بازتولید نکنیم، پیچیده کردن آن در LAMBDA و ایجاد یک محدوده دینامیکی بر اساس آن، یعنی یک عملکرد فشرده و راحت جدید، آسان تر خواهد بود و نام آن را برای مثال RAZDTEXT می گذاریم:

اولین آرگومان این تابع سلول با متن مبدا و دومی کاراکتر جداکننده خواهد بود و نتیجه را در قالب یک آرایه پویا افقی برمی گرداند. کد تابع به صورت زیر خواهد بود:

=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“"&جایگزین(t;d؟ "«)&»";"//Y")))

فهرست نمونه‌ها بی‌پایان است - در هر موقعیتی که اغلب مجبور هستید همان فرمول طولانی و دست و پا گیر را وارد کنید، عملکرد LAMBDA زندگی را به طرز چشمگیری آسان‌تر می‌کند.

شمارش بازگشتی کاراکترها

تمام مثال‌های قبلی تنها یک، واضح‌ترین وجه تابع LAMBDA را نشان داده‌اند - استفاده از آن به‌عنوان «لفاف» برای قرار دادن فرمول‌های طولانی در آن و ساده‌سازی ورودی آن‌ها. در واقع، LAMBDA جنبه دیگری بسیار عمیق‌تر دارد که آن را به یک زبان برنامه‌نویسی تقریباً کامل تبدیل می‌کند.

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

فرض کنید می خواهیم یک تابع تعریف شده توسط کاربر ایجاد کنیم که تمام کاراکترهای داده شده را از متن منبع حذف کند. مفید بودن چنین عملکردی، من فکر می کنم، نیازی به اثبات ندارید - پاک کردن داده های ورودی زباله با کمک آن بسیار راحت است، درست است؟

با این حال، در مقایسه با نمونه های قبلی و غیر بازگشتی، دو مشکل در انتظار ما است.

1. قبل از شروع نوشتن کد تابع خود، باید نامی برای تابع خود بیاوریم، زیرا در آن، از این نام برای فراخوانی خود تابع استفاده می شود.
   
2. وارد کردن چنین تابع بازگشتی به یک سلول و اشکال زدایی آن با مشخص کردن آرگومان ها در براکت بعد از LAMBDA (همانطور که قبلا انجام دادیم) کار نخواهد کرد. شما باید فوراً یک تابع "از ابتدا" ایجاد کنید مدیر نام (نام مدیر).
   

بیایید تابع خود را مثلا CLEAN صدا کنیم و دوست داریم دو آرگومان داشته باشد – متنی که باید پاک شود و لیستی از کاراکترهای حذف شده به عنوان یک رشته متن:

بیایید، همانطور که قبلاً انجام دادیم، در برگه ایجاد کنیم فرمول в نام مدیر محدوده نامگذاری شده، نام آن را CLEAR گذاشته و در فیلد وارد کنید محدوده ساخت زیر:

=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))

در اینجا متغیر t متن اصلی است که باید پاک شود و d لیست کاراکترهایی است که باید حذف شوند.

همه چیز به این صورت عمل می کند:

تکرار 1

قطعه SUBSTITUTE(t;LEFT(d);"")، همانطور که ممکن است حدس بزنید، اولین کاراکتر از کاراکتر سمت چپ مجموعه d که در متن مبدأ t حذف می شود را با یک رشته متن خالی جایگزین می کند، یعنی "" را حذف می کند. آ". به عنوان یک نتیجه متوسط، ما دریافت می کنیم:

وش زکز n 125 روبل.

تکرار 2

سپس تابع خود را فراخوانی می کند و به عنوان ورودی (آگومان اول) آنچه را که پس از پاکسازی در مرحله قبل باقی مانده است دریافت می کند و آرگومان دوم رشته ای از کاراکترهای حذف شده است که نه از کاراکتر اول، بلکه از کاراکتر دوم یعنی “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA شروع می شود. "، بدون "A" اولیه - این کار توسط تابع MID ​​انجام می شود. مانند قبل، تابع اولین کاراکتر را از سمت چپ بقیه (B) می گیرد و در متن داده شده به آن (Zkz n 125 روبل) با یک رشته خالی جایگزین می کند - به عنوان یک نتیجه میانی می گیریم:

125 روبل.

تکرار 3

تابع دوباره خود را فراخوانی می کند و آنچه را که از متن باقی مانده است که در تکرار قبلی پاک می شود (Bsh zkz n 125 ru.) به عنوان اولین آرگومان دریافت می کند، و به عنوان آرگومان دوم، مجموعه ای از کاراکترهای حذف شده با یک کاراکتر دیگر کوتاه شده است. سمت چپ، یعنی «VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.»، بدون «B» اولیه. سپس دوباره اولین کاراکتر را از سمت چپ (B) از این مجموعه می گیرد و آن را از متن حذف می کند - دریافت می کنیم:

sh zkz n 125 ru.

و غیره - امیدوارم ایده را دریافت کرده باشید. با هر بار تکرار، لیست کاراکترهایی که باید حذف شوند در سمت چپ کوتاه می شود و کاراکتر بعدی مجموعه را با یک خالی جستجو کرده و جایگزین می کنیم.

وقتی همه کاراکترها تمام شدند، باید از حلقه خارج شویم - این نقش فقط توسط تابع انجام می شود IF (اگر)، که طرح ما در آن پیچیده شده است. اگر هیچ کاراکتری برای حذف باقی نمانده باشد (d=””)، پس تابع دیگر نباید خود را فراخوانی کند، بلکه باید متنی را که باید پاک شود (متغیر t) را به شکل نهایی بازگرداند.

تکرار بازگشتی سلول ها

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

آن ها در عملکرد ما لیست جایگزین سه استدلال وجود خواهد داشت:

1. سلول با متن برای پردازش (آدرس منبع)
2. اولین سلول یک ستون با مقادیری برای جستجو از جستجو
3. اولین سلول ستون با مقادیر جایگزین از جستجو

تابع باید از بالا به پایین در دایرکتوری برود و تمام گزینه های ستون سمت چپ را به ترتیب جایگزین کند برای پیدا کردن به گزینه های مربوطه از ستون سمت راست جایگزین. شما می توانید این را با تابع لامبدا بازگشتی زیر پیاده سازی کنید:

Shift down در هر تکرار توسط یک تابع استاندارد اکسل پیاده سازی می شود دسترس (انحراف)، که در این مورد دارای سه آرگومان است - محدوده اصلی، تغییر ردیف (1) و تغییر ستون (0).

خوب، به محض اینکه به انتهای دایرکتوری رسیدیم (n = "")، باید بازگشت را پایان دهیم - ما خود را فراخوانی نمی کنیم و آنچه را که پس از همه جایگزینی ها در متغیر متن منبع t جمع شده است، نمایش می دهیم.

همین. بدون ماکروهای پیچیده یا کوئری های Power Query - کل کار با یک تابع حل می شود.

• نحوه استفاده از توابع آرایه پویا جدید اکسل: FILTER، SORT، UNIC
• جایگزین کردن و پاک کردن متن با عملکرد SUBSTITUTE
  
• ایجاد ماکروها و توابع تعریف شده توسط کاربر (UDF) در VBA