Tổng hợp các hàm cơ bản trong Excel


1. SUM() — Tính tổng

Công dụng: Cộng tất cả các số trong phạm vi được chọn.
Cú pháp: =SUM(number1, number2, ...)
Ví dụ:
=SUM(A1:A5)
(Cộng tổng từ A1 đến A5)

2. AVERAGE() — Tính trung bình cộng

Công dụng: Tính giá trị trung bình của các số.
Cú pháp: =AVERAGE(number1, number2, ...)
Ví dụ:
=AVERAGE(B1:B5)

3. MAX() — Giá trị lớn nhất

Công dụng: Tìm số lớn nhất trong dãy.
Cú pháp: =MAX(number1, number2, ...)
Ví dụ:
=MAX(D1:D10)

4. MIN() — Giá trị nhỏ nhất

Công dụng: Tìm số nhỏ nhất trong dãy.
Cú pháp: =MIN(number1, number2, ...)
Ví dụ:
=MIN(E1:E10)

5. COUNT() — Đếm số ô chứa số

Công dụng: Đếm các ô có chứa số.
Cú pháp: =COUNT(value1, value2, ...)
Ví dụ:
=COUNT(B1:B10)

6. COUNTA() — Đếm số ô có dữ liệu

Công dụng: Đếm các ô có chứa dữ liệu.
Cú pháp: =COUNTA(value1, value2, ...)
Ví dụ:
=COUNTA(A1:A10)
(Đếm số ô chứa dữ liệu từ A1 đến A10)

7. UPPER() - Chuyển chuỗi ký tự sang IN HOA

Công dụng:

Chuyển toàn bộ chữ cái trong chuỗi văn bản thành chữ in hoa.

Cú pháp:

=UPPER(text)
  • Text: Là chuỗi văn bản bạn muốn chuyển đổi, có thể là ô chứa văn bản hoặc văn bản trực tiếp.

Ví dụ:

AB
xin chao          =UPPER(A1)XIN CHAO

8. LOWER() - Chuyển chuỗi ký tự sang chữ thường

Công dụng:

Chuyển toàn bộ chữ cái trong chuỗi văn bản thành chữ thường.

Cú pháp:

=LOWER(text)

Ví dụ:

AB
XIN CHAO        =LOWER(A1)xin chao

9. PROPER() - Chuyển chuỗi ký tự sang định dạng ký tự đầu hoa, ký tự sau thường

Công dụng:

Viết hoa chữ cái đầu tiên của mỗi từ, các chữ cái còn lại viết thường.

Cú pháp:

=PROPER(text)

Ví dụ:

AB
xin chào bạn           =PROPER(A1)Xin Chào Bạn

Lưu ý:

  • Các hàm này chỉ ảnh hưởng đến chữ cái, không thay đổi số hoặc ký tự đặc biệt.

  • Nếu văn bản không có chữ cái, kết quả sẽ giống nguyên bản.

10. LEN() — Đếm số ký tự

Công dụng: Đếm tổng số ký tự trong một ô (bao gồm cả khoảng trắng).
Cú pháp: =LEN(text)
Ví dụ:
=LEN(B2)

11. TRIM() — Xóa khoảng trắng thừa

Công dụng: Loại bỏ khoảng trắng thừa (chỉ giữ 1 khoảng trắng giữa các từ).
Cú pháp: =TRIM(text)
Ví dụ:
=TRIM(A2)

12. ROW() – Trả về số thứ tự dòng (Row number)

Cú pháp:

=ROW([ô])
  • Nếu không chỉ định ô, Excel sẽ trả về số dòng của ô chứa công thức.

  • Nếu chỉ định ô (ví dụ B5), Excel sẽ trả về số dòng của ô đó (là 5).

Ví dụ:

ÔCông thứcKết quả
A2=ROW()2
A3          =ROW(B5)            5

13. COLUMN() – Trả về số thứ tự cột (Column number)

Cú pháp:

=COLUMN([ô])
  • Nếu không chỉ định ô, Excel sẽ trả về số cột của ô chứa công thức (ví dụ cột A = 1, B = 2, …).

  • Nếu chỉ định ô (ví dụ D4), Excel sẽ trả về số cột của ô đó (D = 4).

Ví dụ:

ÔCông thứcKết quả
A2=COLUMN()1
B2          = COLUMN(D4)         4

Ứng dụng thực tế

1. Tạo số thứ tự tự động:

=ROW()-1

→ Nếu bảng bắt đầu từ dòng 2 (có tiêu đề ở dòng 1), công thức này sẽ trả về 1 ở dòng 2, 2 ở dòng 3,…

2. Xác định vị trí của một ô:

="Dòng: " & ROW(A5) & ", Cột: " & COLUMN(A5)

→ Trả về: Dòng 5, cột 1

14. NOW() — Hiển thị ngày giờ hiện tại

Công dụng: Trả về ngày và giờ hiện tại.
Cú pháp: =NOW()
Ví dụ:
Gõ =NOW() vào ô bất kỳ.

15. TODAY() — Hiển thị ngày hiện tại

Công dụng: Trả về ngày tháng hiện tại (không có giờ).
Cú pháp: =TODAY()
Ví dụ:
Gõ =TODAY()

16. DAY() – Lấy ngày

Cú pháp:

=DAY(ô hoặc ngày tháng)

Ví dụ:

=DAY("01/05/2025") → Kết quả: 1
=DAY(A2) → Nếu A2 là 15/03/2024 → Kết quả: 15

17. MONTH() – Lấy tháng

Cú pháp:

=MONTH(ô hoặc ngày tháng)

Ví dụ:

=MONTH("01/05/2025") → Kết quả: 5
=MONTH(A2) → Nếu A2 là 15/03/2024 → Kết quả: 3

18. YEAR() – Lấy năm

Cú pháp:

=YEAR(ô hoặc ngày tháng)

Ví dụ:

=YEAR("01/05/2025") → Kết quả: 2025
=YEAR(A2) → Nếu A2 là 15/03/2024 → Kết quả: 2024

Lưu ý:

  • Ô chứa ngày phải có định dạng kiểu ngày (DATE) để hàm hoạt động chính xác.

  • Các hàm này chỉ trả về giá trị số (ví dụ: tháng là "5", không phải "Tháng 5").

19. DATE() - Tạo ra một ngày từ các số độc lập

Công dụng:

Hàm DATE dùng để tạo ra một ngày hợp lệ từ năm, tháng, và ngày cụ thể.

Cú pháp:

=DATE(year, month, day)

Giải thích:

  • Year: Số nguyên chỉ năm (ví dụ: 2025).

  • Month: Số nguyên chỉ tháng (1 đến 12, có thể >12 hoặc <1 – sẽ tự điều chỉnh).

  • Day: Số nguyên chỉ ngày (1 đến 31, cũng tự điều chỉnh nếu vượt giới hạn tháng).

Ví dụ cơ bản:

AB
=DATE(2025,5,2)     👉02/05/2025 (ngày 2 tháng 5 năm 2025)

Ví dụ nâng cao: Tự điều chỉnh tháng và ngày

Công thứcKết quảGiải thích
=DATE(2025, 13, 10)👉 10/01/2026      Tháng 13 => chuyển thành tháng 1 năm sau
=DATE(2024, 2, 30)      👉 01/03/2024Tháng 2 năm nhuận có 29 ngày => cộng 1 ngày

Lưu ý quan trọng:

  • HàmDATE đảm bảo bạn tạo ra ngày hợp lệ dù đầu vào sai (vd. tháng 15 sẽ chuyển thành tháng 3 năm sau).

  • Định dạng ô kết quả nên để là Date (Ngày) để hiển thị đúng.

20. TEXT() — Định dạng văn bản theo mẫu

Công dụng: Chuyển số thành dạng văn bản theo định dạng tùy ý.
Cú pháp: =TEXT(value, format_text)
Ví dụ:
=TEXT(TODAY(), "dd/mm/yyyy")

Hàm TEXT chuyển ngày thành "thứ"

Cú pháp:

=TEXT(ngày, "dddd")

Giải thích:

  • Ngày: Là một giá trị ngày hợp lệ (có thể là ô chứa ngày, hàm Today(), hoặc DATE(...).

  • "dddd": Trả về tên đầy đủ của thứ (ví dụ: "Monday", "Thứ hai").

  • "ddd": Trả về viết tắt của thứ (ví dụ: "Mon", "Hai").

21. ROUND() — Làm tròn số

Công dụng: Làm tròn số đến số lượng chữ số xác định.
Cú pháp: =ROUND(number, num_digits)
Ví dụ:
=ROUND(A2, 2)
(Làm tròn A2 đến 2 chữ số sau dấu phẩy)

22. ROUNDUP() — Làm tròn lên

Công dụng: Luôn làm tròn số lên trên.
Cú pháp: =ROUNDUP(number, num_digits)
Ví dụ:
=ROUNDUP(B2, 0)
(Làm tròn B2 lên số nguyên gần nhất)

23. ROUNDDOWN() — Làm tròn xuống

Công dụng: Luôn làm tròn số xuống dưới.
Cú pháp: =ROUNDDOWN(number, num_digits)
Ví dụ:
=ROUNDDOWN(C2, 0)

24. CONCATENATE() — Ghép chuỗi

Công dụng: Ghép nhiều đoạn văn bản lại thành một.
Cú pháp: =CONCATENATE(text1, text2, ...)
(Excel mới thay bằng CONCAT())
Ví dụ:
=CONCATENATE(A1, " ", B1)

25. LEFT() — Lấy ký tự bên trái

Công dụng: Trích ký tự từ trái sang phải.
Cú pháp: =LEFT(text, [num_chars])
Ví dụ:
=LEFT(A2, 5)
(Lấy 5 ký tự đầu từ ô A2)

26. RIGHT() — Lấy ký tự bên phải

Công dụng: Trích ký tự từ phải sang trái.
Cú pháp: =RIGHT(text, [num_chars])
Ví dụ:
=RIGHT(A2, 3)
(Lấy 3 ký tự cuối từ ô A2)

27. MID() — Lấy ký tự giữa chuỗi

Công dụng: Trích xuất một số ký tự từ vị trí xác định trong chuỗi.
Cú pháp: =MID(text, start_num, num_chars)
Ví dụ:
=MID(A2, 3, 4)
(Lấy 4 ký tự từ ký tự thứ 3 của ô A2)

28. IF() — Hàm điều kiện

Công dụng: Trả về giá trị khác nhau tùy điều kiện đúng hoặc sai.
Cú pháp: =IF(logical_test, value_if_true, value_if_false)
Ví dụ:
=IF(C2>50, "Đạt", "Chưa đạt")

29. AND() — Kiểm tra nhiều điều kiện đúng

Công dụng: Trả về TRUE nếu tất cả điều kiện đúng.
Cú pháp: =AND(logical1, logical2, ...)
Ví dụ:
=AND(A2>50, B2<100)

30. OR() — Kiểm tra ít nhất một điều kiện đúng

Công dụng: Trả về TRUE nếu một trong các điều kiện đúng.
Cú pháp: =OR(logical1, logical2, ...)
Ví dụ:
=OR(A2>50, B2<100)

31. VLOOKUP() — Tìm kiếm theo cột

Công dụng: Tìm một giá trị trong cột đầu tiên của bảng.
Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Ví dụ:
=VLOOKUP(101, A2:C10, 2, 0)
(Tìm mã 101 và trả về giá trị cột thứ 2)

32. HLOOKUP() — Tìm kiếm theo dòng

Công dụng: Tương tự VLOOKUP nhưng tìm theo dòng ngang.
Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Ví dụ:
=HLOOKUP("Tháng 1", A1:D5, 3, FALSE)

33. XLOOKUP() — Tìm kiếm theo dòng hoặc cột

Cú pháp hàm XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Giải thích tham số:

lookup_value: Giá trị bạn muốn tìm kiếm.
lookup_array: Vùng chứa giá trị tìm kiếm.
return_array: Vùng chứa giá trị bạn muốn trả về.
[if_not_found] (tùy chọn): Giá trị trả về nếu không tìm thấy (nếu bỏ qua sẽ báo lỗi).
[match_mode] (tùy chọn): 

Một số điểm nổi bật của XLOOKUP

Không cần phải sắp xếp dữ liệu như VLOOKUP.
Tự động tìm kiếm bên trái hoặc bên phải.
Có thể trả về giá trị mặc định nếu không tìm thấy (if_not_found).
Thay thế tốt cho cả VLOOKUP, HLOOKUP, và INDEX-MATCH.

34. IFERROR() — Xử lý lỗi

Công dụng: Trả giá trị thay thế nếu công thức bị lỗi.
Cú pháp: =IFERROR(value, value_if_error)
Ví dụ:
=IFERROR(A2/B2, "Lỗi chia 0")

35. COUNTIF() — Đếm theo điều kiện

Công dụng: Đếm số ô thỏa mãn điều kiện.
Cú pháp: =COUNTIF(range, criteria)
Ví dụ:
=COUNTIF(A1:A10, ">5")
(Đếm số ô có giá trị lớn hơn 5)

36. SUMIF() — Cộng theo điều kiện

Công dụng: Cộng các giá trị thỏa điều kiện.
Cú pháp: =SUMIF(range, criteria, [sum_range])
Ví dụ:
=SUMIF(B2:B10, ">50", C2:C10)
(Cộng giá trị ở C2:C10 khi B2:B10 > 50)

37. AVERAGEIF() - Tính trung bình theo điều kiện

Công dụng:

Tính giá trị trung bình của các ô thoả mãn điều kiện cho trước.

Cú pháp:

=AVERAGEIF(range, criteria, [average_range])

Giải thích:

  • Range: Vùng dữ liệu dùng để kiểm tra điều kiện.

  • Criteria: Điều kiện dùng để lọc giá trị (có thể là số, văn bản, biểu thức logic).

  • Average_range (tùy chọn): Vùng dữ liệu dùng để tính trung bình. Nếu bỏ qua, Excel sẽ dùng Range

Ví dụ 1: Trung bình điểm các học sinh có điểm trên 7

AB
TênĐiểm
An8
Bình6
Chi9
Dũng                  5
Công thức=AVERAGEIF(B2:B5,">7")8.5
→ Chỉ tính trung bình các điểm >7: 8 và 9.

Ví dụ 2: Trung bình lương của nhân viên phòng "Kế toán"

AB
Phòng banLương
Kế toán10,000
Nhân sự8,000
Kế toán12,000
Kinh doanh             9,500
Công thức=AVERAGEIF(A2:A5, "Kế toán", B2:B5)11,000
→ Chỉ lấy lương của dòng nào có "Kế toán" ở cột A.

Một số cách dùng criteria (điều kiện)

Điều kiệnNghĩa
"=Kế toán"          Bằng "Kế toán"
">7"Lớn hơn 7
"<100"Nhỏ hơn 100
"<>0"Khác 0
"*an"Kết thúc bằng "an" (dùng ký tự đại diện *)
"B*"Bắt đầu bằng chữ "B"

Lưu ý:

  • Nếu không có giá trị nào thỏa điều kiện, hàm sẽ trả về lỗi #DIV/0!

  • Nếu bạn dùng ký tự đại diện (* hoặc ?), phải đặt điều kiện trong dấu ngoặc kép.

38. SUBTOTAL() - Tính toán đa năng

Công dụng:

Hàm SUBTOTAL dùng để tính toán theo nhiều cách khác nhau (Tổng, Trung bình, Đếm, Max, Min, v.v.) mà có thể bỏ qua các hàng ẩn hoặc bị lọc.

Cú pháp:

=SUBTOTAL(function_num, range1, [range2], ...)

Tham số:

  • function_num: Số đại diện cho loại phép tính (xem bảng bên dưới).

  • range1, range2,..: Vùng dữ liệu muốn tính toán.

Bảng function_num:

Loại phép tínhBỏ qua hàng ẩn & lọc (100+)Không bỏ qua (1-11)
AVERAGE (Trung bình)101                                                          1
COUNT (Đếm số)1022
COUNTA (Đếm ô không rỗng)    1033
MAX1044
MIN1055
PRODUCT (Tích)1066
STDEV (Độ lệch chuẩn)1077
STDEVP (Toàn bộ mẫu)1088
SUM (Tổng)1099
VAR11010
VARP11111
Nên dùng số từ 101 trở lên để tự động bỏ qua hàng đã lọc/ẩn.

Ví dụ cơ bản:

AB
Sản phẩm       Doanh thu
A100
B200
C150
Công thức=SUBTOTAL(109, B2:B4)450
→ Tính Tổng doanh thu, bỏ qua hàng bị ẩn hoặc bị lọc (nếu có).

Tip hay:

Để tự động tính tổng khi lọc bảng:

  • Đặt công thức =SUBTOTAL(109, B2:B100) dưới cùng cột → Excel sẽ cập nhật theo dữ liệu lọc.

39. MATCH() — Trả về vị trí dòng, cột trong vùng dữ liệu

Cú pháp:

=MATCH(lookup_value, lookup_array, [match_type])

Giải thích tham số:

Tham sốÝ nghĩa
lookup_valueGiá trị bạn muốn tìm
lookup_arrayVùng dữ liệu bạn muốn tìm trong đó
Match_type (tùy chọn)              Cách tìm: 1, 0 hoặc -1

Các loại Match:

Giá trịÝ nghĩa
1 (mặc định)               Tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value, dữ liệu phải sắp xếp tăng dần
0Tìm chính xác
-1Tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value, dữ liệu phải sắp xếp giảm dần

Ví dụ 1: Tìm vị trí chính xác

Dòng             A
1Cam
2Táo
3Nho
4Xoài
=MATCH("Táo", A2:A5, 0)
→ Kết quả: 2 (vì "Táo" nằm ở dòng thứ 2 trong vùng A2:A5)

Ví dụ 2: Tìm vị trí gần đúng (match_type = 1)

Dòng             A
110
220
330
440
=MATCH(25, A1:A4, 1)
→ Kết quả: 2 (vì 20 là giá trị gần nhất nhỏ hơn 25)

Lưu ý:

Nếu không tìm thấy kết quả phù hợp, hàm sẽ trả về lỗi #N/A.
Hàm MATCH thường được dùng kết hợp với INDEX để lấy dữ liệu theo hàng/cột.

40. INDEX() — Tìm dữ liệu trong bảng

Cú pháp:

=INDEX(array, row_num, [column_num])

Giải thích:

Tham sốÝ nghĩa
arrayVùng dữ liệu cần truy xuất
row_numDòng trong vùng array bạn muốn lấy dữ liệu
column_num (tuỳ chọn)    Cột trong vùng array (nếu là mảng nhiều cột)

Ví dụ cơ bản (1 cột):

Dòng             A
1Cam
2Táo
3Nho
4Xoài

Mục tiêu: Lấy giá trị dòng 2

=INDEX(A1:A4, 2)
→ Kết quả: Táo

Ví dụ nâng cao (nhiều hàng & nhiều cột):

ABC
TênTuổiThành phố
Lan25Hà Nội
Nam30Sài Gòn
Hoa                 28                      Đà Nẵng

Mục tiêu: Lấy "Sài Gòn"

=INDEX(A2:C4, 2, 3)
→ Kết quả: Sài Gòn
(vì dòng 2, cột 3 trong vùng A2:C4 là "Sài Gòn")

Lưu ý:

  • INDEX không gây lỗi nếu giá trị không tồn tại, trừ khi số dòng hoặc cột nằm ngoài vùng.

  • Cực kỳ mạnh khi kết hợp với MATCH, IF, MATCH(MATCH()), V.V...


KHAI GIẢNG THƯỜNG XUYÊN CÁC LỚP TIN HỌC VĂN PHÒNG

Từ cơ bản đến nâng cao cho trẻ em và người đi làm

Liên hệ: GIA SƯ TIN HỌC THỰC CHIẾN
Hotline: 0868.922.687

Nhận xét

BÀI ĐĂNG NHIỀU QUAN TÂM

Hướng dẫn cài đặt và lập trình phần mềm Scratch

Định dạng tiền tệ khi làm Mail Merge trong Word