Để thực hiện tính toán, trích xuất dữ liệu, xử lý số liệu, làm thống kê, báo cáo một cách chính xác… bạn cần phải biết tận dụng các hàm trong Excel. Đây là công cụ đắc lực giúp bạn làm việc một cách thông minh, hoàn thành công việc chỉ với một nửa thời gian.
Bài viết dưới đây, hãy cùng Gitiho tìm hiểu về các hàm và tham khảo các ví dụ để hiểu hơn bạn nhé!
Excel cung cấp nhiều hàm xử lý văn bản giúp bạn thực hiện các tác vụ như cắt, kết hợp, trích xuất thông tin từ chuỗi văn bản, định dạng và biến đổi văn bản. Dưới đây là một số hàm Excel cơ bản giúp xử lý văn bản phổ biến trong Excel:
Hàm TEXT là hàm có chức năng chuyển đổi một số hoặc ngày dưới dạng số về định dạng văn bản mong muốn. Công thức hàm TEXT như sau:
=TEXT(value, format_text)
Trong đó:
Ví dụ về công thức hàm TEXT với định dạng ngày, tháng, năm như sau:
=TEXT(A1,"dd/mm/yyyy")
Công thức này sẽ chuyển đổi định dạng dữ liệu tại ô A1 sang chuỗi văn bản dạng ngày/tháng/năm.
Lưu ý: #### trong Excel thường xuất hiện khi dữ liệu ngày tháng quá dài hoặc khi định dạng cột không đủ rộng để hiển thị toàn bộ dữ liệu.
Để khắc phục, bạn chỉ cần mở rộng cột đó ra là được.
=TEXT(A1, "€#,##0.00")
Công thức này sẽ chuyển đổi số tại ô A1 thành chuỗi kí tự số có đơn vị tiền tệ là €.
Xem thêm: Hàm Text và các ví dụ sử dụng hàm Text trong Excel
Hàm TRIM được sử dụng để loại bỏ hàng đầu trong trang tính hoặc cách dấu cách, khoảng trống vô nghĩa giữa các từ.
Công thức hàm TRIM như sau:
=TRIM(text)
Trong đó: text là đoạn văn bản cần loại bỏ dấu cách hoặc khoảng trống vô nghĩa.
Ví dụ về công thức hàm TRIM như sau:
=TRIM(A1)
Công thức này sẽ xóa đi khoảng trống thừa giữa các từ trong ô A1.
Hàm VALUE có chức năng chuyển đổi một chuỗi văn bản sang định dạng số. Đây là hàm cực kỳ lợi hại khi kết hợp với các hàm tính toán, vì nó có khả năng chuyển đổi các giá trị đại diện cho số thành định dạng số hoàn chỉnh.
Công thức hàm VALUE đơn giản như sau:
=VALUE(text)
Trong đó: text là văn bản muốn chuyển thành định dạng số.
Ví dụ chi tiết về cách dùng hàm Value:
Có hàm Excel giúp ta định dạng văn bản chữ hoa hoặc chữ thường, cụ thể như sau:
Ví dụ về hàm UPPER, hàm LOWER và hàm PROPER:
Nếu bạn cần lấy ra trong chuỗi văn bản một số kí tự nhất định, hãy dùng đến hàm như sau:
Hàm LEFT(text, [num_chars]) trả về một số kí tự nhất định bắt đầu từ bên trái chuỗi văn bản. Trong đó: text là chuỗi văn bản cần trích xuất, [num_chars] là số kí tự cần trích xuất.
Hàm RIGHT(text, [num_chars]) trả về một số kí tự nhất định bắt đầu từ bên phải chuỗi văn bản. Trong đó: text là chuỗi văn bản cần trích xuất, [num_chars] là số kí tự cần trích xuất.
Hàm MID(text, start_num, num_chars) trả về một số kí tự nhất định bắt đầu từ vị trí kí tự bạn chỉ định. Trong đó, text là chuỗi văn bản cần trích xuất, start_num là vị trí kí tự đầu tiên cần trích xuất, [num_chars] là số kí tự cần trích xuất.
Ví dụ về các hàm cơ bản trong Excel này như sau:
Đây là các hàm cơ bản trong Excel giúp người dùng đánh giá điều kiện và trả về kết quả tương ứng.
Các hàm này có chức năng đối chiếu giá trị với điều kiện và trả về kết quả TRUE hoặc FALSE, cụ thể như sau:
Ví dụ về hàm này như sau:
Đây là một hàm logic được sử dụng để đảo ngược giá trị logic của một biểu thức. Nó thường được sử dụng để kiểm tra nếu một biểu thức là sai (FALSE), thì hàm NOT sẽ trả về giá trị đúng (TRUE), và ngược lại.
Công thức của hàm NOT như sau:
=NOT(logical)
Trong đó: logical là đối số.
Ví dụ, cả 2 công thức sau đây sẽ trả về giá trị FALSE:
=NOT(TRUE)
=NOT(3*3=9)
Hàm IF trả về một giá trị tương ứng với điều kiện bạn chỉ định với công thức như sau:
=IF(logical_test, [value_if_true], [value_if_false])
Trong đó:
Ví dụ về hàm IF trong Excel như sau:
Có 2 hàm cơ bản trong Excel giúp kiểm tra lỗi logic của công thức là hàm IFERROR và hàm IFNA:
Ví dụ về hàm kiểm tra lỗi logic:
Dưới đây là một số hàm cơ bản trong Excel giúp bạn tính toán tự động, tham khảo nhé!
Hàm SUM
Hàm SUM trả về tổng các đối số của nó với công thức hàm như sau:
=SUM(number1, [number2],…)
Trong đó: number1, [number2] là các đối số cần tính tổng. Các đối số này có thể là số, các tham chiếu ô hoặc các kết quả công thức.
Ví dụ, công thức hàm =SUM(A1:A3, 1) sẽ tiến hành cộng các giá trị trong ô A1 đến A3, đồng thời cộng thêm 1 vào kết quả cuối cùng.
Hàm SUMIF và hàm SUMIFS
Hai hàm này đều có chứ năng cộng các ô đáp ứng điều kiện được chỉ định trong phạm vi dữ liệu nhất định. Tuy nhiên, hàm SUMIF chỉ có thể đánh giá 1 điều kiện, còn hàm SUMIFS có khả năng đánh giá nhiều điều kiện khác nhau.
Công thức các hàm như sau:
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
Ví dụ:
Xem thêm: Hàm SUMIFS, COUNTIFS và cách viết điều kiện theo dạng số, ngày, text trong Excel
Hướng dẫn sử dụng hàm SUMIF trong Excel
Hàm SUMPRODUCT
Hàm SUMPRODUCT cho phép bạn thực hiện phép nhân và tính tổng của các phần tử tương ứng từ các mảng khác nhau, giúp bạn thực hiện các tính toán phức tạp liên quan đến dữ liệu mảng.
Công thức:
=SUMPRODUCT(array1, array2, ...)
Trong đó:
Ví dụ:
Trong số các hàm cơ bản trong Excel, hàm MOD có chức năng trả về số dư của phép chia. Công thức hàm như sau:
=MOD(number, divisor)
Trong đó:
Ví dụ:
Kết quả của công thức tính số dư ở ví dụ trên là 15.
Dưới đây là các hàm trong Excel có chức năng thống kê dữ liệu, chắc chắn sẽ hữu ích cho bạn trong quá trình làm việc:
Excel cung cấp các hàm sau để người dùng tìm giá trị lớn nhất, nhỏ nhất và giá trị trung bình của dữ liệu.
Ngoài ra, chúng ta có một số hàm thống kê dữ liệu nâng cao khác trong mảng như sau:
Xem thêm: Hàm MAX trong Excel và ứng dụng cơ bản bạn cần biết
Các hàm dưới đây giúp bạn đếm số ô chứa kiểu dữ liệu nhất định hoặc các ô đáp ứng điều kiện nhất định.
Hàm VLOOKUP
Mở đầu với hàm Excel liên quan đến hàm LOOKUP, hãy tìm hiểu về Hàm VLOOKUP. Hàm cơ bản trong Excel này tìm một giá trị được chỉ định trong cột đầu tiên của bảng và kéo dữ liệu phù hợp với giá trị từ cùng hàng trong cột khác của bảng. Công thức hàm VLOOKUP như sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
Ví dụ:
Cách tránh lỗi NA khi dùng hàm VLOOKUP trong Excel
Hàm INDEX trả về tham chiếu đến một ô trong mảng dựa trên số hàng và cột bạn chỉ định. Công thức hàm INDEX như sau:
=INDEX(array, row_num, [column_num])
Trong đó:
Ví dụ về hàm INDEX như sau:
Hàm MATCH có chức năng trả về vị trí tương đối của một mục dữ liệu với công thức như sau:
=MATCH(lookup_value, lookup_array, [match_type])
Trong đó:
Hàm MATCH khi kết hợp với hàm INDEX có thể tạo nên một công thức tìm kiếm dữ liệu mạnh mẽ thay thế cho hàm VLOOKUP trong Excel.
Hàm INDIRECT gián tiếp trả về kết quả một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản.
Công thức hàm như sau:
=INDIRECT(ref_text, [a1])
Trong đó: ref_text là chuỗi văn bản được chỉ định.
Ví dụ đơn giản nhất về hàm INDIRECT trong Excel như sau:
Trong ví dụ này, chúng ta điền công thức =INDIRECT(B2) vào ô C2. Kết quả, Excel trả về giá trị ở ô B2, tham chiếu đến số liệu ở ô A2.
Đây là một hàm được sử dụng để trả về một phạm vi hoặc một ô cụ thể trong một bảng tính dựa trên một ô tham chiếu và các tham số vị trí. Hàm này thường được sử dụng để tạo ra các phạm vi động hoặc thay đổi dựa trên điều kiện hoặc sự thay đổi của dữ liệu.
=OFFSET(reference, rows, cols, [height], [width])
Trong đó:
Ví dụ: giả sử bạn có dãy dữ liệu từ B3 đến B8 và bạn muốn trả về giá trị trong ô cách xa 3 hàng và 2 cột từ ô B3. Bạn có thể sử dụng hàm OFFSET như sau:
Hàm TRANPOSE là một trong số hàm Excel có chức năng độc nhất vô nhị: biến đổi một dải ô nằm ngang thành dải đứng và ngược lại. Công thức hàm cơ bản trong Excel này đơn giản như sau:
=TRANPOSE(array)
Hàm FV có chức năng tính giá trị tương lai (future value) của một dòng tiền hoặc một khoản đầu tư dựa trên lãi suất cố định. Công thức của hàm FV như sau:
=FV(rate, nper, pmt, [pv], [type])
Trong đó:
Ví dụ:
Lưu ý: Dấu trừ “-” trong ví dụ trên được sử dụng để biểu thị giá trị tiêu biểu cho mục đích đầu tư.
Các hàm dưới đây có chức năng tạo lập ngày tháng từ các số:
=DATE(năm, tháng, ngày)
=DATEVALUE(chuỗi_ngày)
Xem chi tiết cách dùng hàm Date và hàm Datevalue
Các hàm Excel dưới đây có sẽ trả về giá trị ngày và giờ tại thời điểm hiện tại bạn đang thao tác trên Excel:
Xem chi tiết cách sử dụng hàm TODAY trong Excel
Hàm dưới đây sẽ trích ra dữ liệu ngày tháng và các thành phần ngày tháng:
Các hàm dưới đây sẽ giúp bạn tính chênh lệch ngày:
=DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, "định_dạng")
=EDATE(ngày_bắt_đầu, số_tháng)
=YEARFRAC(ngày_bắt_đầu, ngày_kết_thúc, [cơ_chế])
Các hàm dưới đây sẽ giúp kế toán viên soạn thảo bảng chấm công hiệu quả và dễ dàng:
=WORKDAY(ngày_bắt_đầu, số_ngày, [danh_sách_ngày_nghỉ])
=WORKDAY.INTL("2023-08-21", 15, , 1)
Các hàm dưới đây là công cụ hiệu quả để xử lí dữ liệu thời gian trong ngày:
Hy vọng bài viết tổng hợp các hàm trong Excel và cách sử dụng và cách sử dụng mà Gitiho hướng dẫn sẽ giúp bạn áp dụng thành thạo trong công việc và học tập hằng ngày.
Để nâng cao kỹ năng sử dụng các hàm từ cơ bản đến nâng cao trong Excel bạn có thể tham khảo học Excel top #1 trên Gitiho là Tuyệt đỉnh Excel - Thành thạo Excel chỉ sau 16h+ học để bứt phá bản thân và làm việc năng suất, hiệu quả hơn nhé!
Bện cạnh đó, Gitiho gửi đến bạn bộ tài liệu MIỄN PHÍ về các hàm Excel để bạn có thể thực hành thường xuyên, quan trọng nhất là biết cách áp dụng ngay để giải quyết công việc.
Tài liệu kèm theo bài viết
KHÓA HỌC EXCEL ONLINE HOÀN TOÀN MIỄN PHÍ
Khóa học với hơn 14000 HỌC VIÊN đang học và đánh giá trung bình 4.76 SAO