AnaSayfa > İp Uçları >> Excel Çok Fonksiyonlu Kriter Arama

Excel Çok Fonksiyonlu Kriter Arama

| 27 Ocak 2012 | 5 Yorum

Excel Çoklu Kritere Göre Arama Fonksiyonları

Bu yazımda İndis (İndex) ve Kaçıncı (Match) Excel fonsiyonlarını kullanarak, çapraz bir tablodan iki farklı değişkene göre nasıl arama yapılacağını anlatmaya çalışacağım. Mevcut Tablom ve bunun yanında girislerimi yapacağım iki alanım olacak, bu alanlarda ki bilgileride veri doğrulama yardımıyla Tablomun üzerinden getireceğim. Daha sonra bu aramanın Düşeyara (Vlookup) ve Yatayara( Hlookup) karşılığınından da bahsedeceğim.



Örnek tablomda Uzunluk ve Genişlik değerleri ve bu iki değişkene karşılık düşen fiyat değerlerini görüyorsunuz. Amacım hemen tablonun altında Uzunluk ve Genişlik bilgisini girdiğimde Fiyat alanına tablomdan ilgili değerin gelmesini istiyorum. 

Öncelikle uzunluk için listemizi tanımlayalım. C13 hücresini seçip, Menu üzerinde Veri sekmesi üzerinden veridoğrulama kısmına girip, örnek ekran çıktısında olduğu gibi bu alan için listemizi =$B$4:$B$10 olarak tanımlayalım. Bu işlemi Kaynak alanını tıklatıp, mouse ile ilgili hücreleri seçerek kolaylıkla yapabilirsiniz.

Artık C13 hücresini seçtiğinizde bir kulakçık üzerinden bu tablodaki değerler listesi oluşmuş olacaktır. Bu özellikle farklı bir sayfa üzerinden giriş yaptığınızda değerleri o an göremeyeceğinizden faydalı olacaktır. Ayrıca aramada değişkenler bire bir tablo ile aynı olmak zorundadır. Burda text bir değişken yer alsaydı ve elle farklı yazsaydınız sonucu ulaşamayabilecektiniz.

Benzeri şekilde Genişlik içinde C14 hücresini seçip, veri doğrulama üzerinden =$C$3:$E$3 hücrelerini seçiniz. 

Artık şu bilgiye ihtiyacım var Benim seçtiğim bu iki değişken tablomun kaçıncı satırı ve kaçıncı sütununa karşılık geliyor ?

D13 Hücresine “Satır: ” diye bir etiketi yazıp, E13 Hücresine  =KAÇINCI(C13;B4:B10;0) formülünü 

D14 Hücresine “Sütun: “, E14 Hücresine =KAÇINCI(C14;C3:E3;0) şeklinde yazalım. Ekran Görüntüsünde gördüğünüz gibi bilgiler geldi.

Uzunluk olarak 14, Genişlik olarak 1oo değerlerine karşılık gelen kesişimi arıyorum yani 154 TL değerini. Bunun tablomdaki matrisi (5:2) şeklinde oluştu.

Şimdi fazla uzatmadan C15 Hücresine =İNDİS($C$4:$E$10;E13;E14) yazıyorum. Bunun anlamı C4 :E10 benim tablomdur. Sen bana bu tablodaki 5. satır ve 2. sütun daki değeri getir. İşte hepsi bu kadar. 

Bu işlemden önce kolaylık olması açısından ben C4 :E10 tabloma bir isim de tanımlayabilirdim. Bunun içinde Menüdeki formüller sekmesinden Ad tanımla ile bu aralığa görseldeki gibi bir isim vermek olacaktı.

Bu durumda Formül C15 Hücreside =İNDİS(tablo;E13;E14) şeklinde olacaktı.

Bu aramayı isterseniz Yatayara ve Düşeyara fonsiyonları ilede yapabilirsiniz. Dikkat edilecek nokta kırmızı ile belirlediğim tablo sınırları.

Yatayara Hlookup Formülünü kullanayım derseniz.

Yatayara da ben arayacağım ben Genişliği tabloma dahil etmek zorundayım o yüzden kırmızı çizgilerle belirlediğim alanın değiştiğine dikkat edin. Bu durumda Diğer kriterim olan uzunluk ilk örnektekinden farklı olarak 6. satıra gelmiş durumda.

 ‘=YATAYARA(C14;C3:E10;E13;0) formülü ile yine aynı sonuca ulaştım. Burada c14 yani 14 ü arıyorum : C3:E10 hücreleri arasındaki tabloya bak : bana E13 de yazan 6 cı satırdaki bilgiyi: 0 yani tam eşleştirerek getir. İsterseniz formuldeki E13 yerine Kaçıncı fonsiyonu yazıp sadeleştirebilirsiniz. Bu durumda 

 ‘=YATAYARA(C14;C3:E10;KAÇINCI(C13;B3:B10;0);0) şeklinde olacaktır.

Düşeyara Vlookup Formülünü kullanayım derseniz.

Tablomuz kırmızı ile gösterilen alan gibi olacaktır. Bu durumda da önceki örnekte D3 Hücresinden yazan 100 değerinin 3. sutuna kaydığına dikkat edelim.

‘=DÜŞEYARA(C13;B4:E10;E14;0) ile C13 DE Yazan 14 nolu değerimi: B4:E10 Aralığındaki tablomda ; 3. sutundaki değerini; 0 tam eşleştirerek buluyorum.

Düşeyara ve Yatayara fonsiyonlarını Kaçıncı Match formülü ile dinamik olarak ikinci kriterin  işleme sokulmaktadır.

Ekleme: Bir soru üzerine eğer derinlik gibi ücüncü bir kriter düşünürsek concatenate yada birleştir fonksiyonu aşağıdaki gibi kullanmamız ve ilk yazdığım örneğe uyarlamanız yeterli olacaktır. Bu fonksiyon çok fazla hücreyi birleştirebilir. Bu örnekte iki değişken derinlik ve uzunluk bir değişkene indirgendi yani C3 hücresine =Birleştir(A3;B3) yazdım, bundan sonrası kaçıncı ve indis fonksiyonları ile yapılacaktır.



Kategori: İp Uçları

Bora M. Deniz ()

Teknoloji, internet, ve bilişim gibi konularda paylaşımlarım ve yazılarımla 2011 den beri teknorapor.com dayım. Sosyal medya da yararlı bulduğunuz yazılarımı paylaşmanız dileklerimle... The author shares and writings IT, internet and technology news blog offering

Yorum (5)

Trackback URL | Yorum RSS

  1. turgut diyor ki:

    peki iki değişken değilde üç değişken olsaydı yani uzunluk genişlik ve derinlik diye üçüncü bileşeni koysaydık. demek istedigim 3 farklı derinlik ölçüsü olan ve 3 farklı genişlikteki olan için boy yazdıgımızda derinlik ve genişlik girdisi yapılarak capraz tablodan veri aratabilimiyiz? bunla ilgili bi dosyam var yardıma ihtiyacım var.
    tesekkürler.

  2. Oguz diyor ki:

    vlooklup ile aynı girdiye karşılık gelen birden fazla değeri nasıl toplarız?

    mesela harcama yeri, tarih ve şehir ismi olarak 3 kolon olsa. izmirde 10.mayıs ve 11.mayısta 25 TL ve 76 TL olarak 2 farklı harcama olsa (aynı tarih de olabilir, ya da tarih sütunu hiç olmayabilir de, sadece farklı kalem, farklı satırda).. diğerleri de başka şehirlerde başka harcamalar. şehir bazlı bir listede harcamalarımı çıkarıyor olsam izmir karşısında vlookup ile çağırdığım zaman ikisinin toplamını nasıl alırım? =sum(vlookup??)

    şimdiden teşekkürler.

    • Bora M. Deniz diyor ki:

      teknorapor.com/wp-content/uploads/2013/11/etopla.png

      Bu linki adres çubuğuna kopyalayalım. Etopla yada sumif işlemi ile yapabilirsin. İzmir yazdığında yukarıdaki tablodan bu aralığa bakıp, toplamı alabilir. Kolay gelsin.

  3. koper diyor ki:

    Excel de öenmli bir bilgi faydalı oldu, teşekkür ederim.

Bir Cevap Yazın

E-posta hesabınız yayınlanmayacak.