Lookup, Grafik, Statistik, dan Tabel Pivot

2024 Pengarang: Geoffrey Carr | [email protected]. Terakhir diubah: 2023-12-17 10:58

Setelah meninjau fungsi dasar, referensi sel, dan fungsi tanggal dan waktu, kita sekarang menyelam ke beberapa fitur yang lebih canggih dari Microsoft Excel. Kami menyajikan metode untuk memecahkan masalah klasik dalam keuangan, laporan penjualan, biaya pengiriman, dan statistik.

NAVIGASI SEKOLAH

Mengapa Anda Perlu Rumus dan Fungsi?
Mendefinisikan dan Membuat Rumus
Referensi Sel Relatif dan Absolut, dan Format
Fungsi Berguna yang Harus Anda Ketahui
Lookup, Grafik, Statistik, dan Tabel Pivot

Fungsi-fungsi ini penting untuk bisnis, siswa, dan mereka yang hanya ingin belajar lebih banyak.

VLOOKUP dan HLOOKUP

Berikut ini contoh untuk menggambarkan fungsi pencarian vertikal (VLOOKUP) dan pencarian horizontal (HLOOKUP). Fungsi-fungsi ini digunakan untuk menerjemahkan angka atau nilai lain menjadi sesuatu yang dapat dimengerti. Misalnya, Anda dapat menggunakan VLOOKUP untuk mengambil nomor bagian dan mengembalikan deskripsi item.

Untuk menyelidiki ini, mari kembali ke spreadsheet “Pengambil Keputusan” kami di Bagian 4, di mana Jane mencoba memutuskan apa yang akan dikenakan ke sekolah. Dia tidak lagi tertarik dengan apa yang dia kenakan, karena dia telah memiliki pacar baru, jadi dia sekarang akan memakai pakaian acak dan sepatu.

Dalam spreadsheet Jane, dia mencantumkan pakaian dalam kolom dan sepatu vertikal, kolom horizontal.

Dia membuka spreadsheet dan fungsi RANDBETWEEN (1,3) menghasilkan angka antara atau sama dengan satu dan tiga yang sesuai dengan tiga jenis pakaian yang bisa dia pakai.

Dia menggunakan fungsi RANDBETWEEN (1,5) untuk memilih di antara lima jenis sepatu.

Karena Jane tidak bisa memakai nomor, kami perlu mengonversi ini menjadi nama, jadi kami menggunakan fungsi pencarian.

Kami menggunakan fungsi VLOOKUP untuk menerjemahkan nomor pakaian ke nama pakaian. HLOOKUP diterjemahkan dari nomor sepatu ke berbagai jenis sepatu di baris.

Spreadsheet berfungsi seperti ini untuk pakaian:

Excel mengambil nomor acak dari satu hingga tiga, karena dia memiliki tiga pilihan pakaian.

Selanjutnya rumus menerjemahkan angka ke teks menggunakan = VLOOKUP (B11, A2: B4,2) yang menggunakan angka acak nilai dari B11 untuk melihat dalam rentang A2: B4. Ini kemudian memberikan hasil (C11) dari data yang tercantum di kolom kedua.

Kami menggunakan teknik yang sama untuk memilih sepatu, kecuali kali ini kami menggunakan VOOKUP sebagai ganti HLOOKUP.

Contoh: Statistik Dasar

Hampir semua orang tahu satu rumus dari statistik - rata-rata - tetapi ada statistik lain yang penting untuk bisnis: standar deviasi.

Sebagai contoh, banyak orang yang telah pergi ke perguruan tinggi telah menderita atas nilai SAT mereka. Mereka mungkin ingin tahu bagaimana peringkat mereka dibandingkan dengan siswa lain. Universitas ingin mengetahui hal ini juga karena banyak universitas, terutama yang bergengsi, menolak siswa dengan nilai SAT rendah.

Jadi bagaimana kita, atau universitas, mengukur dan menafsirkan nilai SAT? Di bawah ini adalah skor SAT untuk lima siswa mulai dari 1.870 hingga 2.230.

Angka-angka penting untuk dipahami adalah:

Rata-rata - Rata-rata juga disebut sebagai "mean."

Standar Deviasi (STD atau σ) - Angka ini menunjukkan seberapa luas jumlah satuan yang tersebar. Jika standar deviasinya besar, maka jumlahnya jauh dan jika nol, semua angka sama. Anda dapat mengatakan bahwa standar deviasi adalah perbedaan rata-rata antara nilai rata-rata dan nilai yang diamati, yaitu 1.998 dan setiap skor SAT. Harap dicatat, itu adalah umum untuk menyingkat standar deviasi menggunakan simbol sigma Yunani “σ.”

Peringkat Persentil - Ketika seorang siswa menerima nilai tinggi, mereka dapat menyombongkan diri bahwa mereka berada di atas 99 persen atau sesuatu seperti itu. "Percentile rank" berarti persentase skor lebih rendah dari satu skor tertentu.

Standar deviasi dan probabilitas terkait erat. Anda dapat mengatakan bahwa untuk setiap deviasi standar, probabilitas atau kemungkinan bahwa angka tersebut berada di dalam jumlah standar deviasi adalah:

STD	Persentase skor	Rentang skor SAT
1	68%	1,854-2,142
2	95%	1,711-2,285
3	99.73%	1,567-2,429
4	99.994%	1,424-2,572

Seperti yang Anda lihat, kemungkinan skor SAT di luar 3 STD hampir nol, karena 99,73 persen skor berada dalam 3 STD.

Sekarang mari kita lihat lagi spreadsheet dan menjelaskan cara kerjanya.

= AVERAGE (B2: B6)

Rata-rata semua nilai di atas rentang B2: B6. Secara khusus, jumlah semua nilai dibagi dengan jumlah orang yang mengikuti tes.

= STDEV.P (B2: B6)

Standar deviasi di atas rentang B2: B6. ".P" berarti STDEV.P digunakan di semua skor, yaitu seluruh populasi dan bukan hanya subkumpulan.

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)

Ini menghitung persentase kumulatif atas rentang B2: B6 berdasarkan skor SAT, dalam hal ini B2. Misalnya, 83 persen skor di bawah skor Walker.

Menghasilkan Hasil

Menempatkan hasil dalam grafik membuatnya lebih mudah untuk memahami hasilnya, ditambah Anda dapat menunjukkannya dalam presentasi untuk membuat poin Anda lebih jelas.

Siswa berada pada sumbu horizontal dan skor SAT mereka ditampilkan sebagai grafik batang biru pada skala (sumbu vertikal) dari 1.600 hingga 2.300.

Peringkat persentil adalah sumbu vertikal kanan dari 0 hingga 90 persen, dan diwakili oleh garis abu-abu.

Cara Membuat Bagan

Membuat grafik adalah topik tersendiri, namun kami akan menjelaskan secara singkat bagaimana bagan di atas dibuat.

Pertama, pilih rentang sel yang ada di bagan. Dalam hal ini A2 ke C6 karena kami menginginkan nomor dan juga nama siswa.

Dari menu “Insert” pilih “Charts” -> “Recommended Charts”:

Komputer merekomendasikan bagan “Kolom Terklus, Kolom Sekunder”. Bagian “Poros Sekunder” berarti menggambar dua sumbu vertikal. Dalam hal ini, bagan ini adalah yang kita inginkan. Kami tidak perlu melakukan hal lain.

Anda dapat menggunakan memindahkan bagan di sekitar dan mengukur ulang hingga Anda memilikinya sebagai ukuran dan posisi yang Anda inginkan. Setelah puas, Anda dapat menyimpan bagan di spreadsheet.

Jika Anda mengklik kanan grafik, lalu “Pilih Data,” ini menunjukkan kepada Anda data apa yang dipilih untuk rentang tersebut.

Fitur "Fitur Grafik" biasanya menolong Anda keluar dari keharusan untuk berurusan dengan rincian rumit seperti menentukan data apa yang harus dimasukkan, bagaimana menetapkan label, dan bagaimana menetapkan sumbu vertikal kiri dan kanan.

Dalam dialog "Pilih Sumber Data", klik "skor" di bawah "Entri Legendaris (Seri)" dan tekan "Edit," dan ubah untuk mengatakan "Skor."

Kemudian ubah seri 2 ("persentil") menjadi "Persentil."

Kembali ke grafik Anda dan klik pada "Judul Bagan" dan ubah ke "Skor SAT." Sekarang kami memiliki bagan lengkap. Ini memiliki dua sumbu horisontal: satu untuk skor SAT (biru) dan satu untuk persentase kumulatif (oranye).

Contoh: Masalah Transportasi

Masalah transportasi adalah contoh klasik dari jenis matematika yang disebut "linear programming." Ini memungkinkan Anda memaksimalkan atau meminimalkan nilai yang tunduk pada batasan tertentu. Ini memiliki banyak aplikasi untuk beragam masalah bisnis, sehingga berguna untuk mempelajari cara kerjanya.

Sebelum kita memulai dengan contoh ini kita harus mengaktifkan "Excel Solver."

Aktifkan Solver Add-In

Pilih "File" -> "Options" -> "Add-ins". Di bagian bawah opsi add-ins, klik tombol "Go" di samping "Kelola: Excel Add-ins."

Contoh: Hitung Biaya Pengiriman iPad Terendah

Misalkan kita pengiriman iPads dan kami mencoba untuk mengisi pusat distribusi kami menggunakan biaya transportasi terendah mungkin. Kami memiliki perjanjian dengan perusahaan angkutan dan maskapai penerbangan untuk mengirimkan iPad dari Shanghai, Beijing, dan Hong Kong ke pusat distribusi yang ditunjukkan di bawah ini.

Harga untuk mengirim setiap iPad adalah jarak dari pabrik ke pusat distribusi ke pabrik dibagi dengan 20.000 kilometer. Misalnya, 8,024 km dari Shanghai ke Melbourne yaitu 8,024 / 20,000 atau $.40 per iPad.

Pertanyaannya adalah bagaimana kita mengirimkan semua iPad ini dari ketiga pabrik ini ke empat tujuan dengan biaya serendah mungkin?

Seperti yang Anda bayangkan, mencari tahu ini bisa sangat sulit tanpa formula dan alat. Dalam hal ini kami harus mengirimkan 462.000 (F12) total iPad. Pabrik memiliki kapasitas terbatas sebesar 500.250 (G12) unit.

Dalam spreadsheet, sehingga Anda dapat melihat cara kerjanya, kami telah mengetik 1 ke sel B10 yang berarti kami ingin mengirimkan 1 iPad dari Shanghai ke Melbourne. Karena biaya transportasi di sepanjang rute tersebut adalah $ 0,40 per iPad, biaya total (B17) adalah $ 0,40.

Jumlah dihitung menggunakan fungsi = SUMPRODUCT (biaya, dikirim) "biaya" adalah rentang B3: E5.

SUMPRODUCT mengalikan “biaya” dikali kisaran “dikirim” (B14). Itu disebut "perkalian matriks."

Agar SUMPRODUCT berfungsi dengan benar, kedua matrik - biaya dan pengiriman - harus berukuran sama. Anda dapat mengatasi keterbatasan ini dengan membuat biaya tambahan dan kolom pengiriman dan baris dengan nilai nol sehingga array adalah ukuran yang sama dan tidak ada dampak pada total biaya.

Menggunakan Solver

Jika yang harus kami lakukan adalah melipatgandakan matriks "biaya" kali "dikirim" yang tidak akan terlalu rumit, tetapi kita harus menghadapi kendala di sana juga.

Kami harus mengirimkan apa yang dibutuhkan setiap pusat distribusi. Kami memasukkan konstanta itu ke pemecah seperti ini: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ini berarti jumlah dari yang dikirim, yaitu, total dalam sel $ B $ 12: $ E $ 12, harus lebih besar dari atau sama dengan yang dibutuhkan setiap pusat distribusi ($ B $ 13: $ E $ 13).

