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 20/10/2020 trên website Doisonggiaitri.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 5,247 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

Giải Vbt Vật Lý Lớp 6 - Xem 33,858

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 ... Trọng lượng của quả cân là 100g là 1N. B. Giải bài tập 1. Bài tập trong SBT Bài 8.1 trang 32 VBT Vật Lí 6: Lời giải: a) Một gàu nước được treo đứng yên ở đầu một sợi


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

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 ... Trong Ethernet Hub, tất cả các cổng kết nối vào một mạng chính, hay nói cách khác, tất cả các thiết bị kết nối Hub sẽ cùng chia sẻ băng thông mạng. Nếu có hai máy trạm được thiết lập phiên kết nối thì chúng sẽ sử dụng một lượng băng thông đáng kể và hoạt động của các thiết bị còn lại kết nối vào Hub sẽ bị giảm xuống. Để giải quyết tình trạng trên, Switch xử lý mỗi


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

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 ... 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 đều tan trong nước để tạo thành dung dịch axit. Tác dụng với oxit bazo tan để tạo


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

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 ... Kéo theo những câu chuyện drama của giới trẻ lan tràn tạo nên những chuỗi hít drama ngập tràn. Nắm bắt được việc đó những hội nhóm được thành lập ngày càng nhiều để phục vụ cho nhu cầu hít drama của giới trẻ. Những câu chuyện được hóng hớt ngày qua ngày như các bộ phim dài tập và nguội dần khi không còn gì để hít nữa. Những thông tin những câu chuyện được lan truyền để các


Đ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 20,097

Đ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 ... Ngoài ra tính đa dạng của sinh học cũng được quy ước ở một số lượng xác định với nhiều đối tượng khác nhau, tính giá trị cũng được thể hiện ở tần số xác định của chúng, và đươc biểu trưng bằng nhiều cấp độ khác nhau chính và chúng có những chuyển biến từ phức tạp đến các cấu trúc hóa học là cơ sở phân tử của thế giới di truyền. Chúng ta cũng có thể tìm hiểu thêm


Tb Là Gì Trên Facebook? - Xem 17,820

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


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

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.


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

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, Doisonggiaitri.com sẽ giải đáp toàn ... Ngoài ra thì " kimochi ii" và " kimochi warui " còn được dùng khi bạn được tặng một món quà nào đó, hay nhận được một lời khen thật lòng từ người khác. Ngoài ra cụm từ kimochi còn được giới trẻ ngày nay sử dụng để biểu cảm cảm giác thích, sướng hay phê, và kimochi còn được nhắc đến đây dùng để mang hàm ý nhắc đến chuyện chăn gối hay việc sinh hoạt vợ chồng và đặc biệt


Mã Zip Iphone Là Gì? - Xem 15,642

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ỉ ... Mã zip này sẽ giúp Apple quản lý tốt hơn thiết bị, đồng thời hỗ trợ bạn trong quá trình sử dụng/ bảo hành sau này. Trong khi đó, mã Zip Code bưu điện thường không cung cấp những thông tin cơ bản về cá nhân hay phương thức thanh toán của người nhận. Các loại mã này chỉ đơn giản biểu thị địa chỉ giao - Nhận hàng nhằm hỗ trợ tối đa cho quá trình vận chuyển bưu phẩm. Mã Zip Code


Từ Hán Việt Là Gì? Các Từ Hán Việt Thường Gặp & Giải Nghĩa - Xem 15,345

Bài học hôm này các em sẽ làm quen với khái niệm từ Hán Việt là gì? các từ Hán Việt thường gặp và một số thông tin quan trọng về vai trò, cách nhận viết và ví dụ của từ Hán Việt. Kiến thức này nằm trong chương trinh ... Khi sử dụng các từ mượn trong cuộc sống hàng ngày người dùng không cảm thấy quá xa lạ hay khác biệt quá nhiều. Ví dụ: Góa phụ (từ Hán Việt) Rocket (từ mượn có nghĩa tên lửa). Đặc điểm từ Hán Việt Ngôn ngữ tiếng Việt có nhiều từ Hán Việt và mang nhiều sắc thái khác nhau như sắc thái ý nghĩa, sắc thái biểu cảm, sắc thái phong cách. - Sắc thái ý nghĩa: từ


Đề xuất

Công Nghiệp Mũi Nhọn Là Những Ngành Công Nghiệp: - Xem 4,851

Chủ đề : Câu hỏi này thuộc đề thi trắc nghiệm dưới đây, bấm vào Bắt đầu thi để làm toàn bài Hướng dẫn Trắc nghiệm Online và Tích lũy điểm thưởng CÂU HỎI KHÁC Vai trò chủ đạo của ngành công nghiệp được thể hiện: Tính chất hai giai ... Đó là đặc điểm của ngành Ngành công nghiệp có khả năng lớn nhất để thúc đẩy cho cả công nghiệp nặng lẫn nông nghiệp phát triển là: Ngành công nghiệp nào sau đây có mặt ở mọi quốc gia trên thế giới? Công nghiệp sản xuất


Spo2 Có Nghĩa Là Gì? Mức Spo2 Bình Thường Là Gì? - Xem 7,227

SpO2 là một trong những chỉ số quan trọng để đánh giá sức khỏe của một người. Vậy bạn có biết SpO2 có nghĩa là gì và chỉ số này ở người bình thường là bao nhiêu? SpO2 có nghĩa là gì? Bạn có biết SpO2 là gì và ý ... Nó được tìm thấy trong các tế bào hồng cầu và cho chúng màu đỏ của chúng. SpO2 có thể được đo bằng phép đo xung, phương pháp gián tiếp, không xâm lấn (nghĩa là nó không liên quan đến việc đưa dụng cụ vào cơ thể). Nó hoạt động bằng cách phát ra và sau đó hấp thụ một làn sóng ánh sáng đi qua các mạch máu (hoặc mao mạch) trong đầu ngón tay. Sự thay đổi của sóng ánh sáng xuyên qua


Bé Sinh Năm 2020 Thuộc Mệnh Gì? Có Tốt Không? - Xem 9,603

Hầu hết ở những nước Á Đông và cả Việt Nam, thông thường thì đặt tên của năm bằng Can Chi. Những Can Chi đó được đặt tên lần lượt là: Giáp, Ất, Bính, Mậu, Kỷ, Canh, Tân, Nhâm, Quý. 12 con giáp là: Tý, Sửu, Dần, Mão, Thìn, Tỵ, ... Những năm này là những năm rất khó khăn đối với người sinh năm 2020. Vào những năm này bệnh tật và thua lỗ tiền tài xảy ra thường xuyên. Trên đây là một số thông tin về năm 2020 là năm con g ì? Mà mình muốn chia sẻ đến với các


Niềm Tin Là Gì? Sức Mạnh Của Niềm Tin? - Xem 5,049

Tại sao một số người có thể đạt được rất nhiều thành công vượt bậc trong cuộc sống? Tại sao một số người có thể đạt được những thành quả phi thường… vượt xa những gì được mong đợi? Có phải đó là vì họ tài năng, nhạy bén và ... Trong đời mỗi người, có những sự kiện không thể nào quên. Ví dụ, một lần đổ vỡ trong hôn nhân khiến bản thân người trong cuộc mất dần niềm tin vào hạnh phúc gia đình, hay một người gặp thất bại trong kinh doanh có thể khiến họ cảm


Xe Sedan Là Gì? Cách Phân Biệt Xe Sedan Với Xe Hatchback, Xe Suv, Xe Coupe, Xe Mpv? - Xem 5,346

Thứ Tư, ngày 23/01/2019 07:28 AM (GMT+7) Tìm hiểu xe sedan và hatchback là gì. Cách phân biệt điểm khác nhau giữa các dòng xe Sedan với xe Hatchback, xe SUV, xe MPV Khi mà hàng loạt các mẫu mã mới của các dòng xe lần lượt trình làng, khách ... Đối với các mẫu mã đã được kết hợp lai hình với nhau thì để phân biệt kiểu dáng xe lại càng khó hơn. Chính do đó, hôm nay chúng tôi sẽ giới thiệu đến quý độc giả thông tin về kiểu dáng xe sedan. Vậy xe sedan là gì? Cách phân


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