Dış Veri Bağlantısı

Dış Veri Bağlantısı (External Data Connection), bir Excel çalışma kitabını SQL Server, Access, web sayfası, metin/CSV dosyası ya da başka bir Excel dosyası gibi dış kaynaklara bağlayan ve bağlantı bilgilerini çalışma kitabının içinde saklayan yapıdır. Veriler elle kopyalanmaz; bağlantı yenilendiğinde kaynaktaki güncel değerler tablo, özet tablo veya grafiğe taşınır. Böylece raporlar kaynağa bağlı kalır ve tekrar tekrar veri yapıştırma gereği ortadan kalkar.

Nerede bulunur​

Dış veri bağlantılarını oluşturmak ve yönetmek için şu komutları kullanın:

• Veri > Veri Al ve Dönüştür > Veri Al (Data > Get & Transform Data > Get Data): Dosyadan, veritabanından, Azure'dan ve çevrimiçi hizmetlerden yeni bağlantı kurar. Bağlantı bir Power Query sorgusu olarak oluşur.
• Veri > Sorgular ve Bağlantılar (Data > Queries & Connections): Sağ tarafta bir bölme açar. Sorgular ve Bağlantılar sekmelerinden çalışma kitabındaki tüm bağlantıları görürsünüz.
• Veri > Veri Al > Veri Kaynağı Ayarları (Data > Get Data > Data Source Settings): Kaynak yollarını, kimlik bilgilerini ve gizlilik izinlerini değiştirir.
• Veri > Tümünü Yenile (Data > Refresh All): Tüm bağlantıları yeniler. Klavye kısayolu Ctrl+Alt+F5'tir.

Bilgisayarınızda eski tip bağlantı penceresi etkinse Veri sekmesindeki Bağlantılar (Connections) komutuyla Çalışma Kitabı Bağlantıları penceresi de açılabilir. Bu komut bazı sürümlerde varsayılan olarak gizlidir ve Dosya > Seçenekler > Şeridi Özelleştir bölümünden eklenebilir.

Bağlantı türleri ve kaynaklar​

Excel çok sayıda kaynağa bağlanabilir. En sık kullanılanlar şunlardır:

• Metin/CSV ve Excel dosyaları: Ağ klasöründeki ya da yerel diskteki dosyalar. Aynı klasördeki birçok dosya tek sorguda birleştirilebilir.
• Web: Bir web sayfasındaki tablolar ya da bir web hizmetinin döndürdüğü veri.
• Veritabanları: SQL Server, Access, Oracle, MySQL gibi kaynaklar. Doğrudan bağlayıcı yoksa ODBC veya OLE DB sürücüsü kullanılır.
• OData ve çevrimiçi hizmetler: SharePoint listeleri, OData akışları ve benzeri kaynaklar.

Bağlantı verinin nereye yükleneceğini de belirler: sayfada bir Tablo (Table), bir Özet Tablo (PivotTable), yalnızca bağlantı ya da Veri Modeli. Büyük veri için yalnızca bağlantı oluşturup Veri Modeli'ne yüklemek çalışma kitabı boyutunu küçük tutar.

Nasıl kullanılır​

Örnek olarak bir CSV dosyasına bağlantı kurmak için şu adımları izleyin:

• Veri > Veri Al > Dosyadan > Metinden/CSV'den (Data > Get Data > From File > From Text/CSV) komutunu seçin.
• Dosyayı seçip İçeri Aktar (Import) düğmesine basın. Önizleme penceresinde sütun ayırıcısını ve dosya kaynağını (kodlama) kontrol edin.
• Verinin olduğu gibi gelmesi için Yükle (Load), temizlik gerekiyorsa Veriyi Dönüştür (Transform Data) düğmesini kullanın.
• Sonuç, çalışma sayfasında bir tablo olarak açılır ve bağlantı Sorgular ve Bağlantılar bölmesinde listelenir.
• Kaynak dosya değiştiğinde tabloya tıklayıp Tablo Sorgusu > Yenile (Query > Refresh) komutunu ya da Tümünü Yenile'yi kullanın.

Bağlantı özelliklerini değiştirmek için bölmede sorguya sağ tıklayıp Özellikler (Properties) komutunu seçin. Burada yenileme sıklığı, dosya açılırken yenileme ve arka planda yenileme seçeneklerini ayarlarsınız.

Yenileme ve güvenlik​

Dış veri içeren bir dosyayı açtığınızda sarı bir Güvenlik Uyarısı (Security Warning) çubuğu çıkabilir: dış veri bağlantıları devre dışı bırakılmıştır. Kaynağa güveniyorsanız İçeriği Etkinleştir (Enable Content) düğmesini kullanın. Bu davranış Dosya > Seçenekler > Güvenlik Merkezi > Güvenlik Merkezi Ayarları > Harici İçerik (File > Options > Trust Center > Trust Center Settings > External Content) bölümünden yönetilir.

• Yenileme sıklığı: Bağlantı özelliklerinde belirli dakikada bir otomatik yenileme tanımlayabilirsiniz.
• Dosyayı açarken yenile: Rapor her açıldığında güncel veriyi getirir.
• Kimlik bilgileri: Veritabanı kullanıcı adı ve parolası Veri Kaynağı Ayarları'nda tutulur. Parolayı dosyaya kaydetmek güvenlik riski taşır.
• Bağlantıyı kaldırma: Sorgular ve Bağlantılar bölmesinde sorguya sağ tıklayıp Sil'i seçerseniz tablodaki veriler kalır ama yenilenmez.

1. Bağlantı sonucunu Tablo olarak tutma​

