Tin tổng hợp

Công Thức Và Cách Sử Dụng Subtotal Trong Excel, Subtotal (Hàm Subtotal) – viettingame

Trong nội dung bài viết này, Excel Trực tuyến sẽ lý luận cụ thể hàm SUBTOTAL và hướng dẫn chúng ta cách sử dụng công thức SUBTOTAL để cùng dữ liệu trong những ô tìm thấy.

Đang xem: Cách sử dụng subtotal

HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG

Hàm SUBTOTAL trong Excel mang trách nhiệm để tính tổng phụ trong danh sách hoặc trung tâm dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) ko phải tổng những số lượng trong 1 dãy ô xác định. Những hàm Excel khác được thiết kế chỉ để tiến hành 1 tác dụng rõ ràng, nhưng hàm SUBTOTAL lại rất linh hoạt – thậm chí tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị to nhất/nhỏ nhất…

Hàm SUBTOTAL mang ở toàn bộ những phiên phiên bản Excel từ 2016 tới 2007 và cả phiên phiên bản thấp hơn.

Cú pháp hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, ,…)

Trong số đó:

Function_num: số lượng xác định tác dụng tiến hành

Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Thiết yếu Ref 1, từ Ref 2 tới 254 là tuỳ mua.

Số xác định tác dụng tiến hành mang 2 loại sau:

1 -11 bỏ qua những ô đã được lọc ra, nhưng để lại những hàng được ẩn thủ công.101 – 111 bỏ qua những ô ẩn – đã lọc ra và ẩn thủ công.

Function_num Tác dụng Mô tả
1 101 AVERAGE Tính trung bình những số lượng
2 102 COUNT Đếm số ô chứa giá trị số
3 103 COUNTA Đếm số ô ko trống
4 104 MAX Tìm giá trị to nhất
5 105 MIN Tìm giá trị nhỏ nhất
6 106 PRODUCT Tính thành quả của những ô
7 107 STDEV Tính độ lệch chuẩn chỉnh mẫu dựa trên mẫu
8 108 STDEVP Tính độ lệch chuẩn chỉnh dựa trên toàn bộ số
9 109 SUM Cùng những số
10 110 VAR Ước tính độ dao động dựa trên mẫu
11 111 VARP Ước tính độ dao động dựa trên toàn bộ số

Chúng ta không nhất thiết phải nhớ hết những số lượng tác dụng. Ngay lúc chúng ta nhập hàm SUBTOTAL Excel vào 1 ô hoặc trên thanh công thức, Excel sẽ tìm ra danh sách những số lượng cho mình.

Ví dụ, phía trên là cách chúng ta sử dụng công thức SUBTOTAL 9 để cùng tổng những giá trị trong ô từ C2 tới C8:

*

Để thêm một số xác định tác dụng vào công thức, nhấn đúp chuột, lưu lại phẩy, xác định dãy ô, đóng ngoặc và nhấn Enter. Công thức hoàn hảo sẽ như sau:

=SUBTOTAL(9,C2:C8)

Tương tự, chúng ta thậm chí viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô ko trống. Trong hình dưới, 3 tác dụng khác đang được sử dụng.

*

Lưu ý:

Lúc chúng ta sử dụng công thức SUBTOTAL Excel với tác dụng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính những ô chứa số, bỏ qua ô trống và ô chứa giá trị ko phải số.

Chúng ta đã hiểu phương pháp lập công thức SUBTOTAL trong Excel, thắc mắc đó là tại vì sao lại phải học hàm này? Sao ko sử dụng những hàm giản dị, thông thường như SUM, COUNT, MAX? Lý do sẽ được trình diễn dưới phía trên.

3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL

So sánh với những hàm Excel truyền thống lịch sử, SUBTOTAL cho mình những lợi thế sau:

Tính giá trị trong những hàng được mua

Vì như thế hàm Excel SUBTOTAL bỏ qua những hàng đã được lọc ra, chúng ta thậm chí sử dụng nó để tính tổng dữ liệu một cách linh hoạt, những giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ, nếu chúng ta lọc bảng doanh số bán sản phẩm chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua toàn bộ vùng khác lúc tính tổng.

*

Lưu ý:

Vì như thế những 2 bộ số xác định tác dụng (1-11 và 101-111) đều bỏ qua những ô đã được lọc ra, chúng ta thậm chí sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.

Xem thêm: Khái Niệm Lượng Giá Trị Hàng Hóa Là Gì? Thước Đo Xác Định Lượng Giá Trị Hàng Hoá

Tính những ô tìm thấy

Công thức SUBTOTAL với số xác định tác dụng 101-111 bỏ qua những ô đã ẩn, đã lọc ra và ẩn thủ công. Vì như thế thế, lúc chúng ta sử dụng tác dụng Excel’s Hide để ẩn dữ liệu ko liên quan, sử dụng số tác dụng 101-111 để loại bỏ những giá trị từ những hàng ẩn khỏi tổng phụ.

Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép

Nếu dãy ô trong công thức SUBTOTAL mang chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên số lượng trong bảng sẽ không còn phải tính 2 lần.

Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua thành quả của công thức SUBTOTAL trong ô C3 và C10.

*

VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL

Lúc chúng ta mới biết hàm SUBTOTAL, thậm chí thấy hàm này phức tạp, rắc rối vô nghĩa. Nhưng lúc chúng ta sử dụng nó để giải quyết và xử lý công việc, các bạn sẽ nhận ra thuần thục hàm SUBTOTAL ko khó. Ví dụ sau phía trên sẽ giúp đỡ bạn với một trong những thủ thuật lúc sử dụng hàm SUBTOTAL.

VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109

Như chúng ta đã biết, Excel SUBTOTAL gật đầu đồng ý 2 bộ số xác định tác dụng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua những hàng đã được lọc ra, nhưng 1-11 bao hàm những hàng được ẩn thủ công còn 101-111 thì loại trừ. Để làm rõ hơn về điểm khác lạ này, xem ví dụ sau:

Với tổng những hàng đã được mua, chúng ta thậm chí sử dụng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

*

Nhưng nếu như có những hàng ko liên quan đã ẩn thủ vô tư cách sử dụng lệnh Hide Rows trong trang chủ tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào những hàng, sau đó Hide, mà giờ chúng ta chỉ việc tổng giá trị trong những hàng tìm thấy, lựa mua SUBTOTAL 109:

*

Những số tác dụng khác cũng tuân theo cách tương tự. Ví dụ, để đếm ô ko trống đã mua, sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 thậm chí đếm những ô ko trống tìm thấy đúng chuẩn nếu như có bất kì hàng ẩn nào trong dãy.

*

Lưu ý: Hàm Excel SUBTOTAL với số tác dụng 101-111 bỏ qua giá trị trong hàng bị ẩn, ko phải cột bị ẩn. Ví dụ, nếu như khách hàng sử dụng công thức như SUBTOTAL(109, A1:E1) để tính tổng những số trong hàng ngang, cột bị ẩn sẽ không còn tác động tới tổng phụ.

VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU

Nếu như bạn đang lập report tổng kết, cần phải tìm ra những dữ liệu tổng kết không giống nhau nhưng chúng ta không tồn tại chỗ trống cho toàn bộ số liệu, thì cách sau thậm chí là giải pháp:

Trong 1 ô, tạo 1 danh sách dạng thả xuống (drop-down) mang chứa tên những hàm như Total, Max, Min…Trong ô kế ô danh sách, thêm công thức hàm IF lồng ghép mang chứa công thức SUBTOTAL tương ứng với những hàm trong danh sách.

Ví dụ, giả sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 chứa Total, Average, Max, Min, công thức hàm SUBTOTAL sẽ như sau:

=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))

Và tiếp theo, tuỳ vào hàm mà người tiêu dùng mua trong danh sách, công thức SUBTOTAL tương ứng sẽ tính giá trị trong những hàng được mua.

*

Thủ pháp:

Nếu đột nhiên danh sách và ô công thức biến mất khỏi bảng tính, thì các bạn hãy mua lại chúng trong danh sách lọc.

HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP

Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân thậm chí là một trong những lý do sau phía trên

#VALUE! Số xác định tác dụng ko nằm trong tầm 1-11 hoặc 101-111 hay mang tham chiếu (ref) là tham chiếu 3D.

#DIV/0! Xẩy ra lúc 1 tổng rõ ràng phải chia cho 0 (ví dụ: tính trung bình cùng hoặc độ lệch chuẩn chỉnh của 1 dãy ô ko chứa giá trị số)

#NAME? tên hàm SUBTOTAL sai chính tả.

Xem thêm: Office Bị Lỗi This Copy Of Microsoft Office Is Not Activated

Thủ pháp:

Nếu như bạn chưa quen với hàm SUBTOTAL, chúng ta thậm chí sử dụng chức năng thiết đặt sẵn của hàm này và để công thức tự hoàn thiện giúp đỡ bạn hoặc theo dõi video sau phía trên:

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu suất cao thì chúng ta còn phải sử dụng tốt những hàm, những dụng cụ khác của Excel.

Một vài hàm cơ phiên bản thường bắt gặp như:

SUMIF, SUMIFS để tính tổng theo 1 ĐK, nhiều điều kiệnCOUNTIF, COUNTIFS để thống kê, đếm theo một ĐK, nhiều điều kiệnCác hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Những hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một vài dụng cụ hay sử dụng như:

Định dạng theo ĐK với Conditional formattingThiết lập ĐK nhập dữ liệu với Data ValidationCách đặt Name và sử dụng Name trong công thứcLập report với Pivot Table…

Rất nhiều kiến thức và kỹ năng phải ko nào? Toàn bộ những kiến thức và kỹ năng này chúng ta đều thậm chí học được trong khóa học EX101 – Excel từ cơ phiên bản tới Chuyên Viên của Học Excel Trực tuyến. Phía trên là khóa học giúp đỡ bạn khối hệ thống kiến thức và kỹ năng một cách không hề thiếu, cụ thể. Không dừng lại ở đó ko hề mang giới hạn về thời hạn học tập nên chúng ta thậm chí thoải mái học bất kể lúc nào, đơn giản tra cứu vãn lại kiến thức và kỹ năng lúc cần. Hiện nay khối hệ thống đang mang ưu đãi rất rộng lớn cho mình lúc đăng ký tham gia khóa học. Cụ thể xem tại: viettingame.com.Trực tuyến

Về Viettingame.com

Viettingame.com - Chuyên trang web tổng hợp những thông tin hữu ích trên internet như thông tin về game, tin tổng hợp
Xem tất cả các bài viết của Viettingame.com →

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *