Bu videoda, son kullanma tarihlerinin nasıl hesaplanacağına ve vurgulanacağına bakacağız.
Diyelim ki şirketiniz bir tür üyelik programı başlattı ve patronunuz size bir dizi veri gönderdi. Size son bir yıl içinde üyeliğini yenileyen 1000 kişinin listesini verdi ve birkaç şey arıyor.
Öncelikle, üyeliğin yenilendiği ayın son günü, ileride bir yıl sonra bir sona erme tarihi hesaplamanızı istiyor.
İkincisi, sürenin dolmasına kaç gün kaldığını görmek istiyor.
Üçüncüsü, zaten sona ermiş tüm üyelikler için "Süresi Doldu" ve sonraki 30 gün içinde sona eren tüm üyelikler için "Yakında sona eriyor" durumunu görmek istiyor.
Sonunda, süresi dolan üyelerin pembe ile vurgulanmasını ve yakında sona eren üyelerin sarı ile vurgulanmasını görmenin güzel olacağını söyledi.
Ayrıca - öğlen öğle yemeği toplantısından önce alabilir mi?
Elbette.
Öncelikle bu verileri uygun bir Excel Tablosuna çevirelim. Bu, formülleri girmeyi çok daha kolay hale getirecek çünkü Excel ilerledikçe bunları otomatik olarak kopyalayacaktır.
Şimdi son kullanma tarihlerini hesaplayalım. Bunların bir yıl sonra aynı ayın sonunda olması gerekiyordu, ancak ilk önce basit bir hack kullanalım, basketbol sahasına girelim. Daha önceki videolarda gördüğünüz gibi, tarihler sadece seri numaralarıdır, bu nedenle Yenileme tarihine 365 gün ekleyen bir formül girebiliriz.
Bu iyi bir başlangıç. Çözümü kabalaştırmayı bitirebilir ve bunu daha sonra düzeltmek için geri gelebiliriz.
Excel'de daha karmaşık bir sorunu çözerken, genel yaklaşımı doğrulamak ve ardından ayrıntılara geri dönmek iyi bir fikirdir. Başlangıçta küçük bir şeye takılıp kalmak istemezsiniz, özellikle de yaklaşım değişebilirse.
Artık bir son kullanma tarihimiz olduğuna göre, kalan günleri hesaplayabiliriz. Bunun gelecekte otomatik olarak güncellenmesi gerekiyor, bu nedenle her zaman bugünün tarihini döndüren BUGÜN işlevini kullanacağız.
Formül basitçe E5 eksi BUGÜN () şeklindedir. İade düğmesine bastığımda, sürenin dolmasına kalan günler alıyoruz. Negatif sayılar üyeliğin zaten sona erdiğini gösterir.
Durum için iç içe geçmiş basit bir EĞER formülü kullanacağız. Kalan günler sıfırdan azsa, üyeliğin süresi dolar. Aksi takdirde, kalan gün sayısı 30'dan azsa, durum "Yakında sona eriyor" olmalıdır. Aksi takdirde, durum hiçbir şey değildir.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Daha sonra, bu değerleri vurgulayan koşullu biçimlendirme kuralları oluşturmamız gerekiyor.
İlk önce verileri seçin ve etkin hücreyi sağ üst köşeye ayarlayın. Ardından, etkin hücreyi "Süresi Doldu" değeri için test eden bir formül kuralı oluşturun. Sütun kilitlenmelidir.
Şimdi aynı işlemi "Yakında sona erecek" üyelikler için tekrarlayın.
Bu iyi görünüyor. Son kullanma tarihlerini ayın son gününe denk getirmemiz gerekiyor.
Geçmişte veya gelecekte bir ayın son gününü alan EOMONTH (ay sonu için) adlı harika bir işlev olduğu ortaya çıktı.
Başlangıç tarihi yenilenme tarihi ve aylar 12.
Ve işte, patronunuzun istediği her şeye sahipsiniz ve öğle yemeğinden önce bir fincan kahve içmek için hala vaktiniz var.
Ders
Temel Formülİlgili kısayollar
Tablo ekle Ctrl + T ⌃ + T Şeridi genişlet veya daralt Ctrl + F1 ⌘ + ⌥ + R Tablo seç Ctrl + A ⌘ + A Etkin hücreyi seçimde saat yönünde taşı Ctrl + . ⌃ + .
