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:
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.
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.
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ự.
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:
Đồ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:
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.
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:
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.
- 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.
- Để 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:
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.
3.2, Chọn Các Giá Trị Cần Lọc
Sau khi sử dụng filter, chọn Mũi tên bên cạnh tiêu đề cột để thu hẹp dữ liệu.
Ấ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ả.
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:
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.
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:
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:
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:
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.
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:
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,…
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