Kami tidak dapat mengirim lebih dari yang kami hasilkan. Kami menulis batasan seperti ini: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Dengan kata lain, apa yang kami kirimkan dari setiap tanaman $ F $ 9: $ F $ 11 tidak dapat melebihi (harus kurang dari atau sama dengan) kapasitas setiap tanaman: $ G $ 9: $ G $ 11.

Sekarang, masuklah ke menu “Data” dan tekan tombol “Solver”. Jika tombol "Solver" tidak ada di sana, Anda harus mengaktifkan add-in Solver.

Ketik dua kendala yang dijelaskan sebelumnya dan pilih rentang "Pengiriman", yang merupakan kisaran angka yang kami inginkan untuk dihitung Excel. Juga memilih algoritma default "Simplex LP" dan menunjukkan bahwa kita ingin "meminimalkan" sel B15 ("biaya pengiriman total"), di mana dikatakan "Set Objective."

Tekan "Selesaikan" dan Excel menyimpan hasilnya ke dalam spreadsheet, yang kami inginkan.Anda juga dapat menyimpan ini sehingga Anda dapat bermain-main dengan skenario lain.

Jika komputer mengatakan tidak dapat menemukan solusi, maka Anda telah melakukan sesuatu yang tidak logis, misalnya, Anda mungkin telah meminta lebih banyak iPads daripada yang bisa dihasilkan oleh tanaman.

Di sini Excel mengatakan bahwa ia menemukan solusi. Tekan "OK" untuk menyimpan solusi dan kembali ke spreadsheet.

Contoh: Nilai Sekarang Bersih

Bagaimana sebuah perusahaan memutuskan apakah akan berinvestasi dalam proyek baru? Jika "nilai sekarang bersih" (NPV) positif, mereka akan berinvestasi di dalamnya. Ini adalah pendekatan standar yang diambil oleh sebagian besar analis keuangan.

Misalnya, perusahaan pertambangan Codelco ingin memperluas tambang tembaga Andinas. Pendekatan standar untuk menentukan apakah akan bergerak maju dengan proyek adalah menghitung nilai bersih sekarang. Jika NPV lebih besar dari nol, maka proyek akan menguntungkan diberikan dua input (1) waktu dan (2) biaya modal.

Dalam bahasa Inggris, biaya modal berarti berapa banyak uang yang akan diperoleh jika mereka hanya meninggalkannya di bank. Anda menggunakan biaya modal untuk mendiskon nilai tunai ke nilai sekarang, dengan kata lain $ 100 dalam lima tahun mungkin menjadi $ 80 hari ini.

Pada tahun pertama, $ 45 juta disisihkan sebagai modal untuk membiayai proyek tersebut. Para akuntan telah menentukan bahwa biaya modal mereka adalah enam persen.

Ketika mereka mulai menambang, uang tunai mulai masuk ketika perusahaan menemukan dan menjual tembaga yang mereka hasilkan. Tentunya, semakin banyak mereka menambang, semakin banyak uang yang mereka hasilkan, dan perkiraan mereka menunjukkan arus kas mereka meningkat hingga mencapai $ 9 juta per tahun.

Setelah 13 tahun, NPV adalah $ 3,945,074 USD, sehingga proyek ini akan menguntungkan. Menurut analis keuangan, "periode pembayaran kembali" adalah 13 tahun.

Membuat Tabel Pivot

Sebuah "tabel pivot" pada dasarnya adalah sebuah laporan. Kami menyebutnya tabel pivot karena Anda dapat dengan mudah mengalihkan mereka satu jenis laporan ke yang lain tanpa harus membuat laporan baru. Jadi mereka poros di tempat. Mari tunjukkan contoh dasar yang mengajarkan konsep dasar.

Contoh: Laporan Penjualan

Staf penjualan sangat kompetitif (itu bagian dari menjadi penjual) sehingga mereka ingin tahu bagaimana mereka bersaing satu sama lain di akhir kuartal dan akhir tahun, plus berapa banyak komisi mereka nantinya.

