LAMBDA არის Excel-ის ახალი სუპერ ფუნქცია

ამ დროისთვის, Microsoft Excel-ს აქვს თითქმის ხუთასი სამუშაო ფურცლის ფუნქცია, რომელიც ხელმისაწვდომია Function Wizard ფანჯრის – ღილაკის მეშვეობით fx ფორმულების ზოლში. ეს არის ძალიან წესიერი ნაკრები, მაგრამ, მიუხედავად ამისა, თითქმის ყველა მომხმარებელი ადრე თუ გვიან ხვდება სიტუაციას, როდესაც ეს სია არ შეიცავს მისთვის საჭირო ფუნქციას - უბრალოდ იმიტომ, რომ ის არ არის Excel-ში.

აქამდე ამ პრობლემის გადაჭრის ერთადერთი გზა იყო მაკროები, ანუ Visual Basic-ში საკუთარი მომხმარებლის მიერ განსაზღვრული ფუნქციის (UDF = User Defined Function) ჩაწერა, რაც საჭიროებს პროგრამირების შესაბამის უნარებს და, ზოგჯერ, სულაც არ არის ადვილი. თუმცა, Office 365-ის უახლესი განახლებებით, სიტუაცია უკეთესობისკენ შეიცვალა - Excel-ს დაემატა სპეციალური "wrapper" ფუნქცია. LAMBDA. მისი დახმარებით, საკუთარი ფუნქციების შექმნის ამოცანა ახლა მოგვარებულია მარტივად და ლამაზად.

მოდით შევხედოთ მისი გამოყენების პრინციპს შემდეგ მაგალითში.

როგორც მოგეხსენებათ, Excel-ს აქვს რამდენიმე თარიღის ანალიზის ფუნქცია, რომელიც საშუალებას გაძლევთ განსაზღვროთ დღის, თვის, კვირისა და წლის რაოდენობა მოცემული თარიღისთვის. მაგრამ რატომღაც არ არსებობს ფუნქცია, რომელიც განსაზღვრავს კვარტალის რაოდენობას, რომელიც ასევე ხშირად საჭიროა, არა? გამოვასწოროთ ეს ხარვეზი და შევქმნათ LAMBDA საკუთარი ახალი ფუნქცია ამ პრობლემის გადასაჭრელად.

ნაბიჯი 1. დაწერეთ ფორმულა

დავიწყოთ იმით, რომ ხელით ჩვეულებრივი გზით დავწერთ ფორმულას ფურცლის უჯრედში, რომელიც ითვლის რა გვჭირდება. კვარტლის ნომრის შემთხვევაში, ეს შეიძლება გაკეთდეს, მაგალითად, ასე:

ნაბიჯი 2. შეფუთვა LAMBDA-ში და ტესტირება

ახლა დროა გამოვიყენოთ ახალი LAMBDA ფუნქცია და შევიტანოთ მასში ჩვენი ფორმულა. ფუნქციის სინტაქსი ასეთია:

=LAMBDA(ცვლადი 1; ცვლადი 2; … ცვლადიN ; გამოხატვის)

სადაც ჯერ ერთი ან მეტი ცვლადის სახელებია ჩამოთვლილი და ბოლო არგუმენტი ყოველთვის არის ფორმულა ან გამოთვლილი გამოხატულება, რომელიც მათ იყენებს. ცვლადების სახელები არ უნდა ჰგავდეს უჯრედის მისამართებს და არ უნდა შეიცავდეს წერტილებს.

ჩვენს შემთხვევაში, იქნება მხოლოდ ერთი ცვლადი - თარიღი, რომლისთვისაც ჩვენ ვიანგარიშებთ კვარტალს. მოდით ვუწოდოთ მას ცვლადი, ვთქვათ, d. შემდეგ შეფუთეთ ჩვენი ფორმულა ფუნქციაში LAMBDA და ორიგინალური A2 უჯრედის მისამართის ჩანაცვლება ფიქტიური ცვლადის სახელით, მივიღებთ:

