लैम्ब्डा एक्सेल का नया सुपर फंक्शन है

फिलहाल, माइक्रोसॉफ्ट एक्सेल में फंक्शन विजार्ड विंडो - बटन के माध्यम से लगभग पांच सौ वर्कशीट फंक्शन उपलब्ध हैं fx सूत्र पट्टी में। यह एक बहुत ही अच्छा सेट है, लेकिन, फिर भी, लगभग हर उपयोगकर्ता जल्द या बाद में ऐसी स्थिति का सामना करता है जहां इस सूची में वह फ़ंक्शन नहीं होता है जिसकी उसे आवश्यकता होती है - केवल इसलिए कि यह एक्सेल में नहीं है।

अब तक, इस समस्या को हल करने का एकमात्र तरीका मैक्रोज़ था, यानी विज़ुअल बेसिक में अपना खुद का उपयोगकर्ता-परिभाषित फ़ंक्शन (यूडीएफ = उपयोगकर्ता परिभाषित फ़ंक्शन) लिखना, जिसके लिए उपयुक्त प्रोग्रामिंग कौशल की आवश्यकता होती है और कभी-कभी, बिल्कुल भी आसान नहीं होता है। हालांकि, नवीनतम Office 365 अपडेट के साथ, स्थिति बेहतर के लिए बदल गई है - एक्सेल में एक विशेष "रैपर" फ़ंक्शन जोड़ा गया है लैम्ब्डा. इसकी मदद से, अपने स्वयं के कार्यों को बनाने का कार्य अब आसानी से और खूबसूरती से हल हो गया है।

आइए निम्नलिखित उदाहरण में इसके उपयोग के सिद्धांत को देखें।

जैसा कि आप सबसे अधिक जानते हैं, एक्सेल में कई तारीख पार्सिंग फ़ंक्शन हैं जो आपको दी गई तारीख के लिए दिन, महीने, सप्ताह और वर्ष की संख्या निर्धारित करने की अनुमति देते हैं। लेकिन किसी कारण से कोई फ़ंक्शन नहीं है जो तिमाही की संख्या निर्धारित करता है, जिसकी अक्सर आवश्यकता होती है, है ना? आइए इस कमी को ठीक करें और इसके साथ बनाएं लैम्ब्डा इस समस्या को हल करने के लिए खुद का नया कार्य।

चरण 1. सूत्र लिखें

आइए इस तथ्य से शुरू करें कि मैन्युअल रूप से सामान्य तरीके से हम एक शीट सेल में एक सूत्र लिखेंगे जो गणना करता है कि हमें क्या चाहिए। तिमाही संख्या के मामले में, यह किया जा सकता है, उदाहरण के लिए, इस तरह:

चरण 2. लैम्ब्डा और परीक्षण में लपेटना

अब समय आ गया है कि हम नए लैम्ब्डा फंक्शन को लागू करें और उसमें अपना फॉर्मूला लपेटें। फ़ंक्शन सिंटैक्स इस प्रकार है:

= लैम्ब्डा (चर1; चर2; ... चरN ; अभिव्यक्ति)

जहां एक या अधिक चरों के नाम पहले सूचीबद्ध होते हैं, और अंतिम तर्क हमेशा एक सूत्र या परिकलित व्यंजक होता है जो उनका उपयोग करता है। परिवर्तनीय नाम सेल पते की तरह नहीं दिखना चाहिए और इसमें डॉट्स नहीं होने चाहिए।

हमारे मामले में, केवल एक चर होगा - वह तिथि जिसके लिए हम तिमाही संख्या की गणना करते हैं। आइए इसके लिए वेरिएबल को कॉल करें, कहते हैं, d. फिर हमारे सूत्र को एक फ़ंक्शन में लपेटना लैम्ब्डा और मूल सेल A2 के पते को एक काल्पनिक चर नाम से प्रतिस्थापित करने पर, हम प्राप्त करते हैं:

कृपया ध्यान दें कि इस तरह के परिवर्तन के बाद, हमारे सूत्र (वास्तव में, सही!) ने एक त्रुटि उत्पन्न करना शुरू कर दिया, क्योंकि अब सेल ए 2 से मूल तिथि को स्थानांतरित नहीं किया गया है। परीक्षण और आत्मविश्वास के लिए, आप फ़ंक्शन के बाद उन्हें जोड़कर तर्क पारित कर सकते हैं लैम्ब्डा कोष्ठकों में:

चरण 3. एक नाम बनाएँ

अब आसान और मजेदार भाग के लिए। हम खुलेंगे नाम प्रबंधक टैब सूत्र (सूत्र - नाम प्रबंधक) और बटन के साथ एक नया नाम बनाएं बनाएं (सृजन करना). हमारे भविष्य के कार्य के लिए एक नाम दर्ज करें और दर्ज करें (उदाहरण के लिए, नोम्क्वर्तला), और क्षेत्र में संपर्क (संदर्भ) फॉर्मूला बार से सावधानीपूर्वक कॉपी करें और हमारे फंक्शन को पेस्ट करें लैम्ब्डा, केवल अंतिम तर्क के बिना (A2):

हर चीज़। पर क्लिक करने के बाद OK इस कार्यपुस्तिका की किसी भी शीट पर किसी भी सेल में निर्मित फ़ंक्शन का उपयोग किया जा सकता है:

अन्य पुस्तकों में प्रयोग करें

लैम्ब्डा और गतिशील सरणियाँ

फ़ंक्शन के साथ बनाए गए कस्टम फ़ंक्शन लैम्ब्डा नए गतिशील सरणियों और उनके कार्यों के साथ सफलतापूर्वक समर्थन कार्य (फ़िल्टर, अद्वितीय, ग्रेड) 2020 में माइक्रोसॉफ्ट एक्सेल में जोड़ा गया।

मान लीजिए कि हम एक नया उपयोगकर्ता-परिभाषित फ़ंक्शन बनाना चाहते हैं जो दो सूचियों की तुलना करेगा और उनके बीच के अंतर को लौटाएगा - पहली सूची के वे तत्व जो दूसरी सूची में नहीं हैं। जीवन का काम, है ना? पहले, इसके लिए वे या तो फ़ंक्शन का उपयोग करते थे a la VPR (VLOOKUP), या PivotTables, या Power Query क्वेरीज़। अब आप एक सूत्र के साथ कर सकते हैं:

अंग्रेजी संस्करण में यह होगा:

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

यहाँ समारोह COUNTIF दूसरी सूची में पहली सूची के प्रत्येक तत्व की घटनाओं की संख्या की गणना करता है, और फिर फ़ंक्शन फ़िल्टर उनमें से केवल उन्हीं का चयन करता है जिनके पास ये घटनाएँ नहीं थीं। इस संरचना को में लपेटकर लैम्ब्डा और इसके आधार पर एक नाम के साथ एक नामित श्रेणी बनाना, उदाहरण के लिए, खोज वितरण - हमें एक सुविधाजनक फ़ंक्शन मिलेगा जो गतिशील सरणी के रूप में दो सूचियों की तुलना करने का परिणाम देता है:

यदि स्रोत डेटा सामान्य नहीं है, लेकिन "स्मार्ट" टेबल है, तो हमारा कार्य भी समस्याओं के बिना सामना करेगा:

एक अन्य उदाहरण टेक्स्ट को एक्सएमएल में परिवर्तित करके गतिशील रूप से विभाजित करना और फिर फ़िल्टर.एक्सएमएल फ़ंक्शन का उपयोग करके सेल द्वारा सेल को पार्स करना है जिसे हमने हाल ही में पार्स किया है। इस जटिल सूत्र को हर बार मैन्युअल रूप से पुन: पेश न करने के लिए, इसे LAMBDA में लपेटना और इसके आधार पर एक गतिशील रेंज बनाना आसान होगा, यानी एक नया कॉम्पैक्ट और सुविधाजनक फ़ंक्शन, इसका नामकरण, उदाहरण के लिए, RAZDTEXT:

इस फ़ंक्शन का पहला तर्क स्रोत टेक्स्ट वाला सेल होगा, और दूसरा - विभाजक वर्ण, और यह परिणाम को क्षैतिज गतिशील सरणी के रूप में वापस कर देगा। फ़ंक्शन कोड इस प्रकार होगा:

= लैम्ब्डा (t;d; ट्रांसपोज़ (फ़िल्टर.एक्सएमएल(“"&स्थानापन्न(t;d? "«)&»";"//Y")))

उदाहरणों की सूची अंतहीन है - किसी भी स्थिति में जहां आपको अक्सर एक ही लंबा और बोझिल फॉर्मूला दर्ज करना पड़ता है, लैम्ब्डा फ़ंक्शन जीवन को काफी आसान बना देगा।

वर्णों की पुनरावर्ती गणना

पिछले सभी उदाहरणों ने लैम्ब्डा फ़ंक्शन का केवल एक, सबसे स्पष्ट, पक्ष दिखाया है - इसमें लंबे फ़ार्मुलों को लपेटने और उनके इनपुट को सरल बनाने के लिए "रैपर" के रूप में इसका उपयोग। वास्तव में, लैम्ब्डा का एक और, बहुत गहरा, पक्ष है जो इसे लगभग एक पूर्ण प्रोग्रामिंग भाषा में बदल देता है।

तथ्य यह है कि लैम्ब्डा कार्यों की मौलिक रूप से महत्वपूर्ण विशेषता उन्हें लागू करने की क्षमता है प्रत्यावर्तन - गणना का तर्क, जब गणना की प्रक्रिया में फ़ंक्शन स्वयं कॉल करता है। आदत से यह डरावना लग सकता है, लेकिन प्रोग्रामिंग में, रिकर्सन एक सामान्य बात है। विजुअल बेसिक में मैक्रोज़ में भी, आप इसे लागू कर सकते हैं, और अब, जैसा कि आप देख सकते हैं, यह एक्सेल में आ गया है। आइए इस तकनीक को एक व्यावहारिक उदाहरण से समझने की कोशिश करते हैं।

मान लीजिए कि हम एक उपयोगकर्ता-परिभाषित फ़ंक्शन बनाना चाहते हैं जो स्रोत टेक्स्ट से सभी दिए गए वर्णों को हटा देगा। इस तरह के एक समारोह की उपयोगिता, मुझे लगता है, आपको साबित करने की आवश्यकता नहीं है - इसकी मदद से भरे हुए इनपुट डेटा को साफ़ करना बहुत सुविधाजनक होगा, है ना?

हालांकि, पिछले, गैर-पुनरावर्ती उदाहरणों की तुलना में, दो कठिनाइयां हमारा इंतजार कर रही हैं।

1. हमें अपने फ़ंक्शन के लिए कोड लिखना शुरू करने से पहले एक नाम के साथ आना होगा, क्योंकि इसमें यह नाम पहले से ही फ़ंक्शन को कॉल करने के लिए उपयोग किया जाएगा।
   
2. इस तरह के एक पुनरावर्ती फ़ंक्शन को एक सेल में दर्ज करना और LAMBDA (जैसा कि हमने पहले किया था) के बाद कोष्ठक में तर्क निर्दिष्ट करके इसे डीबग करना काम नहीं करेगा। आपको तुरंत "स्क्रैच से" एक फ़ंक्शन बनाना होगा नाम प्रबंधक (नाम प्रबंधक).
   

आइए हमारे फ़ंक्शन को कॉल करें, कहते हैं, CLEAN और हम चाहेंगे कि इसमें दो तर्क हों - टेक्स्ट को साफ किया जाए और टेक्स्ट स्ट्रिंग के रूप में बहिष्कृत वर्णों की सूची:

आइए बनाएं, जैसा कि हमने पहले किया था, टैब पर सूत्र в नाम प्रबंधक नामित श्रेणी, इसे नाम दें साफ़ करें और फ़ील्ड में दर्ज करें रेंज निम्नलिखित निर्माण:

= लैम्ब्डा (टी; डी; आईएफ (डी = ""; टी; स्पष्ट (विकल्प (टी; बाएं (डी);""); मध्य (डी; 2; 255))))

यहां वेरिएबल t क्लियर किया जाने वाला मूल टेक्स्ट है, और d डिलीट किए जाने वाले कैरेक्टर्स की लिस्ट है।

यह सब इस तरह काम करता है:

चलना 1

खंड SUBSTITUTE(t;LEFT(d);”"), जैसा कि आप अनुमान लगा सकते हैं, सेट d से बाएं वर्ण के पहले वर्ण को स्रोत टेक्स्ट t में हटाए जाने के लिए एक खाली टेक्स्ट स्ट्रिंग के साथ बदल देता है, अर्थात " ए"। मध्यवर्ती परिणाम के रूप में, हम प्राप्त करते हैं:

Vsh zkz n 125 रूबल।

चलना 2

फिर फ़ंक्शन स्वयं को कॉल करता है और इनपुट के रूप में (पहला तर्क) प्राप्त करता है जो पिछले चरण में सफाई के बाद बचा है, और दूसरा तर्क पहले से नहीं, बल्कि दूसरे वर्ण से शुरू होने वाले बहिष्कृत वर्णों की स्ट्रिंग है, अर्थात "BVGDEEGZIKLMNOPRSTUFHTSCHSHSCHHYYYYYA. , प्रारंभिक "ए" के बिना - यह एमआईडी फ़ंक्शन द्वारा किया जाता है। पहले की तरह, फ़ंक्शन शेष लोगों (बी) के बाईं ओर से पहला वर्ण लेता है और इसे दिए गए पाठ (Zkz n 125 रूबल) में एक खाली स्ट्रिंग के साथ बदल देता है - हमें एक मध्यवर्ती परिणाम के रूप में मिलता है:

125 रु.

चलना 3

फ़ंक्शन खुद को फिर से कॉल करता है, पहले तर्क के रूप में प्राप्त करता है जो पिछले पुनरावृत्ति (Bsh zkz n 125 ru.) पर साफ़ किए जाने वाले पाठ के शेष भाग के रूप में प्राप्त होता है, और दूसरे तर्क के रूप में, बहिष्कृत वर्णों का सेट एक और वर्ण द्वारा छोटा कर दिया जाता है बाईं ओर, अर्थात "VGDEEGZIKLMNOPRSTUFHTSCHSHSCHHYYYYUYA।" प्रारंभिक "बी" के बिना। फिर यह फिर से इस सेट से बाएं (बी) से पहला अक्षर लेता है और इसे टेक्स्ट से हटा देता है - हमें मिलता है:

श zkz एन 125 आरयू।

और इसी तरह - मुझे आशा है कि आपको यह विचार मिल गया होगा। प्रत्येक पुनरावृत्ति के साथ, हटाए जाने वाले वर्णों की सूची को बाईं ओर छोटा कर दिया जाएगा, और हम सेट से अगले वर्ण को शून्य से खोजेंगे और प्रतिस्थापित करेंगे।

जब सभी वर्ण समाप्त हो जाते हैं, तो हमें लूप से बाहर निकलने की आवश्यकता होगी - यह भूमिका केवल फ़ंक्शन द्वारा की जाती है IF (अगर), जिसमें हमारा डिज़ाइन लिपटा हुआ है। यदि हटाने के लिए कोई वर्ण नहीं बचा है (डी = ""), तो फ़ंक्शन को अब स्वयं को कॉल नहीं करना चाहिए, लेकिन टेक्स्ट को अपने अंतिम रूप में साफ़ करने के लिए टेक्स्ट को वापस करना चाहिए (चर टी)।

कोशिकाओं का पुनरावर्ती पुनरावृत्ति

इसी तरह, आप किसी दी गई श्रेणी में कोशिकाओं की पुनरावर्ती गणना को लागू कर सकते हैं। मान लीजिए कि हम एक लैम्ब्डा फंक्शन बनाना चाहते हैं जिसका नाम है प्रतिस्थापन सूची किसी दी गई संदर्भ सूची के अनुसार स्रोत पाठ में अंशों के थोक प्रतिस्थापन के लिए। परिणाम इस तरह दिखना चाहिए:

वे। हमारे समारोह में प्रतिस्थापन सूची तीन तर्क होंगे:

1. प्रक्रिया के लिए पाठ के साथ सेल (स्रोत पता)
2. लुकअप से खोजे जाने वाले मानों वाले कॉलम की पहली सेल
3. लुकअप से प्रतिस्थापन मूल्यों के साथ कॉलम का पहला सेल

फ़ंक्शन को निर्देशिका में ऊपर से नीचे तक जाना चाहिए और क्रमिक रूप से बाएं कॉलम से सभी विकल्पों को बदलना चाहिए ढूँढ़ने के लिए दाएँ कॉलम से संबंधित विकल्पों के लिए विकल्प. आप इसे निम्न पुनरावर्ती लैम्ब्डा फ़ंक्शन के साथ कार्यान्वित कर सकते हैं:

प्रत्येक पुनरावृत्ति पर शिफ्ट डाउन एक मानक एक्सेल फ़ंक्शन द्वारा कार्यान्वित किया जाता है निपटान (ऑफसेट), जिसमें इस मामले में तीन तर्क हैं - मूल श्रेणी, पंक्ति शिफ्ट (1) और कॉलम शिफ्ट (0)।

ठीक है, जैसे ही हम निर्देशिका के अंत तक पहुँचते हैं (n = ""), हमें रिकर्सन को समाप्त करना होगा - हम खुद को कॉल करना बंद कर देते हैं और प्रदर्शित करते हैं कि स्रोत टेक्स्ट वेरिएबल टी में सभी प्रतिस्थापन के बाद क्या जमा हुआ है।

बस इतना ही। कोई ट्रिकी मैक्रोज़ या पावर क्वेरी क्वेरी नहीं - पूरा कार्य एक फ़ंक्शन द्वारा हल किया जाता है।

• एक्सेल के नए गतिशील सरणी कार्यों का उपयोग कैसे करें: फ़िल्टर, सॉर्ट, यूनिक
• SUBSTITUTE फ़ंक्शन के साथ टेक्स्ट को बदलना और साफ़ करना
  
• VBA में मैक्रोज़ और यूज़र-डिफ़ाइंड फ़ंक्शंस (UDFs) बनाना