Các Hàm và Tính Năng Excel Cốt Lỗi Mọi Chuyên Viên Tư Vấn Quản Trị Nên Thành Thạo

Đối với các chuyên viên tư vấn quản trị phải thực hiện phân tích dữ liệu và trình bày báo cáo cho khách hàng, bộ sản phẩm Microsoft là tài nguyên đắc lực. Và chắc chắn, Microsoft Excel là công cụ quan trọng nhất. Bài viết này tổng hợp các hàm và tính năng Excel thiết yếu dành cho chuyên viên tư vấn.

Bạn có thể tải file thực hành tại đây.

Top 70 Hàm Excel Phổ Biến

Phần dưới đây giới thiệu 70 hàm Excel then chốt để quản lý và trình bày dữ liệu, được phân loại theo nhóm chức năng kèm minh họa chi tiết.

Nhóm Hàm Ngày/Tháng (8 Hàm)

Quản lý dữ liệu kinh doanh được hỗ trợ mạnh mẽ bởi các hàm DATE. Dưới đây là mô tả ngắn gọn từng hàm.

Hàm DAY

Hàm DAY chỉ nhận một đối số. Trả về ngày trong tháng (số nguyên từ 1 đến 31) của một ngày cho trước.

• Cú pháp chung

=DAY(serial_number)

• Mô tả đối số

Ví dụ hàm DAY

Áp dụng hàm DAY cho ô B5 (chứa ngày 13/9). Kết quả trả về 13. Công thức tại ô C5:

• Nhấn Enter.

• Kéo chuột xuống để điền công thức cho các ô còn lại.

Hàm MONTH

Trả về tháng (số nguyên 1 đến 12) của một ngày serial number.

• Cú pháp chung

=MONTH(serial_number)

• Mô tả đối số

Ví dụ hàm MONTH

Với ngày 13/9 tại ô B5, hàm trả về 9. Công thức tại C5:

• Nhấn Enter.

• Kéo chuột để điền tiếp.

Hàm YEAR

Trả về năm (số nguyên từ 1900 đến 9999) của một ngày.

• Cú pháp chung

=YEAR(serial_number)

• Mô tả đối số

Ví dụ hàm YEAR

Với ngày 13/9/2022 tại ô B5, hàm trả về 2022. Công thức tại C5:

• Nhấn Enter.

• Kéo chuột để áp dụng cho các ô dưới.

Hàm WEEKDAY

Trả về ngày trong tuần (mặc định: 1 = Chủ nhật đến 7 = Thứ bảy) của một ngày.

• Cú pháp chung

=WEEKDAY(serial_number,[return_type])

• Mô tả đối số

Ví dụ hàm WEEKDAY

Ngày tại B5 là Thứ ba, hàm trả về 3. Công thức tại C5:

• Nhấn Enter.

• Kéo chuột để lấy giá trị các ô còn lại.

Hàm WEEKNUM

Trả về số tuần trong năm của một ngày cụ thể.

• Cú pháp chung

=WEEKNUM(serial_number,[return_type])

• Mô tả đối số

Ví dụ hàm WEEKNUM

Ngày tại B5 rơi vào tuần thứ 38 của năm. Công thức tại C5:

• Nhấn Enter.

• Kết quả trả về số tuần.

Hàm NETWORKDAYS

Tính số ngày làm việc giữa hai ngày, loại trừ cuối tuần và ngày lễ.

• Cú pháp chung

=NETWORKDAYS(start_date, end_date, [holidays])

• Mô tả đối số

Ví dụ hàm NETWORKDAYS

Tính ngày làm việc giữa B5 và C5. Công thức tại D5:

• Nhấn Enter.

• Kết quả: số ngày làm việc trừ cuối tuần.

Hàm DATE

Tạo ngày hợp lệ từ năm, tháng, ngày riêng lẻ.

• Cú pháp chung

=DATE(year, month, day)

• Mô tả đối số

Ví dụ hàm DATE

Ghép năm, tháng, ngày từ B5, C5, D5 tại ô E5:

• Nhấn Enter.

• Kết quả ngày hoàn chỉnh.

Hàm TODAY

Trả về serial number của ngày hiện tại. Không có đối số.

• Cú pháp chung

=TODAY()

Nhóm Hàm Văn Bản (11 Hàm)

Các hàm Text giúp xử lý chuỗi ký tự để trích xuất thông tin hoặc tạo báo cáo chuyên nghiệp.

Hàm FIND

Tìm vị trí bắt đầu của find_text trong within_text (phân biệt chữ hoa/thường).

• Cú pháp chung

=FIND(find_text, within_text, [start_num])

• Mô tả đối số

Ví dụ hàm FIND

Tìm chữ "a" trong "Adam" tại ô B5, trả về 3 (vị trí chữ 'a' thứ 2). Công thức tại C5:

• Nhấn Enter.

• Kết quả hiển thị vị trí.

Hàm SEARCH

Tương tự FIND nhưng không phân biệt chữ hoa/thường.

• Cú pháp chung

=SEARCH(find_text, within_text, [start_num])

• Mô tả đối số

Ví dụ hàm SEARCH

Tìm "a" trong "Adam" (không phân biệt hoa/thường) trả về 1. Công thức tại C5:

• Nhấn Enter.

• Kết quả hiển thị.

Hàm LEFT

Trích xuất số ký tự từ bên trái chuỗi.

• Cú pháp chung

=LEFT(text, num_chars)

• Mô tả đối số

Ví dụ hàm LEFT

Lấy 2 ký tự đầu của "Adam" tại B5 → "Ad". Công thức tại C5:

• Nhấn Enter.

• Kết quả mong muốn.

Hàm RIGHT

Trích xuất số ký tự từ bên phải chuỗi.

• Cú pháp chung

=RIGHT(text, num_chars)

• Mô tả đối số

Ví dụ hàm RIGHT

Lấy 2 ký tự cuối của "Adam" → "am". Công thức tại C5:

• Nhấn Enter.

• Kết quả phần chuỗi bên phải.

Hàm MID

Trích xuất ký tự từ vị trí bất kỳ trong chuỗi.

• Cú pháp chung

=MID(text, start_num, num_chars)

• Mô tả đối số

Ví dụ hàm MID

Lấy 3 ký tự từ vị trí 1 của "Adam" → "Ada". Công thức tại C5:

• Nhấn Enter.

• Kết quả như mong đợi.

Hàm LEN

Đếm tổng số ký tự trong chuỗi (kể cả khoảng trắng).

• Cú pháp chung

=LEN(text)

• Mô tả đối số

Ví dụ hàm LEN

"Adam" có 4 ký tự. Công thức tại C5:

• Nhấn Enter.

• Trả về độ dài chuỗi.

Hàm VALUE

Chuyển đổi văn bản dạng số thành giá trị số thực.

• Cú pháp chung

=VALUE(text)

• Mô tả đối số

Ví dụ hàm VALUE

Chuyển giá trị văn bản tại B5 thành số tại C5:

• Nhấn Enter.

• Kết quả giá trị số.

Hàm TEXT

Định dạng số thành văn bản theo mẫu tuỳ chỉnh.

• Cú pháp chung

=TEXT(value, format_text)

• Mô tả đối số

Ví dụ hàm TEXT

Định dạng ngày tại B5 thành "dd/mm/yyyy" tại C5:

• Nhấn Enter.

• Kết quả ngày theo định dạng mới.

Hàm TRIM

Xóa khoảng trắng thừa, chỉ giữ một khoảng trắng giữa các từ.

• Cú pháp chung

=TRIM(text)

• Mô tả đối số

Ví dụ hàm TRIM

Xóa khoảng trắng thừa tại B5 → kết quả tại C5:

• Nhấn Enter.

• Văn bản đã được làm sạch.

Hàm CLEAN

Loại bỏ các ký tự không in được khỏi văn bản.

