How to Analyzing Data in Excel

Posted byjevonsanoturia - G64190056Posted inUncategorized

Microsoft Excel atau Microsoft Office Excel adalah sebuah program aplikasi lembar kerja yang dibuat dan didistribusikan oleh Microsoft Corporation yang dapat dijalankan pada Microsoft Windows dan Mac OS. Aplikasi ini merupakan bagian dari Microsoft Office System.

Aplikasi ini memiliki fitur kalkulasi dan pembuatan grafik, dengan menggunakan strategi marketing Microsoft yang agresif. Oleh karena itu, Excel sering digunakan untuk menganalisis data. Anda akan belajar menggunakan salah satu fitur Excel yang paling terkenal untuk menunjukkan hasil analisis data.
Anda akan belajar cara membuat pivot table (basic knowledege) dari rentang dengan baris dan kolom di Excel. Anda akan melihat kekuatan Excel pivots dalam tindakan dan kemampuannya untuk meringkas data dengan cara yang fleksibel, memungkinkan eksplorasi data yang cepat dan menghasilkan wawasan berharga dari data yang terakumulasi.

Reporting in Excel

Berikut terdapat sejumlah data yang ingin Anda tampilkan. Untuk menampilkan data agar terlihat lebih menarik, Anda dapat menggunakan salah satu fitur Excel yaitu Chart.

Data yang ingin ditampilkan

Berikut cara membuat Chart.

  1. Highlight Data yang ingin dibuat Chart.
  2. Klik Tab Insert.
  3. Klik Recommend Chart maka Excel akan merekomendasikan Chart yang cocok untuk data Anda, atau Anda dapat sendiri memilih jenis Chart yang ingin Anda gunakan.

Link: Reporting in Excel

Excel Table

Ketika Anda ingin mengelola dan menganalisis sebuah grup data dalam jumlah yang sangat besar, gunakanlah fitur Excel Table. Contoh, terdapat sebuah data yang sangat banyak seperti gambar dibawah, dengan kolom sebanyak 113037 dan memiliki baris sebanyak 15.

Untuk membuat tabel langkah-langkah yang perlu Anda lakukan adalah:

  1. Klik Insert > Table.
  2. Atau menggunakan fitur Search: ketikkan Table > pilih Insert Table.

Add Total Row

Anda dapat menggunakan fitur Total Row untuk menghitung besarnya pendapatan (Revenue):

  1. Klik salah satu Cell pada tabel, maka akan muncul Tab Design.
  2. Klik Total Row, maka baris total akan ditambahkan ke baris paling bawah data.

Anda dapat melihat total dari tiap kolom data, misalnya untuk kolom Revenue akan muncul jumlah seluruh pendapatan berdasarkan data. Tak hanya itu Anda juga dapat mencari rata-rata revenue, nilai min dan max. Berikut caranya:

  1. Klik Down Arrow.
  2. Maka akan tampak pilihan, None, Average, Count, Count Numbers, Max, Min, Sum, dan lain-lain.

Filtering

Anda juga dapat menggunakan fitur Filter untuk melihat data berdasarkan kategori yang sudah dibuat. Dalam contoh ini, Filter akan digunakan untuk melihat revenue berdasarkan kategori Country yaitu United States. Berikut langkahnya:

  1. Klik Down Arrow pada kolom Country.
  2. Deselect “Sellect All”.
  3. Check United States.

Maka jumlah revenue yang ditampilkan akan terfilter berdasarkan kategori United States.

Add Column

Anda juga dapat menambahkan kolom atau baris. Caranya:

  1. Pada Tab Home, Ribbon Home, Group Cells, klik Insert.
  2. Klik Insert Sheet Columns.
  3. Rename kolom.

Dalam kasus ini, saya akan menambahkan kolom Month di sebelah kiri kolom Customer ID.

Formula

Dalam Excel terdapat banyak Formula yang dapat digunakan seperti SUM, MIN, MAX, AVERAGE, dan lain-lain. Dalam contoh ini saya akan menggunakan Formula yaitu TEXT untuk mengambil bulan dari kolom Date.

  1. Pada kolom Formula, ketikkan ” =TEXT([@DATE],”mmmm”) ” (tanpa menggunakan kutip dua terluar)

Saya juga menambah kolom Age Group

  1. Pada kolom Formula, ketikkan ” =IFS([@[Customer Age]]<25,”Youth”,[@[Customer Age]]<35,”Young Adults”,[@[Customer Age]]<65,”Adults”,[@[Customer Age]]<64,”Seniors”) ” (tanpa menggunakan kutip dua terluar)

Sehingga muncul lah kolom seperti di bawah ini

Saya juga menambahkan kolom Frame Size menggunakan Formula IF()…RIGHT()

  1. Pada kolom Formula, ketikkan ” =IF([@[Product Category]]=”Bikes”, RIGHT([@Product],2), ” “) ” (tanpa menggunakan kutip dua terluar)

Saya juga menambahkan kolom Profit pada bagian sebelah kanan kolom Revenue

  1. Pada kolom Formula, ketikkan ” =[@[Revenue]]-[@[Cost]] ” (tanpa menggunakan kutip dua terluar)
  2. Gunakan Total Row untuk melihat jumlah seluruh profit

LINK: Excel Table

Basic Pivot Table

Pivot Table adalah fitur Microsoft Excel memungkinkan pengguna mengambil informasi dengan cepat dari kumpulan data dalam jumlah besar. Pivot Tabel dapat digunakan sebagai alternatif untuk melakukan analisis data terkait mengelompokkan dan meringkas data. Berikut Cara membuat Pivot Table:

  1. Dari data yang sudah dibuat, rename Excel Table sebagai (contoh) SalesTable.
  2. Lalu klik Summarize with pivot table.
  3. Klik OK.

Buat PivotTable berdasarkan table ‘SalesTable’ ke Sheet yang baru. Tambahkan PivotTable Fields:

  1. Click and drag Product Category dan Subcategory ke dalam Rows, Year di Columns, dan Revenue sebagai Value.

Anda juga dapat membuat PivotTable2 yang baru pada Sheet yang sama di sebelah PivotTable1.

  1. Klik Insert > Table Group > PivotTable.
  2. Click dan drag Country dan State ke Row, Year ke Columns, dan Sum of Revenue sebagai Values.
  3. Gunakan fitur Sort, klik Tab Data > pada Group Sort & Filter klik Sort.
  4. Pilih Sort berdasarkan Largest to Smallest.
  5. Klik OK.

Tambahkan PivotTable3

  1. Klik Insert > Table Group > PivotTable.
  2. Click dan drag Frame Size ke Row, Sum of Revenue sebagai Values.
  3. Sembunyikan Row yang tidak mempunyai Frame Size dengan cara klik kanan.
  4. Deselect Blank Frame Size.
  5. klik OK.
  6. Gunakan fitur Sort, klik Tab Data > pada Group Sort & Filter klik Sort.
  7. Pilih Sort berdasarkan Largest to Smallest.
  8. Klik OK.

Tambahkan PivotTable4

  1. Klik Insert > Table Group > PivotTable.
  2. Click dan drag Age Group ke Row, Sum of Revenue sebagai Values.
  3. Gunakan fitur Sort, klik Tab Data > pada Group Sort & Filter klik Sort.
  4. Pilih Sort berdasarkan Largest to Smallest.
  5. Klik OK.

LINK: BasicPivotTable

Dashboards

Slicer

Slicer adalah fasilitas excel yang berfungsi untuk memfilter data secara interaktif pada tabel dan PivotTable. Berikut cara menggunakan fitur slicer.

Terdapat sebuah data seperti dibawah.

Untuk memulainya, pertama kita membuat Sheet baru dengan nama ‘Dashboard’. Lalu Move Chart yang kita miliki kedalam Sheet terbaru.

  1. Click tanda + di bawah bagian kiri, untuk menambah Sheet baru.
  2. Rename Sheet sebagai ‘Dashboard’.
  1. Klik Chart, lalu klik kanan pada Chart.
  2. Klik Move Chart…
  3. Pada Object in, pilih Sheet Dashboard.
  4. Klik OK.

Gunakan Snap to Grid agar Chart terlihat lebih rapih

  1. Pada Page Layout tab, Page Layout ribbon, di group Arrange, klik Align.
  2. Klik Snap to Grid.
  3. Lalu resize Chart.

Lakukan hal yang sama pada Chart yang lainnya.

Sebelumnya mari beri nama tiap Chart agar mudah untuk membedakannya

  1. Klik Chart yang ingin diberi nama.
  2. Pada tab Design, ribbon Design, group Chart layout, klik Add Chart Element.
  3. Pilih Chart Title.
  4. Beri nama Chart.

Tambahkan 7 Slicer, berdasarkan Year, Country, Custom Gender, Age Group, Product Category, Sub Category, Frame Size.

  1. Klik Chart ‘Yearly Sales by Contry’.
  2. Klik tab Analyze, pada ribbon Analyze, group Filter, Klik Insert Slicer.
  3. Pilih tag yang ingin diinput ke Slicer.

Berikutnya connect tiap Chart ke Slicer.

  1. Klik Chart, pada tab Analyze, klik Filter Connection.
  2. Pilih Slicer yang ingin dikoneksikan.

Lakukan pada tiap Chart, Chart ‘Yearly Sales of Country’ tautkan pada semua Slicer kecuali Slicer Year, ‘Yearly Sales by Category’ tautkan pada Slicer Age Group, Country, Frame Size, dan Customer Gender, ‘Sales by Frame Size’ tautkan pada Slicer Frame Size, ‘Sales by Age Group’ tautkan pada Slicer Age Group. Uncheck Gridlines pada tab View, ribbon View, group Show, agar terlihat rapih.

Setelahnya saya akan menambahkan beberapa pivot Chart

Pertama, Buat pivot Chart untuk menampilkan Sales by Country menggunakan Pie Chart. Perlihatkan persentase untuk setiap irisan pai dan hubungkan bagan ke semua Slicer, kecuali Slicer Country.

Kedua, buat bagan pivot tambahan untuk menampilkan Sales by Category menggunakan bagan Pie. Perlihatkan persentase untuk setiap Slicer Pie dan sambungkan bagan ke semua Slicer, kecuali Slicer category.

Sort and Custom Sort

Dalam Excel Anda dapat menggunakan fitur Sort agar lebih mudah melihat data dalam jumlah banyak.

Anda dapat mengurutkan data berdasarkan abjad, bahkan Anda dapat membuat Custom Sort.

  1. Klik Sort, pilih berdasarkan apa Anda ingin mengurutkan data Anda.
  2. Klik Sort & Filter, lalu klik Custom Sort, jika Anda ingin membuat urutan Anda sendiri.
  3. Pada tab Order pilih Custom List.
  4. Tambahkan Custom Sort Anda sendiri.

LINK: Dashboards

Profitability Analysis & Finding Analysis

Conditional Formatting

Pertama buat Sheet baru bernama Details.Buat pivot table yang memperlihatkan Order Quantity, Revenue, dan Provit dan Product Category sebagai row.Tambahkan Year dan Country sebagai filter. Tambahkan Sub Category sebagai row dalam pivot table.

Gunakan Data bars condittional formatting ke Order Quantity, Revenue, pada area Sub Category. Gunakan biru, hijau, dan kuning berturut.

  1. Pada tab Home, ribbon Home, group Styles, Klik Conditional Formatting.
  2. Klik Data bars.
  3. Pilih warna yang diinginkan.

Gunakan Slicer untuk membatasi tampilan menjadi Australia dan 2016 saja.

Calculated Field

Terkadang terdapat beberapa Formula yang tidak bekerja dengan baik dalam sebuah pivot table dikarenakan mengambil data dari tabel Data, sehingga pivot table tidak mengenali Formula yang dimasukkan. Oleh karena itu, gunakan fitur pivot table yaitu Calculated Fields.

  1. Pada tab Analyze, ribbon Analyze, group Calculations, klik Fields, Items, Sets.
  2. Klik Calculated Field.
  3. Beri nama.
  4. Pada block Formula masukkan Fields dan operasi aritmatika.
  5. Klik Add.
  6. Klik OK.

Tambahkan fitiur Color Scale conditional formatting pada kolom Margin pada level Sub Category.

Sekarang tambahkan Product field ke dalam row, dibawah Sub Category, Sekarang tambahkan Color scales conditional formatting ke Margin dari Product.

  1. Pada tab Home, ribbon Home, group Styles, Klik Conditional Formatting.
  2. Klik Merge Rules….
  3. Pilih condittional formatting yang ingin dihapus.
  4. Klik Delete Rule.
  5. Klik OK.

LINK:Profitability Analysis & Finding Analysis

Comparing Year over Year in Pivot Tables

Untuk membandingkan data dalam Excel lebih mudah agar terlihat perkembangannya dapat menggunakan Sparklines.

Sparklines

  1. Pilih Cell yang ingin dimasukkan Sparklines.
  2. Pada tab Insert, ribbon Insert, group Sparklines, klik Line.
  3. Pada column Data Range, masukkan Range.
  4. Klik OK.
  5. Copy Formatting agar lebih efisien.

Conditional Formatting Icon Sets

Jenis Formatting ini digunakan agar lebih mudah melihat perkembangan data, dibantu menggunakan icon dikarenakan terkeadang kita ketika melihat angka saja akan kesulitan melihat perkembangan data.

  1. Masukkan Formatting dalam satu cell.
  2. Pada tab Home, ribbon Home, group Styles, klik Conditional Formatting.
  3. Pilih Icon Sets.
  4. Copy Formatting, pada cell yang ingin digunakan.

LINK: Comparing Year over Year in Pivot Tables

Leave a comment

Design a site like this with WordPress.com
Get started