Περιεχόμενα
Έχουμε δύο πίνακες (για παράδειγμα, την παλιά και τη νέα έκδοση του τιμοκαταλόγου), τους οποίους πρέπει να συγκρίνουμε και να βρούμε γρήγορα τις διαφορές:
Είναι αμέσως ξεκάθαρο ότι κάτι έχει προστεθεί στον νέο τιμοκατάλογο (χουρμάδες, σκόρδο…), κάτι έχει εξαφανιστεί (βατόμουρα, σμέουρα…), οι τιμές έχουν αλλάξει για ορισμένα αγαθά (σύκα, πεπόνια…). Πρέπει να βρείτε γρήγορα και να εμφανίσετε όλες αυτές τις αλλαγές.
Για οποιαδήποτε εργασία στο Excel, υπάρχουν σχεδόν πάντα περισσότερες από μία λύσεις (συνήθως 4-5). Για το πρόβλημά μας, μπορούν να χρησιμοποιηθούν πολλές διαφορετικές προσεγγίσεις:
- λειτουργία VPR (VLOOKUP) — αναζητήστε ονόματα προϊόντων από τον νέο τιμοκατάλογο στον παλιό και εμφανίστε την παλιά τιμή δίπλα στη νέα και, στη συνέχεια, εντοπίστε τις διαφορές
- συγχωνεύστε δύο λίστες σε μία και στη συνέχεια δημιουργήστε έναν συγκεντρωτικό πίνακα με βάση αυτόν, όπου οι διαφορές θα είναι καθαρά ορατές
- χρησιμοποιήστε το πρόσθετο Power Query για Excel
Ας τα πάρουμε όλα με τη σειρά.
Μέθοδος 1. Σύγκριση πινάκων με τη συνάρτηση VLOOKUP
Εάν δεν είστε εξοικειωμένοι με αυτό το υπέροχο χαρακτηριστικό, τότε κοιτάξτε πρώτα εδώ και διαβάστε ή παρακολουθήστε ένα εκπαιδευτικό βίντεο σχετικά με αυτό - εξοικονομήστε μερικά χρόνια ζωής.
Συνήθως, αυτή η συνάρτηση χρησιμοποιείται για την άντληση δεδομένων από τον έναν πίνακα στον άλλο αντιστοιχίζοντας κάποια κοινή παράμετρο. Σε αυτήν την περίπτωση, θα το χρησιμοποιήσουμε για να προωθήσουμε τις παλιές τιμές στη νέα τιμή:
Αυτά τα προϊόντα, έναντι των οποίων προέκυψε το σφάλμα #N/A, δεν βρίσκονται στην παλιά λίστα, δηλαδή προστέθηκαν. Οι αλλαγές στις τιμές είναι επίσης ευδιάκριτες.
ΥΠΕΡ αυτή η μέθοδος: απλή και ξεκάθαρη, «κλασική του είδους», όπως λένε. Λειτουργεί σε οποιαδήποτε έκδοση του Excel.
ΚΑΤΑ είναι επίσης εκεί. Για να αναζητήσετε προϊόντα που προστέθηκαν στον νέο τιμοκατάλογο, θα πρέπει να κάνετε την ίδια διαδικασία προς την αντίθετη κατεύθυνση, δηλαδή να ανεβείτε νέες τιμές στην παλιά τιμή με τη βοήθεια του VLOOKUP. Εάν τα μεγέθη των τραπεζιών αλλάξουν αύριο, τότε οι τύποι θα πρέπει να προσαρμοστούν. Λοιπόν, και σε πραγματικά μεγάλα τραπέζια (> 100 χιλιάδες σειρές), όλη αυτή η ευτυχία θα επιβραδυνθεί αξιοπρεπώς.
Μέθοδος 2: Σύγκριση πινάκων με χρήση άξονα
Ας αντιγράψουμε τους πίνακες μας τον ένα κάτω από τον άλλο, προσθέτοντας μια στήλη με το όνομα του τιμοκαταλόγου, ώστε αργότερα να καταλάβετε από ποια λίστα ποια σειρά:
Τώρα, με βάση τον πίνακα που δημιουργήθηκε, θα δημιουργήσουμε μια σύνοψη μέσω Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας). Ας ρίξουμε ένα χωράφι Προϊόν στην περιοχή των γραμμών, πεδίο Τιμή στην περιοχή και το πεδίο της στήλης ЦΕΝΑ στο εύρος:
Όπως μπορείτε να δείτε, ο συγκεντρωτικός πίνακας θα δημιουργήσει αυτόματα μια γενική λίστα με όλα τα προϊόντα από τους παλιούς και νέους τιμοκαταλόγους (χωρίς επαναλήψεις!) και θα ταξινομήσει τα προϊόντα αλφαβητικά. Μπορείτε να δείτε καθαρά τα προϊόντα που προστέθηκαν (δεν έχουν την παλιά τιμή), τα προϊόντα που αφαιρέθηκαν (δεν έχουν τη νέα τιμή) και τις αλλαγές τιμών, εάν υπάρχουν.
Τα μεγάλα σύνολα σε έναν τέτοιο πίνακα δεν έχουν νόημα και μπορούν να απενεργοποιηθούν στην καρτέλα Κατασκευαστής – Μεγάλα σύνολα – Απενεργοποίηση για σειρές και στήλες (Σχέδιο — Μεγάλα σύνολα).
Εάν αλλάξουν οι τιμές (αλλά όχι η ποσότητα των αγαθών!), τότε αρκεί απλώς να ενημερώσετε τη σύνοψη που δημιουργήθηκε κάνοντας δεξί κλικ πάνω της – Φρεσκάρω.
ΥΠΕΡ: Αυτή η προσέγγιση είναι μια τάξη μεγέθους ταχύτερη με μεγάλους πίνακες από το VLOOKUP.
ΚΑΤΑ: πρέπει να αντιγράψετε χειροκίνητα τα δεδομένα το ένα κάτω από το άλλο και να προσθέσετε μια στήλη με το όνομα του τιμοκαταλόγου. Εάν αλλάξουν τα μεγέθη των τραπεζιών, τότε πρέπει να τα κάνετε όλα από την αρχή.
Μέθοδος 3: Σύγκριση πινάκων με Power Query
Το Power Query είναι ένα δωρεάν πρόσθετο για το Microsoft Excel που σας επιτρέπει να φορτώνετε δεδομένα στο Excel από σχεδόν οποιαδήποτε πηγή και στη συνέχεια να μετατρέπετε αυτά τα δεδομένα με οποιονδήποτε επιθυμητό τρόπο. Στο Excel 2016, αυτό το πρόσθετο είναι ήδη ενσωματωμένο από προεπιλογή στην καρτέλα ημερομηνία (Δεδομένα), και για το Excel 2010-2013 πρέπει να το κατεβάσετε ξεχωριστά από τον ιστότοπο της Microsoft και να το εγκαταστήσετε - αποκτήστε μια νέα καρτέλα Ερώτημα ισχύος.
Πριν φορτώσουμε τους τιμοκαταλόγους μας στο Power Query, πρέπει πρώτα να μετατραπούν σε έξυπνους πίνακες. Για να το κάνετε αυτό, επιλέξτε την περιοχή με δεδομένα και πατήστε τον συνδυασμό στο πληκτρολόγιο Ctrl+T ή επιλέξτε την καρτέλα στην κορδέλα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα). Τα ονόματα των δημιουργημένων πινάκων μπορούν να διορθωθούν στην καρτέλα Κατασκευαστής (Θα αφήσω το πρότυπο Πίνακας 1 и Πίνακας 2, τα οποία λαμβάνονται από προεπιλογή).
Φορτώστε την παλιά τιμή στο Power Query χρησιμοποιώντας το κουμπί Από Πίνακας/Εύρος (Από πίνακα/Εύρος) από την καρτέλα ημερομηνία (Ημερομηνία) ή από την καρτέλα Ερώτημα ισχύος (ανάλογα με την έκδοση του Excel). Μετά τη φόρτωση, θα επιστρέψουμε στο Excel από το Power Query με την εντολή Κλείσιμο και φόρτωση – Κλείσιμο και φόρτωση σε… (Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε…):
… και στο παράθυρο που εμφανίζεται, επιλέξτε Απλώς δημιουργήστε μια σύνδεση (Μόνο σύνδεση).
Επαναλάβετε το ίδιο με τον νέο τιμοκατάλογο.
Τώρα ας δημιουργήσουμε ένα τρίτο ερώτημα που θα συνδυάζει και θα συγκρίνει τα δεδομένα από τα δύο προηγούμενα. Για να το κάνετε αυτό, επιλέξτε στο Excel στην καρτέλα Δεδομένα – Λήψη δεδομένων – Συνδυάστε αιτήματα – Συνδυάστε (Δεδομένα — Λήψη δεδομένων — Ερωτήματα συγχώνευσης — Συγχώνευση) ή πατήστε το κουμπί Συνδυασμός (Συγχώνευση) αυτί Ερώτημα ισχύος.
Στο παράθυρο σύνδεσης, επιλέξτε τους πίνακές μας στις αναπτυσσόμενες λίστες, επιλέξτε τις στήλες με τα ονόματα των αγαθών σε αυτές και στο κάτω μέρος, ορίστε τη μέθοδο ένωσης – Πλήρης εξωτερική (Πλήρης εξωτερική):
Μετά το κλικ OK θα πρέπει να εμφανιστεί ένας πίνακας τριών στηλών, όπου στην τρίτη στήλη πρέπει να αναπτύξετε τα περιεχόμενα των ένθετων πινάκων χρησιμοποιώντας το διπλό βέλος στην κεφαλίδα:
Ως αποτέλεσμα, λαμβάνουμε τη συγχώνευση δεδομένων και από τους δύο πίνακες:
Είναι καλύτερα, φυσικά, να μετονομάσετε τα ονόματα των στηλών στην κεφαλίδα κάνοντας διπλό κλικ σε πιο κατανοητά:
Και τώρα το πιο ενδιαφέρον. Μεταβείτε στην καρτέλα Προσθήκη στήλης (Προσθήκη στήλης) και κάντε κλικ στο κουμπί Στήλη υπό όρους (Στήλη υπό όρους). Και, στη συνέχεια, στο παράθυρο που ανοίγει, εισαγάγετε διάφορες συνθήκες δοκιμής με τις αντίστοιχες τιμές εξόδου:
Απομένει να κάνετε κλικ OK και ανεβάστε την αναφορά που προκύπτει στο Excel χρησιμοποιώντας το ίδιο κουμπί κλείστε και κατεβάστε (Κλείσιμο & Φόρτωση) αυτί Αρχική (Σπίτι):
Ομορφιά.
Επιπλέον, εάν στο μέλλον προκύψουν αλλαγές στους τιμοκαταλόγους (προστίθενται ή διαγράφονται γραμμές, αλλάζουν τιμές κ.λπ.), τότε θα αρκεί απλώς να ενημερώσουμε τα αιτήματά μας με μια συντόμευση πληκτρολογίου Ctrl+άλλος+F5 ή με κουμπί Ανανέωση όλων (Ανανέωση όλων) αυτί ημερομηνία (Ημερομηνία).
ΥΠΕΡ: Ίσως ο πιο όμορφος και βολικός τρόπος από όλους. Λειτουργεί έξυπνα με μεγάλα τραπέζια. Δεν απαιτεί χειροκίνητες επεξεργασίες κατά την αλλαγή μεγέθους πινάκων.
ΚΑΤΑ: Απαιτεί την εγκατάσταση του πρόσθετου Power Query (στο Excel 2010-2013) ή του Excel 2016. Τα ονόματα των στηλών στα δεδομένα προέλευσης δεν πρέπει να αλλάξουν, διαφορετικά θα λάβουμε το σφάλμα "Η στήλη δεν βρέθηκε!" όταν προσπαθείτε να ενημερώσετε το ερώτημα.
- Πώς να συλλέξετε δεδομένα από όλα τα αρχεία Excel σε έναν δεδομένο φάκελο χρησιμοποιώντας το Power Query
- Πώς να βρείτε αντιστοιχίσεις μεταξύ δύο λιστών στο Excel
- Συγχώνευση δύο λιστών χωρίς διπλότυπα