Sổ tay 40 hàm cơ bản trong Excel

Sử dụng ứng dụng Excel thì làm việc với các hàm cơ bản trong Excel là việc rất thường xuyên. Chúng giúp thực hiện tính toán dữ liệu trực tiếp trên bảng tính nhanh và tiện lợi hơn như hàm Excel tính toán, thống kê,... sẽ rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự. Vì thế, hãy chắc chắn rằng bạn đã thuộc nằm lòng những hàm Excel cơ bản này. Mời bạn tham khảo nhé.

 

1. Hàm tính tổng - SUM

Cấu trúc hàm:

=SUM(Number 1, Number 2, ....)

Number 1: Số thứ nhất

Number 2: Số thứ 2,.....

=SUM(Vùng tính tổng)

 

2. Hàm tính nhân - PRODUCT

Cấu trúc hàm:

=PRODUCT(Number 1, [Number 2], ....)

Number 1: Số thứ nhất

Number 2: Số thứ 2,.....

=PRODUCT(Vùng tính nhân)

 

3. Hàm tính trung bình - AVERAGE

Cấu trúc hàm:

=AVERAGE(Number 1, [Number 2], ....)

Number 1: Số thứ nhất

Number 2: Số thứ 2,.....

=AVERAGE(Vùng tính trung bình)

 

4. Hàm tìm số nhỏ nhất trong mảng - MIN

Cấu trúc hàm:

=MIN(Number 1, [Number 2], ....)

Number 1: Số thứ nhất

Number 2: Số thứ 2,.....

=MIN(Vùng cần tìm số nhỏ nhất)

 

5. Hàm tìm số lớn nhất trong mảng - MAX

Cấu trúc hàm:

=MAX(Number 1, [Number 2], ....)

Number 1: Số thứ nhất

Number 2: Số thứ 2,.....

=MAX(Vùng cần tìm số lớn nhất)

 

6. Hàm đếm dữ liệu số trong mảng - COUNT

Cấu trúc hàm:

=COUNT(Value 1, [Value 2], ....)

Value 1: Giá trị thứ nhất

Value 2: Giá trị thứ 2,.....

=COUNT(Vùng cần đếm giá trị số)

 

7. Hàm đếm dữ liệu trong mảng - COUNTA

Cấu trúc hàm:

=COUNTA(Value 1, [Value 2], ....)

Value 1: Giá trị thứ nhất

Value 2: Giá trị thứ 2,.....

=COUNTA(Vùng cần đếm giá trị)

 

8. Hàm trả về vị trí dòng của ô dữ liệu - ROW

Cấu trúc hàm:

=ROW(reference)

Reference: Ô hoặc dòng cần xác định vị trí dòng

Ghi chú: Hàm ROW được dùng kết hợp với hàm tìm kiếm VLOOKUP, HLOOKUP giúp hàm tìm kiếm trở nên tự động hơn khi có sự thay đổi trong bảng tính.

 

9. Hàm trả về vị trí cột của ô dữ liệu - COLUMN

Cấu trúc hàm:

=COLUMN(reference)

Reference: Ô hoặc cột cần xác định vị trí cột

Ghi chú: Hàm COLUMN được dùng kết hợp với hàm tìm kiếm VLOOKUP, HLOOKUP giúp hàm tìm kiếm trở nên tự động hơn khi có sự thay đổi trong bảng tính.

 

10. Hàm làm tròn đến số nguyên gần nhất - INT

Cấu trúc hàm:

=INT(number)

Number: Ô hoặc Số thập phân cần làm tròn

 

11. Hàm làm tròn đến số thập phân xác định - ROUND

Cấu trúc hàm:

=ROUND(number, num_digits)

Number: Ô hoặc Số thập phân cần làm tròn

Num_digits: Số sau thập phân cần lấy

 

12. Hàm trả về số nguyên lẻ gần nhất - ODD

Cấu trúc hàm:

=ODD(number)

Number: Ô hoặc Số cần trả về số nguyên lẻ

 

