makro ile düşeyara nasıl yapılır

Excelde KAÇINCI (MATCH) Formülünün Kullanımı

Yılmaz Temiz
22 Ocak 2017

Merhabalar,
Arama ve Başvuru formülleri arasında bulunan Kaçıncı formülünde belirtilen hücre aralığında istediğiniz veriyi arar ve verinin konumunu cevap olarak döndürür.Yani verinin kendisi değil yerini bulmak istediğinizde KAÇINCI formülünü kullanmalısınız.

Formülün kullanımı

=KAÇINCI(Aranacak_veri;Bakılacak_hücre_Aralığı;Eşleştirme_Türü)

Aranacak_veri : Belirtilecek hücre aralığında aranmasını istediğini değerdir.Sayı,metin yada bir hücre başvurusu olabilir
Bakılacak_hücre_Aralığı: Belirtilen değerin aranacağı hücre aralığı
Eşleştirme_Türü: 3 adet eşleştirme seçeneği mevcuttur. 1-Küçük , 0-Tam Eşleme ve -1-Büyük
1. 1-Küçük : Aranan değere eşit yada aranan değerden küçük olan ilk değeri verir.Bu eşleştirme türü kullanılırken bakılan hücre aralığı artan şekilde sıralanmış olması gerekmektedir.Örnek olarak 0,1,2,3.. yada A’dan Z’ye gibi
2. 0-Tam Eşleme : Aranan değere tam olarak eşit olan veriyi bulur.Bakılan hücre aralığı karışık sırada olabilir
3. -1 Büyük : Aranan değere eşit yada aranan değerden büyük olan ilk değeri verir.Bu eşleştirme türü kullanılırken bakılan hücre aralığı artan şekilde sıralanmış olması gerekmektedir.Örnek olarak 3,2,1,0.. yada Z’den A’ya gibi

Aşağıdaki örnekte KAÇINCI formülümüz “93” sayısını A1 ile A16 arasında arayıp bize cevap olarak satır numarasını verecektir.

excel_kacinci_formulu_kullanimi — Excelde KAÇINCI Formülünün Kullanımı

Yukarıdaki formülün sonucu “93”ün bulunduğu satır numarası “10” dur.

KAÇINCI formülü metin aramalarında büyük-küçük harf duyarlı değildir

Aşağıdaki KAÇINCI formülümüzde eşleştirme türü olarak 1-Küçük kullanılmıştır.Bu yüzden formülde “23” sayısını aramamıza rağmen “23” sayısını bulamadığı için o sayıdan ilk en küçün değer “15” sayısının sıra numarasını veriyor.

excel_kacinci_ornek — Excelde KAÇINCI Formülü Örnekleri

Bu formülün sonucu ise “#YOK” hatasıdır.Çünkü formülümüzün 0-tam eşleme yapmasını istedik.23 sayısını belirttiğimiz hücre aralığında bulamadığı için #YOK hatası vermektedir.

excel_kacinci_formul_ornekleri — Excelde KAÇINCI Formülü Örnekleri

Aşağıdaki ekran görüntüsünde ise KAÇINCI formülümüz de eşleştirme türünü -1 Büyük olarak seçtik.Bu sebeple “23” sayısı listede mevcut olmadığı için “23” den büyük ilk sayı olan “25” sayını cevap olarak verdi.

Ancak dikkat ederseniz aşağıdaki listenin sıralanma şekli diğer ekran görüntülerinden farklı olarak Büyükten küçüğe doğru sıralanmış durumda. -1 Büyük eşleştirme türünü kullanırken formülden doğru sonucu alabilmek için bu şekilde sıralama yaptırmamız gerekmektedir.

Excel Kaçıncı formülünde metin aramaları yaparken formül büyük-küçük harf ayrımı yapmamaktadır.Örnek olarak aşağıdaki ekran görüntüsünde aranan değer büyük olarak yazılmışken listedeki değer Büyük harf ile başlayıp sonra küçük olarak devam etmiştir.

FORMÜL NOTLARI

Kaçıncı formülü aranan değeri değil , aranan değerin konumunu verir
Kaçıncı formülünde aranan değer bulunamaz ise #YOK hatası verir(Eşleştirme türüne ve aranılan hücre aralığı sıralamasına göre değişir)
Kaçıncı formülü kullanırken eşleştirme türü belirtilmez ise varsayılan 1-Küçük olarak arama yapacaktır
Kaçıncı formülü metin aramalarında BÜYÜK-küçük harf duyarlı değildir.

Konuyla ilgili soru ve önerileriniz için yorum kısmını yada iletişim sayfasını kullanabilirsiniz.

Okumaya Devam Et

1 Yorum

Excel Şerit ve Özellikleri (3.Ders)

Yılmaz Temiz
17 Eylül 2016

Merhabalar,

Yeni başlayanlar için Excel derslerinin 1.bölüm 3 dersi Şerit ve özellikleri.

Şerit belirli sekmeler den oluşur ve her sekme kendine özgü buton ve komutları içerir.Şerit özelliği Office 2007 ile kullanılmaya başlanmıştır.

excel_serit_sekme — Excel Şerit Sekmeleri

Giriş Sekmesi

Bir çok kullanıcın en çok kullandığı biçimlendirme ayarlarını içermektedir.

Her sekme içerisinde başlıklarına göre gruplara ayrılmıştır.Örneğin “Giriş” sekmesinde “Pano” ,”Yazı Tipi”,”Hizalama” gibi gruplar bulunmaktadır.

excel_serit_sekme_gruplari — Excel Şerit Sekmelerinin Grupları

