Salah satu metode utama manajemen dan logistik adalah Analisis ABC. Dengan itu, Anda dapat mengklasifikasikan sumber daya perusahaan, barang, pelanggan, dll. Sesuai dengan tingkat kepentingan. Pada saat yang sama, dengan tingkat kepentingan, masing-masing unit di atas ditugaskan satu dari tiga kategori: a, b atau C. Program Excel miliki di alat bagasi-nya yang membuatnya lebih mudah untuk melakukan analisis semacam ini. Mari kita cari tahu bagaimana cara menggunakannya, dan apa analisis ABC.
Menggunakan Analisis ABC.
Analisis ABC adalah semacam peningkatan dan beradaptasi dengan kondisi modern untuk prinsip Pareto. Menurut metode perilakunya, semua elemen analisis dibagi menjadi tiga kategori sesuai dengan tingkat kepentingan:- Kategori A - elemen memiliki kombinasi lebih dari 80% gravitasi tertentu;
- Kategori B - Elemen yang kombinasnya berkisar dari 5% hingga 15% dari gravitasi tertentu;
- Kategori C - elemen yang tersisa, total agregat yaitu 5% dan gravitasi yang kurang spesifik.
Perusahaan yang terpisah menggunakan teknik yang lebih canggih dan membagi item untuk tidak 3, tetapi oleh 4 atau 5 kelompok, tetapi kami akan mengandalkan skema analisis ABC klasik.
Metode 1: Analisis dengan Menyortir
Analisis Excel ABC dilakukan dengan menggunakan penyortiran. Semua item diurutkan dari berminyak menjadi lebih sedikit. Kemudian bagian akumulatif dari setiap elemen dihitung, berdasarkan yang ditugaskan kategori tertentu. Mari cari tahu bagaimana metodologi yang ditentukan digunakan dalam praktik.
Kami memiliki meja dengan daftar produk yang dijual perusahaan, dan jumlah pendapatan yang sesuai dari penjualan mereka untuk jangka waktu tertentu. Di bagian bawah meja, hasil dari pendapatan secara umum pada semua nama barang. Ada tugas yang menggunakan analisis ABC, hancurkan barang-barang ini menjadi kelompok-kelompok dengan pentingnya perusahaan.
- Kami menyorot tabel dengan kursor dengan menutup tombol kiri mouse, tidak termasuk tutup dan string terakhir. Buka tab "Data". Kami mengklik tombol "Sort", yang terletak di bilah alat "Sort and Filter" pada pita.
Anda juga dapat melakukan secara berbeda. Kami mengalokasikan rentang tabel di atas, kemudian pindah ke tab "Home" dan klik tombol "Sort and Filter" yang terletak di Editing Toolbox pada pita. Daftar ini diaktifkan di mana posisi "penyortiran yang dapat disesuaikan" diaktifkan.
- Saat menerapkan tindakan di atas, jendela pengaturan penyortiran diluncurkan. Kami terlihat sehingga "data saya berisi header" parameter diatur ke tanda centang. Dalam hal ketidakhadirannya, instal.
Di bidang "kolom", tentukan nama kolom di mana data pendapatan terkandung.
Di bidang "Sort", Anda perlu menentukan, pada mana kriteria spesifik akan diurutkan. Biarkan pengaturan preset - "Nilai".
Dalam bidang "PESANAN", pamerkan posisi "turun".
Setelah produk dari pengaturan yang ditentukan, klik tombol "OK" di bagian bawah jendela.
- Setelah melakukan tindakan yang ditentukan, semua item diurutkan berdasarkan pendapatan dari lebih ke yang lebih kecil.
- Sekarang kita harus menghitung proporsi masing-masing elemen untuk hasil umum. Buat kolom tambahan untuk keperluan ini, yang kami sebut "berbagi berat". Di sel pertama kolom ini, kami meletakkan tanda "=", setelah itu Anda menentukan tautan ke sel di mana jumlah pendapatan dari implementasi produk yang relevan adalah. Selanjutnya, atur tanda divisi ("/"). Setelah itu, kami menunjukkan koordinat sel, yang berisi jumlah total penjualan barang di seluruh perusahaan.
Mengingat fakta bahwa formula yang ditentukan kita akan menyalin ke sel-sel lain dari kolom "Bagikan" oleh penanda pengisian, alamat tautan ke elemen yang berisi jumlah akhir pendapatan ke perusahaan, kita perlu memperbaikinya. Untuk melakukan ini, buat tautan absolut. Pilih koordinat sel yang ditentukan dalam rumus dan tekan tombol F4. Sebelum koordinat, seperti yang kita lihat, tanda dolar muncul, yang menunjukkan bahwa tautan telah menjadi mutlak. Perlu dicatat bahwa referensi jumlah pendapatan pertama dalam daftar barang (produk 3) harus tetap relatif.
Kemudian, untuk membuat perhitungan, tekan tombol Enter.
- Seperti yang kita lihat, proporsi pendapatan dari produk pertama yang ditentukan dalam daftar muncul di sel target. Untuk menyalin rumus di kisaran di bawah ini, kami menempatkan kursor ke sudut kanan bawah sel. Transformasinya di penanda pengisian, memiliki penampilan salib kecil. Kami mengklik tombol kiri mouse dan seret marker isi ke ujung kolom.
- Seperti yang Anda lihat, seluruh kolom diisi dengan data yang mengkarakterisasi proporsi pendapatan dari implementasi setiap produk. Tetapi nilai gravitasi spesifik ditampilkan dalam format numerik, dan kita perlu mengubahnya menjadi persentase. Untuk melakukan ini, sorot isi kolom "Berat Spesifik". Kemudian kami pindah ke tab "Home". Pada rekaman di grup Pengaturan Grup ada bidang yang menampilkan format data. Secara default, jika Anda tidak menghasilkan manipulasi tambahan, format "Umum" harus diinstal di sana. Klik pada ikon dalam bentuk segitiga yang terletak di sebelah kanan bidang ini. Dalam daftar format, pilih posisi "Persentase".
- Seperti yang kita lihat, semua nilai kolom ditransformasikan menjadi nilai persentase. Sebagaimana mestinya, 100% ditunjukkan dalam string "total". Bagian barang diharapkan di kolom dari yang lebih besar ke yang lebih kecil.
- Sekarang kita harus membuat kolom di mana akumulasi berbagi dengan hasil yang tumbuh akan ditampilkan. Artinya, di setiap baris dengan berat spesifik individu dari produk tertentu akan ditambahkan proporsi semua produk yang terletak di daftar di atas. Untuk barang pertama dalam daftar (produk 3), proporsi individu dan pangsa akumulasi akan sama, tetapi semua setelah indikator individu perlu menambahkan akumulasi bagian dari elemen sebelumnya dari daftar sebelumnya.
Jadi, pada baris pertama kami ditransfer ke kolom "Akumulasi Bagikan" indikator kolom "spesifik".
- Selanjutnya, atur kursor ke sel kedua kolom "Akumulasi Bagikan". Di sini kita harus menerapkan rumus. Kami menempatkan tanda "sama" dan lipat konten sel "proporsi" dari garis yang sama dan isi sel "akumulasi pangsa" dari string di atas. Semua referensi dicadangkan relatif, yaitu, kami tidak menghasilkan manipulasi apa pun dengan mereka. Setelah itu, lakukan klik pada tombol Enter untuk menampilkan hasil akhir.
- Sekarang Anda perlu menyalin rumus ini di sel-sel kolom ini, yang ditempatkan di bawah ini. Untuk melakukan ini, kami menggunakan penanda pengisian yang telah kami pilih saat menyalin rumus di kolom Bagikan. Pada saat yang sama, string "Total" tidak diperlukan, karena akumulasi hasil 100% akan ditampilkan pada produk terakhir dari daftar. Seperti yang Anda lihat, semua elemen kolom kami setelah itu diisi.
- Setelah itu, buat kolom "grup". Kita perlu mengelompokkan barang berdasarkan kategori A, B dan C sesuai dengan akumulasi saham yang ditentukan. Seperti yang kita ingat, semua elemen didistribusikan oleh kelompok sesuai dengan skema berikut:
- A - hingga 80%;
- B - 15% berikut;
- C - sisanya 5%.
Dengan demikian, semua barang yang mengumpulkan bagian dari bobot spesifik yang ada di dalam batas hingga 80%, kami menetapkan kategori A. Barang dengan akumulasi berat spesifik dari 80% hingga 95% ditugaskan untuk kategori B. Sisa sekelompok barang dengan nilai lebih dari 95% dari akumulasi gravitasi spesifik kami menetapkan kategori C.
- Untuk kejelasan, Anda dapat mengisi grup yang ditentukan dengan warna yang berbeda. Tapi itu sesuka hati.
Dengan demikian, kami melanggar unsur-unsur pada tingkat kepentingan, menggunakan analisis ABC. Saat menggunakan beberapa teknik lain, seperti yang disebutkan di atas, digunakan untuk membobol jumlah kelompok yang lebih besar, tetapi prinsip putus dalam hal ini tetap hampir tidak berubah.
Pelajaran: Menyortir dan memfilter di Excel
Metode 2: Menggunakan rumus kompleks
Tentu saja, penggunaan penyortiran adalah cara paling umum untuk melakukan analisis ABC di Excele. Tetapi dalam beberapa kasus perlu untuk melakukan analisis ini tanpa mengatur ulang garis di tempat-tempat di tabel sumber. Dalam hal ini, formula kompleks akan datang untuk menyelamatkan. Misalnya, kami akan menggunakan tabel sumber yang sama seperti pada kasus pertama.
- Kami menambah tabel sumber yang berisi nama barang dan pendapatan dari penjualan masing-masing, kolom "grup". Seperti yang Anda lihat, dalam hal ini, kami mungkin tidak menambahkan kolom dengan perhitungan fraksi individu dan akumulatif.
- Kami memproduksi alokasi sel pertama di kolom grup, setelah itu Anda melakukan klik pada tombol "Sisipkan Fungsi" yang terletak di dekat baris rumus.
- Aktivasi master fungsi. Kami pindah ke kategori "tautan dan array". Pilih fungsi "Pilihan". Kami membuat klik pada tombol "OK".
- Jendela argumen permainan diaktifkan. Sintaks disajikan sebagai berikut:
= Seleksi (Number_intex; Nilai1; Nilai2; ...)
Tugas fitur ini adalah penarikan salah satu dari nilai yang ditentukan, tergantung pada nomor indeks. Jumlah nilai dapat mencapai 254, tetapi kita hanya perlu tiga nama yang sesuai dengan kategori ABC-analisis: A, B, C. Kita dapat segera memasukkan simbol "A" di bidang "B" di "B" "Lapangan, bidang" Nilai3 "-" C ".
- Tetapi dengan argumen "nomor indeks" harus benar-benar terikat dengan membangun beberapa operator tambahan. Instal kursor di bidang "Nomor Indeks". Selanjutnya, klik ikon dengan tampilan segitiga, di sebelah kiri tombol "Sisipkan Fungsi". Daftar operator yang baru digunakan dibuka. Kami membutuhkan fungsi pencarian. Karena tidak ada dalam daftar, maka kita klik pada prasasti "Fungsi lain ...".
- Jendela jendela fungsi wizard dimulai lagi. Sekali lagi, buka kategori "tautan dan array". Kami menemukan posisi "board search", mengalokasikannya dan membuat klik pada tombol "OK".
- Argumen dari argumen operator pencarian terbuka. Sintaksnya memiliki bentuk berikut:
= Board pencarian (search_name; melihat__masif; type_station)
Tujuan dari fungsi ini adalah untuk menentukan jumlah posisi dari item yang ditentukan. Artinya, hanya apa yang kita butuhkan untuk bidang "Nomor Indeks" Fitur pilihan.
Di bidang "Listing Array", Anda dapat segera menanyakan ekspresi berikut:
{0: 0.8: 0,95}
Seharusnya persis dalam tanda kurung keriting, sebagai formula array. Tidak sulit untuk menebak bahwa angka-angka ini (0; 0,8; 0,95) menunjukkan batas-batas akumulasi saham antar kelompok.
Bidang "jenis perbandingan" tidak wajib dan dalam hal ini kami tidak akan mengisinya.
Di bidang "Nilai Kedua", atur kursor. Selanjutnya lagi melalui ikon yang dijelaskan di atas dalam bentuk segitiga, kami pindah ke wizard dari fungsi.
- Kali ini dalam fungsi Wizard, kami pindah ke kategori "matematika". Pilih nama "Silent" dan tekan tombol "OK".
- Jendela argumen fungsi akan diluncurkan. Operator yang ditentukan merangkum sel-sel yang memenuhi kondisi pasti. Sintaksnya adalah:
= Diam (rentang; kriteria; range_suming)
Di bidang "Rentang", masukkan alamat kolom "Pendapatan". Untuk tujuan ini, kami mengatur kursor di lapangan, dan kemudian dengan membuat klip tombol kiri mouse, pilih semua sel kolom yang sesuai, tidak termasuk nilai "Total". Seperti yang Anda lihat, alamatnya segera muncul di lapangan. Selain itu, kita perlu membuat tautan ini absolut. Untuk melakukan ini, produksi alokasi dan klik pada tombol F4. Alamat ini dirilis oleh tanda dolar.
Dalam bidang "Kriteria", kita perlu menetapkan suatu kondisi. Masukkan ekspresi berikut:
">"&
Kemudian segera setelah kami memasukkan alamat sel pertama kolom "Pendapatan". Kami membuat koordinat secara horizontal dalam alamat ini absolut, menambahkan tanda dolar dari keyboard di depan huruf. Koordinat vertikal meninggalkan relatif, yaitu, seharusnya tidak ada tanda di depan digit.
Setelah itu, kami tidak menekan tombol "OK", dan klik pada nama fungsi pencarian di string rumus.
- Kemudian kami kembali ke jendela argumen dari papan pencarian. Seperti yang kita lihat, di bidang "Makna Fogular", data yang ditetapkan oleh operator terdiam. Tapi itu tidak semua. Pergi ke bidang ini dan sudah menambah data yang tersedia untuk menambahkan tanda "+" tanpa tanda kutip. Kemudian kami memperkenalkan alamat sel pertama kolom "Pendapatan". Dan sekali lagi kita membuat koordinat secara horizontal dengan absolut, dan secara vertikal meninggalkan relatif.
Selanjutnya, kami mengambil semua isi bidang "nilai yang diinginkan" dalam tanda kurung, setelah itu kami meletakkan tanda divisi ("/"). Setelah itu, lagi melalui ikon segitiga, buka fungsi pemilihan fungsi.
- Seperti terakhir kali di Wisaya Fungsi Running, kami mencari operator yang diperlukan dalam kategori "Matematika". Kali ini fungsi yang diinginkan disebut "jumlah". Kami menyorotnya dan tekan tombol "OK".
- Jendela argumen operator terbuka. Tujuan utamanya adalah penjumlahan data dalam sel. Sintaks operator ini cukup sederhana:
= Jumlah (Number1; Number2; ...)
Untuk tujuan kami, hanya bidang "Number1" yang akan dibutuhkan. Kami memperkenalkannya koordinat kolom "Pendapatan", menghilangkan sel yang berisi hasil. Kami telah melakukan operasi seperti itu di bidang fungsi "rentang". Saat pada waktu itu, koordinat rentang mutlak, menyoroti mereka dan menekan tombol F4.
Setelah itu, klik tombol "OK" di bagian bawah jendela.
- Seperti yang Anda lihat, kompleks fungsi yang dimasukkan membuat perhitungan dan mengeluarkan hasil pada sel pertama kolom "grup". Produk pertama diberi kelompok "A". Formula penuh yang diterapkan oleh kami untuk perhitungan ini adalah sebagai berikut:
= Pilihan (board pencarian ((diam ($ b $ 2: $ b $ 27; ">" & $ b2) + $ b2) / jumlah ($ B2: $ b $ 27); {0: 0.8: 0: 0 , 95}); "A"; "B"; "C")
Tapi, tentu saja, dalam setiap kasus, koordinat dalam rumus ini akan berbeda. Oleh karena itu, itu tidak dapat dianggap universal. Tetapi, menggunakan manual yang diberikan di atas, Anda dapat memasukkan koordinat dari tabel apa pun dan berhasil menerapkan metode ini dalam situasi apa pun.
- Namun, ini tidak semuanya. Kami dihitung hanya untuk baris pertama tabel. Untuk mengisi sepenuhnya kolom "grup" grup, Anda perlu menyalin rumus ini di kisaran di bawah ini (menghilangkan sel string "total") menggunakan penanda pengisian, seperti yang telah kita lakukan lebih dari sekali. Setelah data dilakukan, analisis ABC dapat dipertimbangkan dilakukan.
Seperti yang kita lihat, hasil yang diperoleh dengan menggunakan opsi menggunakan rumus kompleks tidak berbeda dengan hasil yang kami lakukan dengan menyortir. Semua barang ditugaskan kategori yang sama, hanya pada saat yang sama, baris tidak mengubah posisi awal mereka.
Pelajaran: Master Fungsi di Excele
Program Excel dapat secara signifikan mengurangi analisis ABC untuk pengguna. Ini dicapai dengan menggunakan alat seperti menyortir. Setelah itu, gravitasi spesifik individu, akumulasi pangsa dan, sebenarnya, partisi ke dalam kelompok dihitung. Dalam kasus di mana perubahan pada posisi awal baris dalam tabel tidak diperbolehkan, Anda dapat menerapkan metode menggunakan rumus yang kompleks.