Excel Özet Tablo-Dış Veri Kaynağı Kullanma

Artık excel çalışma sayfasındaki verileri kullanarak nasıl özet tablo oluşturmayı öğrendiğimize göre, aynı işi dış veri kaynağı kullanarak nasıl yapacağımız üzerinde duralım.

Şantiyemizde 1 adet JCB silindir ve 1 adet JCB kepçe çalıştığını varsayalım. Ekipman puantorü aşağıdaki gibi bir Access tablosunu hazırlayarak aylık hakediş için size yolladı.

Şimdi boş bir Excel çalışma sayfası açalım ve ekle sekmesinden özet tabloya tıklayalım. Daha önceki çalışmamızda Excel otomatik olarak sayfadaki tabloyu algılamıştı. Bu çalışmamızda tablonun nerede olduğunu bizim göstermemiz gerekli. Bunun için dış veri kaynağı kullan seçeneğini işaretleyerek bağlantı seç butonuna tıklayalım.

Açılan pencerede çalışma kitabındaki, ağdaki ve bilgisayardaki daha önce tanımlanan bağlantıları görebilirsiniz. Buradaki örnekte kendi bilgisayarımızdaki bir Access dosyasını tanıtalım. Eğer ağ üzerindeki bir dosyayı tanıtacaksanız gerekli ayarları yaptığınızdan emin olun. Access dosyası üzerinde birisi veri girişi yaparken tablo oluşturursanız eksik veri ile tablo oluşturmuş olabilirsiniz. Şimdi daha fazlası içi göz ata tıklayarak bilgisayarımızdaki Access dosyasını seçelim ve aça tıklayalım. Şimdi karşımıza ilk çıkan özet tablo oluşturma penceresine tekrar döndük. Excel sayfamız boş olduğu için özet tabloyu yeni bir sayfada oluşturmamıza gerek yok. O yüzden varolan çalışma sayfası seçeneğini işaretleyelim ve tamama tıklayalım.

Daha önceden bildiğimiz boş özet tablo formatı yine karşımızda. Sağ tarafta özet tablo penceresindeki yazılı satırlar Access tablosundaki kolon başlıkları ile aynı. Buradan sonra ne yapmamız gerektiğini zaten biliyoruz, tekrar anlatmaya gerek yok.

Excel Özet Tablo

Özet tabloların ne işe yaradığını, bize ne kadar zaman kazandırdığını anlatmak için yine proje müdürümüzün şantiyedeki kiralık ekipmanların hak edişini onaylamak için özet bir tablo hazırlamamızı istediğini varsayalım. Bu örnekte sadece 3 ekipman olduğunu varsaydık. Ekipman adını, operatörünü, çalıştığı dönemi ve süreyi şekildeki gibi excele girelim. Özet tablolara veri girerken boş satır bırakmamaya özen gösterin ve kolonların hepsini ayrı ayrı isimlendirmeyi unutmayın lütfen.

Şimdi ekle sekmesinden özet tabloya tıklıyoruz. Gördüğünüz gibi Excel tabloyu otomatik olarak algıladı ve çerçeveye aldı.

Eğer elimizdeki veriler başka bir dosya üzerinde kayıtlı olsaydı, (Excel, Access gibi) dış veri kaynağı kullan diyerek o dosyayı tanıtmalıydık. Şantiyelerde ekipman puantajını tutan kişinin sizden bağımsız olarak çalışıp her ay düzenli olarak size bir Excel veya Access dosyası gönderme olasılığı çok yüksek. Bu detaylı konuyu başka bir zaman daha detaylı anlatmak ümidiyle konumuza devam edelim. Özet tabloyu yeni bir sayfada oluşturmak için yeni çalışma sayfası seçeneğini işaretleyerek tamama tıklayalım. Karşımıza aşağıdaki gibi boş bir özet tablo çıktı.

Şimdi özet tablomuzu oluşturalım. Sayfanın sağ tarafında beliren hazırladığımız tablonun başlıklarına dikkat edin lütfen. İlk olarak nispeten basit bir özet tablo hazırlayalım. Hangi ekipman kaç saat çalışmış onu görelim. Bunun için Makine satırını sürükleyip boş tabloda satır alanlarını buraya bırakın yazan yere bırakalım. Şu an şantiyedeki tüm makineler tabloda belirmiş oldu.

