Excel'de yıl bazında değerleri toplama
Diyelim ki, bir dizi tarih ve satış miktarı içeren bir veri aralığınız var ve aşağıdaki ekran görüntüsünde gösterildiği gibi belirli yıllara göre toplam tutarı almak istiyorsunuz. Excel'de normal SUMIFS ve SUMPRODUCT fonksiyonları bu görevi hızlı ve kolay bir şekilde çözmek için yardımcı olabilir. Bu öğretici, bu iki fonksiyonun nasıl kullanılacağını ayrıntılı olarak gösterecektir.
- Excel'de SUMIFS ve DATE fonksiyonlarıyla yıl bazında değerleri toplama
- Excel'de SUMPRODUCT fonksiyonu ile yıl bazında değerleri toplama
Excel'de SUMIFS ve DATE fonksiyonlarıyla yıl bazında değerleri toplama
Belirli bir yıla göre toplam değeri toplamak için, DATE fonksiyonunu kullanarak yılın ilk ve son tarihini belirtmelisiniz ve ardından SUMIFS fonksiyonunu kullanarak belirtilen tarihler arasındaki tüm değerleri toplarsınız. Genel sözdizimi şu şekildedir:
- toplam_aralığı: Toplanacak değerleri içeren aralık;
- tarih_aralığı: Belirli bir yıl için test edilecek tarih aralığı;
- yıl: Değerleri toplamak istediğiniz belirli yıl. (yıl, 1,1 yılın ilk günü anlamına gelir ve yıl,12,31 yılın son günü anlamına gelir.)
Bu görevi Excel'de çözmek için lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ardından formülü aşağı doğru sürükleyerek diğer hücrelerde sonucu alın:
Bu formülün açıklaması:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- $C$2:$C$12: Toplamak istediğimiz değerleri içeren “toplam_aralığı”.
- $B$2:$B$12,">="&DATE(E2,1,1): B2:B12 aralığındaki tarihlerin E2 hücresindeki yılın ilk günü olan 01/01/2020'den büyük veya ona eşit olup olmadığını kontrol eder.
- $B$2:$B$12,"<="&DATE(E2,12,31): B2:B12 aralığındaki tarihlerin E2 hücresindeki yılın son günü olan 12/31/2020'den küçük veya ona eşit olup olmadığını kontrol eder.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): Kriterler karşılandığında, C2:C12 aralığındaki değerler bulunur ve toplanır.
Excel'de SUMPRODUCT fonksiyonu ile yıl bazında değerleri toplama
Burada, SUMPRODUCT fonksiyonu tarafından oluşturulan başka bir basit formül de size yardımcı olabilir. Genel sözdizimi şu şekildedir:
- tarih_aralığı: Belirli bir yıl için test edilecek tarih aralığı;
- yıl: Değerleri toplamak istediğiniz belirli yıl;
- toplam_aralığı: Toplanacak değerlerin aralığı.
Lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ardından formülü aşağı doğru sürükleyerek diğer hücrelerde sonucu alın:
Bu formülün açıklaması:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- YEAR($B$2:$B$12)=E2: YEAR fonksiyonu, B2:B12 aralığındaki her tarih hücresinden yıl numarasını döndürür. Bu ifade, E2'deki yılın B2:B12 aralığındaki yıla eşit olup olmadığını kontrol eder. Ve şu şekilde bir dizi elde edersiniz: {DOĞRU;YANLIŞ;DOĞRU;DOĞRU;YANLIŞ;DOĞRU;DOĞRU;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={DOĞRU;YANLIŞ;DOĞRU;DOĞRU;YANLIŞ;DOĞRU;DOĞRU;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ}*$C$2:$C$12: Bu ifadede, DOĞRUlar ve YANLIŞlar 1'ler ve 0'lara zorlanır ve bu dizi, C2:C12 aralığındaki her bir tutarla çarpılır ve şu şekilde yeni bir dizi elde edersiniz: {347;0;398;430;0;379;412;0;0;420;0}.
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): SUMPRODUCT fonksiyonu, bu dizideki tüm sayıları nihayetinde toplar ve sonucu 2386 olarak döndürür.
İlgili kullanılan fonksiyonlar:
- SUMIFS:
- Excel'deki SUMIFS fonksiyonu, birden fazla kritere göre hücre değerlerini toplamaya yardımcı olabilir.
- SUMPRODUCT:
- SUMPRODUCT fonksiyonu, iki veya daha fazla sütun veya diziyi birbiriyle çarpmak ve ardından ürünleri toplamak için kullanılabilir.
- DATE:
- DATE fonksiyonu, ayrı hücrelerden yıl, ay ve gün numaralarını birleştirerek geçerli bir tarih oluşturmanıza yardımcı olabilir.
- YEAR:
- YEAR fonksiyonu, verilen tarihe dayalı olarak 4 haneli seri numarası formatında yılı döndürür.
Daha fazla makale:
- Ay Bazında Değerleri Toplama (Yıl Dahil Veya Hariç)
- Bu öğretici, Excel'de belirli bir ay ve yıla göre değerleri toplamak veya belirli bir aya göre değerleri toplarken yılı göz ardı etmek için iki formülü ayrıntılı olarak gösterir.
- Verilen Bir Tarihin Haftasına Göre Değerleri Toplama
- Belirli bir tarihin düştüğü haftaya göre değerleri toplamak için SUMIFS fonksiyonuna dayalı bir formül uygulayabilirsiniz.
- Hafta Numarasına Veya Haftanın Günlerine Göre Değerleri Toplama
- Bu öğretici, Excel'de belirtilen hafta numarasına veya haftanın günlerine göre belirli bir aralıktaki değerleri toplamanıza yardımcı olmak için adım adım formüller gösterir.
En İyi Ofis Üretkenlik Araçları
Kutools for Excel - Kalabalıktan Sıyrılmaya Yardımcı Olur
Kutools for Excel, İhtiyacınız Olan Her Şeyin Tek Tıklama Uzağında Olduğundan Emin Olmak İçin 300'den Fazla Özelliğe Sahiptir...
Office Tab - Microsoft Office'de (Excel dahil) Sekmeli Okuma ve Düzenlemeyi Etkinleştir
- Bir saniyede, onlarca açık belge arasında geçiş yapın!
- Her gün yüzlerce fare tıklamasından kurtulun, fare eline veda edin.
- Birden fazla belgeyi görüntülediğinizde ve düzenlediğinizde üretkenliğinizi %50 artırır.
- Ofis'e (Excel dahil) Chrome, Edge ve Firefox gibi etkili sekmeler getirir.