Hàm Subtotal, Các Ứng Dụng Của Subtotal Trong Excel

Cập nhật thông tin chi tiết về Hàm Subtotal, Các Ứng Dụng Của Subtotal Trong Excel mới nhất ngày 29/09/2020 trên website Zdungk.com. 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. Cho đến thời điểm hiện tại, bài viết này đã đạt được 2,970 lượt xem.

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ừ 2020 đế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:

Để thêm 1 số xác định chức năng vào công thức, nhấn đúp chuột, đánh dấu phẩy, xác định 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 chức năng khác đang được dùng.

Lưu ý:

Khi bạn dùng công thức SUBTOTAL với chức 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ố.

Bạn đã biết cách lập công thức SUBTOTAL trong Excel, câu hỏi chính là tại 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? Lý do 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 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.

Lưu ý:

Vì các 2 bộ số xác định chức 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.

Công thức SUBTOTAL với số xác định chức 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 chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các 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 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.

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ó để 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:

Nhưng nếu có các hàng không liên quan đã ẩn thủ công bằng 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:

Các số chức 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 chính xác nếu có bất kì hàng ẩn nào trong dãy.

Lưu ý: Hàm Excel SUBTOTAL với số chức 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 ảnh hưởng đến tổng phụ.

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.

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 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…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến 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 gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến 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: HocExcel.Online

Liên quan

Ethernet Switch Có Chức Năng Gì? - Xem 22,869

Ethernet Switch có chức năng gì? Switch là một thiết bị chọn lựa đường dẫn để gửi frame đến đích, hoạt động ở Lớp 2 của mô hình OSI. Đôi khi Switch còn được gọi là Bridge đa port hay Hub chuyển mạch. Switch quyết định chuyển frame dựa trên ... Switch bảo đảm cung cấp băng thông nhiều hơn cho người dùng bằng cách tạo ra các miền đụng độ nhỏ hơn. Switch chia nhỏ mạng LAN thành nhiều đoạn mạng (segment) nhỏ. Mỗi segment này là một kết nối riêng giống như một làn đường riêng 100


Oxit Axit Là Gì? Tính Chất Hóa Học Và Hướng Dẫn Bài Tập Oxit Axit - Xem 19,404

Oxit axit là các oxit khi tác dụng với nước sẽ tạo ra axit, tác dụng với kiềm tạo thành muối hóa học. Oxit axit thường là oxit của phi kim ứng với một axit hoặc kim loại có hóa trị cao. Vậy tính chất hóa học của oxit axit ... Oxit axit thường là oxit của phi kim ứng với một axit hoặc kim loại có hóa trị cao. Vậy tính chất hóa học của oxit axit là gì? Cách giải bài tập oxit axit tác dụng với bazo như thế nào? Cách gọi tên oxit axit Tên oxit axit: (Tên tiền tố chỉ số nguyên tử của phi kim) + Tên phi kim + (tên tiền tố chỉ số nguyên tử oxi) + ''Oxit'' Tính chất hóa học của oxit axit Trừ SiO 2 thì hầu hết các oxit axit


Giải Vbt Vật Lý Lớp 6 - Xem 16,335

Giải bài tập môn Vật lý 6 Giải VBT Vật lý lớp 6 – Bài 8: Trọng lực – Đơn vị lực là tài liệu tham khảo môn Vật lý 6 hay dành cho các em học sinh, giúp các em ôn tập và củng cố kiến thức đã học ... Vì lực hút chỉ có tác dụng làm Mặt Trăng quay tròn quanh Trái Đất. Con tàu vũ trụ cũng ở vào tình trạng như Mặt Trăng. Con tàu vũ trụ khi đã bay vào quỹ đạo thì cũng như Mặt Trăng, không còn tên lửa đẩy


Drama Là Gì ? Ý Nghĩa Của Từ Drama Trên Facebook Là Gì? - Xem 16,236

Xã hội ngày càng phát triển kéo theo nhiều trào lưu xuất hiện, các thuật ngữ, ngôn từ mới lạ ngày càng nhiều trên các mạng. Những nội dung, các câu chuyện có tính chất kịch tính, gay cấn kéo dài luôn dành được sự quan tâm của các bạn ... Nhưng hiện tại trên facebook, ngoài nghĩa đó thì drama còn mang nhiều nghĩa khác như: phim dài tập để phân biệt với phim một tập (movie). Hay là những câu chuyện mang tính hồi hợp, gay cấn, kịch tính,... Phim drama Là những bộ phim dài tập có nội dung, tình tiết kéo dài. Phim nói về một câu chuyện xây dựng dựa trên nhân vật thật hoặc hư


Đa Dạng Sinh Học Là Gì? Nguyên Nhân, Biện Pháp Hạn Chế Suy Giảm Đa Dạng Sinh Học - Xem 12,771