Sonraki aşamada toplam saate ihtiyacımız var. Bunun içinde Toplam saat satırını boş tabloda veri öğelerini buraya bırakın yazan yere bırakalım.

Gördüğünüz gibi Kepçe-1 51, Kepçe-2 58 ve Kepçe-3 58 saat çalışmış.

Şimdi tablomuzu hazırlarken bir hata yapıp 30-12-2011 tarihli kepçe-1 saatini yanlış girdiğimizi varsayalım. Hatamızı fark ettik ve F2 hücresindeki değeri 18 olarak düzelttik. Özet tablomuza geliyoruz ve değişiklik var mı yok mu diye baktığımızda hala eski değerlerin olduğunu görüyoruz. Güncel değerleri almak için özet tablomuza sağ tıklayarak yenileye tıklıyoruz.

Gördüğünüz gibi Kepçe-1 59 saat olarak güncellendi.

Şimdi farklı bir şey deneyelim ve 11.aya ait ekipman puantajını da tablomuza şekildeki gibi girelim.

Aynı yöntemle özet tablomuzu oluşturalım ve bu sefer daha öncekinden farklı olarak sağ taraftaki ay satırını tabloda sütün alanlarını buraya bırakın yazan yere bırakalım.

Sonraki aşamada tekrar toplam saate ihtiyacımız var. Bunun içinde Toplam saat satırını boş tabloda veri öğelerini buraya bırakın yazan yere bırakalım.

Gördüğünüz gibi 11.ayda Kepçe-1 30, Kepçe-2 26 ve Kepçe-3 17 saat çalışmış.

Excel Koşullu Biçimlendirme ve Filtreleme

Excelin sunduğu koşullu biçimlendirme ve filtreleme özelliğini bir arada nasıl kullanabileceğimizi basit bir örnek ile açıklayalım. Örneğimizin gerçekçi olması adına, proje müdürümüzün imalatını tamamladığımız a blok temeli için bütçe ve gerçekleşmeleri karşılaştırmamızı istediğini varsayalım. Bütçemizdeki A blok ile ilgili imalat kalemlerini, metrajları, birim fiyatları ve toplam maliyetleri muhasebeden aldığımız toplam gerçekleşmeleri şekildeki gibi karşılaştırabileceğimiz basit bir formatta hazırlayalım.

Daha sonra F kolonun yanına Kontrol başlığını ekleyelim. Burada kontrol etmemiz gereken, gerçekleşmenin bütçeyi aşıp aşmadığı. Bu noktada koşullu biçimlendirme devreye giriyor. G2 hücresini seçiyoruz ve giriş sekmesindeki koşullu biçimlendirmeye tıklayıp yeni kural oluşturuyoruz. Karşımıza çıkan kural türlerinden en alttaki biçimlendirilecek hücreleri belirlemek için formül kulanı seçiyoruz ve formülümüzü yazıyoruz. Eğer gerçekleşme toplam maliyetten fazla ise hücrenin kızarmasını istiyoruz. Yani formülümüz =$F$2>$E$2 şeklinde oluyor. Burada $ işaretlerine dikkat edelim. Hücreyi seçer seçmez Excel kendisi $ işaretini yerleştiriyor, fakat biz bunları silip formülümüzü =F2>E2 olarak değiştiriyoruz. Bu kontrolü sadece bir tek satırda yapacaksak bunu yapmamız sonucu değiştirmez, fakat binlerce satırlık bir bütçe ile uğraşıyorsak koşullu biçimlendirmeyi her hücre için ayrı ayrı yapmaktansa bir kere yapıp kopyalamak işimize gelir. $ işaretlerini kaldırmazsanız kopyalama yaptığını hücre daima 1.satırdaki iş kalemini karşılacaktır. Bu da bizi yanıltır.

Daha sonra biçimlendire tıklayıp dolgu sekmesini seçiyoruz. Karşımıza çıkan renk paletinden kırmızı rengi seçiyoruz.

İşlemi tamamladıktan sonra ilk satırdaki kontrol hücresinde bir değişiklik olmadığını görüyoruz. Gerçekleşme bütçeyi aşmadığından hücre formatı değişmiyor. Şimdi bu koşullu biçimlendirmeyi diğer hücrelere kopyalayalım. G2 hücresini seçip G3 ve G4 hücrelerine doğru sürükleyelim. Aşağıdaki gibi bir tabloyla karşılaşmamız lazım.

Şimdi öncelikli olarak müdürünüzle bütçeyi aşan kalemleri tartışacağınızı varsayıp, bu kalemleri nasıl filtreleyeceğimizi anlatalım. Bunun için öncelikle A1-G4 arası hücrelerin tamamını seçip giriş sekmesindeki sırala ve filtre uygulaya tıklayıp filtreye tıklıyoruz. Gördüğünüz gibi her kolunun ilk hücresine filtremiz eklendi.

Şimdi G1 hücresine tıklayıp renge göre filtreye tıklayıp kırmızı rengi seçiyoruz. Gördüğünüz gibi ikinci satır filtre sonucu gizlendi.

Soğuk Havada Beton Dökmek

Soğuk havada beton dökümünden kasıt tam olarak nedir? Hava sıcaklığı 4°C altındaysa veya 4°C altına düşmesi bekleniyorsa, bu havayı soğuk hava olarak tanımlayabiliriz. Merak edenler için belirtelim, bu tanım ACI 306R-10, Guide to Cold Weather Concreting dökümanından alınmıştır. Bu kaynağın daha önceki revizyonunda farklı bir tanım vardı, ilgilenenler ACI 306R-88 kodlu dökümana bakabilir.

Peki bu durum bizim için neden önemlidir? Çimentonun hidrasyon reaksiyonu ısıya bağlı bir reaksiyondur ve bu reaksiyon 5°C altında durma noktasına gelir ve dahası beton karışımındaki su 0°C altında donmaya başlar. Önlem alınmadığı taktirde donma sonucu hacmi genişleyen su prizini almaya başlamış beton karışımını çatlatarak zarar vermeye başlar, beton dayanımı düşer. Ayrıca suyun donması sonucu hidrasyon için gerekli serbest su en sonunda biter ve beraberinde betonun dayanım kazanması da durur. Hava sıcaklığını beton dökeceğiniz alana asacağınız modern bir termometre ile takip etmek mümkün. İnternet üzerinde 100 TL civarına satın alabileceğiniz iç ve dış ortam için anlık, min. ve maks. hava sıcaklığını gösteren bir ürün işinizi görecektir.

Peki bu durumda ne yapılmalıdır?

İlk olarak beton karışımıyla ilgili yapabileceklerimizden bahsedelim. Karışımdaki çimento miktarını artırarak hidrasyon sonucu açığa çıkan ısıyı arttırabiliriz. Veya sıcak havada beton dökerken nasıl agregaları soğutmaya çalışıyorsak, aynı mantıkla agregaları ısıtabiliriz. Veya beton karışımı için kullandığımız suyu da ısıtabiliriz. Fakat sıcak suyla ilgili dikkat etmemiz gereken bir nokta var, bazı durumlarda çimentonun aniden aşırı sıcak suyla teması sonucu çimento topları oluşabilir. Bunu engellemek için sıcak suyun ilk olarak çimentoyla değil, agregalarla karıştırılmasında fayda var. Soğuk havada beton dökerken amacımız betonun mümkün olan en erken sürede dayanım kazanmasını sağlayarak zarar görmesini engellemek olduğundan, priz hızlandırıcı katkılar da kullanabiliriz. Bu katkılar temel olarak karışım için gerekli su miktarını azaltarak betonun erken priz almasını sağlar. Bu katkıları kullanırken tedarikçi firmanın vereceği limitler dahilinde kullanmaya dikkat etmeliyiz. Erken priz alan betonun yüzey işçiliğinin daha hızlı tamamlanması gerektiğini lütfen unutmayın. Modern beton mikserlerinde bulunan katkı tanklarında mikserin içine ne kadar katkı karıştığını kontrol etmek çok kolayken, eski model mikserlerde böyle bir imkan yoktur. Ekleyeceğimiz katkıyı beher kabı gibi bir ürün kullanarak kontrollü olarak eklemeliyiz.

