Teknik Ofis

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üzeFunction ardısıktoplama(x, y) As SingleFor i = x To yardısıktoplama = ardısıktoplama + iNext iEnd Functionsatı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 önceApplication.Volatile = Truesatı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.