Có thể bạn quan tâm: Tất Cả Các Hàm Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
Giới thiệu chung
Microsoft Excel là một công cụ tính toán, phân tích dữ liệu và trực quan hóa thông tin vô cùng mạnh mẽ. Trong quá trình làm việc, chúng ta thường xuyên phải xử lý dữ liệu, thực hiện các phép tính phức tạp, tìm kiếm, lọc và tổng hợp thông tin. Để làm cho công việc trở nên nhanh chóng và chính xác, Excel cung cấp hàng trăm công thức (functions) đa dạng. Bài viết này sẽ tổng hợp các công thức Excel phổ biến, hữu ích nhất, giúp bạn nắm vững và áp dụng vào thực tế. Chúng ta sẽ chia bài viết thành các phần: công thức tính toán, công thức xử lý chuỗi, công thức tìm kiếm/định vị, công thức điều kiện, công thức ngày giờ, công thức thống kê, và một số công thức nâng cao. Mỗi phần sẽ có ví dụ minh họa và lời khuyên khi sử dụng.
Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Tạo Thẻ Kho Trên Excel: Từ Cơ Bản Đến Nâng Cao
1. Công thức tính toán cơ bản
1.1 Cộng, trừ, nhân, chia
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=SUM(A1:A10) | Tính tổng dải ô | =SUM(A1:A10) trả về tổng của 10 ô A1..A10 |
=SUBTOTAL(9, A1:A10) | Tính tổng theo chế độ ẩn dữ liệu | =SUBTOTAL(9, A1:A10) |
=AVERAGE(A1:A10) | Tính trung bình | =AVERAGE(A1:A10) |
=MEDIAN(A1:A10) | Tính trung vị | =MEDIAN(A1:A10) |
=MIN(A1:A10) | Tìm giá trị nhỏ nhất | =MIN(A1:A10) |
=MAX(A1:A10) | Tìm giá trị lớn nhất | =MAX(A1:A10) |
=PRODUCT(A1:A10) | Tính tích các giá trị | =PRODUCT(A1:A10) |
Mẹo: Sử dụng dấu
:để chỉ định dải ô, dấu,để ngăn các tham số riêng biệt.
1.2 Định dạng số
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=ROUND(A1,2) | Làm tròn số tại 2 chữ số thập phân | =ROUND(A1,2) |
=TRUNC(A1,2) | Cắt bỏ thập phân sau 2 chữ số | =TRUNC(A1,2) |
=INT(A1) | Lấy phần nguyên | =INT(A1) |
=CEILING(A1,1) | Làm tròn lên tới số nguyên gần nhất | =CEILING(A1,1) |
=FLOOR(A1,1) | Làm tròn xuống tới số nguyên gần nhất | =FLOOR(A1,1) |
Có thể bạn quan tâm: Cách Tạo Qr Code Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
2. Công thức xử lý chuỗi
2.1 Kết hợp chuỗi
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=CONCATENATE(A1, " ", B1) | Nối chuỗi trong A1 và B1 | =CONCATENATE(A1, " ", B1) |
=A1 & " " & B1 | Tương đương với CONCATENATE | =A1 & " " & B1 |
=TEXT(A1,"dd-mm-yyyy") | Định dạng ngày tháng thành chuỗi | =TEXT(A1,"dd-mm-yyyy") |
=UPPER(A1) | Chuyển thành chữ hoa | =UPPER(A1) |
=LOWER(A1) | Chuyển thành chữ thường | =LOWER(A1) |
=PROPER(A1) | Chuyển thành dạng tiêu đề (đầu mỗi từ hoa) | =PROPER(A1) |
2.2 Tìm kiếm và thay thế
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=FIND("abc", A1) | Tìm vị trí bắt đầu của chuỗi “abc” trong A1 | =FIND("abc", A1) |
=SEARCH("abc", A1) | Tương tự FIND nhưng không phân biệt chữ hoa/chữ thường | =SEARCH("abc", A1) |
=REPLACE(A1,2,3,"xyz") | Thay thế 3 ký tự bắt đầu từ vị trí thứ 2 | =REPLACE(A1,2,3,"xyz") |
=SUBSTITUTE(A1,"abc","xyz",2) | Thay thế lần xuất hiện thứ hai của “abc” | =SUBSTITUTE(A1,"abc","xyz",2) |
2.3 Phân tách chuỗi
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=LEFT(A1,4) | Lấy 4 ký tự đầu | =LEFT(A1,4) |
=RIGHT(A1,3) | Lấy 3 ký tự cuối | =RIGHT(A1,3) |
=MID(A1,5,3) | Lấy 3 ký tự bắt đầu từ vị trí thứ 5 | =MID(A1,5,3) |
=TEXTSPLIT(A1, " ") | Phân tách chuỗi theo dấu cách (Excel 365) | =TEXTSPLIT(A1, " ") |
Lưu ý:
TEXTSPLITchỉ có trong phiên bản Excel 365/2026 trở lên. Nếu chưa có, bạn có thể dùng công thức phức tạp hơn hoặc Power Query.
3. Công thức tìm kiếm và định vị
3.1 VLOOKUP / HLOOKUP
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE) | Tìm giá trị A2 trong cột A của Sheet2 và trả về giá trị tương ứng ở cột B | =VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE) |
=HLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE) | Tìm giá trị A2 trong hàng 1 của Sheet2 và trả về giá trị tương ứng ở hàng 2 | =HLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE) |
Mẹo: Tham số cuối (
FALSEhoặc0) yêu cầu tìm kiếm chính xác;TRUEhoặc1cho phép tìm kiếm gần đúng.
3.2 INDEX / MATCH
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=INDEX(Sheet2!$B$1:$B$100, MATCH(A2, Sheet2!$A$1:$A$100, 0)) | Phương thức thay thế VLOOKUP, linh hoạt hơn | =INDEX(Sheet2!$B$1:$B$100, MATCH(A2, Sheet2!$A$1:$A$100, 0)) |
Ưu điểm:
INDEX/MATCHcó thể tìm kiếm cả phía trái, hỗ trợ dữ liệu dọc/ngang, và performance tốt hơn khi dùng trên bảng dữ liệu lớn.
3.3 XLOOKUP (Excel 365/2026)
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=XLOOKUP(A2, Sheet2!$A$1:$A$100, Sheet2!$B$1:$B$100) | Tìm kiếm giá trị A2 trong cột A và trả về giá trị tương ứng trong cột B | =XLOOKUP(A2, Sheet2!$A$1:$A$100, Sheet2!$B$1:$B$100) |
Tính năng đặc biệt: XLOOKUP hỗ trợ tìm kiếm từ cuối, trả về giá trị mặc định khi không tìm thấy, và hỗ trợ tìm kiếm trong dải nhiều cột/đoạn.
3.4 Công thức điều kiện trong tìm kiếm
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=INDEX(Sheet2!$B$1:$B$100, MATCH(1, (Sheet2!$A$1:$A$100=A2)(Sheet2!$C$1:$C$100="Yes"), 0)) | Tìm giá trị đáp ứng nhiều điều kiện | =INDEX(Sheet2!$B$1:$B$100, MATCH(1, (Sheet2!$A$1:$A$100=A2)(Sheet2!$C$1:$C$100="Yes"), 0)) |
Lưu ý: Đây là công thức mảng, cần nhập bằng
Ctrl+Shift+Enter(trước khi Excel 365).
4. Công thức điều kiện
4.1 IF
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=IF(A1>100, "Large", "Small") | Kiểm tra điều kiện và trả về giá trị tùy theo kết quả | =IF(A1>100, "Large", "Small") |
=IFERROR(A1/B1, "Error") | Trả về “Error” khi có lỗi (ví dụ chia cho 0) | =IFERROR(A1/B1, "Error") |
4.2 IF với nhiều điều kiện
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=IFS(A1>100, "Large", A1>50, "Medium", TRUE, "Small") | Kiểm tra nhiều điều kiện tuần tự | =IFS(A1>100, "Large", A1>50, "Medium", TRUE, "Small") |
=CHOOSE(MATCH(A1, {1,2,3}, 0), "One", "Two", "Three") | Chọn giá trị dựa trên trường hợp | =CHOOSE(MATCH(A1, {1,2,3}, 0), "One", "Two", "Three") |
4.3 Công thức điều kiện phức tạp
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=SUMPRODUCT((A1:A10>5)(B1:B10<10)) | Đếm số hàng thỏa mãn điều kiện | =SUMPRODUCT((A1:A10>5)(B1:B10<10)) |
=COUNTIFS(A1:A10, ">5", B1:B10, "<10") | Đếm số hàng thỏa mãn nhiều điều kiện | =COUNTIFS(A1:A10, ">5", B1:B10, "<10") |
5. Công thức ngày giờ
Có thể bạn quan tâm: Tổng Hợp Code Vba Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=TODAY() | Trả về ngày hiện tại | =TODAY() |
=NOW() | Trả về ngày và thời gian hiện tại | =NOW() |
=DATE(2026,6,24) | Tạo ngày từ năm, tháng, ngày | =DATE(2026,6,24) |
=TIME(14,30,0) | Tạo thời gian | =TIME(14,30,0) |
=EDATE(A1, 3) | Thêm 3 tháng vào ngày trong A1 | =EDATE(A1, 3) |
=EOMONTH(A1, 0) | Trả về ngày cuối tháng của ngày trong A1 | =EOMONTH(A1, 0) |
=NETWORKDAYS(A1, B1) | Tính số ngày làm việc giữa hai ngày | =NETWORKDAYS(A1, B1) |
=WORKDAY(A1, 10) | Thêm 10 ngày làm việc | =WORKDAY(A1, 10) |
=TEXT(A1,"dddd, mmmm dd, yyyy") | Định dạng ngày thành chuỗi | =TEXT(A1,"dddd, mmmm dd, yyyy") |
6. Công thức thống kê
6.1 Đếm và phân phối
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=COUNT(A1:A10) | Đếm số ô có giá trị số | =COUNT(A1:A10) |
=COUNTA(A1:A10) | Đếm số ô không rỗng | =COUNTA(A1:A10) |
=COUNTIF(A1:A10, ">50") | Đếm số ô >50 | =COUNTIF(A1:A10, ">50") |
=COUNTIFS(A1:A10, ">50", B1:B10, "<20") | Đếm số ô thỏa mãn nhiều điều kiện | =COUNTIFS(A1:A10, ">50", B1:B10, "<20") |
=STDEV.P(A1:A10) | Độ lệch chuẩn (population) | =STDEV.P(A1:A10) |
=STDEV.S(A1:A10) | Độ lệch chuẩn (sample) | =STDEV.S(A1:A10) |
=VAR.P(A1:A10) | Hệ số phương sai (population) | =VAR.P(A1:A10) |
=VAR.S(A1:A10) | Hệ số phương sai (sample) | =VAR.S(A1:A10) |
6.2 Phân phối xác suất
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=NORM.DIST(x, mean, sd, TRUE) | Hàm phân phối chuẩn | =NORM.DIST(5, 10, 2, TRUE) |
=BINOM.DIST(n, trials, p, TRUE) | Phân phối nhị thức | =BINOM.DIST(3, 10, 0.5, TRUE) |
=T.DIST(x, deg_freedom, TRUE) | Phân phối t | =T.DIST(2.5, 10, TRUE) |
6.3 Hàm thống kê nâng cao
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=FORECAST.LINEAR(x, known_y, known_x) | Dự báo tuyến tính | =FORECAST.LINEAR(6, B1:B10, A1:A10) |
=TREND(known_y, known_x, new_x, const) | Dự báo xu hướng | =TREND(B1:B10, A1:A10, 7) |
=PERCENTILE.EXC(A1:A10, 0.25) | Tính phần trăm 25% (có ngoại lệ) | =PERCENTILE.EXC(A1:A10, 0.25) |
=QUARTILE.EXC(A1:A10, 2) | Tính trung vị (giá trị thứ 2) | =QUARTILE.EXC(A1:A10, 2) |
7. Công thức nâng cao và hữu ích
7.1 Power Query (Get & Transform)
- Tính năng: Nhập dữ liệu từ nhiều nguồn (CSV, web, SQL), làm sạch, chuyển đổi, pivot, merge.
- Cách sử dụng:
Data > Get Data > From File > From CSV, và sau khi mở Power Query Editor, sử dụng giao diện kéo thả hoặc viết M code.
7.2 Macro và VBA
- Mục tiêu: Tự động hóa quy trình, tạo giao diện tùy biến.
- Ví dụ đơn giản: Tạo macro ghi lại thao tác tính tổng và lưu trong workbook.
Sub AutoSum() Dim rng As Range Set rng = Application.InputBox("Chọn vùng dữ liệu", Type:=8) rng.Offset(0, 1).Formula = "=SUM(" & rng.Address & ")"
End Sub
7.3 Hàm tùy chỉnh (User-Defined Functions)
- Ví dụ: Hàm tính trọng số trung bình.
Function WeightedAvg(values As Range, weights As Range) As Double Dim i As Long Dim sumVal As Double, sumW As Double For i = 1 To values.Count sumVal = sumVal + values(i).Value weights(i).Value sumW = sumW + weights(i).Value Next i WeightedAvg = sumVal / sumW
End Function
Sử dụng: =WeightedAvg(A1:A10, B1:B10).
7.4 Các công thức mảng
| Công thức | Mô tả | Ví dụ |
|---|---|---|
=SUM(IF(A1:A10>5, B1:B10, 0)) | Tính tổng B1:B10 khi A1:A10 > 5 | =SUM(IF(A1:A10>5, B1:B10, 0)) (Ctrl+Shift+Enter) |
=MAX(IF((A1:A10>5)(B1:B10<10), C1:C10)) | Lấy giá trị lớn nhất trong C1:C10 khi A>5 và B<10 | =MAX(IF((A1:A10>5)(B1:B10<10), C1:C10)) |
Lưu ý: Các công thức mảng còn có thể viết bằng
LETtrong Excel 365 để cải thiện hiệu suất.
7.5 Tính năng Smart Lookup
- Mô tả: Nhấp chuột phải vào một ô và chọn “Smart Lookup” để tra cứu từ điển, Wikipedia, Microsoft Search, giúp hiểu rõ hơn về dữ liệu.
7.6 Conditional Formatting
- Mô tả: Định dạng dữ liệu dựa trên điều kiện, ví dụ: làm nổi bật giá trị > 100, màu đỏ cho số âm, biểu đồ cột đồng nhất.
- Cách sử dụng:
Home > Conditional Formatting.
8. Mẹo tối ưu và lưu ý khi làm việc với công thức
| Mẹo | Mô tả |
|---|---|
| Tránh công thức phức tạp không cần thiết | Sử dụng công thức đơn giản, chia nhỏ thành các cột phụ nếu cần. |
| Sử dụng tên vùng (Named Ranges) | Giúp công thức dễ đọc và bảo trì. |
| Kiểm tra lỗi bằng IFERROR | Tránh hiển thị lỗi #DIV/0! hoặc #VALUE!. |
| Sử dụng tính năng Table | Khi làm việc với dải dữ liệu, chuyển đổi thành Table (Ctrl+T) giúp công thức tự động mở rộng. |
| Sử dụng công thức mảng hiệu quả | Tránh sử dụng SUMPRODUCT hoặc ARRAYFORMULA quá nhiều, có thể gây chậm khi dữ liệu lớn. |
| Thường xuyên lưu và backup | Excel có thể bị treo khi tính toán công thức nặng. |
| Sử dụng Power Query cho dữ liệu lớn | Tránh tính toán trực tiếp trong bảng tính. |
| Tối ưu công thức bằng cách sử dụng công thức động (Dynamic Array) | FILTER, SORT, UNIQUE giúp giảm công thức phức tạp. |
| Kiểm tra tính đúng đắn từng bước | Dùng công cụ Evaluate Formula (Formulas > Evaluate Formula). |
| Sử dụng tính năng ghi lại macro | Tự động hóa công việc lặp đi lặp lại. |
9. Ví dụ thực tiễn: Tạo bảng tổng kết doanh thu
Giả sử bạn có dữ liệu bán hàng:
| A | B | C | D |
|---|---|---|---|
| Sản phẩm | Đơn giá | Số lượng | Doanh thu |
| A | 10 | 5 | =B2C2 |
| B | 15 | 3 | =B3C3 |
| C | 8 | 7 | =B4C4 |
9.1 Tính tổng doanh thu
- Công thức:
=SUM(D2:D4).
9.2 Tính giá trung bình
- Công thức:
=AVERAGE(B2:B4).
9.3 Tìm sản phẩm có doanh thu cao nhất
- Công thức:
=INDEX(A2:A4, MATCH(MAX(D2:D4), D2:D4, 0)).
9.4 Xếp hạng doanh thu (đánh giá top 3)
- Công thức:
=RANK.EQ(D2, D$2:D$4, 0)(đặt trong một cột mới). - Sau đó lọc theo giá trị <= 3.
9.5 Đưa dữ liệu vào biểu đồ
- Chọn cột A và D, chèn biểu đồ cột hoặc biểu đồ tròn.
10. Kết luận
Excel là công cụ linh hoạt, mạnh mẽ cho công việc tính toán, phân tích và báo cáo. Bài viết trên đã tổng hợp các công thức Excel phổ biến và hữu ích nhất: từ tính toán cơ bản, xử lý chuỗi, tìm kiếm, điều kiện, ngày giờ, thống kê, đến công thức nâng cao với Power Query, VBA, và công thức mảng. Bạn có thể áp dụng những công thức này vào các dự án thực tế, từ đơn giản đến phức tạp, giúp tăng năng suất và độ chính xác trong công việc.
Điểm mấu chốt là: đừng ngại thử nghiệm. Một công thức đơn giản nhưng được viết đúng cách sẽ tiết kiệm thời gian, tránh lỗi và giúp bạn nhanh chóng nắm bắt dữ liệu. Hãy áp dụng những mẹo tối ưu và tính năng mạnh mẽ của Excel để làm việc hiệu quả hơn. Happy Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.