İçerik
Tüm klasik arama ve tür değiştirme işlevleri VPR (DÜŞEYARA), GPR (YATAYARA), DAHA MARUZ KALMIŞ (KİBRİT) ve onlar gibi olanların önemli bir özelliği vardır – kaynak verilerde baştan sona, yani soldan sağa veya yukarıdan aşağıya arama yaparlar. İlk eşleşen eşleşme bulunur bulunmaz arama durur ve yalnızca ihtiyacımız olan öğenin ilk oluşumu bulunur.
İlk değil, son olayı bulmamız gerekirse ne yapmalıyız? Örneğin, müşteri için son işlem, son ödeme, en son sipariş vb.?
Yöntem 1: Dizi Formülüyle Son Satırı Bulma
Orijinal tabloda bir satır tarih veya seri numarası (sipariş, ödeme…) içeren bir sütun yoksa, aslında görevimiz verilen koşulu karşılayan son satırı bulmaktır. Bu, aşağıdaki dizi formülü ile yapılabilir:
İşte:
- işlev IF (EĞER) bir sütundaki tüm hücreleri tek tek kontrol eder Hesap ve ihtiyacımız olan adı içeriyorsa satır numarasını görüntüler. Sayfadaki satır numarası bize fonksiyon tarafından verilir. LINE (SIRA), ancak tablodaki satır numarasına ihtiyacımız olduğu için, tabloda bir başlığımız olduğu için ayrıca 1 çıkarmamız gerekiyor.
- Daha sonra fonksiyon MAX (MAKS) oluşturulan satır numaraları kümesinden maksimum değeri, yani müşterinin en son satırının numarasını seçer.
- işlev INDEX (İNDEKS) diğer herhangi bir gerekli tablo sütunundan bulunan son sayı ile hücrenin içeriğini döndürür (sipariş kodu).
Bütün bunlar şu şekilde girilmelidir: dizi formülü, yani:
- En son güncellemelerin yüklü olduğu ve dinamik diziler için desteğin bulunduğu Office 365'te, Keşfet.
- Diğer tüm sürümlerde, formülü girdikten sonra klavye kısayoluna basmanız gerekecektir. Ctrl+vardiya+Keşfet, formül çubuğuna otomatik olarak küme parantezleri ekleyecektir.
Yöntem 2: Yeni ARA işleviyle geriye doğru arama
Zaten yeni bir özellik hakkında bir video içeren uzun bir makale yazdım VIEW (DÜŞEYARA), eski DÜŞEYARA yerine Office'in en son sürümlerinde görünen (DÜŞEYARA). BROWSE yardımıyla, görevimiz oldukça basit bir şekilde çözüldü, çünkü. bu işlev için (DÜŞEYARA'dan farklı olarak), arama yönünü açıkça ayarlayabilirsiniz: yukarıdan aşağıya veya aşağıdan yukarıya – son argümanı (-1) bundan sorumludur:
Yöntem 3. En son tarihe sahip bir dize arayın
Kaynak verilerde seri numarası veya benzer bir rol oynayan bir tarihe sahip bir sütunumuz varsa, görev değiştirilir - eşleşen son (en düşük) satırı değil, en son satırı bulmamız gerekir ( maksimum) tarih.
Klasik fonksiyonları kullanarak bunu nasıl yapacağımı zaten detaylı olarak tartıştım ve şimdi yeni dinamik dizi fonksiyonlarının gücünü kullanmaya çalışalım. Daha fazla güzellik ve rahatlık için orijinal tabloyu klavye kısayolu kullanarak "akıllı" bir tabloya da dönüştürüyoruz Ctrl+T veya komutlar Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir).
Bu "katil çift" onların yardımıyla sorunumuzu çok zarif bir şekilde çözüyor:
İşte:
- Önce işlev FILTRE (FİLTRE) tablomuzdan yalnızca sütunda yer alan satırları seçer Hesap - ihtiyacımız olan isim.
- Daha sonra fonksiyon SINIF (ÇEŞİT) seçilen satırları tarihe göre azalan düzende, en son anlaşma en üstte olacak şekilde sıralar.
- işlev INDEX (İNDEKS) ilk satırı çıkarır, yani ihtiyacımız olan son ticareti döndürür.
- Ve son olarak, harici FILTER işlevi, sonuçlardan fazladan 1. ve 3. sütunları kaldırır (sipariş kodu и Hesap) ve yalnızca tarih ve tutarı bırakır. Bunun için bir dizi sabit kullanılır. {0;1;0;1}, hangi sütunların (1) görüntülenmesini istediğimizi veya (0) istemediğimizi tanımlar.
Yöntem 4: Power Query'de Son Eşleşmeyi Bulma
Tamamlama adına, Power Query eklentisini kullanarak ters arama sorunumuza bir çözüme bakalım. Onun yardımıyla her şey çok hızlı ve güzel bir şekilde çözüldü.
1. Bir klavye kısayolu kullanarak orijinal tablomuzu “akıllı” bir tabloya dönüştürelim Ctrl+T veya komutlar Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir).
2. Düğmeyle Power Query'ye yükleyin Tablodan/Aralıktan çıkıntı Veri (Veri — Tablodan/Aralıktan).
3. Tablomuzu (başlıktaki filtrenin açılır listesi aracılığıyla) azalan tarih sırasına göre sıralarız, böylece en son işlemler en üstte olur.
4… Sekmede Dönüşüm bir takım seç Tarafından gruba göre (Dönüştür — Gruplandırma Ölçütü) ve gruplandırmayı müşterilere göre ayarlayın ve bir toplama işlevi olarak seçeneği belirleyin. Tüm satırlar (Tüm satırlar). Yeni sütuna istediğiniz herhangi bir ad verebilirsiniz – örneğin - Detaylar.
Gruplamadan sonra, müşterilerimizin benzersiz adlarının bir listesini alacağız ve sütunda - Detaylar – ilk satırın en son işlem olacağı, her birinin tüm işlemlerini içeren tablolar, ihtiyacımız olan şey:
5. düğmesiyle yeni bir hesaplanmış sütun ekleyin Özel sütun çıkıntı Sütun ekle (Sütun ekle — Özel sütun ekle)ve aşağıdaki formülü girin:
İşte - Detaylar – bu, müşterilerin tablolarını aldığımız sütundur ve 0 {} çıkarmak istediğimiz satırın numarasıdır (Power Query'deki satır numaralandırması sıfırdan başlar). Kayıtları olan bir sütun alıyoruz (Rekor), burada her giriş her tablonun ilk satırıdır:
Sütun başlığındaki çift oklu düğme ile tüm kayıtların içeriğini genişletmek için kalır. son anlaşma istenen sütunların seçilmesi:
… ve ardından artık gerekmeyen sütunu silin - Detaylar başlığına sağ tıklayarak - Sütunları kaldır (Sütunları kaldır).
Sonuçları sayfaya yükledikten sonra Ana Sayfa — Kapat ve yükle — Kapat ve yükle (Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle…) istediğimiz gibi son işlemlerin bir listesini içeren güzel bir tablo elde edeceğiz:
Kaynak verileri değiştirdiğinizde, üzerlerine sağ tıklayarak sonuçları güncellemeyi unutmamalısınız - komut Güncelle ve Kaydet (Yenile) veya klavye kısayolu Ctrl+Ara Toplam+F5.
- ARA işlevi, DÜŞEYARA'nın soyundan gelir
- SIRALA, FİLTRE ve UNIC yeni dinamik dizi işlevleri nasıl kullanılır?
- ARA işleviyle bir satırda veya sütunda boş olmayan son hücreyi bulma