Tổng hợp các hàm trong Excel được sử dụng thường xuyên nhất

Photo of author
Đoàn Trang
Đăng ngày:

Làm việc với các hàm cơ bản trong Excel là việc rất thường xuyên khi sử dụng Excel. Các hàm Excel là những công thức được định sẵn trước đó vì thế chúng tiện hơn các biểu thức dài dòng. Hãy chú ý ghi nhớ các hàm Excel sau đây nhé.

I. Hàm tính toán Logic

1. Hàm SUM

Hàm SUM dùng để thực hiện tính tổng tất cả các số trong dãy đã chọn. Công thức thực hiện:

=SUM(Number1,Number2,Number3)

Trong đó:

Number1,Number2,Number3: là giá trị các số hạng cần tính tổng.

Kết quả của hàm SUM là tổng cộng các giá trị đã lựa chọn.

Ví dụ sau đây: =SUM(20,40,60) có kết quả trả ra là tổng cộng của 20, 40 và 60.

=SUM(20,40,60) có kết quả trả ra là tổng cộng của 20, 40 và 60.
=SUM(20,40,60) có kết quả trả ra là tổng cộng của 20, 40 và 60.

Ví dụ: Tính tổng các dãy số =SUM(A1:A3) cho ra kết quả là 120.

Tính tổng các dãy số =SUM(A1:A3) cho ra kết quả là 120.
Tính tổng các dãy số =SUM(A1:A3) cho ra kết quả là 120.

2. Hàm MIN/MAX

Hàm MIN/MAX dùng để tìm ra giá trị nhỏ nhất hoặc lớn nhất trong bảng tính hoặc vùng dữ liệu.

Cú pháp thực hiện như sau:

=MIN(Number1,Number2,...)

Với:

Number1,Number2: Các giá trị cần so sánh

Kết quả sẽ đưa ra giá trị nhỏ nhất trong vùng dữ liệu.

Ví dụ: Giá trị nhỏ nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MIN(A2:C5) và nhận được kết quả 25 là giá trị nhỏ nhất cần tìm.

Hàm Min
Hàm Min

Cú pháp:

=MAX(Number1,Number2,...)

Với:

Number1,Number2: các giá trị cần so sánh.

Kết quả sẽ đưa ra giá trị lớn nhất trong vùng dữ liệu.

Ví dụ: Tìm giá trị lớn nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MAX(A2:C5) kết quả đạt được là 89 là giá trị lớn nhất cần tìm.

Hàm max
Hàm max

3. Hàm COUNT/COUNTA

Hàm COUNT được sử dụng để đếm số lượng ô  chứa số trong một phạm vi dữ liệu.

Cú pháp:

=COUNT(giá trị 1, ...)

Khi nào:

Value1, …: tham chiếu đến ô hoặc dải ô bạn muốn đếm.

Ví dụ: Tìm số lượng mà giá trị trong ô của vùng dữ liệu là số, ta có công thức cho ví dụ này là =COUNT(A2:C5). Kết quả đạt được là tổng số lượng ô có chứa số.

=COUNT(A2:C5)
=COUNT(A2:C5)

Hàm COUNTA được sử dụng để đếm các ô không trống trong phạm vi dữ liệu được chỉ định.

Cú pháp:

=COUNTA(giá trị 1, ...)

Trong đó:

Value1, …: Các ô cần đếm hoặc các vùng cần đếm. Số ô tối đa có thể đếm được là 255 (đối với Excel  2007 trở lên), tối đa là 30 (đối với Excel  2003 trở về trước).

Ví dụ: Đếm số lượng các số trong bảng dưới đây cho kết quả là công thức = COUNTA (A2:C5). Kết quả  là số  ô  chứa chữ cái hoặc số.

Hàm COUNT/COUNTA
Hàm COUNT/COUNTA

4. Hàm ODD/EVEN

Hàm ODD là hàm trả về một số được làm tròn đến số nguyên lẻ gần nhất.

Cú pháp:

=ODD(number)

Trong đó:

Number: Một giá trị để làm tròn là bắt buộc

Ví dụ: 4,6 làm tròn  đến số nguyên lẻ gần nhất bằng 5.

Làm tròn số
Làm tròn số

Hàm EVEN  trả về một số được làm tròn đến số nguyên chẵn gần nhất.

Cú pháp:

=EVEN (number)

Khi nào:

Number: Một giá trị để làm tròn là bắt buộc

Ví dụ: 53,4 làm tròn  đến số  chẵn gần nhất  là 54.

Làm tròn số
Làm tròn số

5. Hàm AVERAGE

Hàm AVERAGE được sử dụng để tính giá trị trung bình  của một tập hợp số trong trang tính của bạn.

Cú pháp:

=AVERAGE (số 1, số 2, ...)

Trong đó:

  • Number1 (bắt buộc): Số đầu tiên, tham chiếu ô hoặc phạm vi trung bình. 
  • Number2, … (Tùy chọn): Số bổ sung, tham chiếu ô hoặc phạm vi trung bình (tối đa 255).

Ví dụ: Tính lương trung bình theo thông tin bên dưới, ta có công thức =AVERAGE(C3:C8) có kết quả nhận được là giá trị trung bình của 6 tháng lương.

Tính tổng trung bình cộng
Tính tổng trung bình cộng

II. Hàm điều kiện Logic

1. Hàm COUNTIF

Hàm COUNTIF được sử dụng để đếm các ô đáp ứng một điều kiện trong phạm vi dữ liệu đã chọn. Với cú pháp:

=COUNTIF(range,criteria)

Trong đó:

  • Range: Phạm vi dữ liệu cần đếm.
  • Criteria: Điều kiện để đếm.

Ví dụ: Tôi có một danh sách các mặt hàng và số lượng trong kho như hình bên dưới. Nhập biểu thức để tính số mặt hàng có sẵn cho 150 sản phẩm.

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

Kết quả trả về cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.

Hàm COUNTIF
Hàm COUNTIF

2. Hàm IF

Hàm IF được sử dụng để kiểm tra xem dữ liệu có đáp ứng điều kiện do người dùng xác định hay không và trả về kết quả theo biểu thức logic đúng hay sai. Với cú pháp:

=IF(Logical_test;Value_if_true;Value_if_false)

Trong đó:

  • logic_test: Điều kiện.  
  • Value_if_true: Trả về giá trị nếu  điều kiện được đáp ứng.
  • Value_if_false: Giá trị trả về nếu điều kiện không được đáp ứng.

Lưu ý: Nếu để trống Value_if_true và Value_if_false và đáp ứng điều kiện, giá trị trả về là 0, nếu không  giá trị trả về  là FALSE.

Ví dụ: Hãy tưởng tượng một sinh viên đã đậu một môn học  với các điều kiện sau:

  • 7 điểm trở lên: Đạt
  • Điểm dưới 7: Không đạt

Ô D2 có công thức =IF(C2>=7,”Đạt”,”Không Đạt”) cho kết quả như hình dưới đây.

Cách dùng hàm IF trong Excel
Cách dùng hàm IF trong Excel

3. Hàm SUMIF

Hàm SUMIF được sử dụng để tính tổng các giá trị trong một phạm vi đáp ứng các tiêu chí nhất định.

Cú pháp:

=SUMIF(range,criteria,[sum_range])

Trong đó:

  • Range: Phạm vi ô để đánh giá dựa trên tiêu chí.
  • Criteria: Tiêu chí ở dạng số, công thức, tham chiếu ô, văn bản hoặc hàm xác định  ô nào cần thêm.
  • sum_range: Các ô thực tế cần thêm nếu thêm các ô khác với các ô được chỉ định trong đối số dải ô. Nếu bạn bỏ qua đối số sum_range, các ô được chỉ định bởi đối số dải ô sẽ được thêm vào.

Ví dụ: Tính tổng số quả cam ở cột A và số quả ở cột B bằng công thức = SUMIF (A1: A8, “Orange”, B1: B8).

Tính tổng điều kiện
Tính tổng điều kiện

III. Hàm sử dụng trong văn bản

1. Hàm LEFT/RIGHT/MID

Hàm LEFT dùng để cắt bớt phần bên trái của chuỗi văn bản do người dùng chọn.

Cú pháp:

=LEFT(text,[num_chars])

Khi nào:

  • Text: một chuỗi văn bản chứa các ký tự cần trích xuất.
  • Num_chars: số ký tự mà hàm LEFT sẽ trích xuất.

Ví dụ: Bảng sau sử dụng hàm LEFT để tìm ba ký tự đầu tiên trong ô B2. Nhập công thức = LEFT (B3,3) và nhấn Enter để xem kết quả.

Hàm sử dụng trong văn bản
Hàm sử dụng trong văn bản

