Excel Etopla Formülü ve Çoketopla Formülü Kullanımı [1 şablon içerir]9 min read

Bu eğitim Excel etopla formülünü sade bir dil ile açıklamaktadır. Ana odak noktası, metin, sayılar, tarihler, joker karakterler dahil olmak üzere her türlü kriteri içeren gerçek hayattaki etopla formül örnekleridir.

Microsoft Excel, raporlar ve analizler için büyük veri kümelerini özetlemek üzere birçok formüle sahiptir. Anlaşılmaz bir dizi farklı veriyi anlamlandırmanıza yardımcı olabilecek en kullanışlı işlevlerden biri etopla formülüdür. Bir aralıktaki tüm sayıları toplamak yerine, yalnızca ölçütlerinizi karşılayan değerleri toplamanızı sağlar.

Dolayısıyla, göreviniz Excel’de koşullu toplam gerektirdiğinde, ihtiyacınız olan şey Etopla formülüdir. İşlevin Excel 2000’den Excel 365’e kadar tüm sürümlerde kullanılabilir olması iyi bir şeydir. Bir başka harika şey ise ETOPLA formülünü öğrendikten sonra diğer “EĞER” fonksiyonlarında uzmanlaşmak için çok az çaba harcamanız gerekecek olmasıdır..

Örnek çalışma dosyasını buradan indirebilirsiniz.

Excel’de etopla formülü sözdizimi ve temel kullanımlar

Excel koşullu toplam olarak da bilinen etopla formülü, belirli bir koşula dayalı olarak hücre değerlerini toplamak için kullanılır.

İşlev Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 ve daha düşük sürümlerde kullanılabilir.

Sözdizimi aşağıdaki gibidir:

Gördüğünüz gibi, ETOPLA fonksiyonunun 3 argümanı vardır – ilk 2’si gereklidir ve sonuncusu isteğe bağlıdır.

• aralık Gereklidir. Ölçüte göre değerlendirilmesini istediğiniz hücre aralığı. Aralıklardaki hücrelerde numaralar veya adlar, diziler ya da sayı içeren başvurular bulunmalıdır. Boşluk ve metin değerleri yoksayılır. Seçilen aralık standart Excel biçiminde tarihler içerebilir (aşağıdaki örnekler).
• ölçüt   Gereklidir. Hangi hücrelerin sayılacağını tanımlayan sayı, ifade, hücre başvurusu, metin ya da hangi hücrelerin toplanacağını tanımlayan bir işlev biçimindeki ölçüt. Joker karakterler eklenebilir- herhangi bir tek karakterle eşleşmesi için soru işareti (?), herhangi bir karakter dizisiyle eşleşmesi için yıldız işareti (*) . Gerçek bir soru işaretini veya yıldız işaretini bulmak istiyorsanız, karakterden önce bir tilde işareti (~) koyun.

Örneğin, ölçütler 32, “>32”, B5, “3?”, “elma*”, “*~?” veya BUGÜN() olarak ifade edilebilir.

Önemli: Tüm metin ölçütleri ve mantıksal ya da matematiksel simge içeren ölçütler çift tırnak (“) içine alınmalıdır. Ölçüt sayısal bir değerse, çift tırnak kullanmak gerekmez.

• toplam_aralığı   İsteğe bağlı. Aralık bağımsız değişkeninde belirtilen hücrelerden farklı hücrelerin toplamını almak istiyorsanız, toplamı alınacak hücreler. Toplam_aralığı bağımsız değişkeni belirtilmezse, Excel aralık bağımsız değişkeninde belirtilen hücreleri toplar (bunlar, ölçütün uygulandığı hücrelerdir).

Not. Lütfen tüm metin kriterlerinin veya mantıksal operatörler içeren kriterlerin çift tırnak işareti içine alınmasına dikkat edin, örneğin “elma”, “>10”. Hücre referansları tırnak işaretleri olmadan kullanılmalıdır, aksi takdirde metin dizeleri olarak değerlendirilirler.

Temel ETOPLA formulü

ETOPLA formülü sözdizimini daha iyi anlamak için aşağıdaki örneği göz önünde bulundurun. A sütununda bir ürün listeniz, B sütununda bölgeleriniz ve C sütununda satış tutarlarınız olduğunu varsayalım. Amacınız belirli bir bölge için, örneğin Kuzey için, toplam satışları elde etmektir. Bunu yapmak için, en basit haliyle bir Excel etopla formülü oluşturalım.

Aşağıdaki argümanları tanımlayarak başlarsınız:

– Aralık – bölgelerin bir listesi (B2:B10).

– Kriter – “Kuzey” veya ilgilenilen bölgeyi içeren bir hücre (F1).

