Ç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.
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.
"TUİK yıl bilgisinin yanına …………… yazdığı için Excel bu hücrelerdeki veriyi sayı olarak algılamıyor."
YanıtlaSilErdem şef, yılların tamamını seçip CTRL+F, bul ve değiştir, komutunda "aranana" . yazıp "yeni değeri" boş bıraktıktan sonra, excel olayı anlıyor ve hücre üzerinde biraz bekleyince "sayıya dönüştür" seçeneği beliriyor. Hücre başında yeşil uyarı sembolü çıkanları seçip sayıya dönüştürdükten sonra sayı filtrelemede aktif oluyor.
Sadece 2000, 2001 ve 2002 yıllarında sorun var, sebebi de bu 3 hücre yazılırken başına tırnak konmaması gibi geldi, diğer tüm yıllarda tırnak var. CTRL+F bu 3 hücrede değişiklik yapmıyor, neden bilmiyorum.
Sıkıntıdan denedim olacak mı diye, yoksa senin çözümün baya bir zaman kazandırıyor :))
Okan G.