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

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

Σύγκριση δύο λιστών στο Excel

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

Διατύπωση του προβλήματος

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

1. Εάν τα μοναδικά κελιά καθεμιάς από αυτές τις λίστες είναι τα ίδια και ο συνολικός αριθμός των μοναδικών κελιών είναι ο ίδιος και τα κελιά είναι ίδια, τότε αυτές οι λίστες μπορούν να θεωρηθούν ίδιες. Η σειρά με την οποία στοιβάζονται οι τιμές σε αυτήν τη λίστα δεν έχει τόση σημασία.
2. Μπορούμε να μιλήσουμε για μερική σύμπτωση λιστών εάν οι ίδιες οι μοναδικές τιμές είναι ίδιες, αλλά ο αριθμός των επαναλήψεων είναι διαφορετικός. Επομένως, τέτοιες λίστες μπορεί να περιέχουν διαφορετικό αριθμό στοιχείων.
3. Το γεγονός ότι οι δύο λίστες δεν ταιριάζουν υποδεικνύεται από ένα διαφορετικό σύνολο μοναδικών τιμών.

Και οι τρεις αυτές συνθήκες είναι ταυτόχρονα οι προϋποθέσεις του προβλήματός μας.

Η λύση του προβλήματος

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

Για να συγκρίνετε δύο λίστες, κάντε τα εξής:

1. Σε μια ξεχωριστή στήλη, δημιουργούμε μια λίστα με μοναδικές τιμές που είναι συγκεκριμένες και για τις δύο λίστες. Για αυτό χρησιμοποιούμε τον τύπο: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Ο ίδιος ο τύπος πρέπει να γραφτεί ως τύπος πίνακα.
2. Ας προσδιορίσουμε πόσες φορές εμφανίζεται κάθε μοναδική τιμή στον πίνακα δεδομένων. Εδώ είναι οι τύποι για να το κάνετε αυτό: =COUNTIF(List1,D5) και =COUNTI(List2,D5).
3. Εάν τόσο ο αριθμός των επαναλήψεων όσο και ο αριθμός των μοναδικών τιμών είναι ίδιοι σε όλες τις λίστες που περιλαμβάνονται σε αυτές τις περιοχές, τότε η συνάρτηση επιστρέφει την τιμή 0. Αυτό υποδηλώνει ότι η αντιστοίχιση είναι XNUMX%. Σε αυτήν την περίπτωση, οι επικεφαλίδες αυτών των λιστών θα αποκτήσουν πράσινο φόντο.
4. Εάν όλο το μοναδικό περιεχόμενο βρίσκεται και στις δύο λίστες, τότε επιστρέφεται με τύπους =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) η τιμή θα είναι μηδέν. Εάν το E1 δεν περιέχει μηδέν, αλλά μια τέτοια τιμή περιέχεται στα κελιά E2 και F2, τότε σε αυτήν την περίπτωση οι περιοχές θα αναγνωριστούν ως ταιριάζουν, αλλά μόνο εν μέρει. Σε αυτήν την περίπτωση, οι επικεφαλίδες των αντίστοιχων λιστών θα γίνουν πορτοκαλί.
5. Και αν ένας από τους τύπους που περιγράφονται παραπάνω επιστρέψει μια μη μηδενική τιμή, οι λίστες δεν θα ταιριάζουν πλήρως.

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

Παράδειγμα δοκιμής

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

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

Εύρεση διαφορών σε δύο λίστες με δύο τρόπους

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

Επιλογή 1. Σύγχρονες λίστες

Αυτή είναι μια εύκολη επιλογή. Ας υποθέσουμε ότι έχουμε τέτοιες λίστες.

Για να προσδιορίσετε πόσες φορές οι τιμές δεν συγκλίνουν, μπορείτε να χρησιμοποιήσετε τον τύπο: =SUMPRODUCT(—(A2:A20<>B2:B20)). Εάν έχουμε ως αποτέλεσμα 0, αυτό σημαίνει ότι οι δύο λίστες είναι ίδιες.

Επιλογή 2: Ανακατεμένες λίστες

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

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

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

Παράδειγμα. Πώς να συγκρίνετε 2 στήλες για αντιστοιχίσεις και διαφορές σε μία σειρά

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

Θα πρέπει να γράψουμε έναν τέτοιο τύπο για να καταλάβουμε εάν οι τιμές και στις δύο στήλες είναι ίδιες ή όχι: =IF(A2=B2, "Match", ""). Η λογική αυτής της συνάρτησης είναι πολύ απλή: συγκρίνει τις τιμές στα κελιά A2 και B2 και αν είναι ίδιες, εμφανίζει την τιμή "Συμπίπτει". Εάν τα δεδομένα είναι διαφορετικά, δεν επιστρέφουν καμία τιμή. Μπορείτε επίσης να ελέγξετε τα κελιά για να δείτε εάν υπάρχει αντιστοιχία μεταξύ τους. Σε αυτή την περίπτωση, ο τύπος που χρησιμοποιείται είναι: =IF(A2<>B2, "Do not match", ""). Η αρχή είναι η ίδια, πρώτα πραγματοποιείται ο έλεγχος. Εάν αποδειχθεί ότι τα κελιά πληρούν το κριτήριο, τότε εμφανίζεται η τιμή "Δεν ταιριάζει".

Είναι επίσης δυνατό να χρησιμοποιήσετε τον ακόλουθο τύπο στο πεδίο του τύπου για να εμφανίσετε τόσο το "Match" εάν οι τιμές είναι ίδιες και το "Do not match" εάν είναι διαφορετικές: =IF(A2=B2; "Match", "Do not match"). Μπορείτε επίσης να χρησιμοποιήσετε τον τελεστή ανισότητας αντί για τον τελεστή ισότητας. Μόνο η σειρά των τιμών που θα εμφανίζονται σε αυτήν την περίπτωση θα είναι ελαφρώς διαφορετική: =IF(A2<>B2, "Do not match", "Coincide"). Αφού χρησιμοποιήσετε την πρώτη έκδοση του τύπου, το αποτέλεσμα θα είναι το εξής.

Αυτή η παραλλαγή του τύπου είναι χωρίς διάκριση πεζών-κεφαλαίων. Επομένως, εάν οι τιμές σε μια στήλη διαφέρουν από άλλες μόνο στο ότι είναι γραμμένες με κεφαλαία γράμματα, τότε το πρόγραμμα δεν θα παρατηρήσει αυτή τη διαφορά. Για να κάνετε τη σύγκριση με διάκριση πεζών-κεφαλαίων, πρέπει να χρησιμοποιήσετε τη συνάρτηση στα κριτήρια ΑΚΡΙΒΗΣ. Τα υπόλοιπα επιχειρήματα παραμένουν αμετάβλητα: =IF(EXACT(A2,B2), "Match", "Unique").

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

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

1. Βρείτε εκείνες τις σειρές που έχουν τις ίδιες τιμές παντού.
2. Βρείτε εκείνες τις σειρές όπου υπάρχουν αντιστοιχίες σε δύο μόνο λίστες.

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

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

Ας υποθέσουμε ότι έχουμε μια σειρά από στήλες που περιέχουν τις πληροφορίες που χρειαζόμαστε. Είμαστε αντιμέτωποι με το καθήκον να προσδιορίσουμε εκείνες τις σειρές στις οποίες οι τιμές είναι ίδιες. Για να το κάνετε αυτό, πρέπει να χρησιμοποιήσετε τον ακόλουθο τύπο: =IF(AND(A2=B2,A2=C2), "ταιριάζουν", "").

Εάν υπάρχουν πάρα πολλές στήλες στον πίνακα, τότε απλά πρέπει να το χρησιμοποιήσετε μαζί με τη συνάρτηση IF χειριστής COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"ταιριάζουν";" "). Ο αριθμός που χρησιμοποιείται σε αυτόν τον τύπο υποδεικνύει τον αριθμό των στηλών προς έλεγχο. Εάν διαφέρει, τότε πρέπει να γράψετε όσα ισχύουν για την κατάστασή σας.

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

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

Χρησιμοποιούμε αυτόν τον τύπο: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Μπορεί να υπάρχει μια κατάσταση όταν υπάρχουν πολλές στήλες στον πίνακα. Σε αυτήν την περίπτωση, ο τύπος θα είναι τεράστιος και μπορεί να χρειαστεί πολύς χρόνος για να επιλέξετε όλους τους απαραίτητους συνδυασμούς. Για να λύσετε αυτό το πρόβλημα, πρέπει να χρησιμοποιήσετε τη συνάρτηση COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "Μοναδική συμβολοσειρά"; "Μη μοναδική συμβολοσειρά")

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

Πώς να συγκρίνετε 2 στήλες στο Excel για αντιστοιχίσεις

Τώρα ας πάρουμε ένα παράδειγμα. Ας υποθέσουμε ότι έχουμε έναν πίνακα με δύο στήλες. Πρέπει να ελέγξετε αν ταιριάζουν. Για να το κάνετε αυτό, πρέπει να εφαρμόσετε τον τύπο, όπου θα χρησιμοποιηθεί και η συνάρτηση IFκαι ο χειριστής COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Δεν υπάρχουν αντιστοιχίσεις στη στήλη Β", "Υπάρχουν αντιστοιχίσεις στη στήλη Β")

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

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

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

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

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

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

Εύρεση και επισήμανση γραμμών που ταιριάζουν

Η τεχνική για τον έλεγχο της αντιστοιχίας των χορδών είναι ελαφρώς διαφορετική. Αρχικά, πρέπει να δημιουργήσουμε μια πρόσθετη στήλη και εκεί θα χρησιμοποιήσουμε τις συνδυασμένες τιμές χρησιμοποιώντας τον τελεστή &. Για να το κάνετε αυτό, πρέπει να γράψετε έναν τύπο της φόρμας: =A2&B2&C2&D2.

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

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