Excel'de Dağınık Veriyi Analize Hazırlama

Veri temizleme neden analizden önce gelir?

Bir raporun güvenilirliği, formüllerinden önce verinin kalitesine bağlıdır. Başka sistemlerden dışa aktarılmış, elle girilmiş ya da birleştirilmiş veri neredeyse her zaman kusurludur: aynı müşteri farklı yazımlarla geçer, tarihler metin olarak saklanır, sayıların sonunda görünmez boşluklar vardır. Bu kusurlar sessizce yanlış toplam, eşleşmeyen arama ve eksik özet üretir.

Veri temizleme, bu kusurları sistemli biçimde bulup düzeltme işidir. Amaç mükemmel veri değil, analizin dayanabileceği tutarlı veridir. Excel bu iş için hem komutlar hem işlevler sunar: Yinelenenleri Kaldır, Metni Sütunlara Dönüştür, Bul ve Değiştir, filtreler, koşullu biçimlendirme ve metin işlevleri. Daha büyük ve tekrar eden işlerde Power Query devreye girer.

Baştan konan bir ilke işi kolaylaştırır: ham veriyi asla doğrudan bozmayın. Temizliğe başlamadan önce ham veri sayfasını kopyalayın ya da yeni bir sütunda temizlenmiş sürümü üretin. Bir şey ters giderse başlangıç noktasına dönmek mümkün olsun.

Önce veriyi tanıyın: sorunları bulmanın yolları

Temizliğe girişmeden önce veriye bir süre bakın. Her sütun için şu sorulara yanıt arayın: Bu sütunda hangi değerler var? Beklenmeyen biçim ya da boş hücre var mı? Aynı şeyi anlatan farklı yazımlar var mı?

Filtre açılır listesi bu iş için ucuz ve hızlıdır. Bir sütunun filtresini açtığınızda benzersiz değerlerin hepsini alfabetik görürsünüz: Ankara, ankara, ANKARA ya da Ankara boşluk gibi varyasyonlar hemen belli olur. Boş ve boşluktan ibaret hücreler de listede ayrı bir giriş olarak çıkar.

Koşullu biçimlendirmede yinelenen değerleri vurgulama kuralı, silmeden önce neyin yinelendiğini görmeye yarar. Microsoft'un yönergesi de silmeden önce bu denetimi önerir.

UZUNLUK işleviyle bir sütunun karakter uzunluğunu yan sütunda hesaplamak, görünmeyen boşlukları ortaya çıkarır: ekranda aynı görünen kodların uzunluğu farklıysa biri fazladan karakter taşıyordur. Sayı gibi görünen hücrelerin sola yaslı durması ise çoğunlukla bunların metin olarak saklandığının işaretidir.

Tekrar eden kayıtlar nasıl silinir?

Yinelenen kayıtları kaldırmak için temizlemek istediğiniz hücre aralığını seçin, Veri sekmesinde Yinelenenleri Kaldır komutunu açın ve yinelemeye bakılacak sütunları işaretleyin. Microsoft'un belgesine göre yalnızca işaretli sütunlara bakılır. Müşteri adı ve e-posta sütunlarını işaretleyip diğerlerini bırakırsanız, bu sütunlardaki değerleri aynı olan satırlar yinelenmiş sayılır ve satırın tamamı silinir.

Burada kritik karar, hangi sütunların kayıt kimliğini oluşturduğudur. Yalnızca ad sütununa bakarsanız aynı adı taşıyan farklı kişileri yanlışlıkla tek kişiye indirebilirsiniz. Kimlik niteliğindeki sütunu, örneğin müşteri numarasını, mutlaka kapsayın.

İşlem veriyi kalıcı olarak siler. Microsoft'un İngilizce belgesi de işlemden önce orijinal aralığı başka bir sayfaya kopyalamayı önerir. Yinelenenleri kaldırmadan önce metindeki fazla boşlukları ve büyük küçük harf farklarını giderin; aksi halde Ankara ile Ankara boşluk farklı kayıt sayılır ve yinelenme tespit edilmez. Bu nedenle sıra önemlidir: önce standartlaştırın, sonra yinelenenleri kaldırın.

Metni sütunlara bölme ve birleştirme

Tek hücrede birden çok bilgi tutulması çok yaygın bir sorundur: Ad Soyad tek sütunda, adres tek hücrede, kod ve açıklama bitişik. Metni Sütunlara Dönüştür sihirbazı bu durumda kullanılır. Bölmek istediğiniz sütunu seçin, Veri sekmesinden Metni Sütunlara Dönüştür'ü açın, Sınırlandırılmış seçeneğini işaretleyin ve ayırıcıyı belirleyin; virgül, boşluk, noktalı virgül ya da başka bir karakter olabilir. Sonuçların yazılacağı hedef hücreyi belirtmeyi unutmayın; aksi halde yanındaki dolu sütunların üzerine yazılır.

