Genel Bilgiler / Uygulamalar ve Örnekler 13 Kasım 2018

Elimizde sıralanmış birden fazla başlığa sahip bir tablo olduğunu varsayalım. Bu tabloyu gerek arama gerek grafik vs. için daha küçük parçalara bölmek istiyorsak bunu nasıl yapabiliriz? Tabii ki özet tablo gibi bir aracımız var ancak ben bunu formüller kullanarak daha farklı bir yöntemle yapacağım. Burada en önemli husus verilerin sıralanmış olması, yani birbirinin aynısı olan girdilerin alt alta olması. Her zaman olduğu gibi önce tablomuz:

MüşteriÜrünOca.16žub.16Mar.16Nis.16May.16Haz.16Tem.16A§u.16Eyl.16Eki.16Kas.16Ara.16
ABC MühendislikÜrün A101112131415161718192021
ABC MühendislikÜrün B151617181920212223242526
ABC MühendislikÜrün C202122232425262728293031
ABC MühendislikÜrün D252627282930313233343536
ABC MühendislikÜrün E303132333435363738394041
CDE MakSanÜrün A404142434445464748495051
CDE MakSanÜrün B454647484950515253545556
CDE MakSanÜrün C505152535455565758596061
CDE MakSanÜrün D555657585960616263646566
CDE MakSanÜrün E606162636465666768697071
3R TeknikÜrün A656667686970717273747576
3R TeknikÜrün B707172737475767778798081
3R TeknikÜrün C757677787980818283848586
3R TeknikÜrün D808182838485868788899091
3R TeknikÜrün E858687888990919293949596

Şimdi bu tablomuzu önce müşteriye göre bir alt tabloya sonra da bu müşterinin aldığı belli bir ürüne göre ikinci bir tabloya daha ayırmak isteyelim. Aşağıdaki resimde ne yapmak istediğimi gösterdim:

Alt Tablo Çıkarma Adımları
Hazırlık

Öncelikle ben bu örnekte Excel Tablo ve değişken aralıkları kullanmayacağım. Ama siz isterseniz bu özellikleri kullanarak tabloya yeni eklenen verilere göre arama yaptığımız aralığın otomatik olarak genişlemesini sağlayabilirsiniz. Tek dikkat etmeniz gereken veri ekledikten sonra tabloyu sıralamayı unutmamak.

İkinci olarak ben ben yukarıdaki tabloyu B2 hücresinden itibaren yapıştırdım. Siz farklı bir aralık kullandı iseniz formüldeki ilgili adresleri kendi tablonuzdaki adresler ile değiştirin.

Üçüncü olarak ben alt tabloları belirlemekte kullanacağım ikinci bir mini tablo hazırladım, resimde alta gördüğünüz tablo. Bu tabloyu ben B19:C20 aralığında oluşturdum. Siz farklı bir yerde oluşturdu iseniz formüllerde gerekli değişikli yapın lütfen.

Ve tabi son olarak bu mini tablodaki değerler veri doğrulama-listesine sahip. Bu saye istenilen değerler açılır bir menüden seçilebiliyor. Yapmanız şart değil ancak verileri hızlıca değiştirmek istiyorsanız faydalı olur.

1. Aşama Müşteriye Göre Alt Tablo

İlk önce müşteriye göre bölme işlemini yapacağız. Burada bulmak istediğimiz ilk veri müşteri adı. Aradığımız veri B2:B17 aralığında, yapacağımız ilk iş bu değerin ilk hangi satırda bulunduğunu bulmak ki sonrasında KAYDIR formülü ile ne kadar satır aşağıya ineceğimizi bilelim. Formülümüz tabii ki KAÇINCI:

=KAÇINCI($B$20;$B$2:$B$17;0)

Formül 2 değerini döndürdü. Sonrasında ise toplam kaç tane aynı müşteriden olduğunu bulmak.

=EĞERSAY($B$2:$B$17;$B$20)

B2:B17 aralığında “ABC Mühendislik” toplam 5 tane girdiye sahip olduğundan formül 5 değerini döndürdü. Buradaki amaç ta KAYDIR formülünde aralığımızı bu müşteriden toplam kaç tane varsa o kadar satırdan oluşacak şekilde ayarlamak.

Şimdi bu iki değeri bulduğumuza göre hemen  KAYDIR formülünü kullanarak ilk tablomuzu çıkaralım.

=KAYDIR($B$2;KAÇINCI($B$20;$B$2:$B$17;0)-1;0;EĞERSAY($B$2:$B$17;$B$20);BAĞ_DEĞ_DOLU_SAY($2:$2))

Dikkat ederseniz ben formülün sütun sayısı kısmında BAĞ_DEĞ_DOLU_SAY kullandım. Bu sayede tabloya yeni tarih verileri girilirse KAYDIR formülü kendini güncelleyecektir.

Formülümüzü bir hücreye yazdıktan sonra bir aralık geri döndürdüğü için #DEĞER! hatası alırsınız. Şimdi bu formülü bir isim olarak tanımlarsak aralığın nereyi seçtiğini denetleyebiliriz. Ben bu formülü “MüşteriTablo” adlı bir isim tanımlayarak oraya ekledim. Aşağıdaki videoda hücredeki müşteri değeri değiştiğinde aralığın da nasıl değiştiğini görebilirsiniz.

Müşteri Alt Tablosu Çalışması
2. Aşama Ürüne Göre Alt Tablo

Artık müşteri türüne göre bir alt tablomuz olduğuna göre artık ürün alt tablomuzu oluşturabiliriz. Burada dikkat edeceğimiz tek konu tüm arama, sayma, kaydırma işlemlerini müşteri tablosu üzerinden yapmamız gerektiği. Bunun için de MüşterTablo’sunun gerekli alanlarına indis ile ulaşmak gerektiği.

Şimdi önce ürünümüzün alt tablonun hangi satırında olduğunu bulalım, burada arayacağım sütun MüsteriTablo adlı tablomuzun  2. sütunu. Bu yüzden önce İNDİS ile bu sütunu çekeceğim:

=İNDİS(MüsteriTablo;0;2)

Şimdi bu alanda ürünü aratacağım, ürün bilgisi benim mini tablomda yani C20 hücresinde:

=KAÇINCI($C$20;İNDİS(MüsteriTablo;0;2);0)

Eğer her bir ürün için tek bir satır var ise bundan sonrası adımı atlayabiliriz, ancak ben birden fazla girdi olabileceğini düşünerek toplam kaç tane girdi olduğunu hesaplayacağım. Bunun için yine EĞERSAY kullanacağım.

=EĞERSAY(İNDİS(MüsteriTablo;0;2);$C$20)

Şimdi artık müşteri tablomuzu bu iki değer ile bir başka alt tabloya bölebiliriz. Burada da yine KAYDIR formülünü kullanacağız ancak burada kaydırmaya başlayacağımız başvuru hücresi MüşteriTablo’muzun ilk hücresi ve bunu da İNDİS ile çekeceğiz. Bu nedenle önce  bu hücre başvurusunu çeken formülümüzü yazalım:

=İNDİS(MüşteriTablo;1;1)

Sonrasında KAYDIR ile bu hücreden başlayarak alanı seçelim:

=KAYDIR(İNDİS(MüşteriTablo;1;1);KAÇINCI($C$20;İNDİS(MüşteriTablo;0;2);0)-1;0;EĞERSAY(İNDİS( MüşteriTablo;0;2);$C$20);BAĞ_DEĞ_DOLU_SAY($2:$2))

Şimdi bu formülü de ÜrünTablo adlı ikinci bir isim tanımlayarak onda kullanalım. Ve seçtiğimiz ismin hangi aralığı gösterdiğini test edelim.

Eğer sadece verilerin olduğu kısmı seçmek isterseniz ikinci formülde sütun kaydırma kısmında 0 yerine 2 kullanın ve BAĞ_DEĞ_DOLU_SAY sonucunu da 2 eksiltin (başlık sütunları nedeni ile).

Aşağıdaki videoda formüllerin sonucunu gösterdim. Hatta 2. bir ÜrünVeri ismi tanımlayarak bunu da bir grafikte kullandım.

Alt Tablo Nihai Sonucu
Neler Yapılabilir

Bu gösterdiğim yöntem her ne kadar veriyi alt parçalara bölmek için farklı bir bakış açısı sağlasa da hazırla ve unut tarzı bir yöntem değildir. Örnek olarak KAYDIR formüllerinin sütun kısımlarında elle tanımlanmış rakamlar mevcut. Buraya bir KAÇINCI formülü ile aranılan değeri sütunda bulan ve kaydırma işlemini bu değere göre yapan formül daha eklenebilir. Ben şahsen formülün daha karmaşık olmasını engellemek adına bunu yapmadım.

Bu örnekte sadece 2 kıstasa göre alt tabloya ayırma var. Daha fazla kıstasa göre ayırma işlemi de 2. adımın tekrarlanması ile yapılabilir. Hatta sütunlar da da bölme işlemi yapabilirsiniz.

Yine bu örnekte sadece sütunlara göre bir ayırma mevcut. Ancak satırlardaki, misal Yıl, Bölge, Ay, şehir gibi bilgilerin satırlarda yer aldığı bir tabloyu da benzer formüller ile alt tablolara ayırabilirsiniz.

Nerede kullanabiliriz? Bu yönteme dayanarak çok kıstaslı aramalar yapabilirsiniz, alt tablolar ile hesaplamalar yapabilirsiniz, grafiklerde kullanarak değişken grafikler hazırlayabilirsiniz. Kullanım alanını size bırakıyorum.

İlginizi Çekebilecek Diğer Yazılar
No Comments
Etiketler:

You may also like this

  • Grafiklerde Değişken Aralık Kullanma

    • Leave a Reply