Cách Bỏ Subtotal Trong Excel / TOP #10 ❤️ Xem Nhiều Nhất & Mới Nhất 10/2022 ❣️ Top View | Hoisinhvienqnam.edu.vn

Subtotal Là Gì? Cách Dùng Hàm Subtotal Trong Excel Kèm Ví Dụ

Thiết Lập Biểu Đồ Dựa Trên Dữ Liệu Trang Tính Hiệu Quả Trong Bài Thi Mos Excel

Hàm Average Trong Excel, Hàm Tính Trung Bình Cộng, Cách Dùng, Cú Pháp

Công Thức Hàm Số Mũ, Bình Phương Trong Excel 2010, 2013, 2022, 2022

Cách Sử Dụng Name Box Trong Excel

Đặt Tên Cho 1 Ô, 1 Mảng Dữ Liệu, … Sử Dụng Name Trong Excel

Bạn đã biết hàm SUBTOTAL có chức năng gì hay chưa? Công thức của hàm SUBTOTAL như thế nào? Ví dụ cho hàm SUBTOTAL ra sao? Ứng dụng của hàm SUBTOTAL trong cuộc sống như thế nào? Để trả lời 4 câu hỏi trên, sau đây mình sẽ hướng dẫn các bạn cách sử dụng hàm này cụ thể như sau.

1. Chức năng của hàm SUBTOTAL là gì?

Hàm SUBTOTAL của Excel trả về kết quả tổng hợp cho các giá trị được cung cấp. SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và các thứ khác (xem bảng bên dưới) và hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn.

Mục đích của hàm SUBTOTAL là nhận một tổng số phụ trong danh sách hoặc cơ sở dữ liệu

2. Công thức tính của hàm SUBTOTAL

Công thức tiếng Anh của hàm SUBTOTAL như sau:

=SUBTOTAL (function_num, ref1, [ref2], …)

Đơn giản hơn: =SUBTOTAL(a,b,c,..)

Mô tả cách sử dụng hàm subtotal trong Excel. Hình 1

3. Cách sử dụng hàm SUBTOTAL

Sử dụng hàm SUBTOTAL để lấy tổng phụ trong danh sách hoặc cơ sở dữ liệu. SUBTOTAL có khả năng sử dụng nhiều chức năng khác nhau khi tổng phụ, bao gồm AVERAGE, COUNT, MAX và các chức năng khác (xem bảng bên trên để biết danh sách đầy đủ). Theo mặc định, SUBTOTAL loại trừ các giá trị trong các hàng được ẩn bởi bộ lọc, như được giải thích bên dưới. Điều này làm cho SUBTOTAL rất hữu ích trong Bảng Excel.

4. Ví dụ về cách sử dụng hàm SUBTOTAL

Mô tả cách sử dụng hàm subtotal trong Execl. Hình 2

SUBTOTAL luôn bỏ qua các giá trị trong các ô được ẩn bằng bộ lọc. Các giá trị trong các hàng “được lọc ra” không được bao gồm, bất kể function_num.

Ghi chú:

Khi function_num nằm trong khoảng 1-11, SUBTOTAL bao gồm các giá trị bị ẩn

Khi function_num nằm trong khoảng 101-111, SUBTOTAL loại trừ các giá trị bị ẩn

Trong danh sách được lọc, SUBTOTAL luôn bỏ qua các giá trị trong các hàng ẩn, bất kể function_num.

SUBTOTAL bỏ qua các tổng phụ khác tồn tại trong tài liệu tham khảo được bỏ qua để ngăn chặn tính hai lần

SUBTOTAL được thiết kế để làm việc với các giá trị dữ liệu dọc được sắp xếp theo chiều dọc. Trong phạm vi Ngang, giá trị trong các cột ẩn luôn được bao gồm.

Ví dụ: đây là cách bạn có thể tạo công thức Tổng phụ 9 để tổng hợp các giá trị trong các ô từ C2 đến C8:

Mô tả cách sử dụng hàm subtotal trong Excel. Hình 3

Để thêm số hàm vào công thức, bấm đúp vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức hoàn thành sẽ như thế này:

= SUBTOTAL (9, C2: C8)

Ngoài ra, các bạn có thể tham khảo cách sử dụng các hàm trong Excel từ cơ bản đến nâng cao qua khóa học Trở thành cao thủ hàm Excel trong 10 giờ, giúp bạn nắm trọn hệ thống hàm Excel thông dụng và quan trọng nhất, từ đó thành công xử lý, lưu trữ và thống kê số liệu một cách nhanh chóng, chính xác nhất chỉ với 60 bài giảng cùng giảng viên hàng đầu.

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

Giới Thiệu Về Macro Trong Excel (Windows)

Xem Trước Các Trang Của Trang Tính Trước Khi In

20+ Các Hàm Trong Excel Cho Dân Văn Phòng Cần Phải Biết

15+ Hàm Cơ Bản Trong Excel Cho Dân Văn Phòng

Hàm Subtotal Trong Excel – Cách Dùng Hàm Subtotal Qua Ví Dụ

Hướng Dẫn Hàm Int() Và Hàm Mod() Trong Excel

– Cách Tách Họ Và Tên Trong Excel

Cách Ẩn Cột, Ẩn Hàng Trong Excel 2022, 2013, 2010

How To Unhide Sheets In Excel: Show Multiple Or All Hidden Sheets At A Time

Cách Sử Dụng Phím Tắt Khi Paste Value Trong Excel

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ả.

Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel

Excel 2022: Groups And Subtotals

Hàm Sumif Trong Kiểm Toán – Công Cụ Không Thể Bỏ Qua

Sắp Xếp Thứ Tự Sheet Bằng Vba

Cách Sắp Xếp Sheet (Bảng Tính) Trong File Excel Theo Thứ Tự A Đến Z Hoặc Ngược Lại

Cách Sử Dụng Hàm Subtotal Trong Excel

Công Thức Và Cách Sử Dụng Hàm Subtotal Trong Excel

Hướng Dẫn Kế Toán Sử Dụng Hàm Subtotal Trong Kế Toán Excel

Hướng Dẫn Sử Dụng Hàm Subtotal Trong Excel

Học Ms Excel 2013 Bài 36: Hàm Subtotal

Học Ms Excel 2013 Bài 36: Hàm Subtotal Hàm Tính Toán Cho Một Nhóm Con Trong Excel

Hàm Subtotal trong excel là một trong những hàm excel thường dùng trong kế toán và được sử dụng để tính tổng trong bảng Cân đối kế toán, cân đối phát sinh tháng/năm.. Subtotal là hàm excel tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất. Hàm này là hàm hơi khó sử dụng vì cấu trúc của nó không hề đơn giản, ở bài viết này Trung tâm kế toán Hà Nội sẽ hướng dẫn cách sử dụng hàm Subtotal trong cả excel 2007 và excel 2003.

Công thức sử dụng hàm Subtotal: = SUBTOTAL(function_num,ref1,ref2,…)

Trong đó:

Function_num là các con số từ 1 đến 11 (hay có thêm 101 đến 111 trong phiên bản Excel 2003, 2007) qui định hàm nào sẽ được dùng để tính toán trong subtotal.

Ref1, ref2,… là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó.

Ghi chú:

Nếu có hàm subtotal khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính nhằm tránh trường hợp tính toán 2 lần.

Đối số function_num nếu 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 (hàng ẩn). Đối số function_num nếu 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 (bỏ qua các giá trị ẩn).

Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả các hàng bị ẩn bởi lệnh Filter (Auto Filter) không phụ thuộc vào đối số function_num được dùng.

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ó không được thiết kế để tính theo chiều ngang.

Hàm này chỉ tính toán cho dữ liệu 2-D do vậy nếu dữ liệu tham chiếu dạng 3-D (Ví dụ về tham chiếu 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo lỗi #VALUE!.

Ví dụ về cách sử dụng hàm Subtotal như sau:

Dùng hàm Subtotal để tính tổng các khoản nợ ngắn hạn phải trả trên bảng CĐKT

Các bài viết mới

Các tin cũ hơn

Hướng Dẫn Sử Dụng Chức Năng Subtotal Thống Kê Dữ Liệu Theo Điều Kiện Trong Excel

Bài Tập Hàm Sumifs L Bài Tập Thực Hành Hàm Sumifs L Cú Pháp Hàm Sumifs

Cách Sử Dụng Hàm Sumif Trong Excel

Hàm Sumif Trong Excel, Chi Tiết Cách Sử Dụng Và Ví Dụ Thực Tế

#cách Sắp Xếp Thứ Tự Tăng Dần Trong Excel Nhanh Nhất

Công Thức Và Cách Sử Dụng Hàm Subtotal Trong Excel

Hướng Dẫn Kế Toán Sử Dụng Hàm Subtotal Trong Kế Toán Excel

Hướng Dẫn Sử Dụng Hàm Subtotal Trong Excel

Học Ms Excel 2013 Bài 36: Hàm Subtotal

Học Ms Excel 2013 Bài 36: Hàm Subtotal Hàm Tính Toán Cho Một Nhóm Con Trong Excel

Cách Sắp Xếp Tên Theo Thứ Tự Trong Excel

Tuy nhiên, đừng lo lắng! Trong bài biết này, chúng tôi sẽ giới thiệu với các bạn cách sử dụng đầy đủ và chi tiết nhất của hàm Subtotal trong Excel.

Cách sử dụng hàm Subtotal trong Excel

Cấu trúc và cách sử dụng

– Công dụng: sử dụng một hàm tính toán do người dùng lựa chọn và áp dụng lên vùng dữ liệu được quy định.

– 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.

Đối số thứ nhất của hàm function_num bắt buộc bạn phải nhớ. Tuy nhiên, từ Excel 2007+, Microsoft đã bổ sung tính năng AutoComplete giúp chúng ta lựa chọn mà không cần nhớ nữa.

ref1, ref2, ….: Các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó. Đối với Excel 2007+, bạn có thể dùng đến 254 vùng, còn với Excel 2003 trở về trước thì con số này chỉ là 29.

function_num

Hàm tính toán (Ý nghĩa của đối số function_num)

Tính toán cả giá trị ẩn (do ẩn hàng)

Không tính toán giá trị ẩn (do ẩn hàng)

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ó không được thiết kế để tính theo chiều ngang. Do đó, một số tính năng có thể không đúng khi bạn sử dụng SUBTOTAL để tính theo chiều ngang (ví dụ, ẩn cột).

– Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì các công thức này 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 đều bỏ qua khi tính toán dù function_num là bất cứ giá trị nào.

– Nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi # VALUE!. Ví dụ, để tính tổng giá trị các ô A1 trong các sheet liên tục từ Sheet1 đến Sheet2, nếu sử dụng tham chiếu dạng 3-D là =SUM(Sheet1:Sheet5!A1), tuy nhiên nếu áp dụng kiểu tham chiếu này cho SUBTOTAL, bạn sẽ nhận được thông báo lỗi # VALUE!

Lên đầu trang ↑

Ví dụ minh họa về hàm Subtotal

Ví dụ 1: Trong ví dụ sau đây, chúng tôi minh họa cách sử dụng hàm SUBTOTAL với đối số là 9 và 109 (hàm tính tổng) và so sánh với kết quả khi sử dụng hàm SUM.

Ví dụ 2: Như các bạn đã biết, tham chiếu 3-D tỏ ra đặc biệt hữu hiệu khi cần tổng hợp dữ liệu giữa các sheet có cấu trúc tương tự nhau.

Trong ví dụ sau, tại ô A1 của các sheet từ Sheet1:Sheet5 chứa một số tự nhiên lần lượt từ 1-5. Bằng cách sử dụng tham chiếu 3-D, chúng ta nhanh chóng có được tổng của các ô A1 trong 5 sheet này (tại ô F5).

Lên đầu trang ↑

Lời kết

Cách Sử Dụng Hàm Subtotal Trong Excel

Hướng Dẫn Sử Dụng Chức Năng Subtotal Thống Kê Dữ Liệu Theo Điều Kiện Trong Excel

Bài Tập Hàm Sumifs L Bài Tập Thực Hành Hàm Sumifs L Cú Pháp Hàm Sumifs

Cách Sử Dụng Hàm Sumif Trong Excel

Hàm Sumif Trong Excel, Chi Tiết Cách Sử Dụng Và Ví Dụ Thực Tế

🌟 Home
🌟 Top