Birden fazla ölçüt ile bir Excel arama formülü nasıl oluşturulur

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ğitici, bir tedarikçi bulmak için birden fazla ölçüt kullanan bir arama formülü oluşturma adım adım örnek içerir.titanyum widget'ları Örnek bir veritabanında. Kendi özel arama formüllerinizi oluşturmanın ne kadar kolay olduğunu öğrenmek için takip edin.

01/08

Excel Çalışma Kitabınızı Verilerle Hazırlayın

Bu eğitimdeki adımları izleyebilmek için, yukarıdaki resimde gösterildiği gibi, örnek verileri aşağıdaki hücrelere girerek başlamalısınız. 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.

• Üst veri aralığını hücrelere girin D1 için F2
• İkinci aralığı hücrelere girin D5 için F11

Öğ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/08

Excel'de bir INDEX İşlevi Oluştur

INDEX işlev, Excel'de birden çok biçime sahip olanlardan biridir. İşlevi bir Dizi Formu ve bir Referans Formu. 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 tedarikçinin adını bilmek istiyoruz. INDEX İşlevi oluşturmak için şu adımları izleyin:

1. Hücreyi tıklayın F3 aktif hücre yapmak için - bu yuvalanmış işlevi gireceğiz.
2. Tıkla Formüller şerit menüsünün sekmesi.
3. Seçmek Arama ve Referans işlevini açmak için şerit çubuğundan aşağı açılır.
4. Tıklamak INDEX listeye getirmek için Bağımsız Değişkenleri Seç iletişim kutusu.
5. Seç dizi, satır_sayısı, col_num iletişim kutusundaki seçenek.
6. Click tamam INDEX işlevi iletişim kutusunu açmak için; Bu, Excel'de Formula Builder'ı açacaktır.
7. İçinde Formula Oluşturucu, tıkla Dizi hat.
8. Hücreleri vurgula D6 için F11Oluşturucuya aralığı girmek için çalışma sayfasına tıklayıp sürükleyerek.

Excel'in eski sürümlerinde Formula Oluşturucu tarafından değiştirilecek İşlev Argümanları penceresi. Bu öğreticide belirtilen adımların aynısını pencereye uygulayın.

03/08

Nested MATCH Fonksiyonunu Başlat

Bir işlevi diğerinin içine yerleştirirken, ikinci veya iç içe geçmiş işlevin formül oluşturucusunu açmak mümkün değildir, gerekli argümanları girin. Yuvalanmış işlev, ilk işlevin sıralamasından biri olarak yazılmalıdır.

Fonksiyonları manuel olarak girerken, fonksiyonun argümanları birbirinden virgülle ayrılır.

Yuvalanmış MATCH fonksiyonuna girmenin ilk adımı lookup_value argüman. 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 değerini genişletmeliyiz; tonun işareti ve işareti, iki veya daha fazla hücre referansını, ve işareti simgesiyle birleştirerek birleştirerek yapılır. &.

1. İçinde Formula Oluşturucu, tıkla satır_numarası hat.
2. İşlev adını yazın MAÇ açık bir yuvarlak köşeli ayraç.
3. Hücreyi tıklayın D3 Bu hücre referansını diyalog kutusuna girmek için
4. Hücre referansından sonra bir ampersan yazın D3 ikinci hücre referansı eklemek için.
5. Hücreyi tıklayın E3 Bu ikinci hücre referansını girmek için
6. Hücre referansından sonra virgül yaz E3 MATCH işlevinin girişini tamamlamak için lookup_value argüman.

Eğiticinin son aşamasında Lookup_values çalışma sayfasının D3 ve E3 hücrelerine girilecektir.

04/08

Nested MATCH Fonksiyonunu tamamlayın

Bu adım ekleyerek kapsar arama_dizisi iç içe geçmiş için argüman MAÇ işlevi. 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 sadece bir dizi arar, böylece çoklu dizileri gireriz, dizileri birlestirmek için tekrar birlikte kullaniriz.

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

1. Tıkla satır_numarası Geçerli girişin sonundaki ekleme noktasını yerleştirmek için virgül sonrası satır.
2. Hücreleri vurgula D6 için D11 aralığa girmek için çalışma sayfasında - bu, işlevin aranacağı ilk dizidir.
3. Hücre referanslarından sonra bir ampersan yazın D6: D11 çünkü fonksiyonun iki diziyi aramasını istiyoruz.
4. Hücreleri vurgula E6 için E11 aralığa girmek için çalışma sayfasında - bu, fonksiyonun aranacağı ikinci dizidir.
5. Hücre referansından sonra virgül yaz E3 girişini tamamlamak için MAÇ Fonksiyonunuzda arama_dizisi argüman.
6. Bırak INDEX öğretici bir sonraki adım için işlev iletişim kutusu açın.

05/08

MATCH Türü Bağımsız Değişkeni Ekle

MATCH fonksiyonunun üçüncü ve son argümanı Eşleşme türü argüman; Excel'e nasıl eşleşeceğini anlatıyor lookup_value Lookup_array'deki değerlerle - mevcut seçenekler 1, 0 veya -1'dir.

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

• Eğer Match_type = 1 veya atlanır: 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.
• Eğer Match_type = 0: MATCH, Lookup_value değerine tam olarak eşit olan ilk değeri bulur. Lookup_array verileri herhangi bir sırada sıralanabilir.
• Eğer Match_type = -1: MATCH, Lookup_value değerinden daha büyük veya eşit olan en küçük değeri bulur. Lookup_array verileri azalan düzende sıralanmalıdır.

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

1. Üzerindeki virgülün ardından satır_numarası satır, bir sıfır yazın - 0 - Yuvalı fonksiyonun tam eşleşmeleri hücrelere girdiğimiz terimlere döndürmesini istediğimizden D3 ve E3.
2. Kapanış yuvarlak braketi yazın - ) - tamamlamak için MAÇ işlevi.
3. Bırak Formula Oluşturucu eğitimde bir sonraki adıma açık.

06/08

INDEX Fonksiyonunun Tamamlanması

Şimdi bu MAÇ fonksiyon tamamlandıktan sonra Formula Builder'ın üçüncü satırına gideceğiz ve son argüman için INDEX işlevi. Bu üçüncü ve son argüman sütun_sayısı Excel'e aralıktaki sütun numarasını bildiren argüman D6 için F11; Bu, işlev tarafından döndürülmesini istediğimiz bilgileri bulacağı yerdir. Bu durumda, bir tedarikçi titanyum aletler.

1. Tıkla sütun_sayısı hat.
2. Üç numarayı girin - 3 - Bu satırda, aralığın üçüncü sütununda veri aradığımızdan D6 için F11.
3. Tekrar bırakın Formula Oluşturucu eğitimde bir sonraki adıma açık.

07/08

Dizi Formülü Oluşturulması

Kapatmadan önce Formula Oluşturucuiç içe geçmiş işlevimizi bir dizi formülüne dönüştürmemiz gerekir; Bu, fonksiyonumuzun veri tablosunda birden çok terim aramasını sağlayan şeydir. Bu eğiticide, iki terimi eşleştirmeye çalışıyoruz: widget'lar 1. sütundan ve Titanyum 2. sütundan.

Excel'de bir dizi formülü oluşturmak, CTRL, SHIFT, ve GİRMEK Klavyede aynı anda tuşlar. Bir kez basıldığında, işlev, fonksiyonun artık bir dizi olduğunu belirten küme parantezleri ile çevrelenir.

1. Kapat Formula Oluşturucu tıklayarak tamam buton.
2. Ardından, hücre seç F4 ve basın Girmek formülü görüntülemek için
3. Formülü bir diziye dönüştürmek için, aynı anda CTRL + ÜSTKRKT + ENTER klavyenizde.
4. Doğru bir şekilde yapılırsa # N / A F3 hücresinde hata görünecektir - fonksiyona girdiğimiz hücre.
5. # N / A hata hücrede görünür F3 çünkü hücreler D3 ve E3 boş. D3 ve E3, fonksiyonu bulma işlevini söylediğimiz hücrelerdir. Lookup_values. Bu iki hücreye veri eklendiğinde, hata veritabanından alınan bilgilerle değiştirilecektir.

08/08

Arama Ölçütlerini Ekleme

Son olarak, arama terimlerini çalışma sayfamıza ekleyeceğiz. Önceki adımda belirtildiği gibi, şartları eşleştirmeyi düşünüyoruz widget'lar 1. sütundan ve Titanyum sütun 2'den ve yalnızca, formülümüzün 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.

1. Hücreyi tıklayın D3.
2. tip widget'lar ve basın Girmek klavyede tuşuna basın.
3. Hücreyi tıklayın E3.
4. tip Titanyum ve basın Girmek klavyede tuşuna basın.
5. Tedarikçinin adı Widgets Inc. F3 hücresinde görünmelidir - Titanyum Widget'leri satan tek tedarikçi listelendiği için fonksiyonun yeri.
6. F3 hücresini tıkladığınızda, çalışma sayfasının üstündeki formül çubuğunda tam işlev görüntülenir.

{= INDEX (D6: F11, MATCH (D3 ve E3, D6: D11 ve E6: E11, 0), 3)}

Ö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.