Đa dạng sinh học là sự phong phú của nhiều nhiều dạng, loài và các biến dị di truyền của mọi sinh vật trong đời sống tự nhiên, sự đa dạng và phong phú này được chia làm nhiều cấp độ tổ chức sinh giới đặc biệt là với các ... Hầu hết các chuyên gia đều cho rằng với nguyên nhân từ con người là do sự phá hủy đất để canh tác nông nghiệp cũng như gây xáo trộn trong việc nhập nội các loài đã tính đa dạng đồng thời còn săn bắn và giết thịt, việc loài người định cư cũng là một trong những nguyên nhân chính cho sự tuyệt chủng của những loài động vật và chim đã tồn tại từ nhiều năm trước đây! Tốc độ


Kimochi Yamate Là Gì? Ý Nghĩa Của I Cư Kimochi Yamete Trong Tiếng Nhật? - Xem 12,375

Kimochi Yamate hay i cư kimochi và i kư kimochi là những thuật ngữ, cụm từ được sử dụng khả phổ biến trong thời đại hiện nay. Nhưng bạn có thật sự hiểu được nghĩa của từ kimochi là gì? Ở bài viết dưới đây, Zdungk.com sẽ giải đáp toàn ... Và được sử dụng nhiều với hàm nghĩ khi mà nam giới thoả mãn bạn giới của mình và ngược lại. Cho nên khi sử dụng kimochi trong ngữ cảnh này khá nhạy cảm nên các bạn trẻ nên lưu ý khi sử dụng để tránh gây khiếm nhã nhé. Tại sao các bạn trẻ lại thường xuyên sử dụng kimochi với hàm nghĩa trên? Đây là điều hoàn toàn dễ hiểu bởi chúng ta thường biết rằng, Nhật Bản là nơi có


Z Là Gì Trong Toán Học? - Xem 11,385

Bạn có từng nghe về tập hợp R trong toán học chưa? Hẳn là rất rất quen phải không? Dĩ nhiên rồi vì lớp 6 lớp 7 chúng ta được học cái này mà. Vậy Z là gì trong toán học nhỉ? “Tập hợp Z là tập hợp các số ... Tập hợp số nguyên chỉ ra các số nguyên là miền xác định nguyên duy nhất mà các phần tử dương của nó được sắp thứ tự tốt và các thứ tự đó được bảo toàn dưới phép cộng. Tương tự như các tập hợp số khác, tập hợp Z cũng là một tập hợp vô hạn. Ví dụ về các bài toán sử dụng tập hợp z- (số nguyên) Trong toán học, các dạng bài tập về số nguyên thường rất đa dạng.


Tb Là Gì Trên Facebook? - Xem 11,088

TB nghĩa là gì? TB là viết tắt của từ gì? TB là 1 từ viết tắt có rất nhiều nghĩa tiếng việt và tùy từng ngữ cảnh mà ta hiểu nó sẽ có nghĩa là gì, dưới đây là các nghĩa hay được sử dụng nhất của từ viết ... Đơn vị này cao cấp hơn Gigabyte - GB. 1 TB = 1024 GB 5 TB = 5120 GB 10 TB = 10240 GB Ví dụ: Bạn có ổ cứng dung lượng 2 TB tức là ổ cứng của bạn bằng 2048 GB Một bộ phim, thư mục, video có dung lượng 1TB tức là 1048 GB -phim HD dung lượng cao. Vậy nếu bạn thấy đứng đằng trước TB là 1 con số thì nó thường là viết tắt của cụm từ Terabyte TB trên Facebook nghĩa là gì? Đối với


Mã Zip Iphone Là Gì? - Xem 10,494

Zip Code (mã zip) là một trong những khái niệm không quá xa lạ đối với thế hệ trẻ. Chúng liên quan trực tiếp tới quy trình giao – Nhận bưu kiện khi mua sắm online hay đặt hàng thông qua các ứng dụng giao dịch trực tuyến. Không chỉ ... Vậy, mã zip iPhone là gì? Hãy cùng tìm hiểu nhé. Mã Zip Code là gì? Zip Code (Postcode - Zipcode) là một dãy số được sắp xếp theo quy tắc nhất định và được quy định bởi tổ chức Universal Postal Union (Hiệp hội Bưu chính Toàn cầu - UPU). Mục đích của các loại Zip Code là hỗ trợ xác định vị trí người nhận từ đó giúp cho quá trình vận chuyển hàng hoá bưu phẩm trở nên dễ dàng


Số Cvv/cvc Trên Thẻ Atm Vietcombank Là Gì? - Xem 10,197

