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

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

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

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

VLOOKUP (VLOOKUP) Και PGR (HLOOKUP) λειτουργεί μόνο αν οι τιμές στόχοι είναι στην πρώτη στήλη ή γραμμή του πίνακα από τον οποίο θέλετε να πάρετε τα δεδομένα.

Σε άλλες περιπτώσεις, υπάρχουν δύο επιλογές:

1. Λειτουργία Χρήση VIEW (αναζήτηση).
   Έχει την ακόλουθη σύνταξη: ΠΑΡΑΚΟΛΟΥΘΕΙΣΤΕ (lookup_value Αναζήτηση φορέας? vektor_rezultata). Αλλά για να λειτουργήσει σωστά θα πρέπει να τις αξίες εύρος Αναζήτηση διάνυσμα Ήταν ταξινομημένο σε αύξουσα σειρά:
2. MATCH χρησιμοποιούν ένα συνδυασμό των λειτουργιών (MATCH) και ένα δείκτη (INDEX).
   συνάρτηση MATCH επιστρέφει το σειριακό αριθμό του στοιχείου του πίνακα (με τη βοήθεια του μπορείτε να βρείτε, σε μια σειρά του πίνακα το επιθυμητό στοιχείο), και ο δείκτης συνάρτηση επιστρέφει ένα στοιχείο συστοιχίας με έναν προκαθορισμένο αριθμό (που ξέρουμε να λειτουργήσει MATCH).Σύνταξη επιλογές:
   • MATCH (lookup_value massiv_poiska? match_type) - στην περίπτωσή μας, χρειαζόμαστε ένα είδος σύγκρισης «ακριβή αντιστοίχιση», που αντιστοιχεί στον αριθμό 0.
   • INDEX (σειρά? αριθμός_γραμμής? [Αριθμός_στήλης]). Σε αυτήν την περίπτωση, δεν είναι αναγκαίο να διευκρινιστεί ο αριθμός στήλης όπως η συστοιχία αποτελείται από μία μόνο γραμμή.

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

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

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

Πώς να βρείτε λάθη στον τύπο

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

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

1. Για να υπολογίσετε το σωστό μέρος του τύπου στη γραμμή τύπων, επιλέξτε το πλαίσιο και πατήστε F9:
   
   Σε αυτό το παράδειγμα, υπήρχε ένα πρόβλημα με τη λειτουργία αναζήτησης (Search) - υπήρχαν ανάμεικτα μέχρι επιχειρήματα. Είναι σημαντικό να θυμάστε ότι αν δεν ακυρώσει τον υπολογισμό της συνάρτησης και πατήστε το Enter, τότε η υπολογιζόμενη τμήμα θα παραμείνει αριθμό.
2. Πιέστε το «Υπολογίστε τον τύπο» στην ομάδα «τύπου» πάνω στην ταινία:
   
   
   Στο παράθυρο που εμφανίζεται, μπορείτε να υπολογίσει το βήμα φόρμουλα προς βήμα και να καθορίσει σε ποιο στάδιο και σε ποια συνάρτηση σφάλματος (εάν υπάρχει):
   

Πώς να καθορίσει αυτό που καθορίζει ότι το τύπο ή αναφέρονται

Για να καθοριστεί ποια από τα κύτταρα εξαρτάται από τον τύπο της ομάδας του «τύπου» στην κορδέλα, κάντε κλικ στο κουμπί «Trace Προηγούμενα»:

Υπάρχουν βέλη που δείχνουν από την οποία το αποτέλεσμα εξαρτάται.

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

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

Δίπλα στο «Trace Προηγούμενα» υπάρχει ένα κουμπί «Εξαρτωμένων», που εργάζονται με τον ίδιο τρόπο: εμφανίζει βέλη από το ενεργό κελί με τον τύπο σε κύτταρα που εξαρτώνται από αυτό.

Κουμπί «βέλη Κατάργηση» που βρίσκεται στο ίδιο μπλοκ, επιτρέπει να αφαιρέσετε Προηγούμενα, εξαρτημένων ή και τους δύο τύπους ταυτόχρονα βέλη:

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

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

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

Θα λάβετε το ποσό της διεύθυνσης Β3 κυττάρων με φύλλα «data1», «χαρακτήρα2», «data3»:

