Genel formül
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Özet
Aylık ortalamaya göre, EOMONTH işlevinin yardımıyla ÇOKEĞERORTALAMA işlevine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, F4'teki formül şöyledir:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Bu formül "miktarlar" (D5: D104) ve "tarihler" (C5: C104) olarak adlandırılan aralıkları kullanır.
Açıklama
ÇOKEĞERORTALAMA işlevi, aralıkları birden çok ölçüte göre ortalayabilir. Bu durumda, ÇOKEĞERORTALAMA'yı iki kriter kullanarak aylık ortalama tutarlara yapılandırırız: (1) ayın ilk gününden büyük veya ona eşit tarihler, (2) ayın son gününden küçük veya ona eşit tarihler. DATE işlevini kullanarak formüle Ocak 2016 için sabit kodlar ekleseydik, şöyle görünürdü.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ancak tarihleri sabit kodlamak istemiyoruz, Excel'in bizim için bu tarihleri oluşturmasını istiyoruz. Normalde bu bir sıkıntıdır, çünkü F sütununa metin olarak ay adlarını eklerseniz (örneğin, "Ocak", "Şubat", "Mart", vb.), Kriterler için kullanabileceğiniz tarihler oluşturmak için ekstra sorunlara gitmeniz gerekir. .
Bununla birlikte, bu durumda, işleri kolaylaştırmak için basit bir numara kullanırız: F sütununda, ay adlarını yazmak yerine, her ayın ilk günü için gerçek tarihleri ekleriz (1/1/2016, 2/1/2016, 3 / 1/2016, vb.) Ve ay adlarını görüntülemek için özel bir tarih biçimi ("aaa") kullanın.
Bu, AVERAGEIFS için ihtiyaç duyduğumuz kriterleri oluşturmayı kolaylaştırır. Ayın ilkinden büyük veya ona eşit tarihleri eşleştirmek için şunu kullanırız:
">="&E4
Ve ayın son gününe eşit veya daha küçük tarihleri eşleştirmek için şunu kullanırız:
"<="&EOMONTH(E4,0)
EOMONTH, aylar bağımsız değişkeni için sıfır sağladığımız için aynı ayın son gününü otomatik olarak döndürür.
Not: Hücre referansına dayalı ölçütler oluştururken bir ve işareti (&) ile birleştirme gereklidir.
Pivot Tablo çözümü
Pivot tablo, verileri yıla, aya, çeyreğe vb. Göre özetlemeniz gerektiğinde mükemmel bir çözümdür, çünkü pivot tablolar tarihe göre otomatik gruplama için kontroller sağlar. Formüllerle pivot tabloların yan yana karşılaştırması için şu videoyu izleyin: Neden pivot tablolar.
