Jimmy di Huntsville ingin merancang lengkung loceng yang menunjukkan skor rata-rata beberapa orang. Semasa Jimmy mengemukakan soalan semasa Seminar Power Excel saya, saya memikirkan kembali salah satu video saya yang lebih popular di YouTube.
Dalam Podcast 1665 - Buat Lengkung Lonceng di Excel, saya menjelaskan bahawa untuk membuat lengkung lonceng, anda perlu mengira min dan sisihan piawai. Saya kemudian menghasilkan 30 titik di sepanjang paksi-x yang merangkumi populasi orang hipotesis. Dalam video itu, saya menghasilkan yang merangkumi dari -3 Sisihan Piawai hingga + 3 Sisihan Piawai di sekitar min.
Sebagai contoh, jika anda mempunyai nilai min 50 dan sisihan piawai 10, saya akan membuat paksi-x yang berlari dari 70 hingga 130. Ketinggian setiap titik dikira menggunakan =NORM.DIST(x,mean,standard deviation,False).
Dalam gambar di atas, nombor di A10: A40 pada dasarnya adalah "titik data palsu". Saya menghasilkan 31 nombor untuk membuat keluk lancar yang bagus. Sekiranya saya hanya menggunakan 7 titik data, lengkung akan kelihatan seperti ini:
Untuk set data Jimmy, skor purata sebenar pekerjanya pada dasarnya adalah titik sepanjang paksi-x. Untuk menyesuaikannya pada lengkung loceng, anda perlu mengetahui ketinggian atau nilai Y bagi setiap pekerja.
Ikut langkah-langkah ini:
-
Susun data sehingga skor kelihatan paling rendah hingga tertinggi.
Isih data - Hitung Mean menggunakan fungsi RATA-RATA.
- Hitung Sisihan Piawai menggunakan fungsi STDEV.
-
Hitung nilai Y di sebelah kanan skor menggunakan
=NORM.DIST(L2,$H$2,$H$3,FALSE). Nilai Y akan menghasilkan ketinggian titik setiap orang di sepanjang lengkung loceng. Fungsi NORM.DIST akan mengurus merancang orang yang berdekatan dengan tempat yang lebih tinggi daripada orang yang berada di bahagian atas atau bawah.
Hasilkan satu siri nilai Y. - Pilih data anda di L1: M15
-
Bug yang ganjil baru-baru ini mula muncul di Excel sehingga untuk memastikan kejayaan, pilih Semua Carta pada tab Sisipkan.
Pelancar dialog membawa anda ke semua jenis carta Dalam dialog Sisipkan Carta, klik tab Semua Carta. Klik XY (Penyebaran) di sebelah kiri. Pilih ikon kedua di bahagian atas. Pilih pratonton di sebelah kanan.
Empat klik untuk memilih carta Keluk loceng awal anda akan kelihatan seperti ini:
Keluk loceng
Untuk membersihkan lekuk loceng, ikuti langkah berikut:
- Klik pada tajuk dan tekan kekunci Padam.
- Klik dua kali nombor di sepanjang paksi-Y di bahagian bawah carta. Panel Format Axis akan muncul.
-
Taipkan nilai baru untuk Minimum dan Maksimum. Julat di sini harus cukup luas untuk menunjukkan semua orang di carta. Saya menggunakan 50 hingga 90.
Tukar minimum dan maksimum - Jadikan carta lebih luas dengan menyeret tepi carta.
- Klik ikon + di sebelah kanan carta dan pilih Label Data. Jangan bimbang label belum masuk akal.
- Klik dua kali pada satu label untuk membuka panel Format Label.
- Terdapat empat ikon di bahagian atas panel. Pilih ikon yang menunjukkan carta lajur.
- Klik anak panah di sebelah Pilihan Label untuk mengembangkan bahagian panel.
- Pilih Nilai dari Sel. Kotak dialog akan muncul meminta lokasi label. Pilih nama di K2: K15.
-
Masih di panel Format Data Label, nyah pilih nilai Y. Penting untuk menyelesaikan Langkah 15 sebelum melakukan Langkah 16 atau anda akan membuang label secara tidak sengaja.
Dapatkan label dari sel yang mengandungi nama.
Catatan
Kemampuan untuk mendapatkan label dari sel telah ditambahkan pada Excel 2013. Sekiranya anda menggunakan Excel 2010 atau lebih awal, muat turun add-in XY Chart Labeler dari Rob Bovey. (Google untuk mencarinya).
Pada ketika ini, periksa sama ada anda mempunyai label carta yang saling bersumpah. Untuk memperbaikinya, ikuti langkah-langkah ini dengan teliti.
- Klik satu pada satu label carta. Ini memilih semua label.
- Klik satu pada salah satu label yang berada di atas label lain untuk memilih label itu sahaja.
- Arahkan kursor ke pelbagai bahagian label sehingga anda melihat anak panah berkepala empat. Klik dan seret label ke kedudukan baru.
-
Setelah anda memilih satu label sahaja, anda boleh mengklik satu label lain untuk memilih label tersebut. Ulangi untuk label lain yang perlu dipindahkan.
Carta terakhir
Tonton Video
Transkrip Video
Belajar Excel dari Podcast, Episod 2217: Tempatkan Orang di Bell Curve.
Hei, selamat datang kembali ke siaran net, saya Bill Jelen. Soalan hari ini, dari Jimmy dalam seminar saya di Huntsville, Alabama. Jimmy mempunyai data, dia ingin meringkaskan data ini dan kemudian memetakan hasilnya pada lengkung loceng.
Baiklah? Sekarang, salah satu video saya yang paling popular di YouTube adalah yang ini: nombor 1663, Buat Bell Curve di Excel. Dan dengan nilai min dan sisihan piawai, saya mengetahui rendahnya, iaitu 3 kali sisihan piawai lebih rendah daripada rata-rata, dan tinggi - 3 kali sisihan piawai lebih banyak daripada min - di mana jurangnya-- dan satu siri nilai X di sini, dan untuk mengetahui ketinggiannya, gunakan fungsi ini: = NORM.DIST dari nilai X, min, dan sisihan piawai, koma salah (= NORM.DIST (A10, $ B $ 2, $ B $ 3, SALAH)).
Dan jika anda memikirkannya, video ini benar-benar hanya menggunakan rangkaian nilai X palsu di sini untuk mendapatkan keluk yang menarik. Dan kita akan menggunakan konsep yang sama di sini tetapi bukannya nilai X palsu, kita sebenarnya akan menjadikan orang-orang di sini dan kemudian ketinggiannya akan menjadi formula yang sama persis ini. Baiklah.
Jadi, sekarang, Jimmy mahu membuat jadual pangsi. Oleh itu, kami akan Masukkan, PivotTable, letakkan di sini pada helaian ini, klik OK. Orang di sebelah kiri dan kemudian Skor Purata mereka. Baiklah, jadi bermula dengan Jumlah Skor, saya akan mengklik dua kali di sana dan mengubahnya menjadi purata. Hebat. Sekarang, di bahagian paling bawah, saya tidak mahu jumlah keseluruhan - klik kanan dan Buang Jumlah Besar - dan kami mahu mengatur Orang ini tinggi ke rendah dan ini mudah dilakukan dalam jadual pangsi. Data, A hingga Z-- sangat baik. Baiklah. Sekarang, kita akan melakukan perkara yang sama seperti yang kita lakukan di Podcast 1663, dan itu mengira min dan sisihan piawai. Jadi min adalah purata skor ini, dan kemudian sama dengan sisihan piawai skor tersebut. Baiklah. Sekarang saya tahu bahawa saya dapat membuat nilai-y saya.
Baiklah, jadi beberapa perkara yang akan kita lakukan di sini. Pertama, anda tidak dapat membuat jadual pangsi - carta penyebaran - dari jadual pangsi. Oleh itu, saya akan menyalin semua data ini dan saya akan melakukannya dengan = D2. Perhatikan saya berhati-hati untuk tidak menggunakan tetikus atau kekunci anak panah untuk menunjukkannya. Oleh itu, kita mempunyai nilai-nilai kita di sini. Ini akan menjadi nilai X, nilai Y akan menjadi = NORM.DIST, inilah nilai x, koma, untuk maksudnya, nombor itu, saya akan menekan F4 untuk menguncinya; untuk sisihan piawai itu nombor ini, sekali lagi, tekan F4 untuk mengunci itu, dan kumulatif FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, SALAH)) Dan kami akan klik dua kali untuk menyalinnya. Baiklah. Dan jangan pilih label,pilih saja XY dan kami akan memasukkan carta penyebaran dengan garis - anda boleh memilih satu dengan garis lengkung atau garis lurus sedikit. Di sini, saya akan pergi dengan garis lengkung seperti ini. Dan sekarang kita semua Orang kita meletakkan kurva loceng.
Baiklah. Sekarang, beberapa perkara - beberapa perkara jenis pemformatan - yang akan kita lakukan di sini: Pertama, klik dua kali di sini di sepanjang skala, dan nampaknya bilangan terendah kami mungkin berada di sekitar 50-- jadi saya akan tetapkan min 50 - dan nombor terbesar kami - nombor terbesar kami - adalah 88-- jadi saya akan menetapkan maksimum 90. Baiklah. Dan sekarang, kita harus melabel perkara ini. Sekiranya anda menggunakan Excel 2013 atau yang lebih baru, ini mudah dilakukan; tetapi jika anda menggunakan versi Excel yang lebih lama, anda harus kembali dan menggunakan tambahan Rob Bovey Chart Labeler untuk mendapatkan label titik ini dari tempat yang tidak terdapat dalam carta. Baiklah, jadi kita bermula di sini. Kami akan menambah Label Data, dan ia menambah nombor dan mereka kelihatan mengerikan. Saya akan datang ke sini dan mengatakan bahawa saya mahukan Lebih Banyak Pilihan, Pilihan Label,dan saya ingin mendapatkan Nilai Dari Sel - Nilai Dari Sel. Baiklah? Oleh itu, Julat sel ada di sana, klik OK. Sangat penting untuk menggunakan Nilai dari sel sebelum saya mencabut nilai Y. Ia mula kelihatan baik. Saya akan menyingkirkan ini. Sekarang, keseluruhan kunci di sini - kerana anda mempunyai beberapa orang yang suka menimpa satu sama lain - adalah untuk mencuba dan membuat carta seluas mungkin. Kami tidak perlu menuju ke sana. Kenapa? Padamkan sahaja. Dan saya masih melihat, seperti, Kelly dan Lou dan Andy dan Flo hampir berada di tempat yang sama; Jared dan-- Baiklah. Jadi sekarang, ini akan mengecewakan - ini yang bertindih. Tetapi apabila kita mengklik label, kita memilih semua label, dan kemudian klik pada label sekali lagi, dan kita hanya memilih satu label. Baiklah? Jadi sekarang. dengan berhati-hati. cuba dan klik pada Andy, dan seret sahaja Andy ke kiri.Nampaknya Jared dan Ike bersama, jadi sekarang saya berada dalam mod pemilihan label tunggal, lebih mudah. Dan kemudian Kelly dan Lou, tarik mereka seperti itu. Mungkin ada tempat yang lebih baik yang tidak terlalu dikendalikan Lou, atau bahkan, di sini, saya boleh menyeretnya di kedua-dua belah pihak. Baiklah, jadi, apa yang kita ada? Kami telah memulakan dengan sekumpulan data, membuat jadual pangsi, mengetahui min dan sisihan piawai, yang hanya membolehkan kita mengetahui ketinggian - kedudukan Y untuk setiap skor tersebut, dan ketinggiannya, semoga, kita akan membawa orang ke dalam kurva loceng berbentuk parabola yang bagus, seperti itu.Baiklah, jadi, apa yang kita ada? Kami telah memulakan dengan sekumpulan data, membuat jadual pangsi, mengetahui min dan sisihan piawai, yang hanya membolehkan kita mengetahui ketinggian - kedudukan Y untuk setiap skor tersebut, dan ketinggiannya, semoga, kita akan membawa orang ke dalam kurva loceng berbentuk parabola yang bagus, seperti itu.Baiklah, jadi, apa yang kita ada? Kami telah memulakan dengan sekumpulan data, membuat jadual pangsi, mengetahui min dan sisihan piawai, yang hanya membolehkan kita mengetahui ketinggian - kedudukan Y untuk setiap skor tersebut, dan ketinggiannya, semoga, kita akan membawa orang ke dalam kurva loceng berbentuk parabola yang bagus, seperti itu.
Saya Suka soalan ini dari Jimmy, soalan ini tidak ada dalam buku ini, tetapi akan datang pada masa berikutnya saya menulis buku ini. Saya harus menambahkan ini - ini adalah permintaan yang bagus dan muslihat kecil yang menarik. Lengkung loceng sangat popular di Excel.
Tetapi lihat buku saya, LIVe, 54 Petua Excel Terbaik Sepanjang Masa.
Baiklah, selesaikan episod ini: Jimmy dari Huntsville, ingin mengatur orang dengan keluk loceng. Oleh itu, kami menggunakan jadual pangsi untuk mengetahui skor purata, menyusun jadual pangsi ke skor - disusun tinggi hingga rendah - menyingkirkan jumlah besar di bahagian bawah - ini pada dasarnya akan menjadi nilai X - dan kemudian ke sisi, hitung purata dan sisihan piawai skor tersebut dan gunakan formula untuk menyalin data dari jadual pangsi ke julat baru, kerana anda tidak boleh mempunyai carta XY yang bersilang dengan jadual pangsi. Hitung nilai y untuk setiap orang dengan = NORM.DIST dari nilai x mereka, min, sisihan piawai, koma SALAH; buat carta penyebaran XY dengan garis halus - jika anda Excel 2010 atau lebih awal, anda akan menggunakan add-in Chart Labeler Ron Bovey. Saya akan meminta anda google kerana,sekiranya Rob menukar URL-nya, saya tidak mahu URL yang salah di sini. Di Excel 2013, mempunyai Label Data, Dari Sel, tentukan nama, dan kemudian beberapa penyesuaian - ubah skala di bahagian bawah, saya menukarnya dan Max dan kemudian memindahkan label yang saling mengatur satu sama lain.
Untuk memuat turun buku kerja dari video hari ini, gunakan URL dalam keterangan YouTube. Saya ingin mengucapkan terima kasih kepada Jimmy untuk soalan hebat di Huntsville ini, dan saya ingin mengucapkan terima kasih kerana berhenti. Sampai jumpa lagi untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: place-people-on-bell-curve.xlsx
Terima kasih kepada Jimmy di Huntsville untuk soalan hari ini!
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Jika anda telah meletakkan excel dalam mod penghitungan semula manual pada bulan lalu, sudah waktunya pivot kuasa (anda tidak akan memerlukan mod manual lagi)"
Rob Collie
