Tổng hợp các hàm Excel nâng cao thông dụng nhất

Các hàm Excel nâng cao là công cụ đắc lực, đảm bảo độ chính xác khi phải xử lý khối lượng lớn dữ liệu. Vậy có những hàm nâng cao nào, cách sử dụng ra sao? Trong bài viết này, Sforum sẽ tổng hợp các hàm nâng cao trong Excel phổ biến cùng cú pháp chi tiết.

Nhóm hàm tra cứu và tham chiếu

Nhóm hàm tra cứu và tham chiếu là nhóm các hàm nâng cao của Excel tìm kiếm dữ liệu nhanh chóng. Điều này giúp tự động hóa quy trình tập hợp thông tin mỗi khi có số liệu mới hoặc tra cứu dữ liệu có sẵn.

Hàm VLOOKUP

Hàm VLOOKUP là hàm truy tìm thông tin theo cột trong bảng Excel. Cú pháp là

Thành phần:

• Lookup_value: Nội dung cần tìm.
• Table_array: Dữ liệu tìm kiếm.
• Col_index_num: Số thứ tự cột tra cứu trong vùng dữ liệu.
• Range_lookup: Chọn TRUE (gần đúng) hoặc FALSE (chính xác).

Ví dụ, trong bảng dữ liệu có liệt kê về tên sản phẩm, số lượng tồn và giá bán. Nếu cần tìm giá bán của sản phẩm “Bánh”, công thức minh họa áp dụng như sau:

Sử dụng phần mềm Microsoft bản quyền sẽ giúp khai thác tối đa các tính năng hữu ích của Excel trong công việc. Tìm hiểu ngay các phần mềm Microsoft chính hãng đang có ở CellphoneS:

[Product_Listing categoryid="1149" propertyid="" customlink="https://cellphones.com.vn/phu-kien/may-tinh-laptop/phan-mem/microsoft.html" title="Danh sách phần mềm Microsoft bản quyền đang được quan tâm nhiều tại CellphoneS"]

Hàm HLOOKUP

Là một trong các hàm Excel nâng cao có công dụng tìm kiếm, hàm HLOOKUP giúp tra cứu dữ liệu theo hàng. Cú pháp là:

Thành phần:

• Lookup_value: Nội dung tra cứu.
• Table_array: Bảng dữ liệu tham chiếu.
• Row_index_num: Số thứ tự hàng tìm kiếm trong bảng dữ liệu.
• Range_lookup: Chọn TRUE (gần đúng) hoặc FALSE (chính xác).

Ví dụ, bảng dữ liệu có thông tin về mức lương trợ cấp của từng cấp bậc nhân viên trong mỗi tháng. Nếu muốn tìm trợ cấp của trưởng phòng trong tháng 4, công thức minh họa như sau:

Hàm XLOOKUP

Hàm XLOOKUP là hàm Excel nâng cao cho người đi làm khi là sự cải tiến của VLOOKUP và HLOOKUP. Hàm này giúp tìm kiếm dữ liệu linh hoạt hơn khi có thể dùng tra cứu cột và hàng. Cú pháp hàm là:

Thành phần:

• Lookup_value: Kết quả muốn tìm.
• Lookup_array: Dải ô chứa giá trị tìm kiếm.
• Return_array: Dải ô chứa kết quả trả về.
• [if_not_found]: Kết quả khi không thấy giá trị cần tra cứu.
• [match_mode]: Chế độ so khớp.
• [search_mode]: Chế độ tìm kiếm.

Lưu ý, 3 giá trị [if_not_found], [match_mode], [search_mode] đều không bắt buộc phải điền. Do đó, bạn có thể bỏ qua 3 tham số này nếu không cần thực hiện tìm kiếm nâng cao.

Ví dụ, bạn muốn tra cứu tên của nhân viên bất kỳ khi có mã số nhân viên, cú pháp minh họa sẽ là:

Hàm INDEX + MATCH

Sự kết hợp INDEX + MATCH là một trong các hàm Excel nâng cao rất được ưa chuộng. Hàm này cho phép xác định kết quả dựa trên nhiều điều kiện tìm kiếm cùng lúc. Công thức là:

Thành phần: 

• Array: Vùng tìm kiếm.
• Lookup_value: Giá trị cần tra cứu.
• Lookup_array: Phạm vi tìm kiếm.
• MATCH_type: Kiểu khớp dữ liệu với 3 giá trị 0 (chính xác), 1 (nhỏ hơn gần nhất hoặc bằng) và -1 (lớn hơn gần nhất hoặc bằng).

Ví dụ, bạn muốn tìm kiếm lương của Nguyễn Văn An trong bảng lương tháng 1, công thức minh họa là:

Nhóm hàm xử lý chuỗi nâng cao

Hàm xử lý chuỗi là các hàm Excel nâng cao dùng để tách, nối hay chuẩn hóa chuỗi dữ liệu trong bảng. Sử dụng thành thạo những hàm này giúp vừa tiết kiệm thời gian xử lý vừa giúp dữ liệu chính xác hơn.

Hàm TEXTJOIN

Hàm TEXTJOIN dùng để nối nhiều chuỗi văn bản lại với nhau, cho phép thêm dấu phân cách tùy ý. Cú pháp là:

Thành phần:

• Delimiter: ký tự ngăn cách văn bản (ví dụ: “,” hoặc “-”).
• Ignore_empty: TRUE (bỏ qua ô trống), FALSE (giữ lại ô trống).
• Text1, text2, …: các chuỗi hoặc vùng dữ liệu cần nối.

Ví dụ minh họa, họ, tên đệm và tên được chia thành 4 ô riêng biệt. Cách dùng TEXTJOIN để nối họ tên đầy đủ và bỏ qua ô trống (nếu tên chỉ có 3 chữ) sẽ là:

Hàm CONCAT

Hàm CONCAT là một trong các hàm Excel nâng cao dùng để nối dữ liệu. Cú pháp là:

Thành phần:

• Text1: Chuỗi văn bản
• Text2: Chuỗi văn bản cần kết hợp (tối đa 253 chuỗi).

Ví dụ, để nối tên hoàn chỉnh ở nhiều ô, cú pháp cần dùng là:

Ảnh: Internet

Hàm LEFT, RIGHT, MID

Hàm LEFT, RIGHT và MID là các hàm Excel nâng cao dùng để tách ký tự trong 1 ô dữ liệu. Cú pháp từng hàm:

• Hàm LEFT:

Thành phần: 

• Text: Dữ liệu muốn tách.
• n: số ký tự muốn lấy.
• m (trong hàm MID): vị trí tách (tính cả khoảng trắng)

Ví dụ minh họa, công thức tách họ tên của “Nguyễn Văn Khôi” ra thành 3 phần khi dùng LEFT, RIGHT và MID lần lượt là:

Hàm TRIM, LEN

Hàm TRIM dùng để loại bỏ khoảng trắng dư trong văn bản. Hàm LEN dùng để đếm tổng ký tự trong ô dữ liệu (bao gồm cả khoảng trắng). Công thức mỗi hàm là:

• Hàm TRIM:

• Hàm LEN:

Thành phần text là dữ liệu cần bỏ ô trắng hoặc đếm ký tự.

Ví dụ minh họa, trong danh sách nhân viên, bạn nhận thấy có ô trắng dư thừa trong tên “Nguyễn Văn An”. Công thức loại bỏ khoảng trắng dư thừa áp dụng như sau:

Ví dụ, bạn cần đếm số ký tự trong tên của “Trần Thị Bình”, công thức là:

Hàm SUBSTITUTE

Hàm SUBSTITUTE là hàm chuyển đổi một hoặc nhiều văn bản cũ thành văn bản mới. Cú pháp hàm:

Thành phần: 

• Text: Chuỗi văn bản gốc cần chuyển đổi.
• Old_text: Đoạn văn bản cũ cần thay thế.
• New_text: Đoạn văn bản thay thế. 
• Instance_num (tùy chọn): Số lần xuất hiện của đoạn văn bản cần thay.

Ví dụ, bạn cần thay thương hiệu trong ô dữ liệu “Điện thoại Samsung” thành iPhone, công thức áp dụng là:

Nhóm hàm điều kiện và logic

Để Excel so sánh và trả về kết quả dựa trên điều kiện đặt ra, người dùng sẽ dùng nhóm hàm điều kiện và logic. Đây là các hàm Excel nâng cao trong kế toán và xử lý dữ liệu, giúp tự động ra quyết định trong bảng tính.

Hàm IF, IFS, AND, OR

Hàm IF, IFS, AND, OR là các hàm Excel nâng cao cho người đi làm nên biết. Cụ thể:

Hàm IF

Hàm IF là hàm kiểm tra yêu cầu đưa ra và thông báo kết quả đúng (TRUE) hay sai (FALSE). Cú pháp là:

Thành phần:

• Logical_test: Yêu cầu đưa ra.
• Value_if_true: Kết quả khi logical_test đúng
• Value_if_false: Kết quả khi logical_test sai.

Ví dụ minh họa, kiểm tra học sinh có vượt qua môn Toán hay không với điều kiện tìm từ 5 điểm là Đạt và từ 4 điểm trở xuống là Không. Cách làm như sau:

Hàm IFS

Hàm IFS kiểm tra nhiều điều kiện khác nhau mà không cần dùng nhiều hàm IF. Cú pháp là:

Ví dụ, xếp loại học sinh Nguyễn Văn An dựa vào điểm môn Toán, công thức minh họa như sau:

Hàm AND

Hàm AND là hàm kiểm tra dựa trên nhiều tiêu chí khác nhau. Kết quả là TRUE nếu các điều kiện đưa ra đều đúng. Nếu sai bất kỳ điều kiện nào, kết quả là FALSE. Cú pháp minh họa là:

Ví dụ, bạn xác định việc lên lớp hay không sẽ dựa vào điểm của 3 môn Toán - Văn - Anh. Cú pháp minh họa là:

Hàm OR

Hàm OR cũng dùng để đánh giá nhiều yêu cầu cùng lúc. Tuy nhiên, hàm này chỉ cần 1 điều kiện đúng cũng sẽ đưa ra kết quả TRUE. Nếu tất cả đều sai, kết quả mới trả về FALSE. Cú pháp hàm:

Ví dụ, để lên lớp, chỉ cần điểm của 1 trong 3 môn Toán - Văn - Anh thỏa mãn điều kiện là được. Công thức minh họa là:

Hàm SWITCH

Hàm SWITCH là một trong các hàm Excel nâng cao dùng để so sánh và trả về kết quả trùng khớp điều kiện đưa ra. Cú pháp hàm:

Thành phần:

• Expression: Giá trị muốn so sánh.
• Value1, value2,...: Các trường hợp cần so sánh.
• Result1, result2,...: Kết quả trả về nếu khớp với giá trị tương ứng.
• Default (tùy chọn): Kết quả khi không trùng yêu cầu nào.

Ví dụ, bạn cần xếp hạng điểm Toán của học sinh Nguyễn Văn An, cú pháp minh họa là:

 

Nhóm hàm tính toán và thống kê nâng cao

Nhóm hàm tính toán và thống kê tự động xử lý dữ liệu phức tạp, thực hiện phép tính và phân tích số liệu. Đây là các hàm Excel nâng cao hữu ích khi giảm bớt sai sót trong việc xử lý nhiều dữ liệu cùng lúc.

Hàm SUMIFS, COUNTIFS, AVERAGEIFS

Hàm SUMIFS

Hàm SUMIFS cộng nhiều đối số thỏa mãn nhiều tiêu chí đưa ra. Công thức:

Thành phần: 

• Sum_range: Phạm vi các ô tính tổng.
• Criteria_range1, criteria_range2, …: Phạm vi kiểm tra điều kiện.
• Criteria1, criteria2, …: Điều kiện từng ô.

Ví dụ, dựa trên các đơn hàng, bạn muốn tính tổng số lượng táo đã bán của Nguyễn Văn An. Cú pháp minh họa là:

Hàm COUNTIFS

Hàm COUNTIFS là hàm Excel dùng để đếm nhiều ô dữ liệu thỏa mãn yêu cầu đưa ra. Cú pháp hàm:

Ví dụ, bạn muốn đếm có bao nhiêu đơn hàng táo có số lượng từ 200 trở lên, công thức minh họa là:

Hàm AVERAGEIFS

Hàm AVERAGEIFS là hàm tính giá trị trung bình của các ô thỏa mãn nhiều điều kiện cùng lúc. Công thức là:

Tiêu chí average_range là phạm vi các ô cần tính trung bình. Các tiêu chí còn lại tương tự 2 công thức ở trên.

Ví dụ, bạn muốn tính trung bình số lượng táo đã bán của Nguyễn Văn An. Cú pháp minh họa là:

Hàm RANK

Hàm RANK là hàm xác định thứ hạng của một giá trị cụ thể trong phạm vi điều kiện đưa ra. Cú pháp hàm:

Thành phần: 

• Number: Ô cần xếp hạng.
• Ref: Dữ liệu so sánh.
• Order (tùy chọn): 0 (xếp hạng giảm dần) hoặc 1 (thứ tự tăng dần).

Ví dụ, để kiểm tra điểm toán của Lê Văn Công xếp hạng thứ bao nhiêu, công thức minh họa là:

 

Hàm LARGE, SMALL

Hàm LARGE và SMALL là các hàm Excel nâng cao trích giá trị lớn nhất hoặc nhỏ nhất theo thứ tự trong một dãy số. Cú pháp hàm:

• Hàm LARGE:

• Hàm SMALL:

Thành phần: 

• Array: Phạm vi so sánh.
• K: Thứ tự thứ k muốn tìm.

Ví dụ, trong bảng điểm của lớp, bạn cần tìm điểm Văn cao thứ 2 và điểm văn thấp nhất là bao nhiêu. Cú pháp minh họa là:

Nhóm hàm ngày tháng và thời gian

Nhóm hàm thời gian là công cụ quan trọng giúp tự động tính toán ngày tháng. Tổng hợp các hàm Excel nâng cao và cách dùng sẽ hữu ích khi cần tính lương, lập kế hoạch sản xuất, báo cáo thời gian và nhiều lĩnh vực khác.

Hàm DATEDIF

Hàm DATEDIF là hàm tính khoảng cách giữa hai ngày theo điều kiện. Cú pháp hàm:

Thành phần:

• Start_date: Thời điểm đầu.
• End_date: Thời điểm cuối.
• Unit: Đơn vị ngày, tháng, năm…

Ví dụ, để tính số ngày khoảng cách giữa 1/1/2025 và 6/5/2025, công thức minh họa là:

Hàm NETWORKDAYS

Hàm NETWORKDAYS là hàm tính số ngày làm việc thực tế giữa hai ngày. Số liệu này loại bỏ thứ 7, chủ nhật và có thể bao gồm ngày lễ (tùy chọn). Cú pháp hàm:

Thành phần:

• Start_date: Ngày đầu.
• End_date: Ngày cuối.
• Holidays: Ngày lễ.

Ví dụ, để số ngày làm việc trong khoảng 1/1/2025 và 6/5/2025, công thức minh họa là:

 

Hàm EDATE, EOMONTH

Hàm EDATE và EOMONTH là các hàm Excel nâng cao dùng tính ngày, tháng phổ biến.

Hàm EDATE

Hàm EDATE là hàm tính ngày cách một số tháng nhất định dựa trên ngày cho trước. Công thức hàm:

Thành phần:

• start_date: Ngày bắt đầu.
• months: Số tháng muốn cộng (nếu muốn lùi về trước đó, dùng dấu “-” đằng trước).

Ví dụ, bạn muốn biết 13 tháng sau của ngày 1/1/2025 là tháng nào, công thức minh họa là:

Hàm EOMONTH

Hàm EOMONTH dùng để tìm ngày cuối cùng của tháng trước/sau dựa trên điều kiện đưa ra. Cú pháp hàm:

Thành phần:

• start_date: Ngày khởi đầu.
• months: Số tháng muốn tính (dùng 0 để tính cuối tháng này, số dương tính cho tháng sau và số âm tính cho tháng trước).

Ví dụ, bạn muốn biết ngày cuối tháng của 13 tháng sau tính từ ngày 1/1/2025 là tháng nào, công thức minh họa là:

Hàm WORKDAY

Hàm WORKDAY giúp tính ngày làm việc sau khi cộng thêm một số ngày nhất định từ ngày bắt đầu. Hàm sẽ tự động bỏ qua thứ 7, chủ nhật và các ngày lễ tùy chọn. Công thức là:

Thành phần:

• Start_date: Ngày bắt đầu.
• Days: Số ngày làm việc muốn cộng (có thể là số âm).
• Holidays (tùy chọn): Danh sách ngày lễ.

Ví dụ, bạn muốn biết thời điểm sau 30 ngày làm việc khi tính từ ngày 1/1/2025, công thức minh họa là:

Nhóm hàm mảng (Array Formulas) và Dynamic Array

Nhóm Array Formulas và Dynamic Array là các hàm Excel nâng cao xử lý và phân tích nhiều giá trị cùng lúc. Nhóm hàm này giúp việc thao tác dữ liệu trở nên linh hoạt, nhanh chóng và chính xác hơn.

Hàm FILTER, SORT

Hàm FILTER và SORT là các hàm Excel nâng cao thường dùng để lọc và sắp xếp dữ liệu.

Hàm FILTER

Hàm FILTER để lọc ra các giá trị thỏa mãn điều kiện từ một bảng hoặc phạm vi dữ liệu. Công thức là:

Thành phần:

• Array: Bảng tham chiếu. 
• Include: Yêu cầu lọc (TRUE/FALSE).
• If_empty (tùy chọn): Kết quả nếu không có dữ liệu đáp ứng yêu cầu.

Ví dụ, bạn muốn tìm những thông tin liên quan đến các đơn hàng Táo, công thức minh họa:

Hàm SORT

Hàm SORT dùng để lọc các dữ liệu theo yêu cầu của người dùng. Công thức hàm:

Thành phần:

• Array: Vùng dữ liệu cần sàng lọc.
• Sort_index: Cột/hàng muốn sàng lọc.
• Sort_order: 1 (giá trị tăng dần) hoặc -1 (giá trị giảm dần).
• By_col (không bắt buộc): Chọn sắp xếp theo hàng hay cột.

Ví dụ, bạn cần sắp xếp học sinh dựa trên thứ tự điểm Toán từ thấp đến cao, cú pháp minh họa là:

Hàm UNIQUE

Hàm UNIQUE là hàm chỉ lấy giá trị duy nhất xuất hiện trong bảng và loại bỏ dữ liệu trùng lặp. Công thức hàm:

Thành phần: 

• Array: Phạm vi dữ liệu.
• By_col: TRUE (lấy theo cột), FALSE (lấy theo hàng, thiết lập mặc định).
• Exactly_once: TRUE (chỉ lấy giá trị xuất hiện chỉ 1 lần), FALSE (lấy tất cả các giá trị duy nhất).

Ví dụ, bạn đang cần biết mặt hàng mới chỉ bán 1 lần duy nhất của mỗi nhân viên, công thức minh họa như sau:

 

Hàm SEQUENCE

Hàm SEQUENCE có tác dụng tạo lập bảng dữ liệu chứa các số tạo theo yêu cầu. Công thức hàm:

Thành phần:

• Rows: Số hàng của dãy số.
• Columns: Số cột của dãy số.
• Start: Số bắt đầu.
• Step: Khoảng cách giữa các số.

Ví dụ, bạn muốn tạo bảng có 10 hàng và 1 cột với các số được xếp theo thứ tự tăng dần từ 1 đến 10. Công thức minh họa là:

Hàm RANDARRAY

Hàm RANDARRAY là một trong các hàm Excel nâng cao có công dụng trả kết quả là số ngẫu nhiên. Cú pháp hàm:

Tất cả thành phần đều là tùy chọn, gồm:

• Rows: Số lượng hàng.
• Columns: Số lượng cột.
• Min: Kết quả nhỏ nhất.
• Max: Kết quả lớn nhất
• Whole_number: TRUE (kết quả là số nguyên) hoặc FALSE (kết quả là số thập phân).

Ví dụ, muốn tạo một bảng dữ liệu có 5 hàng và 3 cột chứa các số ngẫu nhiên, cú pháp là:

Các hàm Excel nâng cao cùng cú pháp và ví dụ minh họa đã được hướng dẫn cụ thể. Hãy thực hành thường xuyên để vận dụng thành thạo các hàm này, giúp hỗ trợ tốt hơn trong công việc. Đồng thời, tiếp tục truy cập Sforum để biết thêm nhiều mẹo dùng Excel, Word và PowerPoint nhé.

• Xem thêm: Tin học văn phòng, Thủ thuật Excel