13. Hàm trả về số nguyên chẵn gần nhất - EVEN

Cấu trúc hàm:

=EVEN(number)

Number: Ô hoặc Số cần trả về số nguyên chẵn

 

14. Hàm đo chiều dài chuỗi văn bản - LEN

Cấu trúc hàm:

=LEN(text)

Text: Ô hoặc Chuỗi ký tự cần đo chiều dài

 

15. Hàm cắt bỏ khoảng trống trong chuỗi văn bản - TRIM

Cấu trúc hàm:

=TRIM(text)

Text: Ô hoặc Chuỗi ký tự cần cắt bỏ khoảng trống

 

16. Hàm trả về ngày hiện tại - TODAY

Cấu trúc hàm:

=TODAY()

Trong () không điền bất kỳ dữ liệu nào.

 

17. Hàm trả về ngày giờ hiện tại - NOW

Cấu trúc hàm:

=NOW()

Trong () không điền bất kỳ dữ liệu nào.

 

18. Hàm rút ngày của ô dữ liệu trả về dạng số - DAY

Cấu trúc hàm:

=DAY(serial_number)

Serial_number: Ô hoặc Nhập ngày/tháng/năm cần rút ra giá trị ngày

 

19. Hàm rút tháng của ô dữ liệu trả về dạng số - MONTH

Cấu trúc hàm:

=MONTH(serial_number)

Serial_number: Ô hoặc Nhập ngày/tháng/năm cần rút ra giá trị tháng

 

20. Hàm rút năm của ô dữ liệu trả về dạng số - YEAR

Cấu trúc hàm:

=YEAR(serial_number)

Serial_number: Ô hoặc Nhập ngày/tháng/năm cần rút ra giá trị năm

 

21. Hàm tạo ngày tháng năm từ các ô số độc lập - DATE

Cấu trúc hàm:

=DATE(year,month,day)

Year: Ô chứa số hoặc nhập số năm

Month: Ô chứa số hoặc nhập số tháng

Day: Ô chứa số hoặc nhập số ngày

Hàm để kết hợp các ô năm, tháng, ngày độc lập thành một ô có định dạng ngày/tháng/năm (dd/mm/yy)

 

22. Hàm trả về thứ trong tuần dạng số - WEEKDAY

Cấu trúc hàm:

=WEEKDAY(serial_number, [return_type])

Serial_number: Ô hoặc Nhập ngày/tháng/năm cần trả về số thứ tự thứ trong tuần

Return_type: Loại định dạng thứ tự thứ

• (...)1 - Numbers 1 - (Sunday) through 7 (Saturday) ► Chọn 1 kết quả sẽ trả về 1 cho ngày chủ nhật đến 7 cho ngày thứ 7
• (...)2 - Numbers 1 - (Monday) through 7 (Sunday) ► Chọn 1 kết quả sẽ trả về 1 cho ngày thứ 2 đến 7 cho ngày chủ nhật
• (...)3 - Numbers 0 - (Monday) through 6 (Sunday) ► Chọn 3 kết quả sẽ trả về 0 cho ngày thứ 2 đến 6 cho ngày chủ nhật
• (...)11 - Numbers 1 - (Monday) through 7 (Sunday) ► Chọn 11 kết quả sẽ trả về 1 cho ngày thứ 2 đến 7 cho ngày chủ nhật
• ....

 

23. Hàm trả về thứ trong tuần dạng text - TEXT

Cấu trúc hàm:

=TEXT(value, format_text)

Value: Ô ngày/tháng/năm cần trả về thứ trong tuần

Format_text: Kiểu định dạng trả về thứ trong tuần

• Kiểu United state: Định dạng "dd" trả về dạng số, "ddd" trả về dạng text ngắn gọn Mon, Tue,... "dddd" trả về dạng text đầy đủ Monday, Tuesday,...
• Kiểu Vietnamese: Định dạng "[$-42a]ddd" trả về dạng ngắn gọn T2, T3, T4,... "[$-42a]dddd" trả về dạng text đầy đủ Thứ hai, Thứ ba,....

 

24. Hàm trả về thứ tự tuần trong năm - ISOWEEKNUM

Cấu trúc hàm:

=ISOWEEKNUM(date)

Date: Ô ngày/tháng/năm cần trả về thứ tuần trong năm

 

25. Hàm rút trích ký tự bên trái của chuỗi văn bản - LEFT

Cấu trúc hàm:

=LEFT(text, [num_chars])

Text: Ô hoặc chuỗi ký tự cần rút trích. Chuỗi ký tự cần đặt trong dấu "xxx"

Num_chars: Số ký tự cần rút trích từ bên trái

 

26. Hàm rút trích ký tự bên phải của chuỗi văn bản - RIGHT

Cấu trúc hàm:

=RIGHT(text, [num_chars])

Text: Ô hoặc chuỗi ký tự cần rút trích. Chuỗi ký tự cần đặt trong dấu "xxx"

Num_chars: Số ký tự cần rút trích từ bên phải

 

27. Hàm rút trích ký tự ở giữa của chuỗi văn bản - MID

Cấu trúc hàm:

=MID(text, start_num, num_chars)

Text: Ô hoặc chuỗi ký tự cần rút trích. Chuỗi ký tự cần đặt trong dấu "xxx"

Start_num: Vị trí ký tự bắt đầu cần rút trích, là một giá trị số

Num_chars: Số ký tự cần rút trích tính từ vị trí xác định bắt đầu

 

28. Hàm điều kiện - IF

Cấu trúc hàm:

=IF(logical_test, [value_if_true], [value_if_false])

Logical_test: Điều kiện cho hàm IF

• Số > số
• Số >= số
• Số < số
• Số <= số
• Số, dữ liệu = số, dữ liệu. Đối với dữ liệu được dịch nghĩa "là". Ví dụ: Nếu là "Nam", Nếu nơi ở là "Bình Dương". Dữ liệu chữ phải đặt trong nháy kép "xxx"

Value_if_true: Trả về kết quả đúng (Kết quả 1) nếu điều kiện xảy ra

Value_if_false: Trả về kết quả sai (Kết quả 2) nếu điều kiện không xảy ra

Lưu ý: Dấu "," trong hàm IF được dịch nghĩa "thì", "còn lại"

 

29. Hàm tính tổng có 1 điều kiện - SUMIF

Cấu trúc hàm:

=SUMIF(range, criteria, [sum_range])

Range: Vùng dữ liệu chứa điều kiện cần tính tổng

Criteria: Điều kiện cần chọn để tính tổng

Sum_range: Vùng dữ liệu cần tính tổng.

Lưu ý: Kích thước vùng tính tổng cần phải bằng kích thước vùng dữ liệu chứa điều kiện

 

30. Hàm tính tổng có từ 2 điều kiện trở lên - SUMIFS

Cấu trúc hàm:

=SUMIFS(sum_range, criteria_range 1, criteria 1, criteria_range 2, criteria 2,...)

Sum_range: Vùng dữ liệu cần tính tổng.

Criteria_range 1: Vùng dữ liệu chứa điều kiện 1 cần tính tổng

Criteria 1: Điều kiện 1 cần chọn để tính tổng

Criteria_range 2: Vùng dữ liệu chứa điều kiện 2 cần tính tổng

Criteria 2: Điều kiện 2 cần chọn để tính tổng

.....

Lưu ý: Kích thước vùng tính tổng cần phải bằng kích thước các vùng dữ liệu chứa điều kiện 1, 2, ....

 

31. Hàm đếm có 1 điều kiện - COUNTIF

Cấu trúc hàm:

=COUNTIF(range, criteria)

Range: Vùng dữ liệu chứa điều kiện cần đếm

Criteria: Điều kiện cần chọn để đếm

 

32. Hàm đếm có từ 2 điều kiện trở lên - COUNTIFS

Cấu trúc hàm:

=COUNTIFS(criteria_range 1, criteria 1, criteria_range 2, criteria 2,...)

Criteria_range 1: Vùng dữ liệu chứa điều kiện 1 cần đếm

Criteria 1: Điều kiện 1 cần chọn để đếm

Criteria_range 2: Vùng dữ liệu chứa điều kiện 2 cần đếm

Criteria 2: Điều kiện 2 cần chọn để đếm

.....

Lưu ý: Kích thước vùng đếm giữa các điều kiện phải bằng nhau

 

33. Hàm tính trung bình có 1 điều kiện - AVERAGEIF

Cấu trúc hàm:

=AVERAGEIF(range, criteria, [average_range])

Range: Vùng dữ liệu chứa điều kiện cần tính trung bình

Criteria: Điều kiện cần chọn để tính trung bình

Average_range: Vùng dữ liệu cần tính trung bình

Lưu ý: Kích thước vùng tính trung bình cần phải bằng kích thước vùng dữ liệu chứa điều kiện

 

34. Hàm tính trung bình có từ 2 điều kiện trở lên - AVERAGEIFS

Cấu trúc hàm:

=AVERAGEIFS(average_range, criteria_range 1, criteria 1, criteria_range 2, criteria 2,...)

Average_range: Vùng dữ liệu cần tính trung bình

Criteria_range 1: Vùng dữ liệu chứa điều kiện 1 cần tính trung bình

Criteria 1: Điều kiện 1 cần chọn để tính trung bình

Criteria_range 2: Vùng dữ liệu chứa điều kiện 2 cần tính trung bình

Criteria 2: Điều kiện 2 cần chọn để tính trung bình

.....

Lưu ý: Kích thước vùng tính trung bình cần phải bằng kích thước các vùng dữ liệu chứa điều kiện 1, 2, ....

35. Hàm tính tổng, đếm, trung bình,... loại trừ giá trị ẩn - SUBTOTAL

Cấu trúc hàm:

=SUBTOTAL(function_num, ref 1,...)

Function_num: Số chức năng, mỗi số được chọn sẽ áp dụng cho một hàm chức năng riêng biệt

• (...)1 - Average: Số 1 được chọn thì hàm SUBTOTAL sẽ có chức năng như hàm tính trung bình AVERAGE
• (...)2 - Count: Số 2 được chọn thì hàm SUBTOTAL sẽ có chức năng như hàm đếm số COUNT
• .....
• (...)101 - Average: Số 101 được chọn thì hàm SUBTOTAL sẽ có chức năng như hàm tính trung bình AVERAGE, có loại trừ những ô bị ẩn
• (...)102 - Count: Số 102 được chọn thì hàm SUBTOTAL sẽ có chức năng như hàm đếm số COUNT, có loại trừ những ô bị ẩn
• (...)109 - Count: Số 109 được chọn thì hàm SUBTOTAL sẽ có chức năng như hàm tính tổng SUM, có loại trừ những ô bị ẩn
• ....

Ref 1: Vùng dữ liệu cần tính cho hàm SUBTOTAL

 

36. Hàm tìm kiếm theo cột - VLOOKUP

Cách xác định hàm VLOOKUP:

Hàm VLOOKUP là hàm có giá trị dò tìm được xếp trong bảng dò tìm theo cột.

Cấu trúc hàm:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup_value: Giá trị tìm kiếm (ô dữ liệu chứa giá trị dò tìm).

Table_array: Bảng dò tìm. Bảng dò tìm là bảng dữ liệu có kích thước ít nhất gồm cột chứa giá trị dò tìm và cột kết quả. Bảng phải xuất phát từ cột chứa giá trị dò tìm.

Col_index_num: Giá trị số vị trí thứ tự của cột kết quả trong bảng dò tìm

Range_lookup: Phương pháp tìm kiếm

• (...) True - Approximate match: Tìm tương đối ► Số 1
• (...) False - Exact match: Tìm chính xác ► Số 0

Lưu ý: Thông thường ta tìm kiếm chính xác. Các bạn hãy chọn số 0 tại vị trí này.

 

 

37. Hàm tìm kiếm theo dòng - HLOOKUP

Cách xác định hàm HLOOKUP:

Hàm HLOOKUP là hàm có giá trị dò tìm được xếp trong bảng dò tìm theo hàng.

Cấu trúc hàm:

=HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup_value: Giá trị tìm kiếm (ô dữ liệu chứa giá trị dò tìm).

Table_array: Bảng dò tìm. Bảng dò tìm là bảng dữ liệu có kích thước ít nhất gồm hàng chứa giá trị dò tìm và cột kết quả. Bảng phải xuất phát từ hàng chứa giá trị dò tìm.

Col_index_num: Giá trị số vị trí thứ tự của hàng kết quả trong bảng dò tìm

Range_lookup: Phương pháp tìm kiếm

• (...) True - Approximate match: Tìm tương đối ► Số 1
• (...) False - Exact match: Tìm chính xác ► Số 0

Lưu ý: Thông thường ta tìm kiếm chính xác. Các bạn hãy chọn số 0 tại vị trí này.

 

38. Hàm tìm kiếm nâng cao linh hoạt - LOOKUP/ XLOOKUP

Cấu trúc hàm:

=LOOKUP(1,1/(Giá trị dò tìm = Cột/hàng chứa giá trị dò tìm), Cột/hàng kết quả)

=XLOOKUP(Giá trị dò tìm, Cột/hàng chứa giá trị dò tìm, Cột/hàng kết quả)

Lưu ý: Kích thước vùng chứa giá trị dò tìm phải bằng kích thước vùng kết quả

 

39. Hàm xác định vị trí trong mảng - MATCH

Hàm MATCH trả về giá trị số vị trí của giá trị dò tìm trong vùng dữ liệu cột hoặc hàng.

Cấu trúc hàm:

=MATCH(lookup_value, lookup_array, [match_type])

Lookup_value: Giá trị tìm kiếm (ô dữ liệu chứa giá trị dò tìm).

Lookup_array: Cột/hàng chứa giá trị dò tìm

Match_type: Phương pháp tìm kiếm

• (...)1 - Less than: Nhỏ hơn ► Số 1
• (...)0 - Exact match: Chính xác ► Số 0
• (...)-1 - Greater than: Lớn hơn ► Số -1

Lưu ý: Thông thường ta tìm kiếm chính xác. Các bạn hãy chọn số 0 tại vị trí này. Hàm MATCH kết hợp với hàm INDEX để dò tìm vị trí giá trị trong bảng.

 

40. Hàm dò tìm theo mảng kết hợp với MATCH - INDEX

Cấu trúc hàm:

=INDEX(array, row_num, column_num))

Array: Bảng dò tìm. Bảng phải xuất phát từ vị trí ô đầu tiên của hàm MATCH và kết thúc tại ô cuối cùng của hàm MATCH

Row_num: Giá trị số thứ tự hàng trong bảng dò tìm. Vị trí này sử dụng hàm MATCH xác định vị trí hàng cho giá trị dò tìm.

Column_num: Giá trị số thứ tự cột trong bảng dò tìm. Vị trí này sử dụng hàm MATCH xác định vị trí hàng cho giá trị dò tìm.

 

Tổng kết:

Trên đây là 40 hàm EXCEL có thể áp dụng trong hầu hết lĩnh vực công việc. Tuy nhiên tùy theo từng chuyên môn Kế toán, tài chính, bảo trì,... sẽ có những hàm chuyên sâu, các bạn hãy tìm hiểu thêm theo nhu cầu công việc cá nhân. Hy vọng bài viết sẽ đem lại những kiến thức bổ ích để mọi người có thể dễ dàng áp dụng.

Chúc bạn thành công!

Nếu bạn muốn biết thêm nhiều Kiến thức về Tin học văn phòng, hãy follow gia sư tin học TRANG KIẾN THỨC nhé.