Şefin Special’i: CTRL+ENTER & ÖZEL GİT

Rutin olarak aldığınız tablolarda boş hücreler mi çıkıyor? Satırları silmeye kalkışsanız aynı satırın diğer sütunlarında bilgileriniz mevcut. Sütunu da silemezsiniz. Halbuki boş yerine “Yok”,”Kullanılmamaktadır.” “Hesaplanmamıştır.” gibi cümleler yazılabilir. Sakinlikten gerginliğe geçmek için gereken süre oldukça kısa değil mi? Bu boş hücreler adeta bir virüs gibi tablonuzu/listenizi etki altına alıp sizi çileden çıkartabiliyor. Çileden çıkmak kolay, gelin biz bu durumu nasıl toparlarız ona bakalım…

Oluşturulan veya alınan her listenin/tablonun tüm hücrelerinde bir değer olmayabiliyor. Bunun yerine oraya durumu belirten bir metin yazabilsek ne güzel olur. Bu durumla başa çıkabilmek için ileri excel eğitimine yakışan güzel bir çözüm var. Örneğimizle birlikte çözümümüze bir göz atalım.

Elimizde çeşitli ürünlere dair yıllık satış miktarları mevcut. Bazı ürünlerin bazı yıllarda satışlarına dair hücre değerleri boş. Bu boş hücrelere “Yok” yazmak isteniyor.

Düşeyara gibi popüler değil ama bilenlerin işine defalarca yaramış ve köşede kalmış, ilginizi bekleyen bir komut penceresi var: Özel Git. Bu komut penceresinden açıklamalara, formüllere, hatalı hücrelere, boşluklara vs. gibi değerli hücrelere erişebilirsiniz. Bizde burada Giriş sekmesinde bulunan Düzenleme veri grubunda Bul ve Git seçeneklerinden “Özel Git” komutu seçelim.

“Özel Git” penceresinden Boşluklar’ı seçiyoruz. Excel, otomatik olarak alanınızı algılayıp alanınız içinde kalan boşlukları seçebiliyor. Eğer boşlukları seçme işlemini yapamazsa önce içinde boşluklarınızın seçilmesini istediğiniz alanı seçip sonrasında bu komutu uygulayabilirsiniz.

Bu adımdan sonra hiçbir tıklama işlemi yapmadan “Yok” yazalım. “Yok” kelimesini seçilen ilk hücreye yazacaktır. Yazacağımız metin bittiğinde CTRL+ENTER kombinasyonu yapalım. Bu şekilde istediğimiz metin tablodaki tüm boş hücrelere yazılmış oldu.

Bu şekilde gerçekleşen çözümümüzde rastgele boş hücreleri seçmenin zorluğunu atladık ve hepsine aynı anda veri girişi sağlayabildik. Bu kombinasyonu keyifli kullanmalar dilerim.

Bir sonraki makalemizde görüşmek üzere, hoşçakalın.

Veri Doğrulama Yaparken Formülleri Kullanın

Veri doğrulama, bir hücreye veya seçtiğiniz aralığa girilen verilerle ilgili kısıtlamaları tanımlamak için kullanılan Excel özelliğidir. Veri Doğrulama ekranında liste, tarih, saat, metin uzunluğu gibi hazır bir şekilde kullanıma hazır doğrulama ölçütleri bulunmaktadır. Bu hazır doğrulama ölçütlerinden sadece bir tanesi hücreye uygulanabilmektedir. Birden fazla ya da hazır bulunmayan ölçüte göre veri doğrulama yapabilmek için “Özel” ölçütüne formül yazmak gerekir.

Şimdi İleri Excel Eğitimi konularımızdan biri olan Veri doğrulamada formül kullanımını T.C. Kimlik numarasının 11 haneli ve sadece sayı olacak şekilde girişine izin verildiği bir örnek üzerinden inceleyelim. Birden fazla ölçüt gerektiği için özel bir formül olan VE formülünün içinde UZUNLUK ve  ESAYIYSA formüllerini birlikte kullanmamız gerekiyor.

Öncelikle veri doğrulama uygulamak istediğimiz hücreyi seçtikten sonra Veri sekmesinin altında bulunan Veri Araçları grubundan Veri Doğrulama seçilir. Daha sonra açılan penceredeki Ayarlar bölümünden Doğrulama Ölçütü olarak Özel seçilip formül alanına uygun olan =VE(UZUNLUK(A2)=11;ESAYIYSA(A2)) yazılır.

 

Hücre üzerine gelindiğinde Girdi İletisi uyarısı ve hatalı veri girildiğinde Hata Uyarısı verilmek istenirse uygun alanlara gerekli bilgiler girilir. Girdi İletisi için ileti başlığı ve girdi iletisi alanları; Hata Uyarısı için uyarı stili, başlığı ve iletisi girilmelidir.

 

Tüm bunları bir hücreye uyguladıktan sonra diğer hücrelere uygulamak için ise hücreyi sağ alt köşesinden tutup aşağı doğru çektiğinizde diğer hücrelere de bu veri doğrulama işlemleri otomatik olarak uygulanacaktır. Artık hücrelerinize veri girişlerini daha kontrollü ve düzenli bir şekilde yapabilirsiniz.

Başka bir makalede görüşmek üzere…

 

Düşeyarasız Yapamayanlara: Birden Çok Koşula Göre Düşeyara!

Düşeyara’yı sevmeyen var mı? Sonu gelmeyen listelerde Düşeyara fonksiyonuyla istediğimiz değerleri anında bulabildik. O zaman Excel’i kullanmaktan ne kadar da keyif alıyoruz! Peki, aynı anda birden çok koşula göre değer atama yapılması gereken durumlarda da Düşeyara’yı kullanabilir miyiz? Örneğin, tarihlere ve kişilere göre yapılan satışları bulmak istediğinizde? Cevap: Evet, kullanabiliriz!

Düşeyara, çalışma mantığı gereği tek bir hücreyi alarak belirtilen sütunda arama işlemi yapar. Aranacak tabloda sağa doğru arama yapabilir, solundaki değerleri bulamaz. Eğer Düşeyara tek bir hücreyi alarak arama yapıyorsa bizim çok kritere göre aramamızda Düşeyara’yı nasıl kullanacağız? Aslında bu işlemi Düşeyara’nın huyuna suyuna gitmek olarak adlandırabiliriz. Hadi konumuzu örneğimizle açıklığa kavuşturalım.

Elimizde ad, soyad ve unvanların olduğu bir listemiz mevcut. Bu listede yapılmak istenen ada göre unvanı getirmesi ancak burada bir problem var ki aynı adda çalışanlar mevcut. Bu listemiz için Düşeyara yapmaya karar verdik çünkü hepsine tek tek girmek oldukça manasız.

Düşeyara yapmaya karar verdik ancak bir problem var: Düşeyara tek bir hücreye göre arama yapabilir, bizim koşulumuz ise ad ve soyad olarak iki hücrede bulunuyor. Böyle bir durumda Düşeyara fonksiyonu yazmanın önüne bir adım ekleyerek fonksiyonumuza göre verilerimizi  düzenlemiş olacağız. Bu adımımız, koşullarımızı tek bir sütunda toplamak oluyor. Örneğimizde ad ve soyad değerlerini tek bir sütunda birleştireceğiz. Birleştirme işlemini; “&” işareti, Birleştir fonksiyonu ya da Metinbirleştir fonksiyonu ile yapabilirsiniz.

Bu işlem sonucunda listemiz Düşeyara kullanabileceğimiz duruma gelmiş oldu. Artık o bildiğimiz ve sevdiğimiz Düşeyara’yı yapmaya devam edebiliriz.

Excel’de elimizde bulunan her liste her zaman Düşeyara yapmamıza olanak vermiyor olabilir. Koşul sayısı dışında Düşeyara yapabilmemizi kısıtlayan bir durum söz konusu değilse Düşeyara kullanmak için koşullar tek bir sütunda toplanabilir. Biz de bu makalemizde Düşeyara kullanımına uygun olmayan listemizi nasıl uygun hale getirebileceğimiz üzerine konuştuk.

Bir sonraki makalemizde görüşmek dileğiyle,hoşçakalın.

Son Dakikada Son Tarihin Bugün Olduğunu Öğrenip Panik Olmaya Son Verecek Kombinasyon: İç İçe Eğer & Bugün

Eğer fonksiyonun mantığına baktığımızda değerlendirilmesi gereken bir koşul vardır. Bu koşulun sağlanması ve sağlanamaması durumunda bu fonksiyon kullanılarak farklı seçenekler döndürülür. Örneğin; satış listenizde eğer satılan ürün kısa çorapsa fiyatına 3 ₺ yazdıralım, kısa çorap değilse fiyatını 4 ₺ olarak yazdıralım. Peki çorapta bu tercihler kullanılsın ama ya çorapların yanında V yakalı kazaklara 60 ₺; diğer kazaklara da 55 ₺ değer belirlemek istersek ne olur? Buradaki can simidimiz iç içe eğer olacaktır. Bunların yanında ürünlerin mağazaya geleceği güne göre listede çeşitli talimatlar belirtmek istersek nasıl bir yol izlemeliyiz? İşte bu makalemizde bu tip durumlar için kullanabileceğimiz iç içe eğer ve bugün fonksiyonunu konuşacağız.

Elimizde kişilere göre ödeme miktarları ve son ödeme tarihlerinin olduğu bir listemiz mevcut. Bugünün tarihini de H1 hücresine BUGÜN() fonksiyonunu yazarak elde ettik.

İstediğimiz durumları, C2 hücresini temel alarak şu şekilde yazabiliriz:

Eğer C2 hücresindeki tarih bugün ise  “Bugün ödenmeli” yazsın.

Eğer C2 hücresindeki tarih bugünden eski bir tarihse “Ödendi” yazsın.

Eğer C2 hücresindeki tarih gelecek bir tarih ise “Ödenecek” yazsın.

Bu koşulların hepsini tek bir hücrede yazabilmek için iç içe eğer ve bugün fonksiyonlarını birlikte yazmamız gerekiyor. BUGÜN() fonksiyonu, bulunduğumuz günün tarih formatını bize verir. Formül yazımına geçtiğimizde hangi koşuldan başlamak istersek başlayabiliriz.

Excel, tarihleri de arka planında sayı olarak tuttuğundan dolayı tarihlerle toplama, çıkarma yapılabilir; mantıksal operatörlerle beraber kullanılabilir. Bu sebeple tarihleri geçmiş ve gelecek tarih olarak sınıflandırabilmemizi kolaylaştırır.

Formülü durum sütununa uyguladığımızda, sütunda, istediğimiz ifadeleri hızlıca elde ettik. En güzel kısmı, Excel, BUGÜN() fonksiyonu ile tarih bilgisini verirken bilgisayarın sistem bilgilerinden yararlandığı için her gün buradaki durumlar güncellenecek. Bu da her gün aynı işlemleri yapmak, her gün yeniden listeler oluşturmak gibi büyük zaman kayıplarının önüne geçilebileceği anlamına gelir.

İç içe eğer fonksiyonu ile birçok fonksiyonu kombine edebilirsiniz. Böylelikle iç içe eğer fonksiyonunu daha kullanışlı hale dönüştürür ve işlerinizi daha verimli şekilde halledebilirsiniz.

Bir dahaki makaleye kadar hoşça kalın.

Metin, Sayı ve Tarihlerinizi Kolayca Filtreleyin!

Verileri filtreleme; verilerin daha anlamlı olması, istenilen verilerin kolayca bulunup düzenlenmesi ve neticesinde daha etkili kararlar alınmasına yardımcı olur. Bir veya daha fazla sütuna filtreleme işlemi uygulayabilirsiniz. Bir filtreleme sadece görmek istediklerinizi değil görmek istemediklerinizi de denetler. Verileri filtrelediğinizde, veriler filtre ölçütüyle eşleşmediğinde satırların tamamı gizlenir. Aynı zamanda sayısal ve metin değerlerini filtreleyebilir veya arka planına ya da metnine renk biçimlendirmesi uygulanmış hücreleri rengine göre filtreleyebilirsiniz.

Şimdi Excel İleri Eğitimi konularımızdan biri olan metin, sayı, tarih filtreleme işlemini; müşteri ve satış bilgilerinin olduğu bir listede Satış Bölgesi İstanbul, Satış 10000-15000 tl arası ve Tarih 1.1.2010 sonrası olacak şekilde bir filtreleme uygulamasını birlikte yapalım.

Öncelikle filtreleme işlemi uygulamak için listedeki herhangi bir hücreyi tıkladıktan sonra Veri sekmesinin altında bulunan Sırala ve Filtre Uygula grubundan Filtreleye tıklıyoruz. Sütun başlıklarının yanında filtreleme işlemlerini gerçekleştirmek üzere işaretler belirecektir. Satış  Bölgesini sadece İstanbul olarak filtrelemek için sütundaki işarete tıklayıp filtreleme ekranını açıyoruz. Bu ekranda tüm satış bölgeleri seçili olarak gelecektir. Tümünü seç işaretini kaldırıp ister listeden sadece İstanbul’u seçebilir isterseniz ara alanından İstanbul’u aratarak seçiminizi yapabilirsiniz.

 

