28.10.2017

Excel HLOOKUP Fonksiyonu ve Örnekleri

Excel HLOOKUP (DİKEYARA) Fonksiyonu ve Örnekleri

Dünkü makalemde VLOOKUP (DÜŞEYARA) fonksiyonunu detaylı olarak izah etmeye çalışmış ve pratikte fayda sağlayabilecek bir de örnek ile çözümlerini hazırlayarak paylaşmıştım. HLOOKUP fonksiyonu da çok benzer bir çalışma mantığı ile çalıştığı için göz atmanızda fayda olacağını düşünüyorum.

HLOOKUP Nedir ve Ne işe Yarar?

Excel bir veritabanı programı değildir ancak verileri çok düzenli olarak saklayabileceğiniz, çağırabileceğiniz ve üzerinde çeşitli matematiksel, tarihsel, istatistiksel ya da metinsel düzenlemeler yaparak bir çok işletme bazlı ya da kişisel probleminizi çözebileceğiniz çok pratik bir program. Bunu her defasında sıkılmadan vurgulamaya çalışıyorum çünkü sadece excel çalışma mantığına ve fonksiyonlarına yeterince aşina olursanız, tek satır kod yazmadan hemen her hesabınızı excel ortamında güncel olarak tutabilir ve aradığınız çözümlere ulaşabilirsiniz.

HLOOKUP da VLOOKUP gibi bir referans değer bulma fonksiyonudur. Örnekler her zaman daha açıklayıcı olur ancak kabaca tanımlamaya çalışırsak:

Tanım: HLOOKUP, yukarıdan aşağı işleme mantığı ile çalışan ve önceden hazırlanmış bir tablodan en üstteki değeri referans alarak dilediğimiz satırı (ve bu satırdaki değeri) çağırabilmemizi sağlayan bir Excel fonksiyonudur.

1990’lı yılları görmüş olanlar için aşağıdaki örnek ilgi çekici olabilir.Sıkıcı örneklere her zaman karşı oldum ve bu sebeple elimden geldiğince yaratıcı olmaya çalışıyorum.

Örnek: Arabamız için 1990’lı yılların pop şarkılarından bir playlist oluşturmak istenmektedir ve bunun için Excel programının kullanımı tercih edilmiştir. Ancak usb belleğimizde sadece 3 şarkılık yer vardır. Bilgisayarda bulunan tüm şarkılar çok sevildiği için seçim yapılamamaktadır ve tüm şarkılar içerisinden 3, 7 ve 9 numaralı şarkılar hangileri ise usb belleğe de bu şarkılar aktarılacaktır.

Bilgisayardaki şarkılar:

Excel HLOOKUP tablosu

Soru 1: Soruda belirtilen 3, 7 ve 9 numaralı (ID) şarkıları bulalım.

Cevap: HLOOKUP fonksiyonu, yukarıdan aşağı ilişkisel değerleri bulmak amacını taşımaktadır ve bu sorunun çözümü için en uygun araçtır.

HLOOKUP arayüzünü inceleyecek olursak:

Excel HLOOKUP fonksiyonu

Lookup_value: Tablo içerisinde arıyor olduğumuz değer.

Table_array: İçerisinde arama işlemini gerçekleştireceğimiz tablo

Row_index_num: Tablo içerisinde bulmak istediğimiz değerin sırası

Range_lookup: Tam olarak aradığımız değerin mi yoksa en yakın sonucun mu istendiği

Örneğin 3 numaralı şarkıyı bulmak için fonksiyonumuzu yazarsak:

=HLOOKUP(3;$B$1:$K$3;3;0)

Açıklama: 3 değerinin, B1:K3 arasındaki tablodaki 3. sıradaki karşılığını tam olarak bul.

Burada sadece 3 değeri için hesaplama yapmak istediğimiz için 3 değerini bir referans hücreden almadık. Ancak örneğimizdeki gibi birden fazla değer için HLOOKUP fonksiyonunu kullanmak için, Lookup_value değerlerini de bir hücre referansı alarak yapmamız gerekmektedir ki bir kere yazdığımız fonksiyonu çekip uzatarak diğer değerler için de kullanabilelim.