Misalkan kita memiliki tiga orang penjual - Carlos, Fred, dan Julie - semuanya menjual minyak. Penjualan mereka dalam dolar per kuartal fiskal untuk tahun 2014 ditunjukkan dalam spreadsheet di bawah ini.

Untuk menghasilkan laporan ini, kami membuat tabel pivot:

Pilih "Sisipkan -> Tabel Pivot, itu ada di sisi kiri bilah alat:

Pilih semua baris dan kolom (termasuk nama penjual) seperti ditunjukkan di bawah ini:

Kotak dialog pivot table muncul ke sisi kanan spreadsheet.

Jika kita mengklik keempat bidang di kotak dialog pivot table (Quarter, Year, Sales, dan Salesperson) Excel menambahkan laporan ke spreadsheet yang tidak masuk akal, tapi mengapa?

Seperti yang Anda lihat, kami telah memilih keempat bidang untuk ditambahkan ke laporan. Perilaku default Excel adalah mengelompokkan baris menurut kolom teks dan kemudian menjumlahkan seluruh baris lainnya.

Di sini ia memberi kita jumlah tahun 2014 + 2014 + 2014 + 2014 = 24.168, yang tidak masuk akal. Juga yang diberikan adalah jumlah kuartal 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Kami tidak memerlukan informasi ini, jadi kami tidak memilih bidang ini untuk menghapusnya dari tabel pivot kami.

"Jumlah Penjualan" (penjualan total) terkait, jadi kami akan memperbaikinya.

Contoh: Penjualan oleh Salesman

Anda dapat mengedit “Jumlah Penjualan” itu untuk mengatakan “Total Penjualan,” yang lebih jelas. Juga, Anda dapat memformat sel sebagai mata uang seperti Anda akan memformat sel lain. Klik pertama pada “Jumlah Penjualan” dan pilih “Pengaturan Nilai Bidang.”

Pada dialog yang dihasilkan, kami mengubah nama menjadi "Total Penjualan" lalu mengklik "Format Angka" dan mengubahnya menjadi "Mata Uang."

Anda kemudian dapat melihat hasil karya Anda di tabel pivot:

Contoh: Penjualan oleh Salesman dan Quarter

Sekarang mari tambahkan subtotal untuk setiap kuartal. Untuk menambahkan subtotal cukup klik kiri pada bidang “Kuartal” dan tahan dan seret ke bagian “baris”. Anda dapat melihat hasilnya pada gambar di bawah ini:

Selagi kita melakukannya, mari kita hapus nilai “Sum of Quarter”. Cukup klik tanda panah dan klik "Hapus Bidang." Di tangkapan layar, Anda sekarang dapat melihat kami telah menambahkan baris "Kuartal", yang mengelompokkan setiap penjualan penjualan per kuartal.

Dengan keterampilan ini segar dalam pikiran, Anda sekarang dapat membuat tabel pivot dari data Anda sendiri!

Kesimpulan

Dengan membungkus, kami telah menunjukkan kepada Anda beberapa fitur formula dan fungsi Microsoft Excel yang dapat Anda terapkan Microsoft Excel untuk bisnis, akademik, atau kebutuhan lainnya.

Seperti yang Anda lihat, Microsoft Excel adalah produk luar biasa dengan begitu banyak fitur yang kebanyakan orang, bahkan pengguna tingkat lanjut, tidak tahu semuanya. Beberapa orang mungkin mengatakan itu membuatnya rumit; kami merasa ini lebih komprehensif.

Mudah-mudahan, dengan menghadirkan banyak contoh kehidupan nyata, kami telah menunjukkan tidak hanya fungsi yang tersedia di Microsoft Excel tetapi telah mengajarkan Anda sesuatu tentang statistik, pemrograman linier, membuat bagan, menggunakan angka acak, dan ide lain yang sekarang dapat Anda adopsi dan gunakan di sekolah Anda atau di mana Anda bekerja.

Ingat, jika Anda ingin kembali dan mengambil kelas lagi, Anda dapat memulai dengan Pelajaran 1!