Phương pháp 1 – Tự động chèn tổng phụ trong Excel
- Chọn một ô ngẫu nhiên> đi tới Dữ liệu tab> nhấp vào Sắp xếp .
- Sắp xếp hộp thoại sẽ bật lên trên cửa sổ.
- Trong Sắp xếp theo trường, hãy chọn cột mà bạn muốn chèn tổng phụ.
- Giữ Giá trị ô trong phần Sắp xếp trường.
- Thứ tự sẽ tăng dần:A đến Z .
- Nhấp vào OK .
- Điều này sẽ sắp xếp Đại diện bán hàng cột theo thứ tự abc.
Sử dụng tính năng tích hợp sẵn hay còn gọi là phím tắt tính tổng phụ trong Excel.
- Nhấp vào một ô trong phạm vi> chọn Tổng phụ trong Phác thảo nhóm từ Dữ liệu tab.
- Tổng phụ hộp thoại sẽ xuất hiện bây giờ. Chúng tôi sẽ chèn tổng phụ trong Excel bằng SUM chức năng.
- Tại mỗi lần thay đổi trong trường sẽ chứa dữ liệu mà bạn sẽ chèn tổng phụ.
- Đặt SUM hàm trong Sử dụng hàm trường để tính tổng giá trị.
- Nhấp vào OK .
Bạn sẽ có thể chèn tổng phụ trong Excel. Đại diện bán hàng cột sẽ chứa tổng phụ và tổng cuối .
- Biểu tượng ở góc trên bên trái có số 1, 2 và 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ý các nhóm cũng như hiển thị và ẩn tổng phụ trong Excel.
Phương pháp 2 – Cộng nhiều tổng phụ
2.1. Chèn nhiều tổng phụ vào các cột khác nhau
Trong phần trước, chúng tôi đã trình bày cách áp dụng tổng phụ cho cột Đại diện bán hàng. Thêm một tổng phụ khác dựa trên Danh mục .
- Mở Sắp xếp hộp thoại từ Dữ liệu tab.
- Sắp xếp theo Đại diện bán hàng .
- Thêm một cấp độ khác.
- Chúng tôi sắp xếp theo Đại diện bán hàng; bây giờ chúng ta sẽ sắp xếp theo Danh mục .
- Nhấp vào OK .
- Mở Tổng phụ hộp thoại từ Dữ liệu tab như được mô tả trong Phương thức-1 .
- Chọn Đại diện bán hàng trong Tại mỗi thay đổi trong trường.
- Excel sẽ chèn Tổng phụ vào Đại diện bán hàng cột.
- Mở Tổng phụ cửa sổ bật lên.
- Lần này đảm bảo mỗi thay đổi trong Danh mục .
- Thay thế tổng phụ hiện tại trường không được đánh dấu. Nếu không, nó sẽ xóa tổng phụ trước đó và tạo ra một tổng phụ duy nhất.
- Nhấp vào OK .
Nhóm nhiều lần và chèn tổng phụ trong Excel.
Chọn một thay đổi khác trong Tổng phụ nếu bạn muốn thêm tổng phụ.
2.2. Tìm nhiều tổng phụ trong cùng một cột
Chèn nhiều tổng phụ vào cùng một cột. Sau khi tính tổng ta sẽ tính giá trị trung bình và độ lệch chuẩn. Bạn sẽ cần thêm hai chức năng nữa. Chọn TRUNG BÌNH và sau đó là StdDev chức năng.
Bắt đầu sau khi chèn tổng phụ vào Đại diện bán hàng (thu được từ Phương thức-1 ) bằng cách sử dụng SUM chức năng.
- Mở Tổng phụ hộp gấp 2 lần.
- Chọn TRUNG BÌNH và lần thứ hai chọn StdDev chức năng.
- TRUNG BÌNH hàm sẽ tìm ra giá trị trung bình của các cột đã chọn.
- StdDev hàm 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ụ vào bảng Excel
Bạn không thể áp dụng tổng phụ trực tiếp trong bảng Excel. Tổng phụ tính năng này sẽ không hoạt động ở đây.
Nếu muốn chèn tổng phụ, trước tiên bạn phải chuyển bảng thành phạm vi.
- Chọn một ô ngẫu nhiên trong bảng của bạn và đi tới Thiết kế bảng tab> chọn Chuyển đổi sang phạm vi trong Công cụ nhóm.
- Microsoft Excel sẽ hỏi bạn có muốn chuyển đổi bảng thành một phạm vi hay không. Nhấp vào Có để xác nhận.
Excel bây giờ sẽ chuyển đổi bảng thành một phạm vi bình thường. 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 bằng Filter
Chúng tôi đã sử dụng Tổng phụ tính năng. Excel cũng có hàm SUBTOTAL có sẵn để thêm tổng phụ vào dữ liệu của bạn. Sử dụng hàm cho dữ liệu đã lọc .
Cú pháp của SUBTOTAL chức năng:
SUBTOTAL(function_num,ref1,[ref2],…)
Đối số đầu tiên:function_num được mô tả bằng một số con số (1-9 và 101-109 ) biểu thị các chức năng khác nhau.
4.1. Tổng phụ trong các hàng đã lọc
Chúng tôi đã áp dụng SUBTOTAL sau công thức chèn tổng phụ.
Khi chúng ta sử dụng đối số đầu tiên:9, nó sẽ bỏ qua ô được lọc
- Nhấp vào mũi tên thả xuống tại Đại diện bán hàng cột và chọn tên (ví dụ:Adam ) để lọc theo tên đó.
Bạn sẽ quan sát hàm chỉ tính tổng phụ cho các ô được lọc, nó sẽ bỏ qua phần còn lại.
Hình ảnh bên dưới thể hiện kết quả đầu ra của SUBTOTAL hàm cho cả hai đối số 9 và 109 .
4.2. Tổng phụ trong các hàng được ẩn thủ công
Xem kết quả của SUBTOTAL chức năng ẩn hàng thủ công; ẩn hàng bằng cách nhấp chuột phải vào số hàng và chọn Ẩn từ menu ngữ cảnh.
So sánh kết quả đầu ra cho các đối số 9 và 109 . Đối số 9 sẽ bao gồm các ô của các hàng được ẩn thủ công, nhưng đối số 109 sẽ bỏ qua chúng.
Phương pháp 5 – Tìm tổng phụ bằng mã VBA
- Nhấn ALT+F11 để khởi chạy Trình soạn thảo Visual Basic cửa sổ.
- Nhấp vào Chèn trên Thanh công cụ> chọn Mô-đun .
- Chèn mã VBA của bạn vào Mô-đun cửa sổ.
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 vào Chạy nút để thực thi VBA mã.
Bây giờ Excel sẽ chèn tổng phụ vào dữ liệu của bạn dựa trên Đại diện bán hàng .
Phương pháp 6 – Thêm tổng phụ trong Bảng tổng hợp Excel
Có bốn trường trong bảng tổng hợp và chúng tôi có bảng tổng hợp được định dạng sử dụng dữ liệu của chúng tôi trong các lĩnh vực đó:
1. Bộ lọc :Đại diện bán hàng
- Cột :Trống
- Hàng :Danh mục, Sản phẩm
- Giá trị :Số lượng, đơn giá, tổng giá và lợi nhuận
- Đi tới Thiết kế tab> nhấp vào menu thả xuống của Tổng phụ tùy chọn trong Bố cục nhóm.
- Chọn Hiển thị tất cả Tổng phụ ở cuối nhóm từ trình đơn thả xuống.
Giờ đây, bảng tổng hợp sẽ chèn tổng phụ ở cuối mỗi nhóm dựa trên Nhãn hàng . Thêm tổng cộng của nhóm ở cuối bảng.
Phương pháp 7 – Sử dụng Power Query để chèn tổng phụ
Sử dụng truy vấn nguồn để chèn tổng phụ cho các nhóm .
- Nhấp vào một ô trong phạm vi> đi tới Dữ liệu tab> nhấp vào Từ Bảng/Phạm vi trong Nhận và chuyển đổi dữ liệu .
- Nhấp vào OK trên Tạo bảng hộp thoại.
- Excel sẽ chuyển bạn tới Trình soạn thảo Power Query cửa sổ.
- Chọn các cột (tức là Đại diện bán hàng và Danh mục ) trong dữ liệu bạn muốn xử lý> nhấp vào Nhóm theo biểu tượng từ Biến đổi tab.
- Trong Nhóm theo hộp thoại, đặt tên cột Mới.
- Chọn loại thao tác bạn muốn thực hiện (tức là SUM).
- Chọn cột nơi bạn muốn thực hiện phép tính (tức là Tổng giá).
- Nhấp vào Thêm xâm lược để nhóm thêm.
- Để tính tổng phụ của Lợi nhuận , chọn Lợi nhuận trong Cột trường và Tổng trong Chiến dịch lĩnh vực. Đặt tên cho hành vi gây hấn này (tức là Lợi nhuận ).
- Nhấp vào OK .
- Bạn sẽ thấy các tổng phụ được chèn dựa trên Đại diện bán hàng và Danh mục.
- Đi tới Trang chủ tab> nhấp vào thả xuống của Đóng &tải> chọn Đóng và tải .
Truy vấn Power trình chỉnh sửa sẽ tải dữ liệu đã nhóm của bạn cùng với các tổng phụ vào bảng tính Excel.
Cách xóa tổng phụ trong Excel
- Mở Tổng phụ hộp thoại từ Dữ liệu tab> nhấp vào Xóa tất cả .
- Chuyển đổi dữ liệu của bạn về phạm vi bình thường bằng cách xóa nhóm và tổng phụ khỏi phạm vi đó.
Lưu ý :Việc loại bỏ tổng phụ sẽ khiến dữ liệu được sắp xếp chứ không giống như dữ liệu gốc như 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 của mình nếu nó được bảo vệ bằng mật khẩu.
ii) Sổ làm việc được chia sẻ:Bạn có thể cần hủy chia sẻ sổ làm việc hoặc kiểm tra sổ làm việc nếu sổ làm việc được chia sẻ giữa nhiều người dùng
iii) Công thức hoặc macro tùy chỉnh:Nếu tổng phụ được thêm bằng công thức hoặc macro tùy chỉnh, bạn có thể cần phải sửa đổi hoặc xóa chúng để loại bỏ chúng.
2. Làm cách 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 dàn ý được nhóm để thu gọn hoặc mở rộng tổng phụ trong Excel. Excel tự động thêm các 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 biểu tượng 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ể đặt Excel cập nhật tổng phụ khi dữ liệu tự động thay đổi. Bạn cũng có thể làm mới tổng phụ theo cách thủ công bằng cách nhấp chuột phải vào hàng tổng phụ và chọn “Làm mới” từ menu ngữ cảnh. Nếu chúng không được cập nhật tự động, hãy đảm bảo sổ làm việc của bạn tự động tính các 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 TỔNG HỢP và TỔNG SUB hoạt động mặc dù cú pháp tương tự nhau.
Bài học rút ra 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 được chỉ định.
- Để chèn tổng phụ, hãy chọn phạm vi dữ liệu và chuyển đến tab “Dữ liệu”, sau đó nhấp vào nút “Tổng phụ” trong nhóm “Đề cương”.
- Bạn có thể thêm nhiều cấp độ tổng phụ bằng cách chọn nhiều cột để tính tổng phụ.
- Tổng phụ có thể được thu gọn hoặc mở rộng bằng tính năng dàn ý được nhóm, được biểu thị bằng ký hiệu trừ (-) và dấu cộng (+).
- Tổng phụ có thể được cập nhật tự động khi dữ liệu thay đổi bằng cách sử dụng bảng hoặc phạm vi được đặt tên động của Excel.
Tải xuống sổ tay thực hành
Bạn có thể tải sách bài tập thực hành xuống từ đây.
<