Konten
Anda mungkin pernah mendengar tentang Hukum Pareto atau Prinsip 20/80. Pada akhir abad ke-19, sosiolog dan ekonom Italia Vilfredo Pareto menemukan bahwa distribusi kekayaan dalam masyarakat tidak merata dan tunduk pada ketergantungan tertentu: dengan peningkatan kekayaan, jumlah orang kaya berkurang secara eksponensial dengan koefisien konstan ( di antara rumah tangga Italia, 80% pendapatan ada di 20% keluarga). Kemudian, ide ini dikembangkan dalam bukunya oleh Richard Koch, yang mengusulkan perumusan "Prinsip 20/80" universal (20% upaya memberikan 80% hasil). Dalam praktiknya, hukum ini biasanya tidak dinyatakan dalam angka yang begitu indah (baca "The Long Tail" oleh Chris Anderson), tetapi dengan jelas menunjukkan distribusi sumber daya, keuntungan, biaya, dll yang tidak merata.
Dalam analisis bisnis, bagan Pareto sering dibuat untuk mewakili ketidakrataan ini. Ini dapat digunakan untuk menunjukkan secara visual, misalnya, produk atau pelanggan mana yang paling menguntungkan. Biasanya terlihat seperti ini:
Fitur utamanya:
- Setiap kolom biru dari histogram mewakili keuntungan untuk produk dalam unit absolut dan diplot di sepanjang sumbu kiri.
- Grafik oranye menunjukkan persentase kumulatif keuntungan (yaitu bagian keuntungan secara kumulatif).
- Pada batas bersyarat 80%, garis horizontal ambang batas biasanya ditarik untuk kejelasan. Semua barang di sebelah kiri titik perpotongan garis ini dengan grafik akumulasi keuntungan memberi kita 80% dari uang, semua barang di sebelah kanan - 20% sisanya.
Mari kita lihat cara membuat bagan Pareto di Microsoft Excel sendiri.
Opsi 1. Bagan Pareto sederhana berdasarkan data yang sudah jadi
Jika data sumber datang kepada Anda dalam bentuk tabel yang serupa (yaitu, sudah dalam bentuk jadi):
… maka kita lakukan hal berikut.
Urutkan tabel dalam urutan keuntungan (tab Data – Penyortiran) dan tambahkan kolom dengan rumus untuk menghitung persentase akumulasi keuntungan:
Rumus ini membagi total akumulasi keuntungan dari awal daftar ke item saat ini dengan total keuntungan untuk seluruh tabel. Kami juga menambahkan kolom dengan konstanta 80% untuk membuat garis putus-putus ambang batas horizontal di bagan mendatang:
Kami memilih semua data dan membuat histogram reguler di tab Sisipkan – Histogram (Sisipkan – Bagan Kolom). Seharusnya terlihat seperti ini:
Seri persentase dalam bagan yang dihasilkan harus dikirim sepanjang sumbu sekunder (kanan). Untuk melakukan ini, Anda perlu memilih baris dengan mouse, tetapi ini bisa sulit, karena sulit dilihat dengan latar belakang kolom laba besar. Jadi lebih baik menggunakan daftar drop-down pada tab untuk menyorot tata ruang or dibentuk:
Kemudian klik kanan pada baris yang dipilih dan pilih perintah Format Seri Data dan di jendela yang muncul, pilih opsi Pada sumbu sekunder (Sumbu Sekunder). Akibatnya, diagram kami akan terlihat seperti ini:
Untuk seri Akumulasi Bagi Hasil dan Ambang Batas, Anda perlu mengubah jenis bagan dari kolom menjadi garis. Untuk melakukan ini, klik pada masing-masing baris ini dan pilih perintah Ubah Jenis Bagan Seri.
Yang tersisa hanyalah memilih baris horizontal Threshold dan memformatnya sehingga terlihat seperti garis putus-putus daripada data (yaitu, hapus spidol, buat garis putus-putus menjadi merah, dll.). Semua ini dapat dilakukan dengan mengklik kanan pada baris dan memilih perintah Format Seri Data. Sekarang diagram akan mengambil bentuk akhirnya:
Menurut itu, kita dapat menyimpulkan bahwa 80% dari keuntungan dibawa oleh 5 barang pertama, dan semua barang lain di sebelah kanan kentang hanya menyumbang 20% dari keuntungan.
Di Excel 2013, Anda dapat melakukannya dengan lebih mudah – gunakan tipe bagan kombo bawaan baru segera saat merencanakan:
Opsi 2: PivotTable dan Pivot Pareto Chart
Apa yang harus dilakukan jika tidak ada data siap pakai untuk konstruksi, tetapi hanya informasi mentah asli? Mari kita asumsikan bahwa pada awalnya kita memiliki tabel dengan data penjualan seperti ini:
Untuk membuat bagan Pareto di atasnya dan mencari tahu produk mana yang paling laku, Anda harus menganalisis data sumbernya terlebih dahulu. Cara termudah untuk melakukannya adalah dengan tabel pivot. Pilih sel mana saja di tabel sumber dan gunakan perintah Sisipkan – Tabel Pivot (Sisipkan – Tabel Pivot). Di jendela perantara yang muncul, jangan ubah apa pun dan klik OK, lalu di panel yang muncul di sebelah kanan, seret bidang data sumber dari atas ke bawah area tata letak tabel pivot mendatang:
Hasilnya harus berupa tabel ringkasan dengan total pendapatan untuk setiap produk:
Urutkan dalam urutan pendapatan dengan mengatur sel aktif ke kolom Jumlah di bidang Pendapatan dan menggunakan tombol sortir о (Dari Z ke A) tab Data.
Sekarang kita perlu menambahkan kolom terhitung dengan akumulasi pendapatan bunga. Untuk melakukan ini, seret bidang lagi Pendapatan ke daerah Nilai - Nilai di panel kanan untuk mendapatkan kolom duplikat di pivot. Kemudian klik kanan pada kolom kloning dan pilih perintah Perhitungan tambahan – % dari total lari di lapangan (Show Data As – % Running Total In). Di jendela yang muncul, pilih bidang Nama, di mana persentase pendapatan akan terakumulasi dari atas ke bawah. Outputnya akan terlihat seperti tabel ini:
Seperti yang Anda lihat, ini hampir merupakan tabel yang sudah jadi dari bagian pertama artikel. Ini hanya kekurangan untuk kebahagiaan lengkap kolom dengan nilai ambang 80% untuk membangun garis putus-putus dalam diagram masa depan. Kolom seperti itu dapat dengan mudah ditambahkan menggunakan bidang terhitung. Sorot nomor apa pun dalam ringkasan dan kemudian klik pada tab Beranda – Sisipkan – Bidang Terhitung (Beranda – Sisipkan – Bidang Terhitung). Di jendela yang terbuka, masukkan nama bidang dan rumusnya (dalam kasus kami, konstanta):
Setelah mengklik OK kolom ketiga akan ditambahkan ke tabel dengan nilai 80% di semua sel, dan akhirnya akan mengambil formulir yang diperlukan. Kemudian Anda dapat menggunakan perintah Bagan Pivot (Bagan Pivot) tab parameter (Pilihan) or Analisis (Analisis) dan atur bagan dengan cara yang persis sama seperti opsi pertama:
Menyoroti produk utama
Untuk menyoroti faktor yang paling berpengaruh, yaitu kolom yang terletak di sebelah kiri titik persimpangan kurva bunga akumulasi oranye dengan garis batas horizontal 80% dapat disorot. Untuk melakukan ini, Anda harus menambahkan kolom lain ke tabel dengan rumus:
Rumus ini menghasilkan 1 jika produk berada di sebelah kiri titik persimpangan dan 0 jika berada di sebelah kanan. Maka Anda perlu melakukan hal berikut:
- Kami menambahkan kolom baru ke bagan – cara termudah untuk melakukannya adalah dengan menyalin sederhana, yaitu kolom sorot backlight, salin (Ctrl + C), pilih diagram dan sisipkan (Ctrl + V).
- Pilih baris yang ditambahkan dan alihkan di sepanjang sumbu sekunder, seperti dijelaskan di atas.
- Tipe Bagan Seri backlight berubah menjadi kolom (histogram).
- Kami menghapus izin samping di properti baris (klik kanan pada baris Iluminasi – Format Baris – Celah Samping) sehingga kolom bergabung menjadi satu kesatuan.
- Kami menghapus batas kolom, dan membuat isiannya tembus cahaya.
Hasilnya, kami mendapatkan sorotan yang bagus dari produk terbaik:
PS
Dimulai dengan Excel 2016, bagan Pareto telah ditambahkan ke set standar bagan Excel. Sekarang, untuk membangunnya, cukup pilih rentang dan pada tab Menyisipkan (Memasukkan) pilih jenis yang sesuai:
Satu klik – dan diagram sudah siap:
- Cara membuat laporan menggunakan tabel pivot
- Siapkan perhitungan di PivotTable
- Apa yang Baru di Bagan di Excel 2013
- Artikel Wikipedia tentang hukum Pareto