– toplam aralığı – toplanacak satış tutarları (C2:C10).

Argümanları bir araya getirdiğimizde aşağıdaki formülü elde ederiz:

=ETOPLA(B2:B10;”Kuzey”;C2:C10)

veya

=ETOPLA(B2:B10;G2;C2:C10)

İki formül de Kuzey bölgesindeki satışları toplar:

Not. toplam aralığı parametresi aslında toplanacak aralığın yalnızca en sol üst hücresini tanımlar. Geri kalan alan aralık bağımsız değişkeninin boyutları tarafından tanımlanır. Pratikte bu, toplam aralığı bağımsız değişkeninin aralık bağımsız değişkeniyle aynı boyutta olması gerekmediği, yani farklı sayıda satır ve sütuna sahip olabileceği anlamına gelir. Ancak, sol üst hücre her zaman sağ hücre olmalıdır. Örneğin, yukarıdaki formülde, toplam aralığı bağımsız değişkeni olarak C2 veya C2:C4 veya hatta C2:C100 sağlayabilirsiniz ve sonuç yine de doğru olacaktır. Ancak, en iyi uygulama eşit büyüklükte aralık ve toplam aralığı sağlamaktır.

Not. etopla formülü doğası gereği büyük/küçük harfe duyarsızdır. Ancak, metnin büyük/küçük harfini tanımaya zorlamak mümkündür.

Etopla formülü excelde kullanım örnekleri

Umarım yukarıdaki örnek, fonksiyonun nasıl çalıştığına dair temel bir anlayış kazanmanıza yardımcı olmuştur. Aşağıda, etopla formülünün Excel’de çeşitli ölçütlerle nasıl kullanılacağını gösteren birkaç formül daha bulacaksınız.

ETOPLA (‘dan büyükse veya ‘dan küçükse)

Belirli bir değerden büyük veya küçük sayıları toplamak için ETOPLA kriterlerini aşağıdaki mantıksal operatörlerden biriyle yapılandırın:

• Büyüktür (>)
• Büyük eşittir (>=)
• Küçüktür (<)
• Küçük eşittir (<=)

Aşağıdaki tabloda, 3 veya daha fazla gün içinde gönderilen ürünlerin satış rakamlarını toplamak istediğinizi varsayalım. Bu koşulu ifade etmek için, sayıdan önce bir karşılaştırma operatörü (>) koyun ve yapıyı çift tırnak içine alın:

=ETOPLA(D2:D10;”>3″;C2:C10)

Hedef sayı başka bir hücredeyse, örneğin F1, mantıksal işleci ve hücre başvurusunu birleştirin:

=ETOPLA(D2:D10;”>”&G1;C2:C10)

Benzer şekilde, belirli bir sayıdan daha küçük değerleri de toplayabilirsiniz. Bunun için küçüktür (<) operatörünü kullanın:

=ETOPLA(D2:D10;”<3″;C2:C10)

ETOPLA (eşitse)

“Eşittir” ölçütüne sahip bir ETOPLA formülü hem sayılar hem de metinler için çalışır. Bu tür ölçütlerde, eşittir işareti aslında gerekli değildir.

Örneğin, 3 gün içinde gönderilen ürünlerin toplamını bulmak için aşağıdaki formüllerden herhangi biri işe yarar:

=ETOPLA(D2:D10;3;C2:C10)

veya

=ETOPLA(D2:D10;”=3″;C2:C10)

Hücreye eşitse toplamak için, ölçüt için bir hücre referansı sağlayın:

=ETOPLA(D2:D10;G5;C2:C10)

Burada D2:D10 tutarlar, C2:C10 sevkiyat süresi ve G5 istenen teslimat süresidir.

Aynı şekilde, “eşittir” kriterini metin değerleriyle de kullanabilirsiniz. Örneğin, Elma miktarlarını toplamak için aşağıdaki formüllerden birini seçin:

=ETOPLA(A2:A10;G2;C2:C10)

=ETOPLA(A2:A10;”Elma”;C2:C10)

Burada A2:A10, G2’deki değerle karşılaştırılacak öğelerin listesidir.

Yukarıdaki formüller, ölçütün tüm hücre içeriğiyle eşleştiğini ima eder. Sonuç olarak, Etopla formülü Elma satışlarını toplar, ancak örneğin Yeşil Elmaları toplamaz. Kısmi eşleşmeleri toplamak için, bu ETOPLA joker karakter formülündeki gibi “hücre içeriyorsa” ölçütünü oluşturun.

Not. Excel ETOPLA formüllerinde, ister tek başına ister bir sayı veya metinle birlikte kullanılsın, bir karşılaştırma veya eşittir operatörünün her zaman çift tırnak içine alınması gerektiğine lütfen dikkat edin.

