Hàm lấy giá trị duy nhất trong excel

     

Trong nội dung bài viết này học Excel Online đang hướng dẫn chúng ta cách lọc ra một danh sách các giá trị riêng lẻ độc nhất trong cột bằng cách sử dụng một cách làm và cách tinh chỉnh và điều khiển công thức đó cho các bộ dữ liệu khác nhau. Ko kể ra, Học Excel Online cũng trở nên chỉ cho chúng ta cách lập cập nhất để có được một list riêng biệt bằng phương pháp sử dụng bộ lọc nâng cấp của Excel, hàm liệt kê list trong excel và làm cụ nào nhằm trích xuất các hàng duy nhất áp dụng Duplicate Remover.

Bạn đang xem: Hàm lấy giá trị duy nhất trong excel

Để lọc ra một danh sách các giá trị duy nhất trong Excel chúng ta cũng có thể sử dụng cách xác minh các giá trị duy nhất đó, thanh lọc riêng rồi xào luộc chúng. Tuy nhiên, thực hiện cách làm cho này vẫn tốn những thời gian, để tiến hành nhanh hơn, bạn cũng 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 nhưng mà Học Excel Online sẽ share với chúng ta ngay sau đây.


Xem nhanh


Cách thanh lọc ra những giá trị độc nhất vô nhị trong ExcelCách lọc ra những giá trị biệt lập trong Excel (duy tuyệt nhất + những dữ liệu giống nhau đang chỉ lộ diện một lần)Cách trích xuất các giá trị khác nhau phân biệt chữ hoa chữ thường trong ExcelCách buổi giao lưu của công thức trích xuất những giá trị duy nhất/ khác biệt

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

Để né nhầm lẫn, đầu tiên, bọn họ cần hiểu phần đông giá trị làm sao được điện thoại tư vấn là là các giá trị duy nhất trong Excel. Cực hiếm duy tuyệt nhất là những giá trị chỉ tồn tại độc nhất vô nhị một lần trong cục bộ danh sách. Ví dụ:

*

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

Công thức mảng để tìm ra phần lớn giá trị nhất (hoàn thành công xuất sắc thức ngừng ta nhấn Ctrl + Shift + Enter):

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

Công thức cực hiếm duy nhất tiếp tục (hoàn thành công thức xong xuôi ta nhận Enter):

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

Trong đó:

A2: A10 – danh sách nguồn B1 – ô đầu của danh sách duy độc nhất trừ đi 1. Trong ví dụ này, bọn chúng ta bước đầu danh sách các giá trị tốt nhất ở B2, và vày đó họ đưa B1 vào phương pháp (B2-1 = B1). Giả dụ danh sách những giá trị duy nhất của bạn bước đầu ở ô C3, ta sẽ thay đổi $B$1:B1 thành $C$2:C2.


*

Chú thích. Bởi vì công thức tham chiếu ô bên trên ô thứ nhất của danh sách những giá trị duy nhất, hay là tiêu đề cột (như trong lấy ví dụ như 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 độc nhất không lộ diện ở bất cứ đâu trong cột.

Ở lấy ví dụ này đang trích xuất các tên độc nhất từ cột A (chính xác hơn là trong phạm vi A2: A20), và hình ảnh chụp màn hình hiển thị sau sẽ minh họa cho bí quyết sẽ dùng:

Dưới đấy là giải thích hợp một cách chi tiết về việc áp dụng công thức nhằm trích xuất các giá trị độc nhất trong bảng tính Excel của bạn:

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

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

Như bạn đã đoán ra từ tiêu đề của phần này, những giá trị khác hoàn toàn trong Excel là toàn bộ các giá chỉ trị khác biệt trong danh sách, tức là những quý hiếm đó chỉ xuất hiện một lần nhất dù nó có bao nhiêu nhân bạn dạng đi chăng nữa. Ví dụ:

Để đạt được một danh sách những 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 đông đảo 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 phần lớn 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)), “”)

Trong đó:

A2:A10 là danh sách nguồn B1 là ô nằm phía trên của danh sách các giá trị khác biệt. Trong ví dụ như này, danh sách những giá trị không giống biệt bắt đầu ở ô B2 (đây là ô đầu tiên bạn nhập công thức).

