Excel là một phần mềm do Microsoft cung cấp cho chúng ta một phần mềm có số lượng lớn các chức năng để phân tích, kiểm toán và tính toán dữ liệu. Và để thực hiện được các chức năng trong Excel chúng ta sẽ có những hàm được sử dụng như một phần của công thức trong một ô. Vậy thì trong Excel có những hàm nào để chúng ta sử dụng. Ở bài viết này sẽ nói về các hàm trong Excel và các ví dụ cụ thể về các hàm đó.
Hàm TEXT
Hàm này có chức năng để chuyển đổi một số hoặc một ngày thành một chuỗi kiểm tra ở định dạng được chỉ định. Công thức của hàm:.
TEXT(Value, Format_text).
Trong đó:.
Value là một giá trị số bạn muốn chuyển đổi sang văn bản.
Format_text là định dạng mong muốn.
Ví dụ:.
Ở ví dụ này chúng ta dùng hàm TEXT để chuyển ngày tháng trong ô A1 sang một chuỗi văn bản theo định dạng.
Ở ví dụ lần này, chúng ta dùng hàm TEXT để thêm ký tự tiền tệ vào trong ô A1.
Hàm CONCATENATE
Hàm CONCATENATE trong Excel là hàm được thiết kế để nối các đoạn văn bản khác nhau lại hoặc kết hợp các giá trị từ một vài ô vào một ô. Công thức của hàm:.
CONCATENATE (Text1, [Text2], …).
Trong đó:.
Text là một chuỗi văn bản hay tham chiếu ô hoặc giá trị trong công thức.
Ví dụ:.
Ở ví dụ trên thì chúng ta kết hợp dữ liệu ở ô A1 và B1. Nhưng dữ liệu giữa ô A1 và B1 không có khoảng cách, nếu chúng ta muốn có khoảng cách, thực hiện như sau:.
Hàm TRIM
Hàm TRIM là hàm trong Excel được dùng để loại bỏ hàng đầu, dấu cách cũng như các khoảng trống dư thừa giữa các từ. Trường hợp văn bản hoặc một chuỗi văn bản hay tham chiếu đến ô có chứa văn bản mà bạn muốn loại bỏ không gian. Dưới đây là công thức của hàm:.
TRIM(text).
Trong đó: Text là đoạn văn bản chứa khoảng trắng cần loại bỏ.
Ví dụ:.
Hàm TRIM không quá khó để sử dụng, bạn chỉ cần chọn ô có chứa dữ liệu muốn loại bỏ khoảng trống là bạn được.
Hàm SUBSTITUTE
Hàm SUBSTITUTE là hàm sẽ thay thế một bộ ký tự bằng một ký tự khác trong một ô được chỉ định hoặc một chuỗi văn bản. Cú pháp của hàm SUBSTITUTE như sau:.
SUBSTITUTE(Text, Old_text, New_text, [Instance_num]).
Trong đó:.
Text: Văn bản mà bạn muốn thay thế các ký tự trong đó.
Old_text: Văn bản cũ bạn muốn được thay thế.
New_text: Văn bản mới mà bạn muốn thay thế cho văn bản cũ.
Instance_num: Vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới.
Ví dụ:.
Ở ví dụ này hàm SUBSTITUTE đã thay thế tất cả các dấu phẩy trong ô A1 bằng dấu chấm phẩy.
Dùng để hàm để loại bỏ bớt dữ liệu trong ô.
Hàm VALUE
Loại hàm này dùng chuyển đổi một chuỗi văn bản thành một số. Hàm VALUE rất thích hợp để các giá trị định dạng văn bản đại diện cho các con số thành các con số có thể được sử dụng trong các công thức Excel khác và tính toán. Công thức của hàm:.
VALUE(text).
Trong đó: Text là một chuỗi số được đặt trong dấu ngoặc kép hoặc công thức, tham chiếu đến ô chứa chuỗi số mà các bạn muốn chuyển đổi.
Ví dụ:.
Ở ví dụ này hàm VALUE chuyển chuỗi ký tự chứa số thành giá trị số.
Các hàm dùng chuyển đổi văn bản (UPPER, LOWER, PROPER)
Trong Microsoft Excel sẽ cung cấp cho cho 3 hàm để có thể chuyển đổi ký tự văn bản:.
UPPER(text) – chuyển đổi tất cả các ký tự trong một chuỗi ký tự được chỉ định sang chữ hoa.
LOWER(text) – thay đổi tất cả các chữ in hoa trong một chuỗi văn bản thành chữ thường.
PROPER(text) – viết hoa chữ cái đầu tiên của mỗi từ và chuyển đổi tất cả các chữ cái khác sang chữ thường.
Ví dụ:.
Dùng trích xuất dữ liệu của văn bản (LEFT, RIGHT, MID)
Khi bạn chỉ cần trích xuất một phần của dữ liệu chứ không dùng hết thì Excel có thể giúp bạn làm điều đó. Phần mềm Excel có những hàm như:.
LEFT(Text, [Num_chars]) – trả về một số ký tự nhất định bắt đầu từ đầu chuỗi văn bản.
RIGHT(Text,[Num_chars]) – trả về một số ký tự được chỉ định bắt đầu từ cuối chuỗi văn bản.
MID(Text, Start_num, Num_chars) – trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu ở bất kỳ vị trí nào bạn chỉ định.
Trong đó:.
Text – một chuỗi văn bản hoặc một tham chiếu đến một ô chứa các ký tự mà bạn muốn trích xuất.
Start_num – chỉ ra nơi bắt đầu (tức là vị trí của ký tự đầu tiên bạn muốn trích xuất).
Num_chars – số ký tự bạn muốn trích xuất.
Ví dụ:.
Các hàm logic (OR, AND, XOR)
Những hàm logic giúp đánh giá một điều kiện xác định và trả về giá trị tương ứng. Sau đây là các hàm logic:.
AND(logical1, [logical2], …) – Trả về TRUE nếu tất cả các đối số đánh giá TRUE, FALSE nếu ngược lại.
OR(logical1, [logical2], …) – Trả về TRUE nếu ít nhất một trong các đối số là TRUE.
XOR(logical1, [logical2],…) – Trả về một giá trị hợp lệ duy nhất trong tất cả các đối số.
Trong đó: Logical: là những biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE).
Ví dụ:.
Hàm NOT
Hàm NOT sẽ đảo ngược giá trị đối số của nó, nghĩa là nếu đánh giá lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại. Công thức là:.
NOT(value).
Trong đó: Value là giá trị cần được xác minh.
Ví dụ:.
Hàm IF
Hàm IF trong Excel được gọi hàm điều kiện vì khi sử dụng hàm sẽ trả về một giá trị dựa trên điều kiện mà bạn chỉ định. Cú pháp của hàm IF như sau:.
IF(logical_test, [value_if_true], [value_if_false]).
Trong đó:.
Logical_test: Là một giá trị hay biểu thức logic có giá trị TRUE (đúng) hoặc FALSE (sai). Bắt buộc phải có. Ở tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.
Value_if_true: Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị TRUE hay nói cách khác là điều kiện thỏa mãn.
Value_if_false: là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị FALSE hay nói cách khác là điều kiện không thỏa mãn. Không bắt buộc phải có.
Ở Value_if_true và Value_if_false chỉ cần có 1 trong 2 là được, không bắt buộc phải có cả hai.
Ví dụ:.
Hàm IFERROR và IFNA
Trong Excel haai hàm này sẽ có chức năng dùng để kiểm tra lỗi trong một công thức tính nhất định, và nếu có lỗi xảy ra, các hàm MS Excel trả lại một giá trị được chỉ định thay thế. Công thức của hai hàm này là:.
IFERROR(Value, Value_if_error).
Trong đó :.
Value là đối số cần kiểm tra, có thể là phép tính, công thức, hàm Excel, là giá trị bắt buộc.
Value_if_error là giá trị trả về mà chỉ định nếu công thức bị lỗi.
IFNA(Value, Value_if_na).
Trong đó:.
Value: là giá trị, kết quả của 1 công thức có thể xảy ra lỗi #N/A.
Value_if_na: là giá trị thay thế cho thông báo lỗi #N/A nếu có xảy ra lỗi này.
Ví dụ:.
Các hàm có chức năng toán học trong Excel
Hàm SUM
Với những ai thường xuyên thực hiện với các dữ liệu trên Excel thì hàm SUM không xa lạ. Hàm SUM là loại hàm được dùng để tính tổng. Công thức của hàm:.
SUM (number 1, number 2, …).
Trong đó: number 1, number 2, … Là các đối số mà bạn muốn tính tổng.
Ví dụ:.
Hàm SUMIF và SUMIFS
Ngoài hàm SUM thì còn có hàm SUMIF và SUMIFS để có thể cộng những dữ liệu đã đáp ứng một điều kiện nhất định. Và Ssự khác nhau của SUMIF có thể đánh giá chỉ một tiêu chuẩn, trong khi SUMIFS, có thể dùng nhiều tiêu chí. Công thức của hàm:.
SUMIF(range, criteria, [sum_range]).
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).
Trong đó:.
Range là dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, bắt buộc.
Criteria là điều kiện cần phải đáp ứng.
Criteria_range1 là dải ô đầu tiên được đánh giá theo các điều kiện liên quan, bắt buộc.
Sum_range là các ô tổng hợp nếu điều kiện được đáp ứng.
Criteria_range2, criteria2, … Là các dải được thêm vào và điều kiện liên quan tới các dải này.
Ví dụ:.
Hàm SUMPRODUCT
Hàm SUMPRODUCT là hàm chức năng của Microsoft Excel dùng để xử lý mảng, giúp người xử lý dữ liệu nhân các con số trong mảng xác định, rồi trả về tổng của các tích số đó. Công thức của hàm là:.
SUMPRODUCT(array1, [array2], [array3], …).
Trong đó: Array 1, Array 2 là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại.
Ví dụ:.
Các hàm thống kê trong Excel
Hàm tìm ra giá trị lớn nhất, nhỏ nhất và trung bình
MIN(number1, [number2], …) Sẽ trả về giá trị nhỏ nhất từ danh sách dữ liệu.
MAX(number1, [number2], …) Sẽ trả về giá trị lớn nhất từ danh sách dữ liệu.
AVERAGE(number1, [number2], …) Sẽ trả giá trị trung bình của các dữ liệu.
Trong đó: Number1, number2, … Là các đối số hoặc vùng dữ liệu.
SMALL(array, k) sẽ trả về giá trị nhỏ nhất thứ k của dữ liệu.
LARGE (array, k) sẽ trả về giá trị lớn nhất thứ k của dữ liệu.
Trong đó:.
Array là một mảng hoặc một dải ô để trích xuất giá trị nhỏ nhất.
K là một số nguyên cho biết được vị trí từ giá trị thấp nhất trở về, tức là k là thứu nhỏ nhất.
Ví dụ của các hàm trên:.
Các hàm đếm ô
COUNT(value1, [value2], …) Sẽ trả về số lượng các giá trị số (cả giá trị số và ngày) trong danh sách các dữ liệu.
COUNTA(value1, [value2], …) Sẽ trả về số ô có chứa dữ liệu trong danh sách các dữ liệu, hàm này sẽ đếm các ô có chứa bất kỳ thông tin, bao gồm các giá trị lỗi và các chuỗi văn bản rỗng.
Trong đó: Value1, value2,… Là những ô cần đếm hoặc một vùng cần đếm.
COUNTBLANK(range) sẽ đếm số ô trống trong một phạm vi xác định, các ô có chuỗi văn bản trống cũng tính là các ô trống.
COUNTIF(range, criteria) sẽ tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) Sẽ đếm số ô đáp ứng được tất cả các tiêu chí được xác định.
Trong đó:.
Range là vùng dữ liệu cần đếm.
Criteria là điều kiện để đếm.
Ví dụ:.
Những hàm tìm kiếm và các hàm liên quan trong Excel
Hàm VLOOKUP
Hàm VLOOKUP có thể tìm một giá trị được chỉ định trong cột. Công thức của hàm:.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).
Trong đó:.
Lookup_value là giá trị cần tìm kiếm.
Table_array là bảng chứa giá trị cần dò tìm.
Col_index_num là thứ tự của cột chứa giá trị dò tìm trên table_array.
Range_lookup là phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
Ví dụ:.
Hàm INDEX
Hàm INDEX giúp bạn sẽ trả về giá trị của một ô dữ liệu có chỉ số hàng và số cột đang tìm kiếm. Công thức của hàm:.
INDEX(Array, Row_num, [Column_num]).
Trong đó:.
Ví dụ:.
Hàm MATCH
Hàm Match trong excel là hàm trả về số thứ tự của giá trị cần tìm trong bảng. Công thức của hàm Match:.
MATCH (Lookup_Value, Lookup_array, [Match_type]).
Trong đó:.
Lookup_Value: Giá trị cần tìm kiếm.
Lookup_array: Mảng chứa giá trị cần tìm.
Match_type: Kiểu tìm kiếm.
Ví dụ:.
Hàm INDIRECT
Hàm INDIRECT trong Excel được sử dụng để tham chiếu gián tiếp các ô, dãy, các trang tính hoặc các bảng tính khác. Có công thức là:.
INDIRECT (Ref_text, [a1]).
Trong đó:.
Ref_text – là một ô tham chiếu, hoặc một tham chiếu đến ô ở dạng một chuỗi văn bản, hoặc một mảng mà đã được đặt tên.
A1 – là một giá trị logic xác định loại tham chiếu của đối số ref_text.
Ví dụ:.
Hàm HYPERLINK
Dùng để tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet. Công thức:.
HYPERLINK(link_location, [friendly_name]).
Trong đó:.
Link_locationlà đường dẫn đến trang web hay tệp tin đã được mở sẵn.
Link_location là tham chiếu đến một ô có chứa liên kết hoặc một chuỗi ký tự được bao gồm trong các dấu ngoặc kép có chứa một đường dẫn đến một tệp tin được lưu trữ trên ổ cứng,….
Friendly_name là nội dung liên kết được hiển thị trong ô tính. Nếu bạn bỏ qua thì đối số link_location sẽ được hiển thị là nội dung liên kết luôn.
Các hàm tài chính trong Excel
Hàm FV là một hàm tài chính trong Excel. Hàm này sẽ tính giá trị tại một thời điểm trong tương lai mà các nhà đầu tư sẽ nhận được khi họ đầu tư một kỳ hay nhiều kỳ khi lãi suất không đổi. Công thức:.
FV(Rate,Nper,Pmt,Pv,Type).
Trong đó:.
Rate: tỷ lệ lãi suất mỗi kỳ (tính theo tháng, quý, năm).
Nper: tổng số kỳ chi trả.
Pmt: số tiền chi trả trong mỗi kỳ, cố định trong suốt giai đoạn đầu tư.
Pv: giá trị đầu tư ban đầu. Nếu để trống thì Pv=0.
Type: =1 nếu số tiền trả đầu kỳ,=0 nếu số tiền trả cuối kỳ.
Ví dụ: Gửi 20 triệu vào tài khoản tiết kiệm với lãi suất 6%/năm (0.5% tháng), đầu mỗi tháng bạn gửi vào tài khoản 2 triệu trong vòng 12 tháng. Tính xem trong tài khoản của bạn sẽ có bao nhiêu tiền sau khi kết thúc 12 tháng (Nếu số tiền gốc gửi từ đầu kỳ trước, số tiền gửi thêm bắt đầu từ kỳ tiếp theo).
Những hàm liên quan đến ngày tháng năm
Hàm DATE trả về một ngày được chỉ định dưới dạng số.
DATE(year,month,day).
Hàm DATEVALUE chuyển đổi một chuỗi văn bản đại diện cho ngày để định dạng ngày.
DATEVALUE(Date_text).
Hàm TODAY trả về ngày hiện tại.
TODAY().
Hàm NOW trả về ngày và thời gian hiện tại.
NOW().
Hàm EDATE trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu.
EDATE(start_date, months).
Hàm về thời gian trong Excel
Hàm TIME trả về thời gian dưới dạng số.
TIME(hour, minute, second).
Hàm TIMEVALUE chuyển đổi một thời gian nhập dưới dạng một chuỗi văn bản thành một số thể hiện thời gian dưới dạng số.
TIMEVALUE(time_text).
Hàm HOUR chuyển đổi một số thành một giờ dạng số.
HOUR(serial_number).
Hàm MINUTE chuyển đổi một số thành phút dưới dạng số.
MINUTE(serial_number).
Trong Excel có rất nhiều loại hàm nhưng bài viết này nói về các hàm trong Excel mà thường được sử dụng nhất, các hàm thông dụng nhất. Đối với Excel nếu bạn muốn ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Hi vọng bài viết này sẽ có ích cho các bạn.
Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel.
Team Content Uniace.