Buton yada komutların üzerinde fare ile bir süre beklendiği zaman detaylı bilgi pencereleri görünmektedir.

excel_bilgi_penceresi — Excel Bilgi Penceresi

Sekmelerde ki grupların bir çoğunun sağ alt köşesinde “İletişim Kutusu Başlatıcısı” adında küçük düğmeler mevcuttur.Bu düğmenin üzerinde beklendiğinde işlevi ve klavye kısayolu gibi detayların bulunduğu bir bilgi penceresi açılır ve tıklandığında o grubun detaylı işlev penceresi görüntülenir.

excel_grup_detaylari — Excel Sekme Grupların Detayları

Örnek olarak pano grubunun iletişim kutusunu açtığımız da Excel,Word veya diğer programlardan kopyalanıp hafızada tutulan verileri görebiliriz.

excel_pano_detaylari — Excel Pano Detayları

Yukarıdaki görüntüde “www.excelhocam.com” yazısını kopyala dediğimde onu pano iletişim kutusunun açtığı pano detay penceresinde görünmektedir.Burada birden fazla veri bulunduğu zaman kopyalamak istediğiniz verinin üzerine tıkladığınızda seçili olan hücreye veriyi yapıştıracaktır.Çalıştığınız dosya üzerinde çok fazla kopyala yapıştır işlemi yaptıysanız , panoyu temizlemeniz
hafızada yer açma açısından iyi olacaktır.

Microsoft Office programlarında dosya üzerinde yapılan , üzerinde çalışan nesnelere bağlı olarak sekmeler gösterilir.Bu sekmeler karışıklığı azaltmak adına diğer zamanlarda gizlenir.

Örnek olarak aşağıdaki görüntüde “Ekle” sekmesinde bulunan “Çizimler” grubundan bir şekil ekliyorum sayfama ve gördüğünüz gibi normalde gösterilmeyen “Çizim Araçları” üst sekmesi ve ona bağlı “Biçim” sekmesi görünmektedir.

excel_gizli_sekmeler — Excel Gizli Sekmeler

Daha geniş bir çalışma alanına ihtiyaç duyduğunuz zaman şerit bölümünü gizleyebilirsiniz.Şeriti gizlemek için CTRL+F1 yada simge durumuna küçült butonunun hemen altındaki yukarı yöne olan ok işaretiyle gizleyebilirsiniz.

excel_serit_gizleme — Excel Şerit Gizleme

Şerit kendi isteğinize göre düzenlenebilir bir bölümdür.Sekmelerin sırasını değiştirebilir veya şerit üzerinde sık kullandığınız komutların bulunduğu size özel bir sekme oluşturabilirsiniz.

Bunları yapabilmek için şerit üzerinde boş bir yere sağ tıklayıp “Şeriti Özelleştir” seçeneğini tıklayın.Açılan pencereden yerini değiştirmek istediğiniz sekmeyi sağ sütundan seçip listenin altına yada üstüne doğru kaydırabilir veya seçtikten sonra hemen sağ tarafta bulunan ok işaretleri ile yerlerini değiştirebilirsiniz.Kendi sekmenizi oluşturmak için pencerede sağ sütunun altında bulunan “Yeni Sekme” butonunu tıklayın.

excel_serite_yeni_sekme_ekle — Excel Şerite Yeni Sekme Eklemek

Sekmenizi oluşturduktan sonra sol taraftaki kısımdan istediğiniz komutları oluşturduğunuz sekme içerisine ekleyebilir , sekmenizdeki komutları tamamladıktan sonra “Tamam” düğmesini tıklayarak oluşturduğunuz sekmeyi şerit üzerinde görebilirsiniz.

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

Okumaya Devam Et

Yorum Yok

Formüller İstatistiksel

Excelde KÜÇÜK Formülünün Kullanımı

Yılmaz Temiz
11 Mart 2016

Merhabalar,
Bir sayı kümesi içerisindeki en küçük sayıyı bulmak için KÜÇÜK(small) formülü kullanılmaktadır.KÜÇÜK formülü ile sayı grubu içerisindeki istediğimiz sıradaki en küçük sayıyı (en küçük birinci sayı yada en küçük ikinci sayı gibi) bulabiliriz.

Formülün kullanımı “=KÜÇÜK(Sayıların_bulunduğu_dizi;İstenilen_En_Küçük_Sayının_Sırası)” şeklindedir.

excel_kucuk_formulu_1 — Excelde Küçük Formülünün Kullanımı

Yukarıda ki örnekte olduğu gibi en küçük birinci sayıyı almak için formülde “İstenilen_En_küçük_Sayının_Sırası” kısmına “1” yazıyoruz ve formülümüz bize seçilen hücrelerin arasındaki en küçük birinci sayıyı veriyor.

İkinci sütundaki formülümüz bize ikinci en küçük sayıyı vermektedir.

Seçilen dizide sayıların arasında metin olması bir şeyi değiştirmeyecek formül yalnızca sayıları karşılaştıracak ve istenilen en küçük sayıyı verecektir.

excel_kucuk_formulu_2 — Excelde Küçük Formülünün Kullanımı

“İstenilen_En_Küçük_Sayının_Sırası” kısmına 1’den küçük bir sayı girildiği zaman;
Seçilen dizi boş ise;
“İstenilen_En_Küçük_Sayının_Sırası” kısmına dizideki sayı miktarından daha büyük bir sayı yazıldığı zaman (Dizide 5 sayı var ise ve en küçük 6. sayı istenirse );

Formülümüz “#SAYI!” hatası verecektir.

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

Okumaya Devam Et

Yorum Yok

Makrolar

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

Yılmaz Temiz
7 Kasım 2015

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.

Ö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.