Trích xuất những giá trị khác biệt vào trong một cột và vứt qua các ô trống Nếu danh sách nguồn của bạn chứa ô trống, thì những công thức bọn chúng ra vừa nêu ngơi nghỉ trên đang trả về một vài không cho từng hàng trống. Để khắc phục và hạn chế điều này, họ cần cải tiến công thức thêm 1 chút.

Học lập trình sẵn VBA vào Excel sống đâu?

Công thức trích xuất các giá trị khác hoàn toàn không bao hà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)), “”)

Trích xuất danh sách các giá trị văn phiên bản riêng biệt bỏ qua mất số và khoảng không Theo giải pháp tương tự, bạn cũng có thể nhận được một danh sách những giá trị đơn nhất trừ các ô cùng ô trống có chứa số:

=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à list nguồn, và B1 là ô nằm ở trên ô đầu tiên của list riêng biệt.

Ảnh chụp màn hình hiển thị sau đây cho thấy thêm kết quả của cả hai công thức trên:

Cách trích xuất những giá trị khác biệt 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 phù hợp như mật khẩu, tên người tiêu dùng hoặc thương hiệu tệp, các bạn sẽ cần phải gồm danh sách những giá trị khác biệt nhạy cảm cùng với từng chữ. Lúc đó, bạn cũng có thể sử dụng công thức mảng sau đây, cùng với A2:A10 là list nguồn, với B1 là ô vị trí ô đầu tiên của list riêng biệt:

Công thức mảng nhằm nhận các giá trị riêng lẻ theo từng trường vừa lòng (nhấn Ctrl + Shift + Enter)

=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), “”), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), “”)

Cách hoạt động của công thức trích xuất các giá trị duy nhất/ không giống biệt

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

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

Để đối chiếu sâu hơn, bọn họ sẽ cùng sử dụng công thức mảng nhằm trích ra một danh sách các giá trị khác biệt bởi vì toàn bộ các phương pháp khác được bàn bạc trong lí giải này là những công thức đang được cải tiến hoặc đổi mới thể từ bí quyết cơ bạn dạng sau:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”) Đối với những người mới bắt đầu, chúng ta có thể bỏ qua hàm IFERROR, hàm này được sử dụng với một mục tiêu duy độc nhất để loại trừ lỗi # N/A khi số ô mà bạn đã xào nấu công thức vượt quá con số các giá bán trị biệt lập trong danh sách nguồn.

Và bây giờ, chúng ta cùng khám phá về đông đảo phần cơ bản trong bí quyết tìm cực hiếm khác biệt:

1. COUNTIF(range, criteria) hàm này đã trả về số ô trong một phạm vi thỏa mãn nhu cầu được điều kiện cụ thể. Như làm việc trong lấy một ví dụ này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 và 0 dựa trên bất kỳ giá trị nào trong danh sách nguồn ($A$2:$A$10) xuất hiện thêm trong danh sách những giá trị khác biệt ($B$1:B1). Nếu quý giá được tìm thấy, bí quyết sẽ trả về 1, nếu không tìm thấy đang trả về – 0.

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

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

Và trả về:

0;0;0;0;0;0;0;0;0

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

trở thành:

MATCH(0, 0;0;0;0;0;0;0;0;0,0)

Và trả về 1

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

Trở thành:

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

Và trả về “Ronnie”.

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

Xem thêm: Bán Card Điện Thoại Có Lời Không ? Bán Thẻ Cào

Do đó, khi coppy công thức lịch sự ô B3, COUNTIF($B$1:B1, $A$2:$A$10) chuyển đổi thành COUNTIF($B$1:B2, $A$2:$A$10), với trở thành:

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

trả về:

1;0;0;0;0;0;0;0;0

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

Từ đó, MATCH(0,1;0;0;0;0;0;0;0;0,0) trả về 2, bởi vì 2 là vị trí kha khá của số 0 trước tiên trong mảng. Cuối cùng, INDEX($A$2:$A$10, 2) trả về quý giá từ hàng sản phẩm công nghệ 2, sẽ là “David”.

