Hàm UNIQUE () của Microsoft 365 là một lý do tuyệt vời để nâng cấp. Kết hợp UNIQUE () và SORT () để tạo danh sách duy nhất với một công thức duy nhất.
Xem nhanh
Thông tin thêm về Windows
Danh sách là một phần cơ bản trong hầu hết mọi ứng dụng Microsoft Excel và may mắn thay, chúng rất dễ tạo. Có hai loại danh sách bạn có thể gặp phải: static list (danh sách tĩnh) và danh sách dựa trên dữ liệu tự nhiên.
Loại danh sách này cho phép bạn tạo dropdown lists (danh sách thả xuống) để hỗ trợ nhập dữ liệu và bảo vệ tính hợp lệ của dữ liệu của bạn. Ví dụ: bạn có thể muốn tạo một dropdown lists cho phép người dùng chọn tên nhân viên hoặc sinh viên thay vì nhập chúng theo cách thủ công. Loại danh sách này dựa trên tập dữ liệu thực sự lưu trữ các giá trị đó. Trong bài viết này, tôi sẽ chỉ cho bạn hai cách để tạo danh sách giá trị duy nhất dựa trên một cột duy nhất trong tập dữ liệu:
- An advanced filter
- UNIQUE()
Tôi đang sử dụng Microsoft 365 trên hệ thống Windows 10 64-bit. UNIQUE () chỉ khả dụng trong Microsoft 365, Excel cho Web, Excel cho máy tính bảng và điện thoại Android .
Trước Microsoft 365: cách sử dụng Advanced Filter (bộ lọc nâng cao) trong Excel
Khi Excel giới thiệu tính năng AutoFilter, các tính năng lọc ban đầu của Excel thường bị bỏ qua. Nhưng đôi khi, bạn sẽ gặp phải tình huống mà AutoFilter không thể đáp ứng. Khi điều này xảy ra, bạn có thể chuyển sang tính năng Advanced Filter của Excel, hỗ trợ các tùy chọn nâng cao hơn. Đây là nơi bạn chuyển sang khi cần một danh sách duy nhất dựa trên dữ liệu hiện có. Nhưng trước khi bắt đầu, hãy xem xét một vài điểm khác biệt giữa hai tính năng lọc:
- AutoFilter hoạt động với một tập dữ liệu tĩnh. Sử dụng tính năng nâng cao, bạn có thể sao chép dữ liệu sang một vị trí khác. Bạn có thể xóa các bản sao khỏi tập dữ liệu nguồn nhưng làm như vậy thực sự sẽ thay đổi tập dữ liệu.
- Tính năng nâng cao hỗ trợ các tiêu chí phức tạp.
- Tính năng nâng cao cho phép bạn trích xuất một danh sách duy nhất hoặc các bản ghi duy nhất.
Trước khi có sẵn UNIQUE (), bạn có thể đã sử dụng advanced filter để tạo danh sách giá trị duy nhất. Chúng tôi sẽ xem xét tính năng này cho những người bạn không sử dụng Microsoft 365. Điều quan trọng cần nhớ là kỹ thuật này không động, ngay cả khi bạn sử dụng đối tượng Table để lưu trữ dữ liệu của mình.
Hình A hiển thị một tập dữ liệu đơn giản với các giá trị trùng lặp trong mọi cột. Bây giờ, giả sử bạn cần một danh sách các giá trị duy nhất trong cột Personnel.
Hình A
Để tạo danh sách đó theo cách thủ công, hãy làm như sau:
- Nhấp vào bất kỳ ô nào trong tập dữ liệu.
- Bấm vào tab Data và sau đó bấm Advanced trong nhóm Sort & Filter.
- Nhấp vào tùy chọn Copy to Another Location.
- Excel sẽ hiển thị tham chiếu ô cho toàn bộ tập dữ liệu hoặc đối tượng Table dưới dạng phạm vi danh sách. Nếu bạn giữ lại lựa chọn này, Excel sẽ trả về một tập dữ liệu duy nhất dựa trên tất cả các cột. Thay vào đó, hãy chọn cột Personnel bằng cách bấm vào điều khiển rồi chọn D2: D13. Nếu bạn đang sử dụng đối tượng Table, tính năng này có thể hiển thị tên cột thay vì phạm vi ô.
- Loại bỏ Criteria Range nếu có.
- Bấm vào Copy To control và sau đó chọn một ô khác biệt, chẳng hạn như H2. Đảm bảo chỉ định một phạm vi đủ lớn để bạn không vô tình ghi đè lên dữ liệu hiện có.
- Kiểm tra tùy chọn Unique Records Only, như thể hiện trong Hình A .
- Bấm OK.
Hình B cho thấy danh sách duy nhất. Có sáu tên duy nhất trong cột Personnel. Danh sách tách biệt với dữ liệu nguồn của bạn. Ngoài ra, danh sách này không động. Nếu bạn nhập hồ sơ cho một nhân viên mới, bạn phải nhớ cập nhật danh sách theo cách thủ công. Nếu bạn muốn danh sách được sắp xếp, hãy chọn danh sách, trừ đi phần văn bản tiêu đề và chọn thứ tự sắp xếp trong nhóm Sort & Filter. Bây giờ chúng ta hãy chuyển sang hai hàm loại bỏ tất cả các bước thủ công và tạo một danh sách động: UNIQUE () và SORT ().
Hình B
Cách sử dụng hàm UNIQUE () trong Excel
Nếu đang sử dụng Microsoft 365 hoặc một trong các phiên bản Excel độc lập 2019, bạn có thể nhanh chóng tạo danh sách động bằng cách sử dụng hàm UNIQUE (). Hàm này trả về một danh sách các giá trị duy nhất trong một danh sách hoặc dải ô, sử dụng cú pháp sau:
UNIQUE(array, [by_col], [exactly_once])
Đối số array là phạm vi bạn muốn giảm xuống một danh sách duy nhất. Đối số by_col là một giá trị Boolean: TRUE so sánh các cột và trả về các cột duy nhất, FALSE là mặc định và sẽ so sánh các hàng với các hàng và trả về các hàng duy nhất.
Đối số exactly_once cũng là một giá trị Boolean: TRUE trả về tất cả các hàng hoặc cột riêng biệt xuất hiện chính xác một lần từ phạm vi hoặc mảng, FALSE, mặc định, trả về tất cả các hàng hoặc cột riêng biệt từ phạm vi.
Với mục đích trả về một danh sách duy nhất từ một cột, chúng ta chỉ cần đối số array. Chọn một ô và nhập hàm sau bằng cách chọn và không nhập các ô D2: D13. Nếu bạn đang sử dụng đối tượng Table, hãy để Excel tìm ra cú pháp chính xác. Nếu bạn đang sử dụng một dải ô thông thường, hãy nhập địa chỉ dải ô nếu bạn muốn.
Hình C cho thấy kết quả sử dụng phạm vi tràn. Khi sử dụng UNIQUE (), bạn có thể hiển thị ô tiêu đề hoặc bỏ qua ô này bằng cách loại bỏ nó khỏi vùng chọn.
Hình C
Microsoft 365 hiện hỗ trợ cái được gọi là phạm vi tràn (spill range), là kết quả của công thức mảng động trả về nhiều giá trị, đầu ra của nó tràn ra ngoài ô đầu vào. Nói tóm lại, phạm vi tràn là một phạm vi kết quả được tính toán từ một công thức. Khi bạn chọn bất kỳ ô nào trong phạm vi tràn, Excel sẽ đánh dấu toàn bộ phạm vi bằng đường viền màu xanh lam và hàm trong thanh công thức. Bạn sẽ luôn tìm thấy công thức ở ô trên cùng.
Không giống như danh sách thủ công mà chúng tôi đã tạo trước đó bằng bộ lọc nâng cao, kết quả của UNIQUE () là động. Như bạn có thể thấy trong Hình D đã thêm một giá trị mới vào cột Personnel và danh sách trong cột H sẽ tự động cập nhật.
Hình D
Cách sử dụng SORT () trong kết quả của UNIQUE () trong Excel
Không giống như danh sách kết quả được tạo bởi bộ lọc nâng cao, bạn có thể sử dụng hàm SORT () để sắp xếp kết quả của UNIQUE () và không thể dễ dàng hơn. Hình E cho thấy kết quả của việc gói UNIQUE () trong SORT (). Như bạn thấy, kết quả được sắp xếp, nhưng bạn có thể tìm ra lỗi không? Công thức sắp xếp Personnel với các giá trị còn lại và bạn sẽ không muốn điều đó xảy ra. Cách chữa đơn giản là xóa ô tiêu đề khỏi phạm vi mảng như sau:
=SORT(UNIQUE(D3:D13))
Hình E
Nếu bạn vẫn đang sử dụng phiên bản cũ hơn, hãy sử dụng tính năng Advanced Filter để tạo danh sách giá trị duy nhất tĩnh, không được sắp xếp dựa trên dữ liệu tự nhiên. Nếu bạn đang sử dụng Microsoft 365, hãy bọc một hàm UNIQUE () trong một hàm SORT () để có danh sách động và được sắp xếp.
