Thành thạo Tính tổng phụ trong Excel: 7 kỹ thuật đã chứng minh cho tóm tắt dữ liệu chính xác

Phương pháp 1 – Chèn tổng phụ (Subtotals) trong Excel tự động

• Chọn ô ngẫu nhiên > đi đến tab Dữ liệu (Data) > nhấp Sắp xếp (Sort).

• Hộp thoại Sắp xếp (Sort) sẽ hiện ra.
• Trong trường Sắp xếp theo (Sort by), chọn cột dựa trên đó bạn muốn chèn tổng phụ.
• Giữ Giá trị ô (Cell Values) trong trường Sắp xếp dựa trên (Sort On).
• Thứ tự sẽ tăng dần: A đến Z.
• Nhấp OK.

• Điều này sẽ sắp xếp cột Nhân viên bán hàng (Sales Reps) theo chữ cái.

Sử dụng tính năng tích hợp, còn gọi là phím tắt tổng phụ trong Excel.

• Nhấp vào ô trong phạm vi > chọn Tổng phụ (Subtotal) dưới nhóm Đề mục (Outline) từ tab Dữ liệu (Data).

• Hộp thoại Tổng phụ (Subtotal) sẽ xuất hiện. Chúng ta sẽ chèn tổng phụ trong Excel bằng hàm SUM.
• Trường Tại mỗi thay đổi trong (At each change in) phải chứa dữ liệu bạn sẽ chèn tổng phụ.
• Đặt hàm SUM trong trường Sử dụng hàm (Use function) để tính tổng giá trị.
• Nhấp OK.

Bạn sẽ có thể chèn tổng phụ trong Excel. Cột Nhân viên bán hàng sẽ chứa tổng phụ và tổng chung (grand totals).

• Biểu tượng ở góc trên bên trái với số 1, 2, 3 sẽ hướng dẫn bạn cách sử dụng tổng phụ trong Excel. Nó cho phép bạn xử lý nhóm và hiển thị/ẩn tổng phụ trong Excel.

Phương pháp 2 – Thêm nhiều tổng phụ

2.1. Chèn nhiều tổng phụ trong các cột khác nhau

Trong phần trước, chúng ta đã thấy cách áp dụng tổng phụ cho cột Nhân viên bán hàng. Thêm tổng phụ khác dựa trên Danh mục (Category).

• Mở hộp thoại Sắp xếp (Sort) từ tab Dữ liệu (Data).
• Sắp xếp theo Nhân viên bán hàng.
• Thêm cấp độ khác.
• Chúng ta sắp xếp theo Nhân viên bán hàng; bây giờ sẽ sắp xếp theo Danh mục.
• Nhấp OK.

• Mở hộp thoại Tổng phụ (Subtotal) từ tab Dữ liệu (Data) như mô tả ở Phương pháp-1.
• Chọn Nhân viên bán hàng trong trường Tại mỗi thay đổi trong (At each change in).

• Excel sẽ chèn Tổng phụ vào cột Nhân viên bán hàng.
• Mở hộp thoại Tổng phụ (Subtotal) lại.
• Lần này đảm bảo mỗi thay đổi trong Danh mục.
• Trường Thay thế tổng phụ hiện tại (Replace Current Subtotals) không được tích. Nếu tích, nó sẽ xóa tổng phụ trước và chỉ còn một tổng phụ.
• Nhấp OK.

Nhóm nhiều lần và chèn tổng phụ trong Excel.

Chọn thay đổi khác trong hộp Tổng phụ (Subtotal) nếu muốn thêm tổng phụ hơn.

2.2. Tìm nhiều tổng phụ trong cùng một cột

Chèn nhiều tổng phụ trong cùng một cột. Sau khi tính tổng, chúng ta sẽ tính trung bình và độ lệch chuẩn. Bạn cần hai hàm nữa. Chọn hàm AVERAGE và sau đó chọn hàm StdDev.

Bắt đầu sau khi chèn tổng phụ cho Nhân viên bán hàng (từ Phương pháp-1) bằng hàm SUM.

• Mở hộp Tổng phụ (Subtotal) 2 lần nữa.
• Chọn hàm AVERAGE và lần thứ hai chọn hàm StdDev.

• Hàm AVERAGE sẽ tìm trung bình của các cột được chọn.
• Hàm StdDev sẽ tính độ lệch chuẩn.

Dựa trên các hàm này, tổng phụ sẽ được chèn vào dữ liệu của bạn.

Phương pháp 3 – Chèn tổng phụ trong Bảng Excel (Excel Table)

Bạn không thể áp dụng tổng phụ trực tiếp trong Bảng Excel. Tính năng Tổng phụ (Subtotal) không hoạt động ở đây.

Nếu bạn muốn chèn tổng phụ, bạn phải chuyển bảng sang phạm vi (range) trước.

• Chọn ô ngẫu nhiên trong bảng của bạn, đi đến tab Thiết kế bảng (Table Design) > chọn Chuyển đổi sang phạm vi (Convert to Range) dưới nhóm Công cụ (Tools).

• Microsoft Excel sẽ hỏi bạn có muốn chuyển bảng sang phạm vi không. Nhấp Có (Yes) để xác nhận.

Excel sẽ chuyển bảng thành phạm vi bình thường. Bây giờ bạn có thể chèn tổng phụ bằng cách áp dụng tính năng tổng phụ.

Phương pháp 4 – Áp dụng công thức SUBTOTAL trong Excel với Bộ lọc (Filter)

Chúng ta đã dùng tính năng Tổng phụ (Subtotal). Excel cũng có hàm SUBTOTAL để thêm tổng phụ vào dữ liệu. Dùng hàm cho dữ liệu đã lọc.

Cú pháp của hàm SUBTOTAL:

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

Tham số đầu tiên: function_num được mô tả bởi các số (1-9 và 101-109) biểu thị các hàm khác nhau.

4.1. Tổng phụ trong hàng bị lọc ra

Chúng ta áp dụng công thức SUBTOTAL sau để chèn tổng phụ.

Vì chúng ta dùng tham số đầu tiên: 9, nó sẽ bỏ qua ô đã lọc.

• Nhấp mũi tên thả xuống tại cột Nhân viên bán hàng và chọn một tên (ví dụ Adam) để lọc theo tên đó.

Bạn sẽ quan sát hàm tính tổng phụ chỉ cho các ô đã lọc, nó sẽ bỏ qua phần còn lại.

Hình dưới minh họa đầu ra của hàm SUBTOTAL cho cả hai tham số 9 và 109.

4.2. Tổng phụ trong hàng ẩn thủ công

Xem đầu ra của hàm SUBTOTAL cho hàng ẩn thủ công; ẩn hàng bằng cách chuột phải vào số hàng và chọn Ẩn (Hide) từ menu ngữ cảnh.

So sánh đầu ra cho tham số 9 và 109. Tham số 9 sẽ bao gồm ô của hàng ẩn thủ công, nhưng tham số 109 sẽ bỏ qua chúng.

Phương pháp 5 – Tìm tổng phụ với mã VBA

• Nhấn ALT+F11 để mở cửa sổ Trình soạn thảo Visual Basic.
• Nhấp Chèn (Insert) trên thanh công cụ > chọn Module.

• Chèn mã VBA của bạn vào cửa sổ Module.

Mã:

Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
Rows(iValue + 1).Insert
Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
For iColumn = 7 To 8 'Columns to Calculate Sum
Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
Next iColumn
Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
iValue = iValue + 2
xValue = iValue
Else
iValue = iValue + 1
End If
Loop
Application.ScreenUpdating = True
End Sub

• Nhấp nút Chạy (Run) để thực thi mã VBA.

Excel sẽ chèn tổng phụ vào dữ liệu dựa trên Nhân viên bán hàng.

Phương pháp 6 – Thêm tổng phụ trong Bảng Pivot (Pivot Table)

Có bốn trường trong Bảng Pivot, và chúng ta đã định dạng bảng Pivot bằng dữ liệu trong các trường đó:
1. Bộ lọc (Filter): Nhân viên bán hàng

1. Cột (Columns): Trống
2. Hàng (Rows): Danh mục, Sản phẩm
3. Giá trị (Values): Số lượng, Đơn giá, Tổng giá, Lợi nhuận

• Đi đến tab Thiết kế (Design) > nhấp danh sách thả xuống tùy chọn Tổng phụ (Subtotals) dưới nhóm Bố cục (Layout).

• Chọn Hiển thị tất cả Tổng phụ ở dưới cùng của Nhóm (Show all Subtotals at Bottom of Group) từ menu thả xuống.

Bảng Pivot sẽ chèn tổng phụ ở dưới cùng của mỗi nhóm dựa trên Nhãn hàng (Row Labels). Thêm tổng chung của nhóm ở dưới cùng bảng.

Phương pháp 7 – Sử dụng Power Query để chèn tổng phụ

Sử dụng power query để chèn tổng phụ cho các nhóm.

• Nhấp ô trong phạm vi > đi đến tab Dữ liệu (Data) > nhấp Từ Bảng/Phạm vi (From Table/Range) dưới Lấy & Chuyển đổi dữ liệu (Get & Transform Data).

• Nhấp OK trên hộp thoại Tạo bảng (Create Table).

• Excel sẽ chuyển bạn đến cửa sổ Trình soạn thảo Power Query.
• Chọn các cột (ví dụ Nhân viên bán hàng và Danh mục) trong dữ liệu bạn muốn xử lý > nhấp biểu tượng Nhóm theo (Group By) từ tab Chuyển đổi (Transform).