გთხოვთ გაითვალისწინოთ, რომ ასეთი ტრანსფორმაციის შემდეგ, ჩვენმა ფორმულამ (ფაქტობრივად, სწორია!) დაიწყო შეცდომის გამომუშავება, რადგან ახლა მასზე თავდაპირველი თარიღი A2 უჯრედიდან არ არის გადაცემული. ტესტირებისა და ნდობისთვის, შეგიძლიათ არგუმენტები გადასცეთ მას ფუნქციის შემდეგ მათი დამატებით LAMBDA ფრჩხილებში:

ნაბიჯი 3. შექმენით სახელი

ახლა მარტივი და სახალისო ნაწილისთვის. ჩვენ ვხსნით სახელის მენეჯერი tab formula (ფორმულები - სახელის მენეჯერი) და შექმენით ახალი სახელი ღილაკით შექმნა (Შექმნა). შექმენით და შეიყვანეთ სახელი ჩვენი მომავალი ფუნქციისთვის (მაგალითად, ნომკვართალა), და მინდორში ბმული (მითითება) ფრთხილად დააკოპირეთ ფორმულის ზოლიდან და ჩასვით ჩვენი ფუნქცია LAMBDAმხოლოდ ბოლო არგუმენტის გარეშე (A2):

ყველაფერი. დაწკაპუნების შემდეგ OK შექმნილი ფუნქციის გამოყენება შესაძლებელია ამ სამუშაო წიგნის ნებისმიერი ფურცლის ნებისმიერ უჯრედში:

გამოიყენეთ სხვა წიგნებში

LAMBDA და დინამიური მასივები

მორგებული ფუნქციები შექმნილი ფუნქციით LAMBDA წარმატებით უჭერს მხარს მუშაობას ახალ დინამიურ მასივებთან და მათ ფუნქციებთან (ფილტრი, უნიკალური, GRADE) დაემატა Microsoft Excel-ს 2020 წელს.

ვთქვათ, გვინდა შევქმნათ მომხმარებლის მიერ განსაზღვრული ახალი ფუნქცია, რომელიც შეადარებს ორ სიას და დააბრუნებს მათ შორის განსხვავებას – იმ ელემენტებს პირველი სიიდან, რომლებიც არ არის მეორეში. ცხოვრების საქმეა, არა? ადრე, ამისთვის ისინი იყენებდნენ ორივე ფუნქციას a la VPR (VLOOKUP), ან 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(FILTER.XML(“„&შეცვლა (t;d? "")&"";"//Y")))

მაგალითების სია უსასრულოა - ნებისმიერ სიტუაციაში, როდესაც ხშირად გიწევთ ერთი და იგივე გრძელი და რთული ფორმულის შეყვანა, LAMBDA ფუნქცია შესამჩნევად გაგიადვილებთ ცხოვრებას.

სიმბოლოების რეკურსიული ჩამოთვლა

ყველა წინა მაგალითმა აჩვენა LAMBDA ფუნქციის მხოლოდ ერთი, ყველაზე აშკარა მხარე - მისი გამოყენება როგორც "შეფუთვა" გრძელი ფორმულების მასში შესაფუთად და მათი შეყვანის გასამარტივებლად. სინამდვილეში, LAMBDA-ს აქვს კიდევ ერთი, ბევრად უფრო ღრმა მხარე, რომელიც მას თითქმის სრულფასოვან პროგრამირების ენად აქცევს.

