Cách tính tổng cột và hàng trong Excel cơ bản – nâng cao

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à 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 bảng 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 hay hàng trong Excel bao gồm, sử dụ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, hàm SUMIF và 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ính tổng một cột bằng một cú nhấp chuột sử dụng thanh trạng thái Status Bar

Cách dễ 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 Status Bar ở dưới cùng. Excel có thanh Status Bar ở 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ố 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 Status Bar 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 Status Bar để 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 AutoSum. AutoSum là một tính năng của Microsoft Excel cho phép bạn nhanh chóng thêm một dải ô (cột hoặc hàng) có chứa số, số nguyên hay 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 Home Formula của ruy-băng Excel sẽ chèn hàm SUM vào ô đã 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) mà bạn cần tính tổng.

Sau đó, chọn tab Formula và nhấp vào nút AutoSum trong nhóm thư viện chức năng. 

Hoặc, chuyển đến tab Home và nhấp vào nút AutoSum 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ể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 nó 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 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+= (Press and hold the Alt key and press the equal sign = key

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ính tổng một cột hoặc hàng chỉ bằng một cú nhấp chuột hoặc nhấn phím tắt. Tuy nhiên, có một giới hạn 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à bởi 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.

Tính tổng 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 và dễ sử dụng như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 hay ô 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],...)
  • number1 (bắt buộc) là giá trị số đầu tiên được thêm vào.
  • number 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ổng hợp 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 số cột hoặc hàng ở bất kỳ vị trí nào 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(vào ô.

Sau đó, chọn phạm vi ô 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 và 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, mặc dù 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à bấm vào các ô bạn muốn thêm hoặc nhập các tham chiếu ô theo cách thủ công và phân tách chúng bằng dấu phẩy (,) 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, và 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(vào ô đó. 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 toán 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 phạm vi đã đặt tên trong hàm thay vì cuộn xuống hàng trăm hàng để chọn phạm vi.

Một điều tốt khác khi 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 một 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 Formulas và nhấp vào nút Define Name.

Trong hộp thoại New Name, chỉ định tên bạn muốn đặt cho dải ô đã chọn trong trường Name. Trong trường Scope, 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ộ Workbook 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 Refers to.

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 Name. Để thực hiện việc này, hãy chọn phạm vi, chuyển đến hộp Name ở 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 Name, 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(Prices)

Trong ví dụ trên, công thức trong Sheet 4 tham chiếu đến cột có tên Price trong Sheet 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 một 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 hay ẩ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 hay ẩ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], ...)

Trong đó:

  • function_num (bắt buộc)  Đây 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(bắt buộc)  Dải ô hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ.
  • ref2(tùy chọn) – 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 Data và nhấp vào biểu tượng Filter (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, 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. Cuối cùng, 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ố bạn muốn cộng và nhấp vào AutoSum trong tab Home hoặc Formulas.

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 một 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 ở format 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 Insert 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 Create Table, 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 My table has headers.

Đ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 Design chỉ xuất hiện khi bạn chọn một ô trong bảng và chọn hộp có nội dung Total Row trong nhóm Table Style Options.

Khi bạn đã chọn tùy chọn Total Row, 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 danh sách thả xuống bên cạnh ô đó mà 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 Average, Count, Min, Max 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 tháng, 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(range, criteria, [sum_range])

Đối số / tham số:

  • range  Phạm vi ô mà chúng tôi tìm kiếm các ô đáp ứng tiêu chí.
  • criteria – 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 (tùy chọn) – Là phạm vi dữ liệu có 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 đó, phạm vi 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 doanh số bán hàng từ khu vực ‘South’.

Bạn có thể dễ dàng làm điều đó với công thức sau:

=SUMIF(B2:B19,"South",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ị ‘South’ 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)

Xem thêm về:

Chia sẻ suy nghĩ, quan điểm của bạn

Leave a reply

Nếu bạn cần Báo giá đăng ký lên BigTOP thì hãy tìm trên Google

bằng từ khóa “Đăng bài trên BigTOP

Hotline : 0935923672

BigTOP
Logo