Bạn có thường xuyên phải đối mặt với những bảng tính Excel dài vô tận, xử lý lượng dữ liệu khổng lồ và cảm thấy như có một cách tốt 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 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ờ lao động nhàm chán.
Tôi muốn chia sẻ sáu hàm Excel thiết yếu mà tôi ước mình đã học được sớm hơn rất nhiều. Tôi tin tưởng 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.
Mẹo tối ưu công thức Excel hiệu quả
6. TEXTBEFORE, TEXTAFTER và TEXTSPLIT
Xử lý văn bản như một chuyên gia
Vào tháng 5 năm 2022, Microsoft đã giới thiệu một số hàm mạnh mẽ để thao tác với văn bản. Mặc dù đã có các hàm như SEARCH, FIND, LEFT, RIGHT, MID và SEQUENCE, nhưng những hàm mới (tương đối) này cho phép bạn trích xuất mọi thứ trước hoặc sau một ký tự phân tách (delimiter) cụ thể. Chúng hiệu quả hơn đáng kể trong nhiều tình huống.
Giả sử bạn có danh sách địa chỉ khách hàng trong một cột duy nhất, được định dạng như trong hình dưới đây. 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ể nhập =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 ký tự phân tách được chỉ định.
Bạn thậm chí có thể sử dụng sự kết hợp của TEXTBEFORE và TEXTAFTER. Ví dụ, =TEXTBEFORE(TEXTAFTER(A1, “,”), “,”) sẽ trả về Anytown. TEXTSPLIT tách văn bản thành nhiều cột hoặc hàng bằng cách sử dụng một ký tự phân tách. Nếu bạn nhập =TEXTSPLIT(A1, “, “), nó sẽ tách ô A1 thành ba cột riêng biệt. Nhìn chung, các hàm này là vô giá để 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 báo cáo và phân tích.
5. EDATE và EOMONTH
Thao tác ngày tháng trong Excel
Ví dụ sử dụng hàm EDATE trong Excel để tính ngày
EDATE và EOMONTH là hai công cụ mạnh mẽ để tinh chỉnh các ngày 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 nhất định. Giả sử bạn có danh sách ngày bắt đầu đăng ký ở 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 thời hạn trong quá trình quản lý dự án.
Microsoft Excel lưu trữ các ngày dưới dạng số sê-ri liên tiếp để có thể sử dụng chú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.
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ừ mộ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.
4. VSTACK và HSTACK
Ghép nối các bảng từ nhiều cột
Minh họa cách dùng hàm HSTACK để ghép cột trong Excel
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.
3. XLOOKUP
Bước tiến vượt bậc so với VLOOKUP
Sử dụng hàm XLOOKUP trong Excel để tra cứu dữ liệu
XLOOKUP có thể dễ dàng thay thế VLOOKUP và INDEX/MATCH. 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 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 đối sánh. 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 rất 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.
2. OFFSET
Quên đi hàm SUM đơn thuần
Ví dụ về hàm OFFSET để tạo vùng tham chiếu động trong Excel
Hàm OFFSET cho phép bạn tạo các vùng dữ liệu độ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ụ thể từ một ô hoặc phạm vi bắt đầu. Giả sử bạn có danh sách số liệu bán hàng hàng ngày ở 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ể dùng =SUM(OFFSET(A30, -6, 0, 7, 1)) để tính tổng phạm vi đượ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ẽ tự động cập nhật.
1. AGGREGATE
Tăng cường năng suất cho người dùng chuyên sâu
Cách dùng hàm AGGREGATE để tính toán bỏ qua lỗi trong Excel
AGGREGATE là một hàm đa năng mà tôi ước mình đã 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 ở 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ề số hàm và hành vi tùy chọn từ trang chính thức của Microsoft.
Vượt xa SUM và AVERAGE
Giờ đây bạn đã tìm hiểu về những hàm Excel hữu ích và thiết yếu này, hãy đưa chúng vào thực hành. 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. Cho 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ả làm 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 quên truy cập congnghe360.net để khám phá thêm nhiều bài viết hữu ích về tối ưu hóa và tự động hóa Excel nhé!