Dış Veri Kaynaklarından Veri Alma

Dış veri kaynaklarından veri alma (Get External Data), Excel dışındaki metin dosyalarını, veritabanlarını, web sayfalarını ve başka çalışma kitaplarını Excel'e bağlayıp getirme işlemidir. Bunun için Veri > Veri Al ve Dönüştür > Veri Al (Data > Get & Transform Data > Get Data) komutunu kullanır, kaynağı seçer ve veriyi Yükle (Load) ile çalışma sayfasına alırsınız. Bağlantı saklandığı için kaynak değiştiğinde veriyi yeniden içe aktarmadan yenileyebilirsiniz.

Excel'de dış veri kaynaklarından veri nasıl alınır​

Veri alma işlemi Veri (Data) sekmesindeki Veri Al ve Dönüştür grubundan başlar. Veri Al düğmesi kaynakları gruplar halinde sunar. Düğmelerin tam yeri Excel sürümüne göre biraz değişebilir:

• Dosyadan: Çalışma Kitabından (From Workbook), Metin/CSV'den (From Text/CSV), XML, JSON ve klasör gibi dosya tabanlı kaynaklar.
• Veritabanından: SQL Server ve Access gibi veritabanları.
• Diğer Kaynaklardan: Web'den (From Web) ve ODBC'den (From ODBC) gibi seçenekler.
• Tablo/Aralıktan: Aynı çalışma kitabındaki bir aralığı Power Query'ye göndermek için kullanılır.

Kaynağı seçtikten sonra çoğu durumda Gezgin (Navigator) penceresi açılır. Soldan almak istediğiniz tabloyu ya da sayfayı seçersiniz. Yükle düğmesi veriyi doğrudan yeni bir sayfaya getirir, Dönüştür (Transform Data) düğmesi ise önce temizlemeniz için Power Query Düzenleyicisi'ni açar.
Metin/CSV dosyalarında önizleme penceresinde dosya kaynağını (kodlama), ayırıcıyı ve veri türü algılamasını kontrol edin. Türkçe karakterler bozuk görünüyorsa kodlamayı UTF-8 olarak değiştirmeniz genellikle sorunu çözer. Veritabanı kaynaklarında sunucu adı, veritabanı adı ve kimlik doğrulama bilgisi (Windows kimliği ya da kullanıcı adı ve şifre) istenir. Web kaynağında ise sayfa adresini girer, Gezgin'de listelenen tablolardan birini seçersiniz.

Power Query ve ODBC ile veri alma​

Power Query, veriyi sayfaya getirmeden önce temizleyip biçimlendirmenizi sağlayan araçtır. Gezgin'de Dönüştür dediğinizde açılan düzenleyicide yaptığınız her işlem Uygulanan Adımlar (Applied Steps) listesine kaydedilir. Veri yenilendiğinde bu adımlar yeni verinin üzerinde yeniden çalışır. Sık kullanılan dönüşümler şunlardır:

• Veri türünü değiştirme: Metin olarak gelen sayıları ya da tarihleri doğru türe çevirir.
• Satır ve sütun temizliği: Boş satırları, yinelenen kayıtları ve gereksiz sütunları kaldırır.
• Sütun bölme: Tek sütunda birleşik gelen bilgileri ayırır.
• Sorguları Birleştir ve Ekle: Sorguları Birleştir (Merge Queries) iki tabloyu ortak sütunla eşleştirir, Sorguları Ekle (Append Queries) tabloları alt alta ekler.

Düzenlemeyi bitirince Giriş > Kapat ve Yükle (Home > Close & Load) komutuyla sonucu Excel'e aktarırsınız. Kapat ve Şuraya Yükle seçeneğiyle veriyi tablo, PivotTable raporu ya da yalnızca bağlantı olarak da alabilirsiniz.
ODBC (Open Database Connectivity), Excel'in ODBC sürücüsü bulunan veritabanlarına bağlanmasını sağlayan bir standarttır. Veri Al > Diğer Kaynaklardan > ODBC'den yolunu izler, listeden veri kaynağı adını (DSN) seçer ya da bağlantı dizesini girersiniz. İlgili ODBC sürücüsünün bilgisayarınızda kurulu olması gerekir. Gelişmiş seçenekte isterseniz SQL ifadesi yazarak yalnızca gereken kayıtları getirebilirsiniz.

Dış verileri yenileme ve bağlantıları yönetme​

Dış veri otomatik olarak güncellenmez; varsayılan olarak siz yenilemeyi başlatırsınız. Tüm bağlantıları yenilemek için Veri > Tümünü Yenile (Data > Refresh All) düğmesini kullanın. Tek bir sorguyu yenilemek için getirilen tablonun içinde sağ tıklayıp Yenile (Refresh) seçeneğini seçebilirsiniz.
Bağlantıları görmek için Veri > Sorgular ve Bağlantılar (Data > Queries & Connections) bölmesini açın. Bir sorgunun üzerine gelip Özellikler (Properties) penceresine girdiğinizde şunları ayarlayabilirsiniz:

• Belirli aralıklarla yenileme: Dakika cinsinden süre vererek verinin düzenli yenilenmesini sağlar.
• Dosyayı açarken yenileme: Çalışma kitabı her açıldığında veriyi günceller.
• Arka planda yenileme: Yenileme sürerken Excel'i kullanmaya devam etmenizi sağlar.

Kaynak dosyanın yeri, sunucu adı ya da kimlik bilgisi değiştiğinde Veri Al > Veri Kaynağı Ayarları (Data Source Settings) penceresinden bağlantıyı güncelleyin. Getirilen veri Excel tablosu olarak yüklendiğinden, tablonun satır sayısı yenilemede değişse bile tablo başvurusu kullanan formüller yeni satırları da kapsar.

