HÀM LẤY GIÁ TRỊ DUY NHẤT TRONG EXCEL

Trong bài viết này học Excel Online đã hướng dẫn các bạn cách lọc ra một danh sách các giá trị lẻ tẻ độc độc nhất trong cột bằng phương pháp 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. Ngoài ra, Học Excel Online cũng trở thành chỉ cho chúng ta cách lập cập nhất để sở hữu được một danh sách riêng biệt bằng cách sử dụng cỗ lọc cải thiện của Excel, hàm liệt kê danh sách trong excel và làm cố kỉnh nào để trích xuất những hàng duy nhất thực hiện 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 những giá trị độc nhất vô nhị trong Excel bạn có thể sử dụng cách xác minh các giá trị duy tốt nhất đó, lọc riêng rồi xào luộc chúng. Mặc dù nhiên, áp dụng cách có tác dụng này vẫn tốn các thời gian, để tiến hành nhanh hơn, chúng ta có thể sử dụng một công thức quan trọng đặc biệt cùng với 1 vài kỹ năng khác nhưng Học Excel Online sẽ chia sẻ với các bạn ngay sau đây.


Xem nhanh


Cách lọc ra những giá trị độc nhất trong ExcelCách thanh lọc ra những giá trị biệt lập trong Excel (duy tốt nhất + những tài liệu giống nhau vẫn 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 xuyên 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 thanh lọc ra những giá trị tuyệt nhất trong Excel

Để né nhầm lẫn, đầu tiên, chúng ta cần hiểu đa số giá trị như thế nào được call là là các giá trị tuyệt nhất trong Excel. Giá trị duy nhất là các giá trị chỉ tồn tại nhất một lần trong cục bộ danh sách. Ví dụ:

*

Để trích xuất một danh sách những giá trị tuyệt nhất trong Excel, bạn cũng 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 số đông giá trị độc nhất vô nhị (hoàn thành công thức kết thúc ta dìm 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 quý hiếm duy nhất liên tục (hoàn thành công thức xong xuôi ta dấ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 – list nguồn B1 – ô đầu của danh sách duy độc nhất trừ đi 1. Trong lấy một ví dụ này, bọn chúng ta bắt đầu danh sách các giá trị độc nhất vô nhị ở B2, và do đó bọn họ đưa B1 vào phương pháp (B2-1 = B1). Nếu danh sách những giá trị độc nhất vô nhị của bạn bắt đầu ở ô C3, ta sẽ biến đổi $B$1:B1 thành $C$2:C2.


*

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

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

Dưới đấy là giải đam mê một cách cụ thể về việc sử 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 phương pháp dựa theo tài liệu của bạn. – Nhập công thức vào ô thứ nhất của danh sách các giá trị duy nhất (trong ví dụ sẽ 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ư khách hàng chọn phương pháp bình thường, hãy dấn phím Enter như thường lệ. – xào nấu 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ý. Vì chưng cả hai bí quyết giá trị tốt nhất được gói gọn gàng trong hàm IFERROR, bạn có thể sao chép phương pháp đến cuối bảng nhưng mà không làm lộn xộn tài liệu vì bất kỳ lỗi nào dù những giá trị duy nhất đã được trích xuất ra sao.

Cách lọc ra những giá trị khác hoàn toàn trong Excel (duy độc nhất vô nhị + những tài liệu giống nhau đang chỉ mở ra một lần)

Như các bạn đã đoán ra từ tiêu đề của phần này, các giá trị biệt lập trong Excel là tất cả các giá bán trị không giống nhau trong danh sách, tức là những giá trị đó chỉ xuất hiện một lần nhất dù nó gồm 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 thực hiện công thức sau.

Công thức mảng nhằm tìm đều giá trị biệt lập (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 đầy đủ 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à list nguồn B1 là ô nằm phía trên của danh sách các giá trị khác biệt. Trong lấy ví dụ như này, danh sách các giá trị khác biệt bắt đầu ở ô B2 (đây là ô đầu tiên bạn nhập công thức).

Trích xuất các giá trị biệt lập vào trong một cột và vứt qua những ô trống Nếu danh sách nguồn của bạn chứa ô trống, thì các công thức bọn chúng ra vừa nêu làm việc trên sẽ trả về một số không cho từng hàng trống. Để hạn chế điều này, bọn họ cần đổi mới công thức thêm một chút.

Học xây dựng VBA trong Excel sống đâu?

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)), “”)

Trích xuất danh sách các giá trị văn bạn dạng riêng biệt bỏ lỡ 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 lẻ trừ những ô và ô trống tất 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à danh sách nguồn, cùng B1 là ô nằm ở trên ô đầu tiên của danh sách riêng biệt.

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

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

Khi thao tác với những tài liệu nhạy cảm như trong trường thích hợp như mật khẩu, tên người tiêu dùng hoặc tên tệp, các bạn sẽ cần phải có danh sách những giá trị khác hoàn toàn nhạy cảm cùng với từng chữ. Khi đó, chúng ta có thể sử dụng bí quyết mảng sau đây, cùng với A2:A10 là list nguồn, và B1 là ô nằm trên ô trước 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 biệt theo từng trường đúng theo (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 buổi giao lưu 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 làm rõ về những cách làm này một cách rõ ràng nhất.

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

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

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

Và bây giờ, họ cùng mày mò về đầy đủ phần chủ yếu trong phương pháp tìm giá trị khác biệt:

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

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”)

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 tương xứng với điều kiện tìm. Trong trường phù hợp này, phạm vi ($B$1:B1) gồm 1 mục tuyệt nhất – “Distinct”. 2. MATCH (lookup_value, lookup_array, ) trả về vị trí kha khá của quý giá đang kiếm tìm trong mảng. Trong ví dụ này, lookup_value là 0, đề nghị 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ì chức năng MATCH thừa nhận về giá bán trị thứ nhất cũng đó là giá trị bằng với mức 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ị bên trong một mảng dựa vào hàng đã được hướng đẫn và hầu như số cột (tùy ý). Trong lấy 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 cách làm được sa chép xuống cột, danh sách các giá trị biệt lập ($B$1:B1) sẽ không ngừng mở rộng hơn vì tham chiếu thêm ô sản phẩm hai (B1) là một trong những tham chiếu tương đối – chuyển đổi theo vị trí tương đối của ô lúc công thức di chuyển sang ô khác.

Xem thêm: Giải Vở Bài Tập Toán Lớp 3 Trang 102 Tập 1 Câu 1, 2, 3, 4, Giải Vở Bài Tập Toán Lớp 3 Tập 2 Bài 102: Tháng

Do đó, khi xào luộc công thức sang ô B3, COUNTIF($B$1:B1, $A$2:$A$10) biến hóa 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)), “”)

trả về:

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

Vì bao gồm một “Ronnie” được tra cứu 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 đầu tiên trong mảng. Cuối cùng, INDEX($A$2:$A$10, 2) trả về giá trị từ hàng máy 2, chính là “David”.

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

Công thức cực hiếm duy tốt nhất – chứa thêm một hàm COUNTIF đào 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 danh sách duy nhất tất cả các mục mở ra trong danh sách nguồn nhiều lần: COUNTIF($A$2:$A$10, $A$2:$A$10)1.

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

Các cực hiếm văn phiên bản khác biệt bỏ qua những con số – các bạn sử dụng công dụng ISTEXT để kiểm soát xem giá bán trị liệu có phải là văn phiên bản hay không, và công dụ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 giá trị biệt lập từ một cột sử dụng Bộ lọc nâng cao – Advanced Filter của Excel nếu như khách hàng không hy vọng lãng phí thời gian để đọc cặn kẽ những công thức quý giá khác biệt, chúng ta cũng có thể nhanh chóng giành được danh sách những giá trị khác biệt bằng cách sử dụng bộ 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. Chọn cột dữ liệu bạn muốn 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 vỏ hộp thoại Advanced Filter, các bạn chọn các tuỳ chọn sau: – kiểm tra nút Copy to lớn another location. – vào hộp danh sách range, xác minh rằng phạm vi mối cung cấp được hiển thị thiết yếu xác. – Trong vỏ hộp Copy to, nhập phạm vi trên cùng của dải ô bạn có nhu cầu sao chép tài liệu sang. để ý là bạn chỉ có thể sao chép tài liệu đã được thanh lọc vào trang tính đã hoạt động. – Chọn chất lượng records only.

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

Trích xuất các hàng độc nhất vô nhị và biệt lập sử dụng Duplicate Remover

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

– Tìm và trích xuất các hàng tốt nhất / khác hoàn toàn dựa trên các giá trị vào một hoặc những cột. – Tìm, khắc ghi và xào nấu giá trị nhất đến bất kỳ vị trí làm sao khác, trong và một bảng tính hoặc sang bảng tính khác.

Giờ bọn họ sẽ thuộc xem buổi giao lưu của công thế Duplicate Remover.

Giả sử các 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 ví dụ rằng bảng bắt tắt chứa không ít hàng trùng lặp cùng nhiệm vụ của bạn là trích xuất hàng duy nhất lộ diện trong bảng duy nhất lần hoặc các hàng không giống biệt bao hàm những giá trị mở ra duy độc nhất vô nhị một lần và một lần lộ diện chung cho mọi giá trị trùng lặp. Thực hiện Duplicate Remover quá trình sẽ được hoàn tất trong 5 bước sau:

1. Chọn ngẫu nhiên ô như thế nào trong bảng nguồn của khách hàng và nhấp nút Duplicate Remover bên trên tab Ablebits Data, trong đội Dedupe. thủ pháp Duplicate Remover sẽ chạy và chọn toàn bộ bảng. Vì chưng vậy, chỉ cần nhấp vào Next để thường xuyên bước tiếp theo.

Chọn một số 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 chúng ta để chọn unique (duy nhất) hoặc unique +1st occurrences (distinct) (Những quý hiếm duy nhất với một lần xuất hiện chung cho gần như giá trị trùng lặp).

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

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

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

Trong ví dụ này, chúng ta hãy lựa chọn trang tính mới: 5. Lựa chọn Finish, bởi vậy là đã triển khai 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à nhanh chóng trích xuất được các giá trị tốt nhất hoặc những hàng bạn phải trong Excel.

———

Ngoài ra để vận dụng Excel vào quá trình một cách tác dụng thì các bạn còn buộc phải sử dụng giỏi các hàm, những công vắt khác của Excel.

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

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

Một số công cụ thường dùng như:

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

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