Excel'de veri temizleme (data cleansing)

Veri temizleme (data cleansing)

Microsoft Excel ® ile yaptığımız işlemlerinden önemli bir kısmını veri temizleme oluşturuyor. Veri temizleme ERP ya da başka veri kaynaklarından aldığımız verilerin temizlenmesi.

Bu işlemler; boşlukların kaldırılması, araya ekleme yapılması, tarihlerin düzenlenmesi, sayısal verilerin düzenlenmesi, verilerin farklı şekillerde biçimlenmesi (formatlanması), eşleştirme yapılması, birleştirilmesi, ayrılması, çiftlerinin kaldırılması vs. Bu işlemler için çok sayıda fonksiyon ve araca sahip.

VİDEO:

https://youtu.be/8VrQmz8s0O4

Veri Temizlemek İçin Kullanılan Fonksiyonlar / Araçlar

Türkçe (İngilizce) Sürümler için:

1) Temizleme: KIRP (TRIM), TEMİZ (CLEAN), YERİNEKOY (SUBSTITUTE)

2) Çiftleri Kaldırma

3) Değiştirme: BUL (FIND), BULM (SEARCH), DEĞİŞTİR (REPLACE), YERİNEKOY (SUBSTITUE),

4) Ayırma: SOLDAN (LEFT), SAĞDAN (RIGHT), PARÇAAL (MID), UZUNLUK (LEN)

5) Büyük küçük harf değiştirme

6) Düzenleme: METNEÇEVİR (TEXT), SAYIDÜZENLE (FIXED), SAYIDEĞERİ (VALUE)

7) Birleştirme: BİRLEŞTİR (CONCATENATE)

8) TRANSPOSE / TABLOYU ÇEVİRME

9) EŞLEŞTİRME - DÜŞEYARA (VLOOKUP), KAÇINCI (MATCH), İNDİS (NDEX)

10) Metni Sütuna Çevirme (Text to columns)

11) Tarih Düzenlemeleri: Ayır, birleştir, parçala

12) CTRL+H

Excelde işgünün bulmak ? Ayın, haftanın ilk işgünü bulmak.

https://youtu.be/0g2GXAZULKI

Excelde sık karşılaşılan sorunlardan birisi de tarihle ilgili işlemlerdir.

Excelde yılın ilk iş günü nasıl bulunur?

 Excelde ayın ilk iş günü nasıl bulunur? 

Excelde haftanın ilk iş günü nasıl bulunur? İlgili fonksiyonlar (Türkçe ve İngilizce Excel ) olarak:

HAFTANINGÜNÜ  (WEEKDAY)

İŞGÜNÜ (WORKDAY)

SERİAY (EOMONTH)

TARİH (DATE)

HAFTANINGÜNÜ  (WEEKDAY) - HAFTANIN KAÇINCI GÜNÜ

İŞGÜNÜ (WORKDAY) - İŞGÜNÜNÜ BULUR

SERİAY (EOMONTH) - AYIN SON GÜNÜ

TARİH (DATE) - GÜN, AY, YIL DEĞERLERİNDEN TARİHİ VERİSİNİ OLUŞTURUR.

Www.farurukcubukcu.com

Tarih ve saat fonksiyonları, özellikle tarih ve saat türündeki verileri işlemek üzere geliştirilmiştir.

Hafta, gün ve saat hesaplamaları da Excel’de sık karşılaşılan uygulamalardır. Bu videoda tarih fonksiyonların HAFTANINGÜNÜ (WEEKDAY) fonksiyonu kullanılarak çözüm geliştirilmektedir.

Türkçesi               İngilizcesi             Açıklama

AY          MONTH               Bir tarih seri numarasını (tarih bilgisi) ay değerine çevirir.

BUGÜN                TODAY  Bugünün tarihini seri numarasına çevirir.

DAKIKA                MINUTE               Bir tarih seri numarasını dakikaya çevirir.

ETARİHLİ             DATEDIF              İki tarih arasındaki süreyi gün, hafta, ay ve yıl cinsinden bulur.

GÜN      DAY        Seri tarih numarasını güne çevirir.

GÜN360              DAYS360              Yılı 360 gün olarak hesaplar.

HAFTANINGÜNÜ              WEEKDAY            Bir tarih seri numarasını haftanın gününe çevirir.

HAFTASAY           WEEKNUM          Haftanın sayısını (1-53 arasında) verir.

İŞGÜNÜ               WORKDAY           Belirtilen gün sayısı sonrasında ulaşılan çalışma gününü hesaplar.

SAAT     HOUR    Bir tarih seri numarasını saate çevirir.

SANİYE                 SECOND               Bir tarih seri numarasını saniyeye çevirir.

SERİTARİH           EDATE   Belirtilen sayı kadar, sonraki ayı bulur.

SARİAY  EOMONTH          Ayın son gününü verir.

ŞIMDİ    NOW     Geçerli tarih ve saati verir.

TAMİŞGÜNÜ      NETWORKDAYS İki tarih arasındaki tam çalışma günlerini hesaplar.

TARİH   DATE     Belirli bir tarihin seri numarasını verir. Tarih değerini oluşturur. Yıl, ay ve gün olarak

TARİHSAYISI        DATEVALUE        Metin biçimindeki bir tarihin seri numarasını verir. Tarihe çevirir.

YIL          YEAR      Bir tarih seri numarasını yıla çevirir.

TARİHFARKI        YEARFRAC           İki tarih arasındaki yıl sayısını verir.

ZAMAN                TIME      Belirli bir zamanın seri numarasını verir

ZAMANDEĞERİ   TIMEVALUE        Metin tarih değerini sayısal değere dönüştürür.

Çok sayıda Excel dosyası üzerine Pivot tablo nasıl yapılır?

Çok sayıda Excel dosyası üzerine Pivot tablo nasıl yapılır.  

Çok sayıda Excel dosyasını bir araya getirerek pivot tablo oluşturmak.

https://youtu.be/ryaAdcMz_YY

Pivot tablo ile ne yapılır. Pivot tablo ile veri tablosunda belirtilen sütunlar temelinde gruplama ve toplama işlemleri yapılır. Gruplama temelinde belirtilen alanlar toplanabilir (sum), sayılabilir (count) ya da ortalaması (average) alınabilir. Pivot tablo üzerinde filtreleme yapmak, grafik çizmek çok kolay bir şekilde yapılır. Kurumsal ortamda yaygın olarak kullanılan pivot tabloların oluşturulması ve kullanım şekilleri bu videoda yer almaktadır.

Veri Modeli (Data Model), Excel 2013 ile gelen ve çok sayıda dosyanın / tablonun bir araya getirilebilmesini, aralarından ilişki kurulmasını ve pivot tablonun bu veri modeli üzerine – içindeki tablolar üzerine kurulmasını sağlar.

Bu konuda bilinmesi gereken şeyler:

İlişkiler bire-bir (one to one) ve bire-çok (one to many) olmak üzere iki şekilde oluşturulabilir. Bire-çok ilişkide bir tarafındaki tablonun Birincil (Primary) sütunu diğer tablonun (çok tarafındaki) Yabancı (Foreign) sütununa bağlanır.

Bire-çok ilişki bir tablonun (sorgunun) tek bir kaydı diğer bir tablonun (sorgunun) birden çok kaydıyla eşleşmesini belirtir. Örneğin bir müşterinin çok sayıda hareketi (satış olayı) vardır. Aynı şekilde bir ürünün hareketleri arasındaki ilişki sağlanmış olur.

Veri modeli / data model ve Power BI vidyolarımı izlerseniz bu işin mantığına ilişkin temel bilgileri öğrenebilirsiniz.

Excel’de neler bilinmeli?

Microsoft Excel® ’de neler bilinmeli?

https://youtu.be/byjbaw0zrIo

İşe başvurdunuz ya da bir kurumda çalışıyor ve Excel kullanıyorsunuz. Ama ne kadar biliyorsunuz?

• ·       Excel ortamında temel sayfa (sheet) yapısı – sayfa oluşturma, kopyalama, silme, bulma, vb.
• ·       Temel formül geliştirme (dört işlem, yüzde, karşılaştırma, ….)
• ·       Sabit adresleme (a1     $a$1)
• ·       Temel olarak biçimleme – sayılar, metinler, kenarlık, renklendirme, %, vb.
• ·       Dosya kaydetme şekilleri
• ·       Güvenlik ayarları
• ·       Temel fonksiyonlar: EĞER (IF), DÜŞEYARA (VLOOKUP), ETOPLA (SUMIF), BÜYÜKHARF (UPPER) gibi metin, ve tarih fonksiyonları …..
  25 tane bu konuda videolarımız var.  Sık kullanılan fonksiyonlar
• 25 tane bu konuda videolarımız var.  Sık kullanılan fonksiyonlar
• ·       Bağlantılı Çalışma (Linkler) – Bağlantıların Yönetme
• ·       Grafikler
• ·       Metinlerin ayırma ve birleştirme (metni sütunlara çevirme / text to columns)
• ·       Hesaplama (Calculation) modu – F9
• ·       Koşullu biçimlendirme (Conditional Formatting)
• ·       Ad (name) oluşturma
• ·       Filtreleme
• ·       Pivot tablo, … veri modeli, sorgular (queries) …
• ·       Uygulamalar arasında yapıştırma – PowerPoint
• ·       Makro kaydetme, FÇ makro1 – grafik çizer, FÇ makro2 - filtreleme
• ·       … gereksinimlere göre, şirketlerdeki uygulamalara göre, …
• ·       … Kullanılan Microsoft Excel sürümü, dili, vs. ….
• ·       www.excelci.com
• ·       www.farukcubukcu.com
• ·       https://www.youtube.com/user/farukcubukcu