Excel HLOOKUP problemi

Soru 2: Bu örnek için VLOOKUP kullanılamaz mıydı?

Cevap: hayır, bu tablo yapısı için çözülemezdi çünkü aradığımız değerler en üstte ve referans değerleri de aynı sütunda. Aradaki farkı ve ne zaman hangi fonksiyonu kullanacağımızı ayırdedilebilmesi için ayrı bir başlık açalım.

Soru 3: 1. Soruda tabloyu sabitlemesek ($ işaretleri) olmaz mıydı?

Cevap: Hayır olmazdı, çünkü biz formülü B6 hücresi içinde yazarak aşağı doğru uzattık ve aynı formülün Lookup_value değerlerinin A7 ve A8 olan versiyonları için de aynı referans tablosunu kullandık. Excel formüllerinde ne zaman sabitleme işaretinin kullanılıp ne zaman kullanılmasına gerek olmadığına ilişkin bilgiler ve örnekler için Excel’de referans hücrelerin sabitlenmesi ile ilgili makalesini inceleyebilirsiniz.

VLOOKUP ile HLOOKUP Arasındaki Fark Nedir?

Hem VLOOKUP hem de HLOOKUP fonksiyonları, aradığımız değerlerin karşılıklarını tablodan çekebilmemizi sağlamak amacını taşımaktadırlar. Ancak tablonun yapısı, hangi fonksiyonu kullanmamız gerektiğini belirlemektedir. Örneğin yukarıdaki örnekteki soru, belki de daha da alışkın olduğumuz şekliyle aşağıdaki gibi de olabilirdi (soldan sağa). Bu durumda ise seçmemiz gereken fonksiyon VLOOKUP olacaktı.

Excel HLOOKUP VLOOKUP karşılaştırması

Özet: Eğer tabloda karşılığını aradığımız değer aşağıdaysa (tablo 1) HLOOKUP, eğer sağında ise (tablo 2) VLOOKUP fonksiyonları kullanılmalıdır.

Excel HLOOKUP Örnek Çalışma

İlk örneğimizden biraz daha karmaşık ve gerçek hayatta karşılaşılması daha yüksek ihtimal olan bir senaryo ile iç içe HLOOKUP fonksiyonlarının kullanımını ele alalım.

Örnek: A üniversitesinde öğrencilerin başarılarının ölçümü için 3 adet sınav yapılmaktadır. Bu sınavlar ve ağırlıkları:

1. sınav: %20

2. sınav: %20

3. sınav: %60

100 üzerinden puanlaması yapılan bu sınavların sonucunda dönem sonu ağırlıklı puanları için de karşılık gelen harf notu değerleri aşağıdaki gibidir:

90-100 arası: A

70-90 arası: B

55-70 arası: C

0-55 arası: Kaldı

3 öğrenci için 0 ile 100 arasu rasgele sınav notları üreterek dönem sonunda bu öğrencilerin aldıkları harf notlarını Excel kullanarak bulunuz.

İpucu: Rasgele değerlerin üretimi konusunda bilgi sahibi değilseniz, Excel’de rasgele veri üretimi makalesinden faydalanabilirsiniz.

Çözüm: Bu sorunun çözümünde HLOOKUP ya da VLOOKUP’ın kullanılması, tamamen tablonun hazırlanış biçimine bağlıdır. Ancak HLOOKUP ve VLOOKUP birlikte örneklenmiş olursa daha anlaşılır olacağı düşünülürse, cevapsız soru excel sheet’imiz aşağıdaki gibi olabilir:

Excel HLOOKUP örnek

1. Adım: İlk olarak öğrencilerin sınav notları için 0 ile 100 arası rasgele sayılar üretelim. İpucu içerisinde de bahsettiğimiz gibi, bunun en kolay yolu RANDBETWEEN(0;100) fonksiyonu olacaktır.

Excel HLOOKUP örnek soru

