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, ilgili dosyalara buradan erişebilirsiniz:

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 yapabilmek için önce bir temel sayıya ihtiyacınız var misal 22830, buna SATIRSAY($A$1:$A1) gibi bir formülle değer eklemeniz gerek. Eğer sayıların başında 0 her zaman bulunuyor ise aşağıdaki formülü kullanmanız gerek:
      =”0″ & (22830 + SATIRSAY($A$1:$A1))
      Bu sayede satırda aşağıya kaydırdıkça numara da artacaktır. Ancak sayının başında her zaman sıfır olmuyor ise, yani sayı belli bir değerin üzerinde olunca sıfır koyuyorsanız:

      =EĞER(UZUNLUK(22830+SATIRSAY($A$1:$A1))<6;"0" &(22830 + SATIRSAY($A$1:$A1));22830 + SATIRSAY($A$1:$A1))

      gibi bir formül kullanmanız gerek.

  3. merhaba,benim ürünlerim var.barkodları sıralı.(resim dosyası adlı)
    ben başka bir dosyaya(gönderi dosyası), aynı ürünleri barkodlarıyla girdim. istiyorum ki excel gönderi dosyasında ki ürünle aynı barkoda sahip resim dosyasında ki ürünün Resim sütununda ki bilgiyi getirsin..
    böyle bir şey mümkün müdür?

    • Merhaba,

      Power Query ile bir uygulama yapabilirsiniz. Veri sekmesinde Veri Al–>Dosyadan->Excel Çalışma Kitabından seçeneği ile ilgili resim dosyasını gösterirseniz oradaki çalışma sayfalarını gösteren bir pencere açılacaktır. İstediğiniz çalışma sayfasını göstererek o sayfadaki tüm listeleri bir sorgu ile alabilirsiniz. Sorguyu “Bağlantı olarak” alırsanız sadece veriler hafızaya alınır. Sonrasında sadece barkodların olduğu listeyi de bir Excel Tablosu haline getirerek bunu da yine Veri Sekmesinden Veri Al–>Tablodan diyerek yine bir bağlantı olarak alırsanız elinizde iki tane bağlantı halinde tablo olacaktır. En son adımda Veri sekmesinde Veri Al–>Sorguları Birleştir–> Birleştir seçeneğini kullanarak bu iki tabloyu birleştirmeniz gerekiyor. Bu seçeneği seçtiğinizde iki tane sorgu tablosu seçmeniz istenecek, ilk tablo sizin barkod listeniz olacak. ikinci tablo çektiğiniz tablo.

      Siz bu adımları uygulayın, olmaz ise tekrar iletişime geçin.

      düzeltme: http://excelileharikalar.com/index.php/2020/04/17/power-query-ile-tablolari-birlestirme/

      Saygılarımla,

  4. Merhaba
    Excelde bir sayfada yatay şekilde sıralı veri dizinim var. Bu dizini aynı excel dosyası içinde başka bir sayfaya dikey şekilde formül yardımıyla getirmem mümkün mü?
    Teşekkürler

  5. Merhaba, behim 30 ayrı sayfam var her gün için ayrı veri giriyorum ancak her ayın sonunda tek kek tarih değiştirmek istemiyorum.2-C kutusunda bulunan tarihi bir sayfada değiştirerek tüm sayfalara ardışık geçirebilirmiyim.

    • Merhbaba,

      ‘Sayfa_Adı’!$C$2 şeklinde bir başvuru ile her zaman ilgili sayfanın C2 hücresine başvuru yapılır ve bu hücrenin değeri alınır. Zaten bu tarz çoklu sayfalarda ortak veriler kullanılacak ise, ortak verilerin tek bir sayfada tutulması ve diğer sayfalardan bu sayfadaki verilere başvurulması hem hata ihtimalini azaltır hem de müdahale çok daha kolaydır.

      Saygılarımnla,

  6. Merhaba, sayfanın birinde bütün müşteri listem mevcut satırın başında poliçeleşti kelimesini girdiğim zaman poliçeleşti kelimesinin bulunduğu satırı diğer sayfaya nasıl alabilirim teşekkürler şimdiden.

  7. Merhaba. Ben birşey sormak istiyorum. Mesela kitaplar derken şöyle birşey yapılabilir mi?
    Kitap, kırtasiye sitesi olan https://rehberkitapkirtasiye.com/ sitesinden kitapları excel içine formülle excele aktaramazmıyız. Mesela bu sitede ki tüm kitapları yada diyelim ki kategori linki olan ürünleri excele aktarma işlemini yapabilir miyiz?

    • Merhaba,
      Bunun bir yöntemi var ancak tam olarak neyi çekeceğini belirlemek için sayfada bazı tablo yapılarının olması gerekiyor. Bunun için Veri sekmesinde WEB Sayfasından komutu var, farklı Excel sürümlerinde farklı yerlerde, onu deneyebilirsiniz. Ama dediğim gibi tanımlı tablolar olması gerekiyor
      Saygılarımla

  8. Merhaba, iki farklı excel dosyam var. Birincisinde bir sayıya ürüne atanmış bir sayı var. Diğerinde ürün grubuna atanmış bir sayı ve ürün isimleri geçiyor. Ürüne atanan sayıdan ürün grubuna atanmış sayıyı bulup oradan ürün ismini manuel olarak almam gerekiyor ve bazen 300-400 gibi satır sayılarına çıkıyor. Ürüne atanmış sayıyı yazdığım zaman otomatik olarak ürün gurubuna atanmış sayıdan ismi bir sağ hücresine yazdırma imkanı var mıdır? Birçok ürüne aynı ürün grubu numarası atandığı için sürükle bırak yapamıyorum. Aynı zamanda ürün numarasını yazdığım zaman başka bir excel de bu numarayı aratıp bir sağ hücresindeki veriyi çekme imkanımız varmıdır?

    Teşekkür ederim.

    • Merhaba,
      Ürün grupları numaralarının bulunduğu numarada ürün numaraları da var mı? yoksa ürün grubu ve ürün adı mı yer alıyor? Eğer ürün grubu ve ürün numarası var ise KAÇINCI formülü ile ürün numarasının kaçıncı satırda olduğunu bulup buna göre ürün grup numarasını ve diğer bilgileri çekebilirsiniz. Eğer Ürün gruplarının yer aldığı tabloda sadece ürün adları yer alıyor ise ürün adı kullanarak bir arama yapabilirsiniz ancak ürün adları her iki tabloda da tamamen aynı olmak zorunda.

      Eğer bu iki tablo farklı dosyada ise bu yazıda anlattığım yöntem ile veya Power Query kullanarak bu verileri çekebilirsiniz. Yine Power Query “Tabloları Birleştirme” yöntemini de kullanarak bir çalışma yapabilirsiniz.

      Saygılarımla,

  9. Merhaba, benim merak ettiğim sürekli artan bir excel dosyaları havuzumuz olsun. İçerik olarak hepsi standardize edilmiş olsun.
    ilk sorum, isimlerini standardize etmenin yolları olup olmadığı,
    ikinci sorum, bu dosyalarda biriken veriyi tek bir excel dosyasına otomatik olarak çekip çekemeyeceğimiz.
    Sorularım birbirinden bağımsızdır.
    Teşekkür ederim

    • Merhaba,
      İsimleri standardize etmenin tek yolu bir şablon ve ufak bir makro kullanarak kayıt öncesi dosya ismi ve konumunu kontrol etmek ve otomatik olarak isim vermesini sağlamaktır. Ancak anlatıldığı kadar kolay değildir. Dosyanın kaydedileceği konumda aynı isimde bir başka dosya olmadığını kontrol etmek gerekir.

      Aynı şablona sahip birden fazla dosyadan verileri tek bir dosyaya çekmenin en verimli yolu Power Query kullanmak. Site içinde Power Query ile ilgili sayfalara bakarsanız benzer bir uygulama bulabilirsiniz.

      Saygılarımla,

  10. merhaba,
    elimde barkod numaraları yazılı olan bir liste var ve bu listeye her gün ekleme yapıyorum.yapmak istediğim 1-2-3 v.s ile başlayan numaraların listesini sıralı olarak ayrı bir kitapta listelemek.bunu nasıl yapabilirim.

    • Merhaba,

      Öncelikle değerlerin olduğu alanı bir değişken aralığa çevireceksiniz ya da bir tablo haline getireceksiniz. Sonrasında =TOPLAMA(15;6;(SATIR(ARALIK)/(SOLDAN(ARALIK;1)=”1″));SATIRSAY($B$1:$B1)) gibi bir formül ile hangi satırlarda istediğiniz değerlerin olduğunu bulacaksınız. Burada SATIRSAY($B$1:$B1) formülünü, formülü yazdığınız hücreye göre belirlenmeli. Sonrasıda elde ettiğiniz satır numarasını İNDİS gibi bir formül ile değere döndürebilirsiniz.

      Ya da Power Query ile basit bir filtre uygulaması yapabilirsiniz. Ancak bu durumda tabloya yeni eklemeler olduğunda sorguyu tekrar yenilemeniz gerekir.

      Saygılarımla,

  11. EKLEME;aslında yapmak istediğim ben ana listeye ekleme yaptıkça formül otomatik çalışşsın ve 1-2-3 ile başlayan numaralar otomatik diğer kitapta sıralansın.

  12. Merhaba. 12 sütun bilgi girdiğim bir excel sayfasından başka bir sayfaya bazı sütunlardaki (Örneğin; 1. 3. 4. 7. ve 10. sütunlara ait) son 10 veriyi çekebilir miyim? Mümkün müdür?

    • Merhaba,

      =BÜYÜK(SATIR($A$1:$A$12);n) formülü ile en büyük n. satırı bulabilirsiniz. Buradaki n değerini SATIRSAY($A$1:$A1) gibi bir formülle satıra göre otomatik olarak oluşmasını sağlayabilirsiniz. Sonrasında İNDİS formülü ile istediğiniz satırdaki değeri çekebilirsiniz.

      Saygılarımla,

  13. Merhabalar;

    Bir excel sayfası içerisinde satır içerisinde bir şart var örneğin; ali 1500 bulmaca mayıs yazıyor ilk satırda ve ikinci satırda veli 1300 ankara nisan yazıyor. Satırda yazanları mayıs ve nisan sayfalarına atmak istiyorum ama öncesinde yazanlar ile birlikte nasıl yapabilirim?

    • Merhaba,

      Sayfa ismine göre otomatik olarak alsın diyorsanız bu biraz uzun bir iş. yok sadece aya göre yapsın derseniz bu ifadeler ayrı sütunlarda mı yoksa tek bir hücrede bitişik mi yazılmış? buna göre farklı formüller gerekiyor

      Saygılarımla,

  14. Merhaba,
    farklı raporlar çalıştırıyorum ve diyelim ki 4 rapor sonucu 4 farklı excell listesi var. ben bu 4 excell de olan aynı isimleri nasıltespit ederim.
    farklı raporlardaki ortak isimleri teespit etmek amacım. 4raporda da hasan var ve ben tek tek incelem yerine ortak olanları bulmak istiyorum.
    en az 2 tanesinde ve üstünde olan aynı isimlerin tesğpiti.
    tşkler

Leave a Reply