Hàm subtotal là gì? cách sử dụng hàm subtotal như thế nào? hàm subtotal là gì
Nội Dung Chính Trong Bvàagrave;ι Viết
Nội dung chính
1 HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG2 3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL3 VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL4 HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP
Trong nội dung này, Excel Online sẽ giải thích cụ thể hàm SUBTOTAL và hướng dẫn các bạn cách sử dụng công thức SUBTOTAL để cộng dữ liệu trong các ô nhìn thấy.
Đang xem: Lệnh subtotal là gì
HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG
Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc DataBase. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác nhận. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 tính năng rõ ràng, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…
Hàm SUBTOTAL có ở toàn bộ các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.
Cú pháp hàm SUBTOTAL:
SUBTOTAL(function_num, ref1, ,…)
Trong đó:
Function_num: con số xác nhận tính năng thực hiện
Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
Số xác nhận tính năng thực hiện có 2 loại sau:
1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công. 101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công. Function_num Chức năng Mô tả 1 101 AVERAGE Tính trung bình các con số 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 Tính kết quả của các ô 7 107 STDEV Tính độ lệch chuẩn mẫu dựa trên mẫu 8 108 STDEVP Tính độ lệch chuẩn dựa trên toàn thể số 9 109 SUM Cộng các 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 thể số
Bạn không cần phải nhớ hết các con số tính năng. Ngay khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ mang ra danh sách các con số cho bạn.
Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8:
lệnh subtotal là gì 11
Để thêm 1 số xác nhận tính năng vào công thức, nhấn đúp chuột, đánh dấu phẩy, xác nhận dãy ô, đóng ngoặc và nhấn Enter. Công thức hoàn chỉnh sẽ như sau:
=SUBTOTAL(9,C2:C8)
Tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô không trống. Trong hình dưới, 3 tính năng khác đang được dùng.
lệnh subtotal là gì 12
Lưu ý:
Khi bạn dùng công thức SUBTOTAL với tính năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.
lệnh subtotal là gì 13
Bạn đã biết cách lập công thức SUBTOTAL trong Excel, thắc mắc chính là vì sao lại phải học hàm này? Sao không dùng các hàm đơn giản, bình thường như SUM, COUNT, MAX? Nguyên nhân sẽ được trình bày dưới đây.
3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL
So sánh với các hàm Excel truyền thống, SUBTOTAL cho bạn những lợi thế sau:
Tính giá trị trong các hàng được chọn
Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.
Ví dụ, nếu tất cả chúng ta lọc bảng lợi nhuận bán hàng 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 khi tính tổng.
lệnh subtotal là gì 14
Lưu ý:
Vì các 2 bộ số xác nhận tính năng (1-11 và 101-111) đều bỏ qua các ô đã được lọc ra, bạn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.
Tính các ô nhìn thấy
Công thức SUBTOTAL với số xác nhận tính năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng tính năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số tính năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.
Xem thêm: Cách Làm Tinh Dầu Tràm Huế, Cận Cảnh Nghề Làm Dầu Tràm Huế
Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép
Nếu dãy ô trong công thức SUBTOTAL có 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 con số trong bảng sẽ không 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 kết quả của công thức SUBTOTAL trong ô C3 và C10.
lệnh subtotal là gì 15
VÍ DỤ DÙNG SUBTOTAL TRONG EXCEL
Khi bạn mới biết hàm SUBTOTAL, có thể thấy hàm này phức tạp, rắc rối vô nghĩa. Nhưng khi bạn dùng nó để khắc phục công việc, bạn sẽ nhận thấy thuần thục hàm SUBTOTAL không khó. Ví dụ sau đây sẽ giúp bạn với một số mẹo nhỏ khi dùng hàm SUBTOTAL.
VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109
Như bạn đã biết, Excel SUBTOTAL đồng ý 2 bộ số xác nhận tính năng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã được lọc ra, nhưng 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì ngoại trừ. Để hiểu rõ hơn về điểm nổi bật này, xem ví dụ sau:
Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:
lệnh subtotal là gì 16
Nhưng nếu có các hàng không liên quan đã ẩn thủ công bình cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào các hàng, sau đó Hide, mà giờ bạn chỉ cần tổng giá trị trong các hàng nhìn thấy, lựa chọn SUBTOTAL 109:
lệnh subtotal là gì 17
Các số tính năng khác cũng làm theo cách tương tự. Ví dụ, để đếm ô không trống đã chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm các ô không trống nhìn thấy chuẩn xác nếu có bất kì hàng ẩn nào trong dãy.
lệnh subtotal là gì 18
Lưu ý: Hàm Excel SUBTOTAL với số tính năng 101-111 bỏ qua giá trị trong hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn sẽ không tác động đến tổng phụ.
VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU
Nếu bạn đang lập giải trình tổng kết, cần phải mang ra các dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho toàn bộ số liệu, thì cách sau có thể là phương án:
Trong 1 ô, tạo 1 danh sách dạng thả xuống (drop-down) có chứa tên các hàm như Total, Max, Min… Trong ô kế ô danh sách, thêm công thức hàm IF lồng ghép có chứa công thức SUBTOTAL tương ứng với các 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 sử dụng chọn trong danh sách, công thức SUBTOTAL tương ứng sẽ tính giá trị trong các hàng được chọn.
lệnh subtotal là gì 19
Mẹo nhỏ:
Nếu đột nhiên danh sách và ô công thức biến mất khỏi bảng tính, thì bạn hãy chọn 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 có thể là một trong những nguyên nhân sau đây
#VALUE! Số xác nhận tính năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
#DIV/0! Xảy ra khi 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 của 1 dãy ô không chứa giá trị số)
#NAME? tên hàm SUBTOTAL sai chính tả.
Xem thêm: Tuổi 92 Hợp Màu Gì – Sinh Năm 1992 Mệnh Gì
Mẹo nhỏ:
Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng các tính năng seting 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 đây:
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các dụng cụ khác của Excel.
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số… Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số dụng cụ hay sử dụng như:
Định dạng theo điều kiện với Conditional formatting Thiết lập điều kiện nhập dữ liệu với Data Validation Cách đặt Name và sử dụng Name trong công thức Lập giải trình với Pivot Table…
Rất nhiều tri thức phải không nào? Toàn bộ những tri thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới Chuyên Viên của Học Excel Online. Đây là khóa học giúp bạn hệ thống tri thức một cách đầy đủ, cụ thể. Hơn nữa không hề có hạn chế về thời gian học tập nên bạn có thể thoải mái học bất kể lúc nào, đơn giản tra cứu lại tri thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: cungdaythang.com.Online
The post Hàm subtotal là gì? cách sử dụng hàm subtotal như thế nào? hàm subtotal là gì appeared first on Monrun.vn.
source https://monrun.vn/ham-subtotal-la-gi-cach-su-dung-ham-subtotal-nhu-the-nao-ham-subtotal-la-gi/
Nhận xét
Đăng nhận xét