Çalışma kitaplarını bağlayıp arama işlevleriyle veri getirme (external references and lookup functions), bir dosyadaki listeden başka bir dosyaya anahtar değere göre otomatik veri çekmenizi sağlar. Önce dış başvuruyla kaynak kitaba bağlanır, ardından DÜŞEYARA, İNDİS ve KAÇINCI ya da ÇAPRAZARA ile aradığınız kayda ait fiyat, ad, bölge gibi bilgileri getirirsiniz. Kaynak listede bir değer değiştiğinde hedef dosyadaki sonuçlar da güncellenir; böylece aynı veriyi elle yeniden yazmanız gerekmez.
Kaynak kitap kapalıyken Excel, dosyanın tam yolunu da başvuruya ekler:
Bağlantıları yönetmek için Veri > Sorgular ve Bağlantılar > Bağlantıları Düzenle (Data > Queries & Connections > Edit Links) komutunu kullanın. Açılan pencerede şu işlemler yapılır:
Bağımsız değişkenlerin anlamı şöyledir:
D2 hücresindeki kod için E2 hücresi Monitör, F2 hücresi 4.800,00 değerini getirir. D3 ve D4 satırlarında da aynı formül kendi kodunu arar: Klavye ve 450,00, ardından Webcam ve 950,00. Tablo aralığı dolar işaretleriyle sabitlendiği için formüller aşağı kopyalandığında aralık kaymaz.
KAÇINCI, aranan adı B2:B7 aralığında tam eşleşmeyle bulur ve satır sırasını verir. İNDİS bu sırayı A sütununa uygulayarak sicil numarasını (1005), C sütununa uygulayarak departmanı (Lojistik) getirir. Sütun eklemek ya da sırasını değiştirmek, aralıklar formülde ayrı ayrı tanımlandığı için sonucu bozmaz.
Ankara ve Antalya listede bulunduğu için E2:F3 aralığı gerçek kayıtları getirir: İç Anadolu, 640.000,00, Akdeniz, 530.000,00. Trabzon listede olmadığından E4 ve F4 hücreleri formüldeki yedek metni gösterir: Kayıt yok ve Kayıt yok. Böylece hata değeri raporunuza yansımaz ve eksik kayıtlar kolayca fark edilir.
Başka çalışma kitabına bağlantı nasıl kurulur
Bir hücrede başka bir çalışma kitabındaki hücreye başvurmaya dış başvuru (external reference) denir. En kolay yol, hedef hücreye eşittir işareti yazıp kaynak kitabı açmak ve ilgili hücreye tıklamaktır; Excel başvuruyu sizin yerinize oluşturur. Başvuruda kitap adı köşeli parantez içinde, ardından sayfa adı, ünlem işareti ve hücre adresi yer alır. Kitap ya da sayfa adında boşluk veya özel karakter varsa bu bölüm kesme işareti içine alınır.Kaynak kitap kapalıyken Excel, dosyanın tam yolunu da başvuruya ekler:
Excel formülü
='C:\Raporlar\[Fiyatlar.xlsx]Liste'!$A$2- Değerleri Güncelleştir (Update Values): Kaynak kitaptaki güncel değerleri getirir.
- Kaynağı Değiştir (Change Source): Dosya taşındığında ya da adı değiştiğinde bağlantıyı yeni dosyaya yönlendirir.
- Bağlantıyı Kes (Break Link): Formülleri o anki değerlere çevirir ve kaynakla ilişkiyi sonlandırır.
Başka kitaptaki listeden DÜŞEYARA ile veri getirme
Dış başvuru, arama işlevinin tablo bağımsız değişkeni olarak da kullanılabilir. Aşağıdaki formül A2 hücresindeki kodu Fiyatlar.xlsx dosyasının Liste sayfasındaki A2:C50 aralığının ilk sütununda arar ve aralığın üçüncü sütunundaki değeri getirir. Türkçe Excel'de bağımsız değişkenler, bölgesel ayarlara bağlı olarak noktalı virgülle ayrılır:- Aranan değer: A2 hücresindeki kod. Formülü aşağı kopyaladığınızda her satır kendi kodunu arar.
- Tablo: Arama sütunu en solda olacak şekilde seçilen aralık. Dolar işaretleriyle sabitlenmesi, formül kopyalanırken aralığın kaymasını önler.
- Sütun numarası: Getirilecek değerin tablodaki sütun sırası.
- Eşleşme türü: YANLIŞ (FALSE) tam eşleşme arar. Kod, ad gibi anahtarlarda her zaman tam eşleşmeyi tercih edin.
DÜŞEYARA, İNDİS-KAÇINCI ve ÇAPRAZARA farkları
Hangi işlevi seçeceğiniz, tablonun yapısına ve Excel sürümünüze bağlıdır:- DÜŞEYARA: Yalnızca arama sütununun sağındaki değerleri getirir. Sütun numarası sabit yazıldığı için araya sütun eklenirse sonuç yanlış sütuna kayabilir.
- İNDİS ve KAÇINCI: KAÇINCI aranan değerin satır sırasını bulur, İNDİS o sıradaki değeri döndürür. Arama sütunu hangi tarafta olursa olsun çalışır ve sütun eklemekten etkilenmez.
- ÇAPRAZARA: Arama ve sonuç aralıklarını ayrı ayrı alır, varsayılan olarak tam eşleşme arar ve bulunamayan kayıtlar için yedek metin tanımlamanıza izin verir. Excel 2021 ve Microsoft 365 sürümlerinde bulunur; eski sürümlerde yoktur.
- ARA: Eski bir işlevdir ve arama aralığının artan sırada olmasını bekler; tam eşleşme gerektiren işlerde yukarıdaki işlevler daha güvenlidir.
1. DÜŞEYARA ile koda göre ürün adı ve fiyat getirme
A:C sütunlarında ürün listesi, D sütununda aranan kodlar bulunuyor. E ve F sütunları kodun karşılığındaki ürün adını ve birim fiyatı getirir. Aynı formül, kaynak liste başka bir kitapta olsa da tablo bağımsız değişkenindeki aralığın dış başvuruyla değiştirilmesiyle çalışır.Çalışma Kitaplarını Bağlayıp Arama İşlevleriyle Veri Getirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün Kodu | Ürün Adı | Birim Fiyat | Aranan Kod | Bulunan Ürün | Bulunan Fiyat |
| 2 | K-101 | Klavye | 450,00 | K-103 | Monitör | 4.800,00 |
| 3 | K-102 | Fare | 250,00 | K-101 | Klavye | 450,00 |
| 4 | K-103 | Monitör | 4.800,00 | K-106 | Webcam | 950,00 |
| 5 | K-104 | Yazıcı | 3.200,00 | |||
| 6 | K-105 | Kulaklık | 780,00 | |||
| 7 | K-106 | Webcam | 950,00 |
Ürün ListesiExcelDepo v1.0.0
2. İNDİS ve KAÇINCI ile soldaki sütunu getirme
Tabloda sicil numarası, aranan ad soyadın solunda yer alıyor. DÜŞEYARA sola bakamadığı için KAÇINCI ile satır sırası bulunur, İNDİS ile istenen sütundan değer alınır.Çalışma Kitaplarını Bağlayıp Arama İşlevleriyle Veri Getirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sicil No | Ad Soyad | Departman | Aranan Ad | Bulunan Sicil No | Bulunan Departman |
| 2 | 1001 | Ayşe Yılmaz | Muhasebe | Elif Şahin | 1005 | Lojistik |
| 3 | 1002 | Mehmet Demir | Satış | Mehmet Demir | 1002 | Satış |
| 4 | 1003 | Zeynep Kaya | İnsan Kaynakları | Burak Aydın | 1006 | Muhasebe |
| 5 | 1004 | Ahmet Çelik | Satış | |||
| 6 | 1005 | Elif Şahin | Lojistik | |||
| 7 | 1006 | Burak Aydın | Muhasebe |
Personel SicilExcelDepo v1.0.0
3. ÇAPRAZARA ile bulunamayan kayıtları yönetme
Şehir listesinden bölge ve aylık satış tutarı getiriliyor. Aranan şehirlerden biri listede yok; ÇAPRAZARA dördüncü bağımsız değişkeniyle bu durumda #YOK hatası yerine yedek bir metin gösterir. Bu işlev Excel 2021 ve Microsoft 365 sürümlerinde kullanılabilir.Çalışma Kitaplarını Bağlayıp Arama İşlevleriyle Veri Getirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Şehir | Bölge | Aylık Satış | Aranan Şehir | Bulunan Bölge | Bulunan Satış |
| 2 | İstanbul | Marmara | 1.250.000,00 | Ankara | İç Anadolu | 640.000,00 |
| 3 | Ankara | İç Anadolu | 640.000,00 | Antalya | Akdeniz | 530.000,00 |
| 4 | İzmir | Ege | 720.000,00 | Trabzon | Kayıt yok | Kayıt yok |
| 5 | Bursa | Marmara | 410.000,00 | |||
| 6 | Antalya | Akdeniz | 530.000,00 | |||
| 7 | Adana | Akdeniz | 380.000,00 |
Bölge SatışExcelDepo v1.0.0
Sık sorulan sorular
- Kaynak kitap kapalıyken formül çalışır mı? DÜŞEYARA kapalı kitaptaki listeyi okuyabilir. Buna karşın ETOPLA ve EĞERSAY gibi bazı işlevler kaynak kitap kapalıyken #DEĞER! hatası verebilir; bu durumda kaynağı açın.
- Kaynak dosyanın adını değiştirir ya da taşırsam ne olur? Bağlantı kopar ve Excel dosyayı bulamaz. Bağlantıları Düzenle penceresindeki Kaynağı Değiştir düğmesiyle yeni dosyayı gösterin.
- Neden #YOK hatası alıyorum? Aranan değer listede yoksa, değerin başında ya da sonunda fazladan boşluk varsa veya biri sayı, diğeri metin olarak kayıtlıysa bu hata görülür.
- Bağlantıyı kaldırıp yalnızca değerleri tutabilir miyim? Evet. Bağlantıları Düzenle penceresinde Bağlantıyı Kes düğmesini kullanın; formüller o anki değerlere dönüşür.
Kullanırken dikkat edin
- Tam eşleşmeyi unutmayın: DÜŞEYARA ve KAÇINCI işlevlerinde son bağımsız değişkeni YANLIŞ ya da 0 olarak girmezseniz Excel yaklaşık eşleşme arar ve sıralı olmayan listelerde yanlış kayıt getirebilir.
- Tablo aralığını sabitleyin: Formülü aşağı kopyalarken aralığın kaymaması için tablo başvurusunu F4 tuşuyla mutlak hale getirin. Aranan değer ise satıra göre değişmeli, yani sabitlenmemelidir.
- Boşluk ve veri türü uyuşmazlığı: Başında ya da sonunda fazladan boşluk olan değerler ile metin olarak saklanan sayılar eşleşmez. Boşluklar için KIRP işlevini kullanın, sayıları aynı türe getirin.
- Dosyaları birlikte taşıyın: Bağlantılı dosyaları klasör değiştirirken ya da e-postayla gönderirken birlikte taşıyın. Aksi halde bağlantı kopar; Bağlantıları Düzenle penceresinden kaynağı yeniden gösterebilirsiniz.
- ÇAPRAZARA sürüm şartı: ÇAPRAZARA yalnızca Excel 2021, Excel 2024 ve Microsoft 365 sürümlerinde vardır. Dosyayı eski sürüm kullanan biriyle paylaşacaksanız İNDİS ve KAÇINCI birleşimini tercih edin.
- Bağlantı kaynağına güvenin: Bilmediğiniz kaynaklardan gelen dosyalarda bağlantıları güncellemeden önce kaynağı kontrol edin; güvenmediğiniz bağlantıları Bağlantıyı Kes ile kaldırın.
- İlgili konular: DÜŞEYARA, YATAYARA, İNDİS, KAÇINCI, ÇAPRAZARA, ARA, EĞERHATA, KIRP, DOLAYLI, Bağlantıları Düzenle, Ad Yöneticisi.
Ekli dosyalar
Son düzenleme:
