Tổng hợp các biến thể của hàm SUM trong Excel nâng cao

0
121

Excel là một công cụ mạnh mẽ với số lượng hàm và biến thể vô cùng đa dạng. Với những người lành nghề, việc sử dụng các biến thể và hàm Excel nâng cao sẽ tiết kiệm được rất nhiều thời gian và giúp công việc của bạn đơn giản hơn.

Sum là một hàm rất phổ biến và đơn giản, đây là một trong những hàm đầu tiên bạn sẽ học khi tiếp xúc với Excel. Chức năng của sum chỉ đơn thuần là cộng tổng tất cả các giá trị được chọn. Tuy nhiên, hàm sum còn rất nhiều biến thể rất hữu ích giúp bạn tính toán được những tình huống phức tạp hơn. Hãy cùng điểm qua những biến thể nâng cao của hàm sum trong Excel.

1/ SUMIF

Hàm SUMIF cho phép bạn cộng tổng các giá trị được chọn nếu nó thỏa mãn một điều kiện nhất định. 

Công thức: SUMIF(range, criteria, [sum_range])

  • Range (phạm vi): là giá trị bắt buộc phải có. Đây là vùng sẽ được tính tổng nếu thỏa mãn điều kiện. Các ô trong vùng được chọn phải ở dụng số, tên, mảng hoặc giá trị tham chiếu chứa số. Những ô trống hoặc chứ dữ liệu dạng văn bản sẽ bị bỏ qua.
  • Criteria(tiêu chí): là giá trị bắt buộc. Criteria có thể ở dạng số, biểu thức, tham chiếu ô, văn bản (dạng văn bản phải được điền trong dấu “ ”) hoặc một công thức để xác định ô những ô nào sẽ được cộng. 
  • Sum_range: không bắt buộc. Biến này được dùng nếu bạn muốn cộng các ô không nằm trong các ô đã chọn trong mục range. Nếu sum_range bị bỏ trống, Excel sẽ cộng các ô trong mục range.

*** Một số lưu ý:

  • Bạn có thể thêm dấu chấm hỏi (?) trước giá trị của biến Criteria để dữ liệu giống với bất kỳ ký tự đơn nào. Ví dụ, nếu nhập Ph?ng Excel sẽ tìm các dữ liệu như “Phong”, “Phang”,….Hoặc dùng dấu sao (*) để chọn những giá trị gần giống, ví dụ: nếu nhập *ng Excel sẽ tìm “Phương”, “Long”, “Trung”,… Trường hợp nếu bạn cần tìm một dấu chấm hỏi hoặc dấu hoa thị thêm dấu ~ trước đó. Ví dụ nếu nhập A1 ~? Excel sẽ tìm giá trị “A1?”
  • Hàm SUMIF sẽ báo lỗi nếu bạn dùng nó để nối chuỗi dài hơn 255 kí tự
  • Biến sum_range không cần phải giống chính xác kích thước và vùng của biến range. Excel sẽ tự động xác định ô ngoài cùng bên trái của biến sum_range làm ô đầu tiên, và sẽ bao gồm các ô tương ứng của biến range.

2/ SUMIFS

Hàm SUMIFS giúp bạn cộng tổng vùng được chọn nếu thỏa mãn nhiều điều kiện

Công thức: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

  • Sum_range (bắt buộc): là phạm vi các ô sẽ được cộng tổng
  • Criteria_range1(bắt buộc): là vùng xác định điều kiện 1. Đây là vùng sẽ dò tìm điều kiện criteria1, nếu đúng sẽ cộng giá trị của ô tương ứng trong vùng sum_range.
  • Criteria1 (bắt buộc): là biến điều kiền thứ nhất.
  • criteria_range2, criteria2 (tùy chọn): bạn có thể thêm nhiều biến điều kiện, tối đa 127 biến.

Ví dụ: nếu bạn cần cộng tổng giá trị cột số lượng (Qty) thỏa mãn 2 điều kiện Product là “apples” và Supplier là “Pete”, công thức SUMIFS sẽ được viết như sau:

hàm sumifs trong excel nâng cao

3/ SUMPRODUCT

SUMPRODUCT là hàm cho phép nhân các giá trị tương ứng trong mảng và cộng tổng các tích số.

Công thức: SUMPRODUCT(array1, [array2], [array3],…)

  • Array1(bắt buộc): mảng đối số đầu tiên bạn muốn nhân và cộng tích số.
  • Array2, array3,…(tùy chọn): các mảng đối số (tối đa 255) bạn muốn nhân và cộng tích số

*** Lưu ý:

  • Các mãng đổi số bắt buộc phải có cùng kích thước, nếu không sẽ trả về lôi #VALUE!

hàm sumproduct trong excel nâng cao

  • Nếu các giá trị trong mảng được chọn không ở định dạng số, Excel sẽ hiểu giá trị ở ô đó là 0

lưu ý hàm sumproduct

Ví dụ: nếu bạn muốn tính tổng giá tiền bằng cách cộng tích số của các giá trị tương ứng cột số lượng (Quantity) và giá (Price)

ví dụ hàm sumproduct

4/ DSUM

Hàm DSUM có thể cộng các giá trị trong cột hoặc cơ sở dữ liệu nếu thỏa một điều kiện xác định.

Công thức: DSUM(database, field, criteria)

  • Database (bắt buộc): đây là cơ sở dữ liệu để đối chiếu với điều kiện và cộng các giá trị tương ứng. Hàng đầu tiên phải chứa các tiêu đề cho mỗi cột.
  • Field (bắt buộc): chỉ cột sẽ được sử dụng trong hàm. Nhập tiêu đề cột trong dấu ngoặc kép “ “.
  • Criteria (bắt buộc): là phạm vi ô chức điều kiện cần xác định.

*** Lưu ý:

  • Bạn có thể sử dụng bất cứ phạm vi nào cho biến criteria, miễn là nó chứa 1 tiêu đề cột và ít nhất 1 ô.
  • Đảm bảo phạm vi của biến criteria không chồng lên danh sách
  • Không nên đặt phạm vi của criteria dưới danh sách. Vì nếu trường hợp bạn thêm thông tin vào danh sách, thông tin mới sẽ được thêm vào dòng đầu tiên. Nếu hàng phía dưới của danh sách không rỗng, Excel sẽ không thể thêm thông tin mới vào danh sách.

Ví dụ: để cộng tổng doanh số bán (Sales) của sản phẩm màu xanh (Blue). Ta viết hàm như sau

hàm dsum trong excel nâng cao

5/ SUMSQ

Hàm SUMSQ trả về tổng giá trị của bình phương các đối số.

Công thức: SUMSQ(number1, [number2], …)

  • Number1, number2,…: number1 là biến bắt buộc, các biến số sau là tùy chọn (tối đa 255). 

*** Lưu ý:

  • Các đối số có thể là tên, mảng, hoặc tham chiếu số
  • Nếu đối số được chọn là một mảng, chỉ có giá trị số và tham chiếu sẽ được chọn, còn lại sẽ bị bỏ qua.
  • Nếu đối số được chọn là giá trị lỗi hoặc văn bản không thể chuyển đổi thành số, hàm sẽ báo lỗi.

Ví dụ: để tính tổng bình phương củ giá trị B2 và C2, ta viết công thức như sau

hàm sumsq trong excel nâng cao

Tổng kết

Excel là một phần mềm với đa dạng các hàm và rất nhiều các biến thể. Nếu được sử dụng hiệu quả, các hàm nâng cao này sẽ giúp bạn cải thiện hiệu suất công việc cao hơn rất nhiều lần so với những người chỉ sử dụng Excel thông thường. Hãy khai phá sức mạnh tìm ẩn của Excel ngay hôm nay với khóa học “Excel Và Ứng Dụng Trong Thực Tiễn Mircrosoft Excel 2013 Nâng Cao” từ VietnamWorks Learning để tạo ra sự khác biệt cho mình.

TRẢ LỜI

Please enter your comment!
Please enter your name here