Genel formül
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Özet
Bir listeden veya sütundan yalnızca benzersiz değerleri çıkarmak için, INDEX, MATCH ve COUNTIF'e dayalı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, D5'teki formül aşağıya kopyalanmıştır:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
burada "liste", B5: B11 olarak adlandırılan aralıktır.
Not: Bu bir dizi formülüdür ve kontrol + üst karakter + enter kullanılarak girilmelidir.
Açıklama
Bu formülün özü, INDEX ile temel bir aramadır:
=INDEX(list,row)
Başka bir deyişle, INDEX'e listeyi ve bir satır numarası verin ve INDEX benzersiz listeye eklemek için bir değer alacaktır.
Zor iş, INDEX'i vermek için SATIR numarasını bulmaktır, böylece sadece benzersiz değerler elde ederiz. Bu, MATCH ve COUNTIF ile yapılır ve ana numara burada:
COUNTIF($D$4:D4,list)
Burada, EĞERSAY, $ D $ 4: D4 aralığı için genişleyen bir referans kullanarak ana listede zaten benzersiz listede bulunan öğelerin kaç kez göründüğünü sayar.
Genişleyen referans bir tarafta mutlaktır, diğer tarafta göreli. Bu durumda, formül kopyalandıkça, referans benzersiz listede daha fazla satır içerecek şekilde genişleyecektir.
Referansın, benzersiz listedeki ilk benzersiz girişin bir satır yukarısında, D4'te başladığına dikkat edin. Bu bilinçli bir durumdur - benzersiz listedeki öğeleri * zaten * saymak istiyoruz ve mevcut hücreyi döngüsel bir referans oluşturmadan dahil edemeyiz. Yani, yukarıdaki satırdan başlıyoruz.
Önemli: Benzersiz liste başlığının ana listede görünmediğinden emin olun.
EĞERSAY'daki ölçütler için ana listenin kendisini kullanıyoruz. Birden çok ölçüt verildiğinde, EĞERSAY bir dizide birden çok sonuç döndürür. Her yeni satırda bunun gibi farklı bir dizimiz var:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Not: EĞERSAY, "VEYA" ilişkisine sahip birden çok ölçütü işler (yani EĞERSAY (aralık, ("kırmızı", "mavi", "yeşil")) kırmızı, mavi veya yeşil sayar.
Şimdi konumları bulmamız gereken dizilere sahibiz (satır numaraları). Bunun için, sıfır değerleri bulmak için tam eşleme için ayarlanan MATCH kullanıyoruz. Yukarıdaki COUNTIF ile oluşturulan dizileri MATCH içine koyarsak, işte şunu elde ederiz:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH, sıfır sayısına bakarak öğeleri bulur (yani, benzersiz listede henüz görünmeyen öğeleri arayarak). Bu işe yarar, çünkü MATCH, yinelenenler olduğunda her zaman ilk eşleşmeyi döndürür.
Son olarak, pozisyonlar INDEX'e satır numaraları olarak beslenir ve INDEX bu pozisyondaki ismi verir.
LOOKUP ile dizisiz sürüm
Esnek ARA işlevini kullanarak benzersiz öğeleri ayıklamak için dizi olmayan bir formül oluşturabilirsiniz:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Formül yapısı, yukarıdaki INDEX MATCH formülüne benzer, ancak ARA, dizi işlemini yerel olarak gerçekleştirebilir.
- EĞERSAY, $ D $ 4: D4 genişleyen aralığındaki "listeden" her bir değerin sayısını döndürür
- Sıfırla karşılaştırma, bir DOĞRU ve YANLIŞ değerler dizisi oluşturur
- 1 sayısı diziye bölünerek 1'ler ve # SAYI / 0 hatalarından oluşan bir dizi oluşturulur
- Bu dizi, LOOKUP içindeki arama_vektörü olur.
- 2'nin arama değeri, aranan_vektördeki tüm değerlerden daha büyük
- ARA, arama dizisindeki son hata olmayan değerle eşleşir
- ARA sonuç_vektöründe karşılık gelen değeri döndürür, adlandırılmış aralık "liste"
Yalnızca bir kez görünen öğeleri çıkarın
Yukarıdaki ARA formülünün boole mantığıyla genişletilmesi kolaydır. Kaynak verilerde yalnızca bir kez görünen benzersiz öğelerin bir listesini çıkarmak için aşağıdaki gibi bir formül kullanabilirsiniz:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Tek ekleme, ikinci COUNTIF ifadesidir:
COUNTIF(list,list)=1
Burada, EĞERSAY aşağıdaki gibi bir öğe sayısı dizisi döndürür:
(2;2;2;2;2;1;2)
1 ile karşılaştırılır ve bir DOĞRU / YANLIŞ değerleri dizisi ile sonuçlanır:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
çıktıyı kaynak verilerde yalnızca bir kez oluşan öğelerle sınırlandırmak için bir "filtre" görevi görür.
