Bạn có thường xuyên phải đối mặt với những bảng tính khổng lồ, xử lý vô số dữ liệu và tự hỏi liệu có cách nào tốt hơn để hoàn thành công việc không? Tin vui là có. Qua 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 giúp biến đổ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 hay AVERAGE cơ bản; 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.
Trong bài viết này, 123thuthuat.com sẽ chia sẻ sáu hàm Excel thiết yếu mà tôi ước mình đã biết 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. Hãy cùng tìm hiểu những “thủ thuật Excel” này để nâng cao năng suất và làm việc hiệu quả hơn!
1. TEXTBEFORE, TEXTAFTER và TEXTSPLIT: Xử lý chuỗi văn bản như chuyên gia
Khai phá sức mạnh xử lý văn bản
Vào tháng 5 năm 2022, Microsoft đã giới thiệu một số hàm mới để bạn có thể thao tác với văn bản một cách chuyên nghiệp hơn. Mặc dù đã có các hàm như SEARCH, FIND, LEFT, RIGHT, MID và SEQUENCE, nhưng các hàm mới (tương đố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. Chúng hiệu quả hơn trong nhiều tình huống khác nhau.
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ư “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 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”. Hàm 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 này vô cùng hữu ích để 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.
2. EDATE và EOMONTH: Thao tác ngày tháng hiệu quả
Quản lý thời gian trên bảng tính
Ví dụ sử dụng hàm EDATE trong Excel để tính ngày hết hạn hợp đồng
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 được cung cấp. 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 dụng khi bạn muốn tính toán thời hạn trong quản lý dự án.
Microsoft Excel lưu trữ ngày 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.
EMONTH 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.
3. VSTACK và HSTACK: Ghép nối dữ liệu từ nhiều nguồn
Kết hợp bảng tính linh hoạt
Minh họa cách dùng hàm HSTACK trong Excel để kết hợp dữ liệu từ nhiều cột
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 vào 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 này cực kỳ hữu ích khi bạn cần tổng hợp dữ liệu từ nhiều nguồn hoặc sắp xếp lại cấu trúc bảng tính một cách nhanh chóng.
4. XLOOKUP: Nâng tầm tra cứu dữ liệu, vượt trội VLOOKUP
Tra cứu thông minh và chính xác
Sử dụng hàm XLOOKUP trong Excel để tra cứu thông tin sản phẩm dễ dàng
XLOOKUP có thể dễ dàng thay thế VLOOKUP và INDEX/MATCH cho bạn. Không giống như VLOOKUP, XLOOKUP có thể tra cứu 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 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. Đây là một bước tiến lớn trong việc đơn giản hóa các tác vụ tra cứu phức tạp.
5. OFFSET: Tạo vùng tham chiếu động, thay thế SUM truyền thống
Vùng dữ liệu tự động cập nhật
Ví dụ minh họa cách hàm OFFSET tạo vùng dữ liệu động trong Excel
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ụ 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 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.
Thay vì sử dụng SUM thông thường, bạn có thể chạy =SUM(OFFSET(A30, -6, 0, 7, 1)) và tính tổng của phạm vi trả về. Khi bạn thêm dữ liệu bán hàng mới 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. Điều này cực kỳ tiện lợi cho các báo cáo động hoặc phân tích dữ liệu theo thời gian thực.
6. AGGREGATE: Hàm tổng hợp đa năng cho người dùng chuyên nghiệp
Tính toán linh hoạt, bỏ qua lỗi
Cách dùng hàm AGGREGATE trong Excel để tính toán và bỏ qua lỗi
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 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 phải đượ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 số hàm và hành vi tùy chọn từ trang chính thức của Microsoft. AGGREGATE là một trợ thủ đắc lực giúp bạn làm việc với dữ liệu không hoàn hảo một cách hiệu quả.
Nâng tầm kỹ năng Excel của bạn
Giờ đây bạn đã biết về những hàm Excel thiết yếu này, hãy bắt đầu áp dụng chúng vào thực tế. Hãy thử sử dụng chúng trên 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 khai phá những 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. 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. Đừng quên chia sẻ bài viết này để nhiều người cùng biết đến những hàm Excel hữu ích này nhé!
