Jimmy ở Huntsville muốn vẽ một đường cong hình chuông cho thấy điểm trung bình của một số người. Khi Jimmy đặt câu hỏi trong Hội thảo Power Excel của tôi, tôi đã nghĩ lại một trong những video phổ biến hơn của mình trên YouTube.
Trong Podcast 1665 - Tạo đường cong hình chuông trong Excel, tôi giải thích rằng để tạo đường cong hình chuông, bạn cần tính giá trị trung bình và độ lệch chuẩn. Sau đó, tôi tạo ra 30 điểm dọc theo trục x trải dài một nhóm người giả định. Trong video đó, tôi đã tạo kéo dài từ -3 Độ lệch chuẩn đến + 3 Độ lệch chuẩn xung quanh mức trung bình.
Ví dụ: nếu bạn có giá trị trung bình là 50 và độ lệch chuẩn là 10, tôi sẽ tạo trục x chạy từ 70 đến 130. Chiều cao của mỗi điểm được tính bằng cách sử dụng =NORM.DIST(x,mean,standard deviation,False).
Trong hình trên, các con số trong A10: A40 thực chất là "điểm dữ liệu giả". Tôi tạo ra 31 số để tạo ra một đường cong mịn đẹp. Nếu tôi chỉ sử dụng 7 điểm dữ liệu, thì đường cong sẽ như thế này:
Đối với tập dữ liệu của Jimmy, điểm số trung bình thực tế của nhân viên về cơ bản là các điểm dọc theo trục x. Để phù hợp với họ trên một đường cong hình chuông, bạn cần phải tìm ra chiều cao hoặc giá trị Y cho mỗi nhân viên.
Làm theo các bước sau:
-
Sắp xếp dữ liệu để điểm xuất hiện từ thấp nhất đến cao nhất.
Sắp xếp dữ liệu - Tính giá trị trung bình bằng cách sử dụng hàm AVERAGE.
- Tính độ lệch chuẩn bằng cách sử dụng hàm STDEV.
-
Tính giá trị Y ở bên phải điểm số bằng cách sử dụng
=NORM.DIST(L2,$H$2,$H$3,FALSE). Giá trị Y sẽ tạo ra độ cao của điểm của mỗi người dọc theo đường cong hình chuông. Hàm NORM.DIST sẽ quan tâm đến việc lập kế hoạch những người ở gần trung bình ở vị trí cao hơn những người ở gần đầu hoặc cuối.
Tạo một loạt các giá trị Y. - Chọn dữ liệu của bạn trong L1: M15
-
Gần đây, một lỗi kỳ lạ đã bắt đầu xuất hiện trong Excel, vì vậy để đảm bảo thành công, hãy chọn Tất cả biểu đồ trên tab Chèn.
Trình khởi chạy hộp thoại đưa bạn đến tất cả các loại biểu đồ Trong hộp thoại Chèn biểu đồ, bấm vào tab Tất cả biểu đồ. Nhấp vào XY (Scatter) ở bên trái. Chọn biểu tượng thứ hai ở trên cùng. Chọn bản xem trước ở bên phải.
Bốn lần nhấp để chọn biểu đồ Đường cong chuông nội tâm của bạn sẽ trông như thế này:
Đường cong chuông
Để làm sạch đường cong chuông, hãy làm theo các bước sau:
- Nhấp vào tiêu đề và nhấn phím Xóa.
- Bấm đúp vào bất kỳ số nào dọc theo trục Y ở cuối biểu đồ. Bảng Format Axis sẽ xuất hiện.
-
Nhập các giá trị mới cho Giá trị tối thiểu và Tối đa. Phạm vi ở đây chỉ nên đủ rộng để hiển thị tất cả mọi người trên biểu đồ. Tôi đã sử dụng 50 đến 90.
Thay đổi mức tối thiểu và tối đa - Làm cho biểu đồ rộng hơn bằng cách kéo cạnh của biểu đồ.
- Nhấp vào biểu tượng + ở bên phải biểu đồ và chọn Nhãn dữ liệu. Đừng lo lắng rằng các nhãn chưa có ý nghĩa.
- Bấm đúp vào một nhãn để mở bảng Định dạng Nhãn.
- Có bốn biểu tượng dọc theo đầu bảng điều khiển. Chọn biểu tượng hiển thị biểu đồ cột.
- Nhấp vào mũi tên bên cạnh Tùy chọn Nhãn để mở rộng phần đó của bảng điều khiển.
- Chọn Giá trị từ các Ô. Một hộp thoại sẽ xuất hiện yêu cầu vị trí của các nhãn. Chọn tên trong K2: K15.
-
Vẫn trong bảng Định dạng Nhãn Dữ liệu, bỏ chọn các giá trị Y. Điều quan trọng là phải hoàn thành Bước 15 trước khi thực hiện Bước 16, nếu không bạn sẽ vô tình loại bỏ các nhãn.
Lấy nhãn từ các ô chứa tên.
Ghi chú
Khả năng lấy nhãn từ các ô đã được thêm vào trong Excel 2013. Nếu bạn đang sử dụng Excel 2010 trở về trước, hãy tải xuống phần bổ trợ XY Chart Labeler từ Rob Bovey. (Google để tìm nó).
Tại thời điểm này, hãy xem liệu bạn có bất kỳ nhãn biểu đồ nào trùng khớp với nhau hay không. Để khắc phục chúng, hãy cẩn thận làm theo các bước sau.
- Nhấp một lần vào một nhãn biểu đồ. Thao tác này sẽ chọn tất cả các nhãn.
- Nhấp một lần vào một trong các nhãn nằm trên nhãn khác để chỉ chọn nhãn đó.
- Di chuột qua các phần khác nhau của nhãn cho đến khi bạn nhìn thấy mũi tên bốn đầu. Nhấp và kéo nhãn đến vị trí mới.
-
Khi bạn chỉ có một nhãn được chọn, bạn có thể nhấp một lần vào bất kỳ nhãn nào khác để chọn nhãn đó. Lặp lại bất kỳ nhãn nào khác cần được di chuyển.
Biểu đồ cuối cùng
Xem video
Bản ghi video
Học Excel từ Podcast, Tập 2217: Đặt mọi người lên đường cong hình chuông.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay, từ Jimmy trong buổi hội thảo của tôi ở Huntsville, Alabama. Jimmy có dữ liệu, anh ấy muốn tóm tắt dữ liệu này và sau đó vẽ biểu đồ kết quả trên một đường cong hình chuông.
Ổn thỏa? Bây giờ, một trong những video phổ biến nhất của tôi trên YouTube là video này: số 1663, Tạo đường cong hình chuông trong Excel. Và với giá trị trung bình và độ lệch chuẩn, tôi đã tìm ra mức thấp, tức là độ lệch chuẩn nhỏ hơn 3 lần so với giá trị trung bình và mức cao-- độ lệch chuẩn nhiều hơn 3 lần so với giá trị trung bình - nơi có khoảng cách-- và một loạt các giá trị X ở đây và để tìm ra chiều cao, hãy sử dụng hàm này: = NORM.DIST của giá trị X, giá trị trung bình và độ lệch chuẩn, dấu phẩy false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
Và nếu bạn nghĩ về nó, video này thực sự chỉ đang sử dụng một loạt các giá trị X giả ở đây để có được một đường cong đẹp mắt. Và chúng tôi sẽ sử dụng cùng một khái niệm ở đây nhưng thay vì các giá trị X giả, chúng tôi thực sự sẽ đưa mọi người xuống đây và sau đó chiều cao sẽ là công thức chính xác này. Ổn thỏa.
Vì vậy, bây giờ, Jimmy muốn tạo một bảng tổng hợp. Vì vậy, chúng tôi sẽ Chèn, PivotTable, đặt nó ở đây trên trang tính này, nhấp vào OK. Mọi người xuống phía bên trái rồi đến Điểm trung bình của họ. Được rồi, vì vậy nó bắt đầu với Tổng điểm, tôi sẽ nhấp đúp vào đó và thay đổi nó thành trung bình. Tuyệt quá. Bây giờ, ở dưới cùng, tôi không muốn có tổng số lớn - nhấp chuột phải và Xóa tổng số tổng - và chúng tôi muốn sắp xếp những Người này từ cao xuống thấp và điều này rất dễ thực hiện trong bảng tổng hợp. Dữ liệu, từ A đến Z-- xuất sắc. Ổn thỏa. Bây giờ, chúng ta sẽ thực hiện chính xác điều mà chúng ta đã làm trong Podcast 1663, đó là tính giá trị trung bình và độ lệch chuẩn. Vì vậy, giá trị trung bình là giá trị trung bình của các điểm này, và sau đó bằng độ lệch chuẩn của các điểm đó. Ổn thỏa. Bây giờ tôi biết điều đó, tôi có thể tạo ra giá trị y của mình.
Được rồi, chúng ta sẽ làm một số việc ở đây. Trước hết, bạn không thể tạo bảng tổng hợp - biểu đồ phân tán - từ bảng tổng hợp. Vì vậy, tôi sẽ sao chép tất cả dữ liệu này và tôi sẽ làm điều đó với = D2. Lưu ý rằng tôi không cẩn thận sử dụng chuột hoặc các phím mũi tên để trỏ đến những thứ đó. Và vì vậy chúng tôi có giá trị của mình ở đây. Đây sẽ trở thành giá trị X, giá trị Y sẽ trở thành = NORM.DIST, đây là giá trị x, dấu phẩy, cho giá trị trung bình, số đó, tôi sẽ nhấn F4 để khóa nó lại; đối với độ lệch chuẩn, đó là con số này, một lần nữa, nhấn F4 để khóa nó xuống và FALSE tích lũy. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Và chúng tôi sẽ nhấp đúp để sao chép nó xuống. Ổn thỏa. Và sau đó, không chọn nhãn,chỉ cần chọn XY và chúng tôi sẽ chèn biểu đồ phân tán với các đường - bạn có thể chọn biểu đồ có đường cong hoặc đường thẳng nhỏ. Ở đây, tôi sẽ đi với các đường cong như thế này. Và bây giờ chúng ta có tất cả Nhân dân của chúng ta được đặt trên một đường cong hình chuông.
Ổn thỏa. Bây giờ, một số thứ-- một số thứ thuộc loại định dạng-- chúng ta sẽ thực hiện ở đây: Trước tiên, hãy nhấp đúp vào đây dọc theo thang đo và có vẻ như con số thấp nhất của chúng ta có thể là khoảng 50-- vì vậy tôi sẽ đặt giá trị tối thiểu là 50-- và số lớn nhất - số lớn nhất của chúng tôi - là 88-- vì vậy tôi sẽ đặt giá trị tối đa là 90. Được rồi. Và bây giờ, chúng ta phải gắn nhãn những điểm này. Nếu bạn đang sử dụng Excel 2013 hoặc mới hơn, điều này rất dễ thực hiện; nhưng nếu bạn đang sử dụng phiên bản Excel cũ hơn, bạn sẽ phải quay lại và sử dụng bổ trợ Trình gắn nhãn biểu đồ của Rob Bovey để có các nhãn điểm này đến từ một số vị trí không có trong biểu đồ. Được rồi, vậy chúng ta bắt đầu ở đây. Chúng tôi sẽ thêm Nhãn dữ liệu và nó thêm số và chúng trông rất kinh khủng. Tôi sẽ đến đây và nói rằng tôi muốn có thêm tùy chọn, tùy chọn nhãn,và tôi muốn nhận Giá trị từ ô-- Giá trị từ ô. Ổn thỏa? Vì vậy, Phạm vi ô ở ngay đó, nhấp vào OK. Rất quan trọng để sử dụng Giá trị từ các ô trước khi tôi bỏ chọn giá trị Y. Nó bắt đầu có vẻ tốt. Tôi sẽ loại bỏ điều này. Bây giờ, toàn bộ chìa khóa ở đây - bởi vì bạn có một số người đang ghi đè lẫn nhau - là cố gắng tạo biểu đồ càng lớn càng tốt. Chúng tôi không cần một tiêu đề ở đó. Tại sao? Chỉ cần xóa nó. Và tôi vẫn thấy, giống như, Kelly và Lou và Andy và Flo gần như ở cùng một nơi; Jared và-- Được rồi. Vì vậy, bây giờ, điều này sẽ gây khó chịu - những cái này chồng chéo lên nhau. Nhưng khi chúng tôi nhấp vào một nhãn, chúng tôi đã chọn tất cả các nhãn, sau đó nhấp lại vào một nhãn và chúng tôi chỉ chọn một nhãn duy nhất. Ổn thỏa? Vậy bây giờ. rất cẩn thận. hãy thử và nhấp vào Andy, và chỉ cần kéo Andy lên bên trái.Có vẻ như Jared và Ike đang ở cùng nhau, vì vậy bây giờ tôi đang ở chế độ chọn nhãn duy nhất, mọi việc dễ dàng hơn. Và sau đó Kelly và Lou, kéo họ lên như vậy. Có lẽ có một nơi tốt hơn không phải là Lou hoạt động quá mức, hoặc thậm chí, ở đây, tôi có thể kéo nó sang hai bên. Được rồi, vậy chúng ta có gì? Chúng tôi đã bắt đầu với một loạt dữ liệu, tạo một bảng tổng hợp, tìm ra giá trị trung bình và độ lệch chuẩn, chỉ cho phép chúng tôi tìm ra chiều cao-- vị trí Y cho mỗi điểm số đó và chiều cao của chúng, hy vọng, chúng ta sẽ đưa mọi người vào một đường cong hình chuông đẹp như hình parabol, như vậy.Được rồi, vậy chúng ta có gì? Chúng tôi đã bắt đầu với một loạt dữ liệu, tạo một bảng tổng hợp, tìm ra giá trị trung bình và độ lệch chuẩn, chỉ cho phép chúng tôi tìm ra chiều cao-- vị trí Y cho mỗi điểm số đó và chiều cao của chúng, hy vọng, chúng ta sẽ đưa mọi người vào một đường cong hình chuông đẹp như hình parabol, như vậy.Được rồi, vậy chúng ta có gì? Chúng tôi đã bắt đầu với một loạt dữ liệu, tạo một bảng tổng hợp, tìm ra giá trị trung bình và độ lệch chuẩn, chỉ cho phép chúng tôi tìm ra chiều cao-- vị trí Y cho mỗi điểm số đó và chiều cao của chúng, hy vọng, chúng ta sẽ đưa mọi người vào một đường cong hình chuông đẹp như hình parabol, như vậy.
Tôi thích câu hỏi này của Jimmy, câu hỏi này không có trong cuốn sách này, nhưng nó sẽ có trong lần tiếp theo tôi viết cuốn sách này. Tôi sẽ phải thêm điều này-- đó là một yêu cầu tuyệt vời và một mẹo nhỏ thú vị. Đường cong hình chuông rất phổ biến trong Excel.
Nhưng hãy xem cuốn sách của tôi, LIVe, 54 mẹo Excel hay nhất mọi thời đại.
Được rồi, tóm tắt từ tập này: Jimmy từ Huntsville, muốn sắp xếp mọi người theo đường cong hình chuông. Vì vậy, chúng tôi sử dụng bảng tổng hợp để tìm ra điểm số trung bình, sắp xếp các bảng tổng hợp theo điểm số - được sắp xếp từ cao xuống thấp - loại bỏ tổng tổng ở cuối - về cơ bản đây sẽ là các giá trị X-- và sau đó sang một bên, tính trung bình và độ lệch chuẩn của các điểm số đó và sử dụng công thức để sao chép dữ liệu từ bảng tổng hợp sang một phạm vi mới, vì bạn không thể có biểu đồ XY giao với bảng tổng hợp. Tính giá trị y cho mỗi người với = NORM.DIST giá trị x của họ, giá trị trung bình, độ lệch chuẩn, dấu phẩy FALSE; tạo biểu đồ phân tán XY với các đường trơn - nếu bạn là người dùng Excel 2010 trở xuống, bạn sẽ sử dụng phần bổ trợ Trình gắn nhãn biểu đồ của Ron Bovey. Tôi sẽ yêu cầu bạn google điều đó bởi vì,trong trường hợp Rob thay đổi URL của mình, tôi không muốn URL sai ở đây. Trong Excel 2013, có Nhãn dữ liệu, Từ ô, chỉ định tên và sau đó là một số điều chỉnh - thay đổi tỷ lệ dọc theo phía dưới, tôi thay đổi chúng trong và Max, sau đó di chuyển các nhãn đặt quá mức cho nhau.
Để tải xuống sổ làm việc từ video hôm nay, hãy sử dụng URL trong mô tả YouTube. Tôi muốn cảm ơn Jimmy vì câu hỏi tuyệt vời này ở Huntsville, và tôi muốn cảm ơn bạn đã ghé qua. Hẹn gặp lại bạn lần sau cho một netcast khác từ.
Tải xuống tệp Excel
Để tải xuống tệp excel: place-people-on-bell-curve.xlsx
Cảm ơn Jimmy ở Huntsville cho câu hỏi hôm nay!
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Nếu bạn đã đặt excel ở chế độ tính toán lại thủ công trong tháng trước, thì đã đến lúc xoay nguồn (bạn sẽ không bao giờ cần đến chế độ thủ công nữa)"
Rob Collie
