Excelde Birden Çok Değer Nasıl Aranır: Adım Adım

Microsoft Excel'de büyük veri kümeleriyle çalışırken, birkaç koşulla eşleşen birden çok değer bulmak zor olabilir. DÜŞEYARA gibi bazı yerleşik işlevler orijinal olarak tek değerlerle çalışacak şekilde tasarlanmıştır.

Birden çok değer aramak için birkaç Excel işlevi birleştirilebilir. DÜŞEYARA, DİZİN, KAÇINCI ve EĞER işlevlerini içerir. Excel'in mevcut sürümleri dinamik dizileri kullanabilirken, eski sürümler dizi formüllerini kullanır.

Bu makale, verilerinizde birden çok değer bulan formüllerde bu işlevleri tam olarak nasıl kullanacağınızı gösterecektir.

Hadi gidelim!

İçindekiler

• DÜŞEYARA Birden Fazla Değerle Nasıl Kullanılır?
  • Dizi Formülleri nedir?
  • DÜŞEYARA Örneği
  • Excel'in Son Sürümleri
  • Eski versiyonlar
  • Tam Eşleşme ve Yaklaşık Eşleşme
  • DÜŞEYARA Hakkında Daha Fazla Bilgi
• Birden Çok Değeri Aramak İçin INDEX ve MATCH Nasıl Kullanılır?
  • Excel 365'te INDEX ve MATCH'ı Birlikte Kullanma
  • Excel'in Eski Sürümleriyle Çalışmak
• Birden Çok Değer Bulmak İçin EĞER İşlevini Kullanma
  • Excel'in Geçerli Sürümleri
  • Excel'in Eski Sürümleri
• Birden Fazla Kriter için SUMPRODUCT Nasıl Kullanılır?
• Arama İşlevlerinde Yaygın 3 Hata
  • 1. #YOK Hatalar
  • 2. #REF! Hatalar
  • 3. Döngüsel Hatalar
• Arama İşlevleriyle IFERROR Nasıl Kullanılır?
• 7 Gelişmiş Arama Tekniği
  • 1. Göreceli Konum Arama
  • 2. KÜÇÜK İşlevi
  • 3. SATIR İşlevi
  • 4. FİLTRE İşlevi
  • 5. Yardımcı Sütunlar
  • 6. Dinamik Diziler
  • 7. Güç Sorgusu
• Son düşünceler

DÜŞEYARA Birden Fazla Değerle Nasıl Kullanılır?

DÜŞEYARA işlevi genellikle bir veri aralığındaki tek değerleri bulmak için kullanılır . Ancak, bu arama formülüyle Excel'de birden çok eşleşme de arayabilirsiniz.

Varsayılan olarak, yalnızca bulduğu ilk eşleşen değeri döndürür. Ancak, bir dizi formülü kullanarak işlevi birden çok değer döndürecek şekilde değiştirebilirsiniz.

Dizi Formülleri nedir?

Dizi formülü, veri dizileri üzerinde hesaplamalar yapabilen bir formüldür. Tek bir değer yerine bir sonuç dizisi döndürebildiği için buna dizi formülü denir.

Bir dizi formülü oluştururken birkaç kolay adım vardır:

1. Aramak için bir hücre aralığı seçin.

2. Formül çubuğuna formülü girin.

3. Tamamlamak için Ctrl + Shift + Enter tuşlarına basın.

Bir dizi formülünün sözdizimi, normal bir formüle benzer, ancak formülün etrafında kaşlı ayraçlar {} içerir. Kıvrımlı parantezler, formülün bir dizi formülü olduğunu ve bir dizi değer döndüreceğini gösterir.

Bu makaledeki örnekler, dizi formüllerini doğru bir şekilde nasıl kullanacağınızı gösterecektir.

DÜŞEYARA Örneği

Örneğimizde, bir çalışma sayfasının A sütununda beş öğe vardır:

• Elma

• Muz

• Ekmek

• Havuç

• Kiraz

Görev, bu listede üç belirli meyvenin olup olmadığını kontrol etmektir: elma, muz ve kiraz.

Excel'in Son Sürümleri

DÜŞEYARA sözdizimi, kullandığınız Excel sürümüne bağlıdır.

En yeni sürümler ( Excel 365 veya Excel 2021 ) dinamik dizileri destekler. Bu özellik, formüllerin bitişik hücrelere "dökülen" birden çok sonuç döndürmesine olanak tanır.

Bu söz dizimidir (eşittir işareti formülü başlatır):

=DÜŞEYARA(aranan_değer, tablo_dizisi, sütun_dizin_sayısı, [aralık_bak])

• aranan_değer: Aramak istediğiniz değer.

• tablo_dizisi: Aramak istediğiniz veri tablosunun tamamı.

• col_index_num: Tablo_dizisindeki döndürmek istediğiniz verileri içeren tablo sütun numarası.

• range_lookup: İsteğe bağlı. Tam bir eşleşme mi yoksa yaklaşık bir eşleşme mi istediğinizi belirtir.

Spesifik örneğimizde şu formül kullanılmaktadır:

=DÜŞEYARA({“elma”,”muz”,”kiraz”},A1:A5,1,YANLIŞ)

B1 hücresine formül girildiğinde, sonuçlar C1 ve D1 hücrelerine dökülür. Bu resim, örneği uygulama halinde göstermektedir:

Eski versiyonlar

Excel'in dinamik dizileri desteklemeyen eski bir sürümünü kullanıyorsanız (örneğin, Excel 2019 veya öncesi), dizi formülüyle biraz farklı bir yaklaşım kullanmanız gerekir.

Bu adımları takip et:

1. İlk öğenin sonuçlarını görüntülemek istediğiniz hücreye tıklayın (örneğin, B sütunu).

2. Henüz Enter'a basmadan şu formülü yazın: =DÜŞEYARA({“elma”,”muz”,”kiraz”}, A1:A5, 1, YANLIŞ)

3. Bunu bir dizi formülüne dönüştürmek için Ctrl + Shift + Enter tuşlarına basın.

4. B1 hücresini kopyalayın ve aşağıdaki hücreye yapıştırın (veya doldurma tutamaçlarını kullanın).

Ctrl + Shift + Enter'ı kullandığınızda, Excel formülün etrafına kaşlı ayraçlar ekler. Bu onun bir dizi formülü olduğunu gösterir.

Tam Eşleşme ve Yaklaşık Eşleşme

DÜŞEYARA işlevi varsayılan olarak yaklaşık bir eşleşme kullanır. Bu, hücre değerleri tam olarak eşleşmese bile bulabileceği en yakın eşleşmeyi döndüreceği anlamına gelir.

Tam bir eşleşme gerçekleştirmek istiyorsanız, aralık_bak bağımsız değişkenini YANLIŞ olarak ayarlayabilirsiniz.

Yaklaşık eşleşmelerin sıralı sayısal değerlerle en iyi sonucu verdiğini unutmayın. Hücre değeri metin olduğunda genellikle uygun değildir.

DÜŞEYARA Hakkında Daha Fazla Bilgi

Bu çok yönlü işlev hakkında daha fazla bilgi edinmek istiyorsanız şu makalelere göz atın:

• DÜŞEYARA ile Excel'de iki sütun nasıl karşılaştırılır

• DÜŞEYARA ile tutarsızlıklar nasıl bulunur?

Artık DÜŞEYARA işlevini ayarladığınıza göre, onun yaptığı şeyi farklı bir şekilde yapabilen diğer iki işleve bir göz atalım: DİZİN ve KAÇINCI.

Birden Çok Değeri Aramak İçin INDEX ve MATCH Nasıl Kullanılır?

Birden çok satırda birden çok değer bulmak için DİZİN ve KAÇINCI işlevlerini birleştirebilirsiniz.

Excel'deki DİZİN işlevi, belirtilen aralıktaki bir hücreye bir değer veya başvuru döndürür.

=INDEX(dizi, satır_sayısı, [sütun_sayısı])

• dizi: Değer için aranacak hücre aralığı.

• satır_sayısı: Bir değerin döndürüleceği dizi içindeki satır numarası.

