So sánh dữ liệu trong Excel trên các trang tính khác nhau. Khớp trong cột Excel

Hôm nay tôi sẽ cho bạn biết cách tìm kiếm kết quả khớp trong các cột Excel. Chúng ta hãy xem xét tất cả sự tinh tế bằng cách sử dụng các ví dụ.

Bài 1: Có 6 văn bản trong 6 ô. Bạn cần tìm ra cái nào là duy nhất và cái nào được lặp lại.

Chúng tôi sẽ sử dụng Định dạng có điều kiện.

Chọn các ô cần so sánh;
Trong tab trang chủđi tới "Định dạng có điều kiện -> Quy tắc chọn ô -> Lặp lại giá trị";

Một cửa sổ mới bật lên và các ô trong bảng được lặp lại bắt đầu được đánh dấu.

Trong cửa sổ này, bạn có thể chọn hai cài đặt: tô sáng Lặp đi lặp lại hoặc Các ô duy nhất, cũng như loại đèn nền sẽ sử dụng - đường viền, văn bản, màu văn bản, nền hoặc loại đèn nền độc đáo của riêng bạn.

Hãy xem một ví dụ khác. Bạn cần so sánh hai cột trong Excel để tìm kết quả trùng khớp. Có một bảng cũng có các kết quả khớp, nhưng chúng là số.

Chọn bảng và đi đến Giá trị trùng lặp. Tất cả các trận đấu sẽ được đánh dấu.

Ví dụ: bạn có thể tìm kết quả phù hợp trong một cột. Để làm điều này, chỉ cần chọn nó trước khi sử dụng tùy chọn là đủ.

Chà, như tôi đã nói ở trên, từ danh sách thả xuống, bạn có thể chọn không chỉ các ô lặp lại mà còn cả các ô duy nhất.

Bạn có thể tìm kiếm các chữ cái, từ, ký hiệu, văn bản, v.v. theo cách này.

Trong bài học này tôi sẽ hướng dẫn bạn cách đính kèm tiêu đề vào mỗi trang Excel. Điều này có thể cần thiết nếu bạn đang làm việc với các bảng lớn. Thông thường, khi chúng được in, các tài liệu có số lượng trang lớn sẽ được tạo ra và để thuận tiện khi làm việc với chúng, cần phải lưu tiêu đề bảng trên mỗi tờ.

Trong bài học này tôi sẽ hướng dẫn bạn cách chia văn bản thành các cột trong Excel. Bài học này phù hợp với bạn nếu bạn muốn chia văn bản từ một cột thành nhiều cột. Bây giờ tôi sẽ đưa ra một ví dụ. Giả sử bạn có ô "A", chứa tên, họ và họ của bạn. Bạn cần đảm bảo rằng trong ô đầu tiên “A” chỉ có họ, trong ô “B” - tên và trong ô “C” là tên đệm.

Trong bài viết này tôi sẽ cho bạn biết làm thế nào loại bỏ trùng lặp trong Excel. Chúng ta sẽ xem xét phương pháp tiêu chuẩn đơn giản nhất đã xuất hiện kể từ Excel 2007.

Chúng tôi có hai bảng thứ tự được sao chép vào một bảng tính. Cần phải so sánh dữ liệu từ hai bảng trong Excel và kiểm tra xem vị trí nào ở bảng đầu tiên mà không có ở bảng thứ hai. Không có ích gì khi so sánh thủ công giá trị của từng ô.

So sánh hai cột để tìm kết quả khớp trong Excel

Làm thế nào để so sánh giá trị trong Excel của hai cột? Để giải quyết vấn đề này, chúng tôi khuyên bạn nên sử dụng định dạng có điều kiện để nhanh chóng đánh dấu các mục chỉ có trong một cột. Bảng tính với các bảng:

Bước đầu tiên là đặt tên cho cả hai bảng. Điều này giúp dễ dàng hiểu được phạm vi ô nào đang được so sánh:

Chọn công cụ CÔNG THỨC - Tên được xác định - Gán tên.
Trong cửa sổ xuất hiện, trong trường “Tên:”, nhập giá trị – Bảng_1.
Sử dụng nút chuột trái để nhấp vào trường nhập “Phạm vi:” và chọn phạm vi: A2:A15. Và nhấn OK.

Đối với danh sách thứ hai, thực hiện các bước tương tự, chỉ đặt tên cho nó – Table_2. Và chỉ định phạm vi C2:C15 - tương ứng.

Lời khuyên hữu ích! Tên phạm vi có thể được gán nhanh hơn bằng cách sử dụng trường tên. Nó nằm ở bên trái của thanh công thức. Chỉ cần chọn phạm vi ô và trong trường tên, nhập tên thích hợp cho phạm vi đó rồi nhấn Enter.

