Örnek çalışma kitabını indirin
Bu Öğretici, Excel SUMPRODUCT İşlevi Excel'de.
SUMPRODUCT İşlevine Genel Bakış
SUMPRODUCT İşlevi, sayı dizilerini çarpar ve sonuçtaki diziyi toplar.
SUMPRODUCT Excel Çalışma Sayfası İşlevini kullanmak için bir hücre seçin ve şunu yazın:
(Formül girişlerinin nasıl göründüğüne dikkat edin)
SUMPRODUCT işlevi Sözdizimi ve girişler:
| 1 | =TOPLAÇARI(dizi1,dizi2,dizi3) |
dizi1 - Sayı dizileri.
SUMPRODUCT Fonksiyonu nedir?
SUMPRODUCT işlevi, Excel'deki en güçlü işlevlerden biridir. Adı, yalnızca temel matematik hesaplamaları için olduğuna inanmanıza neden olabilir, ancak çok daha fazlası için kullanılabilir.
diziler
SUMPRODUCT, dizi girişlerini gerektirir.
İlk olarak, "dizi" ile ne demek istiyoruz? Bir dizi, tıpkı bir hücre aralığı gibi, belirli bir sıraya göre düzenlenmiş basit bir öğe grubudur (ör. sayılar). Dolayısıyla, A1:A3 hücrelerinde 1, 2, 3 sayılarına sahip olsaydınız, Excel bunu {1,2,3} dizisi olarak okurdu. Aslında, doğrudan Excel formüllerine {1,2,3} girebilirsiniz ve diziyi tanıyacaktır.
Aşağıda diziler hakkında daha fazla konuşacağız, ama önce basit bir örneğe bakalım.
Temel Matematik
Toplam satışları hesaplamak için SUMPRODUCT'in temel bir örneğine bakalım.
Ürün tablomuz var ve toplam satışları hesaplamak istiyoruz. Sadece yeni bir sütun eklemek, satılan miktarı * fiyatını almak ve ardından yeni sütunu toplamak için cazipsiniz. Ancak bunun yerine SUMPRODUCT İşlevini kullanabilirsiniz. Formülü inceleyelim:
| 1 | =TOPLA(A2:A4,B2:B4) |
İşlev, sayı aralıklarını dizilere yükleyecek, bunları birbiriyle çarpacak ve ardından sonuçları toplayacaktır:
| 1234 | =TOPLA({100, 50, 10}, {6, 7, 5})=TOPLA({100 * 6, 50 * 7, 10 * 5})=TOPLAÇARI({600, 350, 50}=1000 |
SUMPRODUCT Funciton, bizim için tüm sayıları çarpmayı VE toplamayı başardı.
Ağırlıklı ortalama
SUMPRODUCT kullanmanın yararlı olduğu başka bir durum, ağırlıklı ortalama hesaplamanız gerektiğidir. Bu genellikle okul ödevleriyle uğraşırken ortaya çıkar, bu yüzden aşağıdaki tabloyu ele alalım.
Kısa sınavların, testlerin ve ev ödevlerinin genel not için ne kadar değerli olduğunu ve ayrıca her bir belirli öğe için mevcut ortalamanın ne olduğunu görebiliriz. Genel notu daha sonra yazarak hesaplayabiliriz
| 1 | =TOPLA(B2:B4, C2:C4) |
Fonksiyonumuz, toplamı toplamadan önce dizilerdeki her bir öğeyi tekrar çarpar. Bu böyle çalışır
| 123 | =TOPLAÇA({%30, %50, %20}, {73%, %90, %95})=TOPLAÇARI({22%, %45, %19})=86% |
Birden Çok Sütun
SUMPRODUCT'i kullanabileceğimiz başka bir yer, birbiriyle çarpılması gereken daha da fazla sütunun bulunduğu yerdir. Kereste parçalarının hacmini hesaplamamız gereken bir örneğe bakalım.
Her satır için toplam satışı hesaplamak için bir yardımcı sütun oluşturmak yerine, bunu tek bir formülle yapabiliriz. bizim formülümüz olacak
| 1 | =TOPLAÇARI(B2:B5, C2:C5, D2:D5) |
Her dizinin ilk öğeleri birbiriyle çarpılır (örneğin, 4 * 2 * 1 = 8). Ardından, 2. (4 * 2 * 2 = 16) ve 3.rd, vb. Genel olarak, bu, {8, 16, 16, 32) gibi görünen ürün dizisini üretecektir. O zaman toplam hacim o dizinin toplamı olur, 72.
Bir kriter
Tamam, başka bir karmaşıklık katmanı ekleyelim. SUMPRODUCT'in sayı dizilerini işleyebileceğini gördük, peki ya kriterleri kontrol etmek istiyorsak? Boole değerleri için de diziler oluşturabilirsiniz (Boole Değerleri DOĞRU veya YANLIŞ olan değerlerdir).
Örneğin, temel bir dizi {1, 2, 3} alın. Her sayının 1'den büyük olup olmadığını gösteren karşılık gelen bir dizi oluşturalım. Bu dizi {YANLIŞ, DOĞRU, DOĞRU} gibi görünecektir.
Bu, formüllerde son derece yararlıdır, çünkü DOĞRU / YANLIŞ'ı kolayca 1 / 0'a dönüştürebiliriz. Bir örneğe bakalım.
Aşağıdaki tabloyu kullanarak “Kırmızı kaç adet satıldı?” hesaplamak istiyoruz.
Bunu şu formülle yapabiliriz:
| 1 | =TOPLA(A2:A4, --(B2:B4="Kırmızı")) |
"Devam etmek! Oradaki çift eksi sembolü ne? diyorsun. Doğru/Yanlıştan 1/0'a çevirebileceğimizi söylediğimi hatırlıyor musunuz? Bunu bilgisayarı matematiksel bir işlem yapmaya zorlayarak yapıyoruz. Bu durumda “negatif değeri al, sonra negatifi tekrar al” diyoruz. Bunu yazarken dizimiz şu şekilde değişecek:
| 123 | {Doğru, Doğru, Yanlış}{-1, -1, 0}{1, 1, 0} |
Yani, tam SUMPRODUCT formülüne geri dönersek, dizilerimize yüklenecek ve sonra şöyle çarpılacaktır.
| 123 | =TOPLA({100, 50, 10}, {1, 1, 0})=TOPLA({100, 50, 0})=150 |
3'ün nasıl olduğuna dikkat edinrd öğe 0 oldu, çünkü 0 ile çarpılan her şey sıfır olur.
Birden çok kriter
Fonksiyonumuza 255'e kadar dizi yükleyebiliriz, böylece kesinlikle daha fazla kriter yükleyebiliriz. Satılan Ay'ı eklediğimiz bu daha büyük tabloya bakalım.
Satılan ürünlerin kaç tanesinin kırmızı olduğunu bilmek istiyorsak ve Şubat ayında olsaydık formülümüzü şöyle yazabilirdik
| 1 | =TOPLA(A2:A4, --(B2:B4="Kırmızı")), --(C2:C4="Şubat")) |
Bilgisayar daha sonra dizilerimizi değerlendirir ve çarpar. Doğru/Yanlış dizilerinin nasıl 1/0'a dönüştüğünü zaten ele aldık, bu yüzden şimdilik bu adımı atlayacağım.
| 123 | =TOPLAÇARI({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=TOPLA({0, 50, 0})=50 |
Örneğimizde tüm ölçütlerle eşleşen yalnızca bir satırımız vardı, ancak gerçek verilerle birlikte eklenmesi gereken birden çok satırınız olabilirdi.
Karmaşık kriterler
Tamam, bu noktaya kadar etkilenmemiş olabilirsiniz çünkü tüm örneklerimiz SUMIF veya COUNTIF gibi diğer işlevler kullanılarak yapılabilirdi. Şimdi diğer işlevler için bir şeyler yapacağız. yapamam yapmak. Önceden, Ay sütunumuz ayların gerçek adlarını içeriyordu. Ya bunun yerine tarihleri olsaydı?
Şu anda bir SUMIF yapamıyoruz çünkü SUMIF ihtiyacımız olan kriterleri karşılayamıyor. Yine de SUMPRODUCT, diziyi değiştirmemize ve daha derin bir test yapmamıza yardımcı olabilir. Doğru/Yanlış'ı 1/0'a çevirdiğimizde zaten dizileri manipüle ediyorduk. Bu diziyi MONTH fonksiyonu ile manipüle edeceğiz. İşte kullanacağımız tam formül
| 1 | =TOPLA(A2:A4, --(B2:B4="Kırmızı")), --(AY(C2:C4)=2)) |
3'e bakalımrd daha yakından sıralayın. İlk olarak, formülümüz C2:C4'teki her tarihten ay numarasını çıkaracak. Bu bize {1, 2, 2} değerini verecektir. Sonra, bu değerin 2'ye eşit olup olmadığını kontrol ederiz. Şimdi dizimiz {False, True, True} gibi görünüyor. Çift eksiyi tekrar yaparız ve elimizde {0, 1, 1} olur. Şimdi Örnek 3'te sahip olduğumuz benzer bir noktaya geri döndük ve formülümüz bize Şubat ayında kırmızı olan 50 birim satıldığını söyleyebilecek.
Çift eksi ve çarpma
SUMPRODUCT işlevinin daha önce kullanıldığını gördüyseniz, biraz farklı bir gösterim görmüş olabilirsiniz. Çift eksi kullanmak yerine yazabilirsiniz
| 1 | =TOPLA(A2:A4*(B2:B4="Kırmızı")*(AY(C2:C4)=2)) |
Formül yine aynı şekilde çalışacak, biz sadece bilgisayara dizileri çarpmak istediğimizi manuel olarak söylüyoruz. SUMPRODUCT bunu zaten yapacaktı, yani matematiğin işleyişinde bir değişiklik yok. Matematik işlemini gerçekleştirmek, Doğru/Yanlış'ımızı aynı 1/0'a dönüştürür. Peki, neden fark?
Çoğu zaman, çok fazla önemli değil ve kullanıcı tercihine bağlı. Çarpmanın gerekli olduğu durumlarda en az bir durum vardır.
SUMPRODUCT kullandığınızda, bilgisayar tüm bağımsız değişkenlerin (dizi1, dizi2, vb.) aynı boyutta olmasını bekler. Bu, aynı sayıda satır veya sütuna sahip oldukları anlamına gelir. Ancak, bir sonraki örnekte göreceğimiz SUMPRODUCT ile iki boyutlu dizi hesaplaması olarak bilinen şeyi yapabilirsiniz. Bunu yaptığınızda, diziler farklı boyutlardadır, bu yüzden “hepsi aynı boyutta” kontrolünü atlamamız gerekir.
İkili boyutlar
Önceki tüm örneklerde dizilerimiz aynı yönde ilerliyordu. SUMPRODUCT, bir sonraki tabloda göreceğimiz gibi, iki yönde giden şeyleri halledebilir.
İşte satılan birim tablomuz, ancak veriler, kategorilerin en üstte gittiği yerde yeniden düzenlenmiştir. Kaç tane Kırmızı madde olduğunu ve A kategorisinde olduğunu bulmak istiyorsak, yazabiliriz.
| 1 | =TOPLAÇA((A2:A4="Kırmızı")*(B1:C1="A")*B2:C4) |
Burada neler oluyor?? İki farklı yönde çarpacağımız ortaya çıktı. Bunu sadece yazılı bir cümleyle görselleştirmek daha zordur, bu yüzden bize yardımcı olacak birkaç görselimiz var. İlk olarak, satır kriterlerimiz (Kırmızı mı?) dizideki her satırda çarpılacaktır.
| 1 | =TOPLAÇARI((A2:A4="KIRMIZI")*B2:C4) |
Ardından, sütun kriterleri (kategori A mı?) her sütunda çarpılacak
| 1 | =TOPLAÇA((A2:A4="Kırmızı")*(B1:C1="A")*B2:C4) |
Bu kriterlerin her ikisi de işlerini yaptıktan sonra, sıfır olmayan tek şey 5 ve 10'dur. SUMPRODUCT daha sonra bize cevabımız olarak toplam 15'i verecektir.
İki boyutlu yapmıyorsanız dizilerin aynı boyutta olması gerektiğinden bahsettiğimizi hatırlıyor musunuz? Bu kısmen doğruydu. Formülümüzde kullandığımız dizilere tekrar bakar. NS boy uzunluğu dizilerimizden ikisinin aynısı ve Genişlik dizilerimizden ikisi aynı. Yani, yine de işlerin doğru bir şekilde sıralanacağından emin olmanız gerekiyor, ancak bunu farklı boyutlarda yapabilirsiniz.
İki boyutlu ve karmaşık
Çoğu zaman formüllerimize uygun en iyi düzende olmayan verilerle karşılaşırız. Manuel olarak yeniden düzenlemeyi deneyebiliriz veya formüllerimizle daha akıllı olabiliriz. Aşağıdaki tabloyu ele alalım.
Burada, her ay için birlikte karışık ürün ve satış verilerimiz var. Bob'un tüm yıl boyunca kaç ürün sattığını nasıl bulabiliriz?
Bunu yapmak için iki ek işlev kullanacağız: SEARCH ve ISNUMBER. SEARCH işlevi, başlık hücrelerinde anahtar kelimemiz olan “öğeleri” aramamıza izin verecek. Bu işlevin çıktısı ya bir sayıya ya da bir hataya (anahtar kelime bulunamazsa) gidecektir. Ardından, dönüştürmek için ISNUMBER'ı kullanacağız o Boolean değerlerimize çıktı. Formülümüz aşağıdaki gibi olacak.
Şimdiye kadar ilk diziye oldukça aşina olmalısınız. {0, 1, 0, 1} gibi bir çıktı oluşturacak. Az önce bahsettiğimiz bir sonraki kriter dizisi. İçinde “Öğeler” bulunan tüm hücreler için bir sayı ve diğerleri için bir hata oluşturacak {5, #N/A!, 5, #N/A!}. ISNUMBER daha sonra bunu Boolean {True, False, True, False} değerine dönüştürür. Sonra çarptığımızda, yalnızca birinci ve üçüncü sütundaki değerleri tutacak. Tüm diziler birbiriyle çarpıldıktan sonra, sahip olacağımız tek sıfır olmayan sayılar burada vurgulananlardır:
| 1 | =TOPLAÇA((A2:A5="Bob")*(ESAYI(ARAMA("Öğeler",B1:E1))*B2:E5)) |
SUMPRODUCT daha sonra hepsini toplayacak ve 29'luk nihai sonucumuzu elde edeceğiz.
SUMPRODUCT Veya
Ölçüt sütunumuzda bir değer VEYA başka bir değer varsa, değerleri toplayabilmek istediğimiz birçok durum ortaya çıkar. Bunu SUMPRODUCT'te birbirine karşı iki ölçüt dizisi ekleyerek yapabilirsiniz.
Bu örnekte, hem Kırmızı hem de Mavi için satılan birimleri toplamak istiyoruz.
Formülümüz şu şekilde olacak
| 1 | =TOPLA(A2:A7, (B2:B7="Kırmızı")+(B2:B7="Mavi")) |
Kırmızı kriter dizisine bakalım. Şuna benzeyen bir dizi üretecektir: {1, 1, 0, 0, 0, 0}. Mavi ölçüt dizisi {0, 0, 1, 0, 1, 0} gibi görünecektir. Bunları bir araya getirdiğinizde yeni dizi {1, 1, 1, 0, 1, 0} gibi görünecektir. İki dizinin tek bir ölçüt dizisinde nasıl bir araya geldiğini görebiliriz. İşlev bunu ilk dizimizle çarpacak ve {100, 50, 10, 0, 75, 0} elde edeceğiz. Green değerlerinin sıfırlandığına dikkat edin. SUMPRODUCT'in son adımı, 235 çözümümüze ulaşmak için tüm sayıları bir araya getirmektir.
Burada bir uyarı sözü. Ölçüt dizilerinin birbirini dışlamamasına dikkat edin. Örneğimizde, B sütunundaki değerler Kırmızı veya Mavi olabilir, ancak hiçbir zaman ikisinin birden olamayacağını biliyorduk. Bu formülü yazıp yazmadığımızı bir düşünün:
| 1 | =TOPLAÇA(A2:A7, (A2:A7>=50)+(B2:B7="Mavi")) |
Amacımız, satılan veya miktarı 50'den fazla olan Mavi ürünleri bulmaktır. Ancak, tek bir satır A sütununda her ikisi de 50'nin üzerinde olabileceğinden, bu koşullar özel değildir. ve Mavi ol. Bu, ilk ölçüt dizisinin {1, 1, 0, 1, 1, 0} gibi görünmesine, ikinci ölçüt dizisinin {0, 0, 1, 0, 1, 0} olmasına neden olur. Bunları bir araya getirerek {1, 1, 1, 1, 2, 0} elde edilir. Şimdi orada nasıl 2'miz olduğunu görüyor musun? Tek başına bırakılırsa, SUMPRODUCT, 75'i 150'ye değiştirerek o satırdaki değeri ikiye katlar ve yanlış sonuç alırdık. Bunu düzeltmek için dizimize şöyle bir dış ölçüt kontrolü yerleştiririz:
| 1 | =TOPLAÇA(A2:A7, --((A2:A7>=50)+(B2:B7="Mavi")>0)) |
Şimdi, iki iç ölçüt dizisi bir araya toplandıktan sonra, sonucun 0'dan büyük olup olmadığını kontrol edeceğiz. Bu, daha önce sahip olduğumuz 2'den kurtulur ve bunun yerine {1, 1, 1 gibi bir dizimiz olur. , 1, 1, 0} doğru sonucu üretecektir.
SUMPRODUCT Tam
Excel'deki çoğu işlev büyük/küçük harf duyarlı değildir, ancak bazen büyük/küçük harf duyarlılığını göz önünde bulundurarak bir arama yapabilmemiz gerekir. İstenen sonuç sayısal olduğunda bunu SUMPRODUCT fonksiyonunun içinde EXACT kullanarak yapabiliriz. Aşağıdaki tabloyu göz önünde bulundurun:
“ABC123” Maddesinin puanını bulmak istiyoruz. Normalde, EXACT işlevi iki öğeyi karşılaştırır ve iki öğenin doğru olup olmadığını belirten bir Boolean çıktısı döndürür. kesinlikle aynısı. Ancak, bir SUMPRODUCT'in içinde olduğumuz için, bilgisayarımız dizilerle uğraştığımızı bilecek ve bir dizideki her bir öğeyle bir öğeyi karşılaştırabilecektir. Formülümüz şu şekilde olacak
| 1 | =TOPLAÇA(--TAM("ABC123", A2:A5), B2:B5) |
EXACT işlevi daha sonra A2:A5'teki her bir öğeyi değer ve büyük/küçük harfle eşleşip eşleşmediğini görmek için kontrol edecektir. Bu, {0, 1, 0, 0} gibi görünen bir dizi üretecektir. B2:B5 ile çarpıldığında dizi {0, 2, 0, 0} olur. Son toplamdan sonra, 2 çözümümüzü elde ederiz.
Google E-Tablolarda SUMPRODUCT
SUMPRODUCT İşlevi, Google E-Tablolar'da Excel'dekiyle tamamen aynı şekilde çalışır:
VBA'da SUMPRODUCT Örnekleri
VBA'da SUMPRODUCT işlevini de kullanabilirsiniz. Tür: application.worksheetfunction.sumproduct(dizi1,dizi2,dizi3)
Aşağıdaki VBA deyimlerini yürütme
| 1 | Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7")) |
aşağıdaki sonuçları üretecek
İşlev bağımsız değişkenleri (dizi1, vb.) için bunları doğrudan işleve girebilir veya bunun yerine kullanılacak değişkenleri tanımlayabilirsiniz.
