Skip to content

Excel’in DÜŞEYARA İşleviyle Birden Çok Veri Alanını Hızla Bulun

7 de Mayıs de 2021

Excel’in DÜŞEYARA işlevini SÜTUN işleviyle birleştirerek, bir veritabanı veya veri tablosunun tek bir satırından birden çok değer döndüren bir arama formülü oluşturabilirsiniz. Tek bir veri kaydından birden çok değer döndüren bir arama formülü oluşturmayı öğrenin. Bu makaledeki talimatlar Excel 2019, 2016, 2013, 2010 için geçerlidir; ve Microsoft 365 için Excel.

Excel DÜŞEYARA ile Birden Çok Değer Döndürme

Arama formülü, SÜTUN işlevinin DÜŞEYARA’nın içine yerleştirilmesini gerektirir. Bir işlevin yuvalanması, ikinci işlevi birinci işlevin bağımsız değişkenlerinden biri olarak girmeyi içerir.

Eğitim Verilerini Girin

Bu eğiticide, SÜTUN işlevi sütun dizini olarak girilmiştir. DÜŞEYARA için sayı bağımsız değişkeni. Öğreticinin son adımı, seçilen parça için ek değerler almak üzere arama formülünün ek sütunlara kopyalanmasını içerir. Bu öğreticinin ilk adımı, verileri bir Excel çalışma sayfasına girmektir. Bu eğitimdeki adımları takip etmek için, aşağıdaki resimde gösterilen verileri aşağıdaki hücrelere girin:

  • En üst veri aralığını D1 ila G1 hücrelerine girin.
  • İkinci aralığı D4 ila G10 hücrelerine girin.

Bu öğreticide oluşturulan arama kriterleri ve arama formülü, çalışma sayfasının 2. satırına girilir. Bu öğretici, resimde gösterilen temel Excel biçimlendirmesini içermez, ancak bu, arama formülünün nasıl çalıştığını etkilemez.

Veri Tablosu için Adlandırılmış Aralık Oluşturma

Adlandırılmış bir aralık, bir formüldeki bir dizi veriye başvurmanın kolay bir yoludur. Veriler için hücre referanslarını yazmak yerine, aralığın adını yazın. Adlandırılmış bir aralık kullanmanın ikinci bir avantajı, formül çalışma sayfasındaki diğer hücrelere kopyalandığında bile bu aralık için hücre referanslarının hiçbir zaman değişmemesidir. Aralık adları, formülleri kopyalarken hataları önlemek için mutlak hücre referansları kullanmaya bir alternatiftir. Aralık adı, veriler için başlıkları veya alan adlarını içermez (4. satırda gösterildiği gibi), yalnızca verileri içerir.

  1. Vurgulamak hücreler D5 -e G10 çalışma sayfasında.

  2. İmleci A sütununun yukarısında bulunan Ad Kutusuna getirin, yazın Tablo, sonra basın Giriş. D5 ila G10 hücreleri, Tablo aralık adına sahiptir.

  3. DÜŞEYARA tablo dizisi bağımsız değişkeni için aralık adı, bu öğreticide daha sonra kullanılır.

DÜŞEYARA İletişim Kutusunu açın

Arama formülünü doğrudan bir çalışma sayfasındaki bir hücreye yazmak mümkün olsa da, birçok kişi sözdizimini düz tutmakta zorlanır – özellikle bu öğreticide kullanılan gibi karmaşık bir formül için. Alternatif olarak, DÜŞEYARA İşlev Bağımsız Değişkenleri iletişim kutusunu kullanın. Hemen hemen tüm Excel işlevlerinde, işlevin bağımsız değişkenlerinin her birinin ayrı bir satıra girildiği bir iletişim kutusu vardır.

  1. Seçiniz E2 hücresi çalışma sayfasının. Bu, iki boyutlu arama formülünün sonuçlarının görüntüleneceği konumdur.

  2. Şeritte şuraya gidin: Formüller sekme ve seç Arama ve Referans.

  3. Seçiniz DÜŞEYARA açmak için İşlev Bağımsız Değişkenleri iletişim kutusu.

  4. İşlev Bağımsız Değişkenleri iletişim kutusu, DÜŞEYARA işlevinin parametrelerinin girildiği yerdir.

