Với dân văn phòng, thành thạo các hàm trong Excel là một lợi thế lớn trong công việc. Excel trang bị cho người dùng những bộ công cụ có nhiều chức năng xử lý thông tin chính xác và nhanh chóng. Là một kế toán hay nhân viên hành chính nhân sự, bạn đã nắm được tất cả các hàm trong Excel chưa? Bạn đang muốn học thêm về các hàm trong Excel, học Excel cơ bản, … Hãy theo dõi bài viết dưới đây của Office Whyvn để cập nhật một số các hàm trong Excel ngay bạn nhé.

1. Hàm đếm và tính tổng

1.1. Hàm đếm COUNT

COUNT là hàm đếm cơ bản trong các hàm trong Excel được sử dụng thường xuyên và phổ biến. Hàm đếm COUNT được sử dụng khi bạn có một trang tính với những cơ sở dữ liệu lớn và bạn cần thống kê được trong trang tính đó có chưa bao nhiêu đối tượng bạn muốn đếm. Thay vì bạn phải ngồi đếm số lượng ô trong bảng tính bằng cách thủ công.

Ví dụ: Bạn cần đếm số lượng ô từ ô D5 đến ô D14, bạn có thể sử dụng hàm COUNT, nhập trên bảng tính =COUNT(D5:D14)

1.2. Hàm đếm với điều kiện cụ thể COUNTIF

Hàm đếm COUNTIF với điều kiện vùng chọn cụ thể. Hàm COUNTIF là hàm Excel trong các hàm trong Excel cũng có chức năng đếm nhưng có kèm theo điều kiện cụ thể. Bạn có thể đếm được số lượng ô có chứa điều kiện trong vùng chọn.

Giải thích cú pháp:

• Vùng đếm: vùng đếm có nghĩa là các ô có chứa dữ liệu liên tiếp. Vùng đếm có thể chứa các đối tượng là các số, mảng phạm vi có chứa số. Trong vùng đếm, các ô có giá trị trống (những ô không có dữ liệu) hoặc những ô có giá trị được biểu thị bằng chữ được bỏ qua.
• Điều kiện: Đây là điều kiện để đếm dữ liệu. Điều kiện có thể là số hoặc biểu thức, hoặc những điều kiện tham chiếu, những chuỗi văn bản để xác định bạn sẽ đếm ô nào. Nếu điều kiện là chuỗi văn bản, bạn phải đặt điều kiện trong ngoặc kép, ví dụ như “điều kiện”

Vùng đếm và điều kiện là 2 giá trị bắt buộc trong hàm COUNTIF

Lưu ý khi sử dụng hàm COUNTIF:

• Hàm COUNTIF sẽ cho kết quả sai khi bạn dùng nó để khớp chuỗi dài quá 255 ký tự. Nếu như chuỗi dài quá 255 ký tự, bạn nên chuyển sang dùng hàm CONCATENATE hoặc có thể sử dụng ghép nối “&”. Cú pháp ghép nối: =COUNTIF(A1:A9,“chuỗi dài”&”chuỗi dài 2”)
• Đảm bảo điều kiện được ghi trong dấu ngoặc để xuất được kết quả chính xác nhất.
• Với công thức hàm COUNTIF sử dụng tham chiếu đến 1 ô hay 1 vùng chọn trong bảng tính đóng sẽ cho kết quả #VALUE. Khắc phục lỗi bằng cách bạn hãy mở bảng tham chiếu lên.
• Điều kiện sử dụng trong hàm không phân biệt chữ thường và chữ hoa
• Hàm có thể xuất ra giá trị không chính xác khi các giá trị văn bản có chứa khoảng trắng ở cuối và đầu hoặc các trích dẫn của bạn chưa thống nhất.

 

Ví dụ: Chúng ta có bảng dữ liệu sau và yêu cầu thống kê số lượng nam trong bảng

