Bạn có thể tính tổng một cột bằng một cú nhấp chuột, tính năng Tự động cộng gộp, hàm SUM, tính năng lọc và bằng cách chuyển đổi tập dữ liệu thành bảng.
Thêm các cột hoặc hàng số là điều mà hầu hết chúng ta bắt buộc phải làm khá thường xuyên. Ví dụ: nếu bạn lưu trữ dữ liệu quan trọng như hồ sơ bán hàng hoặc danh sách giá trong các ô của một cột, bạn có thể muốn nhanh chóng biết tổng số của cột đó. Vì vậy, cần phải biết cách tính tổng một cột trong Excel.
Có một số cách bạn có thể tính tổng hoặc tổng một cột / hàng trong Excel bao gồm, sử dụng một cú nhấp chuột, tính năng Tự động tính tổng, hàm SUM, tính năng bộ lọc, hàm SUMIF và bằng cách chuyển đổi tập dữ liệu thành bảng. Trong bài viết này, chúng ta sẽ thấy các phương pháp khác nhau để cộng một cột hoặc hàng trong Excel.
TÓM TẮT một cột bằng một cú nhấp chuột bằng cách sử dụng Thanh trạng thái
Cách dễ nhất và nhanh nhất để tính tổng giá trị của một cột là nhấp vào chữ cái của cột có các số và kiểm tra thanh ‘Trạng thái’ ở dưới cùng. Excel có thanh Trạng thái ở cuối cửa sổ Excel, thanh này hiển thị nhiều thông tin khác nhau về trang tính Excel bao gồm giá trị trung bình, số lượng và tổng của các ô đã chọn.
Giả sử bạn có một bảng dữ liệu như hình dưới đây và bạn muốn tìm tổng giá trong cột B.
Tất cả những gì bạn phải làm là chọn toàn bộ cột với các số bạn muốn tính tổng (Cột B) bằng cách nhấp vào chữ B ở đầu cột và nhìn vào thanh Trạng thái Excel (bên cạnh điều khiển thu phóng).
Ở đó, bạn sẽ thấy tổng các ô đã chọn cùng với giá trị trung bình và giá trị đếm.
Bạn cũng có thể chọn dải dữ liệu B2 đến B11 thay vì toàn bộ cột và xem thanh Trạng thái để biết tổng số. Bạn cũng có thể tìm tổng các số trong một hàng bằng cách chọn hàng giá trị thay vì một cột.
Lợi ích của việc sử dụng phương pháp này là nó tự động bỏ qua các ô có giá trị văn bản và chỉ tính tổng các số. Như bạn thấy ở trên, khi chúng tôi chọn toàn bộ cột B bao gồm ô B1 với tiêu đề văn bản (Giá), nó chỉ tổng hợp các số trong cột đó.
SUM một cột với chức năng AutoSum
Một cách nhanh nhất khác để tổng hợp một cột trong Excel là sử dụng tính năng Tự động cộng gộp. AutoSum là một tính năng của Microsoft Excel cho phép bạn nhanh chóng cộng một phạm vi ô (cột hoặc hàng) có chứa số / số nguyên / số thập phân bằng cách sử dụng hàm SUM.
Có một nút lệnh ‘AutoSum’ trên cả tab ‘Trang chủ’ và ‘Công thức’ của dải băng Excel sẽ chèn ‘hàm SUM’ trong ô đã chọn khi được nhấn.
Giả sử bạn có bảng dữ liệu như hình dưới đây và bạn muốn tính tổng các số trong cột B. Chọn một ô trống ngay bên dưới cột hoặc cuối bên phải của một hàng dữ liệu (để tính tổng một hàng) bạn cần tính tổng.
Sau đó, chọn tab ‘Công thức’ và nhấp vào nút ‘Tính tổng’ trong nhóm Thư viện chức năng.
Hoặc, chuyển đến tab ‘Trang chủ’ và nhấp vào nút ‘Tự động tính tổng’ trong nhóm Chỉnh sửa.
Dù bằng cách nào, khi bạn nhấp vào nút, Excel sẽ tự động chèn ‘= SUM ()’ vào ô đã chọn và đánh dấu phạm vi bằng các số của bạn (kiến diễu hành xung quanh phạm vi). Kiểm tra xem phạm vi đã chọn có chính xác hay không và nếu đó không phải là phạm vi chính xác, bạn có thể thay đổi phạm vi này bằng cách chọn một phạm vi khác. Và các thông số của chức năng sẽ tự động điều chỉnh theo đó.
Sau đó, chỉ cần nhấn Enter trên bàn phím của bạn để xem tổng của toàn bộ cột trong ô đã chọn.
Bạn cũng có thể gọi chức năng AutoSum bằng phím tắt.
Để làm điều đó, hãy chọn ô, ô nằm ngay bên dưới ô cuối cùng trong cột mà bạn muốn tổng số và sử dụng phím tắt bên dưới:
Alt + = (Nhấn và giữ phím Alt và nhấn phím dấu bằng =Và nó sẽ tự động chèn hàm SUM và chọn phạm vi cho nó. Sau đó nhấn Enter để tổng số cột.
Tính tổng tự động cho phép bạn nhanh chóng tổng hợp một cột hoặc hàng bằng một cú nhấp chuột hoặc nhấn phím tắt.
Tuy nhiên, có một hạn chế nhất định đối với chức năng AutoSum, nó sẽ không phát hiện và chọn phạm vi chính xác trong trường hợp có bất kỳ ô trống nào trong phạm vi hoặc bất kỳ ô nào có giá trị văn bản.
Như bạn có thể thấy trong ví dụ trên, ô B6 trống. Và khi chúng ta nhập hàm AutoSum vào ô B12, nó chỉ chọn 5 ô ở trên. Đó là vì hàm nhận thấy rằng ô B7 là phần cuối của dữ liệu và chỉ trả về tổng số 5 ô.
Để khắc phục điều này, bạn cần thay đổi phạm vi bằng cách nhấp và kéo bằng chuột hoặc nhập các tham chiếu ô chính xác theo cách thủ công để đánh dấu toàn bộ cột và nhấn Enter. Và, bạn sẽ nhận được kết quả phù hợp.
Để tránh điều này, bạn cũng có thể nhập hàm SUM theo cách thủ công để tính tổng.
SUM một cột bằng cách nhập hàm SUM theo cách thủ công
Mặc dù lệnh AutoSum nhanh chóng và dễ sử dụng, đôi khi, bạn có thể cần phải nhập hàm SUM theo cách thủ công để tính tổng của một cột hoặc hàng trong Excel. Đặc biệt, nếu bạn chỉ muốn thêm một số ô trong cột của mình hoặc nếu cột của bạn chứa bất kỳ ô trống hoặc ô nào có giá trị văn bản.
Ngoài ra, nếu bạn muốn hiển thị giá trị tổng của mình trong bất kỳ ô nào trong trang tính ngoài ô ngay bên dưới cột hoặc ô sau hàng số, bạn có thể sử dụng hàm SUM. Với hàm SUM, bạn có thể tính tổng hoặc tổng các ô ở bất kỳ vị trí nào trong trang tính.
Cú pháp của hàm SUM:
= SUM (number1, [number2], ...).số 1(bắt buộc) là giá trị số đầu tiên được thêm vào.số 2(tùy chọn) là giá trị số bổ sung thứ hai được thêm vào.
Trong khi số 1 là đối số bắt buộc, bạn có thể tính tổng tối đa 255 đối số bổ sung. Các đối số có thể là các số bạn muốn thêm vào hoặc các tham chiếu ô đến các số.
Một lợi ích khác của việc sử dụng hàm SUM theo cách thủ công là bạn cộng các số trong các ô không liền kề của một cột hoặc hàng cũng như nhiều cột hoặc hàng. Đây là cách bạn sử dụng hàm SUM theo cách thủ công:
Đầu tiên, hãy chọn ô mà bạn muốn xem tổng một cột hoặc hàng ở bất kỳ đâu trên trang tính. Tiếp theo, bắt đầu công thức của bạn bằng cách nhập = SUM ( trong phòng giam.
Sau đó, chọn phạm vi ô có các số bạn muốn tính tổng hoặc nhập tham chiếu ô cho phạm vi bạn muốn tính tổng trong công thức.
Bạn có thể nhấp và kéo bằng chuột hoặc giữ phím shift, sau đó sử dụng các phím mũi tên để chọn một phạm vi ô. Nếu bạn muốn nhập tham chiếu ô theo cách thủ công, thì hãy nhập tham chiếu ô của ô đầu tiên của phạm vi, theo sau là dấu hai chấm, tiếp theo là tham chiếu ô của ô cuối cùng của phạm vi.
Sau khi bạn nhập các đối số, hãy đóng dấu ngoặc và nhấn phím Enter để nhận kết quả.
Như bạn có thể thấy, ngay cả khi cột có một ô trống và một giá trị văn bản, hàm cung cấp cho bạn tổng của tất cả các ô đã chọn.
Tổng hợp các ô không liên tục trong một cột
Thay vì tính tổng một dải ô liên tục, bạn cũng có thể tổng hợp các ô không liên tục trong một cột. Để chọn các ô không liền kề, hãy giữ phím Ctrl và nhấp vào các ô bạn muốn thêm hoặc nhập tham chiếu ô theo cách thủ công và phân tách chúng bằng dấu comms (,) trong công thức.
Thao tác này sẽ chỉ hiển thị tổng của các ô đã chọn trong cột.
Tổng hợp nhiều cột
Nếu bạn muốn tổng của nhiều cột, hãy chọn nhiều cột bằng chuột hoặc nhập tham chiếu ô của ô đầu tiên trong phạm vi, tiếp theo là dấu hai chấm, tiếp theo là tham chiếu ô cuối cùng của phạm vi cho các đối số của hàm.
Sau khi bạn nhập các đối số, hãy đóng dấu ngoặc và nhấn phím Enter để xem kết quả.
Tổng hợp các cột không liền kề
Bạn cũng có thể tính tổng các cột không liền kề bằng cách sử dụng hàm SUM. Đây là cách thực hiện:
Chọn bất kỳ ô nào trong trang tính mà bạn muốn hiển thị tổng số các cột không liền kề. Sau đó, bắt đầu công thức bằng cách nhập hàm = SUM ( trong ô đó. Tiếp theo, chọn phạm vi cột đầu tiên bằng chuột hoặc nhập tham chiếu phạm vi theo cách thủ công.
Sau đó, thêm dấu phẩy và chọn phạm vi tiếp theo hoặc nhập tham chiếu phạm vi thứ hai. Bạn có thể thêm bao nhiêu phạm vi tùy thích theo cách này và phân tách từng phạm vi bằng dấu phẩy (,).
Sau các đối số, đóng dấu ngoặc và nhấn Enter để nhận kết quả.
Tổng hợp cột sử dụng phạm vi được đặt tên
Nếu bạn có một trang tính lớn dữ liệu và bạn muốn nhanh chóng tính tổng các số trong một cột, bạn có thể sử dụng các phạm vi đã đặt tên trong hàm SUM để tìm tổng số. Khi bạn tạo Dải ô được Đặt tên, bạn có thể sử dụng những tên này thay vì tham chiếu ô, điều này giúp dễ dàng tham chiếu đến các tập dữ liệu trong Excel. Dễ dàng sử dụng dải ô đã đặt tên trong hàm thay vì cuộn xuống hàng trăm hàng để chọn dải ô.
Một điều tốt khác về việc sử dụng Dải ô được đặt tên là bạn có thể tham chiếu đến tập dữ liệu (dải ô) trong trang tính khác trong đối số SUM và nhận giá trị tổng trong trang tính hiện tại.
Để sử dụng một phạm vi đã đặt tên trong công thức, trước tiên, bạn cần tạo một phạm vi. Đây là cách bạn tạo và sử dụng một dải ô đã đặt tên trong hàm SUM.
Đầu tiên, chọn phạm vi ô (không có tiêu đề) mà bạn muốn tạo Dải ô được đặt tên. Sau đó, chuyển đến tab 'Công thức' và nhấp vào nút 'Xác định Tên' trong nhóm Tên được Xác định.
Trong hộp thoại Tên mới, chỉ định tên bạn muốn đặt cho dải ô đã chọn trong trường "Tên:". Trong trường ‘Phạm vi:’, bạn có thể thay đổi phạm vi của phạm vi được đặt tên dưới dạng toàn bộ Sổ làm việc hoặc một trang tính cụ thể. Phạm vi chỉ định xem phạm vi được đặt tên sẽ có sẵn cho toàn bộ sổ làm việc hay chỉ một trang tính cụ thể. Sau đó, nhấp vào nút ‘OK’.
Bạn cũng có thể thay đổi tham chiếu của phạm vi trong trường 'Tham chiếu đến'.
Ngoài ra, bạn cũng có thể đặt tên cho một dải ô bằng cách sử dụng hộp "Tên". Để thực hiện việc này, hãy chọn phạm vi, chuyển đến hộp 'Tên' ở bên trái của thanh Công thức (ngay phía trên chữ A) và nhập tên bạn muốn gán cho phạm vi ngày đã chọn. Sau đó, nhấn Enter.
Nhưng khi bạn tạo một phạm vi được đặt tên bằng cách sử dụng hộp Tên, nó sẽ tự động đặt phạm vi của phạm vi được đặt tên cho toàn bộ sổ làm việc.
Bây giờ, bạn có thể sử dụng phạm vi đã đặt tên mà bạn đã tạo để nhanh chóng tìm giá trị tổng.
Để thực hiện việc này, hãy chọn bất kỳ ô trống nào ở bất kỳ đâu trên sổ làm việc mà bạn muốn hiển thị kết quả Tổng. Và nhập công thức SUM với phạm vi được đặt tên giống như các đối số của nó và nhấn Enter:
= SUM (Giá)
Trong ví dụ trên, công thức trong Trang tính 4 tham chiếu đến cột có tên là ‘Giá’ trong Trang tính 2 để lấy tổng của một cột.
Chỉ tính tổng các ô có thể nhìn thấy trong một cột bằng cách sử dụng hàm SUBTOTAL
Nếu bạn đã lọc các ô hoặc ô ẩn trong tập dữ liệu hoặc cột, thì việc sử dụng hàm SUM để tính tổng một cột không phải là lý tưởng. Bởi vì hàm SUM bao gồm các ô được lọc hoặc ẩn trong tính toán của nó.
Ví dụ dưới đây cho thấy điều gì sẽ xảy ra khi bạn tính tổng một cột với các hàng bị ẩn hoặc được lọc:
Trong bảng trên, chúng tôi đã lọc cột B theo giá nhỏ hơn 100. Kết quả là chúng tôi có một số hàng được lọc. Bạn có thể nhận thấy rằng có các hàng được lọc / ẩn trong bảng bởi số hàng bị thiếu.
Bây giờ, khi bạn tính tổng các ô hiển thị trong cột B bằng hàm SUM, bạn sẽ nhận được ‘207’ làm giá trị tổng nhưng thay vào đó, nó hiển thị ‘964’. Bởi vì hàm SUM cũng tính đến các ô đã lọc khi tính tổng.
Đây là lý do tại sao bạn không thể sử dụng hàm SUM khi có liên quan đến các ô được lọc hoặc ẩn.
Nếu bạn không muốn các ô đã lọc / ẩn được đưa vào phép tính khi tính tổng một cột và bạn chỉ muốn tính tổng các ô hiển thị, thì bạn cần sử dụng hàm SUBTOTAL.
Hàm SUBTOTAL
SUBTOTAL là một hàm tích hợp mạnh mẽ trong Excel cho phép bạn thực hiện các phép tính khác nhau (SUM, AVERAGE, COUNT, MIN, VARIANCE và các hàm khác) trên một phạm vi dữ liệu và trả về tổng số hoặc kết quả tổng hợp của cột. Hàm này chỉ tóm tắt dữ liệu trong các ô hiển thị trong khi bỏ qua các hàng đã lọc hoặc ẩn. SUBTOTAL là một hàm đa năng có thể thực hiện 11 chức năng khác nhau trong các ô hiển thị của một cột.
Cú pháp của hàm SUBTOTAL:
= SUBTOTAL (function_num, ref1, [ref2], ...)Tranh luận:
function_num(yêu cầu)– Nó là một số hàm chỉ định hàm nào được sử dụng để tính tổng. Đối số này có thể nhận bất kỳ giá trị nào từ 1 đến 11 hoặc 101 đến 111. Ở đây, chúng ta cần tính tổng các ô hiển thị trong khi bỏ qua các ô đã lọc. Để làm được điều đó, chúng ta cần sử dụng "9".ref1(yêu cầu)– Phạm vi hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ.ref2(không bắt buộc) - Dải ô hoặc tham chiếu được đặt tên thứ hai mà bạn muốn tính tổng phụ. Sau tham chiếu đầu tiên, bạn có thể thêm tối đa 254 tham chiếu bổ sung.
Tổng hợp một cột bằng cách sử dụng hàm SUBTOTAL
Nếu bạn muốn tính tổng các ô hiển thị và loại trừ các ô đã lọc hoặc bị ẩn, hãy làm theo các bước sau để sử dụng hàm SUBTOTAL để tính tổng một cột:
Đầu tiên, bạn cần lọc bảng của mình. Để làm điều đó, hãy nhấp vào bất kỳ ô nào trong tập dữ liệu của bạn. Sau đó, điều hướng đến tab "Dữ liệu" và nhấp vào biểu tượng "Bộ lọc" (biểu tượng hình phễu).
Các mũi tên sẽ xuất hiện bên cạnh tiêu đề cột. Bấm vào mũi tên bên cạnh tiêu đề cột mà bạn muốn lọc bảng. Sau đó, chọn tùy chọn bộ lọc bạn muốn áp dụng cho dữ liệu của mình. Trong ví dụ dưới đây, chúng tôi muốn lọc cột B có các số nhỏ hơn 100.
Trong hộp thoại Bộ lọc tự động tùy chỉnh, chúng tôi đang nhập "100" và nhấp vào "OK".
Các số trong cột được lọc theo các giá trị nhỏ hơn 100.
Bây giờ, hãy chọn ô mà bạn muốn hiển thị giá trị tổng và bắt đầu nhập hàm SUBTOTAL. Khi bạn mở hàm SUBTOTAL và nhập dấu ngoặc, bạn sẽ thấy danh sách các hàm bạn có thể sử dụng trong công thức. Nhấp vào ‘9 - SUM’ trong danh sách hoặc nhập ‘9’ theo cách thủ công làm đối số đầu tiên.
Sau đó, chọn phạm vi ô bạn muốn tính tổng hoặc nhập tham chiếu phạm vi theo cách thủ công và đóng dấu ngoặc. Sau đó, nhấn Enter.
Bây giờ, bạn sẽ nhận được tổng (tổng phụ) của chỉ các ô hiển thị - ‘207’
Ngoài ra, bạn cũng có thể chọn phạm vi (B2: B11) với các số mà bạn muốn cộng lại và nhấp vào ‘Tự động tính tổng’ trong tab ‘Trang chủ’ hoặc ‘Công thức’.
Nó sẽ tự động thêm hàm SUBTOTAL vào cuối bảng và tính tổng kết quả.
Chuyển đổi dữ liệu của bạn thành bảng Excel để lấy tổng của cột
Một cách dễ dàng khác mà bạn có thể sử dụng để tính tổng cột của mình là chuyển đổi dữ liệu bảng tính của bạn thành một bảng Excel. Bằng cách chuyển đổi dữ liệu của bạn thành một bảng, bạn không chỉ có thể tính tổng cột của mình mà còn có thể thực hiện nhiều chức năng hoặc hoạt động khác với danh sách của mình.
Nếu dữ liệu của bạn chưa ở định dạng bảng, bạn cần chuyển đổi nó thành bảng Excel. Đây là cách bạn chuyển đổi dữ liệu của mình thành bảng Excel:
Đầu tiên, hãy chọn bất kỳ ô nào trong tập dữ liệu mà bạn muốn chuyển đổi thành Bảng Excel. Sau đó, chuyển đến tab "Chèn" và nhấp vào biểu tượng "Bảng"
Hoặc, bạn có thể nhấn phím tắt Ctrl + T để chuyển đổi phạm vi ô thành Bảng Excel.
Trong hộp thoại Tạo Bảng, xác nhận phạm vi và nhấp vào ‘OK’. Nếu bảng của bạn có tiêu đề, hãy chọn tùy chọn "Bảng của tôi có tiêu đề".
Điều này sẽ chuyển đổi tập dữ liệu của bạn thành Bảng Excel.
Khi bảng đã sẵn sàng, hãy chọn bất kỳ ô nào trong bảng. Sau đó, điều hướng đến tab ‘Thiết kế’ chỉ xuất hiện khi bạn chọn một ô trong bảng và chọn hộp có nội dung ‘Tổng hàng’ trong nhóm ‘Tùy chọn kiểu bảng’.
Khi bạn đã chọn tùy chọn ‘Tổng số hàng’, một hàng mới sẽ ngay lập tức xuất hiện ở cuối bảng của bạn với các giá trị ở cuối mỗi cột (như được hiển thị bên dưới).
Và khi bạn nhấp vào một ô trong hàng mới đó, bạn sẽ thấy một menu thả xuống bên cạnh ô đó, từ đó bạn có thể áp dụng một hàm để tính tổng. Chọn ô ở hàng cuối cùng (hàng mới) của cột bạn muốn tính tổng, nhấp vào menu thả xuống bên cạnh và đảm bảo rằng hàm ‘SUM’ được chọn từ danh sách.
Bạn cũng có thể thay đổi hàm thành Trung bình, Đếm, Tối thiểu, Tối đa và các hàm khác để xem các giá trị tương ứng của chúng trong hàng mới.
Tính tổng một cột dựa trên tiêu chí
Tất cả các phương pháp trước đây đã chỉ cho bạn cách tính tổng của toàn bộ cột. Nhưng điều gì sẽ xảy ra nếu bạn chỉ muốn tính tổng các ô cụ thể đáp ứng tiêu chí thay vì tất cả các ô. Sau đó, bạn phải sử dụng hàm SUMIF thay vì hàm SUM.
Hàm SUMIF tìm kiếm một điều kiện cụ thể trong một phạm vi ô (cột) và sau đó tính tổng các giá trị đáp ứng điều kiện đã cho (hoặc các giá trị tương ứng với các ô đáp ứng điều kiện). Bạn có thể tính tổng các giá trị dựa trên điều kiện số, điều kiện văn bản, điều kiện ngày, ký tự đại diện cũng như dựa trên các ô trống và không trống.
Cú pháp của hàm SUMIF:
= SUMIF (phạm vi, tiêu chí, [sum_range])Đối số / Tham số:
phạm vi– Phạm vi ô mà chúng tôi tìm kiếm các ô đáp ứng tiêu chí.tiêu chuẩn- Các tiêu chí xác định ô nào cần được tính tổng. Tiêu chí có thể là một số, chuỗi văn bản, ngày tháng, tham chiếu ô, biểu thức, toán tử logic, ký tự đại diện cũng như các hàm khác.sum_range(không bắt buộc) - Là phạm vi dữ liệu với các giá trị để tính tổng nếu mục nhập phạm vi tương ứng phù hợp với điều kiện. Nếu đối số này không được chỉ định, thì thay vào đó, 'dải ô' sẽ được tính tổng.
Giả sử bạn có tập dữ liệu dưới đây chứa dữ liệu bán hàng của từng Đại diện từ các khu vực khác nhau và bạn chỉ muốn tính tổng số tiền bán hàng từ khu vực 'Nam'.
Bạn có thể dễ dàng làm điều đó với công thức sau:
= SUMIF (B2: B19, "Nam", C2: C19)Chọn ô mà bạn muốn hiển thị kết quả và nhập công thức này. Công thức SUMIF ở trên tìm kiếm giá trị ‘Nam’ trong cột B2: B19 và cộng Số tiền bán hàng tương ứng trong cột C2: C19. Sau đó hiển thị kết quả trong ô E7.
Bạn cũng có thể tham chiếu đến ô chứa điều kiện văn bản thay vì sử dụng trực tiếp văn bản trong đối số tiêu chí:
= SUMIF (B2: B19, E6, C2: C19)
Đó là nó.