Arama Değeri Bağımsız Değişkenini Girin

Normalde arama değer, veri tablosunun ilk sütunundaki bir veri alanıyla eşleşir. Bu örnekte, arama değer, bilgi bulmak istediğiniz parçanın adını ifade eder. Arama için izin verilen veri türleri değer metin verileri, mantıksal değerler, sayılar ve hücre referanslarıdır.

Mutlak Hücre Referansları

Formüller Excel’de kopyalandığında, hücre referansları yeni konumu yansıtacak şekilde değişir. Bu olursa, D2, arama için hücre referansı değer, değişir ve F2 ve G2 hücrelerinde hatalar oluşturur. Formüller kopyalandığında mutlak hücre başvuruları değişmez. Hataları önlemek için, D2 hücre referansını mutlak hücre referansına dönüştürün. Mutlak bir hücre referansı oluşturmak için F4 tuşuna basın. Bu, hücre başvurusunun etrafına $ D $ 2 gibi dolar işaretleri ekler.

  1. Fonksiyon Bağımsız Değişkenleri iletişim kutusunda, imleci aranan_değer Metin kutusu. Ardından çalışma sayfasında şunu seçin: hücre D2 bu hücre referansını, aranan_değer. Hücre D2, parça adının girileceği yerdir.

  2. Ekleme noktasını hareket ettirmeden, F4 D2’yi mutlak hücre başvurusuna $ D $ 2 dönüştürmek için anahtar.

  3. Öğreticinin bir sonraki adımı için DÜŞEYARA işlevi iletişim kutusunu açık bırakın.

Tablo Dizisi Bağımsız Değişkenini Girin

Tablo dizisi, arama formülünün istediğiniz bilgileri bulmak için aradığı veri tablosudur. Tablo dizisi en az iki veri sütunu içermelidir. İlk sütun, arama değeri bağımsız değişkenini (önceki bölümde ayarlanmış olan) içerirken, ikinci sütun, belirttiğiniz bilgileri bulmak için arama formülü ile aranır. Tablo dizisi bağımsız değişkeni, veri tablosu için hücre referanslarını içeren bir aralık olarak veya bir aralık adı olarak girilmelidir. Veri tablosunu DÜŞEYARA işlevine eklemek için imleci masa dizisi iletişim kutusundaki metin kutusu ve yazın Tablo bu bağımsız değişken için aralık adını girmek için.

COLUMN İşlevini İç içe Geçirme

Normalde, DÜŞEYARA yalnızca bir veri tablosunun bir sütunundan veri döndürür. Bu sütun, sütun dizin numarası bağımsız değişkeni tarafından belirlenir. Ancak bu örnekte, üç sütun vardır ve arama formülünü düzenlemeden sütun dizin numarasının değiştirilmesi gerekir. Bunu başarmak için, SÜTUN işlevini DÜŞEYARA işlevinin içine Sütun_indis_sayısı bağımsız değişkeni olarak iç içe geçirin. İşlevleri iç içe yerleştirirken Excel, bağımsız değişkenlerini girmek için ikinci işlevin iletişim kutusunu açmaz. SÜTUN işlevi manuel olarak girilmelidir. SÜTUN işlevinin yalnızca bir bağımsız değişkeni vardır, bir hücre başvurusu olan Başvuru bağımsız değişkeni. SÜTUN işlevi, Referans bağımsız değişkeni olarak sağlanan sütunun numarasını döndürür. Sütun harfini sayıya dönüştürür. Bir öğenin fiyatını bulmak için veri tablosunun 2. sütunundaki verileri kullanın. Bu örnek, Sütun_indis_sayısı bağımsız değişkenine 2 eklemek için Başvuru olarak B sütununu kullanır.

  1. İçinde İşlev Bağımsız Değişkenleri iletişim kutusunda, imleci Sütun_indis_sayısı metin kutusu ve yazım SÜTUN (. (Açık yuvarlak köşeli ayraç dahil ettiğinizden emin olun.)

  2. Çalışma sayfasında seçin hücre B1 bu hücre referansını Referans bağımsız değişkeni olarak girmek için.

  3. Bir yazın yuvarlak köşeli parantez kapatma SÜTUN işlevini tamamlamak için.

DÜŞEYARA Aralığı Arama Bağımsız Değişkenini Girin

DÜŞEYARA’nın Aralık_bak bağımsız değişkeni, DÜŞEYARA’nın Lookup_value ile tam veya yaklaşık bir eşleşme bulup bulmayacağını belirten mantıksal bir değerdir (DOĞRU veya YANLIŞ).

  • DOĞRU veya Atlanmış: DÜŞEYARA, Lookup_value ile yakın bir eşleşme döndürür. Tam bir eşleşme bulunmazsa, DÜŞEYARA sonraki en büyük değeri döndürür. Tablo_dizisinin ilk sütunundaki veriler artan sırada sıralanmalıdır.
  • YANLIŞ: DÜŞEYARA, Lookup_value ile tam bir eşleşme kullanır. Tablo_dizisinin ilk sütununda arama değeriyle eşleşen iki veya daha fazla değer varsa, bulunan ilk değer kullanılır. Tam bir eşleşme bulunmazsa, #YOK hatası döndürülür.

Bu öğreticide, belirli bir donanım öğesi hakkında belirli bilgiler aranacaktır, bu nedenle Aralık_bak’ı YANLIŞ olarak ayarlanır. Fonksiyon Bağımsız Değişkenleri iletişim kutusunda, imleci Aralık_bak metin kutusuna getirin ve şunu yazın: Yanlış DÜŞEYARA’ya veriler için tam bir eşleşme döndürmesini söylemek için.

Seçiniz TAMAM MI Arama formülünü tamamlamak ve iletişim kutusunu kapatmak için. Arama kriterleri D2 hücresine girilmediğinden E2 hücresi #YOK hatası içerecektir. Bu hata geçicidir. Bu eğitimin son adımında arama kriterleri eklendiğinde düzeltilecektir.

Arama Formülünü Kopyalayın ve Kriter Girin

Arama formülü, bir seferde veri tablosunun birden çok sütunundaki verileri alır. Bunu yapmak için, arama formülünün bilgi almak istediğiniz tüm alanlarda bulunması gerekir. Veri tablosunun 2., 3. ve 4. sütunlarından (fiyat, parça numarası ve tedarikçinin adı) veri almak için, Lookup_value olarak kısmi bir ad girin. Veriler çalışma sayfasında normal bir düzende yerleştirildiğinden, arama formülünü kopyalayın. E2 hücresi -e hücreler F2 ve G2. Formül kopyalandıkça, Excel, SÜTUN işlevindeki (B1 hücresi) göreli hücre başvurusunu formülün yeni konumunu yansıtacak şekilde günceller. Excel mutlak hücre başvurusunu değiştirmez (örneğin $ D $ 2) ve formül olarak adlandırılan aralık (Tablo) kopyalanır. Excel’de veri kopyalamanın birden fazla yolu vardır, ancak en kolay yol Dolgu Tutamaçını kullanmaktır.

  1. Seçiniz E2 hücresi, arama formülünün bulunduğu yer, onu etkin hücre yapmak için.

  2. Doldurma tutamacını üstünden G2 hücresi. F2 ve G2 hücreleri, E2 hücresinde bulunan #YOK hatasını görüntüler.

  3. Veri tablosundan bilgi almak üzere arama formüllerini kullanmak için çalışma sayfasında şunu seçin: D2 hücresi, yazın Araç, ve bas Giriş.

    Aşağıdaki bilgiler E2 ila G2 hücrelerinde görüntülenir.

    • E2: 14,76 $ – bir widget’ın fiyatı
    • F2: PN-98769 – bir parçacığın parça numarası
    • G2: Widgets Inc. – widget tedarikçisinin adı
  4. DÜŞEYARA dizisi formülünü test etmek için, diğer parçaların adını D2 hücresine yazın ve E2 – G2 hücrelerindeki sonuçları gözlemleyin.

  5. Arama formülünü içeren her hücre, aradığınız donanım öğesi hakkında farklı bir veri parçası içerir.

COLUMN gibi iç içe geçmiş işlevlere sahip DÜŞEYARA işlevi, diğer verileri arama referansı olarak kullanarak tablo içindeki verileri aramak için güçlü bir yöntem sağlar.