Yıllarca karmaşık ve karmaşık elektronik tablolarla uğraştıktan sonra, çoğu kişinin manuel olarak gerçekleştirdiği rutin görevleri otomatikleştirerek bana her hafta saatlerce iş tasarrufu sağlayan dört Excel işlevi keşfettim. Bu işlevler, ister profesyonel bir veri analisti olun, ister işini basitleştirmek isteyen sıradan bir kullanıcı olun, düzenli olarak verilerle çalışan herkes için vazgeçilmezdir.
Hızlı Linkler
4. XLOOKUP: Elektronik Tablolarda Gelişmiş Arama
XLOOKUP Microsoft Excel ve Google E-Tablolar gibi elektronik tablo programlarında, geleneksel arama işlevlerinin yeteneklerinin ötesine geçen gelişmiş bir arama işlevidir. VLOOKUP و YATAYARA. Kullanılabilirlik XLOOKUP Daha fazla esneklik, daha verimli veri işleme ve eski işlevlerle ilişkili yaygın hataların azaltılması. XLOOKUP Finansal analistler, veri bilimcileri ve büyük miktarda veriyle çalışan ve belirli bilgileri hızlı ve doğru bir şekilde çıkarması gereken herkes için temel bir araçtır. XLOOKUPSütunların veya satırların konumundan bağımsız olarak, belirli bir aralıktaki bir değeri arayabilir ve başka bir aralıktan karşılık gelen bir değeri döndürebilirsiniz. Ayrıca şunları da destekler: XLOOKUP Sağdan sola ve aşağıdan yukarıya doğru arama yapması diğer fonksiyonlara göre daha çok yönlü olmasını sağlar.
VLOOKUP'a elveda: XLOOKUP mükemmel bir çözümdür
Yıllar önce XLOOKUP'u keşfettiğimde VLOOKUP'u kullanmayı bıraktım. VLOOKUP yalnızca sağa doğru arama yapar ve sütunları taşıdığınızda çökerken, XLOOKUP her yönde çalışır ve esnekliğini korur. XLOOKUP, en iyi seçeneklerden biridir. Zaman kazandırabilecek Excel işlevleri E-tablolarınızda belirli verileri bulun.
Bilgisayar bileşeni fiyatlandırma verilerimde, ürün modellerine göre belirli GPU fiyatlarını bulmam gerekiyor. VLOOKUP ile tüm tabloyu yeniden yapılandırmam gerekirken, XLOOKUP ile tek yapmam gereken şunu yazmak:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP tüm ürün sütununu arar, GPU'mu bulur ve ilgili fiyatı döndürür. Fiyat sütununun nerede olduğu önemli değildir ve daha sonra daha fazla sütun eklesem bile çökmez. Bunu, hiçbir şeyi yeniden biçimlendirmek zorunda kalmadan farklı sayfalardaki ürün bilgilerine başvurmak için her zaman kullanırım.
XLOOKUP'un temel formülü şudur:
=XLOOKUP(aranan_değer, aranan_dizi, dönüş_dizisi)
- aranan_değer: Aradığınız değer.
- arama_dizisi: Değer aradığınız yer.
- dönüş_dizisi: Döndürmek istediğiniz değeri içeren sütun veya satır.
Yani benim durumumda bulmak istediğim değer "GIGABYTE GeForce RTX 3060 12GB Gaming OC" idi. Bu değeri C:C sütununda aramak ve eşleşmenin bulunduğu aynı satırdaki D:D'den karşılık gelen değeri döndürmek istedim.
XLOOKUP'ta hoşuma giden bir diğer şey de, formülün sonuna ",-1" eklediğimde, aşağıdan yukarıya doğru arama yapması ve böylece en son fiyat girişini otomatik olarak bulabilmem. Bu sayede, elektronik tablolarımı her yenilediğimde verileri manuel olarak sıralamak zorunda kalmıyorum.
3. Fonksiyonlarımı kullanma ÇOKETOPLA و COUNTIFS Elektronik tablolarda
Birden fazla standardı profesyonelce ele almak
Temel SUM ve COUNT işlevleri basit görevler için yeterli olsa da, gerçek dünya analizleri söz konusu olduğunda yetersiz kalıyorlar. Fiyatlandırma verilerimi birden fazla koşul altında analiz etmem gerektiğinde, genellikle SUMIFS ve COUNTIFS işlevlerini kullanıyorum. Bunlar, yüzlerce satırı kolayca segmentlere ayırmamı sağlıyor.
Diyelim ki Amazon ABD'de mevcut AMD işlemci sayısını saymak istiyorum. Manuel olarak filtrelemek yerine şunu yazıyorum:
=COUNTIFS(F:F, "Amazon ABD", K:K, "AMD")
Bu, veri setimde Amazon'da listelenen 14 AMD işlemcinin olduğunu hemen gösteriyor. Buradaki güzel şey, ihtiyacım olduğu kadar çok kıyaslama derleyebilmem.
Fiyat analizi için SUMIFS işlevi aynı şekilde çalışır. Şu anda stokta bulunan tüm Intel işlemcilerin toplam değerini hesaplamak için şunu kullanırım:
=SUMIFS(D:D, K:K, "Intel", G:G, "Stokta")
Bu, Markanın “Intel” ve Stok Durumunun “Stokta” olduğu D sütunundaki tüm fiyatları toplar.
SUMIFS işlevinin sözdizimi şöyledir:
=SUMIFS(toplam_aralığı, kriter_aralığı1, kriter1, kriter_aralığı2, kriter2...)
- toplam_aralığı: Toplamak istediğiniz sütun.
- kriter_aralığı1: Koşulları kontrol etmek için ilk sütun.
- kriter1: İlk menzil koşulu.
- kriter_aralığı2, kriter2: Ek şartlar ve koşullar (isteğe bağlı).
COUNTIFS işlevi de benzer şekilde çalışır, ancak değerleri toplamak yerine eşleşen satırları sayar:
=COUNTIFS(kriter_aralığı1, kriter1, kriter_aralığı2, kriter2...)
Hızlı raporlar için SUMIFS ve COUNTIFS kullanmayı tercih ediyorum çünkü yeni verileri anında güncelliyorlar, mevcut formüllerime sorunsuzca uyuyorlar ve ayrı bir pivot tablo oluşturmadan her şeyi aynı hizada tutmama olanak tanıyorlar. Bu araçlar, karmaşık raporlar geliştirirken zamandan ve emekten tasarruf sağlayarak doğru ve verimli veri analizi sağlıyor. SUMIFS ve COUNTIFS gibi işlevleri kullanmak, verilerden hızlı ve kolay bir şekilde değerli bilgiler elde etmek isteyen her veri analisti için olmazsa olmaz bir beceridir.
2. Kırpma ve Temizleme: Görünümü Korumak İçin Temel Adımlar
Elveda veri karmaşası
Hiçbir şey, fazladan boşluklar ve gizli karakterlerle dolu yapılandırılmamış verilerden daha hızlı bir şekilde bir elektronik tabloyu mahvedemez. Form adlarının sonundaki fazladan boşluklar nedeniyle aramalarım sürekli başarısız olduğunda bunu zor yoldan öğrendim.
TRIM işlevi, metnin başındaki ve sonundaki fazladan boşlukları ve kelimeler arasındaki fazladan boşlukları kaldırır. Farklı kaynaklardan veri aktardığımda, ürün adlarında genellikle tutarsız boşluklar oluyor. Her hücreyi manuel olarak temizlemek yerine, bir yardımcı sütun oluşturup şunu kullanıyorum:
=TRIM(C2)
Daha sonra fare işaretçisini hücrenin kenarına artı işaretine (+) dönüşene kadar hareket ettiriyorum ve ardından TRIM fonksiyonunun çalışmasını istediğim tüm satırlara doğru sürüklüyorum.
1. TEXTBEFORE ve TEXTAFTER: Ayrıntılı açıklama ve önemleri
Gerekli verileri doğru bir şekilde çıkarın
TEXTBEFORE ve TEXTAFTER işlevleri, karmaşık elektronik tabloları temizlemek için en sevdiğim Excel işlevleri arasındadır. Excel'in modern metin işlevleri, yapılandırılmamış metin dizelerinden belirli bilgileri çıkarmada mükemmeldir. Örneğin, fiyatlar sütunumda "177.52 ABD doları", "178.33 ABD doları", "9055 ₱" ve "9645.50 PHP" gibi girişler birbirine karışmıştı.
TEXTBEFORE fonksiyonu belirtilen bir ayraçtan önceki her şeyi çıkarır:
=TEXTBEFORE(D2, "USD")
Bu şekilde fonksiyon “178.33 USD” değerinden “178.33” değerini anında çıkardı.
TEXTAFTER fonksiyonu ise ters yönde çalışarak ayırıcıdan sonraki her şeyi çıkarır:
=TEXTAFTER(C2, "AMD ")
Bu şekilde “AMD Ryzen 5 5700X 8-Core AM4 Processor” içinden “Ryzen 5 5700X 8-Core AM4 Processor” fonksiyonunu çıkardım.
Karmaşık çıkarımlar için her iki işlevi de birleştiriyorum. 177.52 ABD doları tutarındaki sayısal fiyatı elde etmek için:
=TEXTBEFORE(TEXTAFTER(D8, "$"), "USD")
TEXTBEFORE ve TEXTAFTER fonksiyonlarının genel sözdizimi şöyledir:
=TEXTBEFORE(metin, ayırıcı) ve =TEXTAFTER(metin, ayırıcı)
Bu iki fonksiyonun sağladığı büyük gelişme, hassasiyetlerinde yatıyor. Karmaşık MID, FIND ve LEN fonksiyonlarının kombinasyonlarını kullanmak yerine, basit ve okunması kolay formüller kullanarak temiz sonuçlar elde edebiliyorum. Bu fonksiyonları, model numaralarını ayırmak, ürün özelliklerini çıkarmak ve eskiden saatlerce manuel düzenleme gerektiren içe aktarılan metinlerden temiz veriler çıkarmak için sıklıkla kullanıyorum.
Bu dört işlev, esnek aramalar kullanarak veri bulma, birden fazla ölçüte göre analiz etme, dağınık içe aktarılan metinleri temizleme ve karmaşık metin dizelerinden belirli bilgileri çıkarma gibi Excel'deki en büyük zaman kayıplarından bazılarını ele alır. Çoğu kişi bu görevleri manuel olarak gerçekleştirir ve doğru formülleri uygulamak normalde yalnızca birkaç dakika sürecek işlere saatler harcar.
Bu işlevleri, bileşen fiyatlandırma analizinden envanter yönetimi raporlarına kadar her şey için kullandınız. Sektörünüz ne olursa olsun çalışırlar, çünkü dağınık veriler ve karmaşık arama gereksinimleri evrensel sorunlardır. Bu işlevlerde ustalaştığınızda, onlarsız elektronik tabloları nasıl yönettiğinize şaşıracaksınız.
