Hàm liệt kê có điều kiện trong excel

Trong bài viết này Học Excel Online vẫn chỉ dẫn các bạn cách thanh lọc ra một danh sách những cực hiếm lẻ tẻ độc nhất vô nhị trong cột bằng cách áp dụng một bí quyết và cách điều khiển và tinh chỉnh công thức đó cho các bộ dữ liệu khác biệt. Ngoài ra, Học Excel Online cũng sẽ chỉ đến chúng ta biện pháp nhanh chóng duy nhất để có được một list đơn nhất bằng phương pháp áp dụng Sở thanh lọc nâng cấp của Excel, hàm liệt kê danh sách vào excel cùng có tác dụng cầm như thế nào để trích xuất những sản phẩm tuyệt nhất thực hiện Duplicate Remover.

You watching: Hàm liệt kê có điều kiện trong excel

Để lọc ra một danh sách các cực hiếm nhất vào Excel chúng ta có thể sử dụng phương pháp khẳng định các cực hiếm nhất kia, lọc riêng rẽ rồi xào nấu bọn chúng. Tuy nhiên, áp dụng cách làm cho này đang tốn nhiều thời hạn, để tiến hành nkhô cứng hơn, chúng ta có thể sử dụng một công thức quan trọng đặc biệt cùng rất một vài ba kĩ thuật không giống mà lại Học Excel Online sẽ chia sẻ với chúng ta tức thì dưới đây.


Xem nhanh


Cách thanh lọc ra những cực hiếm duy nhất trong ExcelCách thanh lọc ra những giá trị khác biệt vào Excel (duy nhất + gần như dữ liệu giống nhau đã chỉ xuất hiện thêm một lần)Cách trích xuất các quý hiếm khác biệt phân biệt chữ hoa chữ hay trong ExcelCách hoạt động vui chơi của bí quyết trích xuất những quý hiếm duy nhất/ khác biệt

Cách lọc ra những quý hiếm duy nhất vào Excel

Để rời nhầm lẫn, thứ nhất, chúng ta bắt buộc phát âm hầu hết giá trị nào được Hotline tà tà những cực hiếm tốt nhất trong Excel. Giá trị nhất là những quý hiếm chỉ lâu dài độc nhất vô nhị một lượt trong toàn cục danh sách. Ví dụ:

*

Để trích xuất một danh sách các quý giá độc nhất vô nhị vào Excel, chúng ta cũng có thể thực hiện một trong số phương pháp dưới đây.

Công thức mảng nhằm tìm ra hầu như cực hiếm duy nhất (xong bí quyết chấm dứt ta dấ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 liên tiếp (xong cách làm 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 – list nguồn B1 – ô đầu của list độc nhất vô nhị trừ đi 1. Trong ví dụ này, họ bước đầu danh sách những giá trị tuyệt nhất nghỉ ngơi B2, với cho nên vì thế họ đưa B1 vào phương pháp (B2-1 = B1). Nếu danh sách các giá trị độc nhất của người tiêu dùng bước đầu sinh sống ô C3, ta sẽ thay đổi $B$1:B1 thành $C$2:C2.


*

Chú ham mê. Bởi vì phương pháp tyêu thích chiếu ô bên trên ô thứ nhất của list những giá trị tốt nhất, thường là tiêu đề cột (nlỗi trong ví dụ này là B1), đề nghị bạn cần đảm bảo rằng tiêu đề của khách hàng là một trong thương hiệu độc nhất ko xuất hiện thêm ở bất cứ đâu trong cột.

Tại ví dụ này vẫn trích xuất các thương hiệu độc nhất từ cột A (chính xác rộng là vào phạm vi A2: A20), và ảnh chụp màn hình sau đã minh họa mang lại công thức vẫn dùng:

Dưới đó là giải thích một biện pháp cụ thể về bài toán thực hiện bí quyết để trích xuất các giá trị độc nhất vô nhị trong bảng tính Excel của bạn:

– Lựa lựa chọn 1 Một trong những phương pháp dựa vào tài liệu của công ty. – Nhập phương pháp vào ô đầu tiên của list các quý hiếm duy nhất (vào ví dụ đã là ô B2). – Nếu ai đang sử dụng bí quyết mảng, thừa nhận Ctrl + Shift + Enter. Nếu bạn chọn phương pháp thông thường, hãy nhấn phím Enter nlỗi thường xuyên lệ. – Sao chép bí quyết xuống càng xa càng giỏi bằng cách kéo chốt giải pháp xử lý. Vì cả hai bí quyết giá trị nhất được gói gọn gàng vào hàm IFERROR, bạn cũng có thể xào luộc phương pháp đến cuối bảng nhưng ko có tác dụng lộn xộn tài liệu vày ngẫu nhiên lỗi nào mặc dù những cực hiếm độc nhất đã có trích xuất ra làm sao.

Cách thanh lọc ra những giá trị biệt lập vào Excel (độc nhất + hồ hết dữ liệu như là nhau đã chỉ mở ra một lần)

Nlỗi các bạn sẽ đoán ra từ tiêu đề của phần này, những giá trị khác hoàn toàn vào Excel là toàn bộ những quý hiếm khác biệt trong danh sách, Tức là đa số giá trị đó chỉ mở ra một lần tuyệt nhất cho dù nó bao gồm bao nhiêu nhân phiên bản đi chăng nữa. Ví dụ:

Để đã có được một danh sách những giá trị khác hoàn toàn vào Excel, ta áp dụng cách làm sau.

Công thức mảng nhằm tìm kiếm hầu hết quý hiếm biệt lập (dấ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 để kiếm tìm gần như quý hiếm không giống 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 mối cung cấp B1 là ô ở bên trên của list những cực hiếm biệt lập. Trong ví dụ này, list những quý giá khác hoàn toàn bước đầu làm việc ô B2 (đấy là ô trước tiên chúng ta nhập công thức).

Trích xuất các giá trị khác biệt vào vào một cột cùng bỏ qua mất những ô trống Nếu danh sách mối cung cấp của người tiêu dùng chứa ô trống, thì các công thức bọn chúng ra vừa nêu sinh hoạt bên trên đã 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ọ bắt buộc cải tiến bí quyết thêm một ít.

Học thiết kế VBA vào Excel sinh sống đâu?

Công thức trích xuất những quý giá biệt lập ko bao hàm khoảng tầm 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 đơn lẻ bỏ qua số với không gian Theo cách tương tự như, bạn có thể cảm nhận một list những quý giá đơn lẻ trừ các ô và ô trống bao gồm cất 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 mối cung cấp, và Bmột là ô nằm ngay trên ô đầu tiên của danh sách cá biệt.

Hình ảnh chụp màn hình dưới đây cho biết tác dụng của tất cả nhị bí quyết trên:

Cách trích xuất những cực hiếm khác nhau minh bạch chữ hoa chữ thường trong Excel

Khi thao tác cùng với đông đảo dữ liệu mẫn cảm nhỏng vào ngôi trường hòa hợp như password, thương hiệu người dùng hoặc tên tệp, bạn sẽ cần phải bao gồm list các cực hiếm khác biệt mẫn cảm cùng với từng chữ. Lúc kia, bạn có thể sử dụng bí quyết mảng tiếp sau đây, với A2:A10 là list nguồn, cùng B1 là ô vị trí ô đầu tiên của danh sách riêng rẽ biệt:

Công thức mảng nhằm thừa nhận các quý hiếm hiếm hoi 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 buổi giao lưu của cách làm 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 rẽ cho người mong mỏi làm rõ về rất nhiều phương pháp này một cách ví dụ nhất.

Việc trích xuất những quý hiếm độc nhất vô nhị cùng biệt lập trong Excel đích thực chưa hẳn một thao tác dễ dàng. Nhưng chúng ta cũng có thể thấy rằng toàn bộ các bí quyết đa số được dựa trên cùng một bí quyết 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, họ sẽ cùng thực hiện bí quyết mảng nhằm trích ra một list những quý hiếm khác hoàn toàn cũng chính vì tất cả các bí quyết khác được bàn thảo trong hướng dẫn này là những bí quyết đã làm được cải tiến hoặc đổi mới thể từ bỏ bí quyết cơ phiên 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 bắt đầu bắt đầu, chúng ta cũng có thể bỏ qua mất hàm IFERROR, hàm này được thực hiện với 1 mục tiêu tốt nhất nhằm đào thải lỗi # N/A lúc số ô cơ mà chúng ta sẽ sao chép phương pháp quá thừa số lượng các quý giá biệt lập vào danh sách nguồn.

Và bây chừ, chúng ta cùng tò mò về những phần cơ bản trong bí quyết tìm quý hiếm không giống biệt:

1. COUNTIF(range, criteria) hàm này sẽ trả về số ô vào một phạm vi đáp ứng nhu cầu được ĐK ví dụ. Nlỗi sinh hoạt vào ví dụ này, COUNTIF($B$1:B1, $A$2:$A$10) trả về mảng 1 cùng 0 dựa vào ngẫu nhiên cực hiếm nào trong danh sách mối cung cấp ($A$2:$A$10) mở ra trong danh sách các cực hiếm biệt lập ($B$1:B1). Nếu quý giá được tra cứu thấy, phương pháp sẽ trả về 1, nếu như không kiếm thấy sẽ trả về – 0.

Cụ thể, vào ô B2, COUNTIF($B$1:B1, $A$2:$A$10 trsinh sống 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

Tại trên đây, không có mục làm sao vào list nguồn (tiêu chí) mở ra trong phạm vi phù hợp với điều kiện tìm. Trong trường thích hợp này, phạm vi ($B$1:B1) gồm một mục duy nhất – “Distinct”. 2. MATCH (lookup_value, lookup_array, ) trả về địa chỉ tương đối của cực hiếm đang tìm kiếm trong mảng. Trong ví dụ này, lookup_value là 0, cần ta có: MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)

trsống thành:

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

Và trả về 1

Bởi bởi tác dụng MATCH dấn về quý hiếm trước tiên cũng chính là cực hiếm bởi với cái giá trị đang sẽ search tìm (mà quý giá đã kiếm tìm 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 sản phẩm đã có được chỉ định và rất nhiều số cột (tùy ý). Trong ví dụ này, INDEX($A$2:$A$10, 1)

Trngơi nghỉ thành:

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

Và trả về “Ronnie”.

khi bí quyết được sa chđè xuống cột, danh sách những quý hiếm khác biệt ($B$1:B1) đã không ngừng mở rộng rộng bởi tsi mê chiếu thêm ô thiết bị hai (B1) là một tyêu thích chiếu tương đối – biến hóa theo địa điểm kha khá của ô khi bí quyết di chuyển thanh lịch ô không giống.

See more: Cách Tách 1 Trang Trong Word 2007, Cách Ngắt Trang, Chia Phần Trong Word 2016

Do kia, Lúc sao chép phương pháp thanh lịch ô B3, COUNTIF($B$1:B1, $A$2:$A$10) biến đổi thành COUNTIF($B$1:B2, $A$2:$A$10), cùng trngơi nghỉ 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ì có một “Ronnie” được tra cứu thấy vào 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à địa điểm kha khá của số 0 trước tiên trong mảng. Cuối thuộc, INDEX($A$2:$A$10, 2) trả về giá trị trường đoản cú hàng thứ hai, sẽ là “David”.

Mẹo: nhằm hiểu rõ rộng vị ngắn gọn xúc tích của công thức, bạn có thể chọn những phần không giống nhau của công thức trong thanh hao bí quyết cùng nhận f9 để xem Review về phần đó: Nlỗi đã nhắc, những bí quyết không giống được bàn bạc vào bài bác gợi ý này dựa vào ngắn gọn xúc tích tương tự, nhưng lại gồm thêm chút ít nỗ lực đổi:

Công thức cực hiếm tuyệt nhất – đựng thêm 1 hàm COUNTIF sa thải hồ hết mục mở ra những lần trong danh sách mối cung cấp ngoài list những quý giá duy nhất: khỏi list duy nhất tất cả các mục lộ diện vào danh sách nguồn nhiều lần: COUNTIF($A$2:$A$10, $A$2:$A$10)1.

Các giá trị khác hoàn toàn làm lơ khoảng chừng White – ở đây bạn thêm một hàm IF để ngăn không cho những ô trống được cung cấp list riêng rẽ biệt: IF($A$2:$A$13=””,1,0).

Các quý giá văn uống bản khác biệt bỏ lỡ các số lượng – các bạn thực hiện tính năng ISTEXT để bình chọn coi quý hiếm có phải là vnạp năng lượng phiên bản hay không, và tác dụng IF nhằm sa thải toàn bộ những một số loại cực hiếm không giống, bao gồm những ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Trích xuất những giá trị biệt lập từ 1 cột thực hiện Bộ thanh lọc nâng cấp – Advanced Filter của Excel Nếu các bạn không muốn tiêu tốn lãng phí thời gian nhằm đọc cặn kẽ những bí quyết giá trị khác hoàn toàn, bạn có thể mau lẹ đã có được danh sách các giá trị khác biệt bằng phương pháp sử dụng Sở thanh lọc nâng cấp bằng phương pháp thực hiện công việc chi tiết tiếp sau đây.

1. Chọn cột tài liệu bạn có nhu cầu trích xuất các quý hiếm khác hoàn toàn. 2. Chuyển lịch sự tab Data > Sort & 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 ít Copy khổng lồ another location. – Trong hộp List range, xác minch rằng phạm vi nguồn được hiển thị đúng mực. – Trong hộp Copy to, nhập phạm vi trên cùng của dải ô bạn có nhu cầu xào nấu dữ liệu lịch sự. Lưu ý là chúng ta chỉ rất có thể coppy dữ liệu đã có lọc vào trang tính sẽ hoạt động. – Chọn Unique records only.

*
4. Cuối cùng, chúng ta dấn OK cùng khám nghiệm lại kết quả: Quý khách hàng đề nghị lưu ý rằng, cho dù tùy lựa chọn của Advanced Filter được lấy tên là “Unique records only” (Chỉ bao hàm phiên bản ghi duy nhất) tuy vậy nó vẫn trích xuất phần đa cực hiếm khác hoàn toàn, nghĩa là bao gồm các cực hiếm chỉ xuất hiện thêm một đợt độc nhất vô nhị và một lượt lộ diện chung mang đến đa số quý giá giống nhau.

Trích xuất mọi hàng tuyệt nhất với biệt lập thực hiện Duplicate Remover

Trong phần ở đầu cuối của bài xích trả lời này, Học Excel Online sẽ chỉ cho mình phương án kiếm tìm cùng trích xuất các quý hiếm cá biệt cùng độc nhất vô nhị trong số trang tính của Excel. Giải pháp này kết hợp linc hoạt các bí quyết Excel và một vài vận dụng dễ dàng và đơn giản của advanced filter. Dường như, giải pháp này còn cung cấp một vài nhân kiệt siêu lạ mắt như:

– Tìm với trích xuất những mặt hàng tuyệt nhất / khác biệt dựa trên các giá trị trong một hoặc nhiều cột. – Tìm, ghi lại với xào luộc quý giá độc nhất đến ngẫu nhiên vị trí nào không giống, trong cùng một bảng tính hoặc sang trọng bảng tính không giống.

Giờ họ đang cùng xem hoạt động của nguyên lý Duplicate Remover.

Giả sử các bạn có một bảng tóm tắt được tạo thành bằng phương pháp hợp tốt nhất tài liệu từ một số bảng khác. cũng có thể thấy cụ thể rằng bảng cầm tắt cất không ít mặt hàng giống nhau với trọng trách của doanh nghiệp là trích xuất mặt hàng tuyệt nhất mở ra trong bảng chỉ một lần hoặc các hàng khác hoàn toàn bao hàm đa số giá trị xuất hiện độc nhất vô nhị một lượt với một lần xuất hiện chung mang đến những quý giá giống nhau. Sử dụng Duplicate Remover quá trình sẽ tiến hành hoàn toàn trong 5 bước sau:

1. Chọn ngẫu nhiên ô như thế nào trong bảng nguồn của người sử dụng với nhấp nút Duplicate Remover trên tab Ablebits Data, vào nhóm Dedupe. Thủ thuật Duplicate Remover đang chạy cùng lựa chọn toàn thể bảng. Vì vậy, chỉ việc nhấp vào Next nhằm thường xuyên bước tiếp sau.

Chọn các loại cực hiếm bạn muốn tìm kiếm cùng nhấp vào Next:

Prúc ở trong vào nhu yếu trích xuất dữ liệu của người tiêu dùng nhằm chọn Unique (duy nhất) hoặc Unique +1st occurrences (distinct) (Những quý hiếm tuyệt nhất với một lần xuất hiện bình thường cho rất nhiều quý hiếm trùng lặp).

Nhỏng trong ví dụ này, họ muốn trích xuất các mặt hàng xuất hiện một đợt độc nhất vô nhị trong bảng mối cung cấp bắt buộc ta sẽ lựa chọn Unique:

Mẹo: Nlỗi các bạn đang thấy vào hình họa chụp màn hình ngơi nghỉ bên trên, có hai tuyển lựa dành cho rất nhiều quý giá mở ra các lần, tuy vậy bạn cần nhớ rằng không được nhầm lẫn với các bảng tính khác.

3. Chọn một hoặc những cột nhằm chất vấn lại giá trị tốt nhất. Nlỗi trong ví dụ này, chúng ta đề xuất tìm thấy một mặt hàng nhất dựa trên các quý hiếm trong cả ba cột (Order number, First name & Last name), vì vậy họ đã lựa chọn tất cả. 4. Chọn ra tùy chọn bắt buộc áp dụng mang lại mọi quý hiếm tuyệt nhất tìm được dựa vào các sàng lọc tất cả sẵn sau đây: – Highlight quality values – Đánh giá chỉ các quý hiếm tốt nhất – Select quality 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 chnghiền lịch sự địa chỉ không giống Vì bọn họ đã phải triết xuất các mặt hàng độc nhất vô nhị, chọn Copy to lớn another location và sau đó chỉ định vị trí đúng chuẩn đề xuất sao chép bọn chúng – địa điểm kia phải là một trong bảng tính đã hoạt động (chọn tuỳ chọn Custom location cùng chỉ định ô trên cùng của dải đích), hoặc vị trí hy vọng sao chép đó cần là bảng tính new.

Trong ví dụ này, họ nên chọn trang tính mới: 5. Chọn Finish, như vậy là sẽ triển khai xong xuôi.

Hy vọng bài bác gợi ý này góp chúng ta cũng có thể thuận tiện cùng mau lẹ trích xuất được các giá trị độc nhất hoặc những mặt hàng bạn phải trong Excel.

See more: Nơi Bán Điện Thoại Oppo F1 Plus Giá Bao Nhiêu, Oppo F1 Plus

———

Dường như nhằm ứng dụng Excel vào công việc một phương pháp kết quả thì các bạn còn cần áp dụng xuất sắc những hàm, những nguyên lý không giống của Excel.

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

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiệnCOUNTIF, COUNTIFS để những thống kê, đếm theo một ĐK, nhiều điều kiệnCác hàm xử lý tài liệu dạng chuỗi, dạng tháng ngày, dạng số…Các hàm dò search tmê mẩn chiếu Index+Match, hàm SUMPRODUCT…

Một số phương pháp thường được sử dụng như:

Định dạng theo ĐK với Conditional formattingThiết lập ĐK nhập dữ liệu cùng với Data ValidationCách đặt Name cùng sử dụng Name trong công thứcLập report với Pivot Table…

Rất các kỹ năng và kiến thức cần ko nào? Toàn bộ đều kiến thức và kỹ năng này các bạn những có thể học tập được vào khóa học EX101 – Excel từ cơ bản tới chăm gia của Học Excel Online. Đây là khóa học khiến cho bạn khối hệ thống kỹ năng và kiến thức một bí quyết không hề thiếu, cụ thể. hơn nữa không thể gồm số lượng giới hạn về thời gian học hành nên chúng ta cũng có thể dễ chịu và thoải mái học tập bất cứ thời điểm nào, thuận tiện tra cứu vớt lại kiến thức lúc buộc phải. Trong thời điểm này khối hệ thống đang sẵn có ưu tiên rất to lớn cho chính mình lúc đăng ký tsay mê gia khóa học. Chi tiết xem tại: mdtq.vn.Online