[Tugas 7 Penkom] – Analisis data menggunakan Excel Lanjutan

Pada blog sebelumnya saya telah menulis “Analisis data menggunakan Excel” yang merupakan penugasan dari link edx mulai dari lab 1-3. Untuk mengunjunginya klik [Tugas 6 Penkom] – Analisis data menggunakan Excel. Selanjutnya saya akan menulis kelanjutan penugasan dari link tersebut, yaitu lab 4-7. Perlu diketahui di sini saya menggunakan microsoft excel 2010.

4. Dashboard

Pertama unduh file ini untuk mengerjakan penugasan. File akan seperti di bawah ini.

Tenambahkan slicers ke sheet. Pilih grafik Yearly Sales By Country, dan tambahkan tujuh slicer yang sesuai dengan Year, Country, Customer Gender, Age Group, Product Category, Sub Category and Frame Size. Caranya klik bagan, Analyze, Insert Slicer. Selanjutnya menghubungkan slicers ini ke bagan, klik bagan, Analyze, Insert Conection.

1. Hubungkan grafik Yearly Sales by Country ke semua slicer, kecuali slicer Year
2. Hubungkan Yearly Sales by Category ke slicers Age Group, Country, Customer Gender, dan Frame Size
3. Hubungkan grafik Sales by Frame Size ke semua slicer, kecuali slicer Frame Size
4. Hubungkan grafik Sales by Age Group ke semua slicers, kecuali Age Group

5. Conditional Formatting

Unduh file ini untuk melanjutkan. File akan berbentuk seperti di bawah ini

Buat lembar baru dengan nama Details. Buat PivotTable dengan cara yang telah saya jelaskan pada blog kemarin. PivotTable dapat dibuat dengan nama SalesTable kemudian masukkan Order Quantity, Revenue, dan Profit by Product Category. Year dan Country sebagai daftar filter, lalu tambahkan Sub Category ke baris tabel.

Buat kolom Margin dengan formula =Profit/Revenue. Klik Options, Fields Items & Sets, Calculated Field.

6. Mencari Perbandingan Pendapatan

Unduh file ini. Buka file nya, maka akan keluar seperti gambar di bawah ini

Menambahkan lembar baru bernama Growth . Dimulai pada sel B1, tambahkan tabel pivot baru menggunakan SalesTable sebagai sumber dengan Country , Customer Gender, dan Age Group sebagai filters, Product Category dan Sub Category sebagai rows, Years sebagai columns, dan Sum of Revenue sebagai Values.

Sekarang, salin struktur baris dan kolom ke sel di sebelah tabel pivot, yaitu, salin label Tahun di C6: H6 (tidak termasuk Grand Total) untuk rentang L6: Q6, dan kategori Produk dan label sub kategori dalam B7: B26 (tidak termasuk Grand Total) untuk rentang K7: K26. Sorot dan salin rentang B6: H26, tempatkan kursor Anda di sel K6, klik kanan dan dari Opsi Tempel, pilih Pemformatan (R).

Populasikan area data dari struktur tabulasi silang yang baru dibuat menggunakan fungsi SUMIFS ().
Dengan asumsi Anda memiliki:

• K7: Product Category misalnya “Accessories”
• Q6: Year misalnya. “2016”
• C1: Filter Country
• C2: Filter Customer Gender
• C3: Filter Age Group

Year 2016 untuk Product Category Accessories akan memiliki rumus berikut :

Selain itu, anggap Anda memiliki:

• K8: Sub Category misalnya “Bike Racks”
• Q6: Year misalnya. “2016”
• C1: Filter Country
• C2: Filter Customer Gender
• C3: Filter Age Group

Year 2016 untuk Sub Category Bike Racks akan memiliki rumus berikut :

Tambah Sparklines:

Ketika Anda selesai dengan laporan tabular silang pertama, buat yang lain dengan struktur yang sama di sebelahnya (tidak termasuk kolom 2011). Kali ini, untuk% Perubahan Pendapatan, yang pada dasarnya merupakan perbedaan antara dua tahun, dibagi dengan tahun sebelumnya.
Misalnya, satu rumus dapat berupa:

Tambah icon untuk menunjukkan naik turun :

Untuk melihat data yang telah saya kerjaan dapat diakses di sini.

Jangan menyerah sebelum mencoba. Memang kalau hanya dilihat sekilas saja tampak susah, namun jika kita memiliki niat untuk mengerjakan dan mempunyai fikiran bisa, pasti bisa.