Tổng hợp các hàm cơ bản trong Excel
1. SUM() — Tính tổng
2. AVERAGE() — Tính trung bình cộng
3. MAX() — Giá trị lớn nhất
4. MIN() — Giá trị nhỏ nhất
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
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:
-
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ụ:
A | B |
---|---|
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:
Ví dụ:
A | B |
---|---|
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:
Ví dụ:
A | B |
---|---|
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ự
11. TRIM() — Xóa khoảng trắng thừa
12. ROW() – Trả về số thứ tự dòng (Row number)
Cú pháp:
-
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ức | Kết quả |
---|---|---|
A2 | =ROW() | 2 |
A3 | =ROW(B5) | 5 |
13. COLUMN() – Trả về số thứ tự cột (Column number)
Cú pháp:
-
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ức | Kết quả |
---|---|---|
A2 | =COLUMN() | 1 |
B2 | = COLUMN(D4) | 4 |
Ứng dụng thực tế
1. Tạo số thứ tự tự động:
→ 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 ô:
→ Trả về: Dòng 5, cột 1
14. NOW() — Hiển thị ngày giờ hiện tại
15. TODAY() — Hiển thị ngày hiện tại
16. DAY() – Lấy ngày
Cú pháp:
Ví dụ:
17. MONTH() – Lấy tháng
Cú pháp:
Ví dụ:
18. YEAR() – Lấy năm
Cú pháp:
Ví dụ:
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:
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:
A | B |
---|---|
=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ức | Kế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/2024 | Thá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
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ố
22. ROUNDUP() — Làm tròn lên
23. ROUNDDOWN() — Làm tròn xuống
24. CONCATENATE() — Ghép chuỗi
25. LEFT() — Lấy ký tự bên trái
26. RIGHT() — Lấy ký tự bên phải
27. MID() — Lấy ký tự giữa chuỗi
28. IF() — Hàm điều kiện
29. AND() — Kiểm tra nhiều điều kiện đúng
30. OR() — Kiểm tra ít nhất một điều kiện đúng
31. VLOOKUP() — Tìm kiếm theo cột
32. HLOOKUP() — Tìm kiếm theo dòng
33. XLOOKUP() — Tìm kiếm theo dòng hoặc cột
Cú pháp hàm XLOOKUP
Giải thích tham số:
Một số điểm nổi bật của XLOOKUP
34. IFERROR() — Xử lý lỗi
35. COUNTIF() — Đếm theo điều kiện
36. SUMIF() — Cộng theo điều kiện
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:
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
A | B |
---|---|
Tên | Điểm |
An | 8 |
Bình | 6 |
Chi | 9 |
Dũng | 5 |
Công thức | =AVERAGEIF(B2:B5,">7") → 8.5 |
Ví dụ 2: Trung bình lương của nhân viên phòng "Kế toán"
A | B |
---|---|
Phòng ban | Lương |
Kế toán | 10,000 |
Nhân sự | 8,000 |
Kế toán | 12,000 |
Kinh doanh | 9,500 |
Công thức | =AVERAGEIF(A2:A5, "Kế toán", B2:B5) → 11,000 |
Một số cách dùng criteria (điều kiện)
Điều kiện | Nghĩ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:
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ính | Bỏ qua hàng ẩn & lọc (100+) | Không bỏ qua (1-11) |
---|---|---|
AVERAGE (Trung bình) | 101 | 1 |
COUNT (Đếm số) | 102 | 2 |
COUNTA (Đếm ô không rỗng) | 103 | 3 |
MAX | 104 | 4 |
MIN | 105 | 5 |
PRODUCT (Tích) | 106 | 6 |
STDEV (Độ lệch chuẩn) | 107 | 7 |
STDEVP (Toàn bộ mẫu) | 108 | 8 |
SUM (Tổng) | 109 | 9 |
VAR | 110 | 10 |
VARP | 111 | 11 |
Ví dụ cơ bản:
A | B |
---|---|
Sản phẩm | Doanh thu |
A | 100 |
B | 200 |
C | 150 |
Công thức | =SUBTOTAL(109, B2:B4) → 450 |
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:
Giải thích tham số:
Tham số | Ý nghĩa |
---|---|
lookup_value | Giá trị bạn muốn tìm |
lookup_array | Vù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 |
0 | Tìm chính xác |
-1 | Tì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 |
---|---|
1 | Cam |
2 | Táo |
3 | Nho |
4 | Xoài |
Ví dụ 2: Tìm vị trí gần đúng (match_type = 1)
Dòng | A |
---|---|
1 | 10 |
2 | 20 |
3 | 30 |
4 | 40 |
Lưu ý:
40. INDEX() — Tìm dữ liệu trong bảng
Cú pháp:
Giải thích:
Tham số | Ý nghĩa |
---|---|
array | Vùng dữ liệu cần truy xuất |
row_num | Dò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 |
---|---|
1 | Cam |
2 | Táo |
3 | Nho |
4 | Xoài |
Mục tiêu: Lấy giá trị dòng 2
Ví dụ nâng cao (nhiều hàng & nhiều cột):
A | B | C |
---|---|---|
Tên | Tuổi | Thành phố |
Lan | 25 | Hà Nội |
Nam | 30 | Sài Gòn |
Hoa | 28 | Đà Nẵng |
Mục tiêu: Lấy "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...
Nhận xét
Đăng nhận xét
Cảm ơn bạn đã quan tâm