Tutorial Microsoft Excel Data Analytics dan Business Intelligence
Setelah menguasai formula tingkat lanjut, Power Query, Power Pivot, DAX, dashboard, dan VBA, tahap berikutnya adalah mempelajari Data Analytics dan Business Intelligence (BI).
Pada tahap ini kita belajar bagaimana mengambil data mentah dalam jumlah besar, membersihkannya, menghubungkan berbagai sumber data, melakukan analisis, kemudian menyajikannya menjadi informasi yang mudah dipahami.
Alur kerjanya:
DATA MENTAH
↓
DATA CLEANING
↓
POWER QUERY
↓
DATA MODEL
↓
DAX
↓
ANALISIS
↓
DASHBOARD
↓
BUSINESS INSIGHT
↓
KEPUTUSAN1. Apa Itu Data Analytics?
Data Analytics adalah proses mengolah data untuk menemukan:
Pola.
Tren.
Perbandingan.
Masalah.
Peluang.
Penyebab perubahan.
Prediksi.
Contohnya bukan hanya:
Penjualan bulan ini Rp500 juta.
Tetapi:
Penjualan turun 12% dibandingkan bulan sebelumnya karena penjualan wilayah Jawa Barat turun 25%.
Informasi kedua jauh lebih berguna untuk mengambil keputusan.
2. Mengenal Business Intelligence
Business Intelligence atau BI adalah proses mengubah data menjadi informasi yang membantu perusahaan mengambil keputusan.
Contohnya:
Database Penjualan
↓
Data Processing
↓
Analisis
↓
Dashboard
↓
Manajemen
↓
Keputusan BisnisBI biasanya digunakan untuk:
Penjualan.
Keuangan.
Marketing.
Inventory.
Customer.
HR.
Operasional.
3. Memahami Data Source
Data tidak selalu berasal dari Excel.
Sumber data dapat berupa:
Excel.
CSV.
TXT.
Database SQL.
Website.
SharePoint.
Folder.
Sistem ERP.
Sistem POS.
API.
Excel dan Power Query dapat menjadi salah satu pintu masuk untuk mengolah berbagai sumber tersebut.
4. Prinsip ETL
Dalam data analytics terdapat konsep ETL.
ETL berarti:
Extract
Mengambil data.
Transform
Membersihkan dan mengubah data.
Load
Memasukkan data ke model atau tujuan akhir.
Contoh:
CSV
↓
Extract
↓
Power Query
↓
Clean Data
↓
Transform
↓
Load
↓
Data Model5. Data Cleaning Profesional
Data mentah sering memiliki masalah seperti:
Jakarta
jakarta
JAKARTA
Jakarta
JakartaSecara manusia terlihat sama.
Namun komputer dapat menganggapnya berbeda.
Data perlu distandarkan.
Contohnya:
=TRIM(A2)dan:
=UPPER(A2)Atau proses tersebut dilakukan melalui Power Query.
6. Menangani Missing Data
Data bisa memiliki nilai kosong.
Contoh:
| Produk | Qty | Harga |
|---|---|---|
| Buku | 10 | 10000 |
| Pulpen | 5000 | |
| Pensil | 5 |
Kita harus menentukan:
Apakah data kosong memang valid?
Apakah harus diisi?
Apakah harus dihapus?
Apakah harus diberi nilai default?
Jangan langsung mengganti semua nilai kosong dengan 0 tanpa memahami arti datanya.
7. Menangani Duplicate Data
Duplikat harus dianalisis terlebih dahulu.
Contohnya:
TRX001
TRX001Apakah itu transaksi yang sama?
Atau memang terdapat dua transaksi dengan ID yang salah?
Gunakan kombinasi:
ID transaksi.
Tanggal.
Produk.
Customer.
Qty.
untuk menentukan apakah data benar-benar duplikat.
8. Membuat Data Model
Data model yang baik biasanya menggunakan beberapa tabel.
Contoh:
DIM_PRODUCT
│
│
DIM_CUSTOMER ─ FACT_SALES ─ DIM_SALES
│
│
DIM_DATEIni merupakan konsep dasar model data yang sangat penting.
9. Fact Table
Fact Table berisi transaksi atau angka yang dapat dianalisis.
Contoh:
| Date | ProductID | CustomerID | SalesID | Qty | Revenue |
|---|
Contohnya:
01/10/2026
P001
C001
S001
10
10000010. Dimension Table
Dimension Table berisi informasi deskriptif.
Contohnya:
Product
| ProductID | Product | Category |
|---|
Customer
| CustomerID | Customer | City |
|---|
Sales
| SalesID | Sales | Region |
|---|
Date
| Date | Year | Month | Quarter |
|---|
11. Star Schema
Model yang sangat umum dalam Business Intelligence adalah Star Schema.
Strukturnya:
Product
│
│
Customer ─── Sales ─── Salesperson
│
│
DateTabel transaksi berada di tengah dan tabel dimensi mengelilinginya.
Model ini membantu membuat analisis lebih terstruktur.
12. Membuat Date Table
Analisis waktu sangat penting.
Date Table sebaiknya memiliki:
| Date | Year | Month | Month No | Quarter | Week |
|---|
Contoh:
01/01/2026 | 2026 | January | 1 | Q1 | 1
02/01/2026 | 2026 | January | 1 | Q1 | 1Dengan Date Table kita dapat melakukan analisis:
Year-over-Year.
Month-over-Month.
Quarter-over-Quarter.
YTD.
MTD.
13. DAX untuk Total Penjualan
Contoh measure:
Total Sales :=
SUM(Sales[Revenue])Measure ini dapat digunakan pada berbagai visual.
14. Total Quantity
Total Qty :=
SUM(Sales[Qty])Dengan measure ini kita dapat mengetahui jumlah barang yang terjual.
15. Average Sales
Average Sales :=
AVERAGE(Sales[Revenue])Dapat digunakan untuk mengetahui rata-rata nilai transaksi.
16. Year-to-Date
YTD digunakan untuk menghitung nilai dari awal tahun sampai tanggal tertentu.
Konsepnya:
Sales YTD :=
TOTALYTD(
[Total Sales],
'Date'[Date]
)Ini berguna untuk membandingkan performa tahun berjalan.
17. Year-over-Year
Misalnya:
Sales tahun 2025 = Rp1 Miliar
Sales tahun 2026 = Rp1,2 MiliarPertumbuhan:
20%Konsep DAX:
Sales Growth :=
DIVIDE(
[Total Sales] - [Previous Year Sales],
[Previous Year Sales]
)18. Analisis Pareto
Pareto digunakan untuk mencari kontribusi terbesar.
Misalnya:
20% produk
menghasilkan
80% penjualanAnalisis ini dapat membantu perusahaan menentukan:
Produk prioritas.
Customer utama.
Wilayah utama.
Sales terbaik.
19. Analisis ABC
Produk dapat dikelompokkan menjadi:
A
Produk dengan kontribusi sangat besar.
B
Kontribusi menengah.
C
Kontribusi kecil.
Contoh:
A = 70% revenue
B = 20% revenue
C = 10% revenueAnalisis ini dapat digunakan untuk inventory dan sales management.
20. Analisis Customer
Dashboard dapat digunakan untuk mengetahui:
Customer terbesar.
Customer yang mulai tidak aktif.
Customer baru.
Customer berulang.
Rata-rata transaksi.
Total lifetime value.
Contoh KPI:
Total Customers
Active Customers
New Customers
Repeat Customers
Average Order Value21. Analisis Inventory
Untuk stok barang, kita dapat membuat:
Stock Awal
+
Pembelian
-
Penjualan
=
Stock AkhirKemudian tambahkan:
Minimum Stock.
Maximum Stock.
Reorder Point.
Contoh:
Jika Stock <= Reorder Point
→ Restock22. Analisis Penjualan Bulanan
Dashboard dapat menampilkan:
JAN ███████████
FEB █████████████
MAR ███████████████
APR █████████
MAY █████████████████Kemudian analisis:
Bulan terbaik.
Bulan terburuk.
Tren.
Pertumbuhan.
Penurunan.
23. Analisis Salesperson
Misalnya:
| Sales | Sales | Target | Achievement |
|---|---|---|---|
| Andi | 150 jt | 120 jt | 125% |
| Budi | 100 jt | 120 jt | 83% |
| Citra | 135 jt | 130 jt | 104% |
Dari sini kita dapat mengetahui siapa yang mencapai target.
24. Membuat KPI
KPI adalah indikator performa utama.
Contoh:
TOTAL SALES
Rp1.250.000.000
TARGET
Rp1.000.000.000
ACHIEVEMENT
125%
GROWTH
+18%KPI harus menjawab pertanyaan bisnis, bukan sekadar memenuhi dashboard.
25. Dashboard yang Baik
Dashboard profesional sebaiknya:
Tidak terlalu ramai.
Memiliki hierarki informasi.
Menampilkan KPI utama.
Memiliki filter.
Menggunakan grafik yang sesuai.
Tidak menggunakan terlalu banyak warna.
Memiliki judul yang jelas.
Menampilkan periode data.
Tujuan dashboard adalah membantu pengguna memahami kondisi bisnis dengan cepat.
26. Dari Dashboard ke Insight
Jangan berhenti pada grafik.
Misalnya dashboard menunjukkan:
Penjualan turun 15%.
Analisis berikutnya:
Produk mana yang turun?
Kota mana yang turun?
Sales mana yang turun?
Kapan mulai turun?
Apakah harga berubah?
Apakah stok bermasalah?
Inilah yang membedakan data reporting dengan data analytics.
27. Root Cause Analysis
Ketika KPI turun, lakukan analisis bertingkat.
Contoh:
Total Sales turun
↓
Wilayah mana?
↓
Jakarta
↓
Produk mana?
↓
Produk A
↓
Mengapa?
↓
Stock OutHasil akhirnya:
Penjualan turun karena Produk A mengalami stock out di wilayah Jakarta.
Informasi ini jauh lebih berguna bagi manajemen.
28. What-If Analysis
Excel memiliki fitur untuk melakukan simulasi.
Misalnya:
Bagaimana jika harga naik 5%?
Atau:
Bagaimana jika penjualan naik 10%?
Atau:
Berapa jumlah penjualan yang dibutuhkan untuk mencapai profit Rp100 juta?
Fitur yang dapat digunakan:
Goal Seek.
Scenario Manager.
Data Table.
29. Forecasting
Excel dapat digunakan untuk membuat perkiraan.
Misalnya data:
Januari = 100 jt
Februari = 110 jt
Maret = 125 jt
April = 130 jtKita dapat membuat forecast untuk bulan berikutnya berdasarkan pola historis.
Namun forecast harus diperlakukan sebagai perkiraan, bukan kepastian.
30. Analisis Statistik
Pada level ini kita juga dapat mempelajari:
Mean.
Median.
Standard deviation.
Variance.
Correlation.
Regression.
Distribution.
Contoh:
=AVERAGE(A2:A100)dan:
=STDEV.S(A2:A100)Statistik membantu memahami karakteristik data secara lebih mendalam.
31. Korelasi
Misalnya kita ingin mengetahui hubungan antara:
Biaya Iklan
dan
PenjualanKita dapat menggunakan:
=CORREL(A2:A100,B2:B100)Nilai mendekati:
+1menunjukkan hubungan positif yang kuat.
Namun korelasi tidak otomatis berarti sebab-akibat.
32. Regresi
Regresi dapat digunakan untuk mempelajari hubungan antarvariabel dan membuat model prediksi.
Contoh:
Biaya Iklan
↓
Model
↓
Prediksi PenjualanIni mulai masuk ke wilayah data analytics yang lebih serius.
33. Power BI
Setelah menguasai konsep di atas, langkah berikutnya adalah mempelajari Power BI.
Power BI dapat digunakan untuk:
Mengambil data.
Transformasi data.
Data modeling.
DAX.
Dashboard interaktif.
Sharing laporan.
Scheduled refresh.
Skill Excel yang sudah dipelajari sebelumnya sangat membantu ketika berpindah ke Power BI.
34. Excel vs Power BI
Excel sangat cocok untuk:
Analisis ad-hoc.
Perhitungan.
Input data.
Model sederhana.
Spreadsheet.
Automasi personal.
Power BI lebih cocok untuk:
Dashboard organisasi.
Data besar.
Banyak sumber data.
Distribusi laporan.
Business Intelligence.
Monitoring KPI.
Keduanya dapat digunakan bersama.
35. Workflow Profesional
Salah satu workflow yang dapat digunakan:
Excel / CSV / Database
↓
Power Query
↓
Data Model
↓
DAX
↓
Power BI / Excel
↓
Dashboard
↓
Insight
↓
DecisionIni merupakan salah satu jalur yang sangat baik untuk dipelajari setelah Excel Expert.
36. Proyek Akhir Level 7
Buat proyek:
SALES BUSINESS INTELLIGENCE DASHBOARD
Gunakan minimal:
10.000 transaksi.
100 produk.
50 customer.
20 salesperson.
10 kota.
Data selama 2 tahun.
Dashboard harus menampilkan:
KPI
Total Sales.
Total Profit.
Total Qty.
Total Customer.
Average Order Value.
Growth.
Analisis
Sales per bulan.
Sales per tahun.
Sales per kota.
Sales per produk.
Sales per salesperson.
Top 10 customer.
Top 10 produk.
Interaktif
Filter tahun.
Filter bulan.
Filter kota.
Filter kategori.
Filter sales.
Analisis lanjutan
YoY Growth.
YTD.
Pareto.
ABC Analysis.
Target Achievement.
Forecast.
37. Skill yang Harus Dikuasai
Setelah menyelesaikan level ini, Anda diharapkan memahami:
Excel
Advanced Formula.
Dynamic Array.
PivotTable.
Dashboard.
Power Query.
Power Pivot.
DAX.
VBA.
Data Analytics
Data Cleaning.
Data Modeling.
KPI.
Trend Analysis.
Pareto.
ABC Analysis.
Forecasting.
Root Cause Analysis.
Business Intelligence
ETL.
Fact Table.
Dimension Table.
Star Schema.
Data Model.
Dashboard.
Business Insight.
Kesimpulan
Pada Level 7, tujuan utama bukan lagi sekadar membuat laporan.
Tujuannya adalah menjawab pertanyaan:
"Apa yang terjadi?"
Kemudian:
"Mengapa hal tersebut terjadi?"
Dan akhirnya:
"Apa yang harus dilakukan?"
Inilah inti dari Data Analytics dan Business Intelligence.
Setelah menguasai level ini, perjalanan berikutnya dapat dilanjutkan ke Level 8 — Power BI Professional & Advanced DAX, kemudian Level 9 — Excel + AI dan Automation, hingga akhirnya masuk ke Level 10 — Data & Business Intelligence Solution Architect.
0 Comments:
Posting Komentar