Veriyi Toplayıp Temizleyerek Görsel Analize Hazırlama

Veriyi toplayıp temizleyerek görsel analize hazırlama (data cleaning), ham verideki fazla boşlukları, metin olarak saklanan sayıları, eksik ve tekrarlı kayıtları düzelterek tabloyu grafik ve özet analiz için kullanılabilir hale getirme işlemidir. Excel'de bu işi KIRP ve SAYIYAÇEVİR gibi işlevler, Yinelenenleri Kaldır komutu ve Power Query ile yaparsınız. Temiz ve düzenli bir tablo, grafiklerin ve özet tabloların doğru sonuç vermesini sağlar.

Veri temizleme neden grafikten önce yapılır?​

Grafik ve özet tablo, hücrede ne görüyorsa onu hesaplar. Sayı gibi görünen ama metin olarak saklanan bir değer toplamaya girmez, sonunda boşluk bulunan bir şehir adı ise aynı şehrin ikinci bir kategorisi olarak görünür. Bu yüzden analizden önce verinin tutarlı hale getirilmesi gerekir.

Veri hazırlığı üç adımda ilerler:

• Toplama: Farklı dosyalardan, sistemlerden ya da metin çıktılarından gelen veriyi tek tabloda birleştirmek. Çok sayıda kaynak varsa Veri > Veri Al (Data > Get Data) ile Power Query kullanmak elle kopyalamaktan daha güvenlidir.
• Temizleme: Fazla boşlukları, yazım farklarını, metin biçimindeki sayıları, eksik ve tekrarlı kayıtları düzeltmek.
• Düzenleme: Her sütunda tek tür veri, ilk satırda başlık ve tabloda boş satır ya da sütun olmayacak şekilde yapıyı sadeleştirmek.

Ham veriyi ayrı bir sayfada koruyun, temizlenmiş halini yeni sayfada ya da yan sütunlarda üretin. Böylece hata olursa başa dönebilirsiniz.

Excel'de veri temizleme yöntemleri​

Sık karşılaşılan sorunlar ve çözümleri şunlardır:

• Fazla boşluklar: KIRP işlevi baştaki, sondaki ve sözcükler arasındaki fazla boşlukları siler. Yazdırılamayan karakterler için TEMİZ işlevini birlikte kullanabilirsiniz.
• Metin olarak saklanan sayılar: SAYIYAÇEVİR işlevi ya da Veri > Veri Araçları > Metni Sütunlara Dönüştür (Data > Data Tools > Text to Columns) komutu metni gerçek sayıya çevirir.
• Tekrarlı kayıtlar: Veri > Veri Araçları > Yinelenenleri Kaldır (Data > Data Tools > Remove Duplicates) komutu ya da EĞERSAY ile kayıtların kaç kez geçtiğini sayarak bulabilirsiniz.
• Eksik veriler: Giriş > Bul ve Seç > Özel Git (Home > Find & Select > Go To Special) ile boş hücreleri seçebilir, EĞER işleviyle eksik satırları işaretleyebilirsiniz.
• Tutarsız yazımlar: Giriş > Bul ve Seç > Değiştir (Home > Find & Select > Replace) ile aynı anlama gelen farklı yazımları tek biçime getirin.

Bir hücredeki boşlukları ve yazdırılamayan karakterleri tek adımda temizlemek için işlevleri iç içe kullanabilirsiniz:
Sonuç formülle üretildiği için ham veriniz değişmez. Temiz sütunu kalıcı hale getirmek isterseniz kopyalayıp Özel Yapıştır > Değerler (Paste Special > Values) ile yapıştırın.

Temiz veriyi görsel analize hazırlama​

Veri temizlendikten sonra grafiğe uygun bir yapıya getirilmelidir. Tabloyu seçip Giriş > Tablo Olarak Biçimlendir (Home > Format as Table) komutunu uygularsanız yeni satırlar grafiğe ve özet tabloya otomatik dahil olur. Grafik için kategorileri toplayan küçük bir özet tablo hazırlamak da yaygın bir yöntemdir; bunu ÇOKETOPLA gibi işlevlerle ya da Ekle > Özet Tablo (Insert > PivotTable) ile yapabilirsiniz.

Grafik türünü verinin amacına göre seçin:

• Çizgi grafiği: Zaman içindeki değişimi ve eğilimi gösterir.
• Sütun ve çubuk grafiği: Kategorileri karşılaştırır.
• Pasta grafiği: Az sayıda kategorinin toplam içindeki payını gösterir.
• Dağılım grafiği: İki sayısal değer arasındaki ilişkiyi gösterir.
• Isı haritası: Ayrı bir grafik türü olmayıp Giriş > Koşullu Biçimlendirme > Renk Ölçekleri (Home > Conditional Formatting > Color Scales) ile hücreleri renklendirerek elde edilir.

Kategori seçimi konusunda emin değilseniz Ekle > Önerilen Grafikler (Insert > Recommended Charts) size verinize uygun seçenekleri sunar.

1. Fazla boşlukları ve metin sayıları temizleme​

Aşağıdaki tabloda ürün adlarında baş, son ve ara boşluklar, adet değerlerinde ise metin olarak saklanan sayılar bulunuyor. Temiz Ürün sütunu KIRP işleviyle, Temiz Adet sütunu SAYIYAÇEVİR işleviyle üretilir. Fazla Boşluk sütunu, ham ve temiz metnin uzunluk farkını UZUNLUK işleviyle hesaplar.
C2 hücresi temizlenmiş ürün adı olarak Klavye sonucunu verir, D2 hücresi ise metin olan adet değerini gerçek sayıya çevirerek 120 değerini üretir. E2 hücresindeki 3 değeri, ilk üründe silinen boşluk sayısını gösterir. Temiz sütunlar hazır olduktan sonra analizde ham sütunlar yerine bunları kullanın.

2. Eksik verileri işaretleme ve tamamlama​

Aylık satış listesinde Mart ve Haziran değerleri girilmemiş. Durum sütunu EĞER işleviyle boş hücreleri işaretler. Tamamlanmış Satış sütunu ise eksik aylar için dolu ayların ortalamasını YUVARLA ve ORTALAMA işlevleriyle hesaplar; ORTALAMA boş hücreleri dikkate almaz.
Mart satırında Durum sütunu Eksik sonucunu verir ve Tamamlanmış Satış sütunu bu ay için 14.875,00 değerini hesaplar. Dolu aylarda ise orijinal değer aynen korunur. Ortalamayla doldurma her veri için uygun değildir; bu yöntemi yalnızca eksik değerin kabul edilebilir bir tahminle yerine konabileceği durumlarda kullanın.

3. Grafiğe hazır bölge özeti oluşturma​

Temizlenmiş satış kayıtlarından grafik için küçük bir özet tablo hazırlanıyor. D sütununda bölge adları, E sütununda ÇOKETOPLA işleviyle bölge toplamları, F sütununda ise her bölgenin toplam içindeki payı yer alır. Bu özet tabloyu seçerek sütun ya da pasta grafiği ekleyebilirsiniz.
Marmara için toplam 59.250,00 olarak hesaplanır ve bu bölgenin payı %51 olur. Özet tabloyu seçip Ekle > Önerilen Grafikler (Insert > Recommended Charts) komutunu kullanarak grafik ekleyebilirsiniz. Üç kategori olduğu için pasta grafiği de okunabilir kalır; kategori sayısı artarsa sütun grafiği tercih edin.

Sık sorulan sorular​

Sayı gibi görünen hücreler neden toplanmıyor? Değer büyük olasılıkla metin olarak saklanıyordur. SAYIYAÇEVİR işlevini ya da Metni Sütunlara Dönüştür komutunu kullanarak gerçek sayıya çevirin.

Yinelenenleri Kaldır komutu veriyi kalıcı olarak siler mi? Evet, tekrarlı satırlar tablodan silinir. Komutu uygulamadan önce verinin bir kopyasını alın.

Eksik değerleri her zaman ortalamayla doldurmalı mıyım? Hayır. Doldurma yöntemi verinin anlamına bağlıdır; bazı durumlarda eksik satırı analiz dışında bırakmak daha doğru olur.

Ham veriyi saklamalı mıyım? Evet. Temizlenmiş veriyi ayrı sütunda ya da sayfada üretmek, hatalı bir düzeltmeyi geri almanızı sağlar.

Kullanırken dikkat edin​

• Ham veriyi üzerine yazmayın: Temizlik işlemlerini ayrı sütunda ya da ayrı sayfada yapın. Yinelenenleri Kaldır ve Bul ve Değiştir gibi komutlar veriyi kalıcı olarak değiştirir; bu komutlardan önce dosyanın bir kopyasını alın.
• Bölünemez boşluğa dikkat edin: Web sayfalarından ya da dış sistemlerden gelen verilerde KIRP işlevinin silmediği bölünemez boşluk karakteri bulunabilir. Bu durumda YERİNEKOY işleviyle KARAKTER(160) ifadesini normal boşlukla değiştirip sonra KIRP uygulayın.
• Boş hücre ile sıfır aynı şey değildir: Ortalama gibi hesaplar boş hücreleri dikkate almaz, sıfır değerini ise hesaba katar. Eksik veriyi sıfır yazarak doldurmak ortalamayı ve grafiği yanıltabilir.
• Grafik için tek tür veri kullanın: Bir sütunda hem sayı hem metin bulunursa grafik ve özet tablo beklenmedik sonuçlar verir. Başlık satırının her sütunda dolu ve benzersiz olduğundan, tabloda birleştirilmiş hücre bulunmadığından emin olun.
• İlgili konular:
  • İşlevler: KIRP, TEMİZ, SAYIYAÇEVİR, UZUNLUK, YERİNEKOY, EĞER, EĞERSAY, ÇOKETOPLA, ORTALAMA, YUVARLA
  • Özellikler: Yinelenenleri Kaldır, Metni Sütunlara Dönüştür, Özel Git, Koşullu Biçimlendirme, Tablo Olarak Biçimlendir, Özet Tablo, Power Query

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.