Bạn có đang cảm thấy tốn quá nhiều thời gian khi vật lộn với các công thức Excel phức tạp, lọc dữ liệu thủ công hay xử lý văn bản hàng loạt? Nếu câu trả lời là có, đã đến lúc bạn cần trang bị cho mình những hàm và phím tắt Excel thiết yếu để tối ưu hóa công việc. Từ các tác vụ tra cứu dữ liệu nâng cao, lọc thông tin động, đến thao tác văn bản siêu tốc hay tính toán tài chính nhanh chóng, chúng tôi đã chọn lọc những hàm Excel mạnh mẽ và các phím tắt vàng giúp bạn hoàn thành công việc trên bảng tính chỉ trong tích tắc. Đây không chỉ là những công cụ giúp bạn giải quyết vấn đề mà còn là chìa khóa để “mở khóa” năng suất làm việc tối đa, biến Excel từ một “gánh nặng” thành trợ thủ đắc lực.
7. XLOOKUP – Kẻ thay thế hoàn hảo cho VLOOKUP và INDEX/MATCH
Đã đến lúc tạm biệt VLOOKUP?
XLOOKUP là một “cú hích” lớn trong Excel, đã gần như thay thế hoàn toàn các hàm VLOOKUP hay INDEX/MATCH trong hầu hết các tình huống. Ưu điểm nổi bật của XLOOKUP là bạn không cần phải đếm số cột như khi dùng VLOOKUP; thay vào đó, bạn chỉ cần chỉ định trực tiếp các mảng tra cứu (lookup array) và mảng trả về (return array). Hơn nữa, XLOOKUP có khả năng tra cứu giá trị sang bên trái của cột tra cứu – một điều mà VLOOKUP không thể làm được. Điều này giúp loại bỏ nhiều hạn chế và làm cho công thức trở nên linh hoạt hơn đáng kể.
Hãy tưởng tượng bạn đang quản lý cơ sở dữ liệu bán hàng cho một cửa hàng trực tuyến với hai bảng: “Chi tiết sản phẩm” (gồm Mã sản phẩm, Tên sản phẩm, Danh mục, và Giá) và “Dữ liệu bán hàng” (gồm Mã đơn hàng, Mã sản phẩm, và Số lượng đã bán). Bây giờ, bạn muốn thêm thông tin Tên sản phẩm và Giá vào bảng “Dữ liệu bán hàng”. Bạn chỉ cần tạo thêm hai cột cho Tên sản phẩm và Giá trong bảng “Dữ liệu bán hàng” và sử dụng hàm XLOOKUP để tìm Mã sản phẩm trong mảng tra cứu, từ đó trả về Tên sản phẩm và giá tương ứng cho mỗi sản phẩm đã bán.
Sử dụng hàm XLOOKUP trong Excel để tra cứu dữ liệu hiệu quả
6. PMT – Thần tốc tính toán khoản vay
Tính toán các khoản vay chỉ trong chớp mắt
Hàm PMT được thiết kế để tính toán khoản thanh toán định kỳ cho một khoản vay (giả sử các khoản thanh toán và lãi suất là không đổi). Đây là một công cụ cơ bản và không thể thiếu cho các phép tính tài chính trong Excel. Bất cứ khi nào bạn cần tạo bảng khấu hao khoản vay, lập kế hoạch đầu tư, thực hiện phân tích tài chính, hay tính toán chi phí thuê mua, PMT sẽ giúp bạn hoàn thành công việc một cách nhanh chóng và chính xác. Hàm này đặc biệt hữu ích cho những ai thường xuyên làm việc với các nghiệp vụ liên quan đến tài chính cá nhân hay doanh nghiệp.
Giả sử bạn vay 200.000 USD để mua nhà với khoản thế chấp 30 năm và lãi suất hàng năm là 5%. Bạn có thể sử dụng công thức dưới đây để tính toán các khoản thanh toán hàng tháng của mình:
=PMT(0.05/12, 30*12, 200000)
Giao diện hàm PMT trong Excel giúp tính toán khoản vay hàng tháng
5. FILTER – Lọc dữ liệu tức thì
Lọc dữ liệu của bạn chỉ trong nháy mắt
Hàm FILTER là một công cụ mạnh mẽ dành cho việc báo cáo và phân tích dữ liệu trong Excel. Nó thường dễ sử dụng hơn nhiều so với việc kết hợp phức tạp các hàm INDEX, MATCH và IF. Hàm FILTER có thể xử lý các logic lọc phức tạp với nhiều điều kiện, và cú pháp của nó tương đối dễ đọc và dễ hiểu. Điều này giúp người dùng tiết kiệm thời gian đáng kể, đặc biệt khi cần trích xuất thông tin cụ thể từ một tập dữ liệu lớn.
Giả sử bạn là một quản lý dự án đang sử dụng Excel để theo dõi các nhiệm vụ của nhóm mình. Bạn đã tạo một cơ sở dữ liệu với các cột như Nhiệm vụ, Người phụ trách, Tình trạng, và Ngày đến hạn. Bây giờ, bạn muốn kiểm tra xem Bob đang làm những nhiệm vụ nào.
=FILTER(A2:D7, B2:B7="Bob")Excel sẽ hiển thị một bảng chỉ chứa các hàng mà cột “Người phụ trách” là Bob. Tương tự, bạn có thể lọc các nhiệm vụ “Đang tiến hành”, “Đã hoàn thành”, và thậm chí hiển thị một thông báo khi không có kết quả nào.
Minh họa cách dùng hàm FILTER trong Excel để lọc dữ liệu theo điều kiện
4. TEXTBEFORE và TEXTAFTER – Trích xuất thông tin giá trị
Trích xuất văn bản và thông tin có giá trị
Hai hàm TEXTBEFORE và TEXTAFTER cho phép bạn trích xuất văn bản xuất hiện trước hoặc sau một ký tự phân tách cụ thể. Bạn có thể sử dụng chúng để tách họ, tên, hay tên miền từ một địa chỉ email, và thậm chí cả tên tệp mà không có phần mở rộng. Sự linh hoạt này giúp bạn xử lý dữ liệu dạng văn bản một cách nhanh chóng và chính xác, loại bỏ nhu cầu sử dụng các công thức phức tạp hoặc các công cụ phân tích văn bản thủ công.
Nếu bạn có một ô chứa tên “Nguyễn Văn A” và bạn muốn trích xuất “Nguyễn”. Bạn có thể chạy công thức =TEXTBEFORE(A1, ” “), và nó sẽ trả về “Nguyễn”. Tương tự, bạn có thể chạy =TEXTAFTER(A1, ” “) để trích xuất “Văn A” hoặc sử dụng kết hợp để lấy tên cuối.
Ví dụ ứng dụng hàm TEXTAFTER và TEXTBEFORE để trích xuất văn bản trong Excel
3. SEQUENCE – Khởi tạo bảng tính nhanh chóng
Bắt đầu bảng tính của bạn thật nhanh
Đây là một hàm Excel mà nhiều người ước rằng mình đã biết sớm hơn. Hàm SEQUENCE tạo ra một chuỗi số trong một mảng. Điều này cực kỳ tiện lợi để tạo các danh sách động, sinh ngày tháng, và tự động hóa các tác vụ lặp đi lặp lại. Ví dụ, nếu bạn muốn tạo các hàng với số từ 1 đến 30, bạn chỉ cần gõ =SEQUENCE(30) để hoàn thành công việc. Bạn cũng có thể gõ =SEQUENCE(5, 3) để tạo một bảng số có 5 hàng và 3 cột. Khả năng ứng dụng của hàm này là vô tận, giúp bạn tiết kiệm thời gian đáng kể trong các công việc lặp lại.
Cách dùng hàm SEQUENCE trong Excel để tạo dãy số tự động
2. UNIQUE – Trích xuất các giá trị duy nhất
Trích xuất các giá trị không trùng lặp
Bạn có thể sử dụng hàm UNIQUE để trích xuất một danh sách các giá trị duy nhất từ một phạm vi hoặc mảng. Hàm này rất hữu ích khi bạn muốn loại bỏ các bản sao và tạo một danh sách riêng biệt. Giả sử bạn có một bảng tính Excel khổng lồ với hàng trăm tên khách hàng trong một cột. Bây giờ bạn muốn trích xuất những khách hàng duy nhất từ danh sách đó để phục vụ cho các báo cáo hoặc phân tích.
Bạn có thể chọn một ô trống (ví dụ B1) nơi bạn muốn danh sách duy nhất xuất hiện. Bây giờ, hãy sử dụng công thức =UNIQUE(A1:A100), và Excel sẽ tự động liệt kê các tên khách hàng duy nhất vào các ô bên dưới B1.
Minh họa hàm UNIQUE để lọc các giá trị không trùng lặp trong bảng tính Excel
1. SWITCH – Áp dụng logic liên quan
Áp dụng logic phù hợp
SWITCH là một hàm logic mạnh mẽ cho phép bạn đánh giá một giá trị duy nhất so với một danh sách các giá trị và trả về kết quả tương ứng. Hàm này giúp đơn giản hóa các công thức phức tạp, đặc biệt khi bạn có nhiều điều kiện IF lồng nhau. Thay vì viết chuỗi IF-ELSE IF dài dòng, SWITCH cung cấp một cách tiếp cận gọn gàng và dễ đọc hơn để ánh xạ các giá trị đầu vào với các kết quả đầu ra cụ thể.
Giả sử ô A1 chứa một số tháng (từ 1 đến 12). Bạn có thể sử dụng công thức dưới đây để chuyển đổi nó thành tên tháng tương ứng:
=SWITCH(A1, 1, "Tháng 1", 2, "Tháng 2", 3, "Tháng 3", 4, "Tháng 4", 5, "Tháng 5", 6, "Tháng 6", 7, "Tháng 7", 8, "Tháng 8", 9, "Tháng 9", 10, "Tháng 10", 11, "Tháng 11", 12, "Tháng 12", "Tháng không hợp lệ")Tương tự, bạn có thể gán ngày trong tuần (chuyển đổi số từ 1-7 thành tên ngày) và thậm chí chuyển mã sản phẩm thành danh mục sử dụng hàm SWITCH.
Các phím tắt Excel yêu thích giúp tiết kiệm thời gian
Ngoài các hàm mạnh mẽ, việc làm chủ các phím tắt bàn phím sẽ giúp bạn di chuyển và thao tác trên bảng tính nhanh như chớp. Dưới đây là một số phím tắt Excel được nhiều chuyên gia sử dụng thường xuyên nhất:
- Ctrl + T: Biến vùng ô được chọn thành một bảng Excel. Điều này giúp quản lý dữ liệu hiệu quả hơn, tự động định dạng và dễ dàng áp dụng các tính năng bảng.
- Ctrl + 1: Mở hộp thoại “Format Cells” (Định dạng ô). Đây là cửa sổ quyền lực để tùy chỉnh mọi thứ từ số liệu, căn chỉnh, phông chữ, viền, đến tô màu ô.
- F4: Lặp lại hành động cuối cùng. Cực kỳ hữu ích khi bạn cần thực hiện cùng một thao tác (ví dụ: định dạng, chèn hàng/cột, áp dụng công thức) nhiều lần.
- Shift + Space: Chọn toàn bộ hàng nơi ô hiện tại của bạn đang nằm.
- Ctrl + Space: Chọn toàn bộ cột nơi ô hiện tại của bạn đang nằm.
- Ctrl + ;: Thêm ngày hiện tại vào ô. Một phím tắt nhanh gọn cho việc ghi lại thời gian.
- Alt + =: Thực hiện AutoSum. Tự động thêm công thức SUM cho phạm vi số liền kề.
Khai phá hiệu suất tối ưu trong Excel
Chúng tôi hiểu rằng việc lãng phí thời gian vào các tác vụ Excel tẻ nhạt có thể gây ra nhiều phiền toái. Việc khám phá và áp dụng các hàm cùng phím tắt này đã thay đổi hoàn toàn quy trình làm việc của chúng tôi, giúp chúng tôi tiết kiệm được hàng giờ mỗi tuần. Giờ đây, chúng tôi hy vọng bạn cũng sẽ trải nghiệm được sự thay đổi tương tự. Đừng chần chừ nữa! Hãy thực hành, áp dụng chúng vào các công việc thực tế hàng ngày của bạn và chứng kiến năng suất của bạn tăng vọt. Ngoài những hàm và phím tắt này, bạn có thể khám phá thêm các công cụ mạnh mẽ khác như Python trong Excel để nâng cao hơn nữa khả năng xử lý dữ liệu của mình.
