Excel'de değer nasıl bulunur: Formüller ve araçlar için eksiksiz kılavuz

Excel'de bir değeri bulmak için manuel arama amacıyla Ctrl+F tuşlarını veya tablolardan otomatik veri çekmek için DÜŞEYARA, İNDİS/KAÇINCI işlevlerini kullanın. Metni değiştirmek için YERİNEKOY işlevini uygulayın. Yöntem seçimi göreve bağlıdır: tek seferlik kontrol mü yoksa dinamik raporlama oluşturma mı.

Elle arama ve verilerde gezinme

Bir sayfada belirli bir sayıyı, kelimeyi veya hatayı bulmanın en hızlı yolu yerleşik arama aracıdır. Formül yazmayı gerektirmez ve hazır raporların analizi için idealdir.

Kısayol tuşları ve parametreler

«Bul ve Değiştir» penceresini açmak için Ctrl + F tuşlarına basın.

• Tümünü Bul: Pencerenin alt kısmında tüm eşleşmelerin listesini görüntüler. Sorunun boyutunu değerlendirmek için kullanışlıdır (örneğin, kaç hücrenin #YOK hatası içerdiği).
• Sonrakini Bul: İmleci sonraki eşleşmeye taşır.
• Seçenekler >>: Aramayı hassaslaştırmanıza olanak tanır:
  • Arama yeri: formüllerde, değerlerde veya notlarda.
  • Büyük/küçük harf duyarlı: kesin kodlar için önemlidir.
  • Hücrenin tamamı: «10»u bulmak için, ancak «100» veya «210»u bulmamak için.

Formüllerle otomatik arama

İki tabloyu bağlamanız veya verileri dinamik olarak yerleştirmeniz gerektiğinde, elle arama yetersiz kalır. Burada özel işlevler devreye girer.

DÜŞEYARA (VLOOKUP) İşlevi

Dikey arama için klasik bir araçtır. Belirtilen aralığın ilk sütununda bir değeri arar ve aynı satırdan, ancak sizin belirttiğiniz sütundan verileri döndürür.

Sözdizimi: =DÜŞEYARA(aranan_değer; tablo; sütun_no; [aralık_türü])

Örnek: A2 hücresinde bir ürün kodunuz var ve fiyat listesi D:F aralığında bulunuyor (burada D — ürün kodu, E — ad, F — fiyat). Fiyatı almak için: =DÜŞEYARA(A2; D:F; 3; 0) Burada 3, aralığın üçüncü sütunudur (fiyat) ve 0 (veya YANLIŞ) tam eşleşme anlamına gelir.

İNDİS ve KAÇINCI Kombinasyonu

DÜŞEYARA'ya daha esnek bir alternatiftir; her yönde arama yapmaya olanak tanır ve tabloya yeni sütunlar eklendiğinde bozulmaz.

Çalışma mantığı:

1. KAÇINCI, aranan değerin bulunduğu satır numarasını bulur.
2. İNDİS, ilgili sütundaki bu satırdan değeri alır.

Sözdizimi: =İNDİS(sonuç_sütunu; KAÇINCI(aranan_değer; arama_sütunu; 0))

Örnek: Personel sicil numarasına (D sütunu) göre çalışan adını (B sütunu) bulmanız gerekiyor. =İNDİS(B:B; KAÇINCI(A2; D:D; 0)) Bu formül, adların bulunduğu sütun numaraların bulunduğu sütunun solunda olsa bile başarıyla çalışacaktır.

Metin parçalarını arama ve değiştirme

Görev tabloda bir satır aramak değil, hücre içeriğini işlemek (bir alt dizgiyi bulmak veya değiştirmek) ise, metin işlevleri kullanılır.

Konum belirleme: BUL ve ARA

Her iki işlev de aranan metnin başladığı karakter numarasını döndürür.

• BUL: Büyük/küçük harfe duyarlı değildir («metin» = «Metin»). Joker karakterleri destekler (? — herhangi bir karakter, * — herhangi bir dizi).
• ARA: Büyük/küçük harfe duyarlıdır. Joker karakterleri desteklemez.

Kullanım örneği: =HATAAYIKLA(BUL("İstanbul"; A1); 0) «İstanbul» kelimesinin konumunu veya kelime yoksa 0 değerini döndürür (hata işleme sayesinde).

Veri değiştirme: YERİNEKOY

İşlev, bir hücre içindeki eski metni yeni metinle değiştirir.

Sözdizimi: =YERİNEKOY(hücre; eski_metin; yeni_metin; [kaçıncı_geçiş])

Örnek: Ürün kodundaki tireleri boşluklarla değiştirin: =YERİNEKOY(A1; "-"; " ") Yalnızca ikinci tireyi değiştirmek istiyorsanız, dördüncü bağımsız değişkeni belirtin: =YERİNEKOY(A1; "-"; " "; 2).

Arama yöntemlerinin karşılaştırılması

Arama sırasında yapılan yaygın hatalar

1. DÜŞEYARA'da #YOK hatası: Çoğunlukla verilerdeki fazla boşluklardan (gözle görülmeyen) kaynaklanır. Çözüm: KIRP işlevini kullanın veya verileri «Sütunlara Dönüştür» aracılığıyla temizleyin.
2. Yanlış sütun numarası: DÜŞEYARA kullanırken numaralandırma sayfanın A sütunundan değil, seçilen aralığın ilk sütunundan başlar.
3. Metin olarak saklanan sayılar: Bir tabloda kod «123» (sayı), diğerinde «123» (metin) ise, arama çalışmaz. Biçimleri tek bir türe dönüştürün.
4. Göreli başvurular: Formülü sürüklerken tablo aralığı «kayar». Aralığı her zaman dolar işaretleriyle sabitleyin: $A$2:$C$100 veya onu «Akıllı Tablo»ya dönüştürün (Ctrl+T).

SSS

DÜŞEYARA ile XARA arasındaki fark nedir? XARA (XLOOKUP), DÜŞEYARA'nın yerini alan modern bir işlevdir (Excel 2021 ve Office 365'te mevcuttur). Her yönde arama yapabilir, yerleşik hata işlemeye sahiptir ve yazımı daha basittir. Yeni bir Excel sürümünüz varsa, bunu kullanın.

Yinelenen değerler nasıl bulunur? Sütunu seçin, «Giriş» sekmesine gidin → «Koşullu Biçimlendirme» → «Hücre Kurallarını Vurgula» → «Yinelenen Değerler». Excel tüm yinelemeleri renkle vurgulayacaktır.

Birden fazla kritere göre aynı anda değer aranabilir mi? Evet. Klasik Excel'de bunun için koşulların çarpıldığı İNDİS/KAÇINCI ile bir dizi formülü kullanılır. Yeni sürümlerde FİLTRE işlevini veya (sayısal bir toplam gerekirse) ÇOKEĞERTOPLA işlevini kullanmak daha uygundur.