Genel formül
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Özet
Çok sayıda farklı dizge içermeyen hücreleri saymak için, MMULT işlevine dayalı oldukça karmaşık bir formül kullanabilirsiniz. Gösterilen örnekte, F5'teki formül şöyledir:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
burada "veri", B5: B14 adlı aralıktır ve "hariç tutma", adlandırılmış aralık D5: D7'dir.
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir
Önsöz
Bu formül, "içerir" gereksinimi nedeniyle karmaşıktır. Birçok şeye * eşit * olmayan hücreleri saymak için bir formüle ihtiyacınız varsa, KAÇINCI işlevine dayalı daha basit bir formül kullanabilirsiniz. Ayrıca, hariç tutulacak sınırlı sayıda dizeniz varsa, COUNTIFS işlevini şu şekilde kullanabilirsiniz:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Ancak, bu yaklaşımla, hariç tutulacak her dizge için yeni bir aralık / ölçüt bağımsız değişkenleri çifti girmeniz gerekir. Buna karşılık, aşağıda açıklanan formül, doğrudan çalışma sayfasına girilenleri hariç tutmak için çok sayıda dizeyi işleyebilir.
Son olarak, bu formül karmaşıktır. Önermek için daha basit bir formülünüz varsa bana bildirin :)
Açıklama
Bu formülün özü ISNUMBER ve ARA'dır:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Burada, "hariç tut" adlı aralıktaki öğeleri aktarırız, ardından sonucu "metin içinde" "veri" ile "metin bul" olarak ARAMA'ya besleriz. SEARCH işlevi, aşağıdaki gibi 2 boyutlu bir DOĞRU ve YANLIŞ değerler dizisi, 10 satıra 3 sütun döndürür:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
"Veriler" deki her bir değer için, # DEĞER hatası veya sayı olan 3 sonucumuz var (arama dizesi başına bir tane). Sayılar, bulunan bir metin dizesinin konumunu temsil eder ve hatalar, bulunmayan metin dizelerini temsil eder. Bu arada, 10 x 3 tam sonuç dizisi oluşturmak için TRANSPOSE işlevine ihtiyaç vardır.
Bu dizi, DOĞRU YANLIŞ değerleri elde etmek için ISNUMBER'a beslenir, bu değerleri çift negatif (-) operatörle 1'ler ve 0'lara dönüştürürüz. Sonuç şuna benzer bir dizidir:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
dizi1 olarak MMULT işlevine gider. Matris çarpımı kurallarına göre, dizi1'deki sütun sayısı dizi2'deki satır sayısına eşit olmalıdır. Dizi2'yi oluşturmak için SATIR işlevini şu şekilde kullanıyoruz:
ROW(exclude)^0
Bu, 1 sütununa 3 satırlık bir dizi verir:
(1;1;1)
MMULT'a dizi2 olarak gider . Dizi çarpımından sonra, orijinal verilerle eşleşecek şekilde boyutlandırılmış bir dizimiz var:
(2;1;0;0;1;1;0;0;0;2)
Bu dizide, sıfır olmayan herhangi bir sayı, hariç tutulan dizelerden en az birinin bulunduğu bir değeri temsil eder. Sıfırlar, hariç tutulan dizelerin bulunmadığını gösterir. Sıfır olmayan tüm değerleri 1'e zorlamak için sıfırdan büyük kullanırız:
(2;1;0;0;1;1;0;0;0;2)>0
başka bir dizi veya DOĞRU ve YANLIŞ değerleri oluşturan:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nihai hedefimiz, yalnızca hariç tutulan dizelerin bulunmadığı metin değerlerini saymaktır, bu nedenle bu değerleri tersine çevirmemiz gerekir. Bunu, diziyi 1'den çıkararak yapıyoruz. Bu, boole mantığının bir örneğidir. Matematik işlemi, DOĞRU ve YANLIŞ değerlerini otomatik olarak 1'ler ve 0'lara zorlar ve sonunda SUM işlevine dönecek bir dizimiz var:
=SUM((0;0;1;1;0;0;1;1;1;0))
TOPLA işlevi 5'in nihai sonucunu döndürür.
