Bạn có thường xuyên nhìn chằm chằm vào một bảng tính Excel khổng lồ, đối mặt với hàng núi dữ liệu và ước rằng có một cách tốt 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 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 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 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 sẽ tạo nên sự khác biệt lớn trong công việc của bạn.
Thủ thuật tối ưu và tăng tốc công thức Excel
TEXTBEFORE, TEXTAFTER và TEXTSPLIT: Xử lý 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 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 những hàm mới (tương đối) này cho phép bạn trích xuất mọi thứ đứng trước hoặc sau một ký tự phân tách (delimiter) cụ thể. 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ư trong hình bên dưới. 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, ","), hàm này 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 (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ể kết hợp TEXTBEFORE và TEXTAFTER. Ví dụ, =TEXTBEFORE(TEXTAFTER(A1, ","), ",") sẽ trả về “Anytown”. Còn TEXTSPLIT sẽ 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 cực kỳ 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.
EDATE và EOMONTH: Thao tác thông minh với ngày tháng trong Excel
Hàm EDATE 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 trước. 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, 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 rất 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 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.
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.
VSTACK và HSTACK: Kết hợp dữ liệu từ nhiều cột
Hàm HSTACK 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. 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 bảng tính khác nhau một cách nhanh chóng.
XLOOKUP: Bước tiến lớn so với VLOOKUP
Hàm XLOOKUP trong Excel
XLOOKUP có thể dễ dàng thay thế VLOOKUP và thậm chí cả INDEX/MATCH. 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 kiểu 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ự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 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ìm kiếm linh hoạt và tùy chọn khớp chính xác làm cho nó trở thành một công cụ không thể thiếu.
OFFSET: Tạo phạm vi động, vượt trội hơn SUM cơ bản
Hàm OFFSET 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 cố định, bạn có thể chạy =SUM(OFFSET(A30, -6, 0, 7, 1)) và tính tổng của phạm vi được 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ẽ được cập nhật. Điều này cực kỳ hữu ích cho các báo cáo cần theo dõi dữ liệu mới nhất một cách liên tục.
AGGREGATE: Trợ thủ đắc lực cho người dùng chuyên nghiệp
Hàm AGGREGATE 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 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 những lỗi này. Bạn có thể chạy công thức =AGGREGATE(1, 6, A1:A10), trong đó số 1 chỉ định hàm AVERAGE, số 6 chỉ định rằng các lỗi sẽ bị 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 số hàm và tùy chọn tại 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.
Với khả năng tùy biến cao và khả năng xử lý dữ liệu lỗi, AGGREGATE là một công cụ không thể thiếu cho các nhà phân tích dữ liệu và bất kỳ ai làm việc với các tập dữ liệu phức tạp.
Vượt xa những hàm SUM và AVERAGE cơ bản
Giờ đây bạn đã tìm hiểu 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 sử dụng chúng trong 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 các 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à nâng cao 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 suất mới, hãy tìm hiểu thêm các thủ thuật tích hợp để tự động hóa quy trình làm việc của mình. Khám phá thêm các hướng dẫn của chúng tôi để biết chi tiết về tự động hóa Excel và biến bạn thành một chuyên gia Excel thực thụ.
