Cách tính tổng trong excel

6 Cách Tính Tổng Trong Excel Và Ví Dụ Cụ Thể

Khi làm việc với bảng số liệu trên Excel, người dùng thường phải tính tổng các ô dữ liệu trong Excel. Một trong những cách cơ bản là sử dụng hàm SUM- một trong 10 hàm Excel phổ biến nhất. Các bạn đã bao giờ thắc mắc: nếu không sử dụng hàm SUM thì có những cách nào khác để tính tổng trong excel hay không?

 

Bài viết này sẽ chia sẻ với bạn cách sử dụng các cách tính tổng trong excel sử dụng hàm cộng, hàm sum, autosum, subtotal, sumif (và sumifs), table tool, pivot table để tính tổng các số trong excel 2003, 2007, 2010, 2013, 2016, 2019.

Mục Lục Bài Viết

Trước khi bắt đầu với các hàm và tính năng hữu ích của excel, hãy thử cách tính tổng trong excel nhanh nhất chỉ bằng một cú kéo chuột: giữ chuột trái từ ô bắt đầu kéo đến ô kết thúc của dữ liệu cần tính tổng và nhìn vào thanh trạng thái Excel để xem tổng của các ô được chọn:

cách tính tổng trong excel nhanh nhất chỉ bằng một cú kéo chuột và xem kết quả tại thanh trạng thái Excel

1, Cách Tính Tổng Trong Excel Bằng Công Thức Cộng Đơn Giản (=A+B+C)

Phép cộng trong excel có cú pháp vô cùng đơn giản:

= [số thứ 1]+[số thứ 2]+…+[số thứ n] với tổng số kí tự tối đa trong một ô tính là 32767 kí tự.

 

Sử dụng công thức cộng trên có ưu điểm là vô cùng đơn giản, ví dụ =1+2+3 sẽ trả kết quả bằng 6, nhưng có nên dùng công thức cộng {=A+B+C} thay cho hàm SUM {=SUM(A,B,C)} với các phép tính dài hơn, số có phần thập phân và có giá trị lớn hay không?

 

Việc sử dụng phép cộng đơn giản thường tốn thời gian cho việc nhập dữ liệu và dễ sai, một khi đã nhập sai thì khó để dò ra lỗi hơn. Thay vào đó, việc nhập dữ liệu vào từng ô và sử dụng hàm sum để thay hàm cộng trong excel giúp việc kiểm tra dữ liệu nhanh và dễ dàng hơn rất nhiều.

 

Ví dụ: việc kiểm tra dữ liệu đã nhập gồm 39 ký tự (không kể dấu “=”) =16979419846.26+45795.24+957913889423.33 sẽ khó khăn hơn việc kiểm tra 3 ô số liệu đã được định dạng có dấu ngăn cách hàng nghìn và hàng thập phân trong excel.

 

Bạn cũng có thể nhập liệu vào các ô A1:A3 rồi sử dụng cú pháp = A1+A2+A3, nhưng rõ ràng với dữ liệu từ vài chục đến hàng trăm giá trị cần cộng thì việc gõ đi gõ lại phép cộng không phải là một sự lựa chọn giúp tiết kiệm thời gian và công sức.

2, Sử Dụng Hàm Sum Để Tính Tổng Trong Excel

2.1, Cú Pháp Hàm SUM:

=sum([số thứ 1], [số thứ 2],…,[số thứ n])

trong đó, số thứ 1, số thứ 2,…,số thứ n các đối số cần tính tổng, với n nhỏ hơn hoặc bằng 255.

 

Lệnh sum trong excel chỉ cộng được 255 số? Không cần lo lắng về giới hạn số đối số, vì đối số trong công thức hàm sum có thể là số một, cũng có thể là một ô như B1, mảng (array), hằng số (constant) hoặc các số trong một phạm vi nhiều ô như C1:D13, hay là sự kết hợp của các hàm.

2.2, Lưu Ý Với Đối Số

