Genel formül
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Özet
Bir aralık eşleştirme ölçütü içindeki ilk n değerleri toplamak için, SUMPRODUCT işlevi içine sarılmış BÜYÜK işlevine dayalı bir formül kullanabilirsiniz. Formülün genel biçiminde (yukarıda), aralık , ölçütlerle karşılaştırılan bir hücre aralığını , değerler en üst değerlerin alındığı sayısal değerleri temsil eder ve N , N'inci değer fikrini temsil eder.
Örnekte, aktif hücre şu formülü içerir:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Burada renk, B5: B12 olarak adlandırılan aralıktır ve değer, adlandırılmış C5: C12 aralığıdır.
Açıklama
En basit haliyle, LARGE, şu yapıyla bir aralıktaki "N. en büyük" değeri döndürür:
=LARGE (range,N)
Yani mesela:
=LARGE (C5:C12,2)
gösterilen örnekte 12 olan C5: C12 aralığında 2. en büyük değeri döndürecektir.
Ancak, ikinci bağımsız değişken olarak BÜYÜK'e bir "dizi sabiti" (örneğin (1,2,3) biçiminde bir sabit) sağlarsanız, BÜYÜK tek bir sonuç yerine bir sonuç dizisi döndürür. Yani formül:
=LARGE (C5:C12, (1,2,3))
1., 2. ve 3. en büyük C5: C12 değerini aşağıdaki gibi bir dizide döndürecektir: (12,12,10)
Dolayısıyla, buradaki hile LARGE çalıştırılmadan önce değerleri renge göre filtrelemektir. Bunu şu ifade ile yapıyoruz:
(color=E5)
DOĞRU / YANLIŞ değerler dizisi ile sonuçlanır. Çarpma işlemi sırasında, bu değerler birlere ve sıfırlara zorlanır:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Dolayısıyla nihai sonuç, yalnızca "kırmızı" rengiyle ilişkili değerlerin işlemden sağ çıkmasıdır:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
ve diğer değerler sıfıra zorlanır.
Not: Bu formül, değer aralığındaki metni işlemez. Aşağıya bakınız.
Değerlerde metin işleme
Değer aralıklarının herhangi bir yerinde metniniz varsa, BÜYÜK işlevi bir # DEĞER hatası verir ve formülün çalışmasını durdurur.
Değer aralığındaki metni işlemek için, EĞERHATA işlevini şu şekilde ekleyebilirsiniz:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Burada, metin değerlerinden kaynaklanan LARGE hatalarını yakalar ve sıfır ile değiştiririz. LARGE içinde IF kullanmak, formülün kontrol + shift + enter ile girilmesini gerektirir, bu nedenle SUMPRODUCT yerine SUM'a geçiyoruz.
Not: Harika Barry Houdini tarafından stackoverflow'da yayınlanan bu formülle karşılaştım.