Satış sütununa 10000-15000 tl arası bir filtre uygulamak için sütundaki işarete tıklayıp filtreleme ekranından Sayı Filtreleri ve ardından Arasında seçilir. Açılan Özel Otomatik Filtrele ekranında 10000’den büyük 15000’den küçük ayarlamalarını yaptıktan sonra İstanbul bölgesindeki 10000-15000 arası olan satışlar filtrelenecektir.

 

Tarih sütununa 1.1.2010 sonrası olacak şekilde bir filtre uygulamak için sütundaki işarete tıklayıp filtreleme ekranından Tarih Filtreleri ve ardından Sonra seçilir. Açılan Özel Otomatik Filtrele ekranında filtre ölçütü olarak 1.1.2010  yazıldıktan sonra İstanbul bölgesindeki 10000-15000 tl arası olan satışlardan 1.1.2010 sonrası olanlar filtrelenecektir.

 

Artık metin, sayı ve tarih verilerinizi kolayca bulup düzenleyebilir ve neticesinde daha etkili kararlar alabilirsiniz.

Başka bir makalede görüşmek üzere…

Eğerhata İle Hataları Dize Getirin

Yöneticinize sunacağınız dokümanda işlemlerin sonucunda Excel #YOK, #DEĞER!, #BAŞV!, #SAY/0!, #SAY!, #AD? veya #BOŞ! hataları mı veriyor? Belki bir tane olunca görmezden gelinebilir ancak göz ardı edilemeyecek kadar çok olduğunu düşünün. Bu rahatsızlık veren durumdan Eğerhata fonksiyonuyla nasıl kurtulabileceğimizi birlikte görelim.

Elimizde satış toplamı, satış adedi ve ortalama fiyatın olduğu bir listemiz var. Bu listede hiç satılmayan bir ürün mevcut.


Satış toplamını satış adedine bölerek ürünlerin ortalama fiyatlarını elde etmek istiyoruz. Basit bir matematiksel operatörle bunu halledebiliriz. Ancak bu işlemin sonucunda ürün adedi sıfır olan B7 hücresinin satış toplamına bölümünde matematiksel işlem olarak 0’a bölme işlemi gerçekleşemeyeceği için C7 hücresinde #SAY/0! hatası verdi. Hücredeki hata türüne göre kullanılabilecek mantıksal fonksiyonlar değişse de biz burada en temel hata işleme fonksiyonu olan Eğerhata işlevi ile bu problemi çözeceğiz.

Eğerhata işlevi formüldeki hataları yakalamayı ve bu hatalar için istenen ifadelerin yazılmasını sağlar. Bir formül bir hata sonucu döndürürse belirttiğimiz sayısal veya metinsel değeri verir; aksi takdirde, formülün sonucunu verir.

Burada yaptığımız işlemi Eğerhata fonksiyonunun içine alıp bu şekilde işlemi yaptıralım. İşlem sonucu bir hata ifadesi belirecekse bu hatanın yerine istediğimiz ifade yazsın. Örneğimizdeki durum için ortalama fiyat sütununda bir hata mevcutsa bu hataların yerine 0 yazmasını istiyoruz.

Görüldüğü gibi artık hata ibarelerinden kurtulup daha uygun gösterime sahip bir dokümanımız oldu.

Eğerhata fonksiyonu, fonksiyonlardan en çok Ehatalıysa fonksiyonu ile karşılaştırılarak anılır. Bu noktada belirtilmesi gereken Ehatalıysa fonksiyonun Eğerhata fonksiyonundan farklı olarak sadece hata denetimi yaptığıdır. Eğerhata gibi kullanmak isteniyorsa başına ekstra bir eğer formülü girilmelidir.

Hataya özel hata işlevini gerçekleştiren başka fonksiyonlar da vardır. Eğerhata fonksiyonu bunların en geniş çaplı olanıdır. Her türlü hata için bir işlem döndürür. Gönül ister ki Excel’deki tüm işlemleriniz hatasız olsun ama eğer çeşitli hatalarla karşılaşıyor olursanız bu fonksiyonu kullanabilirsiniz.

Bir sonraki makalemizde görüşmek üzere.

 

Hızlı Doldur (Flash Fill)

Bu makalemizde sizlere Excel eğitimlerimizde keyifle anlattığımız bir özellik olan Hızlı Doldurma özelliğinden bahsedeceğim.

Hızlı doldurma; Excel 2013 ile gelen, hızlı veri girişini sağlamak için geliştirilmiş oldukça başarılı bir çözümdür. Bu özellik sayesinde Excel’e veri girişi yapılırken hücrelerde var olan verilerde bir düzen algıladığında, Excel geriye kalan hücreleri otomatik doldurur ve kolayca veri girişi sağlanmış olur.

Hızlı doldurma özelliği ağırlıklı olarak metinsel ifadeleri ayrıştırma, birleştirme gibi işlemlerde kullanılır. Çeşitli özellikler kullanılarak çok basit bir verinin ayrıştırılmasının yanı sıra çok karmaşık formüllerle ayrıştırılabilen veya ayrıştırılması formüllerle de çok zor olan bir metinsel ifade de istediğimiz kısımları kolaylıkla ayırabildiğimiz bir yöntemdir.

Metin fonksiyonlarıyla yapılan; bir kelimenin ilk harfi, ilk üç harfi ya da her kelimenin üçüncü karakteri vb. metin verilerinin parçalanmasına ilişkin bir kalıbı algılayıp uygulamayı sağlar.

Tipik kalıplar içinde metin parçalarını tanımak, telefon numaraları ve tarih verisinin parçalarını tanımak gibi uygulamalar vardır.

Excel’in 2013 sürümü ile hayatımıza giren Hızlı Doldur özelliğini Excel’de birkaç farklı yerde bulunur.

  1. Excel’de Veri sekmesi‘nin Veri Araçları grubunda görebilirsiniz.

  1. Giriş sekmesinin Düzenle grubunda Doldur özelliğinin altında da bulabilirsiniz.

  1. Bir hücreyi çoğaltmak için hücrenin sağ alt kısmındaki noktadan aşağı doğru çektiğimizde doldurma seçenekleri olarak Hızlı Doldurma’yı görebilirsiniz.

  1. Hızlı Doldur özelliğini Ctrl+E kısayolu ile kullanabilirsiniz.

Hızlı Doldurma varsayılan olarak açıktır ve bir düzen algıladığında verilerinizi otomatik olarak doldurur. Eğer beklendiği gibi çalışmazsa, Hızlı Doldurma’nın açık olup olmadığını aşağıdaki şekilde kontrol edebilirsiniz.

  1. Dosya > Seçenekler‘i tıklatın.
  2. Gelişmiş‘i tıklatın ve Otomatik Olarak Hızlı Doldur kutusunun işaretli olduğundan emin olun.

  3. Tamam‘ı tıklatın ve çalışma kitabınızı yeniden başlatın.

Bir örnekle özelliğimizi detaylıca görelim.

Örnek 1: Aşağıdaki tabloda tek sütunda yazılan Ad ve Soyadları ayrı ayrı sütunlarda gösterelim.

Yapacağımız işlem Adı Sütununa ilk satırına listenin ilk kaydı olan Tarık ismini yazıyoruz ve imlecimiz Tarık yazan hücrenin üzerinde iken Veri Sekmesi Veri Araçları grubuna gidip Hızlı Doldur özelliğine tıklıyoruz veya Ctrl+E kısayol tuşlarına basıyoruz.

Evet hepsi bu kadar başka bir işlem yapmanıza gerek yok sonuç aşağıdaki gibi olacaktır.

Aşağıdaki gif’de birçok farklı senaryoda Hızlı Doldur özelliği ile verilerimizi hızlıca nasıl düzenlediğimizi görebilirsiniz.

Başka bir makalede görüşmek üzere hoşça kalın.

Birleştir (Consolidate)

Farklı Sayfalardaki Verileri Birkaç Tıklama İle Hızlıca Birleştirin!

Bu makalemde İleri Excel eğitimlerimizde anlattığımız konulardan biri olan farklı sayfalardaki verileri tek bir sayfada tek kalemde birleştirmeye yarayan Birleştir özelliğimizden bahsedeceğim.

