Ο Μπιλ έθεσε την ερώτηση αυτής της εβδομάδας σχετικά με τα περιττά δεδομένα του Excel.
Δημιουργώ μια μηνιαία λίστα συναλλαγών στο Excel. Στο τέλος του μήνα, πρέπει να εξαλείψω τα περιττά δεδομένα και να βρω ένα σύνολο με κωδικό λογαριασμού. Κάθε κωδικός λογαριασμού μπορεί να εμφανιστεί πολλές φορές. Στη συνέχεια, ο Μπιλ περιέγραψε την τρέχουσα μεθοδολογία του Excel που μοιάζει με τη μέθοδο 1 παρακάτω για να βρει μια μοναδική λίστα κωδικών λογαριασμού, με σχέδια να χρησιμοποιήσει έναν πίνακα τύπων ΧΑΚ για να πάρει τα σύνολα. Ρωτάει, υπάρχει ένας ευκολότερος τρόπος για να φτάσετε σε μια μοναδική λίστα κωδικών λογαριασμού με σύνολα για κάθε λογαριασμό;
Αυτή είναι μια τέλεια ερώτηση για διακοπές. Όντας χρήστης του Lotus για 15 χρόνια, αναγνωρίζω τη μέθοδο του Bill ως την κλασική μέθοδο για «γρήγορο και βρώμικο» χειρισμό δεδομένων από τις παλιές καλές μέρες της έκδοσης Lotus 2.1. Αυτή είναι μια εποχή για να μετρήσουμε τις ευλογίες μας. Όταν σκέφτεστε αυτήν την ερώτηση, συνειδητοποιείτε ότι οι λαοί της Microsoft μας έχουν πραγματικά προσφέρει πολλά εργαλεία με τα χρόνια. Εάν χρησιμοποιείτε το Excel 97, υπάρχουν τουλάχιστον πέντε μέθοδοι για να εκτελέσετε αυτήν την εργασία, όλες είναι πολύ ευκολότερες από την κλασική μέθοδο που περιγράφεται από τον Bill. Θα προσφέρω ένα σεμινάριο για τις πέντε μεθόδους αυτήν την εβδομάδα.
Το απλοποιημένο σύνολο δεδομένων μου έχει αριθμούς λογαριασμού στη στήλη Α και ποσά στη στήλη Β. Τα δεδομένα εκτελούνται από το A2: B100. Δεν ταξινομείται στην αρχή.
Μέθοδος 1
Χρησιμοποιήστε δηλώσεις δημιουργικού If σε συνδυασμό με Επικόλληση Ειδικών Τιμών για να βρείτε την απάντηση
Δεδομένων των νεότερων εργαλείων που προσφέρει το Excel, δεν προτείνω πλέον αυτήν τη μέθοδο. Συνήθιζα να το χρησιμοποιώ πολύ πριν προκύψουν καλύτερα πράγματα και εξακολουθούν να υπάρχουν καταστάσεις όπου είναι χρήσιμο. Το εναλλακτικό όνομά μου για αυτό είναι η μέθοδος "The-Lotus-123-When-You-Were-Not-In-The-Mood-to-Use- @ DSUM". Εδώ είναι τα βήματα.
- Ταξινόμηση δεδομένων ανά στήλη A.
- Εφευρέστε έναν τύπο στη στήλη Γ που θα διατηρήσει ένα τρέχον σύνολο ανά λογαριασμό. Το κελί C2 είναι
=IF(A2=A1,C1+B2,B2). - Εφευρέστε έναν τύπο στο D που θα προσδιορίσει την τελευταία καταχώριση για έναν συγκεκριμένο λογαριασμό. Το κελί D2 είναι
=IF(A2=A3,FALSE,TRUE). - Αντιγράψτε το C2: D2 σε όλες τις σειρές σας.
- Αντίγραφο C2: D100. Κάντε μια Επεξεργασία - PasteSpecial - Τιμές πίσω στο C2: D100 για να αλλάξετε τους τύπους σε τιμές.
- Ταξινόμηση κατά στήλη D φθίνουσα.
- Για τις σειρές που έχουν ΑΛΗΘΕΙΑ στη στήλη Δ, έχετε μια μοναδική λίστα αριθμών λογαριασμού στο Α και το τελικό σύνολο εκτέλεσης σε Γ
Πλεονεκτήματα: Είναι γρήγορο. Το μόνο που χρειάζεστε είναι μια έντονη αίσθηση σύνταξης δηλώσεων IF.
Μειονεκτήματα: Υπάρχουν καλύτεροι τρόποι.
Μέθοδος 2
Χρησιμοποιήστε το Φίλτρο δεδομένων - Σύνθετο φίλτρο για να λάβετε τη λίστα με τους μοναδικούς λογαριασμούς.
Η ερώτηση του Bill ήταν πραγματικά πώς να πάρει μια μοναδική λίστα αριθμών λογαριασμού, ώστε να μπορούσε να χρησιμοποιήσει τους τύπους ΧΑΚ για να πάρει τα σύνολα. Αυτή είναι μια μέθοδος για τη λήψη μιας λίστας με τους μοναδικούς αριθμούς λογαριασμού.
- Επισήμανση A1: A100
- Από το Μενού, επιλέξτε Δεδομένα, Φίλτρο, Σύνθετο φίλτρο
- Κάντε κλικ στο κουμπί επιλογής για "Αντιγραφή σε άλλη τοποθεσία".
- Κάντε κλικ στο πλαίσιο ελέγχου για "Μόνο μοναδικές εγγραφές".
- Επιλέξτε μια κενή ενότητα του φύλλου εργασίας όπου θέλετε να εμφανίζεται η μοναδική λίστα. Εισαγάγετε αυτό στο πεδίο "Αντιγραφή σε:". (Σημειώστε ότι αυτό το πεδίο είναι γκριζωπό έως ότου επιλέξετε "Αντιγραφή σε άλλη τοποθεσία".
- Κάντε κλικ στο OK. Οι μοναδικοί αριθμοί λογαριασμού θα εμφανίζονται στο F1.
- Εισαγάγετε τυχόν χειρισμούς downline, τύπους πίνακα κ.λπ. για να λάβετε τα αποτελέσματά σας.
Πλεονεκτήματα: Ταχύτερη από τη Μέθοδο 1. Δεν απαιτείται ταξινόμηση.
Μειονεκτήματα: Οι τύποι ΧΑΚ που απαιτούνται μετά από αυτό θα κάνουν το κεφάλι σας να γυρίσει.
Μέθοδος 3
Χρησιμοποιήστε το Data Consolidate.
Η ποιότητα ζωής μου βελτιώθηκε όταν το Excel προσέφερε Data Consolidate. Αυτό ήταν ΜΕΓΑΛΟ! Χρειάζονται 30 δευτερόλεπτα για να το ρυθμίσετε, αλλά έγραψε το θάνατο για DSUM και άλλες μεθόδους. Ο αριθμός λογαριασμού σας πρέπει να βρίσκεται στα αριστερά των αριθμητικών πεδίων που θέλετε να αθροίσετε. Πρέπει να έχετε επικεφαλίδες πάνω από κάθε στήλη. Πρέπει να αντιστοιχίσετε ένα όνομα εύρους στο ορθογώνιο μπλοκ κελιών που περιλαμβάνει τους αριθμούς λογαριασμού στην αριστερή στήλη και τις επικεφαλίδες κατά μήκος της κορυφής. Σε αυτήν την περίπτωση, αυτό το εύρος είναι A1: B100.
- Επισήμανση A1: B100
- Αντιστοιχίστε ένα όνομα εύρους σε αυτήν την περιοχή κάνοντας κλικ στο πλαίσιο ονόματος (στα αριστερά της γραμμής τύπων) και πληκτρολογώντας ένα όνομα όπως "TotalMe". (Εναλλακτικά, χρησιμοποιήστε το Insert - Name).
- Τοποθετήστε το δείκτη κελιού σε μια κενή ενότητα του φύλλου εργασίας.
- Επιλογή δεδομένων - Ενοποίηση
- Στο πεδίο αναφοράς, πληκτρολογήστε το όνομα εύρους (TotalMe).
- Στην ενότητα Χρήση ετικετών σε, ελέγξτε τόσο την επάνω σειρά όσο και την αριστερή στήλη.
- Κάντε κλικ στο OK
Πλεονεκτήματα: Αυτή είναι η αγαπημένη μου μέθοδος. Δεν απαιτείται ταξινόμηση. Η συντόμευση είναι alt-D N (όνομα περιοχής) alt-T alt-L enter. Είναι εύκολα επεκτάσιμο. Εάν το εύρος σας περιλαμβάνει 12 μηνιαίες στήλες, η απάντηση θα έχει σύνολα για κάθε μήνα.
Μειονεκτήματα: Εάν κάνετε άλλη ενοποίηση δεδομένων στο ίδιο φύλλο, πρέπει να διαγράψετε το παλιό όνομα εύρους από το πεδίο Όλες οι αναφορές χρησιμοποιώντας το κουμπί Διαγραφή. Ο αριθμός λογαριασμού πρέπει να βρίσκεται στα αριστερά των αριθμητικών δεδομένων σας. Είναι ελαφρώς πιο αργό από τους συγκεντρωτικούς πίνακες που γίνεται αισθητός για σύνολα δεδομένων με 10.000+ εγγραφές.
Μέθοδος 4
Χρησιμοποιήστε υποσύνολα δεδομένων.
Αυτό είναι ένα υπέροχο χαρακτηριστικό. Επειδή τα δεδομένα που προκύπτουν είναι παράξενα για χρήση, τα χρησιμοποιώ λιγότερο συχνά από το Data Consolidate.
- Ταξινόμηση κατά στήλη Α αύξουσα.
- Επιλέξτε οποιοδήποτε κελί στο εύρος δεδομένων.
- Επιλέξτε Δεδομένα - Μερικά σύνολα από το μενού.
- Από προεπιλογή, το Excel προσφέρει το άθροισμα της τελευταίας στήλης των δεδομένων σας. Αυτό λειτουργεί σε αυτό το παράδειγμα, αλλά συχνά πρέπει να μετακινηθείτε στη λίστα "Προσθήκη υποσυνόλου σε:" για να επιλέξετε τα σωστά πεδία.
- Κάντε κλικ στο OK. Το Excel θα εισαγάγει μια νέα σειρά σε κάθε αλλαγή αριθμού λογαριασμού με ένα σύνολο.
Αφού έχετε τα υποσύνολα, θα δείτε ένα μικρό 123 να εμφανίζεται κάτω από το πλαίσιο ονόματος. Κάντε κλικ στο 2 για να δείτε μόνο μία γραμμή ανά λογαριασμό με τα σύνολα. Διαβάστε το Copy Excel Subtotals για να εξηγήσετε τα ειδικά βήματα που απαιτούνται για να τα αντιγράψετε σε μια νέα τοποθεσία. Κάντε κλικ στο 3 για να δείτε όλες τις γραμμές. Πλεονεκτήματα: Cool Feature. Ιδανικό για εκτύπωση αναφορών με σύνολα και διαλείμματα σελίδων μετά από κάθε ενότητα.
Μειονεκτήματα: Τα δεδομένα πρέπει να ταξινομηθούν πρώτα. Αργή για πολλά δεδομένα. Πρέπει να χρησιμοποιήσετε το Goto-Special-VisbileCellsOnly για να λάβετε τα σύνολα αλλού. Για να επιστρέψετε στα αρχικά σας δεδομένα, πρέπει να χρησιμοποιήσετε το Data-Subtotals-RemoveAll.
Μέθοδος 5
Χρησιμοποιήστε έναν συγκεντρωτικό πίνακα.
Οι συγκεντρωτικοί πίνακες είναι οι πιο ευέλικτοι από όλους. Τα δεδομένα σας δεν χρειάζεται να ταξινομηθούν. Οι αριθμητικές στήλες μπορούν να βρίσκονται αριστερά ή δεξιά του αριθμού λογαριασμού. Μπορείτε εύκολα να μειώσετε τους αριθμούς λογαριασμού ή σε ολόκληρη τη σελίδα.
- Επιλέξτε οποιοδήποτε κελί στο εύρος δεδομένων.
- Επιλέξτε Δεδομένα - Συγκεντρωτικός πίνακας από το μενού.
- Αποδεχτείτε τις προεπιλογές στο Βήμα 1
- Βεβαιωθείτε ότι το εύρος δεδομένων στο βήμα 2 είναι σωστό (συνήθως είναι)
- Εάν χρησιμοποιείτε το Excel 2000, κάντε κλικ στο κουμπί Διάταξη στο βήμα 3. Οι χρήστες του Excel 95 & 97 μεταβαίνουν αυτόματα στη διάταξη ως βήμα 3.
- Στο διάλογο διάταξης, σύρετε το κουμπί Λογαριασμός από τη δεξιά πλευρά του διαλόγου και αφήστε το στην περιοχή Σειρά.
- Σύρετε το κουμπί Ποσό από τη δεξιά πλευρά του διαλόγου και αφήστε το στην περιοχή Δεδομένα.
- Οι χρήστες του Excel 2000 κάνουν κλικ στο OK, οι χρήστες του Excel 95/97 κάντε κλικ στο Επόμενο.
- Καθορίστε εάν θέλετε τα αποτελέσματα σε ένα νέο φύλλο ή σε μια συγκεκριμένη ενότητα ενός υπάρχοντος φύλλου. Διαβάστε περισσότερα σχετικά με τους συγκεντρωτικούς πίνακες στο Excel Pivot Table Advanced Tricks.
- Οι συγκεντρωτικοί πίνακες προσφέρουν απίστευτη λειτουργικότητα και κάνουν αυτήν την εργασία ένα γρήγορο. Για να αντιγράψετε τα αποτελέσματα του συγκεντρωτικού πίνακα, πρέπει να κάνετε μια Επεξεργασία-Επικόλληση Ειδικών Τιμών, διαφορετικά το Excel δεν θα σας επιτρέψει να εισαγάγετε σειρές κ.λπ.
Πλεονεκτήματα: Γρήγορη, ευέλικτη, ισχυρή. Γρήγορη, ακόμη και για πολλά δεδομένα.
Μειονεκτήματα: Κάπως εκφοβιστικό.
Ο Bill έχει τώρα τέσσερις νέες μεθόδους για την εξάλειψη των περιττών δεδομένων. Ενώ αυτές οι μέθοδοι δεν ήταν διαθέσιμες από την αρχή του χρόνου, τόσο το Lotus όσο και το Excel υπήρξαν εξαιρετικοί καινοτόμοι για να μας φέρουν γρηγορότερους τρόπους για να επιτύχουμε αυτό το απλό έργο.
