İçerik
Şu anda, Microsoft Excel'in İşlev Sihirbazı penceresinden erişilebilen yaklaşık beş yüz çalışma sayfası işlevi vardır - düğme fx formül çubuğunda. Bu çok iyi bir set, ancak yine de, er ya da geç, hemen hemen her kullanıcı, bu listenin ihtiyaç duyduğu işlevi içermediği bir durumla karşılaşır - sadece Excel'de olmadığı için.
Şimdiye kadar, bu sorunu çözmenin tek yolu makrolardı, yani uygun programlama becerileri gerektiren ve bazen hiç de kolay olmayan Visual Basic'te kendi kullanıcı tanımlı işlevinizi (UDF = Kullanıcı Tanımlı İşlev) yazmaktı. Ancak, en son Office 365 güncellemeleriyle durum daha iyiye doğru değişti - Excel'e özel bir "sarmalayıcı" işlevi eklendi LAMBDA. Onun yardımıyla, kendi işlevlerinizi yaratma görevi artık kolay ve güzel bir şekilde çözülüyor.
Aşağıdaki örnekte kullanım prensibine bakalım.
Büyük olasılıkla bildiğiniz gibi, Excel'de belirli bir tarih için gün, ay, hafta ve yıl sayısını belirlemenize izin veren birkaç tarih ayrıştırma işlevi vardır. Ama nedense çoğu zaman ihtiyaç duyulan çeyrek sayısını belirleyen bir fonksiyon yok değil mi? Bu eksikliği giderelim ve ile oluşturalım LAMBDA Bu sorunu çözmek için kendi yeni işlevi.
Adım 1. Formülü yazın
Her zamanki gibi manuel olarak, ihtiyacımız olanı hesaplayan bir sayfa hücresine bir formül yazacağımız gerçeğiyle başlayalım. Çeyrek sayısı durumunda, bu, örneğin şu şekilde yapılabilir:
Adım 2. LAMBDA'ya sarma ve test etme
Şimdi yeni LAMBDA işlevini uygulama ve formülümüzü buna sarma zamanı. İşlev sözdizimi aşağıdaki gibidir:
=LAMBDA(değişken1; değişken2; ... DeğişkenN ; ifade)
burada bir veya daha fazla değişkenin adları ilk olarak listelenir ve son argüman her zaman bir formül veya bunları kullanan hesaplanmış bir ifadedir. Değişken isimleri hücre adreslerine benzememeli ve nokta içermemelidir.
Bizim durumumuzda sadece bir değişken olacak - çeyrek sayısını hesapladığımız tarih. Bunun için değişkeni diyelim, d diyelim. Sonra formülümüzü bir fonksiyona sarmak LAMBDA ve orijinal A2 hücresinin adresini hayali bir değişken adıyla değiştirerek şunu elde ederiz:
Lütfen böyle bir dönüşümden sonra formülümüzün (aslında doğru!) bir hata üretmeye başladığını unutmayın, çünkü şimdi A2 hücresindeki orijinal tarih ona aktarılmıyor. Test ve güven için, argümanları fonksiyondan sonra ekleyerek ona argümanlar iletebilirsiniz. LAMBDA parantez içinde:
Adım 3. Bir ad oluşturun
Şimdi kolay ve eğlenceli kısım için. Açığız İsim Yöneticisi çıkıntı formül (Formüller — Ad Yöneticisi) ve düğmesiyle yeni bir ad oluşturun oluşturmak (Oluşturmak). Gelecek fonksiyonumuz için bir isim bulun ve girin (örneğin, Nomkvartala) ve sahada Link (Referans) formül çubuğundan dikkatlice kopyalayın ve işlevimizi yapıştırın LAMBDA, yalnızca son argüman olmadan (A2):
Her şey. tıkladıktan sonra OK oluşturulan işlev, bu çalışma kitabının herhangi bir sayfasındaki herhangi bir hücrede kullanılabilir:
Diğer kitaplarda kullanın
LAMBDA ve dinamik diziler
Bir işlevle oluşturulan özel işlevler LAMBDA yeni dinamik diziler ve işlevleriyle çalışmayı başarıyla destekleyin (FILTRE, UNIK, SINIF) 2020'de Microsoft Excel'e eklendi.
Diyelim ki, iki listeyi karşılaştıracak ve aralarındaki farkı döndürecek yeni bir kullanıcı tanımlı işlev oluşturmak istiyoruz – ilk listeden ikincide olmayan öğeler. Hayat iştir, değil mi? Daha önce, bunun için her iki işlevi de kullanıyorlardı. VPR (DÜŞEYARA)veya PivotTable'lar veya Power Query sorguları. Şimdi bir formülle yapabilirsiniz:
İngilizce versiyonda şöyle olacak:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Buradaki fonksiyon COUNTIF ikincideki ilk listenin her bir öğesinin oluşum sayısını ve ardından işlevi sayar FILTRE yalnızca bu olaylara sahip olmayanları seçer. Bu yapıyı sararak LAMBDA ve buna dayalı olarak bir adla adlandırılmış bir aralık oluşturmak, örneğin, ARAMA DAĞILIMI – dinamik bir dizi biçiminde iki listeyi karşılaştırmanın sonucunu döndüren kullanışlı bir işlev elde edeceğiz:
Kaynak veriler sıradan değil, “akıllı” tablolar ise, fonksiyonumuz da sorunsuz bir şekilde başa çıkacaktır:
Başka bir örnek, metni XML'e dönüştürerek dinamik olarak bölmek ve daha sonra, yakın zamanda ayrıştırdığımız FILTER.XML işlevini kullanarak hücre hücre ayrıştırmaktır. Bu karmaşık formülü her seferinde manuel olarak yeniden üretmemek için, onu LAMBDA'ya sarmak ve buna dayalı dinamik bir aralık oluşturmak daha kolay olacaktır, yani yeni bir kompakt ve kullanışlı işlev, örneğin RAZDTEXT olarak adlandırın:
Bu işlevin ilk argümanı, kaynak metni içeren hücre, ikincisi ise ayırıcı karakter olacak ve sonucu yatay dinamik bir dizi biçiminde döndürecektir. Fonksiyon kodu aşağıdaki gibi olacaktır:
=LAMBDA(t;d; TRANSPOSE(FİLTRE.XML(“
Örneklerin listesi sonsuzdur – genellikle aynı uzun ve zahmetli formülü girmek zorunda olduğunuz her durumda, LAMBDA işlevi hayatı fark edilir ölçüde kolaylaştıracaktır.
Karakterlerin özyinelemeli numaralandırması
Önceki tüm örnekler, LAMBDA işlevinin yalnızca bir, en belirgin yanını göstermiştir - uzun formülleri içine sarmak ve girdilerini basitleştirmek için "sarmalayıcı" olarak kullanımı. Aslında, LAMBDA'nın onu neredeyse tam teşekküllü bir programlama diline dönüştüren çok daha derin bir yönü daha vardır.
Gerçek şu ki, LAMBDA işlevlerinin temel olarak önemli bir özelliği, bunları uygulama yeteneğidir. yineleme – hesaplama mantığı, hesaplama sürecinde fonksiyon kendini çağırır. Alışkanlıktan ürkütücü gelebilir, ancak programlamada özyineleme yaygın bir şeydir. Visual Basic'te makrolarda bile uygulayabiliyorsunuz ve şimdi de gördüğünüz gibi Excel'e geldi. Bu tekniği pratik bir örnekle anlamaya çalışalım.
Verilen tüm karakterleri kaynak metinden kaldıracak kullanıcı tanımlı bir işlev oluşturmak istediğimizi varsayalım. Böyle bir işlevin kullanışlılığını, bence kanıtlamanıza gerek yok - onun yardımıyla dağınık girdi verilerini temizlemek çok uygun olur, değil mi?
Ancak önceki, özyinelemeli olmayan örneklerle karşılaştırıldığında, bizi iki zorluk beklemektedir.
- Kodunu yazmaya başlamadan önce fonksiyonumuz için bir isim bulmamız gerekecek, çünkü bu isim fonksiyonun kendisini çağırmak için zaten kullanılacak.
- Böyle bir özyinelemeli işlevi bir hücreye girmek ve LAMBDA'dan sonra (daha önce yaptığımız gibi) parantez içinde argümanlar belirterek hata ayıklamak işe yaramaz. Hemen "sıfırdan" bir işlev oluşturmanız gerekecek. İsim Yöneticisi (İsim Yöneticisi).
Diyelim ki işlevimizi CLEAN olarak adlandıralım ve iki argümana sahip olmasını istiyoruz – temizlenecek metin ve metin dizesi olarak hariç tutulan karakterlerin listesi:
Daha önce yaptığımız gibi sekmede oluşturalım formül в İsim yöneticisi adlandırılmış aralık, CLEAR olarak adlandırın ve alana girin Aralık aşağıdaki inşaat:
=LAMBDA(t;d;EĞER(d=””;t;TEMİZ(YEDEK(t;SOL(d);””);ORTA(d;2;255))))
Burada t değişkeni temizlenecek orijinal metindir ve d silinecek karakterlerin listesidir.
Her şey şu şekilde çalışır:
yineleme 1
SUBSTITUTE(t;LEFT(d);””) parçası, tahmin edebileceğiniz gibi, kaynak metin t'de silinecek d kümesindeki sol karakterden ilk karakteri boş bir metin dizesiyle değiştirir, yani “ A". Ara sonuç olarak şunu elde ederiz:
Vsh zkz n 125 ruble.
yineleme 2
Daha sonra fonksiyon kendini çağırır ve girdi olarak (ilk argüman) önceki adımda temizlemeden sonra kalanları alır ve ikinci argüman birinci karakterden değil ikinci karakterden başlayan hariç tutulan karakterler dizisidir, yani “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, ilk “A” olmadan – bu, MID işlevi tarafından yapılır. Daha önce olduğu gibi, işlev, kalanların (B) solundaki ilk karakteri alır ve kendisine verilen metinde (Zkz n 125 ruble) boş bir dize ile değiştirir - ara sonuç olarak şunu elde ederiz:
125 ruble.
yineleme 3
İşlev, önceki yinelemede temizlenecek metinden kalanları ilk argüman olarak alarak kendini tekrar çağırır (Bsh zkz n 125 ru.), Ve ikinci argüman olarak, hariç tutulan karakterler kümesi bir karakter daha kısaltılır. sol, yani “VGDEEGZIKLMNOPRSTUFHTSCHSHCHYYYYUYA.”, baş “B” olmadan. Sonra yine bu kümeden soldan (B) ilk karakteri alır ve metinden çıkarır – şunu elde ederiz:
sh zkz n 125 ru.
Ve benzeri - Umarım fikri anlamışsınızdır. Her yinelemede, kaldırılacak karakterlerin listesi solda kesilecek ve kümedeki bir sonraki karakteri arayacağız ve bir boşlukla değiştireceğiz.
Tüm karakterler bittiğinde döngüden çıkmamız gerekecek – bu rol sadece fonksiyon tarafından gerçekleştirilir. IF (EĞER), tasarımımızın sarıldığı. Silinecek karakter kalmadıysa (d=””), işlev artık kendisini çağırmamalı, ancak temizlenecek metni (t değişkeni) son biçimiyle döndürmelidir.
Hücrelerin yinelemeli yinelemesi
Benzer şekilde, belirli bir aralıktaki hücrelerin özyinelemeli numaralandırmasını uygulayabilirsiniz. adlı bir lambda işlevi oluşturmak istediğimizi varsayalım. YEDEK LİSTESİ Kaynak metindeki parçaların belirli bir referans listesine göre toptan değiştirilmesi için. Sonuç şöyle görünmelidir:
Şunlar. bizim fonksiyonumuzda YEDEK LİSTESİ üç argüman olacak:
- işlenecek metin içeren hücre (kaynak adres)
- aramadan aranacak değerlere sahip bir sütunun ilk hücresi
- aramadaki değiştirme değerleriyle sütunun ilk hücresi
İşlev, dizinde yukarıdan aşağıya gitmeli ve sırayla sol sütundaki tüm seçenekleri değiştirmelidir. Bulmak sağ sütundan ilgili seçeneklere Vekil. Bunu aşağıdaki özyinelemeli lambda işleviyle uygulayabilirsiniz:
Her yinelemede aşağı kaydırma, standart bir excel işlevi tarafından uygulanır BERTARAF (TELAFİ ETMEK), bu durumda üç bağımsız değişkeni vardır - orijinal aralık, satır kaydırma (1) ve sütun kaydırma (0).
Pekala, dizinin sonuna (n = “”) ulaşır ulaşmaz, özyinelemeyi sonlandırmalıyız – kendimizi aramayı bırakıyoruz ve kaynak metin değişkeni t'deki tüm değiştirmelerden sonra birikmiş olanı gösteriyoruz.
Bu kadar. Zor makrolar veya Power Query sorguları yok - tüm görev tek bir işlev tarafından çözülür.
- Excel'in yeni dinamik dizi işlevleri nasıl kullanılır: FİLTRE, SIRALAMA, UNIC
- SUBSTITUTE işleviyle metni değiştirme ve temizleme
- VBA'da makrolar ve kullanıcı tanımlı işlevler (UDF'ler) oluşturma