Genel formül
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Özet
Kısmi eşleşmeye göre tüm eşleşmeleri ayıklamak için, ISNUMBER ve SEARCH desteğiyle, INDEX ve AGGREGATE işlevlerine dayalı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, G5'teki formül şöyledir:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
şu adlandırılmış aralıklarla: "arama" = D5, "ct" = D8, "veri" = B5: B55.
Not: Bu bir dizi formülüdür, ancak AGGREGATE dizileri yerel olarak işleyebileceğinden, kontrol + shift + enter gerektirmez.
Açıklama
Bu formülün özü, ayıklama alanındaki her satır için "n'inci eşleşmeyi" bulmak için AGGREGATE kullanılan INDEX işlevidir:
INDEX(data,nth_match_formula)
Neredeyse tüm iş, "veriler" deki hangi satırların arama dizesiyle eşleştiğini bulmak ve raporlamak ve her eşleşen değer için konumunu INDEX'e bildirmektir. Bu, AGGREGATE işlevi aşağıdaki şekilde yapılandırılarak yapılır:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
İlk bağımsız değişken olan 15, AGGREGATE'e KÜÇÜK gibi davranmasını ve n'inci en küçük değerleri döndürmesini söyler. İkinci argüman olan 6, hataları göz ardı etme seçeneğidir. Üçüncü bağımsız değişken, eşleşen sonuçlar dizisi oluşturan bir ifadedir (aşağıda açıklanmıştır). Dördüncü argüman olan F5, "n'inci" değerini belirtmek için KÜÇÜK'te "k" gibi davranır.
AGGREGATE, diziler üzerinde çalışır ve aşağıdaki ifade, AGGREGATE içindeki üçüncü bağımsız değişken için bir dizi oluşturur:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Burada, SATIR işlevi, göreceli satır numaralarından oluşan bir dizi oluşturmak için kullanılır ve ISNUMBER ve SEARCH, arama dizesini verilerdeki değerlerle eşleştirmek için birlikte kullanılır, bu da bir DOĞRU ve YANLIŞ değerler dizisi oluşturur.
Akıllı bit, satır numaralarını arama sonuçlarına göre bölmektir. Bunun gibi bir matematik işleminde, DOĞRU 1 gibi davranır ve YANLIŞ sıfır gibi davranır. Sonuç, pozitif bir eşleşmeyle ilişkili satır numaraları 1'e bölünür ve işlemden sonra kalır, eşleşmeyen değerlerle ilişkili satır numaraları yok edilir ve # SAYI / 0 hatası olur. AGGREGATE, hataları yok sayacak şekilde ayarlandığından, # SAYI / 0 hatalarını yok sayar ve kalan değerlerde "n'inci" en küçük sayıyı, "n'inci" için F sütunundaki sayıyı kullanarak döndürür.
Yönetim performansı
Tüm dizi formülleri gibi, bu formül de büyük veri kümesine sahip kaynaklar açısından "pahalıdır". Performans etkilerini en aza indirmek için, INDEX ve MATCH formülünün tamamı şu şekilde EĞER'e sarılmıştır:
=IF(F5>ct,"",formula)
"ct" (D8) adlı aralıkta bu formül bulunur:
=COUNTIF(data,"*"&search&"*")
Bu kontrol, tüm eşleşen değerler çıkarıldıktan sonra formülün INDEX ve AGGREGATE bölümünün çalışmasını durdurur.
SMALL ile dizi formülü
Excel sürümünüzde TOPLAMA işlevi yoksa, KÜÇÜK ve EĞER temelinde alternatif bir formül kullanabilirsiniz:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