Formülle bölme daha esnektir ve kaynak değişince sonuç da güncellenir. SOLDAN, SAĞDAN ve PARÇAAL işlevleri belirli konumdan karakter alır; BUL işlevi ayırıcının konumunu bulur. Yeni sürümlerde METİNBÖL işlevi sınırlayıcıya göre metni doğrudan birden çok hücreye döker. Bunun karşıtı METİNBİRLEŞTİR'dir; sınırlayıcı ekleyerek hücreleri birleştirir.

Ad ve soyadı ayırırken çift isimli ve çok parçalı soyadları gibi istisnalar vardır. Bölme sonrası sonucu mutlaka bir örnek kümesi üzerinde gözle kontrol edin; otomatik bölme her kaydı doğru ayıramaz.

Boşluklar, bozuk karakterler ve metin olarak kayıtlı sayılar

Dış sistemlerden gelen verinin klasik sorunları şunlardır.

Fazla boşluklar: KIRP işlevi metnin başındaki, sonundaki ve kelimeler arasındaki fazla boşlukları temizler. Bir yardımcı sütunda KIRP uygulayıp sonucu değer olarak yapıştırmak, arama formüllerinin neden eşleşmediği sorusunun çoğu zaman çözümüdür.

Yazdırılamayan karakterler: TEMİZ işlevi, metinden yazdırılamayan karakterleri çıkarır. Web'den ya da sistem dışa aktarımından gelen satır sonu ve kontrol karakterleri bu işlevle gider. Belirli bir karakteri değiştirmek için YERİNEKOY işlevi kullanılır; örneğin noktalı virgülleri virgüle çevirmek ya da bölünemez boşluğu normal boşlukla değiştirmek için.

Metin olarak saklanan sayılar: Sola yaslı duran, toplanmayan sayılar metin olarak kayıtlıdır. SAYIYAÇEVİR işlevi metin değerini sayıya dönüştürür. Alternatif olarak hücrenin yanında çıkan hata göstergesinden sayıya çevirme seçeneği kullanılabilir. Tarihler de aynı biçimde metin olarak gelebilir; ayırıcı ve sıra yerel ayara göre değiştiği için ayrıştırdıktan sonra birkaç örneği doğrulayın.

Yazım standardı: BÜYÜKHARF ve YAZIM.DÜZENİ işlevleri metni büyük harfe ya da her sözcüğün baş harfi büyük olacak biçime getirir. Türkçe karakterlerde (i ve ı, İ ve I) sonucu kontrol edin.

Temizlik akışı ve sık yapılan hatalar

Düzenli bir akış şöyle işler: ham veriyi kopyalayın; sütun başlıklarını tek satıra indirin ve her sütunun tek bir veri türü taşımasını sağlayın; boşlukları ve görünmez karakterleri giderin; yazımları standartlaştırın; metin sütunlarını bölün ya da birleştirin; sayı ve tarih türlerini düzeltin; en sonda yinelenenleri kaldırın; eksik değerleri işaretleyin ya da kaynağıyla tamamlayın.

Sık hatalar: • Formül sonucunu değere çevirmeden kaynak sütunu silmek. Yardımcı sütundaki sonuçlar kaynağa bağlıdır; Özel Yapıştır ile yalnızca değerleri yapıştırın. • Birleştirilmiş hücreleri veri alanında bırakmak. Sıralama ve filtre bozulur. • Yinelenenleri her sütun işaretliyken kaldırmak. Satırlar sadece tüm sütunlarda aynıysa silinir; kimlik sütunu temelinde karar vermek gerekir. • Düzeltmeyi elle, tek tek yapmak. Bir kuralı Bul ve Değiştir ya da formülle uygulayın ki yeni gelen veriyle yinelenebilsin. • Yapılan değişiklikleri kayda almamak. Hangi sütunda ne yapıldığını bir not sayfasında tutun.

Aynı kaynak her hafta ya da her ay yeniden geliyorsa tüm bu adımları elle yinelemek verimsizdir. Power Query bu adımları kaydedip yeni veriyle otomatik çalıştırır.

Bu beceriyi nerede geliştirirsiniz?

Veri temizleme, analizle uğraşan herkesin ilk edindiği becerilerden biridir ve metin işlevleri, filtreler ile yinelenen kontrolüne dayanır. Daha derinleşmek için Excel metin fonksiyonları ve Excel Power Query sayfalarına bakabilirsiniz.

Mindset kataloğunda Excel Orta Düzey programının müfredatı Veri Temizleme ve Düzenli Veri Yapısı ile Veri Doğrulama ve Koşullu Biçimlendirme modüllerini içerir. Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı) programı ise Veri Analizi ve İleri Formüller ile Veri Bağlantıları ve Dış Veri Aktarma gibi modüllerle temizlenmiş verinin analizine ve dış kaynaklardan veri çekmeye uzanır.