Mẹo: để làm rõ hơn vì xúc tích của công thức, bạn có thể chọn các phần không giống nhau của bí quyết trong thanh bí quyết và dìm f9 nhằm xem review về phần đó: Như sẽ đề cập, những công thức khác được thảo luận trong bài hướng dẫn này dựa trên xúc tích tương tự, nhưng tất cả thêm chút chũm đổi:

Công thức giá trị duy độc nhất – chứa thêm một hàm COUNTIF sa thải những mục lộ diện nhiều lần trong danh sách nguồn khỏi danh sách các giá trị duy nhất: khỏi list duy nhất toàn bộ các mục lộ diện trong list nguồn các lần: COUNTIF($A$2:$A$10, $A$2:$A$10)1.

Các giá bán trị biệt lập bỏ qua khoảng trắng – tại đâ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 phiên bản khác biệt bỏ qua các con số – chúng ta sử dụng tính năng ISTEXT để soát sổ xem giá trị liệu có phải là văn bản hay không, và công dụng IF để loại bỏ tất cả những loại cực hiếm khác, bao gồm các ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Trích xuất các giá trị khác biệt từ một cột thực hiện Bộ lọc nâng cấp – Advanced Filter của Excel nếu như khách hàng không ý muốn lãng phí thời gian để hiểu cặn kẽ các công thức giá trị khác biệt, bạn có thể nhanh chóng đạt được danh sách các giá trị không giống biệt bằng phương pháp sử dụng cỗ lọc nâng cao bằng phương pháp thực hiện công việc chi tiết bên dưới đây.

1. Lựa chọn cột dữ liệu bạn có nhu cầu trích xuất những giá trị khác biệt. 2. Gửi sang tab Data > Sort và Filter với nhấp vào nút Advanced: 3. Trong hộp thoại Advanced Filter, các bạn chọn các tuỳ lựa chọn sau: – kiểm tra nút Copy to lớn another location. – vào hộp menu range, xác minh rằng phạm vi mối cung cấp được hiển thị bao gồm xác. – Trong vỏ hộp Copy to, nhập phạm vi trên cùng của dải ô bạn muốn sao chép tài liệu sang. Chú ý là các bạn chỉ hoàn toàn có thể sao chép tài liệu đã được lọc vào trang tính vẫn hoạt động. – Chọn unique records only.

*
4. Cuối cùng, bạn nhấn OK và chất vấn lại kết quả: chúng ta cần để ý rằng, dù tùy lựa chọn của Advanced Filter được lấy tên là “Unique records only” (Chỉ gồm những phiên bản ghi duy nhất) tuy vậy nó vẫn trích xuất các giá trị khác biệt, nghĩa là bao gồm các cực hiếm chỉ mở ra một lần duy nhất và một lần xuất hiện chung cho hồ hết giá trị trùng lặp.

Trích xuất những hàng nhất và khác hoàn toàn sử dụng Duplicate Remover

Trong phần sau cuối của bài hướng dẫn này, học tập Excel Online sẽ chỉ mang lại bạn giải pháp tìm cùng trích xuất các giá trị đơn nhất và độc nhất trong số trang tính của Excel. Giải pháp này phối hợp linh hoạt các công thức Excel và một vài ứng dụng đơn giản dễ dàng của advanced filter. Ngoại trừ ra, giải pháp này còn cung ứng một số bản lĩnh rất lạ mắt như:

– Tìm cùng trích xuất các hàng độc nhất / khác biệt dựa trên những giá trị vào một hoặc nhiều cột. – Tìm, khắc ghi và coppy giá trị nhất đến bất kỳ vị trí nào khác, trong và một bảng tính hoặc quý phái bảng tính khác.

Giờ chúng ta sẽ cùng xem buổi giao lưu của công cố Duplicate Remover.

Giả sử bạn có một bảng tóm tắt được chế tạo ra bằng phương pháp hợp nhất tài liệu từ một trong những bảng khác. Rất có thể thấy cụ thể rằng bảng tóm tắt chứa không ít hàng trùng lặp với nhiệm vụ của công ty là trích xuất sản phẩm duy nhất xuất hiện thêm trong bảng có một lần hoặc các hàng không giống biệt bao gồm những giá trị mở ra duy độc nhất một lần và một lần xuất hiện chung cho phần đông giá trị trùng lặp. Thực hiện Duplicate Remover các bước sẽ được hoàn toàn trong 5 cách sau:

1. Chọn ngẫu nhiên ô nào trong bảng nguồn của công ty và nhấp nút Duplicate Remover trên tab Ablebits Data, trong team Dedupe. thủ thuật Duplicate Remover sẽ chạy và chọn cục bộ bảng. Bởi vì vậy, chỉ cần nhấp vào Next để liên tục bước tiếp theo.

Chọn loại giá trị bạn có nhu cầu tìm và nhấp vào Next:

Phụ nằm trong vào nhu yếu trích xuất dữ liệu của doanh nghiệp để chọn chất lượng (duy nhất) hoặc quality +1st occurrences (distinct) (Những quý giá duy nhất và một lần xuất hiện thêm chung cho phần lớn giá trị trùng lặp).

Như trong lấy một ví dụ này, họ muốn trích xuất những hàng lộ diện một lần độc nhất vô nhị trong bảng nguồn yêu cầu ta sẽ chọn Unique:

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

3. Chọn 1 hoặc những cột để kiểm soát lại quý hiếm duy nhất. Như trong lấy một ví dụ này, họ cần đưa ra một hàng duy nhất dựa trên những giá trị trong cả bố cột (Order number, First name và Last name), vì chưng thế bọn họ sẽ lựa chọn tất cả. 4. Chọn ra tùy lựa chọn cần vận dụng cho những giá trị duy nhất tìm được dựa trên những lựa chọn bao gồm sẵn sau đây: – Highlight quality values – Đánh giá các giá trị nhất – Select unique values – Chọn những giá trị nhất – Identify in a status column – khẳng định trong cột tâm lý – Copy to lớn another location – coppy sang địa điểm khác Vì chúng ta đang đề nghị chiết xuất những hàng duy nhất, lựa chọn Copy to another location và sau đó chỉ xác định trí đúng đắn cần coppy chúng – vị trí đó phải là 1 trong những bảng tính đang vận động (chọn tuỳ lựa chọn Custom location và chỉ định và hướng dẫn ô trên thuộc của dải đích), hoặc vị trí muốn sao chép đó đề nghị là bảng tính mới.

Trong lấy ví dụ như này, họ hãy chọn trang tính mới: 5. Chọn Finish, vì vậy là đã thực hiện xong.

Hy vọng bài bác hướng dẫn này giúp bạn cũng có thể dễ dàng và lập cập trích xuất được các giá trị tuyệt nhất hoặc những hàng bạn cần trong Excel.

Xem thêm: Hàm Lấy Thời Gian Hiện Tại Trong Excel (Tĩnh Hoặc Cập Nhật), 15 Hàm Xử Lý Date Và Time Phổ Biến Trong Excel

———

Ngoài ra để ứng dụng Excel vào công việc một cách công dụng thì chúng ta còn phải sử dụng giỏi các hàm, những công cầm khác của Excel.

Một số hàm cơ phiên bản thường chạm mặt như:

SUMIF, SUMIFS nhằm tính tổng theo 1 điều kiện, nhiều điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, nhiều điều kiệnCá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ò tra cứu tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ thường được sử dụng như:

Định dạng theo điều kiện với Conditional formattingThiết lập điều kiện nhập tài liệu với Data ValidationCách để Name và áp dụng Name vào công thứcLập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? toàn cục những kiến thức này chúng ta đều rất có thể học được vào khóa học EX101 – Excel từ bỏ cơ bạn dạng tới siêng gia của học Excel Online. Đây là khóa học giúp cho bạn hệ thống kiến thức một giải pháp đầy đủ, chi tiết. Rộng nữa không còn có số lượng giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, tiện lợi tra cứu vãn lại kiến thức khi cần. Hiện thời hệ thống đang xuất hiện ưu đãi siêu lớn cho chính mình khi đăng ký tham gia khóa học. Chi tiết xem tại: dulichnangdanang.com.Online