Tính tổng các giá trị theo nhóm trong Excel
Đôi khi, bạn có thể có yêu cầu tính tổng các giá trị dựa trên nhóm trong bảng. Ví dụ: tôi có một danh sách các sản phẩm với số tiền tương ứng của chúng trong một cột khác, bây giờ, tôi muốn lấy tổng phụ cho mỗi sản phẩm như hình minh họa bên dưới. Hướng dẫn này sẽ giới thiệu một số công thức để giải quyết công việc này trong Excel.
- Tính tổng các giá trị theo nhóm - tổng phụ trong bảng dữ liệu gốc
- Tính tổng các giá trị theo nhóm - tổng phụ ở một vị trí khác
Tính tổng các giá trị theo nhóm - tổng phụ trong bảng dữ liệu gốc
Ở đây, cú pháp chung để tính tổng các giá trị dựa trên nhóm trong Excel sẽ như sau:
- group_name: Ô có tên nhóm bạn muốn tính tổng;
- cell_above_group_name: Ô phía trên tên nhóm;
- group_range: Phạm vi ô chứa tên nhóm;
- sum_range: Phạm vi ô tương ứng với tên nhóm được chỉ định để cộng lại với nhau.
Nếu dữ liệu được sắp xếp theo cột nhóm, cùng một sản phẩm được sắp xếp cùng nhau như hình minh họa bên dưới. Để dữ liệu tổng phụ theo nhóm, bạn có thể tạo công thức dựa trên các hàm IF và SUMIF.
1. Vui lòng sao chép và dán công thức sau vào ô trống bên cạnh dữ liệu của bạn:
- Chú thích: Trong công thức:
- A1 là ô tiêu đề và A2 là ô đầu tiên chứa tên sản phẩm bạn muốn sử dụng;
- A2: A13 là danh sách chứa các tên sản phẩm mà bạn muốn tính tổng dựa trên;
- B2: B13 là dữ liệu cột bạn muốn lấy tổng phụ.
2. Và sau đó, kéo chốt điền xuống các ô mà bạn muốn sử dụng công thức này và tổng phụ được tính dựa trên từng tên sản phẩm, xem ảnh chụp màn hình:
Giải thích công thức:
=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))
- SUMIF($A$2:$A$13,A2,$B$2:$B$13): Hàm SUMIF này sẽ chỉ tính tổng các giá trị trong phạm vi B2: B13 nếu các giá trị tương ứng của chúng trong phạm vi A2: A13 bằng với tiêu chí A2.
- IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)): Hàm IF này kiểm tra từng giá trị trong cột A nếu giá trị của nó giống với giá trị trong ô trên. Ví dụ: nếu giá trị ô A2 bằng ô A1, không có gì (“”) sẽ trả về, nếu không khớp, kết quả của hàm SUMIF sẽ được trả về.
Tính tổng các giá trị theo nhóm - tổng phụ ở một vị trí khác
Nếu các giá trị của mỗi nhóm không được sắp xếp cùng nhau và được liệt kê ngẫu nhiên trong cột, để tính tổng các giá trị phù hợp dựa trên nhóm hoặc danh mục, bạn nên trích xuất từng tên nhóm từ tên sản phẩm, sau đó lấy tổng phụ dựa trên tên nhóm này như ảnh chụp màn hình bên dưới được hiển thị.
1. Trước tiên, vui lòng trích xuất tên nhóm duy nhất bằng cách sử dụng công thức mảng sau, sau đó nhấn Ctrl + Shift + Enter các phím với nhau để có kết quả đầu tiên.
- Chú thích: Trong công thức:
- A2: A13 là phạm vi ô nơi trích xuất tất cả các giá trị duy nhất;
- D1 là ô phía trên công thức bạn đã nhập.
2. Và sau đó, chọn ô công thức và kéo chốt điền xuống các ô cho đến khi tất cả các tên sản phẩm được hiển thị, xem ảnh chụp màn hình:
3. Bây giờ, bạn có thể tính tổng các giá trị theo tên nhóm mà bạn đã trích xuất. Trong trường hợp này, một hàm SUMIF sẽ được sử dụng ở đây, vui lòng nhập công thức dưới đây vào một ô trống, trong ví dụ này, tôi sẽ đặt nó vào ô E2.
4. Sau đó, kéo chốt điền xuống để sao chép công thức này để trả về tổng thứ tự của các nhóm khác, xem ảnh chụp màn hình:
Hàm tương đối được sử dụng:
- TÓM TẮT:
- Hàm SUMIF có thể giúp tính tổng các ô dựa trên một tiêu chí.
- IF:
- Hàm IF kiểm tra một điều kiện cụ thể và trả về giá trị tương ứng mà bạn cung cấp cho TRUE hoặc FALSE.
Các bài viết khác:
- Tổng số tiền trong hóa đơn theo độ tuổi
- Để tính tổng số tiền hóa đơn dựa trên độ tuổi như hình minh họa bên dưới có thể là một nhiệm vụ phổ biến trong Excel, hướng dẫn này sẽ chỉ ra cách tính tổng số tiền hóa đơn theo độ tuổi bằng hàm SUMIF thông thường.
- Tổng các giá trị N nhỏ nhất hoặc dưới cùng
- Trong Excel, chúng ta dễ dàng tính tổng một dải ô bằng cách sử dụng hàm SUM. Đôi khi, bạn có thể cần tính tổng 3, 5 hoặc n số nhỏ nhất hoặc dưới cùng trong một dải dữ liệu như hình minh họa bên dưới. Trong trường hợp này, hàm SUMPRODUCT cùng với hàm SMALL có thể giúp bạn giải quyết vấn đề này trong Excel.
- Tổng các giá trị nhỏ nhất hoặc dưới cùng của N dựa trên tiêu chí
- Trong hướng dẫn trước, chúng ta đã thảo luận về cách tính tổng n giá trị nhỏ nhất trong một phạm vi dữ liệu. Trong bài viết này, chúng tôi sẽ thực hiện một thao tác nâng cao hơn - để tính tổng n giá trị thấp nhất dựa trên một hoặc nhiều tiêu chí trong Excel.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
