Trong excel có khá nhiều cách để tính tổng khác nhau. Nhưng khi bạn gặp phải trường hợp có cả số lẫn chữ thì bạn không thể tính tổng một cách đơn giản. Vậy hôm nay 123job.vn sẽ hướng dẫn bạn hàm tính tổng trong Excel hay gặp.

Excel chính là một phần mềm được nằm trong bộ cài Microsoft Office được dùng để ghi lại và trình bày các thông tin dưới dạng bảng. Một trong những phép tính tổng cơ bản, hoặc được mọi người sử dụng chính là đó hàm tính tổng. Nhưng bạn vẫn chưa biết cách thực hiện nó như thế nào? Rất đơn giản bài viết dưới đây, tôi sẽ hướng dẫn tính tổng trong excel cho bạn nhé!

I. 4 Cách Tính Tổng Nâng Cao Trong Excel

Có thể bạn sẽ gặp phải những trường hợp dữ liệu không chỉ có riêng số mà trong đó nó có lẫn cả kiểu chữ trong đó. Thì hôm nay tôi sẽ hướng dẫn bạn cách tách "kg" ra khỏi số lượng để có thể dễ dàng thực hiện cách tính tổng trong Excel. Bài toán này có đơn vị là "kg", còn những bài toán khác có đơn vị là "chai", "lít",... thì bạn chỉ cần thay "kg" thành đơn vị bạn cần tính tổng trong Excel. 

1. Sử dụng hàm SUBSTITUTE 

Đầu tiên tôi sẽ hướng dẫn bạn sử dụng hàm SUBSTITUTE. Hàm SUBSTITUTE trong Excel sẽ thay thế một hay nhiều kí tự cho trước tại một hay nhiều vị trí bằng kí tự cụ thể. Ở đây tôi thay thế " kg" thành chuỗi kí tự rỗng để cách " kg" ra khỏi. 

Hướng dẫn cách tính tổng nâng cao hàm Excel

Cách tính tổng hàm SUM theo hàng ngang trong Excel

Lưu ý: phải có khoảng trắng trước "kg" để sau khi thay thế chỉ còn mỗi kiểu dữ liệu số. Sau khi cách ra còn mỗi kiểu dữ liệu số, nhưng kiểu dữ liệu vẫn là kiểu chữ nên để chuyển định dạng sang số ta chỉ cần nhân với 1 (*1) hoặc là cộng với 0 (+0).

2. Sử dụng hàm LEFT và FIND.

Như ta cũng đã biết hàm FIND là tìm ra vị trí của chuỗi mà ta cần tìm. Thì như ta thấy kiểu dữ liệu số và "kg" cách nhau bởi khoảng trắng, ta có thể dựa vào đó để tìm vị trí của khoảng trắng. 

Tiếp tục ta dùng hàm LEFT, và kết hợp với hàm FIND vừa tìm ở trên để lấy ra kí tự ta cần lấy. Nhưng lưu ý phải trừ đi 1 để lấy được chuỗi, tại vì hàm FIND trên trả ra kí tự của khoảng trắng. Và như ở cách trên ta nhân với 1 (*1) hoặc cộng với 0 (+0) để chuyển kiểu dữ liệu sang kiểu dữ liệu số. Tiếp theo đó ta chỉ cần gọi hàm SUMPRODUCT để tính tổng trong Excel dữ liệu trên. Và kết quả sau khi tính cho ta được kết quả như cách 1.

3. Sử dụng hàm SUM kết hợp hàm SUBSTITUTE.

Tương tự như cách sử dụng hàm SUBSTITUTE ở cách 1. Ta thay thế hàm SUMPRODUCT bằng hàm SUM và nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện tính tổng trong Excel theo kiểu mảng. Và kết quả cũng cho ra kết quả tương tự như 2 cách trên.

4. Sử dụng hàm SUM kết hợp hàm LEFT và FIND

Sử dụng tương tự hàm LEFT và FIND ở cách 2. Ta tiếp tục thay thế hàm SUMPRODUCT bằng hàm SUM và nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện tính tổng trong Excel theo kiểu mảng.
Làm tương tự cách 3 và chỉ thay thế hàm SUBSTITUTE ở cách một bằng hàm hàm LEFT và FIND ở cách 2, và kết quả cũng không khác gì các kết quả ở trên.

II. Cách tính tổng hàm SUM theo hàng ngang trong Excel

Cách 1: Bạn có thể áp dụng tính tổng excel trong hàm sum hàng ngang bằng cách giữ phím Alt vào ô cần nhập vào công thức sau đó nhấn phím "=", ngay lập tức công thức hàm tính tổng trong excel sẽ hiện ra là khoanh toàn bộ các giá trị theo hàng ngang. Kết quả sẽ tính tổng excel hiện ra bằng 27, bạn có thể tính nhẩm để tra kết quả xem có khớp không.

Cách tính tổng hàm SUM theo hàng ngang trong Excel

Cách tính tổng hàm SUM theo hàng ngang trong Excel

Cách 2: Cách thứ 2 bạn sử dụng theo phương pháp hàm tính tổng trong excel truyền thống đó là nhập công thức hàm tính tổng Excel là hàm SUM = SUM sau đó nhấn chuột vào hàng đầu tiên rồi kéo từ trái sang phải .

Cách 3: Bạn chỉ nhập nhập dấu "=" vào ô cần tính tổng excel hàm Sum sau đó chọn vào tab Home, phần Editing và AutoSUM. Ngay lập tức thao tác trên sẽ giúp bạn tính tổng toàn bộ hàng ngang trên hàm tính tổng trong Excel và bạn chỉ cần nhấn Enter để kết quả hiện ra mà thôi.

Có một cách để bạn kiểm tra xem tổng của một hàng chính xác sẽ là bao nhiêu bằng cách bôi đen vào tất cả các hàng, ở dưới thì bạn sẽ nhìn thấy có chữ Sum và kết quả hiện ra, đây là hệ thống tính tổng trong Excel giúp bạn tính tổng trong excel là hàm sum hàng ngang trong Excel.

III. Hướng dẫn cách tính tổng theo các ô có ký tự giống nhau trong Excel

Cách 1: Tạo cột phụ nhận biết giá trị điều kiện tính
Bởi vì các ký tự nằm lẫn trong chuỗi ký tự ở mỗi ô vậy nên chúng ta có thể dùng hàm tính tổng trong excel xác định riêng từng ký tự xem ô nào chứa ký tự đó. Chèn thêm cột phụ ở phía sau cột Doanh thu và dùng hàm SEARCH trong excel xác định như sau:

Trong câu lệnh hàm IFERROR(SEARCH($C$1,A2),0) ta có:

Thứ 1: Search giá trị ở ô C1 (là KD1) xem có trong ô A2 hay không. Kết quả trả về nếu có sẽ là vị trí ký tự bắt đầu xuất hiện trong ô A2. Nếu không sẽ trả về lỗi #VALUE

Thứ 2: Kết hợp hàm IFERROR để có thể tránh trường hợp lỗi #VALUE khi không tìm thấy bởi hàm SEARCH trong Excel, lúc đó giá trị lỗi sẽ được thay bằng số 0. Áp dụng tương tự với giá trị ở ô D1 là KD2. Khi đó kết quả > 0 tức là có giá trị bạn cần tìm, = 0 là không có giá trị cần tìm. Bây giờ bài toán sẽ thành đơn giản hơn, chúng ta chỉ cần sử dụng hàm SUMIF để tính kết quả như sau:

Kết quả tổng doanh thu của nhân viên có mã ID là KD1: =SUMIF(C2:C10,”>0″,B2:B10). Tương tự nhân viên có mã KD2 là =SUMIF(D2:D10,”>0″,B2:B10)

Cách 2: Tính trực tiếp không dùng cột phụ với hàm SUMIF

Việc thêm cột phụ sẽ khiến chúng ta phải thêm một số điều kiện là 1 cột, tại mỗi cột lại dùng nhiều công thức để tính tổng excel. Tại sao không làm trực tiếp để có thể tiết kiệm tài nguyên tính tổng trong Excel nhỉ? Câu trả lời chính là hoàn toàn có thể làm trực tiếp được. Cách viết công thức tính tổng trong Excel như sau:

Đối với điều kiện KD1 chúng ta có: H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)

Còn với vùng điều kiện là ở cột A, từ A2:A10 là ID nhân viên. Điều kiện là “*”&F3&”*” có nghĩa là thêm 2 dấu * ở trước và sau giá trị trong ô F3. Vì dấu * là ký tự đặc biệt nối với ô F3 nên cần đặt trong dấu nháy kép, sau đó sử dụng dấu & để nối ký tự. Vùng tính tổng là cột Doanh thu, từ B2:B10 và kết quả cũng bằng với cách thứ 1.

Nếu các bạn không muốn sử dụng hàm SUMIF thì các bạn có thể thay bằng hàm SUMIFS như sau: H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”). Các nội dung trong hàm này sẽ vẫn giống hàm SUMIF, tuy nhiên thứ tự có thay đổi 1 chút khi vùng tính tổng trong Excel được đưa lên thành phần thứ 1 ở trong hàm tính tổng trong Excel.

Cách 3: Sử dụng hàm SUMPRODUCT

Dĩ nhiên bạn đã nghe nói tới hàm SUMPRODUCT có thể thay thế hoàn toàn cho hàm SUMIF và SUMIFS. Trong ví dụ này thì chúng ta sẽ xem hàm SUMPRODUCT có thể sử dụng được không nhé. Công thức trong ô I3 sử dụng hàm SUMPRODUCT như sau: =SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>. Các bạn có thể thấy phần IFERROR (SEARCH(F3,$A$2:$A$10),0) chính là nội dung trong cột phụ mà chúng ta đã làm ở trên. Khi đặt trong hàm SUMPRODUCT  thì chúng ta sẽ so sánh kết quả đó có > 0 hay không, và đối với những giá trị > 0 sẽ dùng để tính tổng trong excel ở cột Doanh thu.

* Lưu ý: Đối với cách viết tính tổng trong Excel sau đây sẽ không ra kết quả: =SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)

IV. Cách tính tổng giá trị danh sách đã lọc trong Excel

Bài hướng dẫn này đang được thực hiện trên Excel 2019, tuy nhiên bạn vẫn có thể áp dụng cách tính này trên các phiên bản Excel khác như là Excel 2007, Excel 2010, Excel 2013, Excel 2016, vì hướng dẫn sử dụng hàm Excel Subtotal. Sau khi lọc ra danh sách sản phẩm thuộc loại sách ta có bảng tính như sau:

Ví dụ về bảng Excel có dữ liệu đã được lọc: Yêu cầu đặt ra là cách tính tổng trong Excel tiền sách. Nếu sử dụng hàm SUM() cho bảng dữ liệu đã lọc trên, kết quả bạn nhận được không phải là tổng tiền sách mà bạn sẽ là tổng tiền của tất cả các sản phẩm. Trong trường hợp này ta sử dụng hàm SUBTOTAL như sau, tại ô E13, bạn nhập =SUBTOTAL(9,H7:H11). Trong đó, 9 là giá trị đối số tương ứng cùng với hàm cần dùng hàm tính tổng trong excel, ở đây ta muốn tính tổng, vì vậy hàm cần dùng là hàm SUM để tính tổng trong excel, bạn có thể xem ở bảng bên dưới, H7:H11 là phạm vi cần tính tổng trong Excel. 

Cách tính tổng giá trị danh sách đã lọc trong Excel

Cách tính tổng giá trị danh sách đã lọc trong Excel

Về hàm SUBTOTAL()

Hàm SUBTOTAL() sẽ xem xét toàn bộ danh sách giá trị trong cột D và chỉ tính toán những giá trị thỏa mãn bộ lọc. Tuy nhiên, đối số này tính tổng trong Excel cho biết chúng ta muốn tính tổng trong excel các giá trị tham chiếu. Bảng sau liệt kê các đối số được chấp nhận. Sau khi xem có lẽ các bạn vẫn đang băn khoăn sự khác nhau giữa 9 và 109. Khi ta dùng đối số 9, hàm SUBTOTAL() sẽ tính tổng trong Excel cả các giá trị đã bị ẩn. Còn khi ta dùng đối số 109, hàm SUBTOTAL() sẽ bỏ qua các giá trị đã bị ẩn. Vậy chúng ta cần phân biệt rõ giá trị bị ẩn và giá trị bị loại do nó không thỏa mãn bộ lọc..

V. Kết luận 

Trên đây là cách tính tổng trong Excel nâng cao có thể bạn quan tới, hy vọng rằng qua bài viết này sẽ giúp bạn có những thông tin bổ ích nhất. Chúc bạn thực hiện thành công!