Trong excel có khá nhiều cách khác nhau để tính tổng. Nhưng khi bạn có cả số và chữ cái, bạn không thể đơn giản tính tổng. Vì vậy hôm nay 123job.vn sẽ hướng dẫn các bạn hàm sum trong Excel thông dụng.
Excel là một phần mềm có trong bộ cài Microsoft Office được sử dụng để ghi và trình bày thông tin dưới dạng bảng. Một trong những phép tính tổng cơ bản hay được mọi người sử dụng là hàm tổng. Nhưng bạn vẫn chưa biết phải làm như thế nào? Bài viết dưới đây rất đơn giản, mình sẽ hướng dẫn tính tổng trong excel cho các bạn!
I. 4 Phương pháp Tính Tổng Nâng cao trong Excel
Bạn có thể sẽ gặp phải những trường hợp dữ liệu không chỉ là những con số mà nó còn có một kiểu chữ trong đó. Thì hôm nay mình sẽ hướng dẫn các bạn cách tách “kg” ra khỏi đại lượng để các bạn dễ dàng thực hiện phép tính tổng trong Excel. Bài toán này có đơn vị là “kg”, còn các bài toán khác có đơn vị là “chai”, “lít”, … thì bạn chỉ cần đổi “kg” thành đơn vị cần tính tổng trong Excel.
1. Sử dụng SUBSTITUTE. hàm số
Đầu tiên, tôi sẽ hướng dẫn bạn cách sử dụng hàm SUBSTITUTE. Hàm SUBSTITUTE trong Excel sẽ thay thế một hoặc nhiều ký tự được chỉ định tại một hoặc nhiều vị trí bằng ký tự được chỉ định. Ở đây tôi thay thế “kg” bằng chuỗi trống để tách “kg” khỏi.
Cách tính tổng hàm SUM theo chiều ngang trong Excel
Lưu ý: phải có khoảng trắng trước “kg” để sau khi thay thế chỉ còn lại kiểu dữ liệu số. Sau khi tách, mỗi kiểu dữ liệu số, nhưng kiểu dữ liệu vẫn là kiểu văn bản nên để chuyển định dạng sang kiểu số ta chỉ cần nhân với 1 (* 1) hoặc cộng với 0 (+0).
2. Sử dụng hàm LEFT và FIND.
Như chúng ta cũng biết hàm FIND là tìm vị trí của chuỗi ký tự mà chúng ta cần tìm. Khi đó chúng ta có thể thấy kiểu dữ liệu là số và “kg” được ngăn cách nhau bởi một khoảng trắng, chúng ta có thể dựa vào đó để tìm ra vị trí của khoảng trắng.
Chúng ta tiếp tục sử dụng hàm LEFT, và kết hợp với hàm FIND vừa tìm ở trên để có được ký tự cần lấy. Nhưng lưu ý rằng bạn phải trừ đi 1 để lấy chuỗi, vì hàm FIND ở trên trả về ký tự của một khoảng trắng. Và như trên, chúng 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, chúng ta chỉ cần gọi hàm SUMPRODUCT để tính tổng trong Excel các dữ liệu trên. Và kết quả sau khi tính toán cho ta kết quả giống như cách 1.
3. Sử dụng hàm SUM kết hợp với hàm SUBSTITUTE.
Tương tự như cách sử dụng hàm SUBSTITUTE ở phương pháp 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 kết quả tương tự như 2 phương pháp trên.
4. Sử dụng hàm SUM kết hợp với LEFT và FIND. chức năng
Sử dụng tương tự hàm LEFT và FIND ở phương pháp 2. Chúng 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ự như cách 3 và chỉ cần thay hàm SUBSTITUTE ở phương pháp 1 bằng hàm LEFT và FIND ở phương pháp 2, và kết quả cũng giống như kết quả trên.
II. Cách tính tổng hàm SUM theo chiều ngang trong Excel
Cách 1: Bạn có thể áp dụng tính tổng trong excel trong hàm tính tổng ngang bằng cách giữ phím Alt trong ô để nhập công thức rồi 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 dưới dạng Khoanh tròn tất cả các giá trị theo chiều ngang. Kết quả sẽ tính ra tổng excel hiện lên 27, bạn có thể tính nhẩm để kiểm tra kết quả xem có khớp nhau không nhé.
Cách tính tổng hàm SUM theo chiều 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 truyền thống trong excel là nhập công thức hàm tính tổng trong Excel là hàm SUM = SUM, sau đó bấm vào hàng đầu tiên rồi kéo từ trái sang phải.
Cách 3: Bạn chỉ cần nhập dấu “=” vào ô tính tổng của hàm Sum trong Excel, sau đó chọn tab Home, phần E Edit và AutoSUM. Ngay lập tức thao tác trên sẽ giúp bạn tính tổng tất cả các hàng ngang trên hàm sum trong Excel và bạn chỉ cần nhấn Enter là kết quả hiện ra.
Có một cách để bạn kiểm tra xem tổng chính xác của một hàng sẽ là bao nhiêu đó là bôi đen tất cả các hàng, bên dưới bạn sẽ thấy chữ Sum và kết quả hiện ra, đây là hệ thống tổng. trong Excel giúp bạn tính tổng trong excel là hàm tính tổng ngang trong Excel.
III. Hướng dẫn cách tính tổng theo các ô có cùng chữ cái trong Excel
Cách 1: Tạo thêm một cột để nhận biết giá trị của điều kiện tính toán Do các ký tự nằm lẫn trong chuỗi ký tự trong mỗi ô nên ta có thể sử dụng hàm sum trong excel để xác định riêng từng ký tự xem ô đó chứa ký tự nào. bản thân đó. Chèn thêm một cột sau cột Doanh thu và sử dụng hàm TÌM KIẾM trong excel để xác định những điều sau:
Trong câu lệnh hàm IFERROR (SEARCH ($ C $ 1, A2), 0) chúng ta có:
Đầu tiên: Tìm kiếm giá trị trong ô C1 (là KD1) để xem nó có nằm trong ô A2 hay không. Kết quả trả về, nếu có, sẽ là vị trí ký tự bắt đầu trong ô A2. Nếu không, giá trị #VALUE. lỗi sẽ được trả lại
Thứ hai: Kết hợp hàm IFERROR để tránh lỗi #VALUE khi không tìm thấy bởi hàm SEARCH trong Excel, khi đó giá trị lỗi sẽ được thay thế bằng 0. Áp dụng tương tự với giá trị trong ô D1 là KD2. Khi đó kết quả> 0 nghĩa là có giá trị cần tìm, = 0 nghĩa là không có giá trị cần tìm. Bây giờ bài toán sẽ đơn giản hơn, chúng ta chỉ cần sử dụng hàm SUMIF để tính ra 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)
Phương pháp 2: Tính toán trực tiếp không dùng cột phụ với SUMIF. hàm số
Thêm một cột phụ sẽ khiến chúng ta thêm một số điều kiện là 1 cột, trong mỗi cột sử dụng nhiều công thức để tính tổng trong excel. Tại sao không làm điều đó trực tiếp để tiết kiệm tổng tài nguyên trong Excel? Câu trả lời chính là nó có thể được thực hiện trực tiếp. 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)
Đối 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 * vào trước và sau giá trị trong ô F3. Vì * là một ký tự đặc biệt nối với ô F3 nên nó cần được đặt trong dấu ngoặc kép, sau đó sử dụng & để nối ký tự. Tổng diện tích là cột Doanh thu, từ B2: B10 và kết quả cũng bằng phương pháp thứ nhất.
Nếu không muốn sử dụng hàm SUMIF, bạn có thể thay thế bằng hàm SUMIFS như sau: H3 = SUMIFS (B2: B10, A2: A10, “*” & F3 & “*”). Nội dung trong hàm này sẽ vẫn giống như hàm SUMIF, nhưng thứ tự sẽ thay đổi một chút khi vùng tổng trong Excel được đưa lên phần tử thứ nhất trong hàm tổng trong Excel.
Phương pháp 3: Sử dụng SUMPRODUCT. hàm số
Tất nhiên bạn đã từng nghe đến hàm SUMPRODUCT có thể thay thế hoàn toàn các hàm SUMIF và SUMIFS. Trong ví dụ này, chúng ta sẽ xem liệu hàm SUMPRODUCT có thể được sử dụng hay không. 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)>. IFERROR (SEARCH ( F3, $ A $ 2: $ A $ 10,0) là nội dung trong cột bổ sung mà chúng ta đã làm ở trên. Khi được đặt trong hàm SUMPRODUCT, chúng ta sẽ so sánh kết quả đó có> 0 hay không và đối với các giá trị> 0 sẽ được sử dụng để tính tổng trong excel trong cột Doanh thu.
* Lưu ý: Đối với cách viết tổng sau đây trong Excel sẽ không cho kết quả: = SUMPRODUCT (($ B $ 2: $ B $ 10) * ($ A $ 2: $ A $ 10 = ”*” & F3 & ”*”)
IV. Cách tính tổng các giá trị danh sách đã lọc trong Excel
Hướng dẫn này đượ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ư Excel 2007, Excel 2010, Excel 2013, Excel 2016, vì hướng dẫn sử dụng hàm Subtotal trong Excel. Sau khi lọc ra danh sách các sản phẩm thuộc thể loại sách, chúng ta có bảng tính như sau:
Ví dụ về bảng Excel với dữ liệu đã lọc: Yêu cầu là cách tính tổng trong Excel. Nếu bạn 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 số tiền của cuốn sách mà bạn sẽ là tổng của tất cả các sản phẩm. Trong trường hợp này, chúng ta sử dụng hàm SUBTOTAL như sau, trong ô E13, nhập = SUBTOTAL (9, H7: H11). Trong đó, 9 là giá trị đối số tương ứng với hàm cần sử dụng hàm sum trong excel, ở đây chúng ta muốn tính tổng nên hàm cần sử dụng là hàm SUM để tính tổng trong excel, các bạn có thể xem tại bảng bên dưới Dưới đây, H7: H11 là phạm vi tính tổng trong Excel.
Cách tính tổng các giá trị danh sách đã lọc trong Excel
Giới thiệu về hàm SUBTOTAL ()
Hàm SUBTOTAL () xem xét toàn bộ danh sách các giá trị trong cột D và chỉ tính những giá trị thỏa mãn bộ lọc. Tuy nhiên, đối số sum trong Excel này chỉ ra rằng chúng ta muốn tính tổng các giá trị tham chiếu trong excel. Bảng sau đây liệt kê các đối số được chấp nhận. Sau khi xem, bạn có thể vẫn còn thắc mắc sự khác biệt giữa 9 và 109. Khi chúng ta sử dụng đối số 9, hàm SUBTOTAL () sẽ tính tổng trong Excel tất cả các giá trị ẩn. Khi chúng ta sử dụng đối số 109, hàm SUBTOTAL () sẽ bỏ qua các giá trị ẩn. Vì vậy chúng ta cần phân biệt rõ ràng giá trị bị ẩn và giá trị bị loại vì 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 mà bạn có thể quan tâm, hy vọng qua bài viết này sẽ giúp bạn có những thông tin hữu ích nhất. Hy vọng bạn đang thành công.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.