Phạm vi tổng. Tổng toàn bộ cột. Tính tổng các ô không liền kề. Tự động tính tổng. Tổng có điều kiện. Tính tổng mỗi hàng thứ N. Tổng các số lớn nhất. Phạm vi tổng có lỗi
Sử dụng hàm SUM trong Excel để tính tổng một dãy ô, cả một cột hoặc các ô không liền kề nhau. Để tạo công thức SUM tuyệt vời, hãy kết hợp hàm SUM với các hàm Excel khác
Phạm vi tổng
Hầu hết thời gian, bạn sẽ sử dụng hàm SUM trong Excel để tính tổng một dải ô
How to Sum Across Multiple Sheets in Excel?
Chỉ tính tổng các ô hiển thị trong một cột
Trường hợp bạn có bộ dữ liệu mà đã lọc ô hoặc ẩn ô thì không dùng được hàm SUM
Dưới đây là một ví dụ về những gì có thể đi sai
Trong ví dụ trên, khi tôi tính tổng các ô hiển thị, nó cho tôi kết quả là 2549, trong khi kết quả thực tế của tổng các ô hiển thị sẽ là 2190
Sở dĩ ta ra kết quả sai là do hàm SUM cũng lấy các ô đã lọc/ẩn khi tính tổng của một cột
Trong trường hợp bạn chỉ muốn lấy tổng của các ô hiển thị thì không thể sử dụng hàm SUM. Trong trường hợp này, bạn cần sử dụng hàm TỔNG HỢP hoặc SUBTOTAL
Nếu bạn đang sử dụng phiên bản Excel 2010 trở lên, bạn có thể sử dụng hàm TỔNG. Nó làm mọi thứ mà hàm SUBTOTAL làm, và hơn thế nữa. Trong trường hợp bạn đang sử dụng các phiên bản cũ hơn, thì bạn có thể sử dụng hàm SUBTOTAL để chỉ lấy TỔNG của các ô hiển thị [i. e. , nó bỏ qua các ô đã lọc/ẩn]
Dưới đây là công thức bạn có thể sử dụng để lấy tổng của chỉ những ô hiển thị trong một cột
=AGGREGATE[9,3,B2:B6]
Hàm Aggregate nhận các đối số sau
- hàm_num. Đây là một con số cho biết hàm TỔNG HỢP phép tính cần được thực hiện. Trong ví dụ này, tôi đã sử dụng 9 vì tôi muốn tổng
- tùy chọn. Trong đối số này, bạn có thể chỉ định những gì bạn muốn bỏ qua khi thực hiện phép tính. Trong ví dụ này, tôi đã sử dụng 3, trong đó 'Bỏ qua hàng ẩn, giá trị lỗi, hàm SUBTOTAL lồng nhau và TỔNG HỢP'. Nói tóm lại, nó chỉ sử dụng các ô hiển thị để tính toán
- mảng. Đây là phạm vi ô mà bạn muốn nhận giá trị. Trong ví dụ của chúng tôi, đây là B2. B6 [cũng có một số hàng bị ẩn/đã lọc]
Trong trường hợp bạn đang sử dụng Excel 2007 hoặc các phiên bản trước, bạn có thể sử dụng công thức Excel sau
=SUBTOTAL[9,B2:B6]
Dưới đây là công thức mà tôi trình bày cách tính tổng một cột khi có các ô được lọc [sử dụng hàm SUBTOTAL]
Chuyển đổi dữ liệu dạng bảng sang bảng Excel để lấy tổng của cột
Khi bạn chuyển đổi dữ liệu dạng bảng thành Bảng Excel, việc lấy tổng của các cột trở nên thực sự dễ dàng
Tôi luôn khuyên bạn nên chuyển đổi dữ liệu thành bảng Excel, vì nó mang lại rất nhiều lợi ích. Và với các công cụ mới như Power Query, Power Pivot và Power BI hoạt động rất tốt với các bảng, thì đó là một lý do nữa để sử dụng nó
Để chuyển đổi dữ liệu của bạn thành bảng Excel, hãy làm theo các bước dưới đây
- Chọn dữ liệu mà bạn muốn chuyển đổi sang Bảng Excel
- Nhấp vào tab Chèn
- Bấm vào biểu tượng Bảng
- Trong hộp thoại Tạo bảng, đảm bảo phạm vi là chính xác. Ngoài ra, hãy kiểm tra tùy chọn 'Bảng của tôi có tiêu đề' trong trường hợp bạn có tiêu đề trong dữ liệu của mình
- Nhấp vào được
Các bước trên sẽ chuyển đổi dữ liệu dạng bảng của bạn thành Bảng Excel
Phím tắt để chuyển thành bảng là Control + T [giữ phím control và nhấn phím T]
Khi bạn đã có bảng, bạn có thể dễ dàng lấy tổng của tất cả các cột
Dưới đây là các bước để lấy tổng các cột trong Bảng Excel
- Chọn một ô bất kỳ trong bảng Excel
- Nhấp vào tab Thiết kế. Đây là tab ngữ cảnh chỉ xuất hiện khi bạn chọn một ô trong bảng Excel
- Trong nhóm 'Tùy chọn kiểu bảng', hãy chọn tùy chọn 'Tổng số hàng'
Các bước trên sẽ ngay lập tức thêm một hàng tổng ở cuối bảng và đưa ra tổng của tất cả các cột
Một điều khác cần biết về việc sử dụng bảng Excel là bạn có thể dễ dàng thay đổi giá trị từ SUM của cột thành Trung bình, Đếm, Tối thiểu/Tối đa, v.v.
Để thực hiện việc này, hãy chọn một ô trong các hàng tổng và sử dụng trình đơn thả xuống để chọn giá trị bạn muốn
Lấy tổng của cột dựa trên tiêu chí
Tất cả các phương pháp nêu trên sẽ cung cấp cho bạn tổng của toàn bộ cột
Trong trường hợp bạn chỉ muốn lấy tổng của những giá trị thỏa mãn một tiêu chí, bạn có thể dễ dàng thực hiện điều đó bằng công thức SUMIF hoặc SUMIFS
Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và bạn chỉ muốn lấy tổng của các giá trị lớn hơn 500
Bạn có thể dễ dàng làm điều này bằng cách sử dụng công thức dưới đây
=SUMIF[B2:B11,">500",B2:B11]
Với công thức SUMIF, bạn có thể sử dụng điều kiện số cũng như điều kiện văn bản
Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn lấy TỔNG của tất cả doanh số bán hàng do Mark thực hiện
Trong trường hợp này, bạn có thể sử dụng cột A làm phạm vi tiêu chí và "Đánh dấu" làm tiêu chí và công thức sẽ cung cấp cho bạn tổng tất cả các giá trị cho Đánh dấu
Công thức dưới đây sẽ cho bạn kết quả
=SUMIF[A2:A11,"Mark",B2:B10]
Ghi chú. Một cách khác để lấy tổng của một cột đáp ứng tiêu chí có thể là lọc cột dựa trên tiêu chí, sau đó sử dụng công thức TỔNG hoặc SUBTOTAL để chỉ lấy tổng của các ô hiển thị