• Cú pháp chung

=CLEAN(text)

• Mô tả đối số

Ví dụ hàm CLEAN

Tạo ký tự không in tại B5 bằng =CHAR(7)&"A", sau đó dùng CLEAN tại C5:

• Nhấn Enter.

• Ký tự không in đã bị xóa.

Hàm CONCATENATE (hoặc CONCAT/&)

Gộp nhiều chuỗi thành một chuỗi duy nhất.

• Cú pháp chung

=CONCATENATE(text1, [text2], …)

• Mô tả đối số

Ví dụ hàm CONCATENATE

Nối văn bản từ B5 và C5 tại D5:

• Nhấn Enter.

• Hai chuỗi đã được gộp.

Nhóm Hàm Logic (8 Hàm)

Các hàm Logic cho phép so sánh giá trị với điều kiện kỳ vọng.

Hàm TRUE / FALSE

Trả về giá trị logic TRUE hoặc FALSE. Không có đối số.

• Cú pháp: =TRUE() / =FALSE()

Hàm AND

Trả về TRUE nếu tất cả điều kiện đều đúng.

• Cú pháp: =AND(logical1, [logical2], ...)

Hàm OR

Trả về TRUE nếu ít nhất một điều kiện đúng.

• Cú pháp: =OR(logical1, [logical2], ...)

Hàm NOT

Đảo ngược giá trị logic của đối số.

• Cú pháp: =NOT(logical)

Hàm SWITCH

Đánh giá biểu thức so với danh sách giá trị, trả về kết quả tương ứng. Có giá trị mặc định nếu không khớp.

• Cú pháp chung

=SWITCH(expression, value1, result1, [default or value2, result2], …)

• Mô tả đối số

Ví dụ hàm SWITCH

Phân loại chức vụ theo tên tại B5. Công thức tại C5:

• Nhấn Enter.

• Kết quả: "Executive" cho tên Adam.

Hàm IF

Kiểm tra điều kiện, trả về giá trị này nếu TRUE, giá trị kia nếu FALSE.

• Cú pháp chung

=IF(logical_test, value_if_true, [value_if_false])

• Mô tả đối số

Hàm IFERROR

Trả về giá trị tùy chỉnh nếu công thức báo lỗi, ngược lại trả về kết quả công thức.

• Cú pháp chung

=IFERROR(value, value_if_error)

• Mô tả đối số

Ví dụ hàm IFERROR

Chia B5/B6 gây lỗi, trả về "No Result" tại D5:

• Nhấn Enter.

• Hiển thị thông báo thay vì lỗi.

Kết Hợp TRUE, FALSE, AND, IF

Kết hợp các hàm logic để tạo điều kiện phức tạp.

Ví dụ: Công thức tại D5:

• Nhấn Enter.

• Kết quả logic.

Kết Hợp TRUE, FALSE, OR, IF

Ví dụ: Công thức tại D5:

• Nhấn Enter.

• Kết quả đầu ra.

Kết Hợp TRUE, FALSE, NOT, IF

Ví dụ: Công thức tại D5:

• Nhấn Enter.

• Kết quả logic.

Nhóm Hàm Tra Cứu & Tham Chiếu (13 Hàm)

Dùng để truy xuất dữ liệu, mở rộng bảng, ánh xạ bản ghi.

Hàm CHOOSE

Chọn một trong 254 giá trị dựa trên index_num.

• Cú pháp chung

=CHOOSE(index_num, value1, [value2], …)

• Mô tả đối số

Ví dụ hàm CHOOSE

Số 1 tại C5 tương ứng "Manager". Công thức tại C5:

• Nhấn Enter.

• Kết quả: "Manager".

Hàm ROW / COLUMN

Trả về số hàng / cột của tham chiếu ô.

• Cú pháp: =ROW([reference]) / =COLUMN([reference])

Ví dụ ROW: =ROW(B5) → 5. Ví dụ COLUMN: =COLUMN(B5) → 2.

Hàm ROWS / COLUMNS

Trả về số hàng / cột trong một phạm vi.

• Cú pháp: =ROWS(array) / =COLUMNS(array)

Ví dụ ROWS: =ROWS(B5:B8) → 4. Ví dụ COLUMNS: =COLUMNS(B5:B8) → 1.

Hàm VLOOKUP

Tìm kiếm theo cột đầu tiên của bảng, trả về giá trị cột chỉ định.

• Cú pháp chung

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Mô tả đối số

Ví dụ VLOOKUP

Tìm "Adam" trong B5:C8, trả về cột 2 → 102. Công thức tại D5:

• Nhấn Enter.

• Kết quả VLOOKUP.

Hàm HLOOKUP

Tìm kiếm theo hàng đầu tiên của bảng, trả về giá trị hàng chỉ định.

• Cú pháp chung

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Mô tả đối số

Ví dụ HLOOKUP

Tìm "Adam" trong B5:C8, trả về hàng 2 → "Jhon". Công thức tại D5:

• Nhấn Enter.

• Kết quả HLOOKUP.

Hàm LOOKUP

Tìm kiếm trong một hàng/cột đơn, trả về giá trị cùng vị trí từ hàng/cột khác.

• Cú pháp chung

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• Mô tả đối số

Ví dụ LOOKUP

Tìm "Adam" trong B5:C8 → 102. Công thức tại D5:

• Nhấn Enter.

• Kết quả lookup.

Hàm MATCH

Trả về vị trí tương đối của giá trị trong mảng.

• Cú pháp chung

=MATCH(lookup_value, lookup_array, [match_type])

• Mô tả đối số

Ví dụ MATCH

"Adam" ở vị trí 1 trong B5:B8. Công thức:

• Nhấn Enter.

• Kết quả khớp.

Hàm INDEX

Trả về giá trị tại giao điểm hàng/cột trong mảng.

• Cú pháp chung

=INDEX(array, row_num, [column_num])

• Mô tả đối số

Ví dụ INDEX

Giá trị hàng 2 trong B5:B8 → "Jhon". Công thức tại D5:

• Nhấn Enter.

• Giá trị ô được chỉ mục.

Hàm OFFSET

Trả về tham chiếu ô/plage dịch chuyển từ ô gốc.

• Cú pháp chung

=OFFSET(reference, rows, cols, [height], [width])

• Mô tả đối số

Ví dụ OFFSET

Ô cách B5 2 hàng xuống, 1 cột phải → 101. Công thức tại D5:

• Nhấn Enter.

• Kết quả đầu ra.

Hàm INDIRECT

Trả về tham chiếu được chỉ định bởi chuỗi văn bản.

• Cú pháp chung

=INDIRECT(ref_text, [a1])

• Mô tả đối số

Ví dụ INDIRECT

D8 chứa "C8", C8 giá trị 103. Công thức tại E5:

• Nhấn Enter.

• Trả về giá trị ô C8 (103).

Hàm GETPIVOTDATA

Trích xuất dữ liệu từ báo cáo PivotTable.

• Cú pháp chung

=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

• Mô tả đối số

Ví dụ GETPIVOTDATA

Lấy doanh thu tháng January từ PivotTable tại $B$4. Công thức tại E5:

• Nhấn Enter.

• Kết quả: 450.

Nhóm Hàm Toán Học (15 Hàm)

Dù có đội ngũ phân tích chuyên sâu, chuyên viên tư vấn vẫn cần hàm toán học/thống kê hàng ngày.

Hàm RAND / RANDBETWEEN

RAND(): Số thực ngẫu nhiên [0,1). RANDBETWEEN(bottom, top): Số nguyên ngẫu nhiên trong khoảng.

• Ví dụ RAND: =RAND()*(C5-B5)+B5 → số ngẫu nhiên giữa B5 và C5.
• Ví dụ RANDBETWEEN: =RANDBETWEEN(B5,C5).

Hàm INT / ROUND / FLOOR / CEILING

INT(number): Làm tròn xuống số nguyên gần nhất.
ROUND(number, num_digits): Làm tròn đến số chữ số chỉ định.
FLOOR(number, significance): Làm tròn xuống bội số significance gần nhất.
CEILING(number, significance): Làm tròn lên bội số significance gần nhất.

Ví dụ với 2.05: INT→2, ROUND(,1)→2.1, FLOOR(,1)→2, CEILING(,1)→3.

Hàm SUM / SUMIF / SUMIFS

SUM(number1,[number2],…): Tổng các số.
SUMIF(range, criteria, [sum_range]): Tổng có điều kiện đơn.
SUMIFS(sum_range, criteria_range1, criteria1, …): Tổng nhiều điều kiện.

Ví dụ: =SUM(B5:B8), =SUMIF(B5:B8,">0"), =SUMIFS(B5:B8,B5:B8,">0").

Hàm PRODUCT / POWER / SQRT / MOD

PRODUCT: Tích các số. POWER(number, power): Lũy thừa. SQRT(number): Căn bậc hai. MOD(number, divisor): Phần dư chia.

Ví dụ: =PRODUCT(B5:B8), =POWER(B5,C5), =SQRT(B5), =MOD(B5,C5).

Hàm SUBTOTAL / SUMPRODUCT

SUBTOTAL(function_num, ref1, …): Tính toán phụ (1-11 bao gồm hàng ẩn, 101-111 loại trừ).
SUMPRODUCT(array1, [array2], …): Nhân tương ứng các mảng rồi cộng tổng.

Ví dụ: =SUBTOTAL(1,B5:B8) (trung bình), =SUMPRODUCT(B5:B8,C5:C8).

Nhóm Hàm Thống Kê (15 Hàm)

Hỗ trợ phân tích dữ liệu chuyên sâu cho chuyên viên tư vấn.

Hàm COUNT / COUNTA / COUNTIF / COUNTIFS

COUNT: Đếm ô chứa số. COUNTA: Đếm ô không rỗng. COUNTIF(range, criteria): Đếm điều kiện đơn. COUNTIFS(criteria_range1, criteria1, …): Đếm nhiều điều kiện.

Ví dụ: =COUNT(B5:B8), =COUNTA(B5:B8), =COUNTIF(B5:B8,">0"), =COUNTIFS(B5:B8,">10").

Hàm AVERAGE / AVERAGEIF / AVERAGEIFS

AVERAGE: Trung bình cộng. AVERAGEIF(range, criteria, [average_range]): TB điều kiện đơn. AVERAGEIFS(average_range, criteria_range1, criteria1, …): TB nhiều điều kiện.

Ví dụ: =AVERAGE(B5:B8), =AVERAGEIF(B5:B8,">0"), =AVERAGEIFS(B5:B8,B5:B8,">0").

Hàm MIN / MAX / MEDIAN

MIN: Giá trị nhỏ nhất. MAX: Giá trị lớn nhất. MEDIAN: Trung vị.

Ví dụ: =MIN(B5:B8), =MAX(B5:B8), =MEDIAN(B5:B8).

Hàm STDEV / VAR

STDEV: Độ lệch chuẩn mẫu. VAR: Phương sai mẫu.

Ví dụ: =STDEV(B5:B8), =VAR(B5:B8).

Hàm RANK / LARGE / SMALL

RANK(number, ref, [order]): Xếp hạng (0/omit = giảm dần, ≠0 = tăng dần).
LARGE(array, k): Giá trị lớn thứ k. SMALL(array, k): Giá trị nhỏ thứ k.

Ví dụ: =RANK(B5,$B$5:$B$8), =LARGE(B5:B8,2), =SMALL(B5:B8,2).

Top 7 Tính Năng Excel Nâng Cao

1. Named Ranges (Đặt Tên Phạm Vi)

Làm công thức dễ đọc, dễ hiểu, tự động cập nhật khi dữ liệu thay đổi.

Cách tạo:

1. Chọn phạm vi ô (ví dụ D5:D8).
2. Tab Formulas → nhóm Defined Names → Define Name.
3. Đặt tên trong hộp Name, kiểm tra Refers to, nhấn OK.

2. Conditional Formatting (Định Dạng Có Điều Kiện)

Tô màu, biểu tượng, thanh dữ liệu để trực quan hóa, tìm trùng, so sánh.

Tạo Dải Màu Xen Kẻ

Làm nổi bật hàng chẵn/lẻ.

1. Chọn vùng dữ liệu (B4:D8).
2. Tab Home → Conditional Formatting → New Rule.
3. Chọn Use a formula to determine which cells to format.
4. Nhập công thức: =EVEN(ROW())=ROW() (hàng chẵn) hoặc =ODD(ROW())=ROW() (hàng lẻ).
5. Nhấn Format → tab Fill → chọn màu → OK.

Tìm Giá Trị Trùng Lặp

1. Chọn vùng (B4:D10).
2. Home → Conditional Formatting → New Rule.
3. Chọn Format only unique or duplicate values.
4. Chọn duplicate trong Format all.
5. Format → Fill → chọn màu → OK.

3. Pivot Tables & Charts (Bảng & Biểu Đồ Pivot)

Phân tích nhanh, tổng hợp, cắt xén dữ liệu theo nhiều chiều.

Pivot Table

1. Tab Insert → PivotTable → From Table/Range.
2. Chọn dữ liệu, tích Existing Worksheet, đặt vị trí, OK.
3. Kéo thả trường vào khu vực Rows, Columns, Values.
4. Chuột phải giá trị → Summarize Values By → chọn Average, Count, …

Pivot Chart

1. Tab Insert → PivotChart → PivotChart.
2. Chọn dữ liệu, vị trí, OK.
3. Tùy chỉnh biểu đồ liên động với PivotTable.

4. Hàm RSQ (Hệ Số Quyết Định R²)

Đo lường mối tương quan tuyến tính giữa hai tập dữ liệu.

1. Ô F5: =RSQ(D5:D10,C5:C10) → Enter.
2. Chọn C5:D10 → Insert → Scatter Plot.
3. Chuột phải điểm dữ liệu → Add Trendline → chọn Linear → tích Display Equation on chart và Display R-squared value on chart.

5. Regression (Hồi Quy)

Dự đoán kết quả, tối ưu vận hành (ví dụ: dự báo bán hàng, mối quan hệ nhiệt độ lò - hạn sử dụng).

1. Tab Data → Data Analysis (cần kích hoạt Add-in Analysis ToolPak).
2. Chọn Regression → OK.
3. Điền Input Y Range (biến phụ thuộc, ví dụ D5:D10), Input X Range (biến độc lập, ví dụ C5:C10).
4. Chọn New Worksheet Ply, tích Residuals, Residual Plots, Line Fit Plots → OK.

6. Excel Solver (Bộ Giải Quyết)

Công cụ tối ưu hóa tìm giải pháp tốt nhất cho bài toán có ràng buộc (what-if analysis). Dùng để tối đa hóa lợi nhuận, tối thiểu hóa chi phí, phân bổ nguồn lực, v.v. Kích hoạt qua File → Options → Add-ins → Solver Add-in.

7. Lập Trình VBA (Visual Basic for Applications)

Tự động hóa tác vụ lặp lại: in hàng trăm file, tạo slide PowerPoint liên động dữ liệu Excel, thu thập dữ liệu web, xây dựng công cụ tùy chỉnh. Kỹ năng VBA nâng cao năng suất đáng kể cho chuyên viên tư vấn.

Kết Luận

Bài viết đã trình chi tiết 70 hàm và 7 tính năng Excel cốt lõi cho chuyên viên tư vấn quản trị. Thành thạo chúng giúp bạn quản lý, phân tích và trình bày dữ liệu chuyên nghiệp, chính xác, tiết kiệm thời gian – yếu tố then chốt tạo giá trị cho khách hàng.