Hàm SUBTOTAL trong Excel

Hàm SUBTOTAL và ví dụ về cách dùng đơn giản, dễ hiểu

Hàm SUBTOTAL và ví dụ về cách dùng đơn giản, dễ hiểu

avata
QTV Điện Thoại Vui
30/12/2023

Hàm SUBTOTAL trong Excel là gì? Hàm SUBTOTAL là hàm tính tổng của một phạm vi dữ liệu trong bảng tính. Vậy công thức của hàm SUBTOTAL và cách dùng hàm cụ thể như nào. Cùng tìm hiểu qua bài viết dưới đây để hiểu rõ hơn về hàm này nhé!

Hàm SUBTOTAL trong Excel là gì? 

Hàm SUBTOTAL trong Excel là một hàm tính tập hợp con của một danh sách hoặc cơ sở dữ liệu trong bảng tính Excel. Hàm này được ứng dụng trong nhiều trường hợp như: tính tổng, tính trung bình, đánh số thứ tự, tìm giá trị lớn nhất hoặc nhỏ nhất trong bảng dữ liệu,...            

Hàm SUBTODAY trong Excel là gì?

Lợi ích khi sử dụng hàm SUBTOTAL trong bảng tính Excel là:

  • Tính tổng giá trị trong các hàng được chọn một cách linh hoạt.
  • Dễ dàng tính toán cả các giá trị ẩn trong bảng dữ liệu.
  • Trường hợp giá trị ref1, ref2,... có chứa hàm SUBTOTAL thì sẽ tự động bỏ qua để không bị tính trùng 2 lần.

Công thức hàm SUBTOTAL

Cú pháp hàm SUBTOTAL như sau: =SUBTOTAL (function_num, ref1, [ref2],…)

Trong đó: 

  • Function_num: Là một số từ 1-11 hoặc 101-111 để xác định hàm sử dụng trong tính toán tổng phụ. Số từ 1-11 được tính toán cho cả các giá trị ẩn trong vùng dữ liệu. Nếu chọn từ 101-111 sẽ tính toán cho các giá trị không ẩn trong vùng dữ liệu.
  • Ref1: Là tham chiếu hoặc phạm vi vùng được đặt tên đầu tiên để tính tổng phụ.
  • Ref2,…: Là phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 cần tính tổng phụ.

Dưới đây là bảng Function_num gồm các mã số dùng để tính trong hàm SUBTOTAL:

Function_num (bao gồm các giá trị ẩn) Function_num (bỏ qua các giá trị ẩn) Hàm Mô tả hàm
1 101 AVERAGE Tính trung bình
2 102 COUNT Đếm số ô chứa giá trị số
3 103 COUNTA Đếm số ô không trống
4 104 MAX Tìm giá trị lớn nhất
5 105 MIN Tìm giá trị nhỏ nhất
6 106 PRODUCT Nhân các ô
7 107 STDEV Tính độ lệch chuẩn mẫu 
8 108 STDEVP Tính độ lệch chuẩn trên toàn bộ số
9 109 SUM Cộng các số
10 110 VAR Ước tính độ dao động trên mẫu
11 111 VARP Ước tính độ dao động trên toàn bộ số

Một số lưu ý cần nhớ khi dùng hàm SUBTOTAL trong Excel:

  • Hàm SUBTOTAL được dùng để tính cho các cột dữ liệu hay phạm vi dọc. Hàm này không được dùng cho các hàng dữ liệu hoặc phạm vi ngang.
  • Hàm SUBTOTAL chỉ tính được giá trị của hàng ẩn khi dùng lệnh Hide, sẽ bỏ qua giá trị các hàng bị ẩn bởi lệnh Auto Filter.
  • Nếu có hàm SUBTOTAL lồng ghép trong ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua để tránh trường hợp tính toán 2 lần.
  • Bất kỳ dữ liệu nào dưới dạng 3-D, hàm SUBTOTAL sẽ trả về lỗi #VALUE!.

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

Để hiểu rõ hơn về cách dùng hàm SUBTOTAL trong Excel, bạn có thể tham khảo một số ví dụ sau đây.

Ví dụ 1: Tính tổng vùng được lọc

Trong bảng tính DANH SÁCH HOA HỒNG THÁNG, có 2 cột Nhóm và Phí hoa hồng. Bạn đang cần lọc ra tổng phí hoa hồng của đội 1. Có thể thực hiện như sau:

Bước 1: Bôi đen toàn bộ bảng dữ liệu, vào mục Home và chọn Sort and Filter.

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

