Excel Formüllerinde Kendinizin Tanımladığı Fonksiyonları Kullanma

Excel'de hücre içerisinde sürekli kullandığınız uzun formülleriniz varsa, kendi fonksiyonlarınızı kullanmak sayfa düzeni açısından faydalı olacaktır. Yaptığınız çalışmaları başka insanlar incelediği zaman anlaşılması, takibi ve hatta modifiyesi daha rahat olacaktır. Bunu yapmak için geliştirici sekmesindeki visual basic düğmesine tıklayalım.

Şimdi soldaki listede boş bir alana sağ tıklayalım ve insert ardından module’e tıklayalım. Modülümüz eklendi, şimdi formülü tanıtalım. Burada tanıtacağımız her fonksiyon için fonksiyon başını ve sonunu tanıtmalıyız. Bunu yapmak için Function FonksiyonAdı(Girdi1,Girdi2,…) as GirdiTipi ve End function terimleri kullanılmalıdır. FonksiyonAdı fonksiyonu parantez içerisinde tanımlı GirdiTipi tipindeki girdileri kullanarak bu iki satır arasında yazılmış tüm işlevleri yerine geçirecektir.

Yazmak istediğimiz fonksiyonun 2 sayı arasındaki tüm sayıları toplamasını istiyoruz. Bunun için modülümüze

Function ardısıktoplama(x, y) As Single

For i = x To y

ardısıktoplama = ardısıktoplama + i

Next i

End Function

satırlarını ekleyelim.

Bu fonksiyon single tipindeki x ve y değerini alıp for döngüsünde kullanarak sonuca ulaşır. Topladığımız sayılar tam sayı olduğu için single tanımlaması kullandık. Toplayacağımız sayılar ondalıklı sayılar olsaydı double tanımlaması kullanmalıydık. Bu tanımlamaları neden yapıyoruz? Eğer girditipi tanımlamazsanız excel gereksiz yere bellek kullanacaktır. Şu an sadece 5 satırdan oluşan bir kod hazırladık. Eğer binlerce satırdan ve yüzlerce değişkenden oluşan kodlar kullanırsanız bilgisayarınızın belleği zorlanabilir, yani excel yavaşlayabilir.

Bu fonksiyonu kullanmak için hücre içerisinde = ile başlayarak module içerisinde daha önce yazdığımız FonksiyonAdı formülünü yazıp(ardısıktoplama) parantez içerisinde (B1;B2) yazmalıyız.

Formülümüz çalışıyor, sonuç olarak 515 değerini verdi. Bazı durumlarda B1 ve B2 hücrelerini değiştirmenize rağmen formülün sonucu değişmeyebilir. Bunu engellemek için module içerisinde fonksiyonu tanımlarken for döngüsünden önce

Application.Volatile = True

satırlarını eklemeliyiz. Bunun sakıncası ise B1 ve B2 değerlerini değiştirmemenize rağmen bu formüldeki tüm işlemleri gereksiz yere tekrarlayabilir, yani excel yavaşlayabilir.

Module içerisine yukarıdaki koşullara uyarak istediğiniz kadar farklı fonksiyon tanımlayabilirsiniz. Hatta bir fonksiyon içerisinde başka bir fonksiyon bile çağırabilirsiniz. Bunun için x değerini baslangıc adlı başka bir formül ile hesaplayacağımızı varsayalım.

For döngüsünden önce

x = baslangıc(B1)

satırlarını eklemeliyiz. Bu durumda baslangıc fonksiyonu ardısıkfonksiyonu hala aktifken B1 hücresindeki değeri kullanarak yeni x değerini atayacaktır.

Mobil Vinçler için Yük Tabloları

Şantiyelerde kullanacağımız vinçlerin hangi koşullarda ne kadar yük kaldırabileceğini öğrenmek için o vince ait yük tablolarına ihtiyacımız var. Bu tabloları nasıl okuyacağımızı öğrenelim.

Aşağıda Grove, Liebherr ve Terex firmalarına ait 3 farklı vincin kullanma kılavuzları mevcut. Bu kılavuzlar hakkında bilmemiz gereken ilk konu yük tablolarında kullanılan sembollerin anlamlarıdır. İncelediğiniz tablo sadece ve sadece o sembollerdeki koşullar için geçerlidir. Grove vince ait kılavuzun 3. ve 19.sayfalarında, Liebherr vince ait kılavuzun 25.sayfasında ve Terex vince ait kılavuzun 3.sayfasında bu sembolleri ve anlamlarını bulabilirsiniz.

Şimdi Grove vince ait bir yük tablosunu inceleyelim. 13.sayfadan başlayalım. Sayfadaki ilk tablonun üzerinde 4 farklı sembol var. Sembollerin anlamlarına bakarsanız ilk sembol Boom(bom), ikinci sembol Outrigger(vinc ayağı), üçüncü sembol Working Range(Çalışma aralığı) ve dördüncü sembol Counterweight(balans ağırlığı) için kullanılmış. Yani bu tablo bom uzunluğu 8.8-29.0 metre arasındayken, ayaklar tam açıkken, 360 derece aralıkta ve 3810 kg balans ağırlığı olduğunda geçerli. İlerleyen sayfalarda ayaklar yarım açıkken veya kapalıyken ve farklı balans ağırlıkları takılıyken geçerli yük tablolarını bulabilirsiniz.

Peki bu tabloyu nasıl okuyacağız? Tablonun sol üst köşesindeki sembol Radius(yarıçap) için. Tablonun hemen üstündeki sembol ise yine bom için. Yarıçap vincin merkezinden kancaya kadar olan mesafe. Dikkat ederseniz tablodaki en yüksek kapasite sol en üstteki 30 ton. (Vincin özelliklerinin yazılı olduğu ilk sayfada zaten 30 ton olduğu belirtilmiş.) 30.000 ton kaldırabileceğimiz bom uzunluğunu bulmak için tablonun ilk satırındaki rakamlara bakmalıyız, yani 8.8 metre. Bu konumdaki yarıçap için ise ilk kolondaki rakamlara bakmalıyız, yani 2.5 metre.

Tabloyu incelemeye devam edelim. Bom uzunluğumuzu değiştirmeden,(8.8 metre) yarıçapı değiştirirsek ne olacak? Bunun için 8.8 metre yazan kolondan aşağıya doğru inmeliyiz. Örneğin yarıçapı iki katına, yani 5 metreye, çıkartırsak bu koşulda en fazla 17.875 ton yük kaldırabilirsiniz. Yarıçap arttıkça kaldırma kapasitesi düşüyor, beklenen bir sonuç.

Şimdi 13.sayfaya bakalım. İlk tablodakine göre tek farklı koşul ayakların yarım açık olması. Bu durumda kaldırılabilecek maksimum yük, tablonun sol en üst köşesi, 30 tondan 27 tona düşüyor.

Grove

Liebherr vincin 5.sayfasına ilk tabloya bakalım. Yani bu tablo bom uzunluğu 11.5-60.0 metre arasındayken, ayaklar tam açıkken, 360 derece aralıkta ve 7600 kg balans ağırlığı olduğunda geçerli. Şimdi elimizde 5 ton bir yük olduğunu ve bu yükü kaldırıp 25 metre yarıçapa koyup koyamıyacağımıza bakalım. (İnşaatlarda genelde vinçler için bu durumdaki gibi yarıçapı arttıran fiziksel engeller olur, sabit yapılar, çalışan başka ekipler, vinç çalışmasına uygun olmayan zeminler gibi.) Tabloda 25 metre olmadığı için güvenli yönde kalmak adına yarıçağı 26 metre olan satıra bakmalıyız. Gördüğünüz gibi bu koşullarda vinç 1.2-3.5 ton aralığında yük kaldırabiliyor. Yani bu vinç bu koşullarda işimizi görmüyor.

4.sayfadaki ikinci tabloya bakarsanız 26 metre yarıçapta 3.5-5.7 ton aralığında yük kaldırmak mümkün. Yani bu işi yapmamız için 7600 kg olan balans ağırlığını 17200 kg olarak değiştirmeliyiz. Bunu yapsak bile bom uzunluğu 30.1-47.5 metre aralığındayken yapabiliriz. Bom uzunluğu 48.7-60.0 metre aralığındayken bu yükü kaldırmamız bu koşullarda bile mümkün değil.

LIEBHERR

Şu ana kadar sadece belirli koşullarda belirli bir yükü kaldırıp kaldıramayacağımızı kontrol ettik. Peki bu yükü belirli bir yüksekliğe koyup koyamayacağımız nasıl kontrol ederiz? Bunu öğrenmek için Terex vincin 8.sayfasını açalım ve bom uzunluğu 15.24 metre iken 9 metre yarıçaptaki bir yükü en fazla hangi yüksekliğe koyabiliriz öğrenelim. Bunun için yatay eksendeki 9 metreye ait çizgiyi 15.24 metre yarıçapında çizilmiş daire ile kesiştirmeliyiz. Bu kesişimin değeri(yaklaşık 13.5 metre) kanca bloğunun üst yüksekliğini verecek. Bu sebeple bulduğumuz değerden grafiğin üstünde bulunan 1.6 metreyi çıkarmalıyız. (13.5-1.6=11.9 metre) Şimdi kanca altı yüksekliğini bulmuş olduk. Bu yükü kaldırmak için sapan, aparat vb. kullanıyorsak onların da yüksekliğini ve kaldıracağımız cismin yüksekliğini bulduğumuz son değerden düşmemiz gerekir. (1 metre aparat ve 2 metrelik yük olduğunu varsayalım.) Yani bom uzunluğu 15.24 metre iken 9 metre yarıçapta tır üzerinde bulunan bir yükü en fazla 11.9-1-2=8.9 metre yüksekliğe koyabiliriz.

TEREX

Buraya koyduğumuz tüm örneklerde tablolar 360 derece için geçerliydi. Peki bu ne demek? Bazı vinçlerin zayıf yönleri olabiliyor. Belirli bir kapasitede yük kaldırabilmesi için bomun vince paralel olması gerekiyor. Bu durumda tabloda çalışma aralığı sembolü olarak 360 derece sembolünden farklı bir sembol görecektik. Yani kısıtlı açıda çalışabiliyorsunuz. Bu açının vinci yöneten bilgisayarın yazılımından ayarlanması mümkün.

Yine aynı şekilde bazı vinçlerde yük havadayken düşük hızlarda yatay taşımaya izin veriliyor. Bu durumda da bomun vince paralelken kitlenmesi gerekebiliyor. Vincin yazılımı bomun kitlenmesini de sağlıyor.

Son bir not. Bu örneklerde sürekli vincin kapasitesini ve imkanlarını kontrol ettik. Unutulmaması gereken vincin donamının da kaldırılması düşünülen yüke uygun olması gerekiyor. Halat kanca bloğunda ne kadar tur atarsa kaldırma kapasitesi o kadar fazla, ama kaldırma hızı da o kadar düşük oluyor. Bazı projelerde çalışılan yükler az olduğundan vincin çalışmasını hızlandırmak için o yüke uygun donam konfigurasyonu seçiliyor, yani başka bir şantiyeye giderse o konfigurasyonun değiştirilmesi gerekir.

Excel ile Basit Doküman Takip Tablosu Oluşturma

Şantiyelerde işveren, müşavir ve taşeronlar ile yapılan yazışmaların sayıları uzun süreli projelerde binlerceye ulaşmakta ve basit gibi görünen kayıt tutma süreci zaman zaman baş ağrıtmaktadır. Farklı dokümanlara aynı takip numarası verilmesi olasıdır. Bunu engellemek için şantiye başında takibi rahat bir formatta doküman takip tablosu oluşturmalıyız. Şantiyemizde 3 farklı kategoride doküman numarası vereceğimizi varsaydık. (Bu sayıyı aşağıda anlatılan formülleri revize ederek istediğimiz kadar arttırmak mümkün.) İşveren yazışmalarının numaraları 1, müşavir yazışmalarınınki 2 ve son olarak taşeron yazışmalarınınki ise 3 ile başlayan 4 haneli rakamlardan oluşacak. Yani kategorilere ait ilk doküman numarası sırasıyla 1001, 2001 ve 3001 olacak.

İlk olarak kategoriyi belirlemeye yarayan formülü inceleyelim. (Kategoriyi bilmemiz hem yeni numara verirken hem de tabloya filtre eklenmesi durumunda gerekli.) Eğer formülünü kullanarak doküman numarasının sırasıyla 3000, 2000 ve 1000 değerinden büyük olup olmadığını kontrol ediyoruz. Bunu yapmak için D2 hücresinde =EĞER(A2>3000;3;EĞER(A2>2000;2;1)) formülünü kullandık. Karşılaştırmaya 3000 ile başladık çünkü eğer 1000 ile başlasaydık formül her koşulda 3 değerini verecekti.

Yeni numara vermek için ise her kategorinin D kolonunda toplam kaç kez kullanıldığını bulmalıyız. Ardından bu sayıya 1 ekleyerek yeni numarayı elde edeceğiz. Bunu yapmak için H2 hücresine =EĞERSAY($D:$D;F2)+1+1000, H3 hücresine =EĞERSAY($D:$D;F3)+1+2000 ve H4 hücresine =EĞERSAY($D:$D;F4)+1+3000 formüllerini yazalım. Formül içerisinde neden 1000,2000 ve 3000 kullandığımızı örneklerle anlatalım.

Örneğimizde 1 kategorisi toplamda 10 kez geçtiği için formül =10+1+1000=1011 değerini, 2 kategorisi toplamda 3 kez geçtiği için =3+1+2000=2004 değerini, 3 kategorisi ise toplamda 10 kez geçtiği için =2+1+3000=3003 değerini verecek.

Son olarak tablodaki satırları kategoriye göre renklendirmeyi deneyelim. Bunun için A2 hücresine aşağıdaki formülleri ekleyelim.

A2 hücresini seçelim ve bu formatı biçim boyacısı ile B2:D2 aralığına taşıyalım. (Biçim boyacısı sadece koşullu biçimlendirmeyi değil, yazi tipi, rengi ve çizgileri de taşıyacak, bunu en son da düzelteceğiz.)

A2:D2 aralığını seçelim ve bu formatı biçim boyacısı ile A3:D16 aralığına taşıyalım.

Biçim boyacısının bozduğu yerleri düzeltelim.

Şimdi A1:D16 aralığını seçelim ve giriş sekmesindeki sırala ve filtre uygula butonuna tıklayıp, filtreye tıklayalım.

Doküman takip tablomuz hazır. İstediğimiz kategoriye tik atarak veya tiki kaldırarak filtreleme yapmamız mümkün.

Önemli bir not: Önceki yazılarımızda “İşveren teslim tarihinden itibaren sözleşmede belirtilen belirli bir iş günü içerisinde projeye cevap vermekle yükümlüdür.” demiştik. Aynı mantıkla resmi yazılarımıza da belirli bir süre içinde cevap verilmesi gerekmektedir. Bu yüzden bu tablo formatına cevap verilmesi gereken tarih ve cevap verilen tarih kolonları ekleyebiliriz. Bunu yapmamızdaki sebep nedir? Bir örnekle açıklayalım. Proje malzeme ve ekipmanları satın alınmadan önce işveren onayı alınmalıdır. Eğer işveren bu onayı vermede gecikirse tedarik süreci uzayabilir. Sürenin uzaması durumunda alış fiyatı artabilir veya fiyatı artmasa bile döviz kurundan dolayı fiyat farkı oluşabilir. Bunların hiçbirisi yaşanmasa bile sadece tedarik süreci uzadığı için aktivite iş programında kritik yol üzerinde ise işin teslim tarihi uzayabilir. Bu durumda süre uzatımı ve maddi zararınızın tazmini için resmi bir dayanağınız olmalıdır. Bunun için de satın alınacak ürüne ait teknik bilgileri ne zaman sunduğunuz, bu ürünün onayının geciktiğine dair hatırlatıcı nitelikteki resmi yazıyı ne zaman hazırladığınız gibi sorulara cevap vermeniz gerekmektedir.

Excel ile Proje Takip Tablosu Hazırlama

Bugunkü yazımızda proje takip tablosu oluşturmayı deneyeceğiz. Projelerde genelde yüzlerce proje olur ve işveren dizaynın gecikmemesi için proje başında bu işi çok sıkı tutar. Günlük/haftalık toplantılarda bu konular tartışılır, suçlu bulmak yerine gecikmenin sebebi tespit edilmeye çalışılır.

Bunu nasıl yapabilirsiniz? Projeleri teslim edilen, teslim edilip cevap bekleyen, cevabı çok geciken, hiç teslim edilmemiş gibi kategorilere ayırabilirsiniz. (İşveren teslim tarihinden itibaren sözleşmede belirtilen belirli bir iş günü içerisinde projeye cevap vermekle yükümlüdür.) Daha da ileri gitmek gerekirse bu sistematiği farklı disiplinler için ayırabilirsiniz. Yani geciken projeleriniz mimari mi, statik mi bu tablolar sayesinde anlayabilirsiniz.

Biz bu denememizde disiplinlere ayırmayacağız, basit olması açısından sadece proje durumuna göre listeleme yapacağız ve her projenin en fazla 7 kez revize edileceği varsayımını yapacağız.

Aşağıda oluşturduğum tablo formatını ve B-G kolonlarındaki formülleri görebilirsiniz. Önce tablo formatından bahsedelim ve ardından sırasıyla formüller üzerinden geçelim.

İlk hücresi SAKLA olan kolonlara görsel olarak ihtiyacımız yok, bu kolonları formülasyonda ara eleman olarak kullanacağız.

Revizyon: B kolonunda projenin kaç kez revize edildiğini hesap edeceğiz. Proje adını verirken bu bilgiye sahip olmalıyız. Bunun için 7 farklı revizyonun teslim tarihinin dolu olup olmadığını kontrol edeceğiz. Teslim tarihi hücresi dolu ise eğer fonksiyonu 1 değerini alacak, değil ise 0 değerini alacak ve 7 farklı eğer fonksiyonunun sonucu B kolonunda toplanacak. Yani bir proje için 3 farklı revizyon teslim edilmişse formül =1+1+1+0+0+0+0=3 sonucunu verecek.

=TOPLA(EĞER(H5>0;1;0)+EĞER(K5>0;1;0)+EĞER(N5>0;1;0)+EĞER(Q5>0;1;0)+EĞER(T5>0;1;0)+EĞER(W5>0;1;0)+EĞER(Z5>0;1;0))

Proje Adı: C kolonunda proje adını görebilirsiniz. İşveren adı_bina adı_disiplin adı_revizyon sayısı şeklinde bir formata sahip. Dosya adını A kolonundan ve revizyon sayısını B kolonundan alacağız ve C kolonunda birleştireceğiz. Proje adının formatını proje detaylarına ve işveren taleplerine göre değiştirmek mümkün. Burada basit olması açısından her binaya ait her disiplinde sadece 1 proje olduğunu varsaydık. Yani 3 farklı binanın 4 farklı disiplinde toplam 12 projesi var.

=A5&"_Rev_"&B5

Durum: D kolonunda ulaşmaya çalıştığımız durum tiplerini görebilirsiniz. Formülasyonda kullanıdığımız 4 farklı durum tipini formülasyonda kullandığımız sırayla detaylı şekilde açıklayalım:

=EĞER(E5>$D$2;"TESLİMEDİLMEDİ";EĞER(F5>E5;"CEVAP GELDİ";EĞER(İŞGÜNÜ(E5;7)<$D$2;"CEVAP GECİKTİ";"CEVAP BEKLENİYOR")))

Teslim edilmedi: H kolonuna projenin teslim edildiyse ilk teslim tarihini, teslim edilmesi ise tahmini teslim tarihini girdiğimizi varsayıyoruz. Projenin teslim edilip edilmediğini D2 hücresindeki özet tarihi ile karşılaştırarak anlayacağız. (Müteahhit projeyi teslim etmekte geç kalmış.)

Cevap geldi: Teslim edilmedi kontrolü ardından, proje teslim edildiyse ve bu proje için cevap ve bir not alındıysa (cevap tarihi ve notu hücreleri dolu ise) cevap geldi kategorisini seçeceğiz. (Eğer alınan not A ise proje revizyonuna gerek yok, B veya C ise proje A notu alana kadar revize edilmeli.)

Cevap gecikti: Cevap geldi kontrolünün ardından proje teslim tarihinden itibaren 7 iş günü içerisinde cevap gelmediyse cevap gecikti kategorisini kullanacağız. (İşveren projeyi onaylamada geç kalmış.)

Cevap bekleniyor: Cevap gecikti kontrolünün ardından proje teslim edilmiş ve teslim tarihinden itibaren 7 iş günü geçmemiş ise bu kategoriyi kullanacağız. (Sorun yok, proje onay aşamasında.)

Son Teslimat: 7 farklı revizyonun teslim tarihini mak fonksiyonu ile karşılaştıracağız ve son teslimat tarihini tespit edeceğiz.

=MAK(H5;K5;N5;Q5;T5;W5;Z5)

Son Cevap: Son teslimat kolonunda olduğu gibi 7 farklı revizyonun cevap tarihini mak fonksiyonu ile karşılaştıracağız ve son cevap tarihini tespit edeceğiz.

=MAK(I5;L5;O5;R5;U5;X5;AA5)

Son Not: F kolonunda elde ettiğimiz son cevap tarihinin hangi revizyonda gerçekleştiğini bulana kadar eğer fonksiyonu ile karşılaştırma yapacağız ve bulduğumuz zaman o revizyonun notunu çekeceğiz.

=EĞER(I5=F5;J5;EĞER(L5=F5;M5;EĞER(O5=F5;P5;EĞER(R5=F5;S5;EĞER(U5=F5;V5;EĞER(X5=F5;Y5;EĞER(AA5=F5;AB5)))))))

H kolonundan sonraki bilgilerin tamamını elle girmemiz gerekiyor. Elle girmemiz gereken bilgiler her revizyonun teslim tarihi, cevap tarihi ve notunu içeriyor.

Şimdi SAKLA ile başlayan kolonları saklayalım ve proje takip tablomuz hazır. Eğer proje teslim edildiyse ve cevap gelmediyse veya proje hiç teslim edilmediyse son not kolonu #### değerini veriyor, bu iki koşulu kontrol edip ardından yukarıdaki formülü çalıştırırsak #### işaretinden kurtulabiliriz. Burada kullandığımız formül yeteri kadar uzun olduğundan daha da karmaşıklaştırmamak adına #### değerine göz yumduk.

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.

Excel ile Basit İş Programı Hazırlama - 2.Çözüm

Daha önce hazırladığımız iş programını grafik üzerine işlemeyi denemiştik. Şimdi biraz daha düzgün bir format oluşturmayı deneyelim. Aşağıdaki gibi bir formatla işe başlayalım.

İlk olarak proje tarihini girelim. Buradaki örnekte 07.02.2012 tarihi girilmiş. Şimdi takvimin ilk gününü proje tarihi olarak atayalım. Bunun için E1 hücresine =B4 yazalım. Bu tarihin haftanın hangi gününe denk geldiğini görmek için E2 hücresine =METNEÇEVİR(E1;"gggg"). Eğer bu hücrenin gereksiz uzun olduğunu düşünüyorsanız E3 hücresine =SOLDAN(E2;1) yazabilirsiniz. Bu hücrelerin metin yönlendirmesini istediğiniz bu şekilde ayarlamak için hücreye sağ tıklayıp hücreyi biçimlendire tıklayınca karşınıza çıkan pencerenin hizalama sekmesindeki yönlendirmeye 90 derece yazabilirsiniz. Biçimlendirmeyi tamamladıktan sonra E1:E3 hücrelerini seçip takvimin devam etmesini istediğiniz güne kadar ekranın sağ tarafına doğru sürükleyerek takvimi oluşturabilirsiniz.

Takvimimiz ve aktivitelerimiz hazır olduğuna göre koşullu biçimlendirme ile aktiviteleri takvim üzerine nasıl işaretleyebileceğimizi deneyelim. Bunun için E7 hücresini seçelim ve giriş sekmesindeki koşullu biçimlendirmeye tıklayalım. Açılan pencereden yeni kurala tıklayalım.

Kural türü olarak biçimlendirilecek hücreleri belirlemek için formül kullana tıklayalım ve formül olarak =VE(E$1>=$B7;E$1<=$D7) yazalım. Bu formül takvim gününü iş programındaki aktivite başlangıç ve bitiş günleriyle karşılaştırıyor ve iki koşulunda sağlanması durumunda (takvim gününün aktivite başlangıç ve bitiş tarihi arasında olması durumunda) doğru değerini alıyor. Formul doğru değerini alırsa hücre biçimlendirmesi önizlemedeki hale gelecek.

Bu biçimlendirmeyi yapmak için ise biçimlendire tıklayalım. Karşımıza çıkan penceredeki dolgu sekmesine tıklayalım ve yeşil renkli butona ve ardından tamama tıklayalım.

Gördüğünüz gibi formül doğru değerini aldı ve koşullu biçimlendirme hücreyi yeşile boyadı.

Bu formülü her hücreye tek tek girme şansımız olmadığından, bu hücreyi aşağı doğru sürükleyerek koşullu biçimlendirmeyi diğer aktivitelere de taşıyalım.

Şimdi E7:E15 hücrelerini seçelim ve takvimin son gününe kadar ekranın sağ tarafına doğru sürükleyelim.

E7 nolu hücreye dönecek olursanız, iş programımızın oluştuğunu göreceksiniz.