Các hàm trong excel thông dụng nhất là những hàm nào? Bạn có biết cú pháp của 20+ hàm excel sau đây không? Cùng đọc bài viết để học các hàm excel cơ bản có ví dụ minh họa cụ thể giúp bạn hiểu rõ các hàm excel nhé.
1. Hàm SUM: Tính tổng
– Công dụng của hàm SUM: Hàm SUM trong Excel được sử dụng để tính tổng các đối số trên bảng tính. Sử dụng hàm SUM giúp bạn tính toán được số liệu lớn, bảng số liệu dày đặc và thực hiện phép tính nhanh chóng hơn cách nhập phép tính thông thường. Đặc biệt hữu ích với những con số lẻ, như vậy thì tránh được những sai sót gặp phải nếu nhập thiếu một con số hay bỏ sai dấu , giữa các đơn vị tiền tệ.
– Cú pháp của hàm SUM: =SUM (number 1, number 2,…)
Trong đó:
- number 1 là giá trị thứ nhất
- number 2 là giá trị thứ hai
– Chức Năng: Tính tổng number 1 + number 2 + …
– Chú ý:
Ô tính có giá trị logic TRUE được xem là 1, FALSE được xem là 0.
Nếu các đối số number 1, number 2 là mảng hay tham chiếu thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được tính. Các giá trị khác trong mảng hoặc tham chiếu bị bỏ qua.
– Ví dụ:
=SUM (1, 2, 3) có giá trị bằng 6.
=SUM (3, 4, TRUE) có giá trị bằng 7.
=SUM (“2″,”4”,1) có giá trị bằng 6.
2. Hàm AVERAGE: Tính trung bình cộng
– Công dụng hàm AVERAGE: Hàm AVERAGE trong Excel được dùng để hỗ trợ tính trung bình cộng của một dãy số. Hàm AVERAGE hỗ trợ người dùng tính toán nhanh hơn nếu số lượng phần tử trong dãy lớn và dài.
– Cú pháp: AVERAGE(Number1, [Number2], [Number3],…)
Trong đó:
- Number1 là đối số thứ nhất. Đây là đối số bắt buộc
- Number2, Number3,…: là các đối số tùy chọn.
– Chức năng: Tính trung bình cộng của các số Number1, Number2, Number3…
– Hàm AVERAGE cho phép thực hiện phép tính tối đa có 256 đối số. Dữ liệu có thể là số, tên, phạm vi hoặc tham chiếu ô có chứa số. Một đối số tham chiếu ô hoặc phạm vi có chứa giá trị logic, văn bản hay ô rỗng thì những giá trị đó sẽ bị bỏ qua, trừ giá trị 0 hoặc được nhập trực tiếp vào danh sách đối số.
– Ví dụ: Cho bảng điểm của các học sinh sau. Tính điểm trung bình cộng các môn học của từng học sinh.
3. Hàm MIN/MAX: Cho giá trị lớn nhất, nhỏ nhất
– Công dụng hàm MIN/MAX: Cách sử dụng hàm MAX tìm giá trị lớn nhất của các đối số trong vùng dữ liệu. Hàm MIN tìm giá trị nhỏ nhất của các đối số trong vùng dữ liệu.
– Cú pháp hàm MIN/MAX:
MAX (number 1, number 2, …)
MIN (number 1, number 2, …)
Trong đó:
- Number 1, number 2… là các đối số hoặc vùng dữ liệu.
– Chức năng của hàm MIN/MAX: Trả về giá trị lớn nhất, nhỏ nhất của các đối số hoặc vùng dữ liệu có chứa số.
– Ví dụ: Đưa về giá trị lớn nhất và giá trị nhỏ nhất trong dãy các số 65, -20, 3, 4, 32.
– Công thức: = MAX (65, -20, 3, 4, 32) = 65
= MIN (65, -20, 3, 4, 32) = -20.
– Xem công thức hàm MIN/MAX cụ thể trên excel với giá trị được nhập vào ô.
4. Hàm TRIM: Loại bỏ khoảng trống trong văn bản
– Công dụng hàm TRIM: Hàm TRIM là một trong các hàm cơ bản Excel giúp bạn xử lý các khoảng trắng nằm rải rác trong ô tính khi bạn nhập hay dán văn bản từ một cơ sở dữ liệu, một website khác về.
– Cú pháp: =TRIM(Text)
Trong đó:
- Text là dữ liệu bạn muốn loại bỏ khoảng trống.
– Ví dụ: Bạn có một bảng tính có cột Họ và Tên chứa các ký tự trắng như bảng dưới đây. Hãy sử dụng hàm TRIM để loại bỏ các khoảng trắng này.
– Công thức được nhập cho các ô cụ thể ở các hình dưới đây:
5. Hàm COUNT: Đếm số lượng dữ liệu dạng số
– Công dụng hàm COUNT: Thay vì đếm thủ công các ô chứa số trong một bảng dữ liệu khổng lồ thì hàm COUNT sẽ hỗ trợ bạn.
– Cú pháp hàm COUNT: Count (vùng chọn để đếm)
– Chức năng: Đếm số lượng các ô chứa số.
– Lưu ý: Chỉ đếm dữ liệu dạng số.
– Ví dụ: Đếm số học sinh đỗ trong đợt thi này, học sinh trượt được ký hiệu là “tr”.
6.Hàm COUNTA: Đếm số lượng dữ liệu trong ô không trống
– Công dụng hàm COUNTA: Hàm COUNTA cũng dùng hỗ trợ đếm ô. Tuy nhiên, thay vì chỉ đếm các ô chứa số, COUNTA sẽ hỗ trợ bạn đếm bất kỳ ô nào có chứa nội dung (cả số và cả chữ cái).
– Cú pháp hàm COUNTA: Counta (Value1, [value2], [value3],…)
Trong đó:
- Value1 là đối số bắt buộc, là vùng dữ liệu cần đếm.
- Value2 và Value3 là các tùy chọn vì được đặt trong dấu [], không bắt buộc có.
– Chức năng: đếm số lượng các ô không trống trong vùng chọn.
– Ví dụ: Đếm số lượng các ô không trống trong vùng chọn bảng sau:
7. Hàm COUNTIF: Đếm dữ liệu có điều kiện
– Công dụng hàm COUNTIF: Là hàm thường sử dụng trong Excel dùng để đếm số lượng với điều kiện. Hàm COUNTIF giúp bạn chọn lọc dữ liệu, trích xuất dữ liệu trong trường hợp tính toán số lượng sản phẩm A đã bán ra bao nhiêu chiếc trong tổng số sản phẩm trong kho…
– Cú pháp hàm COUNTIF: COUNTIF(range, criteria)
Trong đó:
- range là vùng chọn
- criteria là đối số điều kiện bạn muốn tìm kiếm.
– Chức năng: Tính số ô trong phạm vi đáp ứng điều kiện được chỉ định.
– Ví dụ: Số lượng học sinh Nam trong bảng sau:
8. Hàm COUNTIFS: Đếm dữ liệu với nhiều điều kiện
– Cú pháp hàm COUNTIFS: Là hàm thường sử dụng trong Excel dùng để đếm số lượng với nhiều vùng chọn và nhiều điều kiện cùng một lúc. Hàm COUNTIFS có công dụng tương tự hàm COUNTIF.
– Cú pháp hàm COUNTIFS:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Trong đó:
- criteria_range1 là vùng chọn 1
- criteria1 là điều kiện 1
- criteria_range2 là vùng chọn 2
- criteria2 là điều kiện 2
– Chức năng: Đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định.
– Ví dụ: Số lượng học sinh Nữ, có tuổi là 20 trong bảng sau:
9. Hàm LEN: Đo độ dài của chuỗi ký tự
– Công dụng hàm LEN: Để đo độ dài của chuỗi ký tự bất kỳ bạn có thể sử dụng hàm LEN trong Excel, hàm này trả về độ dài chính xác của chuỗi ký tự từ đó bạn có thể thực hiện những yêu cầu bạn muốn.
– Cú pháp hàm LEN :LEN (text)
– Trong đó:
- Text: là chuỗi ký tự
– Chức năng: Trả về giá trị là độ dài của chuỗi ký tự, kể cả ký tự khoảng trống.
– Ví dụ: Đo độ dài của chuỗi “University of Technology”.
10. Hàm CONCATENATE: Nối các chuỗi ký tự
– Công dụng hàm CONCATENATE: Hàm CONCATENATE giúp kết hợp nội dung của các ô với nhau. Nếu bạn muốn kết hợp các ô tên, tên đệm, họ thành một ô thể hiện tên gọi hoàn chỉnh hãy sử dụng hàm CONCATENATE và di chuyển đếm các ô cần kết hợp là có kết quả như mong muốn. Để tạo khoảng cách hoặc dấu chấm, dấu phẩy.. giữa các ô kết hợp, bạn chỉ việc đưa dấu chấm, dấu phẩy… vào dấu ngoặc kép.
– Cú pháp: CONCATENATE (text 1, text 2, …)
– Trong đó:
- Text 1: là chuỗi thứ 1. Đây là đối số bắt buộc.
- Text 2: là đối số tùy chọn. Có thể lên đến tối đa 255 chuỗi. Các chuỗi phải được phân tách nhau bởi dấu phẩy.
– Chức năng: Dùng để ghép nối các chuỗi văn bản thành một chuỗi duy nhất. Các chuỗi được nối có thể là số, văn bản, ô tham chiếu.
– Ví dụ: Ghép các tên sau:
= CONCATENATE (B1,” “,C1): Mark Twain
= CONCATENATE (A2,” “,A1,” is ”,A3,” USD”): Kawasaki Z250 price is 3365 USD.
= CONCATENATE (B2,” & “,C2): Phineas & Ferb.
11. Hàm DAYS: Trả về số ngày giữa 2 ngày trong Excel
– Công dụng hàm DAYS: Hàm trả về số ngày giữa 2 ngày trong Excel
– Cú pháp: DAYS(end_date, start_date).
Trong đó:
- end_date là ngày kết thúc muốn xác định số ngày, là tham số bắt buộc.
- start_date là ngày đầu tiên muốn xác định số ngày, là tham số bắt buộc.
– Lưu ý:
- Nếu end_date và start_date đều có dạng số thì hàm dùng EndDate – StartDate để tính số ngày giữa 2 ngày này.
- Nếu end_date và start_date ở dạng văn bản hàm tự động chuyển sang định dạng ngày bằng cách sử dụng hàm DATEVALUE(date_text) rồi tính toán.
- Nếu đối số vượt ngoài phạm vi của ngày tháng năm -> hàm trả về giá trị lỗi #NUM!
- Nếu đối số dạng văn bản mà không thể chuyển sang định dạng ngày hợp lệ -> hàm trả về giá trị lỗi #VALUE!
- Nếu chuyển dữ liệu sang năm thì chia cho 365.
- Nếu tính ở ngày hiện tại thì kết hợp với hàm TODAY()
– Ví dụ: Tính số năm công tác của các nhân viên:
12. Hàm PRODUCT: Hàm nhân
– Công dụng hàm PRODUCT: Hàm PRODUCT là hàm tính phép nhân của các đối số.
– Cú pháp: =PRODUCT(number 1, number 2, …)
Trong đó:
- number 1, number 2, … là các đối số.
– Ví dụ: Nhân các điểm thành tích.
– Áp dụng công thức: =PRODUCT(A2:C2) ta có kết quả:
13. Hàm NETWORKDAYS: Tính số ngày làm việc
– Công dụng Hàm NETWORKDAYS: Thay vì đếm lịch để tính số ngày làm việc. Excel hỗ trợ cho bạn một hàm NETWORKDAYS tính số lượng ngày làm việc toàn thời gian giữa hai ngày một cách chính xác, nhanh gọn.
– Cú pháp: =NETWORKDAYS(START_DATE,END_DATE,[HOLIDAYS])
– Chức năng: Trả về số lượng ngày làm việc trừ những ngày lễ và ngày cuối tuần bắt đầu từ ngày start_date và kết thúc bằng ngày end_date.
– Lưu ý: Cài đặt định dạng ngày, tháng, năm.
– Ví dụ: Ta có một bảng chấm công nhân viên A, bao gồm cả số ngày nghỉ như sau. Tính số này làm việc của A.
14. Hàm NOW: Trả về ngày và giờ hiện tại
– Công dụng của hàm Now: Hiển thị ngày và thời gian hiện tại trên trang tính hoặc muốn tính toán dựa trên thời gian hiện tại thì hàm NOW là giải pháp dành cho bạn.
– Cú pháp: = NOW ()
Cú pháp hàm NOW không sử dụng đối số.
Kết quả của hàm NOW được cập nhật khi bạn thực hiện một công thức hoặc edit một ô giá trị chứ không cập nhật liên tục.
– Chức năng: Trả về giá trị ngày tháng năm và giờ phút hiện hành.
Ví dụ: Bây giờ là 17 giờ 32 phút ngày 16/12/2020. Khi viết cú pháp hàm =NOW() cho ô B6 sẽ trả về giá trị chính xác giờ phút ngày tháng năm hiện hành.
15. Hàm CHOOSE: Hàm tìm chuỗi kí tự
– Cú pháp: =CHOOSE(vị trí chuỗi kí tự, chuỗi thứ nhất, chuỗi thứ hai,…)
– Ví dụ: Cho dữ liệu các ô: B1=”Van”; B2=”Su”; B3=”Dia ly”Ngoai Ngu”; B4=”Tin hoc”
=CHOOSE(4,B1,B2,B3,B4) = Tin hoc
16. Hàm VLOOKUP: Hàm tham chiếu dữ liệu cột dọc
– Công dụng của hàm VLOOKUP: Hàm VLOOKUP hay còn gọi là hàm tham chiếu cột, nó lấy thông tin từ bảng phụ với điều kiện dò tìm ở bảng chính để điền vào cột nào đó của bảng chính nếu thỏa mãn điều kiện dò tìm.
– Cú pháp hàm VLOOKUP:
=VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Trong đó:
- lookup_value: là giá trị để tham chiếu có thể là một ký tự, chuỗi ký tự, tham chiếu hay giá trị nào đó của bảng chính.
- table_array: vùng chứa dữ liệu của bảng phụ (lấy vùng bằng cách dùng chuột quét cả bảng phụ). Luôn phải để ở dạng địa chỉ tuyệt đối bằng cách quét xong rồi nhấn F4 để có dấu $ đằng trước nhé
- row_index_num: giá trị cần lấy ra thuộc cột mấy trong bảng phụ
- range_lookup: muốn tìm chính xác hay tương đối (0 là chính xác, 1 là tương đối) thường giá trị này là 0 nhé.
– Lưu ý: Bài toán về hàm vlookup thường cho ra khi bắt ta tham chiếu cột từ một bảng phụ cho trước.
– Ví dụ: Hãy điền tên vào cột tên hàng dựa vào ký tự của cột mã hàng lấy tham chiếu từ bảng phụ.
17. Hàm HLOOKUP: Hàm tham chiếu dữ liệu hàng ngang
– Công dụng hàm HLOOKUP: Hàm HLOOKUP dùng dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nó sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà chúng ta chỉ định.
– Cú pháp: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Trong đó:
- lookup_value là giá trị tìm kiếm, giá trị dùng để tìm kiếm, có thể là một chuỗi ký tự, một tham chiếu, một giá trị nào đó của bảng chính. Giá trị này phải có tên trong vùng dữ liệu tìm kiếm.
- table_array là vùng dữ liệu tìm kiếm: Vùng chứa dữ liệu cần tìm. Vùng chứa dữ liệu tìm kiếm này thường nằm ở bảng phụ. Lưu ý: trong vùng dữ liệu tìm kiếm này phải chứa giá trị tìm kiếm
- row_index_num là hàng trả về giá trị tìm kiếm: giá trị lấy ở hàng thứ mấy trong bảng phụ
- range_lookup là tham số: muốn tìm chính xác hay tương đối. Tham số này điền “0” nếu bạn muốn dò tìm chính xác, điền số “1” nếu muốn dò tìm giá trị tương đối.
18: Hàm LEFT: Lấy kí tự bên trái
– Hàm Left là hàm sẽ lọc giá trị bên trái của chuỗi.
– Cú pháp: =LEFT(Text,N)
Trong đó:
- Text là chuỗi văn bản.
- N là Số ký tự mà bạn muốn trích dẫn.
– Ví dụ:
=LEFT(“Thuthuatmaytinh”,7) => Kết quả trả về: Thuthuat
19: Hàm RIGHT: Lấy kí tự bên phải
– Dùng hàm RIGHT khi cần lấy những ký tự từ phía bên phải của chuỗi.
– Công thức: =RIGHT(ô chứa chuỗi cần lấy, số ký tự muốn lấy)
20: Hàm INDEX: Bảng, dòng số, cột số
– Công dụng: trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.
– Cú pháp: INDEX(array, row_num, [column_num])
– Ví dụ: 92 được tìm thấy tại giao điểm của hàng 3 và cột 2 trong dải E4:F7.
Xem thêm: Video học các hàm trong excel
21: Hàm MATCH: hàm tra cứu, dò giá trị
– Công dụng: tìm kiếm lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.
– Cú pháp: MATCH(lookup_value, lookup_array, [match_type])
– Ví dụ: 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.
Như vậy là chúng ta đã tìm hiểu về các hàm trong excel cơ bản và thông dụng nhất. Hãy nhớ rõ cú pháp cũng như chức năng của từng hàm để vận dụng trong thực tiễn công việc bạn nhé!