Excel'de tek tek hücreler nasıl özetlenir. Excel. Koşullu biçimlendirme ölçütlerini karşılayan hücreleri sayın ve toplayın
Genellikle bir elektronik tablodaki hücrelerde her saniye, üçüncü, dördüncü ve benzerlerini toplamanız gerekir. Şimdi, bir sonraki numara sayesinde yapılabilir.
Excel, her birini toplayabilen standart bir işlev sağlamaz n'inci hücre veya dize. Ancak, bu görevi birkaç farklı yollar... Tüm bu yaklaşımlar ROW ve MOD işlevlerine dayanmaktadır.
SATIR işlevi Verilen hücre başvurusunun satır numarasını döndürür: Excel ROW'un Rusça sürümünde SATIR (başvuru) (başvuru).
OSTAT işlevi (MOD) Excel OSTAT'ın Rusça sürümünde (sayı; bölen) bir sayıyı bölen: MOD (sayı; bölen) bölmenin kalanını döndürür.
SATIR işlevini MOD işlevine koyun (sayısal bir bağımsız değişken geçirmek için), 2'ye bölün (her diğer hücreyi toplamak için) ve sonucun sıfır olup olmadığını kontrol edin. Öyleyse, hücre eklenir. Bu işlevler çok çeşitli şekillerde kullanılabilir - bazıları diğerlerinden daha iyi sonuçlar verecektir. Örneğin, $ A $ 1: $ A $ 100 aralığındaki her ikinci hücrenin toplanması için bir dizi formülü şöyle görünebilir: \u003d SUM (IF (MOD (SATIR ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), Excel'in Rusça sürümünde \u003d TOPLA (EĞER (OSTAT (SATIR ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A 1 $: A $ 500; 0)).
Bu bir dizi formülü olduğundan, Ctrl + Shift + Enter tuşlarına basarak girmelisiniz, Excel şuna benzemesi için kaşlı ayraçlar ekleyecektir: (\u003d SUM (IF (MOD (SATIR ($ A $ 1: $ A $ 500 ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), Excel'in Rusça sürümünde: (\u003d TOPLA (EĞER (Kalan (SATIR ($ A $ 1: $ A $ 500)); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Excel'e kendi başına kaşlı ayraç eklemek için ihtiyacınız var; onları kendiniz eklerseniz formül çalışmayacaktır.
Hedefe ulaşılsa da bu yöntem tasarımı olumsuz etkilemektedir. hesap tablosu... Bu, bir dizi formülünün gereksiz bir uygulamasıdır. Sorunları daha da kötüleştirmek için, bu uzun formül, büyük bir formülü yeniden hesaplanabilir bir işleve dönüştüren yeniden hesaplanabilir bir SATIR işlevi içerir. Bu, çalışma kitabında ne yaparsanız yapın, sürekli olarak yeniden hesaplanacağı anlamına gelir. Bu çok kötü bir yol!
İşte birazcık olan başka bir formül en iyi seçim: \u003d SUMPRODUCT ((MOD (SIRA ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), Excel'in Rusça sürümünde \u003d SUMPRODUCT ((GERİ (SATIR ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Ancak bu formülün # DEĞER! (# DEĞER!) Aralıktaki herhangi bir hücre sayı yerine metin içeriyorsa. Bu formül, aslında bir dizi formülü olmasa da, aynı zamanda yavaşlar excel çalışmasıçok fazla kullanırsanız veya her seferinde geniş bir aralığa atıfta bulunursa.
Neyse ki var en iyi yol, bu sadece daha verimli değil, aynı zamanda çok daha esnek. DSUM işlevinin kullanılmasını gerektirir. Bu örnekte, her n'inci hücrenin eklenmesi gereken aralık olarak A1: A500 aralığını kullandık.
E1 hücresine Ölçüt sözcüğünü girin. E2 hücresine aşağıdaki formülü girin: \u003d MOD (SATIR (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, Excel'in Rusça sürümünde \u003d OSTAT (SATIR (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. C2 hücresini seçin ve Veri → Doğrulama komutunu seçin.
İzin Ver alanında Liste'yi seçin ve Kaynak alanına 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 girin. Onay kutusunun işaretlendiğinden emin olun. (Hücre İçi) ve Tamam düğmesini tıklayın. C1 hücresine, SUM every… metnini girin. Satır 1 dışındaki herhangi bir hücrede aşağıdaki formülü girin: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), Excel'in Rusça sürümünde \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
DSUM işlevini girdiğiniz hücrenin hemen yukarısındaki hücreye, \u003d "Her Birini Topla" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Hücre". Şimdi geriye kalan tek şey C2 hücresindeki istenen sayıyı seçmektir ve gerisini DSUM işlevi halleder.
BDSUM (DSUM) işlevini kullanarak, hücreleri belirttiğiniz aralıkta toplayabilirsiniz. DSUM işlevi, bir dizi formülünden veya SUMPRODUCT işlevinden çok daha etkilidir. Kurmak biraz daha uzun sürse de, öğrenmenin zor olduğu, savaşmanın kolay olduğu durum budur.
Daha önce bir. Maalesef, hücreler renkliyse bu işlev çalışmaz. koşullu biçimlendirme... İşlevi "iyileştireceğime" söz verdim. Ancak bu notun yayınlanmasından bu yana geçen iki yıl içinde, ne kendi başıma ne de internetteki bilgileri kullanarak sindirilebilir bir kod yazamadım ... ( 29 Mart 2017 itibarıyla güncelleme Beş yıl sonra, hala kodu yazmayı başardım; notun son kısmına bakın). Ve kısa bir süre önce D. Hawley, R. Hawley "Excel 2007. Püf Noktaları" kitabında yer alan ve kod olmadan yapmanıza izin veren bir fikirle karşılaştım.
A1: A100 aralığında bulunan 1'den 100'e kadar sayıların bir listesi olsun (Şekil 1; Excel dosyasının "SUMIF" sayfasına da bakın). Aralık, 10'dan büyük ve 20'ye eşit veya daha küçük sayılar içeren hücreleri işaretlemek için koşullu olarak biçimlendirilmiştir.
Şekil: 1. Sayı aralığı; koşullu biçimlendirme, 10 ile 20 arasında değerler içeren vurgulanmış hücreler
Notu formatta, formatta örnekler indirin
Şimdi, az önce belirlenen kriterleri karşılayan hücrelere değerler eklemeniz gerekir. Bu hücrelere ne tür biçimlendirmenin uygulandığı önemli değildir, ancak hücrelerin vurgulandığı kriterleri bilmeniz gerekir.
Eşleşen bir hücre aralığı eklemek için bir ölçüt, ETOPLA işlevini kullanabilirsiniz (Şekil 2).
Şekil: 2. Bir koşulu karşılayan hücrelerin toplamı
Eğer varsa birkaç SUMIFS işlevini kullanabilirsiniz (Şekil 3).
Şekil: 3. Birkaç koşulu karşılayan hücreleri toplamak
Bir kriteri karşılayan hücrelerin sayısını saymak için EĞERSAY işlevini kullanabilirsiniz.
Birden çok ölçütü karşılayan hücrelerin sayısını saymak için EĞERSAY işlevini kullanabilirsiniz.
Excel, birden çok koşul belirlemenize izin veren başka bir işlev sağlar. Bu işlev, bazların işlevlerine dahildir excel verileri ve BDSUMM olarak adlandırılır. Kontrol etmek için A2: A100 aralığındaki aynı sayı kümesini kullanın (Şekil 4; ayrıca Excel dosyasının "BDSUMM" sayfasına bakın).
Şekil: 4. Veritabanı işlevlerini kullanma
C1: D2 hücrelerini seçin ve formül çubuğunun solundaki ad kutusuna yazarak bu aralığı Kriter olarak adlandırın. Şimdi C1 hücresini seçin ve \u003d $ A $ 1 girin; bu, veritabanının adını içeren sayfadaki ilk hücreye bir referansdır. D1 hücresine \u003d $ A $ 1 girin ve sütun başlığı A'nın iki kopyasını alacaksınız. Bu kopyalar, Ölçüt olarak adlandırdığınız BDSUMM (C1: D2) koşulları için başlık olarak kullanılacaktır. C2 hücresine\u003e 10 girin. D2 hücresine girin<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ 101,1 $, Kriter)
Birden çok ölçütü karşılayan hücre sayısını saymak için COUNT işlevini kullanabilirsiniz.
John Walkenbach'ın kitabını okurken, Excel 2010'dan başlayarak VBA'nın yeni bir DisplayFormat özelliğine sahip olduğunu öğrendim (örneğin, Range.DisplayFormat Özelliğine bakın). Yani VBA, ekranda görüntülenen formatı okuyabilir. Doğrudan kullanıcı ayarları tarafından veya koşullu biçimlendirme kullanılarak nasıl alındığı önemli değildir. Ne yazık ki MS, DisplayFormat özelliğinin yalnızca VBA'dan çağrılan prosedürlerde çalışmasını ve bu özelliğe dayalı UDF'lerin # DEĞER! Ancak, bir yordamı (bir makro, ancak bir işlev değil) kullanarak belirli bir renkteki hücrelere göre bir aralıktaki değerlerin toplamını elde edebilirsiniz. Aç (VBA kodunu içerir). Menüden geç Görünüm -> Makrolar -> Makrolar; pencerede Makro, çizgiyi vurgulayın SumColorUsl, ve bas Yürüt... Makroyu çalıştırın, toplama aralığını ve kriteri seçin. Cevap pencerede görünecektir.
Prosedür kodu
Sub SumColorConv () Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range SumColor \u003d 0 "Aralık sorgusu Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Toplama aralığını seçin ", _ Başlık: \u003d "Aralık Seçimi", _ Varsayılan: \u003d ActiveCell.Address, _ Type: \u003d 8) "Criterion Request Set CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Select toplama kriteri", _ Başlık: \u003d" Ölçüt seçimi ", _ Varsayılan: \u003d ActiveCell.Address, _ Tür: \u003d 8)" i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat ise UserRange'deki Her i için "doğru" hücreleri toplama . Interior.Color Sonra SumColor \u003d SumColor + i Sonraki MsgBox SumColor End Sub
Alt SumColorUl () Uygulama. Uçucu Doğru Dim SumColor As Double Dim i As Range Kullanıcı Aralığını Aralık Olarak Karart Dim Ölçütü Aralık Olarak Aralık SumColor \u003d 0 "Aralık sorgusu UserRange \u003d Application.InputBox (_ Bilgi istemi: \u003d "Toplama aralığını seçin", _ Başlık: \u003d "Aralık seçimi", _ Varsayılan: \u003d ActiveCell.Address, _ Tür: \u003d 8) "Proscriptor CriterionRange \u003d Uygulama olarak ayarlayın. InputBox (_ İstem: \u003d "Bir toplama kriteri seçin", _ Başlık: \u003d "Ölçüt seçimi", _ Varsayılan: \u003d ActiveCell. Adres, _ |
Bunun gibi bir satış raporunuz olduğunu varsayalım:
Ondan ne kadar bulmalısın kalemler satış temsilcisi tarafından satılan Ivanov içinde ocak.
SORUN: Veriler birkaç kritere göre nasıl özetlenir ??
KARAR: Yöntem 1:
BDSUM (A1: G16; F1; I1: K2)
İngilizce versiyonda:
DSUM (A1: G16, F1, I1: K2)
NASIL ÇALIŞIR:
Belirttiğimiz veritabanından A1: G16 işlevi BDSUMM sütun verilerini alır ve özetler numara (argüman " Alan" = F1) verilen hücrelerde göre I1: K2 (Satıcı \u003d Ivanov; Ürünler \u003d Kalemler; Ay \u003d Ocak) kriterler.
EKSİLERİ: Kriter listesi sayfada olmalıdır.
NOTLAR: Toplama kriterlerinin sayısı RAM ile sınırlıdır.
UYGULAMA ALANI: Herhangi bir Excel sürümü
Yöntem 2:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
İngilizce versiyonda:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
NASIL ÇALIŞIR:
SUMPRODUCT işlevi, Excel belleğinde seçilen ölçütlere göre DOĞRU ve YANLIŞ değer dizilerini oluşturur.
Hesaplamalar sayfanın hücrelerinde yapıldıysa (netlik için, formülün tüm çalışmasını, hesaplamalar bellekte değil, sayfada yapılıyormuş gibi göstereceğim), o zaman diziler şöyle görünecektir:
Açıktır ki, örneğin, D2 \u003d Kalemler, değer DOĞRU olur ve eğer D3 \u003d Klasörler, sonra YANLIŞ (örneğimizde bir ürün seçme kriteri değer olduğu için Kalemler).
DOĞRU'nun her zaman 1'e eşit olduğunu ve YANLIŞ'ın her zaman 0'a eşit olduğunu bilerek, 0 ve 1 sayılarında olduğu gibi dizilerle çalışmaya devam ediyoruz.
Dizilerin elde edilen değerlerini kendi aralarında sırayla çarparak, BİR dizi sıfır ve bir elde ederiz. Üç seçim kriterinin de karşılandığı durumlarda, ( IVANOV, KALEMLER, OCAK) yani tüm koşullar DOĞRU değerlerini aldı, 1 (1 * 1 * 1 \u003d 1) alıyoruz, en az bir koşul karşılanmazsa 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Şimdi geriye kalan sadece sonuç olarak toplamamız gereken verileri içeren bir dizi ile elde edilen diziyi çarpmaktır F2: F16) ve aslında, 0 ile çarpılmayanları toplayın.
Şimdi formül kullanılarak ve sayfadaki adım adım hesaplama sırasında elde edilen dizileri karşılaştırın (kırmızıyla vurgulanmıştır).
Sanırım her şey açık :)
EKSİ: SUMPRODUCT - "ağır" dizi formülü. Büyük veri aralıklarında hesaplama yapılırken, yeniden hesaplama süresi gözle görülür şekilde artar.
NOTLAR
UYGULAMA ALANI: Herhangi bir Excel sürümü
Yöntem 3: Dizi Formülü
TOPLA (EĞER ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
İngilizce versiyonda:
TOPLA (EĞER ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
NASIL ÇALIŞIR: Yöntem 2 ile aynı şekilde. Yalnızca iki fark vardır - bu formül tuşuna basılarak girilir Ctrl + Üst Karakter + Entersadece basmak yerine Giriş ve 0'ıncı ve 1-q dizisi, toplama aralığı ile çarpılmaz, ancak IF işlevi kullanılarak seçilir.
EKSİ: Büyük veri aralıklarını hesaplarken dizi formülleri, yeniden hesaplama süresini önemli ölçüde artırır.
NOTLAR: İşlenen dizi sayısı 255 ile sınırlıdır.
UYGULAMA ALANI: Herhangi bir Excel sürümü
Yöntem 4:
SUMIFN (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)