Nội dung
Excel là một chương trình xử lý dữ liệu hiệu quả. Và một trong những phương pháp phân tích thông tin là so sánh hai danh sách. Nếu bạn so sánh chính xác hai danh sách trong Excel, việc tổ chức quá trình này sẽ rất dễ dàng. Chỉ cần tuân theo một số điểm sẽ được thảo luận ngày hôm nay là đủ. Việc thực hiện phương pháp này trên thực tế hoàn toàn phụ thuộc vào nhu cầu của cá nhân hoặc tổ chức tại một thời điểm cụ thể. Do đó, cần xem xét một số trường hợp có thể xảy ra.
So sánh hai danh sách trong Excel
Tất nhiên, bạn có thể so sánh hai danh sách theo cách thủ công. Nhưng sẽ mất nhiều thời gian. Excel có bộ công cụ thông minh riêng cho phép bạn so sánh dữ liệu không chỉ một cách nhanh chóng mà còn lấy được thông tin không dễ dàng có được bằng mắt thường. Giả sử chúng ta có hai cột với tọa độ A và B. Một số giá trị được lặp lại trong chúng.
Công thức của vấn đề
Vì vậy, chúng ta cần so sánh các cột này. Quy trình so sánh hai tài liệu như sau:
- Nếu các ô duy nhất của mỗi danh sách này giống nhau và tổng số ô duy nhất bằng nhau và các ô giống nhau, thì các danh sách này có thể được coi là giống nhau. Thứ tự các giá trị trong danh sách này được xếp chồng lên nhau không quan trọng lắm.
- Chúng ta có thể nói về sự trùng hợp từng phần của các danh sách nếu bản thân các giá trị duy nhất giống nhau, nhưng số lần lặp lại khác nhau. Do đó, các danh sách như vậy có thể chứa một số phần tử khác nhau.
- Thực tế là hai danh sách không khớp được chỉ ra bởi một tập hợp các giá trị duy nhất khác nhau.
Tất cả ba điều kiện này đồng thời là điều kiện của vấn đề của chúng ta.
Giải pháp của vấn đề
Hãy tạo hai phạm vi động để so sánh danh sách dễ dàng hơn. Mỗi người trong số họ sẽ tương ứng với mỗi danh sách. 
Để so sánh hai danh sách, hãy làm như sau:
- Trong một cột riêng biệt, chúng tôi tạo danh sách các giá trị duy nhất cụ thể cho cả hai danh sách. Đối với điều này, chúng tôi sử dụng công thức: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Bản thân công thức phải được viết dưới dạng công thức mảng.
- Hãy xác định xem mỗi giá trị duy nhất xuất hiện bao nhiêu lần trong mảng dữ liệu. Dưới đây là các công thức để làm điều này: = COUNTIF (Danh sách1, D5) và = COUNTI (Danh sách2, D5).
- Nếu cả số lần lặp lại và số giá trị duy nhất đều giống nhau trong tất cả danh sách được bao gồm trong các phạm vi này, thì hàm trả về giá trị 0. Điều này cho biết kết quả khớp là XNUMX%. Trong trường hợp này, tiêu đề của các danh sách này sẽ có nền màu xanh lá cây.
- Nếu tất cả nội dung duy nhất nằm trong cả hai danh sách, thì được trả về bởi các công thức =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) giá trị sẽ bằng không. Nếu E1 không chứa 2, nhưng giá trị như vậy được chứa trong các ô E2 và FXNUMX, thì trong trường hợp này các phạm vi sẽ được công nhận là phù hợp, nhưng chỉ một phần. Trong trường hợp này, các tiêu đề của danh sách tương ứng sẽ chuyển sang màu cam.
- Và nếu một trong các công thức được mô tả ở trên trả về giá trị khác XNUMX, danh sách sẽ hoàn toàn không khớp.
Đây là câu trả lời cho câu hỏi làm thế nào để phân tích cột cho các trận đấu bằng cách sử dụng công thức. Như bạn có thể thấy, với việc sử dụng các hàm, bạn có thể thực hiện hầu hết mọi tác vụ mà thoạt nhìn, không liên quan đến toán học.
Thử nghiệm mẫu
Trong phiên bản bảng của chúng tôi, có ba loại danh sách của mỗi loại được mô tả ở trên. Nó có một phần và hoàn toàn khớp, cũng như không khớp.
Để so sánh dữ liệu, chúng tôi sử dụng phạm vi A5: B19, trong đó chúng tôi xen kẽ chèn các cặp danh sách này. Về kết quả của việc so sánh, chúng ta sẽ hiểu theo màu sắc của danh sách ban đầu. Nếu chúng hoàn toàn khác nhau, thì nó sẽ là nền màu đỏ. Nếu một phần của dữ liệu giống nhau, thì màu vàng. Trong trường hợp nhận dạng hoàn toàn, các tiêu đề tương ứng sẽ có màu xanh lá cây. Cách pha màu phụ thuộc vào kết quả như thế nào? Điều này yêu cầu định dạng có điều kiện.
Tìm sự khác biệt trong hai danh sách theo hai cách
Hãy mô tả thêm hai phương pháp để tìm sự khác biệt, tùy thuộc vào việc danh sách có đồng bộ hay không.
Tùy chọn 1. Danh sách đồng bộ
Đây là một lựa chọn dễ dàng. Giả sử chúng ta có những danh sách như vậy.
Để xác định số lần các giá trị không hội tụ, bạn có thể sử dụng công thức: =SUMPRODUCT(—(A2:A20<>B2:B20)). Nếu chúng ta nhận được kết quả là 0, điều này có nghĩa là hai danh sách giống nhau.
Tùy chọn 2: Danh sách bị xáo trộn
Nếu danh sách không giống nhau theo thứ tự của các đối tượng mà chúng chứa, bạn cần áp dụng một tính năng như định dạng có điều kiện và tô màu các giá trị trùng lặp. Hoặc sử dụng chức năng COUNTIF, bằng cách sử dụng mà chúng tôi xác định số lần một phần tử từ một danh sách xuất hiện trong danh sách thứ hai.
Cách so sánh 2 cột từng hàng
Khi chúng tôi so sánh hai cột, chúng tôi thường cần so sánh thông tin nằm trong các hàng khác nhau. Để làm điều này, nhà điều hành sẽ giúp chúng tôi NẾU. Chúng ta hãy xem nó hoạt động như thế nào trong thực tế. Để làm được điều này, chúng tôi xin đưa ra một số tình huống minh họa.
Thí dụ. Cách so sánh 2 cột đối sánh và sự khác biệt trong một hàng
Để phân tích xem các giá trị nằm trong cùng một hàng nhưng các cột khác nhau có giống nhau hay không, chúng ta viết hàm IF. Công thức được chèn vào mỗi hàng được đặt trong cột bổ trợ, nơi kết quả xử lý dữ liệu sẽ được hiển thị. Nhưng không nhất thiết phải quy định nó trong mỗi hàng, chỉ cần sao chép nó vào các ô còn lại của cột này hoặc sử dụng điểm đánh dấu tự động điền.
Chúng ta nên viết ra một công thức như vậy để hiểu liệu các giá trị trong cả hai cột có giống nhau hay không: = IF (A2 = B2, “Khớp”, “”). Logic của hàm này rất đơn giản: nó so sánh các giá trị trong ô A2 và B2, và nếu chúng giống nhau, nó sẽ hiển thị giá trị “Coincide”. Nếu dữ liệu khác, nó không trả về bất kỳ giá trị nào. Bạn cũng có thể kiểm tra các ô để xem có sự trùng khớp giữa chúng hay không. Trong trường hợp này, công thức được sử dụng là: =IF(A2<>B2, “Không khớp”, “”). Nguyên tắc là như nhau, đầu tiên việc kiểm tra được thực hiện. Nếu hóa ra là các ô đáp ứng tiêu chí, thì giá trị "Không khớp" sẽ được hiển thị.
Cũng có thể sử dụng công thức sau trong trường công thức để hiển thị cả "Khớp" nếu các giá trị giống nhau và "Không khớp" nếu chúng khác nhau: = IF (A2 = B2; “Khớp”, “Không khớp”). Bạn cũng có thể sử dụng toán tử bất bình đẳng thay vì toán tử bình đẳng. Chỉ thứ tự của các giá trị sẽ được hiển thị trong trường hợp này sẽ hơi khác một chút: =IF(A2<>B2, “Không khớp”, “Trùng”). Sau khi sử dụng phiên bản đầu tiên của công thức, kết quả sẽ như sau.
Biến thể này của công thức không phân biệt chữ hoa chữ thường. Do đó, nếu các giá trị trong một cột chỉ khác các giá trị khác ở chỗ chúng được viết bằng chữ in hoa, thì chương trình sẽ không nhận thấy sự khác biệt này. Để so sánh phân biệt chữ hoa chữ thường, bạn cần sử dụng hàm trong tiêu chí CHÍNH XÁC. Phần còn lại của các đối số được giữ nguyên: = IF (EXACT (A2, B2), “Match”, “Unique”).
Cách so sánh nhiều cột cho các kết quả phù hợp trong một hàng
Có thể phân tích các giá trị trong danh sách theo toàn bộ bộ tiêu chí:
- Tìm những hàng có cùng giá trị ở mọi nơi.
- Tìm những hàng có kết quả phù hợp chỉ trong hai danh sách.
Hãy xem một vài ví dụ về cách tiến hành trong từng trường hợp này.
Thí dụ. Cách tìm các kết quả phù hợp trong một hàng trong nhiều cột của bảng
Giả sử chúng ta có một loạt các cột chứa thông tin chúng ta cần. Chúng tôi phải đối mặt với nhiệm vụ xác định những hàng có giá trị giống nhau. Để làm điều này, bạn cần sử dụng công thức sau: = IF (AND (A2 = B2, A2 = C2), “khớp”, ”“).
Nếu có quá nhiều cột trong bảng, bạn chỉ cần sử dụng nó cùng với hàm IF nhà điều hành COUNTIF: = IF (COUNTIF ($ A2: $ C2, $ A2) = 3; ”khớp”; ”“). Số được sử dụng trong công thức này cho biết số cột cần kiểm tra. Nếu nó khác, thì bạn cần phải viết càng nhiều càng tốt cho tình huống của bạn.
Thí dụ. Cách tìm các kết quả phù hợp trong một hàng trong 2 cột bất kỳ của bảng
Giả sử chúng ta cần kiểm tra xem các giá trị trong một hàng có khớp trong hai cột với các giá trị trong bảng hay không. Để làm điều này, bạn cần sử dụng hàm như một điều kiện OR, trong đó lần lượt viết bằng nhau của từng cột với cột kia. Đây là một ví dụ.
Chúng tôi sử dụng công thức này: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Có thể xảy ra trường hợp có rất nhiều cột trong bảng. Trong trường hợp này, công thức sẽ rất lớn và có thể mất rất nhiều thời gian để chọn tất cả các kết hợp cần thiết. Để giải quyết vấn đề này, bạn cần sử dụng hàm COUNTIF: = IF (COUNTIF (B2: D2, A2) + COUNTIF (C2: D2, B2) + (C2 = D2) = 0; “Chuỗi duy nhất”; “Không phải chuỗi duy nhất”)
Chúng tôi thấy rằng tổng cộng chúng tôi có hai chức năng COUNTIF. Với cột đầu tiên, chúng tôi lần lượt xác định xem có bao nhiêu cột có giá trị tương tự với A2, và với cột thứ hai, chúng tôi kiểm tra số cột tương tự với giá trị của B2. Nếu kết quả của việc tính toán theo công thức này, chúng ta nhận được giá trị bằng XNUMX, điều này cho thấy rằng tất cả các hàng trong cột này là duy nhất, nếu nhiều hơn, có những điểm giống nhau. Do đó, nếu kết quả của phép tính theo hai công thức và cộng kết quả cuối cùng chúng ta nhận được giá trị bằng XNUMX, thì giá trị văn bản “Chuỗi duy nhất” được trả về, nếu số này lớn hơn thì được viết là chuỗi này không phải là duy nhất.
Cách so sánh 2 cột trong Excel để so khớp
Bây giờ chúng ta hãy lấy một ví dụ. Giả sử chúng ta có một bảng với hai cột. Bạn cần kiểm tra xem chúng có khớp nhau không. Để làm điều này, bạn cần áp dụng công thức, nơi hàm cũng sẽ được sử dụng IF, và nhà điều hành COUNTIF: = IF (COUNTIF ($ B: $ B, $ A5) = 0, “Không có kết quả phù hợp nào trong cột B”, “Có kết quả phù hợp trong cột B”)
Không có thêm hành động được yêu cầu. Sau khi tính toán kết quả theo công thức này, chúng ta nhận được nếu giá trị của đối số thứ ba của hàm IF diêm. Nếu không có giá trị nào, thì nội dung của đối số thứ hai.
Cách so sánh 2 cột trong Excel để tìm các kết quả phù hợp và tô sáng bằng màu
Để giúp xác định trực quan các cột phù hợp dễ dàng hơn, bạn có thể đánh dấu chúng bằng màu sắc. Để làm điều này, bạn cần sử dụng chức năng "Định dạng có điều kiện". Hãy xem trong thực tế.
Tìm và đánh dấu các kết quả phù hợp theo màu trong nhiều cột
Để xác định các kết quả phù hợp và đánh dấu chúng, trước tiên bạn phải chọn phạm vi dữ liệu mà việc kiểm tra sẽ được thực hiện, sau đó mở mục “Định dạng có điều kiện” trên tab “Trang chủ”. Tại đó, chọn “Giá trị trùng lặp” làm quy tắc chọn ô.
Sau đó, một hộp thoại mới sẽ xuất hiện, trong danh sách bật lên bên trái, chúng tôi tìm thấy tùy chọn “Lặp lại” và trong danh sách bên phải, chúng tôi chọn màu sẽ được sử dụng cho lựa chọn. Sau khi chúng tôi nhấp vào nút “OK”, nền của tất cả các ô có điểm tương đồng sẽ được chọn. Sau đó, chỉ cần so sánh các cột bằng mắt.
Tìm và đánh dấu các đường phù hợp
Kỹ thuật kiểm tra xem các chuỗi có khớp nhau một chút hay không. Đầu tiên, chúng ta cần tạo một cột bổ sung và ở đó chúng ta sẽ sử dụng các giá trị kết hợp bằng cách sử dụng toán tử &. Để làm điều này, bạn cần viết một công thức có dạng: = A2 & B2 & C2 & D2.
Chúng tôi chọn cột đã được tạo và chứa các giá trị được kết hợp. Tiếp theo, chúng tôi thực hiện cùng một chuỗi hành động được mô tả ở trên cho các cột. Các đường trùng lặp sẽ được đánh dấu bằng màu bạn chỉ định.
Chúng tôi thấy rằng không có gì khó khăn trong việc tìm kiếm sự lặp lại. Excel chứa tất cả các công cụ cần thiết cho việc này. Điều quan trọng là chỉ cần thực hành trước khi đưa tất cả kiến thức này vào thực tế.