Hàm SUM trong Excel được dùng khá phổ biến. Dưới đây là chi tiết cách tính hàm SUM và những vấn đề liên quan trong Microsoft Excel.

Bạn đang xem: Sử dụng hàm sum trong excel

Nếu công việc của bạn làm văn phòng và thường xuyên phải xử lý hàng loạt dữ liệu trong bảng tính Excel, ắt hẳn bạn đã biết phần mềm này có rất nhiều hàm hữu ích. Từ những hàm đơn giản như cộng, trừ, nhân chia tới phức tạp như ngày tháng, tính số nhỏ nhất, lớn nhất,… Ở bài viết này, chúng ta sẽ cùng nhau tìm hiểu hàm tính tổng SUM trong Excel.

Đây là những nội dung chính trong bài viết này:

Hàm SUM trong Excel là gì?

Nói đơn giản, hàm SUM bổ sung thêm các giá trị. Nó được thiết kế dể trả về hàm SUM của các giá trị người dùng cung cấp, từ những giá trị riêng lẻ, phạm vi, tham chiếu ô hay kết hợp cả ba. Hàm SUM trong Excel đều có thể xử lý tốt, lên tới 255 tham chiếu riêng lẻ.

Công thức của hàm SUM trong Excel

Nắm được công thức của hàm SUM trong Excel có thể giúp bạn hiểu rõ về cách thức hoạt động của hàm này. Công thức hàm tính tổng trong Excel như sau:

Hàm SUM có hai cấu trúc do đó có thể được viết theo hai cách:

=SUM(num1,num2, ... num_n). Ở đây " num1,num2, ... num_n" biểu thị cho những số chúng ta sẽ tính.=SUM(Cell
Range1,Cell
Range2,...Cell
Range_n).
Trong đó: "Cell
Range1,Cell
Range2,... Cell
Range_n"
là những cột số.

Những chú ý trong phép tính SUM:


Hàm SUM có thể tính số thập phân và phân số.Trong phép tính SUM: =SUM(num1,num2, ... num_n), nếu bạn nhập tham số "num" không phải là số thì hàm SUM sẽ hiện thị lỗi #Name? Error.Và tương tự như vậy, ở phép tính =SUM(Cell
Range1,Cell
Range2, ...Cell
Range_n)
chứa nội dung không phải số thì hàm SUM sẽ bỏ qua giá trị đó.Hàm SUM không phải là dạng hàm động lực học. Vì vậy, nếu bạn sử dụng phép tính SUM vào dãy các ô rồi lọc ra một vài giá trị nào đó thì đầu ra của phép tính SUM sẽ không thay đổi theo gía trị gần nhất. Do đó tốt hơn hết, bạn nên dùng hàm Subtotal cho trường hợp này.

Những ví dụ về hàm SUM

Ở bảng trên, có bốn loại của hàm SUM và dưới đây tôi sẽ giải thích từng ví dụ cụ thể:

1. Ví dụ 1, tổng các số nguyên: =SUM(10,11,19) nghĩa là cộng các số 10,11,19 và 40 là kết quả 40.

2. Ví dụ 2, tổng các số thập phân: =SUM(10.2,9.6,2,4), đơn giản là phép tính cộng của 10,2+9.6+2.4 và cho kết quả 25.8.

3. Ví dụ 3, tổng các số phân số: =SUM(4/2,8/2). Phép tính cộng các số 4/2 và 8/2 và kết quả là 6.

4. Ví dụ 4, hàm SUM bị lỗi do có chứa nội dung không phải số và đó cũng là lý do phép tính cho ra #Name? Error.

5. Sử dụng dạng thứ hai của hàm SUM <=SUM(B2:B10)> để tính tổng cho các số trong dãy số (B2:B10) thay cho các tham số và cho ra kết quả 60.

Rút gọn của phép tính SUM:

Thay vì viết phép tính theo cách đơn thuần, bạn có thể sử dụng cách sau:


Đầu tiên, lựa chọn dãy số rồi ấn "Alt +" và kết quả sẽ hiển thị ở ô tiếp theo.

Sử dụng các phép toán khác trong phép tính SUM

Các phép tính khác như (+, - , / và *) có thể được dùng trong hàm SUM. Ví dụ như =SUM(2*4) và hàm SUM sẽ cho ra là 8. Tương tự như vậy với các phép tính còn lại.

Trên thực tế, hàm SUM sẽ xử lý tất cả các tập hợp số bao gồm cả dãy số hoặc phép tính. Vì vậy, điều đầu tiên nó sẽ đánh giá kết quả của phép toán vá nếu không có gì khác biệt thì sẽ cho ra kết quả.

