Excel'in DÜŞEYARA işlevini SÜTUN işleviyle birleştirerek, bir veritabanının 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.
Alt Satır
Arama formülü, SÜTUN işlevinin DÜŞEYARA içinde yuvalanmasını gerektirir. Bir işlevi iç içe yerleştirmek, ikinci işlevi birinci işlevin argümanlarından biri olarak girmeyi içerir.
Öğretici Verilerini Girin
Bu öğreticide, DÜŞEYARA için sütun dizin numarası bağımsız değişkeni olarak COLUMN işlevi girilir. Eğiticideki 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 öğreticideki ilk adım, verileri bir Excel çalışma sayfasına girmektir. Bu öğreticideki 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 ile G1 arasındaki hücrelere girin.
- İkinci aralığı D4 ile G10 arasındaki hücrelere 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ış Bir Aralık Oluştur
Adlandırılmış bir aralık, bir formülde 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ığın hücre başvurularının asla değişmemesidir. Aralık adları, formülleri kopyalarken hataları önlemek için mutlak hücre başvurularını kullanmaya bir alternatiftir.
Aralık adı, verilerin (4. satırda gösterildiği gibi) başlıklarını veya alan adlarını içermez, yalnızca verileri içerir.
-
Çalışma sayfasında
Vurgula hücreler D5 - G10.
Image -
İmleci A sütununun üzerinde bulunan Ad Kutusuna yerleştirin, Table yazın, ardından Enter tuşlarına basın. D5 ila G10 arasındaki hücreler Tablo aralığı adına sahiptir.
Image - DÜŞEYARA tablo dizisi bağımsız değişkeni için aralık adı bu öğreticide daha sonra kullanılacaktır.
DÜŞEYARA İletişim Kutusunu Aç
Arama formülünü bir çalışma sayfasındaki bir hücreye doğrudan yazmak mümkün olsa da, birçok kişi sözdizimini düz tutmayı zor buluyor - ö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şlevlerinin, işlevin bağımsız değişkenlerinin her birinin ayrı bir satıra girildiği bir iletişim kutusu vardır.
-
Çalışma sayfasındaki hücre E2'yi seçin. Bu, iki boyutlu arama formülünün sonuçlarının görüntüleneceği konumdur.
Image -
Şeritte, Formüller sekmesine gidin ve Arama ve Referans öğesini seçin.
Image -
İşlev Bağımsız Değişkenleri iletişim kutusunu açmak için DÜŞEYARA öğesini seçin.
Image - İş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ğeri, veri tablosunun ilk sütunundaki bir veri alanıyla eşleşir. Bu örnekte, arama değeri, bilgi bulmak istediğiniz parçanın adını ifade eder. Arama değeri için izin verilen veri türleri metin verileri, mantıksal değerler, sayılar ve hücre başvuruları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, arama değeri için hücre başvurusu olan D2, F2 ve G2 hücrelerinde değişir ve hatalar oluşturur.
Formüller kopyalandığında mutlak hücre referansları değişmez.
Hataları önlemek için D2 hücre referansını mutlak hücre referansına dönüştürün. Mutlak hücre referansı oluşturmak için F4 tuşuna basın. Bu, hücre referansının etrafına $D$2. gibi dolar işaretleri ekler.
-
İşlev Bağımsız Değişkenleri iletişim kutusunda, imleci lookup_value metin kutusuna yerleştirin. Ardından, bu hücre başvurusunu arama_değeri öğesine eklemek için çalışma sayfasında hücre D2 öğesini seçin. D2 hücresi, parça adının girileceği yerdir.
Image -
Ekleme noktasını hareket ettirmeden, D2'yi $D$2 mutlak hücre referansına dönüştürmek için F4 tuşuna basın.
Image - Öğreticideki 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, istediğiniz bilgiyi bulmak için arama formülünün 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 içerir (önceki bölümde ayarlanmıştır), ikinci sütun ise belirttiğiniz bilgileri bulmak için arama formülüyle aranır.
Tablo dizisi bağımsız değişkeni, veri tablosu için hücre referanslarını içeren bir aralık veya bir aralık adı olarak girilmelidir.
Veri tablosunu DÜŞEYARA işlevine eklemek için, imleci iletişim kutusundaki table_array metin kutusuna getirin ve Table yazınbu argümanın aralık adını girmek için.
COLUMN İşlevini Yuvalayın
Normalde DÜŞEYARA, bir veri tablosunun yalnızca bir sütunundaki verileri 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 sütun dizin numarasının arama formülünü düzenlemeden değiştirilmesi gerekir. Bunu gerçekleştirmek için, SÜTUN işlevini DÜŞEYARA işlevinin içine Sütun_dizini_sayısı bağımsız değişkeni olarak yerleştirin.
İşlevleri iç içe yerleştirirken, Excel bağımsız değişkenlerini girmek için ikinci işlevin iletişim kutusunu açmaz. COLUMN işlevi manuel olarak girilmelidir. COLUMN işlevinin yalnızca bir bağımsız değişkeni vardır; bu, bir hücre başvurusu olan Başvuru bağımsız değişkenidir.
COLUMN 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, Col_index_num bağımsız değişkenine 2 eklemek için Referans olarak B sütununu kullanır.
-
Function Arguments iletişim kutusunda, imleci Col_index_num metin kutusuna getirin ve COLUMN(. (Açık yuvarlak ayracı eklediğinizden emin olun.)
Image -
Çalışma sayfasında, o hücre referansını Referans bağımsız değişkeni olarak girmek için cell B1 öğesini seçin.
Image - SÜTUN işlevini tamamlamak için bir kapatma yuvarlak ayracı yazın.
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 Aranan_değerle tam mı yoksa yaklaşık bir eşleşme mi bulması gerektiğini belirten mantıksal bir değerdir (DOĞRU veya YANLIŞ).
- TRUE veya Atlanmış: DÜŞEYARA, Aranan_değer ile yakın bir eşleşme döndürür. Tam bir eşleşme bulunamazsa, DÜŞEYARA sonraki en büyük değeri döndürür. Table_array'in ilk sütunundaki veriler artan düzende sıralanmalıdır.
- FALSE: 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 eşleşme bulunamazsa bir YOK hatası döndürülür.
Bu öğreticide, belirli bir donanım öğesiyle ilgili belirli bilgiler aranacaktır, bu nedenle Aralık_bak YANLIŞ olarak ayarlanır.
İşlev Bağımsız Değişkenleri iletişim kutusunda, imleci Aralık_bak metin kutusuna getirin ve DÜŞEYARA'ya veriler için tam bir eşleşme döndürmesini söylemek için Yanlış yazın.
Arama formülünü tamamlamak ve iletişim kutusunu kapatmak için
Seçin OK. Arama ölçütleri D2 hücresine girilmediği için E2 hücresi YOK hatası içerecektir. Bu hata geçicidir. Bu öğreticinin son adımında arama kriterleri eklendiğinde düzeltilecektir.
Arama Formülünü Kopyalayın ve Kriterleri Girin
Arama formülü, aynı anda veri tablosunun birden çok sütunundan veri alır. Bunu yapmak için, arama formülü bilgi almak istediğiniz tüm alanlarda bulunmalıdır.
Veri tablosunun 2, 3 ve 4 numaralı sütunlarından (fiyat, parça numarası ve tedarikçinin adı) veri almak için, Aranan_değer olarak kısmi bir ad girin.
Veriler çalışma sayfasında düzenli bir düzende düzenlendiğinden, hücre E2 içindeki arama formülünü cells F2'ye kopyalayın ve G2 Formül kopyalanırken 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, formül kopyalanırken mutlak hücre referansını ($D$2 gibi) ve adlandırılmış aralığı (Tablo) değiştirmez.
Excel'de veri kopyalamanın birden fazla yolu vardır, ancak en kolay yol Doldurma Tutamacı'nı kullanmaktır.
-
Aktif hücre yapmak için arama formülünün bulunduğu hücre E2'yi seçin.
Image -
Doldurma tutamacını cell G2 üzerine sürükleyin. F2 ve G2 hücreleri, E2 hücresinde bulunan N/A hatasını görüntüler.
Image -
Veri tablosundan bilgi almak üzere arama formüllerini kullanmak için, çalışma sayfasında cell D2 öğesini seçin, Widget yazın ve tuşuna basın Gir.
Image Aşağıdaki bilgiler E2 ile G2 arasındaki hücrelerde görüntülenir.
- E2: $14.76 - bir widget'ın fiyatı
- F2: PN-98769 - bir widget'ın parça numarası
- G2: Widgets Inc. - widget tedarikçisinin adı
-
DÜŞEYARA dizi formülünü test etmek için, diğer parçaların adını D2 hücresine yazın ve E2 ila G2 hücrelerindeki sonuçları gözlemleyin.
Image - Arama formülünü içeren her hücre, aradığınız donanım öğesi hakkında farklı bir veri parçası içerir.
SÜTUN gibi iç içe işlevlere sahip DÜŞEYARA işlevi, diğer verileri arama referansı olarak kullanarak bir tablo içindeki verileri aramak için güçlü bir yöntem sağlar.
