Đánh giá lequocthai.com:
Hàm UNIQUE của Excel là một công cụ đột phá để làm sạch dữ liệu, nhưng nó có một hạn chế đáng tiếc: nó chỉ hoạt động với các cột liền kề. Tuy nhiên, bằng cách kết hợp thêm các hàm khác bên trong, bạn có thể tạo ra một danh sách tùy chỉnh, động mà loại trừ những cột bạn không muốn đưa vào.
Kiến thức cơ bản: Cách hàm UNIQUE thường hoạt động
Hàm UNIQUE của Excel, có sẵn trong các phiên bản Excel độc lập từ 2021 trở lên, Excel cho Microsoft 365, Excel cho web và các ứng dụng di động, máy tính bảng mới nhất, sử dụng cú pháp sau:
=UNIQUE(mảng,[theo_cột],[chính_xác_một_lần])trong đó:
- mảng(bắt buộc): Là phạm vi hoặc bảng bạn muốn trích xuất các giá trị duy nhất.
- theo_cột(tùy chọn): Yêu cầu Excel so sánh các cột (TRUE) thay vì các hàng (mặc định là FALSE).
- chính_xác_một_lần(tùy chọn): Trả về các mục chỉ xuất hiện một lần (TRUE), thay vì mọi giá trị riêng biệt (mặc định là FALSE).
Hàm UNIQUE thuộc nhóm hàm mảng động. Điều này có nghĩa dù bạn chỉ nhập công thức vào một ô, kết quả sẽ tự động tràn (spill) sang các ô liền kề. Bạn sẽ thấy một đường viền xanh mảnh bao quanh kết quả, và nếu có vật cản, lỗi #SPILL! sẽ xuất hiện.
Trích xuất các cột liền kề
Giả sử bạn đang theo dõi chi tiêu gia đình trong một bảng Excel tên T_ChiTieu, và bạn muốn liệt kê mọi tổ hợp duy nhất giữa danh mục thanh toán và cửa hàng.
Vì đây là các cột liền kề, bạn có thể kết hợp chúng bằng dấu hai chấm trong đối số mảng:
=UNIQUE(T_ChiTieu[[Danh mục]:[Cửa hàng]])
Vấn đề khi trích xuất các cột không liền kề
Bây giờ, giả sử bạn muốn liệt kê mọi tổ hợp duy nhất của danh mục và phương thức thanh toán, là các cột không liền kề.
Nếu bạn thử chọn hai cột riêng biệt này làm đối số trong UNIQUE, bạn sẽ gặp lỗi #VALUE!. Lý do là UNIQUE yêu cầu một mảng dữ liệu liền kề. Khi bạn chọn hai cột riêng biệt bằng dấu phẩy, Excel sẽ nhầm cột thứ hai thành đối số theo_cột (vốn cần giá trị TRUE/FALSE), gây ra lỗi.
Dưới đây là hai cách để giải quyết.
Cách khắc phục nhanh: Kết hợp hàm CHOOSECOLS bên trong UNIQUE
Để loại bỏ cột, bạn cần cung cấp cho UNIQUE một tập dữ liệu ảo chỉ chứa thông tin bạn muốn. Hàm CHOOSECOLS được thiết kế cho việc này — nó cho phép bạn chọn cột cụ thể từ một bảng dựa trên số thứ tự.
Trong bảng T_ChiTieu, Danh mục là cột 2 và Phương thức là cột 4. Công thức sẽ là:
=UNIQUE(CHOOSECOLS(T_ChiTieu,2,4))CHOOSECOLS xử lý toàn bộ bảng T_ChiTieu và chỉ giữ lại cột 2 và 4. Tập dữ liệu mới này sau đó được chuyển cho UNIQUE để xử lý và trả kết quả.
Cách khắc phục tối ưu: Sử dụng MATCH để tham chiếu động
Phương pháp CHOOSECOLS hoạt động tốt, nhưng có một nhược điểm: nó phụ thuộc vào số thứ tự cố định. Nếu bạn chèn thêm cột vào bảng, vị trí cột “Phương thức” sẽ thay đổi và công thức sẽ sai.
Để tạo công thức linh hoạt, tự điều chỉnh, hãy dùng hàm MATCH để tìm cột theo tên thay vì số:
=UNIQUE(
CHOOSECOLS(
T_ChiTieu,
MATCH(G1,T_ChiTieu[#Headers],0),
MATCH(H1,T_ChiTieu[#Headers],0)
))Nhấn Alt+Enter khi nhập trong thanh công thức để xuống dòng, giúp công thức dễ đọc hơn.
Ở đây, thay vì nhập số 2 và 4, hàm MATCH sẽ làm việc đó. Hàm MATCH đầu tiên tìm chữ “Danh mục” trong ô G1 ở hàng tiêu đề của bảng. Số 0 yêu cầu khớp chính xác. Nó trả về vị trí tương đối (ví dụ: 2 nếu Danh mục là cột thứ hai). Hàm MATCH thứ hai làm tương tự với “Phương thức” trong ô H1.
Đây là giải pháp toàn diện vì:
- Linh hoạt cấu trúc: Bạn có thể di chuyển cột “Danh mục” hoặc chèn cột mới. Vì MATCH luôn tra cứu theo tên, công thức sẽ tự động cập nhật.
- Phòng tránh lỗi: Loại bỏ rủi ro đếm sai cột trong bảng lớn.
- Tùy chọn linh hoạt: Chỉ cần thay đổi tên cột trong ô G1 và H1 để tạo danh sách duy nhất mới.
Mẹo chuyên nghiệp: Tạo menu thả xuống tiêu đề bằng hàm INDIRECT
Để thao tác trực quan hơn, bạn có thể biến ô G1 và H1 thành menu thả xuống, tránh lỗi do gõ sai tên.
Tuy nhiên, công cụ Xác thực Dữ liệu (Data Validation) của Excel không hỗ trợ trực tiếp tham chiếu có cấu trúc (như T_ChiTieu[#Headers]). Bạn có thể dùng hàm INDIRECT để chuyển chuỗi văn bản thành tham chiếu hợp lệ.
Đầu tiên, chọn các ô đặt tên cột động (ví dụ G1 và H1), vào tab Dữ liệu, nhấp “Xác thực Dữ liệu”.
Trong menu “Cho phép”, chọn “Danh sách”, và nhập công thức sau vào ô “Nguồn”:
=INDIRECT("T_ChiTieu[#Headers]")
Nhấp “OK”, ô G1 và H1 sẽ có mũi tên thả xuống hiển thị tất cả tiêu đề cột của bảng T_ChiTieu.
Khi bạn chọn tên cột mới từ danh sách, MATCH sẽ tìm vị trí mới, CHOOSECOLS lấy dữ liệu và UNIQUE cập nhật ngay lập tức. Mọi cột mới thêm vào bảng T_ChiTieu sẽ tự động xuất hiện trong menu thả xuống này.
So với công cụ Xóa Trùng lặp (Remove Duplicates) có thể làm mất dữ liệu, hàm UNIQUE dựa trên mảng động là cách xử lý dữ liệu an toàn và hiệu quả hơn nhiều. Với thiết lập này, dữ liệu gốc của bạn được giữ nguyên và bản tóm tắt duy nhất luôn chính xác, bất kể bạn sắp xếp lại bảng tính thế nào.
Tags: Excel UNIQUE, Hàm Excel, Xử lý dữ liệuExcel UNIQUE, Hàm Excel, Xử lý dữ liệu