Sử dụng Hàm SUM cùng với những hàm khác

1. Hoàn toàn có thể sử dụng hàm SUM cùng với các hàm khác. Dưới đây là một ví dụ tính trung bình hàng tháng:

=SUM(A2:L2)/COUNTA(A2:L2)

2. SUM của A2:L2 chia cho số tháng trong năm trong A2:L2 không tính ô bị bỏ trống.

Sử dụng hàm SUM trên nhiều trang tính

1. Thông thường, để tính tổng những ô có cùng vị trí trong các trang, bạn sẽ phải click vào ô đó ở từng trang và sử dụng dấu "+" giữa các giá trị nhưng như vậy sẽ rất mất thời gian và phép tính sẽ bị lỗi.

=Sheet1!A1+Sheet2!A1+Sheet3!A1

Do vậy, vấn đề này sẽ trở nên dễ dàng hơn khi sử dụng hàm SUM 3D:

=SUM(Sheet1:Sheet3!A1)

Hàm SUM đã tính tổng các ô A1 có trong tất cả các trang từ 1 đến 3.

Cách này rất hữu ích để tính từng trang đơn của những tháng (tháng 1 đến tháng 12).

=SUM(January:December!A2) đã cộng tất cả ô A2 ở từng trang từ tháng 1 đến tháng 12

Phương pháp 3D này cũng được sử dụng tương tự như vậy trong các hàm AVERAGE, MIN, MAX,...

=AVERAGE(Sheet1:Sheet3!A1)=MIN(Sheet1:Sheet3!A1)=MAX(Sheet1:Sheet3!A1)

Các vấn đề thường gặp và cách giải quyết:

Trường hợp 1: Hàm SUM hiển thị #####.

Hãy kiểm tra độ rộng cột, vì ##### thông báo không đủ chỗ trống để hiện thị kết quả.


Trường hợp 2: Hàm SUM hiển thị chữ và không ra kết quả. Hoặc chỉ hiện số nhưng không phải số thập phân.

Kiểm tra lại định dạng của cột. Hãy vào Format và chọn Number để lựa chọn dạng bạn muốn. Nếu sau khi định dạng, ô hiển thị chữ không thay đổi, bạn nên ấn F2enter.

Trường hợp 3: Hàm SUM không câp nhật.

Bạn phải chắc chắn rằng Calculation được đặt ở chế độ Automatic. Đầu tiên vào Formula, chọn tùy chỉnh Calculation. Sau đó ấn F9 để trang tự động cập nhật.

Trường hợp 4: Một vài giá trị không được sử dụng trong hàm SUM.

Chỉ có giá trị hoặc dãy có chứa số mới được đặt ở trong hàm. Những ô trống, hàm logic, hoặc chữ sẽ bị loại bỏ.

Trường hợp 5: Hàm cho ra kết quả #NAME? Error.

Đã có lỗi chính tả ở đây. Ví dụ, =sume(A1:A10) thay vì =SUM(A1:A10).

Những câu hỏi thường gặp khi dùng hàm SUM trong Excel

Làm thế nào hiện nhiều/ít chữ số thập phân hơn? Bạn có thể thay đổi định dạng số. Chọn ô hoặc phạm vi được đề cập và dùng Ctrl + 1 để mở hộp thoại Format Cells, sau đó click tab Number và chọn định dạng bạn muốn, đảm bảo chỉ ra số vị trí thập phân bạn muốn.

Làm thế nào thêm hoặc trừ thời gian? Bạn có thể thêm và trừ thời gian theo một số cách khác nhau. Ví dụ, để tính chênh lệch giữa 8 giờ sáng và 12 giờ trưa nhằm mục đích tính lương, bạn sẽ dùng =("12:00 PM"-"8:00 AM")*24, lấy thời gian kết thúc trừ đi thời gian bắt đầu. Lưu ý, Excel tính thời gian dưới dạng phân số của một ngày, vì thế, bạn cần nhân với 24 để tính tổng giờ làm việc. Ở ví dụ đầu tiên, dùng =((B2-A2)+(D2-C2))*24 để tính tổng số giờ từ lúc bắt đầu tới khi kết thúc, trừ đi thời gian nghỉ trưa (tổng 8,5 tiếng).

