Bu yazımda biraz ilginç bir konuya değinmek istiyorum. Misal aylık veya haftalık olarak farklı sayfalarda hazırladığınız bir raporunuz var ve siz bu ay hazırladığınız sayfada bir önceki ayın sayfasından bazı değerler almak istiyorsunuz yada haftalık raporlarınızı tek bir sayfada özetlemek istiyorsunuz. Bunu yapmak için her seferinde diğer sayfalardan referans almak ya da o sayfalardaki veriyi kopyalamak bir çözüm olabilir tabii ancak verimli mi? Peki biz bunu otomatik olarak bir formülle yapabilir miyiz? Tabii ki evet ama bir şart var. Sayfa isimleri bir standarda bağlı olmalı. Hatta size bu sayede başka çalışma kitaplarında da veri çekebileceğinizi söylesem? 

Not: Bu yazı oldukça fazla ilgi gördü, şahsen bu kadar büyük bir ihtiyaç olduğunu fark etmemiştim. Bu yazıda anlatılan yöntem oldukça eski, formüle dayalı ve kullanıcıya bağlı güncelleme isteyen bir yöntem. Bu yöntemden daha verimli olan Power Query kullanarak ver çekme için buradaki ve buradaki yazıları okumanızı şiddetle tavsiye ediyorum.
Uygulama
1. Farklı Sayfadan Veri Çekme

​Elimizde aşağıdaki gibi farklı sayfalarda aynı başlıklara sahip farklı zaman dilimlerine ait verilerin olduğu bir dosya olduğunu varsayalım:

Verimlilik Tablosu
Ana Rapor

Resimlerde gördüğünüz üzere basit bir üretim takip tablomuz var ve bu takip haftalık olarak farklı sayfalarda toplanıyor. Ancak yıl sonunda veya belirli aralıklara tezgah verimliliği raporladığımızı düşünelim. Bu durumda verimleri her sayfadan ayrı ayrı kopyalamak biraz sıkıntı olacaktır. Hatta sadece verimlilik değil bu tabloların sadece belirli ve birbirinden bağımsız hücrelerinden veri çekmek durumunda kalabiliriz.

Formül ile değişken sayfadan veri çekme
2. Farklı Kitaptan Veri Çekme

Farklı kitaptan veri çekme için kullanacağımız formül de benzer bir yapıda olacak. Misal ben buradaki tezgah verimlerini takip ettiğim dosyaları Hafta1 ve Hafta2 gibi isimlere sahip ayrı dosyalar olarak tuttuğumu varsayalım. Aşağıdaki resimde dosya içeriğini ve dizin yapısını gösterdim:

Hafta 1 Verim Dosyası
Hafta 2 Verim Dosyası
Dosyaların Konulduğu Dizin

Excel’de açık olan ana çalışma kitabına, açık olan başka bir kitaptan veri çekmek için ana kitapta verinin yer almasını istediğiniz hücreye girerek “=” yazdıktan sonra çekmek istediğiniz verinin yer aldığı sayfadaki hücreye tıklayın. Dosya açık olduğu için Excel ‘[DosyaAdi.xlsx]SayfaAdı’!HucreAdresi şeklinde bir formül oluşturur ve o değeri ana dosyada gösterir. Siz alt dosyayı kapattığınız anda bu formül ‘Dosyadizini\[DosyaAdı.xlsx]SayfaAdı’!HucreAdresi şekline dönüşecektir.

İşin güzel tarafı siz bu formülü elle yazarak ta istediğiniz sonucu elde edebilirsiniz. Yani illa alt dosyayı açmanıza gerek yok. Şimdi ben bu formülü kendim yazacağım:

=’D:\Test\[TezgahVerim.xlsx]Hafta 1′!$E3

Bu formülü yazıp çalıştırdığımda Excel ilgili dosyadaki verileri çekerek bu dosya içine getirecektir. Dosyayı kaydedip tekrar açtığınızda ise farklı bir dosyadan veri çekildiği ve bu verileri güncellemek isteyip istemediğiniz soracaktır. Güncelle derseniz ilgili dosyadan güncel değerler alınacaktır.

Formül İle Başka Dosyadan Veri Çekme Uygulaması

Gördüğünüz gibi istediğimiz dosyaların istediğimiz sayfalarından verilerimizi çektik. Şimdi burada hafta numarasını 3. satırdan, hücre satır numarasını da formülün yazıldığı satıra göre oluşturan bir dolaylı formülünü kullanmayı deneyelim:

=DOLAYLI(“‘D:\Test\[Hafta” &C$3 &”.xlsx]Verim’!$E” &SATIR()-1)

Aşağıdaki videoda formülü, sonucunu ve değerlendirme sonucunu gösterdim:

Dolaylı Formülü İle Farklı Dosyalardan Veri Çekme

Gördüğünüz gibi formül #BAŞV! (#REF!) hatası verdi. Biraz hayal kırıklığı oluşmuştur tabii ama ne yazık ki dolaylı formülü dosya, sayfa ismini ve hücre adresini doğru olarak hesaplasa dahi kapalı dosyalardan veri çekemez. Eğer hatalı isim ve hücre adresi girildi ise de aynı hatayı alırsınız. Verileri çekebilmek için ilgili dosyaların açık olması gerekmektedir. İsterseniz ilgili dosyaları açarak tekrar deneyelim:

Gördüğünüz üzere ilgili dosyalar açıldıktan sonra formülde bir değişiklik yapmamamıza rağmen verileri çekerek ilgili hücrelere yazdı.

3. Neler Yapılabilir

Aylık hazırlanan raporlarda aylar metin olarak sayfa isimlerine yazıldı ise misal siz Ocak ayına ait verileri Ocak adında bir sayfada tutuyorsanız METNEÇEVİR(1, “aaaa”) formülü ile Ocak değerini elde edebilir ve bunu DOLAYLI formülünde kullanarak Ocak ayı verilerinin yer aldığı sayfadan veri çekebilirsiniz. Hatta buradaki 1 rakamını tarih içeren bir hücreden AY formülü ile  de çekebilirsiniz.

Gördüğünüz gibi DOLAYLI formülü bize çokça esneklik sağlamakta. Umarım sizler için faydalı bir yazı olmuştur.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , ,

Leave a Reply