Bu yazımızda Excel'de TOPLA ve SUMIF işlevi yerine EĞER işlevinin nasıl kullanılacağını öğreneceğiz.
Senaryo:
Basit bir deyişle, uzun bir dağınık veri kümesiyle çalışırken, bazen üzerinde bazı kriterler bulunan sayıların toplamını bulmamız gerekir. Örneğin, belirli bir departmandaki maaşların toplamını bulmak veya tarih, adlar, departman üzerinden birden fazla kritere sahip olmak veya maaşlar değerin altında veya değerin üzerinde miktar gibi verileri bile numaralandırabilir. Bunun için genellikle SUMPRODUCT veya SUMIFS işlevini kullanırsınız. Ama inanamazsınız, Excel temel işlevi EĞER işleviyle aynı işlevi gerçekleştirirsiniz.
Sorun nasıl çözülür?
EĞER işlevini kullanarak tablo dizileri üzerinde mantıksal işlemler yapmak için bunun nasıl mümkün olduğunu düşünüyor olmalısınız. Excel'deki EĞER işlevi çok kullanışlıdır, Excel'deki veya diğer kodlama dillerindeki bazı zor görevlerin üstesinden gelmenizi sağlar. EĞER işlevi, gerekli değerlere karşılık gelen dizideki koşulları test eder ve sonucu 1 olarak Doğru koşullara ve 0 olarak False'a karşılık gelen dizi olarak döndürür.
Bu problem için aşağıdaki fonksiyonları kullanacağız:
- TOPLA işlevi
- EĞER işlevi
Bu yukarıdaki fonksiyonlara ve bazı temel veri operasyonlarına ihtiyaç duyacağız. dizilerdeki mantıksal koşullar, mantıksal operatörler kullanılarak uygulanabilir. Bu mantık operatörleri hem metin hem de sayılar üzerinde çalışır. Aşağıda genel formül bulunmaktadır. { } küme parantezleri, EĞER işleviyle dizi formülleri gerçekleştirmek için sihirli araçtır.
Genel formül:
| { = TOPLA ( EĞER ( ((mantıksal_1) * (mantıksal_2) *… * (mantıksal_n) , toplam_dizi ) ) } |
Not: Kıvrımlı parantezler için ( { } ) Kullanmak Ctrl + Üst Karakter + Enter Excel'de diziler veya aralıklarla çalışırken. Bu, varsayılan olarak formülde Kıvrımlı Parantezler oluşturacaktır. Kıvrımlı parantez karakterlerini sabit kodlamaya ÇALIŞMAYIN.
Mantıksal 1: dizi 1'de koşul 1'i test eder
Mantıksal 2: 2. koşulda 2. koşulu test eder ve bu şekilde devam eder
sum_array : dizi, işlem toplamı gerçekleştirilir
Örnek :
Bütün bunları anlamak kafa karıştırıcı olabilir. Şimdi bu formülü aşağıda gösterilen örnek üzerinde çalıştırarak test edelim. Burada, ilgili kategori alanları ve miktarları ile birlikte farklı şehirlere teslim edilen ürünlerin verilerine sahibiz. Burada veriler var ve miktarın 40'tan büyük olduğu Boston'a gönderilen çerezlerin miktarını bulmamız gerekiyor.
Veri tablosu ve kriterler tablosu yukarıdaki resimde gösterilmiştir. Anlamak amacıyla, kullanılan diziler için adlandırılmış aralıklar kullandık. Adlandırılmış aralıklar aşağıda listelenmiştir.
Buraya :
A2:A17 dizisi için tanımlı şehir.
B2:A17 dizisi için tanımlanan kategori.
C2:C17 dizisi için tanımlanan miktar.
Artık aşağıdaki formülü kullanarak istediğiniz sonucu elde etmeye hazırsınız.
Formülü kullanın:
| { = TOPLA ( IF ( ( (City="Boston") * (Kategori="Çerezler") * (Miktar>40) , Miktar)) } |
Açıklama :
- City ="Boston" : Şehir aralığındaki değerleri "Boston" ile eşleştirmek için kontrol eder.
- Kategori="Çerezler" : Kategori aralığındaki değerleri "Çerezler" ile eşleşecek şekilde kontrol eder.
- Miktar > 40 : Miktar aralığındaki değerleri kontrol eder.
- Miktar, toplamın gerekli olduğu dizi olabilir.
- EĞER işlevi tüm kriterleri kontrol eder ve yıldız işareti (*) tüm dizi sonuçlarını çarpar.
= SUM ( IF ( { 0; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 } , { 33 ; 87 ; 58 ; 38 ; 54 ; 51 ; 28 ; 36 ; 28 ; 44 ; 23 ; 27 ; 43 ; 42 ; 33 ; 30 } ) )
- Şimdi EĞER işlevi yalnızca 1'lere karşılık gelen miktarları döndürür ve geri kalanlar yoksayılır.
- SUM işlevi SUM değerini döndürür.
Şimdi 1'lere karşılık gelen miktar sadece sonucu almak için toplanır.
Gördüğünüz gibi, 43 miktarı iade edildi ancak "Boston" a teslim edilen 38, 36 ve 43 numaralı üç çerez siparişi var. Miktarın 40'ın üzerinde olduğu bir miktar toplamına ihtiyacımız vardı. Yani formül sadece 43'ü döndürür. Şimdi Şehir için SUM Miktarını almak için diğer kriterleri kullanın: "Los Angeles" & Kategori: "Barlar" & Miktar 50'den az olmalıdır.
formülü kullan
| { = TOPLA ( EĞER ( ( Şehir = "Los Angeles") * (Kategori="Barlar") * (Miktar < 50), Miktar ) } |
Gördüğünüz gibi formül sonuç olarak 86 değerlerini döndürüyor. Bu, 44 ve 42 miktarına sahip koşulları sağlayan 2 siparişin toplamıdır. Bu makale, bir dizi formülünde iç içe EĞER formülünün tek bir EĞER ile nasıl değiştirileceğini gösterir. Bu, karmaşık formüllerdeki karmaşıklığı azaltmak için kullanılabilir. Ancak, bu özel sorun, SUMIF veya SUMPRODUCT işleviyle kolayca çözülebilir.
SUMPRODUCT işlevinin kullanımı:
SUMPRODUCT işlevi, dizideki karşılık gelen değerlerin toplamını döndürür. Böylece dizilerin 1s a True deyimi değerlerini ve 0s'ı False deyimi değerlerine döndürmesini sağlayacağız. Böylece, son toplam, tüm ifadelerin Doğru olduğu yere karşılık gelecektir.
Formülü kullanın:
| = SUMPRODUCT ( -- (Şehir = "Boston") , -- (Kategori = "Çerezler") , -- (Miktar > 40) , Miktar ) |
-- : tüm TRUE'ları 1'lere ve False'ı 0'a dönüştürmek için kullanılan işlem.
SUMPRODUCT işlevi, yukarıda açıklanan SUM ve IF işlevi tarafından döndürülen miktarın SUM'unu yeniden kontrol eder.
Benzer şekilde ikinci örnek için de sonuç aynıdır.
Gördüğünüz gibi SUMPRODUCT işlevi aynı görevi gerçekleştirebilir.
Formülün kullanımıyla ilgili tüm gözlem notları burada.
Notlar:
- Formüldeki toplam_dizi yalnızca sayılarla çalışır.
- Formül #DEĞER hatası veriyorsa, makaledeki örneklerde gösterildiği gibi formülde kaşlı ayraçların olup olmadığını kontrol edin.
- Olumsuzlama (--) karakteri değerleri, DOĞRU'ları veya 1'leri YANLIŞ veya 0'ları ve YANLIŞ'ları veya 0'ları DOĞRU'lara veya 1'lere değiştirir.
- Şuna eşittir: ( = ), daha az eşittir ( <= ), büyük ( > ) veya eşit değil ( ) uygulanan bir formül içinde yalnızca sayılarla gerçekleştirilebilir.
Excel'de SUMPRODUCT ve SUMIFS işlevi yerine EĞER işlevinin nasıl kullanılacağı hakkındaki bu makalenin açıklayıcı olmasını umuyoruz. Toplama formülleri hakkında daha fazla makaleyi burada bulabilirsiniz. Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınız ile paylaşın. Ayrıca bizi Twitter ve Facebook'ta da takip edebilirsiniz. Sizden haber almayı çok isteriz, işimizi nasıl iyileştirebileceğimizi, tamamlayabileceğimizi veya yenileyebileceğimizi ve sizin için daha iyi hale getirebileceğimizi bize bildirin. Bize e-posta sitesinde yazın
Excel'de SUMPRODUCT işlevi nasıl kullanılır?: Excel'de birden çok dizideki değerlerin çarpılmasından sonra TOPLAM değerini döndürür.
Tarih arasında ise SUM : Excel'de verilen tarihler veya dönem arasındaki değerlerin TOPLAMINI döndürür.
Tarih verilen tarihten büyükse toplayın: Excel'de belirtilen tarih veya dönemden sonraki değerlerin TOPLAMINI döndürür.
Excel'de Aya Göre Toplamanın 2 Yolu: Excel'de belirli bir ay içindeki değerlerin TOPLAMINI döndürür.
Koşullu Birden Çok Sütun Nasıl Toplanır: Excel'de koşulu olan birden çok sütundaki değerlerin TOPLAMINI döndürür.
Popüler Makaleler :
Verimliliğinizi Arttıracak 50 Excel Kısayolu : Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
nasıl kullanılırExcel'de DÜŞEYARA İşlevi : Bu, farklı aralık ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de COUNTIF işlevi nasıl kullanılır? : Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri 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.