Hàm RIGHT dùng để tách chuỗi  từ phía bên phải thành các chuỗi mà người dùng chọn.

Cú pháp:

=RIGHT(text,[num_chars])

Trong đó:

  • Text: một chuỗi văn bản chứa các ký tự cần trích xuất
  • Num_chars: Số ký tự mà hàm RIGHT trích xuất.

Ví dụ: Trong bảng sau, chúng ta sử dụng hàm RIGHT để tìm 7 ký tự cuối cùng trong ô B2. Nhập công thức = RIGHT (B3,7) và nhấn Enter để xem kết quả.

Hàm sử dụng trong văn bản
Hàm sử dụng trong văn bản

Hàm MID trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu từ một vị trí xác định và dựa trên một số ký tự đã chỉ định. cú pháp:

=MID(text, start_num, num_chars)

Khi nào:

  • Text: Một chuỗi văn bản chứa các ký tự cần trích xuất. start_num: vị trí của ký tự đầu tiên được trích xuất trong văn bản
  • Num_chars: bắt buộc cho giữa. Chỉ định số ký tự mà hàm MID trả về từ văn bản. 

Ví dụ: Trong bảng sau, chúng ta sử dụng hàm MID để tìm 7 ký tự  bắt đầu từ vị trí 3 trong ô B2. Nhập công thức = LEFT (B3,3,7) rồi nhấn Enter để nhận kết quả “tại van”.

Hàm MID
Hàm MID

2. Hàm CONCAT

Hàm CONCAT được sử dụng để nối hai hoặc nhiều chuỗi  thành một chuỗi.

Cú pháp:

=IF(Logical_test;Value_if_true;Value_if_false)

Trong đó:

  • Logic_test: Điều kiện.
  • Value_if_true: Trả về giá trị nếu  điều kiện được đáp ứng
  • Value_if_false: Giá trị trả về nếu điều kiện không được đáp ứng.

Lưu ý: Nếu để trống Value_if_true và Value_if_false và đáp ứng điều kiện, giá trị trả về  là 0, nếu không  giá trị trả về  là FALSE.

IV. Hàm ngày tháng

1. Hàm NOW

Hàm NOW được sử dụng để hiển thị ngày và giờ hiện tại của hệ thống  hoặc để tính toán một giá trị dựa trên ngày và giờ hiện tại và cập nhật nó mỗi khi  mở lại trang tính.

Với cú pháp:

=NOW()

Ví dụ:

  • = NOW (): Trả về ngày và giờ hiện tại của hệ thống. 
  • = NOW () + 7: Trả về ngày và giờ trước 7 ngày

2. Hàm DATE

Hàm DATE trả về một kết quả số cho một ngày cụ thể.

Cú pháp:

=DATE(Year,Month,Day)

Trong đó:

  • Year: Chỉ năm. Excel diễn giải tham số năm theo hệ thống ngày tháng được thiết lập trên máy tính của bạn.
  • Month: Cho biết tháng. Một số nguyên đại diện cho tháng trong năm, từ tháng Giêng đến tháng Mười Hai.
  • Day: Cho biết ngày. Một số nguyên dương tương ứng với ngày trong tháng.

Ví dụ: = DATE (2015,5,20) trả về ngày 20 tháng 5 năm 2015.

V. Hàm tra cứu dữ liệu

1. Hàm VLOOKUP

Sử dụng hàm Vlookup khi cần tìm kiếm dữ liệu trong bảng, theo dãy dọc và trả về dữ liệu tương ứng theo hàng ngang.

Cú pháp:

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

Với:

  • Lookup_value: Giá trị cần tra cứu, nhập trực tiếp giá trị hoặc tham chiếu đến một ô trên bảng tính.
  • Table_array: Giới hạn các bảng để tìm kiếm.
  • col_index_num: Lấy số cột của dữ liệu trong bảng cần tìm, từ trái sang phải.
  • Range_lookup: Tìm kiếm chính xác hoặc tương đối cho bảng giới hạn, nếu bị bỏ qua, mặc định là 1.

Nếu Range_lookup = 1 (TRUE): Tra cứu tương đối.

Nếu Range_lookup = 0 (FALSE): Tra cứu chính xác.

Ví dụ: Xác định mức phụ cấp cho nhân viên. Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$H$3:$I$8,2,0)

Mô tả công thức (theo thứ tự của các tham số trong công thức từ trái sang phải):

  • Biểu tượng $ được sử dụng để cố định các hàng và cột của Bảng 2 khi công thức được sao chép sang các ô khác.
  • 2 là số thứ tự của cột dữ liệu.
  • Range_lookup = 0 (FALSE) để phát hiện chính xác.
Hàm VLOOKUP
Hàm VLOOKUP

2. Hàm INDEX

Hàm INDEX là một hàm trả về giá trị hoặc tham chiếu đến giá trị trong bảng hoặc dải ô.

Cú pháp: 

=INDEX(array, row_num, column_num)

Với:

  • Array: phạm vi ô hoặc hằng số mảng. Nếu mảng chỉ chứa một hàng hoặc cột, thì row_num hoặc column_num tương ứng là tùy chọn. Nếu mảng có nhiều hàng và nhiều hơn một cột và bạn chỉ khai báo một trong 2 tham số row_num hoặc column_num, thì hàm trả về một mảng gồm tất cả các hàng hoặc cột trong mảng.
  • Row_num: Thứ tự của các hàng trong mảng chứa giá trị trả về. Đây là tham số bắt buộc trừ khi bạn khai báo column_num. Nếu row_num bị bỏ qua, thì column_num cần được khai báo.
  • Column_num: Thứ tự của các cột trong mảng chứa giá trị trả về. Nếu column_num bị bỏ qua, thì row_num cần được khai báo.

Ví dụ: Bây giờ giả sử bạn cần tìm giá trị của phần tử trong hàng 4, cột 1 của một mảng. Mảng ở đây bao gồm 10 hàng và 4 cột (địa chỉ B4: E13). Bạn nhập công thức: = INDEX(B4:E13,4,1).

Kết quả hiện ra
Kết quả hiện ra

3. Hàm MATCH

Hàm MATCH là một hàm tìm kiếm xác định vị trí tương đối của một giá trị trong một mảng hoặc ô.

Với cú pháp:

=Match(Lookup_value, Lookup_array, Match_type)

Trong đó:

Lookup_value: Giá trị bạn muốn tìm kiếm.

Lookup_array: Mảng hoặc dải ô cần tra cứu Lookup_value. Dải dữ liệu bạn chọn chỉ có thể có 1 hàng hoặc 1 cột.

Match_type: Chỉ định kiểu so khớp là -1; 0 và 1.

  • Loại đối sánh 0: Trả về vị trí tương đối của giá trị nếu phạm vi dữ liệu không được sắp xếp.
  • Loại so khớp 1: Trả về vị trí nhỏ hơn hoặc bằng giá trị lớn nhất của giá trị tìm kiếm. Giả sử phạm vi dữ liệu được sắp xếp theo thứ tự tăng dần.
  • Kiểu so khớp -1: Trả về vị trí của giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tìm kiếm. Giả sử phạm vi dữ liệu được sắp xếp theo thứ tự giảm dần.

Ví dụ: Dưới đây là thông tin hồ sơ thi tuyển của các học sinh và bạn đang muốn tìm vị trí của học sinh Lâm Thiên Trang.Tại ô G3 ta thực hiện công thức: =MATCH(G1,A2:A9,0)

Tìm vị trí hàm Match
Tìm vị trí hàm Match

4. Hàm HLOOKUP

Tương tự với VLOOKUP, với cú pháp:

=HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)

Với:

  • Lookup_value: Giá trị cần so sánh với hàng đầu tiên của bảng tham chiếu để lấy giá trị cần tra cứu.
  • table_array: là địa chỉ tuyệt đối của bảng tham chiếu, không có tiêu đề cột.
  • Row_index_Num: Số hàng (từ trên xuống dưới, dựa trên 1) chứa giá trị cần truy xuất từ ​​bảng tham chiếu.

Range_lookup: Cách tìm kiếm bảng tham chiếu.

  • “+0”: Hàng đầu tiên của bảng tham chiếu chưa được sắp xếp.
  • “+1”: Hàng đầu tiên của bảng tham chiếu được sắp xếp theo thứ tự tăng dần (mặc định).

Trên đây là toàn bộ các hàm cơ bản được sử dụng trong Excel. Việc hiểu và biết cách sử dụng các hàm này sẽ giúp tiết kiệm thời gian, đem lại nhiều lợi ích cho các bạn trong công việc. Chúc các bạn có thể sử dụng thành thạo các hàm này nhé.