Σύγκριση πινάκων τιμών 1c 8. Πώς να συγκρίνετε δύο στήλες στο Excel για αντιστοιχίσεις

Η ανάγνωση αυτού του άρθρου θα σας πάρει περίπου 10 λεπτά. Στα επόμενα 5 λεπτά μπορείτε εύκολα να συγκρίνετε δύο στήλες στο Excel και να μάθετε εάν υπάρχουν διπλότυπες, να τις διαγράψετε ή να τις επισημάνετε με χρώμα. Έφτασε λοιπόν η ώρα!

Το Excel είναι μια πολύ ισχυρή και πραγματικά δροσερή εφαρμογή για τη δημιουργία και την επεξεργασία μεγάλων ποσοτήτων δεδομένων. Εάν έχετε πολλά βιβλία εργασίας με δεδομένα (ή μόνο έναν τεράστιο πίνακα), τότε πιθανότατα θα θέλετε να συγκρίνετε 2 στήλες, να βρείτε διπλές τιμές και, στη συνέχεια, να κάνετε κάτι με αυτές, όπως διαγραφή, επισήμανση ή εκκαθάριση των περιεχομένων . Οι στήλες μπορεί να βρίσκονται στον ίδιο πίνακα, γειτονικές ή μη, να βρίσκονται σε 2 διαφορετικά φύλλα ή ακόμα και σε διαφορετικά βιβλία εργασίας.

Φανταστείτε ότι έχουμε 2 στήλες με ονόματα ανθρώπων - 5 ονόματα ανά στήλη ΕΝΑκαι 3 ονόματα σε μια στήλη σι. Πρέπει να συγκρίνετε τα ονόματα σε αυτές τις δύο στήλες και να βρείτε τυχόν διπλότυπα. Όπως καταλαβαίνετε, πρόκειται για εικονικά δεδομένα που λαμβάνονται μόνο για επεξηγηματικούς σκοπούς. Σε πραγματικούς πίνακες έχουμε να κάνουμε με χιλιάδες, ή και δεκάδες χιλιάδες δίσκους.

Επιλογή Α:και οι δύο στήλες βρίσκονται στο ίδιο φύλλο. Για παράδειγμα, στήλη ΕΝΑκαι στήλη σι.

Επιλογή Β:Οι στήλες βρίσκονται σε διαφορετικά φύλλα. Για παράδειγμα, στήλη ΕΝΑσε ένα φύλλο Φύλλο2και στήλη ΕΝΑσε ένα φύλλο Φύλλο 3.

Τα Excel 2013, 2010 και 2007 διαθέτουν ένα ενσωματωμένο εργαλείο Αφαιρέστε το διπλότυπο(Κατάργηση διπλότυπων) αλλά είναι ανίσχυρο σε αυτήν την περίπτωση επειδή δεν μπορεί να συγκρίνει τα δεδομένα σε 2 στήλες. Επιπλέον, μπορεί να αφαιρέσει μόνο διπλότυπα. Δεν υπάρχουν άλλες επιλογές, όπως επισήμανση ή αλλαγή χρωμάτων. Και περίοδος!

Συγκρίνετε 2 στήλες στο Excel και βρείτε διπλότυπες εγγραφές χρησιμοποιώντας τύπους

Επιλογή Α: και οι δύο στήλες βρίσκονται στο ίδιο φύλλο

Ενδειξη:Σε μεγάλους πίνακες, η αντιγραφή ενός τύπου θα είναι πιο γρήγορη εάν χρησιμοποιείτε συντομεύσεις πληκτρολογίου. Επιλέξτε ένα κελί Γ1και πατήστε Ctrl+C(για να αντιγράψετε τον τύπο στο πρόχειρο) και μετά κάντε κλικ Ctrl+Shift+End(για να επιλέξετε όλα τα μη κενά κελιά στη στήλη Γ) και τέλος κάντε κλικ Ctrl+V(για να επικολλήσετε τον τύπο σε όλα τα επιλεγμένα κελιά).

Επιλογή Β: δύο στήλες βρίσκονται σε διαφορετικά φύλλα (σε διαφορετικά βιβλία)

Επεξεργασία που βρέθηκαν διπλότυπα

Ωραία, βρήκαμε καταχωρήσεις στην πρώτη στήλη που υπάρχουν και στη δεύτερη στήλη. Τώρα πρέπει να κάνουμε κάτι μαζί τους. Η μη αυτόματη διεξαγωγή όλων των διπλότυπων εγγραφών σε έναν πίνακα είναι αρκετά αναποτελεσματική και απαιτεί πολύ χρόνο. Υπάρχουν καλύτεροι τρόποι.

Εμφάνιση μόνο διπλών σειρών στη στήλη Α

Εάν οι στήλες σας δεν έχουν επικεφαλίδες, τότε πρέπει να τις προσθέσετε. Για να το κάνετε αυτό, τοποθετήστε τον κέρσορα στον αριθμό που υποδεικνύει την πρώτη γραμμή και θα μετατραπεί σε μαύρο βέλος, όπως φαίνεται στην παρακάτω εικόνα:

Κάντε δεξί κλικ και επιλέξτε από το μενού περιβάλλοντος Εισάγετε(Εισάγετε):

Δώστε τα ονόματα των στηλών, για παράδειγμα, " Ονομα" Και " Αντίγραφο?” Στη συνέχεια, ανοίξτε την καρτέλα Δεδομένα(Δεδομένα) και πατήστε Φίλτρο(Φίλτρο):

Μετά από αυτό, κάντε κλικ στο μικρό γκρι βέλος δίπλα στο " Αντίγραφο?“ για επέκταση του μενού φίλτρου. καταργήστε την επιλογή όλων των στοιχείων αυτής της λίστας εκτός από Αντίγραφοκαι πατήστε Εντάξει.

Αυτό ήταν, τώρα βλέπετε μόνο αυτά τα στοιχεία στηλών ΕΝΑ, τα οποία είναι διπλότυπα στη στήλη ΣΕ. Υπάρχουν μόνο δύο τέτοια κελιά στον προπονητικό μας πίνακα, αλλά, όπως καταλαβαίνετε, στην πράξη θα υπάρχουν πολλά περισσότερα από αυτά.

Για να εμφανιστούν ξανά όλες οι σειρές μιας στήλης ΕΝΑ, κάντε κλικ στο σύμβολο φίλτρου στη στήλη ΣΕ, που τώρα μοιάζει με χωνί με ένα μικρό βέλος και επιλέξτε Επιλογή όλων(Επιλογή όλων). Ή μπορείτε να κάνετε το ίδιο μέσω της Κορδέλας κάνοντας κλικ Δεδομένα(Δεδομένα) > Επιλογή & Φιλτράρισμα(Ταξινόμηση και Φιλτράρισμα) > Σαφή(Διαγραφή) όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

Αλλάξτε το χρώμα ή επισημάνετε τα διπλότυπα που βρέθηκαν

Εάν τα σημάδια " ΑντίγραφοΤο " δεν επαρκεί για τους σκοπούς σας και θέλετε να επισημάνετε διπλότυπα κελιά με διαφορετικό χρώμα γραμματοσειράς, χρώμα γεμίσματος ή με κάποιο άλλο τρόπο...

Σε αυτήν την περίπτωση, φιλτράρετε τα διπλότυπα όπως φαίνεται παραπάνω, επιλέξτε όλα τα φιλτραρισμένα κελιά και κάντε κλικ Ctrl+1για να ανοίξετε το παράθυρο διαλόγου Μορφοποίηση κελιών(Μορφή κελιού). Για παράδειγμα, ας αλλάξουμε το χρώμα πλήρωσης των κελιών σε σειρές με διπλότυπα σε έντονο κίτρινο. Φυσικά, μπορείτε να αλλάξετε το χρώμα πλήρωσης χρησιμοποιώντας το εργαλείο Γέμισμακαρτέλα (Χρώμα γεμίσματος). Σπίτι(Αρχική), αλλά το πλεονέκτημα του πλαισίου διαλόγου Μορφοποίηση κελιών(Μορφοποίηση κελιών) είναι ότι μπορείτε να διαμορφώσετε όλες τις επιλογές μορφοποίησης ταυτόχρονα.

Τώρα σίγουρα δεν θα χάσετε ούτε ένα κελί με διπλότυπα:

Αφαίρεση διπλότυπων τιμών από την πρώτη στήλη

Φιλτράρετε τον πίνακα για να εμφανίζονται μόνο κελιά με διπλότυπες τιμές και επιλέξτε αυτά τα κελιά.

Εάν οι 2 στήλες που συγκρίνετε βρίσκονται σε διαφορετικά φύλλα, δηλαδή σε διαφορετικούς πίνακες, κάντε δεξί κλικ στην επιλεγμένη περιοχή και επιλέξτε από το μενού περιβάλλοντος Διαγραφή σειράς(Διαγραφή γραμμής):

Κάντε κλικ Εντάξειόταν το Excel σάς ζητά να επιβεβαιώσετε ότι θέλετε πραγματικά να διαγράψετε ολόκληρη τη σειρά του φύλλου εργασίας και στη συνέχεια να διαγράψετε το φίλτρο. Όπως μπορείτε να δείτε, παραμένουν μόνο σειρές με μοναδικές τιμές:

Αν σε ένα φύλλο βρίσκονται 2 στήλες, κοντά το ένα στο άλλο (γειτονικό) ή όχι κοντά το ένα στο άλλο (όχι γειτονικό), τότε η διαδικασία αφαίρεσης των διπλότυπων θα είναι λίγο πιο δύσκολη. Δεν μπορούμε να διαγράψουμε ολόκληρη τη σειρά με διπλότυπες τιμές, επειδή αυτό θα διαγράψει κελιά και από τη δεύτερη στήλη. Έτσι, για να διατηρείτε μόνο μοναδικές καταχωρήσεις σε μια στήλη ΕΝΑ, κάντε τα εξής:

Όπως μπορείτε να δείτε, η κατάργηση των διπλότυπων από δύο στήλες στο Excel χρησιμοποιώντας τύπους δεν είναι τόσο δύσκολη.

Μερικές φορές υπάρχει ανάγκη σύγκρισης δύο αρχείων MS Excel. Αυτό μπορεί να είναι η εύρεση αποκλίσεων στις τιμές για ορισμένα είδη ή η αλλαγή οποιωνδήποτε ενδείξεων, δεν έχει σημασία, το κύριο πράγμα είναι ότι είναι απαραίτητο να βρεθούν ορισμένες αποκλίσεις.

Δεν θα ήταν λάθος να αναφέρουμε ότι εάν υπάρχουν μερικές εγγραφές στο αρχείο MS Excel, τότε δεν έχει νόημα να καταφύγουμε στον αυτοματισμό. Εάν το αρχείο περιέχει αρκετές εκατοντάδες, ή ακόμα και χιλιάδες εγγραφές, τότε είναι αδύνατο να γίνει χωρίς τη βοήθεια της υπολογιστικής ισχύος ενός υπολογιστή.

Ας προσομοιώσουμε μια κατάσταση όπου δύο αρχεία έχουν τον ίδιο αριθμό γραμμών και η διαφορά πρέπει να αναζητηθεί σε μια συγκεκριμένη στήλη ή σε πολλές στήλες. Αυτή η κατάσταση είναι δυνατή, για παράδειγμα, εάν πρέπει να συγκρίνετε την τιμή των αγαθών σύμφωνα με δύο τιμοκαταλόγους ή να συγκρίνετε μετρήσεις αθλητών πριν και μετά την προπονητική περίοδο, αν και για τέτοιους αυτοματισμούς πρέπει να υπάρχουν πολλές από αυτές.

Ως παράδειγμα εργασίας, ας πάρουμε ένα αρχείο με την απόδοση εικονικών συμμετεχόντων: τρέξιμο 100 μέτρων, τρέξιμο 3000 μέτρων και έλξεις. Το πρώτο αρχείο είναι μια μέτρηση στην αρχή της σεζόν και το δεύτερο είναι το τέλος της σεζόν.

Ο πρώτος τρόπος επίλυσης του προβλήματος. Η λύση είναι μόνο η χρήση τύπων MS Excel.

Δεδομένου ότι οι εγγραφές είναι διατεταγμένες κάθετα (η πιο λογική διάταξη), είναι απαραίτητο να χρησιμοποιήσετε τη συνάρτηση. Εάν χρησιμοποιείτε οριζόντια τοποθέτηση εγγραφών, θα πρέπει να χρησιμοποιήσετε τη συνάρτηση.

Για να συγκρίνετε τις επιδόσεις τρεξίματος 100 μέτρων, ο τύπος έχει ως εξής:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"Καμία διαφορά")
Εάν δεν υπάρχει διαφορά, εμφανίζεται ένα μήνυμα ότι δεν υπάρχει διαφορά, εάν υπάρχει διαφορά, τότε η τιμή στο τέλος της σεζόν αφαιρείται από την τιμή στην αρχή της σεζόν.

Η φόρμουλα για το τρέξιμο των 3000 μέτρων είναι η εξής:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;"Υπάρχει διαφορά";"Δεν υπάρχει διαφορά")
Εάν η τελική και η αρχική τιμή δεν είναι ίσες, εμφανίζεται ένα αντίστοιχο μήνυμα. Η φόρμουλα για έλξεις μπορεί να είναι παρόμοια με οποιαδήποτε από τις προηγούμενες· δεν έχει νόημα να την προσφέρουμε επιπλέον. Το τελικό αρχείο με τις αποκλίσεις που βρέθηκαν φαίνεται παρακάτω.

Μια μικρή διευκρίνηση. Για να διευκολυνθεί η ανάγνωση των τύπων, τα δεδομένα από τα δύο αρχεία μετακινήθηκαν σε ένα (σε διαφορετικά φύλλα), αλλά αυτό δεν μπορούσε να γίνει.

Βίντεο που συγκρίνει δύο αρχεία MS Excel χρησιμοποιώντας και λειτουργίες.

Ο δεύτερος τρόπος επίλυσης του προβλήματος. Λύση με χρήση MS Access.

Αυτό το πρόβλημα μπορεί να λυθεί εάν πρώτα εισαγάγετε αρχεία MS Excel στην Access. Όσον αφορά την ίδια τη μέθοδο εισαγωγής εξωτερικών δεδομένων, δεν υπάρχει διαφορά στην εύρεση διαφορετικών πεδίων (οποιαδήποτε από τις επιλογές που παρουσιάζονται θα κάνει).

Το τελευταίο είναι μια σύνδεση μεταξύ αρχείων Excel και Access, επομένως όταν αλλάζετε δεδομένα σε αρχεία Excel, οι αποκλίσεις θα εντοπίζονται αυτόματα όταν εκτελείτε ένα ερώτημα στο MS Access.

Το επόμενο βήμα μετά την εισαγωγή είναι η δημιουργία σχέσεων μεταξύ πινάκων. Ως πεδίο σύνδεσης, επιλέξτε το μοναδικό πεδίο "Αριθμός αντικειμένου".
Το τρίτο βήμα είναι να δημιουργήσετε ένα απλό ερώτημα επιλογής χρησιμοποιώντας το Εργαλείο δημιουργίας ερωτημάτων.

Στην πρώτη στήλη υποδεικνύουμε ποιες εγγραφές πρέπει να εμφανίζονται και στη δεύτερη - υπό ποιες συνθήκες θα εμφανίζονται οι εγγραφές. Φυσικά, για το δεύτερο και το τρίτο πεδίο οι ενέργειες θα είναι παρόμοιες.

Βίντεο που συγκρίνει αρχεία MS με Excel χρησιμοποιώντας MS Access.

Ως αποτέλεσμα των χειρισμών που πραγματοποιήθηκαν, εμφανίζονται όλες οι εγγραφές, με διαφορετικά δεδομένα στο πεδίο: "Τρέχοντας 100 μέτρα". Το αρχείο MS Access παρουσιάζεται παρακάτω (δυστυχώς, το SkyDrive δεν επιτρέπει την ενσωμάτωση ως αρχείο Excel)

Αυτές οι δύο μέθοδοι υπάρχουν για την εύρεση αποκλίσεων στους πίνακες του MS Excel. Το καθένα έχει και πλεονεκτήματα και μειονεκτήματα. Προφανώς, αυτή δεν είναι μια εξαντλητική λίστα συγκρίσεων μεταξύ των δύο αρχείων Excel. Περιμένουμε τις προτάσεις σας στα σχόλια.

Έχουμε δύο πίνακες παραγγελιών αντιγραμμένους σε ένα φύλλο εργασίας. Είναι απαραίτητο να συγκρίνετε δεδομένα από δύο πίνακες στο Excel και να ελέγξετε ποιες θέσεις βρίσκονται στον πρώτο πίνακα αλλά όχι στον δεύτερο. Δεν έχει νόημα η μη αυτόματη σύγκριση της τιμής κάθε κελιού.

Συγκρίνετε δύο στήλες για αντιστοιχίσεις στο Excel

Πώς να συγκρίνετε τιμές στο Excel δύο στηλών; Για να λύσετε αυτό το πρόβλημα, συνιστούμε τη χρήση μορφοποίησης υπό όρους, η οποία επισημαίνει γρήγορα στοιχεία που βρίσκονται σε μία μόνο στήλη. Φύλλο εργασίας με πίνακες:

Το πρώτο βήμα είναι να ονομάσετε και τους δύο πίνακες. Αυτό διευκολύνει την κατανόηση ποιες περιοχές κελιών συγκρίνονται:

1. Επιλέξτε το εργαλείο FORMULAS - Defined Names - Assign Name.
2. Στο παράθυρο που εμφανίζεται, στο πεδίο «Όνομα:» εισάγετε την τιμή – Πίνακας_1.
3. Χρησιμοποιήστε το αριστερό κουμπί του ποντικιού για να κάνετε κλικ στο πεδίο εισαγωγής "Range:" και επιλέξτε το εύρος: A2:A15. Και κάντε κλικ στο OK.

Για τη δεύτερη λίστα, εκτελέστε τα ίδια βήματα, δώστε της μόνο όνομα – Πίνακας_2. Και καθορίστε το εύρος C2:C15 - αντίστοιχα.

Χρήσιμες συμβουλές! Τα ονόματα εύρους μπορούν να αντιστοιχιστούν πιο γρήγορα χρησιμοποιώντας το πεδίο ονομάτων. Βρίσκεται στα αριστερά της γραμμής τύπων. Απλώς επιλέξτε σειρές κελιών και στο πεδίο όνομα, πληκτρολογήστε το κατάλληλο όνομα για την περιοχή και πατήστε Enter.

Τώρα ας χρησιμοποιήσουμε τη μορφοποίηση υπό όρους για να συγκρίνουμε δύο λίστες στο Excel. Πρέπει να έχουμε το εξής αποτέλεσμα:

Τα στοιχεία που βρίσκονται στον Πίνακα_1 αλλά όχι στον Πίνακα_2 θα εμφανίζονται με πράσινο χρώμα. Ταυτόχρονα, οι θέσεις που βρίσκονται στον Πίνακα_2, αλλά όχι στον Πίνακα_1, θα επισημαίνονται με μπλε χρώμα.

Η αρχή της σύγκρισης δεδομένων μεταξύ δύο στηλών στο Excel

Κατά τον καθορισμό των συνθηκών για τη μορφοποίηση των κελιών στηλών, χρησιμοποιήσαμε τη συνάρτηση COUNTIF. Σε αυτό το παράδειγμα, αυτή η συνάρτηση ελέγχει πόσες φορές εμφανίζεται η τιμή του δεύτερου ορίσματος (για παράδειγμα, A2) στη λίστα του πρώτου ορίσματος (για παράδειγμα, Table_2). Εάν αριθμός φορών = 0, τότε ο τύπος επιστρέφει TRUE. Σε αυτήν την περίπτωση, στο κελί εκχωρείται η προσαρμοσμένη μορφή που καθορίζεται στις επιλογές μορφοποίησης υπό όρους.

Ο σύνδεσμος στο δεύτερο όρισμα είναι σχετικός, πράγμα που σημαίνει ότι όλα τα κελιά της επιλεγμένης περιοχής θα ελέγχονται ένα προς ένα (για παράδειγμα, A2:A15). Για παράδειγμα, για να συγκρίνετε δύο τιμοκαταλόγους στο Excel, ακόμη και σε διαφορετικά φύλλα. Ο δεύτερος τύπος λειτουργεί παρόμοια. Η ίδια αρχή μπορεί να εφαρμοστεί σε διάφορες παρόμοιες εργασίες.

Ίσως όλοι όσοι εργάζονται με δεδομένα στο Excel αντιμετωπίζουν το ερώτημα πώς να συγκρίνουν δύο στήλες στο Excel για ομοιότητες και διαφορές. Υπάρχουν διάφοροι τρόποι για να γίνει αυτό. Ας ρίξουμε μια πιο προσεκτική ματιά σε καθένα από αυτά.

Πώς να συγκρίνετε δύο στήλες στο Excel σειρά προς σειρά

Όταν συγκρίνετε δύο στήλες δεδομένων, είναι συχνά απαραίτητο να συγκρίνετε τα δεδομένα σε κάθε μεμονωμένη σειρά για αντιστοιχίσεις ή διαφορές. Μπορούμε να κάνουμε μια τέτοια ανάλυση χρησιμοποιώντας τη συνάρτηση. Ας δούμε πώς λειτουργεί αυτό χρησιμοποιώντας τα παρακάτω παραδείγματα.

Παράδειγμα 1: Πώς να συγκρίνετε δύο στήλες για αντιστοιχίσεις και διαφορές στην ίδια σειρά

Για να συγκρίνουμε τα δεδομένα σε κάθε γραμμή δύο στηλών στο Excel, ας γράψουμε έναν απλό τύπο. Ο τύπος πρέπει να εισαχθεί σε κάθε γραμμή στη διπλανή στήλη, δίπλα στον πίνακα στον οποίο βρίσκονται τα κύρια δεδομένα. Έχοντας δημιουργήσει έναν τύπο για την πρώτη σειρά του πίνακα, μπορούμε να τον τεντώσουμε/αντιγράψουμε στις υπόλοιπες σειρές.

Για να ελέγξουμε αν δύο στήλες της ίδιας σειράς περιέχουν τα ίδια δεδομένα, χρειαζόμαστε τον τύπο:

=IF(A2=B2; "Match"; "")

Ο τύπος που καθορίζει τις διαφορές μεταξύ των δεδομένων δύο στηλών σε μία σειρά θα μοιάζει με αυτό:

=IF(A2<>Β2; "Δεν ταιριάζουν"; "")

Μπορούμε να χωρέσουμε τη δοκιμή αντιστοίχισης και διαφοράς μεταξύ δύο στηλών σε μία γραμμή σε έναν τύπο:

=IF(A2=B2; "Match"; "Do not match")

=IF(A2<>Β2; "Δεν ταιριάζουν"; "Ταιριάξει")

Ένα παράδειγμα αποτελέσματος υπολογισμού μπορεί να μοιάζει με αυτό:

Για να συγκρίνετε δεδομένα σε δύο στήλες της ίδιας γραμμής με διάκριση πεζών-κεφαλαίων, χρησιμοποιήστε τον τύπο:

=IF(MATCH(A2,B2), "Match", "Μοναδικό")

Πώς να συγκρίνετε πολλές στήλες για αντιστοιχίσεις σε μια σειρά του Excel

Το Excel σάς επιτρέπει να συγκρίνετε δεδομένα σε πολλές στήλες της ίδιας γραμμής χρησιμοποιώντας τα ακόλουθα κριτήρια:

• Βρείτε σειρές με τις ίδιες τιμές σε όλες τις στήλες του πίνακα.
• Βρείτε σειρές με τις ίδιες τιμές σε οποιεσδήποτε δύο στήλες του πίνακα.

Παράδειγμα 1. Πώς να βρείτε αντιστοιχίσεις σε μια σειρά σε πολλές στήλες ενός πίνακα

Ας φανταστούμε ότι ο πίνακας μας αποτελείται από πολλές στήλες δεδομένων. Το καθήκον μας είναι να βρούμε σειρές στις οποίες οι τιμές ταιριάζουν σε όλες τις στήλες. Το Excel και οι λειτουργίες θα μας βοηθήσουν σε αυτό. Η φόρμουλα για τον καθορισμό των αντιστοιχιών θα είναι η εξής:

=IF(AND(A2=B2,A2=C2); "Συμπίπτει"; " ")

Εάν ο πίνακας μας έχει πολλές στήλες, τότε θα είναι ευκολότερο να χρησιμοποιήσουμε τη συνάρτηση σε συνδυασμό με:

=IF(COUNTIF($A2:$C2,$A2)=3;"Ταίριασμα";" ")

Στον τύπο, το "5" υποδεικνύει τον αριθμό των στηλών του πίνακα για τις οποίες δημιουργήσαμε τον τύπο. Εάν ο πίνακάς σας έχει περισσότερες ή λιγότερες στήλες, τότε αυτή η τιμή πρέπει να είναι ίση με τον αριθμό των στηλών.

Παράδειγμα 2: Πώς να βρείτε αντιστοιχίσεις της ίδιας γραμμής σε οποιεσδήποτε δύο στήλες ενός πίνακα

Ας φανταστούμε ότι το καθήκον μας είναι να προσδιορίσουμε από έναν πίνακα με δεδομένα σε πολλές στήλες εκείνες τις σειρές στις οποίες τα δεδομένα είναι ίδια ή επαναλαμβανόμενα σε τουλάχιστον δύο στήλες. Οι λειτουργίες και θα μας βοηθήσουν σε αυτό. Ας γράψουμε έναν τύπο για έναν πίνακα που αποτελείται από τρεις στήλες δεδομένων:

=IF(OR(A2=B2,B2=C2,A2=C2);"Match";" ")

Σε περιπτώσεις που υπάρχουν πάρα πολλές στήλες στον πίνακά μας, ο τύπος μας με τη συνάρτηση θα είναι πολύ μεγάλος, αφού στις παραμέτρους του πρέπει να καθορίσουμε τα κριτήρια αντιστοίχισης μεταξύ κάθε στήλης του πίνακα. Ένας ευκολότερος τρόπος, σε αυτήν την περίπτωση, είναι να χρησιμοποιήσετε το .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "Μοναδική συμβολοσειρά"; "Μη μοναδική συμβολοσειρά")

=IF(COUNTIF($B:$B,$A5)=0; "Δεν υπάρχουν αντιστοιχίσεις στη στήλη Β"; "Υπάρχουν αντιστοιχίσεις στη στήλη Β")

Αυτός ο τύπος ελέγχει τις τιμές στη στήλη Β για να δει αν ταιριάζουν με τα κελιά της στήλης Α.

