Trọn bộ các hàm cơ bản trong Excel mà bạn cần phải biết (Update mới nhất)

Việc sử dụng các hàm sẵn có trên Excel sẽ giúp bạn thống kế các số liệu trong công việc. Sau đây, mình sẽ giới thiệu chi tiết trọn bộ các hàm cơ bản trong Excel mà bạn cần phải biết để áp dụng cho công việc khi cần thiết nhé!

1. Các hàm văn bản trong Excel

– Hàm TEXT

Hàm TEXT được sử dụng để chuyển đổi giá trị số sang dạng văn bản.

Trường hợp để sử dụng hàm TEXT là:

+ Ngày tháng hiển thị theo định dạng cụ thể.

+ Để hiển thị số dễ đọc, theo định dạng dễ dàng hơn cho người dùng.

+ Hiển thị số, ngày tháng, kí tự theo định dạng nhất định.

Ví dụ: Để thêm ”thứ” vào trước định dạng ngày của cột A1, chuyển sang hiển thị ‘” Saturday 11,2019”.

Công thức: =TEXT(A1, ”dddd mm,yyyy”)

Công thức sử dụng hàm TEXT

– Hàm CONCATENATE

Hàm CONCATENATE hay còn gọi là hàm kết hợp 2 hay nhiều ô vào một ô nhất định.

Những trường hợp bạn có thể dùng hàm CONCATENATE như:

+ Kết hợp 2 ô có chữ và số vào cùng một ô tạo thành 1 nội dung văn bản.

+ Có thể dùng kết hợp với các hàm khác như hàm TEXT để tính ra giá trị tổng hoặc kết quả tổng hợp.

Ví dụ: Bạn muốn hợp nhất 5 nội dung ở 5 ô khác nhau vào cùng 1 ô, gõ công thức =CONCATENATE(A1,B1,C1,D1,E1,).

Hàm kết hợp CONCATENATE gộp nhiều ô thành một

– Hàm TRIM

Hàm TRIM được dùng để loại bỏ khoảng trống dư thừa trong 1 nội dung văn bản, một chuỗi văn bản dài.

Ví dụ: Bạn nhập nội dung trong 1 ô bị thừa khoảng trống, bạn dùng công thức =TRIM(A10).

Sử dụng hàm TRIM để loại bỏ khoảng trống dư thừa

– Hàm SUBSTITUTE

Hàm SUBSTITUTE dùng để thay thế một hoặc nhiều kí tự, thay thế cho những kí tự trong một nội dung hiện tại.

Những trường hợp có thể sử dụng hàm SUBSTITUTE như:

+ Loại bỏ ngắt dòng trong một ô.

+ Chuyển đổi chuỗi văn bản với dấu phân cách thay thế thành ngày.

Ví dụ: Thay thế kí tự “dấu phẩy” thành “dấu /”, sự dụng công thức =SUBSTITUE(A10, “,”,”/”).

Dùng hàm TRIM để thay thế nội dung văn bản, kí tự trong 1 ô

– Hàm VALUE

Hàm VALUE dùng để chuyển đổi giá trị định dạng văn bản thành số.

Ví dụ: Chuyển đổi giá trị định dạng văn bản “020” thành định dạng số “20”, công thức =VALUE(A10).

Công thức chuyển đổi giá trị văn bản thành số bằng hàm VALUE

– Hàm sử dụng để chuyển đổi văn bản (UPPER, LOWER, PROPER)

Hàm UPPER dùng chuyển đổi chữ thường thành chữ in hoa, công thức =UPPER(ô chứa chữ cần chuyển).

fhffgfdgfdgf

Hàm LOWER dùng để chuyển đổi chữ in hoa thành chữ thường, công thức =LOWER(ô chứa cần chuyển).

Chuyển đổi chữ in hoa thành chữ thường bằng hàm LOWER

Để viết hoa các chữ cái đầu tiên bạn sử dụng hàm PROPER, công thức =PROPER(ô chứa chuỗi cần viết hoa chữ cái đầu từ).

Dùng hàm PROPER để viết hoa chữ cái đầu tiên

– Hàm trích xuất các ký tự văn bản (LEFT, RIGHT, MID)

Hàm LEFT được sử dụng để lấy các ký tự bên trái trong một chuỗi, công thức =LEFT(ô chứa chuỗi cần lấy, số ký tự muốn lấy).

Hàm LEFT dùng để lấy các ký tự từ bên trái sang trong một chuỗi

Hàm RIGHT được dùng để lấy các ký tự trong chuỗi từ phía bên phải, công thức =RIGHT(ô chứa chuỗi cần lấy, số ký tự muốn lấy).

Hàm RIGHT dùng để lấy các ký tự từ phải sang trong 1 chuỗi

Hàm MID dùng để lấy chuỗi ký tự bất kì từ trái sang và kết thúc ở một ký tự cần lấy, công thức =MID(ô chứ chuỗi cần lấy, vị trí lấy, số ký tự cần lấy).

Hàm MID dùng để lấy chuỗi ký tự bất kì và kết thúc ở môt ký tự cần lấy

– Hàm logic

Hàm AND dùng để trả về TRUE khi tất cả đối số đánh giá TRUE, FALSE nếu ngược lại, công thức =AND(logical1,[logical2], …).

Hàm AND dùng để trả giá trị đối số về TRUE, FALSE

Hàm OR dùng để trả về TRUE nếu ít nhất một trong đối số là TRUE, công thức =OR(logical1, [logical2], …).

Hàm OR dùng để trả về TRUE nếu ít nhất 1 trong đối số là TRUE

Hàm NOT dùng để đảo ngược giá trị đối số, nếu giá trị đối số là TRUE thì hàm NOT sẽ đổi thành FALSE và ngược lại, ví dụ công thức =NOT(TRUE).

Hàm NOT dùng để đảo ngược giá trị đối số từ TRUE thành FALSE hoặc ngược lại

– Hàm IF

Hàm IF dùng để kiểm tra điều kiện và trả về giá trị dựa trên điều kiện bạn chỉ định, công thức =IF(logical_test, [value_if_true], [value_if_false])​.

Hàm IF dùng để trả giá trị dựa trên điều kiện bạn chỉ định


– Hàm IFERROR và IFNA

Hàm IFERROR dùng để xử lý tất cả lỗi, IFNA chỉ xử lý lỗi #N/A, cả 2 đều dùng để kiểm tra lỗi trong một công thức và trả lại một giá trị thay thế theo chỉ định.

Công thức =IFERROR(value, value_if_error).

Hàm kiểm tra lỗi IFERORR

Công thức =IFNA(value, value_if_na).

Hàm kiểm tra lỗi IFNA

2. Các hàm công thức toán học trong Excel

– Hàm tìm tổng của các ô

Dùng để tính tổng giá trị của các ô mà bạn chọn.

+ Hàm SUM

Dùng để cộng các giá trị trong các ô và trả về một kết quả, công thức =SUM(number1,[number2],…).

Hàm SUM dùng để tính tổng các giá trị

+ Hàm SUMPRODUCT​

Dùng để xử lý các thành phần theo mảng giá trị và trả về giá trị tổng sản phẩm, công thức =SUMPRODUCT(array1,array2, …)​.

Tính tổng số lượng sản phẩm bằng hàm SUMPRODUCT

– Hàm làm tròn

Hàm ROUND làm tròn giá trị số =5 thì làm tròn lên, công thức =ROUND(ô chứa giá trị cần làm tròn).

Làm tròn giá trị bằng hàm ROUND

Hàm ROUNDUP làm tròn giá trị lên, công thức =ROUNDUP(ô chứa giá trị cần làm tròn).

Làm tròn giá trị lên bằng hàm ROUNDUP

Hàm ROUNDDOWN làm tròn giá trị xuống, công thức =ROUNDDOWN(ô chứa giá trị cần làm tròn).

Làm tròn giá trị xuống bằng hàm ROUNDDOWN

Hàm MROUND làm tròn đến bội số chỉ định, công thức =MROUND(ô chứa giá trị cần làm tròn).

Làm tròn đến bội số chỉ định bằng MROUND

Hàm FLOOR làm tròn xuống đến bội số chỉ định, công thức =FLOOR(ô chứa giá trị cần làm tròn).

Làm tròn xuống đến bội số chỉ định bằng FLOOR

Hàm CEILING làm tròn số lên đến bội số chỉ định, công thức =CEILING(ô chứa giá trị cần làm tròn).

Làm tròn số lên đến bội số chỉ định bằng CEILING

Hàm INT làm tròn đến số nguyên gần nhất, công thức =INT(ô chứa giá trị cần làm tròn).

Làm tròn số đến số nguyên gần nhất bằng INT

Hàm TRUNC cắt bớt số đến số nguyên gần nhất, công thức =TRUNC(ô chứa giá trị cần làm tròn).

Cắt bớt số đến số nguyên gần nhất bằng TRUNC

Hàm EVEN làm tròn đến số nguyên chẵn gần nhất, công thức =EVEN(ô chứa giá trị cần làm tròn).

Làm tròn đến số nguyên chẵn gần nhất bằng EVEN

Hàm ODD làm tròn đến số nguyên lẻ gần nhất, công thức =ODD(ô chứa giá trị cần làm tròn).

Làm tròn đến số nguyên lẻ gần nhất bằng ODD

– Hàm trả về số dư sau khi chia (hàm MOD)

Hàm MOD dùng để trả về số dư sau khi chia, công thức =MOD(number, divisor).

3. Các hàm thống kê trong Excel

– Hàm tìm các giá trị lớn nhất, nhỏ nhất và trung bình

Hàm MIN trả về giá trị nhỏ nhất từ danh sách các đối số, công thức =MIN(number1, [number2], …).

Hàm MAX trả về giá trị lớn nhất từ danh sách các đối số, công thức =MAX(number1, [number2], …).

Hàm AVERAGE trả giá trị trung bình của các đối số, công thức =AVERAGE(number1, [number2], …).

Hàm SMALL trả về giá trị nhỏ nhất thứ n trong mảng, công thức =SMALL(array, n).

Hàm LARGE trả về giá trị lớn nhất thứ n trong mảng, công thức =LARGE(mảng, n).

– Hàm đếm ô

Hàm COUNT trả về số lượng các giá trị số (số và ngày) trong danh sách các đối số, công thức =COUNT(value1, [value2], …)

Hàm COUNTA trả về số ô có chứa dữ liệu trong danh sách các đối số và đế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 (“”) được trả về bởi các công thức khác, công thức =COUNTA(value1, [value2], …)

Hàm COUNTBLANK đế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 được tính là các ô trống, công thức =COUNTBLANK(range).

Hàm COUNTIF tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định, công thức =COUNTIF(range, criteria).

Hàm COUNTIFS dùng đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định, công thức =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…).

4. Hàm lookup trong Excel và các hàm liên quan

– Hàm VLOOKUP

Hàm VLOOKUP dùng tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác, công thức =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).

Lookup_value – giá trị cần tìm kiếm.Table_array – hai hoặc nhiều cột dữ liệu.Col_index_num – số cột để kéo dữ liệu.Range_lookup – xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).

– Hàm INDEX

Hàm INDEX 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ông thức =INDEX(array, row_num, [column_num]).

– Hàm MATCH

Hàm MATCH 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ông thức =MATCH(lookup_value, lookup_array, [match_type]).

– Hàm INDIRECT

Hàm NDIRECT trả về 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 =NDIRECT(ref_text, [a1])​.

– Hàm OFFSET

Hàm OFFSET dùng trả về một tham chiếu đến một dãy ô được tính từ một ô bắt đầu hoặc một dãy các ô theo số hàng và cột được chỉ định, công thức =OFFSET(reference, rows, cols, [height], [width]).

– Hàm TRANSPOSE

Hàm TRANSPOSE dùng để biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng, công thức =TRANSPOSE(array).

– Hàm HYPERLINK

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]).

5. Các hàm tài chính trong Excel

– Hàm FV

Hàm FV dùng để tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định, công thức =FV(rate, nper ,pmt ,[pv], [type]).

6. Hàm ngày tháng trong Excel

– Hàm tạo lập ngày tháng

DATE trả về một ngày được chỉ định dưới dạng số sê ri.DATEVALUE dùng để chuyển đổi một chuỗi văn bản đại diện cho ngày để định dạng ngày.

– Hàm ngày và giờ hiện thời

TODAY trả về ngày hiện tại.NOW trả về ngày và thời gian hiện tại.

– Hàm trích ra ngày tháng và các thành phần ngày tháng

DAY trả về ngày trong tháng.

MONTH trả về tháng của một ngày được chỉ định.

YEAR trả về năm của một ngày nhất định.

EOMONTH trả về ngày cuối cùng của tháng.

WEEKDAY trả về ngày trong tuần.

WEEKNUM trả về số tuần của một ngày.

– Hàm tính chênh lệch ngày

DATEDIF trả về sự khác biệt giữa hai ngày.

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.

YEARFRAC trả về tỷ lệ của một khoảng thời gian trong một năm.

– Hàm tính các ngày làm việc

WORKDAY dụng trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó.

WORKDAY.INTL tính ngày tháng là số ngày trong tuần được chỉ định trước hoặc sau ngày bắt đầu, với thông số cuối tuần tùy chỉnh.

NETWORKDAYS trả về số ngày làm việc giữa hai ngày được chỉ định.

NETWORKDAYS.INTL trả về số ngày làm việc giữa hai ngày được chỉ định với ngày cuối tuần tùy chỉnh.

– Hàm thời gian trong Excel

TIME(hour, minute, second) trả về thời gian dưới dạng số sê ri.

TIMEVALUE(time_text) 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ố sê ri.

NOW(serial_number) trả về số tương ứng với ngày và thời gian hiện tại dạng số sê ri.

HOUR(serial_number) chuyển đổi một số thành một giờ dạng số sê ri.

MINUTE(serial_number) chuyển đổi một số thành phút dưới dạng số sê ri.

SECOND(serial_number) chuyển đổi một số thành giây dưới dạng số sê ri.

– Hàm đếm và tính tổng các ô theo màu sắc (chức năng do người dùng xác định)

GetCellColor(cell) trả về mã màu của màu nền thuộc một ô xác định.

GetCellFontColor(cell) trả về mã màu của màu phông chữ thuộc một ô xác định.

CountCellsByColor(range, color code) đếm các ô có màu nền được chỉ định.

CountCellsByFontColor(range, color code) đếm các ô có màu phông chữ được chỉ định.

SumCellsByColor(range, color code) tính tổng của các ô có màu nền nhất định.

SumCellsByFontColor(range, color code) trả về tổng của các ô với một màu chữ nhất định.

WbkCountCellsByColor(cell) tính các ô có màu nền được chỉ định trong toàn bộ bảng tính.

WbkSumCellsByColor(cell) tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.

5>

Từ khóa: Excel, hàm Excel, hàm cơ bản trong Excel, hàm excel cơ bản

28 lượt xem

Chấm điểm bài viết

Chấm điểm

Cám ơn bạn đã đánh giá bài viết

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *