Bir okuyucu bana bu hafta ilginç bir formül problemi gönderdi, bu yüzden bunu bir formül mücadelesi olarak paylaşacağımı düşündüm. Sorun şudur:
Sabit bir aylık ödemeniz, bir başlangıç tarihiniz ve belirli sayıda ayınız var. Aşağıdaki çalışma sayfasına göre, toplam ödemeleri yıllara göre toplamak için hangi formülü kullanabilirsiniz:
Başka bir deyişle, gösterilen her yıl için bir toplam almak için I5'e kopyalanan E5'te hangi formül çalışır?
Kendim de bir formül buldum, ama fikirlerinizi de görmek isterim. Eğer ilgileniyorsanız, önerdiğiniz formülle bir yorum bırakın.
Formülünüzde isterseniz aşağıdaki adlandırılmış aralıkları kullanabilirsiniz: mos (C5), amount (C6), start (C7), end (C8).
Aşağıdaki çalışma sayfasını indirebilirsiniz.
Cevap (genişletmek için tıklayın)Pek çok harika formül! Cevap vermek için zaman ayıran herkese teşekkürler. Aşağıda, sorunla ilgili düşüncelerim ve aşağıdaki çözümlerden bazıları var.
Not: Ay sınırlarının nasıl ele alınması gerektiğini asla açıklamadım. Örnek olarak başka bir çalışma sayfasını takip ediyordum. Önemli bilgiler, 2017'de 1 Mart'ta başlayan 30 ödeme, 2018'de 12 ödeme ve 2019'da 8 ödemedir (bakiye).
Öyleyse, böyle bir sorunu nasıl çözmeye çalışabileceğinizi anlamakta zorlanıyorsanız, önce ödemelere odaklanın. Bir yılda kaç ödeme olduğunu öğrendikten sonra, bu sayıyı tutarla çarpabilirsiniz ve bitirdiniz.
Peki, belirli bir yıldaki ödemelerin sayısını nasıl bulabilirsiniz? Aşağıdaki yorumlarda birçok iyi fikir bulacaksınız. Fark ettiğim birkaç model var ve birkaçını aşağıda listeledim. Bu devam eden bir çalışmadır…
Tasarım desenleri
IF + AND/OR + YEAR + MONTH
IF, pek çok formülde güvenilir bir beklemedir ve önerilen formüllerin çoğunda ilgi yılının başlangıç ve bitiş tarihlerinin "sınırları içinde" olup olmadığını anlamak için kullanılır. Çoğu durumda IF, formülleri kompakt tutmak için OR veya AND ile birleştirilir.
IFERROR + DATEDIF + MAX + MIN
DATEDIF, iki tarih arasındaki farkı ay cinsinden döndürebilir, bu nedenle buradaki fikir, her yıl için bir başlangıç tarihi ve bitiş tarihi hesaplamak için MAX ve MIN (kısalık için IF yerine) kullanmak ve DATEDIF aradaki ayları almasına izin vermektir. DATEDIF, başlangıç tarihi bitiş tarihinden küçük olmadığında #SAYI hatası atar, bu nedenle hatayı yakalamak ve sıfır döndürmek için EĞERHATA kullanılır. Aşağıdaki 闫 强, Arun ve David formüllerine bakın.
MAX + MIN + YEAR + MONTH
Robert ve Peter'ın formülleri neredeyse tüm işi MAX ve MIN ile yapıyor, görünürde IF yok. İnanılmaz. IF yerine MAX ve MIN kullanma fikri sizin için yeniyse, bu makale kavramı açıklamaktadır.
DAYS360
Excel GÜN360 işlevi, 360 günlük bir yılı temel alarak iki tarih arasındaki günlerin sayısını döndürür. Her ayın 30 günü olduğu fikrine dayanarak ayı hesaplamanın bir yolu.
SUM + DATE
Bu, DATE işlevini ve her ay için bir sayı içeren bir dizi sabitini kullanan verimsiz (ama zarif!) Yaklaşımım. TARİH işlevi, bir dizi sabiti kullanarak yılın her ayı için bir tarih döndürür ve çakışmayı kontrol etmek için boole mantığı kullanılır.
