Xu Hướng 2/2024 # Những Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu # Top 11 Xem Nhiều

Bạn đang xem bài viết Những Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu được cập nhật mới nhất tháng 2 năm 2024 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Trong bài viết lần này, chúng tôi sẽ chỉ ra cho các bạn cách hiệu quả nhất để tìm kiếm, chọn lọc và đánh dấu các giá trị duy nhất và khác biệt trong Excel.

Giá trị duy nhất là các mục xuất hiện trong tập dữ liệu duy nhất một lần.

Giá trị khác biệt là tất cả các mục khác nhau trong danh sách, nghĩa là giá trị duy nhất và lần xuất hiện thứ nhất của các giá trị nhân bản.

Tìm các giá trị duy nhất trong một cột

Để tìm các giá trị khác biệt và duy nhất trong một danh sách, bạn sử dụng một trong các công thức sau đây, trong đó A2 là ô đầu tiên và A10 là ô cuối cùng có dữ liệu.

Cách tìm các giá trị duy nhất trong Excel:

=IF(COUNTIF($A$2:$A$10, $A2)=1, “Unique”, “”)

Cách tìm các giá trị khác biệt trong Excel:

=IF(COUNTIF($A$2:$A2, $A2)=1, “Distinct”, “”)

Trong công thức tìm giá trị khác biệt chỉ có một độ lệch nhỏ trong tham chiếu ô thứ hai nhưng lại tạo ra một sự khác biệt lớn:

Tìm hàng duy nhất / khác biệt trong Excel

Theo cách tương tự, bạn có thể tìm các hàng duy nhất trong bảng Excel dựa trên các giá trị trong 2 cột trở lên. Trong trường hợp này, bạn cần phải sử dụng hàm COUNTIFS thay vì COUNTIF để đánh giá các giá trị trong một vài cột (có thể đánh giá được tới 127 phạm vi/ cặp tiêu chuẩn trong một công thức đơn).

Ví dụ: để tìm tên duy nhất hoặc khác biệt trong danh sách, hãy sử dụng các công thức sau:

Công thức để tìm các hàng duy nhất:

=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, “Unique row”, “”)

Công thức để tìm các hàng khác biệt:

=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, “Distinct row”, “”)

Tìm các giá trị duy nhất / khác biệt trong Excel phân biệt chữ hoa và chữ thường

Nếu bạn đang làm việc với một bộ dữ liệu trong trường hợp có vấn đề, bạn cần một công thức mảng phức tạp hơn một chút.

Tìm các giá trị duy nhất phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A$10,A2)))=1,”Unique”,””)

Tìm các giá trị riêng biệt phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Cách lọc các giá trị duy nhất và khác biệt trong Excel

Để xem riêng các giá trị duy nhất hoặc khác biệt trong danh sách, chúng ta cần lọc chúng ra bằng cách thực hiện các bước sau.

Cách chọn các giá trị khác biệt / độc nhất

Nếu bạn có một danh sách các giá trị duy nhất / khác biệt tương đối nhỏ, bạn chỉ cần chọn nó theo cách thông thường bằng cách dùng chuột. Nếu danh sách được lọc có hàng trăm hoặc hàng nghìn hàng, bạn có thể sử dụng một trong số các phím tắt để tiết kiệm thời gian.

Để nhanh chóng, bạn chọn danh sách duy nhất hoặc khác biệt bao gồm tiêu đề cột, lọc giá trị duy nhất, nhấp vào bất kỳ ô nào trong danh sách duy nhất, sau đó nhấn Ctrl + A.

Để chọn các giá trị khác biệt hoặc duy nhất mà không có tiêu đề cột, hãy lọc giá trị duy nhất, chọn ô đầu tiên với dữ liệu và nhấn Ctrl + Shift + End để mở rộng vùng chọn vào ô cuối cùng.

Mẹo. Trong một số trường hợp hiếm hoi, chủ yếu là các trường hợp làm việc với dữ liệu quá lớn, các phím tắt trên có thể sẽ chọn cả những ô hiển thị và không hiển thị. Để khắc phục điều này, nhấn Ctrl + A hoặc Ctrl + Shift + End đầu tiên, và sau đó nhấn Alt +; Để chỉ chọn các ô hiển thị, bỏ qua hàng ẩn.

Sao chép các giá trị duy nhất hoặc khác biệt đến vị trí khác

Để sao chép danh sách các giá trị duy nhất sang vị trí khác, chỉ cần thực hiện theo các bước sau:

– Chọn các giá trị được lọc bằng chuột hoặc các phím tắt đã đề cập ở trên. – Nhấn Ctrl + C để sao chép các giá trị đã chọn. – Chọn ô trên cùng bên trái trong dải đích (nó có thể nằm trên cùng một tờ hoặc khác tờ), và nhấn Ctrl + V để dán giá trị.

Cách làm nổi bật các giá trị duy nhất và khác biệt trong Excel

Bất cứ khi nào bạn cần làm nổi bật bất cứ điều gì trong Excel dựa trên một điều kiện nhất định, thì cần áp dụng tính năng định dạng có điều kiện.

Đánh dấu các giá trị duy nhất trong một cột (quy tắc built-in)

Cách nhanh nhất và dễ dàng nhất để làm nổi bật các giá trị duy nhất trong Excel là áp dụng quy tắc định dạng có điều kiện cho sẵn:

3. Trong cửa sổ hộp thoại Duplicate Values, chọn Unique trong hộp bên tay trái và chọn định dạng mong muốn trong hộp bên phải, sau đó nhấn OK.

Mẹo. Nếu bạn không hài lòng với bất kỳ định dạng nào được thiết lập trước, hãy nhấp vào Custom Format…(mục cuối cùng trong danh sách thả xuống) và đặt màu và/ hoặc phông chữ theo ý thích của bạn.

Như bạn thấy, làm nổi bật các giá trị duy nhất trong Excel là một trong những nhiệm vụ vô cùng đơn giản. Tuy nhiên, quy tắc được xây dựng trong Excel chỉ hoạt động cho các mục xuất hiện trong danh sách duy nhất một lần. Nếu bạn cần làm nổi bật các giá trị khác biệt – gồm 1 lần xuất hiện đại diện cho những giá trị trùng lặp – bạn sẽ phải tạo quy tắc của riêng mình dựa trên công thức. Bạn cũng cần phải tạo một quy tắc tùy chỉnh để đánh dấu các hàng duy nhất dựa trên các giá trị trong một hoặc nhiều cột.

Đánh dấu các giá trị duy nhất và khác biệt trong Excel (quy tắc tùy chỉnh)

Để làm nổi bật các giá trị duy nhất hoặc khác biệt trong một cột, hãy chọn dữ liệu nhưng không bao gồm tiêu đề cột (Hẳn là bạn cũng không muốn làm nổi bật tiêu đề cột) Và tạo ra một quy tắc định dạng có điều kiện với một trong các công thức sau.

Đánh dấu các giá trị duy nhất

Để làm nổi bật các giá trị xuất hiện trong một danh sách duy nhất một lần, sử dụng công thức sau:

=COUNTIF($A$2:$A$10,$A2)=1

Với A2 là ô đầu tiên và A10 là ô cuối cùng của phạm vi áp dụng.

Đánh dấu các giá trị khác biệt Để làm nổi bật tất cả các giá trị khác nhau trong một cột, tức là giá trị duy nhất và 1 lần xuất hiện đại diện cho những giá trị trùng lặp, hãy làm theo công thức sau:

=COUNTIF($A$2:$A2,$A2)=1

Với A2 là ô trên cùng của dải.

Cách tạo quy tắc dựa trên công thức

Để tạo một quy tắc định dạng có điều kiện dựa trên công thức, hãy thực hiện theo các bước sau:

Đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất / khác biệt trong một cột

Để đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất trong một cột cụ thể, hãy sử dụng các công thức cho các giá trị duy nhất và khác biệt mà chúng ta đã sử dụng trong ví dụ trước, nhưng áp dụng quy tắc của bạn cho toàn bộ bảng chứ không phải là một cột đơn.

Ảnh chụp màn hình sau minh họa quy tắc làm nổi bật các hàng dựa trên số liệu riêng biệt trong cột A:

Cách làm nổi bật các hàng duy nhất trong Excel

Nếu bạn muốn làm nổi bật các hàng dựa trên các giá trị trong 2 hoặc nhiều cột, hãy sử dụng chức năng COUNTIFS cho phép chỉ định một số tiêu chí trong một công thức.

Làm nổi bật các hàng duy nhất =COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)=1

Làm nổi bật các hàng khác biệt (các hàng duy nhất cùng với 1 lần xuất hiện đại diện cho những hàng trùng lặp) =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1

Cách nhanh và dễ dàng để tìm và đánh dấu các giá trị duy nhất trong Excel

Như bạn đã thấy, Microsoft Excel cung cấp khá nhiều tính năng hữu ích có thể giúp bạn xác định và đánh dấu các giá trị duy nhất trong bảng tính của bạn. Tuy nhiên, tất cả những giải pháp đó khó có thể gọi là trực quan và dễ sử dụng vì chúng đòi hỏi phải ghi nhớ một số công thức khác nhau. Tất nhiên, Với các chuyên gia Excel thì việc ghi nhớ chẳng có gì khó khăn cả. Nhưng đối với những người dùng Excel thì có một cách nhanh chóng và đơn giản để tìm các giá trị duy nhất trong Excel.

Trong phần cuối cùng của hướng dẫn hôm nay, chúng tôi sẽ sử dụng tiện ích Duplicate Remover cho Excel. Xin đừng nhầm lẫn bởi tên của công cụ này. Ngoài các hồ sơ trùng lặp, add-in có thể hoàn toàn xử lý các mục duy nhất và khác biệ một cách nhanh chóng. 1. Chọn bất kỳ ô nào trong bảng mà bạn muốn tìm các giá trị duy nhất và nhấn vào nút Duplicate Remover trên tab Ablebits Data trong nhóm Dedupe.

Trình hướng dẫn sẽ chạy và toàn bộ bảng sẽ được chọn tự động. Vì vậy, chỉ cần nhấp vào Next để tiếp tục bước tiếp theo.

Mẹo. Khi sử dụng add-in lần đầu tiên, bạn nên kiểm tra hộp Create a backup copy. 2. Tùy thuộc vào mục tiêu của bạn, hãy chọn một trong các tùy chọn sau, sau đó bấm Next: Unique (Duy nhất) Unique +1st occurrences (distinct) (Duy nhất và khác biệt)

3. Chọn một hoặc nhiều cột mà bạn muốn kiểm tra các giá trị. Trong ví dụ này, chúng ta cần tìm các tên duy nhất dựa trên các giá trị trong 2 cột (First name and Last name), do đó ta sẽ chọn cả hai.

Mẹo. Nếu bảng của bạn có tiêu đề, hãy chọn My table has headers. Và nếu bảng của bạn có thể có ô trống, phải kiểm tra lại phần Skip empty cells. Cả hai tùy chọn đều nằm ở phần trên của cửa sổ hộp thoại và thường được chọn theo mặc định.

Chọn một trong các thao tác sau để thực hiện trên các giá trị đã tìm thấy:

Highlight unique values with color – Đánh dấu các giá trị duy nhất dùng màu sắc

Select unique values – Chọn giá trị duy nhất

Identify in a status column – Xác định trong cột trạng thái

Copy to another location – Sao chép đến vị trí khác

Nhấn Finish và xem kết quả:

Nếu việc tìm kiếm các giá trị trùng lặp và duy nhất trong Excel là một phần phổ biến trong công việc hàng ngày của bạn thì hy vọng rằng bài viết này có nhiều phần bổ ích, giúp công việc của bạn thuận lợi hơn. Cảm ơn các bạn đã theo dõi bài viết này.

Các Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu

Trong bài viết lần này, Học Excel Online sẽ chỉ ra cho các bạn cách hiệu quả nhất để tìm kiếm, chọn lọc và đánh dấu các giá trị duy nhất và khác biệt trong Excel.

Giá trị duy nhất là các mục xuất hiện trong tập dữ liệu duy nhất một lần.

Giá trị khác biệt là tất cả các mục khác nhau trong danh sách, nghĩa là giá trị duy nhất và lần xuất hiện thứ nhất của các giá trị nhân bản.

Tìm các giá trị duy nhất trong một cột

Để tìm các giá trị khác biệt và duy nhất trong một danh sách, bạn sử dụng một trong các công thức sau đây, trong đó A2 là ô đầu tiên và A10 là ô cuối cùng có dữ liệu.

Cách tìm các giá trị duy nhất trong Excel:

=IF(COUNTIF($A$2:$A$10, $A2)=1, “Unique”, “”)

Cách tìm các giá trị khác biệt trong Excel:

=IF(COUNTIF($A$2:$A2, $A2)=1, “Distinct”, “”)

Tìm hàng duy nhất / khác biệt trong Excel

Theo cách tương tự, bạn có thể tìm các hàng duy nhất trong bảng Excel dựa trên các giá trị trong 2 cột trở lên. Trong trường hợp này, bạn cần phải sử dụng hàm COUNTIFS thay vì COUNTIF để đánh giá các giá trị trong một vài cột (có thể đánh giá được tới 127 phạm vi/ cặp tiêu chuẩn trong một công thức đơn).

Ví dụ: để tìm tên duy nhất hoặc khác biệt trong danh sách, hãy sử dụng các công thức sau:

Công thức để tìm các hàng duy nhất:

=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, “Unique row”, “”)

Công thức để tìm các hàng khác biệt: Tìm các giá trị duy nhất / khác biệt trong Excel phân biệt chữ hoa và chữ thường

