Excel'de verileri hızlıca bulma: Basit formüllerden akıllı filtrelere

Excel'de belirli bir değeri veya satırı bulmak için görsel arama amacıyla Ctrl + F kısayol tuşu kombinasyonunu veya koşula göre verileri otomatik olarak çekmek için XLOOKUP (yeni sürümlerde) / DÜŞEYARA işlevini kullanın. Karmaşık kriterlere göre tüm satırları seçmek için ise dinamik FILTER işlevi en iyi seçenektir. Yöntem seçimi, Excel sürümünüze ve görevinize bağlıdır: tek seferlik bir arama mı yoksa otomatik raporlama oluşturma mı?

Temel arama ve gezinme araçları

Bir sayfada metin veya sayı bulmanın en hızlı yolu, yerleşik "Bul" aracıdır. Bu araç tüm Excel sürümlerinde çalışır ve formül yazmayı gerektirmez.

Kısayol tuşları ve iletişim kutusu

Arama panelini açmak için Ctrl + F tuşlarına basın.

• Seçenekler: Aramayı daraltmak için "Seçenekler" düğmesine tıklayın. Sadece formüllerde, değerlerde veya yorumlarda arama yapabilirsiniz.
• Biçim: Hücre rengi veya yazı tipine göre arama yapın ("Biçim" düğmesi).
• Arama alanı: Sadece aktif sayfada değil, dosyadaki tüm sayfalarda arama yapmak için "Kitap" seçeneğini belirleyin.

Verileri değiştirmek için Ctrl + H kullanın. Bu, yazım hatalarını toplu olarak düzeltmek veya veri biçimini değiştirmek (örneğin, tüm noktaları virgülle değiştirmek) için kullanışlıdır.

Formüllerle değer arama

Anahtar tanımlayıcıya dayalı olarak verileri otomatik olarak almak gerektiğinde (örneğin, ürün koduna göre fiyat), arama işlevleri kullanılır.

XLOOKUP işlevi (Yeni nesil DİKEY/İNDEX)

Excel 2021 ve Office 365'te mevcuttur. Bu, en güçlü ve en basit araçtır. Sözdizimi: =XLOOKUP(aranan_değer; arama_dizisi; dönüş_dizisi; [bulunamazsa])

Örnek: Koduna göre ürün fiyatını bulma.

=XLOOKUP("A-123"; A2:A100; C2:C100; "Ürün bulunamadı")

• A sütununda "A-123" değerini arar.
• C sütununun aynı satırındaki değeri döndürür.
• Kod bulunamazsa, hata yerine "Ürün bulunamadı" metnini görüntüler.

Klasik DÜŞEYARA işlevi (VLOOKUP)

Eski sürümler dahil tüm sürümlerde çalışır. Aranan sütunun aralıktaki ilk sütun olmasını gerektirir. Sözdizimi: =DÜŞEYARA(aranan_değer; tablo; sütun_numarası; [aralık_taraması])

Önemli: Tam eşleşme için son argümanı her zaman 0 (veya YANLIŞ) olarak ayarlayın.

=DÜŞEYARA("A-123"; A2:C100; 3; 0)

Burada 3, seçilen aralığın üçüncü sütunundan veri döndürülmesi gerektiği anlamına gelir.

Çoklu koşullara göre arama (İNDEX + KAÇINCI)

İki kritere göre bir değer bulmanız gerekiyorsa (örneğin, "B Bölgesi"ndeki "A Ürünü" için "Fiyat"), dizileri kullanın.

=INDEX(C2:C100; MATCH(1; (A2:A100="Ürün A")*(B2:B100="Moskova"); 0))

Eski Excel sürümlerinde bu formülü Ctrl+Shift+Enter kombinasyonu ile onaylamanız gerekir.

Koşullara göre tüm satırları vurgulama

Göreviniz sadece bir sayı bulmak değil, uygun olan tüm satırları filtrelemek ve listelemekse (örneğin, 10.000 RUB üzerindeki tüm siparişler), dinamik dizileri kullanın.

FILTER işlevi

Excel 2021 ve Office 365'te mevcuttur. Arama sonuçlarıyla yeni bir tablo oluşturur ve bu tablo otomatik olarak güncellenir.

Sözdizimi: =FILTER(dizi; koşul; [boşsa])

Örnek 1: Basit koşul Durumu "Aktif" olan tüm satırları seçme:

=FILTER(A2:D100; D2:D100="Aktif"; "Aktif kayıt yok")

Örnek 2: Birden fazla koşul (VE) Durum = "Aktif" VE Tutar > 5000 olan satırları seçme. Çarpma işlemi * mantıksal "VE" olarak çalışır.

=FILTER(A2:D100; (D2:D100="Aktif") * (C2:C100>5000))

Örnek 3: Koşullar (VEYA) Şehir = "Moskova" VEYA Şehir = "SPb" olan satırları seçme. Toplama işlemi + mantıksal "VEYA" olarak çalışır.

=FILTER(A2:D100; (B2:B100="Moskova") + (B2:B100="SPb"))

Metinde kısmi eşleşme arama

FILTER işlevi, alt dizgeleri aramak için SEARCH (ARA) veya FIND (BUL) işlevleriyle mükemmel şekilde çalışır.

=FILTER(A2:B100; ISNUMBER(SEARCH("rapor"; A2:A100)))

Bu formül, A sütununda "rapor" kelimesinin geçtiği (hücrenin herhangi bir yerinde) tüm satırları döndürür.

Büyük veriler ve performansla çalışma

On binlerce satırlık tablolarla çalışırken, yanlış arama yaklaşımı dosyayı yavaşlatabilir.

1. "Akıllı Tablolar"ı kullanın (Ctrl + T). Aralığı bir tabloya dönüştürün. Formüller yeni satırlara otomatik olarak yayılır ve başvurular yapılandırılmış hale gelir (örneğin, Tablo1[Fiyat]). Bu, okumayı kolaylaştırır ve sütun ekleme sırasında hatalara karşı daha az hassastır.

2. Dizi formüllerinde tüm sütun başvurularından kaçının. A:A yerine belirli bir aralık belirtin (A2:A10000) veya tablo başvurularını kullanın. Tüm sütunda (1 milyondan fazla satır) FILTER veya XLOOKUP gibi işlevler işlemciyi önemli ölçüde yükleyebilir.

3. Veri biçimlerini kontrol edin. Aramanın çalışmamasının ("Değer mevcutken 'Bulunamadı' hatası") en yaygın nedeni biçim uyumsuzluğudur.

   • Sayı 123 (sayısal biçim) metin "123" (metin biçimi) ile eşit değildir.
   • Fazladan boşluklar: "Ivan " "Ivan" ile eşit değildir. Aramadan önce verileri temizlemek için KIRP (TRIM) işlevini kullanın.

Arama sırasında sık yapılan hatalar

SSS

Bir değerin tüm oluşumlarını, sadece ilkini değil, nasıl bulabilirim? DÜŞEYARA ve XLOOKUP işlevleri yalnızca ilk bulunan eşleşmeyi döndürür. Tüm kopyaların bir listesini almak için, aranan değere eşitlik koşuluyla FILTER işlevini kullanın.

Verileri diğer sayfalarda arayabilir miyim? Evet. Formülde aralıktan önce sayfa adını belirtin: =XLOOKUP(A1; 'Sayfa2'!B:B; 'Sayfa2'!C:C). Arama penceresinde (Ctrl+F) arama alanı olarak "Kitap" seçeneğini belirleyin.

Neden sayı araması, hücrede gördüğüm sayıyı bulamıyor? Büyük olasılıkla sayı metin olarak kaydedilmiştir (genellikle hücrenin köşesinde yeşil bir üçgenle işaretlenir). Sütunu seçin, "Veri" → "Sütunlara Dönüştür" seçeneğine gidin ve bunları sayıya dönüştürmek için "Son" düğmesine tıklayın.

Büyük/küçük harf duyarlı değerleri nasıl arayabilirim? Standart arama işlevleri büyük/küçük harfi yoksayar. Büyük/küçük harfe duyarlı arama için FILTER veya KAÇINCI içinde TAM (EXACT) işlevini kullanın: =KAÇINCI(DOĞRU; TAM(A2:A100; "Metin"); 0).