Bạn đã biết hàm SUBTOTAL có chức năng gì hay chưa? Công thức của hàm SUBTOTAL thế nào? Ví dụ cho hàm SUBTOTAL ra sao? Ứng dụng của hàm SUBTOTAL trong công việc và học tập thế nào? Để trả lời 4 thắc mắc trên, sau đây mình sẽ hướng dẫn các bạn cách sử dụng hàm SUBTOTAL trong Excel này cụ thể như sau.

Bạn Đang Xem: Subtotal là gì? Cách dùng hàm Subtotal trong Excel chi tiết nhất

1. Hàm SUBTOTAL là gì?

Hàm SUBTOTAL của Excel trả về kết quả tổng hợp cho những giá trị được cung cấp. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và các thứ khác (xem bảng phía bên dưới) và hàm SUBTOTAL có thể gồm có hoặc loại trừ các giá trị trong các hàng ẩn.

Mục tiêu của hàm SUBTOTAL là nhận một tổng số phụ trong list hoặc cơ sở tài liệu.

2. Công thức tính của hàm SUBTOTAL

Công thức SUBTOTAL Excel bằng tiếng Anh như sau:

=SUBTOTAL (function_num, ref1, [ref2], …)

Đơn giản hơn lệnh Subtotal : =SUBTOTAL(a,b,c,..)

Trong số đó:

– Function_num: Một số chỉ định hàm nào sẽ sử dụng để tính tổng phụ trong list. Xem bảng sau đây để biết list đầy đủ.

– a,b,c: là các giá trị đi kèm với SUBTOTAL ví dụ như các giá trị có trong bảng sau:

Công thức sử dụng hàm subtotal trong Excel

>> Xem thêm: Cách dùng hàm WEEKDAY trong Excel chi tiết cụ thể nhất

3. Cách sử dụng hàm SUBTOTAL

Sử dụng hàm SUBTOTAL để lấy tổng phụ trong list hoặc cơ sở tài liệu. SUBTOTAL có khả năng sử dụng nhiều chức năng khác nhau khi tổng phụ, gồm có AVERAGE, COUNT, MAX và các chức năng khác (xem bảng phía trên để biết list đầy đủ). Theo mặc định, SUBTOTAL loại trừ các giá trị trong các hàng được ẩn bởi bộ lọc, như được giảng giải phía bên dưới. Điều này làm cho SUBTOTAL rất hữu ích trong Bảng Excel.

Để tìm hiểu chi tiết cụ thể về phong thái sử dụng hàm SUBTOTAL trong Excel, độc giả có thể tham khảo nội dung Video sau đây ở 02 phút 45 giây nhé.

Hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel

4. Ví dụ về phong thái sử dụng hàm SUBTOTAL

Ở chỗ này là một vài ví dụ về phong thái dùng hàm SUBTOTAL được định cấu hình thành SUM, COUNT và AVERAGE các giá trị trong một phạm vi. Lưu ý sự khác biệt duy nhất là giá trị được sử dụng cho đối số function_num:

= SUBTOTAL (109, phạm vi) // SUM

= SUBTOTAL (103, phạm vi) // COUNT

= SUBTOTAL (101, phạm vi) // AVERAGE

Giá trị ẩn thủ công:

Lưu ý các giá trị được sử dụng cho đối số function_num là “được ghép nối” (ví dụ: 1-101, 2-102, 3-103, …).

Điều này còn có liên quan đến cách SUBTOTAL xử lý các hàng ẩn thủ công. Khi function_num nằm trong khoảng chừng 1-11, SUBTOTAL gồm có các ô đã được ẩn thủ công. Khi function_num nằm trong khoảng chừng 101-111, SUBTOTAL bỏ qua các giá trị trong các hàng được ẩn thủ công.

Khám phá thêm cuốn sách Excel để biết thêm nhiều tri thức hay từ các Chuyên Viên Unica.

Ví dụ cách sử dụng hàm subtotal trong Execl

SUBTOTAL luôn bỏ qua các giá trị trong các ô được ẩn bằng bộ lọc. Các giá trị trong các hàng “được lọc ra” không được gồm có, bất kể function_num.

Xem Thêm : ​Cư ngụ là gì? Những quyền lợi mà người cư ngụ sẽ được hưởng

Ghi chú:

– Khi function_num nằm trong khoảng chừng 1-11, SUBTOTAL gồm có các giá trị bị ẩn

– Khi function_num nằm trong khoảng chừng 101-111, SUBTOTAL loại trừ các giá trị bị ẩn

– Trong list được lọc, SUBTOTAL luôn bỏ qua các giá trị trong các hàng ẩn, bất kể function_num.

SUBTOTAL bỏ qua các tổng phụ khác tồn tại trong tài liệu tham khảo được bỏ qua để ngăn chặn tính hai lần SUBTOTAL được thiết kế để thao tác với những giá trị tài liệu dọc được sắp xếp theo chiều dọc. Trong phạm vi Ngang, giá trị trong các cột ẩn luôn luôn được gồm có.

Ví dụ: đây là cách chúng ta có thể tạo công thức Tổng phụ 9 để tổng hợp các giá trị trong các ô từ C2 đến C8:

Mô tả cách sử dụng hàm subtotal trong Excel. Hình 3

Để thêm số hàm vào công thức, bấm lưu ban vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức hoàn thành sẽ như vậy này:

= SUBTOTAL (9, C2: C8)

Tính tổng vùng được lọc

Ví dụ: Quan sát bảng sau, yêu cầu lọc ra KPI cho đội A.

– Bước 1: Dùng chuột bôi đen bảng Excel chứa tài liệu. Trên thanh dụng cụ, chọn trang chủ -> Sort and Filter -> Filter.

Tính tổng vùng được lọc – Hình 1

– Bước 2: Trong cột C, bạn nhấn vào biểu tượng tam giác lộn ngược rồi tích chọn A và nhấn OK.

Tính tổng vùng được lọc – Hình 2

– Bước 3: Nhập công thức =SUBTOTAL(9,D2:D11) tại ô bạn phải lấy kết quả.

Kết quả sau lúc hoàn thành như sau:

Tính tổng vùng được lọc – Hình 3

Đếm các ô được lọc không trống

Ví dụ: Quan sát bảng tài liệu, yêu cầu đếm số lượng sinh viên nhóm C đã có link bài tập để thống kê.

– Bước 1: Lọc ra các sinh viên nhóm C như các thao tác lọc đã hướng dẫn phía trên.

– Bước 2: Nhập công thức =SUBTOTAL(3,D4:D14) vào ô muốn lấy kết quả.

Trong số đó:

Xem Thêm : Chiêu trò “chiếu sớm”

– function_num = 3: Gía trị đối số tương ứng với hàm cần sử dụng, trong ví dụ này tất cả chúng ta chọn hàm COUNTA – tương ứng với số 3.

– ref1 = D4:D14: là phạm vi cần tính tổng.

Kết quả sau lúc hoàn thành như sau:

Xem thêm: Học tin học văn phòng trực tuyến trọn đời chỉ từ 99K

Kết quả sau lúc hoàn thành

Đánh số trật tự nhanh

Cách thực hiện vô cùng đơn giản, bạn chỉ có nhập công thức =SUBTOTAL(3,$B$2:B2) vào ô A2.

Trong số đó:

– Funcation_num là 3: Ứng với hàm COUNTA – đếm ô không trống.

– ref1 là $B$2:B2: Đếm ký tự phát sinh trong khoảng chừng $B$2:B2, trong đó ô B2 được nhất định bằng dấu”$” ở trước địa chỉ hàng và ô.

Kết quả sau lúc hoàn thành như sau:

Kết quả sau lúc hoàn thành

5. Cách fix lỗi #VALUE! hàm SUBTOTAL

– Lỗi #VALUE xẩy ra khi số xác định chức năng funcation_num không nằm trong khoảng chừng 1-11 hoặc101-111. Hoặc tham chiếu ref tham chiếu đến những ô trong một trang tính.

Ví dụ: bạn đang dùng SUBTOTAL ở sheet STT2 nhưng tham chiếu đến một ô, một dãy nào đó ở sheet STT1.

Fix lỗi#VALUE! trong hàm SUBTOTAL

– Cách fix lỗi như sau:

+ Nếu sai số xác định chức năng, điều trước tiên bạn phải làm là kiểm tra lại xem tôi đã dùng đúng số chức năng chưa hoặc trong quá trình nhập có dư ký tự nào không.

+ Nếu nguyên nhân là vì lỗi tham chiếu, bạn hãy xóa phần tham chiếu sai đi, thay thế lại bằng dãy ô đúng được đặt trong cùng một trang tính mà bạn muốn thực hiện với hàm SUBTOTAL.

Như vậy là tôi đã giới thiệu cho những bạn về phong thái sử dụng hàm SUBTOTAL, mong rằng nội dung bài viết này sẽ giúp ích được nhiều cho những bạn.

Chúc các bạn có một buổi học Excel trực tuyến hiệu quả.

>> Xem thêm: Cách dùng hàm so sánh chuỗi trong excel kèm ví dụ minh họa

Tags: ExcelUSDUSD