2. Adım: Öğrencilerin harf notunu bulabilmek için, dönem sonu ağırlıklı puanlara ihtiyacımız olacak. Ağırlıklı puanları bulmak için ise 1., 2. ve 3. sınav notlarını HLOOKUP için almamız gerekiyor.

Örneğin Mustafa’nın ağırlıklı not ortalaması aşağıdaki gibi olacaktır:

=HLOOKUP($B7;$B$1:$D$4;2;FALSE)*0,2+HLOOKUP($B7;$B$1:$D$4;3;FALSE)*0,2+HLOOKUP($B7;$B$1:$D$4;4;FALSE)*0,6

Excel HLOOKUP örnekleri

3. Adım: Öğrencilerin ağırlıklandırılmış notlarını bulduğumuza göre artık tek yapmamız gereken karşılık gelen harf notunu bulmak. Ama burada bir sorunumuz var, biz HLOOKUP ile sürekli bir referans değeri başka bir tablodan eşleştirerek işlem yapıyorduk. Ancak bu sefer elimizdeki not 44 iken harf notu tablosunda böyle bir değer yok. Ne yapacağız?

Hem VLOOKUP hem de HLOOKUP fonksiyonlarındaki son parametre olan Range_lookup kullanımı tam da bu amaçlar için hazırlanmış. Eğer tabloda aradığımız değere tam karşılık gelen bir değer yoksa en yakınını bulabiliriz. Ancak bunun için kullanacağımız tablonun artan sırada hazırlanmış olması gerekiyor.

Bakalım 44,2 notu için karşılık gelen harf notu gerçekten de kaldı olacak mı?

Excel HLOOKUP formülü

Excel ne güzel bir araçlar bütünü değil mi :) Son minik problemimizi de çözdüğümüze göre artık cevabımızı tamamlayalım.

Excel HLOOKUP çözümü

Excel HLOOKUP Fonksiyonu ve Örnekleri
Bu makalenin telif hakkı ve tüm sorumlulukları yazara ait olup, şikayetler için lütfen bizimle iletişime geçiniz.
URL:
Etiketler:

Bu makale 6310 kez okundu

28.10.2017 tarihinde yazıldı
Reitix

Yorumlar

  • hburkek
    26.05.2019

    ister vlookup olsun isterse de hlookup olsun her ikisi de aynı mantıkta fonksiyonlardır, kullanım olarak ise bir tablonun başlıkları hangi yana doğru fazla uzuyor ve tablonun okunmasını zorlaştırıyorsa o alana göre ekrana sığdırılması önemlidir, bundan sonra yatay ya da dikey olmasına göre hlookup da kullanılabilir vlookup da ikisi de aynı

  • bugra08250
    25.05.2019

    tablo yapıları hep dikey olarak hazırlandığı için hlookup fonksiyonu pek kullanılmaz, belki de bilinmez bile. kullanım mantığı açısından vlookup ile aynı olduğu için lazım olduğunda kullanabileceğinizi bilmek güzel ama belki tablonun transpozunu alır yine de vlookup kullanabilirim ters gelmesin diye

  • kuandık
    02.10.2018

    tabloyu sabitlememe hatasına düşerseniz yapacağınız bütün hesaplamalar hatalı olabiliyor, bu yüzden aşama aşama dikkatli olmak ve ister hlookup ister vlookup fonksiyonu olsun, çekeceğiniz verinin yaptığınız hesaplardaki önemini iyi anlamanız gerekiyor

  • Ahmet.Güler
    27.08.2018

    tablolama sistemimizin yapısal alışkanlığından dolayı hep aradıklarımızı vlookup ile bulduğumuzdan hlookup'a hiç gerek yokmuş gibi gelir, ama özellikle internetten bulacağınız ters olarak dizilmiş tablolarda veri çekerken de hlookup kullanmak gerekiyor, aynı mantık ve aynı işlevsellik

Bu yazıya siz de yorum yapabilirsiniz

İnternet sitemizdeki deneyiminizi iyileştirmek için çerezler kullanıyoruz. Bu siteye giriş yaparak çerez kullanımını kabul etmiş sayılıyorsunuz. Daha fazla bilgi.