İki Çalışma Kitabı Arasında DÜŞEYARA: Kolay Adım Adım Kılavuz

Excel'de birden çok çalışma kitabıyla çalışıyorsanız, bir çalışma kitabından diğerine veri almak için DÜŞEYARA işlevini kullanmanız gerekebilir.

DÜŞEYARA işlevi, ayrı bir çalışma kitabındaki bir tablodaki belirli bir değeri arar ve aynı satırdan karşılık gelen bir değer döndürür. Sözdizimi, ikinci çalışma kitabına ve çalışma sayfası adına tam nitelikli bir başvuru içermelidir.

İşlevi ilk kez kullandığınızda referansları doğru almak zor olabilir. Bu makale, formülleri tam olarak nasıl gireceğinizi göstermek için örnek elektronik tablolar kullanır.

DÜŞEYARA'ya yönelik bazı geliştirmeleri ve işleve yönelik çeşitli alternatifleri de öğreneceksiniz.

Başlayalım!

İçindekiler

• DÜŞEYARA nedir?
• DÜŞEYARA İki Çalışma Kitabı Arasında Nasıl Kullanılır
  • Örnek veri
  • 1. Adım: Her İki Çalışma Kitabını da Açık Tutun
  • 2. Adım: DÜŞEYARA Formülünü Girmeye Başlayın
  • 3. Adım: İkinci Çalışma Kitabına Nasıl Başvurulur?
• 3 Yaygın DÜŞEYARA Hatası ve Nasıl Düzeltilir?
  • 1. #YOK Hatası
  • 2. #Ref! Sütun Numarası Hatası
  • 3. Tırnaklar ve Köşeli Parantez Hatası
• Farklı Çalışma Kitaplarını Yönetmek İçin 4 İpucu
• Verileri Otomatik Olarak Güncelleme
• DÜŞEYARA İşlevinin Gelişmiş Kullanımları
• DÜŞEYARA için 2 Ana Alternatif
  • 1. ENDEKS EŞLEŞMESİ
  • 2. XLOOKUP
• Birden Fazla Sayfada DÜŞEYARA Nasıl Kullanılır?
  • 1. Adım: Bileşenleri Tanımlayın
  • 2. Adım: Verilerinizi Ayarlayın
  • 3. Adım: DÜŞEYARA İşlevini kullanın
  • (İsteğe bağlı) 4. Adım: #N/A Hatalarını Giderin
• Son düşünceler

DÜŞEYARA nedir?

Bir ürün satış çalışma kitabınız ve her bir ürünün fiyatlarının olduğu ayrı bir çalışma kitabınız olduğunu varsayalım. İkinci çalışma kitabından fiyatlara ulaşmak ve bunları ilk çalışma kitabında hesaplamalarda kullanmak istiyorsunuz.

DÜŞEYARA, bu görev için bir çözümdür. Terim, “Dikey Arama” anlamına gelir . “

Bu, bir sütunda belirli bir değeri aramanıza ve konuma göre başka bir sütunda karşılık gelen bir değer döndürmenize izin veren yerleşik bir işlevdir.

Şununla birlikte kullanılabilir:

• tek bir e-tablo

• aynı çalışma kitabında birden çok sayfa

• iki Excel çalışma kitabı

Birden çok çalışma kitabının ek karmaşıklığına sahip değilseniz, DÜŞEYARA kullanarak Excel'de iki sütunu karşılaştırmaya yönelik bu eğiticiye göz atın .

DÜŞEYARA İki Çalışma Kitabı Arasında Nasıl Kullanılır

DÜŞEYARA işlevi, birden çok çalışma sayfasında veya çalışma kitabında veri aramanız gerektiğinde özellikle kullanışlıdır.

Bunu yapmak için üç ana adım şunlardır:

1. Her iki çalışma kitabını da açın.

2. DÜŞEYARA formülünü ilk Excel çalışma kitabına girin.

3. İkinci çalışma kitabına tam nitelikli bir başvuru sağlayın.

Örnek veri

Bu basit öğretici, örnek verilere dayalı örneklerle çalışır. Devam etmek için "Satış" adlı yeni bir çalışma kitabı oluşturun ve bu verileri A ve B sütunlarına girin:

• Elmalar, 40

• Portakal, 30

• Armut, 70

• Muz, 20

Fiyat bilgilerini tutmak için üçüncü bir sütun ekleyin:

"Ürün" adlı ikinci bir Excel çalışma kitabı oluşturun ve Sayfa1'i "Fiyatlar" olarak yeniden adlandırın. Bu verileri A sütununa ve B sütununa girin:

• Elmalar, 1.50

• Portakal, 2.30

• Armut, 3.60

• Muz, 2.10

Veriler şöyle görünür:

İlk e-tablodaki satışların gelirini hesaplamak için ikinci e-tablodaki fiyata bakmamız gerekir. DÜŞEYARA kurtarmaya!

1. Adım: Her İki Çalışma Kitabını da Açık Tutun

İki çalışma kitabı arasında DÜŞEYARA formülü kullanılırken her iki çalışma kitabı da açık olmalıdır. İkinci çalışma kitabı kapatılırsa işlev bir hata döndürür.

Bunları ayrı ayrı açabilir veya Excel'deki “Aç” komutunu kullanarak ikisini birden açabilirsiniz. Her iki çalışma kitabını da açmanın iki yolu vardır:

• Dosya sekmesine gidin ve "Aç" ı seçin.

• “Ctrl + O” klavye kısayolunu kullanın .

Her iki çalışma kitabını da açtıktan sonra, Excel penceresinin altındaki uygun sekmeye tıklayarak bunlar arasında geçiş yapabilirsiniz.

2. Adım: DÜŞEYARA Formülünü Girmeye Başlayın

DÜŞEYARA işlevinin temel söz dizimi şöyledir:

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

• aranan_değer : Diğer çalışma kitabında aramak istediğiniz değer.

• tablo_dizisi : Almak istediğiniz verileri içeren diğer çalışma kitabındaki hücre aralığı.

• col_index_num : Almak istediğiniz verileri içeren aralıktaki sütun numarası.

• range_lookup : Bu son parametre isteğe bağlıdır. DOĞRU olarak ayarlarsanız veya atlarsanız, DÜŞEYARA yaklaşık bir eşleşme arar. YANLIŞ olarak ayarlarsanız, DÜŞEYARA yalnızca tam bir eşleşme arar.

Örnek verilerimizin ikinci çalışma kitabında elma fiyatlarına bakmak istediğinizi varsayalım. Formül şu şekilde bozulacak:

• Aranan_değer, ilk çalışma kitabının A2 hücresindedir.

• Tablo dizisi, ikinci çalışma kitabının A1:B5 hücre aralığındadır.

• col_index_num, fiyatı tutan ikinci sütundur.

DÜŞEYARA formülünün en karmaşık kısmı, ikinci çalışma kitabına doğru şekilde atıfta bulunmaktır.

3. Adım: İkinci Çalışma Kitabına Nasıl Başvurulur?

DÜŞEYARA formülünü iki çalışma kitabı için tamamlamak üzere, ikinci çalışma kitabından şu ayrıntıları içeren tam nitelikli bir başvuru kullanmanız gerekir:

• çalışma kitabı adı

• sayfa adı

• hücre aralığı

Bu, Excel'in aradığınız verileri tam olarak nerede bulacağını bilmesini sağlar. DÜŞEYARA işlevinin tablo_dizisi bağımsız değişkeni için tam nitelikli başvuru kullanılır.

Örnek veriler için değerler şunlardır:

• çalışma kitabı adı: Product.xlsx

• sayfa adı: Fiyatlar

• hücre aralığı: $A1:$B5

İlk öğenin fiyatını aramak için, ilk çalışma kitabının C2 hücresine aşağıdaki formülü girin:

=DÜŞEYARA(A2, [Product.xlsx]Fiyatlar!$A$1:$B$5, 2, YANLIŞ)

Bu resim, ilk hücre için formül çubuğundaki işlevi çalışırken gösterir:

Çalışma kitabı adının köşeli parantez içine alındığını ve sayfa adının ardından bir ünlem işareti geldiğini unutmayın.

Excel dosya adı bir boşluk veya özel karakterler içeriyorsa, tüm çalışma kitabını ve sayfayı tek tırnak içine almalısınız.

Formül, hücre aralığı ($ öneki) için mutlak değerler kullanır. Bu, doldurma tutamacına tıklayarak formülü satırların geri kalanına kopyalamanıza olanak tanır.

İşte DÜŞEYARA sonuçları:

Ve bu kadar! Artık DÜŞEYARA'yı etkili bir şekilde nasıl kullanacağınızı biliyorsunuz. Herhangi bir hatayla karşılaşmanız durumunda size yardımcı olmak için, bir sonraki bölümde en sık rastlanan DÜŞEYARA hatalarından üçünü derledik.

3 Yaygın DÜŞEYARA Hatası ve Nasıl Düzeltilir?