Bước 2: Ở mục Sort and Filter, tiếp tục chọn Filter.

Sử dụng tính năng Sort & Filter

Bước 3: Tại cột dữ liệu Nhóm, chọn dấu mũi tên hình tam giác ngược. Sau đó, bấm chọn dấu tick vào 1 và nhấn OK.

Thực hiện lọc dữ liệu có trong cột

Bước 4: Các giá trị của Nhóm 1 trong bảng dữ liệu đã được lọc ra. Khi đó, tại ô hiển thị kết quả bạn nhập công thức: =SUBTOTAL(9,D3:D9)

Sử dụng công thức hàm SUBTOTAL

Trong đó: 

  • function_num: Lấy giá trị là 9 để tính tổng phí hoa hồng. Giá trị này tương ứng với hàm SUM (cộng các số).
  • ref1: Phạm vi cần tính tổng lấy từ D3 đến D9.

Sau khi nhập công thức, bạn bấm Enter sẽ ra kết quả như hình:

Kết quả sau khi sử dụng hàm SUBTOTAL

Ở đối số function_num, bạn cũng có thể lấy giá trị 109 để bỏ qua các giá trị đã bị ẩn để tính tổng các hàng đã lọc.

Ví dụ 2: Đếm số ô được lọc không trống

Giả sử bạn có bảng DANH SÁCH SẢN PHẨM CẦN MUA, bao gồm danh mục đồ gia dụng và mỹ phẩm. Bạn đang cần đếm số lượng sản phẩm của nhóm đồ gia dụng đã có link mua hàng để tiện thống kê. 

Bước 1: Đầu tiên, bạn cần tiến hành lọc ra các sản phẩm thuộc nhóm đồ gia dụng bằng lệnh Auto Filter. Các thao tác lọc tương tự như ví dụ ở trên, vào mục Sort and Filter chọn Filer.

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

Bước 2: Tại cột Danh mục, chọn dấu mũi tên hình tam giác ngược để tick chọn mục Đồ gia dụng và nhấn OK.

Sử dụng tính năng Soft & Filter lọc dữ liệu đồ gia dụng

Bước 3: Sau khi lọc xong dữ liệu, bạn nhập cú pháp hàm SUBTOTAL ở ô kết quả: =SUBTOTAL(3,D3:D10)

Nhập cú pháp hàm SUBTOTAL để đếm số ô được lọc

Với công thức trên: 

  • function_num: là giá trị tương ứng với hàm cần dùng. Do bạn đang cần đếm các ô không trống nên giá trị này sẽ bằng 3, tương ứng với hàm COUNTA.
  • ref1: phạm vi cần tính từ D3:D10 sau khi đã lọc dữ liệu.

Bước 4: Nhấn phím Enter để thu được kết quả sau: 

Kết quả thu được khi dùng hàm SUBTOTAL đếm số ô được lọc

Ở bước nhập công thức, bạn có thể nhập đối số 103 (bỏ qua các giá trị đã bị ẩn) để tính đếm các ô được lọc không trống.

Ví dụ 3: Đánh số thứ tự nhanh

Giả sử bạn cần đánh số thứ tự cho một danh sách với các hàng liên tiếp. Bạn có thể dùng hàm SUBTOTAL trong Excel như sau: 

Bước 1: Tại cột STT, bạn nhập công thức vào ô A2 là: =SUBTOTAL(3,$B$2:B2) và nhấn Enter.

Đánh số thứ tự nhanh

Trong đó:

  • function_num: có giá trị là 3, ứng với hàm COUNTA là đếm số ô không trống.
  • ref1: nhập giá trị là $B$2:B2 để đếm ký tự trong phạm vi đó. Lúc này, bạn cần cố định ô B2 bằng cách nhấn phím F4. 

Bước 2: Sau đó, ở góc dưới bên phải của ô B2 xuất hiện dấu cộng, bạn hãy nhấp chuột kéo thẳng xuống các ô khác để sao chép công thức. 

Thực hiện đánh số thứ tự trong bảng dữ liệu

Bước 3: Bạn sẽ nhận được kết quả như hình sau:

Kết quả sau khi thực hiện đánh số thứ tự trong bảng

Lưu ý: khi sao chép công thức, giá trị phải thay đổi theo kiểu: $B$2:B3, $B$2:B4,... thì hàm mới đếm đúng.

Ví dụ 4: Giá trị lớn nhất

Trong bảng dữ liệu DANH SÁCH ĐỒ DÙNG, cần tìm ra giá trị sản phẩm có giá cao nhất. Bạn hãy sử dụng hàm SUBTOTAL để tìm ra giá trị lớn nhất trong bảng tính.

Bước 1: Tại ô tham chiếu hiện kết quả, bạn nhập hàm =SUBTOTAL(4,C3:C7).

Tìm giá trị lớn nhất bằng hàm SUBTOTAL trong Excel

Trong đó:

  • function_num: bằng 4 vì cần dùng hàm MAX để lấy giá trị lớn nhất.
  • ref1:là vùng dữ liệu cần tính, lấy từ C3:C7.

Bước 2: Sau khi nhập công thức, nhấn Enter để hiển thị kết quả.

Nhập công thức hàm SUBTOTAL trong Excel

Tương tự, bạn cũng có thể tìm ra giá trị nhỏ nhất trong bảng dữ liệu Excel với các bước như trên. Thay vào đó, ở phần giá trị đối số function_num sẽ bằng 5 - ứng với hàm MIN.

Hàm MAX trong Excel dùng để làm gì? Cách sử dụng hàm MAX hiệu quả, xem ngay!

Kết hợp hàm SUBTOTAL và một số hàm khác trong Excel

Hàm SUBTOTAL còn được sử dụng lồng ghép vào các hàm khác, chẳng hạn như hàm VLOOKUP, IF. Việc kết hợp này sẽ giúp cho hàm SUBTOTAL tính toán hiệu quả hơn.

Kết hợp Hàm SUBTOTAL và VLOOKUP

Khi hàm SUBTOTAL kết hợp với VLOOKUP sẽ có công thức sau:

=SUBTOTAL(funtion_num, VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Hàm VLOOKUP được sử dụng khi bạn cần tìm dữ liệu trong một bảng và trả về dữ liệu khớp với hàng ngang tương ứng. Khi hàm VLOOKUP kết hợp với hàm SUBTOTAL sẽ giúp bạn phân tích các bảng dữ liệu nhanh chóng mà không cần một bảng tổng hợp phức tạp.

Cùng xem ví dụ sau: 

Khi bạn có 2 bảng dữ liệu ĐIỂM KPI CỦA CÁC NHÓM và DANH SÁCH NHÂN VIÊN TỪNG NHÓM. Bạn đang muốn tìm điểm KPI cao nhất Tháng 11 của nhân viên thuộc Nhóm 2. Bạn có thể làm theo các thao tác dưới đây để tìm ra kết quả:

Bước 1: Nhập công thức tại ô cần trả kết quả:

=SUBTOTAL(4;VLOOKUP(F5;A3:D7;3;false);VLOOKUP(F7;A3:D7;3;false))

Mã hàm SUBTOTAL tương ứng là số 4 để tìm giá trị lớn nhất trong bảng.

Kết hợp Hàm SUBTOTAL và  VLOOKUP

Bước 2: Nhấn phím Enter trả kết quả.

Nhập công thức hàm SUBTOTAL kết hợp với hàm VLOOKUP trong Excel

Như vậy, hàm VLOOKUP đã trả về giá trị điểm KPI cho nhân viên nhóm 2. Và khi kết hợp với hàm SUBTOTAL sẽ tìm ra giá trị lớn nhất trong số các kết quả trả về.

Kết hợp Hàm SUBTOTAL và Hàm IF

Hàm SUBTOTAL được sử dụng như một hàm lồng ghép của hàm IF. Thường được ứng dụng khi cần lọc dữ liệu có điều kiện, có thể áp dụng khi đánh số thứ tự theo cột.

Cú pháp tổng quát là: =IF(logical_test, value_if_true, SUBTOTAL(funtion_num, ref1,...))

Ví dụ: Khi bạn có một bảng dữ liệu cần đếm số thứ tự, nhưng xuất hiện lộn xộn các hàng trống và không trống. Trong trường hợp này, bạn có thể kết hợp SUBTOTAL với hàm IF như sau:

Bước 1: Tại cột STT, bạn nhập công thức tại ô A2: =IF(B2='','',SUBTOTAL(3,$B$2:B2)).

Kết hợp hàm Subtotal và hàm IF

Khi đó với hàm IF:

  • logical_test: điều kiện của hàm IF, sẽ lấy giá trị ô B2 bằng khoảng trắng. Nếu ô B2 trống thì trả về giá trị đúng.
  • value_if_true: Giá trị trả về trống (“”) nếu điều kiện của hàm IF đúng.
  • value_if_false: Giá trị trả về nếu điều kiện hàm IF sai. Lúc này, bạn sẽ dùng hàm SUBTOTAL để đánh số thứ tự.

Trong hàm SUBTOTAL, giá trị đối số bằng 3 tương ứng với hàm COUNTA - đếm số ô không trống. Số thứ tự ở các ô có giá trị sẽ được đánh lại từ đầu khi thực hiện lọc dữ liệu có điều kiện.

Bước 2: Sau khi nhập công thức, bạn nhấn Enter rồi tiến hành sao chép công thức từ ô A2 đến các ô còn lại. Kết quả ta được bảng sau:

Nhập công thức hàm Subtotal và hàm IF

Tóm lại, nếu như có một ô trống thì hàm IF sẽ bỏ qua không đếm. Đến ô không trống thì hàm SUBTOTAL sẽ đánh số thứ tự.

Một số lỗi thường gặp khi dùng hàm SUBTOTAL trong Excel

Khi sử dụng hàm SUBTOTAL, đôi khi bạn sẽ gặp một số lỗi sau:

Lỗi #VALUE! 

Thường xảy ra do 1 trong 2 nguyên nhân:

  • Mã định chức năng function_num là số không nằm trong khoảng 1-11 hoặc 101-111. 
  • Phạm vi dữ liệu cần tính là tham chiếu dạng 3D. Nghĩa là, khi phạm vi bạn chọn đến các ô trong một trang tính khác sẽ trả về lỗi này.

Để khắc phục lỗi #VALUE!, bạn cần sửa lại thông tin số được nhập ở phần function_num và kiểm tra lại dữ liệu tham chiếu cần tính.

Lỗi #DIV/0!

Lỗi này xảy ra khi bạn sử dụng hàm để chia một số hoặc một tổng cụ thể cho 0. Thông thường là do tính giá trị trung bình cộng hoặc độ lệch chuẩn của một dãy ô không chứa số.

Để fix lỗi này, bạn cần kiểm tra lại thông tin dữ liệu đã nhập vào ô tính.

Lỗi #NAME?

Đây là lỗi do bạn nhập sai tên hàm SUBTOTAL. Vì vậy, bạn cần nhập chính xác tên hàm để không bị hiển thị lỗi này. 

Khi nhập công thức trong ô tính Excel, bạn chỉ cần nhập dấu = và chữ cái đầu S, sẽ xuất hiện một bảng gợi ý để lựa chọn. Bạn có thể bấm vào gợi ý để chọn đúng hàm SUBTOTAL.

Kết luận

Trên đây là hướng dẫn cách dùng hàm SUBTOTAL trong Excel với các ví dụ cụ thể. Bạn có thể thực hiện hàm này trong tính toán để đạt được hiệu quả cao nhất. Hy vọng bài viết sẽ giúp ích cho công việc của bạn đơn giản và dễ dàng hơn.

Bạn đang đọc bài viết Hàm SUBTOTAL và ví dụ về cách dùng đơn giản, dễ hiểu tại chuyên mục Excel trên website Điện Thoại Vui.

Hỏi và đáp
hello
HỆ THỐNG CỬA HÀNG
dtv

- Gọi tư vấn sửa chữa: 1800.2064

- Gọi góp ý - khiếu nại: 1800.2063

- Hoạt động: 08:00 – 21:00 các ngày trong tuần

Kết nối với Điện Thoại Vui
    instagraminstagramyoutubetiktokzalo
Website thành viên
  • Hệ thống bán lẻ di động toàn quốc.

    sforum
  • Kênh thông tin giải trí công nghệ cho giới trẻ.

    sforum
  • Trang thông tin công nghệ mới nhất.

    sforum

Bản quyền 2022 – © Công ty TNHH Dịch vụ sửa chữa Điện Thoại Vui – MST: 0108709419 – GPDKKD: 0316179378 do sở KH & ĐT TP.HCM cấp ngày 05/03/2020
Địa chỉ văn phòng: 30B, Hồ Hảo Hớn, phường Cô Giang, quận 1, TP Hồ Chí Minh – Điện thoại: 1800.2064 – Email: [email protected] – Chịu trách nhiệm nội dung: Bùi Văn Định.
Quý khách có nhu cầu sửa chữa vui lòng liên hệ hoặc đến trực tiếp các trung tâm Điện Thoại Vui
dtvdtv
Trang chủ
Danh mục
Cửa hàngĐặt lịch sửa
Xem thêm