İkinci olarak sahada alınabilecek tedbirlerden bahsedelim. Eğer zemin betonu döküyorsak en önemli nokta donmuş zemine beton dökmemekte. Hava sıcaklığı arttığı zaman zemin çözüleceğinden yaptığımız zemin sıkıştırması zarar görecektir. Bu durumda beton dökümüne başlamadan önce zemin tekrar sıkıştırılmalıdır. Alınabilecek diğer önlemler dökülen betonun direkt veya endirekt yöntemlerle ısıtılarak korunması, yalıtım malzemeleri kullanılarak hidrasyon sonucu açığa çıkan ısının ani kaybının engellenmesi, kapalı hacimler yaratarak rüzgar ve soğuğun azaltılması, gerekirse ısıtıcıların kullanılması olarak sayılabilir. Direkt yöntem olarak betonun elektrik ile yeterli dayanım kazanana kadar ısıtılması, buhar kazanı kullanarak ısıtma ve endirekt yöntem olarak beton dökülecek kalıbın ısıtılmasını düşünebiliriz. Kapalı alanda fosil kaynaklı yakıtla çalışan ısıtıcı kullanılması ortamdaki karbonmonoksit miktarını artıracağından zehirlenmeleri engellemek için düzenli olarak ölçüm yapılması gerekmektedir. Aynı şekilde karbonmonoksitin taze betonla tepkimeye girmesi sonucu beton yüzeyi üzerinde oluşabilecek zayıf tabaka hakkında internette bazı kaynakları bulabilirsiniz.

Sonuç olarak soğuk havada beton dökmek gerekli önlemler alındığında kesinlikle mümkündür. Dikkat etmemiz gereken yaptığımız işin ekonomik olmasıdır. Yukarıda saydığımız tüm yöntemler ek maliyet demektir fakat elimizde para olması sınırlı dünya kaynaklarını istediğimiz şekilde kullanmamızı gerektirmez. Sürdürülebilir bir dünya için o anki bütçe ve ihtiyaçlarımıza göre bir yöntem seçmeliyiz.

Excel'de Yığın Veri İle Çalışmak

Çalışmalarınızda, özellikle yığın veri içeren tablolarda işinizi kolaylaştıracak birkaç püf noktasını sizinle paylaşmak istiyorum. Bunlardan biri “subtotal” (alttoplam) fonksiyonunun, liste sonunda değil, listenin başında kullanılması. Bu sayede farklı listelemeler için toplamları direkt görebilir, listenin sonuna gitmek için uğraşmak zorunda kalmazsınız. Diğer ipucu ise “subtotal” foksiyonunun “109” foksiyon numarası ile kullanılması. Bu sayede değişik filtrelemeler için sadece filtrelenen değerlerin toplamını görmüş olacaksınız.

Örnek çalışmamızda kullanılmak üzere TUİK’nin “Yıllara Göre Dış Ticaret” verisini kullanacağız.

http://www.tuik.gov.tr/PreIstatistikTablo.do?istab_id=621

Excel dosyasını açıyoruz. 88 satır ve 8 kolon bilgi mevcut. Örneğimizde kullandığımız bu tablo göreceli olarak az satırdan oluşmakta, siz binlerce satırdan oluşan tablolarda da aynı formülleri uygulayabilirsiniz.

Dikkat ettiyseniz 4. satır ve 13. satır boş. 4. satırı filtreleme, 13. satırı da toplamlar için kullanabiliriz. Ya da tablonun başlıklarını kendi isteğinize göre tekrardan şekillendirebilirsiniz. Zaman kazanmak adına tablo başlıklarını olduğu gibi bırakıyorum.

Filtre atılması: 13A-L ile 101A-L arasını işaretleyip “Data” menüsünden “Filter”ı seçiyoruz.

Subtotal Fonksiyonu: A4 hücresine “Toplam” yazıyoruz. İhracat toplamını hesaplamak için B4 hücresine geliyoruz. “=SUBTOTAL(109;B14:B101)” fonksiyonunu tırnaklar olmadan yazıyoruz. İthalat toplamını da hesaplamak istediğim için aynı işlemi E4 hücresi için de yapıyoruz, “=SUBTOTAL(109;E14:E101)” fonksiyonunu E5 hücresine yazıyoruz.

