20 hàm Excel quan trọng nhất phải biết

kienthuc247.com

Nhóm hàm Excel cơ bản tính toán và thống kê số liệu

Đây là những nền tảng đầu tiên mà bất kỳ ai khi tiếp cận bảng tính đều phải thành thạo. Các hàm Excel cơ bản này giúp bạn thực hiện nhanh các phép toán cộng, trung bình, đếm dữ liệu và làm tròn số cực kỳ nhanh chóng.

1. Hàm SUM - Tính tổng tất cả các giá trị

Hàm SUM là công thức Excel kinh điển nhất dùng để cộng tất cả các số trong một vùng dữ liệu được chọn.

• Cú pháp: =SUM(number1, [number2], ...) hoặc =SUM(vùng_chọn)
• Ví dụ thực tế: Bạn cần tính tổng doanh thu tháng của bộ phận bán hàng nằm ở cột C từ dòng 2 đến dòng 100. Công thức sẽ là: =SUM(C2:C100).
• Lưu ý: Hàm SUM sẽ tự động bỏ qua các ô chứa ký tự văn bản (text) hoặc ô trống, nhưng sẽ trả về lỗi nếu trong vùng chọn có chứa bất kỳ ô nào bị lỗi hệ thống như #N/A, #VALUE!.

2. Hàm AVERAGE - Tính giá trị trung bình cộng

Dùng để tính toán giá trị trung bình của một dãy số, giúp nhà quản trị đánh giá được mức độ hiệu quả trung bình của doanh số hoặc chi phí.

• Cú pháp: =AVERAGE(number1, [number2], ...)
• Ví dụ thực tế: Tính điểm trung bình của ứng viên trong kỳ đánh giá năng lực tại các ô từ D2 đến D15: =AVERAGE(D2:D15).

3. Hàm COUNT & COUNTA - Đếm số lượng ô dữ liệu

Rất nhiều người nhầm lẫn giữa hai hàm này, dẫn đến việc thiết lập báo cáo bị sai lệch số liệu nghiêm trọng.

• Hàm COUNT: Chỉ đếm các ô có chứa dữ liệu dạng số (numbers, dates). Cú pháp: =COUNT(value1, [value2], ...).
• Hàm COUNTA: Đếm tất cả các ô không trống, bất kể ô đó chứa chữ, số, biểu tượng hay công thức rỗng. Cú pháp: =COUNTA(value1, [value2], ...).
• Ứng dụng: Dùng COUNTA để đếm tổng số nhân sự có mặt trong danh sách (dữ liệu dạng chữ), dùng COUNT để đếm số lượng nhân viên đã hoàn thành chỉ tiêu doanh số (dữ liệu dạng số).

4. Hàm MAX & MIN - Tìm giá trị lớn nhất và nhỏ nhất

Giúp xác định nhanh điểm số cao nhất, giá trị đơn hàng lớn nhất hoặc chi phí thấp nhất trong một bảng dữ liệu lớn mà không cần dùng bộ lọc Sort thủ công.

• Cú pháp: =MAX(vùng_dữ_liệu) và =MIN(vùng_dữ_liệu)
• Ví dụ: Tìm đơn hàng có giá trị lớn nhất phát sinh trong tháng tại cột E: =MAX(E2:E500).

5. Hàm ROUND - Làm tròn số thập phân

Trong kế toán và tài chính, việc để các con số thập phân lẻ tẻ sau dấu phẩy dễ gây lệch dòng tiền khi cộng tổng. Hàm ROUND giúp bạn chuẩn hóa định dạng số liệu.

• Cú pháp: =ROUND(number, num_digits)
• Trong đó: num_digits là số chữ số thập phân bạn muốn làm tròn. Nếu truyền số âm, hàm sẽ làm tròn đến hàng chục, hàng trăm, hàng nghìn.
• Ví dụ: Làm tròn số 12345.678 đến 2 chữ số thập phân: =ROUND(12345.678, 2) cho kết quả là 12345.68. Làm tròn đến hàng nghìn: =ROUND(12345.678, -3) cho kết quả là 12000.

kienthuc247.com

Nhóm hàm điều kiện logic xử lý dữ liệu thông minh

Để biến những bảng tính tĩnh thành những công cụ phân tích động, bạn bắt buộc phải biết cách sử dụng các hàm điều kiện logic. Nhóm hàm này cho phép Excel tự đưa ra các quyết định xử lý dựa trên các quy tắc bạn đã định nghĩa trước.

6. Hàm IF - Hàm điều kiện cơ bản

Hàm IF là nền tảng của mọi tư duy lập trình và phân tích dữ liệu trên Excel. Nó trả về một giá trị nếu điều kiện đúng, và một giá trị khác nếu điều kiện sai.

• Cú pháp: =IF(logical_test, value_if_true, value_if_false)
• Ví dụ thực tế: Đánh giá kết quả làm việc của nhân viên dựa trên KPI tại ô B2. Nếu KPI đạt từ 80 trở lên thì ghi "Đạt", ngược lại ghi "Không đạt": =IF(B2>=80, "Đạt", "Không đạt").
• Hàm IF lồng nhau (Nested IF): Khi có nhiều hơn 2 điều kiện cần phân loại. Ví dụ xếp loại học lực: =IF(B2>=90, "Xuất sắc", IF(B2>=75, "Khá", "Trung bình")).

7. Bộ đôi hàm AND & OR - Kết hợp nhiều điều kiện

Thông thường, hàm IF đơn lẻ chỉ giải quyết được một tiêu chí. Để xét duyệt các trường hợp phức tạp hơn, chúng ta cần lồng ghép AND hoặc OR vào trong biểu thức logic.

• Hàm AND: Chỉ trả về TRUE khi tất cả các điều kiện con đều đúng. Cú pháp: =AND(điều_kiện1, điều_kiện2, ...).
• Hàm OR: Trả về TRUE chỉ cần ít nhất một điều kiện con đúng. Cú pháp: =OR(điều_kiện1, điều_kiện2, ...).
• Ví dụ phối hợp: Duyệt thưởng cho nhân viên nếu có số ngày công lớn hơn 24 ngày VÀ doanh số vượt mức 100 triệu: =IF(AND(C2>24, D2>100000000), "Duyệt Thưởng", "Không Duyệt").

8. Hàm SUMIF & SUMIFS - Tính tổng theo điều kiện

Khi quản trị dòng tiền hay vận hành doanh nghiệp, việc cộng tổng toàn bộ bảng tính thường không đem lại nhiều giá trị phân tích bằng việc cộng tổng từng danh mục riêng lẻ.

• Hàm SUMIF (Một điều kiện): =SUMIF(vùng_điều_kiện, điều_kiện, vùng_tính_tổng). Ví dụ: Tính tổng doanh thu của riêng mặt hàng "Điện thoại": =SUMIF(A2:A100, "Điện thoại", C2:C100).
• Hàm SUMIFS (Nhiều điều kiện cùng lúc): =SUMIFS(vùng_tính_tổng, vùng_điều_kiện1, điều_kiện1, vùng_điều_kiện2, điều_kiện2, ...). Lưu ý cực kỳ quan trọng là thứ tự các tham số của SUMIFS ngược lại hoàn toàn so với SUMIF, vùng tính tổng luôn được đưa lên đầu tiên.
• Ứng dụng: Bạn có thể dễ dàng thiết kế một File quản lý kho hàng tự động tính lượng xuất - nhập dựa theo mã hàng hóa và thời gian cụ thể nhờ vào hàm SUMIFS cực kỳ linh hoạt này.

9. Hàm COUNTIF & COUNTIFS - Đếm theo điều kiện

Tương tự như nhóm hàm SUM điều kiện, bộ đôi này giúp bạn đếm tần suất xuất hiện của các đối tượng thỏa mãn yêu cầu cụ thể.

• Cú pháp COUNTIF: =COUNTIF(vùng_chọn, điều_kiện)
• Cú pháp COUNTIFS: =COUNTIFS(vùng_điều_kiện1, điều_kiện1, vùng_điều_kiện2, điều_kiện2, ...)
• Ví dụ: Đếm số lần nhân viên Nguyễn Văn A đi làm muộn trong tháng (có ký hiệu "Muộn" ở cột trạng thái): =COUNTIFS(A2:A31, "Nguyễn Văn A", B2:B31, "Muộn"). Việc đếm thủ công hàng trăm nhân sự giờ đây được giải quyết chỉ trong một giây, giúp giảm thiểu rủi ro khi tính lương và phát hiện rủi ro qua dữ liệu chấm công bị sai lệch.

10. Hàm IFERROR - Bẫy lỗi công thức chuyên nghiệp

Một bảng tính chuyên nghiệp không được phép hiển thị các mã lỗi gây mất mỹ quan như #N/A, #VALUE!, #DIV/0! khi chia cho số 0 hoặc không tìm thấy dữ liệu.

• Cú pháp: =IFERROR(biểu_thức_tính, giá_trị_thay_thế)
• Ví dụ thực tế: Khi tính tỷ lệ tăng trưởng doanh số bằng công thức: =(Doanh_thu_mới - Doanh_thu_cũ) / Doanh_thu_cũ. Nếu doanh thu cũ bằng 0, phép chia sẽ lỗi. Khắc phục bằng: =IFERROR((B2-C2)/C2, 0). Lúc này, thay vì hiện lỗi xấu xí, Excel sẽ hiển thị số 0 tròn trĩnh và sạch sẽ.

Nhóm hàm Excel nâng cao tìm kiếm và tham chiếu tối ưu

Nếu muốn thăng tiến và tự tin khẳng định kỹ năng xử lý dữ liệu của mình, bạn không thể bỏ qua các hàm Excel nâng cao dùng để liên kết các bảng dữ liệu lại với nhau. Đây chính là xương sống của mọi hệ thống báo cáo quản trị động.

kienthuc247.com

11. Hàm VLOOKUP - Dò tìm dữ liệu theo chiều dọc

Đây là hàm tìm kiếm phổ biến nhất, giúp bạn lấy thông tin từ bảng danh mục gốc đưa vào bảng chi tiết dựa trên một khóa liên kết (mã nhân viên, mã sản phẩm...).

• Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Trong đó, tham số cuối cùng [range_lookup] nên để là 0 (hoặc FALSE) để dò tìm chính xác tuyệt đối.
• Ví dụ: Điền tên sản phẩm vào bảng bán hàng dựa trên Mã sản phẩm ở ô A2 và bảng danh mục sản phẩm từ ô F2 đến G100: =VLOOKUP(A2, F2:G100, 2, 0).
• Hạn chế: Hàm VLOOKUP chỉ có thể tìm kiếm từ trái qua phải. Nghĩa là cột chứa từ khóa tìm kiếm bắt buộc phải nằm ở vị trí đầu tiên bên trái của bảng tham chiếu.

12. Hàm HLOOKUP - Dò tìm dữ liệu theo chiều ngang

Hoạt động tương tự như VLOOKUP nhưng áp dụng cho cấu trúc bảng nằm ngang (các tiêu đề danh mục được xếp theo hàng thay vì xếp theo cột).

• Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Ứng dụng: Thường dùng để dò tìm mức thuế suất áp dụng theo các bậc thu nhập nằm ngang hoặc dò tìm ca làm việc của nhân viên theo ngày trong tháng sắp xếp theo dòng nằm ngang.

13. Bộ đôi INDEX & MATCH - Giải pháp thay thế hoàn hảo cho VLOOKUP

Khi bảng dữ liệu có cấu trúc phức tạp và cột tìm kiếm nằm ở bên phải của cột kết quả cần lấy, VLOOKUP sẽ hoàn toàn bất lực. Lúc này, sự kết hợp giữa INDEX và MATCH là cứu cánh tối ưu nhất.

• Hàm MATCH: Xác định vị trí (dòng hoặc cột) của một giá trị trong một dãy. Cú pháp: =MATCH(lookup_value, lookup_array, 0).
• Hàm INDEX: Trả về giá trị tại giao điểm của một dòng và một cột cụ thể. Cú pháp: =INDEX(array, row_num, [column_num]).
• Công thức kết hợp: =INDEX(Cột_chứa_kết_quả, MATCH(Giá_trị_dò, Cột_chứa_giá_trị_dò, 0)). Sự linh hoạt của cặp đôi này giúp cải thiện đáng kể tốc độ xử lý của file Excel đối với các tập dữ liệu lớn lên đến hàng chục nghìn dòng.

14. Hàm XLOOKUP - Đỉnh cao dò tìm thế hệ mới

Nếu bạn đang sử dụng phiên bản Excel 365 hoặc Excel 2021 trở lên, XLOOKUP là hàm tìm kiếm mạnh mẽ nhất được sinh ra để thay thế hoàn toàn cho cả VLOOKUP, HLOOKUP và tổ hợp INDEX-MATCH.

• Cú pháp: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Ưu điểm vượt trội: Tìm kiếm được theo mọi chiều (trái sang phải, phải sang trái, trên xuống dưới), tích hợp sẵn tính năng bẫy lỗi nếu không tìm thấy dữ liệu thông qua tham số [if_not_found] mà không cần dùng thêm hàm IFERROR hỗ trợ bên ngoài.
• Ví dụ: =XLOOKUP(A2, D2:D100, B2:B100, "Không tồn tại").

15. Hàm CHOOSE - Lựa chọn giá trị theo chỉ mục

Hàm CHOOSE cho phép bạn trả về một giá trị cụ thể từ danh sách các lựa chọn dựa trên một số chỉ mục (index) được chỉ định trước.

• Cú pháp: =CHOOSE(index_num, value1, [value2], ...)
• Ứng dụng: Thường dùng để chuyển đổi nhanh số tháng sang quý trong năm. Ví dụ với ô A2 chứa giá trị tháng là 5: =CHOOSE(A2, "Quý 1", "Quý 1", "Quý 1", "Quý 2", "Quý 2", "Quý 2", "Quý 3", "Quý 3", "Quý 3", "Quý 4", "Quý 4", "Quý 4") sẽ cho ra kết quả ngay lập tức là "Quý 2".

Nhóm hàm xử lý chuỗi ký tự và định dạng thời gian văn phòng

Dữ liệu thô tải xuống từ các phần mềm kế toán hoặc ERP thường bị lỗi định dạng khoảng trắng thừa, dính chữ hoặc ngày tháng không chuẩn. Việc làm sạch dữ liệu bằng các hàm xử lý chuỗi là bước chuẩn bị bắt buộc trước khi tiến hành phân tích chuyên sâu.

16. Hàm LEFT, RIGHT & MID - Cắt chuỗi ký tự linh hoạt

Bộ ba hàm này giúp bạn trích xuất các phần nhỏ của một chuỗi văn bản dài dựa theo số lượng ký tự yêu cầu.

• Hàm LEFT: Cắt chuỗi từ bên trái qua. Cú pháp: =LEFT(text, [num_chars]).
• Hàm RIGHT: Cắt chuỗi từ bên phải qua. Cú pháp: =RIGHT(text, [num_chars]).
• Hàm MID: Cắt chuỗi từ một vị trí bất kỳ ở giữa. Cú pháp: =MID(text, start_num, num_chars).
• Ví dụ thực tế: Bạn có một cột mã nhân viên định dạng dạng "HN-KETOAN-05" tại ô A2. Để lấy mã vùng miền (HN), dùng: =LEFT(A2, 2). Để lấy số thứ tự nhân viên (05) nằm ở cuối, dùng: =RIGHT(A2, 2).

17. Hàm CONCAT hoặc CONCATENATE - Ghép nối các chuỗi dữ liệu

Ngược lại với nhóm hàm cắt chuỗi, CONCAT dùng để gộp thông tin từ nhiều ô khác nhau thành một chuỗi văn bản duy nhất phục vụ việc lập mã định danh.

• Cú pháp: =CONCAT(text1, [text2], ...) hoặc sử dụng ký tự nối nhanh &.
• Ví dụ: Ghép Họ ở ô A2 và Tên ở ô B2 lại với nhau kèm một khoảng trắng ở giữa: =CONCAT(A2, " ", B2) hoặc =A2 & " " & B2.

18. Hàm TRIM - Chuẩn hóa khoảng trắng dữ liệu thô

Lỗi thừa khoảng trắng ở đầu hoặc cuối chuỗi ký tự là nguyên nhân hàng đầu khiến hàm VLOOKUP hoặc MATCH báo lỗi #N/A một cách vô lý dù nhìn bằng mắt thường hai ô hoàn toàn giống nhau. Hàm TRIM sẽ loại bỏ toàn bộ khoảng trắng thừa đó, chỉ giữ lại một khoảng trắng duy nhất giữa các từ.

• Cú pháp: =TRIM(text)
• Mẹo thực chiến: Luôn lồng ghép hàm TRIM bên trong hàm tìm kiếm để tăng độ chính xác: =VLOOKUP(TRIM(A2), D2:E100, 2, 0).

19. Hàm LEN - Đo độ dài chuỗi ký tự

Hàm LEN trả về tổng số lượng ký tự có trong ô dữ liệu, bao gồm cả các ký tự chữ, số, dấu câu và khoảng trắng.

• Cú pháp: =LEN(text)
• Ứng dụng: Dùng để kiểm tra độ dài mã số thuế (thường là 10 hoặc 13 số), số tài khoản ngân hàng hoặc số điện thoại của khách hàng có hợp lệ hay không trước khi lưu trữ vào hệ thống quản lý tập trung.

20. Hàm TODAY & NOW - Cập nhật thời gian thực tự động

Khi lập các báo cáo công nợ quá hạn, tính tuổi nợ hoặc đếm số ngày còn lại để hoàn thành dự án, bạn cần một mốc thời gian luôn tự động thay đổi theo ngày hiện tại.

• Hàm TODAY: Trả về ngày, tháng, năm hiện tại của hệ thống máy tính. Cú pháp: =TODAY() (không truyền bất kỳ tham số nào vào trong ngoặc).
• Hàm NOW: Trả về cả ngày, tháng, năm và giờ phút hiện tại. Cú pháp: =NOW().
• Ví dụ ứng dụng: Tính toán số ngày một dự án bị chậm tiến độ bằng cách lấy ngày hiện tại trừ đi ngày hạn chót được ghi ở ô C2: =TODAY() - C2. Việc kiểm soát này giúp nhà quản trị điều phối nhân sự và thực hiện quản lý thời gian hiệu quả hơn rất nhiều.

Câu hỏi thường gặp:

Tại sao hàm VLOOKUP của tôi liên tục trả về lỗi #N/A dù giá trị tìm kiếm hoàn toàn tồn tại?

Có hai nguyên nhân phổ biến nhất: Một là giá trị tìm kiếm chứa khoảng trắng ẩn ở đầu/cuối (hãy xử lý bằng hàm TRIM). Hai là định dạng dữ liệu giữa ô tìm kiếm và cột đầu tiên của vùng bảng tham chiếu không đồng nhất (một bên là dạng Text, một bên là dạng Number). Bạn cần chuyển đổi chúng về cùng một kiểu định dạng để Excel nhận diện được liên kết.

Làm thế nào để học và ghi nhớ các công thức Excel này một cách nhanh chóng?

Đừng cố học thuộc lòng cú pháp. Thay vào đó, hãy tập trung nắm vững tư duy giải quyết vấn đề bằng cách chia nhỏ yêu cầu nghiệp vụ thành từng điều kiện. Hãy thực hành trực tiếp trên các tình huống công việc thực tế hàng ngày, kết hợp sử dụng công cụ hỗ trợ thông minh để tăng tốc quá trình làm việc của bạn.

Tôi nên chọn sử dụng VLOOKUP hay INDEX & MATCH khi xử lý các bảng dữ liệu lớn?

Với các bảng tính lớn (trên 10,000 dòng), bạn nên ưu tiên sử dụng tổ hợp INDEX & MATCH hoặc hàm XLOOKUP. Lý do là VLOOKUP sẽ tải toàn bộ bảng tham chiếu vào bộ nhớ tạm của máy tính, khiến file bị giật lag nghiêm trọng, trong khi INDEX & MATCH chỉ quét đúng hai cột chứa giá trị tìm kiếm và kết quả, giúp cải thiện đáng kể tốc độ xử lý của máy tính.

Kết luận

Việc làm chủ 20 hàm Excel quan trọng nhất dân văn phòng phải biết không chỉ đơn thuần là việc nhớ tên các công thức, mà đó là hành trình xây dựng tư duy phân tích dữ liệu một cách logic và khoa học. Hãy bắt đầu áp dụng ngay những kiến thức thực tiễn này vào công việc hàng ngày của bạn để tự động hóa tối đa các tác vụ lặp đi lặp lại. Để bứt phá hiệu suất công việc xa hơn nữa trong môi trường số hóa hiện nay, việc kết hợp thành thạo các kỹ năng bảng tính với giải pháp tối ưu hiệu suất bằng AI chắc chắn sẽ là bệ phóng hoàn hảo giúp sự nghiệp của bạn thăng tiến nhanh chóng.

Mô tả tìm kiếm: Tổng hợp chi tiết 20 hàm Excel quan trọng nhất dân văn phòng phải biết giúp xử lý dữ liệu thông minh, tăng hiệu suất công việc cực nhanh.

Slug: 20-ham-excel-quan-trong-nhat-dan-van-phong-phai-biet

Chuyên mục: Tin học văn phòng, Kỹ năng công việc