1. Metin dosyasından gelen verileri temizleme​

Metin/CSV dosyasından getirilen bir sipariş listesinde şehir adlarının başında ya da sonunda gereksiz boşluklar vardır ve tutarlar metin olarak durmaktadır. D ve E sütunları bu verileri temizler; son satırda temizlenmiş tutarların toplamı hesaplanır.
D2 hücresi şehir adındaki fazla boşlukları temizler ve sonuç Ankara olur. E2 hücresi metin olarak gelen tutarı gerçek sayıya çevirir ve 1.250 değerini verir. Tüm tutarların toplamı E8 hücresinde 8.675 olarak hesaplanır. Aynı işlemi Power Query'de veri türünü değiştirerek ve boşlukları kırparak yenileme sırasında otomatik de yaptırabilirsiniz.

2. Başka çalışma kitabından gelen fiyat listesini eşleştirme​

Fiyat listesi başka bir çalışma kitabından getirilmiş ve E:F sütunlarına yerleşmiştir. Soldaki sipariş listesi için birim fiyat DÜŞEYARA (VLOOKUP) ile bulunur, tutar ise adet ile birim fiyatın çarpımıdır.
C2 hücresi fare için listedeki fiyatı bulur ve 280,00 sonucunu verir. D2 hücresinde 10 adetlik siparişin tutarı 2.800,00 olarak hesaplanır. D9 hücresindeki sipariş toplamı 36.980,00 olur. Fiyat listesi yenilendiğinde formüller yeni fiyatlara göre kendiliğinden yeniden hesaplanır.

3. Yenilenen veriyi şehre göre özetleme​

Veritabanından ya da web kaynağından getirilen satış kayıtları A:C sütunlarındadır. E:F sütunlarındaki özet, ÇOKETOPLA (SUMIFS) ile şehir bazında toplam alır; veri yenilendiğinde özet de güncellenir.
F2 hücresi İstanbul'un toplam satışını 45.700,00, F3 hücresi Ankara'nın toplamını 23.500,00, F4 hücresi İzmir'in toplamını 13.500,00 olarak verir. Genel toplam F5 hücresinde 82.700,00 değerindedir. Getirilen veriyi Excel tablosu olarak yükler ve formüllerde tablo sütun adlarını kullanırsanız, yenileme sonrası eklenen satırlar özete kendiliğinden dahil olur.

Sık sorulan sorular​

• Veriler kendiliğinden güncellenir mi? Hayır. Yenileme yapmanız ya da bağlantı özelliklerinden belirli aralıkla ya da dosya açılırken yenilemeyi etkinleştirmeniz gerekir.
• Excel'de değişiklik yaparsam kaynak dosya etkilenir mi? Hayır. Getirilen veri bir kopyadır; sayfada yaptığınız değişiklikler kaynağa geri yazılmaz ve bir sonraki yenilemede sorgu çıktısı yeniden oluşur.
• Veri Al düğmesini göremiyorum, ne yapmalıyım? Excel 2016 ve sonrasında bu özellik yerleşiktir. Excel 2010 ve 2013'te Power Query eklentisini ayrıca kurmanız gerekir.
• Türkçe karakterler bozuk geliyor, nasıl düzeltirim? Metin/CSV önizleme penceresinde dosya kaynağı (kodlama) ayarını değiştirin; çoğu dosyada UTF-8 doğru sonucu verir.

Kullanırken dikkat edin​

• Sürüm farkına dikkat edin: Veri Al ve Dönüştür grubu Excel 2016 ve sonrasında yerleşiktir. Excel 2010 ve 2013'te Power Query ayrı bir eklenti olarak kurulur. Menü adları ve düğmelerin yeri Microsoft 365 güncellemeleriyle zaman zaman değişebilir.
• Yenileme kendiliğinden olmaz: Varsayılan ayarda veri yalnızca siz yenilediğinizde güncellenir. Raporunuzun güncel kalması gerekiyorsa sorgu özelliklerinden dosya açılırken ya da belirli aralıklarla yenilemeyi açın.
• Kaynak yolu değişirse bağlantı kopar: Kaynak dosyayı başka klasöre taşırsanız ya da sunucu adı değişirse yenileme hata verir. Bu durumda Veri Kaynağı Ayarları penceresinden yolu güncelleyin. Çalışma kitabını paylaşırken kaynağa diğer kullanıcıların da erişebildiğinden emin olun.
• Güvenlik ve kimlik bilgileri: Bağlantılı bir dosyayı açtığınızda Excel güvenlik uyarısı gösterebilir; içeriği yalnızca güvendiğiniz dosyalarda etkinleştirin. Kimlik bilgisi içeren bağlantıları paylaşmadan önce kontrol edin ve gerekirse bilgileri silin.
• Web verisi değişebilir: Web sayfasının yapısı değiştiğinde sorgu doğru tabloyu bulamayabilir ve yenileme hata verebilir. Web sitelerinden veri alırken sitenin kullanım koşullarını da göz önünde bulundurun.
• Veri türlerini kontrol edin: Metin dosyalarında sayılar ve tarihler metin olarak gelebilir. Ayırıcı ve yerel ayar farklarından dolayı tarih ve ondalıklı sayılar yanlış yorumlanabildiği için Power Query'de sütun türlerini yüklemeden önce denetleyin.
• İlgili konular: Power Query Düzenleyicisi, Sorgular ve Bağlantılar bölmesi, Tümünü Yenile, Excel tablosu ve yapısal başvurular, Veri Kaynağı Ayarları, DÜŞEYARA, ÇOKETOPLA, ETOPLA, KIRP işlevleri.

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