Bạn thường xuyên phải đối mặt với các bảng tính Excel khổng lồ, xử lý vô số dữ liệu và cảm thấy như phải có một cách hiệu quả hơn để hoàn thành công việc? Chắc chắn là có. Qua nhiều năm làm việc với Excel, tôi đã khám phá ra một tập hợp các hàm đã thực sự thay đổi cách tôi xử lý công việc. Đây không phải là những công thức cơ bản như SUM hay AVERAGE; chúng là những viên ngọc ẩn có thể giúp bạn tiết kiệm hàng giờ làm việc tẻ nhạt.
Tôi muốn chia sẻ sáu hàm Excel thiết yếu mà tôi ước gì mình đã biết sớm hơn. Tôi tin rằng chúng cũng sẽ tạo ra sự khác biệt lớn trong công việc của bạn. Những thủ thuật Excel này không chỉ giúp bạn tối ưu hóa công việc mà còn nâng cao kỹ năng xử lý dữ liệu, biến bạn thành một chuyên gia Excel thực thụ.
Hình ảnh minh họa các biểu tượng và công thức Excel, tượng trưng cho việc tối ưu và tăng tốc xử lý dữ liệu trong Excel
1. TEXTBEFORE, TEXTAFTER, và TEXTSPLIT: Thao tác văn bản chuyên nghiệp
Tháng 5 năm 2022, Microsoft đã giới thiệu một số hàm mới giúp bạn thao tác với văn bản một cách chuyên nghiệp. Mặc dù đã có các hàm như SEARCH, FIND, LEFT, RIGHT, MID và SEQUENCE, nhưng các hàm tương đối mới này cho phép bạn trả về mọi thứ trước hoặc sau một dấu phân cách được chỉ định. Chúng hiệu quả hơn trong nhiều tình huống.
Giả sử bạn có một danh sách địa chỉ khách hàng trong một cột duy nhất, được định dạng như “123 Main St., Anytown, CA 90210”. Bây giờ, bạn muốn trích xuất địa chỉ đường, thành phố, tiểu bang và mã ZIP vào các cột riêng biệt. Bạn có thể sử dụng =TEXTBEFORE(A1, ","), và nó sẽ yêu cầu Excel tìm trong ô A1 và trả về mọi thứ trước dấu phẩy đầu tiên (123 Main St.). Tương tự, TEXTAFTER trích xuất văn bản xuất hiện sau một dấu phân cách cụ thể.
Bạn thậm chí có thể sử dụng kết hợp TEXTBEFORE và TEXTAFTER. Chẳng hạn, =TEXTBEFORE(TEXTAFTER(A1, ","), ",") sẽ trả về Anytown. TEXTSPLIT chia văn bản thành nhiều cột hoặc hàng bằng cách sử dụng dấu phân cách. Nếu bạn nhập =TEXTSPLIT(A1, ", "), nó sẽ chia ô A1 thành ba cột riêng biệt.
Nhìn chung, các hàm TEXTBEFORE TEXTAFTER TEXTSPLIT Excel này cực kỳ giá trị để làm sạch dữ liệu lộn xộn được nhập từ các nguồn khác. Bạn có thể nhanh chóng tái cấu trúc dữ liệu cho các báo cáo và phân tích, giúp quản lý dữ liệu Excel hiệu quả hơn.
2. EDATE và EOMONTH: Làm chủ các phép tính ngày tháng trong Excel
EDATE và EOMONTH là hai công cụ mạnh mẽ để điều chỉnh ngày tháng trong Excel. EDATE trả về ngày là một số tháng cụ thể trước hoặc sau một ngày bắt đầu đã cho. Giả sử bạn có danh sách ngày bắt đầu đăng ký trong cột A và bạn muốn tính ngày hết hạn, là 12 tháng sau đó.
Bạn có thể nhập =EDATE(A1, 12) và kéo công thức xuống để áp dụng cho tất cả các ô. Hàm này cũng khá tiện lợi khi bạn muốn tính toán các thời hạn trong quá trình quản lý dự án Excel.
Microsoft Excel lưu trữ ngày tháng dưới dạng các số sê-ri tuần tự để chúng có thể được sử dụng trong các phép tính. Mặc định, ngày 1 tháng 1 năm 1900 là số sê-ri 1, và ngày 1 tháng 1 năm 2026 là số sê-ri 46023 vì nó là 46.023 ngày sau ngày 1 tháng 1 năm 1900.
Ví dụ sử dụng hàm EDATE trong Excel để tính toán ngày tháng
EOMONTH trả về ngày cuối cùng của tháng, trước hoặc sau một số tháng cụ thể từ ngày bắt đầu. Ví dụ, nếu bạn muốn tính ngày kết thúc hợp đồng, bạn có thể nhập =EOMONTH(A1, 11) và nó sẽ trả về ngày cuối cùng của tháng, 11 tháng sau ngày bắt đầu trong ô A1. Các hàm EDATE EOMONTH Excel là công cụ đắc lực cho mọi tác vụ liên quan đến thời gian.
3. VSTACK và HSTACK: Kết hợp các bảng và cột một cách dễ dàng
VSTACK và HSTACK kết hợp nhiều mảng hoặc phạm vi theo chiều dọc và chiều ngang thành một mảng duy nhất. Giả sử bạn có dữ liệu bán hàng cho hai khu vực khác nhau trong các bảng riêng biệt. Bạn có thể kết hợp hai bảng này thành một danh sách dọc duy nhất bằng cách sử dụng hàm VSTACK.
Tương tự, bạn có thể kết hợp hai cột thành một bảng bằng cách sử dụng hàm HSTACK. Nếu bạn có tên khách hàng trong một cột và tên công ty của họ trong một cột khác, hãy sử dụng =HSTACK(A1:A4, C1:C4) để kết hợp chúng thành một bảng duy nhất. Các hàm VSTACK HSTACK Excel này giúp bạn tổ chức và tổng hợp dữ liệu cực kỳ nhanh chóng.
Minh họa cách sử dụng hàm HSTACK trong Excel để kết hợp các cột dữ liệu
Mẹo tạo bảng tính Excel chuyên nghiệp và hiệu quả với các công thức được tối ưu
4. XLOOKUP: Bước tiến vượt trội so với VLOOKUP
XLOOKUP có thể dễ dàng thay thế VLOOKUP và INDEX/MATCH cho bạn. Không giống như VLOOKUP, XLOOKUP có thể tìm kiếm giá trị trong bất kỳ cột nào, từ trái sang phải hoặc từ phải sang trái, và cung cấp nhiều quyền kiểm soát hơn đối với các loại khớp. Giả sử bạn có một bảng với ba cột: ID Sản phẩm (A), Tên Sản phẩm (B) và Giá (C).
Giả sử bạn muốn tìm giá của sản phẩm có ID 1003. Bạn có thể thêm =XLOOKUP(E1, A2:A6, C2:C6) để hiển thị giá chính xác. XLOOKUP đặc biệt hữu ích trong quản lý kho hàng, phân tích bán hàng, cơ sở dữ liệu nhân sự, hoặc trong bất kỳ tình huống nào bạn cần tra cứu dữ liệu dựa trên một giá trị khóa. Hàm XLOOKUP Excel thực sự là một sự thay thế VLOOKUP đáng giá.
Cách áp dụng hàm XLOOKUP trong Excel để tìm kiếm dữ liệu nâng cao
5. OFFSET: Tạo vùng dữ liệu động linh hoạt
Hàm OFFSET cho phép bạn tạo các dải ô động trong Excel. Nó trả về một dải ô là một số hàng và cột cụ thể từ một ô hoặc dải ô bắt đầu. Giả sử bạn có danh sách số liệu bán hàng hàng ngày trong cột A (A1:A30) và bạn muốn tính tổng doanh số bán hàng của 7 ngày gần nhất.
Thay vì sử dụng SUM tĩnh, bạn có thể chạy =SUM(OFFSET(A30, -6, 0, 7, 1)) và tính tổng của trang được trả về. Khi bạn thêm nhiều dữ liệu bán hàng vào cột A, hàm OFFSET (không giống như SUM) sẽ tự động điều chỉnh phạm vi, và tổng trong ô B1 sẽ cập nhật. Hàm OFFSET Excel này mang lại sự linh hoạt đáng kinh ngạc cho các phân tích dữ liệu trong Excel.
Minh họa việc sử dụng hàm OFFSET trong Excel để tạo dải ô động
6. AGGREGATE: Trợ thủ đắc lực cho người dùng chuyên nghiệp
AGGREGATE là một hàm đa năng mà tôi ước gì đã biết sớm hơn. Nó thực hiện các phép tính như AVERAGE, COUNT, MAX, MIN, SUM và nhiều hơn nữa, đồng thời cung cấp các tùy chọn để bỏ qua các loại giá trị cụ thể.
Giả sử bạn có danh sách số liệu bán hàng trong cột A, nhưng một số ô chứa lỗi như #DIV/0!. Bây giờ, bạn muốn tính doanh số bán hàng trung bình trong khi bỏ qua các lỗi này. Bạn có thể chạy công thức =AGGREGATE(1, 6, A1:A10), trong đó 1 chỉ định hàm AVERAGE, 6 chỉ định rằng các lỗi nên được bỏ qua và A1:A10 là phạm vi số liệu bán hàng. Bạn có thể tìm hiểu thêm về số hàm và hành vi tùy chọn từ trang chính thức của Microsoft. Hàm AGGREGATE Excel là một công cụ tối ưu cho việc xử lý dữ liệu Excel sạch sẽ và chính xác.
Ví dụ về hàm AGGREGATE trong Excel giúp tính toán tổng hợp và bỏ qua lỗi
Vượt xa SUM và AVERAGE
Giờ đây bạn đã biết về các hàm Excel thiết yếu này, hãy bắt đầu áp dụng chúng vào thực tế. Bắt đầu áp dụng chúng vào các bảng tính của riêng bạn và xem sự khác biệt mà chúng tạo ra. Cho dù bạn đang phân tích dữ liệu, tự động hóa báo cáo, hay chỉ đơn giản là tổ chức thông tin, những công cụ này sẽ giúp bạn tiết kiệm thời gian và tăng hiệu suất làm việc đáng kể.
Nếu bạn đang tìm cách mở khóa các cấp độ hiệu quả mới, hãy sử dụng các thủ thuật tích hợp sẵn để tự động hóa bảng tính của bạn. Hãy xem hướng dẫn riêng của chúng tôi để tìm hiểu thêm về tự động hóa Excel. Nâng cao kỹ năng Excel cho người dùng chuyên nghiệp ngay hôm nay!