Satırlarda ve sütunlarda arama yapmak neden gereklidir? Çoğu zaman elimizdeki tablolar yatay ve dikeyde başlıklar içerir. Misal isimler, ürünler genelde dikey eksende kullanılırken tarihler yatay eksende olacak şekilde bir tablo hazırlanır. Bu tarz tablolarda veri arama için kullanılan YATAYARA ve DİKEYARA formülleri tek başına istediğimiz sonucu vermez. Bunun yerine farklı çözümler kullanmamız gerekir Nasıl yapılır başlayalım.
Elimizde aylara göre satış grafikleri olsun. Aşağıdaki tabloyu kopyalayarak lütfen B2 hücresinden itibaren yapıştı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 | |
| Ürün A | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| Ürün B | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| Ürün C | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
| Ürün D | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| Ürün E | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 |
Şimdi örnek olarak seçtiğimiz bir ürün için seçtiğimiz bir tarihteki adetleri bulmak isteyelim. Örnek olarak Ürün B’nin Eylül 2016 tarihinde kaç adet satıldığını bulmak isteyelim. Burada tarihler 2. satırda. ürünler ise B sütununda. ve hali ile YATAYARA ve DüŞEYARA formülleri ile istediğimiz sonuca ulaşamayız. Neyseki KAÇINCI, KAYDIR ve İNDİS gibi çok amaçlı formüllerimiz var.
KAYDIR FORMÜLÜ İLE
Kaydır formülü ile bu işi nasıl yapabileceğimize bakalım Burada dikkat edeceğimiz konu KAYDIR formülü ile ilk hücreden itibaren toplamda kaç adet hücre kayacağımızı bulmak. Burada başvuru hücremiz tablonun sol üst kenarındaki ilk hücre yani B2 hücresi. Buradan ne kadar kayarsak istediğimiz hücreyi buluruz. Aşağıdaki resimde ne yapmak istediğimi gösterdim.
Sırası ile önce seçtiğimiz ürünün hangi satırda olduğunu bulalım:
=KAÇINCI(“Ürün B”;$B$3:$B$7;0)
Formül sonucu 2 olacaktır. Şimdi de aynı formülü tarihi bulmak için kullanalım:
=KAÇINCI(TARİH(2016;9;1);$C$2:$N$2;0)
Burada gördüğünüz gibi TARİH işlevini kullandım çünkü tarih değerleri Excel’de bir sayı olarak saklanır ve bu sayıyı bulmak için tarih işlevi ile belirli bir tarihi sayıya çevirecek işlevi kullanmanız gerekir. Burada da formül 9 değerini döndürecektir.
Şimdi bu iki değeri KAYDIR formülünde kullanalım.
=KAYDIR($B$2;KAÇINCI(“Ürün B”;$B$3:$B$7;0);KAÇINCI(TARİH(2016;9;1);$C$2:$N$2;0);1;1)
Formül sonucunda elde ettiğimiz değer 23. Bu değer dikkat ederseniz tabloda Ürün B ile Eylül 2016 tarihlerinin kesiştiği hücrede yer almaktadır.
İNDİS FORMÜLÜ İLE
İNDİS formülü kullanırken dikkat edeceğimiz konu ise tüm tablonun, sol üst hücresinden başlayarak sütunlara ve satırlara numara verdiğimizde hangi satır ve sütununu geri döndürmek istediğimiz. Aşağıdaki resimde ne yapmak istediğimi gösterdim.
Bu nedenle arama işlemlerine ilk hücreden başlayarak yapmamız lazım. Hemen formüllerimizi yazalım. Önce ürün türü için:
=KAÇINCI(“ÜRÜN B”;$B$2:$B$7;0)
Arama aralığının nasıl değiştiğine dikkat edin.
Şimdi de tarih için:
=KAÇINCI(“Ürün B”;$B$2:$B$7;0)
Şimdi her de bu formülleri İNDİS formülü ile birleştirelim. Tablomuz B2:N7 arasında idi:
=İNDİS($B$2:$N$7;KAÇINCI(“Ürün B”;$B$2:$B$7;0);KAÇINCI(TARİH(2016;9;1);$B$2:$N$2;0))
Formülün döndürdüğü değer bir önceki formül ile aynı olacaktır.
Listeden Seçme ve İsim Kullanarak Otomatik Veri Çekme
Şimdi isterseniz kullanıcının bir listeden seçim yaparak istediği ürünün istediği tarihte ne kadar satıldığını bulmasını sağlamak istediğimizi düşünelim. Önce listelerde kullanmak için isimleri tanımlayalım. B3:B7 aralığını seçerek URUNLER ismini verelim. Aynı şekilde C2:N2 aralığını seçerek TARIHLER ismini verelim. İsim tanımlama konusunda daha ayrıntılı bilgi istiyorsanız bu yazıma bakabilirsiniz.
Şimdi de B9 hücresine girip ÜRÜN yazın ve hemen ardından B9 hücresine tıklayarak Veri Doğrulama ile liste seçeneğini seçin ve F3’e basıp gelen pencereden URUNLER ismini seçin. Ardından C9 hücresine geçerek TARİH yazın ve C10 hücresinde de yine veri doğrulama ile TARIHLER ismini seçin. Şimdi artık 2 tane açılır listemiz var.
Ardından D9 hücresine gelin ve “Adet” yazın. C10 hücresine ise aramayı B10 ve C10 hücrelerin alacak şekilde yukarıda gösterdiğim formülleri yazın. Verilerin hangi satır ve sütunda bulunduğunu bulmaya yarayan formülleri sırası ile:
=KAÇINCI($B$10;URUNLER;0)
=KAÇINCI($C$10;TARIHLER;0)
Dikkat ederseniz tarih arama formülünde değeri hücreden aldım önceki formüldeki gibi bir TARİH formülü kullanmadım. Ve en nihayetinde KAYDIR formülümüzü D10 hücresine yazalım:
=KAYDIR($B$2;KAÇINCI($B$10;URUNLER;0);KAÇINCI($C$10;TARIHLER;0);1;1)
Burada İNDİS kullanmak isterseniz yukarıda anlattığım nedenden dolayı KAÇINCI ile bulduğunuz değerlere 1 eklemek durumundasınız. Bu nedenle İNDİS formülü:
=KAYDIR($B$2:$N$7;KAÇINCI($B$10;URUNLER;0)+1;KAÇINCI($C$10;TARIHLER;0)+1)
şeklinde olacaktır.
Aşağıdaki videoda işlem yukarıdaki işlem sonuçlarını göstermeye çalıştım.
merhaba, tablo şu şekilde; “”ürün barkodu-açıklaması-mağaza adı – stok “” yapmak istediğim barkodu yazdığımda ilgili ürün hangi mağazalarda var
Merhaba,
Tüm eşleşenleri çekme yöntemini kullanabilirsiniz
http://excelileharikalar.com/index.php/2019/04/24/tum-eslesenleri-cekme/
Ya da power query ile tablo birleştirme işlemi yapabilirsini
http://excelileharikalar.com/index.php/2020/04/17/power-query-ile-tablolari-birlestirme/
Power Query kullanırsanız ürün kodu değiştiğinde sorguyu yenilemeniz gerekir.
Saygılarımla,