Şimdiye kadar Excel'de bir tablonun tüm eşleşen sütununu nasıl toplayacağımızı öğrendik. Ancak sütun ve satırı eşleştirmemiz gerektiğinde değerleri nasıl toplarız. Bu yazıda, eşleşen satır ve sütunların toplamını nasıl yapacağımızı öğreneceğiz.
Bunu yapmak için iki formül var ama önce senaryoyu verelim.
Burada, çalışanların farklı aylarda yaptığı satışları kaydeden bir tablom var. Çalışanların adı tekrar edebilir. Aşağıdaki şekle bakın:
Satıcının Donald olduğu Mayıs ayının toplamını almamız gerekiyor.
Yöntem 1: Eşleşen sütun başlığını ve satır başlığını SUMPRODUCT işlevini kullanarak toplama.
NS SUMPRODUCT Fonksiyonu zor kriterlerle değerleri toplama ve sayma söz konusu olduğunda en çok yönlü işlevdir. Sütun ve satırı eşleştirerek toplanacak genel işlev:
| =SUMPRODUCT((sütunlar)*(column_headers=column_heading)*(row_headers=row_heading) |
Sütunlar:Toplamak istediğiniz sütunların 2 boyutlu aralığıdır. Başlık içermemelidir. Yukarıdaki tabloda C3:N7'dir.
sütun_başlıkları:Bu, başlık aralığıdırsütunlar özetlemek istediğiniz Yukarıdaki verilerde C2:N2'dir.
Sütun başlığı: Eşleştirmek istediğiniz başlıktır. Yukarıdaki örnekte, B13'tedir.
Daha fazla gecikmeden formülü kullanalım.
satır_başlıkları:Bu, başlık aralığıdırsatırlar özetlemek istediğiniz Yukarıdaki verilerde B3:B10'dur.
satır_başlığı: Satırlarda eşleştirmek istediğiniz başlıktır. Yukarıdaki örnekte, F13'tedir.
Daha fazla gecikmeden formülü kullanalım.
Bu formülü D13 hücresine yazın ve Excel'in sihrini yapmasına izin verin (sihir diye bir şey yoktur)…
| =TOPLAÇA((C3:N10)*(C2:N2=B13)*(B3:B10=E13)) |
Bu değeri döndürür:
Artık ayı veya satıcıyı değiştirdiğinizde, toplam satır başlığına ve sütun başlığına göre değişecektir.
O nasıl çalışır?
Bu basit boole mantığı.
(C2:N2=B13): Bu ifade DOĞRU ve YANLIŞ dizisini döndürür. Sütundaki tüm eşleşen değerler doğru ve diğerleri yanlış olacaktır. Bu durumda, C2:N2 aralığı yalnızca bir örnek içerdiğinden, yalnızca bir True değerine sahip olacağız.NS Konum.
(B3:B10=E13): Bu, yukarıdakiyle aynı şekilde çalışacak ve DOĞRU ve YANLIŞ bir dizi döndürecektir. Tüm eşleşen değerler DOĞRU ve diğerlerinde YANLIŞ olacaktır. Bu durumda, B3:B10 aralığı iki "Donald" örneğine sahip olduğundan 2 DOĞRU'ya sahip olacağız.
(C2:N2=B13)*(B3:B10=E13): Şimdi ifadelerle döndürülen dizileri çarpıyoruz. Bu uygulayacak ve mantıklı olacak ve 1'ler ve 0'lardan oluşan bir dizi elde edeceğiz. Şimdi 2 1 ve kalan 0'ları içeren bir 2B dizimiz olacak.
(C3:N10)*(C2:N2=B13)*(B3:B10=E13)= Son olarak 2B diziyi 2B tablo ile çarpıyoruz. Tekrar 0'lardan oluşan bir diziyi ve kriterlere uyan sayıları döndürür.
Son olarak, SUMPRODUCT işlevi, istenen çıktıyla sonuçlanacak diziyi toplayacaktır.
Yöntem 2: Eşleşen sütun başlığını ve satır başlığını özetleme SUM ve IF işlevini kullanarak
SUM ve EĞER Excel işlevini kullanarak eşleşen satır ve sütunu toplamak için genel formül:
| =TOPLA(EĞER(sütun_başlıkları=sütun_başlığı,EĞER(satır_başlıkları=satır_başlıkları,sütunlar))) |
Tüm değişkenler yukarıda açıklanan yöntemle aynıdır. Burada, sadece farklı bir sırayla kullanılmaları gerekiyor.
Bu formülü D13 hücresine yazın:
| =TOPLA(EĞER(C2:N2=B13,EĞER(B3:B10=E13,C3:N10))) |
Bu doğru cevabı döndürür. Aşağıdaki ekran görüntüsüne bakın:
O nasıl çalışır?
Mantık, ilk SUMPRODCUT yöntemiyle aynıdır, yalnızca mekanizma farklıdır. Kısaca açıklarsam, iç IF İşlevi, tabloyla aynı boyutta 2B bir dizi döndürür. Bu dizi, eşleşen iki satırın sayısını içerir. Ardından, iç EĞER İşlevi bu dizideki iki sütunlu başlıklarla eşleşir ve yalnızca hem sütun hem de başlıkla eşleşen sayıları içeren 2B diziyi döndürür. Dizinin diğer tüm elemanları YANLIŞ olacaktır.
Son olarak, SUM işlevi bu diziyi özetler ve toplamımızı alırız.
Evet arkadaşlar, Excel'de bir tablodaki eşleşen satırları ve sütunları bu şekilde özetleyebilirsiniz. Umarım sizler için açıklayıcı ve faydalı olmuştur. Bu konuyla ilgili herhangi bir şüpheniz varsa veya başka bir excel/VBA ile ilgili şüpheniz varsa, aşağıdaki yorumlar bölümünde sorun.
Başlık Eşleştirilerek Excel'de Sütun Nasıl Toplanır | Sadece sütun adını kullanarak bir sütunun toplamını almak istiyorsanız, bunu Excel'de 3 kolay yolla yapabilirsiniz. Eşleşen sütunu toplamak için SUMPRODUCT yönteminin sözdizimi şöyledir:
Excel'de 3B Başvuru ile SUMIF |Eğlenceli gerçek şu ki, normal Excel 3B referansı, SUMIF işlevi gibi koşullu işlevlerle çalışmaz. Bu yazıda, SUMIF işleviyle çalışan 3B referans almanın nasıl yapıldığını öğreneceğiz.
Excel'de Göreli ve Mutlak Başvuru | Excel'de referans vermek her yeni başlayan için önemli bir konudur. Deneyimli excel kullanıcıları bile referans vermede hata yapar.
Dinamik Çalışma Sayfası Referansı | Excel'in DOLAYLI işlevini kullanarak dinamik olarak referans sayfaları verin. Bu basit…
Excel'de Referansları Genişletme | Genişleyen referans, aşağı veya sağa kopyalandığında genişler. Bunu yapmak için sütun ve satır numarasından önceki $ işaretini kullanırız. İşte bir örnek…
Mutlak Referans Hakkında Her Şey | Excel'de varsayılan referans türü görecelidir, ancak hücre ve aralıkların referansının mutlak olmasını istiyorsanız $ işaretini kullanın. Excel'de mutlak referanslamanın tüm yönleri.
Popüler Makaleler:
Verimliliğinizi Artıracak 50 Excel Kısayolu | Görevinizde daha hızlı olun. Bu 50 kısayol, çalışmanızı Excel'de daha da hızlandıracak.
Excel'de DÜŞEYARA İşlevi | Bu, farklı aralıklardan ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel 2016'da EĞERSAY | Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli bir değeri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi Nasıl Kullanılır | Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.