Thủ thuật Excel này sẽ chấm dứt nỗi đau khi phải thay đổi kích thước bảng!

Không gì phá hỏng quy trình làm việc bằng việc chèn dữ liệu mới và nhận ra bảng Excel của bạn không đủ lớn. Mọi người đã từng phải liên tục kéo các cạnh cho đến khi học được mẹo đơn giản này giúp bảng Excel tự mở rộng.

Dynamic Arrays (mảng động) là cách đúng đắn để mở rộng bảng Excel

Thay vì trả về một giá trị duy nhất, mảng động sẽ làm tràn kết quả sang nhiều ô mà không cần bạn xác định phạm vi chính xác trước. Hành vi này khiến chúng phù hợp để tạo các bảng tự mở rộng.

Các ví dụ bao gồm những hàm UNIQUE, SORT và FILTER. Các hàm này trả về những mảng động có thể mở rộng hoặc thu hẹp dựa trên dữ liệu nguồn của bạn. Khi bạn thêm các mục mới vào tập dữ liệu, kết quả sẽ được cập nhật ngay lập tức mà không cần bất kỳ sự can thiệp thủ công nào.

Khi bạn nhập công thức mảng động vào một ô, Excel sẽ tự động điền vào các ô liền kề cần thiết để hiển thị tất cả kết quả. Bạn sẽ biết nó hoạt động khi thấy đường viền màu xanh lam bao quanh phạm vi này. Việc cố gắng nhập gì đó vào vùng tràn này sẽ dẫn đến lỗi #SPILL!, đây là một cảnh báo hữu ích.

Sử dụng mảng động không chỉ tiện lợi mà còn đáng tin cậy, vì việc quản lý bảng thủ công có thể dẫn đến lỗi, bỏ sót dữ liệu và gây khó chịu.

Cách tạo danh sách tự mở rộng cho các mục độc nhất

Hàm UNIQUE là một trong những hàm có thể giúp bạn tiết kiệm rất nhiều công sức. Thay vì phải quét thủ công qua các danh sách, hãy sử dụng hàm này để tự động trích xuất các giá trị riêng biệt từ dữ liệu.

Đây là cú pháp cơ bản:

=UNIQUE(array, [by_col], [exactly_once])

Tham số array chứa dữ liệu nguồn, chẳng hạn như một cột gồm các phòng ban của nhân viên hoặc tên khách hàng. Tham số by_col (TRUE hoặc FALSE) xác định so sánh theo cột hay theo hàng, trong khi exactly_once lọc các giá trị chỉ xuất hiện một lần.

Giả sử bạn đang làm việc trên bảng tính dữ liệu nhân viên và cần một danh sách tất cả các phòng ban, chỉ cần nhập:

=UNIQUE(R2:R3004)

Ở đây, cột R chứa tên phòng ban từ hàng 2 đến 3004. Excel sẽ ngay lập tức tạo một danh sách động gồm các phòng ban duy nhất, danh sách này sẽ được cập nhật mỗi khi có người gia nhập nhóm mới.

Phương pháp này cũng vượt trội hơn phương pháp loại bỏ các mục trùng lặp truyền thống trong Excel vì những mảng động vẫn được kết nối với dữ liệu nguồn của bạn. Ngược lại, việc loại bỏ mục trùng lặp thủ công sẽ tạo ra các danh sách tĩnh, trở nên lỗi thời ngay khi bạn thêm mục mới.

Đối với các trường hợp exact_once, bạn có thể sử dụng công thức sau để tìm các phòng ban chỉ có một nhân viên. Công thức này hiệu quả trong việc xác định các nhóm thiếu nhân sự hoặc những vai trò duy nhất trong tổ chức của bạn.

=UNIQUE(R2:R3004,,TRUE)

Bạn cũng có thể tự động sắp xếp các danh sách động của mình