Artık DÜŞEYARA'yı başka bir çalışma kitabından nasıl kullanacağınızı öğrendiğinize göre, bazı yaygın hatalarla karşılaşabilirsiniz.

İşte en sık görülen üç hata ve bunların nasıl düzeltileceği.

1. #YOK Hatası

#YOK hatası, tablo dizisinde aranan değer bulunamadığında oluşur. Bu hatayı düzeltmek için aşağıdakileri deneyebilirsiniz:

• Arama değerinin yazımını kontrol edin.

• Aranan değerin tablo dizisinin ilk sütununda olduğundan emin olun.

• Arama değerinin iki sayfada metin olarak biçimlendirilip biçimlendirilmediğini kontrol edin.

• Tam bir eşleşme bulmak için aralık_bak bağımsız değişkeninin YANLIŞ olarak ayarlandığından emin olun.

Yukarıdaki örnek, ikinci çalışma kitabında Cherries için eşleşen bir değer bulamadı.

2. #Ref! Sütun Numarası Hatası

#Ref ! col_index_num bağımsız değişkeni tablo dizisindeki sütun sayısından büyük olduğunda hata oluşur.

Bu hatayı düzeltmek için col_index_num bağımsız değişkeninin kaynak tablodaki sütun sayısından küçük veya ona eşit olduğunu kontrol edin.

Aşağıdaki resimde col_index_num 3 olarak ayarlanmıştır ancak referans aralığında sadece iki sütun vardır.

3. Tırnaklar ve Köşeli Parantez Hatası

Tırnak işaretleri veya köşeli parantezler içeren bir değerle DÜŞEYARA gerçekleştirdiğinizde Tırnaklar ve Köşeli Parantez Hatası oluşur.

Bu hatayı düzeltmek için aşağıdakileri deneyebilirsiniz:

• Arama değerinin etrafında çift tırnak kullanın.

• Çift tırnak eklemek için CHAR(34) işlevini kullanın .

• Arama değeri tırnak işaretleri veya köşeli parantezler içeriyorsa, boş bir hücre döndürmek için EĞERHATA işlevini kullanın.

Bu hatalara aşina olduğunuzda, bunları düzeltmek kolaydır! DÜŞEYARA uzmanı olmanıza daha fazla yardımcı olmak için, bir sonraki bölümde farklı çalışma kitaplarını yönetmeye yönelik dört ipucu sağladık.

Farklı Çalışma Kitaplarını Yönetmek İçin 4 İpucu

Birden çok çalışma kitabıyla çalışırken, hangi çalışma kitabının hangi verileri içerdiğini takip etmek önemlidir.

Akıl sağlığınızı korumak için en iyi dört ipucumuz:

1. Hangi Excel elektronik tablolarının hangi verileri içerdiğini hatırlamanıza yardımcı olması için adlandırma kurallarını kullanın.

2. Formülleri başka bir konuma veya Excel sayfasına taşısanız veya kopyalasanız bile arama aralığının doğru hücrelere başvurduğundan emin olmak için formüllerinizdeki mutlak başvuruları kullanın.

3. Kaynak verilerde arama değerleri bulunamazsa oluşabilecek #N/A hatalarını işlemek için EĞERHATA işlevini kullanın.

4. Üzerine yazılamamaları için referans çalışma kitabındaki hücreleri kilitlemeyi düşünün .

Verileri Otomatik Olarak Güncelleme

Birden çok Excel dosyasıyla çalışırken, verileri manuel olarak güncellemek zaman alabilir .

Diğer çalışma kitaplarına veri eklediğinizde veya bu kitaplardan veri çıkardığınızda DÜŞEYARA formülünüzün otomatik olarak güncellenmesini istiyorsanız, DOLAYLI işlevini kullanabilirsiniz.

İşte nasıl:

• DÜŞEYARA formülünde, tablo dizisi bağımsız değişkenini DOLAYLI işlevle değiştirin.

• DOLAYLI işlevinde, diğer çalışma kitabındaki tablo dizisini içeren hücreye bir başvuru kullanın.

Bunu örnek çalışma kitaplarımızla başarmak için aşağıdaki adımları izleyin.

1. "Ürün" çalışma kitabının "Fiyatlar" sayfasına ve A1:B5 hücre aralığına şu şekilde referans girmek için D sütunundaki bir hücreyi kullanın: [Ürün.xlsx]Fiyatlar!A:B

2. "Satış" çalışma kitabının C2 hücresinde şu formülü kullanın: =DÜŞEYARA(A2, DOLAYLI($D$1), 2, YANLIŞ)

