LAMBDA هي وظيفة Excel الجديدة الفائقة

في الوقت الحالي ، يحتوي Microsoft Excel على ما يقرب من خمسمائة وظيفة ورقة عمل متاحة من خلال نافذة معالج الوظائف - زر fx في شريط الصيغة. هذه مجموعة جيدة جدًا ، ولكن ، مع ذلك ، يواجه كل مستخدم تقريبًا ، عاجلاً أم آجلاً ، موقفًا لا تحتوي فيه هذه القائمة على الوظيفة التي يحتاجها - ببساطة لأنها ليست في Excel.

حتى الآن ، كانت الطريقة الوحيدة لحل هذه المشكلة هي وحدات الماكرو ، أي كتابة دالة محددة بواسطة المستخدم (UDF = وظيفة محددة من قبل المستخدم) في Visual Basic ، والتي تتطلب مهارات برمجة مناسبة ، وفي بعض الأحيان ، ليست سهلة على الإطلاق. ومع ذلك ، مع آخر تحديثات Office 365 ، تغير الوضع للأفضل - تمت إضافة وظيفة "مجمّع" خاصة إلى Excel LAMBDA. بفضل مساعدتها ، يتم الآن حل مهمة إنشاء الوظائف الخاصة بك بسهولة وبشكل جميل.

دعونا نلقي نظرة على مبدأ استخدامه في المثال التالي.

كما تعلم على الأرجح ، يحتوي Excel على العديد من وظائف تحليل التاريخ التي تسمح لك بتحديد عدد اليوم والشهر والأسبوع والسنة لتاريخ معين. لكن لسبب ما لا توجد دالة تحدد عدد ربع السنة ، وهو أمر مطلوب أيضًا في كثير من الأحيان ، أليس كذلك؟ دعونا نصلح هذا القصور وننشئ باستخدام LAMBDA وظيفة جديدة لحل هذه المشكلة.

الخطوة 1. اكتب الصيغة

لنبدأ بحقيقة أنه يدويًا بالطريقة المعتادة سنكتب صيغة في خلية ورقة تحسب ما نحتاجه. في حالة رقم الربع ، يمكن القيام بذلك ، على سبيل المثال ، مثل هذا:

الخطوة 2. الالتفاف في LAMBDA والاختبار

حان الوقت الآن لتطبيق وظيفة LAMBDA الجديدة ولف صيغتنا فيها. بناء جملة الدالة كما يلي:

= LAMBDA (متغير 1; متغير 2. ... متغير ; التعبير)

حيث يتم سرد أسماء متغير واحد أو أكثر أولاً ، وتكون الوسيطة الأخيرة دائمًا صيغة أو تعبيرًا محسوبًا يستخدمها. يجب ألا تبدو أسماء المتغيرات كعناوين خلية ويجب ألا تحتوي على نقاط.

في حالتنا ، سيكون هناك متغير واحد فقط - التاريخ الذي نحسب فيه رقم ربع السنة. دعنا نسمي المتغير لها ، لنقل ، د. ثم نلف الصيغة في دالة LAMBDA واستبدال عنوان الخلية الأصلية A2 باسم متغير وهمي ، نحصل على:

يرجى ملاحظة أنه بعد هذا التحويل ، بدأت الصيغة (في الواقع ، صحيحة!) في ظهور خطأ ، لأنه الآن لم يتم نقل التاريخ الأصلي من الخلية A2 إليها. للاختبار والثقة ، يمكنك تمرير الحجج إليها عن طريق إضافتها بعد الوظيفة LAMBDA بين قوسين:

الخطوة 3. قم بإنشاء اسم

الآن للجزء السهل والممتع. نفتح مدير الاسم علامة التبويب معادلة (الصيغ - مدير الاسم) وإنشاء اسم جديد باستخدام الزر إنشاء (خلق). ابتكر وأدخل اسمًا لوظيفتنا المستقبلية (على سبيل المثال ، نومكفارتالا) وفي الميدان لينك (مرجع) انسخ بعناية من شريط الصيغة والصق وظيفتنا LAMBDA، فقط بدون الوسيطة الأخيرة (A2):

كل شىء. بعد الضغط على OK يمكن استخدام الوظيفة التي تم إنشاؤها في أي خلية في أي ورقة من هذا المصنف:

استخدم في كتب أخرى

المصفوفات الديناميكية و LAMBDA

وظائف مخصصة تم إنشاؤها باستخدام وظيفة LAMBDA دعم بنجاح العمل مع المصفوفات الديناميكية الجديدة ووظائفها (منقي, فريدة من نوعها, درجة) تم إضافته إلى Microsoft Excel في عام 2020.

لنفترض أننا نريد إنشاء وظيفة جديدة معرّفة من قبل المستخدم تقارن قائمتين وتعيد الفرق بينهما - تلك العناصر من القائمة الأولى غير الموجودة في الثانية. عمل الحياة ، أليس كذلك؟ في السابق ، استخدموا لهذا الغرض أيًا من الوظائف VPR (البحث)أو PivotTables أو Power Query. الآن يمكنك القيام بصيغة واحدة:

في النسخة الإنجليزية سيكون:

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

هنا الوظيفة COUNTIF تحسب عدد تكرارات كل عنصر من عناصر القائمة الأولى في الثانية ، ثم الوظيفة منقي يختار فقط أولئك الذين لم تحدث هذه التكرارات منهم. عن طريق التفاف هذا الهيكل في LAMBDA وإنشاء نطاق مسمى بناءً عليه باسم ، على سبيل المثال ، توزيع البحث - سنحصل على دالة ملائمة تُرجع نتيجة مقارنة قائمتين في شكل مصفوفة ديناميكية:

إذا لم تكن بيانات المصدر عادية ، لكنها جداول "ذكية" ، فإن وظيفتنا ستتعامل أيضًا دون مشاكل:

مثال آخر هو تقسيم النص ديناميكيًا عن طريق تحويله إلى XML ثم تحليله خلية تلو الأخرى باستخدام وظيفة FILTER.XML التي قمنا بتحليلها مؤخرًا. من أجل عدم إعادة إنتاج هذه الصيغة المعقدة يدويًا في كل مرة ، سيكون من الأسهل لفها في LAMBDA وإنشاء نطاق ديناميكي قائم على أساسها ، أي وظيفة مدمجة ومناسبة جديدة ، تسميتها ، على سبيل المثال ، RAZDTEXT:

ستكون الوسيطة الأولى لهذه الوظيفة هي الخلية التي تحتوي على نص المصدر ، والثانية - الحرف الفاصل ، وستعيد النتيجة في شكل مصفوفة ديناميكية أفقية. سيكون رمز الوظيفة كما يلي:

= LAMBDA (t;d؛ TRANSPOSE (تصفية .XML (“"&بديل(t;d؟ â € œ«) &»"؛" // Y ")))

قائمة الأمثلة لا حصر لها - في أي موقف تضطر فيه غالبًا إلى إدخال نفس الصيغة الطويلة والمرهقة ، ستجعل وظيفة LAMBDA الحياة أسهل بشكل ملحوظ.

تعداد متكرر للأحرف

أظهرت جميع الأمثلة السابقة جانبًا واحدًا فقط ، وهو الأكثر وضوحًا ، من وظيفة LAMBDA - استخدامه كـ "غلاف" لتغليف الصيغ الطويلة فيه وتبسيط مدخلاتها. في الواقع ، تمتلك LAMBDA جانبًا آخر أعمق بكثير يحولها إلى لغة برمجة كاملة تقريبًا.

الحقيقة هي أن الميزة الأساسية المهمة لوظائف LAMBDA هي القدرة على تنفيذها في العودية - منطق العمليات الحسابية ، عندما تستدعي الوظيفة نفسها في عملية الحساب. من العادة ، قد يبدو الأمر مخيفًا ، لكن في البرمجة ، يعد التكرار أمرًا شائعًا. حتى في وحدات الماكرو في Visual Basic ، يمكنك تنفيذه ، والآن ، كما ترى ، وصل إلى Excel. دعنا نحاول فهم هذه التقنية بمثال عملي.

لنفترض أننا نريد إنشاء وظيفة معرّفة من قبل المستخدم من شأنها إزالة جميع الأحرف المعطاة من النص المصدر. أعتقد أن فائدة مثل هذه الوظيفة لا تحتاج إلى إثبات - سيكون من الملائم جدًا مسح بيانات الإدخال المتناثرة بمساعدتها ، أليس كذلك؟

ومع ذلك ، بالمقارنة مع الأمثلة السابقة غير العودية ، هناك صعوبتان تنتظراننا.

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 بسلسلة نصية فارغة ، أي يزيل " أ". كنتيجة وسيطة ، نحصل على:

Vsh zkz n 125 روبل.

التكرار 2

ثم تستدعي الوظيفة نفسها وكمدخل (الوسيطة الأولى) تتلقى ما تبقى بعد التنظيف في الخطوة السابقة ، والوسيطة الثانية هي سلسلة الأحرف المستبعدة التي لا تبدأ من الحرف الأول ، ولكن من الحرف الثاني ، أي “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ، "بدون الحرف الأولي" A "- يتم ذلك بواسطة وظيفة MID. كما في السابق ، تأخذ الوظيفة الحرف الأول من يسار الأحرف المتبقية (B) وتستبدلها في النص المعطى لها (Zkz n 125 روبل) بسلسلة فارغة - نحصل على نتيجة وسيطة:

125 رو.

التكرار 3

تستدعي الوظيفة نفسها مرة أخرى ، وتتلقى كأول وسيط ما تبقى من النص ليتم مسحه في التكرار السابق (Bsh zkz n 125 ru.) ، وكوسيطة ثانية ، يتم اقتطاع مجموعة الأحرف المستبعدة بحرف واحد آخر إلى اليسار ، أي "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA." بدون الحرف "B" الأولي. ثم يأخذ مرة أخرى الحرف الأول من اليسار (B) من هذه المجموعة ويزيله من النص - نحصل على:

ش زكز ن 125 رو.

وهكذا - أتمنى أن تكون قد فهمت الفكرة. مع كل تكرار ، سيتم اقتطاع قائمة الأحرف المراد إزالتها على اليسار ، وسنبحث عن الحرف التالي من المجموعة واستبداله بالفراغ.

عندما تنفد جميع الشخصيات ، سنحتاج إلى الخروج من الحلقة - يتم تنفيذ هذا الدور فقط بواسطة الوظيفة IF (إذا)، حيث يتم تغليف تصميمنا. إذا لم تكن هناك أحرف متبقية لحذفها (d = ””) ، فلا يجب أن تستدعي الوظيفة نفسها بعد الآن ، ولكن يجب ببساطة إرجاع النص المراد مسحه (المتغير t) في شكله النهائي.

التكرار العودي للخلايا

وبالمثل ، يمكنك تنفيذ تعداد تعاودي للخلايا في نطاق معين. لنفترض أننا نريد إنشاء دالة lambda مسماة قائمة الاستبدال لاستبدال الأجزاء في النص المصدر بالجملة وفقًا لقائمة مرجعية معينة. يجب أن تبدو النتيجة كما يلي:

أولئك. في وظيفتنا قائمة الاستبدال ستكون هناك ثلاث حجج:

1. خلية مع نص للمعالجة (عنوان المصدر)
2. الخلية الأولى في العمود الذي يحتوي على قيم للبحث من البحث
3. الخلية الأولى من العمود بقيم الاستبدال من البحث

يجب أن تنتقل الوظيفة من أعلى إلى أسفل في الدليل وأن تستبدل بالتسلسل جميع الخيارات من العمود الأيسر لايجاد إلى الخيارات المقابلة من العمود الأيمن استبدل. يمكنك تنفيذ ذلك باستخدام دالة lambda العودية التالية:

يتم تنفيذ التحول لأسفل عند كل تكرار بواسطة دالة Excel قياسية تصرف (عوض)، والذي يحتوي في هذه الحالة على ثلاث وسيطات - النطاق الأصلي وإزاحة الصف (1) وإزاحة العمود (0).

حسنًا ، بمجرد أن نصل إلى نهاية الدليل (n = "") ، يجب أن ننهي العودية - نتوقف عن استدعاء أنفسنا ونعرض ما تراكم بعد كل الاستبدالات في متغير النص المصدر t.

هذا كل شئ. لا توجد وحدات ماكرو صعبة أو استعلامات Power Query - يتم حل المهمة بأكملها بواسطة وظيفة واحدة.

• كيفية استخدام وظائف الصفيف الديناميكي الجديدة في Excel: FILTER ، SORT ، UNIC
• استبدال النص وتنظيفه بوظيفة الاستبدال
  
• إنشاء وحدات ماكرو ووظائف معرّفة من قبل المستخدم (UDFs) في VBA