Kurs
Orta Düzey Google Sheets
4 sa
58K
Bazen veri bilimi derin öğrenme, büyük veri veya öneri sistemleriyle ilgili değildir. Özellikle finans gibi bir sektörde çalışıyorsanız ya da geçmişte yoğun şekilde Excel'e güvenen bir şirketteyseniz, büyük olasılıkla sık sık Excel elektronik tablolarıyla uğraşmak zorunda kalacaksınız. Ben de ikinci gruba dahil olduğumu itiraf ediyorum. İşverenim Excel'i yoğun şekilde kullanıyordu ve bu nedenle bazı iş sorumluluklarım için çeşitli yetenekleri hakkında çok şey öğrenmem gerekti.
R ve Python birçok açıdan Excel'e göre bariz avantajlar sunsa da, Excel yine de iş raporları hazırlamak gibi belirli görevler için güçlü bir araçtır. Bu eğitimde ise aynı anda çeşitli Excel tablolarıyla çalışırken büyük ölçüde yardımcı olan, Excel'in yerleşik işlevlerinden birine odaklanacağım.
Sözünü ettiğim işlev VLOOKUP. VLOOKUP, kullanıcının aynı sayfadaki veya aynı çalışma kitabındaki farklı sayfalar arasındaki sütunları ilişkilendirmesine olanak tanır. Özünde, bir sütundan bir değeri arayıp farklı bir sütundan karşılık gelen değeri döndürmenizi sağlar. Bu işlevsellik, sıralanmamış büyük veri tablolarınız varsa ve bunları belirli bir sütuna (tercihen benzersiz bir anahtar içeren) göre ilişkilendirmeniz gerekiyorsa çok kullanışlıdır.
VLOOKUP işlevini aynı sayfadaki sütunlar arasında kullanma sürecinin nasıl işlediğini küçük bir örnek veri tablosuyla göstereyim:
Yukarıdaki ekran görüntüsünde gördüğünüz şey, iş raporlarında yazacağınız türden yaygın bir VLOOKUP ifadesidir. Bileşenleri şunlardır:
- IFERROR işlevi: Anahtar boşsa veya veri tablosunda yoksa hatayı kısa çizgi (-) ile değiştirmek için kullanılır. Bu, bir hata oluştuğunda sayfayı daha temiz gösteren iyi bir hata işleme uygulamasıdır; ancak VLOOKUP'ın çalışması için zorunlu değildir.
- VLOOKUP bağımsız değişkenleri:
- E2, aranacak değerdir (bu durumda örnek olarak KOG kullandık, ancak anahtarlardan herhangi biri olabilir). Bu değer, aramak istediğiniz seçili tablo aralığının en soldaki sütununda yer almalıdır.
- B1:C18, değerlerin aranacağı tablo aralığı/dizisidir. En soldaki sütunun dizini 1'dir. İşlevin çalışması için bu sütunun aynı zamanda aranan değerleri içermesi gerekir.
- 2, değeri almak istediğiniz seçili tablo aralığının sütun dizin numarasıdır. Bu örnekte açıklamayı almak istediğimiz için tablo dizisinin 2. sütununu seçiyoruz.
- FALSE, aranan değerin tam eşleşmesini istediğinizi belirten bir bayraktır. Bu durumda KOG anahtarı için tam eşleşme istediğinizden FALSE'u seçtik. Diğer seçenek TRUE'dur; aranan değerin yaklaşık eşleşmesini arar ve bu bağımsız değişkeni belirtmezseniz varsayılandır. Anahtarlar veya metin dizeleri eşleştirirken genellikle TRUE seçmenizi önermeyiz; tuhaf sonuçlar verebilir.
Şimdi, bu VLOOKUP ifadesini yazdıktan sonra Excel'de ENTER'a basarsanız aşağıdaki sonucu elde edersiniz:
Gördüğünüz gibi VLOOKUP işlevi beklendiği gibi, KOG anahtarıyla ilişkili açıklama olan "King of Gondor" sonucunu döndürdü. Bu, artık E2 hücresine girdiğiniz herhangi bir anahtar için geçerlidir. Örneğin, KIN anahtarını girersek ne olacağını görelim.
Kuzeyin Kralı! Evet, KIN anahtarını girersek, sevgili Jon(Snow) için açıklama bu olduğundan bunu bekleriz.
Ancak, VLOOKUP'ın bir anahtara bağlı tek bir değeri getirmekten daha fazla kullanım alanı vardır. Örneğin, bu tabloyu ikiye böldüğümüzü ve aynı çalışma kitabında farklı iki sayfaya yerleştirdiğimizi varsayalım.
Tabloları Birleştirme
 |
 |
Gördüğünüz gibi artık veriler iki farklı sayfaya (S1 ve S2) bölünmüş durumda ve ikinci sayfadaki (S2) tablodaki anahtarların sırası farklı. Neyse ki, sıra fark etmeksizin anahtar sütunu her iki sayfada da ortak olduğundan, S1'de VLOOKUP kullanarak verileri birleştirebiliriz (bunu tek sütunlu bir left join olarak düşünebilirsiniz). Bunu başarmak için sözdizimi şöyledir:
Bu kez, ilk bağımsız değişken olan aranacak değer B2 olur; çünkü S2'deki tablodaki B2 anahtarını eşleştirmek istiyoruz. Tablo aralığı artık S2'deki, ilk sütunu ortak anahtarları içeren tüm veri tablosu olur. Tablo aralığında dolar işaretleri olduğunu fark edebilirsiniz. Aralığı seçtikten sonra F4'e basarak yapılır ve formülü S1'de aşağı doğru sürüklediğinizde, VLOOKUP işlevinin özgün aralığa bakmaya devam etmesini, aralığın aşağı kaymamasını sağlar. Sütun dizin numarası 2 olarak kalır; çünkü S2'de ikinci sütun açıklamadır ve onu istiyoruz. Son olarak, anahtarlar için tam eşleşme istediğimizden son bağımsız değişken FALSE olmalıdır.
Son adım, formülü tablonun son girdisine kadar aşağı doğru sürüklemektir. S1'de oluşan tablo aşağıdaki gibidir:
Voilá! İşte bu şekilde, VLOOKUP kullanarak verileri yeniden bir araya getirdik. Bu tür tablo birleştirmeleri, iş ortamında çalışırken çok yaygındır ve SQL, R veya Python, üst düzey yöneticilerin alışık olduğu araçlar olmayabilir. Örneğin, aylık bir satış Excel raporu için benzersiz bir fırsat kimliğiyle ilişkilendirilebilen, nitelikli satış fırsatlarına dair farklı ayrıntıları içeren iki dosyanız olabilir (kendi adıma Excel'in en iyi kullanım örneği).
Yaklaşık Eşleşmeleri Kullanma
Şu ana kadar VLOOKUP işlevlerimde son bağımsız değişken olarak TRUE kullanımını (diğer adıyla yaklaşık eşleşme) görmezden geldiğimizi fark etmiş olabilirsiniz. Bunun nedeni, benzersiz anahtarları tam olarak eşleştirmeye odaklanmış olmamızdır. Ancak, sayısal verilerle çalışırken VLOOKUP'ta son bağımsız değişken için TRUE'yu güvenle kullanabilirsiniz.
Örneğin, balık sattığımızı ve müşteriler belirli miktarlarda aldığında aşağıdaki indirimleri sunduğumuzu varsayalım (Neden balık diye soran olursa, basit: Balıklar harikadır!).
Bu kez F2 hücresindeki VLOOKUP işlevi şudur:
Bu formülü yerleştirdikten sonra, indirim tablodaki eşiklere en yakın olana göre belirlenir. Örneğin, 25 balık alırsak indirimimiz %5, 70 balık alırsak %10 ve 120 balık alırsak %15 olur. Bu örneklerin tümü aşağıda görülebilir:
 |
 |
 |
INDEX-MATCH'e Giriş
VLOOKUP, farklı sütunları ilişkilendirmek için güçlü bir işlevdir. Ancak bunu yapmanın alternatif bir yolu daha vardır; INDEX-MATCH birleşimi. VLOOKUP tek bir işlevken, INDEX-MATCH iki işlevin birleşimidir ve yazması biraz daha karmaşık olsa da standart VLOOKUP'a göre birkaç avantajı vardır.
Bu eğitimin amaçları doğrultusunda, yukarıda VLOOKUP ile yaptığımız gibi ortak bir anahtara dayalı olarak iki tabloyu birleştirmek için INDEX-MATCH kullandığımız basit bir örnek üzerinden geçecek ve sözdizimini açıklayacağız (aşağıya bakın).
INDEX-MATCH birleşiminin bileşenleri şunlardır:
- INDEX Bağımsız Değişkenleri:
- M2:M18: Değerlerini almak istediğiniz sütun aralığıdır; bu durumda açıklama sütununu istiyoruz, bu yüzden bu aralığı seçiyoruz. Aralığı seçtikten sonra F4 kullandığımıza ve böylece formülü sürüklediğimizde aralığın kaymamasını sağladığımıza dikkat edin.
- MATCH bağımsız değişkenleri:
- B2: Aramak istediğimiz anahtardır. VLOOKUP'taki aranacak değerin karşılığıdır.
- L2:L18: Aranacak değerin bulunması gereken anahtarları içeren sütun aralığıdır. Burada da F4 kullandığımıza dikkat edin.
- 0: VLOOKUP'taki FALSE ile aynıdır. Tam eşleşme için bir bayraktır.
Sözdizimine bakıldığında, INDEX-MATCH'ın VLOOKUP'a göre başlıca avantajlarından biri hemen fark edilir. INDEX-MATCH ile sütun bağışıklığına sahibiz. Yani, yukarıdaki sağ tablodaki Anahtar ile Açıklama arasına şimdi ek bir sütun eklemeye karar verirsek, formül yine de Açıklama sütununa referans verecek şekilde uyum sağlayacağından fark etmez. Bunu aşağıda görebilirsiniz:
VLOOKUP ile bu, formüle geri dönmeden mümkün değildir; çünkü statik bir sütun referansına sahiptir. Başka bir deyişle, yukarıdaki örnekte başlangıçta Açıklamayı almak için sütun dizin numarası olarak 2 yazmış olurdunuz. Ancak Fish sütunu eklendikten sonra, VLOOKUP işlevi artık tablo aralığında 2. sütun Fish olduğundan Fish sütunundaki değeri döndürürdü. VLOOKUP işlevine elle gidip sütun dizin numarasını yeniden doğru çalışması için 3 olarak değiştirmeniz gerekir.
Bunu gösterdikten sonra, soldaki tablodaki isme açıklamayı INDEX-MATCH kullanarak eklemek, VLOOKUP ile aynı işlemdir. Aşağıda gösterildiği gibi formülü aşağı doğru sürüklememiz yeterlidir:
Aynı çalışma kitabındaki farklı sayfalar arasındaki tabloları birleştirmek de aynı işlemdir. Tek fark, ortak anahtarları içeren sütun aralığını ve ikinci sayfadan değerlerini almak istediğiniz sütun aralığını seçmeniz gerektiğidir.
Sonuç
Umarım bu eğitimden keyif almışsınızdır. VLOOKUP ve INDEX-MATCH, iş ortamında Excel ile çalışan herkes için neredeyse vazgeçilmezdir. INDEX-MATCH'i burada ele alınanın ötesinde daha derinlemesine incelemenizi öneririm; büyük/küçük harfe duyarlı eşleştirme (Evet, varsayılan olarak INDEX-MATCH ve VLOOKUP büyük/küçük harfe duyarsızdır) veya birden çok sütuna dayalı eşleştirme gibi pek çok güzel yeteneği vardır. Öğrenmeye devam edin; sınır gökyüzü!
VLOOKUP hakkında daha fazla bilgi edinmek isterseniz, DataCamp'in Spreadsheets ile Veri Analizi kursunu alın.