Etopla formülü (eşit değilse)

“Eşit değil” kriterini oluşturmak için “<>” mantıksal operatörünü kullanın.

Kriterlerde metin veya sayı olarak bir değer sabit kodlandığında, tüm yapıyı çift tırnak işaretiyle çevrelemeyi unutmayın.

Örneğin, 3 gün dışında sevkiyatı olan tutarları toplamak için formül aşağıdaki gibidir:

=ETOPLA(D2:D10; “<>”&G6; C2:C10)

Elmalar hariç tüm öğelerin toplamını bulmak için formül şöyledir:

=ETOPLA(A2:A10; “<>”&G2; C2:C10)

Etopla formülü (boş değilse)

“Hücre boş değilse topla” türünde bir formül oluşturmak için ölçüt olarak “<>” kullanın. Bu, sıfır uzunluklu dizeler de dahil olmak üzere, içinde herhangi bir şey içeren tüm hücreleri toplayacaktır.

Örneğin, B sütununun boş olmadığı tüm bölgeler için satışları şu şekilde toplayabilirsiniz:

=ETOPLA(B2:B10; “<>”; C2:C10)

Excel ETOPLA metin kriteri

Bir sütundaki sayıları başka bir sütundaki metin değerlerine göre toplarken, tam ve kısmi eşleşme arasında ayrım yapmak önemlidir.

ETOPLA formulü ve joker karakter kullanımı

Hücreleri kısmi eşleşmeye göre koşullu olarak toplamak için, ölçütlerinize aşağıdaki joker karakterlerden birini ekleyin:

– Soru işareti (?) belirli bir konumdaki herhangi bir tek karakterle eşleşmek için.

– Yıldız işareti (*) herhangi bir sayıda karakterle eşleşmek için.

Örnek 1. Kısmi eşleşmeye dayalı toplam değerler

Kuzey, Kuzey-Doğu ve Kuzey-Batı dahil olmak üzere tüm kuzey bölgelerinin satışlarını toplamak istediğinizi varsayalım. Bunu yapmak için “kuzey” kelimesinden hemen sonra bir yıldız işareti koyun:

=ETOPLA(B2:B10;”Kuzey*”;C2:D10)

Her iki tarafa da yıldız işareti koymak da işe yarayacaktır:

=ETOPLA(B2:B10;”*kuzey*”;C2:D10)

Alternatif olarak, ilgilenilen bölgeyi önceden tanımlanmış bir hücreye (F1) yazabilir ve ardından bir hücre referansı ile tırnak içine alınmış bir joker karakteri birleştirebilirsiniz:

=ETOPLA(B2:B10;F1&”*”;C2:C10)

Örnek 2. Hücre * veya ? içeriyorsa toplama

Gerçek bir soru işareti veya yıldız işaretiyle eşleştirmek için karakterin önüne bir tilde (~) koyun, örneğin “~?” veya “~*”.

Örneğin, * ile işaretlenmiş bölgelerin satışlarını toplamak için kriter olarak “*~*” kullanın. Bu durumda, ilk yıldız işareti bir joker karakterdir ve ikincisi gerçek bir yıldız karakteridir:

=ETOPLA(B2:B10; F1&”*”; C2:C10)

Ölçüt (bizim durumumuzda *) ayrı bir hücreye girilirse, tilde ve hücre referansını aşağıdaki gibi birleştirin:

=ETOPLA(B2:B10; “*”&”~”&F5; C2:C10)

Etopla formülünün tarihlerle kullanımı

Tarihleri ETOPLA kriteri olarak kullanmak sayıları kullanmaya çok benzer. En önemli şey, Excel’in anladığı biçimde bir tarih sağlamaktır. Hangi tarih biçiminin desteklenip hangisinin desteklenmediğinden emin değilseniz, TARİH işlevi bir çözüm olabilir.

10-Eylül-2020 tarihinden önce teslim edilen ürünlerin satışlarını toplamak istediğinizi varsayarsak, kriterler şu şekilde ifade edilebilir:

=ETOPLA(C2:C10; “<“&F1; B2:B10)

veya

=ETOPLA(C2:C10;”<“&TARİH(2020.9.10);B2:B10)

veya

=ETOPLA(C2:C10;”<“&F1;B2:B10)

Burada F1 hedef tarihtir.

Bir tarih aralığında toplama yapmak için, daha küçük ve daha büyük bir tarihi ayrı ayrı tanımlamanız gerekir. Bu, birden çok ölçütü destekleyen ÇOKETOPLA işlevi yardımıyla yapılabilir.