Bây giờ hãy sử dụng định dạng có điều kiện để so sánh hai danh sách trong Excel. Chúng ta cần nhận được kết quả sau:

Các mục có trong Bảng_1 nhưng không có trong Bảng_2 sẽ được hiển thị bằng màu xanh lục. Đồng thời, các vị trí nằm trong Bảng_2 nhưng không có trong Bảng_1 sẽ được tô sáng màu xanh lam.

Nguyên tắc so sánh dữ liệu giữa 2 cột trong Excel

Khi xác định điều kiện định dạng ô cột, chúng tôi đã sử dụng hàm COUNTIF. Trong ví dụ này, hàm này kiểm tra số lần giá trị của đối số thứ hai (ví dụ: A2) xuất hiện trong danh sách đối số thứ nhất (ví dụ: Bảng_2). Nếu số lần = 0 thì công thức trả về TRUE. Trong trường hợp này, ô được gán định dạng tùy chỉnh được chỉ định trong các tùy chọn định dạng có điều kiện.

Liên kết trong đối số thứ hai là tương đối, có nghĩa là tất cả các ô của phạm vi đã chọn sẽ được kiểm tra từng ô một (ví dụ: A2:A15). Ví dụ: để so sánh hai bảng giá trong Excel, thậm chí trên các trang tính khác nhau. Công thức thứ hai hoạt động tương tự. Nguyên tắc tương tự có thể được áp dụng cho nhiều nhiệm vụ tương tự khác nhau.

Câu hỏi từ người dùng

Xin chào!

Tôi có một nhiệm vụ, và đã đến ngày thứ ba rồi, tôi phải vắt óc suy nghĩ - tôi không biết làm cách nào để hoàn thành nó. Có 2 bảng (mỗi bảng khoảng 500-600 hàng), bạn cần lấy cột có tên sản phẩm từ một bảng và so sánh với tên sản phẩm ở bảng kia, nếu trùng khớp thì sao chép và dán giá trị từ bảng 2 vào bảng 1. Giải thích một cách khó hiểu, nhưng tôi nghĩ bạn sẽ hiểu nhiệm vụ từ bức ảnh ( khoảng : ảnh đã bị kiểm duyệt cắt bỏ, vẫn là thông tin cá nhân).

Cảm ơn bạn trước. Andrei, Mátxcơva.

Chúc mọi người một ngày tốt lành!

Những gì bạn mô tả đề cập đến các tác vụ khá phổ biến, có thể giải quyết tương đối dễ dàng và nhanh chóng bằng Excel. Tất cả những gì bạn cần làm là đưa hai bảng của mình vào chương trình và sử dụng hàm VLOOKUP. Thông tin thêm về công việc của cô ấy dưới đây...

Một ví dụ về cách làm việc với hàm VLOOKUP

Ví dụ: tôi lấy hai dấu hiệu nhỏ, chúng được hiển thị trong ảnh chụp màn hình bên dưới. Trong bảng đầu tiên (cột A, B- sản phẩm và giá cả) không có dữ liệu trên cột B; ở cột thứ hai, cả hai cột (sản phẩm và giá cả) đều được điền vào. Bây giờ bạn cần kiểm tra các cột đầu tiên trong cả hai bảng và tự động, nếu tìm thấy kết quả khớp, hãy sao chép giá vào bảng đầu tiên. Có vẻ như một nhiệm vụ đơn giản ...

Làm thế nào để làm nó...

Đặt con trỏ chuột vào ô B2- tức là tại ô đầu tiên của cột nơi chúng ta không có giá trị và viết công thức:

=VLOOKUP(A2,$E$1:$F$7,2,FALSE)

A2- giá trị từ cột đầu tiên của bảng đầu tiên (giá trị chúng ta sẽ tìm kiếm trong cột đầu tiên của bảng thứ hai);

$E$1:$F$7- một bảng thứ hai được chọn hoàn toàn (trong đó chúng tôi muốn tìm và sao chép nội dung nào đó). Hãy chú ý đến dấu "$" - cần thiết để khi sao chép công thức, các ô của bảng thứ hai đã chọn không thay đổi;

2 - số cột mà chúng ta đang sao chép giá trị (lưu ý rằng bảng thứ hai đã chọn của chúng ta chỉ có 2 cột. Nếu nó có 3 cột thì giá trị có thể được sao chép từ cột thứ 2 hoặc thứ 3);

NÓI DỐI- chúng tôi đang tìm kiếm một kết quả trùng khớp chính xác (nếu không, kết quả tương tự đầu tiên sẽ được thay thế, điều này rõ ràng là không phù hợp với chúng tôi).

Trên thực tế, bạn có thể điều chỉnh công thức đã hoàn thành theo nhu cầu của mình bằng cách thay đổi một chút. Kết quả của công thức được trình bày trong hình bên dưới: giá được tìm thấy trong bảng thứ hai và được nhập ở chế độ tự động. Mọi thứ đang hoạt động!

Để đặt giá cho các tên sản phẩm khác, chỉ cần mở rộng (sao chép) công thức sang các ô khác. Ví dụ dưới đây.

Sau đó, như bạn có thể thấy, các cột đầu tiên của bảng sẽ được so sánh: từ các hàng có giá trị ô trùng nhau, dữ liệu cần thiết sẽ được sao chép và thay thế. Nói chung, rõ ràng là các bảng có thể lớn hơn nhiều!

Ghi chú: Phải nói rằng hàm VLOOKUP khá tốn tài nguyên máy tính. Trong một số trường hợp, với một tài liệu quá lớn, có thể sẽ mất khá nhiều thời gian để so sánh các bảng. Trong những trường hợp này, cần xem xét các công thức khác hoặc các giải pháp hoàn toàn khác nhau (mỗi trường hợp là riêng lẻ).

Chỉ vậy thôi, chúc may mắn!

Có lẽ ai làm việc với dữ liệu trong Excel đều gặp phải câu hỏi làm sao để so sánh 2 cột trong Excel để tìm điểm giống và khác nhau. Có nhiều hướng khác nhau để làm điều đó. Chúng ta hãy xem xét kỹ hơn về từng người trong số họ.

Cách so sánh hai cột trong Excel theo hàng

Khi so sánh hai cột dữ liệu, thường cần phải so sánh dữ liệu ở từng hàng riêng lẻ để tìm ra sự trùng khớp hay khác biệt. Chúng ta có thể thực hiện phân tích như vậy bằng cách sử dụng hàm. Hãy xem cách này hoạt động như thế nào bằng cách sử dụng các ví dụ dưới đây.

Ví dụ 1: Cách so sánh hai cột tìm sự trùng khớp và khác biệt trong cùng một hàng

Để so sánh dữ liệu trong mỗi hàng của hai cột trong Excel, chúng ta hãy viết một công thức đơn giản. Công thức phải được chèn vào từng hàng trong cột liền kề, bên cạnh bảng chứa dữ liệu chính. Sau khi tạo công thức cho hàng đầu tiên của bảng, chúng ta có thể kéo giãn/sao chép công thức sang các hàng còn lại.

Để kiểm tra xem hai cột của cùng một hàng có chứa cùng một dữ liệu hay không, chúng ta cần công thức:

=IF(A2=B2; “Trùng khớp”; “”)

Công thức xác định sự khác biệt giữa dữ liệu của hai cột trong một hàng sẽ như sau:

=IF(A2<>B2; "Không khớp"; “”)

Chúng ta có thể thực hiện kiểm tra sự trùng khớp và khác biệt giữa hai cột trên một dòng trong một công thức:

=IF(A2=B2; “Khớp”; “Không khớp”)

=IF(A2<>B2; "Không khớp"; "Phù hợp")

Một kết quả tính toán ví dụ có thể trông như thế này:

Để so sánh dữ liệu trong hai cột của cùng một hàng theo cách phân biệt chữ hoa chữ thường, hãy sử dụng công thức:

=IF(MATCH(A2,B2), “Trùng khớp”, “Duy nhất”)

Cách so sánh nhiều cột cho kết quả trùng khớp trong một hàng Excel

Excel cho phép bạn so sánh dữ liệu ở nhiều cột của cùng một hàng bằng các tiêu chí sau:

Tìm các hàng có cùng giá trị trong tất cả các cột của bảng;
Tìm các hàng có cùng giá trị trong hai cột bất kỳ của bảng;

Ví dụ 1. Cách tìm kết quả khớp trong một hàng trong nhiều cột của bảng

Hãy tưởng tượng rằng bảng của chúng ta bao gồm một số cột dữ liệu. Nhiệm vụ của chúng ta là tìm những hàng có giá trị khớp với tất cả các cột. Excel và các hàm sẽ giúp chúng ta điều này. Công thức xác định trận đấu sẽ như sau:

=IF(AND(A2=B2,A2=C2); “Trùng hợp”; “ “)

Nếu bảng của chúng ta có nhiều cột thì việc sử dụng hàm kết hợp với:

=IF(COUNTIF($A2:$C2,$A2)=3;”Khớp”;” “)

Trong công thức, “5” cho biết số cột của bảng mà chúng ta đã tạo công thức. Nếu bảng của bạn có nhiều hoặc ít cột thì giá trị này phải bằng số cột.

Ví dụ 2: Cách tìm kết quả trùng khớp của cùng một hàng trong hai cột bất kỳ của bảng

