Pencarian, Grafik, Statistik, dan Tabel Pivot
Setelah meninjau fungsi dasar, referensi sel, dan fungsi tanggal dan waktu, kami sekarang menyelami beberapa fitur Microsoft Excel yang lebih canggih. Kami menyajikan metode untuk menyelesaikan masalah klasik dalam keuangan, laporan penjualan, biaya pengiriman, dan statistik.
NAVIGASI SEKOLAH- Mengapa Anda Membutuhkan Formula dan Fungsi?
- Mendefinisikan dan Membuat Formula
- Referensi Sel Relatif dan Mutlak, dan Pemformatan
- Fungsi Berguna Yang Harus Anda Ketahui
- Pencarian, Grafik, Statistik, dan Tabel Pivot
Fungsi-fungsi ini penting untuk bisnis, siswa, dan mereka yang hanya ingin belajar lebih banyak.
VLOOKUP dan HLOOKUP
Berikut adalah contoh untuk menggambarkan fungsi pencarian vertikal (VLOOKUP) dan pencarian horisontal (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 kita kembali ke spreadsheet "Pengambil Keputusan" 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 mendapatkan pacar baru, jadi dia sekarang akan memakai pakaian dan sepatu acak.
Dalam lembar bentang Jane, ia 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 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 dapat memakai nomor, kami perlu mengonversinya 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 memilih nomor acak dari satu hingga tiga, karena ia memiliki tiga opsi 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. Itu 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 alih-alih 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 kuliah merasa sedih atas skor SAT mereka. Mereka mungkin ingin tahu bagaimana peringkat mereka dibandingkan dengan siswa lain. Universitas juga ingin mengetahui hal ini karena banyak universitas, terutama yang bergengsi, menolak siswa dengan nilai SAT yang rendah.
Jadi bagaimana kita, atau universitas, mengukur dan menafsirkan skor SAT? Di bawah ini adalah nilai SAT untuk lima siswa mulai dari 1.870 hingga 2.230.
Angka-angka penting untuk dipahami adalah:
Rata-rata - Rata-rata juga disebut sebagai "rata-rata."
Standar Deviasi (STD atau σ) - Angka ini menunjukkan seberapa luas sebaran angka. Jika standar deviasi besar, maka angkanya berjauhan dan jika nol, semua angkanya sama. Anda bisa 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, adalah umum untuk menyingkat standar deviasi menggunakan simbol Yunani sigma "σ."
Peringkat Persentil - Ketika seorang siswa menerima skor tinggi, mereka dapat menyombongkan diri bahwa mereka berada dalam 99 persen teratas atau sesuatu seperti itu. “Peringkat persentil” berarti persentase skor lebih rendah dari satu skor tertentu.
Standar deviasi dan probabilitas terkait erat. Anda dapat mengatakan bahwa untuk setiap standar deviasi, probabilitas atau kemungkinan bahwa angka itu ada di dalam jumlah standar deviasi itu adalah:
| STD | Persentase skor | Rentang nilai 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, peluang bahwa skor SAT di luar 3 STD praktis nol, karena 99,73 persen dari skor berada dalam 3 STD.
Sekarang mari kita lihat lagi spreadsheet dan jelaskan cara kerjanya.
Sekarang kami jelaskan rumusnya:
= AVERAGE (B2: B6)
Rata-rata semua skor pada rentang B2: B6. Secara khusus, jumlah semua skor dibagi dengan jumlah orang yang mengikuti tes.
= STDEV.P (B2: B6)
Simpangan baku pada rentang B2: B6. “.P” berarti STDEV.P digunakan untuk semua skor, mis., Seluruh populasi dan bukan hanya sebagian.
= 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 berada di bawah skor Walker.
Grafik Hasil
Menempatkan hasil dalam grafik membuatnya lebih mudah untuk memahami hasil, ditambah Anda dapat menunjukkannya dalam presentasi untuk membuat poin Anda lebih jelas.
Siswa berada pada sumbu horizontal dan skor SAT mereka ditampilkan sebagai grafik balok biru pada skala (sumbu vertikal) dari 1.600 ke 2.300.
Peringkat persentil adalah sumbu vertikal kanan dari 0 hingga 90 persen, dan diwakili oleh garis abu-abu.
Cara Membuat Bagan
Membuat bagan adalah topik tersendiri, namun kami akan menjelaskan secara singkat bagaimana bagan di atas dibuat.
Pertama, pilih rentang sel yang akan di bagan. Dalam hal ini A2 hingga C6 karena kami menginginkan angka serta nama siswa.
Dari menu "Insert" pilih "Charts" -> "Recommended Charts":
Komputer merekomendasikan grafik "Clustered-Column, Secondary Axis". Bagian "Sumbu Sekunder" artinya menarik dua sumbu vertikal. Dalam hal ini, bagan ini adalah yang kita inginkan. Kami tidak harus melakukan hal lain.
Anda dapat menggunakan gerakkan bagan di sekitar dan ukur kembali sampai Anda memilikinya sebagai ukuran dan di posisi yang Anda inginkan. Setelah puas, Anda dapat menyimpan grafik di spreadsheet.
Jika Anda mengklik kanan bagan, maka "Pilih Data," itu menunjukkan kepada Anda data apa yang dipilih untuk rentang tersebut.
Fitur "Bagan yang Dianjurkan" biasanya membantu Anda keluar dari keharusan berurusan dengan perincian rumit seperti menentukan data apa yang akan dimasukkan, cara menetapkan label, dan cara menetapkan sumbu vertikal kiri dan kanan.
Dalam dialog "Pilih Sumber Data", klik "skor" di bawah "Entri Legenda (Seri)" dan tekan "Edit," dan ubah untuk mengatakan "Skor."
Kemudian ubah seri 2 ("persentil") menjadi "Persentil."
Kembali ke bagan Anda dan klik "Judul Bagan" dan ubah menjadi "SAT Skor." Sekarang kami memiliki bagan lengkap. Ini memiliki dua sumbu horizontal: satu untuk skor SAT (biru) dan satu untuk persentase kumulatif (oranye).
Contoh: Masalah Transportasi
Masalah transportasi adalah contoh klasik dari jenis matematika yang disebut "pemrograman linier." Ini memungkinkan Anda memaksimalkan atau meminimalkan nilai yang tunduk pada kendala tertentu. Ini memiliki banyak aplikasi untuk berbagai masalah bisnis, jadi sangat berguna untuk mempelajari cara kerjanya.
Sebelum kita mulai dengan contoh ini, kita harus mengaktifkan "Excel Solver."
Aktifkan Add-In Solver
Pilih "File" -> "Opsi" -> "Add-in". Di bagian bawah opsi add-in, klik tombol "Pergi" di sebelah "Kelola: Add-in Excel."
Pada menu yang dihasilkan, klik kotak centang untuk mengaktifkan, "Solver Add-in," dan klik "OK."
Contoh: Hitung Biaya Pengiriman iPad Terendah
Misalkan kita mengirimkan iPad dan kami mencoba mengisi pusat distribusi kami menggunakan biaya transportasi serendah mungkin. Kami memiliki perjanjian dengan perusahaan angkutan truk dan maskapai untuk mengirimkan iPad dari Shanghai, Beijing, dan Hong Kong ke pusat distribusi yang ditunjukkan di bawah ini..
Harga untuk mengirimkan setiap iPad adalah jarak dari pabrik ke pusat distribusi ke pabrik dibagi 20.000 kilometer. Sebagai contoh, 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 tiga pabrik ini ke empat tujuan dengan biaya serendah mungkin?
Seperti yang dapat 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 500.250 (G12) unit.
Di 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 itu adalah $ 0,40 per iPad, total biaya (B17) adalah $ 0,40.
Angka itu dihitung menggunakan fungsi = SUMPRODUCT (biaya, dikirim) "biaya" adalah rentang B3: E5.
Dan "dikirim" adalah kisaran B9: E11:
SUMPRODUCT mengalikan "biaya" kali rentang "dikirimkan" (B14). Itu disebut "perkalian matriks."
Agar SUMPRODUCT berfungsi dengan benar, kedua matriks - biaya dan pengiriman - harus berukuran sama. Anda dapat mengatasi batasan ini dengan membuat biaya tambahan dan pengiriman kolom dan baris dengan nilai nol sehingga array berukuran sama dan tidak ada dampak pada total biaya.
Menggunakan Solver
Jika semua yang harus kita lakukan adalah melipatgandakan matriks "biaya" kali "dikirim" yang tidak akan terlalu rumit, tetapi kita harus berurusan dengan kendala di sana juga.
Kami harus mengirimkan apa yang dibutuhkan setiap pusat distribusi. Kami menempatkan konstanta itu ke dalam solver seperti ini: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ini berarti jumlah dari apa yang dikirimkan, mis., Total dalam sel $ B $ 12: $ E $ 12, harus lebih besar dari atau sama dengan yang dibutuhkan oleh setiap pusat distribusi ($ B $ 13: $ E $ 13).
Kami tidak dapat mengirimkan lebih dari yang kami hasilkan. Kami menulis batasan seperti ini: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Sekarang pergi ke menu "Data" dan tekan tombol "Solver". Jika tombol "Solver" tidak ada di sana, Anda harus mengaktifkan add-in Solver.
Ketik dua kendala yang dirinci sebelumnya dan pilih rentang "Pengiriman", yang merupakan rentang angka yang ingin kita hitung Excel. Pilih juga algoritme default "Simplex LP" dan tunjukkan bahwa kami ingin "meminimalkan" sel B15 ("total biaya pengiriman"), di mana dikatakan "Tetapkan Tujuan."
Tekan "Selesaikan" dan Excel menyimpan hasilnya ke dalam spreadsheet, yang kami inginkan. Anda juga dapat menyimpan ini sehingga Anda bisa 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 iPad daripada yang dapat dihasilkan tanaman.
Di sini Excel mengatakan bahwa ia menemukan solusi. Tekan "OK" untuk menyimpan solusi dan kembali ke spreadsheet.
Contoh: Nilai Sekarang Bersih
Bagaimana cara perusahaan memutuskan apakah akan berinvestasi dalam proyek baru? Jika "net present value" (NPV) positif, mereka akan berinvestasi di dalamnya. Ini adalah pendekatan standar yang diambil oleh sebagian besar analis keuangan.
Misalnya, anggap perusahaan pertambangan Codelco ingin memperluas tambang tembaga Andinas. Pendekatan standar untuk menentukan apakah akan melanjutkan proyek adalah dengan menghitung nilai sekarang bersih. Jika NPV lebih besar dari nol, maka proyek akan menguntungkan dengan dua input (1) waktu dan (2) biaya modal.
Dalam bahasa Inggris yang sederhana, biaya modal berarti berapa banyak uang yang akan didapat jika mereka meninggalkannya di bank. Anda menggunakan biaya modal untuk mendiskontokan 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. 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. Jelas, 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 pengembalian" adalah 13 tahun.
Membuat Tabel Pivot
"Tabel pivot" pada dasarnya adalah laporan. Kami menyebutnya tabel pivot karena Anda dapat dengan mudah mengalihkannya satu jenis laporan ke yang lain tanpa harus membuat keseluruhan laporan baru. Jadi mereka poros di tempat. Mari kita tunjukkan contoh dasar yang mengajarkan konsep dasar.
Contoh: Laporan Penjualan
Tenaga penjualan sangat kompetitif (itu bagian dari menjadi tenaga penjualan) sehingga mereka secara alami ingin tahu bagaimana mereka saling berhadapan di akhir kuartal dan akhir tahun, ditambah berapa banyak komisi mereka akan.
Misalkan kita memiliki tiga tenaga penjualan - Carlos, Fred, dan Julie - semuanya menjual minyak bumi. 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, ada di sebelah kiri bilah alat:
Pilih semua baris dan kolom (termasuk nama salesman) seperti yang ditunjukkan di bawah ini:
Kotak dialog tabel pivot muncul di sebelah kanan spreadsheet.
Jika kita mengklik keempat bidang di kotak dialog tabel pivot (Kuartal, Tahun, Penjualan, dan Tenaga Penjualan) Excel menambahkan laporan ke spreadsheet yang tidak masuk akal, tetapi mengapa?
Seperti yang Anda lihat, kami telah memilih keempat bidang untuk ditambahkan ke laporan. Perilaku default Excel adalah mengelompokkan baris menurut bidang teks dan kemudian menjumlahkan semua baris lainnya.
Ini memberi kita jumlah tahun 2014 + 2014 + 2014 + 2014 = 24.168, yang merupakan omong kosong. Juga diberikan adalah jumlah kuartal 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Kami tidak memerlukan informasi ini, jadi kami membatalkan pilihan bidang ini untuk menghapusnya dari tabel pivot kami.
"Jumlah Penjualan" (total penjualan) terkait, namun, jadi kami akan memperbaikinya.
Contoh: Penjualan oleh Penjual
Anda dapat mengedit "Jumlah Penjualan" yang mengatakan "Total Penjualan," yang lebih jelas. Anda juga dapat memformat sel sebagai mata uang seperti halnya Anda memformat sel lain. Klik pertama pada "Jumlah Penjualan" dan pilih "Pengaturan Nilai Bidang."
Pada dialog yang dihasilkan, kami mengubah nama menjadi "Total Penjualan" lalu klik "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 kita tambahkan subtotal untuk setiap kuartal. Untuk menambahkan subtotal, klik kiri pada bidang “Quarter” dan tahan dan seret ke bagian “rows”. Anda dapat melihat hasilnya pada tangkapan layar di bawah ini:
Sementara kita melakukannya, mari kita hapus nilai "Sum of Quarter". Cukup klik pada panah dan klik "Hapus Bidang." Pada tangkapan layar, Anda sekarang dapat melihat kami telah menambahkan baris "Perempat", yang memecah penjualan setiap tenaga penjualan per kuartal.
Dengan keterampilan ini, Anda sekarang dapat membuat tabel pivot dari data Anda sendiri!
Kesimpulan
Setelah selesai, kami telah menunjukkan kepada Anda beberapa fitur formula dan fungsi Microsoft Excel yang dapat Anda terapkan Microsoft Excel untuk bisnis Anda, akademik, atau kebutuhan lain.
Seperti yang Anda lihat, Microsoft Excel adalah produk yang sangat besar dengan begitu banyak fitur yang kebanyakan orang, bahkan pengguna tingkat lanjut, tidak tahu semuanya. Beberapa orang mungkin mengatakan itu membuatnya rumit; kami merasa itu lebih komprehensif.
Semoga, dengan menghadirkan banyak contoh kehidupan nyata, kami telah menunjukkan tidak hanya fungsi yang tersedia di Microsoft Excel tetapi juga mengajarkan Anda sesuatu tentang statistik, pemrograman linier, membuat grafik, menggunakan angka acak, dan ide-ide lain yang sekarang dapat Anda adopsi dan gunakan di sekolah Anda atau di mana Anda bekerja.
Ingat, jika Anda ingin kembali dan mengikuti kelas lagi, Anda bisa mulai dengan Pelajaran 1!