Örneğin, C sütunundaki bir tarih iki tarih arasındaysa B sütunundaki değerleri toplamak için kullanılacak formül şudur:

=ÇOKETOPLA(B2:B10;C2:C10;”>=”&I1;C2:C10;”<=”&J1)

Burada B2:B10 toplam aralığı, C2:C10 kontrol edilecek tarihlerin listesi, F1 başlangıç tarihi ve G1 bitiş tarihidir.

Etopla formülü neden çalışmaz?

Excel etopla formülünün sizin için çalışmamasının birkaç nedeni olabilir. Bazen formülünüzün beklediğiniz sonucu vermemesinin tek nedeni, bir hücredeki veya bazı bağımsız değişkenlerdeki veri türünün Etopla formülü için uygun olmamasıdır. Aşağıda kontrol edilmesi gereken önemli şeylerin bir listesi bulunmaktadır.

1. ETOPLA sadece bir şart destekler

Etopla formülünün sözdiziminde yalnızca bir koşul için yer vardır. Birden çok ölçütle toplama yapmak için ya ÇOKETOPLA işlevini kullanın (tüm koşulları karşılayan hücreleri toplar) ya da birden çok VEYA ölçütü içeren bir etopla formülü oluşturun (koşullardan herhangi birini karşılayan hücreleri toplar).

2. Aralık ve toplam aralık boyu aynı olmalı

Bir etopla formülünün doğru çalışması için, aralık ve topla aralık bağımsız değişkeninin aynı boyutlara sahip olması gerekir, aksi takdirde yanıltıcı sonuçlar alabilirsiniz. Burada önemli olan nokta, Microsoft Excel’in kullanıcının eşleşen aralıklar sağlama becerisine güvenmemesi ve olası tutarsızlık sorunlarını önlemek için, toplam aralığını bu şekilde otomatik olarak belirlemesidir:

Toplam aralığı yalnızca toplanacak aralığın sol üst hücresini tanımlar, kalan alan aralık bağımsız değişkeninin boyutu ve şekli tarafından belirlenir.

Yukarıdakiler göz önüne alındığında, aşağıdaki formül aslında C2:D10’daki değil C2:C10’daki hücreleri toplayacaktır. Neden mi? Çünkü aralık 1 sütun ve 9 satırdan oluşur ve toplam aralık da öyle.

=ETOPLA(B2:B10; “kuzey”; C2:D10)

Eski Excel sürümlerinde, eşit olmayan boyuttaki aralıklar birçok soruna neden olabilir. Modern Excel’de, toplam aralığı öğesinin aralıktan daha az satır ve/veya sütuna sahip olduğu karmaşık etopla formülleri de kaprislidir. Bu nedenle, bu iki bağımsız değişken için her zaman aynı sayıda satır ve sütun tanımlamak iyi bir uygulamadır.

3. ETOPLA kriter sözdizimi

ETOPLA fonksiyonu kriterler için metin, sayılar, tarihler, hücre referansları, mantıksal operatörler (>, <, =, <>), joker karakterler (?, *, ~) ve diğer fonksiyonlar dahil olmak üzere farklı veri türlerinin kullanılmasına izin verir. Bu tür kriterlerin sözdizimi oldukça spesifiktir.

Ölçüt bağımsız değişkeni bir metin değeri, joker karakter veya mantıksal işleç içeriyorsa ve ardından metin, sayı veya tarih geliyorsa, tüm ölçütü tırnak işaretleri içine alın. Örneğin:

=ETOPLA(B2:B10;”kuzey*”; C2:D10)

=ETOPLA(C2:D10; “>100”)

=ETOPLA(B2:B10; “<>kuzey”; C2:D10)

=ETOPLA(C2:C10; “<=9/10/2020”; B2:B10)

Bir mantıksal işleci bir hücre başvurusu veya başka bir işlev izlediğinde, ölçütler bir dize biçiminde sağlanmalıdır. Bu nedenle, bir mantıksal işleç ile bir başvuruyu veya işlevi birleştirmek için bir ve işareti (&) kullanırsınız. Örneğin:

=ETOPLA(B2:B10;”>”&F1)

=ETOPLA(C2:D10; “<=”&BUGÜN(); B2:B10)

4. ETOPLA metin büyük/küçük harflerini tanımıyor

Tasarım gereği, Excel’deki ETOPLA büyük/küçük harfe duyarlı değildir, yani büyük ve küçük harfleri aynı karakterler olarak ele alır. Büyük/küçük harfe duyarlı bir ETOPLA formülü oluşturmak için, TOPLA.ÇARPIM işlevini ÖZDEŞ ile birlikte kullanın.

Microsoft destek sayfasını da inceleyebilirsiniz.