• Trong hộp thoại Nhóm theo (Group By), đặt tên cột mới.
• Chọn loại phép toán bạn muốn thực hiện (ví dụ: SUM).
• Chọn Cột (Column) nơi bạn muốn thực hiện tính toán (ví dụ: Tổng giá).
• Nhấp Thêm tổng hợp (Add Aggregation) để nhóm thêm.

• Để tính tổng phụ của Lợi nhuận, chọn Lợi nhuận trong trường Cột (Column) và Sum trong trường Phép toán (Operation). Đặt tên cho tổng hợp này (ví dụ: Lợi nhuận).
• Nhấp OK.

• Bạn sẽ thấy tổng phụ được chèn dựa trên Nhân viên bán hàng và Danh mục.
• Đi đến tab Trang chủ (Home) > nhấp danh sách thả xuống Đóng & Tải (Close & Load) > chọn Đóng & Tải (Close & Load).

Trình soạn thảo Power Query sẽ tải dữ liệu đã nhóm với tổng phụ vào trang tính Excel.

Cách xóa tổng phụ trong Excel

• Mở hộp thoại Tổng phụ (Subtotals) từ tab Dữ liệu (Data) > nhấp Xóa tất cả (Remove all).

• Nhận dữ liệu chuyển về phạm vi bình thường bằng cách xóa nhóm và tổng phụ.

Lưu ý: Việc xóa tổng phụ để lại dữ liệu đã sắp xếp, không như dữ liệu gốc trước khi sắp xếp.

Câu hỏi thường gặp

1. Tại sao tôi không thể xóa tổng phụ trong Excel?

Tổng phụ có thể không hoạt động vì một số lý do.

i) Trang tính được bảo vệ: Bạn phải bỏ bảo vệ trang tính nếu nó được bảo vệ bằng mật khẩu.

ii) Sổ làm việc chia sẻ: Bạn có thể cần bỏ chia sẻ sổ làm việc hoặc kiểm tra nếu sổ làm việc được chia sẻ giữa nhiều người dùng.

iii) Công thức tùy chỉnh hoặc macro: Nếu tổng phụ được thêm bằng công thức tùy chỉnh hoặc macro, bạn có thể cần sửa đổi hoặc xóa chúng để gỡ bỏ.

2. Làm thế nào để thu gọn hoặc mở rộng tổng phụ trong Excel?

Bạn có thể sử dụng tính năng phác thảo nhóm để thu gọn hoặc mở rộng tổng phụ trong Excel. Excel tự động thêm ký hiệu phác thảo cho phép bạn thu gọn hoặc mở rộng các nhóm tổng phụ. Nhấp vào ký hiệu trừ (-) hoặc cộng (+) bên cạnh các hàng nhóm sẽ thu gọn hoặc mở rộng tổng phụ tương ứng.

3. Tôi có thể tự động cập nhật tổng phụ trong Excel khi dữ liệu thay đổi không?

Có, bạn có thể thiết lập Excel để tự động cập nhật tổng phụ khi dữ liệu thay đổi. Bạn cũng có thể làm mới tổng phụ thủ công bằng cách chuột phải vào hàng tổng phụ và chọn “Làm mới (Refresh)” từ menu ngữ cảnh. Nếu chúng không tự động cập nhật, hãy đảm bảo sổ làm việc của bạn tự động tính công thức.

4. Hàm AGGREGATE có giống hàm SUBTOTAL trong Excel không?

Không, có sự khác biệt giữa AGGREGATE và SUBTOTAL mặc dù cú pháp tương tự.

Điểm chính từ bài viết

• Tính năng “Tổng phụ” trong Excel cho phép bạn chèn tổng phụ cho các nhóm dữ liệu dựa trên các cột chỉ định.
• Để chèn tổng phụ, chọn phạm vi dữ liệu và đi đến tab “Dữ liệu”, sau đó nhấp vào nút “Tổng phụ” trong nhóm “Đề mục”.
• Bạn có thể thêm nhiều cấp tổng phụ bằng cách chọn nhiều cột để tổng phụ.
• Tổng phụ có thể thu gọn hoặc mở rộng bằng tính năng phác thảo nhóm, được chỉ ra bằng ký hiệu trừ (-) và cộng (+).
• Tổng phụ có thể tự động cập nhật khi dữ liệu thay đổi bằng cách sử dụng Bảng Excel hoặc phạm vi đặt tên động.

Tải xuống tệp thực hành

Bạn có thể tải xuống tệp thực hành từ đây.

<< Quay lại Tổng phụ trong Excel | Học Excel

Nhận miễn phí Bài tập Excel nâng cao có lời giải!