Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính tổng phụ theo nhóm bằng cách sử dụng Hàm SUMIFS trong Excel và Google Trang tính.
Bảng tổng số phụ theo danh mục hoặc nhóm
Trước tiên, chúng tôi sẽ trình bày cách tạo bảng tóm tắt tổng phụ động từ một phạm vi dữ liệu trong Excel 365 trở đi hoặc Google Trang tính.
Chúng tôi sử dụng Hàm UNIQUE và Hàm SUMIFS để tự động tính tổng Số lượng sản phẩm qua Nhóm sản phẩm:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Để tạo bảng tổng phụ này, chúng tôi sử dụng ứng dụng tiêu chuẩn của Hàm SUMIFS để tính tổng Số lượng sản phẩm phù hợp với từng Nhóm sản phẩm. Tuy nhiên, trước khi có thể thực hiện được điều này, chúng ta cần tạo một danh sách Nhóm sản phẩm. Người dùng Microsoft Excel 365 và Google Trang tính có quyền truy cập vào Hàm UNIQUE để tạo danh sách động các giá trị duy nhất từ một phạm vi ô. Trong ví dụ này, chúng tôi thêm công thức sau vào ô E3:
| 1 | = DUY NHẤT (B3: B11) |
Khi công thức này được nhập, một danh sách sẽ tự động được tạo bên dưới ô để hiển thị tất cả các giá trị duy nhất được tìm thấy trong Nhóm sản phẩm phạm vi dữ liệu. Trong ví dụ này, danh sách tự mở rộng đến E3: E5 để hiển thị cả 3 Nhóm sản phẩm các giá trị.
Đây là một hàm mảng động trong đó kích thước của danh sách kết quả không cần xác định và nó sẽ tự động thu nhỏ và lớn lên khi các giá trị dữ liệu đầu vào thay đổi.
Lưu ý rằng trong Excel 365, Hàm UNIQUE không phân biệt chữ hoa chữ thường, nhưng trong Google Trang tính thì có. Xem xét danh sách {“A”; "Một"; "NS"; "NS"}. Đầu ra của Hàm UNIQUE phụ thuộc vào chương trình:
- {"MỘT"; "NS"; “C”} trong Excel 365
- {"MỘT"; "Một"; "NS"; “C”} trong Google Trang tính
Nếu bạn đang sử dụng phiên bản Excel trước Excel 365, bạn sẽ cần phải thực hiện một cách tiếp cận khác. Điều này sẽ được thảo luận trong phần tiếp theo.
Bảng tổng hợp con theo thể loại hoặc nhóm - trước Excel 365
Nếu bạn đang sử dụng phiên bản Excel trước Excel 365, thì Hàm UNIQUE không có sẵn để sử dụng. Để sao chép cùng một hành vi, bạn có thể kết hợp Hàm INDEX và Hàm MATCH với Hàm COUNTIF để tạo công thức mảng nhằm tạo ra danh sách các giá trị duy nhất từ một phạm vi ô:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Để công thức này hoạt động, các tham chiếu ô cố định cần được viết cẩn thận, với Hàm COUNTIF tham chiếu đến phạm vi $ E $ 2: E2, là phạm vi bắt đầu từ E2 cho đến ô phía trên ô chứa công thức.
Công thức cũng cần được nhập dưới dạng công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER sau khi nó đã được viết. Công thức này là một Công thức mảng 1 ô, sau đó có thể sao chép-dán vào các ô E4, E5, v.v. Không nhập điều này làm công thức mảng cho toàn bộ phạm vi E3: E5 trong một hành động.
Theo cách tương tự như trong ví dụ trước, một Hàm SUMIFS sau đó được sử dụng để tính tổng phụ của Số lượng sản phẩm qua Nhóm sản phẩm:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Tổng theo Danh mục hoặc Nhóm - Tổng phụ trong Bảng Dữ liệu
Để thay thế cho phương pháp bảng tóm tắt được trình bày ở trên, chúng ta có thể thêm tổng phụ trực tiếp vào bảng dữ liệu. Chúng tôi sẽ chứng minh điều này bằng cách sử dụng Hàm IF cùng với Hàm SUMIFS để thêm Tổng phụ theo nhóm vào bảng dữ liệu ban đầu.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Ví dụ này sử dụng một hàm SUMIFS được lồng trong một hàm IF. Hãy chia nhỏ ví dụ thành các bước:
Để thêm thống kê tóm tắt trực tiếp vào bảng dữ liệu, chúng ta có thể sử dụng Hàm SUMIFS. Chúng tôi bắt đầu bằng cách tổng Số lượng sản phẩm phù hợp với liên quan Nhóm sản phẩm:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Công thức này tạo ra một giá trị tổng phụ cho mọi hàng dữ liệu. Để chỉ hiển thị tổng phụ trong hàng dữ liệu đầu tiên của mỗi Nhóm sản phẩm, chúng tôi sử dụng Hàm IF. Lưu ý rằng dữ liệu phải được sắp xếp theo Nhóm sản phẩm để đảm bảo rằng các tổng phụ được hiển thị chính xác.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Hàm IF so sánh từng hàng dữ liệu Nhóm sản phẩm giá trị với hàng dữ liệu phía trên nó và nếu chúng có cùng giá trị, nó sẽ xuất ra một ô trống (“”).
Nếu Nhóm sản phẩm các giá trị khác nhau, tổng được hiển thị. Theo cách này, mỗi Nhóm sản phẩm tổng chỉ được hiển thị một lần (trên hàng của trường hợp đầu tiên của nó).
Sắp xếp tập dữ liệu theo nhóm
Nếu dữ liệu chưa được sắp xếp, chúng tôi vẫn có thể sử dụng cùng một công thức cho tổng phụ.
Tập dữ liệu ở trên không được sắp xếp theo Nhóm sản phẩm, nên Tổng phụ theo nhóm cột hiển thị mỗi tổng phụ nhiều hơn một lần. Để có được dữ liệu ở định dạng chúng tôi muốn, chúng tôi có thể chọn bảng dữ liệu và nhấp vào “Sắp xếp A đến Z”.
Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị một số công thức không có tham chiếu ô bị khóa:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Đọc bài viết của chúng tôi về Khóa tham chiếu ô để tìm hiểu thêm.
Sử dụng bảng tổng hợp để hiển thị tổng phụ
Để loại bỏ yêu cầu sắp xếp trước dữ liệu theo Nhóm sản phẩm, chúng ta có thể sử dụng sức mạnh của Pivot Table để tóm tắt dữ liệu. Bảng tổng hợp tính toán tổng phụ tự động và hiển thị tổng và tổng phụ ở một số định dạng khác nhau.
Tổng theo Danh mục hoặc Nhóm trong Google Trang tính
Các công thức này hoạt động tương tự trong Google Trang tính cũng như trong Excel. Tuy nhiên, Hàm UNIQUE phân biệt chữ hoa chữ thường trong Google Trang tính.
