Bu yazıda, Excel'de aya göre son girişin nasıl alınacağını öğreneceğiz.
Senaryo:
Veri kümeleriyle çalışırken bazen farklı kriterlere göre bazı sonuçlar çıkarmamız gerekir. Burada problem kriteri, değerin belirli bir aya karşılık gelen son giriş olması gerektiğidir. Problem iki şekilde yorumlanabilir.
- Aylara göre sütuna son giriş
- Sütundaki ayın son tarih değeri
İkisi arasındaki farkın ne olduğunu düşünüyor olmalısınız. Aradaki fark, ilk sorunun sütundan ay adıyla eşleşen son girişi, ikinci sorunun ise ay sonu tarih değerinden en yakın tarih değerini çıkarmasıdır. Her iki durumu da tek tek anlayacağız. İlk olarak, sütundaki son girişin aya göre nasıl alınacağını ele alacağız.
Excel'de aya göre sütundaki Son giriş nasıl alınır?
Bunun için METİN işlevini ve ARA işlevini kullanacağız. Burada, son değer için ARA işlevi aramasının bir özniteliğini kullanıyoruz. ARA işlevi 3 bağımsız değişken arama değeri, arama dizisi ve sonuç dizisi alır. Bu yüzden arama dizisi argümanını 1/arama dizisi olarak kullanıyoruz. Daha fazla ayrıntı için aşağıdaki formül sözdizimine bakın:
formül sözdizimi:
| =ARA(2,1/(METİN(tarihler,"aayyy")=METİN(ay,"aayyy")),değerler) |
tarihler : verilerdeki tarihler dizisi
değerler : verilerde sonuç dizisi
ay : ay kriterleri
Örnek :
Bütün bunları anlamak kafa karıştırıcı olabilir. Bu formülün nasıl kullanılacağını bir örnekte anlayalım. Burada tarih ve Fiyat değerlerine sahip verilerimiz var. Bunun için Ocak 2019 tarihli son giriş olacak Ocak 2019 ayının en son girişine ihtiyacımız var. Burada tarih dizisi ve Fiyat dizisi için adlandırılmış aralıkları kullandık.
Formülü kullanın:
| =ARA(2,1/(METİN(tarihler,"aayyyy")=METİN(F9,"aayyyy")),Fiyat) |
Açıklama:
- METİN(tarihler,"aayyyy"), değer dizisini metin değerlerine dönüştürdükten sonra "aayyyy" biçiminde döndürür.
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- METİN(F9,"aayyyy"), "012019" olan arama tarih değerini dönüştürdükten sonra (F9 hücresindeki) tarih değerini "aayyyy" biçiminde döndürür ve bu değer yukarıda belirtilen diziyle eşleştirilir.
- 1 ile bölme yapmak, True değerleri dizisini 1'lere ve False değerleri #SAYI/0 hatasına dönüştürür. Böylece döndürülen diziyi şu şekilde alacağız.
{ 1; 1 ; 1 ; 1 ; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0!; #BÖL/0! }
- Şimdi ARA işlevi dizide bulunamayacak olan 2 değerini bulur. Böylece son 1 değerine karşılık gelen girişi döndürür.
Kullanılan adlandırılmış aralıklar
tarihler : C3:C15
Fiyat : D3:D15
Son Fiyat 31-01-2019'a karşılık gelen 78.48'dir. Ctrl + D tuşlarını kullanarak veya hücrenin sağ alt köşesinden aşağı doğru sürükleyerek formülü diğer hücrelere kopyalayın.
Gördüğünüz gibi formül gerekli tüm değerleri döndürür. Aranan ay değeri herhangi bir tarih girişiyle eşleşmezse işlev #YOK hatası verir. Yukarıdaki örnekte tarih girişleri sıralanmıştır. Yani ayın son girişi, ayın son tarih girişiyle eşleşir. Şimdi eğer tarih girişleri sıralı değilse, o zaman önce tarih değerlerini sıralayacağız ve yukarıdaki formülü kullanacağız.
Excel'de sütunda aya göre son tarih girişi nasıl alınır?
Bunun için DÜŞEYARA ve EOMONTH işlevini kullanacağız. EOMONTH işlevi, ayın tarih değerini alır ve gerekli ayın son tarihini döndürür. DÜŞEYARA işlevi, ayın son tarihini veya en yakın önceki tarihi bulur ve o değere karşılık gelen bir değer döndürür.
Formülü kullanın:
| = DÜŞEYARA ( EOMONTH (F3,0), tablo, 2) |
Açıklama :
- EOMONTH (F3,0) aynı ayın son tarihini döndürür. Aynı aydaki tarihi döndürmek için kullanılan 0 argümanı.
- Artık arama değeri, verilen ayın son tarihi olur.
- Şimdi değer, tablonun ilk sütununda aranacak ve son tarihi arayacaktır, bulunursa değere karşılık gelen değeri döndürür ve bulunamazsa, arama değerine en yakın son eşleşen sonucu döndürür ve karşılık gelen sonucu döndürür.
- İşlev, 3. bağımsız değişken 2 olduğu için tablonun 2. sütunundaki değeri döndürür.
Burada formül, sonuç olarak 78.48'i döndürür. Şimdi Ctrl + D kısayolunu kullanarak veya hücrenin sağ alt köşesinden aşağı sürükleyerek formülü diğer hücrelere kopyalayın.
Gördüğünüz gibi formül iyi çalışıyor. Sadece bir problem var. Nisan ayına denk gelen son tarihe baktığımızda, tabloda nisan tarihi girişi olmadığı için fonksiyon Mart ayına karşılık gelen değeri döndürür. Bu sonuçları yakaladığınızdan emin olun.
Formülün kullanımıyla ilgili tüm gözlem notları burada.
Notlar:
- Makalede belirtilen soruna karşılık gelen formülü kullanın.
- DÜŞEYARA ve ARA işlevi, arama değeri sayı olduğunda ve arama dizisinde bulunmadığında işlevlerdir, arama değerinden daha küçük olan en yakın sayıya karşılık gelen değeri döndürür.
- Excel, tarih değerlerini sayı olarak saklar.
- Yukarıda belirtilen iki işlev yalnızca bir değer döndürür.
- Tablodaki arama değerinin tam eşleşmesini elde etmek için DÜŞEYARA işlevindeki 4. bağımsız değişkeni 0 olarak kullanın.
Excel'de aya göre son girişin nasıl alınacağı hakkındaki bu makalenin açıklayıcı olmasını umuyorum. Buradaki formülleri kullanarak tablodan değer çıkarma hakkında daha fazla makale bulun. Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınızla 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 karışık veriler içeren son satırı bulun : aynı dizide sayılar, metin veya boş hücre ile çalışma. Excel'deki KAÇINCI işlevini kullanarak boş olmayan hücreli son satırı ayıklayın.
Belirli Bir Ayın Son Gününü Bulma : EOMONTH işlevi verilen tarih değerinin ayın son tarihini döndürür.
Sütundaki Son Değer Nasıl Alınır : Büyük bir verideki son giriş, HÜCRE ve INDEX işlevinin birleşimi, bir sütundaki veya veri listesindeki son değeri döndürür.
Son boş olmayan hücre ile fark : Excel'de EĞER ve ARA işlevini kullanarak sayılarla listedeki son değerden alternatif farkı alır.
Excel'de metin değerlerine sahip son veri satırını bulun : Metin değerleri ve boş hücreler dizisinde, excel'deki MATCH ve REPT işlevini kullanarak dizideki son değeri döndürün.
Excel'de SUMPRODUCT işlevi nasıl kullanılır?: Excel'de birden çok dizide karşılık gelen değerlerin çarpımını aldıktan sonra TOPLA döndürür.
Excel'de joker karakterler nasıl kullanılır : Excel'de joker karakterleri kullanarak ifadeleri eşleştiren hücrelerde arama, sayma, toplama, ortalama ve daha fazla matematiksel işlem.
Popüler Makaleler:
Excel'de EĞER İşlevi nasıl kullanılır? : Excel'deki EĞER ifadesi koşulu kontrol eder ve koşul DOĞRU ise belirli bir değer veya YANLIŞ ise başka bir belirli değer döndürür.
Excel'de DÜŞEYARA İşlevi nasıl kullanılır? : 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 İş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.