Cách Sử Dụng Công Thức Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Di Chuyển Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Giới thiệu chung về công thức trong Excel

Excel không chỉ là một công cụ tính toán đơn thuần mà còn là một môi trường mạnh mẽ cho việc phân tích dữ liệu, tự động hoá công việc, và tạo ra các báo cáo chuyên nghiệp. Trọng tâm của sức mạnh này chính là công thức (formula) – những dãy ký tự mà người dùng nhập vào các ô để thực hiện các phép tính, tra cứu, logic, và nhiều thao tác phức tạp khác. Trong bài viết này, chúng ta sẽ đi sâu vào cách sử dụng công thức Excel, từ những kiến thức cơ bản nhất cho người mới bắt đầu, tới các kỹ thuật nâng cao dành cho người dùng chuyên nghiệp.

Có thể bạn quan tâm: Hiện Đường Lưới Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Cấu trúc một công thức Excel

1.1. Dấu “=” – Khởi đầu của mọi công thức

Mỗi công thức trong Excel luôn bắt đầu bằng dấu bằng (=). Đây là tín hiệu cho Excel biết rằng nội dung ô sẽ được tính toán thay vì hiển thị nguyên văn.

=SUM(A1:A10)

1.2. Các thành phần cơ bản

• Hàm (Function): Tên hàm như SUM, AVERAGE, VLOOKUP… được viết bằng chữ in hoa (hoặc in thường, Excel sẽ tự chuyển thành in hoa).
• Tham chiếu ô (Cell reference): Địa chỉ ô hoặc dải ô, ví dụ A1, B2:C5.
• Toán tử (Operator): +, -, , /, ^ (lũy thừa), & (nối chuỗi).
• Đối số (Argument): Các giá trị hoặc tham chiếu được đưa vào hàm, ngăn cách bằng dấu phẩy , (hoặc dấu chấm phẩy ; tùy ngôn ngữ).

1.3. Ví dụ tổng hợp

=IF(A2>100, SUM(B2:B10)0.9, SUM(B2:B10))

Công thức trên kiểm tra nếu giá trị ô A2 lớn hơn 100, thì tính tổng dải B2:B10 và giảm 10%; nếu không, chỉ tính tổng bình thường.

Có thể bạn quan tâm: Cách Hiện Cột Bị Ẩn Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các loại tham chiếu ô

2.1. Tham chiếu tương đối (Relative reference)

Mặc định, khi sao chép công thức, các tham chiếu ô sẽ thay đổi tương ứng với vị trí mới. Ví dụ, công thức =A1+B1 ở ô C1, khi sao chép xuống C2 sẽ trở thành =A2+B2.

2.2. Tham chiếu tuyệt đối (Absolute reference)

Được ký hiệu bằng dấu $ trước cột và/hoặc dòng, ví dụ $A$1. Khi sao chép, tham chiếu này vẫn giữ nguyên.

2.3. Tham chiếu hỗn hợp (Mixed reference)

Chỉ cố định cột hoặc dòng, ví dụ $A1 (cột cố định) hoặc A$1 (dòng cố định). Thích hợp cho các bảng tính có công thức lặp lại theo hàng hoặc cột.

2.4. Ứng dụng thực tế

Giả sử bạn có bảng tính chi phí và muốn tính tổng chi phí cho mỗi tháng, trong khi tỷ lệ thuế cố định ở ô $D$1. Công thức trong cột Total sẽ là:

=SUM(B2:C2)(1+$D$1)

Khi sao chép xuống các dòng khác, chỉ dải B2:C2 thay đổi, còn $D$1 vẫn cố định.

3. Các hàm cơ bản cần nắm

3.1. Hàm SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK

• SUM(range): Tính tổng các giá trị số.
• AVERAGE(range): Tính trung bình cộng.
• COUNT(range): Đếm số ô chứa số.
• COUNTA(range): Đếm số ô không rỗng (bao gồm cả văn bản).
• COUNTBLANK(range): Đếm số ô trống.

3.2. Hàm MIN, MAX, SMALL, LARGE

• MIN(range), MAX(range): Tìm giá trị nhỏ nhất và lớn nhất.
• SMALL(range, k), LARGE(range, k): Lấy giá trị thứ k trong dải (k nhỏ nhất hoặc lớn nhất).

3.3. Hàm IF và các hàm logic

• IF(logical_test, value_if_true, value_if_false): Kiểm tra điều kiện, trả về giá trị tương ứng.
• AND, OR, NOT: Kết hợp nhiều điều kiện.

Ví dụ:

=IF(AND(A2>0, B2<100), "OK", "Check")

3.4. Hàm VLOOKUP, HLOOKUP, XLOOKUP

• VLOOKUP(lookup_value, table_array, col_index_num, range_lookup): Tìm kiếm theo cột dọc.
• HLOOKUP(lookup_value, table_array, row_index_num, range_lookup): Tìm kiếm theo hàng ngang.
• XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode): Hàm mới, thay thế VLOOKUP/HLOOKUP với tính năng mạnh mẽ hơn.

Ví dụ sử dụng XLOOKUP:

=XLOOKUP(D2, A2:A100, B2:B100, "Không tìm thấy")

3.5. Hàm TEXT, CONCAT, TEXTJOIN

• TEXT(value, format_text): Định dạng số thành chuỗi.
• CONCAT(text1, text2, ...): Nối các chuỗi (thay thế CONCATENATE).
• TEXTJOIN(delimiter, ignore_empty, text1, text2, ...): Nối chuỗi với dấu phân cách, có thể bỏ qua ô trống.

Ví dụ:

=TEXTJOIN(", ", TRUE, A2:A5)

Kết quả: các giá trị trong A2:A5 được nối bằng dấu phẩy và khoảng trắng, bỏ qua ô trống.

4. Thực hành: Xây dựng bảng tính doanh thu

4.1. Mô tả bài toán

Bạn có bảng dữ liệu bán hàng gồm các cột: Ngày, Sản phẩm, Số lượng, Đơn giá, Chiết khấu (% ). Yêu cầu:
1. Tính Doanh thu = Số lượng × Đơn giá × (1 – Chiết khấu).
2. Tính Tổng doanh thu theo tháng.
3. Xác định sản phẩm bán chạy nhất mỗi tháng.

4.2. Bước 1: Tính doanh thu từng dòng

Giả sử dữ liệu bắt đầu ở dòng 2:

= C2 D2 (1 - E2/100)

Kéo công thức xuống toàn bộ dải.

4.3. Bước 2: Tổng doanh thu theo tháng

Sử dụng hàm SUMIFS (tổng có điều kiện):

=SUMIFS(F:F, A:A, ">=01/01/2026", A:A, "<=31/01/2026")

Đối với mỗi tháng, thay đổi phạm vi ngày tương ứng. Để tự động hoá, bạn có thể dùng PivotTable (bảng tổng hợp) – sẽ được trình bày ở phần 6.

Có thể bạn quan tâm: Cách Khôi Phục File Excel Bị Hỏng Hoặc Mất Dữ Liệu: Hướng Dẫn Chi Tiết Từ A Đến Z

4.4. Bước 3: Sản phẩm bán chạy nhất

Dùng hàm MAXIFS và XLOOKUP:

=MAXIFS(C:C, A:A, ">=01/01/2026", A:A, "<=31/01/2026")

Sau đó, tìm tên sản phẩm:

=XLOOKUP(MAXIFS(C:C, A:A, ">=01/01/2026", A:A, "<=31/01/2026"), C:C, B:B)

5. Kỹ thuật nâng cao với 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 thực hiện tính toán trên một dải ô mà không cần lặp lại công thức cho từng ô. Trước Excel 365, người dùng phải nhập công thức mảng bằng Ctrl+Shift+Enter (CSE). Từ Excel 365 trở đi, các công thức mảng động (dynamic array) tự động mở rộng.

5.2. Các hàm mảng phổ biến

• FILTER(array, include, if_empty): Lọc dữ liệu thỏa mãn điều kiện.
• SORT(array, sort_index, sort_order, by_col): Sắp xếp.
• UNIQUE(array, by_col, exactly_once): Lấy danh sách giá trị duy nhất.
• SEQUENCE(rows, columns, start, step): Tạo dãy số.

5.3. Ví dụ thực tế: Lấy danh sách khách hàng mua trên 5.000$

Giả sử cột A là Tên khách hàng, cột B là Giá trị giao dịch.

=UNIQUE(FILTER(A2:A100, B2:B100>5000))

Kết quả là một dải ô chứa tên khách hàng duy nhất có giao dịch > 5.000$.

5.4. Kết hợp nhiều hàm mảng

Bạn muốn tính tổng doanh thu theo từng khách hàng, chỉ tính những giao dịch trong năm 2026:

=LET( data, FILTER(A2:D100, YEAR(A2:A100)=2026), customers, UNIQUE(INDEX(data,,2)), totals, MAP(customers, LAMBDA(c, SUMIFS(INDEX(data,,4), INDEX(data,,2), c))), HSTACK(customers, totals)
)

Trong ví dụ này, hàm LET giúp lưu trữ dữ liệu tạm, MAP và LAMBDA (có từ Excel 365) thực hiện vòng lặp ảo.

6. PivotTable và công thức trong Pivot

6.1. Khi nào nên dùng PivotTable?

PivotTable (bảng tổng hợp) là công cụ mạnh mẽ để tóm tắt, phân nhóm và phân tích dữ liệu lớn mà không cần viết công thức phức tạp. Tuy nhiên, đôi khi bạn cần tính toán tùy chỉnh không có sẵn trong Pivot – lúc này Calculated Field (trường tính) và Calculated Item (mục tính) sẽ giúp.

6.2. Tạo Calculated Field

Giả sử trong Pivot bạn có Doanh thu và Chi phí, muốn tính Lợi nhuận:
1. Chọn Pivot → Analyze → Fields, Items & Sets → Calculated Field.
2. Đặt tên Profit.
3. Nhập công thức: =Revenue - Cost.

6.3. Sử dụng công thức GETPIVOTDATA

Khi cần lấy dữ liệu từ Pivot vào các ô khác, hàm GETPIVOTDATA rất hữu ích:

=GETPIVOTDATA("Revenue", $A$3, "Month", "Jan")

Công thức này trả về doanh thu tháng 1 từ Pivot nằm ở ô A3.

7. Xử lý lỗi trong công thức

7.1. Các lỗi phổ biến

• #DIV/0! – chia cho 0 hoặc ô trống.
• #N/A – không tìm thấy giá trị (thường xuất hiện trong VLOOKUP).
• #VALUE! – kiểu dữ liệu không hợp lệ (ví dụ: cộng chuỗi với số).
• #REF! – tham chiếu ô không tồn tại (đã xóa).
• #NAME? – tên hàm hoặc tên phạm vi không được công nhận.

7.2. Hàm xử lý lỗi

• IFERROR(value, value_if_error): Trả về value_if_error nếu value gây lỗi.
• IFNA(value, value_if_na): Chỉ bắt #N/A.
• ISNUMBER, ISTEXT, ISBLANK, ISERROR để kiểm tra kiểu dữ liệu.

Ví dụ:

=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "Không tìm thấy")

7.3. Ngăn ngừa lỗi chia cho 0

=IF(B2=0, 0, A2/B2)

Hoặc dùng IFERROR:

=IFERROR(A2/B2, 0)

8. Tối ưu hiệu suất công thức

8.1. Tránh sử dụng toàn dải cột (A:A) trong công thức tính toán lớn

Sử dụng dải cụ thể (A2:A1000) sẽ giảm tải tính toán.

8.2. Giảm số lần lặp lại công thức

• Dùng công thức mảng thay cho nhiều công thức riêng lẻ.
• Sử dụng Helper Columns (cột trợ giúp) để tính toán trung gian, tránh lồng quá nhiều hàm trong một ô.

8.3. Tắt tính toán tự động khi làm việc với dữ liệu lớn

Formulas → Calculation Options → Manual. Sau khi hoàn thành, nhấn F9 để tính lại.

8.4. Sử dụng hàm INDEX/MATCH thay cho VLOOKUP khi cần tìm kiếm nhanh

INDEX trả về giá trị dựa trên vị trí, MATCH trả về vị trí. Khi dữ liệu rộng, INDEX/MATCH nhanh hơn VLOOKUP vì không cần duyệt toàn bộ bảng.

Ví dụ:

=INDEX(B:B, MATCH(D2, A:A, 0))

9. Các mẹo “thủ thuật” giúp công việc với công thức trở nên nhanh hơn

1. Sao chép công thức nhanh: Kéo góc dưới bên phải hoặc dùng Ctrl + D (điền xuống) và Ctrl + R (điền sang phải).
2. Đặt tên cho dải dữ liệu: Formulas → Define Name. Sau khi đặt tên (vd: SalesData), công thức trở nên dễ đọc: =SUM(SalesData).
3. Sử dụng thanh công thức (Formula Bar) để chỉnh sửa: Nhấn Ctrl + Shift + U để mở rộng/thu hẹp thanh công thức khi công thức dài.
4. Kiểm tra công thức: Formulas → Evaluate Formula để từng bước kiểm tra cách Excel tính toán.
5. Sử dụng F9 để tính toán phần công thức đã chọn: Khi chọn một đoạn trong công thức và nhấn F9, Excel sẽ hiển thị kết quả tạm thời.

10. Kết luận

Việc thành thạo công thức Excel không chỉ giúp bạn thực hiện các phép tính nhanh chóng mà còn mở ra khả năng phân tích dữ liệu sâu rộng, tự động hoá quy trình làm việc và tạo ra các báo cáo chuyên nghiệp. Từ các hàm cơ bản như SUM, IF, VLOOKUP đến các hàm mảng động như FILTER, UNIQUE, và các kỹ thuật tối ưu hiệu suất, mỗi công cụ đều có vị trí và mục đích riêng. Khi áp dụng đúng cách, bạn sẽ giảm thiểu lỗi, tăng tốc độ xử lý và nâng cao chất lượng dữ liệu.

Hãy dành thời gian thực hành trên các dự án thực tế, tạo ra các bảng tính mẫu, và dần dần khám phá các tính năng mới mà Excel liên tục cập nhật. Khi bạn đã nắm vững những kiến thức này, không còn gì là không thể giải quyết bằng một công thức duy nhất trong Excel!

Tiến sĩ Lê Thống Nhất

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ẻ.