Lookups, Charts, Statistik, dan Jadual Pivot
Setelah mengkaji semula fungsi asas, rujukan sel, dan fungsi tarikh dan masa, kini kami menyelam ke dalam beberapa ciri yang lebih canggih dari Microsoft Excel. Kami mengemukakan kaedah untuk menyelesaikan masalah klasik dalam kewangan, laporan jualan, kos penghantaran dan statistik.
NAVIGASI SEKOLAH- Kenapa Anda Perlu Formula dan Fungsi?
- Menentukan dan Mewujudkan Formula
- Rujukan Sel Relatif dan Mutlak, dan Pemformatan
- Fungsi Berguna yang Harus Anda Ketahui
- Lookups, Charts, Statistik, dan Jadual Pivot
Fungsi-fungsi ini penting untuk perniagaan, pelajar, dan mereka yang hanya ingin belajar lebih banyak.
VLOOKUP dan HLOOKUP
Berikut adalah contoh untuk menggambarkan fungsi carian menegak (VLOOKUP) dan fungsi mendatar (HLOOKUP). Fungsi ini digunakan untuk menterjemahkan nombor atau nilai lain ke dalam sesuatu yang dapat difahami. Sebagai contoh, anda boleh menggunakan VLOOKUP untuk mengambil nombor bahagian dan kembali penerangan item.
Untuk menyiasat ini, mari kita kembali ke spreadsheet "Pembuat Keputusan" di Bahagian 4, di mana Jane cuba memutuskan apa yang harus dipakai ke sekolah. Dia tidak lagi berminat dengan apa yang dia pakai, kerana dia telah mendarat seorang teman lelaki baru, jadi dia kini akan memakai pakaian dan kasut rawak.
Dalam spreadsheet Jane, dia menyenaraikan pakaian dalam lajur dan kasut menegak, lajur mendatar.
Dia membuka hamparan dan fungsi RANDBETWEEN (1,3) menghasilkan nombor antara atau sama dengan satu dan tiga yang bersamaan dengan tiga jenis pakaian yang boleh dipakai.
Dia menggunakan fungsi RANDBETWEEN (1,5) untuk memilih antara lima jenis kasut.
Oleh kerana Jane tidak boleh memakai nombor yang kita perlukan untuk menukar ini kepada nama, jadi kita menggunakan fungsi carian.
Kami menggunakan fungsi VLOOKUP untuk menterjemahkan nombor pakaian kepada nama pakaian. HLOOKUP diterjemahkan daripada nombor kasut kepada pelbagai jenis kasut di baris.
Hamparan ini berfungsi seperti ini untuk pakaian:
Excel memilih nombor rawak dari satu hingga tiga, kerana dia mempunyai tiga pilihan pakaian.
Seterusnya formula diterjemahkan nombor ke teks menggunakan = VLOOKUP (B11, A2: B4,2) yang menggunakan nombor rawak nilai dari B11 untuk melihat dalam julat A2: B4. Ia kemudian memberikan hasil (C11) dari data yang disenaraikan dalam lajur kedua.
Kami menggunakan teknik yang sama untuk memilih kasut, kecuali kali ini kami menggunakan VOOKUP dan bukannya HLOOKUP.
Contoh: Statistik Asas
Hampir semua orang tahu satu formula dari statistik - purata - tetapi terdapat statistik lain yang penting untuk perniagaan: sisihan piawai.
Sebagai contoh, ramai orang yang telah pergi ke kolej telah menyerang dengan skor SAT mereka. Mereka mungkin mahu tahu bagaimana kedudukan mereka dibandingkan dengan pelajar lain. Universiti ingin tahu ini juga kerana banyak universiti, terutama yang berprestij, menolak pelajar dengan skor SAT yang rendah.
Jadi bagaimana kita, atau universiti, mengukur dan mentafsirkan skor SAT? Berikut adalah markah SAT bagi lima orang pelajar dari 1,870 hingga 2,230.
Nombor penting yang perlu difahami ialah:
Purata - Purata juga dirujuk sebagai "min."
Penyimpangan piawai (STD atau σ) - Nombor ini menunjukkan betapa luasnya kumpulan yang disebarkan. Jika sisihan piawai adalah besar, maka bilangannya jauh dan jika ia adalah sifar, semua nombor adalah sama. Anda boleh mengatakan bahawa sisihan piawai adalah perbezaan purata antara nilai purata dan nilai diperhatikan, iaitu 1,998 dan setiap skor SAT. Sila ambil perhatian, adalah perkara biasa untuk menyingkat sisihan piawai menggunakan simbol Yunani sigma "σ."
Kedudukan Peratusan - Apabila seorang pelajar menerima skor yang tinggi, mereka boleh membanggakan bahawa mereka berada dalam persentil teratas 99 atau sesuatu seperti itu. "Pangkat persentil" bermaksud peratusan skor adalah lebih rendah daripada satu skor tertentu.
Penyimpangan dan kebarangkalian standard dikaitkan rapat. Anda boleh mengatakan bahawa bagi setiap sisihan piawai, kebarangkalian atau kebarangkalian bahawa bilangan itu berada di dalam bilangan penyimpangan piawai tersebut ialah:
| STD | Peratusan skor | Julat 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 dapat lihat, kemungkinan bahawa mana-mana skor SAT di luar 3 STD adalah praktikal sifar, kerana 99,73 peratus skor berada dalam 3 STD.
Sekarang mari kita lihat lagi spreadsheet dan terangkan bagaimana ia berfungsi.
Sekarang kita menjelaskan formula:
= RINGKASAN (B2: B6)
Purata semua markah atas julat B2: B6. Khususnya, jumlah semua markah dibahagikan dengan bilangan orang yang mengambil ujian.
= STDEV.P (B2: B6)
Penyimpangan piawai di atas julat B2: B6. "P" bermaksud STDEV.P digunakan untuk semua skor, iaitu populasi keseluruhan dan bukan sekadar subset.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Ini mengira peratusan kumulatif sepanjang julat B2: B6 berdasarkan skor SAT, dalam kes ini B2. Sebagai contoh, 83 peratus skor berada di bawah skor Walker.
Memetik Keputusan
Meletakkan keputusan dalam graf menjadikannya lebih mudah untuk memahami hasilnya, dan anda boleh menunjukkannya dalam persembahan untuk membuat titik anda lebih jelas.
Pelajar berada di paksi mendatar dan skor SAT mereka ditunjukkan sebagai graf bar biru pada skala (paksi menegak) dari 1,600 hingga 2,300.
Kedudukan persentil adalah paksi menegak kanan dari 0 hingga 90 peratus, dan diwakili oleh garisan kelabu.
Bagaimana Membuat Carta
Mencipta carta adalah topik kepada dirinya sendiri, namun kami akan menerangkan secara ringkas bagaimana carta di atas dicipta.
Pertama, pilih julat sel untuk berada dalam carta. Dalam hal ini A2 hingga C6 kerana kita mahu nombor-nombor serta nama-nama pelajar.
Dari menu "Sisipkan" pilih "Carta" -> "Carta yang Disyorkan":
Komputer mengesyorkan carta "Clustered-Column, Secondary Axis". Bahagian "Axis Sekunder" bermakna ia menarik dua paksi menegak. Dalam kes ini, carta ini adalah yang kita mahu. Kami tidak perlu melakukan apa-apa lagi.
Anda boleh menggunakan carta bergerak di sekitar dan saiz semula sehingga anda memilikinya sebagai saiz dan kedudukan yang anda mahu. Sebaik sahaja anda berpuas hati anda boleh menyimpan carta dalam hamparan.
Jika anda klik kanan carta, kemudian "Pilih Data," ia menunjukkan kepada anda data yang dipilih untuk julat.
Ciri "Carta Disyorkan" biasanya melepaskan anda daripada harus berurusan dengan butiran rumit seperti menentukan data yang hendak disertakan, cara menetapkan label, dan cara menetapkan paksi menegak kiri dan kanan.
Dalam dialog "Pilih Sumber Data", klik "skor" di bawah "Penyertaan Legenda (Siri)" dan tekan "Edit," dan ubahnya untuk mengatakan "Markah."
Kemudian ubah siri 2 ("persentil") kepada "Percentile."
Kembali ke carta anda dan klik pada "Tajuk Carta" dan ubah ke "Skor SAT." Sekarang kita mempunyai carta lengkap. Ia mempunyai dua paksi mendatar: satu untuk skor SAT (biru) dan satu untuk peratusan kumulatif (oren).
Contoh: Masalah Pengangkutan
Masalah pengangkutan adalah contoh klasik dari jenis matematik yang dipanggil "pengaturcaraan linear." Ini membolehkan anda memaksimumkan atau meminimumkan nilai tertakluk kepada kekangan tertentu. Ia mempunyai banyak aplikasi untuk pelbagai masalah perniagaan, jadi berguna untuk mengetahui cara kerjanya.
Sebelum kita memulakan contoh ini kita perlu mengaktifkan "Excel Solver."
Dayakan Penyelesaian Solver
Pilih "Fail" -> "Pilihan" -> "Tambahan". Di bahagian bawah pilihan tambahan, klik butang "Pergi" di sebelah "Urus: Tambah Excel."
Pada menu yang dihasilkan, klik kotak semak untuk membolehkan, "Solver Add-in," dan klik "OK."
Contoh: Kira Kos Penghantaran Kos Rendah
Katakan kami perkapalan iPads dan kami cuba mengisi pusat pengedaran kami dengan kos pengangkutan terendah. Kami mempunyai perjanjian dengan syarikat pengangkutan dan syarikat penerbangan untuk menghantar iPads dari Shanghai, Beijing, dan Hong Kong ke pusat pengedaran yang ditunjukkan di bawah.
Harga untuk menghantar setiap iPad ialah jarak dari kilang ke pusat pengedaran ke kilang yang dibahagikan dengan 20,000 kilometer. Sebagai contoh, ia adalah 8,024 km dari Shanghai ke Melbourne yang 8,024 / 20,000 atau $ .40 setiap iPad.
Persoalannya ialah bagaimana kita menghantar semua iPads dari ketiga-tiga tumbuhan ke empat destinasi ini dengan kos terendah?
Seperti yang anda boleh bayangkan, memikirkan ini mungkin sangat sukar tanpa beberapa formula dan alat. Dalam kes ini kita perlu menghantar 462,000 (F12) jumlah iPads. Tumbuhan mempunyai kapasiti terhad sebanyak 500,250 (G12).
Di dalam hamparan, supaya anda dapat melihat bagaimana ia berfungsi, kami telah menaip 1 ke dalam sel B10 yang bermaksud kami ingin menghantar 1 iPad dari Shanghai ke Melbourne. Oleh kerana kos pengangkutan di sepanjang laluan itu ialah $ 0.40 setiap iPad, jumlah kos (B17) ialah $ 0.40.
Nombor tersebut dikira menggunakan fungsi = SUMPRODUCT (kos, dihantar) "kos" adalah julat B3: E5.
Dan "dihantar" adalah julat B9: E11:
SUMPRODUCT melipatgandakan "kos" kali julat "dihantar" (B14). Itu dipanggil "pendaraban matriks."
Untuk SUMPRODUCT berfungsi dengan baik, dua matriks - kos dan penghantaran - perlu saiz yang sama. Anda boleh mendapatkan sekitar had ini dengan membuat kos tambahan dan lajur dan baris penghantaran dengan nilai sifar sehingga array adalah saiz yang sama dan tidak ada kesan ke atas jumlah kos.
Menggunakan Penyelesaian
Jika semua yang kita perlu lakukan ialah banyakkan matriks "kos" kali "dihantar" yang tidak terlalu rumit, tetapi kita perlu menangani kekangan di sana juga.
Kita perlu menghantar apa yang diperlukan setiap pusat pengedaran. Kami meletakkan pemalar itu menjadi pemecah seperti ini: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ini bermakna jumlah apa yang dihantar, iaitu jumlah dalam sel $ B $ 12: $ E $ 12, mestilah lebih besar daripada atau sama dengan apa yang diperlukan setiap pusat pengedaran ($ B $ 13: $ E $ 13).
Kita tidak boleh menghantar lebih banyak daripada yang kita hasilkan. Kami menulis kekangan 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 butang "Solver". Jika butang "Solver" tidak ada, anda perlu mendayakan add-in Solver.
Ketik dua kekangan yang terperinci lebih awal dan pilih julat "Pengiriman", yang merupakan bilangan angka yang kami mahu Excel dikira. Juga pilih algoritma lalai "Simplex LP" dan nyatakan bahawa kita mahu "meminimumkan" sel B15 ("jumlah kos penghantaran"), di mana ia mengatakan "Set Objektif."
Tekan "Menyelesaikan" dan Excel menjimatkan hasilnya ke dalam spreadsheet, yang kami mahu. Anda juga boleh menyimpannya supaya anda dapat bermain-main dengan senario lain.
Jika komputer mengatakan ia tidak dapat mencari penyelesaian, maka anda telah melakukan sesuatu yang tidak logik, sebagai contoh, anda mungkin telah meminta iPads lebih banyak daripada tanaman yang dapat menghasilkan.
Di sini, Excel berkata bahawa ia mendapat penyelesaian. Tekan "OK" untuk menyimpan penyelesaian dan kembali ke hamparan.
Contoh: Nilai Had Semasa Bersih
Bagaimanakah syarikat memutuskan sama ada untuk melabur dalam projek baru? Jika "nilai semasa bersih" (NPV) adalah positif, mereka akan melabur di dalamnya. Ini adalah pendekatan standard yang diambil oleh kebanyakan penganalisis kewangan.
Contohnya, katakan syarikat perlombongan Codelco ingin mengembangkan lombong tembaga Andinas. Pendekatan standard untuk menentukan sama ada untuk bergerak ke hadapan dengan projek adalah untuk mengira nilai semasa bersih. Sekiranya NPV lebih besar daripada sifar, maka projek itu akan menguntungkan diberi dua input (1) masa dan (2) kos modal.
Dalam bahasa Inggeris biasa, kos modal bermakna berapa banyak wang yang akan diperoleh jika mereka hanya meninggalkannya di bank. Anda menggunakan kos modal untuk menolak nilai tunai untuk nilai sekarang, dengan kata lain $ 100 dalam tempoh lima tahun mungkin $ 80 hari ini.
Pada tahun pertama, $ 45 juta diketepikan sebagai modal untuk membiayai projek itu. Akauntan telah menentukan bahawa kos modal mereka adalah enam peratus.
Apabila mereka memulakan perlombongan, wang tunai mula masuk kerana syarikat mendapati dan menjual tembaga yang mereka hasilkan. Jelas sekali, semakin banyak mereka saya, semakin banyak wang yang mereka buat, dan ramalan mereka menunjukkan aliran tunai mereka meningkat sehingga mencapai $ 9 juta per tahun.
Selepas 13 tahun, NPV ialah $ 3,945,074 USD, jadi projek itu akan menguntungkan. Menurut penganalisis Kewangan, "tempoh bayar balik" adalah 13 tahun.
Mewujudkan Jadual Pivot
Jadual "pangsi" pada dasarnya adalah laporan. Kami memanggil mereka jadual pivot kerana anda dengan mudah boleh menukar satu jenis laporan kepada yang lain tanpa perlu membuat laporan baru. Jadi mereka pivot di tempatnya. Mari kita tunjukkan contoh asas yang mengajar konsep asas.
Contoh: Laporan Jualan
Orang jualan sangat kompetitif (itu sebahagian daripada jualan) supaya mereka secara semulajadi ingin tahu bagaimana mereka menumpukan satu sama lain pada penghujung suku dan akhir tahun ini, ditambah berapa jumlah komisen mereka.
Katakan kita mempunyai tiga orang jualan - Carlos, Fred, dan Julie - semua menjual petroleum. Jualan mereka dalam dolar pada suku tahun fiskal untuk tahun 2014 ditunjukkan dalam spreadsheet di bawah.
Untuk menjana laporan ini, kami membuat jadual pangsi:
Pilih "Sisipkan -> Jadual Pivot, ia berada di sebelah kiri bar alat:
Pilih semua baris dan lajur (termasuk nama penjual) seperti ditunjukkan di bawah:
Kotak dialog jadual pivot muncul pada sebelah kanan hamparan.
Jika kita mengklik semua empat bidang dalam kotak dialog jadual pivot (Quarter, Year, Sales, and Salesperson) Excel menambah laporan ke spreadsheet yang tidak masuk akal, tetapi mengapa?
Seperti yang dapat anda lihat, kami telah memilih semua empat bidang untuk ditambahkan ke laporan. Tingkah laku lalai Excel adalah untuk kumpulan baris oleh medan teks dan kemudian jumlah semua baris yang lain.
Di sini ia memberi kami jumlah tahun 2014 + 2014 + 2014 + 2014 = 24,168, yang tidak masuk akal. Juga memberikan jumlah kuantiti 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Kami tidak memerlukan maklumat ini, jadi kami tidak memilih bidang ini untuk menghapusnya dari jadual pangsi kami.
"Jumlah Jualan" (jumlah jualan) adalah penting, jadi, kami akan memperbetulkannya.
Contoh: Penjualan oleh Jurujual
Anda boleh mengedit "Jumlah Jualan" yang mengatakan "Jumlah Jualan," yang lebih jelas. Juga, anda boleh memformatkan sel sebagai mata wang sama seperti anda memformatkan mana-mana sel lain. Klik pertama pada "Jumlah Jualan" dan pilih "Tetapan Medan Nilai."
Pada dialog yang terhasil, kami menukar nama itu menjadi "Jumlah Jualan" kemudian klik "Format Nombor" dan ubah ke "Mata Wang."
Anda kemudian dapat melihat perbuatan anda dalam jadual pangsi:
Contoh: Penjualan oleh Jurujual dan Suku
Sekarang mari tambahkan subtotal untuk setiap suku tahun. Untuk menambah subtotal, klik kiri pada bidang "Suku" dan tahan dan seret ke bahagian "baris". Anda boleh melihat keputusan pada tangkapan skrin di bawah:
Walaupun kita berada di sini, mari kita keluarkan nilai "Jumlah Suku". Cukup klik pada anak panah dan klik "Hapus Bidang." Dalam tangkapan skrin, anda kini dapat melihat kami telah menambah baris "Quarter", yang memecahkan jualan setiap penjual mengikut suku.
Dengan kemahiran ini segar, anda kini boleh membuat jadual pangsi dari data anda sendiri!
Kesimpulannya
Dengan membungkus, kami telah menunjukkan kepada anda beberapa ciri formula dan fungsi Microsoft Excel yang anda boleh menggunakan Microsoft Excel untuk keperluan perniagaan anda, akademik atau lain-lain..
Seperti yang anda lihat, Microsoft Excel adalah produk yang sangat besar dengan begitu banyak ciri yang kebanyakan orang, malah pengguna yang maju, tidak tahu semuanya. Sesetengah orang mungkin mengatakan bahawa menjadikannya rumit; kami rasa ia lebih komprehensif.
Mudah-mudahan, dengan membentangkan anda banyak contoh kehidupan sebenar, kami telah menunjukkan bukan sahaja fungsi yang ada di Microsoft Excel tetapi telah mengajarkan anda tentang statistik, pengaturcaraan linear, membuat carta, menggunakan nombor rawak, dan idea-idea lain yang kini boleh diadopsi dan gunakan di sekolah atau di mana anda bekerja.
Ingat, jika anda mahu kembali dan mengambil kelas lagi, anda boleh mula segar dengan Pelajaran 1!
