Công thức và cách sử dụng của một số hàm thông dụng trong Excel có ví dụ minh họa

Phần mềm Excel là một trong những công cụ quan trọng được sử dụng phân tích, kiểm toán và tính toán dữ liệu một cách chính xác và nhanh chóng nhất bằng cách tận dụng tối đa các hàm trong Excel cung cấp. Trong bài viết hôm nay, mình sẽ chia sẻ với các bạn công thức và  cách sử dụng của một số hàm thông dụng trong Excel nhé.

1. Các hàm tính tổng trong Excel

1.1. Hàm tính tổng - Hàm SUM trong Excel

 

Hàm SUM là hàm tính tổng tất cả các số có trong dãy ô dữ liệu, hàm SUM có công thức như sau:

=SUM(number1, [number2],…)

 

Trong đó: 

  • Number1, Number2,...có thể là các số, các tham chiếu ô hoặc các giá trị số bằng công thức.

 

1.2. Hàm tính tổng có điều kiện - Hàm SUMIF

 

Hàm SUMIF là hàm tính tổng một vùng nào đó trong bảng tính excel theo điều kiện cho trước, hàm SUMIF có công thức như sau:

= SUMIF(Range, Criteria, Sum_range).

 

Trong đó: 

 

  •  Range có nghĩa là dãy số mà chúng ta muốn xác định.
  •  
  •  Criteria có nghĩa là điều kiện, tiêu chuẩn muốn tính tổng, có thể là số, biểu thực hoặc chuỗi
  •  
  • Sum_range có nghĩa là các ô thực sự cần tính tổng.

 

2. Các hàm đếm dữ liệu trong Excel

2.1. Hàm đếm dữ liệu theo điều kiện - hàm COUNTIF/COUNTIFS

 

Hàm COUNTIF là hàm đếm một điều kiện, được dùng để đếm số ô thỏa mãn điều kiện (criteria) trong một vùng dữ liệu được chọn (range) với công thức như sau:

= COUNTIF(range, criteria)

 

 Trong đó:

 

  • Range có nghĩa là vùng dữ liệu cần đếm

  •  

  • Criteria có nghĩa là điều kiện để đếm, chẳng hạn như “>80”; 200;…

 

Ví dụ: Cho bảng dữ liệu thống kê các mặt hàng và số lượng tồn tương ứng. Hãy thống kê xem có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm.

 

Chúng ta nhập công thức tại ô G2 như sau:

=COUNTIF(C2:C11,">150")

 

Sau đó, nhấn Enter và kết quả hiển thị cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.

 

 

các hàm thông dụng trong excel

 

 

Tuy nhiên hãy lưu ý rằng: Chúng ta có thể dùng các ký tự đại diện trong điều kiện: Chẳng hạn như dấu ? đại diện cho một ký tự bất kỳ "?ĐH?" hay để tìm điều kiện là những dấu ? hoặc * thì chúng ta hãy gõ thêm dấu ~ ở trước dấu ? hay * chẳng hạn như "~?", "~*". Ngoài ra, hàm COUNTIF sẽ không phân biệt chữ hoa hay chữ thường.

 

2.2. Hàm đếm với hai điều kiện trở lên - hàm COUNTIFS trong Excel

 

Hàm COUNTIFS được dùng để đếm số ô thỏa mãn nhiều điều kiện trong vùng dữ liệu được chọn với công thức như sau:

= COUNTIFS(range1, criteria1, range2, criteria2,…) 

 

Trong đó:

  • range1, range2…có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số và có tối đa 127 dãy các ô để đếm.

  •  

  • criteria1, criteria2…: có nghĩa là điều kiện để đếm.

 

Ví dụ: Cho bảng  dữ liệu thống kê các mặt hàng và số lượng tồn tương ứng. Hãy áp dụng hàm đếm xem có bao nhiêu mặt hàng tại kho 1 còn tồn trên 150 sản phẩm.

 

Chúng ta thực hiện công thức tại ô G2 như sau:

=COUNTIF(C2:C11,">150",D2:D11,"1")

 

Sau đó, chúng ta nhấn Enter và kết quả hiển thị cho thấy có tổng cộng 2 mặt hàng tại kho 1 còn tồn trên 150 sản phẩm như hình minh họa dưới đây:

 

 

các hàm thông dụng trong excel


3. Các hàm tính giá trị trung bình trong Excel

Hàm tính giá trị trung bình được sử dụng phổ biến nhất trong Excel là hàm Average với công thức như sau:

=AVERAGE(number1,[number2],…)

 

Trong đó:

 

  • number 1 là giá trị bắt buộc và nó có thể là số, tham chiếu ô hoặc dải ô để tính giá trị trung bình.
  •  
  • number 2 là giá trị tùy chọn, có tối đa 255 số và nó có thể là các số, tham chiếu ô hoặc phạm vi chứa số bổ sung để tính trung bình.

 

Ví dụ: Cho bảng dữ liệu sau. Hãy áp dụng hàm Average để tính điểm trung bình của từng học sinh dưới đây

 

 

các hàm thông dụng trong excel

 

 

Đầu tiên, chúng ta hãy chọn ô muốn tính điểm trung bình, sau đó chúng ta nhập công thức vào ô đó như sau:

=AVERAGE(B2,C2,D2)

 

 

các hàm thông dụng trong excel

 

 

Sau đó, chúng ta nhấn phím Enter trên bàn phím thì sẽ hiển thị được giá trị trung bình của điểm số cho học sinh đầu tiên như sau:

 

 

các hàm thông dụng trong excel

 

 

Tiếp theo, để tính trung bình cộng của các học sinh còn lại, chúng ta hãy di chuyển chuột đến cuối ô vừa tính được cho fill handle hiện ra, sau đó chúng ta kéo xuống các ô còn lại để copy công thức và kết quả sẽ hiển thị như sau:

 

 

các hàm thông dụng trong excel

 

4. Các hàm thống kê trong Excel

4.1. Hàm MIN - Hàm tìm giá trị nhỏ nhất trong Excel

 

Hàm MIN là hàm trả về giá trị nhỏ nhất trong tập giá trị cho trước, hàm MIN có công thức như sau:

= MIN (number 1, number 2, …)

 

Trong đó:

  • Number1, Number2, ... có nghĩa là các đối số hoặc vùng dữ liệu.

 

4.2. Hàm MAX - Hàm tìm giá trị lớn nhất trong Excel

 

Hàm MAX là hàm trả về giá trị lớn nhất trong tập giá trị cho trước, hàm MAX có công thức như sau:

=MAX(number 1, number 2, …) 

 

Trong đó:

  • Number1, Number2, ... có nghĩa là các đối số hoặc vùng dữ liệu.

 

Ví dụ: Cho trước dữ liệu ở cột Numbers. Hãy áp dụng hàm Min, Max để tìm giá trị nhỏ nhất, lớn nhất. Chúng ta nhập công thức hàm và sẽ hiển thị kết quả như hình minh họa dưới đây:

 

 

các hàm thông dụng trong excel

 

 

Trên đây là những chia sẻ của mình về một số hàm thông dụng trong Excel. Chúc các bạn thực hiện thành công nhé.




Copyright © 2017