Những hàm hay dùng nhất trong Excel

1. HÀM LOGIC

1.1. Hàm IF

Công thức =IF(điều kiện,”giá trị đúng”,”giá trị sai”).

Hàm IF giúp bạn kiểm tra xem điều kiện có được đáp ứng không, nếu đúng nó sẽ trả về giá trị đúng, nếu sai sẽ trả về giá trị sai. 

Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem ô F4 có thỏa mãn điều kiện lớn hơn 5 không, nếu thảo mãn thì trả về giá trị lên lớp trong ô G4, nếu không thỏa mãn sẽ trả về giá trị Học lại.

1.2. Hàm IF lồng nhau

Công thức =IF (điều kiện 1,”giá trị đúng 1″, IF(điều kiện 2,”giá trị đúng 2″),…IF(điều kiện thứ n, “giá trị đúng n”, “giá trị sai thứ n”)…)

Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem ô F4 có thỏa mãn điều kiện lớn hơn 8 không, nếu thảo mãn thì trả về giá trị “Học sinh gỏi”, nếu không thỏa mãn thì kiểm tra Đk 2 có lớn hơn 6.5 ko nếu có thì trả về “Học sinh tiên tiến”, nếu không thỏa mãn thì kiểm tra Đk 3 có lớn hơn 5 ko nếu có thì trả về “Học sinh TB”, nếu ko thì “Học sinh bị đúp” trong ô G4.

1.3. Hàm IFERROR

Cú pháp  IFERROR(value,value_if_error) 

Trả về giá trị do ta nhập vào khi công thức có lỗi, khi công thức không có lỗi thì trả về kết quả. Hàm này dùng để bẫy lỗi trong công thức

Value Là biểu thức cần kiểm tra lỗi
Value_if_error Là giá trị trả về khi có lỗi ở đang công thức kiểm tra. Các lỗi là #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hay #NULL!.

Ghi chú
Nếu value hoặc value_if_error rỗng, hàm IFERROR gán nó thành chuỗi rỗng (“”).
Nếu value là một biểu thức mãng, IFERROR trả về mãng kết quả cho mỗi ô trong mãng của biểu thức.

1.4. Hàm And

Công thức =And (Điều kiện 1, Điều kiện 2,…)

Hàm AND sẽ trả về giá trị đúng nếu tất cả các điều kiện được thỏa mãn và trả về giá trị sai nếu có bất kỳ điều kiện nào sai.

Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem các ô F4, C4, D4, E4 cùng lúc thỏa mãn các điều kiện thì trả về giá trị “ Học sinh xuất sắc” ở cột G4, không đúng trả về ‘ Không đạt”

1.5. Hàm OR

Công thức = OR (Điều kiện 1, Điều kiện 2,…)

Ngược lại với AND, OR trả về giá trị đúng nếu có bất kỳ điều kiện nào được thỏa mãn và trả về giá trị sai nếu tất cả các điều kiện đều không được đáp ứng.

Ở đây chỉ cần B4>8 là trả về giá trị đúng

2. HÀM TÍNH TỔNG

2.1. Hàm tính tổng SUM

Đây là hàm khá thông dụng và phổ biến cho phép ta tính tổng các ô lựa chọn bất kể số hay chữ đều cho ta kết quả là một số xác định

Cú pháp: =SUM(Number1, Number2..)

Vd tính tổng các ô từ B3-B6

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

Cú pháp: =SUMIF(Number1- NumberN,” điều kiện”)

VD tính tổng các ô từ B3-B6 với điều kiện >10 cho ta kết quả là 36

2.3. Hàm tính tổng với nhiều điều kiện SUMIFS

Cú pháp: =SUMIFS(Number1- NumberX, Number1- NumberN,” điều kiện1”, Number1- Number M, ,” điều kiện2”)

Ví dụ tính tổng cột D10, với vùng tính tổng là D3- D7, điều kiện 1 là từ cột B3-B7 phải chứa màu xanh, ô từ c3-c7 phải chứa giá trị >10

3. HÀM ĐẾM

3.1. Hàm COUNT

Cú pháp: = COUNT (Number1 : NumberX)

Bạn có một bảng tính với cơ sở dữ liệu khổng lồ và cần biết có bao nhiêu ô trong một vùng nào đó hay trong toàn bộ bảng tính chỉ chứa chữ số, không chứa chữ cái. Thay vì phải ngồi đếm một cách thủ công thì bạn có thể sử dụng hàm =COUNT. Ví dụ, bạn cần đếm từ ô C1 đến C10, hãy gõ =COUNT(C1:C10)

3.2. Hàm đếm các ô trống COUNTBLANK

Cú pháp: = COUNTBLANK (Number1 : NumberX)

Sử dụng hàm này để đếm các ô trong trong bảng tính

3.3. Hàm đếm các ô không trống COUNTA

Cú pháp: = COUNTA (Number1 : NumberX)

3.4. Hàm COUNTIF

Cú pháp: = COUNTIF (Number1 : NumberX, “Đk n”)

VD hàm đếm với điều kiện >100

3.5. Hàm COUNTIFS

Cú pháp: = COUNTIFS (Number1 : Number n, “Đk n”, Number1 : Number m, “Đk m”,  )

VD điếm tất cả các ô màu xanh, chứa giá trị lớn hơn 10

3.6. Hàm tính trung bình Average

Cú pháp: = Average (Number1 : Number n)

VD tính trung bình cộng của ba môn

3.7. Hàm tính tổng tiền SUMPRODUCT

Cú pháp: = SUMPRODUCT (A1 : An, B1 : Bn)

VD tính tổng tiền Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: (12 * 120) + (7 * 80) + (9 * 100) + (16* 2300) + (18* 150)  = 9280.

Phạm vi tính tổng phải có cùng kích thước, nếu không Excel sẽ hiển thị lỗi #VALUE!

3.8. Hàm Min, Max

Là hàm cho phép chúng ta tìm giá trị lớn nhất hoặc nhỏ nhất của một vùng dữ liệu

Cú pháp: = MIN (A1 : An) hoặc Cú pháp: = Max (A1 : An)

Ví dụ ta tìm giá trị lớn nhất của chuỗi dữ liệu

4. HÀM TRA CỨU THAM CHIẾU

4.1.Hàm Vlookup, Hlookup

Cú pháp = VLOOKUP ( Ô chứa giá trị tìm kiếm, bảng chứa giá trị tìm kiếm và trả về, cột chứa giá trị trả về)

Cú pháp = HLOOKUP (Ô chứa giá trị tìm kiếm, bảng chứa giá trị tìm kiếm và trả về, hàng chứa giá trị trả về)

VD trong ví đụ này ta thấy tại ô B15 cho ta giá trị 20% với ô chứa giá trị tìm kiếm là A12, vùng chứa giá trị tìm kiếm là A5-M10,cột chứa giá trị tìm kiếm trả về là cột thứ 2, nếu sai thì trả về giá trị là 0

4.2. Hàm match

Cú pháp = match (Giá trị tìm kiếm, vùng tìm kiếm, kết quả)

Yellow được tìm thấy ở vị trí thứ 3 trong dải E4:E7. Đối số thứ 3 là tùy chọn, bạn có thể đặt đối số này là 0 để trả về vị trí của giá trị trong ô hoặc trả về #N/A nếu không tìm thấy.

4.3. Hàm index

Cú pháp = Index( vùng tìm kiếm, hàng, cột)

Trong vd trên kết quả trả về hàng 4 cột 2 trong vùng tìm kiếm là 230

5. HÀM LÀM TRÒN SỐ

5.1. Hàm ROUND() làm tròn số sau và trước dấu phẩy

Hàm này sẽ giúp bạn làm tròn số để rút ngắn số lại hơn tùy theo số lượng chữ số cần làm tròn khá đơn giản và thong dụng

Cú pháp: = ROUND(Số cần làm tròn, num_digits)

Trong đó

  • num_digits > 0 : làm tròn đến số thập phân được chỉ định
  • num_digits = 0 : làm tròn đến số nguyên gần nhất
  • num_digits < 0 : làm tròn đến phần nguyên được chỉ định

Ví dụ: Với con số 1234.5678

  • ROUND(1234.5678, 3) = 1234.568
  • ROUND(1234.5678, 2) = 1234.57
  • ROUND(1234.5678, 1) = 1234.6
  • ROUND(1234.5678, 0) = 1235
  • ROUND(1234.5678, -1) = 1230
  • ROUND(1234.5678, -2) = 1200
  • ROUND(1234.5678, -3) = 1000

5. 2. Hàm MROUND() Làm tròn đến bội số của một số khác

Cú pháp: = MROUND(số cần làm tròn, multiple)

  • multiple: Con số mà bạn cần làm tròn number đến bội số của nó

– Nếu number và multiple khác dấu, hàm sẽ báo lỗi #NUM!
– Nếu number và multiple bằng nhau, kết quả là chính số đó
– MROUND() sẽ làm tròn lên, nếu phần chia của phép chia number cho multiple lớn hơn hoặc bằng 1/2 multiple, và làm tròn xuống nếu phần chia của phép chia number cho multiple nhỏ hơn 1/2 multiple

Ví dụ:

  • MROUND(5, 2) = 6 (do 5/2 > 2/2, bội số của 2 gần nhất mà lớn hơn 5 là 6)
  • MROUND(11, 5) = 10 (do 11/5 < 5/2, bội số của 5 gần nhất mà nhỏ hơn 11 là 10)
  • MROUND(13, 5) = 15 (do 13/5 > 5/2, bội số của 5 gần nhất mà lớn hơn 13 là 15)
  • MROUND(5, 5) = 5 (number và multiple bằng nhau)
  • MROUND(7.31, 0.5) = 7.5 (do 7.31/0.5 > 0.5/2, bội số của 0.5 gần nhất mà lớn hơn 7.31 là 7.5)
  • MROUND(-11, -5) = -10 (do -11/-5 > -5/2, bội số của -5 gần nhất mà lớn hơn -11 là -10)
  • MROUND(-11, 5) = #NUM! (number và multiple khác dấu)

5.3. Hàm ROUNDDOWN() và Hàm ROUNDUP()

Hai hàm này, về cơ bản thì giống hàm ROUND(), chỉ khác là chúng chỉ làm tròn theo một chiều: ROUNDDONW() luôn luôn làm tròn một số về số 0, còn ROUNDUP() thì luôn luôn làm tròn một số ra xa số 0.
Cú pháp:

= ROUNDDOWN(Số cần làm tròn, num_digits)
= ROUNDUP(number, num_digits)

  • num_digits: Là một số nguyên, chỉ cách mà bạn muốn làm tròn
  • num_digits > 0 : làm tròn đến số thập phân được chỉ định
  • num_digits = 0 : làm tròn đến số nguyên gần nhất
  • num_digits < 0 : làm tròn đến phần nguyên được chỉ định

Ví dụ: So sánh giữa ROUNDDOWN() và ROUNDUP()

5.4. Hàm CEILING() và Hàm FLOOR()

Hai hàm này, hao hao giống hàm MROUND(), là làm tròn đến bội số gần nhất của một số được chỉ định, chỉ khác đôi chút về cách tính: CEILING() luôn luôn làm tròn một số ra xa số 0, còn FLOOR() làm tròn về số 0.
Cú pháp:

= CEILING(Số cần làm tròn, significance)
= FLOOR(number, significance)

significance: Con số mà bạn cần làm tròn number đến bội số của nó

– Nếu number và significancekhác dấu, hàm sẽ báo lỗi #NUM!
– Nếu number là bội số của significance, kết quả là chính số đó

Ví dụ: So sánh giữa CEILING(), FLOOR() và MROUND() – ở ví dụ này, xem như significance là multiple của MROUND()

5.5. Hàm EVEN() và Hàm ODD()

Hai hàm này làm tròn rất đơn giản. EVEN() làm tròn đến số nguyên chẵn gần nhất, còn ODD() làm tròn đến số nguyên lẻ gần nhất. Cả hai đều làm tròn theo kiểu chạy xa khỏi số 0.
Cú pháp: = EVEN(number) | = ODD(number)

Ví dụ:

  • EVEN(14.2) = 16
  • EVEN(-23) = 24
  • ODD(58.1) = 59
  • ODD(-6) = -7

5.6. Hàm INT() và hàm TRUNC()

Hai hàm này gần như giống nhau nếu như bạn muốn làm tròn một số thành một số nguyên.

Cú pháp:

= INT(number)

= TRUNC(number [, num_digits])

  • num_digits: Là một số nguyên, chỉ cách mà bạn muốn cắt bớt số

num_digits > 0 : nếu number là một số thập phân, thì num_digits chỉ ra số con số thập phân mà bạn muốn giữ lại (sau dấu phẩy)

num_digits = 0 hoặc không nhập: cắt bỏ hết phần thập phân của number (nếu có)

num_digits < 0 : làm tròn number thành một số nguyên và làm tròn number sang trái thành một bội số của 10 (xem thêm ở ví dụ)

Hàm INT() làm tròn một số tới số nguyên gần nhất

Đối với số dương, hàm INT() và TRUNC() cho kết quả giống nhau (num_digits của TRUNC() = 0 hoặc không có), nhưng đối với số âm thì hai hàm này sẽ cho kết quả hoàn toàn khác nhau.

Ví dụ:

  • INT( 123.456) = 123 | TRUNC( 123.456) = 123
  • INT(-123.456) = -124 | TRUNC(-123.456) = -123

Khi num_digits khác 0, TRUNC() khác hẳn với ROUND() ở chỗ: ROUND() thì làm tròn, còn TRUNC() chỉ cắt bỏ bớt số chứ không làm tròn.

Một số ví dụ về hàm TRUNC()

6. HÀM CHUỖI VĂN BẢN

6.1. Hàm LEFT

Cú pháp = Left ( Ô chứa chuỗi cần lấy, số ký tự muốn lấy)

VD trên ta thấy ô chứa giá trị muốn lấy là B12, số ký tự muốn lấy là 8

6.2. Hàm RIGHT

Cú pháp = Left (Ô chứa chuỗi cần lấy, số ký tự muốn lấy)

Dùng để lấy những ký tự từ bên phải màn hình

VD trên ta thấy ô chứa giá trị muốn lấy là B12, số ký tự muốn lấy là 9 từ phía bên phải

6.2. Hàm MID

Cú pháp = MID (Ô chứa chuỗi cần lấy, vị trí lấy, số ký tự cần lấy)

VD trên ta thấy ô chứa giá trị muốn lấy là B12, vị trí lấy là bắt đầu từ ký tự thứ 9, sô ký tự muốn lấy là 5)

6.3. Hàm Len

Cú pháp = LEN (Ô chứa chuỗi cần đến)

VD trên ta thấy ô chứa giá trị muốn đến là B12

6.4. Hàm FIND

Cú pháp = FIND (“chuỗi cần tìm”, ô chứa giá trị chuỗi)

Dùng để tìm vị trí của chuỗi con trong một tập hợp chuỗi

6.5. Hàm TRIM

Dùng để cắt khoảng trống trong một đoạn văn bản

Cú pháp = TRIM (chuỗi cần chỉnh)

6.6. Hàm chuyển đổi chữ Hoa thành chữ thường hoặc ngược lại  LOWER, UPPER, PROPER

6.7. Hàm nối chuối ký tự CONCATENATE

6.8. Nối chuỗi văn bản

Để nối chuỗi văn bản bạn sử dụng ký tự &, muốn chèn thêm dấu cách thì sử dụng ” ” (mở ngoặc kép, cách, đóng ngoặc kép).

6.9. Hàm hiển thị thời gian

  • Nếu muốn điền thời gian hiện tại vào một ô nào đó trong bảng tính Excel, hãy sử dụng hàm =NOW. Trước hết, hãy di chuyển con trỏ chuột đến một ô bất kỳ rồi chỉ cần gõ cú pháp =NOW(). Điểm tiện lợi của hàm này là không yêu cầu bất kỳ tham số nào bên trong dấu ngoặc. Kết quả trả về chính là thời gian hiện tại trên hệ thống máy tính

Chú ý: Nội dung trong các bài viết chỉ có tính chất tham khảo. Độc giả vui lòng tham khảo ý kiến của bác sĩ trước bất cứ áp dụng nào! Vui lòng liên hệ để gỡ bài nếu bạn thấy sai và không đúng! Blog xin chân thành cảm ơn!