• sütun_sayısı: (İsteğe bağlı) Değer döndürülecek dizi içindeki sütun numarası. Atlanırsa, işlev tüm satırı döndürür.

Excel'deki KAÇINCI işlevi, belirli bir aralıktaki bir değerin konumunu döndürür.

=MATCH(aranan_değer, aranan_dizi, [eşleşme_türü])

• aranan_değer: aranan_dizi içinde aranacak değer.

• aranan_dizi: Aranan_değer için aranacak hücre aralığı.

• match_type: (İsteğe bağlı) Gerçekleştirilecek eşleşme türü. Atlanırsa, işlev tam bir eşleşme gerçekleştirir.

Excel 365'te INDEX ve MATCH'ı Birlikte Kullanma

Excel'de birden çok değer aramak üzere INDEX ve MATCH'ı birlikte kullanmak için bir dizi formülü kullanmanız gerekir.

Önceki örnek verilerle çalışırken, Excel 365'teki formül şu şekildedir:

=DİZİN(A1:A5, MATCH({“elma”,”muz”,”kiraz”}, A1:A5, 0))

Yukarıdaki örnek şu şekilde bozulur:

• DİZİN: Bu, belirli bir satır ve sütun numarasına göre belirli bir aralıktaki bir hücrenin değerini döndürür. Bu durumda, A1:A5 aralığındaki değeri döndürür.

• A1:A5: Değeri aradığınız ve sonucun döndürüleceği tanımlı tablo aralığıdır.

• KAÇINCI: bu, bir hücre aralığında belirtilen bir öğeyi arar ve bu öğenin aralıktaki göreli konumunu döndürür.

• {“apple”,”banana”,”cherry”}: bu, aramak istediğiniz değerleri içeren dizi sabitidir.

• A1:A5: bu, MATCH'ın dizi sabitindeki değerleri arayacağı aralıktır.

• 0: bu, KAÇINCI işlevi için eşleme türüdür. Bu durumda, 0'dır, yani yakın bir eşleşme yerine tam bir eşleşme arıyorsunuz demektir.

 Bu resim, formülü çalışırken gösterir:

Excel'in Eski Sürümleriyle Çalışmak

Dinamik dizileri desteklemeyen daha eski bir Excel dosyası kullanıyorsanız (ör. Excel 2019 veya öncesi), farklı bir yaklaşım kullanmanız gerekir.

Eski sürümler formüllerin bitişik hücrelere "dökülmesini" desteklemediğinden, kullanımı üç ayrı formüle ayırmanız gerekir.

Bu adımları takip et:

1. İlk öğe için sonucu istediğiniz hücreye tıklayın (örneğin, B1 hücresi)

2. Aşağıdaki formülü yazın:

3. =DİZİN(A1:A5, KAÇINCI(“elma”, A1:A5, 0))

4. Formülü çalıştırmak için Enter tuşuna basın.

5. B2 hücresine şu formülü yazın: =INDEX(A1:A5, MATCH(“banana”, A1:A5, 0))

6. B3 hücresine şu formülü yazın: =INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

Bu resim B3 hücre referansını gösterir:

Birden çok değer bulmanızı sağlayan yalnızca DİZİN ve KAÇINCI işlevleri değildir. Bir sonraki bölümde, alternatif olarak EĞER işlevini nasıl kullanabileceğinize bakıyoruz.

Birden Çok Değer Bulmak İçin EĞER İşlevini Kullanma

Belirli ölçütlere göre birden çok hücre değeri aramanın başka bir yolu, EĞER işlevini diğer işlevlerle birlikte kullanmaktır.

EĞER işlevi, birden fazla koşulu test etmenize ve bu testlerin sonucuna bağlı olarak farklı sonuçlar döndürmenize olanak tanır.

Örneğin, Ürün ve Satış sütunları içeren bir satış verileri tablonuz olduğunu varsayalım. Üç üründen ikisinin satış tutarını aramak ve toplamak istiyorsunuz.

Excel'in Geçerli Sürümleri

Ürünün “Elma” veya “Muz” olduğu Satış sütununun toplamını EĞER işlevini kullanarak bulmak için EĞER, TOPLA ve VEYA işlevleriyle bir dizi formülü kullanabilirsiniz.

Verilerinizin A1 hücresinde başladığını varsayarsak, aşağıdaki formülü kullanın:

=TOPLA(EĞER((A2:A4=”Elma”)+(A2:A4=”Muz”), B2:B4, 0))

(A2:A4=”Elma”)+(A2:A4=”Muz”) bölümü , A2:A4 aralığındaki hücre “Elma” veya “Muz” içeriyorsa 1, aksi takdirde 0 değerine sahip bir dizi oluşturur. .

IF ifadesi, dizi bağımsız değişkeninin her bir öğesini kontrol eder. Değer 1 ise (yani ürün “Elma” veya “Muz” ise), Satış sütununda karşılık gelen değeri alır (B2:B4 aralığı); aksi halde 0 alır.

TOPLA işlevi, EĞER işlevinden gelen değerleri toplayarak hem "Elma" hem de "Muz" için satış değerlerini etkili bir şekilde toplar.

Bu resim, arama aralığındaki formülü çalışırken gösterir:

Excel'in Eski Sürümleri

Excel 2019 veya önceki sürümlerde dizi formülü kullanmanız gerekir. Bu adımları takip et:

1. Formülü yazın ama Enter'a basmayın.

2. Dizi formülü yapmak için Ctrl + Shift + Enter tuşlarına basın.

Excel, formülün etrafına süslü parantezler {} ekleyerek bunun bir dizi formülü olduğunu belirtir.

Ardından, SUMPRODUCT'ı kriterlerinize göre çeşitli değerleri aramak için nasıl kullanabileceğinize bakacağız. Hadi gidelim!

Birden Fazla Kriter için SUMPRODUCT Nasıl Kullanılır?

TOPLA işlevi, birden çok ölçüte göre birden çok değer aramanıza da olanak tanır.

Bir dizi formülünün kullanılmasını gerektirmediğinden sözdizimi, Excel sürümünden bağımsız olarak aynıdır.

Önceki örnektekiyle aynı verileri kullanan formül şöyle görünür:

=SUMPRODUCT((A2:A4=”Elma”)+(A2:A4=”Muz”), B2:B4)

(A2:A4=”Elma”)+(A2:A4=”Muz”) bölümü , A2:A4 aralığındaki hücre “Elma” veya “Muz” içeriyorsa 1, aksi takdirde 0 değerine sahip bir dizi oluşturur. .

SUMPRODUCT işlevi, dizinin öğelerini Satış sütunundaki (B2:B4 aralığı) karşılık gelen öğelerle çarpar. Ardından, hem "Elma" hem de "Muz" için satış değerlerini etkili bir şekilde toplayarak ortaya çıkan değerleri toplar.

Aşağıdaki formül eylem halinde gösterir:

Excel işlevleri beklendiği gibi çalıştıklarında harikadır, ancak bazen hatalarla karşılaşabilirsiniz. Bir sonraki bölümde, bazı yaygın hataları ve bunlarla nasıl başa çıkabileceğinizi ele alacağız.

Arama İşlevlerinde Yaygın 3 Hata

Arama işlevleri bazen sinir bozucu ve giderilmesi zaman alan hatalar döndürebilir. Karşılaşacağınız en yaygın üç hata şunlardır:

1. #YOK Hatalar

2. #REF! Hatalar

3. Dairesel Hatalar

1. #YOK Hatalar

#YOK hatası, arama değeri arama dizisinde bulunamadığında oluşur.

Aşağıdakiler de dahil olmak üzere bu hatanın ortaya çıkmasının birkaç nedeni vardır:

• arama değeri yanlış yazılmış veya yanlış.

• arama dizisi artan düzende sıralanmaz.

• arama değeri veri kümesinde değil.

