Çoklu Ölçütlü Excel Arama Formülü

Excel'de bir dizi formülü kullanarak, bir veritabanındaki veya veri tablosundaki bilgileri bulmak için birden çok ölçüt kullanan bir arama formülü oluşturabiliriz.

Dizi formülü, MATDE işlevinin INDEX işlevinin içine yerleştirilmesini içerir.

Bu eğitimde, örnek veritabanındaki bir titanyum Widget'ları tedarikçisi bulmak için birden fazla ölçüt kullanan bir arama formülü oluşturma adım adım bir adımı içerir.

Aşağıdaki öğretici konulardaki adımların ardından, yukarıdaki resimde görülen formülün oluşturulmasını ve kullanılmasını sağlar.

01/09

Öğretici Verilerini Girme

Öğreticideki ilk adım, verileri bir Excel çalışma sayfasına girmektir.

Öğreticideki adımları takip etmek için, yukarıdaki resimde gösterilen verileri aşağıdaki hücrelere girin .

• Üst veri aralığını D1 ila F2 hücrelerine girin
• İkinci aralığı D5 ila F11 hücrelerine girin

Bu eğitim sırasında oluşturulan dizi formülünü karşılamak için satır 3 ve 4 boş bırakılmıştır.

Öğretici, görüntüde görülen formatlamayı içermez, ancak bu, arama formülünün nasıl çalıştığını etkilemez.

Yukarıda görülenlere benzer formatlama seçenekleri ile ilgili bilgiler, bu Temel Excel Formatting Eğitimi'nde mevcuttur.

02/09

INDEX İşlevi Başlatma

INDEX işlevi, Excel'de birden çok biçime sahip olanlardan biridir. Bu işlevin bir Dizi Formu ve bir Referans Formu vardır .

Dizi Formu, bir veritabanından veya veri tablosundan gerçek verileri döndürürken, Referans Formu size hücre referansı veya verilerin tabloda yer almasını sağlar.

Bu yazıda, Array Formunu kullanacağız çünkü veritabanımızdaki bu tedarikçinin hücre referansı yerine titanyum aletler için bir tedarikçinin adını bilmek istiyoruz.

Her form, işleve başlamadan önce seçilmesi gereken farklı bir argüman listesine sahiptir.

Öğretici Adımlar

1. Aktif hücre yapmak için F3 hücresini tıklayın. İç içe geçmiş fonksiyona gireceğimiz yer burası.
2. Şerit menüsünün Formüller sekmesine tıklayın.
3. İşlev açılır listesini açmak için Şerit'ten Arama ve Referans'ı seçin.
4. Argüman Seç iletişim kutusunu açmak için listedeki INDEX'e tıklayın.
5. İletişim kutusundaki diziyi, row_num, col_num seçeneğini seçin.
6. INDEX işlevi iletişim kutusunu açmak için Tamam'a tıklayın.

03/09

INDEX İşlev Dizisi Değişkeni Girme

Gerekli ilk argüman Dizi argümanıdır. Bu argüman istenen veriler için aranacak hücre aralığını belirtir.

Bu öğretici için bu argüman örnek veritabanımız olacaktır .

Öğretici Adımlar

1. INDEX işlevi iletişim kutusunda Dizi satırına tıklayın.
2. Aralık diyaloğuna girmek için çalışma sayfasındaki D6 - F11 hücrelerini vurgulayın.

04/09

Nested MATCH Fonksiyonunu Başlatma

Bir fonksiyonu diğerinin içine yerleştirirken, gerekli argümanları girmek için ikinci veya iç içe geçmiş fonksiyonun diyalog kutusunu açmak mümkün değildir.

Yuvalanmış işlev, ilk işlevin argümanlarından biri olarak yazılmalıdır.

Bu öğreticide, iç içe geçmiş MATCH işlevi ve argümanları, INDEX işlev iletişim kutusunun ikinci satırına - Row_num satırına girilecektir .

Fonksiyonları manuel olarak girerken, fonksiyonun argümanlarının birbirinden virgülle ayrıldığını unutmamak önemlidir.

MATCH İşlevinin Lookup_value Argümanı Girme

Yuvalanmış MATCH fonksiyonuna girmenin ilk adımı, Lookup_value argümanını girmek.

Lookup_value , veritabanında eşleştirmek istediğimiz arama terimi için konum veya hücre referansı olacaktır.

Normalde, Lookup_value yalnızca bir arama kriteri veya terimini kabul eder. Birden çok kriteri aramak için, Lookup_value öğesini genişletmeliyiz.

Bu, " & " işareti ve işareti kullanılarak iki veya daha fazla hücre referansının birleştirilmesi veya birleştirilmesiyle yapılır.

Öğretici Adımlar

1. INDEX işlevi iletişim kutusunda, Row_num satırına tıklayın.
2. İşlev adı eşleşmesini ve ardından açık yuvarlak köşeli ayraç yazın " ( "
3. Bu hücre referansını diyalog kutusuna girmek için D3 hücresini tıklayın.
4. Bir ikinci hücre referansı eklemek için hücre referansı D3'ten sonra bir " ve " işareti yazın.
5. Bu ikinci hücre referansını iletişim kutusuna girmek için E3 hücresini tıklayın.
6. MATCH fonksiyonunun Lookup_value argümanının girişini tamamlamak için E3 hücre referansından sonra bir virgül "," yazın .
7. Eğiticideki bir sonraki adım için INDEX işlev iletişim kutusunu açık bırakın.

Eğiticinin son adımında, Lookup_values ​​çalışma sayfasının D3 ve E3 hücrelerine girilecektir.

05/09

MATCH İşlevi için Lookup_array Ekleme

Bu adım, iç içe geçmiş MATCH işlevi için Lookup_array argümanını eklemeyi kapsar.

Lookup_array , MATCH işlevinin, öğreticinin önceki adımında eklenen Lookup_value argümanını bulmak için arayacağı hücrenin aralığıdır .

Lookup_array argümanında iki arama alanı belirlediğimizden, Lookup_array için aynısını yapmalıyız . MATCH işlevi, belirtilen her bir terim için yalnızca bir dizi arar.

Birden fazla diziye girmek için dizileri birlikte birleştirmek için " ve " ampersanını tekrar kullanırız.

Öğretici Adımlar

Bu adımlar, INDEX işlevi iletişim kutusundaki Row_num satırında bir önceki adımda girilen virgül girdikten sonra girilmelidir.

1. Geçerli girişin sonundaki ekleme noktasını yerleştirmek için virgülden sonra Row_num satırına tıklayın.
2. Menüye girmek için çalışma sayfasındaki D6 - D11 hücrelerini vurgulayın. Bu, fonksiyonun aranacağı ilk dizidir.
3. Hücre referansları D6: D11'den sonra bir ampersan " & " yazın, çünkü fonksiyonun iki diziyi aramasını istiyoruz.
4. Menüye girmek için çalışma sayfasında E6 - E11 hücrelerini vurgulayın. Bu, fonksiyonun aranacağı ikinci dizidir.
5. MATCH fonksiyonunun Lookup_array argümanı girişini tamamlamak için E3 hücre referansından sonra bir virgül "," yazın .
6. Eğiticideki bir sonraki adım için INDEX işlev iletişim kutusunu açık bırakın.

06/09

Eşleme türünü ekleme ve MATCH İşlevini Tamamlama

MATCH fonksiyonunun üçüncü ve son argümanı Match_type argümanıdır.

Bu argüman, Excel'e Lookup_value öğesindeki Lookup_valray değerleriyle nasıl eşleşeceğini anlatır. Seçenekler şunlardır: 1, 0 veya -1.

Bu argüman isteğe bağlıdır. Eğer atlanırsa, işlev 1 varsayılan değerini kullanır.

• Match_type = 1 ise veya atlanırsa: MATCH, Lookup_value değerinden küçük veya eşit olan en büyük değeri bulur. Lookup_array verileri artan düzende sıralanmalıdır.
• match_type = 0 ise: MATCH, Lookup_value değerine tam olarak eşit olan ilk değeri bulur. Lookup_array verileri herhangi bir sırada sıralanabilir.
• Match_type = -1: MATCH öğesi, Lookup_value değerinden büyük veya eşit olan en küçük değeri bulursa. Lookup_array verileri azalan düzende sıralanmalıdır.

Öğretici Adımlar

Bu adımlar, INDEX işlevi iletişim kutusundaki Row_num satırında bir önceki adımda girilen virgül girdikten sonra girilmelidir.

1. Row_num satırındaki virgülü takip ederek, yuvalanmış işlevin, D3 ve E3 hücrelerine girdiğimiz terimlere tam eşleşmeleri döndürmesini istediğimizden, sıfır " 0 " yazın.
2. MATCH fonksiyonunu tamamlamak için kapanış yuvarlak braketi " ) " yazın.
3. Eğiticideki bir sonraki adım için INDEX işlev iletişim kutusunu açık bırakın.

07/09

INDEX İşlevine Geri Dön

Artık MATCH fonksiyonu yapıldıktan sonra açık diyalog kutusunun üçüncü satırına gideceğiz ve INDEX fonksiyonu için son argümanı gireceğiz.

Bu üçüncü ve son argüman, Excel'e D6 - F11 aralığındaki sütun numarasını söyleyen Column_num argümanıdır, burada fonksiyon tarafından döndürülen bilgiyi bulacaktır. Bu durumda, titanyum aletler için bir tedarikçi.

Öğretici Adımlar

1. İletişim kutusundaki Column_num satırına tıklayın.
2. D6 - F11 aralığının üçüncü sütununda veri aradığımız için bu satırda üç " 3 " (tırnak işareti yok) girin.
3. Tamam'ı tıklatmayın veya INDEX işlevi iletişim kutusunu kapatın. Eğitimde bir sonraki adım için açık kalmalıdır - dizi formülü oluşturulur .

08/09

Dizi Formülü Oluşturulması

İletişim kutusunu kapatmadan önce iç içe geçmiş işlevimizi dizi formülü haline getirmemiz gerekir.

Dizi formülü, veri tablosunda birden çok terim aramasını sağlayan şeydir. Bu eğitimde iki terimi eşleştirmeyi düşünüyoruz: Sütun 1'den gelen widget'lar ve sütun 2'den titanyum.

Excel'de bir dizi formülü oluşturmak, aynı anda klavyedeki CTRL , SHIFT ve ENTER tuşlarına basılarak yapılır.

Bu tuşlara birlikte basmanın etkisi, işlevi küme parantezleriyle sarmaktır: {} şimdi bir dizi formülü olduğunu belirten.

Öğretici Adımlar

1. Tamamlanan iletişim kutusu hala bu öğreticinin önceki adımından açıkken, klavyedeki CTRL ve SHIFT tuşlarına basılı tutun ve ardından ENTER tuşuna basın ve bırakın.
2. Doğru yapıldığında, iletişim kutusu kapanacak ve F3 hücresinde bir # N / A hatası görünecektir - işleve girdiğimiz hücre.
3. Hücre D3 ve E3 boş olduğundan, # N / A hatası F3 hücresinde görünür. D3 ve E3, öğreticinin 5. adımında Lookup_values ​​öğesini bulma işlevini söylediğimiz hücrelerdir. Bu iki hücreye veri eklendiğinde, hata veritabanından alınan bilgilerle değiştirilecektir.

09/09

Arama Ölçütlerini Ekleme

Eğiticideki son adım, arama terimlerini çalışma sayfamıza eklemektir.

Önceki adımda belirtildiği gibi, Sütun 1'den ve Titanium'dan gelen sütunlar widget 2'den eşleşmeye bakıyoruz.

Formül, yalnızca formülde, her iki terim için de veritabanındaki uygun sütunlarda bir eşleşme bulursa, değeri üçüncü sütundan döndürür.

Öğretici Adımlar

1. D3 hücresini tıklayın.
2. Widget yazın ve klavyede Enter tuşuna basın.
3. E3 hücresini tıklayın.
4. Titanyum yazın ve klavyede Enter tuşuna basın.
5. Tedarikçinin adı Widgets Inc. , F3 hücresinde görünmelidir - Titanyum Widget'ları satan tek tedarikçi listelendiği için fonksiyonun yeri.
6. F3 hücresini tıkladığınızda tam işlev
   {= INDEX (D6: F11, MATCH (D3 ve E3, D6: D11 ve E6: E11, 0), 3)}
   Çalışma sayfasının üstündeki formül çubuğunda görünür.

Not: Bizim örneğimizde titanyum widget'ları için tek bir tedarikçi vardı. Birden fazla tedarikçi varsa, veritabanında ilk önce listelenen tedarikçi işlev tarafından iade edilir.