Làm thế nào để có được sự khác biệt giữa ngày tháng? Tương tự như thời gian, bạn có thể cộng và trừ ngày tháng. Đây là một ví dụ cực kỳ phổ biến về cách tính số ngày giữa hai ngày tháng khác nhau. Công thức dùng hàm SUM trong Excel ở đây rất đơn giản: =B2-A2… “Chìa khóa” để xử lý thông tin ở đây là lấy ngày/giờ kết thúc và trừ đi ngày/giờ bắt đầu.

Làm thế nào tính tổng những ô hiển thị? Thỉnh thoảng khi bạn tự tay ẩn hàng hoặc dùng Auto
Fillter để chỉ hiện dữ liệu nhất định, bạn cũng chỉ muốn tính các ô hiển thị. Bạn có thể dùng hàm SUBTOTAL. Nếu đang dùng một hàng tổng trong bảng Excel, bất kỳ hàm bạn chọn từ menu Total thả xuống sẽ tự động được nhập làm subtotal.

Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách tính tổng trong Excel bằng cách dùng tính năng Auto
Sum, và cách tạo công thức Sum để tính tổng các cột, hàng hoặc một dải ô đã được chọn. Ngoài ra, các bạn cũng sẽ học được cách tính tổng riêng những ô được hiển thị, tổng của các trang tính, đồng thời giúp bạn tìm hiểu thêm về những lý do khiến hàm Sum không hoạt động.

Nếu bạn muốn tính tổng của những ô nhất định một cách nhanh nhất, bạn chỉ cần chọn những ô đó rồi nhìn sang thanh trạng thái ở góc dưới cùng bên phải của cửa sổ Excel: Nếu muốn có được một công thức sử dụng lâu bền hơn thì bạn nên dùng hàm Sum. Hàm này rất đơn giản và dễ hiểu nên dù bạn là người mới bắt đầu học Excel thì cũng sẽ dễ dàng hiểu được những ví dụ dưới đây:


Xem nhanh


Cách dùng Auto
Sum trong Excel:Những lưu ý dành cho Excel Auto
Sum
Cách tính tổng một cột trong Excel
Cách tính tổng các ô được (có thể nhìn thấy được) trong Excel
Cách tính tổng cho nhiều trang tính trên cùng bảng tính
Excel SUM không hoạt động – lý do và giải pháp

Cách tính tổng trong Excel dùng phép tính số học đơn giản

Nếu bạn cần tính nhanh tổng của một số ô, bạn có thể sử dụng Microsoft Excel như một chiếc máy tính mini. Chỉ cần dùng dấu (+) giống như trong phép toán số học thông thường. Ví dụ:

=1+2+3

hoặc

=A1+C1+D1 Tuy nhiên, nếu bạn cần phải tính tổng vài chục, hay vài trăm hàng thì cộng từng ô không phải ý kiến hay. Trong trường hợp như vậy, bạn có thể sử dụng hàm Sum được thiết kế đặc biệt để tính tổng của một lượng đã được chỉ định sẵn.

Tìm hiểu ngay: Địa chỉ học Excel tại Hà Nội

Cách sử dụng hàm Sum trong Excel

Excel SUM là một hàm toán học dùng để tính tổng. Cú pháp của hàm Sum như sau:


*

Sum (số thứ nhất, ,…) Đối số đầu tiên là bắt buộc, các cố khác là tùy chọn, bạn có thể áp dụng hàm Sum cho tối đa 225 số trong một công thức.

Trong công thức Sum của Excel, mỗi đối số có thể là một giá trị số dương hoặc số âm, một hàng hoặc một chuỗi các ô. Ví dụ như:

=SUM(A1:A100)

=SUM(A1, A2, A5)

=SUM(1,5,-2)

Hàm SUM trong Excel thực sự là lựa chọn hữu ích khi bạn cần cộng các giá trị nằm ở cách dải khác nhau hoặc tính tổng các giá trị số, các ô tham chiếu hay các dãy. Ví dụ: =SUM(A2:A4, A8:A9)

=SUM(A2:A6, A9, 10)

Tham khảo hình bên dưới: Trong trang tính thực tế, hàm SUM thường được dùng với những công thức lơn hơn và được coi như một phần của những phép tính phức tạp hơn.

Ví dụ, bạn có thể kết hợp hàm SUM với đối số value_if_true thuộc hàm IF để cộng các giá trị trong cột B, C và D nếu tất cả các ô trong cùng hàng có cả ô chứa giá trị và ô trống:

=IF(AND($B2””, $D2””), SUM($B2:$D2), “Value missing”)

Cách dùng Auto
Sum trong Excel:

