Excel ile Basit İş Programı Hazırlama - Cumartesi ve Pazar Hesaba Katılmış 3. Çözüm

Daha önce aşağıdaki formata benzer bir iş programı oluşturmuştuk. Fakat bunu yaparken tatil günlerini işlemlerimizde kullanmamıştık. Öncelikle kendi belirleyeceğimiz 2 tatil gününü dikkate alarak iş programı oluşturmayı denedim ama süreç karmaşık bir hal alınca alıştırma olarak cumartesi ve pazar tatil günlerine döndüm. Yine de altyapıyı tatil gününü değiştirebileceğimiz şekilde hazırladım.

Aslında Office 2010 versiyonunda haftanın hangi günlerinin tatil olduğunu belirleyebildiğiniz yeni bir formül var, bu formül kullanıldığı taktirde zaten elle formül yazmaya gerek kalmıyor. Bunu keşfettiğimde dosyayı zaten hazırlamış olduğum için bozmak istemedim. İlgilenenler için aşağıda link var.

http://office.microsoft.com/en-us/excel-help/networkdays-intl-function-HA010354379.aspx

Excelin gelişmiş ayarlarındaki formülleri gösterme seçeneğini kullanarak hazırladığımız formüllerin ne işe yaradığını inceleyelim. Bunun için ekranın sol üst köşesindeki office düğmesine tıklayıp, açılan penceredeki excel seçeneklerine tıklayalım.

Karşımıza çıkan penceredeki gelişmiş sekmesine tıklayalım ve bu çalışma sayfasının seçeneklerini göster başlığı altındaki hücrelerde formüllerin hesaplanmış sonuçları yerine formülleri göstere tik atalım ve tamama tıklayalım.

İlk olarak daha önce yaptığımız gibi proje tarihini girmemiz gerekli. Ardından projedeki tatil günlerini tanıtmalıyız. Bunun için B19-B20 hücrelerine tatil günlerini girmeliyiz.

Şimdi formülleri incelemeye başlayabiliriz. C kolonundaki formülün görevi elle girilen başlangıç tarihinin tanımlı tatil gününe gelip gelmediğini kontrol etmek. Bu karşılaştırmayı yapmak için H18 hücresinden itibaren bir kontrol oluşturmalıyız. (Bu kontrol görsel olarak gerekli olmadığı için metni beyaz yapılarak gizlenmiş durumda.) H18 hücresine =YADA(H2=$B$19;H2=$B$20) formülünü girip takvimin son gününe kadar sürükleyelim. 18.satırdaki bu formül tatil gününe denk gelirse doğru, gelmez ise yanlış sonucunu verecek. C kolonundaki yatayara formülü elle girilen tarihi takvimin en üst satırındaki tarihler arasında buluyor, ardından 18 satır aşağı iniyor ve buradaki sonucu hücreye atıyor.

D kolonundaki formül ise aynı işlemi elle girilen başlangıç tarihinin bir gün sonrası için yapıyor. Eğer başlangıç tarihi tatil gününe denk geldi diye bu tarihi bir gün sonraya atarsak ve bir gün sonrası da yine tatil gününe denk gelirse sorunla karşılaşmamak için böyle bir yol izlemeliyiz.

E kolonundaki formül ise başlangıç tarihinin tatil gününe denk gelmesi durumunda bir gün sonrasının da tatile gelip gelmediğini kontrol ederek başlangıç tarihini revize ediyor.

F kolonunda bir formül yok, buraya aktivite sürelerini elle giriyoruz.

H kolonunda ise exceldeki işgünü fonksiyonu kullanılarak bitiş tarihi hesaplanıyor. Bu formül cumartesi ve pazarı tatil sayıp, başlangıç gününden sonra aktivite süresi kadar iş günü ekler ve bitiş tarihini hesap eder.

Takvim için kullandığımı formüller daha önce kullandığımız formüller ile aynı, onları tekrar anlatmıyorum.

Tatil günlerini tanımlarken yazma hatalarına karşı aşağıdaki gibi dropdown list eklemekte fayda var.

Bu listeyi hazırlamak için listede gözükmesini istediğimiz günleri alt alta olacak şekilde A22:A28 hücrelerine yazalım. Daha sonra veri sekmesindeki veri doğrulama düğmesine tıklayıp tekrar veri doğrulamayı seçelim.

Karşımıza çıkan pencerede izin verilen tür olarak listeyi seçelim.

Kaynak olarak ise A22:A28 aralığını seçelim ve tamama tıklayalım. Şimdi aynı işlemi ikinci tatil günü içinde yapalım. Ardından A22:A28 hücrelerindeki metni beyaz yaparak saklayalım, bu öğelere de görsel olarak ihtiyacımız yok. Küçük bir not, metni saklamak yerine A22:A28 hücrelerini temizlerseniz, boş bir dropdown list açılır.

Şimdi takvimi nasıl renklendirdiğimizi anlatalım. H3 hücresine aşağıdaki biçimlendirme formülünü ve formatını girip, biçim boyacısı ile takvimin son gününe kadar taşıyalım. Bu formül 3.satırdaki tek harfli takvim günlerini kırmızıya boyamak için gerekli.

Aktivite sürelerini boyamak için ise H7 hücresine 2 farklı formül girmeliyiz. Birinci formül o günün tatil günü olup olmadığını kontrol ederek kırmızıya boyamak için, ikinci formül ise o günün aktivitenin başlangıç ve bitiş tarihi arasında olup olmadığını kontrol edip gerekli boyamayı yapmak için gerekli. Koşullu biçimlendirme yaparken eğer iki formüldeki koşulda sağlanırsa excel üstte kalan biçimlendirmeyi yapar. Eğer yazdığınız formül alttaysa üste çıkarmak için mavi renkli oku kullanabilirsiniz.

Formülleri aşağıdaki resimlerde görmeniz mümkün. Son olarak H7 hücresini önce aşağıya son aktiviteye kadar sürükleyelim ve sonrasında takvim sonuna kadar sağ tarafa doğru sürükleyelim. İş programımız hazır.

Bu dosyanın size ne gibi yararı olabilir? Eğer raporlama yaptığınız kişi primavera-ms project gibi popüler programları bilmiyor, excel gibi daha sade formatlarda raporlama istiyor ise iş programının excel çıktısını alarak aktivitelerin başlangıç ve bitiş tarihlerini E ve G kolonlarına elle taşıyabilirsiniz. Böylelikle her raporlama zamanı hücre boyamaktan kurtulmuş olursunuz.