Hepsi aynı belirli bir miktar. Excel'de toplanan işlevi kullanma örnekleri

Belirli bir kriteri (koşulu) karşılayan değerleri toplamak için SUMIF () işlevini kullanın , İngilizce sürüm SUMIF ().

İşlev sözdizimi

SUMIF(Aralık; Durum; [Toplam aralığı])

Aralık - bağımsız değişkene karşılık gelen değerlerin arandığı hücre aralığı Durum... Aralık, sayılar, tarihler, metin değerleri veya diğer hücrelere referanslar içerebilir. Diğer argümanın olması durumunda Toplam aralığı - atlanır, ardından argüman Aralık ayrıca bağımsız değişkeni karşılayan değerlerin toplamının gerçekleştirildiği aralıktır Durum (bu durumda Aralık sayı içermelidir).

Durum - hangi hücrelerin eklenmesi gerektiğini belirleyen sayı, ifade veya metin biçimindeki bir kriter. Örneğin, argüman Durum 32, "elma" veya "\u003e 32" olarak ifade edilebilir.

Toplam aralığı - bağımsız değişkende karşılık gelen hücreler belirtilmişse, toplanacak sayıları içeren bir hücre aralığı Aralıkargümanla eşleş Durum. Toplam aralığı - isteğe bağlı argüman. Atlanırsa, toplam, bağımsız değişkende belirtilen hücre aralığı üzerinden gerçekleştirilir. Aralık.

Örnekleri

Argümanın olduğu durumu düşünün Toplam aralığı ihmal edildi. Bu durumda, toplam, ilk bağımsız değişkende belirtilen hücre aralığı üzerinden gerçekleştirilecektir. Aralık (yani sayı içermelidir). Ayrıca bağımsız değişkene karşılık gelen değerleri arayacaktır. Durumdaha sonra özetlenecek. Bir aralık olsun B5: B15 örnek dosyaya bakınız.

Sorunları çözeceğiz:

10'a eşit veya daha büyük tüm sayıların toplamını bulun. Cevap: 175. Formül: \u003d SUMIF (B5: B15; "\u003e \u003d 10")
10'a eşit veya daha küçük tüm sayıların toplamını bulun. Cevap: 42. Formül: \u003d SUMIF (B5: B15, "<=10")
bir aralıktaki tüm pozitif sayıların toplamını bulun. Formül: \u003d SUMIF (B5: B15; "\u003e 0")... SUMPRODUCT () işlevini kullanan alternatif bir sürüm şuna benzer: \u003d TOPLA ÇARPIM ((B5: B15) * (B5: B15\u003e 0))

Görev formu şartlar(ölçüt) yeterince esnektir. Örneğin formülde \u003d SUMIF (B5: B15; D7 ve D8) kriter<=56 задан через ссылку D7&D8 : в D7 metin değeri içerir<=, а в D8 - 56 numara (aşağıdaki şekle bakın). Örneğin kullanıcı, ölçütü hücrede kullanarak kolayca değiştirebilir. D7 ... Eşdeğer formül \u003d SUMIF (B5: B15, "<=56") или \u003d SUMIF (B5: B15, "<="&56) veya \u003d SUMIF (B5: B15, "<="&D8) veya \u003d SUMIF (B5: B15; D7 ve 56).

ETOPLA işlevi, belirli bir ölçütü karşılayan bir aralıktaki değerleri toplamanız gerektiğinde kullanılır.

Fonksiyon açıklaması

ETOPLA işlevi, belirli bir ölçütü karşılayan bir aralıktaki değerleri toplamanız gerektiğinde kullanılır. Örneğin, bir sayı sütununda yalnızca 5'ten büyük değerler toplamanız gerekir. Bunu yapmak için aşağıdaki formülü kullanabilirsiniz:
\u003d SUMIF (B2: B25, "\u003e 5") Bu örnekte, toplanan değerler kritere göre kontrol edilir. Gerekirse, koşul bir aralığa uygulanabilir ve başka bir aralıktan karşılık gelen değerler toplanabilir. Örneğin formül:
\u003d SUMIF (B2: B5; "Ivan"; C2: C5), yalnızca B2: B5 aralığındaki karşılık gelen değerlerin "Ivan" a eşit olduğu C2: C5 aralığındaki değerleri toplar.
Fonksiyonu kullanmanın başka bir örneği aşağıdaki şekilde gösterilmektedir:

Bir koşulla kar hesaplamak için SUMIF kullanma örneği

Sözdizimi

\u003d SUMIF (aralık, koşul, [toplam_aralık])

Argümanlar

aralık koşulu toplam aralığı

Gerekli argüman. Kriterlere göre değerlendirilecek hücre aralığı. Her aralıktaki hücreler sayılar, adlar, diziler veya sayı başvuruları içermelidir. Metin değerleri içeren boş hücreler ve hücreler atlanır.

Gerekli argüman. Hangi hücrelerin toplanacağını belirleyen sayı, ifade, hücre başvurusu, metin veya işlev biçimindeki bir koşul. Örneğin, bir koşul 32, "\u003e 32", B5, "32", "elma" veya BUGÜN () olarak temsil edilebilir.

Önemli! Mantıksal ve matematiksel işaretler içeren tüm metin koşulları ve koşulları çift tırnak işareti (“) içine alınmalıdır. Koşul bir sayı ise, tırnak işareti kullanmanıza gerek yoktur.

İsteğe bağlı argüman. Aralık olarak belirtilen hücrelerden farklılarsa, değerlerin eklendiği hücreler. Toplam_aralık atlanırsa, Microsoft Excel aralık bağımsız değişkeninde belirtilen hücreleri (koşulun geçerli olduğu aynı hücreler) toplar.

Uyarılar

Koşul argümanında joker karakterler kullanabilirsiniz: soru işareti (?) Ve yıldız işareti (*). Soru işareti herhangi bir karakterle eşleşir ve yıldız işareti herhangi bir karakter dizisiyle eşleşir. Doğrudan bir soru işareti (veya yıldız işareti) bulmak istiyorsanız, önüne bir tilde (~) koymalısınız.
SUMIF işlevi, 255 karakterden uzun dizeleri # DEĞER! Dizesiyle eşleştirmek için kullanılıyorsa yanlış sonuçlar döndürür.
Toplam_aralık bağımsız değişkeni, aralık bağımsız değişkeniyle aynı boyutta olmayabilir. Toplanacak gerçek hücreler belirlenirken, toplam_aralık bağımsız değişkeninin sol üst hücresi başlangıç \u200b\u200bhücresi olarak kullanılır ve ardından aralık bağımsız değişkeniyle boyutlandırılan aralığın bölümündeki hücreler toplanır.

Alanları aynı anda iki kriteri sağlayan değerleri satırlara ekleyelim (Koşul VE). Metin kriterleri, Sayısal ve Tarih kriterlerini göz önünde bulundurun. ETOPLA işlevini inceleyelim ( ), İngilizce sürüm SUMIFS ().

Kaynak tablo olarak iki sütun (alan) içeren bir tablo alalım: metin " Meyve"Ve sayısal" Stok adedi"(Örnek dosyaya bakın).

Problem1 (1 metin ölçütü ve 1 sayısal)

Belli bir kutuya sahip malların sayısını bulalım. Meyve VE, hangi Stokta kalan kutular minimumdan az değil. Örneğin, mal içeren kutuların sayısı şeftaliler (hücre D 2 ), Depoda kalan kutular\u003e \u003d 6 (hücre E 2 ) ... 64 sonucunu almalıyız. Sayma birçok formülle uygulanabilir, birkaç tane vereceğiz (örnek dosyaya bakın Sayfa Metni ve Numarası):

1. =SUMIFN (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

İşlev sözdizimi: SUMIF (toplam_aralık; koşul_aralığı1; koşul1; koşul_aralığı2; koşul2 ...)

B2: B13 Sum_Interval - adlar, diziler veya sayı içeren başvurular dahil olmak üzere toplama için hücreler. Boş değerler ve metin göz ardı edilir.
A2: A13ve B2: B13 koşul_aralığı1; koşul_aralığı2; ... karşılık gelen koşulun kontrol edildiği 1 ila 127 aralığı temsil eder.
D2ve "\u003e \u003d" & E2 Koşulu1; koşul2; ... sayı, ifade, hücre referansı veya hangi hücrelerin toplanacağını belirleyen metin biçimindeki 1 ila 127 koşuldur.

Bağımsız değişkenlerin sırası SUMIF () ve SUMIF () işlevlerinde farklıdır. ETOPLA () bağımsız değişkeni summation_interval ilk bağımsız değişkendir ve SUMIF () içinde üçüncü bağımsız değişkendir. Bu benzer işlevleri kopyalarken ve düzenlerken, bağımsız değişkenlerin doğru sırada olduğundan emin olun.

2. diğer seçenek \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
SUMPRODUCT () işlevini kullanmaya daha yakından bakalım:

A2: A13 \u003d D2'nin değerlendirilmesinin sonucu bir dizidir (FALSE: FALSE: FALSE: FALSE: TRUE: TRUE: TRUE: FALSE: FALSE: FALSE: FALSE: FALSE) VE kriter, yani kelime şeftaliler... Dizi, A2: A13 \u003d D2'de vurgulanarak ve ardından;
B2: B13'ü hesaplamanın sonucu (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), yani sadece bir sütundaki değerler B ;
Dizilerin (A2: A13 \u003d D2) * (B2: B13) eleman bazında çarpılmasının sonucu (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). Bir sayının FALSE ile çarpılması 0 ile sonuçlanır; ve DOĞRU (\u003d 1) değeri için sayının kendisi elde edilir;
İkinci koşulu inceleyelim: Hesaplamanın sonucu - (B2: B13\u003e \u003d E2) bir dizidir (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Sütundaki değerler " Stoktaki kutu sayısı»\u003e \u003d E2 (yani\u003e \u003d 6) kriterini karşılayanlar 1'e karşılık gelir;
Ayrıca, SUMPRODUCT () işlevi, dizilerin öğelerini çiftler halinde çarpar ve elde edilen ürünleri toplar. Biz - 64 alırız.

3. SUMPRODUCT () işlevinin başka bir kullanımı formüldür \u003d TOPLA ÇARPIM ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Gerekirse, tarihler doğrudan formüle girilebilir \u003d SUMIFS (B6: B17; A6: A17; "\u003e \u003d 01/15/2010"; A6: A17; "<=06.03.2010")

Seçim ölçütlerini bir metin dizesinde görüntülemek için formülü kullanın \u003d "" & TEXT (D6; "gg.MM.yy") & "ile" & TEXT (E6; "gg.MM.yy") arasındaki dönem için satış hacmi

Son formülde kullanılmıştır.

Task4 (Ay)

Önceki sorunun durumunu biraz değiştiriyoruz: ayın toplam satışlarını bulun (örnek dosyaya bakın Sayfa Ayı).

Formüller, görev 3'e benzer şekilde oluşturulur, ancak kullanıcı 2 tarih değil, ayın adını girer (tablodaki verilerin 1 yıl içinde olduğu varsayılır).

3. görevi çözmek için, yapılandırılmış otomatik filtreli tablo aşağıdaki gibi görünür (örnek dosyaya bakın) Sayfa 2 Tarihler).

Önceden, tablonun Toplamlar satırına dönüştürülmesi ve bu satırı içermesi gerekir.

İyi günler, sevgili abonelerimiz ve blog sitesinin ziyaretçileri. Son zamanlarda, DÜŞEYARA formülünü bulduk ve hemen ardından çok kullanışlı başka bir Excel işlevi hakkında yazmaya karar verdim - SUMIF... Bu işlevlerin her ikisi de farklı kaynaklardan (tablolar) gelen verileri anahtar alana göre "bağlayabilir" ve bazı koşullar altında birbirinin yerine kullanılabilir. Aynı zamanda amaç ve kullanımlarında da önemli farklılıklar vardır.

DÜŞEYARA işlevinin amacı verileri Excel'de bir yerden diğerine "çekmek" ise, SUMIF sayısal verileri belirli bir kritere göre toplamak için kullanılır. Bu formülün temel amacı, verileri bazı koşullara göre özetlemektir. Bunun gibi bir şey yardımda yazılmıştır. Ancak bu araç başka amaçlar için de çalışacaktır. Örneğin, bir şey bulamadığında #YOK hataları oluşturmadığından, DÜŞEYARA yerine SUMIF formülünü sıklıkla kullanıyorum. Ve bu, işi büyük ölçüde kolaylaştırır ve hızlandırır.

SUMIF işlevi, çok çeşitli sorunları çözmek için başarıyla uyarlanabilir. Bu nedenle bu yazımızda 1 (bir) değil 2 (iki) örnek ele alacağız. Birincisi, belirli bir kritere göre toplama ile ilişkilendirilir, ikincisi - veri "çeken", yani DÜŞEYARA alternatif olarak.

SUMIF İşlevini Kullanarak Toplama Örneği

Tablo, pozisyonları, miktarlarını ve belirli bir mal grubuna ait olanları (ilk sütun) içerir. Şimdilik, yalnızca belirli bir koşula karşılık gelen değerler olan bu konumlar için miktarı hesaplamamız gerektiğinde, SUMIF'in basitleştirilmiş bir kullanımını düşünün. Örneğin, kaç tane üst pozisyon satıldığını bilmek istiyoruz, yani değeri 70 birimi aşanlar. Bu tür malları gözlerinizle aramak ve ardından manuel olarak özetlemek çok kolay ve hızlı değildir, bu nedenle SUMIF işlevi burada çok uygundur.

Her şeyden önce, miktarın hesaplanacağı hücreyi seçin. Ardından, İşlev Sihirbazı adını veriyoruz. Bu ikon fx formül çubuğunda. Ardından, listede SUMIF işlevini arayın ve üzerine tıklayın. Belirli bir görevi çözmek için önerilen üç alandan yalnızca ikisini (ilk) doldurmanız gereken bir iletişim kutusu açılır.

Bu yüzden bu örneği basitleştirilmiş olarak adlandırdım. Neden 3 (üç) üzerinden 2 (iki)? Çünkü kriterimiz tam da toplama aralığı içindedir.

"Aralık" alanı, bir şey seçip eklemeniz gereken tüm orijinal değerlerin bulunduğu Excel tablosunun alanını gösterir. Genellikle fare ile ayarlanır.

"Ölçüt" alanı, formülün seçeceği koşulu gösterir. Bizim durumumuzda "\u003e 70" gösteririz. Alıntıları koymazsanız, kendiliğinden tamamlanacaktır. Ancak genel olarak böyle bir kriter tırnak içinde olmalıdır.

İlk alanda zaten belirtildiği için son "Sum_range" alanını doldurmuyoruz.

Bu nedenle, ETOPLA işlevi bir ölçüt alır ve belirtilen aralıktan belirtilen ölçütü karşılayan tüm değerleri seçmeye başlar. Bundan sonra, seçilen tüm değerler eklenir. Fonksiyon algoritması bu şekilde çalışır.

İşlev Sihirbazında gerekli alanları doldurduktan sonra, klavyedeki büyük "Enter" düğmesine basın veya Sihirbaz penceresinde "Tamam" düğmesine tıklayın. Bundan sonra, hesaplanan değer giriş işlevinin yerinde görünmelidir. Örneğimde 224 parça çıktı. Yani 70 adetten fazla satılan malların toplam değeri 224 adettir. (Bu, "ok" tıklanmadan önce Sihirbaz penceresinin sol alt köşesinde görülebilir). Bu kadar. Bu, kriter ve toplama aralığının aynı yerde olduğu basitleştirilmiş bir örnekti.

Şimdi, ölçütün toplama aralığı ile eşleşmediği bir örneğe bakalım. Bu durum çok daha yaygındır. Aynı koşullu verileri düşünün. Miktarı bir değerden fazla veya az değil, ancak belirli bir mal grubunun miktarını, örneğin G grubunu bilmemiz gerektiğini varsayalım.

Bunu yapmak için, tutarın gelecekteki hesaplamasını içeren hücreyi tekrar seçin ve İşlev Sihirbazını çağırın. Şimdi, ilk pencerede, toplama için seçimin yapılacağı kriteri içeren aralığı belirtiyoruz, yani bizim durumumuzda bu, ürün gruplarının adlarını içeren bir sütundur. Ayrıca, kriterin kendisi ya manuel olarak, uygun alanda "D grubu" girişini bırakarak veya sadece gerekli kriterin bulunduğu hücreyi fare ile belirterek belirlenir. Son kutu, özetlenen verilerin bulunduğu aralıktır. Bunu fare ile belirtiyoruz, ancak baskı severlerin elle yazmaları yasak değil.

Sonuç, D - 153 adet grubundan satılan mal miktarı olacaktır.

Bu nedenle, belirli bir kriter için bir miktarın nasıl hesaplanacağına baktık. Bununla birlikte, daha sık olarak, birkaç kriter için birkaç miktarın hesaplanması gerektiğinde sorun ortaya çıkar. Daha kolay olamazdı! Örneğin, her grup için satılan ürün miktarını bulmamız gerekiyor. Yani 4 (dört) grup (A, B, C ve D) için 4 (dört) değerle ilgileniyorum. Bunun için, genellikle Excel'in açık alanlarında bir yerde ayrı bir tablo şeklinde bir grup listesi yapılır. Grupların isimlerinin kaynak tablodaki grup isimleriyle tam olarak eşleşmesi gerektiği açıktır. Hemen toplamın hala sıfır olduğu son satırı ekleyelim.

Daha sonra, birinci grup için formül reçete edilir ve diğerlerinin tümüne yayılır. Burada sadece bağlantıların göreliliğine dikkat etmeniz gerekiyor. Ölçüt ve toplama aralığı olan aralık, mutlak bağlantılar olmalıdır, böylece formülü sürüklediğinizde bunlar "aşağıya inmez", ancak ölçütün kendisi, öncelikle fareyle belirtmeniz gerekir (ve manuel olarak yazmazsınız) ve ikinci olarak, göreceli bir bağlantı olmalıdır, böylece her toplamın kendi toplama kriteri vardır.

İşlev Sihirbazının böyle bir hesaplamayla doldurulmuş alanları şuna benzer.

Gördüğünüz gibi, ilk A grubu için satılan mal miktarı 161 adetti (şeklin sol alt köşesi). Şimdi enter tuşuna basın ve formülü aşağı sürükleyin.

Tüm tutarlar hesaplandı ve genel toplamları 535'tir, bu da orijinal verilerdeki toplamla çakışmaktadır. Bu, tüm değerlerin toplandığı, hiçbir şeyin gözden kaçırılmadığı anlamına gelir. SUMIF için son kullanım durumu şahsen bana oldukça sık geliyor.

Verileri karşılaştırmak için SUMIF işlevini kullanmaya bir örnek

Birden fazla kez belirttiğim gibi, SUMIF işlevi verileri bağlamak için mantıklı bir şekilde kullanılabilir. Aslında, bir değer eklerseniz, o değeri elde edersiniz. Bu aritmetik özelliği iyilik için kullanmak günah değildir. Excel tasarımcılarının beyin çocuklarının bu kadar barbarca kullanıldığından haberdar olmaması bile mümkündür.

Kısacası, SUMIF'in VLOOKUP'a alternatif olarak veri bağlamaya uyarlanması kolaydır. DÜŞEYARA varken neden SUMIF kullanmalı? Açıklamama izin ver. Birincisi, SUMIF, DÜŞEYARA'nın aksine, veri biçimine duyarsızdır ve hiç beklemediğiniz yerde bir hata oluşturmaz; ikinci olarak, belirli bir kritere göre değerlerin eksikliğinden kaynaklanan hatalar yerine ETOPLA 0 (sıfır) verir, bu da SUMIF formülüyle aralığın toplamlarını gereksiz hareketler olmadan hesaplamanıza olanak tanır. Bununla birlikte, bir dezavantaj var. İstenen tabloda herhangi bir kriter tekrarlanırsa, karşılık gelen değerler toplanacaktır ve bu her zaman bir "yukarı çekme" değildir. Korumanda olmak daha iyi. Öte yandan, bu genellikle gereklidir - değerleri belirli bir yere çekmek ve aynı anda yinelenen konumları eklemek. SUMIF işlevinin özelliklerini bilmeniz ve kullanım kılavuzuna göre kullanmanız yeterlidir.

Şimdi, ETOPLA işlevinin verileri çekmek için DÜŞEYARA'dan daha uygun olduğunu gösteren bir örneğe bakalım. Örneğinizdeki veriler Ocak ayındaki bazı ürünlerin satışı olsun. Şubat ayında nasıl değiştiklerini bilmek istiyoruz. Daha önce sağa başka bir sütun ekledikten ve onu Şubat ayı verileriyle doldurduktan sonra aynı plakada bir karşılaştırma yapmak uygundur. Başka bir Excel dosyasında, tüm ürün yelpazesi için Şubat ayı istatistikleri var, ancak tüm ürünler için satış istatistiklerini içeren büyük bir dosyadan gerekli değerleri tablomuza almamız gereken bu konumları tam olarak analiz etmek istiyoruz. İlk önce DÜŞEYARA formülünü kullanmayı deneyelim. Ürün kodunu kriter olarak kullanacağız. Sonuç şekildedir.

Açıkça görülüyor ki bir pozisyon sıkılmamış ve sayısal bir değer yerine #YOK hatası görüntüleniyor. Büyük olasılıkla, bu ürün Şubat ayında satışta değildi ve bu nedenle Şubat veritabanında mevcut değil. Sonuç olarak, # N / A hatası toplamda gösterilir. Çok fazla pozisyon yoksa, sorun büyük değildir, hatayı manuel olarak kaldırmak yeterlidir ve miktar doğru şekilde yeniden hesaplanacaktır. Bununla birlikte, satır sayısı yüzlerce olarak ölçülebilir ve manuel ayarlamalara güvenmek pek doğru bir karar değildir. Şimdi DÜŞEYARA yerine SUMIF formülünü kullanalım.

Sonuç aynıdır, yalnızca # N / A SUMIF hatası yerine sıfır verir, bu da normalde son satırdaki miktarı (veya başka bir göstergeyi, örneğin ortalama) hesaplamanıza izin verir. Bu, neden bazen DÜŞEYARA yerine ETOPLA kullanılması gerektiğinin ana fikridir. Çok sayıda pozisyonla, etki daha da belirgin olacaktır.

Gerçekte, genellikle monitörün boyutundan kat kat daha büyük olan büyük miktarda verilerle çalışmak gerekir. Hataları tespit etmek ve izlemek çok zordur. Ancak sonraki hesaplamalarda tüm pervazlar ortaya çıkar. Makaledeki ekran görüntülerini kullanarak bu tür örnekleri parçalarına ayırmak uygun değildir, bu nedenle sizi SUMIF işlevi hakkında özel olarak kaydedilmiş bir video eğitimini izlemeye davet ediyorum.

İşte SUMIF ile ilgili başka bir ders