Nếu bạn đang làm việc với một bộ dữ liệu trong trường hợp có vấn đề, bạn cần một công thức mảng phức tạp hơn một chút.

Tìm các giá trị duy nhất phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A$10,A2)))=1,”Unique”,””)

Tìm các giá trị riêng biệt phân biệt chữ hoa và chữ thường:

=IF(SUM((-EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Cách lọc các giá trị duy nhất và khác biệt trong Excel

Để xem riêng các giá trị duy nhất hoặc khác biệt trong danh sách, chúng ta cần lọc chúng ra bằng cách thực hiện các bước sau.

Cách chọn các giá trị khác biệt / độc nhất

Nếu bạn có một danh sách các giá trị duy nhất / khác biệt tương đối nhỏ, bạn chỉ cần chọn nó theo cách thông thường bằng cách dùng chuột. Nếu danh sách được lọc có hàng trăm hoặc hàng nghìn hàng, bạn có thể sử dụng một trong số các phím tắt để tiết kiệm thời gian.

Để nhanh chóng, bạn chọn danh sách duy nhất hoặc khác biệt bao gồm tiêu đề cột, lọc giá trị duy nhất, nhấp vào bất kỳ ô nào trong danh sách duy nhất, sau đó nhấn Ctrl + A.

Để chọn các giá trị khác biệt hoặc duy nhất mà không có tiêu đề cột, hãy lọc giá trị duy nhất, chọn ô đầu tiên với dữ liệu và nhấn Ctrl + Shift + End để mở rộng vùng chọn vào ô cuối cùng.

Mẹo. Trong một số trường hợp hiếm hoi, chủ yếu là các trường hợp làm việc với dữ liệu quá lớn, các phím tắt trên có thể sẽ chọn cả những ô hiển thị và không hiển thị. Để khắc phục điều này, nhấn Ctrl + A hoặc Ctrl + Shift + End đầu tiên, và sau đó nhấn Alt +; Để chỉ chọn các ô hiển thị, bỏ qua hàng ẩn.

Sao chép các giá trị duy nhất hoặc khác biệt đến vị trí khác

Để sao chép danh sách các giá trị duy nhất sang vị trí khác, chỉ cần thực hiện theo các bước sau:

– Chọn các giá trị được lọc bằng chuột hoặc các phím tắt đã đề cập ở trên. – Nhấn Ctrl + C để sao chép các giá trị đã chọn. – Chọn ô trên cùng bên trái trong dải đích (nó có thể nằm trên cùng một tờ hoặc khác tờ), và nhấn Ctrl + V để dán giá trị.

Cách làm nổi bật các giá trị duy nhất và khác biệt trong Excel

Bất cứ khi nào bạn cần làm nổi bật bất cứ điều gì trong Excel dựa trên một điều kiện nhất định, thì cần áp dụng tính năng định dạng có điều kiện.

Đánh dấu các giá trị duy nhất trong một cột (quy tắc built-in)

Cách nhanh nhất và dễ dàng nhất để làm nổi bật các giá trị duy nhất trong Excel là áp dụng quy tắc định dạng có điều kiện cho sẵn:

Mẹo. Nếu bạn không hài lòng với bất kỳ định dạng nào được thiết lập trước, hãy nhấp vào Custom Format…(mục cuối cùng trong danh sách thả xuống) và đặt màu và/ hoặc phông chữ theo ý thích của bạn.

Như bạn thấy, làm nổi bật các giá trị duy nhất trong Excel là một trong những nhiệm vụ vô cùng đơn giản. Tuy nhiên, quy tắc được xây dựng trong Excel chỉ hoạt động cho các mục xuất hiện trong danh sách duy nhất một lần. Nếu bạn cần làm nổi bật các giá trị khác biệt – gồm 1 lần xuất hiện đại diện cho những giá trị trùng lặp – bạn sẽ phải tạo quy tắc của riêng mình dựa trên công thức. Bạn cũng cần phải tạo một quy tắc tùy chỉnh để đánh dấu các hàng duy nhất dựa trên các giá trị trong một hoặc nhiều cột.

Đánh dấu các giá trị duy nhất và khác biệt trong Excel (quy tắc tùy chỉnh)

Để làm nổi bật các giá trị duy nhất hoặc khác biệt trong một cột, hãy chọn dữ liệu nhưng không bao gồm tiêu đề cột (Hẳn là bạn cũng không muốn làm nổi bật tiêu đề cột) Và tạo ra một quy tắc định dạng có điều kiện với một trong các công thức sau.

Đánh dấu các giá trị duy nhất

Để làm nổi bật các giá trị xuất hiện trong một danh sách duy nhất một lần, sử dụng công thức sau:

=COUNTIF($A$2:$A$10,$A2)=1

Với A2 là ô đầu tiên và A10 là ô cuối cùng của phạm vi áp dụng.

Đánh dấu các giá trị khác biệt Để làm nổi bật tất cả các giá trị khác nhau trong một cột, tức là giá trị duy nhất và 1 lần xuất hiện đại diện cho những giá trị trùng lặp, hãy làm theo công thức sau:

=COUNTIF($A$2:$A2,$A2)=1

Với A2 là ô trên cùng của dải.

Cách tạo quy tắc dựa trên công thức

Để tạo một quy tắc định dạng có điều kiện dựa trên công thức, hãy thực hiện theo các bước sau:

Đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất / khác biệt trong một cột

Để đánh dấu toàn bộ các hàng dựa trên các giá trị duy nhất trong một cột cụ thể, hãy sử dụng các công thức cho các giá trị duy nhất và khác biệt mà chúng ta đã sử dụng trong ví dụ trước, nhưng áp dụng quy tắc của bạn cho toàn bộ bảng chứ không phải là một cột đơn.

Cách làm nổi bật các hàng duy nhất trong Excel

Nếu bạn muốn làm nổi bật các hàng dựa trên các giá trị trong 2 hoặc nhiều cột, hãy sử dụng chức năng COUNTIFS cho phép chỉ định một số tiêu chí trong một công thức.

Làm nổi bật các hàng duy nhất =COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)=1

Cách nhanh và dễ dàng để tìm và đánh dấu các giá trị duy nhất trong Excel

Như bạn đã thấy, Microsoft Excel cung cấp khá nhiều tính năng hữu ích có thể giúp bạn xác định và đánh dấu các giá trị duy nhất trong bảng tính của bạn. Tuy nhiên, tất cả những giải pháp đó khó có thể gọi là trực quan và dễ sử dụng vì chúng đòi hỏi phải ghi nhớ một số công thức khác nhau. Tất nhiên, Với các chuyên gia Excel thì việc ghi nhớ chẳng có gì khó khăn cả. Nhưng đối với những người dùng Excel thì có một cách nhanh chóng và đơn giản để tìm các giá trị duy nhất trong Excel.

Chọn một trong các thao tác sau để thực hiện trên các giá trị đã tìm thấy:

Highlight unique values with color – Đánh dấu các giá trị duy nhất dùng màu sắc

Select unique values – Chọn giá trị duy nhất

Identify in a status column – Xác định trong cột trạng thái

Cách Lọc Ra Một Danh Sách Các Giá Trị Duy Nhất Và Khác Biệt Trong Excel

Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách lọc ra một danh sách các giá trị riêng biệt/độc nhất trong cột bằng cách sử dụng một công thức và cách tinh chỉnh công thức đó cho các bộ dữ liệu khác nhau. Ngoài ra, Học Excel Online cũng sẽ chỉ cho các bạn cách nhanh chóng nhất để có được một danh sách riêng biệt bằng cách sử dụng Bộ lọc nâng cao của Excel, và làm thế nào để trích xuất các hàng duy nhất sử dụng Duplicate Remover.

Để lọc ra một danh sách các giá trị duy nhất trong Excel bạn có thể sử dụng cách xác định các giá trị duy nhất đó, lọc riêng rồi sao chép chúng. Tuy nhiên, sử dụng cách làm này sẽ tốn nhiều thời gian, để thực hiện nhanh hơn, bạn có thể sử dụng một công thức đặc biệt cùng với một vài kĩ thuật khác mà Học Excel Online sẽ chia sẻ với các bạn ngay sau đây.

Cách lọc ra các giá trị duy nhất trong Excel

Để trích xuất một danh sách các giá trị duy nhất trong Excel, bạn có thể sử dụng một trong các công thức sau đây.

Công thức mảng để tìm ra những giá trị duy nhất (hoàn thành công thức xong ta nhấn Ctrl + Shift + Enter): Công thức giá trị duy nhất thường xuyên (hoàn thành công thức xong ta nhấn Enter):

Chú thích. Bởi vì công thức tham chiếu ô bên trên ô đầu tiên của danh sách các giá trị duy nhất, thường là tiêu đề cột (như trong ví dụ này là B1), nên bạn cần đảm bảo rằng tiêu đề của bạn là một tên duy nhất không xuất hiện ở bất cứ đâu trong cột.

– Lựa chọn một trong những công thức dựa theo dữ liệu của bạn. – Nhập công thức vào ô đầu tiên của danh sách các giá trị duy nhất (trong ví dụ sẽ là ô B2). – Nếu bạn đang sử dụng công thức mảng, nhấn Ctrl + Shift + Enter. Nếu bạn chọn công thức bình thường, hãy nhấn phím Enter như thường lệ. – Sao chép công thức xuống càng xa càng tốt bằng cách kéo chốt xử lý. Vì cả hai công thức giá trị duy nhất được gói gọn trong hàm IFERROR, bạn có thể sao chép công thức đến cuối bảng mà không làm lộn xộn dữ liệu vì bất kỳ lỗi nào dù các giá trị duy nhất đã được trích xuất ra sao.

Cách lọc ra các giá trị khác biệt trong Excel (duy nhất + những dữ liệu giống nhau sẽ chỉ xuất hiện một lần)

Để có được một danh sách các giá trị khác biệt trong Excel, ta sử dụng công thức sau.

Công thức mảng để tìm những giá trị khác biệt (nhấn Ctrl + Shift + Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”)

Công thức chuẩn để tìm những giá trị khác biệt

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), “”)

Công thức trích xuất các giá trị khác biệt không bao gồm khoảng trống:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF($A$2:$A$10=””,1,0), 0)), “”)

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), “”)

Trong đó, A2: A10 là danh sách nguồn, và B1 là ô nằm ngay trên ô đầu tiên của danh sách riêng biệt.

Cách trích xuất các giá trị khác nhau phân biệt chữ hoa chữ thường trong Excel

Khi làm việc với những dữ liệu nhạy cảm như trong trường hợp như mật khẩu, tên người dùng hoặc tên tệp, bạn sẽ cần phải có danh sách các giá trị khác biệt nhạy cảm với từng chữ. Khi đó, bạn có thể sử dụng công thức mảng sau đây, với A2:A10 là danh sách nguồn, và B1 là ô nằm trên ô đầu tiên của danh sách riêng biệt:

Công thức mảng để nhận các giá trị riêng biệt theo từng trường hợp (nhấn Ctrl + Shift + Enter) Cách hoạt động của công thức trích xuất các giá trị duy nhất/ khác biệt

Phần này được viết riêng cho những người muốn hiểu rõ về những công thức này một cách cụ thể nhất.

Việc trích xuất các giá trị duy nhất và khác biệt trong Excel thực sự không phải một thao tác đơn giản. Nhưng bạn có thể thấy rằng tất cả các công thức đều được dựa trên cùng một cách tiếp cận – sử dụng INDEX / MATCH kết hợp với hàm COUNTIF, hoặc COUNTIF + IF.

Và bây giờ, chúng ta cùng tìm hiểu về những phần cốt lõi trong công thức tìm giá trị khác biệt:

Cụ thể, trong ô B2, COUNTIF($B$1:B1, $A$2:$A$10 trở thành:

COUNTIF(“Distinct”, {“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”})

Ở đây, không có mục nào trong danh sách nguồn (tiêu chí) xuất hiện trong phạm vi phù hợp với điều kiện tìm. Trong trường hợp này, phạm vi ($B$1:B1) bao gồm một mục duy nhất – “Distinct”. 2. MATCH (lookup_value, lookup_array, [match_type]) trả về vị trí tương đối của giá trị đang tìm trong mảng. Trong ví dụ này, lookup_value là 0, nên ta có: MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)

MATCH(0, {0;0;0;0;0;0;0;0;0},0)

Bởi vì chức năng MATCH nhận về giá trị đầu tiên cũng chính là giá trị bằng với giá trị đang đang tìm kiếm (mà giá trị đang tìm là 0). 3. INDEX(array, row_num, [column_num]) trả về một giá trị nằm trong một mảng dựa trên hàng đã được chỉ định và những số cột (tùy ý). Trong ví dụ này, INDEX($A$2:$A$10, 1)

INDEX({“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”}, 1)

Và trả về “Ronnie”.

Khi công thức được sa chép xuống cột, danh sách các giá trị khác biệt ($B$1:B1) sẽ mở rộng hơn vì tham chiếu thêm ô thứ hai (B1) là một tham chiếu tương đối – thay đổi theo vị trí tương đối của ô khi công thức di chuyển sang ô khác.

Do đó, khi sao chép công thức sang ô B3, COUNTIF($B$1:B1, $A$2:$A$10) thay đổi thành COUNTIF($B$1:B2, $A$2:$A$10), và trở thành:

COUNTIF({“Distinct”;”Ronnie”}, {“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”}), 0)), “”)

Vì có một “Ronnie” được tìm thấy trong phạm vi $B$1:B2.

Các giá trị khác biệt bỏ qua khoảng trắng – ở đây bạn thêm một hàm IF để ngăn không cho các ô trống được thêm vào danh sách riêng biệt: IF($A$2:$A$13=””,1,0).

Các giá trị văn bản khác biệt bỏ qua các con số – bạn sử dụng chức năng ISTEXT để kiểm tra xem giá trị có phải là văn bản hay không, và chức năng IF để loại bỏ tất cả các loại giá trị khác, bao gồm các ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Trích xuất những hàng duy nhất và khác biệt sử dụng Duplicate Remover Giờ chúng ta sẽ cùng xem hoạt động của công cụ Duplicate Remover.

Giả sử bạn có một bảng tóm tắt được tạo ra bằng cách hợp nhất dữ liệu từ một số bảng khác. Có thể thấy rõ ràng rằng bảng tóm tắt chứa rất nhiều hàng trùng lặp và nhiệm vụ của bạn là trích xuất hàng duy nhất xuất hiện trong bảng chỉ một lần hoặc các hàng khác biệt bao gồm những giá trị xuất hiện duy nhất một lần và một lần xuất hiện chung cho những giá trị trùng lặp. Sử dụng Duplicate Remover công việc sẽ được hoàn tất trong 5 bước sau:

Chọn loại giá trị bạn muốn tìm và nhấp vào Next:

Phụ thuộc vào nhu cầu trích xuất dữ liệu của bạn để chọn Unique (duy nhất) hoặc Unique +1st occurrences (distinct) (Những giá trị duy nhất và một lần xuất hiện chung cho những giá trị trùng lặp).

Mẹo: Như bạn đã thấy trong ảnh chụp màn hình ở trên, có hai lựa chọn dành cho những giá trị xuất hiện nhiều lần, nhưng bạn cần nhớ là không được nhầm lẫn với những bảng tính khác.

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện

COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện

Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…

Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

Định dạng theo điều kiện với Conditional formatting

Thiết lập điều kiện nhập dữ liệu với Data Validation

Cách đặt Name và sử dụng Name trong công thức

Lập báo cáo với Pivot Table…

Cách Đếm Các Giá Trị Khác Biệt Và Duy Nhất Trong Excel

Trong hướng dẫn này, Blog Học Excel Online sẽ hướng dẫn các bạn cách đếm các giá trị duy nhất trong Excel bằng công thức và cách đếm tự động các giá trị khác biệt trong bảng. Ngoài ra, Học Excel Online cũng sẽ đề cập đến một số công thức ví dụ để đếm các tên, văn bản, số, giá trị phân biệt chữ hoa và chữ thường, ….

Khi làm việc với một tập dữ liệu lớn trong Excel, bạn có thể sẽ cần phải biết có bao nhiêu giá trị trùng lặp và duy nhất ở đó. Và đôi khi, bạn có thể muốn chỉ đếm riêng các giá trị riêng biệt (khác nhau).

Trước tiên hãy nhắc lại một số định nghĩa cần biết

Giá trị duy nhất – đây là các giá trị xuất hiện trong danh sách duy nhất một lần. Giá trị khác biệt – tất cả các giá trị khác nhau trong danh sách, nghĩa là giá trị duy nhất cộng với lần xuất hiện lần đầu tiên đại diện cho các giá trị trùng lặp. Ảnh chụp màn hình sau đây thể hiện sự khác biệt:

Và bây giờ, hãy xem cách bạn có thể đếm các giá trị duy nhất và khác biệt trong Excel sử dụng các công thức.

Cách đếm các giá trị duy nhất trong Excel Đếm các giá trị duy nhất trong một cột

Giả sử bạn có một cột tên trong bảng tính Excel của mình và bạn cần đếm các tên duy nhất trong cột đó. Giải pháp là sử dụng hàm SUM kết hợp với IF và COUNTIF:

Trong ví dụ này, chúng ta đang đếm tên duy nhất trong phạm vi A2: A10, vì vậy công thức của chúng ta có dạng sau:

Cách hoạt động của công thức đếm các giá trị duy nhất trong Excel

Như bạn thấy, 3 chức năng khác nhau được sử dụng trong công thức tìm giá trị duy nhất là – SUM, IF và COUNTIF. Nhìn một cách cụ thể ta có:

Đếm các giá trị duy nhất dạng văn bản trong Excel

=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))

Như bạn thấy trong ảnh chụp màn hình ở trên, công thức trả về tổng số giá trị văn bản duy nhất, trừ ô trống, số, giá trị logic của TRUE và FALSE, và lỗi.

Đếm các giá trị số duy nhất trong Excel

Để đếm các giá trị số duy nhất trong một danh sách dữ liệu, hãy sử dụng công thức mảng như chúng ta đã sử dụng để đếm các giá trị văn bản duy nhất với sự khác biệt duy nhất là bạn sử dụng ISNUMBER thay vì ISTEXT trong công thức giá trị duy nhất:

Đếm các giá trị duy nhất phân biệt chữ hoa chữ thường trong Excel

Nếu bảng của bạn có chứa dữ liệu phân biệt chữ hoa chữ thường, cách đơn giản nhất để đếm các giá trị duy nhất là tạo ra một cột trợ giúp với công thức mảng sau để xác định các mục trùng lặp và duy nhất:

=IF(SUM((-EXACT($A$2:$A$10,A2)))=1,”Unique”,”Dupe”)

Và sau đó, sử dụng một hàm COUNTIF đơn giản để tính các giá trị duy nhất:

Đếm các giá trị khác biệt trong Excel (giá trị duy nhất và 1 xuất hiện đại diện cho các giá trị trùng lặp)

Để có được một số các giá trị khác biệt trong một danh sách, ta sử dụng công thức sau:

Ngoài ra, bạn có thể sử dụng chức năng SUMPRODUCT và hoàn thành công thức theo cách thông thường bằng cách nhấn phím Enter:

=SUM(1/COUNTIF(A2:A10,A2:A10))

Cách hoạt động của công thức giá trị khác biệt của Excel

Như bạn đã biết, chúng ta sử dụng hàm COUNTIF để tìm ra số lần mỗi giá trị xuất hiện trong phạm vi được chỉ định. Trong ví dụ trên, kết quả của hàm COUNTIF là mảng sau: {2;2;3;1;2;2;3;1;3}.

Sau đó, một số hoạt động phân chia được thực hiện, trong đó mỗi giá trị của mảng được sử dụng như một số chia với 1 là cổ tức. Điều này sẽ biến tất cả các giá trị trùng lặp thành các số phân số tương ứng với số lần xuất hiện trùng lặp. Ví dụ, nếu một giá trị xuất hiện 2 lần trong danh sách, nó tạo ra 2 mục trong mảng với một giá trị là 0.5 (1/2=0.5). Và nếu giá trị xuất hiện 3 lần, nó sẽ tạo ra 3 mục trong mảng với giá trị là 0.3(3). Trong ví dụ của chúng ta, kết quả của 1/COUNTIF(A2:A10,A2:A10)) là mảng {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}.

Khi một trong các hàm này cộng thêm các giá trị trong mảng, tổng của tất cả các số phân số cho mỗi mục riêng lẻ luôn là 1, cho dù có bao nhiêu lần xuất hiện của mục đó tồn tại trong danh sách. Và bởi vì tất cả các giá trị duy nhất xuất hiện trong mảng là lần đại diện duy nhất (1/1=1), kết quả cuối cùng được trả về bởi công thức là tổng số các giá trị khác nhau trong danh sách

Công thức đếm các giá trị khác biệt theo những cách khác nhau

Cũng như trường hợp đếm các giá trị duy nhất trong Excel, bạn có thể sử dụng các biến thể của công thức đếm số Excel cơ bản để xử lý các loại giá trị cụ thể như số, văn bản và các giá trị phân biệt chữ hoa và chữ thường.

Đếm các giá trị khác biệt bỏ qua ô trống Công thức đếm các giá trị văn bản khác biệt

Để đếm các giá trị văn bản khác biệt trong một cột, chúng ta sẽ sử dụng phương pháp tương tự mà đã sử dụng để loại trừ các ô trống.

Như bạn có thể dễ dàng đoán được, chúng ta chỉ đơn giản là thêm hàm ISTEXT vào công thức giá trị khác biệt trong Excel:

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

Công thức đếm các giá trị số khác biệt

Để đếm các giá trị số khác biệt (số, ngày tháng và thời gian), ta sử dụng hàm ISNUMBER:

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

Đếm các giá trị khác biệt phân biệt chữ hoa chữ thường trong Excel

Tương tự như đếm các giá trị duy nhất có phân biệt chữ hoa chữ thường, cách đơn giản nhất để đếm các giá trị khác biệt phân biệt chữ hoa chữ thường là thêm cột trợ giúp bằng công thức mảng xác định các giá trị duy nhất kể cả lần xuất hiện trùng lặp đầu tiên. Công thức này về cơ bản giống như công thức mà chúng ta đã sử dụng để tính các giá trị duy nhất phân biệt chữ hoa chữ thường, với một thay đổi nhỏ trong một tham chiếu ô nhưng lại tạo ra sự khác biệt lớn:

=IF(SUM((-EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Tất nhiên, vẫn phải nhớ rằng tất cả các công thức mảng trong Excel yêu cầu nhấn Ctrl + Shift + Enter.

Đếm các hàng duy nhất và khác biệt trong Excel

Đếm các hàng duy nhất / khác biệt trong Excel giống như việc đếm các giá trị duy nhất và riêng biệt, với sự khác biệt duy nhất là bạn sử dụng hàm COUNTIFS thay vì COUNTIF, hàm này cho phép bạn chỉ định một vài cột để kiểm tra các giá trị duy nhất.

Ví dụ: để đếm các tên duy nhất hoặc khác biệt dựa trên các giá trị trong cột A (First Name) và B (Last Name), hãy sử dụng một trong các công thức sau:

Công thức đếm các hàng duy nhất

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

Công thức đếm các hàng khác biệt

Để có thể biết thêm nhiều hàm nâng cao trong Excel cũng như sử dụng tốt hơn các công cụ của Excel, các bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới nâng cao của Học Excel Online. Đây là khóa học chi tiết và đầy đủ nhất, giúp bạn phát triển kiến thức một cách đầy đủ, có hệ thống. Đặc biệt khóa học này không giới hạn thời gian học tập, nên bạn có thể học thoải mái và xem lại bất cứ khi nào.

Hàm Small Của Excel Để Tìm Và Đánh Dấu Các Giá Trị Thấp Nhất

Trong hướng dẫn ngắn này, chúng ta sẽ nói về hàm SMALL của Excel, cách hoạt động và cách sử dụng nó để tìm số, ngày hoặc giờ nhỏ nhất thứ N.

Bạn cần tìm một vài số thấp nhất trong một trang tính? Điều này khá dễ thực hiện với tính năng Sắp xếp trong Excel. Bạn không muốn lãng phí thời gian vào việc sắp xếp lại dữ liệu của mình với mỗi lần thay đổi? Hàm SMALL sẽ giúp bạn nhanh chóng tìm ra giá trị nhỏ nhất, nhỏ nhất thứ hai, nhỏ nhất thứ ba, v.v.

Hàm SMALL của Excel

SMALL là một hàm thống kê trả về giá trị nhỏ nhất thứ n trong một tập dữ liệu.

Cú pháp của hàm SMALL bao gồm hai đối số, cả hai đều là bắt buộc.

Small (mảng, k)

Ở đâu:

Mảng – một mảng hoặc một dải ô để trích xuất giá trị nhỏ nhất.

K – một số nguyên cho biết vị trí từ giá trị thấp nhất trở về, tức là nhỏ nhất thứ k.

Hàm có sẵn trong tất cả các phiên bản Excel cho Office 365, Excel 2024, Excel 2024, Excel 2013, Excel 2010 và các phiên bản cũ hơn.

Công thức Small cơ bản trong Excel

Một công thức Small ở dạng cơ bản rất dễ xây dựng – bạn chỉ cần chỉ định phạm vi và vị trí từ mục nhỏ nhất để trả về.

Trong danh sách các số trong B2: B10, giả sử bạn muốn trích xuất 3 rd giá trị nhỏ nhất. Công thức đơn giản như sau:

=SMALL(B2:B10, 3)

Để giúp bạn kiểm tra kết quả dễ dàng hơn, cột B được sắp xếp theo thứ tự tăng dần:

4 điều bạn nên biết về hàm Small

Những lưu ý sử dụng sau đây sẽ giúp bạn hiểu rõ hơn về hoạt động của hàm SMALL và tránh nhầm lẫn khi xây dựng công thức cho riêng mình.

Bất kì ô trống, bản văn giá trị và hợp lý giá trị TRUE và FALSE trong mảng đối số bị bỏ qua.

Nếu mảng chứa một hoặc nhiều sai sót, một lỗi được trả lại.

Trong trường hợp có trùng lặp trong mảng, công thức của bạn có thể dẫn đến “ràng buộc”. Ví dụ: nếu hai ô chứa số 1 và hàm SMALL được cấu hình để trả về giá trị nhỏ nhất và số 2nd giá trị nhỏ nhất, bạn sẽ nhận được 1 trong cả hai trường hợp.

Giả sử n là số giá trị trong mảng, SMALL (mảng, 1) sẽ trả về giá trị thấp nhất và SMALL (mảng, n) sẽ chọn giá trị cao nhất.

Cách sử dụng hàm SMALL trong Excel – ví dụ về công thức

Và bây giờ, chúng ta hãy xem xét thêm một số ví dụ về hàm SMALL của Excel vượt ra ngoài cách sử dụng cơ bản của nó.

Tìm các giá trị 3, 5, 10, v.v.

Như bạn đã biết, hàm SMALL được thiết kế để tính giá trị thấp nhất thứ n. Ví dụ này cho thấy cách làm điều này hiệu quả nhất.

=SMALL($B$2:$B$10, D3)

Trong E3, công thức trích ra giá trị nhỏ nhất bằng cách sử dụng số trong D3 cho k tranh luận. Điều quan trọng là cung cấp các tham chiếu ô thích hợp để công thức sao chép chính xác trong các ô khác: tuyệt đối cho mảng và quan hệ cho k.

Bạn không muốn làm phiền gõ các cấp bậc theo cách thủ công? Sử dụng chức năng ROWS với mở rộng phạm vi tham chiếu để cung cấp k giá trị. Đối với điều này, chúng tôi thực hiện một tham chiếu tuyệt đối cho ô đầu tiên (hoặc chỉ khóa tọa độ hàng như B $ 2) và tham khảo tương đối cho ô cuối cùng:

=SMALL($B$2:$B$10, ROWS(B$2:B2))

Do đó, tham chiếu phạm vi mở rộng khi công thức được sao chép xuống cột. Trong D2, ROWS (B $ 2: B2) tạo ra 1 cho kvà công thức trả về chi phí thấp nhất. Trong D3, ROWS (B $ 2: B3) mang lại 2 và chúng tôi nhận được 2 nd chi phí thấp nhất, v.v.

Chỉ cần sao chép công thức qua 5 ô và bạn sẽ nhận được 5 giá trị dưới cùng:

Tính tổng N giá trị dưới cùng

Muốn tìm tổng các giá trị n nhỏ nhất trong một tập dữ liệu? Nếu bạn đã trích xuất các giá trị như được hiển thị trong ví dụ trước, giải pháp đơn giản nhất sẽ là TỔNG công thức như:

Hoặc bạn có thể tạo một công thức độc lập bằng cách sử dụng hàm SMALL cùng với GIỚI THIỆU:

SUMPRODUCT (Small ( mảng, {1,…, n}))

Để nhận tổng của 3 giá trị dưới cùng trong tập dữ liệu của chúng tôi, công thức có dạng sau:

=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))

Hàm SUM sẽ cho kết quả tương tự:

Ghi chú. Nếu bạn dùng tham chiếu ô thay vì mảng hằng số cho k, bạn cần nhấn Ctrl + Shift + Enter để biến nó thành một công thức mảng. Trong Excel 365 hỗ trợ mảng động, SUM SMALL hoạt động như một công thức thông thường trong cả hai trường hợp.

=SUM(SMALL(B2:B10, {1,2,3}))

Trong một công thức thông thường, SMALL trả về một giá trị nhỏ nhất thứ k trong một phạm vi. Trong trường hợp này, chúng tôi cung cấp một mảng hằng số như {1,2,3} cho đối số k, buộc nó trả về một mảng gồm 3 giá trị nhỏ nhất:

Công thức này hoạt động như thế nào:

Hàm SUMPRODUCT hoặc SUM cộng các số trong mảng và xuất ra tổng. Đó là nó!

{29240, 43610, 58860}

Công thức INDEX MATCH SMALL để nhận các kết quả phù hợp nhỏ nhất

Trong trường hợp bạn muốn truy xuất một số dữ liệu được liên kết với giá trị nhỏ nhất, hãy sử dụng INDEX MATCH kết hợp với SMALL cho giá trị tra cứu:

INDEX( return_array, MATCH (Small ( lookup_array, n), lookup_array, 0))

Ở đâu:

Lookup_array là một phạm vi để tìm kiếm giá trị thứ n thấp nhất.

N là vị trí của giá trị nhỏ nhất cần quan tâm.

Ví dụ, để lấy tên của dự án có chi phí thấp nhất, công thức trong E3 là:

Trong đó A2: A10 là tên dự án, B2: B10 là chi phí và D3 là thứ hạng từ nhỏ nhất.

=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))

Sao chép công thức vào các ô bên dưới (E4 và E5), và bạn sẽ nhận được tên của 3 dự án rẻ nhất:

Giải pháp này hoạt động tốt cho một tập dữ liệu không có bản sao. Tuy nhiên, hai hoặc nhiều giá trị trùng lặp trong một cột số có thể tạo ra “ràng buộc” trong xếp hạng, dẫn đến kết quả sai. Trong trường hợp này, hãy sử dụng tinh vi hơn một chút công thức để phá vỡ quan hệ.

Trong Excel 365, tác vụ này có thể được thực hiện với sự trợ giúp của các hàm mảng động mới. Ngoài việc đơn giản hơn nhiều, cách tiếp cận này tự động giải quyết vấn đề ràng buộc. Để biết chi tiết đầy đủ, vui lòng xem Cách lấy giá trị N dưới cùng trong Excel.

Ghi chú:

Sắp xếp các số từ thấp nhất đến cao nhất với một công thức

Tôi tin rằng mọi người đều biết cách đặt các con số theo thứ tự Sắp xếp Excel đặc tính. Nhưng bạn có biết cách thực hiện sắp xếp theo công thức không? Người dùng Excel 365 có thể làm điều đó một cách dễ dàng với Hàm SORT. Trong Excel 2024, 2024 và các phiên bản trước đó, SORT không hoạt động, than ôi. Nhưng có một chút niềm tin, và Small sẽ đến giải cứu 🙂

Giống như trong ví dụ đầu tiên, chúng tôi sử dụng hàm ROWS với tham chiếu phạm vi mở rộng để tăng k bởi 1 trong mỗi hàng nơi công thức được sao chép:

Nhập công thức vào ô đầu tiên, sau đó kéo công thức xuống bao nhiêu ô có giá trị trong tập dữ liệu gốc (trong ví dụ này là C2: C10):

=SMALL($A$2:$A$10, ROWS(A$2:A2))

Tiền boa. Xắp xếp giảm dần, sử dụng hàm LARGE thay vì SMALL.

Vì ngày và giờ cũng là giá trị số (trong hệ thống Excel nội bộ, ngày được lưu trữ dưới dạng số tuần tự và lần dưới dạng phân số thập phân), hàm SMALL cũng có thể xử lý chúng mà không cần thêm bất kỳ nỗ lực nào từ phía bạn.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, một công thức cơ bản mà chúng tôi đã sử dụng cho các con số cũng hoạt động tốt cho ngày và giờ:

Công thức Small để tìm 3 ngày sớm nhất:

=SMALL($B$2:$B$10, D2)

Công thức Small để nhận được 3 lần ngắn nhất:

Trong danh sách ngày tháng, giả sử bạn muốn tìm ngày gần nhất trước một ngày cụ thể. Điều này có thể được thực hiện bằng cách sử dụng hàm SMALL kết hợp với COUNTIF.

Với danh sách các ngày trong B2: B10 và ngày mục tiêu trong E1, công thức sau sẽ trả về một ngày trước đó gần với ngày mục tiêu nhất:

Để trích xuất một ngày hai ngày trước ngày trong E1, tức là một ngày trước đó nhưng một ngày, công thức là:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)) =SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1)

Để tìm một ngày trong quá khứ gần nhất với ngày hôm nay, sử dụng Hàm TODAY cho tiêu chí của COUNTIF:

Tiền boa. Để tránh lỗi trong trường hợp không tìm thấy ngày phù hợp với tiêu chí của bạn, bạn có thể kết thúc Hàm IFERROR xung quanh công thức của bạn, như sau:

=SMALL(B2:B10, COUNTIF(B2:B10, "<"&TODAY()))

Ý tưởng chung là đếm số ngày nhỏ hơn ngày mục tiêu bằng COUNTIF. Và số lượng này chính xác là những gì hàm SMALL cần cho k tranh luận.

Để hiểu rõ hơn về khái niệm này, chúng ta hãy xem xét nó từ một góc độ khác:

=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<"&E1)-1), "Not Found")

Nếu ngày 1 tháng 8 năm 2024 (ngày mục tiêu ở E1) xuất hiện trong tập dữ liệu của chúng tôi, thì đó sẽ là ngày 7 thứ tự ngày lớn nhất trong danh sách. Do đó, có sáu ngày nhỏ hơn nó. Có nghĩa là 6 thứ tự ngày nhỏ nhất là ngày trước đó gần nhất với ngày mục tiêu.

Cách hoạt động của các công thức này:

Vì vậy, trước tiên, chúng tôi tính toán xem có bao nhiêu ngày nhỏ hơn ngày trong E1 (kết quả là 6):

Và sau đó, cắm số đếm vào 2 nd đối số của Small:

Để có được ngày trước nhưng một ngày (là 5 thứ tự ngày nhỏ nhất trong trường hợp của chúng tôi), chúng tôi trừ 1 từ kết quả của COUNTIF.

COUNTIF(B2:B10, "<"&E1)

Cách tô sáng các giá trị dưới cùng trong Excel

=SMALL(B2:B10, 6)

Chọn phạm vi mà bạn muốn đánh dấu các giá trị dưới cùng. Trong trường hợp của chúng tôi, các số ở B2: B10, vì vậy chúng tôi chọn nó. Nếu bạn muốn đánh dấu toàn bộ hàng, hãy chọn A2: B10.

bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng.

bên trong Định dạng các giá trị trong đó công thức này đúng , hãy nhập một công thức như sau:=B2<=SMALL($B$2:$B$10, 3)Trong đó B2 là ô ngoài cùng bên trái của phạm vi số sẽ được kiểm tra, $ B $ 2: $ B $ 10 là toàn bộ phạm vi và 3 là n giá trị dưới cùng để đánh dấu.Trong công thức của bạn, hãy lưu ý đến các loại tham chiếu: ô ngoài cùng bên trái là tham chiếu tương đối (B2) trong khi phạm vi là tham chiếu tuyệt đối ($ B $ 2: $ B $ 10).

Nhấn vào định dạng và chọn bất kỳ định dạng nào bạn thích.

Bấm OK hai lần để đóng cả hai cửa sổ hộp thoại.

Làm xong! 3 giá trị dưới cùng trong cột B được đánh dấu:

Để biết thêm thông tin, vui lòng xem Định dạng có điều kiện trong Excel dựa trên công thức.

Hàm SMALL của Excel không hoạt động

Như bạn vừa thấy từ các ví dụ của chúng tôi, việc sử dụng hàm SMALL trong Excel khá dễ dàng và bạn không gặp khó khăn gì với nó. Nếu công thức của bạn không hoạt động, rất có thể đó sẽ là lỗi #NUM! , có thể xảy ra vì những lý do sau:

Mảng trống hoặc không chứa một giá trị số.

Các k giá trị nhỏ hơn 0 (một lỗi đánh máy ngớ ngẩn có thể khiến bạn mất hàng giờ khắc phục sự cố!) hoặc vượt quá số giá trị trong mảng.

Hàm Unique Trong Excel Lọc Giá Trị Duy Nhất Trong Vùng

Hàm Unique trong Excel xuất hiện trong phiên bản Excel trong bộ Office 365 sẽ hỗ trợ bạn trong việc lọc ra các giá trị duy nhất trong 1 hàng hoặc 1 cột hoặc 1 vùng trên bảng tính của bạn. Giá trị cần lọc duy nhất có thể ở dạng text, dạng số, dạng ngày tháng, dạng thời gian, …

Cách sử dụng hàm Unique trong Excel để lọc duy nhất

Các tham số:

array: 1 vùng, cột hoặc hàng mà bạn cần lọc ra những giá trị duy nhất

[by_col]: tham số không bắt buộc, giá trị mặc định là FALSE – sắp xếp theo hàng, TRUE – sắp xếp theo cột

[occurs_once]: tham số không bắt buộc, giá trị mặc định là FALSE – lọc lấy ra đại diện mỗi giá trị 1 lần; TRUE – lọc lấy ra giá trị chỉ xuất hiện 1 lần duy nhất trong array

Các ví dụ và giải thích

Ví dụ công thức tại ô E2 =UNIQUE(A1:I1,TRUE)

Trong ví dụ này, vùng cần lọc duy nhất là A1:I1 – 1 mảng ngang, chúng ta muốn kết quả trả về theo hàng ngang, vậy nên tham số [by_row] sẽ là TRUE hoặc 1

Ví dụ công thức tại ô D5 =UNIQUE(B5:B13)

Trong ví dụ này, vùng cần lọc duy nhất là B5:B13 – 1 mảng dọc, đây là cách sử dụng hàm Unique đơn giản nhất với tham số duy nhất trong trường hợp này là vùng cần lọc

Ví dụ công thức tại ô E5, G5 E5 =UNIQUE(B5:B13,FALSE) G5 =UNIQUE(B5:B13,,FALSE)

Trong 2 ô này, vì giá trị mặc định của tham số [by_row] và [occurs_once] đều là FALSE, vậy nên kết quả của 2 công thức trên đều đồng nhất với kết quả của công thức tại ô D5.

Ví dụ công thức tại ô F5 =UNIQUE(B5:B13,,TRUE)

Trong ô F5, công thức có sử dụng tham số thứ 3 – [occurs_once] với giá trị là TRUE, nghĩa là hàm Unique sẽ lọc ra những giá trị chỉ xuất hiện duy nhất 1 lần trong vùng B5:B13, vì vậy “Đỏ” và “Vàng” không xuất hiện trong vùng kết quả.

Ngoài ra, bạn có thể tham khảo video sau đây để có thể hiểu rõ hơn về cách hoạt động của hàm Unique trong Excel

Cập nhật thông tin chi tiết về Những Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!