Excel Çok Fonksiyonlu Kriter Arama

| 27 Ocak 2012 | 16 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 (16)

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.

  4. eylül diyor ki:

    peki excell de 150 bin ile 170 bin arasındaki ev fiyatlarını hangi formulle listeletebiliriz.

  5. Halil KEMAL diyor ki:

    Excel sayfa1 de A1-A50 arasında türkçe , B1-B50 arasında ingilizce karşılıkları ve c1-c50 arası da rakamlardan oluşan kod numaraları olan bir liste oluştursam.
    excel sayfa2 ye A sütününa türkçesini yazdığımda B sütununa ingilizcesi c sütününa kodlar gelecek şekilde düşeyara formülünü ile yazabilir miyim? Veya hangi formülü nasıl kullanmalıyım?

    • Bora M. Deniz diyor ki:

      Merhaba,
      Sayfa2 de
      A1 e Türkçe yazın,
      B1 e =DÜŞEYARA(A1;Sayfa1!A:C;2;0)
      C1 e =DÜŞEYARA(A1;Sayfa1!A:C;3;0)

      yazın. Sayfa1 de A,B,C sütünlarına yazdığınız bilgileri bu Sayfa2 ye getirecektir.

  6. dilber diyor ki:

    Merhaba Hocam;
    verdiğiniz örnek üzrinden birşey sormak istiyorum. ilk açıklamada yapmış olduğunuz tabloda sütunlarda 50 100 150 değerleri var satılar ise 3 6 10 12 14 diye devam ediyor. ben sürünları c\d değeri diye satırlarıda a\b değeri diye isimlendirdim. C, D , A, B değerlerini oluşturduğum başka bir tabloda girip bir C\D dğeri ile A\B değeri buluyorum ve bulduğum bu değerlerin indisini hesaplıyorum. örneğin c\d değerini 50 , a\b değerini 6 bulduğum da indis formülü bana 44 değerini veriyor.Ama sorun şu ki bunlardan farklı bir değer bulduğumda formül hata veriyor. mesela c\d değerini 60 a\b değerini 8 bulduğumda bu ara değerleri nasıl hesaplamalıyım ?

    • Bora M. Deniz diyor ki:

      Tablonuzun satır ve sütün başlık bilgileride olmayan değerlerle, tablo içindeki bir bilgiye ulaşmak istediğinizde hata alıyorsunuz anladığım kadarı ile.
      ara değer yoksa bir hesaba gitmek istiyorsanınız.

      =EĞER(EYOKSA(İNDİS($C$4:$E$10;E13;E14));”hesaplama”;İNDİS($C$4:$E$10;E13;E14)) diyebilirsiniz. “hesaplama” ya bu tablo dışında bir hesabı girebilirsiniz.
      Yada bu mesajı aldığınızda girişte kullandığımız uzunluk ve genişlik değerleri için tablomuzda başlık yok eklemeyelim diye düşüneceğiz.
      Formulde EYOKSA yı gerekirse EHATALIYSA olarakda kullanabilirsiniz.

  7. Tarık diyor ki:

    Faydalı bilgi için teşekkürler. Maliyet tablosu olusturyorum. 1 tablomda sırasıyla urun kodu, reçeteye ait hammaddeler, ve kullanım miktarları,
    Diğer tablomda ise, T5 satırından itibaren yatay şekilde hammaddeler, P13 satırından dikey mamul kodları.
    Benim yapamadığım ise 1 tablodaki verileri 2. tablodaki mamul kodları ile karşılaştırıp mamul kodunun karşısına doğru reçete miktarını getirebilmek, teşekkürler.

  8. Sinan diyor ki:

    Merhabalar.Yazı için teşekkürler.Peki bir hücreye karşılık gelen bir çok hücre var ise bu hücrenin karsılığı olan hücrelerin hepsıni düşey arada nasıl getirtebiliriz? Mesala buzdolabı kodu yazıyor yanında da ayru hucrelere yazılmıs 5 yedek parca kodu var (yedek parcaları ayrı hucrelere yazmam lazım cunku sonra adetıne bakıyorum). daha sonra bana yedek parca istegi geldi ben dusey ara yapınca ürün kodunun karsısındaki ilk yedek parca kodu geliyor. Simdiden tesekkürler

    • Bora M. Deniz diyor ki:

      Merhaba, Örnegin
      =DÜŞEYARA(K16;F9:J9;2;0)&” “&DÜŞEYARA(K16;F9:J9;3;0) gibi &” “& gibi n adet düşeyara yazabilirsiniz. Tek bir hücreye yazacaktır.

  9. Hüseyin diyor ki:

    Merhaba,
    Düşeyara fonksiyonuna değişken sayfa adı nasıl yazarız?

    Örnek:
    Aşağıdaki düşeyara da Sayfa2!$A$1 alanını C2 hücresinde yazan değeri alsın diyebilirmiyiz.
    =DÜŞEYARA(C7;Sayfa2!$A$1:$B$3;2;YANLIŞ)

    Buradaki amaç her 3 ayda bir değişen Excel dosya adı ve sayfa adını tek seferde C2 hücresine yazıp tüm düşeyara ları tekrar yazmamak.

    Şimdiden teşekkürler.

  10. PosBiL Mustafa diyor ki:

    İYİ GÜNLER, KOLAY GELSİN.
    BENİM DÜŞEYARA İLE YAPMAK İSTEDİĞİM BİR KONU VAR,

    2 AYRI EXCEL DOSYAM VAR ÖRNEK VERİRSEK 1. DOSYAM AHMET 2. DOSYAM MEHMET
    ÖNCELİKLE AHMET VE MEHMET DOSYASINDA NE VAR ONU BELİRTEYİM A1 HÜCRESİNDE KOD VAR B1 HÜCRESİNDE FİYAT VAR.
    BEN YENİ BİR EXCEL DOSYASI AÇTIM MUSTAFA DİYE MUSTAFADA NE VAR ONU BELİRTEYİM
    SADECE A1 HÜCRESİNDE KOD VAR
    BEN MUSTAFANIN B1 HÜCRESİNE ŞÖYLE BİR FORMÜL YAZMAK İSTİYORUM
    MUSTAFANIN A1 HÜCRESİNDE BULUNAN KOD AHMETTE VARSA AHMETTEKİ B1 HÜCRESİNDEKİ FİYATI BURAYA YAZ AMA AHMETTE BÖYLE BİR KOD YOKSA AYNI ŞEKİLDE MEHMETE BAK VARSA ONDAKİ FİYATI YAZ.

    BU YAPILABİLİRMİ EĞER YAPILABİLİYORSA ÖRNEK FORMÜLLE YARDIMCI OLURSANIZ ÇOK AMA ÇOK SEVİNİRİM ŞİMDİDEN TEŞEKKÜRLER.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir