Makrolar

Excelde Makro İle DÜŞEYARA Formülü Nasıl Yapılır

Merhabalar,

Excelde en çok kullanılan formüllerin başında gelen düşeyara formülünü hücrelere yazmadan makro ile nasıl yapılabileceğine göz atalım.Eğer ki düşeyara formülünü yeni kullanıyorsanız Excelde Düşeyara (VLOOKUP) Formülünü Nasıl Kullanırım yazımı okumanızı tavsiye ederim.

Makro yazarken düşeyara formülünü 2 şekilde kullanabiliriz,

1- Application.VLOOKUP(Aranacak_Veri, Aranacak_Tablo_Yeri, Bulunursa_Getirilecek_Sütun_Numarası, Eşleştirme_Şekli)

yada

2- Application.WorksheetFunction.VLOOKUP(Aranacak_Veri, Aranacak_Tablo_Yeri, Bulunursa_Getirilecek_Sütun_Numarası, Eşleştirme_Şekli)

Konunun daha kolay anlaşılması için Düşeyara Formülünün Detaylı Örnekleri yazımda yaptığım örnekleri burada makrolarla yapacağım.

Örnek-1  Aşağıda ki satış raporu tablosundan “Yasin” isimli personelin aylık sattığı ürün sayısını mesaj kutusu (MessageBox) ile gösterelim.

makro_ile_düseyara_formulu
Excel’de Makro ile Düşeyara Formülü

Bu işlem için kullanacağımız kod:

[code lang=”vb”]
Sub DUSEYARA()
Dim isim As String
isim = “Yasin”
urun_miktari = Application.WorksheetFunction.VLookup(isim, Sayfa1.Range(“B1:D12”), 3, False)
MsgBox “Aylık Satılan Ürün Miktarı : ” & urun_miktari
End Sub
[/code]

Yukarıdaki kod blogumuzda String tipinde bir isim değişkeni tanımladık ve ona “Yasin” değerini atadık ve düşeyara formülümüzün sonucunu “urun_miktari” adlı değişkene aktardık.
“urun_miktari” değişkenine herhangi bir tip tanımlaması yapmadığımız için default olarak “variant” tipinde olacaktır.Variant veri tipi hafızada gereğinden fazla yer kaplayacağı için değişken tipi tanımlanması tavsiyedir.

excel_makro_duseyara
Excel’de Marko İle Düşeyara Kullanımı

Aslında normal düşeyara formülünün kullanımından bir farkı yok.Yalnızca yazdığımız kısım ve cümleler farklılık gösteriyor.

Örnek-2  Satış Raporu tablosundan “Yasemin” isimli personelin görevli olduğu reyonu bulalım.

Kodlarımızda kullanacağımız sütunları kırmızı tablo ile belirttim ve sütun index numaralarını yazdım.

excelde_macro_duseyara
Excel’de Makro ile Düşeyara Formülü

“Yasemin” isimli personelin görevli olduğu reyonu bulmak için kullanacağımız kod aşağıdaki gibidir.

[code lang=”vb”]
Sub DUSEYARA()
Dim isim As String
Dim urun_miktari As String
isim = “Yasemin”
urun_miktari = Application.WorksheetFunction.VLookup(isim, Sayfa2.Range(“B1:E12”), 4, False)
MsgBox “Reyon : ” & urun_miktari
End Sub
[/code]

İlk örnekteki kod bloğumuz ile bu kodumuz arasında yalnızca seçtiğimiz sütun (“B1:E12”) ve bulunduğu zaman gösterilecek olan sütun index numarası (4.sütun) değişmiştir.

excelde_makro_ile_duseyara
Excel’de Makro ile Düşeyara Formülünün Kullanımı

Örnek-3  Satış Raporu Tablosundan Personel ID numarasına göre Personel Bilgileri Getirme

H1 hücresine gireceğimiz Personel ID numarasına göre Personelin tüm bilgilerini mesaj kutusu (MessageBox) ile gösterelim. Tablomuzda ki tüm sütunlarımızı kullanacağız ve arama yapacağımız sütunumuz en baştaki Personel ID sütunu.

excelde_duseyara_kullanimi
Excel’de Makro İle Düşeyara Formülünün Kullanımı

Bu sorgulamada kullanacağımız kod bloğumuz aşağıdaki gibidir.

[code lang=”vb”]
Sub DUSEYARA()
Dim personel_ID As Double
Dim isim, satis_miktari, urun_miktari, reyon, mesaj As String
personel_ID = Sayfa2.Range(“H1”).Value
isim = “Personel İsmi : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 2, False)
satis_miktari = “Satış Miktarı :” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12″), 3, False) & ” TL”
urun_miktari = “Ürün Miktarı : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 4, False)
reyon = “Reyon : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 5, False)
mesaj = “Personel Detayları : ” & vbNewLine & isim & vbNewLine & satis_miktari & vbNewLine & urun_miktari & vbNewLine & reyon
MsgBox mesaj
End Sub

[/code]

Yukarıdaki kod bloğumuzda “personel_ID” değişkenine H1 hücresinden aldığımız değeri ve tüm formüllerimizin sonuçlarını bir değişkene atadık ve bu değişkenleri ” vbNewLine ” komutu ile satır satır mesaj kutusuna yazdırdık.Kod bloğumuz çalıştırıldıktan sonra sonuç aşağıdaki gibidir.

excelde_makro_ile_duseyara
Excel’de Makro ile Düşeyara Formülünün Kullanımı

Örnek-4  3. örneğimizde H1 hücresinden aldığımız personel ID numarasını mesaj kutusu (inputbox) ile girelim

Bir önceki örneğimizde H1 hücresinde bulunan personel ID numarasını bilgi girme penceresi ile alıp düşeyara formülümüze aktaralım.

Bu örneğimizde kullanacağımız kod bloğumuz aşağıdaki gibidir.

[code lang=”vb”]
Sub DUSEYARA()
On Error GoTo HataYakalama:
Dim personel_ID As Double
Dim isim, satis_miktari, urun_miktari, reyon, mesaj As String
personel_ID = InputBox(“Personel ID numarasını Giriniz :”)
isim = “Personel İsmi : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 2, False)
satis_miktari = “Satış Miktarı :” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12″), 3, False) & ” TL”
urun_miktari = “Ürün Miktarı : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 4, False)
reyon = “Reyon : ” & Application.WorksheetFunction.VLookup(personel_ID, Sayfa2.Range(“A1:E12”), 5, False)
mesaj = “Personel Detayları : ” & vbNewLine & isim & vbNewLine & satis_miktari & vbNewLine & urun_miktari & vbNewLine & reyon
MsgBox mesaj
Exit Sub
HataYakalama:
If Err.Number = 1004 Then
MsgBox “Personel ID numarası Bulunamadı.”
ElseIf Err.Number = 13 Then
MsgBox “Geçersiz Bir Değer Girdiniz.”
End If
End Sub
[/code]

“personel_ID” değişkenimize “Inputbox” ile veri aktarılmasını sağlıyoruz.Herhangi bir hata olmasına karşı “HataYakalama” bloğu oluşturduk.Burada eğer girdiğimiz numara bulunamazsa yada girilen değer geçersiz (harf yada özel işaretler ) olursa hata verecektir.

GEÇERSİZ DEĞER AÇIKLAMASI: “personel_ID” değişkenimizi numara olarak tanımladığımız için numara dışında başka bir değer girildiği zaman hata vermektedir.

Kod bloğumuzu çalıştırıldığı zaman sonuç aşağıdaki gibi olacaktır.

excelde_makro_ile_duseyara
Excel’de Makro ile Düşeyara Formülünün Kullanımı
excelde_makro_ile_duseyara
Excel’de Makro ile Düşeyara Formülünün Kullanımı
excelde_makro_ile_duseyara
Excel’de Makro ile Düşeyara Formülünün Kullanımı

Konuyla ilgili soru ve önerileriniz için iletişim sayfasını kullanabilir yada yorum bırakabilirsiniz.

22 Yorumlar

  • Avatar
    Fatih

    Merhaba
    Aşağıdaki kodları kullanıyorum fakat #YOK hatası yerine hiç bir olmasın istiyorum
    yardımcı olabilirmisiniz.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range(“a16:A68”)) Is Nothing Then Exit Sub
    Range(“E16:E68”) = Application.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 7, False)
    Range(“B16:B68”) = Application.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 2, False)
    Range(“D16:D68”) = Application.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 9, False)
    Range(“G16:G68”) = Application.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 8, False)
    Range(“E22”) = Application.VLookup(Range(“A22”), Sheets(“Sayfa2”).Range(“A:H”), 7, False)

    End Sub

    Yanıtla
  • Yılmaz Temiz
    Yılmaz Temiz

    Fatih Bey Merhabalar,

    Düşeyara formülünü EĞERHATA formülü içerisine alarak #YOK hatası yerine istediğiniz değeri gösterebilirsiniz.

    Range(“E16:E68”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 7, False), “Hata”)

    Formülde “Hata ” yazan yere “” yazdığınız zaman #YOK hatası yerine boşluk ekleyecektir.

    İyi Çalışmalar

    Yanıtla
  • Avatar
    Metin Demir

    Emeğinize sağlık. Bu anlatımlarınızı videoya dönüştürseniz çok güzek olur diye düşünüyorum.

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Metin Bey,

      Rica ederim , Videolu derslere de en kısa sürede başlayacağım.Daha yararlı ve kolay anlaşılacağını düşünüyorum.

      İyi Çalışmalar

      Yanıtla
  • Avatar
    sezgin

    Peki ben personel ismini aratıp personel id sini msgboxta göstermek istiyorsam? Application.WorksheetFunction.VLookup(isim, Sayfa2.Range(“B1:E12”), -1 False)

    -1 kullanarak çözemedim. tablonun sol tarafına doğru değerleri nasıl kullanabilicem acaba ?

    Yanıtla
  • Yılmaz Temiz
    Yılmaz Temiz

    Merhabalar,

    Düşeyara formülü arama yaptığınız tabloda en soldaki veriyi arar ve bulduğu zaman aranan verinin sağ tarafında kalan verileri getirir. İstediğiniz gibi bir arama yapabilmek için “personel isimi” sütunu ile “personel ID” sütununun yerini değiştirmeniz gerekmektedir. Son hali “personel ismi , personel ID , aylık satış miktarı ” şekilde olmalıdır.

    İyi Çalışmalar

    Yanıtla
  • Avatar
    sezgin

    Yılmaz bey teşekkür ederim ilginiz için,

    Ancak orayı anladığım için sormaktayım. Başka bir yapı üzerinde çalışıyorum ve o sütunların yerini değiştirmem yapıyı anlamsızlaştıracak. Sanırım VLookup fonksiyonu ihtiyacımı karşılayamıyor. Bu isteğimi yerine getirebilecek bir fonksiyon biliyor musunuz? Öğrenmek istediğim buydu.

    Teşekkürler

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhabalar,

      Yurtdışında olduğum için geç cevap verebildim.İstediğiniz işlemi Vlookup fonksiyonu ile yapamazsınız.Ancak Vlookup yerine indis ve kaçıncı formüllerini kullanabilirsiniz.Aşağıdaki örnek size yardımcı olacaktır.

      Application.WorksheetFunction.Index(Sayfa2.Range(“A1:A12”), Application.WorksheetFunction.Match(personel_ID, Sayfa2.Range(“B1:B12”), 0))

      Application.WorksheetFunction.Index(Hangi_Alanı_Göstermek_İstiyorsanız,Application.WorksheetFunction.Match(Aranacak_Veri, Aranılacak_Sütun, 0))

      İyi çalışmalar

      Yanıtla
  • Avatar
    Sezgin

    Cok tesekkur ederim hocam emegine saglik 🙂

    Yanıtla
  • Avatar
    Mustafa

    Merhabalar,

    İstatistikle alakalı bir sorunum var bu konuda bana yardımcı olabilecek arkadaşlar varsa çok memnun olurum. Örnek dosya ektedir. Şimdiden çok teşekkür ederim ederim.

    Örnek olarak 2. sayfada not yazdım daha çok açıklama isteyen olursa mesaj veya konuya yanıt yolu ile sorularınızı sorabilirsiniz.

    Yanıtla
  • Yılmaz Temiz
    Yılmaz Temiz

    Merhaba Mustafa,
    Aslında istediğin raporu pivot tablo ile kolayca ve daha esnek bir yapıyla oluşturabilirsin.Örnek olması için ekte benim oluşturduğum dosyayı inceleyebilirsin.Takıldığın yer olursa yazabilirsin

    İyi Çalışmalar

    Yanıtla
  • Avatar
    ahmet

    Hocam elinize sağlık çok faydalı bir konu olmuş. Yanlız ben bir veriyi başka sayfada arayım getiriyorum fakat bunu makroya dökemedim örneğin =DÜŞEYARA(C2;[aaaa.xlsx]Sayfa1!$C:$L;4;YANLIŞ) aktif aradığım sayfadaki değeri diğer sayfada arasın bulduğu takdirde aktif aradığım sayfadaki belirttiğim satıra getirsin istiyorum mümkünmüdür acaba ?

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Ahmet,

      Sorunun cevabı zaten yazı da mevcut.Yaptığım örnekleri incelersen eğer istediğin veri getirme olayını yapabilirsin.

      İyi Çalışmalar

      Yanıtla
  • Avatar
    Berkcanali

    Merhaba
    240 adet excel kitabım var içinde her birinin 3-5 bin arası kayıt var. Telno ve adsoyad olmak üzere hepsi iki sütun. Telno sütunu dolu adsoyad sütunu boş. Ana dosyamda yine iki sütun var. Telno ve adsoyad. İkisi de dolu. Yaklaşık 270 bin kayıt. 240 adet dosyanın adsoyadı alanına anadosyada telno ile eşleşenlerin adsoyad bilgilerini getirmek istiyorum. Herbirini açıp düşeyara yazmam çok zor ayrıca anadosyada 270bin kayıt olduğundan düşeyara ya da indis olmuyor 65536 satıra kadar izin veriyor formül. Bunun kolay yolu var mıdır makro ile yapılabilir mi 240 adet dosyanın adını 1.2.3..240.xlsx olarak değiştirebilirim döngü ile yapılabilirse. Ya da bunun için access mi gerekir şimdiden teşekkür ederim

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Berkecan Ali,
      Sorununu çözmenin en iyi yolu makro.Çünkü diğer yollardan fazlasıyla iş yükü ve zaman kaybı olacaktır.Konuyla ilgili detaylı görüşmek istersen iletişim sayfasından bana ulaşabilirsin.

      Not:Düşeyara formülünün herhangi bir sınırı yok.istersen belirli satır numarasına kadar istersen direk sütun olarak arama yaptırabilirsin.

      İyi Çalışmalar

      Yanıtla
  • Avatar
    Fatih

    Merhabalar Yılmaz bey
    düşeyarada yok hatasını engellemek için aşağıdaki makroyu yaptım çalışmadı nerde yanlış yapmışım bakabilirmisiniz. dosyasıda ektedir.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, [A16:A68]) Is Nothing Then Exit Sub
    Range(“E16:E68”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 7, False), “Hata”)
    Range(“B16:B68”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 2, False), “Hata”)
    Range(“D16:D68”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 9, False), “Hata”)
    Range(“G16:G68”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A16:A68”), Sheets(“Sayfa2”).Range(“A:T”), 8, False), “Hata”)

    Range(“E22”) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range(“A22”), Sheets(“Sayfa2”).Range(“A:H”), 7, False), “Hata”)

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Fatih,

      Kodlarında herhangi bir sorun görünmüyor.Çalıştığın dosyayı gönderebilirsen o şekilde kontrol etmek daha iyi olacaktır.

      İyi Çalışmalar

      Yanıtla
  • Avatar
    Ayhan

    Hocam merhabalar,
    Y=Application.WorksheetFunction.Vlookup(S1.Cells(2,9),S3.Range(“A1:M24),S1.Cells(2,8),False)
    Hocam kodu çalistirdigimda Run time error 1004 mesajini veriyor worksheetfunction sinifinin vlookup ozelligi alinamiyor

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Ayhan,
      İletişim sayfasından dosyanı gönderebilir misin.
      İyi Çalışmalar

      Yanıtla
  • Avatar
    mehmet Güder

    Slm.
    Arkadaşlar,
    iki dosyam ver.
    1.Dosyam ::içinde datalarımın olduğu bir dosya.
    2.Dosyam:Rapor Analiz dosyam. Bu dosya ya Data dosyasından veri çekiyorum.
    Çektiğim veriler birden fazla kritere sahip olduğundan ÇOKETOPLA ile yapabiliyorum. Fakat, Data dosyası kapalı olduğunda verileri çekemiyorum.
    Araştırdım, kapalı dosyalarda ÇOKETOPLA işlem yapmıyor. Bu sorunu başka hangi yöntem ile çözebilirim

    Yanıtla
  • Avatar
    Emine

    Merhaba,

    Excel’de bir sütunda bulunan verilere Makro ile filtre koymak ve burada bulunan verileri büyükten küçüğe sıralama ya da alfabetik sıraya göre sıralamak istiyorum. Yardımcı olur musunuz ?

    Yanıtla
    • Yılmaz Temiz
      Yılmaz Temiz

      Merhaba Emine,
      İletişim sayfasından örnek dosyanı ve detaylarını belirtir misin.
      İyi Çalışmalar

      Yanıtla

Yorum Yap

Pin It on Pinterest