Bạn đang xem bài viết Hàm Subtotal Trong Excel – Cách Dùng Hàm Subtotal Qua Ví Dụ được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
4.6
/
5
(
9
bình chọn
)
Hướng dẫn cách dùng hàm SUBTOTAL trong Excel
Cú pháp: =SUBTOTAL(function_num,ref1,[ref2],…)
Giải thích các giá trị
function_num: Nhập vào một số để chỉ định hàm sử dụng trong phép tính. Giá trị nhập vào là một số từ 1 đến 11 (tính toán bao gồm các giá trị ẩn) hoặc từ 101 đến 111 (tính toán không bao gồm các giá trị ẩn). Đây là giá trị bắt buộc. Các số tương ứng với các hàm như sau:
ref1: Phạm vi vùng chọn tham chiếu đầu tiên mà bạn muốn tính toán. Đây là giá trị bắt buộc.
ref2: Phạm vi hoặc chuỗi từ 2 đến 254 mà bạn muốn tính.
Lưu ý:
Nếu có hàm SUBTOTAL khác trong giá trị ref1, ref2,…thì các hàm lồng vào này sẽ được bỏ qua để tránh bị tính 2 lần.
Lựa chọn các function_num cho hàm SUBTOTAL sao cho phù hợp. Các hàm từ 1 đến 11 sẽ tính toán bao gồm các giá trị ẩn trong vùng dữ liệu được chọn. Các hàm từ 101 đến 111 sẽ loại trừ các giá trị ẩn trong vùng dữ liệu (chỉ tính toán với các giá trị hiển thị).
Hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter).
Hàm SUBTOTAL chỉ dùng cho cột dữ liệu hoặc phạm vi dọc mà không dùng cho hàng hay phạm vi ngang. Vì thế khi bạn ẩn cột sẽ không ảnh hưởng tới giá trị tính toán, còn ẩn hàng thì có ảnh hưởng.
Các tham chiếu nào là tham chiếu 3-D thì hàm SUBTOTAL sẽ trả về giá trị #VALUE!
Ví dụ về cách dùng hàm SUBTOTAL trong Excel
Giả sử chúng ta có bảng dữ liệu sau:
Yêu cầu ở đây là tính tổng số sản phẩm và tìm ra lượng sản phẩm còn ít nhất là bao nhiêu và nhiều nhất là bao nhiêu.
Chúng ta sẽ sử dụng hàm SUBTOTAL và dựa vào bảng giá trị function_num ở trên để chọn hàm và tính được lần lượt các giá trị như sau:
Tại ô D15 công thức tính tổng là
=SUBTOTAL(9;D5:D13)
Tại ô D16 công thức tính giá trị nhỏ nhất là
=SUBTOTAL(5;D5:D13)
Tại ô D17 công thức tính giá trị lớn nhất là
=SUBTOTAL(4;D5:D13)
Ta có kết quả như sau
Ví dụ về điểm nổi bật của hàm SUBTOTAL trong Excel
Cũng trong ví dụ trên bên cột E chúng ta sử dụng các hàm SUM, MIN, MAX để tính và có kết quả như sau
Kết quả tính toán là giống nhau đúng không nào. Bây giờ mình sẽ làm như sau và bạn sẽ thấy hàm SUBTOTAL phát huy được điểm mạnh của nó như nào.
Ví dụ trong bảng dữ liệu chúng ta dùng Filter để ẩn số liệu của nhà cung cấp Lazada khi đó chúng ta sẽ có kết quả như sau:
Kết quả đã có sự khác biệt khi sử dụng hàm SUBTOTAL
Với hàm SUBTOTAL tổng là 34 vì hàm SUBTOTAL chỉ tính tổng của các sản phẩm hiển thị của nhà cung cấp Adayroi, còn hàm SUM vẫn tính cả tổng các mặt hàng đã bị ẩn.
Giá trị nhỏ nhất thì cả 2 hàm giống nhau vì giá trị trong bảng lúc hiển thị và lúc ẩn đều là 7.
Giá trị lớn nhất của hàm SUBTOTAL là 10 vì hàm SUBTOTAL chỉ dò tìm các giá trị hiển thị còn hàm MAX vẫn dò tìm cả các giá trị bị ẩn.
Có 1 điểm đặc biệt trong kết quả này nếu bạn chú ý kỹ sẽ thấy đó là trong các công thức trên chúng ta dùng các giá trị function_num là 9,5,4 (các function_num sẽ tính toán bao gồm cả các giá trị ẩn), nhưng trong này nó lại không tính (có gì sai ư?).
Ví dụ về cách dùng công cụ SUBTOTAL
Cũng với ví dụ chúng tôi dùng ở trên mình sẽ dùng công cụ SUBTOTAL để tính tổng các sản phẩm của mỗi nhà cung cấp.
Yêu cầu cần thiết là bạn phải sắp xếp tên nhà cung cấp trùng nhau ở gần nhau. Ở ví dụ này mình đã sắp xếp trước rồi nên mình không cần làm việc này nữa.
Hộp thoại Subtotal hiện lên như sau:
Trong đó chúng ta có các thông số như sau:
Mục At each change in: Bạn chọn Nhà cung cấp vì chúng ta sẽ tính tổng cho từng Nhà cung cấp.
Mục Use function: Bạn chọn hàm cần để tính. Ở đây mình tính tổng nên sẽ chọn là SUM.
Mục Add subtotal to: Bạn chọn cột cần tính toán. Ở đây mình muốn tính số lượng nên chọn cột Số lượng.
Ấn OK và chúng ta có kết quả thu được như sau
Download file ví dụ về hàm SUBTOTAL trong Excel
Lời kết
Với những kiến thức về hàm SUBTOTAL ở trên hi vọng các bạn có thể hiểu được cách dùng hàm SUBTOTAL và vận dụng nó tốt hơn trong học tập và trong công việc. Hàm SUBTOTAL là 1 hàm rất linh hoạt, với những ưu điểm của hàm này bạn có thể dùng để tính toán hiệu quả.
Hàm Subtotal, Các Ứng Dụng Của Subtotal Trong Excel
Trong bài viết này, Excel Online sẽ giải thích chi tiết 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.
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 cơ sở dữ liệu. 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. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, 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ó ở tất cả 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, [ref2],…)
Trong đó:
Function_num: con số xác định chức 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 chức 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.
Bạn không cần phải nhớ hết các con số chức 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ẽ đưa 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:
=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 chức năng khác đang được dùng.
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 chúng ta lọc bảng doanh số 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 tất cả vùng khác khi tính tổng.
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.
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ó để giải quyết công việc, bạn sẽ nhận ra 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 chấp nhận 2 bộ số xác định chức 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ì loại trừ. Để hiểu rõ hơn về điểm khác biệ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:
VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU
Nếu bạn đang lập báo cáo tổng kết, cần phải đưa ra các dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho tất cả số liệu, thì cách sau có thể là giải pháp:
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.
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 lý do sau đây
#VALUE! Số xác định chức 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 cụ thể 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ả.
Mẹo nhỏ:
Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng các chức năng cài đặ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 đâ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 cô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ố cô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 báo cáo với Pivot Table…
Hướng Dẫn Cách Dùng Hàm Sumproduct Trong Excel Qua Ví Dụ
Cũng lâu ghê mình chưa viết bài về Excel đấy nhỉ. Thời gian vừa qua mình cũng bận việc với phải dành thời gian thay đổi địa chỉ cho blog và chỉnh sửa lại blog 1 chút. Hôm nay cuối tuần rảnh mình cố gắng ngồi viết bài để cho các bạn có bài để đọc 😀
Hướng dẫn cách dùng hàm SUMPRODUCT trong Excel
Hàm SUMPRODUCT là hàm được dùng khá nhiều trong kế toán chính vì thế nó có thể áp dụng được trên hầu hết các phiên bản Excel như Excel 2007, 2010, 2013, 2016 và kể cả Excel cho điện thoại iPhone, Android,…Về cách dùng thì trên các phiên bản nó đều tương tự nhau, ở đây mình hướng dẫn các bạn làm trên Excel 2016 còn bạn nào dùng các phiên bản khác cũng làm tương tự không có gì khác cả.
Cú pháp của hàm SUMPRODUCT
Các giá trị trong hàm SUMPRODUCT
Array1: Mảng đầu tiên bạn muốn nhân các thành phần của nó rồi cộng tổng. Đây là giá trị bắt buộc.
[array2], [array3],…: Đây là các mảng từ 2 đến tối đa 255 mà bạn muốn nhân các thành phần của nó rồi cộng tổng. Đây là giá trị tùy chọn.
Lưu ý
Tất cả các đối số trong hàm SUMPRODUCT phải có cùng số hàng, số cột. Nếu không, hàm SUMPRODUCT trả về giá trị lỗi #VALUE! .
Bất kỳ ô dữ liệu nào trong mảng không có dạng số thì hàm SUMPRODUCT sẽ coi đó là số 0.
Đối với Excel 2003 bạn có thể thêm tối đa 30 mảng, còn từ phiên bản Excel 2007 trở lên bạn có thể dùng tối đa 255 mảng.
Yêu cầu: Chúng ta có 1 bảng thống kê đơn hàng ở 1 cửa hàng. Chúng ta phải tính tổng doanh thu của tất cả các đơn hàng trong cửa hàng đó. Chúng ta sẽ áp dụng hàm SUMPRODUCT để tính tổng doanh thu.
Như trong hình trên bạn cũng thấy, để tính được tổng doanh thu chúng ta cứ lấy ( Số lượng bán * Đơn giá) rồi cộng tổng vào là ra. Nhưng ở đây chúng ta có sự hỗ trợ của hàm SUMPRODUCT thì sao phải nhân rồi cộng tổng bằng chi cho vất vả.
Tại ô F4 chúng ta nhập vào công thức hàm SUMPRODUCT như sau: =SUMPRODUCT(D4:D9,E4:E9) = 1657.5
Để các bạn hiểu hơn mình sẽ dựa vào ví dụ trên và nó sẽ có công thức về cơ chế tính toán như sau: =D4*E4 + D5*E5 + D6*E6 + D7*E7 + D8*E8 + D9*E9
Ngoài ra, kết quả trên nó cũng tương đương khi bạn dùng để tính với công thức như sau: =SUM(D4*E4,D5*E5,D6*E6,D7*E7,D8*E8,D9*E9)=1657.5
Yêu cầu: Tính tổng số sản phẩm bán ra có số lượng KHÔNG đạt chỉ tiêu.
Ở đây, nếu bạn không biết dùng hàm SUMPRODUCT bạn cũng có thể sử dụng hàm COUNTIF để đếm, nhưng nó sẽ rất vất vả nếu trong bảng tính có tới hàng nghìn đầu mục. Còn khi bạn đã biết cách dùng hàm SUMPRODUCT rồi thì việc tính toàn đơn giản như sau:
=SUMPRODUCT(-(E4:E9<F4:F9)) = 4
hoặc công thức
=SUMPRODUCT((E4:E9<F4:F9)*1) = 4
Trong công thức đầu tiên, bạn thấy có 2 dấu trừ (-) về mặt kỹ thuật thì đây là hai toán tử đơn phân, nó giúp biến TRUE thành 1 và FALSE thành 0.
Ví dụ trong công thức =SUMPRODUCT(-(E4:E9<F4:F9)) bạn bôi đen đoạn E4:E9<F4:F9 rồi ấn phím F9 thì công thức sẽ chuyển thành =SUMPRODUCT(-({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE})) và 2 dấu trừ nó sẽ giúp công thức có dạng tương tự là: =SUMPRODUCT({1;1;0;1;0;1}) = 4
Ví dụ 3: Cách dùng hàm SUMPRODUCT với nhiều điều kiện
Ở đây, chúng ta sẽ sử dụng lại ví dụ 2 nhưng sẽ thêm 1 điều kiện nữa cho nó khó 😀
Yêu cầu: Tính tổng số sản phẩm SONY bán ra có số lượng KHÔNG đạt chỉ tiêu.
Tải file ví dụ về hàm SUMPRODUCT trong Excel
Hàm Subtotal: Công Thức Và Cách Sử Dụng Hàm Subtotal Trong Excel
Công thức SUBTOTAL trong Excel
Hàm SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:
Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.
Lưu ý:
Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc.
Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.
Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.
Cách sử dụng hàm SUBTOTAL trong Excel
1. Tính tổng các hàng được lọc
Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:
=SUBTOTAL(9,pham_vi)
pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.
2. Đếm các ô được lọc không trống
Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.
Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.
Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.
Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.
Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.
3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau
Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.
Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.
Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.
Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.
Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel
Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:
#VALUE!: Số xác định chức 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 cụ thể 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ả.
Cập nhật thông tin chi tiết về Hàm Subtotal Trong Excel – Cách Dùng Hàm Subtotal Qua Ví Dụ trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!