Χθες το βράδυ στο ραδιοφωνικό σόου του Craig Crossman's Computer America, ο Joe από τη Βοστώνη είχε μια ερώτηση:
Έχω μια στήλη αριθμών τιμολογίου. Πώς μπορώ να χρησιμοποιήσω το Excel για να επισημάνω τα διπλότυπα;
Πρότεινα τη χρήση μορφών υπό όρους και τον τύπο COUNTIF. Ακολουθούν οι λεπτομέρειες σχετικά με τον τρόπο λειτουργίας.
Θέλουμε να ρυθμίσουμε τη μορφοποίηση υπό όρους για ολόκληρο το εύρος, αλλά είναι πιο εύκολο να ρυθμίσετε μια μορφή υπό όρους για το πρώτο κελί της περιοχής και, στη συνέχεια, να αντιγράψετε αυτήν τη μορφή υπό όρους. Στην περίπτωσή μας, το κελί A1 έχει επικεφαλίδα του αριθμού τιμολογίου, οπότε θα επιλέξω το κελί A2 και από το μενού, επιλέξτε Μορφή> Μορφοποίηση υπό όρους. Ο διάλογος υπό όρους μορφοποίησης ξεκινά με το αρχικό αναπτυσσόμενο μενού λέγοντας "Cell Value Is". Εάν αγγίξετε το βέλος δίπλα σε αυτό, μπορείτε να επιλέξετε "Formula Is".
Αφού επιλέξετε "Formula Is", το παράθυρο διαλόγου αλλάζει εμφάνιση. Αντί για κουτιά για "Μεταξύ x και y", υπάρχει τώρα ένα μόνο πλαίσιο τύπου. Αυτό το κουτί τύπων είναι απίστευτα ισχυρό. Μπορείτε να πληκτρολογήσετε οποιονδήποτε τύπο που μπορείτε να ονειρευτείτε, αρκεί αυτός ο τύπος να αξιολογηθεί σε TRUE ή FALSE.
Στην περίπτωσή μας, πρέπει να χρησιμοποιήσουμε έναν τύπο COUNTIF. Ο τύπος που πρέπει να πληκτρολογήσετε στο πλαίσιο είναι
=COUNTIF(A:A,A2)>1
Στα Αγγλικά, αυτό λέει, "Κοιτάξτε ολόκληρο το εύρος της στήλης A. Μετρήστε πόσα κελιά σε αυτό το εύρος έχουν την ίδια τιμή με αυτό που υπάρχει στο Α2. (Είναι πραγματικά σημαντικό το" A2 "στον τύπο να δείχνει προς το τρέχον κελί - το κελί στο οποίο ρυθμίζετε τη μορφοποίηση υπό όρους. Επομένως - εάν τα δεδομένα σας βρίσκονται στη στήλη Ε και ρυθμίζετε την πρώτη μορφοποίηση υπό όρους στο E5, ο τύπος θα ήταν =COUNTIF(E:E,E5)>0). Στη συνέχεια, συγκρίνουμε για να δούμε αν αυτό μετράει είναι> 1. Στην ιδανική περίπτωση, χωρίς διπλότυπα, ο αριθμός θα είναι πάντα 1 - επειδή το κελί A2 βρίσκεται στο εύρος - θα πρέπει να βρούμε ακριβώς ένα κελί στη στήλη Α που περιέχει την ίδια τιμή με το A2.
Κάντε κλικ στο κουμπί Μορφή…
Τώρα είναι καιρός να επιλέξετε μια ενοχλητική μορφή. Υπάρχουν τρεις καρτέλες στην κορυφή αυτού του διαλόγου Μορφοποίηση κελιών. Η καρτέλα Γραμματοσειρά είναι συνήθως πρώτη, ώστε να μπορείτε να επιλέξετε μια έντονη, κόκκινη γραμματοσειρά, αλλά μου αρέσει κάτι πιο ενοχλητικό. Συνήθως κάνω κλικ στην καρτέλα Μοτίβα και επιλέγω είτε έντονο κόκκινο είτε έντονο κίτρινο. Επιλέξτε το χρώμα και, στη συνέχεια, κάντε κλικ στο OK για να κλείσετε το διάλογο Μορφοποίηση κελιών.
Θα δείτε την επιλεγμένη μορφή στο πλαίσιο "Προεπισκόπηση μορφής προς χρήση". Κάντε κλικ στο OK για να κλείσετε το παράθυρο διαλόγου Μορφοποίηση υπό όρους…
… και δεν συμβαίνει τίποτα. Ουάου. Εάν αυτή είναι η πρώτη φορά που ρυθμίζετε τη μορφοποίηση υπό όρους, θα ήταν πολύ ωραίο να λάβετε κάποια σχόλια εδώ ότι λειτούργησε. Όμως, αν δεν είστε αρκετά τυχεροί που το 1098 στο κελί A2 είναι ένα αντίγραφο κάποιου άλλου κελιού, η κατάσταση δεν είναι αλήθεια και δεν φαίνεται να συνέβη τίποτα.
Πρέπει να αντιγράψετε τη μορφοποίηση υπό όρους από το A2 προς τα άλλα κελιά της περιοχής σας. Με το περβάζι του δρομέα στο A2, κάντε Επεξεργασία> Αντιγραφή. Πατήστε Ctrl + Spacebar για να επιλέξετε ολόκληρη τη στήλη. Επεξεργασία> Ειδική επικόλληση. Στο διάλογο Ειδική επικόλληση, κάντε κλικ στην επιλογή Μορφές. Κάντε κλικ στο OK.
Αυτό θα αντιγράψει τη μορφοποίηση υπό όρους σε όλα τα κελιά της στήλης. Τώρα - επιτέλους - βλέπετε κάποια κελιά με την κόκκινη μορφοποίηση, υποδεικνύοντας ότι έχετε ένα αντίγραφο.
Είναι ενημερωτικό να μεταβείτε στο κελί A3 και να δείτε τη μορφή υπό όρους μετά το αντίγραφο. Επιλέξτε A3, πατήστε od για να εμφανιστεί η μορφοποίηση υπό όρους. Ο τύπος στο πλαίσιο Formula Is όπως άλλαξε για να μετρήσει πόσες φορές εμφανίζεται το A3 στη στήλη A: A.
Σημειώσεις
Στην ερώτηση του Τζο, είχε μόνο 1700 τιμολόγια στο εύρος. Έχω ρυθμίσει 65536 κελιά με μορφοποίηση υπό όρους και κάθε κελί συγκρίνει το τρέχον κελί με 65536 άλλα κελιά. Στο Excel 2005 - με περισσότερες σειρές - το πρόβλημα θα είναι ακόμη χειρότερο. Τεχνικά, ο τύπος στο πρώτο βήμα θα μπορούσε να ήταν:=COUNTIF($A$2:$A$1751,A2)>1
Επίσης, κατά την αντιγραφή της μορφής υπό όρους σε ολόκληρη τη στήλη, θα μπορούσατε να έχετε επιλέξει μόνο τις σειρές με δεδομένα πριν κάνετε την Επικόλληση ειδικών μορφών.
Περισσότερο
Το άλλο ζήτημα που περιέγραψα μετά την ερώτηση είναι ότι πραγματικά δεν μπορείτε να ταξινομήσετε μια στήλη βάσει μιας μορφής υπό όρους. Εάν πρέπει να ταξινομήσετε αυτά τα δεδομένα έτσι ώστε τα αντίγραφα να βρίσκονται σε μία περιοχή, ακολουθήστε αυτά τα βήματα. Πρώτα, Προσθέστε μια επικεφαλίδα στο B1 που ονομάζεται "Διπλότυπο;". Πληκτρολογήστε αυτόν τον τύπο στο Β2: =COUNTIF(A:A,A2)>1.
Με το δείκτη κελιού στο B2, κάντε κλικ στη λαβή αυτόματης συμπλήρωσης (το μικρό τετράγωνο στην κάτω δεξιά γωνία του κελιού) για να αντιγράψετε τον τύπο μέχρι το εύρος.
Τώρα μπορείτε να ταξινομήσετε κατά στήλη Β φθίνουσα και Α αύξουσα για να έχετε τα προβλήματα τιμολόγια στην κορυφή του εύρους.
Αυτή η λύση προϋποθέτει ότι θέλετε να επισημάνετε και τα δύο από τα διπλά τιμολόγια, ώστε να μπορείτε να καταλάβετε με μη αυτόματο τρόπο ποια διαγραφή ή διόρθωση. Εάν δεν θέλετε να σηματοδοτήσει την πρώτη εμφάνιση του αντιγράφου, μπορείτε να ρυθμίσετε τον τύπο να είναι: =COUNTIF($A$2:$A2,A2)>1. Είναι σημαντικό να εισαγάγετε τα σύμβολα του δολαρίου ακριβώς όπως φαίνεται. Αυτό θα κοιτάξει μόνο όλα τα κελιά από το τρέχον κελί, αναζητώντας διπλές καταχωρήσεις.
Ευχαριστώ τον Joe από τη Βοστώνη για την ερώτηση!