ფაქტია, რომ LAMBDA ფუნქციების ფუნდამენტურად მნიშვნელოვანი მახასიათებელია მათი განხორციელების შესაძლებლობა რეკურსია – გამოთვლების ლოგიკა, როდესაც გამოთვლის პროცესში ფუნქცია თავის თავს იძახებს. ჩვევიდან გამომდინარე, შეიძლება საშინლად ჟღერდეს, მაგრამ პროგრამირებაში რეკურსი ჩვეულებრივი რამ არის. Visual Basic-ში მაკროებშიც კი შეგიძლიათ მისი დანერგვა და ახლა, როგორც ხედავთ, ის მოვიდა Excel-ში. შევეცადოთ გავიგოთ ეს ტექნიკა პრაქტიკული მაგალითით.

დავუშვათ, გვინდა შევქმნათ მომხმარებლის მიერ განსაზღვრული ფუნქცია, რომელიც ამოიღებს ყველა მოცემულ სიმბოლოს საწყისი ტექსტიდან. ასეთი ფუნქციის სარგებლიანობა, ვფიქრობ, თქვენ არ გჭირდებათ დამტკიცება - ძალიან მოსახერხებელი იქნება მისი დახმარებით დაბინძურებული შეყვანის მონაცემების გასუფთავება, არა?

თუმცა, წინა, არარეკურსიულ მაგალითებთან შედარებით, ორი სირთულე გველოდება.

1. ჩვენ მოგვიწევს ჩვენი ფუნქციის სახელის მოფიქრება, სანამ დავიწყებთ მისი კოდის წერას, რადგან მასში ეს სახელი უკვე გამოყენებული იქნება თავად ფუნქციის გამოსაძახებლად.
   
2. ასეთი რეკურსიული ფუნქციის უჯრედში შეყვანა და მისი გამართვა LAMBDA-ს შემდეგ ფრჩხილებში არგუმენტების მითითებით (როგორც ადრე გავაკეთეთ) არ იმუშავებს. თქვენ მოგიწევთ დაუყოვნებლივ შექმნათ ფუნქცია "ნულიდან". სახელის მენეჯერი (სახელი მენეჯერი).
   

მოდით ვუწოდოთ ჩვენს ფუნქციას, ვთქვათ, CLEAN და გვსურს, რომ მას ჰქონდეს ორი არგუმენტი - ტექსტი, რომელიც უნდა გაიწმინდოს და გამორიცხული სიმბოლოების სია, როგორც ტექსტის სტრიქონი:

მოდით შევქმნათ, როგორც ადრე გავაკეთეთ, ჩანართზე formula в სახელი მენეჯერი დაასახელეთ დიაპაზონი, დაარქვით 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 ცარიელი ტექსტის სტრიქონით, ანუ შლის “ A”. როგორც შუალედური შედეგი, ჩვენ ვიღებთ:

Vsh zkz n 125 რუბლი.

გამეორება 2

შემდეგ ფუნქცია იძახებს საკუთარ თავს და შეყვანის სახით (პირველი არგუმენტი) იღებს იმას, რაც დარჩა წინა საფეხურზე გაწმენდის შემდეგ, ხოლო მეორე არგუმენტი არის გამორიცხული სიმბოლოების სტრიქონი, რომელიც იწყება არა პირველი, არამედ მეორე სიმბოლოდან, ანუ „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA. "A" საწყისის გარეშე - ეს კეთდება MID ფუნქციით. როგორც ადრე, ფუნქცია იღებს პირველ სიმბოლოს დარჩენილი სიმბოლოების მარცხნიდან (B) და ანაცვლებს მას მიცემულ ტექსტში (Zkz n 125 რუბლი) ცარიელი სტრიქონით - შუალედურ შედეგს ვიღებთ:

125 რუ.

გამეორება 3

ფუნქცია კვლავ იძახებს საკუთარ თავს, პირველ არგუმენტად იღებს იმას, რაც დარჩა ტექსტიდან გასასუფთავებლად წინა გამეორებისას (Bsh zkz n 125 ru.), ხოლო მეორე არგუმენტად, გამორიცხული სიმბოლოების სიმრავლე, რომელიც შეკვეცილია კიდევ ერთი სიმბოლოთი. მარცხენა, ანუ „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.“, საწყისი „B“-ის გარეშე. შემდეგ ის კვლავ იღებს პირველ სიმბოლოს მარცხნიდან (B) ამ ნაკრებიდან და შლის მას ტექსტიდან - ვიღებთ:

sh zkz n 125 ru.

და ასე შემდეგ - იმედი მაქვს, რომ თქვენ მიიღებთ იდეას. ყოველი გამეორებისას, ამოსაშლელი სიმბოლოების სია შეიკვეცება მარცხნივ, ჩვენ მოვძებნით და შევცვლით შემდეგი სიმბოლოს ნაკრებიდან void-ით.

როდესაც ყველა სიმბოლო ამოიწურება, ჩვენ დაგვჭირდება ციკლიდან გასვლა - ამ როლს მხოლოდ ფუნქცია ასრულებს IF (თუ), რომელშიც ჩვენი დიზაინია გახვეული. თუ წასაშლელი სიმბოლოები აღარ დარჩა (d=””), მაშინ ფუნქცია აღარ უნდა იძახდეს საკუთარ თავს, არამედ უბრალოდ უნდა დააბრუნოს გასასუფთავებელი ტექსტი (ცვლადი t) მის საბოლოო ფორმაში.

უჯრედების რეკურსიული გამეორება

ანალოგიურად, თქვენ შეგიძლიათ განახორციელოთ უჯრედების რეკურსიული ჩამოთვლა მოცემულ დიაპაზონში. დავუშვათ, რომ გვინდა შევქმნათ ლამბდა ფუნქცია სახელად ჩანაცვლების სია წყარო ტექსტში ფრაგმენტების საბითუმო ჩანაცვლებისთვის მოცემული საცნობარო სიის მიხედვით. შედეგი ასე უნდა გამოიყურებოდეს:

იმათ. ჩვენს ფუნქციაზე ჩანაცვლების სია იქნება სამი არგუმენტი:

1. უჯრედი ტექსტით დასამუშავებლად (წყაროს მისამართი)
2. სვეტის პირველი უჯრედი მნიშვნელობებით საძიებელიდან
3. სვეტის პირველი უჯრედი ჩანაცვლების მნიშვნელობებით საძიებელიდან

ფუნქცია უნდა გადავიდეს დირექტორიაში ზემოდან ქვემოდან და თანმიმდევრულად შეცვალოს ყველა ვარიანტი მარცხენა სვეტიდან Პოვნა მარჯვენა სვეტიდან შესაბამის ვარიანტებზე შემცვლელი. ამის განხორციელება შეგიძლიათ შემდეგი რეკურსიული ლამბდა ფუნქციით:

ყოველი გამეორების დროს Shift down ხორციელდება სტანდარტული Excel ფუნქციით განკარგვა (OFFSET), რომელსაც ამ შემთხვევაში აქვს სამი არგუმენტი – თავდაპირველი დიაპაზონი, მწკრივის ცვლა (1) და სვეტის ცვლა (0).

ისე, როგორც კი მივაღწევთ დირექტორიის ბოლოს (n = ""), ჩვენ უნდა დავასრულოთ რეკურსია - ჩვენ ვწყვეტთ საკუთარ თავს გამოძახებას და ვაჩვენებთ იმას, რაც დაგროვდა ყველა ჩანაცვლების შემდეგ, საწყისი ტექსტის ცვლადში t.

Სულ ეს არის. არ არის რთული მაკროები ან Power Query მოთხოვნები - მთელი ამოცანა წყდება ერთი ფუნქციით.

• როგორ გამოვიყენოთ Excel-ის ახალი დინამიური მასივის ფუნქციები: FILTER, SORT, UNIC
• ტექსტის ჩანაცვლება და გასუფთავება SUBSTITUTE ფუნქციით
  
• მაკროების და მომხმარებლის მიერ განსაზღვრული ფუნქციების (UDF) შექმნა VBA-ში