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