Cách sử dụng hàm SUBTOTAL trong Excel dễ hiểu, có ví dụ minh họa

Hàm tổng phụ trong excel có thể dùng để cộng, đếm các ô không trống trong một dải dữ liệu đã lọc, đánh số tự động, … mời các bạn cùng tìm hiểu với bài viết dưới đây của dien may xanh để biết công thức hàm tổng phụ nhé. và sử dụng cụ thể!

1 công thức hàm tổng phụ

hàm tổng phụ trong excel được áp dụng trong nhiều trường hợp, cụ thể là cộng, tính trung bình, đếm số ô, tìm giá trị lớn nhất hoặc nhỏ nhất của dữ liệu, đánh số thứ tự, di chuyển, …

Công thức cho hàm tổng phụ như sau:

ở đâu:

• function_num: nếu function_num là 1 – 11 thì hàm tổng phụ thực hiện phép tính bằng cách bao gồm giá trị ẩn trong vùng dữ liệu có chứa các giá trị ẩn ở đó . nếu function_num được chọn trong khoảng 101 – 111 , thì hàm tổng phụ sẽ bỏ qua, không đếm các giá trị trong hàng ẩn .
• ref1, ref2,…: 1 hoặc nhiều ô hoặc phạm vi ô thành tổng phụ, tối đa 254.

mã cho function_num như sau:

function_num (bao gồm các giá trị ẩn)

function_num (bỏ qua giá trị ẩn)

tương đương

chức năng

1

101

trung bình

2

102

số lượng

3

103

tài khoản

4

104

tối đa

5

Xem thêm: 2 cách làm muối ớt xanh chấm hải sản, đồ nướng ngon đúng vị

105

tối thiểu

6

106

sản phẩm

7

107

Xem Thêm : Công thức trừ độ hở làm cửa nhôm Xingfa hệ 55, 63, 93

stdvd

8

108

nhà phát triển tiêu chuẩn

9

109

tổng số

10

110

var

11

111

varp

Ví dụ về hàm tổng phụ 2

đây là một số cách để áp dụng hàm tổng phụ phổ biến trong thực tế, hãy kiểm tra!

tổng diện tích được lọc

giả sử bạn có bảng sau và bạn cần lọc tổng kpi của nhóm a. làm như sau:

Xem thêm: Nồng độ mol là gì? Công thức tính số mol, nồng độ mol?

bước 1: ẩn bảng chứa dữ liệu để tính toán & gt; đi tới trang chủ & gt; sắp xếp và lọc & gt; bộ lọc.

bước 2: nhấp vào mũi tên hình tam giác ngược trong tiêu đề cột “nhóm”, chọn “a” và nhấn ok .

bước 3: excel lọc bảng dữ liệu nhóm a. bây giờ, trong ô để nhận kết quả, hãy nhập:

= tổng phụ (9, d2: d11)

Xem thêm: Cách dùng hàm NETWORKDAYS trong Excel để tính ngày làm việc đơn giản

Xem Thêm : 3 cách làm sốt me chua ngọt cực hấp dẫn, ăn gì cũng ngon | VinID

giải thích công thức:

• function_num = 9 là giá trị của đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị khi bạn nhập công thức cho hàm tổng phụ. ở đây, vì chúng ta cần thêm, chúng ta sẽ chọn hàm cộng, tương ứng với số 9.
• ref1 = d2: d11 là phạm vi cần thêm.

bạn sẽ nhận được kết quả sau:

lưu ý: bạn có thể sử dụng 9 (để tính tổng các giá trị bị ẩn) hoặc 109 (bỏ qua các giá trị ẩn) để tính tổng các hàng đã lọc.

đếm các ô đã lọc không trống

Giả sử bạn có một bảng dữ liệu như bảng bên dưới. Nhiệm vụ của bạn là đếm số học sinh trong nhóm c có liên kết đến bài tập để thống kê.

trước tiên, hãy tiến hành lọc ra các sinh viên trong nhóm c bằng cách lọc tương tự như hướng dẫn trong phần trước.

sau khi lọc dữ liệu, trong ô để nhận kết quả, hãy nhập:

= tổng phụ (3, d4: d14)

Xem thêm: Cách dùng hàm NETWORKDAYS trong Excel để tính ngày làm việc đơn giản

Xem Thêm : 3 cách làm sốt me chua ngọt cực hấp dẫn, ăn gì cũng ngon | VinID

giải thích công thức:

• function_num = 3 là giá trị của đối số tương ứng với hàm cần sử dụng. Bạn sẽ thấy giá trị đối số này được hiển thị khi bạn nhập công thức cho hàm tổng phụ. ở đây, vì chúng ta cần đếm các ô không trống, chúng ta sẽ chọn hàm counta, tương ứng với số 3.
• ref1 = d4: d14 là dải ô cần thêm.