Hãy tưởng tượng rằng nhiệm vụ của chúng ta là xác định từ một bảng có dữ liệu ở nhiều cột, những hàng có dữ liệu giống nhau hoặc được lặp lại trong ít nhất hai cột. Các hàm và sẽ giúp chúng ta điều này. Hãy viết công thức cho một bảng gồm ba cột dữ liệu:

=IF(OR(A2=B2,B2=C2,A2=C2);”Trùng khớp”;” “)

Trong trường hợp có quá nhiều cột trong bảng của chúng ta, công thức có hàm của chúng ta sẽ rất lớn, vì trong các tham số của nó, chúng ta cần chỉ định tiêu chí khớp giữa mỗi cột của bảng. Trong trường hợp này, một cách dễ dàng hơn là sử dụng .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Chuỗi duy nhất”; “Chuỗi không phải duy nhất”)

=IF(COUNTIF($B:$B,$A5)=0; “Không có kết quả trùng khớp ở cột B”; “Không có kết quả trùng khớp ở cột B”)

Công thức này kiểm tra các giá trị trong cột B để xem chúng có khớp với các ô trong cột A hay không.

Nếu bảng của bạn bao gồm một số hàng cố định, bạn có thể chỉ định một phạm vi rõ ràng trong công thức (ví dụ: $B2:$B10). Điều này sẽ tăng tốc công thức.

Cách so sánh hai cột trong Excel để tìm kết quả trùng khớp và đánh dấu chúng bằng màu

Khi tìm kiếm sự trùng khớp giữa hai cột trong Excel, chúng ta có thể cần trực quan hóa sự trùng khớp hoặc khác biệt được tìm thấy trong dữ liệu, chẳng hạn như sử dụng đánh dấu màu. Cách dễ nhất để làm nổi bật sự trùng khớp và khác biệt về màu sắc là sử dụng Định dạng có điều kiện trong Excel. Hãy xem cách thực hiện việc này bằng các ví dụ dưới đây.

Tìm và đánh dấu các kết quả khớp màu trong nhiều cột trong Excel

Trong trường hợp chúng ta cần tìm kết quả khớp trong một số cột, thì để làm được điều này, chúng ta cần:

Chọn các cột có dữ liệu mà bạn muốn tính toán kết quả trùng khớp;
Trên tab “Trang chủ” trên Thanh công cụ, nhấp vào mục menu “Định dạng có điều kiện” -> “Quy tắc đánh dấu ô” -> “Giá trị lặp lại”;
Trong hộp thoại bật lên, chọn “Lặp lại” trong danh sách thả xuống bên trái và trong danh sách thả xuống bên phải, chọn giá trị lặp lại màu nào sẽ được đánh dấu. Nhấp vào nút “OK”:
Sau đó, các kết quả trùng khớp sẽ được đánh dấu trong cột đã chọn:

Tìm và đánh dấu các hàng khớp trong Excel

Tìm các ô khớp với dữ liệu trong hai hoặc nhiều cột và tìm kiếm kết quả khớp của toàn bộ hàng có dữ liệu là các khái niệm khác nhau. Xin lưu ý hai bảng dưới đây:

Các bảng trên chứa cùng một dữ liệu. Sự khác biệt của chúng là trong ví dụ bên trái, chúng tôi đang tìm kiếm các ô phù hợp và ở bên phải, chúng tôi tìm thấy toàn bộ các dòng lặp lại có dữ liệu.

Hãy xem cách tìm các hàng khớp trong bảng:

Ở bên phải của bảng chứa dữ liệu, chúng ta sẽ tạo một cột phụ, trong đó, đối diện với mỗi hàng có dữ liệu, chúng ta sẽ đặt một công thức kết hợp tất cả các giá trị của hàng trong bảng vào một ô:

=A2&B2&C2&D2

Ở cột phụ bạn sẽ thấy dữ liệu bảng tổng hợp:

Bây giờ, để xác định các hàng khớp trong bảng, hãy thực hiện các bước sau:

Chọn vùng có dữ liệu ở cột phụ (trong ví dụ của chúng ta đây là dãy ô E2:E15 );
Trên tab “Trang chủ” trên Thanh công cụ, nhấp vào mục menu “Định dạng có điều kiện” -> “Quy tắc đánh dấu ô” -> “Giá trị lặp lại”;
Trong hộp thoại bật lên, chọn “Lặp lại” trong danh sách thả xuống bên trái và trong danh sách thả xuống bên phải, chọn giá trị lặp lại màu nào sẽ được đánh dấu. Nhấp vào nút “OK”:
Sau đó, các dòng trùng lặp sẽ được đánh dấu trong cột đã chọn: