WikiHow này hướng dẫn bạn cách sử dụng định dạng có điều kiện để so sánh tất cả các ô trên hai danh sách riêng biệt trong bảng tính Excel và đánh dấu các ô xuất hiện trên cả hai danh sách. Tính năng này chỉ khả dụng trên phiên bản Excel dành cho máy tính để bàn và ứng dụng dành cho thiết bị di động không hỗ trợ tính năng này.
Các bước
Phương pháp 1/2: Sử dụng công thức CountIf
Bước 1. Mở bảng tính Excel bạn muốn chỉnh sửa
Tìm tệp bảng tính có danh sách bạn muốn so sánh và bấm đúp vào tệp để mở trong Microsoft Excel.
Bước 2. Chọn danh sách đầu tiên của bạn
Nhấp vào ô đầu tiên trong danh sách đầu tiên của bạn và kéo chuột xuống ô cuối cùng của danh sách để chọn toàn bộ phạm vi dữ liệu.
Bước 3. Nhấp vào tab Công thức trên ruy-băng của thanh công cụ
Bạn có thể tìm thấy tab này phía trên thanh công cụ ở đầu bảng tính của mình. Nó sẽ mở các công cụ công thức của bạn trên ruy-băng của thanh công cụ.
Bước 4. Nhấp vào Xác định Tên trên thanh công cụ
Bạn có thể tìm thấy tùy chọn này ở giữa dải băng "Công thức". Nó sẽ mở ra một cửa sổ bật lên mới và cho phép bạn đặt tên cho danh sách của mình.
Bước 5. Nhập List1 vào trường Tên
Nhấp vào trường văn bản ở đầu cửa sổ bật lên và nhập tên danh sách tại đây.
- Sau đó, bạn có thể sử dụng tên này để chèn danh sách của mình vào công thức so sánh.
- Ngoài ra, bạn có thể đặt tên khác cho danh sách của mình tại đây. Ví dụ: nếu đây là danh sách các vị trí, bạn có thể đặt tên nó là "location1" hoặc "locationList".
Bước 6. Nhấp vào OK trong cửa sổ bật lên
Điều này sẽ xác nhận hành động của bạn và đặt tên cho danh sách của bạn.
Bước 7. Đặt tên cho danh sách thứ hai của bạn là List2
Làm theo các bước tương tự như danh sách đầu tiên và đặt tên cho danh sách thứ hai của bạn. Điều này sẽ cho phép bạn nhanh chóng sử dụng danh sách thứ hai này trong công thức so sánh của bạn sau này.
Bạn có thể cung cấp cho danh sách bất kỳ tên nào bạn muốn. Đảm bảo ghi nhớ hoặc ghi lại tên bạn đặt cho mỗi danh sách của mình tại đây
Bước 8. Chọn danh sách đầu tiên của bạn
Nhấp vào ô đầu tiên trên danh sách đầu tiên và kéo xuống để chọn toàn bộ phạm vi dữ liệu.
Đảm bảo danh sách đầu tiên của bạn được chọn trước khi bạn bắt đầu thiết lập định dạng có điều kiện
Bước 9. Nhấp vào tab Trang chủ trên thanh công cụ
Đây là tab đầu tiên ở góc trên bên trái của dải băng thanh công cụ. Nó sẽ mở các công cụ bảng tính cơ bản của bạn trên thanh công cụ.
Bước 10. Nhấp vào Định dạng có Điều kiện trên thanh công cụ
Tùy chọn này trông giống như một biểu tượng bảng tính nhỏ với một số ô được đánh dấu bằng màu đỏ và xanh lam. Nó sẽ mở một menu thả xuống gồm tất cả các tùy chọn định dạng của bạn.
Bước 11. Nhấp vào Quy tắc mới trên menu thả xuống
Nó sẽ mở một cửa sổ bật lên mới và cho phép bạn thiết lập thủ công quy tắc định dạng mới cho phạm vi đã chọn.
Bước 12. Chọn tùy chọn "Sử dụng công thức để xác định ô cần định dạng"
Tùy chọn này sẽ cho phép bạn nhập công thức định dạng theo cách thủ công để so sánh hai danh sách của bạn.
- Trên các cửa sổ, bạn sẽ tìm thấy nó ở cuối danh sách quy tắc trong hộp "Chọn loại quy tắc".
- Trên Mac, lựa chọn Cổ điển trong menu thả xuống "Kiểu" ở đầu cửa sổ bật lên. Sau đó, tìm tùy chọn này trong menu thả xuống thứ hai bên dưới menu Kiểu.
Bước 13. Nhấp vào trường công thức trong cửa sổ bật lên
Bạn có thể nhập bất kỳ công thức Excel hợp lệ nào tại đây để thiết lập quy tắc định dạng có điều kiện.
Bước 14. Gõ = countif (List2, A1) = 1 vào thanh công thức
Công thức này sẽ quét hai danh sách của bạn và đánh dấu tất cả các ô trong danh sách đầu tiên của bạn cũng xuất hiện trong danh sách thứ hai.
- Thay A1 trong công thức bằng số ô đầu tiên trong danh sách đầu tiên của bạn.
- Ví dụ: nếu ô đầu tiên của danh sách đầu tiên của bạn là ô D5, thì công thức của bạn sẽ giống như = countif (List2, D5) = 1.
- Nếu bạn đã đặt một tên khác cho danh sách thứ hai của mình, hãy đảm bảo thay thế List2 trong công thức bằng tên thực của danh sách của riêng bạn.
- Ngoài ra, hãy thay đổi công thức thành = countif (List2, A1) = 0 nếu bạn muốn đánh dấu các ô đừng xuất hiện trong danh sách thứ hai.
Bước 15. Gõ = countif (List1, B1) = 1 vào thanh công thức (tùy chọn)
Nếu bạn muốn tìm và đánh dấu các ô trên thứ hai danh sách cũng xuất hiện trên danh sách đầu tiên, hãy sử dụng công thức này thay vì công thức đầu tiên.
Thay thế List1 bằng tên của danh sách đầu tiên của bạn và B1 bằng ô đầu tiên của danh sách thứ hai của bạn
Bước 16. Chọn một định dạng tùy chỉnh để đánh dấu các ô (tùy chọn)
Bạn có thể chọn màu tô nền tùy chỉnh và các kiểu phông chữ khác nhau để đánh dấu các ô mà công thức của bạn tìm thấy.
- Trên các cửa sổ, nhấn vào Sự sắp xếp ở dưới cùng bên phải của cửa sổ bật lên. Bạn có thể chọn màu nền trong tab "Tô màu" và kiểu phông chữ trong tab "Phông chữ".
- Trên Mac, hãy chọn một định dạng đặt trước trên menu thả xuống "Định dạng với" ở dưới cùng. Bạn cũng có thể chọn định dạng tùy chỉnh ở đây để chọn kiểu tô nền và kiểu phông chữ theo cách thủ công.
Bước 17. Nhấp vào OK trong cửa sổ bật lên
Điều này sẽ xác nhận và áp dụng công thức so sánh của bạn. Tất cả các ô trong danh sách đầu tiên của bạn cũng xuất hiện trong danh sách thứ hai sẽ được đánh dấu bằng màu và phông chữ đã chọn của bạn.
- Ví dụ: nếu bạn chọn tô màu đỏ nhạt với văn bản màu đỏ đậm, tất cả các ô lặp lại sẽ chuyển sang màu này trong danh sách đầu tiên của bạn.
- Nếu bạn sử dụng công thức thứ hai ở trên, định dạng có điều kiện sẽ đánh dấu các ô lặp lại trên danh sách thứ hai của bạn thay vì ô đầu tiên.
Phương pháp 2/2: Sử dụng Công thức VLookup
Bước 1. Mở bảng tính Excel của bạn
Tìm tệp Excel có danh sách bạn muốn so sánh và bấm đúp vào tên tệp hoặc biểu tượng để mở bảng tính trong Microsoft Excel.
Bước 2. Nhấp vào ô trống bên cạnh mục đầu tiên trong danh sách thứ hai của bạn
Tìm danh sách thứ hai của bạn trên bảng tính và nhấp vào ô trống bên cạnh mục danh sách đầu tiên ở trên cùng.
- Bạn có thể chèn công thức VLookup của mình tại đây.
- Ngoài ra, bạn có thể chọn bất kỳ ô trống nào trên bảng tính của mình. Ô này sẽ chỉ giúp bạn thuận tiện hơn khi xem so sánh của mình bên cạnh danh sách thứ hai.
Bước 3. Gõ = vlookup (vào ô trống
Công thức VLookup sẽ cho phép bạn so sánh tất cả các mục trên hai danh sách riêng biệt và xem một giá trị là giá trị lặp lại hay giá trị mới.
Không đóng ngoặc công thức cho đến khi công thức của bạn hoàn tất
Bước 4. Chọn mục đầu tiên trong danh sách thứ hai của bạn
Không đóng ngoặc công thức, hãy nhấp vào mục đầu tiên trong danh sách thứ hai của bạn. Thao tác này sẽ chèn ô đầu tiên của danh sách thứ hai của bạn vào công thức.
Bước 5. Nhập a, dấu phẩy vào công thức
Sau khi chọn ô đầu tiên của danh sách thứ hai, hãy nhập dấu phẩy vào công thức. Tiếp theo, bạn sẽ có thể chọn phạm vi so sánh của mình.
Bước 6. Nhấn giữ và chọn toàn bộ danh sách đầu tiên của bạn
Thao tác này sẽ chèn phạm vi ô của danh sách đầu tiên của bạn vào phần thứ hai của công thức VLookup.
Điều này sẽ cho phép bạn tìm kiếm mục đã chọn từ danh sách thứ hai trong danh sách đầu tiên (mục đầu tiên ở đầu danh sách thứ hai) và quay lại nếu đó là giá trị lặp lại hoặc mới
Bước 7. Nhập a, dấu phẩy vào công thức
Điều này sẽ khóa phạm vi so sánh trong công thức của bạn.
Bước 8. Nhập 1 vào công thức sau dấu phẩy
Con số này đại diện cho số chỉ mục cột của bạn. Nó sẽ nhắc công thức VLookup tìm kiếm cột danh sách thực tế thay vì một cột khác bên cạnh nó.
Nếu bạn muốn công thức của mình trả về giá trị từ cột ngay bên cạnh danh sách đầu tiên, hãy nhập 2 vào đây
Bước 9. Nhập a, dấu phẩy vào công thức
Điều này sẽ khóa số chỉ mục cột của bạn (1) trong công thức VLookup.
Bước 10. Gõ FALSE vào công thức
Thao tác này sẽ tìm kiếm trong danh sách một kết quả phù hợp chính xác của mục tìm kiếm đã chọn (mục đầu tiên ở đầu danh sách thứ hai) thay vì các kết quả gần đúng.
- Thay vì FALSE, bạn có thể sử dụng 0, nó hoàn toàn giống nhau.
- Ngoài ra, bạn có thể nhập TRUE hoặc 1 nếu bạn muốn tìm kiếm kết quả gần đúng.
Bước 11. Nhập) ở cuối để đóng công thức
Bây giờ bạn có thể chạy công thức của mình và xem mục tìm kiếm đã chọn trong danh sách thứ hai của bạn là giá trị lặp lại hay mới.
Ví dụ: nếu danh sách thứ hai của bạn bắt đầu từ B1 và danh sách đầu tiên của bạn đi từ ô A1 đến A5, công thức của bạn sẽ giống như = vlookup (B1, $ A $ 1: $ A $ 5, 1, sai).
Bước 12. Nhấn ↵ Enter hoặc ⏎ Quay lại bàn phím của bạn.
Thao tác này sẽ chạy công thức và tìm kiếm mục đầu tiên trong danh sách đầu tiên từ danh sách thứ hai của bạn.
- Nếu đây là một giá trị lặp lại, bạn sẽ thấy cùng một giá trị được in lại trong ô công thức.
- Nếu đây là một giá trị mới, bạn sẽ thấy " # N / A"được in tại đây.
- Ví dụ: nếu bạn đang tìm kiếm "John" trong danh sách đầu tiên và bây giờ thấy "John" trong ô công thức, thì đó là một giá trị lặp lại xuất hiện trên cả hai danh sách. Nếu bạn thấy "# N / A", đó là một giá trị mới trong danh sách thứ hai.
Bước 13. Chọn ô công thức của bạn
Sau khi chạy công thức và xem kết quả cho mục danh sách đầu tiên, hãy nhấp vào ô công thức để chọn nó.
Bước 14. Nhấp và kéo xuống chấm màu xanh lục ở góc dưới bên phải của ô
Thao tác này sẽ mở rộng ô công thức của bạn dọc theo danh sách và áp dụng công thức cho mọi mục danh sách trong danh sách thứ hai của bạn.
- Bằng cách này, bạn có thể so sánh mọi mục trong danh sách thứ hai với toàn bộ danh sách đầu tiên.
- Thao tác này sẽ tìm kiếm từng mục trong danh sách thứ hai của bạn trong danh sách đầu tiên và hiển thị kết quả bên cạnh từng ô riêng biệt.
- Nếu bạn muốn thấy một điểm đánh dấu khác cho các giá trị mới thay vì "# N / A", hãy sử dụng công thức này: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "Giá trị mới"). Thao tác này sẽ in "Giá trị mới" cho các giá trị mới thay vì "# N / A".
