Bạn đang xem bài viết Cách Đếm Các Giá Trị Duy Nhất Trong Một Dải Ô Excel được cập nhật mới nhất 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.
Chúng ta muốn biết có bao nhiêu người đã nhận được tin nhắn. Như bạn thấy trong danh sách này, có tổng cộng tổng cộng 14 tin nhắn SMS, nhưng Rachel đã gửi 2 tin nhắn cho Ingrid, James và Arthur. Như vậy thật sự chỉ có 11 người dùng được nhận tin nhắn mà thôi. Chúng ta sẽ sử dụng một công thức bao gồm hàm SUMPRODUCT và COUNTIF .
= SUMPRODUCT (1 / COUNTIF (A1: A14, A1: A14 ))
Để bắt đầu, chúng ta sẽ xem COUNTIF làm gì.
= COUNTIF (A1: A14, A1: A14 )
Phần này đếm xem mỗi người dùng có bao nhiêu trong danh sách. Nó là một công thức mảng. Nó phải được hiểu như sau:
= COUNTIF (A1, A1: A14 ) = 2= COUNTIF (A2, A1: A14 ) = 1….= COUNTIF (A14, A1: A14 ) = 2
Đưa kết quả của công thức này dưới dạng một mảng.
= COUNTIF (A1: A14, A1: A14 ) = {2, 1, 2; 1; 1; 1; 2; 2; 1; 2; 1; 1; 1; 2}
Để xem mảng, chọn một ô, nhấn F2 + F9.
1 / COUNTIF (A1: A14, A1: A14 ) = {0,5; 1; 0,5; 1; 1; 1; 0,5; 0,5; 1; 0,5; 1; 1; 1; 0,5}
= SUMPRODUCT ( 1 / COUNTIF (A1: A14, A1: A14 ) ) = {0,5 + 1 + 0,5 + 1 + 1 + 1 + 0,5 + 0,5 + 0,5 + 1 + 1 + 1 + 1 + 0,5} = 11
Ở phần trên, chúng ta đã tìm hiểu cách đếm số lượng giá trị duy nhất trong một phạm vi. Thủ thuật đó về cơ bản là sử dụng hàm SUMPRODUCT và COUNTIF .
Bây giở, chúng ta sẽ tìm hiểu một cách thú vị khác, sử dụng công thức bao gồm hàm FREQUENCY và MATCH.
Để bạn dễ dàng đối chiếu so sánh, và chọn công thức phù hợp nhất với mình, chúng ta sẽ sử dụng lại ví dụ bên trên.
Công thức này là một công thức mảng hoặc ma trận. Hãy xem cách nó hoạt động từng bước.
Để xem toàn bộ kết quả của từng công thức, bạn cần nhấn F2 + F9, một lần trong ô.
1. =MATCH(Name_list,Name_list,0)
Để hiểu một công thức tương đối dài này, điều chính là bắt đầu với cạnh nhỏ hơn, thường ở giữa nhiều hơn. Trong trường hợp này, điều đầu tiên cần hiểu là điều gì tạo nên MATCH (Name_list, Name_list, 0) . Đầu tiên hãy kiểm tra MATCH nếu bạn có bất kỳ nghi ngờ nào về cách hoạt động của hàm.
Công thức mảng này sẽ cho chúng ta biết đối với từng tên, chúng xuất hiện ở vị trí nào trên danh sách.
=MATCH(Name_list,Name_list,0) = {1; 2; 3; 4; 5; 6; 1; 3; 9; 10; 11; 12; 13; 10}
Như được hiển thị trong kết quả, chỉ có các bản sao có số lượng khác với vị trí của nó. Có thể thấy rằng Arthur, một trong những cái tên được lặp lại như trong A1 và A7, nhận được giá trị 1 trong cả hai trường hợp, cho chúng ta biết rằng giá trị đầu tiên trong danh sách mà Arthur xuất hiện nằm ở vị trí đầu tiên.
Hàm ROW là hàm tạo ra một ma trận với tất cả các số từ 1 đến 14, có 14 giá trị trong phạm vi của chúng ta.
=ROW(Name_list) = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}
3. =FREQUENCY(MATCH(Name_list,Name_list,0),ROW(Name_list))
Hàm tần số tính toán tần suất mà một giá trị xuất hiện trong một phạm vi giá trị và trả về một dãy số dọc.
Trong trường hợp của chúng tôi, về cơ bản nó đếm số lần mỗi giá trị xuất hiện trong danh sách.
= FREQUENCY (MATCH (Name_list, Name_list, 0), ROW (Name_list)) ={2, 1, 2; 1; 1; 1; 0; 0; 1; 2; 1; 1; 1; 0; 0}
Như có thể thấy trong ma trận thu được ở bước 1, có 2 giá trị bằng 1, 2 bằng 3 và 2 bằng 10, điều này rõ ràng chỉ ra rằng cả Arthur, Ingrid và James đều nhận hơn 1 tin nhắn.
Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
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 ExcelKhi 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ệtPhầ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…
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ấtCá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íchVí 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
Đếm Tần Suất Xuất Hiện Của Một Giá Trị
Trong ví dụ tiếp theo, chúng ta dùng kết hợp hàm IF và SUM. Đầu tiên hàm IF kiểm tra giá trị trong một số ô và sau đó nếu kết quả kiểm tra là True thì SUM sẽ tính tổng các giá trị qua được phần kiểm tra đó.
Lưu ý: Hãy nhập công thức trong ví dụ này dưới dạng công thức mảng.
Nếu bạn có phiên bản hiện tại của Microsoft 365, thì bạn chỉ cần nhập công thức ở phía trên cùng bên trái của phạm vi đầu ra, rồi nhấn Enter để xác nhận công thức dưới dạng công thức mảng động.
Nếu bạn đã mở sổ làm việc này trong Excel for Windows hoặc Excel 2023 cho Mac và các phiên bản mới hơn, và muốn thay đổi công thức hoặc tạo công thức tương tự, hãy nhấn F2, rồi nhấn Ctrl + Shift + Enter để tạo công thức trả về kết quả bạn mong đợi. Trong các phiên bản cũ hơn của Excel for Mac, hãy dùng+ Shift + Enter.
Ví dụ 1
Hàm above nói rằng nếu C2: C7 có chứa các giá trị Buchanan và Dodsworth, thì hàm Sum sẽ hiển thị tổng của các bản ghi có điều kiện đã được đáp ứng. Công thức tìm ba bản ghi cho Buchanan và một đối với một trong các phạm vi đã cho và hiển thị 4.
Ví dụ 2
Hàm above nói rằng nếu D2: D7 có chứa các giá trị nhỏ hơn $9000 hoặc lớn hơn $19.000, thì SUM sẽ hiển thị tổng của tất cả những bản ghi mà điều kiện đã được đáp ứng. Công thức tìm thấy hai bản ghi D3 và D5 với giá trị nhỏ hơn $9000, sau đó là D4 và D6 với các giá trị lớn hơn $19.000, và hiển thị 4.
Ví dụ 3
Hàm above nói rằng nếu D2: D7 có hóa đơn cho Buchanan nhỏ hơn $9000, thì SUM sẽ hiển thị tổng của các bản ghi có điều kiện đã được đáp ứng. Công thức thấy rằng C6 đáp ứng điều kiện và hiển thị 1.
Cập nhật thông tin chi tiết về Cách Đếm Các Giá Trị Duy Nhất Trong Một Dải Ô Excel 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!