Bạn có thường xuyên nhìn chằm chằm vào một bảng tính, đối mặt với vô số dữ liệu và cảm thấy rằng 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ó. Trong nhiều năm làm việc với Excel, tôi đã khám phá ra một bộ sưu tập các hàm đã thay đổi hoàn toàn quy trình làm việc của mình. Đây không phải là những công thức SUM và AVERAGE cơ bản mà bạn vẫn thường dùng; đây 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 nâng cao mà tôi ước gì đã học được sớm hơn rất nhiều. Tôi tin tưởng rằng chúng sẽ tạo ra sự khác biệt lớn trong công việc của bạn, giúp bạn tối ưu hóa quy trình làm việc và tăng năng suất đáng kể khi xử lý dữ liệu phức tạp.
1. TEXTBEFORE, TEXTAFTER và TEXTSPLIT – Chuyên gia xử lý văn bản
Thao tác văn bản chuyên nghiệp
Vào tháng 5 năm 2022, Microsoft đã giới thiệu một số hàm mạnh mẽ để bạn có thể thao tác văn bản một cách chuyên nghiệp. Mặc dù đã có sẵn các hàm như SEARCH, FIND, LEFT, RIGHT, MID và SEQUENCE, nhưng những hàm 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 đã chọn một cách hiệu quả hơn trong nhiều tình huống. Chúng là công cụ không thể thiếu để làm sạch dữ liệu lộn xộn thường được nhập từ các nguồn khác, giúp bạn nhanh chóng tái cấu trúc dữ liệu cho các báo cáo và phân tích.
Thủ thuật Excel để tối ưu hóa và tăng tốc công thức
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à Excel sẽ tìm trong ô A1 và trả về mọi thứ trước dấu phẩy đầu tiên (ví dụ: “123 Main St.”). Tương tự, TEXTAFTER sẽ trích xuất văn bản xuất hiện sau một dấu phân cách được chỉ định.
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”. Hàm TEXTSPLIT sẽ chia văn bản thành nhiều cột hoặc hàng bằng cách sử dụng một 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 một cách dễ dàng.
2. EDATE và EOMONTH – Thao tác ngày tháng hiệu quả
EDATE và EOMONTH là hai công cụ mạnh mẽ để điều chỉnh ngày tháng trong Excel. Hàm EDATE trả về ngày, là một số tháng được chỉ định trước hoặc sau một ngày bắt đầu nhất định. Chẳng hạn, bạn có một danh sách ngày bắt đầu đăng ký trong cột A và muốn tính ngày hết hạn, tức 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 dụng 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 lưu trữ ngày tháng dưới dạng số sê-ri tuần tự để chúng có thể được sử dụng trong các phép tính. Theo 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ụ hàm EDATE trong Excel để tính ngày hết hạn
Hàm EOMONTH trả về ngày cuối cùng của tháng, trước hoặc sau một số tháng được chỉ định kể 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.
3. VSTACK và HSTACK – Kết hợp bảng dữ liệu linh hoạt
VSTACK và HSTACK kết hợp nhiều mảng hoặc phạm vi dữ liệu 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.
Ví dụ hàm HSTACK trong Excel để ghép các cột
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 vào một bảng duy nhất. Những hàm này đặc biệt hữu ích khi bạn cần tổng hợp dữ liệu từ nhiều nguồn hoặc cấu trúc lại bảng tính để phân tích.
4. XLOOKUP – Bước tiến vượt trội so với VLOOKUP
Hàm XLOOKUP có thể dễ dàng thay thế VLOOKUP và cặp INDEX/MATCH cho bạn. Không giống như VLOOKUP, XLOOKUP có thể tra cứu các 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 (match types).
Hàm XLOOKUP trong Excel thay thế VLOOKUP
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). 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ực kỳ hữu ích trong quản lý kho, 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. Khả năng tùy chỉnh linh hoạt của nó giúp bạn giải quyết các tác vụ phức tạp hơn mà VLOOKUP không thể đáp ứng.
5. OFFSET – Tạo phạm vi động, vượt xa hàm SUM
Hàm OFFSET cho phép bạn tạo các phạm vi động trong Excel. Nó trả về một phạm vi ô được dịch chuyển một số hàng và cột được chỉ định từ một ô hoặc phạm vi bắt đầu. Giả sử bạn có một 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ố của 7 ngày gần nhất.
Hàm OFFSET trong Excel để tạo phạm vi động
Thay vì sử dụng SUM thông thường, bạn có thể chạy =SUM(OFFSET(A30, -6, 0, 7, 1)) để tính tổng của phạm vi 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 tĩnh) sẽ tự động điều chỉnh phạm vi, và tổng trong ô B1 sẽ tự động cập nhật. Đây là một công cụ mạnh mẽ cho các báo cáo và biểu đồ động, nơi dữ liệu thường xuyên thay đổi.
6. AGGREGATE – Tăng cường năng suất 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 phép tính khác, đồng thời cung cấp các tùy chọn để bỏ qua các loại giá trị cụ thể.
Hàm AGGREGATE trong Excel bỏ qua lỗi khi tính toán
Giả sử bạn có một danh sách các 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ố 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ề các mã hàm và hành vi tùy chọn từ trang chính thức của Microsoft để tận dụng tối đa sức mạnh của hàm này. AGGREGATE đặc biệt hữu ích trong việc phân tích dữ liệu “bẩn” mà không cần phải làm sạch thủ công trước.
Vượt xa SUM và AVERAGE
Giờ đây bạn đã tìm hiểu về những hàm Excel hữu ích này, hãy đưa 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 mang lại. Dù bạn đang phân tích dữ liệu, tự động hóa báo cáo, hay đơn giản là sắp xếp 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 cường hiệu quả công việc.
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 sổ làm việc của mình. Đừng bỏ lỡ hướng dẫn riêng của chúng tôi để tìm hiểu thêm về cách tự động hóa Excel.