Nếu bạn cần tính tổng một dãy các số hoặc tổng của vài hàng, cột liền kề thì có thể sử dụng công thức SUM được Microsoft Excel thiết kế sẵn.

Bạn chỉ cần chọn một ô bên cạnh các con số cần tính tổng rồi nhấn Auto
Sum trên tab Trang Chủ, phần Editing rồi chọn Enter là có thể tự động chèn công thức SUM vào đó: Như các bạn có thể thấy trong ảnh chụp màn hình, tính năng Auto
Sum của Excel không chỉ nhập một công thức Sum, mà còn chọn phạm vi của các ô bạn muốn. Excel thường thực hiện việc chọn phạm vi tính một cách chuẩn xác, tuy nhiên, nếu xảy ra lỗi, bạn có thể tự sử phạm vi tính bằng cách kéo con trỏ qua các ô cần tính tổng rồi nhấn Enter là được.

Mẹo: Cách nhanh hơn để sử dụng Auto
Sum trong Excel là dùng phím tắt Alt + =. Bạn chỉ cần giữ phím Alt, nhấn phím Equal Sign, rồi Enter là hoàn thành việc chèn công thức Sum.

Ngoài tính tổng, bạn có thể sử dụng Auto
Sum để tự động nhập các hàm AVERAGE, COUNT, MAX, hay MIN.

Những lưu ý dành cho Excel Auto
Sum

Khi sử dụng Auto
Sum trong Excel để tự động cộng tổng các ô, chắc chắn các bạn sẽ muốn tìm hiểu một số thủ thuật để có thể làm việc với nó một cách hiệu quả hơn.

Cách sử dụng Auto
Sum với hơn một ô trong cùng một thời điểm

Nếu bạn muốn tính tổng các giá trị trong một vài hàng và cột thì chọn toàn bộ những ô mà bạn muốn tính tổng rồi nhấn nút Auto
Sum trên thanh thực đơn hoặc nhấn phím tắt Sum của Excel.

Ví dụ, bạn có thể chọn ô A10, B10, C10 rồi nhấp vào Auto
Sum, như vậy có 3 cột được tính tổng cùng lúc và giá trị trong mỗi cột đó sẽ được tính riêng lẻ:
*
Tinh-tong-ba-cot-dung-autosum Cách tính tổng các ô đã chọn theo hàng dọc và hàng ngang

Để tính tổng cho riêng những ô được chọn nhất định, bạn chọn các ô đó rồi nhấn Auto
Sum. Như vậy bạn sẽ tính tổng được các ô đã chọn theo chiều dọc:

*
Tinh-tong-cac-o-theo-chieu-doc

Nếu bạn muốn tính tổng các ô theo hàng, bạn chọn các ô muốn tính tổng cùng với một ô trống ở bên phải. Excel sẽ tính tổng các ô đã chọn theo chiều ngang và chèn công thức Sum vào ô trống ở trong vùng được chọn.

*
Tinh-tong-cac-o-theo-hang

Để tính tổng cách ô theo chiều dọc và chiều ngang, bạn chọn các ô cần tính tổng, cộng thêm một hàng trống bên dưới và một cột trống bên phải, Excel sẽ tính tổng các ô theo cả chiều dọc và ngang:

*
Tinh-tong-cac-o-theo-chieu-doc-va-ngang
Cách sao chép công thức Auto
Sum sang những ô khác

Khi Auto
Sum thêm một hàm SUM vào ô đã chọn thì nó sẽ hoạt động như một công thức Excel thông thường. Do đó, bạn có thể sao chép công thức đó sang các ô khác theo cách thông thường, ví dụ như có thể kéo từ ô này điền sang ô khác. Nhưng các bạn cần lưu ý rằng Auto
Sum của Excel sử dụng để tham chiếu các ô tương đối (không chứa $) để điều chỉnh vị trí của công thức mới dựa trên vị trí tương đối của hàng và cột. Ví dụ, bạn có thể dùng Auto
Sum để chèn công thức vào ô A10 giúp tính tổng các giá trị trong cột A: = SUM (A1: A9). Khi bạn sao chép công thức đó sang ô B10 nó sẽ chuyển thành = SUM (B1: B9) và tính tổng các số trong cột B. Trong hầu hết các trường hợp, đó là những gì bạn cần. Nhưng nếu bạn muốn sao chép công thức mà không làm thay đổi tham chiếu ô, bạn cần sửa các tham chiếu bằng cách thêm ký hiệu $.

Excel Auto
Sum không hoạt động

Lý do phổ biến nhất khiến Auto
Sum không hoạt động trong Excel là do các con số được đặt ở định dạng văn bản. Thoạt nhìn thì những giá trị này có thể giống các con số bình thường những Excel lại coi chúng là chuỗi văn bản và không thực hiện tính toán với chúng.

Các chỉ số rõ ràng nhất của các con số được định dạng dưới dạng văn bản là sự xắp xếp chúng sát lề bên trái theo mặc định và các hình tam giác màu xanh lá cây xuất hiện ở góc trên cùng bên trái của ô. Để sửa những số để dạng văn bản như vậy, bạn chọn tất cả các ô có vấn đề rồi nhấp vào dấu hiệu cảnh báo, chọn Convert to Number.

*
Excel-autosum-khong-hoat-dong

Số có thể được định dạng dưới dạng văn bản vì nhiều lý do, chẳng hạn như bạn sử dụng dữ liệu từ nguồn bên ngoài, hoặc trong dữ liệu nhập vào có chứa các giá trị số trong dấu ngoặc kép ở công thức tính của Excel. Nếu không thấy có tam giác màu xanh lá cây và dấu hiệu cảnh báo xuất hiện trong các ô, có thể là do Excel cho rằng bạn muốn xuất một chuỗi văn bản với mục đích cụ thể. Ví dụ, công thức tính IF sau đây:

=IF(A1=”OK”, “1”, “0”)

Nhìn thì thấy công thức hoạt động tốt nhưng vấn đề là 1’s và 0’s là các giá trị văn bản chứ không phải số, Excel Sum sẽ chuyển đầu ra thành số và vẫn cộng được giá trị đúng.

Cách tính tổng một cột trong Excel

Để tính tổng các số liệu trong một cột cụ thể, bạn có thể sử dụng hàm SUM thông thường hoặc tính năng Auto
Sum. Ví dụ, để tính tổng các giá trị trong cột B, từ B2 đến B8, bạn nhập công thức SUM như sau:

=SUM(B2:B8)

Cách tính tổng toàn bộ cột với số hàng vô hạn không xác định được

Nếu cột mà bạn muốn tính tổng có số lượng dòng thay đổi (nghĩa là các ô mới có thể được thêm vào và những giá trị cõ sẵn có thể bị xóa đi), bạn có thể tính tổng toàn bộ cột bằng cách thêm tham chiếu cột, không xác định giới hạn trên và dưới. Ví dụ:

=SUM(B:B)

Lưu ý quan trọng: Bạn không nên đặt công thức tính tổng của một cột ở trong cùng cột mà bạn cần tính tổng vì làm thế sẽ tạo ra một tham chiếu ô tròn (nghĩa là tổng kết đệ quy bất tận) như thế công thức Sum sẽ trả về cho bạn kết quả là 0.

Tính tổng cột nhưng bỏ hàng tiêu đề hoặc một vài hàng đầu tiên

Thông thường, Hàm SUM trong Excel thường sẽ tính tổng cột và bỏ qua hàng tiêu đề như ảnh chụp màn hình bên trên. Tuy nhiên, trong một số trường hợp, hàng tiêu đề vẫn được cộng luôn vào trong phép tính tổng, hoặc ngoài hàng tiêu đề, bạn muốn loại bỏ thêm vài hàng đầu tiên có giá trị số không liên quan đến giá trị bạn muốn cộng tổng. Mà ở Microsoft Excel không chấp nhận công thức SUM hỗn hợp với giới hạn trên dưới rõ ràng như công thức =SUM(B2:B) vẫn áp dụng được trên Google trang tính. Để bỏ một số hàng đầu tiên khỏi phép tính tổng, bạn có thể dùng một trong những cách sau đây:

1. Tính tổng toàn bộ cột rồi trừ đi các ô không muốn tính tổng (như trong ví dụ này cần loại từ ô từ B1 đến B3) =SUM(B:B)-SUM(B1:B3)

Học Word Excel cơ bản

Kích thước bảng tính có giới hạn nhất định nên bạn có thể chỉ định giới hạn trên của công thức SUM dựa trên số hàng tối đa trong phiên bản Excel mà bạn dùng. Ví dụ: để tính tổng cột B mà không bao gồm hàng tiêu đề (loại trừ ô B1) bạn có thể sử dụng các công thức sau:

Trong Excel 2007, Excel 2010, Excel 2013 và Excel 2016:

= SUM (B2: B1048576)

Trong Excel 2003 và phiên bản thấp hơn:

= SUM (B2: B655366)

Cách tính tổng các hàng trong Excel

Tương tự như tính tổng một cột, bạn có thể tính tổng một hàng trong Excel bằng cách sử dụng hàm SUM, hoặc Auto
Sum để chèn công thức tính.

Ví dụ, để tính tổng các giá trị trong ô B2 đến D2, ta sử dụng công thức sau:

= SUM (B2: D2)

*

Cách tính tổng nhiều hàng trong Excel

Để tính tổng các giá trị trong từng hàng đơn lẻ ta chỉ cần kéo công thức SUM xuống, nhưng nhiều khi chúng ta cần tính tổng của hàng có chứa kí tự $. Ví dụ: =SUM($B2:$D2) Để tính tổng các giá trị trong một dải có chứa nhiều hàng, ta chỉ cần xác định dải cần áp dụng công thức Sum. Ví dụ:

= SUM (B2: D6) – tổng các giá trị từ hàng 2 đến hàng 6.

= SUM (B2: D3, B5: D6) – tổng các giá trị trong các hàng 2, 3, 5 và 6.

Cách tính tổng toàn bộ một hàng

Để tính tổng toàn bộ hàng với một số cột không xác định, ta cần cung cấp tham chiếu toàn bộ hàng cho công thức Excel Sum, ví dụ:

= SUM (2: 2)

Cần lưu ý rằng, bạn không nên nhập công thức ‘tổng một hàng’ vào một ô ở trong cùng hàng đó vì như thế sẽ tạo ra một tham chiếu tròn khiến cho kết quả của phép tính bị sai. Để tính tổng hàng không bao gồm (một số) cột nhất định, ta cộng toàn bộ hàng và sau đó trừ các cột không liên quan. Ví dụ: để tính tổng hàng 2 trừ hai cột đầu tiên, ta sử dụng công thức sau:

= SUM (2: 2) -SUM (A2: B2)

Sử dụng Excel Total Row để tính tổng dữ liệu trong một bảng:

Nếu dữ liệu của bạn được nằm trong một bảng tính của Excel, bạn có thể sử dụng tính năng Total Row để nhanh chóng tính tổng dữ liệu trong bảng và tổng số hiển thị trong hàng cuối cùng.

Một lợi thế lớn của việc sử dụng các bảng Excel là chúng tự động mở rộng để thêm được các hàng mới, do đó, bất kỳ dữ liệu mới nào bạn nhập vào trong một bảng sẽ được tự động thêm luôn vào công thức đã có.

Để chuyển đổi một dải ô bình thường thành một bảng, hãy nhấn chọn và ấn phím tắt Ctrl + T (hoặc nhấp vào Table trên tab Insert).

Cách thêm hàng total trong bảng Excel

Khi dữ liệu của bạn được sắp xếp trong một bảng, bạn có thể chèn thêm hàng total theo cách sau:

1. Nhấp vào bất kỳ đâu trong bảng để hiển thị Table Tools với tab Design. 2. Trên tab Design, trong phần Table Style Options, chọn hộp Total Row: Một cách khác để thêm total row trong Excel là nhấp chuột phải vào bất kỳ ô nào trong bảng và sau đó chọn vào Table > Totals Row.

Khóa học lập trình VBA trong Excel 

Cách tính tổng dữ liệu trong bảng

Khi tổng số hàng xuất hiện ở cuối bảng, Excel sẽ tự xác định cách tính toán dữ liệu trong bảng.

Như trong bảng mẫu bên dưới, các giá trị trong cột D (cột bên phải) được thêm tự động và tổng được hiển thị trong hàng Total: Để tính tổng giá trị trong các cột khác, chỉ cần chọn một ô tương ứng trong tổng số hàng, nhấp vào mũi tên trong ô để hiển thị danh sách thả xuống và chọn Sum:

Nếu bạn muốn thực hiện một số phép tính khác, hãy chọn chức năng tương ứng từ danh sách thả xuống như Average, Count, Max, Min, …

Nếu hàng total tự động hiển thị tổng cho một cột mà bạn không cần dùng đến tổng đó thì mở danh sách thả xuống của cột đó và chọn None.

Chú thích. Khi sử dụng tính năng Excel Total Row để tính tổng một cột, tổng số Excel chỉ có giá trị trong hàng hiển thị bằng cách chèn hàm SUBTOTAL với đối số đầu tiên là 109.

Nếu bạn muốn tổng hợp dữ liệu cả trong hàng hiển thị và không hiển thị thì không thêm tổng số hàng nữa, đồng thời sử dụng hàm SUM bình thường:

Cách tính tổng các ô được (có thể nhìn thấy được) trong Excel

Đôi khi, để việc phân tích dữ liệu hiệu quả hơn, bạn cần phải lọc hoặc ẩn một số dữ liệu trong bảng tính. Một công thức tổng hợp thông thường sẽ không hoạt động trong trường hợp này bởi vì hàm Excel SUM sẽ cộng tất cả các giá trị trong phạm vi được chỉ định bao gồm các hàng ẩn (đã lọc ra).

Nếu bạn muốn tính tổng các ô hiển thị trong một danh sách được lọc, cách nhanh nhất là đưa chúng vào một bảng Excel, và sau đó bật tính năng Excel Total Row. Như đã trình bày trong ví dụ trước, chọn Sum ở hàng total của bảng rồi chèn chức năng SUBTOTAL bỏ qua các ô ẩn.

Một cách khác để tính tổng các ô đã lọc trong Excel là áp dụng Auto
Filter cho dữ liệu của bạn theo cách thủ công bằng cách nhấp vào nút Filter trên tab Data. Và sau đó, tự viết một công thức Subtotal.

Hàm SUBTOTAL có cú pháp sau:

SUBTOTAL (function_num, ref1, , …) Trong đó, Function_num – một số từ 1 đến 11 hoặc từ 101 đến 111 dùng để xác định chức năng sẽ được dùng cho việc tính tổng phụ.

Bạn có thể tìm thấy danh sách đầy đủ các chức năng trên support.office.com. Bây giờ, chúng ta chỉ quan tâm đến chức năng SUM, được định nghĩa bởi các số 9 và 109. Cả hai số này loại trừ các hàng được lọc ra. Sự khác biệt là 9 bao gồm các ô ẩn thủ công (tức là bạn chuột phải rồi chọn Hide), trong khi đó 109 không bao gồm các ô đó.

Vì vậy, nếu bạn muốn tính tổng cho riêng những hàng được hiển thị, không tính những hàng đã bị ẩn thì có thể dụng 109 làm đối số cho công thức Subtotal của bạn.

Ref1, Ref2, … – ô hoặc dãy mà bạn muốn tính tổng phụ. Đối số Ref đầu tiên là bắt buộc, những đối số tiếp theo (lên đến 254) là tùy chọn. Trong ví dụ này, ta sẽ tính tổng các ô có thể nhìn thấy trong khoảng B2: B14 bằng cách sử dụng công thức sau:

= SUBTOTAL (109, B2: B14) Giờ, hãy lọc riêng hàng “Banana” và sử dụng công thức Subtotal để tính tổng riêng cho các ô được hiển thị. Mẹo: Bạn có thể dùng tính năng Auto
Sum của Excel để chèn công thức Subtotal một cách tự động. bạn chỉ cần tổ chức dữ liệu của bạn trong bảng (Ctrl + T) hoặc lọc dữ liệu theo cách bạn muốn bằng cách nhấp vào nút Filter. Sau đó, chọn ô bên dưới cột mà bạn muốn tính tổng, và nhấp vào nút Auto
Sum trên thanh thực đơn. Công thức SUBTOTAL sẽ được chèn vào và nó chỉ tính tổng các ô hiển thị trong cột.

Cách sử dụng running total (tính tổng lũy tích) trong Excel

Để tính tổng lũy tích trong Excel, bạn viết công thức SUM như bình thường và sử dụng thông minh các tham chiếu của các ô một các tuyệt đối và tương đối.

Ví dụ, muốn hiển thị sô lũy tích của các số trong cột B, ta nhập công thức sau và ô B2 rồi sao chép sang các ô khác:

=SUM($B$2:B2)

Cách tính tổng cho nhiều trang tính trên cùng bảng tính

Nếu bạn có một vài bảng tính với bố cũng và dữ liệu tương tự nhau, bạn có thể cộng tổng các dữ liệu trong cùng một ô hoặc dải ô ở các trang tính khác nhau với cùng một công thức SUM.

Chúng ta có một công thức gọi là tham chiếu 3-D:

=SUM(Jan:Apr!B6)

hoặc

=SUM(Jan:Apr!B2:B5)

Công thức đầu tiên là cộng tổng các giá trị trong ô B6, còn công thức thứ 2 cộng tổng của dải B2:B5 trong tất cả các bảng tính nằm giữa hai ranh giới mà bạn đã chỉ định sẵn (Như trong ví dụ này ranh giới là Jan và Apr).

Tính tổng có điều kiện trong Excel

Nếu công việc của bạn yêu cầu tính tổng các ô đáp ứng điều kiện nhất định nào đó thì bạn có thể sử dụng hàm SUMIF hoặc SUMIFS tương ứng.

Ví dụ: công thức SUMIF sau chỉ cộng tổng các giá trị trong cột B có trạng thái “Completed” (đã hoàn thành) trong cột C, ta có:

= SUMIF (C: C, “completed”, B: B) Để tính tổng có điều kiện cho nhiều tiêu chí khác nhau, bạn sử dụng hàm SUMIFS. Trong ví dụ trên, để có được tổng số đơn đặt hàng “Completed” với số tiền trên $200, hãy sử dụng công thức SUMIFS sau:

=SUMIFS(B:B,C:C,”completed”,B:B, “>200” ) Chú thích: Các chức năng tính tổng có điều kiện có sẵn trong các phiên bản của Excel bắt đầu từ phiên bản 2003 (cụ thể hơn, SUMIF có ở Excel 2003, nhưng SUMIFS chỉ có từ phiên bản Excel 2007). Nếu bạn vẫn còn sử dụng những phiên bản Excel cũ hơn thì cần phải sử dụng công thức SUM dạng mạng để tính tổng các dữ liệu với điều kiện đi kèm.

Excel SUM không hoạt động – lý do và giải pháp

Khi bạn tính tổng của một vài giá trị hoặc một số cột trong bảng tính Excel nhưng công thức SUM đơn giản không hoạt động thì có thể là do những lý do sau đây:

1. #Name error xuất hiện thay vì kết quả mong đợi

Đó là lỗi dễ sửa chữa nhất. 99 trên 100 trường hợp, lỗi #Name error chỉ ra rằng hàm SUM bị sai chính tả.

2. Một số con số không được cộng vào tổng chung

Một lý do phổ biến khác khiến một công thức Sum (hoặc Excel Auto
Sum) không hoạt động là các con số được định dạng dưới dạng các giá trị văn bản. Thoạt nhìn, chúng trông giống như các con số bình thường, nhưng Microsoft Excel nhận dạng chúng như chuỗi văn bản nên không đưa chúng vào tính toán.

Một số trường hợp, chúng ta có thể nhận ra lỗi này một cách trực quan khi thấy văn bản được căn lề trái một cách mặc định hoặc xuất hiện tam giác màu xanh lá cây ở góc trên cùng bên trái của ô, như trong ảnh chụp màn hình bên dưới: Để khắc phục điều này, bạn chọn tất cả các ô có vấn đề, nhấp vào dấu hiệu cảnh báo, sau đó bấm Convert to Number.

3. Hàm Excel SUM trả về kết quả bằng 0

Ngoài các con số được định dạng dưới dạng văn bản, tham chiếu vòng tròn cũng là nguyên nhân của các vấn đề trong công thức Sum, đặc biệt là khi bạn cố gắng tính tổng một cột trong Excel. Vì vậy, nếu các con số của bạn được định dạng dưới dạng số, nhưng công thức Excel Sum của bạn vẫn trả về kết quả bằng 0, bạn cần sửa các tham chiếu tròn trong bảng của bạn (vào tab Formula > Error Checking > Circular Reference).

4. Công thức Excel Sum trả về một con số cao hơn dự kiến

Nếu công thức Sum của bạn sẽ trả về một số lớn hơn dự kiến, bạn cần nhớ rằng hàm SUM trong Excel sẽ thêm cả các ô ẩn nà ta không nhìn thấy được. Trong trường hợp này, bạn có thể sử dụng chức năng Subtotal để thay thế công thức SUM.

5. Công thức Excel SUM không cập nhật

Khi một công thức SUM trong Excel tiếp tục hiển thị tổng số cũ ngay cả sau khi bạn đã cập nhật các giá trị cần tính tổng mới, nhiều khả năng Calculation Mode được đặt thành Manual. Để khắc phục sự cố này, hãy chuyển đến tab Formulas, nhấp vào mũi tên thả xuống bên cạnh Calculate Options, rồi nhấn Automatic.

Trên đây là những lý do phổ biến nhất khiến công thức SUM trong Excel không hoạt động. Nếu không có trường hợp nào ở trên giống với trường hợp của bạn, thì có thể nghiên cứu thêm về những lý do khiến các công thức Excel không hoạt động, không cập nhật, không tính.

Xem thêm: Just a moment - tình yêu tuổi học trò là gì

– – – – –

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

Định dạng theo điều kiện với Conditional formatting
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm