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: , , ,
  1. A B C D
    Ürün Ürün fiyatı
    1 Elma 15
    2 Armut 13
    3 Kiraz 17
    4 Elma 21
    5 Kiraz 52
    6 Elma 57

    EXCELDE ŞARTA BAĞLI EN SON METİN VERİYİ AKTARMA
    Yukarıdaki tabloda bir ürüne bağlı fiyat değerleri var. Ben ürünle ilgili güncel olan en son veriyi çağırmak, almak yada aktarmak istiyorum. Örn. Burada elma birden çok değeri var ve ben elmaya bağlı en son en güncel değeri çağırmak istiyorum yani 1. Sıradaki elmanın değeri olan 15 değil de 6. Sıradaki elmanın değeri olan 57’yi bulmak çağırmak istiyorum. Düşeyara formülünü kullandım. Düşeyara formülü ilk veriyi çağırıyor. Yani elmaya göre düşünürsek 15 değerini getiriyor ama ben 57’yi çağırmak istiyorum. Nasıl yapabilirim. Yardımcı olursanız sevinirim. İyi günler.

    • Merhaba,
      Burada ARA formülünü kullanabilirsiniz. Ürün isimlerini B sütununa, fiyatlarını C sütununa yazdığınızı varsayıyorum (başlarında 1-2-3 vs olmamalı) aşağıdaki formül en son elma değerini bulur ve C sütununda denk gelen rakamı geri döndürür.
      =ARA(2;1/($B$2:$B$7=”Elma”);$C$2:$C$7)

  2. merhaba, benim şöyle bir sorunum var. 2019 isimli bir klasörün içinde excel dosyaları var. dosyaların isimleri de sıralı (022834,022835 … şeklinde) bir tane ana veri dosyam var. bu ana dosyaya 2019 klasöründeki dosyalardan sabit verileri getirmek istiyorum. örnek vermek gerekirse; Y11 hücresinde tutar var, tutarı ana veri dosyasına getirmek için formül girdiğimde =[022834.xlsx]Sayfa1!$Y$11 bu şekilde bir formül oluşuyor. yapmak istediğim formülü aşağıya çektiğimde 022834 olan dosya adı 022835,022836,022837 şeklinde değişsin ki tek tek uğramayalım. yardım edebilirseniz çok sevinirim.

    • Merhaba,
      Bunu yapabilmeniz için DOLAYLI formülünü kullanmanız lazım ancak bu formülü kullanırsanız ilgili dosya açık değilse veri çekemez. Bunun yerine Power Query ile dizindeki tüm dosyalardan veri çekme yazısındak yöntemi kullanabilirsiniz. Sorguyu bir kere oluşturduktan sonra dizine yeni dosya eklediğinizde sadece sorguyu güncellerseniz yeni eklenen dosyadaki değerler de ana dosyaya aktarılır.

Leave a Reply