Ek Bilgi: “Subtotal” fonksiyon numaraları 1-11 ve 101-111 arasında değişik değerler alabilir. Genel olarak bu değerlerin işlemleri, topla, ortalama al, maksimumu ver v.b. işlerdir. 1-11 arasındaki değerler “saklanmış” satılarıda hesaplamalarına katarken, 101-111 arasındaki değerler “saklanmış” satırları göz ardı eder. Saklanmış satırlar, belirli bir filtreleme sonucu veya el ile satırın saklanması ile ekranda gözükmeyen satırlardır. Bizim yukarıda kullandığımız 109 fonksiyon numarası, işaretli olan alanda saklanmamış olan satılardaki değerleri toplar.

Hücrelerdeki değerler büyük sayıların olduğundan, okumayı ve algılamayı kolaylaştırmak için binlik ayıraç ve 2 ondalık değerle gösterilmesini sağlıyorum. (“Format Cells”, “Number”, “Decimal Places” 2, “Use 1000 separators”) Değerler hücrelere sığmazsa ##### olarak gözükecektir. Kolon genişliklerini arttırarak değerleri görünür hale getirebilirsiniz.

Gördüğünüz gibi 1923-2010 arasında Türkiye’nin toplam ihracatı 1.119.778.662.370 USD (TUİK değerleri 000 USD olduğu için hesapladığımız değeri buraya yazarken 1000 ile çarptım), ithalatı ise 1.763.617.506.210 USD’ymiş.

Tablolar ile çalışırken işinizi kolaylaştıracak bir diğer özellik de çalıştığınız tabloyu, ilgili satır ve kolondan dondurmanızdır (“freze” özelliği). Aşağıya doğru uzayan tablolarda başlık satırlarını, sağa doğru uzayan tablolarda da ilgili kolonu dondurmanız tablo ile çalışmanızı kolaylaştıracaktır. Bu nedenle A14 satırına gelip, “View” menüsünden “Freeze Panes”i seçiyorum. Bu sayede A14 satırının üstündeki tüm satırlar ekranı hareket ettirsem bile ekranımda görünüyor.

Tablomuzun çalışma alt yapısını oluşturduk. Şimdi filtreler ile çalışmaya başlayalım.

1923-1929 yılları arasındaki ihracat ve ithalat değerleri için A13 hücresinden filtre okuna tıklayıp bu yılları seçiyoruz. TUİK yıl bilgisinin yanına …………… yazdığı için Excel bu hücrelerdeki veriyi sayı olarak algılamıyor. (Bu nedenle büyüktür, küçüktür veya arasında filtreleri kullanamıyoruz. Bunu kaldırmak için siz bu hücrelere el ile tekrar yıl bilgisi girebilirsiniz. Birkaç hücre girip, hücreleri aşağıda doğru çekip kopyalarsanız Excel sizin yerinize seriyi hücrelere kopyalayacaktır. Bu sayede “Numbers Filters” özelliğinden de yararlanabilirsiniz.) 1923-1929 yılları arası ihracat 576.216.000 USD ve ithalat 782.718.000 USD’ymiş. İstediğiniz yılları seçebilirsiniz, subtotal fonksiyonu filtrelenen hücreleri toplayıp size getirecektir.

Seçilen yılların toplamlarının yanı sıra ortalamalarını da görmek istiyoruz. A5 hüceresine gelip, “Ortalama” yazın. B5 hücresine “=SUBTOTAL(101;B14:B101)”, E5 hücresine ise “=SUBTOTAL(101;E14:E101)” fonksiyonlarını yazalım. Bu sayede 5. satır bize filtrelenen yılların ortalamalarını verecek.

1923-2010 arasında ihracat değişiminin negatif (-) olduğu yıllarda, ortalama ihracat ve ithalat tutarları nelermiş bir bakalım.

C13 hücresindeki filtreleme okuna tıklıyoruz. “Number Filters” , “Less Than…” seçiyoruz. Karşımıza çıkan kutucuğa “0” yazıyoruz. Toplam 26 yıl negatif büyüme gerçekleşmiş, En büyük küçülme 1958 yılında gerçekleşmiş, -%28,4 oranıyla.