3. Formülü aşağı doğru kopyalayın (mutlak referanslar, elektronik tablonun bulunduğu hücrenin korunmasını sağlar).

Bu eylemler, orijinal sayfadaki değişikliklerin bağlı hücrelerde otomatik olarak güncellenmesini sağlar.

Bu resim, DÜŞEYARA ile birlikte DOLAYLI işlevi göstermektedir:

Bu örneğin, iki sütuna başvurmak için "A:B" aralığını kullandığına dikkat edin. Bu, daha fazla ürün ve fiyat eklediğinizde formülün geçerli çalışma kitabında çalışmaya devam edeceği anlamına gelir.

Artık DÜŞEYARA'nın gücünün tadına vardığınıza göre, bu bölümde işlevin bazı gelişmiş kullanımlarına bir göz atalım!

DÜŞEYARA İşlevinin Gelişmiş Kullanımları

Bu makaledeki örnekler, her arama değeri için tek bir eşleşme döndürür. Farklı bir çalışma kitabından birden çok eşleşmeyle çalışmak istiyorsanız bunun yerine bir dizi formülü kullanabilirsiniz.

DÜŞEYARA'yı diğer işlevlerle de birleştirebilirsiniz. Örneğin, ek karmaşık analiz için bir EĞER işlevinin içine yerleştirebilirsiniz.

Karmaşık analiz, ki-kare testleri ve eşleştirilmiş örnekler T-testleri gibi teknikleri içerir. İşte Excel'de ki-kare testinin kullanımına bir örnek:

İleri düzey kullanıcılar, veri kümelerini karşılaştırmak ve uyumsuzlukları belirlemek için DÜŞEYARA'dan yararlanır. Excel'de tutarsızlıkları bulma konusunda bu makalede başka yollar bulacaksınız .

Ancak herhangi bir nedenle DÜŞEYARA kullanmak istemezseniz ne olur? Şanslısın! Bir sonraki bölümde ele alacağımız bazı alternatifler var.

DÜŞEYARA için 2 Ana Alternatif

Farklı sayfalardaki değerleri aramak söz konusu olduğunda, DÜŞEYARA genellikle gidilecek işlevdir. Ancak, belirli durumlarda yararlı olabilecek bazı alternatifler vardır.

En yaygın iki tanesi:

• ENDEKS EŞLEŞMESİ

• XLOOKUP

1. ENDEKS EŞLEŞMESİ

INDEX ve MATCH, bir tablodaki değerleri aramak için kullanılabilen iki işlevin güçlü bir birleşimidir. Aranan değerin tablonun en soldaki sütununda olmasını gerektiren DÜŞEYARA'nın aksine, INDEX MATCH herhangi bir sütundaki değerleri arayabilir.

İşte nasıl çalıştığı:

1. İNDİS işlevi, satır ve sütun numarasına göre belirtilen aralıktan bir değer döndürür.

2. KAÇINCI işlevi, belirtilen bir aralıktaki bir arama değerinin konumunu döndürür.

INDEX MATCH'ı kullanmak için, aramak istediğiniz aralığı, aranan değeri bulmak istediğiniz satırı ve sonucu döndürmek istediğiniz sütunu belirtmeniz gerekir.

İşte örnek bir formül:

=INDEX(TableRange,MATCH(AramaDeğeri,AramaAralığı,0),ColumnIndex)

Bunu örnek e-tablolarla kullanmak için formül şudur:

=DİZİN([Ürün.xlsx]Fiyatlar!$B$1:$B$5, MATCH(A2, [Ürün.xlsx]Fiyatlar!$A$1:$A$5, 0))

Bu şu şekilde ayrılır:

• KAÇINCI işlevi, "Ürün" çalışma kitabının "Fiyatlar" sayfasındaki A1:A5 aralığında A2 hücresindeki (ürün adı) değeri arar.

• INDEX işlevi, KAÇINCI işlevi tarafından sağlanan satır numarasını temel alarak, "Ürün" çalışma kitabının "Fiyatlar" sayfasındaki B1:B5 aralığından bir değer döndürür.

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

2. XLOOKUP

XLOOKUP, Excel 365'te tanıtılan daha yeni bir işlevdir . DÜŞEYARA'ya benzer, ancak bazı ek özelliklere sahiptir.

XLOOKUP'ın en büyük avantajlarından biri, arama sütununun solundaki değerleri arayabilmesidir.

İşte nasıl çalıştığı:

1. XLOOKUP işlevi, bir arama değerine dayalı olarak belirtilen bir aralıktan bir değer döndürür.

2. Tam bir eşleşme mi yoksa yaklaşık bir eşleşme mi istediğinizi belirtebilirsiniz.

3. Eşleşme bulunamazsa neyin döndürüleceğini de belirtebilirsiniz.

XLOOKUP'ı kullanmak için arama değerini, aramak istediğiniz aralığı ve sonucu döndürmek istediğiniz sütunu belirtmeniz gerekir.

İşte örnek bir formül:

=XLOOKUP(AramaDeğeri,AramaAralığı,DönüşAralığı,”Bulunamadı”,0)

Bunu örnek e-tablolarla kullanmak için formül şudur:

=XLOOKUP(A2, [Ürün.xlsx]Fiyatlar!$1$1:$5$, [Ürün.xlsx]Fiyatlar!$B$1:$B$5)

Bu şu şekilde ayrılır:

• A2: Aramak istediğiniz değer (ürün adı).

• [Product.xlsx]Fiyatlar!$A$1:$A$5: Arama değerini aramak istediğiniz aralık ("Ürün" çalışma kitabının "Fiyatlar" sayfasındaki ürün adları).

• [Product.xlsx]Fiyatlar!$B$1:$B$5: Dönüş değerlerini içeren aralık ("Ürün" çalışma kitabının "Fiyatlar" sayfasındaki fiyatlar).

Bu, eylemdeki formüldür:

Birden Fazla Sayfada DÜŞEYARA Nasıl Kullanılır?

İki çalışma kitabı arasında DÜŞEYARA gerçekleştirmeniz gerekiyorsa, DÜŞEYARA'yı birden çok sayfada da kullanmanız gerekebilir.

Neyse ki, bunu yapma süreci, iki çalışma kitabı arasında DÜŞEYARA gerçekleştirmeye çok benzer.

Dört adım var.

1. Adım: Bileşenleri Tanımlayın

Bir sayfa için yaptığınız gibi tablo dizisini dahil etmek yerine, sayfa adını da dahil etmeniz gerekecektir.

Örneğin, Sales_reports.xlsx çalışma kitabındaki Ocak sayfasında A2:B6 aralığında bir değer aramak isterseniz, formülünüz şöyle görünür:

=DÜŞEYARA(A2,[Sales_reports.xlsx]Ocak!$A$2:$B$6,2,YANLIŞ)

2. Adım: Verilerinizi Ayarlayın

Verilerinizin, birden çok sayfaya başvurmayı kolaylaştıracak şekilde düzenlendiğinden emin olun.

Belirli referans verileri için farklı sekmeler oluşturmayı düşünebilirsiniz.

3. Adım: DÜŞEYARA İşlevini kullanın

Verileriniz ayarlandıktan sonra, diğer sayfalardan veri aramak ve almak için DÜŞEYARA işlevini kullanabilirsiniz.

Sayfa adını yukarıda gösterildiği gibi formülünüze eklediğinizden emin olun.

(İsteğe bağlı) 4. Adım: #N/A Hatalarını Giderin

DÜŞEYARA'yı birden çok sayfada kullanırken #YOK hatalarıyla karşılaşırsanız, gerekli tüm sayfalara ve aralıklara doğru şekilde başvurduğunuzdan emin olmak için formülünüzü bir kez daha kontrol edin.

Aralık araması yerine tam bir eşleşme gerçekleştirip gerçekleştirmediğinizi de kontrol etmek isteyebilirsiniz.

Son düşünceler

İki e-tabloda veri aramak ve almak için DÜŞEYARA'yı nasıl kullanacağınızı öğrendiniz. Bu çok yönlü işlev, bir çalışma kitabındaki değerleri diğerindeki karşılık gelen değerlere göre aramayı kolaylaştırır.

DÜŞEYARA işlevinin gücü, verileri bağlamak ve analiz etmek için farklı çalışma kitaplarına ulaşarak tek çalışma sayfalarının çok ötesine geçer.

Bu işlevde uzmanlaşmak, verilerinizi yönetmek ve yorumlamak için yeni olanaklar sunduğundan, Excel'de yetkin hale gelme yolunda önemli bir adımdır.

INDIRECT, INDEX-MATCH veya XLOOKUP gibi işlevleri dahil ederek, veri aramalarınızın esnekliğini ve etkinliğini daha da artırabilirsiniz.

Excel'in geniş yeteneklerini uygulamaya ve keşfetmeye devam edin; bu buzdağının sadece görünen kısmı. Mutlu veri avı!