Excel formülü: Yardımcı sütunla tüm eşleşmeleri ayıklayın -

İçindekiler

Genel formül

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Özet

Excel'de birden çok eşleşmeyi ayıklamanın bir yolu, eşleşen verileri işaretleyen bir yardımcı sütunla DİZİN ve KAÇINCI kullanmaktır. Bu, daha gelişmiş bir dizi formülünün karmaşıklığını önler. Gösterilen örnekte, H6'daki formül şöyledir:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

burada ct (G3), veri (B3: E52) ve yardımcı (E3: E52) adlandırılmış aralıklardır.

Açıklama

Birden fazla eşleşme getiren arama formülleriyle ilgili zorluk, kopyaları (yani birden çok eşleşme) yönetmektir. VLOOKUP ve INDEX + MATCH gibi arama formülleri ilk eşleşmeyi kolayca bulabilir, ancak kriterler birden fazla eşleşme bulduğunda "tüm eşleşmeleri" aramak çok daha zordur.

Bu formül, birden çok eşleşmeyi kolayca ayıklamak için kullanılabilecek sayısal bir değer döndüren bir yardımcı sütun kullanarak bu zorluğu ele alır. Yardımcı sütundaki formül şuna benzer:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Yardımcı sütun, C sütunundaki Departman'ın I3'teki değerle ve D sütunundaki Bina'nın J3'teki değerle eşleşip eşleşmediğini görmek için verilerdeki her satırı test eder. VE'nin TRUE sonucunu döndürmesi için her iki mantıksal test de DOĞRU vermelidir.

Her satır için, AND işlevinin sonucu, bir sayım oluşturmak için yardımcı sütundaki "yukarıdaki değere" eklenir. Bu formülün pratik etkisi, yalnızca (yeni) bir eşleşme bulunduğunda değişen artan bir sayaçtır. Ardından, bir sonraki eşleşme bulunana kadar değer aynı kalır. Bu işe yarar, çünkü DOĞRU / YANLIŞ sonuçları VE tarafından döndürülür, toplam işleminin bir parçası olarak 1/0 değerlere zorlanır. YANLIŞ sonuçlar hiçbir şey getirmez ve DOĞRU sonuçlar 1 ekler.

Ayıklama alanına geri döndüğünüzde, H sütunundaki Ad için arama formülü şöyle görünür:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

İçten dışa doğru çalışarak, formülün INDEX + MATCH kısmı, bulunan ilk eşleşmenin adını, eşleşme değeri olarak G sütunundaki satır numarasını kullanarak arar:

INDEX(data,MATCH($G6,helper,0),1)

INDEX, dizi olarak 3 veri sütununun tümünü alır (aralık "veri" olarak adlandırılır) ve MATCH, tam eşleme modunda (3. bağımsız değişken sıfıra ayarlanmış) yardımcı sütunun ("yardımcı" adlı aralık) içindeki satır numarasıyla eşleşecek şekilde yapılandırılır. .

İşte formülün zekası burada ortaya çıkıyor. Yardımcı sütun açıkça kopyalar içeriyor, ancak önemli değil çünkü MATCH yalnızca ilk değerle eşleşecek. Tasarım gereği, her "ilk değer" veri tablosundaki doğru satıra karşılık gelir.

I ve J sütunlarındaki formüller, her durumda birer artırılan sütun numarası dışında H ile aynıdır.

İNDİS / KAÇINCI formülünü saran EĞER ifadesi basit bir işlev gerçekleştirir - çıkarma alanındaki her satır numarasını, satır numarasının G3'teki değerden ("ct" olarak adlandırılır) küçük veya ona eşit olup olmadığını kontrol eder. eşleşen tüm kayıtların toplam sayısı. Eğer öyleyse, INDEX / MATCH mantığı çalıştırılır. Aksi takdirde, IF boş bir dize ("") verir.

G3'teki formül ("ct" olarak adlandırılır) basittir:

=MAX(helper)

Yardımcı sütundaki maksimum değer, toplam eşleşme sayısıyla aynı olduğundan, tek ihtiyacımız olan MAX işlevi.

Not: Çıkarma alanının, gerektiği kadar çok veriyi (yani 5 satır, 10 satır, 20 satır, vb.) İşlemek için manuel olarak yapılandırılması gerekir. Bu örnekte, çalışma sayfasını kompakt tutmak için yalnızca 5 satırla sınırlandırılmıştır.

Bu tekniği Mike Girvin'in Control + Shift + Enter kitabında öğrendim.