Birden fazla kıstasa göre arama yapmak için kullanacağımız diğer yöntem bizim her derde deva İNDİS, KAÇINCI formüllerini kullanmak. Joker karakteri gibi bu işlevler. Evir çevir kullan. Hemen tablomuzu yeniden verelim:
| 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 |
Şimdi ne yapacağız, öncelikle hem müşteri hem de ürün değerinin ikisinin kesiştiği bir satırı bulmamız lazım. Sonrasında aradığımız tarihin hangi sütunda olduğunu bulacağız. En sonunda bulduğumuz bu değerleri kullanarak ya KAYDIR ile kesişen hücreye gideceğiz ya da İNDİS ile ana tablonun kesiştiği hücreyi bulacağız. Aşağıdaki resimde ne yapacağız, işlem adımlarını gösterdim.
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.
Bu örneği dizin formülü şeklinde de kullanabiliriz. Yeri geldiğine ben indis ile nasıl kullanılacağını anlatacağım. İkisinden birini kullanabilirsiniz.
2. Uygulama
Şimdi ilk aşamamız hem müşteri hem de ürün değerinin kesiştiği satırı bulmak. Bunun için her zaman olduğu gibi KAÇINCI formülünü kullanacağım. Önce şartları yazalım:
=($B$2:$B$17=$B$20)
=($C$2:$C$17=$C$20)
Bu formülleri tek başına yazarsanız #DEĞER! hatası alırsınız, çünkü gördüğünüz üzere karşılaştırma bir aralık ile yapılıyor. Ancak biz bunu daha sonrasında dizin olarak işleyecek hale getireceğiz. Yani her iki karşılaştırma da doğru veya yanlış olması durumuna göre {1,1,1,1,1,0,0,0….} ve {0,0,0,1,0,0,0,0,…} şeklinde dizinlere çevireceğiz.
Sonraki aşamada bu iki karşılaştırmanın doğru olduğu şartı bulmamı gerek. Burada da * işlemini kullanacağım. Kabaca yukarıda anlattığım iki dizinin elemanlarını çarpacağım ve sadece iki değerin de eşit olduğu bir yerde 1 değerinin olduğu bir dizin elde edeceğim:
=($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20)
Artık elimde {0,0,0,1,0,0,0,….} gibi bir dizin var. Ancak bu dizin Excel tarafından işlenemiyor. Öncesinde bunu dizin olarak işleyecek bir formüle ihtiyacım var. Bunu da İNDİS ile yapıyorum:
=İNDİS(($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);;)
Burada İNDİS formülünü kullanmaz isek bir sonraki aşamada dizin formülü kullanmak durumunda kalırız.
Şimdi bu dizinde hangi elemanın 1 olduğunu bulacağız. Bunu da KAÇINCI formülü ile yapıyoruz:
=KAÇINCI(1;İNDİS(($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);;);0)
Dizin formülü olarak devam etmek istiyorsak aşağıdaki formülü yazıp CTRL+SHIFT+ENTER tuşlarına basıyoruz:
=KAÇINCI(1;($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);0)
Şimdi artık elimizde bir satır numarası var. Sonraki aşamada tarih değerinin hangi sütunda olduğunu bulmak var. Yine Kaçıncı formülünü kullanacağız.
=KAÇINCI($D$20;$B$2:$O$2;0)
Artık elimizde bir satır bir de sütun değeri var. Ve kesiştiği değeri bulmak artık bizim için çok kolay. KAYDIR veya İNDİS formüllerinden birini kullanabiliriz, KAYDIR kullanır isek dikkat etmemiz gereken konu kaydırma miktarı başlangıç hücresine eklendiğinden bulduğumuz değerlerin bir eksiğini kullanmak. Hemen formüllerimizi yazalım:
=KAYDIR($B$2;KAÇINCI(1;İNDİS(($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);;);0)-1; KAÇINCI($D$20;$B$2:$O$2;0)-1;1;1)
=İNDİS($B$2:$O$17;KAÇINCI(1;İNDİS(($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);;);0); KAÇINCI($D$20;$B$2:$O$2;0))
Eğer dizin formülü olarak yazmak isterseniz de:
=İNDİS($B$2:$O$17;KAÇINCI(1;($B$2:$B$17=$B$20)*($C$2:$C$17=$C$20);0);KAÇINCI($D$20;$B$2:$O$2;0))
Formüllerin çalışmasını aşağıdaki videoda görebilirsiniz.
3. Neler Yapılabilir
Ben formülün daha da karmaşık olmasını engellemek üzere sütun kısmında yapılan aramayı tek bir kıstasa göre yaptım ancak siz burada da birden fazla kıstas kullanabilirsiniz.
Bir ana tablo değişken ismi tanımlayarak aramaları bu tablonun satır ve sütunlarında yapabilirsiniz. Burada dikkat edeceğiniz husus ana tablonun ilgili satır ve sütunlarını çekmek için İNDİS formülünü kullanmak ve karşılaştırma için bu aralığı kullanmak.
Bu iki denemeyi de sizlere bırakıyorum.