Bạn có thường xuyên nhìn chằm chằm vào bảng tính Excel, vật lộn với hàng tá 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ó. Suốt 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, congnghehot.net sẽ 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 ra sự khác biệt lớn trong công việc của bạn. Từ việc làm sạch dữ liệu lộn xộn cho đến tự động hóa các báo cáo phức tạp, những hàm này chính là chìa khóa để bạn nâng cao hiệu suất và trở thành một chuyên gia Excel thực thụ. Hãy cùng khám phá và đưa kỹ năng sử dụng bảng tính của bạn lên một tầm cao mới.
Tối ưu và tăng tốc công thức Excel
6. TEXTBEFORE, TEXTAFTER và TEXTSPLIT: “Chuyên gia” Thao Tác Chuỗi Văn Bản
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ă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 mới này (tương đối mới) 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ó một danh sách địa chỉ khách hàng trong một cột duy nhất, được định dạng như ví dụ 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ể sử dụng công thức =TEXTBEFORE(A1, ","), công thức 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ụ, công thức =TEXTBEFORE(TEXTAFTER(A1, ","), ",") sẽ trả về “Anytown” (nếu “Anytown” nằm giữa hai dấu phẩy). Hàm 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, mỗi cột chứa một phần của địa chỉ.
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 để phục vụ việc lập báo cáo và phân tích.
5. EDATE và EOMONTH: Quản Lý Ngày Tháng Chuyên Nghiệp
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ề một ngày, là số tháng được chỉ định 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ý thuê bao 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 lợi khi bạn muốn tính toán các thời hạn trong quản lý dự án.
Hàm EDATE trong Excel
Điều thú vị là 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.
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 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: Hợp Nhất Dữ Liệu Đa Chiều
VSTACK và HSTACK kết hợp nhiều mảng hoặc phạm vi dữ liệu theo chiều dọc (Vertical Stack) và chiều ngang (Horizontal Stack) vào một mảng duy nhất. Giả sử bạn có dữ liệu doanh số 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.
Hàm HSTACK trong Excel
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. Các 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 dữ liệu cho các báo cáo tổng hợp.
3. XLOOKUP: Nâng Tầm Tra Cứu Dữ Liệu, Thay Thế VLOOKUP Mạnh 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 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). 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).
Hàm XLOOKUP trong Excel
Ví dụ, bạn muốn tìm giá của sản phẩm có ID là 1003. Bạn có thể thêm công thức =XLOOKUP(E1, A2:A6, C2:C6) để hiển thị giá chính xác. Trong đó, E1 là ô chứa ID sản phẩm cần tìm, A2:A6 là phạm vi cột ID sản phẩm để tìm kiếm, và C2:C6 là phạm vi cột giá mà bạn muốn trả về. XLOOKUP rất hữu ích trong quản lý kho hàng, phân tích doanh số, 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: Tạo Phạm Vi Động, Tính Tổng Linh Hoạt Hơn 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ụ thể 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
Thay vì sử dụng SUM truyền thống, bạn có thể chạy công thức =SUM(OFFSET(A30, -6, 0, 7, 1)) để tính tổng của phạm vi được trả về. Cụ thể, A30 là điểm bắt đầu; -6 dịch chuyển lên 6 hàng (để lấy 7 ngày bao gồm A30); 0 không dịch chuyển cột; 7 là chiều cao (số hàng) của phạm vi; và 1 là chiều rộng (số cột) của phạm vi. 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 cố định) sẽ tự động điều chỉnh phạm vi, và tổng trong ô B1 sẽ tự động cập nhật, giúp bạn tiết kiệm thời gian điều chỉnh công thức.
1. AGGREGATE: Hàm Đa Năng Cho Người Dùng Chuyên Nghiệp
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ể (ví dụ: lỗi, ô bị ẩn).
Hàm AGGREGATE trong Excel
Giả sử bạn có một 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ể sử dụng 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 giá trị 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ã số hàm và hành vi tùy chọn từ trang hỗ trợ chính thức của Microsoft để tận dụng tối đa sức mạnh của hàm AGGREGATE.
Vượt Xa SUM và AVERAGE
Với việc nắm vững những hàm Excel thiết yếu này, bạn đã sẵn sàng để áp dụng chúng vào thực tế. Hãy bắt đầu 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 tạo ra. Cho dù bạn đang phân tích dữ liệu phức tạp, tự động hóa các báo cáo hàng ngày hay đơn giản chỉ là tổ chức thông tin hiệu quả hơn, những công cụ này sẽ giúp bạn tiết kiệm đáng kể thời gian và nâng cao năng suất 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 tìm hiểu các thủ thuật tích hợp để tự động hóa sổ làm việc của mình. Đừng ngần ngại khám phá và thử nghiệm. Việc thành thạo các hàm này không chỉ giúp bạn hoàn thành công việc nhanh hơn mà còn mở ra những khả năng mới trong việc xử lý và phân tích dữ liệu, biến bạn trở thành một chuyên gia thực sự trong thế giới bảng tính.