Đêm qua trong chương trình radio Computer America của Craig Crossman, Joe đến từ Boston đã có một câu hỏi:
Tôi có một cột số hóa đơn. Làm cách nào để sử dụng Excel để đánh dấu các bản sao?
Tôi đã đề xuất sử dụng các định dạng có điều kiện và công thức COUNTIF. Dưới đây là chi tiết về cách làm cho điều đó hoạt động.
Chúng tôi muốn thiết lập định dạng có điều kiện cho toàn bộ phạm vi, nhưng sẽ dễ dàng hơn nếu thiết lập định dạng có điều kiện cho ô đầu tiên trong phạm vi và sau đó sao chép định dạng có điều kiện đó. Trong trường hợp của chúng tôi, ô A1 có tiêu đề là số hóa đơn, vì vậy tôi sẽ chọn ô A2 và từ menu, chọn Định dạng> Định dạng có Điều kiện. Hộp thoại Định dạng có Điều kiện bắt đầu với trình đơn thả xuống ban đầu có nội dung "Giá trị Ô là". Nếu bạn chạm vào mũi tên bên cạnh mục này, bạn có thể chọn "Công thức là".
Sau khi chọn "Công thức Là", hộp thoại thay đổi hình thức. Thay vì các hộp cho "Giữa x và y", giờ đây có một hộp công thức duy nhất. Hộp công thức này cực kỳ mạnh mẽ. Bạn có thể nhập bất kỳ công thức nào mà bạn có thể mơ ước, miễn là công thức đó sẽ đánh giá là TRUE hoặc FALSE.
Trong trường hợp của chúng tôi, chúng tôi cần sử dụng công thức COUNTIF. Công thức để nhập vào ô là
=COUNTIF(A:A,A2)>1
Trong tiếng Anh, điều này có nghĩa là, "hãy nhìn qua toàn bộ phạm vi của cột A. Đếm xem có bao nhiêu ô trong phạm vi đó có cùng giá trị với giá trị trong ô A2. (Điều thực sự quan trọng là" A2 "trong công thức phải trỏ đến ô hiện tại - ô mà bạn đang thiết lập định dạng có điều kiện. Vì vậy - nếu dữ liệu của bạn nằm trong cột E và bạn đang thiết lập định dạng có điều kiện đầu tiên trong E5, công thức sẽ là =COUNTIF(E:E,E5)>0). Sau đó, chúng tôi so sánh để xem liệu số là> 1. Tốt nhất, không có trùng lặp, số đếm sẽ luôn là 1 - vì ô A2 nằm trong phạm vi - chúng ta nên tìm chính xác một ô trong cột A có chứa cùng giá trị với A2.
Nhấp vào nút Định dạng…
Bây giờ là lúc để chọn một định dạng đáng ghét. Có ba tab trên đầu hộp thoại Định dạng Ô này. Tab Phông chữ thường ở đầu tiên, vì vậy bạn có thể chọn phông chữ Đậm, màu đỏ, nhưng tôi thích thứ gì đó khó hiểu hơn. Tôi thường nhấp vào tab Mẫu và chọn màu đỏ tươi hoặc vàng tươi. Chọn màu, sau đó bấm OK để đóng hộp thoại Định dạng ô.
Bạn sẽ thấy định dạng đã chọn trong hộp "Xem trước định dạng để sử dụng". Nhấp vào OK để đóng hộp thoại Định dạng có điều kiện…
… Và không có gì xảy ra. Chà. Nếu đây là lần đầu tiên bạn thiết lập định dạng có điều kiện, sẽ rất vui nếu nhận được một số phản hồi ở đây rằng nó đã hoạt động. Nhưng, trừ khi bạn đủ may mắn rằng 1098 trong ô A2 là một bản sao của một số ô khác, điều kiện không đúng và có vẻ như không có gì xảy ra.
Bạn cần sao chép định dạng có điều kiện từ A2 xuống các ô khác trong phạm vi của bạn. Với ngưỡng con trỏ trong A2, thực hiện Chỉnh sửa> Sao chép. Nhấn Ctrl + Phím cách để chọn toàn bộ cột. Thực hiện Chỉnh sửa> Dán Đặc biệt. Trong hộp thoại Dán Đặc biệt, bấm vào Định dạng. Bấm OK.
Thao tác này sẽ sao chép định dạng có điều kiện vào tất cả các ô trong cột. Bây giờ - cuối cùng - bạn thấy một số ô có định dạng màu đỏ, cho biết rằng bạn có một bản sao.
Sẽ rất hữu ích khi chuyển đến ô A3 và xem định dạng có điều kiện sau bản sao. Chọn A3, nhấn od để hiển thị định dạng có điều kiện. Công thức trong hộp Công thức Là như đã thay đổi để đếm số lần A3 xuất hiện trong cột A: A.
Ghi chú
Trong câu hỏi của Joe, anh ta chỉ có 1700 hóa đơn trong phạm vi. Tôi đã thiết lập 65536 ô với định dạng có điều kiện và mỗi ô đang so sánh ô hiện tại với 65536 ô khác. Trong Excel 2005 - với nhiều hàng hơn - vấn đề sẽ còn tồi tệ hơn. Về mặt kỹ thuật, công thức trong bước đầu tiên có thể là:=COUNTIF($A$2:$A$1751,A2)>1
Ngoài ra, khi sao chép định dạng có điều kiện vào toàn bộ cột, thay vào đó bạn có thể chỉ chọn các hàng có dữ liệu trước khi thực hiện Dán Định dạng Đặc biệt.
Hơn
Một vấn đề khác mà tôi đã mô tả sau câu hỏi là bạn thực sự không thể sắp xếp một cột trên cơ sở định dạng có điều kiện. Nếu bạn cần sắp xếp dữ liệu này để các dữ liệu trùng lặp nằm trong một khu vực, hãy làm theo các bước sau. Đầu tiên, Thêm một tiêu đề vào B1 được gọi là "Nhân bản?". Nhập công thức này vào B2 : =COUNTIF(A:A,A2)>1.
Với con trỏ ô trong B2, hãy nhấp vào ô điều khiển tự động điền (hình vuông nhỏ ở góc dưới bên phải của ô) để sao chép công thức xuống toàn bộ phạm vi.
Bây giờ bạn có thể sắp xếp theo cột B giảm dần và A tăng dần để có các hóa đơn có vấn đề ở đầu phạm vi.
Giải pháp này giả định rằng bạn muốn đánh dấu CẢ HAI hóa đơn trùng lặp để bạn có thể tự tìm ra cái nào cần xóa hoặc sửa. Nếu bạn không muốn đánh dấu sự xuất hiện đầu tiên của trùng lặp, bạn có thể điều chỉnh công thức là: =COUNTIF($A$2:$A2,A2)>1. Điều quan trọng là nhập các ký hiệu đô la chính xác như được hiển thị. Thao tác này sẽ chỉ xem xét tất cả các ô từ ô hiện tại trở lên, tìm kiếm các mục nhập trùng lặp.
Cảm ơn Joe từ Boston cho câu hỏi!
