Excel'de birden fazla ölçüte göre değer bulmak: üç yöntem

Neden tek kriter yetmez?

Bir fiyat listesinde aynı ürünün her bölge için farklı birim fiyatı olabilir. Ürün koduna göre arama yaparsanız Excel ilk karşılaştığı bölgenin fiyatını getirir; sonuç hatasız görünür ama yanlıştır. Satırı gerçekten tanımlayan şey, ürün ile bölgenin birlikte oluşturduğu çifttir.

Bu durum sık görülür: çeyrek ve departman, müşteri ve ürün, şube ve tarih. Tek ölçütlü arama fonksiyonları böyle durumlarda ya yanlış satırı getirir ya da hata verir. Çözüm, ölçütleri ya fonksiyona birlikte sınatmak ya da ölçütleri tek bir değere dönüştürmektir.

Yöntemlerde örnek olarak Satışlar adlı bir tablo kullanılır: Bölge, Ürün ve Tutar sütunları var; formüllerin okunabilir olması için hücre adresi yerine tablo ve sütun adları yazılır. Aranan bölge ve ürün ise iki ayrı giriş hücresindedir.

Yöntem bir: ÇAPRAZARA ile koşulları çarpmak

Bu yöntemin mantığı basittir. Her koşul, satır başına doğru ya da yanlış üretir. İki koşulun çarpımı yalnızca ikisi birden doğruysa bir, aksi halde sıfır verir. ÇAPRAZARA'ya "bir sayısını ara" dersek, her iki koşulu da sağlayan ilk satırı bulur.

Formül şu biçimde yazılır: =ÇAPRAZARA(1;(Satışlar[Bölge]=GirişBölge)*(Satışlar[Ürün]=GirişÜrün);Satışlar[Tutar];"Bulunamadı"). Listeyi ayıran karakter Excel'in yerel ayarına göre noktalı virgül ya da virgül olabilir. Dördüncü bağımsız değişken, eşleşme olmadığında #YOK yerine okunabilir bir metin gösterir.

Üçüncü bir koşul eklemek gerektiğinde bir çarpım daha eklenir. Koşulların hepsi aynı uzunlukta aralıklardan gelmelidir; farklı boyutlu aralıklar hata üretir. Microsoft, ÇAPRAZARA'nın Microsoft 365 için Excel, Excel 2021 ve Excel 2024'te bulunduğunu, Excel 2016 ve Excel 2019'da kullanılamadığını belirtir.

Yöntem iki: İNDİS ve KAÇINCI ile aynı mantık

Eski sürümde çalışmanız gerekiyorsa aynı koşul çarpımı İNDİS ve KAÇINCI ile kurulur: =İNDİS(Satışlar[Tutar];KAÇINCI(1;(Satışlar[Bölge]=GirişBölge)*(Satışlar[Ürün]=GirişÜrün);0)). KAÇINCI, bir sayısının koşul çarpımı dizisindeki sırasını verir; sondaki sıfır tam eşleşme demektir. İNDİS de o sıradaki tutarı getirir.

Burada KAÇINCI'nın içindeki çarpım bir dizi hesabıdır. Güncel sürümlerde bu kendiliğinden hesaplanır; daha eski sürümlerde formülü dizi formülü olarak onaylamak gerekebilir. Formül çalışmıyor gibi görünüyorsa ilk bakılacak yer burasıdır.

Çoklu ölçüt, tek ölçütlü İNDİS ve KAÇINCI kurgusunun bir farkla genellemesidir: arama değeri bir, arama dizisi ise koşul çarpımıdır.

Yöntem üç: birleşik anahtar sütunu ve DÜŞEYARA

Formülü sade tutmak ve herkesin anlamasını istiyorsanız tabloya bir yardımcı sütun eklenir: bölge ve ürünü bir ayraçla birleştiren Anahtar sütunu. Ayraç önemlidir; ayraçsız birleştirmede iki farklı çift aynı metni üretebilir. Örneğin kısa bir bölge kodu ile ürün kodunun birleşimi başka bir çiftin birleşimiyle çakışabilir.

Anahtar sütunu, arama aralığının ilk sütunu olacak şekilde konumlandırılır, çünkü DÜŞEYARA yalnız aralığın en soldaki sütununda arar. Sonra giriş hücrelerindeki bölge ve ürün aynı ayraçla birleştirilip DÜŞEYARA'ya arama değeri olarak verilir. Tam eşleşme seçeneği mutlaka belirtilir.

Bu yöntemin avantajı, dizi mantığı bilmeyen ekip arkadaşının da formülü okuyabilmesidir. Dezavantajı tabloya ek sütun gerektirmesidir; kaynak tabloyu başka biri düzenliyor ve sütun yapısını değiştiriyorsa anahtar bozulabilir.

Örnek: bölgeye göre değişen fiyat listesi

Bir dağıtım şirketinin satış operasyon ekibi, sipariş satırlarına birim fiyat getirmek istiyor. Fiyat listesinde her ürün her bölge için ayrı satırda; yani ürün tek başına anahtar değil. Sipariş tablosunda bölge ve ürün sütunları var, fiyat sütunu ise boş.

Ekip önce fiyat listesini tabloya çevirir ve sütunlara anlamlı adlar verir. Sipariş tablosundaki fiyat sütununa, o satırın bölge ve ürün değerlerini ölçüt alan bir arama yazar. Tablo yapılandırılmış başvuruyla çalıştığı için yeni sipariş satırı eklendiğinde formül kendiliğinden aşağı doğru genişler.

Sonra bir denetim hücresi ekler: fiyat bulunamayan satırların sayısı sıfır olmalıdır. Sıfırdan farklıysa ya listede olmayan bir bölge-ürün çifti ya da yazım farkı vardır. Bu denetim, arama formülünün sessizce yanlış sonuç vermesini önleyen en ucuz güvencedir.

Hangisini seçmeli?

Seçim üç soruya bağlıdır: dosyayı kimler hangi sürümle açacak, formülü kimler bakımını yapacak ve aynı arama kaç yerde kullanılacak? Hepsi yeni sürüm kullanıyorsa koşul çarpımlı ÇAPRAZARA en kısa ve en esnek yoldur. Karışık sürüm varsa İNDİS ve KAÇINCI güvenlidir. Aynı arama çok sayıda hücrede tekrarlanıyorsa ve bakımı daha az deneyimli biri yapacaksa anahtar sütunu daha dayanıklıdır.

Çalışma kitabına başkaları da formül eklemeyecekse en okunabilir yöntem seçilir; hız farkı genellikle ancak büyük tablolarda hissedilir.

Sık yapılan hatalar

İlk tuzak, veri türü uyuşmazlığıdır. Giriş hücresindeki ürün kodu sayı, tablodaki kod metin olarak saklanmışsa görünüşte aynı değerler eşleşmez. Hücre biçimi yerine içeriği kontrol etmek ve gerekirse tabloyu tek türe çevirmek gerekir.

İkinci tuzak boşluklardır: sondaki görünmez bir boşluk, iki değeri farklı yapar. Kaynak verideki fazla boşluklar önce temizlenir.

Üçüncü tuzak yinelenen satırlardır. Aynı bölge ve ürün için iki kayıt varsa üç yöntem de ilkini döndürür ve diğeri sessizce atlanır. Böyle bir risk varsa önce tabloda yinelenen koşul çiftleri aranır.

Son tuzak, kapsamı elle yazılmış sabit aralıklarla kurmaktır. Tabloya yeni satır eklenince formül yeni kayıtları görmez; tablo yapılandırılmış başvurusu bu sorunu önler.

Bu beceriyi Mindset programlarında nerede çalışırsınız

Microsoft Excel ile Veri Analizi eğitiminin ileri formüller ve veri tablolarıyla ilgili modülleri, birden çok ölçütle arama gibi gündelik raporlama sorunlarını uygulamalı olarak ele alır. Raporlarını elle birleştirmek yerine tek formülle kurmak isteyen finans, satış operasyonu ve raporlama ekipleri için ilgili bir zemindir.