Dış kaynaktan (CSV, SQL Server, web) getirilen veri genellikle sayfada bir Tablo olarak yüklenir. Aşağıdaki örnek, bağlantıyla gelmiş satış verisinin tablo halini gösterir. Gerçek bir bağlantı kurmak için Veri > Veri Al komutuyla kaynağı seçin ve Yükle düğmesine basın. Burada veri tablo olarak kurulmuştur ve Tutar sütunu formüllüdür.
Veri, tblSatis adlı bir tablo olarak kuruldu. Bağlantılı bir sorgu yenilendiğinde tablo satırları kaynağa göre genişler ya da daralır ve Tutar sütunundaki formül yeni satırlara otomatik uygulanır. İlk satırın tutarı 142.500,00, Antalya satırının tutarı 85.500,00 olarak hesaplanır.

2. Bağlantılı veriden Özet Tablo oluşturma​

Dış veri bağlantılarının en yaygın kullanımı, gelen veriyi Özet Tablo ile raporlamaktır. Bağlantı yenilendiğinde özet tablo da Tümünü Yenile ile güncellenir. Örnekte veri A1:C10 aralığındadır; özet tablo aynı sayfada F3 hücresinden başlayarak şehir bazında toplam tutarı verir. Elle yapmak için veriyi seçip Ekle > Özet Tablo komutunu kullanın, Şehir alanını Satırlar'a, Tutar alanını Değerler'e sürükleyin.
Sayfanın sağında, F3 hücresinden başlayan ptSehir adlı özet tablo şehirleri satırlara dizer ve her şehrin Tutar toplamını verir. Kaynak veri dış bağlantıdan geliyorsa Veri > Tümünü Yenile komutu önce bağlantıyı, sonra özet tabloyu günceller. Veri satır sayısı değişecekse kaynağı tablo yapmak, özet tablonun yeni satırları kaçırmasını önler.

3. Bağlantı yenileme durumunu izleme​

Çok sayıda bağlantısı olan raporlarda hangi kaynağın ne zaman yenilendiğini kayıt altında tutmak yararlıdır. Bu örnekte bağlantı adı, kaynak türü, son yenileme tarihi ve kontrol tarihi bir listede tutulur. E sütunu iki tarih arasındaki gün farkını hesaplar; F sütunu fark 7 günden büyükse Eski, değilse Güncel yazar. Eski durumundaki hücreler koşullu biçimlendirmeyle kırmızıya boyanır.
Kur_Web bağlantısında gün farkı 13 olarak hesaplanır ve durum Eski görünür; bu satırın yalnızca Durum hücresi koşullu biçimlendirmeyle kırmızı tonlarla boyanır. Satis_SQL bağlantısının durumu Güncel sonucunu verir ve bu hücre boyanmaz. Gerçek raporlarda son yenileme tarihini bağlantı özelliklerinden ya da yenileme sırasında güncellenen bir hücreden alabilirsiniz.

Kullanırken dikkat edin​

• Dosya yolu değişirse bağlantı kopar: Metin, CSV ve Excel kaynaklarında bağlantı dosya yolunu saklar. Dosya taşınır ya da yeniden adlandırılırsa yenileme hata verir. Yolu Veri > Veri Al > Veri Kaynağı Ayarları penceresinden güncelleyin. Ortak kullanılan raporlarda yerel disk yolu yerine ağ yolu kullanın.
• Yenileme verileri ezer: Bağlantıyla yüklenen tabloya elle yazdığınız değerler, yenilemede kaybolabilir. Hesaplamalarınızı tablonun dışındaki sütunlara ya da tablonun hesaplanmış sütunlarına koyun. Kaynaktaki sütun adı değişirse sorgu adımı hata verebilir.
• Güvenlik uyarısı ve parolalar: Dış veri bağlantıları güvenilmeyen dosyalarda varsayılan olarak devre dışıdır. Tanımadığınız kaynaktan gelen dosyalarda İçeriği Etkinleştir düğmesini kullanmayın. Veritabanı parolasını dosyada saklamak yerine Windows kimlik doğrulaması ya da her açılışta parola sorma seçeneğini tercih edin.
• Arka planda yenileme ve sıra: Birden çok bağlantı birbirine bağlıysa (örneğin bir sorgu başka bir sorguyu kullanıyorsa) Arka planda yenileme seçeneği sıralamayı bozabilir. Bu durumda bağlantı özelliklerinden arka planda yenilemeyi kapatın. Yenileme sırasında Excel yanıt vermiyorsa büyük veri için filtreyi kaynakta uygulayın.
• Sürüm farkları: Veri Al menüsü ve Power Query, Excel 2016 ve sonrasında yerleşiktir; Excel 2010 ve 2013'te eklenti olarak yüklenir. Eski tip bağlantılar (Veri > Bağlantılar, Dış Veriyi Al grubu) bazı sürümlerde Eski Sihirbazlar adıyla gizli gelir. Excel için Mac sürümünde bağlayıcı sayısı Windows sürümünden azdır.
• Bağlantıları Düzenle ile karıştırmayın: Veri > Bağlantıları Düzenle (Edit Links) komutu başka çalışma kitaplarına verilen formül bağlantılarını yönetir. Dış veri bağlantıları ise Sorgular ve Bağlantılar bölmesinde yönetilir. Bu iki yapı ayrı çalışır.
• İlgili konular:
  • Power Query ve Veri Al ve Dönüştür
  • Sorgular ve Bağlantılar bölmesi
  • Tümünü Yenile komutu
  • Veri Kaynağı Ayarları
  • Tablo ve Özet Tablo
  • Bağlantıları Düzenle (Edit Links)
  • Güvenlik Merkezi ve Güvenilir Konumlar
  • DÜŞEYARA ve EĞER işlevleri

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.