Tablo Çözümleme - 2.Yöntem: Array Formülleri

Dünkü yazımızda düşeyara formülünü kullanmıştık. Bugün ise array formülleri kullanacağız. Yine aynı formattaki tablomuzu kullanacağız. İlk olarak array formüllerinde kullanacağımız adları tanımlamakla işe başlayalım. Bunun için formüller sekmesindeki ad yönetcisi düğmesine tıklayalım.

Açılan pencerede yeniye tıklayalım ve 8.kattaki kesitleri arrayler halinde tanıtalım.

Ad olarak kat_8 yazalım ve başvuru yeri olarak B3:L3 aralığını seçelim. Böylece 1 satır * 8 kolonluk bir array tanımladık.

Aynı şekilde tüm katlara ait adları tanıtalım. Toplamda 8 adet (1 * 8) boyutunda array tanımlamış olduk. Değer kolonunda arraylerin aldığı değerleri görmeniz mümkün.

N kolonuna daha önceden hazırladığımız kolon numaralarını yazalım. O2:O12 aralığını seçelim entera basalım ve formül olarak =DEVRİK_DÖNÜŞÜM(kat_1) yazalım ve yazdıktan sonra ctrl, shift ve enter tuşlarına basalım. Dikkat ederseniz excel formülün başına ve sonuna parantez ekledi. Bu hareketi yapmamızdaki amaç excele bu formülün bir array formülü olduğunu göstermekti. Devrikdönüşüm formülünü kullanmamızdaki amaç ise 1 satır * 8 kolonluk arrayin formatını 8 satır * 1 kolon formatına çevirmek. Formülü yazarken kat_1 karakterlerini girdiğimizde excel ad yöneticisinden bizim için seçtiği arraylari listeledi.

Formülümüzün doğru çalışıp çalışmadığını kontrol edelim, B10:L10 ve O2:O12 aralığındaki sayıların toplamını kontrol edelim. Her iki aralığın toplamı da 13550, yani formülasyonumuz doğru.

Aynı işlemleri diğer katlar için de yapalım. Bunun için O2:O12 aralığındaki hücreleri kopyalayalım ve O13:O23 aralığına yapıştıralım. Ardından bul ve değiştir (ctrl ve f) fonksiyonu yardımıyla formüller içerisindeki kat_1 değerini kat_2 ile değiştirelim. Bunun için aranan değeri olarak kat_1, yeni değer olarak kat_2 ve konum olarak formülleri seçelim ve tümünü değiştire tıklayalım.

Yeni tablomuz hazır.

Tablo Çözümleme - 1.Yöntem: Düşeyara Formülü

Aşağıdaki tabloda projemizdeki tüm kolonlara ait kesit bilgisi mevcut. Bu yazımızda kolonlara sistematik bir numara vermeyi ve bu numarayı kullanarak tabloyu çözümlemeyi deneyelim. Peki bunu neden yapıyoruz? Elimizde dizayn departmanından gelen tahmini kolon kesitleri var ve kat yükseklikleri aynı değil. Yani basit bir tablo yaparak tahmini beton metrajını çıkartabiliriz. Eğer kat yüksekliğiniz değişmiyorsa zaten böyle bir çözümlemeye gerek yok, B3:L10 seçimindeki hücrelerin toplamını, yani toplam kesit alanını kat yüksekliği ve toplam kat sayısı ile çarparak aynı sonuca ulaşabilirsiniz. Ama en önemli amacımız excel pratiği yapmak, farklı bakış açıları geliştirmek. Bunun için yarın yayınlanacak 2.yöntemde ad tanımlama ve array formülleri ile aynı işi yapacağız.

Konumuza geri dönelim. Kullanacağımız numara sistemi çok basit. Örnek vermek gerekirse 108 numaralı kolon, 1.kata ait 8 numaralı kolon. Yani tablodan çekmemiz gereken değer I10 hücresindeki 1000 değeri.

Şimdi N kolonuna kolon numaralarını girelim. Bu numaraları makro ile yazdırmak mümkün, ileride makro çözümlere başladığımız zaman göreceğiz. Şimdilik elle 101-111,201-211,…,801-811 şeklinde kolon numaralarını girelim.

Tabloyu çözümlemek için kolonun hangi katta olduğunu ve kaçıncı kolon olduğunu bilmeliyiz. Kolonun hangi katta olduğunu bulmak için O kolonunda soldan formülünü kullanacağız. O2 hücresine =SOLDAN(N2;1) yazalım ve 811 numaralı kolona kadar formülü aşağıya doğru sürükleyelim.

Kolonun kaçıncı kolon olduğunu bulmak için ise tablonun sistematiğinden faydalanacağız. Kolon numaraları tablonun aşağısına doğru artarak ilerlediğinden, belirli bir satırdaki kolona kadar O kolonundaki belirli bu katın toplamda kaç kez yazıldığını bulabiliriz. Bunun için eğersay formülünü kullanacağız. P2 hücresine =EĞERSAY($O$2:O2;O2) yazalım ve 811 numaralı kolona kadar formülü aşağıya doğru sürükleyelim.

Şu an yapmamız gereken O ve P kolonlarındaki veriyi birleştirip tablodan kesit bilgisini çekmek. Düşeyara formülü bu noktada devreye giriyor. Fakat O kolonundaki soldan formülü sonucu metin olarak verdiği için öncelikle metni sayıya çevirmek için sayıyaçevir formülünü kullanmalıyız. Q2 hücresine =DÜŞEYARA(SAYIYAÇEVİR(O2);$A$3:$L$10;P2+1;0) yazalım. Bu formül kat ve kolon numarasını kesiştirecek. Formülü aşağı doğru sürüklediğimiz zaman referans aralığının değişmemesi için $ işaretleri kullanmalıyız. Son olarak 811 numaralı kolona kadar formülü aşağıya doğru sürükleyelim.

Doğru yapıp yapmadığımız kontrol etmek için tüm kesitlerin toplamını karşılaştıralım. B3:L10 seçimindeki hücrelerin toplamı ekranın sağ altında görebileceğiniz gibi 94875.

Q kolonundaki kesitlerin toplamı yine ekranın sağ altında görebileceğiniz gibi 94875. Yani formülasyonumuzda bir hata yok.

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.