Bu yazımda YatayAra ve DüşeyAra formüllerinin en büyük zaafı olan birden fazla kıstasa göre aradığımız değeri bulma konusuna değinmek istiyorum. Bunun için birden fazla yöntem mevcut. Anlatımları uzun olduğundan her biri için ayrı bir yazı hazırlayacağım.
Bu ilk yöntem daha önce anlattığım alt tabloya bölme yöntemini ufak bir fark ile kullanacağım. Bu nedenle buradaki uygulamayı yapmadan önce bu yazıyı okumanızda fayda var. Sonra elde ettiğim alt tablonun ilk sütununda DüşeyAra formülünü uygulayacağım. Ve tabii orada kullandığım örnek tablo üzerinde örnekleri yapacağım. Olmayanlar için tabloyu aşağıda paylaşıyorum.
| Müşteri | Ürün | Oca.16 | ub.16 | Mar.16 | Nis.16 | May.16 | Haz.16 | Tem.16 | A§u.16 | Eyl.16 | Eki.16 | Kas.16 | Ara.16 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ABC Mühendislik | Ürün A | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| ABC Mühendislik | Ürün B | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| ABC Mühendislik | Ürün C | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
| ABC Mühendislik | Ürün D | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ABC Mühendislik | Ürün E | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 |
| CDE MakSan | Ürün A | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 |
| CDE MakSan | Ürün B | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 |
| CDE MakSan | Ürün C | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 |
| CDE MakSan | Ürün D | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | 65 | 66 |
| CDE MakSan | Ürün E | 60 | 61 | 62 | 63 | 64 | 65 | 66 | 67 | 68 | 69 | 70 | 71 |
| 3R Teknik | Ürün A | 65 | 66 | 67 | 68 | 69 | 70 | 71 | 72 | 73 | 74 | 75 | 76 |
| 3R Teknik | Ürün B | 70 | 71 | 72 | 73 | 74 | 75 | 76 | 77 | 78 | 79 | 80 | 81 |
| 3R Teknik | Ürün C | 75 | 76 | 77 | 78 | 79 | 80 | 81 | 82 | 83 | 84 | 85 | 86 |
| 3R Teknik | Ürün D | 80 | 81 | 82 | 83 | 84 | 85 | 86 | 87 | 88 | 89 | 90 | 91 |
| 3R Teknik | Ürün E | 85 | 86 | 87 | 88 | 89 | 90 | 91 | 92 | 93 | 94 | 95 | 96 |
Yapmak istediğim şey Müşteri Adına göre ürünlerden başlayan bir alt tablo oluşturmak, sonra bu tabloda DüşeyAra kullanmak. Aşağıda göstermeye çalıştım.
1. Hazırlık
Öncelikle ben yukarıdaki tabloyu B2 hücresinden itibaren yapıştırdım. Ve seçimleri yapacağım ve Müşteri, Ürün, Tarih ve formülü yazarak sonucun gösterileceği Adet adlı tabloyu da, resimde görebilirsiniz, B19-E20 arasında oluşturdum. Formülleri de E20 hücresine yazacağım. Siz eğer bu tabloları farklı yerlerde oluşturdu iseniz formüllerde ilgili başvuruları değiştirin.
Mini tabloda kolay seçin yapmak adına Veri Doğrulama, Liste özelliğini kullandım. Siz isterseniz bu değerleri elle değiştirebilirsiniz.
Bu örnekte Excel Tablosu kullanmadım. Siz kullanmak isterseniz. Başvurularda ilgili alanların değişmesi gerekir. Ek olarak tablonun sıralanmış olmasına da dikkat edin. Bu yöntem sadece sıralı tabolarda çalışır. Ve tabii her bir müşteri için aranan ürün bilgisinden yalnızca bir tane olmalıdır.
2. Uygulama
Önce alt tablomuz için formülümüzü yazalım.:
=KAYDIR($B$2;KAÇINCI($B$20;$B$2:$B$17;0)-1;1;EĞERSAY($B$2:$B$17;$B$20); BAĞ_DEĞ_DOLU_SAY($2:$2)-1)
Burada dikkat ederseniz KAYDIR formülünde sütun değerini 1 yaptım. Çünkü daha önce de bahsettiğim gibi sadece Ürünlerden başlayan bir alt tablo elde etmek istiyorum ve tabii tablonun genişliğini de buna göre 1 azalttım. Yoksa tablomuz fazladan boş bir sütun içerir. Bu örnekte sonuca etki etmez ancak her halükarda dikkatli olmakta fayda var.
Bu ismi direk DüşeyAra formülünde kullanabileceğimiz gibi bir isim tanımlayarak her seferinde bu ismi kullanabiliriz. Şahsen ben isim kullanmayı daha çok seviyorum çünkü yazdığım formülün nasıl çalıştığını tetkik etme ve formülde hata olup olmadığını görme şansım oluyor. Bu nedenle ben burada MüşterAltTablo adlı bir isim tanımlayarak bu formülü orada kullanacağım. Sonucunda ilgili tabloda müşteri değiştirdiğimde tablomun nasıl değiştiğini de görebilirim.
Sonrasında ilk satırda Tarih değerini aratacağım. Ancak burada dikkat etmem gereken husus benim alt tablom Ürün sütunundan başlıyor. Değeri bulurken buna dikkat etmem gerek.
=KAÇINCI($D$20;$2:$2;0)-KAÇINCI(“Ürün”;$2:$2;0)
Şimdi ise bulduğumuz değerleri DüşeyAra ile istediğim değeri geri döndürmem gerekiyor.
=DÜŞEYARA($C$20;MüşteriAltTablo;KAÇINCI($D$20;$2:$2;0)-KAÇICI(“Ürün”;$2:$2;0);0)
Formülün sonucu resimdeki örnek için 28. Siz mini tabloda yer alan müşteri adı, tarih ve ürün bilgisi ile oynayarak istediğiniz seçim için istediğiniz ürün adedini bulabilirsiniz.
Eğer daha fazla kıstasınız veya sütunlarda yer alan verilere göre arama yapmak isterseniz alt tabloyu daha küçük tablolara bölebilirsiniz.
Alttaki videoda formülün nasıl çalıştığını gösterdim.
İlginizi Çekebilecek Diğer Yazılar
Veri Doğrulama Örnekleri
Dilimli Vergi Hesaplama
Hedef/Gerçekleşen Grafiği 1
Çok Kıstaslı Arama İndis-Kaydır İle
Dinamik Formüller İle Farklı Kıstaslara Göre Veri Toplama
Farklı Sayfalardan Formülle Otomatik Veri Alma
Sıralı Sayı Dizisi Oluşturma
Değişken Aralıklar
Sütundaki Son Değeri Bulma
You may also like this
