LAMBDA د Excel نوی سوپر فنکشن دی

اوس مهال، مایکروسافټ ایکسل د فنکشن وزرډ کړکۍ - تڼۍ له لارې نږدې پنځه سوه کاري پاڼې شتون لري fx په فورمول بار کې. دا خورا ښه سیټ دی ، مګر په هرصورت ، نږدې هر کارونکي ژر یا وروسته له داسې وضعیت سره مخ کیږي چیرې چې دا لیست هغه فعالیت نلري چې ورته اړتیا لري - یوازې دا چې دا په Excel کې ندي.

تر اوسه پورې، د دې ستونزې د حل کولو یوازینۍ لار میکرو وه، د بیلګې په توګه په بصری اساس کې د خپل کارونکي لخوا ټاکل شوي فنکشن (UDF = د کاروونکي تعریف شوي فنکشن) لیکل، کوم چې د مناسب پروګرام کولو مهارتونو ته اړتیا لري او په ځینو وختونو کې، هیڅ کله هم اسانه ندي. په هرصورت، د وروستي Office 365 تازه معلوماتو سره، وضعیت د ښه لپاره بدل شوی - یو ځانګړی "ریپر" فنکشن په Excel کې اضافه شوی. LAMBDA. د دې په مرسته ، ستاسو د خپلو دندو رامینځته کولو دنده اوس په اسانۍ او ښکلي ډول حل کیږي.

راځئ چې په لاندې مثال کې د هغې د کارولو اصول وګورو.

لکه څنګه چې تاسو ډیری احتمال پوهیږئ، Excel د نیټې د پارس کولو ډیری فعالیتونه لري چې تاسو ته اجازه درکوي د ټاکل شوې نیټې لپاره د ورځې، میاشت، اونۍ او کال شمیر وټاکئ. مګر د ځینو دلیلونو لپاره هیڅ فعالیت شتون نلري چې د ربع شمیره ټاکي، کوم چې ډیری وختونه ورته اړتیا وي، سمه ده؟ راځئ چې دا نیمګړتیا حل کړو او د هغې سره یې جوړ کړو LAMBDA د دې ستونزې د حل لپاره خپل نوی فعالیت.

مرحله 1. فورمول ولیکئ

راځئ چې د دې حقیقت سره پیل وکړو چې په لاسي ډول په معمول ډول موږ به د شیټ حجره کې یو فارمول ولیکو چې هغه څه محاسبه کوي چې موږ ورته اړتیا لرو. د ربعې شمیرې په صورت کې، دا کیدی شي، د بیلګې په توګه، دا:

مرحله 2. په LAMBDA کې لپاسه او ازموینه

اوس د دې وخت دی چې د نوي LAMBDA فنکشن پلي کړئ او زموږ فارمول په دې کې وتړئ. د فنکشن ترکیب په لاندې ډول دی:

= لامبدا(متغیر1; متغیر2؛ … متغیر این ; بیان)

چیرته چې د یو یا ډیرو متغیرونو نومونه لومړی لیست شوي، او وروستی دلیل تل یو فورمول یا حساب شوي بیان دی چې دوی کاروي. متغیر نومونه باید د حجرې پتې په څیر نه ښکاري او باید ټکي ولري.

زموږ په قضیه کې به یوازې یو متغیر وي - هغه نیټه چې موږ د ربع شمیره محاسبه کوو. راځئ چې د دې لپاره متغیر غږ وکړو، ووایه، d. بیا زموږ فورمول په فنکشن کې وتړئ LAMBDA او د اصلي حجرې A2 پته د جعلي متغیر نوم سره ځای په ځای کول، موږ ترلاسه کوو:

مهرباني وکړئ په یاد ولرئ چې د داسې بدلون وروسته، زموږ فورمول (په حقیقت کې، سم!) د تېروتنې تولید پیل کړ، ځکه چې اوس د A2 حجرې اصلي نیټه دې ته نه لیږدول کیږي. د ازموینې او باور لپاره، تاسو کولی شئ د فعالیت وروسته د دوی په اضافه کولو سره دې ته دلیلونه انتقال کړئ LAMBDA په قوسونو کې:

3 ګام. یو نوم جوړ کړئ

اوس د اسانه او ساتیرۍ برخې لپاره. موږ خلاصوو نوم مدیر ټوپ فورمول (فارمول - د نوم مدیر) او د تڼۍ سره یو نوی نوم جوړ کړئ جوړول (جوړول). راشئ او زموږ د راتلونکي فعالیت لپاره نوم دننه کړئ (د مثال په توګه ، Nomkvartala)، او په ساحه کې لینک (حواله) په احتیاط سره د فورمول بار څخه کاپي کړئ او زموږ فعالیت پیسټ کړئ LAMBDAیوازې د وروستي دلیل پرته (A2):

هر څه. د کلیک کولو وروسته OK جوړ شوی فنکشن د دې کاري کتاب په هره پاڼه کې په هر حجره کې کارول کیدی شي:

په نورو کتابونو کې وکاروئ

LAMBDA او متحرک صفونه

دودیز افعال د فنکشن سره رامینځته شوي LAMBDA په بریالیتوب سره د نوي متحرک صفونو او د دوی دندو سره د کار ملاتړ کوي (Filter, UNIK, ټولګي) په 2020 کې مایکروسافټ ایکسل ته اضافه شوی.

راځئ چې ووایو موږ غواړو یو نوی کارونکي تعریف شوی فنکشن رامینځته کړو چې دوه لیستونه پرتله کړي او د دوی ترمینځ توپیر بیرته راولي - هغه عناصر چې د لومړي لیست څخه په دوهم کې ندي. د ژوند کار، نه دی؟ پخوا، د دې لپاره دوی یا هم افعال کارول VPR (ولوک اپ)، یا PivotTables، یا د بریښنا پوښتنې پوښتنې. اوس تاسو کولی شئ د یوې فورمول سره ترسره کړئ:

په انګلیسي نسخه کې به دا وي:

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

دلته فعالیت COUNTIF په دویمه کې د لومړي لیست د هر عنصر د پیښو شمیر، او بیا فعالیت Filter یوازې هغه څوک غوره کوي چې دا پیښې نه وې. د دې جوړښت په پوښلو سره LAMBDA او د نوم سره د دې پر بنسټ نومول شوی سلسله رامینځته کول، د بیلګې په توګه، د لټون توزیع - موږ به یو مناسب فعالیت ترلاسه کړو چې د متحرک صف په شکل کې د دوه لیستونو پرتله کولو پایله بیرته راګرځوي:

که د سرچینې ډاټا عادي نه وي، مګر "سمارټ" میزونه، زموږ فعالیت به پرته له ستونزو سره مخ شي:

بله بیلګه په متحرک ډول د متن ویشل دي چې XML ته یې بدل کړي او بیا یې د حجرو په واسطه حجره د FILTER.XML فنکشن په کارولو سره تجزیه کړي چې موږ پدې وروستیو کې تجزیه کړې. د دې لپاره چې دا پیچلي فورمول هر ځل په لاسي ډول تولید نه کړي، دا به اسانه وي چې دا په LAMBDA کې وتړئ او د دې پر بنسټ یو متحرک سلسله رامینځته کړئ، د بیلګې په توګه یو نوی کمپیکٹ او مناسب فعالیت، د دې نومول، د بیلګې په توګه، RAZDTEXT:

د دې فنکشن لومړی دلیل به د سرچینې متن سره حجره وي، او دویم - جلا کوونکی کرکټر، او دا به پایله د افقی متحرک صف په بڼه بیرته راولي. د فعالیت کوډ به په لاندې ډول وي:

= لامبدا(t;d; انتقال(FILTER.XML(“"&بدله(t;d؟ "«)&»"؛"//Y")))

د مثالونو لیست نه ختمیدونکی دی - په هر حالت کې چیرې چې تاسو ډیری وختونه ورته اوږد او پیچلي فارمول ته ننوځي ، د LAMBDA فعالیت به ژوند د پام وړ اسانه کړي.

د کرکټرونو تکراري شمیرل

ټولو پخوانیو مثالونو د LAMBDA فعالیت یوازې یو، خورا څرګند، اړخ ښودلی دی - دا د "ریپر" په توګه کارول په دې کې د اوږد فارمولونو لپاس کولو او د دوی انډول ساده کولو لپاره. په حقیقت کې، LAMBDA یو بل، خورا ژور، اړخ لري چې دا تقریبا د بشپړ پروګرام کولو ژبې ته اړوي.

حقیقت دا دی چې د LAMBDA دندو بنسټیز مهم ځانګړتیا د دوی پلي کولو وړتیا ده تکرار - د محاسبې منطق، کله چې د محاسبې په بهیر کې فنکشن خپل ځان غږوي. د عادت څخه، دا کیدای شي ډارونکی وي، مګر په پروګرام کولو کې، تکرار یو عام شی دی. حتی په Visual Basic کې میکرو کې، تاسو کولی شئ دا پلي کړئ، او اوس، لکه څنګه چې تاسو لیدلی شئ، دا Excel ته راغلی. راځئ هڅه وکړو چې دا تخنیک په عملي مثال سره پوه کړو.

فرض کړئ چې موږ غواړو د کارونکي لخوا تعریف شوی فنکشن رامینځته کړو چې د سرچینې متن څخه ټول ورکړل شوي حروف لرې کړي. زما په اند د داسې فنکشن ګټورتوب ، تاسو اړتیا نلرئ ثابت کړئ - دا به خورا اسانه وي چې د دې په مرسته د کثافاتو ان پټ ډیټا پاک کړئ ، سمه ده؟

په هرصورت، د تیرو، غیر تکراري مثالونو په پرتله، دوه ستونزې زموږ په تمه دي.

1. موږ باید د خپل فنکشن لپاره یو نوم راوړو مخکې لدې چې موږ د دې کوډ لیکل پیل کړو ، ځکه چې پدې کې به دا نوم دمخه پخپله فنکشن ته ویل کیږي.
   
2. حجرې ته د ورته تکراري فعالیت داخلول او د LAMBDA وروسته په بریکٹونو کې د دلیلونو په ټاکلو سره د دې ډیبګ کول (لکه څنګه چې موږ مخکې کړي وو) به کار ونکړي. تاسو باید سمدلاسه یو فنکشن جوړ کړئ "له پیل څخه" دننه نوم مدیر (د نوم مدیر).
   

راځئ چې خپل فعالیت ته ووایو، ووایو، CLEAN او موږ غواړو چې دا دوه دلیلونه ولري - متن باید پاک شي او د ایستل شوي حروفونو لیست د متن تار په توګه:

راځئ چې جوړ کړو، لکه څنګه چې موږ مخکې جوړ کړی و، په ټب کې فورمول в د مدیر نوم نومول شوی حد، نوم یې CLEAR او په ساحه کې دننه کړئ Range لاندې جوړښت:

= 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

بیا فنکشن ځان ته زنګ وهي او د ننوتلو په توګه (لومړی دلیل) هغه څه ترلاسه کوي چې په تیر مرحله کې د پاکولو وروسته پاتې کیږي، او دوهم دلیل د خارج شوي حروفونو تار دی چې له لومړي څخه نه پیل کیږي، مګر د دویم کرکټر څخه پیل کیږي، د بیلګې په توګه "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYA. د ابتدايي "A" پرته - دا د MID فعالیت لخوا ترسره کیږي. لکه څنګه چې مخکې، فنکشن د پاتې پاتې (B) له کیڼ اړخ څخه لومړی کرکټر اخلي او هغه ته ورکړل شوي متن کې ځای په ځای کوي (Zkz n 125 rubles) د خالي تار سره - موږ د منځمهاله پایلې په توګه ترلاسه کوو:

125 ru.

سپړنه 3

فنکشن ځان ته بیا غږ کوي، د لومړي دلیل په توګه ترلاسه کوي چې د متن څخه څه پاتې دي چې په تیرو تکرار کې پاک شي (Bsh zkz n 125 ru.)، او د دویم دلیل په توګه، د خارج شوي حروفونو سیټ د یو بل کرکټر لخوا کم شوی. کیڼ اړخ ته، د بیلګې په توګه "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYUYA." پرته له ابتدايي "B" څخه. بیا دا د دې سیټ څخه کیڼ اړخ ته لومړی کرکټر (B) اخلي او له متن څخه یې لرې کوي - موږ ترلاسه کوو:

sh zkz n 125 ru.

او داسې نور - زه امید لرم چې تاسو نظر ترلاسه کړئ. د هر تکرار سره، د لیرې کولو لپاره د حروفونو لیست به په ښي خوا کې ټوټه شي، او موږ به د سیټ څخه راتلونکی کرکټر د باطل سره وپلټئ او ځای په ځای کړو.

کله چې ټول کرکټرونه ختم شي، موږ به د لوپ څخه وتلو ته اړتیا ولرو - دا رول یوازې د فنکشن لخوا ترسره کیږي IF (که)، په کوم کې چې زموږ ډیزاین پوښل شوی. که چیرې د حذف کولو لپاره هیڅ حروف پاتې نه وي (d = "")، نو فنکشن باید نور ځان ونه غږوي، مګر په ساده ډول باید متن بیرته په خپل وروستي شکل کې پاک شي (متغیر t).

د حجرو تکراري تکرار

په ورته ډول، تاسو کولی شئ په ورکړل شوي حد کې د حجرو تکراري شمیره پلي کړئ. فرض کړئ چې موږ غواړو د لامبډا فنکشن په نوم جوړ کړو د ځای پرځای کولو لیست د ورکړل شوي حوالې لیست سره سم د سرچینې متن کې د ټوټو د عمده پلور بدلولو لپاره. پایله باید داسې ښکاري:

هغوی. زموږ په فعالیت کې د ځای پرځای کولو لیست دلته به درې دلیلونه وي:

1. د پروسس کولو لپاره د متن سره حجره (د سرچینې پته)
2. د کالم لومړۍ حجره د ارزښتونو سره د لټون څخه لټون کولو لپاره
3. د کالم لومړی حجره د لټون څخه د بدلیدونکي ارزښتونو سره

فنکشن باید په ډایرکټر کې له پورته څخه ښکته ته لاړ شي او په ترتیب سره د کیڼ کالم څخه ټول اختیارونه ځای په ځای کړي پیدا کول د ښي کالم څخه اړونده اختیارونو ته متبادل. تاسو کولی شئ دا د لاندې تکراري لامبډا فنکشن سره پلي کړئ:

په هر تکرار کې ښکته کول د معیاري ایکسل فنکشن لخوا پلي کیږي ضایع کول (OFFSET)، کوم چې پدې حالت کې درې دلیلونه لري - اصلي رینج، د قطار بدلون (1) او د کالم بدلون (0).

ښه، هرڅومره ژر چې موږ د لارښود پای ته ورسیږو (n = "")، موږ باید تکرار پای ته ورسوو - موږ خپل ځان ته زنګ وهو او هغه څه وښیو چې د سرچینې متن متغیر t کې د ټولو بدیلونو وروسته راټول شوي.

بس نور څه نه. هیڅ پیچلي میکرو یا د بریښنا پوښتنې پوښتنې نشته - ټوله دنده د یو فنکشن لخوا حل کیږي.

• د ایکسل نوي متحرک صف افعالو کارولو څرنګوالی: FILTER، SORT، UNIC
• د SUBSTITUTE فنکشن سره د متن بدلول او پاکول
  
• په VBA کې د میکرو او د کارونکي لخوا ټاکل شوي افعال (UDFs) رامینځته کول