Genel formül
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Özet
Excel'de büyük / küçük harfe duyarlı aramalar
Varsayılan olarak, Excel'deki standart aramalar büyük / küçük harfe duyarlı değildir. Hem DÜŞEYARA hem de DİZİN / MAÇ, büyük / küçük harfe bakmadan ilk eşleşmeyi döndürür.
Bu sınırlamayı geçici olarak çözmenin doğrudan bir yolu, INDEX / MATCH ile EXACT'e dayalı bir dizi formülü kullanmaktır. Ancak, yalnızca sayısal değerleri arıyorsanız, SUMPRODUCT + EXACT, büyük / küçük harfe duyarlı bir arama yapmak için ilginç ve esnek bir yol da sağlar.
Örnekte aşağıdaki formülü kullanıyoruz
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Bu formül bir dizi formülü olsa da, SUMPRODUCT dizileri yerel olarak işlediği için Control + Shift + Enter ile girilmesine gerek yoktur.
Açıklama
SUMPRODUCT, çarptığı ve sonra topladığı dizilerle çalışmak üzere tasarlanmıştır.
Bu durumda, SUMPRODUCT ile iki diziyiz: B3: B8 ve C3: C8. İşin püf noktası, B sütunundaki değerler üzerinde bir test yapmak ve ardından elde edilen DOĞRU / YANLIŞ değerlerini 1'lere ve 0'lara dönüştürmektir. Testi EXACT ile şu şekilde çalıştırıyoruz:
EXACT(E3,B3:B8)
Bu diziyi üreten:
(YANLIŞ; YANLIŞ; DOĞRU; YANLIŞ; YANLIŞ; YANLIŞ)
3. konumdaki gerçek değerin bizim eşleşmemiz olduğunu unutmayın. Daha sonra bu DOĞRU / YANLIŞ değerlerini 1 ve 0'a zorlamak için çift negatif (yani -, teknik olarak "çift tekli") kullanırız. Sonuç şu dizidir:
(0; 0; 1; 0; 0; 0)
Hesaplamanın bu noktasında, SUMPRODUCT formülü şuna benzer:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT daha sonra son bir dizi oluşturmak için her dizideki öğeleri çarparak basitçe çoğaltır:
(0; 0; 775; 0; 0; 0)
Hangi SUMPRODUCT sonra toplar ve 775 döndürür.
Dolayısıyla, bu formülün özü, YANLIŞ değerlerinin diğer tüm değerleri iptal etmek için kullanılmasıdır. Hayatta kalan tek değerler DOĞRU olanlardır.
SUMPRODUCT kullandığımız için, bu formülün benzersiz bir bükülme ile geldiğine dikkat edin: birden çok eşleşme varsa, SUMPRODUCT bu eşleşmelerin toplamını döndürecektir. İstediğiniz şey bu olabilir veya olmayabilir, bu nedenle birden fazla maç bekliyorsanız dikkatli olun!
Unutmayın, bu formül yalnızca sayısal değerler için çalışır, çünkü SUMPRODUCT metni işlemez. Metni geri almak istiyorsanız, INDEX / MATCH + EXACT kullanın.