2.2.1, Hàm Sum Có Đối Số Là TRUE/FALSE

Ô tính có giá trị TRUE là 1, FALSE là 0. Ví dụ: Ô tính có công thức =SUM(5,TRUE) sẽ trả về giá trị là 6.

2.2.2, Đối Số Là Văn Bản

Chỉ các giá trị số của đối số trong ô tính, mảng hay tham chiếu mới được tính. Các giá trị khác là văn bản đều được bỏ qua.

06 cách tính tổng trong excel và ứng dụng hàm sum, subtotal, pivot table, sumif, sumifs, table tool trong tính tổng

Khi nhập liệu, cần lưu ý định dạng số trước khi thực hiện phép cộng bằng hàm sum.

Như ví dụ dưới đây, khi dòng “Ổi” với giá tiền 300.000 đáng lẽ phải được cộng vào kết quả nhưng vì mang định dạng text (dấu hiệu là tam giác màu xanh lá cây ở góc trên cùng bên trái ô B4) nên kết quả hàm sum chỉ bao gồm Táo + Cam  trong khi kết quả phép cộng vẫn có giá trị “Ổi”.

Một dấu hiệu khác của dữ liệu tồn tại dưới dạng văn bản là dữ liệu được mặc định căn lề trái.

Lỗi hàm SUM do đối số có lỗi

2.2.3, Lỗi Hàm SUM Do Đối Số Có Lỗi

Nếu đối số có lỗi, SUM sẽ trả về lỗi. Hàm AGGREGATE có thể tính tổng trong khi bỏ qua lỗi.

Một số lỗi đối số hay gặp là #REF!, #VALUE!.

 

Lỗi value trong excel #VALUE!: lỗi với cách nhập công thức của bạn hoặc lỗi với các ô bạn đang tham chiếu tới. Ví dụ khi giá tiền của Ổi là một công thức =100+A với A là văn bản, thì đối số B4 gặp lỗi #VALUE! dẫn đến tổng bằng hàm SUM ở B7 gặp lỗi tương tự.

 

Lỗi ref trong excel #REF!: lỗi hiển thị khi công thức tham chiếu đến một ô không phải là hợp lệ, thường xảy ra khi ô chứa giá trị là một công thức tham chiếu đến các ô khác đã bị mất vì bị xóa, ghi đè, hoặc tham chiếu vượt quá phạm vi, tham chiếu tới một file excel đang đóng, v.v…

Ví dụ khi giá tiền của Ổi tại ô B4 là một công thức =A5+B5 với A5, B5 là các ô tính trong excel, người dùng lỡ tay xóa dòng 5 bằng chức năng Home > Delete > Delete sheet rows, thì đối số B4 gặp lỗi #VALUE! dẫn đến kết quả tính tổng tiền trong excel bằng hàm SUM ở B7 gặp lỗi tương tự.

loi-ham-sum

2.2.4, Hàm SUM Khi Xóa Hoặc Thêm Hàng/Cột

Khi xóa hàng/cột thì hàm SUM tự động loại trừ hàng đã xóa nên không bị lỗi #REF! như việc sử dụng phép cộng đơn thuần:

06 cách tính tổng trong excel và ứng dụng hàm sum, subtotal, pivot table, sumif, sumifs, table tool trong tính tổng

 

Đồng thời, khi thêm hàng/cột công thức tổng SUM bao gồm giá trị ô cần tính trong hàng hoặc cột được thêm:

06 cách tính tổng trong excel và ứng dụng hàm sum, subtotal, pivot table, sumif, sumifs, table tool trong tính tổng



2.3 Một Số Cách Sử Dụng Hàm SUM Trong Excel

2.3.1, Sử Dụng AUTOSUM Tính Tổng Dãy Số Hoặc Các Ô Liền Mạch Trong Một Cột Trong Excel

Để tính tổng các giá trị trong ô B18 đến B20, các bạn có thể chọn một ô bên dưới các số bạn muốn tính tổng, bấm Autosum trên tab Home hoặc áp dụng nhanh tổ hợp phím “Alt” và “=” tại ô B21 hoặc bất kỳ một ô nào bên dưới dãy ô B18:B20 mà giữa chúng là các khoảng trống.

 

Như ví dụ dưới đây, hoctin.vn đã sử dụng Autosum tại ô B23: ấn Alt = sẽ cho ra kết quả là tổng các ô từ B18 đến B22.

Cách tính tổng trong excel sử dụng autosum
Cách tính tổng trong Excel sử dụng AUTOSUM tính tổng dãy số hoặc các ô liền mạch trong một cột trong Excel

 

Hoặc chọn cả vùng dữ liệu và ấn AutoSum để tính tổng hàng, cột, hay toàn bộ dữ liệu:

06 cách tính tổng trong excel và ứng dụng hàm sum, subtotal, pivot table, sumif, sumifs, table tool trong tính tổng

2.3.2, Cách Dùng Hàm SUM Tính Tổng Toàn Bộ Một Cột/Hàng Trong Excel

Nếu dữ liệu được nhập vào cột có thể được bổ sung thường xuyên và có thể vượt quá phạm vi cộng của công thức bạn đã đặt, bạn có thể tính tổng toàn bộ cột.

 

Lưu ý cần đặt công thức ở một cột khác cột cần cộng để tránh kết quả trả về 0 do lỗi tham chiếu vòng (Kiểm tra bằng cách vào tab Formula > Error Checking > Circular Reference).

 

Ngoài ra, lưu ý rằng hàm SUM sẽ hoạt động ngay cả với các giá trị từ các hàng bị ẩn và được filter. Nếu bạn chỉ muốn tính tổng các ô hiển thị, hãy sử dụng hàm Subtotal thay thế (vui lòng chuyển đến mục 3 để xem hướng dẫn chi tiết).

 

Cách tính tổng cột và hàng ngang trong excel có cú pháp:

  • Tính tổng cột A trong excel: =sum(A:A), hoặc sử dụng tổ hợp phím tắt: gõ =sum( rồi sử dụng mũi tên di chuyển hoặc chuột đến một ô bất kì trong cột cần tính, ấn tổ hợp Ctrl Space để chọn nguyên cột.
tinh-tong-cot-trong-excel
  • Tính tổng hàng 1 trong excel: =sum(1:1), hoặc sử dụng tổ hợp phím tắt: gõ =sum( rồi sử dụng mũi tên di chuyển hoặc chuột đến một ô bất kì trong hàng cần tính, ấn tổ hợp Shift Space để chọn nguyên hàng.
Cách dùng hàm SUM tính tổng toàn bộ một hàng trong Excel
  • Để trả kết quả về Giá trị hợp đồng tối thiểu thì Col_index_ num dưới đây là 2
  • Để trả kết quả về % hoa hồng thì Col_index_ num dưới đây là 3

2.3.3, Kết Hợp Với Các Hàm Khác

Trong thực tế, hàm SUM thường được dùng với những công thức phức tạp hơn và được kết hợp với rất nhiều hàm khác tùy mục đích của người sử dụng.

 

Ví dụ, bạn có thể kết hợp hàm sumproduct và hàm sum để tính trung bình cộng có trọng số hay số bình quân gia quyền như ví dụ dưới đây:

Cách tính tổng trong Excel sử dụng hàm SUM kết hợp các hàm khác

 

Việc kết hợp các hàm excel sẽ được hoctin.vn hướng dẫn các bạn ở series bài viết ứng dụng excel.

3. Sử Dụng Hàm Subtotal Để Tính Tổng Các Ô Được Lọc Khi Filter

Hồi còn làm kiểm toán ở Big4, hàm subtotal là một trong những hàm được mình sử dụng nhiều nhất.

Nhớ hồi đó team có một bạn chỉ quen sử dụng hàm sum và pivot table, vậy là khi lọc (filter) ra một số nghiệp vụ đặc biệt, bạn ấy luôn phải copy nghiệp vụ ra một trang tính mới, hoặc nhấn chuột từng ô giá trị cần tính để nhìn thanh trạng thái tính tổng Excel.

 

Khi sử dụng filter để lọc một số giá trị đặc biệt từ một vùng dữ liệu, hàm sum mặc định cộng toàn bộ các giá trị, bao gồm các giá trị bị ẩn khi filter, còn hàm subtotal chỉ tính tổng cộng các ô được hiển thị.

3.1, Lọc Bảng Dữ Liệu Bằng Filter

Để lọc bảng dữ liệu không có các dòng trống, chọn hàng đầu tiên của bảng (bằng phím tắt Ctrl Space), với bảng dữ liệu chứa các dòng trống, bạn nên chọn toàn bộ dữ liệu trong bảng; chuyển đến tab Data và nhấp vào biểu tượng Filter.

Một phím tắt mình thường sử dụng thay cho việc nhấp chuột để thực hiện Filter là Ctrl Shift L.

Lọc dữ liệu bằng Filter trong Excel để tính tổng

3.2, Chọn Các Giá Trị Cần Lọc

Sau khi sử dụng filter, chọn Mũi tên Mũi tên lọc bên cạnh tiêu đề cột để thu hẹp dữ liệu. 

Cách tính tổng trong Excel sử dụng Filter lọc dữ liệu có điều kiện

 

Ấn vào mũi tên, bỏ chọn Select All và chỉ chọn (các) giá trị để lọc. Nhấp OK để xem kết quả.

Lọc dữ liệu bằng Filter để tính tổng trong excel


3.3, Cách Sử Dụng Hàm Subtotal Để Tính Tổng Các Giá Trị Được Hiển Thị Trong Excel

Cú pháp hàm Subtotal để tính tổng:

=Subtotal(9,[số thứ 1], [số thứ 2],…,[số thứ n])

trong đó, số thứ 1, số thứ 2,…,số thứ n các đối số cần tính tổng, với n nhỏ hơn hoặc bằng 255.

 

Khi áp dụng hàm Subtotal để tính tổng trong bảng tính đã được filter, chỉ các ô đã lọc trong cột được tính tổng:

Sử dụng hàm Subtotal để tính tổng các giá trị được hiển thị trong Excel

4. Cách Sử Dụng Pivot Table Để Tính Tổng Nhanh Và Tính Tổng Có Điều Kiện Trong Excel

Tiếp tục ví dụ ở mục 3, thay vì lọc cho từng loại máy, có một chức năng rất thú vị của excel giúp bạn tính tổng tiền cho từng loại máy mà không cần phải thao tác lọc filter lặp đi lặp lại, đó là sử dụng Pivot table.

4.1, Tạo Pivot Table Cơ Bản Cho Vùng Dữ Liệu

Với bảng dữ liệu không có các hàng hay cột trống, chọn một ô bất kỳ trong vùng dữ liệu, với bảng dữ liệu chứa các hàng hoặc cột trống, bạn nên chọn toàn bộ dữ liệu trong bảng; chuyển đến tab Insert và nhấp vào biểu tượng PivotTable sau đó chọn OK để tạo pivot table cơ bản phục vụ tính tổng nhanh có điều kiện.

Tạo pivot table cơ bản cho vùng dữ liệu để tính tổng nhanh trong excel



4.2, Tính Tổng Nhanh Bằng Pivot Table

 

Pivot table được tạo trong một trang tính mới. Tại PivotTable Fields, kéo trường “Tên” vào Areas “Rows”, trường “Giá tiền (triệu đồng)” vào Areas “Values”, kết quả tổng giá tiền theo tên các loại máy hiện ra:

Tính tổng nhanh trong excel bằng pivot table

5. Cách Sử Dụng Hàm SUMIF Và SUMIFS Để Tính Tổng Có Điều Kiện Trong Excel

5.1, Cú pháp Hàm SUMIF và SUMIFS

Hàm SUMIF có từ phiên bản Excel 2003 với cú pháp như sau:

=SUMIF(range, criteria, [sum_range])

Trong đó:

 

  • Range: phạm vi được đánh giá bằng cách sử dụng Criteria (tiêu chí), là dữ liệu dải ô, ví dụ A1: A20
  • Criteria: điều kiện, tiêu chí sử dụng để đánh giá, ví dụ >100, =”done”

  • Sum_range: các ô được dùng để tính tổng nếu điều kiện được đáp ứng. Nếu sum_range bị bỏ qua, hàm sumif mặc định cộng các ô được xác định trong đối số range. 

Hàm SUMIFS chỉ có từ phiên bản Excel 2007 với cú pháp như sau:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:

  • Sum_range: Phạm vi ô cần tính tổng.
  • Criteria_range1: Phạm vi được kiểm tra bằng cách sử dụng Criteria1

  • Criteria1: Tiêu chí để sử dụng đánh giá ô nào trong Criteria_range1 thỏa mãn và xác định ô nào trong Criteria_range1 sẽ được cộng vào

  • Criteria_range2, criteria2, …các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/ tiêu chí.

5.2, Cách Tính tổng Trong Excel Có Điều Kiện Bằng Hàm SUMIF Và SUMIFS

Với dữ liệu các loại máy ở mục 3 và 4, nếu thêm một trường dữ liệu thể hiện tình trạng đã lắp đặt hay đang lắp đặt của các loại máy, thì công thức SUMIF giúp tính tổng các giá trị trong cột B có trạng thái “Yes” (đã lắp đặt) trong cột C:

Sử dụng hàm SUMIF giúp tính tổng có điều kiện trong excel

 

Với nhiều điều kiện hơn, công thức SUMIFS giúp tính tổng các giá trị trong cột B có trạng thái “Yes” (đã lắp đặt) trong cột C theo tên loại máy là máy in:

Sử dụng hàm SUMIFS giúp tính tổng có nhiều điều kiện trong excel

6. Sử Dụng Table Tool Chuyển Đổi Dữ Liệu Để Tính Tổng Nhanh Theo Cột

 

Nếu bạn thường xuyên cần tính tổng các cột, bạn có thể chọn cách tính tổng trong excel bằng việc chuyển đổi bảng tính của bạn sang bảng Excel.

Cách tính tổng trong Excel này sẽ đơn giản hóa việc tính tổng cột và hàng.

6.1, Cách Định Dạng Bảng Trong Excel

Để định dạng bảng, chọn ô bất kỳ trong bảng hoặc phạm vi ô mà bạn muốn định dạng dưới dạng bảng, ấn Ctrl T >> OK để định dạng vùng dữ liệu thành bảng tính.

chuyển đổi bảng tính sang bảng Excel để tính tổng

6.2, Cách Tính Tổng Trong Excel Bằng Table Tool

Tab Design (Thiết kế) xuất hiện, chọn hộp kiểm Total Row (hàng tổng) trong tab này hoặc sử dụng tổ hợp phím Ctrl Shift T:

Tính tổng bằng Table Tool trong excel

Hàng tổng sẽ được thêm vào cuối bảng của bạn. Chọn cột muốn tính tổng, nhấp vào mũi tên nhỏ hướng xuống rồi chọn SUM từ danh sách thả xuống. Bạn có thể thấy hàm Sum cũng như nhiều hàm khác như Average, Count, Count Numbers, Max, Min,…

Tính tổng bằng Table Tool trong excel

Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích về các cách tính tổng trong Excel. Ngoài ra, để ứng dụng hiệu quả Excel, 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, hãy cùng đón đọc series bài viết ứng dụng các hàm excel:

Hàm AVERAGE.

Hàm SUBTOTAL

Hàm SUMIF

Hàm SUMIFS

Hàm SUMPRODUCT

4 thoughts on “6 Cách Tính Tổng Trong Excel Và Ví Dụ Cụ Thể”

Leave a Comment