Aranan değer veri kümesinde olmadığında, bu yararlı bir bilgidir. Bununla birlikte, deneyimsiz Excel kullanıcıları, #YOK'un formülde bir şeylerin ters gittiği anlamına geldiğini düşünebilir. Bir sonraki bölüm, bunun nasıl daha kullanıcı dostu hale getirileceğini göstermektedir.

2. #REF! Hatalar

#REF! arama dizisi veya dönüş dizisi silindiğinde veya taşındığında hata oluşur.

Bu hata, arama işlevindeki hücre referansları güncellenerek düzeltilebilir.

3. Döngüsel Hatalar

İşlevleri karmaşık formüllerde birleştirdikçe, Excel size döngüsel bir başvurunuz olduğunu söyleyebilir.

Excel'de döngüsel başvurular bulma kılavuzumuzu kullanarak bunları araştırmayı daha kolay bulacaksınız .

Arama İşlevleriyle IFERROR Nasıl Kullanılır?

IFERROR işlevi, arama işlevlerindeki hataları işlemek için yararlı bir araçtır. Arama işlevi bir hata döndürürse döndürülecek bir değer veya formül belirtmenizi sağlar.

IFERROR işlevinin sözdizimi aşağıdaki gibidir:

=EĞERHATA(değer; eğer_hataysa değer)

· değer: değerlendirmek istediğiniz değer veya formül.

· Value_if_error: ilk bağımsız değişken bir hata döndürürse döndürülecek değer veya formül.

Örneğin, bir tabloda birden çok değer arayan bir DÜŞEYARA işleviniz olduğunu varsayalım. Aşağıdaki resimde, aranan veri aralığında değerlerden biri bulunmamaktadır.

Gördüğünüz gibi, deneyimsiz Excel kullanıcıları için kafa karıştırıcı olabilecek #YOK hatası görüntüleniyor.

Bunun yerine, boş bir hücre veya şu söz dizimiyle “Bulunamadı” yazan bir mesaj görüntülemek için IFERROR'u kullanabilirsiniz:

=EĞERGÖR(DÜŞEYARA(aranan_değer, tablo_dizisi, sütun_index, YANLIŞ), “Bulunamadı”)

Bu örnekte, DÜŞEYARA işlevi bir hata döndürürse, EĞERHANGİ işlevi bunun yerine "Bulunamadı" mesajını döndürür.

Bu resim, formülü çalışırken göstermektedir. Sütun B eksik değere sahipken, sütun C ve sütun D eşleşmeler buldu.

Şimdiye kadar pek çok zemini ele aldık ve sonunda bir sonraki bölümün konusu olan aramalar için daha gelişmiş teknikleri öğrenmeye hazırsınız.

7 Gelişmiş Arama Tekniği

Excel'de birden çok değer aramak, özellikle büyük veri kümeleriyle uğraşırken zorlu bir görev olabilir. Yavaş işleme ile ilgili performans sorunlarıyla karşılaşabilirsiniz.

İşlemi daha kolay ve verimli hale getirmek için kullanabileceğiniz yedi gelişmiş arama tekniği vardır.

• Göreceli konum araması

• KÜÇÜK işlevi

• SATIR işlevi

• FİLTRE İşlevi

• Yardımcı sütunlar

• Dinamik Diziler

• Güç Sorgusu

1. Göreceli Konum Arama

Excel'de birden çok değeri aramanın en basit yollarından biri, göreli konum aramayı kullanmaktır. Bu, istenen değeri bulmak için belirli bir hücreden satır ve sütun ofsetlerinin belirtilmesini içerir.

Örneğin, bir veri tablonuz varsa ve belirli bir hücrenin iki satır aşağı ve üç sütun sağındaki bir değeri aramak istiyorsanız, aşağıdaki formülü kullanabilirsiniz:

=OFFSET(hücre, 2, 3)

2. KÜÇÜK İşlevi

Çoklu değer arama için başka bir yararlı teknik, KÜÇÜK işlevini kullanmaktır. Bu işlev, bir hücre aralığındaki n'inci en küçük değeri döndürür.

Bu işlevi, INDEX ve MATCH gibi diğer arama işlevleriyle birleştirerek, belirli ölçütlere göre birden çok değer arayabilirsiniz.

Örneğin, aşağıdaki formül, belirli bir koşulu karşılayan bir hücre aralığındaki en küçük ikinci değeri arar:

=INDEX(veri, KAÇINCI(KÜÇÜK(EĞER(ölçüt, aralık), 2), aralık, 0))

3. SATIR İşlevi

SATIR işlevi, Excel'de birden çok değer araması için de kullanılabilir. Bu işlev, bir veri tablosundaki hücrelere başvurmak için kullanılabilen belirli bir hücrenin satır numarasını döndürür.

Örneğin, aşağıdaki formül, benzersiz bir tanımlayıcıya dayalı olarak bir tablodaki değeri arar:

=INDEX(veri, MATCH(benzersiz_tanımlayıcı, veri[benzersiz_tanımlayıcı], 0), sütun_index_number)

4. FİLTRE İşlevi

FİLTRE işlevi, Excel'in eski sürümlerinde mevcut değildir.

Excel 365'te, belirli ölçütlere göre bir hücre aralığını filtrelemek ve yalnızca bu ölçütleri karşılayan değerleri döndürmek için kullanabilirsiniz. Bu sözdizimi ve üç bağımsız değişkendir:

=FILTER(dizi, dahil et, [eğer_boşsa])

• dizi: Filtrelemek istediğiniz belirli veriler.

• include: İnclude bağımsız değişkeni, diziye uygulamak istediğiniz ölçüt veya koşullardır.

• [if_empty] (isteğe bağlı): İçerme bağımsız değişkeninde belirtilen ölçütleri karşılayan satır veya sütun yoksa döndürülecek değer.

Örneğin, aşağıdaki formül, birinci sütundaki üç öğeden ikisinin eşleşmelerini bulmak ve ikinci sütunda bunlara karşılık gelen değerleri toplamak için örnek veriler üzerinde çalışır.

=TOPLA(FILTER(B2:B4, (A2:A4=”Elma”)+(A2:A4=”Muz”)))

Bu resim, kaç satırın eşleştirildiğini ve farklı değerlerin toplamını gösterir:

5. Yardımcı Sütunlar

DÜŞEYARA gibi bir işlevde birden çok alanı birleştirmek için bir yardımcı sütun kullanabilirsiniz.

Ayrı bir sayfada ad ve soyadlarla çalıştığınızı varsayalım. Bunları, son formülde başvurulan bir yardımcı sütunda birleştirebilirsiniz.

6. Dinamik Diziler

Daha önceki örneklerde öğrendiğiniz gibi Microsoft 365 kullanıcıları, Excel'de birden çok değer aramak için dinamik dizilerden yararlanabilir.

Dinamik diziler, tek bir formülden birden fazla değer döndürmenizi sağlayarak büyük miktarda veriyi aramayı kolaylaştırır.

7. Güç Sorgusu

Power Query, Excel'de birden çok ölçüte göre değer döndürmek için kullanılabilen güçlü bir araçtır.

Örneğin, bu video bir e-tablodaki dağınık verileri bulur ve temizler.

Son düşünceler

Bu, Excel'de birden çok değer arama sanatına yaptığımız derin dalışı tamamlıyor! DÜŞEYARA, DİZİN, KAÇINCI ve dizi formüllerinde ustalaştıktan sonra, kendinizi sıcak bir bıçak gibi karmaşık veri kümeleri arasında hızla ilerlerken bulacaksınız.

Anahtar, sözdizimini ve her formülün arkasındaki mantığı anlamaktır. Bu formüllerin karmaşık olabileceğini unutmayın; bu nedenle, önemli veri analizi için onlara güvenmeden önce zaman ayırmanız ve formüllerinizi kapsamlı bir şekilde test etmeniz önemlidir.

Excel güçlü bir araçtır, ancak yalnızca kullanıcı kadar iyidir. Bu yüzden, bu becerileri geliştirmeye devam edin, denemeye devam edin ve yakında çok değerli aramaların ustası olacaksınız. Bir dahaki sefere kadar, bu sayıları hesaplamaya devam edin ve zor işleri Excel'e yaptırın!