Top #10 ❤️ Data Subtotal Trong Excel Xem Nhiều Nhất, Mới Nhất 10/2022 ❣️ Top Trend | Trucbachconcert.com

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

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

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ừ 2022 đế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…

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

Đây Là Những Hàm Cơ Bản Nhất Trong Excel Mà Bạn Cần Nắm Rõ

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

Sorting And Filtering Data With Excel

Làm Cách Nào Để Lọc Mọi Hàng Khác (Hàng Chẵn

How To Sort And Filter Data In Excel

How To Freeze Panes In Excel

How To Freeze A Row In Excel So It Always Stays On The Screen, Even When You Scroll Away

8 Cách Làm Tròn Số Trong Excel Với Hàm Round, Roundup

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Go down to the Sort option – when hovering over Sort the sub-menu will appear

Select Expand the selection

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

Go to the Data tab on Excel ribbon

Select the Filter tool

Select Eastern Company from the dropdown menu

Select Dylan Rogers from the Salesperson dropdown menu

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

Cách Sử Dụng Excel 2022 – Tính Năng Sort Và Filter

Excel Autofilter Shows Filter Mode – Contextures Blog

Chọn Lọc Ô Cell Theo Màu Sắc Trong Excel

Excel Filter For Date Range 2 Ways – Contextures Blog

Hướng Dẫn Khóa Một Vùng Dữ Liệu Trong Excel Bằng Mật Khẩu

Mẹo Sử Dụng Hàm Subtotal Trong Excel

Cách Dùng Hàm Subtotal Trong Excel

Cách Tạo Danh Sách Sổ Xuống (Drop List) Trên Excel 2022

Cách Lọc Và Sắp Xếp Theo Màu Ô, Màu Phông Chữ Và Biểu Tượng Trong Excel

Hướng Dẫn Cách Xử Lý Lỗi Dữ Liệu Dạng Text Trong Excel

Hàm Sumif / Sumifs Tính Tổng Theo Điều Kiện

Lượt Xem:1695

Mẹo sử dụng hàm Subtotal trong excel

Excel online đưa ra Lệnh Excel Subtotal cung cấp một cách nhanh chóng để tạo ra các tổng phụ cho các trường riêng lẻ trong một bảng dữ liệu.

Lưu ý rằng lệnh này không giống như hàm Subtotal của Excel , thực hiện các phép tính toán học cho một phạm vi ô hiển thị.

Bảng tính số liệu bán hàng trong ba tháng

Chúng tôi sẽ minh họa lệnh Excel Subtotal sử dụng bảng tính bên phải , trong đó liệt kê các số liệu bán hàng cho ba đội trong ba tháng đầu tiên của năm.

Các bước sau có thể được sử dụng để hiển thị tổng phụ cho mỗi tháng, tháng 1, tháng 2 và tháng 3:

Chọn bất kỳ ô nào trong bảng dữ liệu của bạn (Excel sẽ tự động phát hiện toàn bộ phạm vi dữ liệu để sử dụng trong lệnh tổng phụ);

Nhấp vào tùy chọn Subtotal nằm trong nhóm ‘ Phác thảo‘, trong tab Dữ liệu của ruy-băng Excel.

Điều này sẽ làm cho hộp thoại Subtotal mở ra (xem bên phải ). Trong hộp này:

Trong A t mỗi thay đổi trong: trường nhập, chọn Tháng ;

Trong hàm U se: trường nhập, chọn Sum ;

Trong trường A d d subtotal to: field, đảm bảo rằng tùy chọn Sales được chọn;

Các tùy chọn này yêu cầu Excel hiển thị tổng phụ mỗi lần giá trị trong cột Tháng thay đổi và tổng số phụ này sẽ hiển thị Tổng của dữ liệu trong cột Bán hàng .

Bảng tính kết quả được hiển thị bên dưới:

Nhấp vào các phác thảo, ở bên trái của bảng cho phép bạn ẩn hoặc hiển thị chi tiết của mỗi phần trong bảng dữ liệu của bạn.

Tùy chọn phụ khác

Hộp thoại cho lệnh Subtotal của Excel

Các Một t mỗi thay đổi trong: lĩnh vực của hộp thoại Subtotal cho phép bạn chọn những lĩnh vực bảng dữ liệu của bạn, bạn muốn Subtotals để áp dụng cho.

Ví dụ, trong bảng tính trên, nếu chúng ta đã chọn lĩnh vực Đội , một tổng số phụ sẽ xuất hiện mỗi khi tên đội thay đổi (mặc dù trong trường hợp này, nó sẽ hữu ích hơn để đặt hàng các bảng dữ liệu bởi đội ngũ trước khi áp dụng tổng số phụ).

Hàm U se: trường của hộp thoại Subtotal cho phép bạn chọn một phép toán được thực hiện trên các nhóm dữ liệu.

Ví dụ: trong bảng tính ở trên, thay vì tính Tổng của các số liệu bán hàng hàng tháng, chúng tôi có thể tính số liệu doanh số trung bình cho mỗi tháng.

Các A d d tổng số phụ để: lĩnh vực của Subtotal hộp thoại cho phép bạn chọn các cột của bảng mà bạn muốn các phép toán được lựa chọn để được áp dụng cho. Bạn có thể chọn nhiều trường để áp dụng thao tác tổng phụ, mặc dù tùy chọn này thực sự chỉ có ý nghĩa đối với các trường chứa số.

Các Replace c tổng số phụ urrent checkbox cho bạn tùy chọn để thêm nhiều hơn một tổng số phụ vào một bảng dữ liệu.

Ví dụ, nếu trong bảng tính trên, chúng tôi muốn thể hiện sự Sum và các Trung bình số liệu bán hàng cho từng tháng, chúng tôi có thể lần đầu tiên áp dụng Sum tổng phụ (như trong ví dụ) và sau đó chúng ta có thể chọn tùy chọn tổng phụ một lần nữa và yêu cầu Tính trung bình . Theo đây:

Nếu Replace c tổng số phụ urrent hộp được kiểm tra, yêu cầu tổng phụ thứ hai (đối với trung bình ) sẽ thay thế các Sum tổng số phụ;

Nếu Replace c tổng số phụ urrent hộp là không kiểm tra, yêu cầu tổng phụ thứ hai (đối với trung bình ) sẽ được hiển thị ngoài các Sum tổng số phụ.

Bảng tính của các số liệu bán hàng có tổng phụ và tổng số trung bình được áp dụng

Các bảng tính ở bên phải cho thấy kết quả sau khi áp dụng cả Sum và các trung bình tổng số phụ.

P nghỉ tuổi giữa các nhóm

Nếu bạn đánh dấu vào P nghỉ tuổi giữa các nhóm hộp kiểm, Excel sẽ chèn ngắt trang vào bảng tính của bạn sau mỗi nhóm dữ liệu. Điều này có thể hữu ích nếu bạn muốn in bảng tính của mình.

S ummary bên dưới dữ liệu

Hộp kiểm S ummary bên dưới dữ liệu chỉ định rõ vị trí của các bản tóm tắt nhóm.

Cách xóa Subtotals khỏi bảng tính của bạn

Để xóa tổng phụ từ bảng của bạn:

Chọn bất kỳ ô nào trong bảng dữ liệu;

Nhấp vào tùy chọn Subtotal (trong tab Dữ liệu của ruy-băng Excel):

Khi hộp thoại Subtotal mở ra, nhấp vào nút R emove All .

Các ví dụ khác về hàm Excel Subtotal được cung cấp trên trang web Hỗ trợ Microsoft Office .

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

Các tin cũ hơn

Hướng Dẫn Chi Tiết Cách Sử Dụng Công Thức Subtotal Trong Excel

Cách Sử Dụng Hàm Sumif Và Sumifs Để Tính Tổng Có Điều Kiện Trong Excel

Bỏ Túi Ngay Các Kiến Thức Tổng Hợp Về Hàm Sum Trong Excel

Hướng Dẫn Sử Dụng Hàm Sumproduct Nâng Cao Trong Excel

Cách Sử Dụng Hàm Sumproduct Trong Excel Đơn Giản Nhất

🌟 Home
🌟 Top