Chris có một lượng lớn dữ liệu trong một trang tính và anh ấy muốn phân tích dữ liệu dựa trên các nhóm khác nhau trong đó. Ví dụ: anh ta có dữ liệu trong các ô A2: B36001, trong đó hàng 1 chứa tiêu đề cột Thời gian và Tín hiệu. Anh ta muốn chia dữ liệu thành các nhóm bao gồm một số giá trị tuần tự tùy ý, sau đó trích xuất cho mỗi nhóm một giá trị trung bình cho Thời gian, giá trị trung bình cho Tín hiệu và độ lệch chuẩn cho Tín hiệu.

Cách dễ nhất để xử lý loại yêu cầu này là thêm một cột được sử dụng để biểu thị số nhóm cho mỗi hàng. Làm theo các bước sau:

  1. Đặt Nhóm đề mục vào ô C1.

  2. Trong ô E1, nhập số giá trị cần có trong mỗi nhóm.

Ví dụ: nếu bạn muốn mỗi nhóm chứa 10 giá trị tuần tự, hãy nhập số 10 vào ô E1.

  1. Trong ô C2 nhập công thức này: = INT ROW () – ROW ($ C $ 2 / $ E $ 1) +1. Sao chép công thức trong ô C2 vào phạm vi C3: C36001. Cột C hiện chứa “số nhóm” cho mỗi hàng, dựa trên giá trị trong ô E1.

Nếu E1 là 10, bạn sẽ có 3600 nhóm, 1 đến 3600. Nếu E1 là 100, bạn sẽ có 360 nhóm, 1 đến 360.

Với số nhóm được thiết lập, bạn đã sẵn sàng thực hiện phân tích. Có một số cách bạn có thể làm điều này. Một cách là sử dụng khả năng tính tổng phụ của Excel. Chọn một trong các ô trong vùng dữ liệu và làm theo các bước sau:

  1. Chọn Tổng phụ từ menu Dữ liệu. Excel sẽ hiển thị hộp thoại Tổng phụ.

  2. Thay đổi danh sách thả xuống At Every Change In thành Nhóm.

  3. Thay đổi danh sách thả xuống Sử dụng Hàm để cho biết loại thống kê bạn muốn tính toán cho mỗi nhóm.

  4. Thay đổi vùng Add Subtotal To để chỉ Thời gian hoặc Tín hiệu được chọn, nếu thích hợp.

  5. Bấm OK.

Excel nhóm và tổng phụ dữ liệu, theo chỉ dẫn. Bạn có thể ẩn chi tiết (và chỉ hiển thị tổng phụ) bằng cách nhấp vào số 2 nhỏ (có hộp xung quanh) trong vùng phác thảo ở bên trái của trang tính. Nếu sau này bạn muốn thay đổi những gì được tính hoặc nếu bạn cần thay đổi số lượng mục trong mỗi nhóm, chỉ cần xóa tổng phụ (Dữ liệu | Tổng phụ | Xóa tất cả) và lặp lại các bước trên.

Một cách khác để lấy thống kê từ dữ liệu của bạn là sử dụng PivotTable. Đảm bảo rằng không có tổng phụ trong dữ liệu và chọn một ô trong dữ liệu. Sau đó làm theo các bước sau:

  1. Chọn PivotTable và Báo cáo PivotChart từ menu Dữ liệu. Excel khởi động Trình hướng dẫn PivotTable và PivotChart.

  2. Bấm tiếp. (Các lựa chọn mặc định ở bước 1 là OK.) Bước 2 của Trình hướng dẫn PivotTable và PivotChart được hiển thị.

  3. Toàn bộ dải dữ liệu của bạn (A1: C36001) phải được chọn. Bấm tiếp. Bước 3 của Trình hướng dẫn PivotTable và PivotChart được hiển thị.

  4. Đảm bảo Trang tính mới được chọn, sau đó nhấp vào Kết thúc. Excel tạo PivotTable trống và hiển thị danh sách trường.

  5. Kéo trường Nhóm vào vùng Hàng.

  6. Kéo trường Thời gian vào vùng Dữ liệu.

  7. Kéo trường Tín hiệu vào vùng Dữ liệu.

  8. Kéo trường Tín hiệu một lần nữa vào vùng Dữ liệu. PivotTable bây giờ sẽ hiển thị “Đếm thời gian”, “Tổng tín hiệu” và “Tổng tín hiệu2”

cho mỗi nhóm.

  1. Trong vùng Dữ liệu, nhấp chuột phải vào một trong các nhãn “Đếm thời gian”. Excel sẽ hiển thị menu Ngữ cảnh.

  2. Chọn Cài đặt Trường từ menu Ngữ cảnh. Excel sẽ hiển thị hộp thoại Trường PivotTable.

  3. Trong danh sách Tóm tắt, hãy chọn Trung bình.

  4. Bấm OK. Tất cả các nhãn “Đếm thời gian” thay đổi thành “Thời gian trung bình”.

  5. Trong vùng Dữ liệu, bấm chuột phải vào một trong các nhãn “Tổng tín hiệu”. Excel sẽ hiển thị menu Ngữ cảnh.

  6. Chọn Cài đặt Trường từ menu Ngữ cảnh. Excel lại hiển thị hộp thoại Trường PivotTable.

  7. Trong danh sách Tóm tắt, hãy chọn Trung bình.

  8. Bấm OK. Tất cả các nhãn “Tổng tín hiệu” thay đổi thành “Trung bình của tín hiệu”.

  9. Trong vùng Dữ liệu, bấm chuột phải vào một trong các nhãn “Tổng tín hiệu2”.

Excel sẽ hiển thị menu Ngữ cảnh.

  1. Chọn Cài đặt Trường từ menu Ngữ cảnh. Excel lại hiển thị hộp thoại Trường PivotTable.

  2. Trong danh sách Tóm tắt, chọn StdDev.

  3. Bấm OK. Tất cả các nhãn “Tổng của Signal2” thay đổi thành “StdDev của Signal2.”

Bây giờ bạn làm thế nào dữ liệu mong muốn. Bạn có thể thích một thiết lập chỉ hiển thị một hàng cho mỗi danh mục cho dữ liệu của mình. Nếu vậy, chỉ cần nhấp vào tiêu đề Dữ liệu và giữ nút chuột khi bạn từ từ di chuyển chuột sang phải. Khi bạn di chuyển con trỏ chuột vào vùng tiêu đề Tổng, một biểu tượng nhỏ gần con trỏ chuột cho thấy sự “thay đổi” trong bố cục. Thả nút chuột và bạn sẽ chỉ có một hàng cho mỗi nhóm trong dữ liệu của mình.

Nếu bạn cần thay đổi số lượng mục dữ liệu trong mỗi nhóm, chỉ cần quay lại trang tính dữ liệu và thay đổi ô E1 thành một giá trị khác. Sau đó, bạn có thể quay lại PivotTable, nhấp chuột phải vào nó và chọn Làm mới dữ liệu.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2771) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: