Orta Ders 5: Metin Fonksiyonlarıyla Veri Temizleme

Veri neden kirli gelir?

Başka bir yazılımdan, web formundan ya da e-postadan kopyalanan veriler çoğu zaman gözle görülmeyen farklar taşır. Ad soyadın önünde iki boşluk, telefon numarasında araya serpiştirilmiş boşluklar, aynı müşterinin bir satırda küçük, diğerinde büyük harfle yazılması bunların tipik örnekleridir.

Bu farklar yalnız görüntüyü bozmaz. Önceki derslerde gördüğünüz ETOPLA, DÜŞEYARA ve KAÇINCI, "Ayşe Kaya" ile "Ayşe Kaya" metinlerini farklı değer sayar. Temizlik yapılmadan kurulan özet tabloları eksik toplar ve aramalar #YOK verir.

KIRP ile boşlukları silmek

KIRP, metnin başındaki ve sonundaki boşlukları siler, kelimeler arasındaki birden çok boşluğu da teke indirir. Tek argüman alır: =KIRP(metin).

=KIRP(A2)

=TRIM(A2)

" Ayşe Kaya " metni 14 karakterdir, temizlendiğinde "Ayşe Kaya" ile 9 karaktere iner. Farkı görmek için temizlik öncesi ve sonrası =UZUNLUK(A2) ile karakter sayısına bakabilirsiniz. Web sayfalarından gelen bölünemez boşluk karakteri KIRP ile silinmez. Bu durumda önce YERİNEKOY(A2;DAMGA(160);" ") ile onu normal boşluğa çevirmeniz gerekir.

Harf düzenini toparlamak

YAZIM.DÜZENİ her kelimenin ilk harfini büyük, kalanını küçük yapar. "zeynep arslan" ya da "ZEYNEP ARSLAN" metni "Zeynep Arslan" olur. BÜYÜKHARF ve KÜÇÜKHARF ise metnin tamamını tek düzene çevirir; ürün kodu ve plaka gibi alanlarda BÜYÜKHARF işe yarar.

Türkçe metinlerde i, ı, İ ve I harflerine ayrıca dikkat edin. Büyük küçük harf dönüşümünün sonucu kullandığınız programa ve dil ayarına göre değişebilir. Dönüştürdüğünüz sütunda bu harfleri içeren birkaç satırı gözle kontrol etmek en güvenli yoldur.

Kodu parçalara ayırmak

BUL ve PARÇAAL birlikte kullanıldığında bir metni belirli bir ayraçtan bölebilirsiniz. BUL, ayracın kaçıncı karakterde olduğunu döndürür. PARÇAAL ise o konumdan sonraki karakterleri alır.

=SAYIYAÇEVİR(PARÇAAL(A2;BUL("-";A2)+1;UZUNLUK(A2)))

=VALUE(MID(A2,FIND("-",A2)+1,LEN(A2)))

"KTP-1024" kodunda tire 4. karakterdir. PARÇAAL 5. karakterden başlayıp metnin uzunluğu kadar karakter alır, böylece kaç hane olursa olsun tirenin sağındaki bütün kısım gelir. SAYIYAÇEVİR de "1024" metnini hesaplanabilir sayıya çevirir. Tirenin solundaki kısım için =SOLDAN(A2;BUL("-";A2)-1) yazarsınız ve "KTP" sonucunu alırsınız.

İstenmeyen karakterleri kaldırmak

YERİNEKOY, metindeki belirli bir karakteri ya da kelimeyi başka bir şeyle değiştirir. Değiştirilecek metni boş tırnak ("") yaparsanız o karakter tamamen silinir.

=YERİNEKOY(A2;" ";"")

=SUBSTITUTE(A2," ","")

"0532 418 77 90" numarasındaki bütün boşluklar kalkar ve 11 haneli "05324187790" kalır. Aynı yöntemle parantezleri, tireleri ya da TL ifadesini de temizleyebilirsiniz. Birden çok karakter için YERİNEKOY'ları iç içe yazarsınız: =YERİNEKOY(YERİNEKOY(A2;"(";"");")";"").

Formülden değere geçmek

Temizlik formülleri yardımcı bir sütunda çalışır ve kaynak sütuna bağlıdır. Kaynak sütunu silerseniz temiz sütun da #BAŞV! hatasına döner. Bu yüzden iş bitince temiz sütunu kopyalayıp Yapıştırma Seçenekleri'nden Değerler'i seçerek sabit metne çevirin.

Bundan sonra eski sütunu silebilir ya da arşiv sayfasına taşıyabilirsiniz. Her ay aynı formatta gelen bir dosyayı temizliyorsanız formülleri bir şablonda tutmak, değere dönüştürmeyi ise yalnız rapor çıkarırken yapmak zamandan kazandırır.

İki müşteri listesini birleştirmek

İki farklı kaynaktan gelen müşteri listesini birleştirdiğinizi düşünün. Birinde adlar "AYŞE KAYA", diğerinde "ayşe kaya " biçiminde, telefonlar da bir yerde boşluklu, bir yerde bitişik yazılmış. Bu haliyle yinelenen kayıtları bulmak mümkün değildir, çünkü Excel bu satırları farklı müşteriler olarak görür.

Önce iki listeyi alt alta tek bir sayfaya yapıştırırsınız. Ardından yardımcı sütunlarda adı =YAZIM.DÜZENİ(KIRP(A2)), telefonu =YERİNEKOY(B2;" ";"") ile temizlersiniz. Temiz sütunlar hazır olduğunda Veri sekmesindeki Yinelenenleri Kaldır komutu, artık aynı görünen kayıtları tek satıra indirebilir.

Bu sırayı korumak önemlidir. Yinelenenleri temizlik yapmadan kaldırmaya çalışırsanız yalnız birebir aynı yazılmış satırlar silinir, küçük yazım farkı olanlar listede kalır.

Temizlik formüllerinin sonucunu doğrulamanın kısa bir yolu da sayım karşılaştırmasıdır. Temizlikten önce ve sonra =BAĞ_DEĞ_DOLU_SAY(A2:A500) ile dolu hücre sayısını alın; Yinelenenleri Kaldır'dan sonra kaç kaydın silindiğini bu iki sayının farkı gösterir. Silinen kayıt sayısı beklediğinizden çok fazlaysa temizlik formülü farklı müşterileri aynı metne çevirmiş olabilir, birkaç satırı gözle kontrol edin.

Sıradaki adım

Alıştırmada dört müşteri adını, fazla boşluklarını silip her kelimesi büyük harfle başlayacak biçimde tek bir formülle temizleyeceksiniz. KIRP ve YAZIM.DÜZENİ'nin iç içe hangi sırayla yazılacağını önce kendiniz deneyin.