Excel Veri Analizi ve Dashboard Çalışması

Excel Veri Analizi ve Dashboard Çalışması

Veri analizi ve dashboard çalışması; farklı veri kaynaklarından gelen çok sayda veri dosyası ya da tabloyu bir araya getirip özel fonksiyonlar aracılığıyla bunları işlemek, sorgular (query) ve PowerPivot özellikleriyle de özetleyerek görsel bir şekilde raporlanmasını (dashboard) sağlamaktır.

 

Birinci aşama: SİPARİŞ verilerinin ilgili alanlarının diğer tablolardan toplanması sağlanır:

Kullanılan fonksiyonlar (Türkçe / İngilizce):

DÜŞEYARA  / VLOOKUP

KIRP   / TRIM

İNDİS / INDEX

KAÇINCI / MATCH

EĞERHATA / IFERROR

 

İkinci AŞAMA: Pivot tablo ile dashboard oluşturma. İstenilen alanların gruplanması tablo ve grafik oluşturulması. Ayrıca hesaplanmış alan oluşturularak çeşitli oranların vb hesapların gösterilmesi.

 

Üçüncü aşama: Farklı veri kaynaklarından verileri almak, ilişkilendirmek.

Dördüncü aşama: Power Pivot ve Dashboard.

 

http://farukcubukcu.com/egitim/3-veri-analizi-ve-dashboard/64/

 

https://youtu.be/_4NKVw1Ng8A

https://youtu.be/ni0Us4mLxtI

VLOOKUP ile 2. Eşleşeni bulmak

 2. ya da istenilen eşleşeni bulmak için: 

DÜŞEYARA / VLOOKUP gibi arama fonksiyonları iş hayatında sıkça kullanılan; bir verinin ilişkili olduğu diğer bir veriyi arayıp bulmayı sağlar.

DÜŞEYARA ilk eşleşeni bulur ya da ilk bulduğunu getirir. 2. Eşleşeni getirmek için ne yapmak gerekir. Bu videoda 2. Eşleşen ve 3. Eşleşen nasıl getirilecek anlatılmaktadır.

EĞERSAY (COUNTIF) ile aranacak sütunun bir kaçıncı olduklarını gösteren bir sütün oluşturulur.

Birleştirme işlem &  ile yapılır.

Excel’de arama işlemleri; bir veriye ilişkin bilgilerin diğer bir veri tablosundan bulunup getirilmesi gibi çeşitli “arama ve bulma” işlevlerini kapsar. Örneğin bir ürün koduna karşılık gelen fiyat bilgisinin bulunması ya da bir personel adına karşılık gelen adres bilgisinin bulunması gibi.

DÜŞEYARA (İngilizce sürümdeki karşılığı VLOOKUP) fonksiyonudur. “Düşeyarama” olarak adlandırabileceğimiz bu fonksiyon, bir veriyi belirtilen bir sütunda aşağıya doğru arayıp bulmak, bulduğunda da diğer bir sütundaki (sağ tarafta) ilgili bir veri getirmeyi sağlar.

DÜŞEYARA aşağıya doğru arama işlemini yapıp sağ taraftaki sütundan verileri getirmeyi sağlarken YATAYARA (HLOOKUP) fonksiyonu da bunun yana doğru olanını yanı verilerin sağa doğru atanmasını ve bulunduğunda alt tarafta belirtilen satırdan verilerin getirilmesini sağlar.

 

DÜŞEYARA (VLOOKUP) fonksiyonu bir veriyi, tablo verisi üzerinde aşağıya doğru arayarak bulur. Bu ünitede DÜŞEYARA (VLOOKUP) ile ilgili çok sayıda açıklama ve uygulama bulabilirsiniz. Ancak arama yapılacak verinin yatay bir tabloda yapılması durumunda DÜŞEYARA’nın aynısının YATAYARA’sının geliştirilmesine neden olmuştur.

YATAYARA (HLOOKUP) fonksiyonu aynı DÜŞEYARA (VLOOKUP) fonksiyonu gibi çalışır ancak arama yapacağı veri üzerinde sağa doğru bir arama yaparak ilgili değeri bulur. Ardından aşağıya doğru belirtilen sayıda satır giderek istenilen değeri bulur.

 

Arama işlemleri tipik olarak DÜŞEYARA (VLOOKUP), İNDİS (INDEX), KAÇINCI (MATCH) ve benzeri diğer fonksiyonlarla yapılır.

 

Çok sayıda eşleşenin bulmak için de videolarımız var. Onlara da bakınız.

 

https://youtu.be/-vNeOFeI_40

https://www.youtube.com/watch?v=4-dewrhVSSM&list=PL3dc_-B83C8AmT4pqt6oy57xzUUYLDG0S

 

DÜŞEYARA ile 2. Eşleşeni bulmak

 

DÜŞEYARA ile 2. Eşleşeni bulmak

DÜŞEYARA ile 3. Eşleşeni bulmak

DÜŞEYARA ile istenilen eşleşeni bulmak

VLOOKUP ile 2. Eşleşeni bulmak

DÜŞEYARA / VLOOKUP gibi arama fonksiyonları iş hayatında sıkça kullanılan; bir verinin ilişkili olduğu diğer bir veriyi arayıp bulmayı sağlar.

DÜŞEYARA ilk eşleşeni bulur ya da ilk bulduğunu getirir. 2. Eşleşeni getirmek için ne yapmak gerekir. Bu videoda 2. Eşleşen ve 3. Eşleşen nasıl getirilecek anlatılmaktadır.

EĞERSAY (COUNTIF) ile aranacak sütunun bir kaçıncı olduklarını gösteren bir sütün oluşturulur.

Birleştirme işlem &  ile yapılır.

Excel’de arama işlemleri; bir veriye ilişkin bilgilerin diğer bir veri tablosundan bulunup getirilmesi gibi çeşitli “arama ve bulma” işlevlerini kapsar. Örneğin bir ürün koduna karşılık gelen fiyat bilgisinin bulunması ya da bir personel adına karşılık gelen adres bilgisinin bulunması gibi.

DÜŞEYARA (İngilizce sürümdeki karşılığı VLOOKUP) fonksiyonudur. “Düşeyarama” olarak adlandırabileceğimiz bu fonksiyon, bir veriyi belirtilen bir sütunda aşağıya doğru arayıp bulmak, bulduğunda da diğer bir sütundaki (sağ tarafta) ilgili bir veri getirmeyi sağlar.

DÜŞEYARA aşağıya doğru arama işlemini yapıp sağ taraftaki sütundan verileri getirmeyi sağlarken YATAYARA (HLOOKUP) fonksiyonu da bunun yana doğru olanını yanı verilerin sağa doğru atanmasını ve bulunduğunda alt tarafta belirtilen satırdan verilerin getirilmesini sağlar.

 

DÜŞEYARA (VLOOKUP) fonksiyonu bir veriyi, tablo verisi üzerinde aşağıya doğru arayarak bulur. Bu ünitede DÜŞEYARA (VLOOKUP) ile ilgili çok sayıda açıklama ve uygulama bulabilirsiniz. Ancak arama yapılacak verinin yatay bir tabloda yapılması durumunda DÜŞEYARA’nın aynısının YATAYARA’sının geliştirilmesine neden olmuştur.

YATAYARA (HLOOKUP) fonksiyonu aynı DÜŞEYARA (VLOOKUP) fonksiyonu gibi çalışır ancak arama yapacağı veri üzerinde sağa doğru bir arama yaparak ilgili değeri bulur. Ardından aşağıya doğru belirtilen sayıda satır giderek istenilen değeri bulur.

 

Arama işlemleri tipik olarak DÜŞEYARA (VLOOKUP), İNDİS (INDEX), KAÇINCI (MATCH) ve benzeri diğer fonksiyonlarla yapılır.

 

Çok sayıda eşleşenin bulmak için de videolarımız var. Onlara da bakınız.

 

https://youtu.be/-vNeOFeI_40

https://www.youtube.com/watch?v=4-dewrhVSSM&list=PL3dc_-B83C8AmT4pqt6oy57xzUUYLDG0S

 

Excel veri temizleme

 Excel 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. 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) Boşlukları kaldırma 13) CTRL+H

Excel Power Pivot

 

Excel Power Pivot

Power Pivot tasarımı ve uygulaması

Excel Power Pivot uygulaması

Power Pivot, Excel’in veri modeli (data model) yapısına bir ek olarak geliştirilmiş bir analiz aracıdır. Power Pivot aracılığıyla veriler üzerinde analiz yapmayı sağlayan ölçüler (measures) ve KPI’lar oluşturulur.

Ölçüler oluşturmak için DAX fonksiyonları kullanılır. DAX (Data Analyssis Expressıons) uygulamaları: SUM, MIN, MAX, AVERAGE, COUNT, CALCULATE, FIRSDATE, LASTDATE, FILTER, SAMEPERIODLASTYEAR, BLANK,  gibi

=CALCULATE(DISTINCTCOUNT('SİPARİŞLER'[Ürün Kodu]))

=FIRSTDATE('SİPARİŞLER'[Tarih])

=DIVIDE(SUM('SİPARİŞLER'[Birim Fiyatı]);SUM('SİPARİŞLER'[maliyet]))

=DIVIDE((SUM('SİPARİŞ'[Tutar])-SUM('SİPARİŞ'[Maliyet]));SUM('SİPARİŞ'[Maliyet]))

 

Eğitimlerde ve Video’larımızda örneklerini görebilirsiniz.

KPI (Key Performance Indicators) ise koşullu biçimlendirmelere benzer görsel simgelerin kullanılmasını sağlayan araçlardır. Ölçüler üzerine hazırlanır. Eğitimlerde ve Video’larımızda örneklerini görebilirsiniz.

Excel 2010’da Com-Addin olarak, sonraki sürümlerde standart olarak yer alır. Excel 2016’da “Manage Data Model” olarak Office 365 Excel sürümünde ise Power Pivot olarak bir şeride ve üzerinde Ölçüler (Measure) ve KPI araçlarına sahiptir.

 

Videolarım:

https://youtu.be/BBkNRHtieeM

https://youtu.be/eMgRKEVSCCw