Hàm SORT sẽ đưa mảng động lên một tầm cao mới. Tiếp tục với ví dụ về dữ liệu nhân viên, thay vì sắp xếp thủ công tên nhân viên hoặc dữ liệu lương, Excel sẽ tự động xử lý việc sắp xếp bất cứ khi nào dữ liệu thay đổi. Sau đây là cú pháp của hàm SORT:

=SORT(array, [sort_index], [sort_order], [by_col])

Tham số array chứa phạm vi dữ liệu, sort_index xác định cột cần sắp xếp, sort_order xác định tăng dần (1) hoặc giảm dần (-1), và theo cột cho biết sắp xếp theo cột (TRUE) hay theo hàng (FALSE).

Có thể kết hợp hàm SORT với hàm UNIQUE để có kết quả tốt hơn. Ví dụ, công thức sau sẽ cung cấp danh sách các phòng ban duy nhất được sắp xếp theo thứ tự bảng chữ cái từ danh sách nhân viên. Khi phòng Nhân sự thêm phòng ban mới, chúng sẽ tự động xuất hiện ở đúng vị trí bảng chữ cái.

=SORT(UNIQUE(R2:R3004))

Nếu cần phân tích lương, chúng ta có thể sử dụng:

=SORT(A:H, 8, -1)

Công thức trên sắp xếp dữ liệu nhân viên theo mức lương giảm dần. Cột thứ 8 chứa mức lương, và -1 sắp xếp từ cao nhất đến thấp nhất.

Bạn có thể xem hướng dẫn về hàm SORT trong Excel để biết thêm ví dụ. Mục tiêu là tự động cập nhật, nhờ đó các danh sách đã sắp xếp sẽ được làm mới ngay lập tức mà không cần bất kỳ sự can thiệp thủ công nào.

Hàm FILTER là lựa chọn hàng đầu cho các báo cáo động

Nếu chưa bao giờ sử dụng hàm FILTER của Excel, bạn thực sự đang bỏ lỡ một điều tuyệt vời. Hàm FILTER tạo ra một trong những báo cáo động mạnh mẽ nhất. Bạn có thể sử dụng nó để tự động chỉ hiển thị dữ liệu đáp ứng tiêu chí của mình mà không cần tạo các bản sao tĩnh sẽ trở nên lỗi thời.

Cú pháp của hàm Filter là:

=FILTER(array, include, [if_empty])

array chứa toàn bộ tập dữ liệu của bạn, include xác định tiêu chí của bạn và if_empty hiển thị thông báo tùy chỉnh khi không có kết quả nào khớp với điều kiện.

Mọi người thường xuyên sử dụng hàm này cho các báo cáo khác nhau. Ví dụ, nếu muốn hiển thị tất cả các thành viên trong nhóm bán hàng từ cơ sở dữ liệu nhân viên, chúng ta có thể sử dụng nó theo cách sau:

=FILTER(A:Q, Q:Q="Sales")

Khi ai đó chuyển sang bộ phận bán hàng, họ sẽ tự động xuất hiện trong kết quả được lọc. Tương tự, để phân tích lương, chúng ta có thể sử dụng công thức sau để hiển thị những nhân viên có thu nhập trên 50.000 USD.

=FILTER(A:H, H:H>50000)

Nếu ai đó được tăng lương, họ sẽ đột nhiên xuất hiện trong báo cáo thu nhập cao này. Bên cạnh đó, chúng ta cũng có thể kết hợp các tiêu chí:

=FILTER(A:Q, (Q:Q="Doanh số") * (H:H>50000))

Công thức trên hiển thị các thành viên trong nhóm bán hàng có thu nhập trên 50.000 USD. Dấu hoa thị (*) hoạt động như toán tử AND, yêu cầu cả hai điều kiện đều đúng.

Mảng động hữu ích vì chúng loại bỏ việc cập nhật bảng thủ công và loại bỏ các mục nhập bị thiếu. Excel trở nên trực quan hơn khi bạn bắt đầu sử dụng UNIQUE, SORT và FILTER cùng nhau.