Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm SUBTOTAL trong Excel trong Excel để tính toán thống kê tóm tắt.
Tổng quan về chức năng SUBTOTAL
Hàm SUBTOTAL Tính toán thống kê tóm tắt cho một chuỗi dữ liệu. Số liệu thống kê có sẵn bao gồm, nhưng không giới hạn ở mức trung bình, độ lệch chuẩn, số lượng, tối thiểu và tối đa. Xem danh sách đầy đủ bên dưới trong phần đầu vào chức năng:
Để sử dụng Hàm bảng tính Excel SUBTOTAL, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm SUBTOTAL Cú pháp và đầu vào:
| 1 | = SUBTOTAL (function_num, REF1) |
function_num - Một số đại diện cho phép toán nào để thực hiện.
REF1 - Phạm vi hoặc tham chiếu chứa dữ liệu để tính toán.
Chức năng SUBTOTAL là gì?
SUBTOTAL là một trong những chức năng độc đáo trong bảng tính vì nó có thể cho biết sự khác biệt giữa các ô ẩn và ô không ẩn. Điều này có thể tỏ ra khá hữu ích khi xử lý các phạm vi được lọc hoặc khi bạn cần thiết lập tính toán dựa trên các lựa chọn của người dùng khác nhau. Vì nó cũng biết bỏ qua các hàm SUBTOTAL khác khỏi các tính toán của nó, chúng tôi cũng có thể sử dụng nó trong dữ liệu tổng hợp lớn mà không sợ phải đếm hai lần.
Tóm tắt cơ bản với SUBTOTAL
Giả sử bạn có một bảng doanh số sản phẩm được sắp xếp và muốn tạo tổng số cho từng sản phẩm, cũng như tạo tổng số tổng thể. Bạn có thể sử dụng PivotTable hoặc bạn có thể chèn một số công thức. Hãy xem xét bố cục này:
Tôi đã đặt một số hàm SUBTOTAL trong các ô B5 và B8 trông giống như
| 1 | = SUBTOTAL (9, B2: B4) |
Từ cú pháp, bạn có thể sử dụng nhiều số khác nhau cho đối số đầu tiên. Trong trường hợp cụ thể của chúng tôi, chúng tôi đang sử dụng 9 để chỉ ra rằng chúng tôi muốn tính tổng.
Hãy tập trung vào ô B9. Nó có công thức này, bao gồm toàn bộ phạm vi dữ liệu cột B, nhưng không bao gồm các tổng phụ khác.
| 1 | = SUBTOTAL (9, B2: B8) |
LƯU Ý: Nếu bạn không muốn tự mình viết tất cả các công thức tóm tắt, bạn có thể đi tới ruy-băng Dữ liệu và sử dụng trình hướng dẫn Đại cương - Tổng phụ. Nó sẽ tự động chèn các hàng và đặt các công thức cho bạn.
Sự khác biệt trong các đối số đầu tiên
Trong ví dụ đầu tiên, chúng tôi sử dụng số 9 để chỉ ra rằng chúng tôi muốn tính tổng. Sự khác biệt giữa việc sử dụng 9 và 109 sẽ là cách chúng ta muốn hàm xử lý các hàng ẩn. Nếu bạn sử dụng ký hiệu 1XX, hàm sẽ không bao gồm các hàng đã được ẩn hoặc lọc theo cách thủ công.
Đây là bàn của chúng ta trước đây. Chúng tôi đã chuyển các chức năng để có thể thấy sự khác biệt giữa đối số 9 và 109. Với tất cả những gì có thể nhìn thấy, kết quả là như nhau.
Nếu chúng ta áp dụng bộ lọc để lọc ra giá trị của 6 trong cột B, thì hai hàm vẫn giữ nguyên.
Nếu chúng ta ẩn các hàng theo cách thủ công, chúng ta sẽ thấy sự khác biệt. Hàm 109 có thể bỏ qua hàng ẩn trong khi hàm 9 thì không.
Thay đổi hoạt động toán học với SUBTOTAL
Đôi khi bạn có thể muốn cung cấp cho người dùng của mình khả năng thay đổi loại phép tính được thực hiện. Ví dụ, họ muốn lấy tổng hay trung bình. Vì SUBTOTAL điều khiển phép toán bằng một số đối số, bạn có thể viết nó trong một công thức duy nhất. Đây là thiết lập của chúng tôi:
Chúng tôi đã tạo một danh sách thả xuống trong D2, nơi người dùng có thể chọn “Tổng” hoặc “Trung bình”. Công thức trong E2 là:
| 1 | = SUBTOTAL (IF (D2 = "Trung bình", 1, IF (D2 = "Tổng", 9)), B2: B4) |
Ở đây, hàm IF sẽ xác định đối số số nào sẽ cung cấp cho SUBTOTAL. Nếu A5 là "Trung bình", thì nó sẽ xuất ra 1 và SUBTOTAL sẽ cho mức trung bình là B2: B4. Hoặc, nếu A5 bằng “Tổng”, thì IF xuất ra 9 và chúng ta nhận được một kết quả khác.
Bạn có thể mở rộng khả năng này bằng cách sử dụng bảng tra cứu để liệt kê ra nhiều loại thao tác hơn nữa mà bạn muốn thực hiện. Bảng tra cứu của bạn có thể trông như thế này
Sau đó, bạn có thể thay đổi công thức trong E2 thành
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Công thức có điều kiện với SUBTOTAL
Mặc dù SUBTOTAL có nhiều hoạt động mà nó có thể thực hiện, nhưng nó không thể tự kiểm tra các tiêu chí. Tuy nhiên, chúng ta có thể sử dụng nó trong cột trợ giúp để thực hiện thao tác này. Khi bạn có một cột dữ liệu mà bạn biết sẽ luôn có một phần dữ liệu trong đó, bạn có thể sử dụng khả năng SUBTOTALs để phát hiện các hàng ẩn.
Đây là bảng chúng tôi sẽ làm việc với trong ví dụ này. Cuối cùng, chúng tôi muốn có thể tính tổng các giá trị cho “Apple”, nhưng cũng cho phép người dùng lọc cột Qty.
Đầu tiên, tạo một cột trợ giúp sẽ chứa hàm SUBTOTAL. Trong C2, công thức là:
| 1 | = SUBTOTAL (103, A2) |
Hãy nhớ rằng 103 có nghĩa là chúng ta muốn thực hiện COUNTA. Tôi khuyên bạn nên sử dụng COUNTA vì sau đó bạn có thể điền vào ô tham chiếu A2 của mình một trong hai số hoặc văn bản. Bây giờ bạn sẽ có một bảng giống như sau:
Điều này thoạt đầu có vẻ không hữu ích vì tất cả các giá trị chỉ là 1. Tuy nhiên, nếu chúng ta ẩn hàng 3, thì “1” đó trong C3 sẽ chuyển thành 0 vì nó đang trỏ vào một hàng bị ẩn. Mặc dù không thể có hình ảnh hiển thị giá trị của ô ẩn cụ thể, nhưng bạn có thể kiểm tra nó bằng cách ẩn hàng và sau đó viết một công thức cơ bản như thế này để kiểm tra.
| 1 | = C3 |
Bây giờ chúng ta có một cột sẽ thay đổi giá trị tùy thuộc vào việc nó có bị ẩn hay không, chúng ta đã sẵn sàng để viết phương trình cuối cùng. SUMIFS của chúng ta sẽ trông như thế này
Trong công thức này, chúng tôi sẽ chỉ tính tổng các giá trị từ cột B khi cột A bằng "Apple", và giá trị trong cột C là 1 (hay còn gọi là hàng không bị ẩn). Giả sử rằng người dùng của chúng tôi muốn lọc ra 600, vì nó có vẻ cao bất thường. Chúng ta có thể thấy rằng công thức của chúng ta cho kết quả chính xác.
Với khả năng này, bạn có thể áp dụng séc cho COUNTIFS, SUMIFS hoặc thậm chí SUMPRODUCT. Bạn thêm khả năng cho phép người dùng của mình điều khiển một số máy thái bàn và bạn đã sẵn sàng tạo một trang tổng quan tuyệt vời.
SUBTOTAL trong Google Trang tính
Hàm SUBTOTAL hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ví dụ về SUBTOTAL trong VBA
Bạn cũng có thể sử dụng hàm SUBTOTAL trong VBA. Kiểu:application.worksheet functions.subtotal (function_num, reh1)
Thực thi các câu lệnh VBA sau
| 1234567891011121314151617 | Range ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Range ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Range ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Range ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Range ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Range ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Range ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Range ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Phạm vi ("E7") = Application.WorksheetFunction.Subtotal (1, Phạm vi ("E2: E5"))Phạm vi ("E8") = Application.WorksheetFunction.Subtotal (2, Phạm vi ("E2: E5"))Phạm vi ("E9") = Application.WorksheetFunction.Subtotal (4, Phạm vi ("E2: E5"))Phạm vi ("E10") = Application.WorksheetFunction.Subtotal (5, Phạm vi ("E2: E5"))Phạm vi ("E11") = Application.WorksheetFunction.Subtotal (9, Phạm vi ("E2: E5")) |
sẽ tạo ra kết quả sau
Đối với các đối số của hàm (function_num, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Quay lại danh sách tất cả các hàm trong Excel
