Jimmy di Huntsville ingin menggambar kurva lonceng yang menunjukkan skor rata-rata beberapa orang. Ketika Jimmy mengajukan pertanyaan selama Seminar Power Excel, saya teringat kembali pada salah satu video saya yang lebih populer di YouTube.
Dalam Podcast 1665 - Buat Kurva Lonceng di Excel, saya jelaskan bahwa untuk membuat kurva lonceng, Anda perlu menghitung mean dan deviasi standar. Saya kemudian menghasilkan 30 titik di sepanjang sumbu x yang menjangkau populasi hipotetis orang. Dalam video itu, saya menghasilkan rentang dari -3 Standar Deviasi hingga + 3 Deviasi Standar di sekitar mean.
Misalnya, jika Anda memiliki rata-rata 50 dan simpangan baku 10, saya akan membuat sumbu x dari 70 hingga 130. Tinggi setiap titik dihitung menggunakan =NORM.DIST(x,mean,standard deviation,False).
Pada gambar di atas, angka di A10: A40 pada dasarnya adalah "titik data palsu". Saya menghasilkan 31 angka untuk membuat kurva mulus yang bagus. Jika saya hanya menggunakan 7 titik data, kurva akan terlihat seperti ini:
Untuk kumpulan data Jimmy, skor rata-rata aktual karyawannya pada dasarnya adalah poin di sepanjang sumbu x. Untuk mencocokkannya pada kurva lonceng, Anda perlu mencari tahu tinggi atau nilai Y untuk setiap karyawan.
Ikuti langkah ini:
-
Urutkan data sehingga skor tampak dari terendah hingga tertinggi.
Sortir data - Hitung Mean menggunakan fungsi AVERAGE.
- Hitung Deviasi Standar 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 kurva lonceng. Fungsi NORM.DIST akan menangani plot orang yang dekat dengan mean di lokasi yang lebih tinggi daripada orang yang dekat atas atau bawah.
Hasilkan serangkaian nilai Y. - Pilih data Anda di L1: M15
-
Bug aneh baru-baru ini mulai muncul di Excel jadi untuk memastikan kesuksesan, pilih Semua Bagan pada tab Sisipkan.
Peluncur dialog membawa Anda ke semua jenis bagan Dalam dialog Sisipkan Bagan, klik tab Semua Bagan. Klik XY (Sebar) di sebelah kiri. Pilih ikon kedua di bagian atas. Pilih pratinjau di sebelah kanan.
Empat klik untuk memilih grafik Kurva lonceng awal Anda akan terlihat seperti ini:
Kurva lonceng
Untuk membersihkan kurva lonceng, ikuti langkah-langkah berikut:
- Klik pada judul dan tekan tombol Hapus.
- Klik dua kali nomor apa pun di sepanjang sumbu Y di bagian bawah diagram. Panel Format Axis akan muncul.
-
Ketikkan nilai baru untuk Minimum dan Maksimum. Rentang di sini harus cukup lebar untuk menampilkan semua orang di bagan. Saya menggunakan 50 hingga 90.
Ubah minimum dan maksimum - Buat diagram lebih lebar dengan menyeret tepi diagram.
- Klik ikon + di sebelah kanan diagram dan pilih Label Data. Jangan khawatir labelnya belum masuk akal.
- Klik dua kali satu label untuk membuka panel Format Labels.
- Ada empat ikon di sepanjang bagian atas panel. Pilih ikon yang menunjukkan bagan kolom.
- Klik panah di samping Opsi Label untuk memperluas bagian panel itu.
- Pilih Nilai dari Sel. Kotak dialog akan muncul menanyakan lokasi label. Pilih nama di K2: K15.
-
Masih di panel Format Data Label, batalkan pilihan nilai Y. Penting untuk menyelesaikan Langkah 15 sebelum melakukan Langkah 16 atau Anda akan menghapus label secara tidak sengaja.
Dapatkan label dari sel yang berisi nama.
Catatan
Kemampuan untuk mendapatkan label dari sel telah ditambahkan di Excel 2013. Jika Anda menggunakan Excel 2010 atau yang lebih lama, unduh add-in XY Chart Labeler dari Rob Bovey. (Google untuk menemukannya).
Pada titik ini, lihat apakah Anda memiliki label bagan yang bertabrakan satu sama lain. Untuk memperbaikinya, ikuti langkah-langkah berikut dengan cermat.
- Klik satu kali pada satu label bagan. Ini memilih semua label.
- Klik sekali pada salah satu label yang ada di atas label lain untuk memilih label itu saja.
- Arahkan kursor ke berbagai bagian label sampai Anda melihat panah berkepala empat. Klik dan seret label ke posisi baru.
-
Setelah Anda hanya memilih satu label, Anda dapat mengklik sekali label lain untuk memilih label itu. Ulangi untuk label lain yang perlu dipindahkan.
Grafik terakhir
Menonton video
Transkrip Video
Belajar Excel dari Podcast, Episode 2217: Tempatkan Orang di Kurva Lonceng.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini, dari Jimmy dalam seminar saya di Huntsville, Alabama. Jimmy memiliki data, dia ingin meringkas data ini dan kemudian memplot hasilnya pada kurva lonceng.
Baik? Sekarang, salah satu video saya yang paling populer di YouTube adalah yang ini: nomor 1663, Buat Kurva Lonceng di Excel. Dan dengan nilai mean dan deviasi standar, saya menemukan nilai terendahnya, yaitu 3 kali deviasi standar lebih kecil dari rata-rata, dan tinggi-- 3 kali deviasi standar lebih banyak daripada rata-rata - di mana selisihnya-- dan serangkaian nilai X di sini, dan untuk mengetahui tingginya, gunakan fungsi ini: = NORM.DIST dari nilai X, mean, dan deviasi standar, koma salah (= NORM.DIST (A10, $ B $ 2, $ B $ 3, SALAH)).
Dan jika Anda memikirkannya, video ini sebenarnya hanya menggunakan serangkaian nilai X palsu di sini untuk mendapatkan kurva yang terlihat bagus. Dan kami akan menggunakan konsep yang sama di sini, tetapi alih-alih nilai X palsu, kami akan menempatkan orang-orang di sini dan tingginya akan menjadi rumus yang sama persis. Baik.
Nah, sekarang, Jimmy ingin membuat tabel pivot. Jadi kita akan Sisipkan, PivotTable, taruh di sini di lembar ini, klik OK. Orang-orang di sisi kiri dan kemudian Skor Rata-rata mereka. Baiklah, jadi ini dimulai dengan Jumlah Skor, saya akan mengklik dua kali di sana dan mengubahnya menjadi rata-rata. Bagus. Sekarang, di bagian paling bawah, saya tidak ingin jumlah total-- klik kanan dan Hapus Total Keseluruhan-- dan kami ingin mengatur Orang-orang ini dari tinggi ke rendah dan ini mudah dilakukan dalam tabel pivot. Data, A hingga Z-- luar biasa. Baik. Sekarang, kita akan melakukan hal yang persis sama seperti yang kita lakukan di Podcast 1663, dan itu menghitung mean dan deviasi standar. Jadi meannya adalah rata-rata dari skor ini, dan kemudian sama dengan standar deviasi dari skor tersebut. Baik. Sekarang setelah saya mengetahuinya, saya dapat membuat nilai y saya.
Baiklah, ada beberapa hal yang akan kita lakukan di sini. Pertama, Anda tidak dapat membuat tabel pivot-- bagan sebar-- dari tabel pivot. Jadi saya akan menyalin semua data ini dan saya akan melakukannya dengan = D2. Perhatikan bahwa saya berhati-hati untuk tidak menggunakan mouse atau tombol panah untuk menunjuk ke sana. Jadi kami memiliki nilai-nilai kami di sini. Ini akan menjadi nilai X, nilai Y akan menjadi = NORM.DIST, inilah nilai x, koma, untuk mean, angka itu, saya akan menekan F4 untuk menguncinya; untuk deviasi standar adalah angka ini, sekali lagi, tekan F4 untuk menguncinya, dan kumulatif FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Dan kami akan mengklik dua kali untuk menyalinnya. Baik. Dan kemudian, jangan pilih labelnya,cukup pilih XY dan kami akan menyisipkan diagram pencar dengan garis - Anda dapat memilih salah satu dengan garis lengkung atau sedikit garis lurus. Di sini, saya akan menggunakan garis lengkung seperti ini. Dan sekarang kami memiliki semua Orang yang ditempatkan pada kurva lonceng.
Baik. Sekarang, beberapa hal-- beberapa hal jenis pemformatan-- yang akan kita lakukan di sini: Pertama, klik dua kali di sini di sepanjang skala, dan sepertinya angka terendah kita mungkin sekitar 50-- jadi saya akan tetapkan minimal 50-- dan angka terbesar kami-- angka terbesar kami-- adalah 88-- jadi saya akan menetapkan maksimal 90. Baiklah. Dan sekarang, kita harus memberi label pada poin-poin ini. Jika 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 add-in Labeler Bagan Rob Bovey agar label titik ini berasal dari tempat yang tidak ada dalam bagan. Baiklah, jadi kita mulai dari sini. Kami akan menambahkan Label Data, dan itu menambahkan angka dan mereka terlihat mengerikan. Saya akan datang ke sini dan mengatakan bahwa saya ingin Lebih Banyak Opsi, Opsi Label,dan saya ingin mendapatkan Value From Cells-- Value From Cells. Baik? Jadi Rentang sel ada di sana, klik OK. Sangat penting untuk menggunakan Nilai dari sel sebelum saya menghapus centang pada nilai Y. Ini mulai terlihat bagus. Saya akan menyingkirkan ini. Sekarang, kunci utamanya di sini - karena Anda memiliki beberapa orang yang agak saling menimpa - adalah mencoba dan membuat bagan sebesar mungkin. Kami tidak membutuhkan judul di atas sana. Mengapa? Hapus saja itu. 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 membuat frustasi-- yang ini tumpang tindih. Tapi saat kita mengklik label, kita memilih semua label, lalu mengklik label lagi, dan kita memilih hanya satu label. Baik? Jadi sekarang. dengan sangat hati-hati. coba dan klik Andy, dan seret saja Andy ke kiri.Sepertinya Jared dan Ike bersama, jadi sekarang aku dalam mode pemilihan label tunggal, itu lebih mudah. Lalu Kelly dan Lou, tarik mereka seperti itu. Mungkin ada tempat yang lebih baik yang tidak terlalu memaksakan Lou, atau bahkan, seperti, di sini aku bisa menyeretnya ke kedua sisi. Baiklah, jadi, apa yang kita punya? Kami telah mulai dengan sekumpulan data, membuat tabel pivot, mengetahui mean dan deviasi standar, yang memungkinkan kami untuk mengetahui ketinggian-- posisi Y untuk masing-masing skor tersebut, dan tingginya, semoga, kita akan membuat orang menjadi kurva lonceng berbentuk parabola yang bagus, seperti itu.Baiklah, jadi, apa yang kita punya? Kami telah mulai dengan sekumpulan data, membuat tabel pivot, mengetahui mean dan deviasi standar, yang memungkinkan kami untuk mengetahui ketinggian-- posisi Y untuk masing-masing skor tersebut, dan tingginya, semoga, kita akan membuat orang menjadi kurva lonceng berbentuk parabola yang bagus, seperti itu.Baiklah, jadi, apa yang kita punya? Kami telah mulai dengan sekumpulan data, membuat tabel pivot, mengetahui mean dan deviasi standar, yang memungkinkan kami untuk mengetahui ketinggian-- posisi Y untuk masing-masing skor tersebut, dan tingginya, semoga, kita akan membuat orang menjadi kurva lonceng berbentuk parabola yang bagus, seperti itu.
Saya suka pertanyaan ini dari Jimmy, pertanyaan ini tidak ada di buku ini, tapi akan ada di lain waktu saya menulis buku ini. Saya harus menambahkan ini-- ini permintaan yang keren dan trik kecil yang keren. Kurva lonceng sangat populer di Excel.
Tapi lihatlah buku saya, LIVe, The 54 Greatest Excel Tips of All Time.
Baiklah, penutup dari episode ini: Jimmy dari Huntsville, ingin mengatur orang dengan kurva lonceng. Jadi kami menggunakan tabel pivot untuk mengetahui skor rata-rata, mengurutkan tabel pivot ke skor - diatur dari tinggi ke rendah - singkirkan total keseluruhan di bagian bawah-- ini pada dasarnya akan menjadi nilai X-- lalu ke samping, hitung rata-rata dan deviasi standar dari skor tersebut dan gunakan rumus untuk menyalin data dari tabel pivot ke rentang baru, karena Anda tidak dapat memiliki bagan XY yang berpotongan dengan tabel pivot. Hitung nilai y untuk setiap orang dengan = NORM.DIST nilai x mereka, mean, deviasi standar, koma FALSE; buat bagan sebar XY dengan garis halus-- jika Anda menggunakan Excel 2010 atau sebelumnya, Anda akan menggunakan add-in Labeler Bagan Ron Bovey. Saya akan meminta Anda mencari di Google karena,jika Rob mengubah URL-nya, saya tidak ingin URL yang salah di sini. Di Excel 2013, miliki Label Data, Dari Sel, tentukan nama, dan kemudian beberapa penyesuaian-- ubah skala di sepanjang bagian bawah, saya mengubahnya di dan Max lalu pindahkan label yang saling mengatur satu sama lain.
Untuk mengunduh buku kerja dari video hari ini, gunakan URL dalam deskripsi YouTube. Saya ingin berterima kasih kepada Jimmy atas pertanyaan yang luar biasa ini di Huntsville, dan saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: place-people-on-bell-curve.xlsx
Terima kasih kepada Jimmy di Huntsville untuk pertanyaan hari ini!
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Jika Anda telah menempatkan excel dalam mode hitung ulang manual dalam sebulan terakhir, inilah waktunya untuk pivot daya (Anda tidak akan memerlukan mode manual lagi)"
Rob Collie
