Excel 2013 bao gồm 52 hàm mới, hầu hết trong số đó đã được thêm vào để tuân thủ các tiêu chuẩn của Bảng tính Tài liệu Mở.
Bài đăng này sẽ đề cập đến hàm Gauss trong Excel 2013.
Hiện tại, trợ giúp Excel hơi mờ nhạt trong mô tả của họ về hàm.
Cú pháp: =GAUSS(x)- Trả về nhỏ hơn 0,5 so với phân phối tích lũy chuẩn thông thường.
Như một sự giải thích nhanh, phân phối chuẩn chuẩn là một trường hợp đặc biệt với giá trị trung bình là 0 và độ lệch chuẩn là 1. Bạn sẽ nhận ra nó là đường cong hình chuông.
Excel luôn có một cách tính xác suất cho đường cong chuẩn thông thường. NORMSDIST đầu tiên và sau đó trong Excel 2010 NORM.S.DIST (z, True) sẽ tính toán xác suất. Đối số "z" là số độ lệch chuẩn so với giá trị trung bình.
Đây là một ví dụ đơn giản về việc sử dụng NORM.S.DIST để tính xác suất. Xác suất để một thành viên ngẫu nhiên trong tổng thể nhỏ hơn -0,5 độ lệch chuẩn so với giá trị trung bình là bao nhiêu? Đây là vùng được tô bóng trong Hình 2. Công thức rất đơn giản =NORM.S.DIST(-0.5,True).
Đủ đơn giản, phải không? Nếu bạn chỉ quan tâm đến những điều nhỏ nhặt, công thức này sẽ là tất cả những gì bạn cần. Tuy nhiên, các nhà nghiên cứu thường quan tâm đến các phạm vi khác ngoài phía bên trái của đường cong.
Trong Hình 3, bạn muốn biết xác suất của một thành viên ngẫu nhiên nằm giữa (độ lệch chuẩn trung bình-0,5) và (độ lệch chuẩn trung bình + 1). Không có hàm NORM.S.DIST.RANGE, vì vậy bạn chỉ cần yêu cầu xác suất trong khoảng -0,5,1). Thay vào đó, bạn phải tìm câu trả lời trong hai công thức con. Tính xác suất nhỏ hơn +1 với =NORM.S.DIST(1,True)và sau đó trừ xác suất nhỏ hơn -0,5 với =NORM.S.DIST(-.5,True). Bạn có thể thực hiện điều này trong một công thức duy nhất như trong Hình 3.
Tôi nhận thấy đây là một bài đăng dài, nhưng hình ảnh trên là hình ảnh quan trọng nhất để hiểu chức năng GAUSS mới. Đọc lại đoạn văn đó để đảm bảo bạn hiểu khái niệm. Để có xác suất một thành viên của quần thể sẽ nằm giữa hai điểm trên đường cong, bạn bắt đầu với NORM.S.DIST của điểm bên phải và trừ NORM.S.DIST của điểm bên trái. Nó không phải là khoa học tên lửa. Nó thậm chí không phức tạp như hàm VLOOKUP. Hàm luôn trả về xác suất từ cạnh trái của đường cong (-vô cùng) đến giá trị của z.
Điều gì sẽ xảy ra nếu bạn quan tâm đến xác suất lớn hơn một kích thước nhất định? Để tìm cơ hội lớn hơn (trung bình + 1 độ lệch chuẩn), bạn có thể bắt đầu với 100% và trừ khả năng nhỏ hơn (trung bình + 1 độ lệch chuẩn). Đây sẽ là =100%-NORM.S.DIST(1,True). Vì 100% giống với 1, bạn có thể rút ngắn công thức thành =1-NORM.S.DIST(1,True). Hoặc, bạn có thể nhận ra rằng đường cong là đối xứng và yêu cầu NORM.S.DIST (-1, True) để nhận được câu trả lời tương tự.
Đối với những bạn OCD như tôi, tôi có thể đảm bảo với bạn rằng nếu =SUM(30.85,53.28,15.87)bạn kết thúc với 100%. Tôi biết vì tôi đã kiểm tra nó trong trang tính.
Quay trở lại Hình 3 - bạn nên biết cách tính xác suất từ hai điểm bất kỳ z1 và z2. Trừ NORM.S.DIST (z2, True) -NORM.S.DIST (z1, True) và bạn sẽ có câu trả lời. Hãy xem xét trường hợp rất đặc biệt trong đó z1 là giá trị trung bình. Bạn đang cố gắng tìm ra xác suất ai đó nằm giữa độ lệch chuẩn trung bình và +1,5 so với giá trị trung bình, như được minh họa trong Hình 6.
Sử dụng những gì bạn đã học được từ Hình 3, cái nào trong số này sẽ tìm được xác suất của diện tích nằm dưới đường cong ở trên?
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) - Không có điều nào ở trên
Bạn đã làm như thế nào? Miễn là bạn trả lời A, B hoặc C, bạn đã đạt 100% điểm trong bài kiểm tra. Xin chúc mừng. Như tôi đã nói, nó thực sự không phải là khoa học tên lửa.
Đối với những người bạn yêu thích phím tắt, hãy nhớ rằng có 50% xác suất rằng một cái gì đó sẽ nhỏ hơn hoặc bằng giá trị trung bình. Khi bạn thấy = NORM.S.DIST (0, True), bạn có thể ngay lập tức nghĩ, "Ồ - đó là 50%!". Vì vậy, câu trả lời B ở trên có thể được viết lại thành
=NORM.S.DIST(1.5,True)-50%
Nhưng nếu bạn yêu thích phím tắt, bạn ghét nhập 50% và sẽ rút ngắn nó thành .5:
=NORM.S.DIST(1.5,True)-.5
Bạn có thể sử dụng đối diện đối xứng của khu vực dưới đường cong không? Đúng, = .5-NORM.S.DIST (-1,5, Đúng) sẽ cho bạn kết quả tương tự. Vì vậy, câu đố ở trên có thể là:
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-.5 -
=.5-NORM.S.DIST(-1.5,True) - Tất cả những điều trên
Miễn là bạn chọn một câu trả lời, tôi sẽ cung cấp cho bạn toàn bộ tín dụng. Rốt cuộc, đó là Excel. Có năm cách để làm bất cứ điều gì và tôi sẽ chấp nhận bất kỳ câu trả lời nào hoạt động (tốt, ngoại trừ mã hóa cứng = 0,433 trong một ô).
Đối với những bạn có câu trả lời đúng cho câu hỏi cuối cùng, hãy dừng đọc. Mọi người khác sẽ cần GAUSS:
Còn về chức năng GAUSS? Chà, hàm GAUSS cung cấp cho chúng ta một cách khác để giải quyết trường hợp cụ thể trong đó phạm vi đi từ giá trị trung bình đến một điểm cao hơn giá trị trung bình. Thay vì sử dụng các câu trả lời ở trên, bạn có thể sử dụng =GAUSS(1.5).
Có… họ đã thêm một hàm cho những người không thể trừ 0,5 khỏi NORM.S.DIST!
Nếu bạn giống tôi, bạn đang hỏi, "Nghiêm túc sao? Họ đã lãng phí tài nguyên khi thêm chức năng này?" Vâng, trở lại Excel 2007, nhóm Excel đã đưa ra quyết định cho phép chúng tôi lưu tài liệu ở định dạng .ODS. Đây là định dạng Bảng tính Tài liệu Mở. Nó không phải là một định dạng do Microsoft kiểm soát. Vì họ đang cung cấp hỗ trợ cho ODS, Microsoft buộc phải thêm tất cả các chức năng mà Bảng tính Tài liệu Mở hỗ trợ. Rõ ràng, phần lớn mọi người trong tổ hợp Open Document Spreadsheet không thể nhận ra rằng câu trả lời cho câu hỏi đầu tiên của tôi là A, vì vậy họ đã thêm một hàm hoàn toàn mới.
Tôi đoán Microsoft đã không vui mừng về việc thêm hỗ trợ cho các hàm tương tự như các hàm khác đã có trong Excel. Tôi gần như có thể tưởng tượng cuộc trò chuyện giữa nhà văn công nghệ được giao nhiệm vụ viết về GAUSS trong Trợ giúp Excel và người quản lý dự án trong nhóm Excel:
Người viết: "Vì vậy, hãy cho tôi biết về GAUSS"
PM: "Nó không thành vấn đề. Lấy =NORM.S.DISTvà trừ đi 0,5. Tôi không thể tin rằng chúng tôi đã phải thêm điều này."
Sau đó, người viết đã chỉnh sửa các nhận xét biên tập và đưa ra chủ đề Trợ giúp này:
Vì vậy - hãy để tôi cung cấp chủ đề trợ giúp thay thế này:
GAUSS (z) - Tính xác suất để một thành viên của quần thể bình thường chuẩn sẽ nằm giữa độ lệch chuẩn trung bình và + z so với giá trị trung bình.
- z Bắt buộc. Số độ lệch chuẩn trên giá trị trung bình. Nói chung trong khoảng từ +0.01 đến +3.
- Đã thêm vào Excel 2013 để hỗ trợ những người không thể trừ hai số.
- Không có ý nghĩa đặc biệt đối với các giá trị âm của Z. Để tính xác suất mà một thứ gì đó nằm trong khoảng -1,5 đến giá trị trung bình, hãy sử dụng
=GAUSS(1.5). - Sẽ không hoạt động trong Excel 2010 trở về trước. Trong Excel 2010 trở về trước, hãy sử dụng
=NORM.S.DIST(z,True)-0.5.
Bạn đã có nó… nhiều hơn những gì bạn muốn biết về GAUSS. Nó chắc chắn nhiều hơn những gì tôi muốn biết. Nhân tiện, các cuốn sách Chuyên sâu về Excel của tôi cung cấp mô tả đầy đủ về tất cả 452 hàm trong Excel. Kiểm tra phiên bản trước, Excel 2010 In Depth hoặc Excel 2013 In Depth mới sẽ được phát hành vào tháng 11 năm 2012.
