Câu hỏi này xuất hiện mỗi thập kỷ một lần: Bạn có thể tính Trung vị trong bảng tổng hợp không. Theo truyền thống, câu trả lời là Không. Tôi nhớ vào năm 2000 khi tôi thuê Excel MVP Juan Pablo Gonzalez để viết một macro tuyệt vời tạo ra các báo cáo trông giống như bảng tổng hợp nhưng có Trung vị.
Vì vậy, khi Alex trong buổi hội thảo Power Excel ở Houston của tôi hỏi về việc tạo các trung bình, tôi đã nhanh chóng nói "Không". Nhưng sau đó… tôi tự hỏi liệu DAX có chức năng trung bình không. Tìm kiếm nhanh trên Google và Có! Có một hàm DAX cho Trung vị.
Sử dụng DAX trong bảng tổng hợp cần những gì? Bạn cần phiên bản Windows của Excel chạy Excel 2013 hoặc mới hơn.
Đây là tập dữ liệu rất đơn giản của tôi mà tôi sẽ sử dụng để kiểm tra cách tính trung bình của bảng tổng hợp. Bạn có thể thấy giá trị trung bình cho Chicago phải là 5000 và trung bình cho Cleveland phải là 17000. Trong trường hợp của Chicago, có năm giá trị, vì vậy Trung vị sẽ là giá trị cao thứ 3. Nhưng đối với toàn bộ tập dữ liệu, có 12 giá trị. Điều đó có nghĩa là giá trị trung bình nằm trong khoảng từ 11000 đến 13000. Excel tính trung bình hai giá trị đó để trả về 12000.
Để bắt đầu, hãy chọn một ô trong dữ liệu của bạn và nhấn Ctrl + T để định dạng dữ liệu dưới dạng bảng.
Sau đó, chọn Chèn, Bảng tổng hợp. Trong hộp thoại Chèn PivotTable, hãy chọn hộp Thêm Dữ liệu Này vào Mô hình Dữ liệu.
Trong Trường bảng tổng hợp, hãy chọn Khu vực và Quận. Nhưng không chọn Bán hàng. Thay vào đó, hãy nhấp chuột phải vào tiêu đề Bảng và chọn Biện pháp mới. "Đo lường" là một cái tên ưa thích cho một trường được tính toán. Các phép đo mạnh hơn các trường được tính toán trong bảng tổng hợp thông thường.
Trong hộp thoại Xác định Đo lường, hãy điền vào bốn mục nhập được hiển thị bên dưới:
- Tên đo lường: Trung bình doanh số bán hàng
- Công thức
=MEDIAN((Sales)) - Định dạng số: Số
- Vị trí thập phân: 0
Sau khi tạo thước đo, nó được thêm vào danh sách các trường, nhưng bạn phải chọn mục nhập để thêm nó vào vùng Giá trị của bảng tổng hợp. Như bạn có thể thấy bên dưới, bảng xoay vòng đang tính toán chính xác các giá trị trung bình.
Xem video
Bản ghi video
Học Excel từ Podcast, Tập 2197: Giá trị trung bình trong bảng tổng hợp.
Tôi phải nói với bạn, tôi cực kỳ vui mừng về điều này. khi tôi đang ở Houston để thực hiện một hội thảo Power Excel ở đó, và Alex giơ tay, nói, "Này, có cách nào để thực hiện trung vị trong bảng xoay không?" Không, bạn không thể làm số trung vị trong bảng tổng hợp. Tôi nhớ cách đây 25 năm, người bạn tốt của tôi, Juan Pablo Gonzales, thực sự đã đưa một macro để làm tương đương với trung vị, không sử dụng bảng xoay - điều đó là không thể.
Nhưng tôi đã có một tia lửa. Tôi nói, "Chờ một chút," và tôi mở một trình duyệt và tôi tìm kiếm "DAX median", và chắc chắn, có một hàm MEDIAN trong DAX, có nghĩa là chúng tôi có thể giải quyết vấn đề này.
Được rồi, vì vậy, để sử dụng DAX, bạn phải ở trong Excel 2013 hoặc Excel 2016 hoặc Excel 2010 và có bổ trợ Power Pivot; bạn không cần phải có tab Power Pivot, chúng tôi chỉ cần có Mô hình Dữ liệu. Ổn thỏa? Vì vậy, tôi sẽ chọn dữ liệu này, tôi sẽ biến nó thành một bảng bằng Ctrl + T, và họ đặt cho nó một cái tên không tưởng là "Bảng 4". Trong trường hợp của bạn, nó có thể là "Bảng 1". Và chúng tôi sẽ chèn Bảng Pivot, thêm dữ liệu này vào Mô hình Dữ liệu, nhấp vào OK, và chúng tôi sẽ chọn Khu vực ở phía bên trái, chứ không phải Bán hàng. Thay vào đó, tôi muốn tạo một phép đo mới được tính toán. Vì vậy, tôi đến đây với bảng và nhấp chuột phải và nói, Thêm số đo. Và thước đo này sẽ được gọi là "Trung vị của Doanh số", và công thức sẽ là: = MEDIAN. Nhấn tab ở đó và chọn Bán hàng,đóng ngoặc. Tôi có thể chọn đây là số không có chữ số thập phân, sử dụng dấu phân cách hàng nghìn và nhấp vào OK. Và, hãy xem, trường mới của chúng tôi được thêm vào đây, chúng tôi phải đánh dấu chọn để thêm nó và nó cho biết mức trung bình cho Midwest là 12.000.
Bây giờ hãy kiểm tra điều đó. Vì vậy, ở đây, toàn bộ Trung Tây, trung bình của toàn bộ tập dữ liệu nằm trong khoảng từ 11.000 đến 13.000. Vì vậy, nó có giá trị trung bình là 11 và 13, chính xác là những gì trung bình sẽ làm trong Excel thông thường. Bây giờ chúng ta hãy thêm quận vào và nó cho biết trung bình của Chicago là 5.000, trung bình của Cleveland là 17.000; Tổng số Trung Tây và tổng số 12.000. Tất cả những điều đó đều đúng. Điều đó tuyệt vời như thế nào? Cuối cùng, trung vị trong bảng tổng hợp, nhờ vào trường được tính toán hoặc số đo, như nó được gọi và Mô hình dữ liệu.
Bây giờ, nhiều mẹo yêu thích của tôi - mẹo cho buổi hội thảo Power Excel trực tiếp của tôi - trong cuốn sách này LIVe, 54 mẹo hay nhất mọi thời đại. Nhấp vào "Tôi" ở góc trên cùng bên phải để đọc thêm về cuốn sách.
Ổn thỏa. Tóm lại: Bạn có thể thực hiện trung vị trong bảng tổng hợp không? Ồ không, vâng, bạn có thể làm được, nhờ Mô hình dữ liệu. Bạn có thể tạo một dải phân cách; Ctrl + T để biến dữ liệu của bạn thành một bảng; Chèn bảng tổng hợp; và chọn hộp đó, Thêm dữ liệu này vào Mô hình Dữ liệu; bấm chuột phải vào tên bảng và chọn số đo mới, và số đo sẽ là = MEDIAN ((Bán hàng)). Thật tuyệt vời.
Cảm ơn Alex đã xuất hiện trong buổi hội thảo của tôi và đặt câu hỏi đó, 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: pivot-table-median.xlsx
