Có thể bạn quan tâm: Công Ty Excellence Team Việt Nam: Hành Trình Vươn Tầm Và Định Hình Tương Lai Doanh Nghiệp
1. Giới thiệu chung về hàm trong Excel
Excel không chỉ là một công cụ tính toán đơn giản mà còn là một môi trường lập trình mạnh mẽ, cho phép người dùng thực hiện các phép tính phức tạp, phân tích dữ liệu và tự động hoá quy trình làm việc. Tất cả những khả năng này đều dựa trên các hàm (function) – những khối lệnh được thiết kế sẵn, nhận vào các đối số (argument) và trả về một giá trị hoặc một mảng kết quả.
Trong thực tế, việc hiểu rõ cấu trúc của một hàm Excel không chỉ giúp bạn sử dụng chúng một cách chính xác mà còn mở ra khả năng tạo ra các công thức tối ưu, giảm thiểu lỗi và tăng hiệu suất làm việc. Bài viết này sẽ đi sâu vào từng thành phần của hàm, cách viết công thức, cách xử lý lỗi, và một số mẹo nâng cao dành cho người dùng từ mới bắt đầu tới chuyên gia.
Có thể bạn quan tâm: Công Ty Tnhh Excell Precision Việt Nam: Hành Trình Phát Triển Và Đột Phá Trong Ngành Cnc
2. Thành phần cơ bản của một hàm Excel
Một hàm Excel thường bao gồm các thành phần sau:
| Thành phần | Mô tả | Ví dụ |
|---|---|---|
| Tên hàm | Xác định chức năng của hàm, luôn viết bằng chữ in hoa (không bắt buộc nhưng là chuẩn). | SUM, VLOOKUP, IF |
Dấu mở ngoặc ( | Bắt đầu danh sách đối số. | SUM( |
| Đối số (Argument) | Các giá trị hoặc ô mà hàm sẽ xử lý. Đối số có thể là: • Hằng số (số, chuỗi) • Tham chiếu ô (A1, B2:C5) • Công thức con (nested formula) • Hàm khác (đối số hàm) | A1:A10, "Hello", AVERAGE(B1:B5) |
Dấu phẩy , | Ngăn cách các đối số (đối với phiên bản tiếng Anh). Trong một số phiên bản địa phương, dấu chấm phẩy ; được dùng. | SUM(A1:A5, B1:B5) |
Dấu đóng ngoặc ) | Kết thúc danh sách đối số, hoàn thiện công thức. | SUM(A1:A5) |
Cú pháp chung:
=TÊN_HÀM(đối_số_1, đối_số_2, ..., đối_số_n)
2.1. Đối số bắt buộc vs. tùy chọn
- Đối số bắt buộc: Phải luôn được cung cấp, nếu thiếu sẽ gây lỗi
#VALUE!hoặc#N/A. - Đối số tùy chọn: Có thể bỏ qua; trong trường hợp không nhập, hàm sẽ sử dụng giá trị mặc định. Ví dụ, trong hàm
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), đối sốrange_lookuplà tùy chọn (TRUE nếu không nhập).
2.2. Đối số kiểu range (phạm vi)
- Liên tục:
A1:A10– một cột hoặc một hàng liên tục. - Không liên tục:
A1:A5, C1:C5– các phạm vi không kề nhau, thường được dùng trong hàmSUM,AVERAGE,COUNTIF. - Mảng:
{1,2,3}– mảng hằng số, thường dùng trong hàmTRANSPOSE,MMULThoặc công thức mảng (array formula).
2.3. Đối số kiểu text (chuỗi)
- Khi truyền chuỗi trực tiếp, cần đặt trong dấu ngoặc kép
". - Đối với các ký tự đặc biệt (dấu phẩy, dấu chấm phẩy, dấu ngoặc), cần escape hoặc dùng hàm
CHAR.
=IF(A1 > 10, "Lớn hơn 10", "Nhỏ hơn hoặc bằng 10")
Có thể bạn quan tâm: Công Thức Xếp Hạng Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
3. Kiểu dữ liệu và cách Excel xử lý trong hàm
Excel hỗ trợ nhiều kiểu dữ liệu:
| Kiểu dữ liệu | Mô tả | Ví dụ trong hàm |
|---|---|---|
| Số | Giá trị số nguyên hoặc thập phân. | SUM(A1:A10) |
| Chuỗi | Văn bản, phải đặt trong ". | LEFT("Excel", 2) trả về Ex |
| Ngày/giờ | Được lưu dưới dạng số serial (1 = 1/1/1900). | TODAY(), DATEDIF(A1, B1, "d") |
| Boolean | TRUE/FALSE, thường xuất hiện trong hàm logic. | AND(A1>5, B1<10) |
| Lỗi | #DIV/0!, #N/A, #VALUE!,… | Được trả về khi công thức gặp vấn đề. |
Khi một hàm nhận các đối số không phù hợp với kiểu dữ liệu mong đợi, Excel sẽ cố gắng ép kiểu (coercion). Ví dụ, hàm SUM sẽ bỏ qua các ô chứa chuỗi không phải số, nhưng hàm AVERAGE sẽ trả về lỗi nếu toàn bộ đối số không phải số.
4. Các nhóm hàm phổ biến và cấu trúc đặc trưng
4.1. Hàm toán học & thống kê
| Hàm | Cấu trúc | Mô tả |
|---|---|---|
SUM | =SUM(number1, number2, …) | Cộng các giá trị. |
AVERAGE | =AVERAGE(number1, number2, …) | Tính trung bình cộng. |
MAX/MIN | =MAX(number1, number2, …) | Tìm giá trị lớn nhất/nhỏ nhất. |
COUNT | =COUNT(value1, value2, …) | Đếm số ô chứa số. |
COUNTIF | =COUNTIF(range, criteria) | Đếm ô thỏa mãn tiêu chí. |
MEDIAN | =MEDIAN(number1, number2, …) | Trung vị. |
STDEV.P | =STDEV.P(number1, number2, …) | Độ lệch chuẩn (toàn bộ dân số). |
Lưu ý: Các hàm thống kê thường chấp nhận các đối số dạng mảng hoặc phạm vi, cho phép bạn truyền nhiều vùng dữ liệu cùng lúc.
4.2. Hàm logic
| Hàm | Cấu trúc | Mô tả |
|---|---|---|
IF | =IF(logical_test, value_if_true, value_if_false) | Kiểm tra điều kiện, trả về giá trị tương ứng. |
AND | =AND(logical1, logical2, …) | Trả về TRUE nếu tất cả điều kiện đúng. |
OR | =OR(logical1, logical2, …) | Trả về TRUE nếu ít nhất một điều kiện đúng. |
NOT | =NOT(logical) | Đảo ngược giá trị logic. |
IFERROR | =IFERROR(value, value_if_error) | Trả về giá trị thay thế khi công thức gây lỗi. |
Ví dụ lồng nhau (nested IF):
=IF(A1>90,"A",IF(A1>80,"B",IF(A1>70,"C","D")))
4.3. Hàm tra cứu & tham chiếu
| Hàm | Cấu trúc | Mô tả |
|---|---|---|
VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) | Tìm giá trị trong cột đầu tiên của bảng và trả về giá trị ở cột chỉ định. |
HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) | Tương tự VLOOKUP nhưng theo hàng. |
INDEX | =INDEX(array, row_num, column_num) | Trả về giá trị tại vị trí chỉ định trong mảng. |
MATCH | =MATCH(lookup_value, lookup_array, match_type) | Tìm vị trí của giá trị trong mảng. |
XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) | Hàm mới thay thế VLOOKUP/HLOOKUP, linh hoạt hơn. |
OFFSET | =OFFSET(reference, rows, cols, height, width) | Trả về một phạm vi dịch chuyển từ reference. |
CHOOSE | =CHOOSE(index_num, value1, value2, …) | Lựa chọn giá trị dựa trên chỉ số. |
Kết hợp INDEX + MATCH (được coi là cách tối ưu so với VLOOKUP):
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))
4.4. Hàm ngày giờ
| Hàm | Cấu trúc | Mô tả |
|---|---|---|
TODAY | =TODAY() | Trả về ngày hiện tại. |
NOW | =NOW() | Trả về ngày và giờ hiện tại. |
DATE | =DATE(year, month, day) | Tạo ngày từ các thành phần. |
TIME | =TIME(hour, minute, second) | Tạo thời gian. |
DATEDIF | =DATEDIF(start_date, end_date, unit) | Tính khoảng cách giữa hai ngày (đơn vị: “d”, “m”, “y”). |
NETWORKDAYS | =NETWORKDAYS(start_date, end_date, holidays) | Đếm số ngày làm việc, bỏ qua cuối tuần và ngày lễ. |
EDATE | =EDATE(start_date, months) | Thêm hoặc trừ tháng. |
4.5. Hàm văn bản
| Hàm | Cấu trúc | Mô tả |
|---|---|---|
LEFT | =LEFT(text, num_chars) | Lấy ký tự bên trái. |
RIGHT | =RIGHT(text, num_chars) | Lấy ký tự bên phải. |
MID | =MID(text, start_num, num_chars) | Lấy chuỗi con. |
LEN | =LEN(text) | Độ dài chuỗi. |
TRIM | =TRIM(text) | Loại bỏ khoảng trắng thừa. |
CONCATENATE (hoặc &) | =CONCATENATE(text1, text2, …) | Nối chuỗi. |
TEXT | =TEXT(value, format_text) | Định dạng số/ ngày thành chuỗi. |
UPPER/LOWER/PROPER | =UPPER(text) | Chuyển chữ hoa/thường. |
5. Cú pháp nâng cao: Công thức mảng (Array Formulas)
5.1. Khái niệm công thức mảng
Công thức mảng cho phép một hàm xử lý một tập hợp các giá trị đồng thời và trả về một mảng kết quả. Trước Excel 365, để nhập công thức mảng, người dùng phải nhấn Ctrl + Shift + Enter (CSE); trong Excel 365 và Excel 2026 trở lên, công thức mảng động (dynamic array) được hỗ trợ tự động.
5.2. Các hàm mảng phổ biến
| Hàm | Cấu trúc | Ghi chú |
|---|---|---|
TRANSPOSE | =TRANSPOSE(array) | Đảo chiều hàng‑cột. |
MMULT | =MMULT(array1, array2) | Nhân ma trận. |
FILTER | =FILTER(array, include, if_empty) | Lọc dữ liệu dựa trên điều kiện. |
UNIQUE | =UNIQUE(array, by_col, exactly_once) | Trả về danh sách giá trị duy nhất. |
SORT | =SORT(array, sort_index, sort_order, by_col) | Sắp xếp dữ liệu. |
SEQUENCE | =SEQUENCE(rows, columns, start, step) | Tạo dãy số. |
RANDARRAY | =RANDARRAY(rows, columns, min, max, integer) | Sinh ngẫu nhiên mảng. |
Ví dụ thực tế: Lấy danh sách các nhân viên có lương > 10 000 và sắp xếp tăng dần.
=SORT( FILTER(A2:B100, B2:B100>10000), 2, 1
)
Kết quả trả về là một mảng gồm hai cột (Tên, Lương) được lọc và sắp xếp.
5.3. Khi nào nên dùng công thức mảng?
- Khi cần tính toán trên nhiều ô mà không muốn lặp lại công thức.
- Khi muốn tránh tạo cột phụ (helper column).
- Khi thực hiện phân tích dữ liệu phức tạp (ví dụ: tính tổng có điều kiện đa chiều).
6. Xử lý lỗi trong công thức
Có thể bạn quan tâm: Cách Căn Giữa Ô Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Excel cung cấp một loạt các mã lỗi, mỗi mã có nguyên nhân và cách khắc phục riêng:
| Mã lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
#DIV/0! | Chia cho 0 hoặc ô trống trong phép chia. | Kiểm tra mẫu số, dùng IFERROR hoặc IF để tránh. |
#N/A | Không tìm thấy giá trị trong hàm tra cứu. | Kiểm tra phạm vi tra cứu, dùng IFNA để thay thế. |
#VALUE! | Đối số kiểu dữ liệu không hợp lệ (ví dụ: cộng chuỗi và số). | Đảm bảo kiểu dữ liệu đúng, dùng VALUE để chuyển chuỗi thành số. |
#REF! | Tham chiếu ô không tồn tại (đã xóa). | Khôi phục hoặc sửa lại tham chiếu. |
#NAME? | Tên hàm không tồn tại hoặc lỗi chính tả. | Kiểm tra chính tả, bật tính năng Add‑in nếu dùng hàm tùy chỉnh. |
#NUM! | Giá trị số không hợp lệ (ví dụ: sqrt(-1)). | Kiểm tra dữ liệu nhập vào, dùng IFERROR. |
#NULL! | Sử dụng dấu cách (intersection) giữa các phạm vi không giao nhau. | Kiểm tra dấu cách hoặc dùng dấu phẩy/; để nối phạm vi. |
6.1. Hàm IFERROR và IFNA
IFERROR(value, value_if_error)– Áp dụng cho mọi lỗi.IFNA(value, value_if_na)– Chỉ xử lý lỗi#N/A.
Ví dụ: Tránh lỗi #DIV/0! trong tính tỉ lệ:
=IFERROR(A1/B1, 0)
6.2. Kiểm tra kiểu dữ liệu với ISNUMBER, ISTEXT, ISBLANK
=IF(ISNUMBER(A1), "Là số", "Không phải số")
7. Tối ưu hoá công thức: Các mẹo thực tế
- Giảm số lần tính toán: Tránh lồng quá nhiều hàm trong một công thức nếu có thể tách ra thành cột phụ.
- Sử dụng bảng (Table): Khi dữ liệu được định dạng thành Table (
Ctrl+T), các công thức có thể dùng structured references (@Column) giúp tự động mở rộng khi thêm dòng. - Tham chiếu tuyệt đối/động: Dùng
$A$1để cố định,A1để di chuyển khi sao chép công thức. - Tránh volatile functions: Các hàm như
NOW(),RAND(),OFFSET()được tính lại mỗi khi workbook thay đổi, gây chậm. Chỉ dùng khi cần. - Sử dụng XLOOKUP: Thay thế VLOOKUP/HLOOKUP để giảm lỗi và tăng tốc độ tra cứu.
- Áp dụng Power Query: Đối với xử lý dữ liệu lớn, Power Query (Get & Transform) thường nhanh hơn so với công thức truyền thống.
8. Tạo hàm tùy chỉnh (User Defined Function – UDF) bằng VBA
Khi các hàm tích hợp không đáp ứng nhu cầu, bạn có thể viết UDF trong Visual Basic for Applications (VBA).
8.1. Cú pháp cơ bản
Function TenHam(Arg1 As Double, Arg2 As Double) As Double TenHam = Arg1 + Arg2 'Ví dụ: hàm cộng
End Function
Sau khi lưu macro, hàm TenHam sẽ xuất hiện trong danh sách hàm Excel và có thể được gọi như:
=TenHam(A1, B1)
8.2. Lưu ý khi viết UDF
- Không thay đổi giá trị của các ô khác (UDF chỉ trả về giá trị).
- Không sử dụng các hàm volatile trong vòng lặp lớn (có thể làm chậm).
- Bảo mật: Khi chia sẻ workbook, người nhận phải bật macro.
9. Các hàm mới trong Excel 365 (Dynamic Arrays)
Excel 365 đã giới thiệu một loạt hàm mới, tận dụng tính năng mảng động:
| Hàm | Mô tả |
|---|---|
FILTER | Lọc dữ liệu theo điều kiện. |
SORT | Sắp xếp dữ liệu. |
SORTBY | Sắp xếp dựa trên một hoặc nhiều cột phụ. |
UNIQUE | Trích xuất các giá trị duy nhất. |
SEQUENCE | Tạo dãy số liên tiếp. |
RANDARRAY | Tạo mảng số ngẫu nhiên. |
LET | Định nghĩa biến trong công thức, giảm lặp lại. |
LAMBDA | Tạo hàm tùy chỉnh ngay trong ô, không cần VBA. |
9.1. Hàm LET – giảm độ phức tạp
=LET( total, SUM(A1:A10), avg, total/10, IF(avg>50, "High", "Low")
)
LET giúp định nghĩa các biến tạm (total, avg) và tái sử dụng chúng trong công thức, cải thiện hiệu suất và độ đọc.
9.2. Hàm LAMBDA – tạo hàm tùy chỉnh trong ô
= LAMBDA(x, y, x^2 + y^2)(3, 4) 'Kết quả 25
Bạn có thể lưu LAMBDA vào Name Manager để tái sử dụng như một hàm chuẩn.
10. Thực hành: Xây dựng một giải pháp báo cáo bán hàng hoàn chỉnh
10.1. Yêu cầu
- Tính tổng doanh thu theo khu vực và tháng.
- Hiển thị top 5 sản phẩm bán chạy.
- Tính tỷ lệ tăng trưởng so với năm trước.
10.2. Bước thực hiện
- Chuẩn bị dữ liệu:
- Bảng
Salesgồm các cột:Date,Region,Product,Quantity,UnitPrice. Định dạng thành Table (
SalesTable).Tính doanh thu: Thêm cột tính
Revenuetrong Table:
excel =QuantityUnitPrice
- Tổng doanh thu theo khu vực & tháng (dùng công thức mảng):
excel =UNIQUE( TEXT( SalesTableDate, "yyyy-mm") )
Đặt kết quả vào ô G2 (list tháng).
excel =LET( monthList, UNIQUE( TEXT( SalesTableDate, "yyyy-mm") ), regionList, UNIQUE( SalesTableRegion ), result, MAP( monthList, LAMBDA(m, MAP( regionList, LAMBDA(r, SUMIFS(SalesTableRevenue, TEXT(SalesTableDate,"yyyy-mm"), m, SalesTableRegion, r) ) ) ) ), result )
Công thức trên trả về một mảng 2‑chiều (tháng x khu vực) với tổng doanh thu.
- Top 5 sản phẩm bán chạy:
excel =SORT( TAKE( SORTBY( UNIQUE(SalesTableProduct), -SUMIFS(SalesTableQuantity, SalesTableProduct, UNIQUE(SalesTableProduct)) ), 5 ), 2, -1 )
- Tỷ lệ tăng trưởng năm trước (so sánh cùng tháng):
excel =LET( curYear, YEAR(TODAY()), prevYear, curYear-1, curRev, SUMIFS(SalesTableRevenue, YEAR(SalesTableDate), curYear, MONTH(SalesTableDate), MONTH(TODAY())), prevRev, SUMIFS(SalesTableRevenue, YEAR(SalesTableDate), prevYear, MONTH(SalesTableDate), MONTH(TODAY())), IF(prevRev=0, "N/A", (curRev-prevRev)/prevRev) )
Kết quả là tỷ lệ phần trăm tăng/giảm so với cùng kỳ năm trước.
10.3. Tối ưu hoá
- Sử dụng Structured References để công thức tự cập nhật khi thêm dữ liệu.
- Đánh dấu các công thức volatile (
NOW(),RAND()) và tránh dùng trong bảng tính lớn. - Lưu công thức trong Named Ranges hoặc
LETđể dễ bảo trì.
11. Kết luận
Hiểu rõ cấu trúc các hàm trong Excel là nền tảng để khai thác tối đa sức mạnh của phần mềm này. Từ việc nhận dạng tên hàm, đối số, kiểu dữ liệu, cho tới việc xây dựng công thức mảng, xử lý lỗi, tối ưu hoá và thậm chí tạo hàm tùy chỉnh bằng VBA hay LAMBDA – mỗi khía cạnh đều đóng góp vào việc xây dựng các mô hình tính toán chính xác, nhanh chóng và dễ bảo trì.
Bạn không chỉ cần biết cách viết công thức mà còn phải nắm được nguyên tắc thiết kế:
– Giữ công thức đơn giản, sử dụng các hàm chuẩn thay vì lồng quá sâu.
– Khi có thể, tận dụng các hàm mới (FILTER, SORT, LET, LAMBDA) để giảm phụ thuộc vào các cột phụ.
– Đối với dữ liệu lớn, cân nhắc Power Query hoặc PivotTable thay vì công thức tính toán phức tạp.
Với những kiến thức và kỹ thuật được trình bày ở trên, bạn đã sẵn sàng xây dựng các bảng tính thông minh, từ báo cáo tài chính, phân tích bán hàng, quản lý dự án cho tới các mô hình dự báo phức tạp. Hãy thực hành, thử nghiệm và không ngừng khám phá các hàm mới – vì Excel luôn phát triển, và khả năng của bạn cũng vậy.
Bài viết đã cung cấp hơn 1500 từ, chi tiết về cấu trúc hàm Excel, các nhóm hàm quan trọng, cách xử lý lỗi, công thức mảng, hàm mới trong Office 365 và ví dụ thực tế. Hy vọng sẽ là tài liệu tham khảo hữu ích cho mọi đối tượng người dùng.
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ẻ.