Ta sử dụng cú pháp hàm COUNTIF như sau: =COUNTIF(I8:I23,“Nam”) Trong đó, I8:I23 là vùng chọn, “Nam” là điều kiện đếm.

1.3. Hàm đếm COUNTIFS với nhiều điều kiện

Sử dụng hàm COUNTIFS khi có nhiều điều kiện. Khi muốn xuất ra kết quả với hai hay nhiều điều kiện cụ thể, bạn có thể sử dụng hàm đếm COUNTIFS

Ví dụ bạn phải xuất ra kết quả số lượng sinh viên nữ ở lớp QLHDTTVH37A2. Bạn sử dụng hàm COUNTIFS với cú pháp =COUNTIFS(I8:I23,”Nữ”,J8:J23,”QLHDTTVH37A2″)

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

Hàm tính tổng SUM là hàm Excel cơ bản mà bất kỳ ai học Excel cũng đều phải biết. Hàm SUM là hàm tính tổng của một dòng hay một cột tùy thuộc vào cách thức của người sử dụng.

Nếu là vùng chọn bạn có thể sử dụng cú pháp =SUM(ô đầu tiên:ô cuối cùng)

Những giá trị được sử dụng trong hàm SUM là các giá trị liên quan đến số liệu, ngày tháng.

Giá trị logic và giá trị văn bản được xử lý khác nhau tùy theo giá trị đó được cung cấp trực tiếp vào hàm SUM hay được lưu trữ tại các ô trong bảng tính.

Các lỗi phổ biến khi sử dụng hàm SUM:

Lỗi #VALUE!: Lỗi #VALUE! xảy ra khi có bất kỳ số liệu nào được cung cấp trực tiếp vào hàm SUM khó, hoặc không thể diễn giải ra được bằng những giá trị số.

Cách khắc phục lỗi: Cách đơn giản nhất để bạn có thể khắc phục lỗi #VALUE! là xây dựng công thức bỏ qua vùng chọn có chứa lỗi rồi tính trung bình các giá trị không chứa lỗi còn lại.
Ngoài ra bạn cũng có thể sử dụng hàm SUMIF để khắc phục lỗi trên.

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

Hàm tính tổng SUMIF được dùng để tính tổng có kèm theo điều kiện. Ngoài ra, bạn có thể sử dụng hàm SUMIF để khắc phục các lỗi của hàm Excel cơ bản như #VALUE!, #N/A, #p/0!, #NULL và một số lỗi cơ bản khác trong hàm SUM.

Lưu ý: Hàm SUMIF là công thức mảng, bạn phải nhập công thức bằng cách nhấn tổ hợp phím CTRL+SHIFT+ENTER, khi đó, Excel có chức năng tự bọc lại công thức trong dấu ngoặc {}

Ví dụ: Bạn cần xuất ra tổng số tiền có giá trị lớn hơn 150.000 Bạn sử dụng công thức: =SUMIF(H4:H8,“>150.000”)

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

Hàm tính tổng SUMIFS là hàm tính tổng với nhiều điều kiện khác nhau trong vùng chọn. Khi bạn muốn xuất ra kết quả với từ 2 điều kiện trở lên.

 1.7. Hàm AVERAGE tính giá trị trung bình

Trong đó:

• Number 1: đây là giá trị bắt buộc trong hàm
• Number 2, number 3, … là giá trị tùy chọn

 

Lưu ý:

• Đối số tối đa trong hàm AVERAGE là 255.
• Các giá trị có thể là số, phạm vi, tên hoặc tham chiếu các ô chứa số.
• Những ô có giá trị là các văn bản hay trống giá trị thì sẽ được bỏ qua.

 

Ví dụ: Bạn muốn tính trung bình cộng điểm 3 môn XDĐ, QLCVĐXH, DLXH của các bạn trong lớp. Sử dụng công thức hàm AVERAGE như sau: =AVERAGE(K8,L8,M8) Hoặc =AVERAGE(K8:M8). Để tính điểm những bạn tiếp theo, bạn chỉ cần đặt con trỏ chuột góc dưới bên phải ô kết quả vừa tính, kéo thả chuột xuống các ô còn lại để copy công thức và xuất ra kết quả.

1.8. Hàm đếm ô trống COUNTBLANK

Hàm COUNTBLANK là một trong các hàm trong Excel được sử dụng để đếm số lượng các ô trống giá trị.

 Lưu ý: Hàm chỉ đếm những ô trống giá trị, những ô có giá trị bằng 0 sẽ không được đếm.

Ví dụ: Bạn cần xuất ra những sinh viên còn thiếu điểm môn QLCVĐXH. Sử dụng công thức hàm sau: =COUNTBLANK(L8:L23)

1.9. Hàm đếm ô không trống COUNTA

Các hàm trong Excel thì hàm COUNTA là hàm được sử dụng để đếm số ô có dữ liệu (những ô không trống). Dữ liệu trong ô có thể là số, chữ, hay các biểu tượng. Bất kỳ dữ liệu nào xuất hiện trong ô cũng đều được đếm.

 Ví dụ: Bạn cần đếm tổng số đầu điểm của cả 3 môn trong bảng điểm. Bạn sử dụng công thức hàm: =COUNTA(K8:M23)

1.10. Hàm tính tổng giá tiền sản phẩm SUMPRODUCT

Một trong số các hàm tính tổng cơ bản của các hàm trong Excel là hàm tính tổng giá tiền sản phẩm SUMPRODUCT. Để tính được tổng số tiền khi bạn có đơn giá và tổng số sản phẩm bạn có thể dễ dàng sử dụng hàm SUMPRODUCT.

Trong đó:

• Vùng chọn 1: đây là phần bắt buộc, là vùng chọn đầu tiên mà bạn muốn nhân các số liệu để tính tổng.
• Vùng chọn 2, vùng chọn 3: là số vùng chọn có thể có hoặc không, trong phạm vi cho phép dưới 255.

 

Các dữ liệu trong các vùng phải có cùng một kích thước. Trong trường hợp các mảng không phải dạng số thì hàm SUMPRODUCT trong các hàm Excel sẽ mặc định có giá trị bằng 0.

Ví dụ: Bạn cần xuất ra số tiền cho những mẫu trang sức đã có đơn giá và số lượng. Bạn sử dụng hàm SUMPRODUCT. =SUMPRODUCT(D3,E3). Để tính số tiền những mặt hàng tiếp theo, bạn chỉ cần đặt con trỏ chuột góc dưới bên phải ô kết quả vừa tính, kéo thả chuột xuống các ô còn lại để copy công thức và xuất ra kết quả.

1.11. Hàm MIN, MAX

Hàm MIN: Đây là hàm để bạn xuất ra những giá trị nhỏ nhất trong vùng chọn. Thay vì phải ngồi dò từng con số bạn có thể sử dụng hàm MIN với

 Ví dụ: bạn muốn tìm ra số điểm nhỏ nhất của môn XDĐ bạn sử dụng hàm sau: =MIN(K8:K23)

Hàm MAX: Ngược lại với hàm MIN, hàm MAX xuất ra kết quả là con số có giá trị lớn nhất trong vùng chọn. Cú pháp tương tự hàm MIN, hàm MAX có cú pháp: =MAX(vùng chọn)

2. Hàm LOGIC

2.1. Hàm IF

Một trong các hàm để kiểm tra điều kiện đúng của các hàm trong Excel là hàm IF.

Ví dụ: Chúng ta muốn kiểm tra xem ô H có thỏa mãn điều kiện lớn hơn 7 hay không, thỏa mãn điều kiện trả về giá trị Correct, không thỏa mãn điều kiện trả về giá trị Incorrect

2.2. Hàm AND

Hàm AND cho phép bạn kiểm tra điều kiện, cho ra kết quả đúng nếu điều kiện được thỏa mãn. Hàm xuất ra giá trị sai khi có bất kỳ điều kiện nào đó sai.

Trở lại ví dụ đã lấy với hàm IF, bạn có thể kiểm tra đồng thời 2 điều kiện điểm XDD lớn hơn 6 và QLCVĐXH lớn hơn 7, Nếu cả 2 giá trị đúng hàm trả về giá trị Correct, nếu giá trị sai hàm trả về Incorrect.

Công thức hàm: =IF(AND(H3>6,I3>7),”correct”,”incorrect”)

2.3. Hàm OR

Đều là một trong các hàm trong Excel cho ra các kết quả đúng hoặc sai. Tuy nhiên, ngược lại với hàm AND, hàm Excel OR cho về giá trị đúng nếu như trong điều kiện có bất kỳ điều kiện nào đúng, và trả về giá trị sai nếu như tất cả các điều kiện đều sai.

Vẫn quay trở lại ví dụ bên trên, khi kiểm tra lại điều kiện với hàm OR. Sử dụng công thức: =IF(OR(H3>6,I3>7),”correct”,”incorrect”)

2.4. Hàm IF lồng nhau

Hàm IF lồng nhau được sử dụng khi bạn có từ 2 điều kiện trở lên. Hàm IF lồng nhau có thể sử dụng là hàm IFS (trong Excel 2016)

Giả sử: Bạn muốn cho ra giá trị các hàm: H3=7 → True; I3=8 → exactly; J3=8 → Good; nếu không đúng trả về kết quả False. Công thức hàm IF lồng nhau như sau: =IF(H3=7,”true”,IF(I3=8,”exactly”,IF(J3=8,”good”,”False”)))

3. Hàm ngày tháng

Đây là một trong các hàm trong Excel để cập nhật ngày tháng. Cách để điền ngày tháng năm vào trang tính: Bạn sử dụng dấu “/” hoặc dấu gạch ngang “-” để biểu thị ngày tháng năm.

3.1. Hàm YEAR

Hàm YEAR trả về năm tương ứng của một ngày nào đó. Kết quả trả về ở dạng số nguyên trong khoảng 1900-9999.

Ví dụ: Sử dụng hàm YEAR để lấy ra năm ở ô A1, A2, A3.

Tại ô B1 ta sử dụng công thức và copy cho các ô khác: =YEAR(B1)

3.2. Hàm MONTH

Hàm MONTH trong Excel là hàm có chức năng tách giá trị tháng trong một ngày cụ thể. Tháng được trả về dưới dạng số nguyên và có giá trị từ 1 đến 12.

Ví dụ: Hãy sử dụng hàm MONTH để lấy ra tháng ở ô A1.

Bước 1: Trong bảng tính Excel bạn nhập hàm =MONTH(A1) vào ô bạn muốn trả kết quả.

Bước 2: Nhấn phím Enter và kiểm tra kết quả.

3.3. Hàm DAY

Hàm DAY là hàm trả về ngày của ngày, tháng, năm.

Ví dụ: Sử dụng hàm DAY để tách ngày.

Bước 1: Trong bảng tính Excel bạn nhập hàm =DAY(B3) vào ô muốn hiển thị kết quả. Ở đây mình chọn ô C3.

Bước 2: Nhấn Enter để hiển thị kết quả.

3.4. Hàm DATE

Hàm DATE là một trong các hàm trong Excel dùng để thêm ngày vào bảng tính. Để thêm số ngày vào phần ngày tháng, bạn chỉ cần sử dụng công thức đơn giản bằng cách lấy ô chứa ngày tháng đó cộng với số lượng ngày mà bạn muốn thêm.

 Ví dụ: Bạn muốn cộng thêm 5 ngày so với ngày 2/3/2020. Nhập công thức: =D3+5

3.5. Hàm hiển thị thời gian hệ thống NOW

Với hàm NOW bạn có thể dễ dàng nhập được chính xác thời gian hiện tại của hệ thống vào bảng Excel. Đây là hàm đơn giản nhất trong các hàm trong Excel.

Bạn không cần phải nhập bất kỳ số liệu nào trong dấu () nhưng lại cho kết quả chính xác tuyệt đối.

3.6. Hàm HOUR, MINUTE, SECOND

Đây là hàm xuất ra kết quả là giờ, phút và giây trong ô chứa thời gian.

Cú pháp tương tự với MINUTE và SECOND

3.7. Hàm TIME

Giống với hàm DATE bên trên, hàm TIME dùng để thêm số thời gian vào thời gian đã có ban đầu

3.8. Hàm DATEDIF

Hàm DATEDIF là hàm dùng để tính số khoảng cách ngày, tháng, năm trong trang tính Excel.

Phần đối số ở vị trí thứ 3 chính là số mà bạn muốn xuất ra. Các hàm trong Excel quy định “d” biểu thị ngày, “m” biểu thị tháng, “y” biểu thị năm. Thay các đối số để đưa ra kết quả bạn muốn.

Ví dụ: xuất ra kết quả là khoảng cách giữa các tháng. bạn sử dụng công thức như sau: =DATEDIF(D2,D3,”m”)

3.9. Hàm WEEKDAY

Hàm WEEKDAY được sử dụng để bạn xác định ngày thuộc thứ nào trong tuần. Hiển thị các giá trị từ 1(ngày chủ nhật) đến 7(ngày thứ 7).

 Ví dụ: ngày 5/8/2020 là ngày thứ 6 trong tuần theo công thức =WEEKDAY(D3)

3.10. Hàm TEXT

Hàm TEXT được sử dụng để biểu thị các ngày trong tuần ở dạng chữ

Ví dụ: để xác định ngày 5/8/2020 vào ngày nào trong tuần bạn sử dụng công thức: =TEXT(D3,“dddd”)

3.11. Hàm NETWORKDAYS tính số ngày làm việc

Hàm NETWORKDAYS dùng khi bạn tính số lượng ngày làm việc. Trong một khung thời gian cụ thể nào đó.

Ví dụ:

3.12. Hàm EOMONTH

Hàm EOMONTH sử dụng với mục đích lấy ra ngày cuối cùng của tháng mà bạn đang thao tác.

Bạn có thể sử dụng hàm EOMONTH để tìm ra xuất ra kết quả là ngày cuối cùng của tháng trước đó hoặc sau đó bằng cách thay vào đối số thứ 2. Ví dụ bạn muốn lấy ngày cuối cùng của 2 tháng sau thì thay vào công thức =EOMONTH(ô chứa thời gian,2), hoặc trước đó 3 tháng =EOMONTH(ô chứa thời gian,-3)

4. Hàm làm việc với chuỗi văn bản

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

Khi bạn muốn nối các văn bản khác nhau trong bảng tính, các hàm trong Excel có thể giúp bạn dễ dàng thao tác nhanh chóng. Hàm Excel giúp nối chuỗi văn bản, bạn có thể sử dụng ký tự đặc biệt &, tạo dấu cách bằng cách sử dụng ngoặc kép “khoảng trắng”.

4.2. Hàm LEFT

Sử dụng hàm LEFT khi bạn cần xuất ra kết quả là ký tự bên trái của chuỗi.

 

4.3. Hàm RIGHT

Hàm RIGHT được sử dụng khi bạn muốn xuất ra kết quả là ký tự bên phải của chuỗi giá trị

 

4.4. Hàm MID

Khá đơn giản nếu như bạn muốn xuất ra kết quả là các ký tự xuất hiện ở các vị trí khác nhau bằng hàm MID. Các hàm trong Excel cho phép bạn cho ra kết quả là các ký tự xuất hiện ở đâu trong chuỗi văn bản

Ví dụ: bạn muốn lấy từ vị trí thứ 4, và lấy ra 3 ký tự

4.5. Hàm LEN

Hàm LEN là hàm Excel cơ bản dùng để đo chiều dài chuỗi ký tự hay còn gọi là đếm số ký tự trong chuỗi. Hàm LEN có thể đếm được cả các chữ, số, ký tự đặc biệt trong ô.

Ví dụ: số ký tự trong ô D3: =LEN(D3)

4.6. Hàm FIND

Hàm FIND là một trong số các hàm trong Excel dùng để xác định vị trí ký tự.

Ví dụ: Bạn cần tìm vị trí chuỗi ký tự GR trong ô F2. Nhấn =FIND(“GR”,F2)

4.7. Hàm SUBSTITUTE

Trong các hàm trong Excel, hàm SUBSTITUTE là hàm dùng để thay thế một văn bản trong chuỗi bằng văn bản khác.

 

4.8. Hàm cắt khoảng trống TRIM

TRIM là hàm Excel cơ bản giúp loại bỏ khoảng trống, khắc phục lỗi copy văn bản từ một cơ sở dữ liệu khác. Tuy nhiên, hàm TRIM chỉ có thể xử lý được văn bản lỗi ở duy nhất 1 ô. Bạn có thể sử dụng hàm trong 1 ô rồi copy công thức đến hết danh sách

4.9. Hàm LOWER, UPPER, PROPER để chuyển đổi chữ hoa, chữ thường

Hàm LOWER, hàm UPPER, hàm PROPER là các hàm trong Excel dùng để chuyển đổi loại chữ trong các bảng tính.

Hàm LOWER được sử dụng để chuyển đổi các ký tự trong chuỗi từ chữ in hoa thành chữ thường

Ngược lại với hàm LOWER, hàm UPPER giúp chuyển đổi toàn bộ ký tự thường thành ký tự in hoa trong bảng

Hàm PROPER sử dụng khi bạn muốn viết hoa chữ cái đầu của mỗi chữ trong chuỗi ký tự

 

4.10. Hàm EXACT để so sánh hai cột

Hàm EXACT là các hàm trong Excel để so sánh dữ liệu giữa các cốt để đánh giá độ trùng lặp giữa chúng.

 

4.11. Hàm CONCATENATE kết hợp nội dung

CONCATENATE là một trong các hàm Excel cơ bản để kết hợp nội dung giữa 2 hay nhiều ô thành một ô duy nhất.

 

5. Hàm tra cứu và tham chiếu

5.1. Hàm VLOOKUP

Hàm VLOOKUP là một trong các hàm trong Excel có chức năng tìm kiếm. Hàm VLOOKUP là hàm Excel cơ bản sẽ tìm ra giá trị mà bạn đưa vào và xuất ra kết quả là giá trị cùng dòng ở cột khác trong bảng

Trong đó, 3 đối số đầu tiên là bắt buộc, đối số thứ 4 có thể có hoặc không.

Ví dụ:

5.2. Hàm MATCH

Hàm MATCH để xác định vị trí của một giá trị nào đó trong dải nhất định.

 

5.3. Hàm INDEX

Hàm INDEX là hàm Excel cơ bản trả về một giá trị hoặc có thể tham chiếu tới một giá trị nào đó ở phạm vi bảng.

5.4. Hàm CHOOSE

Hàm CHOOSE là một hàm trong số các hàm trong Excel được sử dụng khá phổ biến. Hàm CHOOSE dùng để tìm kiếm 1 giá trị nào đó trong chuỗi các giá trị.

Lưu ý: Nếu như Vị trí dữ liệu trả về không phải số nguyên hàm thì giá trị trả về sẽ lỗi #VALUE!

Bài viết trên đây của Office Whyvn đã hệ thống cho bạn các hàm cơ bản trong Excel, bạn hoàn toàn có thể tự học Excel với các hàm Excel cơ bản nhất. Tự học Excel cơ bản có thể giúp cho bạn có nhiều kiến thức hơn, hỗ trợ giải quyết công việc hiệu quả hơn. Mong rằng những thông tin này sẽ giúp ích cho bạn trong việc hiểu và cách sử dụng các hàm cơ bản cho công việc của mình.