Các Công Thức Excel Thường Dùng: Hướng Dẫn Chi Tiết Cho Người Mới Và Chuyên Nghiệp

Excel là một công cụ mạnh mẽ, được sử dụng rộng rãi trong mọi lĩnh vực: kế toán, tài chính, quản trị, nghiên cứu dữ liệu, thậm chí cả trong học tập và sinh hoạt cá nhân. Mỗi người dùng Excel đều cần biết những công thức cơ bản để xử lý dữ liệu nhanh chóng và chính xác. Bài viết dưới đây sẽ cung cấp một danh sách “công thức Excel thường dùng” cùng với ví dụ thực tế, giúp bạn trở thành chuyên gia trong việc áp dụng công thức vào công việc hàng ngày.

Có thể bạn quan tâm: Câu Hỏi Trắc Nghiệm Về Excel: Tổng Hợp 1500+ Câu Hỏi Đánh Giá Kiến Thức Và Kỹ Năng

1. Tổng hợp công thức tính toán cơ bản

1.1. SUM – Tổng hợp

Công thức: =SUM(A1:A10)
Mô tả: Tính tổng các giá trị trong phạm vi từ A1 tới A10.

Ví dụ thực tiễn:
Bạn có bảng doanh thu hàng tháng trong cột B từ B2 đến B13. Để tính tổng doanh thu năm, bạn nhập =SUM(B2:B13) vào ô B14.

1.2. AVERAGE – Tính trung bình

Công thức: =AVERAGE(A1:A10)
Mô tả: Tính giá trị trung bình của dãy số.

Ví dụ:
Để tính mức lương trung bình của nhân viên, bạn nhập =AVERAGE(C2:C15) nếu cột C chứa mức lương.

1.3. COUNT – Đếm số ô chứa số

Công thức: =COUNT(A1:A10)
Mô tả: Đếm số ô trong phạm vi có chứa dữ liệu số.

Ví dụ:
Đếm số khách hàng đã mua trong một ngày: =COUNT(D2:D100).

1.4. COUNTA – Đếm số ô chưa rỗng

Công thức: =COUNTA(A1:A10)
Mô tả: Đếm số ô trong phạm vi có chứa bất kỳ dữ liệu nào, kể cả văn bản.

Ví dụ:
Đếm số đơn hàng đã được ghi nhận: =COUNTA(E2:E200).

1.5. MAX & MIN – Giá trị lớn nhất & nhỏ nhất

Công thức:
– =MAX(A1:A10)
– =MIN(A1:A10)

Mô tả: Trả về giá trị lớn nhất và nhỏ nhất trong dãy.

Ví dụ:
Xác định mức lương cao nhất trong phòng ban: =MAX(C2:C15).

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

2. Các công thức điều kiện

2.1. IF – Kiểm tra điều kiện

Công thức: =IF(điều_kiện, giá_trị_nếu_sai, giá_trị_nếu_dung)
Mô tả: Trả về giá trị tùy theo điều kiện.

Ví dụ:
Nếu doanh thu trên 1.000.000, trả về “Đạt”, ngược lại “Chưa đạt”:
=IF(B2>1000000,"Đạt","Chưa đạt").

2.2. AND & OR – Kết hợp nhiều điều kiện

Công thức:
– =IF(AND(điều_kiện1, điều_kiện2), "Đạt", "Chưa đạt")
– =IF(OR(điều_kiện1, điều_kiện2), "Đạt", "Chưa đạt")

Ví dụ:
Đánh giá hiệu suất: nếu doanh số > 500.000 và số lượng bán > 50: =IF(AND(B2>500000, C2>50), "Tốt", "Cần cải thiện").

2.3. IFERROR – Xử lý lỗi

Công thức: =IFERROR(công_thức, giá_trị_nếu_lỗi)
Mô tả: Trả về một giá trị thay thế khi công thức nguồn gây lỗi.

Ví dụ:
Khi chia số tiền cho số lượng: =IFERROR(A2/B2,"Không thể chia").

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

3. Công thức tìm kiếm và tra cứu

3.1. VLOOKUP – Tìm kiếm dọc

Công thức: =VLOOKUP(giá_trị_tìm, bảng_tra_cứu, số_cột, đúng_hoặc_lượng)
Mô tả: Tìm một giá trị trong cột đầu tiên của bảng rồi trả về giá trị trong cột chỉ định.

