Excel formülü: Eşleşen sütunları topla -

Genel formül

=SUMPRODUCT(data*(headers=A1))

Özet

Eşleşen sütun başlıklarını eşleştirerek sütunlardaki değerleri toplamak için, SUMPRODUCT işlevine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, J5'teki formül şöyledir:

=SUMPRODUCT(data*(LEFT(headers)=J4))

burada "veri", B5: G14 adlı aralıktır ve "üstbilgiler", B4: G4 adlı aralıktır.

Formül, başlıkların "a" ile başladığı ve 201 değerini verdiği sütunları toplar.

Açıklama

Temelde, bu formül "veri" C5: G14 adlı aralıktaki eşleşen sütunlardaki değerleri toplamak için SUMPRODUCT işlevine dayanır. Tüm veriler SUMPRODUCT ürününe tek bir aralıkta sağlandıysa sonuç, aralıktaki tüm değerlerin toplamı olur:

=SUMPRODUCT(data) // all data, returns 387

Sütun başlıklarını ("A" ile başlayan başlıklara sahip sütunları) eşleştirerek bir filtre uygulamak için LEFT işlevini şu şekilde kullanırız:

LEFT(headers)=J4) // must begin with "a"

Bir sütun başlığı "a" ile başlıyorsa bu ifade TRUE, değilse FALSE döndürür. Sonuç bir dizidir:

(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)

1,2 ve 5 değerlerinin "a" ile başlayan sütunlara karşılık geldiğini görebilirsiniz.

SUMPRODUCT içinde bu dizi "veri" ile çarpılır. Yayın nedeniyle sonuç şu iki boyutlu bir dizidir:

(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)

Bu diziyi bir tabloda görselleştirirsek, sadece "a" ile başlayan sütunlardaki değerlerin işlemden sağ çıktığını, diğer tüm sütunların sıfır olduğunu görmek kolaydır. Başka bir deyişle, filtre ilgilenilen değerleri tutar ve geri kalanı "iptal eder":

A001	A002	A003
8	10	7
9	10	10
8	6	6
7	6	6
8	6	6
10	11	7
7	8	8
2	3	3
3	4	4
7	7	4

İşlenecek tek bir dizi ile SUMPRODUCT, tüm değerlerin toplamını (201) döndürür.

Tam eşlemeye göre toplama

Yukarıdaki örnek, bir veya daha fazla belirli karakterle başlayan sütunların nasıl toplanacağını gösterir. Sütunu tam eşleşmeye göre toplamak için, aşağıdaki gibi daha basit bir formül kullanabilirsiniz: