Trong học tập và công việc, bạn thường gặp những dữ liệu với hàng chục trang, thậm chí là hàng trăm trang giấy với rất nhiều số liệu cần xử lý. Việc tính toán những con số bằng phương pháp thủ công là không thể. Và giải pháp tốt nhất chính là sử dụng Excel, các hàm trong ứng dụng này sẽ giúp bạn xử lý số liệu chuẩn xác trong tích tắc.
Hãy cùng Trang thủ thuật khám phá các hàm trong Excel nhé!
Các hàm trong Excel – Hàm đếm và hàm tính tổng
#1. Hàm COUNT
Hàm COUNT được sử dụng để đếm số ô có chứa dữ liệu kiểu số trong một vùng dữ liệu có sẵn nhanh chóng, giúp bạn tiết kiệm thời gian đếm thủ công.
Cú pháp
=COUNT(Giá trị 1, Giá trị 2,…)
Ví dụ: Học sinh trượt ký hiệu “tr”. Hãy đếm số học sinh đậu trong kỳ thi theo hình minh họa bên dưới.
Công thức: C15: =COUNT(C6:C14)
#2. Hàm COUNTA
Đây là hàm sử dụng để đếm các ô chứa dữ liệu, không phân biệt dữ liệu là số hay ký tự trong một vùng dữ liệu/ tập giá trị có sẵn.
Cú pháp
=COUNTA(Value 1, value 2,…). Hoặc: =COUNTA(vùng dữ liệu)
Ví dụ: Đếm số ô có chứa dữ liệu trong bảng sau:
Công thức: D10=COUNTA(A4:D9).
#3.Hàm COUNTIF
Hàm COUNTIF là hàm đếm các ô với điều kiện cụ thể.
Cú pháp
=COUNTIF(Range, Criteria)
Trong đó:
- Range: vùng chứa ô dữ liệu bạn muốn đếm.
- Criteria: điều kiện bạn muốn thiết lập dưới dạng số, ký tự hoặc một logic để chỉ định những giá trị nào sẽ được đếm trong dữ liệu.
Ví dụ: Tính số lần bán Cam 1 theo dữ liệu trong bảng:
Công thức: E4=COUNTIF(B5:B12, “Cam 1”).
#4. Hàm COUNTIFS
Đây là hàm trả về số lượng các ô nhằm thỏa mãn một hoặc nhiều điều kiện. Hàm COUNTIFS có thể sử dụng với các đa dạng dữ kiện như ngày, số, ký tự,…
Cú pháp
=COUNTIFS(Criteria_range1, Criteria1,[Criteria_range2, Criteria2],…)
Trong đó:
- range1: phạm vi đầu tiên để đánh giá.
- criteria1: xác định phạm vi đầu tiên sẽ áp dụng điều kiện đầu tiên (bắt buộc).
- range2: điều kiện bổ sung phạm vi thứ hai để đánh giá.
- criteria2: điều kiện bổ sung phạm vi sẽ áp dụng điều kiện thứ 2.
Ví dụ: Đếm những học sinh đạt điểm 9 ở cả 3 môn Toán, Văn, Anh.
Công thức: D11=COUNTIFS(D5:D9,9,E5:E9,9,F5:F9,9).
#5. Hàm COUNTBLANK
Hàm COUNTBLANK được sử dụng để đếm số ô trống trong vùng dữ liệu đã được xác định trong bảng.
Cú pháp
=COUNTBLANK(Range)
Trong đó: Range là phạm vi bắt buộc, từ đó bạn sẽ đếm các ô trống trong bảng.
Ví dụ: Đếm số học sinh chưa được tính điểm trong bảng
Công thức: E15=COUNTBLANK(E6:E13)
#6. Hàm RANK
Hàm RANK là hàm trả về thứ hạng, xếp hạng của một số trong danh sách các số. Thứ hạng của con số đó là vị trí tương quan giữa một giá trị với các giá trị còn lại trong danh sách.
Cú pháp
=RANK(Number, Ref, [order])
Trong đó:
- Number: đối số mà bạn muốn tìm thứ hạng, (bắt buộc).
- Ref: một mảng hoặc tham chiếu đến danh sách các số (bắt buộc).
- Order: số chỉ rõ cách xếp hạng. Nếu Order = 0 thì xếp hạng theo thứ tự giảm dần còn những số còn lại thì xếp hạng theo thứ tự tăng dần.
#7. Hàm AVERAGE
Đây là hàm trả về giá trị trung bình cộng của một dãy số trong bảng.
Cú pháp
AVERAGE(Number1, [Number2], [Number3],…)
Trong đó:
- Number1: Bắt buộc.
- Number2, Number3,…: Tùy chọn.
Ví dụ: Tính trung bình cộng của các số từ D5 => D8 và số 10.
Công thức: D10=AVERAGE(D5:D8,10).
#8. Hàm COLUMN
Đây là hàm trả về số cột của tham chiếu ô nhất định
Cú pháp
=COLUMN([Reference])
Trong đó: Reference là ô hoặc phạm vi ô muốn trả về số cột, tham số tùy chọn.
Lưu ý
- Nếu bạn bỏ qua tham số Reference thì hàm sẽ trả về số cột của tham chiếu ở mảng ngang.
- Nếu đổi số ở tham chiếu phạm vi ô và hàm COLUMN không được nhập ở dạng công thức ngang. Lúc này, hàm sẽ trả về số cột ngoài cùng bên trái.
Ví dụ: Tìm số cột của ô C8
Công thức: =COLUMN(C8)
#9. Hàm DATEDIF
Đây là hàm tính tổng số ngày, tháng, năm với hai mốc thời gian cho trước trong bảng Excel.
Cú pháp
=DATEDIF(start_day,end_day,unit)
Trong đó:
- start_day: số ngày bắt đầu cần tính toán.
- end_day: số ngày kết thúc, end_day cần lớn hơn start_day.
- unit: loại kết quả trả về của việc tính toán, cần viết trong dấu ngoặc kép.
Ví dụ: Tính tổng số ngày, tháng, năm theo dữ liệu trong bảng bên dưới.
#10. Hàm SUM
Hàm SUM được sử dụng để tính tổng với các phép tính thông thường bằng cách cộng những con số ở các cột lại với nhau.
Cú pháp
=SUM(number1,[number2],…)
Các hàm trong Excel làm việc với chuỗi văn bản
#1. Hàm LEFT
Hàm LEFT là hàm sử dụng để lấy những ký tự bên trái của một chuỗi văn bản.
Cú pháp
=LEFT(ô chứa chuỗi văn bản cần lấy, số ký tự muốn lấy).
#2. Hàm RIGHT
Đây là hàm được sử dụng để lấy những ký tự từ phía bên phải của chuỗi văn bản.
Cú pháp
=RIGHT(ô chứa chuỗi văn bản cần lấy, số ký tự cần lấy).
#3. Hàm MID
Hàm MID được sử dụng để lấy các ký tự nằm ở giữa tính từ vị trí bên trái qua bên phải.
Cú pháp
=MID(ô chứa chuỗi văn bản cần lấy, số ký tự cần lấy)
#4. Hàm FIND
Đây là hàm sử dụng để tìm kiếm vị trí của một chuỗi con trong chuỗi văn bản.
Cú pháp
=FIND(“chuỗi văn bản con”, ô chứa chuỗi văn bản)
Ví dụ: Tìm vị trí của chuỗi “in” trong chuỗi “minh anh”.
#5. Hàm LEN
Hàm LEN là hàm đếm ký tự có trong một chuỗi text/ chuỗi văn bản để tính toán hoặc kết hợp cùng các hàm khác.
Cú pháp
=LEN(Text)
Trong đó: Text là một chuỗi các chữ, số, ký tự đặc biệt hoặc khoảng trống (dấu cách) bất kỳ.
Lưu ý: Khi nhập chuỗi Text trực tiếp vào hàm LEN, bạn phải cho chuỗi văn bản này trong dấu ngoặc kép (“ ”).
Ví dụ: Tính độ dài của chuỗi Text “Phạm Hà Anh” trong bảng bên dưới:
Công thức: =LEN(B2)
#6. Hàm SUBSTITUTE
Đây là hàm được sử dụng để thay thế văn bản trong chuỗi bằng một văn bản khác.
Cú pháp
=SUBSTITUTE(ô chứa chuỗi ban đầu, “văn bản ban đầu”, “văn bản thay thế”).
#7. Hàm LOWER, UPPER, PROPER
Hàm LOWER được sử dụng để chuyển đổi tất cả những chữ in hoa trong chuỗi sang chữ thường.
Cú pháp
=LOWER(ô chứa chữ cần chuyển đổi)
Trái ngược với hàm LOWER, hàm UPPER sử dụng để chuyển các chữ thường trong chuỗi ký tự thành chữ in hoa.
Cú pháp
=UPPER(ô chứa chuỗi cần chuyển đổi)
Hàm PROPER sử dụng khi bạn muốn viết hoa các chữ cái đầu tiên trong chuỗi.
Cú pháp
=PROPER(ô chứa chuỗi cần chuyển đổi)
#8. Hàm EXACT
Đây là hàm sử dụng để so sánh các cột xem giá trị có trùng nhau hay không.
Cú pháp
=EXACT(ô cần so sánh)
#9. Hàm CONCATENATE
Hàm CONCATENATE là hàm có chức năng kết hợp nội dung của hai hay nhiều ô vào chung một ô duy nhất.
Cú pháp
=CONCATENATE(A1,B1,C1,D1)
Các hàm logic trong Excel
#1. Hàm IF
Hàm IF được sử dụng để kiểm tra điều kiện có đáp ứng hay không. Nếu thỏa mãn điều kiện, một xử lý tương ứng sẽ được thực hiện. Ngược lại, một xử lý khác được thực hiện.
Tuy nhiên, những tính toán có nhiều điều kiện thì hàm IF sẽ không thể đáp ứng. Thay vào đó, bạn có thể sử dụng hàm IF lồng.
Cú pháp
=IF(điều kiện,“giá trị đúng”,”giá trị sai”)
Ví dụ: Bảng dưới đây là tổng hợp tỷ số ghi bàn của các cầu thủ. Hãy kiểm tra xem các cầu thủ này có vượt qua kỳ thi kiểm tra hay không, điều kiện là tỷ số ghi bàn >=60 thì “pass” còn dưới mức này là “fail”.
Công thức: =IF(B2>=60, “pass”,”fail”)
#2. Hàm OR
Hàm OR được sử dụng để trả về giá trị “TRUE” (đúng) nếu có một điều kiện là “TRUE”. Khi hàm trả về “FALSE” (sai) khi tất cả điều kiện là “FALSE”.
Cú pháp
=OR(điều kiện 1, điều kiện 2,…, điều kiện n)
Trong đó:
- Điều kiện 1: bắt buộc là một giá trị logic.
- Điều kiện 2: tùy chọn một giá trị logic hoặc có thể là nhiều điều kiện khác.
Ví dụ: Hãy kiểm tra kết quả ghi bàn của cầu thủ trong bảng bên dưới.
Nếu kết quả ghi bàn Score >= 60 hoặc kết quả Score 2 >= 60 thì trả về TRUE.
Công thức: =OR(B2>=60,C2>=60)
#3. Hàm AND
Hàm AND được sử dụng để kết hợp nhiều điều kiện với nhau.
Nếu tất cả các điều kiện đúng, hàm trả về giá trị “TRUE”. Ngược lại, hàm trả về giá trị “FALSE” nếu các điều kiện là sai.
Hàm AND thường được kết hợp cùng hàm IF để tính tổng thỏa mãn nhiều điều kiện.
Cú pháp
=AND([logical1], [logical2], [logical3]…)
Trong đó: Logical là những biểu thức được xem xét TRUE hay FALSE.
Ví dụ: Kiểm tra tên và giới tính của các học sinh đúng hay sai trong bảng bên dưới
Công thức: C6=AND(A6=”Nguyễn Văn Đạt”,B6=”Nam”)
Nếu đúng thì kết quả trả về là TRUE.
#4. Hàm NOT
Hàm NOT được sử dụng để đảo ngược giá trị của các đối số nhập vào ô. Nếu đối số cho giá trị TRUE thì hàm sẽ trả về giá trị FALSE. Ngược lại đối số cho giá trị FALSE, hàm trả về TRUE.
Cú pháp
=NOT(logical)
Trong đó: Logical là giá trị/ biểu thức cho giá trị là TRUE hoặc FALSE.
Ví dụ: Kiểm tra những cầu thủ có Score 1 >= 60 hoặc Score 2 >= 60. Chỉ cần một trong hai điều kiện này đúng thì hàm trả về FALSE, ngược lại trả về TRUE.
Công thức: =NOT(OR(B2>=60,C2>=60))
Các hàm trong Excel – Hàm tra cứu và tham chiếu
#1. Hàm VLOOKUP
Hàm VLOOKUP được sử dụng để tìm kiếm các giá trị tại cột ngoài cùng bên trái của bảng và trả về giá trị trong cùng một hàng từ cột khác theo bạn chỉ định.
Cú pháp
=VLOOKUP(ô chứa giá trị tìm kiếm, vùng bảng chứa giá trị tìm kiếm và trả về, cột chứa giá trị trả về)
#2. Hàm MATCH
Hàm MATCH là hàm trả về vị trí của giá trị trong một dải ký tự nhất định
Ví dụ: Yellow ở 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ố 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.
#3. Hàm INDEX
Hàm INDEX sẽ trả về một giá trị cụ thể trong một phạm vi hai chiều.
Ví dụ: Trong dải E4:E7, 92 được tìm thấy tại giao điểm của hàng thứ 3 và cột 2. Lúc này, hàm INDEX trả về giá trị đặc biệt trong phạm vi một chiều.
Giá trị 97 được tìm thấy tại vị trí thứ 3 của dải E4:E7.
#4. Hàm CHOOSE
Đây là hàm trả về giá trị tại danh sách mà bạn chỉ định, dựa trên vị trí được yêu cầu.
Các hàm xác định ngày tháng năm trong Excel
#1. Hàm YEAR
Hàm YEAR được sử dụng để lấy được giá trị số năm từ một đối tượng thời gian cụ thể.
Cú pháp
=YEAR(serial_number)
Trong đó: serial_number là một giá trị về thời gian, dữ liệu ở dạng số.
#2. Hàm MONTH
Hàm MONTH được sử dụng để lấy giá trị số tháng từ một đối tượng thời gian.
Cú pháp
=MONTH(serial_number)
Trong đó: serial_number được xem là một giá trị thời gian dưới dạng dữ liệu số.
#3. Hàm DAY
Hàm DAY được sử dụng để xác định số ngày từ một giá trị thời gian.
Cú pháp
=DAY(serial_number)
Trong đó: serial_number là giá trị thời gian ở dạng dữ liệu số.
Trên đây là tổng hợp các hàm trong Excel mà Trang thủ thuật đã tổng hợp để gửi đến bạn. Hy vọng những kiến thức chia sẻ trong bài viết sẽ hữu ích, giúp bạn ứng dụng thành thạo những hàm cơ bản trong Excel vào công việc hiệu quả hơn. Chúc các thành công!