Peki İhracat negatif (-) büyüme yaşarken, ithalatın (+) olduğu yıllara bir bakalım. Mevcut durumda, F13 hücresine geliyorsunuz ve filtreleme okuna tıklıyorsunuz. “Number Filters” ,”Greater Than …” seçiyoruz. Karşımıza çıkan kutucuğa “0” yazıyoruz. Toplam 10 yıl, (1929, 1948, 1955, 1963, 1968, 1975, 1977, 1979, 1983, 1989 yıllarında)  ihracatta negatif büyüme varken, ithalatta pozitif büyüme yaşanmış. Bu yıllar boyunca ortalama ihracat 2.421.729.990 USD olarak gerçekleşirken, ortalama ithalat 4.297.893.550 USD olarak gerçekleşmiş.

İşte bu şekilde, zekice hazırlanmış bir tablo ve formüller size çeşitli sorulara hızlı cevaplar vermenizi sağlayacaktır. Bir sunum veya yönetici toplantısında, anlık sorulara bu tip hazırlanmış bir tablo ile kolaylıkla cevap verebilirsiniz.

Aklın yolu birdir. O yoldan şaşmamamız dileğiyle.

Meslek mi? Görev mi?

Bu yazıda, inşaat işlerinde incelediğim organizasyon şemalarında, personel bütçelerinde, puantaj bilgilerinde ve günlük konuşmalarda anlamlarını birbirine karıştırdığımız kelimeler olan “meslek” ve “görev”den bahsetmek istiyorum. Önce sözlük anlamlarına bir bakalım;

Meslek: Belli bir eğitim ile kazanılan sistemli bilgi ve becerilere dayalı, insanlara yararlı mal üretmek, hizmet vermek ve karşılığında para kazanmak için yapılan, kuralları belirlenmiş iş.

Görev: Bir nesne veya bir kimsenin yaptığı iş.

Örnek vermek gerekirse, “inşaat mühendisliği” bir meslek, “planlama mühendisliği” ise bir görevdir. “Mühendislik” bir meslek dalı iken, kullanımlarla görev tanımının içine girmiş, “Planlama Mühendisi” adında bir görev çıkmıştır ortaya. Burada önemli olan konu size mesleğiniz sorulduğunda görevinizi söylememenizdir. 

Gelelim organizasyon şemalarına, bu şemalarda genelde yukarıdan tanımlara “görev” ile başlanır, aşağı doğru inildikçe “meslek” yazılır geçilir. Proje Koordinatörü, Proje Müdürü, Şantiye Şefi, … , Mimar, İnşaat Mühendisi, İnşaat Teknikeri … diye gider. Şemalarda yukarıdan aşağıya inildikçe “görev” tanımı kullanılmayıp, “meslek” tanımı kullanılması, bu bölümlerin işleyiş prosedürlerinin iyi çalışılmadığını gösterir. Çünkü “görev” tanımı, prosedürün ve görevlerin tanımlanmasını gerektirir. Bu tanımlamamanın gerekli olduğunun farkında olup uygulayan firma sayısı yok denecek kadar azdır.

Bütçelerde de durum aynıdır. Personel bütçelerinde, istihdam edilmesi planlanan “görev”in yanında “meslek” bilgisinin de bulunması, hem maaş tahminlerinizi kolaylaştıracaktır hem de organizasyonunuzdaki meslek dağılımlı ve oranlı istatistikler tutmanızı sağlayacaktır. Şantiyelerinizde çalışan mimar, mühendis v.b. meslek gruplarının sayısı ve dağılımı bilgisi kaliteli istatistiklerdir.

Yukarıda bahsettiğimiz durumlar projeleri sadece kendi içinde etkileyecekken, firmaların personel departmanlarında “görev” ve “meslek”in karıştırılması çok daha vahim bir durumdur. “Personel ve Özlük İşleri” birimlerinin bu farkındalığı edinmemiş olduğu firmalarda, diğer detaylardan bahsetmek birazcık boşa kürek çekmektir.

Güzel yurdumda herkesin istediği, yatkın olduğu mesleklerde eğitim görmesi ve hayal etkileri görevlerde çalışması dileğiyle. (O kadar kolay değil, daha çok çalışmamız gerek çok!)