Εάν ο πίνακάς σας αποτελείται από έναν σταθερό αριθμό σειρών, μπορείτε να καθορίσετε ένα σαφές εύρος στον τύπο (για παράδειγμα, $B2:$B10). Αυτό θα επιταχύνει τη φόρμουλα.

Πώς να συγκρίνετε δύο στήλες στο Excel για αντιστοιχίσεις και να τις επισημάνετε έγχρωμα

Όταν αναζητούμε αντιστοιχίσεις μεταξύ δύο στηλών στο Excel, ίσως χρειαστεί να οπτικοποιήσουμε τις αντιστοιχίσεις που βρέθηκαν ή τις διαφορές στα δεδομένα, για παράδειγμα χρησιμοποιώντας χρωματική επισήμανση. Ο ευκολότερος τρόπος για να επισημάνετε τις αντιστοιχίσεις και τις διαφορές στα χρώματα είναι να χρησιμοποιήσετε τη Μορφοποίηση υπό όρους στο Excel. Ας δούμε πώς να το κάνετε αυτό χρησιμοποιώντας τα παρακάτω παραδείγματα.

Εύρεση και επισήμανση αντιστοιχιών σε χρώμα σε πολλές στήλες στο Excel

Σε περιπτώσεις που πρέπει να βρούμε αντιστοιχίσεις σε πολλές στήλες, τότε για αυτό χρειαζόμαστε:

• Επιλέξτε τις στήλες με δεδομένα στις οποίες θέλετε να υπολογίσετε αντιστοιχίσεις.
• Στην καρτέλα "Αρχική σελίδα" στη γραμμή εργαλείων, κάντε κλικ στο στοιχείο μενού "Μορφοποίηση υπό όρους" -> "Κανόνες για την επισήμανση κελιών" -> "Επαναλαμβανόμενες τιμές".
• Στο αναδυόμενο παράθυρο διαλόγου, επιλέξτε "Επανάληψη" στην αριστερή αναπτυσσόμενη λίστα και στη δεξιά αναπτυσσόμενη λίστα, επιλέξτε σε ποιες τιμές επαναλαμβανόμενων χρωμάτων θα επισημανθούν. Κάντε κλικ στο κουμπί "OK":
• Μετά από αυτό, οι αντιστοιχίσεις θα επισημαίνονται στην επιλεγμένη στήλη:

Βρείτε και επισημάνετε τις αντίστοιχες σειρές στο Excel

Η εύρεση αντιστοίχισης κελιών με δεδομένα σε δύο ή περισσότερες στήλες και η αναζήτηση αντιστοιχιών ολόκληρων σειρών με δεδομένα είναι διαφορετικές έννοιες. Σημειώστε τους δύο παρακάτω πίνακες:

Οι παραπάνω πίνακες περιέχουν τα ίδια δεδομένα. Η διαφορά τους είναι ότι στο παράδειγμα στα αριστερά αναζητούσαμε κελιά που ταιριάζουν και στα δεξιά βρήκαμε ολόκληρες επαναλαμβανόμενες γραμμές με δεδομένα.

Ας δούμε πώς μπορείτε να βρείτε αντίστοιχες σειρές σε έναν πίνακα:

• Στα δεξιά του πίνακα με τα δεδομένα, θα δημιουργήσουμε μια βοηθητική στήλη στην οποία, απέναντι από κάθε γραμμή με τα δεδομένα, θα βάλουμε έναν τύπο που συνδυάζει όλες τις τιμές της γραμμής του πίνακα σε ένα κελί:

=A2&B2&C2&D2

Στη βοηθητική στήλη θα δείτε τα συνδυασμένα δεδομένα πίνακα:

Τώρα, για να προσδιορίσετε τις αντίστοιχες σειρές στον πίνακα, κάντε τα ακόλουθα βήματα:

• Επιλέξτε την περιοχή με δεδομένα στη βοηθητική στήλη (στο παράδειγμά μας αυτό είναι μια περιοχή κελιών Ε2: Ε15 );
• Στην καρτέλα "Αρχική σελίδα" στη γραμμή εργαλείων, κάντε κλικ στο στοιχείο μενού "Μορφοποίηση υπό όρους" -> "Κανόνες για την επισήμανση κελιών" -> "Επαναλαμβανόμενες τιμές".
• Στο αναδυόμενο παράθυρο διαλόγου, επιλέξτε "Επανάληψη" στην αριστερή αναπτυσσόμενη λίστα και στη δεξιά αναπτυσσόμενη λίστα, επιλέξτε με ποιο χρώμα θα επισημανθούν οι επαναλαμβανόμενες τιμές. Κάντε κλικ στο κουμπί "OK":
• Μετά από αυτό, οι διπλές γραμμές θα επισημανθούν στην επιλεγμένη στήλη:

Συχνά το καθήκον είναι να συγκρίνετε δύο λίστες στοιχείων. Το να το κάνετε αυτό με το χέρι είναι πολύ κουραστικό και επίσης δεν μπορεί να αποκλειστεί η πιθανότητα σφαλμάτων. Το Excel κάνει αυτή τη λειτουργία εύκολη. Αυτή η συμβουλή περιγράφει μια μέθοδο που χρησιμοποιεί μορφοποίηση υπό όρους.

Στο Σχ. Το Σχήμα 164.1 δείχνει ένα παράδειγμα δύο πολυστηλών λιστών ονομάτων. Η χρήση μορφοποίησης υπό όρους μπορεί να κάνει εμφανείς τις διαφορές στις λίστες. Αυτά τα παραδείγματα λίστας περιέχουν κείμενο, αλλά η εν λόγω μέθοδος λειτουργεί και με αριθμητικά δεδομένα.

Η πρώτη λίστα είναι A2:B31, αυτή η περιοχή ονομάζεται OldList. Η δεύτερη λίστα είναι D2:E31, η περιοχή καλείται Νέα Λίστα. Τα εύρη ονομάστηκαν χρησιμοποιώντας την εντολή Τύποι Καθορισμένα ονόματα Εκχωρήστε ένα όνομα. Δεν είναι απαραίτητο να ονομάσετε τις περιοχές, αλλά διευκολύνει την εργασία μαζί τους.

Ας ξεκινήσουμε προσθέτοντας μορφοποίηση υπό όρους στην παλιά λίστα.

1. Επιλέξτε κελιά σε μια περιοχή OldList.
2. Επιλέγω.
3. Στο παράθυρο Δημιουργήστε έναν κανόνα μορφοποίησηςεπιλέξτε το στοιχείο που καλείται Χρησιμοποιήστε τη φόρμουλα
4. Εισαγάγετε αυτόν τον τύπο στο πεδίο παραθύρου (Εικ. 164.2): =COUNTIF(NewList;A2)=0.
5. Κάντε κλικ στο κουμπί Μορφήκαι καθορίστε τη μορφοποίηση που θα εφαρμοστεί όταν η συνθήκη είναι αληθής. Είναι καλύτερο να επιλέξετε διαφορετικά χρώματα γεμίσματος.
6. Κάντε κλικ στο OK.

Κύτταρα εντός εύρους Νέα Λίσταχρησιμοποιήστε έναν παρόμοιο τύπο μορφοποίησης υπό όρους.

1. Επιλέξτε κελιά σε μια περιοχή Νέα Λίστα.
2. Επιλέγω Αρχική Μορφοποίηση υπό όρους Δημιουργία κανόναγια να ανοίξετε ένα παράθυρο διαλόγου Δημιουργήστε έναν κανόνα μορφοποίησης.
3. Στο παράθυρο Δημιουργήστε έναν κανόναμορφοποίηση επιλεγμένου στοιχείου Χρησιμοποιήστε τη φόρμουλαγια να ορίσετε τα κελιά που θα μορφοποιηθούν.
4. Εισαγάγετε αυτόν τον τύπο στο πεδίο παραθύρου: =COUNTIF(OldList;D2)=0 .
5. Κάντε κλικ στο κουμπί Μορφήκαι ορίστε τη μορφοποίηση που θα εφαρμόζεται όταν η συνθήκη είναι αληθής (διαφορετικό χρώμα γεμίσματος).
6. Κάντε κλικ στο OK.

Ως αποτέλεσμα, ονόματα που βρίσκονται στην παλιά λίστα, αλλά όχι στη νέα, θα επισημαίνονται (Εικ. 164.3). Επιπλέον, τα ονόματα στη νέα λίστα που δεν βρίσκονται στην παλιά λίστα επισημαίνονται επίσης, αλλά με διαφορετικό χρώμα. Τα ονόματα που εμφανίζονται και στις δύο λίστες δεν επισημαίνονται.

Και οι δύο τύποι μορφοποίησης υπό όρους χρησιμοποιούν τη συνάρτηση COUNTIF. Υπολογίζει πόσες φορές εμφανίζεται μια συγκεκριμένη τιμή σε μια περιοχή. Εάν ο τύπος επιστρέψει 0, σημαίνει ότι το στοιχείο δεν βρίσκεται στην περιοχή. Με αυτόν τον τρόπο, η μορφοποίηση υπό όρους αναλαμβάνει και το χρώμα φόντου του κελιού αλλάζει.