Τέτοια αντιμετώπιση λειτουργεί για φύλλα διατεταγμένα διαδοχικώς. Η σύνταξη είναι: = ΜΕΓΑΛΟΥ (pervyy_list: posledniy_list αναφορά στη σειρά!).

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

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

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

ΠΑΡΑΔΕΙΓΜΑ δημιουργία φράσεις πρότυπο χρησιμοποιώντας τύπους:

έχει ως αποτέλεσμα:

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

ΚΕΙΜΕΝΟ (τιμή? μορφή)

Η μορφή καθορίζεται σε εισαγωγικά με τον ίδιο τρόπο σαν να εισέλθει σε μια προσαρμοσμένη μορφή στα «Format Cells.»

Μπορείτε να αυτοματοποιήσετε πιο σύνθετα κείμενα. Στην πράξη μου ήταν να αυτοματοποιήσει μεγάλο χρονικό διάστημα, αλλά η ρουτίνα σχόλια σχετικά με τους λογαριασμούς διαχείρισης με τη μορφή «αριθμός έπεσε / τριαντάφυλλο στο ΧΧ για το σχέδιο, κυρίως λόγω της αύξησης / μείωσης της FAKTORA1 XX, αύξηση / μείωση του FAKTORA2 ΥΥ... »με τη μεταβαλλόμενη λίστα παράγοντες. Αν γράψετε τέτοια σχόλια συχνά και η διαδικασία της γραφής μπορεί algorithmization - αξίζει μία φορά προβλημάτισε δημιουργήσετε έναν τύπο ή μακροεντολή που θα σας σώσει από τουλάχιστον ένα μέρος του έργου.

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

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

Κατά συνέπεια, αν είχατε έναν τύπο που εξαρτάται από το κάθε κύτταρο, θα παύσουν να εργάζονται μετά την ένωσή τους (bug # N / A στις γραμμές 3-4 παραδείγματα):

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

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

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

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

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

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

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

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

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

Αναφορά Συγκεντρωτικού πίνακα έτοιμο. Το φίλτρο «Page 1» μπορείτε να επιλέξετε μόνο μία από τις πηγές δεδομένων, εάν είναι απαραίτητο:

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

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

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

1. LEN (LEN) - υπολογίζει την έκταση του κειμένου, ένα ενιαίο επιχείρημα - το κείμενο. Παράδειγμα: LEN ( "μηχανή") = 6.
2. Αναπληρωτής (αναπληρωματικά) - αντικαθιστά στο αλφαριθμητικό κειμένου ένα συγκεκριμένο κείμενο με το άλλο. Σύνταξη: Αναπληρωτής (κειμένου? star_tekst? nov_tekst). Παράδειγμα: ΥΠΟΚΑΤΑΣΤΑΤΟ ( "αυτοκίνητο", "αυτοκίνητο", "") = "κινητό".
3. ΠΑΝΩ (ΠΑΝΩ) - αντικαθιστά όλους τους χαρακτήρες σε μια σειρά με κεφαλαία γράμματα. Το μόνο επιχείρημα - το κείμενο. Παράδειγμα: UPPER ( "μηχανή") = "ΜΗΧΑΝΗ". Θα χρειαστεί Αυτή η δυνατότητα να κάνουν διάκριση πεζών-κεφαλαίων η αναζήτηση. Μετά από όλα, ΠΑΝΩ ( "Machine") = UPPER ( "μηχανή")

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

LEN ( "Ταρίφα Σούπερ MTS MTS") - LEN ( "Σούπερ Rate") = 6

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

6 / LEN ( "MTS") = 2

Αυτό είναι δύο φορές τη γραμμή «MTS» είναι στο πρωτότυπο.

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

= (LEN (κείμενο) -DLSTR (ΥΠΟΚΑΤΑΣΤΑΤΩΝ (UPPER (κείμενο)? UPPER (sought-για)? ""))) / LEN (sought-για)

Στο παράδειγμά μας, ο τύπος είναι ως εξής:

= (LEN (Α2) -DLSTR (ΥΠΟΚΑΤΑΣΤΑΤΩΝ (UPPER (Α2)? UPPER ($ E $ 1)? ""))) / LEN ($ E $ 1)