Số Cvv/Cvc trên thẻ atm đang được rất nhiều chủ thẻ quan tâm. Nhất là với những người dùng thẻ atm Vietcombank. Vậy số Cvv/Cvc trên thẻ atm vietcombank là gì? Bị lộ có sao không? Số Cvv/Cvc trên thẻ atm vietcombank là gì? Không phải ai dùng thẻ atm ... Xem thẻ atm còn giá trị sử dụng nữa hay không. Như vậy, chức năng chính của số Cvv/Cvc chính là để tăng tính bảo mật cho thẻ atm Vietcombank. Nhất là đối với những giao dịch online thanh toán qua thẻ Visa/Mastercard. Hướng dẫn sử dụng số Cvv/Cvc Như đã nói ở trên, số Cvv/Cvc có tác dụng trong việc thanh toán hóa đơn mua hàng online qua thẻ Visa/Mastercard. Cách sử dụng mã số này như sau:


Đề xuất

Tôn Sư Trọng Đạo Là Gì? - Xem 4,059

– Tôn sư: (tôn: là tôn trọng, kính trọng và đề cao; sư: là thầy dạy học, dạy người, dạy chữ). Vậy tôn sư là người học trò thì phải biết tôn trọng, kính trọng và đề cao vai trò của người thầy trong quá trình học tập và trong ... Vậy tôn sư là người học trò thì phải biết tôn trọng, kính trọng và đề cao vai trò của người thầy trong quá trình học tập và trong cuộc sống. - Trọng đạo: (trọng: coi trọng, tôn trọng; đạo: đạo lí, con đường làm người, đạo đức, đạo lí truyền thống tốt đẹp của con người): Vậy trọng đạo: là người học trò phải biết tôn trọng, lễ phép, kính trọng người thầy, vì người


Auto Bias Và Fix Bias Cho Đèn! - Xem 3,267

Trước đây, hồi mới lắp đèn thì tôi chỉ mạch đèn lắp auto bias, mà chính xác là không rõ auto bias là gì, fix bias là gì, chỉ lắp theo sơ đồ có sẵn và cầu mong nó hát ra tiếng, sau đó hỏi các nghệ nhân đi trước ... Vậy nên điện trở thoát K cho đèn EL34 thường là 220R, 270R, 300R (nếu ngại thử thì mình khuyên các bạn nên sử dụng điện trở 270R). Khi đó điện áp rơi trên chân Katot so với mass đạt từ 16V-20V là đạt. Điện áp rơi trên OPT thường là 10V -


2 Chữ Quan Trọng Nhất Trong Cuộc Sống Vợ Chồng Để Hôn Nhân Không Tan Vỡ - Xem 2,772

Nếu muốn hôn nhân không tan vỡ, vợ chồng sống với nhau cần cảm thông và chấp nhận nhau. Thay vì bắt người kia phải thay đổi theo ý mình. Hai người cần chấp nhận và bao dung những mặt chưa tốt của nhau. Có thế mới hạnh phúc lâu ... Hai người cần chấp nhận và bao dung những mặt chưa tốt của nhau. Có thế mới hạnh phúc lâu dài được. Nam nữ yêu nhau rồi kết hôn, ai cũng mong muốn có thể cùng chung sống với vợ/chồng mình hạnh phúc trăm năm, đến khi đã cạn sông mòn.


Drama Là Gì ? Ý Nghĩa Của Từ Drama Trên Facebook Là Gì? - Xem 16,236

Xã hội ngày càng phát triển kéo theo nhiều trào lưu xuất hiện, các thuật ngữ, ngôn từ mới lạ ngày càng nhiều trên các mạng. Những nội dung, các câu chuyện có tính chất kịch tính, gay cấn kéo dài luôn dành được sự quan tâm của các bạn ... Ví dụ như vụ câu chuyện nữ sinh chia tay bạn trai bị phát tán clip quan hệ trên mạng. Để lại hậu quả đó là nữ sinh không chịu nổi dư luận mà chọn kết thúc cuộc sống bằng cách tự tử. Đó là một câu chuyện minh chứng cho dư luận là con dao giết người vô


Cập Nhật Bảng Màu Son Ink Full Mới Nhất Cho Các Dòng - Xem 3,069

Son Ink hay son Ink velvet là thỏi son đến từ thương hiệu mỹ phẩm Peripera Hàn Quốc, sở hữu mức giá rẻ, chất son kem lì mịn, khả năng bám màu hoàn hảo, bạn có thể thoải mái ăn uống mà không lo son bị trôi. Nếu đang tìm ... Da ngăm đen các bạn có thể cân nhắc lựa chọn màu son khác phù hợp với mình hơn. Son Ink Airy màu 04 (Hồng sen) Son màu hồng đào mang đến sự nhẹ nhàng nữ tính cho bạn, lựa chọn màu son này dùng hàng ngày hay khi đi học là vô cùng phù hợp. Son màu


Bạn đang xem bài viết Hàm Subtotal, Các Ứng Dụng Của Subtotal Trong Excel trên website Zdungk.com. 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. Chúc bạn một ngày tốt lành!