Ví dụ:
Tìm tên sản phẩm theo mã:
=VLOOKUP(A2, $D$2:$E$100, 2, FALSE) (cột D là mã, E là tên).

3.2. HLOOKUP – Tìm kiếm ngang

Công thức: =HLOOKUP(giá_trị_tìm, bảng_tra_cứu, số_hàng, đúng_hoặc_lượng)
Mô tả: Tìm một giá trị trong hàng đầu tiên của bảng rồi trả về giá trị trong hàng chỉ định.

Ví dụ:
Tìm số lượng bán theo tháng:
=HLOOKUP("Tháng 3", $B$1:$G$10, 2, FALSE).

3.3. INDEX & MATCH – Kết hợp linh hoạt

Công thức:
=INDEX(cột_trả_về, MATCH(giá_trị_tìm, cột_tìm, 0))

Mô tả: INDEX trả về giá trị tại vị trí dòng/ cột cụ thể; MATCH tìm vị trí của giá trị cần tìm.

Ví dụ:
Tìm giá trị ở cột C nếu tên nằm trong cột A:
=INDEX(C:C, MATCH("Tên", A:A, 0)).

3.4. XLOOKUP – Mẫu mới, mạnh mẽ

Công thức: =XLOOKUP(giá_trị_tìm, cột_tìm, cột_trả_về, giá_trị_nếu_không_tìm, kết_quả_tùy_chọn, sắp_xếp)
Mô tả: Thay thế VLOOKUP/HLOOKUP, hỗ trợ tìm kiếm từ trái sang phải, từ phải sang trái, tìm kiếm gần nhất.

Ví dụ:
=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "Không tìm thấy").

4. Công thức làm việc với ngày tháng

4.1. DATE, YEAR, MONTH, DAY

• =DATE(năm, tháng, ngày) trả về ngày cụ thể.
• =YEAR(A1), =MONTH(A1), =DAY(A1) trích xuất thành phần năm, tháng, ngày.

4.2. TODAY & NOW

• =TODAY() trả về ngày hiện tại, không bao gồm thời gian.
• =NOW() trả về ngày và giờ hiện tại.

4.3. DAYOFWEEK, NETWORKDAYS

Có thể bạn quan tâm: Học Vba Excel Có Khó Không? – Giải Đáp Tất Cả Thắc Mắc Của Người Mới Bắt Đầu

• =WEEKDAY(A1, 1) trả về số thứ ngày trong tuần (1 = Chủ Nhật).
• =NETWORKDAYS(start_date, end_date, holidays) tính số ngày làm việc giữa hai ngày, bỏ qua cuối tuần và ngày lễ (nếu có).

4.4. DATEVALUE & TEXT

• =DATEVALUE("01/01/2026") chuyển chuỗi thành số ngày.
• =TEXT(A1, "dd/mm/yyyy") định dạng ngày dưới dạng văn bản.

5. Các công thức xử lý văn bản

5.1. CONCATENATE / CONCAT & TEXTJOIN

• =CONCATENATE(A1, " ", B1) tách từ hai ô.
• =CONCAT(A1, B1, C1) là phiên bản ngắn gọn.
• =TEXTJOIN(", ", TRUE, A1:C1) nối dãy ô, bỏ qua ô trống.

5.2. LEFT, RIGHT, MID

• =LEFT(A1, n) lấy n ký tự từ đầu.
• =RIGHT(A1, n) lấy n ký tự từ cuối.
• =MID(A1, start, length) lấy chuỗi từ vị trí start với độ dài length.

5.3. FIND & SEARCH

• =FIND("abc", A1) trả về vị trí bắt đầu của chuỗi “abc” (không phân biệt chữ hoa/như).
• =SEARCH("abc", A1) tương tự nhưng phân biệt.

5.4. TRIM, UPPER, LOWER, PROPER

• =TRIM(A1) loại bỏ khoảng trắng thừa.
• =UPPER(A1), =LOWER(A1), =PROPER(A1) chuyển chữ hoa/như/đầu riêng.

6. Công thức làm việc với mảng và dữ liệu động

6.1. SUMIFS, COUNTIFS, AVERAGEIFS

• =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
• =COUNTIFS(criteria_range1, criteria1, …)
• =AVERAGEIFS(average_range, criteria_range1, criteria1, …)

Ví dụ:
Tổng doanh thu theo tháng và khu vực:
=SUMIFS(B2:B100, C2:C100, "Tháng 1", D2:D100, "Bắc").

6.2. OFFSET

• =OFFSET(reference, rows, cols, height, width) trả về vùng dữ liệu offset từ tham chiếu.

Ví dụ:
Tính tổng 10 ô gần nhất:
=SUM(OFFSET(B2, -10, 0, 10, 1)).

6.3. INDEX + MATCH + OFFSET (Dynamic range)

Kết hợp để tạo dải dữ liệu động thay vì cố định.

6.4. FILTER, SORT, UNIQUE (Excel 365)

• =FILTER(range, include, if_empty) lọc dữ liệu dựa trên điều kiện.
• =SORT(range, sort_index, sort_order, by_col) sắp xếp dữ liệu.
• =UNIQUE(range, by_col, exactly_once) lấy giá trị duy nhất.

7. Công thức nâng cao: Xử lý lỗi, điều kiện phức tạp

7.1. CHOOSE

• =CHOOSE(index_num, value1, value2, …) trả về giá trị tương ứng với chỉ số.

Ví dụ:
=CHOOSE(2, "A", "B", "C") trả về “B”.

7.2. TRANSPOSE

• =TRANSPOSE(range) chuyển dãy ngang thành dọc hoặc ngược lại.

7.3. NORM.S.INV, NORM.DIST

• =NORM.S.INV(probability) trả về giá trị z tương ứng.
• =NORM.DIST(x, mean, standard_dev, cumulative) phân phối chuẩn.

7.4. RANK, RANK.AVG, PERCENTRANK

• =RANK(number, ref, order) xếp hạng số.
• =PERCENTRANK(array, x, significance) xác định phần trăm vị trí của giá trị.

7.5. NETWORKDAYS.INTL

• =NETWORKDAYS.INTL(start_date, end_date, weekend, holidays) tính số ngày làm việc tùy chỉnh tuần làm việc.

8. Công thức đối chiếu dữ liệu: Kết hợp bảng dữ liệu

8.1. Pivot Table (Bảng Pivot)

• Tạo bảng tổng hợp dữ liệu nhanh, cho phép phân loại, tính tổng, trung bình, đếm, v.v.

8.2. Conditional Formatting (Định dạng điều kiện)

• Sử dụng công thức trong định dạng điều kiện để làm nổi bật dữ liệu:
  =AND(A2>1000, B2="Hoạt động") → tô màu đỏ.

8.3. Data Validation (Xác thực dữ liệu)

• Sử dụng công thức trong xác thực dữ liệu để giới hạn giá trị nhập:
  =AND(A1>=0, A1<=100).

9. Một vài mẹo nâng cao

1. Kết hợp công thức với macro VBA: Khi cần thực hiện các tác vụ lặp lại, bạn có thể viết macro để chạy các công thức tự động.
2. Sử dụng tên vùng (Named Range): Giúp công thức dễ đọc, tránh lỗi khi di chuyển dữ liệu. Ví dụ: đặt tên DoanhThu cho cột B, rồi dùng =SUM(DoanhThu).
3. Sử dụng công thức mảng: Nhấn Ctrl+Shift+Enter (hoặc trong Excel 365, nhập công thức bình thường) để tính mảng. Ví dụ: {=SUM(A1:A10B1:B10)} tính tổng tích từng cặp.
4. Sử dụng hàm LET (Excel 365) để gán biến trong công thức, làm cho công thức ngắn gọn và nhanh hơn:
   =LET(x, A1:A10, y, B1:B10, SUM(xy)).

10. Kết luận

Hiểu và vận dụng thành thạo các công thức Excel thường dùng không chỉ giúp bạn xử lý dữ liệu nhanh hơn mà còn nâng cao năng suất làm việc và độ chính xác. Từ những công thức cơ bản như SUM, AVERAGE, IF cho tới các công thức nâng cao như INDEX/MATCH, XLOOKUP, FILTER, bạn có thể xây dựng các bảng tính phức tạp, tự động và có khả năng mở rộng. Hãy thực hành thường xuyên, tìm hiểu thêm các hàm mới và áp dụng vào thực tiễn để trở thành người sử dụng Excel chuyên nghiệp.

Chúc bạn thành công và tận hưởng hạnh phúc khi làm việc với 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ẻ.