kết quả sẽ như thế này:

lưu ý: bạn có thể sử dụng giá trị đối số là 3 (bao gồm các giá trị đã được ẩn theo cách thủ công bằng tính năng ẩn) hoặc 103 (bỏ qua, không đếm các giá trị ẩn theo cách thủ công) để đếm các ô không trống trong các hàng đã lọc.

Bạn có thể xem cách hoạt động của hàm counta và một số ví dụ tại đây.

đánh số nhanh

Nếu bạn chỉ cần đánh số một danh sách dữ liệu liên tiếp (không có hàng trống) như bên dưới, bạn có thể nhập một công thức đơn giản vào ô a2 như:

= tổng phụ (3, $ b $ 2: b2)

Xem thêm: Cách dùng hàm NETWORKDAYS trong Excel để tính ngày làm việc đơn giản

Xem Thêm : 3 cách làm sốt me chua ngọt cực hấp dẫn, ăn gì cũng ngon | VinID

giải thích công thức:

• function_num là 3: tương ứng với counta – đếm các ô không trống.
• ref1 là $ b $ 2: b2: đếm các ký tự trong phạm vi $ b $ 2: b2, trong đó ô b2 là được cố định bằng “$” trước địa chỉ hàng và ô. Nếu bạn sao chép công thức xuống, giá trị sẽ thay đổi như: $ b $ 2: b3, $ b $ 2: b4, $ b $ 2: b5, … và hàm sẽ đếm chính xác, cho kết quả mong muốn.

sau đó kéo và sao chép công thức (bằng cách chọn ô chứa công thức cần sao chép> di chuột qua góc dưới cùng bên phải của ô và kéo và thả dấu cộng mới hiển thị vào cuối ô cuối cùng cần thiết. như sau:

tuy nhiên, nếu bạn gặp dữ liệu “cứng” với các hàng trống và không trống không theo thứ tự, bạn sẽ cần kết hợp tổng phụ với hàm if . số thứ tự của giá trị đã chọn sẽ được viết lại từ đầu khi lọc dữ liệu và điều này chỉ áp dụng khi sắp xếp theo cột.

cú pháp chung:

chẳng hạn, bạn có một bảng dữ liệu như hình dưới đây.

trong ô a2 nhập công thức:

= if (b2 = “”, “”, tổng phụ (3, $ b $ 2: b2))

suy luận: hàm chính sẽ sử dụng là if. nếu có một ô trống, hãy bỏ qua số đếm. nếu ô không trống, hãy sử dụng tổng phụ để đánh số.

công thức cho hàm if là: = if (logic_test, value_if_true, value_if_false), với:

• logic_test: nếu điều kiện của hàm.
• value_if_true: trả về giá trị nếu điều kiện là đúng.
• value_if_false: trả về giá trị nếu điều kiện là sai. ở đây chúng tôi sử dụng hàm tổng phụ.

các đối số cần hoàn thành trong hàm tổng phụ là:

• function_num = 3: count counta – đếm các ô không trống.
• ref1 = $ b $ 2: b2: đếm các ký tự trong phạm vi $ b $ 2: b2, trong đó ô b2 là cố định với “$” ở trước địa chỉ hàng và ô. Nếu bạn sao chép công thức xuống, giá trị sẽ thay đổi như: $ b $ 2: b3, $ b $ 2: b4, $ b $ 2: b5, … và hàm sẽ đếm chính xác, cho kết quả mong muốn.

sau khi nhập công thức, nhấn enter rồi sao chép công thức từ ô a2 sang các ô còn lại. kết quả là bảng sau:

3 cách để sửa lỗi #value! hàm tổng phụ

nguyên nhân lỗi:

– số nhận dạng của function_num không nằm trong khoảng 1 – 11 hoặc 101 – 111.

– tham chiếu tham chiếu tham chiếu đến các ô trong trang tính khác (trang tính khác), ví dụ: bạn đang sử dụng tổng phụ trong trang tính stt2 nhưng nó tham chiếu đến một ô nhất định, một dải ô trong trang tính stt1.

cách khắc phục:

Đối với nguyên nhân đầu tiên là do lỗi định nghĩa hàm, bạn cần kiểm tra xem mình đã sử dụng đúng số hàm chưa hoặc có ký tự thừa trong quá trình nhập liệu hay không để khắc phục.

Đối với nguyên nhân thứ hai gây ra lỗi tham chiếu, hãy xóa tham chiếu không chính xác và thay thế bằng phạm vi ô chính xác nằm trên cùng một trang tính mà bạn muốn thực hiện với hàm tổng phụ.

Trên đây là hướng dẫn cách sử dụng hàm tổng phụ trong excel đơn giản và dễ thực hiện. Tôi hy vọng bài viết sẽ giúp ích cho bạn khi làm việc với bảng tính!