Birleştir özelliği ile bir Excel belgesinin bir sayfasında bulunan verileri ya da farklı sayfaları içinde bulunan aynı sütun isimlerine sahip olan verileri tek kalemde istediğimiz aritmetiksel işlemi (Toplama, Ortalama, En büyük, Say, Standart Sapma vb.) uygulayarak birleştirebiliriz.

Bu özellik var olan verileri kayıt kayıt alt alta birleştirmeye yaramaz aynı sütuna sahip olan birden çok kaydı tek kalemde ilgili matematiksel işlemi yaparak birleştirmeye yarar.

Bu işlemin fonksiyonlar ile yapılması oldukça zahmetli olduğundan ve verilerde çok fazla fonksiyon olacağından çok kullanışlı değildir bu yüzden Birleştir özelliğini kullanmak çok pratik ve hızlı bir çözümdür.

Birleştir özelliği Veri sekmesinin Veri Araçları grubu içinde bulunur.

Aşağıdaki Excel belgesinde Ocak ayından Haziran ayına kadar olan farklı sayfalardaki A:B aralığındaki verileri Birleştir isimli sayfada tek kalemde birleştirilmek istenmektedir. Bu veriler Satış Temsilcisi ve Satış bilgilerinden oluşmaktadır. Satış Temsilcisi alanı isimleri, Satış değeri ise bu isimlerin yapmış oldukları Satış değerlerini göstermektedir.

Yukarıdaki şekilde de görüldüğü üzere her sayfada aynı türden farklı isim ve Satış değerleri vardır. Bizim amacımız bu sayfalardaki verilerinin hepsini Birleştir isimli sayfada ortak Satış temsilcilerinin adlarını sadece bir kez yazılmasını sağlayarak Satış alanındaki değerleri toplamak veya başka bir matematiksel işlem yapmak olacaktır olacaktır.(Ör. Ortalama, Standat Sapma vb.)

Birleştir Özelliğini Kullanmak:

  1. İlk etapta Birleştir isimli sayfaya geliriz ve A1 hücresine tıklarız.
  2. Daha sonra Veri sekmesinin Veri araçları grubunda Birleştir özelliğimize tıklarız ve aşağıdaki pencerenin açılmasını sağlarız.

  3. Bu pencerede Birleştirme işlemin yaptığımızda uygulamak istediğimiz matematiksel işlemi İşlev kısmından seçeriz.
  4. Daha sonra Başvuru yazan kısma bir kere tıklar ve eğer işlem yapılacak sayfalar bu Excel belgesi içinde ise ki bizim Ocak ayından Haziran ayına kadar tüm sayfalarımız bu belge içinde olduğundan ilk Ocak isimli sayfaya gidip tüm A:B aralığındaki veriyi seçeriz ve Ekle düğmesine tıklayarak sırayla tüm sayfalar için aynı işlemi tekrarlayarak tüm sayfaların Tüm
    Başvurular yazan yere eklenmesini sağlarız. Eğer birleştirme işlemi yapmak istediğimiz sayfalar başka belgeler içinde ise Gözalt diyerek ilgili belgelerin konumlarını gösteririz ve gerekli alanları taratırız.

    Eğer verinin Başlıkları varsa onların çıkması için Üst Satır kutucuğu tıklanır.

    Eğer Satış toplamları yanında Satışı kimlerin yaptığı görüntülenmek isteniyorsa Sol Sütun da seçilir. Ayrıca bu verilere bağlantı yapılmak istenirse Kaynak Veriye Bağlantı Oluştur kutucuğuna tıklanır. Yalnız eğer birleştirilecek veriler aynı sayfa içinde ise Kaynak Veriye Bağlantı Oluştur kutucuğu seçilmez. Ben Üst Satır ve Sol Sütun gelsin istediğim için tıklıyorum.


    Tamam düğmesine tıkladığımda birleştir sayfasında aynı isme Sahip tüm temsilcilerin tek kalemde tüm aylardaki Satış toplamlarını aşağıdaki görebiliriz.

    İşlem olarak topla seçtiğimiz için her temsilcinin tüm aylardaki sayfalardan Satış tutarlarını toplattık. Bu işlemi Etopla fonksiyonu ile de gerçekleştirebiliriz ama bu kadar pratik değildir.

    Eğer Kaynak Veriye Bağlantı Oluştur kutucuğuna tıklayıp Tamam düğmesine tıklasaydık ekran görüntüsü aşağıdaki gibi olacaktır.

    Aşağıdaki gif’de işlemin nasıl yapıldığını görebilirsiniz.

    Örnek 2:

    Aşağıda ekran görüntüsü verilen TÜM YILLAR isimli sayfada Ay ve Gün bilgileri olan bir çapraz tablo vardır. Ayrıca 2015, 2016 ve 2017 yılları bulunan sayfalarda ay ve gün bazlı satışlar bulunmaktadır. Amacımız bu tablolardaki değerleri TÜM YILLAR sayfasında birleştirmek.

Aşağıdaki gif’de bu üç yıldaki verileri TÜM YILLAR isimli sayfada nasıl birleştirdiğimizi görebilirsiniz.

Başka bir makalede görüşmek üzere hoşça kalın.

PivotTable Hesaplanmış Alan Ekleme

Merhaba,

Bu makalemizde İleri Excel eğitimizde en çok üstünde durduğumuz konu olan PivotTable’a nasıl Hesaplanmış Alan (Calculated Field) ekliyoruz hep birlikte örnekleriyle birlikte görelim.

Hesaplanmış Alan/Calculated Field Ekleme

Excel’de ana veride var olan alanları kullanarak yeni alanları hesaplamak mümkün. Bunun için PivotTable’nın Hesaplanmış Alan Ekleme özelliğini kullanıyoruz. Hesaplanmış alan ekleyerek ana veride yeni hesaplamalar yapmadan PivotTable üzerinde sanal olarak matematiksel işlemleri kolaylıkla yapabileceğimiz alanları ekleyebiliyoruz.

Hemen konuyu daha derinleştirmek için örneğimize başlayalım.

Aşağıdaki tablodan bir PivotTable yapıyoruz.


Ürün bilgisi Satır alanına Satış Tutarı bilgisi de Değerler alanına atıyoruz.


Aşağıdaki gibi bir Pivot elde ettik.


Yukarıdaki PivotTable’da bizim yapmak istediğimiz hesaplama ise Satış Tutarının %18’ni hesaplamak. Bunun için ana veride Satış Tutarının %18’i hesaplanmamış, bu işlemi PivotTable üzerinden yaparak ana veride herhangi bir veri büyümesinin de önüne geçmiş oluyoruz.

Özet Tablo üzerinde iken menüde aktif olan PivotTable araçlarına/PivotTable Tools gidiyoruz ve Çözümle sekmesinin Hesaplamlar grubundan Alanlar/Öğeler ve Kümeler yazan yerden Hesaplanmış Alan/Calculated Field komutuna tıklıyoruz.


Hesaplanmış Alan komutuna tıklayınca aşağıdaki gibi Hesaplanmış Alan Ekle penceresi açılıyor.


Açılan bu pencerede Ad yazan yere alanımıza vermek istediğimiz ismi giriyoruz. Biz KDV adını vereceğiz.

Formül yazan yere de 0 değerini silip Satış Tutarı ifadesine çift tıklayarak bu değerin burada yazılmasını sağlıyoruz daha sonra bu ifadeyi 0,18 değeri ile çarpıyoruz ve Ekle düğmesine tıklayarak alanımızı ekliyoruz.


KDV baslığı adında bir hesaplanmış alan eklenmiş oldu.

Ve PivotTable aşağıdaki gibi hesaplanmış alanımızı kullanabiliyoruz.


Yapılan işlemi aşağıdaki gif’den detaylıca izleyebilirsiniz.

Hesaplanmış alanda formülü güncelleme:

1. Ayni şekilde PivotTable araçlarına/PivotTable Tools gidelim ve seçenekler tabından Hesaplanmış Alan/Calculated Field komutuna tıklayalım.

2. Ad listesinden düzenlemek istediğimiz hesaplanmış alan adını seçelim.

3. Değişiklikleri yaptıktan sonra Değiştir tuşuna ardından da Tamam’a
basalım.


KDV alanını seçtikten sonra aşağıdaki gibi formül gelecektir. Formülü %8 olarak güncelledikten sonra Değiştir düğmesine tıklayarak formülü değiştirmiş olduk.


Hesaplanmış alanı silme:

1.Ayni şekilde PivotTable Araçlarına/PivotTable Tools gidelim ve seçenekler tabından Hesaplanmış Alan/Calculated Field komutuna tıklayalım.

2.Ad listesinden silmek istediğimiz hesaplanmış alan adını seçelim.

3. Sil komutuna tıklayalım.

Görüldüğü gibi PivotTable ile ana veride olmadığı halde PivotTable ile yeni alanlar oluşturmak çok kolay bir işlem. Bu sayede Pivot’u yaptığımız tablodaki verilerimiz hiç büyümeden daha karmaşık hesaplamaları kolaylıkla yapabileceğiz.

Başka bir makalede görüşmek üzere hoşça kalın.

Ufkunuzu 2 Katına Çıkarma Vaadi: Düşeyara’yı [aralık_bak]= 1 Yazarak Kullanmayı Öğrenin!

Belirtilmesi gereken aralıklar için uzun uzun İç İçe Eğerler yazmaktan sıkılıp Düşeyara kullanmayı denediniz mi?

İK departmanında çalışan biri olarak kişilerin çalıştıkları yıllara göre izin günleri sayısını bulma görevi size kalmış olabilir. Ürünleri belirli aralıklara göre sınıflandırmanız istenmiş olabilir. Sizden önce gelenler belki de bunu Doğu Ekspresi yolu kadar uzun iç içe eğerlerle çözüyorlardı. Buna karşılık siz “Yok mu bunun daha pratik, hızlı bir yolu?” dediyseniz, bizdensiniz! Pratik bir yolu var gerçekten, gelin birlikte bakalım.

Düşeyara’yı bu ana kadar bir değerin aynısını listede bulmak için mi kullandınız? Zaten metin arıyorsanız böyle kullanmak doğru bir yaklaşımdır. Peki sayılara, belirlenen aralıklarlara göre istenen değerleri atamak için Düşeyara kullanabileceğimizi biliyor muydunuz? Gelin bu özelliğe bir örnekle değinelim.

Elimizde araç parçalarına göre arıza gerçekleşme oranlarının olduğu bir listemiz var. Bu listemizdeki oranların sağ tarafta gösterilen risk grubu aralıklarına göre adlandırılması ve daha sonra bir sorgulama alanında yazılan değerin hangi risk grubuna ait olduğu bulunması isteniyor.

İkinci adımda Düşeyara yapabileceğimiz aklımıza hemen gelmiştir. İlk adım için listeye Risk Grubu sütunu eklediğimizde bu sütundaki verileri oluşturmak için iç içe eğer mi yazacağız? Vakti verimli kullanmak adına bu işi Düşeyara yazarak hiç yıpranmadan halledebiliriz.

Düşeyara’da aralık bak kısmına 1 yazdığımızda Düşeyara yaklaşık eşleme yapacaktır. Değerin aynısını bulamasa bile, çalışma mantığı gereği, ona en yakın taban değerine karşılık gelen aralığa ait kategoriyi getirecektir. Burada önemli olan ölçütleri Düşeyara fonksiyonunu kullanabilecek görünüme getirmek ve taban değerlerini küçükten büyüğe sıralamaktır. Sıralama yaparak değerlerin başka bir aralığa gitmesini engellemiş oluruz.

Ölçütlerimizi tekrar düzenleyerek bu problemimizi çözmeye başlayalım. Grup tabanları başlığı altına grupların değer aralıklarının tabanlarını yazalım. Bu taban değerlerini Düşeyara yapabilmemiz için risk grupları başlığının sol tarafına yazmamız gerekir. Bu sayede taban değerine bakarak oranı bir risk grubuna atayabilir. Son olarak sıralama işlemini yaparız.

Başlangıç olarak fonksiyonu C3 hücresine yazalım. Aradığımız değer parçanın arıza oranı yani b3 hücresi, aradığımız tablo kategori ve grup tabanlarının olduğu tablo, değerini yazacağı alan risk gruplarının yazılı olduğu 2.sütun ve en önemli kısım aralık bak kısmı 1 veya doğru. Bu fonksiyonu aşağıya çektiğimizde her oranın bir risk grubuna ait olduğunu görebilirsiniz. İkinci adım için zaten her zamanki gibi düşey ara yapacağımızı yukarıda söylemiştik.

Ne kadar kolay olduğunu fark ettiniz mi? Artık bu yöntemi kullanmayı seçtiğinizde ölçüt listenizi tamamen silmediğiniz müddetçe tekrar fonksiyonları düzenlemek zorunda kalmayacak bu şekilde fazladan efor sarf etmenize gerek kalmamış olacak. Şimdi artan zaman sizin!

Bir sonraki makalemizde görüşünceye kadar hoşça kalın.