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

2024 Συγγραφέας: Malcolm Clapton | [email protected]. Τελευταία τροποποίηση: 2024-01-13 01:11

Σε αυτήν την ανάρτηση, ο Renat Shagabutdinov, Βοηθός Γενικός Διευθυντής του Mann, Ivanov and Ferber Publishing House, μοιράζεται μερικές όμορφες παραβιάσεις στο Excel. Αυτές οι συμβουλές θα είναι χρήσιμες για οποιονδήποτε εμπλέκεται σε διάφορες αναφορές, επεξεργασία δεδομένων και δημιουργία παρουσιάσεων.

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

Απλές εναλλακτικές για το VLOOKUP και το HLOOKUP, εάν οι επιθυμητές τιμές δεν βρίσκονται στην πρώτη στήλη του πίνακα: LOOKUP, INDEX + SEARCH

Οι συναρτήσεις VLOOKUP και HLOOKUP λειτουργούν μόνο εάν οι επιθυμητές τιμές βρίσκονται στην πρώτη στήλη ή γραμμή του πίνακα από την οποία σκοπεύετε να λάβετε δεδομένα.

Διαφορετικά, υπάρχουν δύο επιλογές:

1. Χρησιμοποιήστε τη λειτουργία LOOKUP.

   Έχει την ακόλουθη σύνταξη: LOOKUP (τιμή_αναζήτησης; αναζήτηση_διάνυσμα; αποτέλεσμα_διάνυσμα). Αλλά για να λειτουργήσει σωστά, οι τιμές του εύρους view_vector πρέπει να ταξινομηθούν με αύξουσα σειρά:

   

2. Χρησιμοποιήστε έναν συνδυασμό συναρτήσεων MATCH και INDEX.

   Η συνάρτηση MATCH επιστρέφει τον τακτικό αριθμό ενός στοιχείου στον πίνακα (με τη βοήθειά της μπορείτε να βρείτε σε ποια σειρά του πίνακα βρίσκεται το στοιχείο που αναζητήσατε) και η συνάρτηση INDEX επιστρέφει ένα στοιχείο πίνακα με έναν δεδομένο αριθμό (που θα μάθουμε χρησιμοποιώντας τη συνάρτηση MATCH).

   

   Σύνταξη συνάρτησης:

   • ΑΝΑΖΗΤΗΣΗ (τιμή_αναζήτησης, πίνακας_αναζήτησης, τύπος_ταίριαξης) - για την περίπτωσή μας, χρειαζόμαστε έναν αντίστοιχο τύπο "ακριβής αντιστοίχιση", αντιστοιχεί στον αριθμό 0.

   • INDEX (πίνακας; αριθμός_γραμμής; [αριθμός_στήλης]). Σε αυτήν την περίπτωση, δεν χρειάζεται να καθορίσετε τον αριθμό της στήλης, καθώς ο πίνακας αποτελείται από μία σειρά.

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

Η εργασία είναι να συμπληρώσετε τα κελιά στη στήλη με τις τιμές στην κορυφή (έτσι ώστε το θέμα να βρίσκεται σε κάθε σειρά του πίνακα και όχι μόνο στην πρώτη σειρά του μπλοκ βιβλίων για το θέμα):

Επιλέξτε τη στήλη "Θέμα", κάντε κλικ στην κορδέλα στην ομάδα "Αρχική σελίδα", το κουμπί "Εύρεση και επιλογή" → "Επιλέξτε μια ομάδα κελιών" → "Κενά κελιά" και ξεκινήστε να εισάγετε τον τύπο (δηλαδή, βάλτε ένα ίσο σημάδι) και ανατρέξτε στο κελί στην κορυφή, απλά κάνοντας κλικ στο επάνω βέλος στο πληκτρολόγιό σας. Μετά από αυτό, πατήστε Ctrl + Enter. Μετά από αυτό, μπορείτε να αποθηκεύσετε τα ληφθέντα δεδομένα ως τιμές, καθώς οι τύποι δεν χρειάζονται πλέον:

Πώς να βρείτε σφάλματα σε έναν τύπο

Υπολογισμός ενός ξεχωριστού μέρους ενός τύπου

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

1. Για να υπολογίσετε μέρος ενός τύπου απευθείας στη γραμμή τύπων, επιλέξτε αυτό το τμήμα και πατήστε F9:

   

   Σε αυτό το παράδειγμα, υπήρξε πρόβλημα με τη συνάρτηση SEARCH - ανταλλάχθηκαν ορίσματα σε αυτήν. Είναι σημαντικό να θυμάστε ότι εάν δεν ακυρώσετε τον υπολογισμό του τμήματος της συνάρτησης και πατήσετε Enter, τότε το υπολογιζόμενο τμήμα θα παραμείνει αριθμός.

2. Κάντε κλικ στο κουμπί Υπολογισμός τύπου στην ομάδα Τύποι στην κορδέλα:

   

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

   

Πώς να προσδιορίσετε από τι εξαρτάται ή σε τι αναφέρεται ένας τύπος

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

Τα βέλη εμφανίζονται για να υποδεικνύουν από τι εξαρτάται το αποτέλεσμα υπολογισμού.

Εάν εμφανίζεται το σύμβολο που επισημαίνεται στην εικόνα με κόκκινο χρώμα, τότε ο τύπος εξαρτάται από τα κελιά σε άλλα φύλλα ή σε άλλα βιβλία:

Κάνοντας κλικ σε αυτό, μπορούμε να δούμε ακριβώς πού βρίσκονται τα κελιά ή οι περιοχές που επηρεάζουν:

Δίπλα στο κουμπί "Επηρεάζοντας κελιά" βρίσκεται το κουμπί "Εξαρτημένα κελιά", το οποίο λειτουργεί με τον ίδιο τρόπο: εμφανίζει βέλη από το ενεργό κελί με έναν τύπο προς τα κελιά που εξαρτώνται από αυτό.

Το κουμπί "Κατάργηση βελών", που βρίσκεται στο ίδιο μπλοκ, σας επιτρέπει να αφαιρέσετε βέλη σε κελιά που επηρεάζουν, βέλη σε εξαρτημένα κελιά ή και τους δύο τύπους βελών ταυτόχρονα:

Πώς να βρείτε το άθροισμα (αριθμός, μέσος όρος) των τιμών των κελιών από πολλά φύλλα

Ας υποθέσουμε ότι έχετε πολλά φύλλα του ίδιου τύπου με δεδομένα που θέλετε να προσθέσετε, να μετρήσετε ή να επεξεργαστείτε με κάποιον άλλο τρόπο:

Για να το κάνετε αυτό, στο κελί στο οποίο θέλετε να δείτε το αποτέλεσμα, εισαγάγετε έναν τυπικό τύπο, για παράδειγμα SUM (SUM) και καθορίστε το όνομα του πρώτου και του τελευταίου φύλλου από τη λίστα των φύλλων που πρέπει να επεξεργαστείτε το όρισμα, που χωρίζεται με άνω και κάτω τελεία:

Θα λάβετε το άθροισμα των κελιών με τη διεύθυνση B3 από τα φύλλα "Data1", "Data2", "Data3":

Αυτή η διευθυνσιοδότηση λειτουργεί για φύλλα που βρίσκονται με συνέπεια … Η σύνταξη είναι η εξής: = FUNCTION (first_list: last_list! Range reference).

Πώς να δημιουργήσετε αυτόματα φράσεις προτύπων

Χρησιμοποιώντας τις βασικές αρχές της εργασίας με κείμενο στο Excel και μερικές απλές λειτουργίες, μπορείτε να προετοιμάσετε φράσεις προτύπων για αναφορές. Διάφορες αρχές εργασίας με κείμενο:

• Συνδέουμε το κείμενο χρησιμοποιώντας το σύμβολο & (μπορείτε να το αντικαταστήσετε με τη συνάρτηση CONCATENATE, αλλά αυτό δεν έχει πολύ νόημα).
• Το κείμενο γράφεται πάντα σε εισαγωγικά, οι αναφορές σε κελιά με κείμενο είναι πάντα χωρίς.
• Για να λάβετε τον χαρακτήρα υπηρεσίας "εισαγωγικά", χρησιμοποιήστε τη συνάρτηση CHAR με το όρισμα 32.

Ένα παράδειγμα δημιουργίας μιας φράσης προτύπου χρησιμοποιώντας τύπους:

Αποτέλεσμα:

Σε αυτήν την περίπτωση, εκτός από τη συνάρτηση CHAR (για την εμφάνιση εισαγωγικών), χρησιμοποιείται η συνάρτηση IF, η οποία σας επιτρέπει να αλλάξετε το κείμενο ανάλογα με το αν υπάρχει θετική τάση πωλήσεων και η συνάρτηση TEXT, η οποία σας επιτρέπει να εμφανίσετε αριθμός σε οποιαδήποτε μορφή. Η σύνταξή του περιγράφεται παρακάτω:

TEXT (τιμή, μορφή)

Η μορφή καθορίζεται σε εισαγωγικά, ακριβώς σαν να εισάγατε μια προσαρμοσμένη μορφή στο παράθυρο Μορφοποίηση κελιών.

Πιο πολύπλοκα κείμενα μπορούν επίσης να αυτοματοποιηθούν. Στην πρακτική μου, υπήρχε η αυτοματοποίηση μακροχρόνιων, αλλά συνηθισμένων σχολίων στην αναφορά διαχείρισης με τη μορφή «Ο ΔΕΙΚΤΗΣ έπεσε / αυξήθηκε κατά ΧΧ σε σχέση με το σχέδιο, κυρίως λόγω της ανάπτυξης / πτώσης του FACTOR1 κατά ΧΧ, της ανάπτυξης / πτώσης του FACTOR2 κατά YY …» με μια μεταβαλλόμενη λίστα παραγόντων. Εάν γράφετε συχνά τέτοια σχόλια και η διαδικασία σύνταξης τους μπορεί να αλγοριθμηθεί, αξίζει τον κόπο να δημιουργήσετε μια φόρμουλα ή μια μακροεντολή που θα σας εξοικονομήσει τουλάχιστον ένα μέρος της δουλειάς.

Πώς να αποθηκεύσετε δεδομένα σε κάθε κελί μετά τη συνένωση

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

Αντίστοιχα, εάν είχατε έναν τύπο ανάλογα με κάθε κελί, θα σταματήσει να λειτουργεί αφού τα συνδυάσετε (# N / Ένα σφάλμα στις γραμμές 3-4 του παραδείγματος):

Για να συγχωνεύσετε κελιά και να διατηρήσετε τα δεδομένα σε καθένα από αυτά (ίσως έχετε έναν τύπο όπως σε αυτό το αφηρημένο παράδειγμα, ίσως θέλετε να συγχωνεύσετε κελιά, αλλά να διατηρήσετε όλα τα δεδομένα για το μέλλον ή να τα αποκρύψετε σκόπιμα), συγχωνεύστε τυχόν κελιά στο φύλλο, επιλέξτε τα και, στη συνέχεια, χρησιμοποιήστε την εντολή Format Painter για να μεταφέρετε τη μορφοποίηση στα κελιά που πρέπει να συνδυάσετε:

Πώς να δημιουργήσετε ένα pivot από πολλές πηγές δεδομένων

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

Μπορείτε να το κάνετε ως εξής: "Αρχείο" → "Επιλογές" → "Γραμμή εργαλείων γρήγορης πρόσβασης" → "Όλες οι εντολές" → "Οδηγός συγκεντρωτικού πίνακα και γραφήματος" → "Προσθήκη":

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

Όταν κάνετε κλικ σε αυτό, εμφανίζεται ένα παράθυρο διαλόγου:

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

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

Μετά από αυτό, στο τελευταίο παράθυρο διαλόγου, καθορίστε πού θα τοποθετηθεί η αναφορά συγκεντρωτικού πίνακα - σε ένα υπάρχον ή νέο φύλλο:

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

Πώς να υπολογίσετε τον αριθμό των εμφανίσεων του κειμένου Α στο κείμενο Β ("Τιμολόγιο MTS SuperMTS" - δύο εμφανίσεις της συντομογραφίας MTS)

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

Για να λύσετε αυτό το πρόβλημα, μπορείτε να χρησιμοποιήσετε έναν σύνθετο τύπο που αποτελείται από τις ακόλουθες συναρτήσεις:

1. DLSTR (LEN) - υπολογίζει το μήκος του κειμένου, το μόνο όρισμα είναι το κείμενο. Παράδειγμα: DLSTR ("μηχανή") = 6.
2. SUBSTITUTE - αντικαθιστά ένα συγκεκριμένο κείμενο σε μια συμβολοσειρά κειμένου με ένα άλλο. Σύνταξη: SUBSTITUTE (κείμενο; παλιό_κείμενο; νέο_κείμενο). Παράδειγμα: SUBSTITUTE ("αυτοκίνητο"; "auto"; "") = "κινητό".
3. UPPER - αντικαθιστά όλους τους χαρακτήρες σε μια συμβολοσειρά με κεφαλαία. Το μόνο επιχείρημα είναι το κείμενο. Παράδειγμα: UPPER ("μηχανή") = "CAR". Χρειαζόμαστε αυτή τη λειτουργία για να κάνουμε αναζητήσεις χωρίς διάκριση πεζών-κεφαλαίων. Μετά από όλα, UPPER ("αυτοκίνητο") = UPPER ("Μηχανή")

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

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super") = 6

Και μετά διαιρέστε αυτή τη διαφορά με το μήκος της συμβολοσειράς που αναζητούσαμε:

6 / DLSTR ("MTS") = 2

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

Απομένει να γράψουμε αυτόν τον αλγόριθμο στη γλώσσα των τύπων (ας υποδηλώσουμε με "κείμενο" το κείμενο στο οποίο αναζητούμε εμφανίσεις και με "αναζήτηση" - αυτό του οποίου ο αριθμός των εμφανίσεων μας ενδιαφέρει):

= (DLSTR (κείμενο) -LSTR (SUBSTITUTE (UPPER (κείμενο); UPPER (αναζήτηση), ""))) / DLSTR (αναζήτηση)

Στο παράδειγμά μας, ο τύπος μοιάζει με αυτό:

= (DLSTR (A2) -LSTR (ΥΠΟΚΑΤΑΣΤΑΣΗ (UPPER (A2), UPPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)