Αυτό είναι το πρόβλημα του προϋπολογισμού των πυροσβεστών. Οι άνθρωποι σε ένα πυροσβεστικό σπίτι έχουν κάνει λάθος τους προϋπολογισμούς τους στο Excel. Ένας εκπληκτικός μετασχηματισμός Power Query παρέχει τη λύση.
Δες το βίντεο
- Ο Steve πρέπει να αθροίσει αριθμούς που έχουν εισαχθεί σε μια στήλη κειμένου
- Υπάρχουν πολλές γραμμές σε κάθε κελί, διαχωρισμένες με alt = "" + Enter
- Πρέπει να χωρίσετε αυτές τις γραμμές σε σειρές και μετά να αναλύσετε το ποσό του δολαρίου από τη μέση κάθε κελιού
- Σύνοψη ανά Κέντρο κόστους
- Δημιουργήστε έναν πίνακα αναζήτησης
- Λάβετε σύνολα από τον πίνακα αναζήτησης, χρησιμοποιώντας το IFNA για να αγνοήσετε τα σφάλματα στην κενή σειρά
- Μπόνους: Προσθέστε μια μακροεντολή συμβάντος για να ενημερώσετε το φύλλο εργασίας όταν αλλάζουν ένα κελί.
Μεταγραφή βίντεο
Μάθετε το Excel από, Podcast Επεισόδιο 2160: SUM Data που έχουν εισαχθεί Alt +.
Γεια. Καλώς ήλθατε πίσω στο netcast. Είμαι ο Μπιλ Τζέλεν. Δεν το κάνω αυτό. Έλαβα μια ερώτηση από κάποιον που έχει δεδομένα - δεδομένα προϋπολογισμού - μοιάζει με αυτό. Τώρα, βάζω ψεύτικες λέξεις εδώ, ώστε να μην έχουμε τα στοιχεία του προϋπολογισμού τους, αλλά το άτομο που ήταν νέο στο λογιστικό τμήμα, πήγε σε μια εταιρεία και αυτή η εταιρεία εδώ και χρόνια κάνει τους προϋπολογισμούς της έτσι. Δεν είναι λογιστές που κάνουν τον προϋπολογισμό, είναι γραμμικοί άνθρωποι, αλλά αυτός είναι ο τρόπος που το έχουν κάνει και δεν μπορεί να τους κάνει να αλλάξουν. Ορίστε λοιπόν ο στόχος μας. Λέει ότι αυτό είναι τόσο κακό όσο το να πληκτρολογείς τον προϋπολογισμό στο Word.
Λοιπόν, σχεδόν, αλλά ευτυχώς, χάρη στο ερώτημα ενέργειας, θα σώσει το πρόβλημά μας. Εδώ είναι ο στόχος μας. Για κάθε ΚΕΝΤΡΟ ΚΟΣΤΟΣ εδώ, θέλουμε να αναφέρουμε το σύνολο όλων αυτών των αριθμών. Υπάρχει, λοιπόν, ένα όνομα δαπανών, ένα -, συνήθως ένα -, τότε ένα σύμβολο $ και, στη συνέχεια, για να κάνουμε τη ζωή ενδιαφέρουσα, κάθε φορά, μια τυχαία νότα μετά. όχι όλες τις ώρες, μόνο μερικές φορές. Κενή σειρά μεταξύ καθεμιάς. Τόνοι και τόνοι δεδομένων.
Λοιπόν, εδώ θα κάνω. Θα έρθω στο κάτω μέρος, στο τελευταίο κελί, θα επιλέξω όλα αυτά, συμπεριλαμβανομένων των τίτλων. Θα δημιουργήσω ένα ΟΝΟΜΑ. Θα το ονομάσω MyData. MyData, έτσι, εντάξει; Καλώς. Τώρα πρόκειται να χρησιμοποιήσουμε το ερώτημα τροφοδοσίας το οποίο είναι δωρεάν το 2010 ή το 2013, ενσωματωμένο στο 2016 και το 2016 στο Office 365. Αυτό θα προέλθει από έναν ΠΙΝΑΚΑ Ή ΣΕΙΡΑ. Καλώς. Πρώτο, όποτε έχουμε αυτά τα κενά στην ΣΤΗΛΗ Α, όλα τα NULLS που θέλουμε να ξεφορτωθούμε. Θα καταργήσω λοιπόν το NULL. Φοβερός. Εντάξει. Πραγματικά, σε αυτά τα δεδομένα, σε αυτήν την έκδοση των δεδομένων, επειδή πρόκειται να δημιουργήσω ένα VLOOKUP, δεν χρειαζόμαστε αυτήν τη στήλη. Λοιπόν, θα κάνω δεξί κλικ και θα ξεφορτωθώ αυτήν τη στήλη.
Καλώς. Τώρα, εδώ θα συμβεί η φρικιαστική μαγεία. Επιλέξτε αυτήν τη στήλη, SPLIT COLUMN BY A DELIMITER και σίγουρα θα πάμε στο ADVANCED. Ο οριοθέτης πρόκειται να είναι ένας ειδικός χαρακτήρας και θα χωρίσουμε κάθε εμφάνιση του οριοθέτη. Λοιπόν, εδώ, νομίζω ότι το έχουν ήδη καταλάβει γιατί το επέκτεινα, αλλά θα σας δείξω. ΕΙΣΑΓΩΓΗ ΕΙΔΙΚΟΥ ΧΑΡΑΚΤΗΡΑ. Θα πω ότι είναι ένα LINE FEED, εντάξει, έτσι, σε κάθε εμφάνιση του LINE FEED, και θα πάω στο SPLIT INTO ROWS. Εντάξει, και ακριβώς αυτό που θα συμβεί εδώ είναι, 1, 2, 3, 4, 5, θα πάρω 5 σειρές ή θα πω 1001, αλλά, σε κάθε σειρά, θα έχει διαφορετική γραμμή από αυτό το κελί. Αυτό είναι καταπληκτικό. Υπάρχουν 1, 2, 3, 4, 5, 1001. Εντάξει. Τώρα πρέπει απλώς να αναλύσουμε αυτό το κακό αγόρι. Καλώς,λοιπόν, επιλέξτε αυτήν τη στήλη, ΠΕΤΡΕΛΑΙΑ ΣΤΗΛΗ ΑΠΟ ΔΙΑΜΟΝΗ. Αυτή τη φορά, οριοθέτης θα είναι ένα σύμβολο $. Αυτό είναι τέλειο, μία φορά, στο πρώτο σύμβολο $ που βρίσκουμε, σε περίπτωση που υπάρχει έξοδος $ στο μέλλον. Θα πηγαίνουμε ΣΤΙΣ ΣΤΗΛΕΣ. Κάντε κλικ στο OK. Καλώς. Υπάρχουν λοιπόν λεπτομέρειες. Εδώ είναι τα χρήματά μας.
Τώρα, θα το χωρίσω στο SPACE. Επιλέξτε λοιπόν αυτήν τη στήλη, SPLIT COLUMN BY A DELIMITER και ο οριοθέτης θα είναι ΧΩΡΟΣ, ναι, μία φορά στο LEFT-MOST DELIMITER, κάντε κλικ στο OK και δεν χρειάζομαι αυτά τα σχόλια εκεί έξω, έτσι ώστε αυτά τα σχόλια επανέρχομαι στην ΑΦΑΙΡΕΣΗ. Στην πραγματικότητα, δεν το χρειάζομαι ούτε επειδή προσπαθώ απλώς να πάρω συνολικά όλα αυτά τα πράγματα, οπότε πρόκειται να ΚΑΤΑΡΓΗΣΗ.
Τώρα, μεταμορφώστε. ΟΜΑΔΑ ΑΠΟ ΚΕΝΤΡΟ ΚΟΣΤΟΥΣ, ΝΕΟ ΟΝΟΜΑ ΣΤΗΛΗΣ θα ονομάζεται ΣΥΝΟΛΟ, ΛΕΙΤΟΥΡΓΙΑ θα είναι το SUM, και σε ποια στήλη πρόκειται να SUM; Οι ΛΕΠΤΟΜΕΡΕΙΕΣ2.1. Πανεμορφη. Κάντε κλικ στο OK, εντάξει και αυτό που καταλήγουμε είναι μία γραμμή ανά ΚΕΝΤΡΟ ΚΟΣΤΟΥ με το ΣΥΝΟΛΟ όλων αυτών των στοιχείων γραμμής. ΑΡΧΙΚΗ, ΚΛΕΙΣΤΟ & ΦΟΡΤΩΣΗ. Πιθανότατα θα εισαγάγει ένα νέο φύλλο εργασίας. Ελπίζω να εισαγάγει ένα νέο φύλλο εργασίας, και το κάνει, και αυτό το φύλλο εργασίας ονομάζεται MYDATA_1. MYDATA_1.
Καλώς. Τώρα θα επιστρέψουμε εδώ στα αρχικά δεδομένα και θα κάνουμε αυτά τα βήματα. Στην πρώτη, = VLOOKUP από 1001 στα αποτελέσματά μας. Αυτό είναι σαν να δημιουργούμε μια κυκλική αναφορά, αλλά δεν πρόκειται να μας δώσει μια κυκλική αναφορά. , 2, ΛΑΘΟΣ. Θέλω τον ακριβή αγώνα. Εντάξει, αλλά δεν πρόκειται να το κάνουμε αυτό για τα κενά κελιά. Λοιπόν, θα πω, λοιπόν, στην πραγματικότητα, ας το αντιγράψουμε εντελώς. CONTROL + C, προχωρήστε απλώς για να δείτε τι παίρνουμε. Ίσως παίρνουμε N / As και μπορώ να το ξεφορτωθώ με το IFNA. Ναι, όμορφη, εντάξει. Ας απαλλαγούμε από το N / As. Αν δεν υπάρχει, τότε θέλουμε απλώς "". Δεν θέλουμε τίποτα εκεί. CONTROL + ENTER. Καλώς. Τώρα, αυτό πρέπει να είναι το ΣΥΝΟΛΟ. Ας δούμε αν μπορούμε να βρούμε ένα σύντομο και απλά να κάνουμε τα μαθηματικά. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94 και το ΣΥΝΟΛΟ, 27742.23 είναι αυτό. Καταπληκτικό. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
Τώρα, εδώ είναι η συμφωνία. Λοιπόν, έχουμε εκείνους τους ανθρώπους που βρίσκονται εδώ να αλλάζουν πράγματα, εντάξει, και ας πούμε ότι περνούν και αλλάζουν τον προϋπολογισμό, 40294.48, και έρχονται εδώ και αλλάζουν αυτό σε 6000, έτσι, και προσθέτουν ένα νέο, ALT + ENTER, SOMETHING - $ sign, $ 1000 μόλις προστέθηκε. Καλώς. Τώρα, φυσικά, όταν πατάω ENTER, αυτός ο αριθμός, 40294.48, δεν πρόκειται να ενημερωθεί, εντάξει, αλλά αυτό που πρέπει να κάνουμε είναι να μεταβούμε στην καρτέλα ΔΕΔΟΜΕΝΑ και θέλουμε να ανανεώσουμε όλα. Λοιπόν, 40294.48. Ρολόι, ρολόι, ρολόι, ρολόι. ΔΙΑΦΟΡΑ ΟΛΩΝ. Εκπληκτικό.
Λατρεύω το ερώτημα ισχύος. Το ερώτημα ενέργειας είναι το πιο εκπληκτικό πράγμα. Αυτά τα δεδομένα, τα οποία ουσιαστικά μοιάζουν με τα δεδομένα λέξεων σε ένα κελί, τα έχουμε τώρα ενημερώσει. Θα μπορούσατε μάλιστα να δημιουργήσετε κάποια μακροεντολή που λέει ότι κάθε φορά που κάποιος αλλάζει κάτι στο COLUMN C, προχωράμε και κάνουμε κλικ στο REFRESH ALL χρησιμοποιώντας τη μακροεντολή και απλώς έχουμε αυτά τα αποτελέσματα συνεχώς, συνεχώς αναζωογονητικά.
Τι φρικτή ερώτηση έστειλε. Αισθάνομαι άσχημα για τον Steve που πρέπει να το αντιμετωπίσει, αλλά τώρα, χρησιμοποιώντας το ερώτημα ενέργειας στο Office 365 ή το κατεβάσατε για το 2010 ή το 2013, έχετε έναν πολύ, πολύ εύκολο τρόπο να το λύσετε.
Περίμενε. Εντάξει, ένα πρόσθετο: ας το κάνουμε ακόμη καλύτερο. Αυτό το φύλλο ονομάζεται DATA και έχω αποθηκεύσει το βιβλίο εργασίας με δυνατότητα μακροεντολής, έτσι xlsm. Εάν είστε xlsx, μην παραλείψετε την αποθήκευση ως xlsm. ALT + F11. Βρείτε το βιβλίο εργασίας που ονομάζεται DATA, κάντε διπλό κλικ, πάνω αριστερά, ΦΥΛΛΟ ΕΡΓΑΣΙΑΣ και, στη συνέχεια, ΑΛΛΑΓΗ κάθε φορά που αλλάζουμε το φύλλο εργασίας και θα πούμε ACTIVEWORKBOOK.REFRESHALL και, στη συνέχεια, κλείστε, εντάξει και τώρα ας το δοκιμάσουμε. Ας επεξεργαστούμε κάτι. Λοιπόν, θα πάρουμε εκείνα τα σμέουρα που είναι σήμερα 8.000 και θα τα αλλάξουμε σε 1000, οπότε μειώνουμε κατά 7000. Όταν πατάω το ENTER, θέλω να δω ότι οι 42.000 πέφτουν σε 35.000. Αχ. Φοβερός.
Λοιπόν, γεια. Εκεί συνήθως σας παρακαλώ να αγοράσετε το βιβλίο μου, αλλά σήμερα θα σας ζητήσω να αγοράσετε το βιβλίο των φίλων μου - Ken Puls και Miguel Escobar - Το M είναι για (DATA) MONKEY. Όλα όσα έμαθα για το ερώτημα ισχύος, έμαθα από αυτό το βιβλίο. Είναι ένα καταπληκτικό βιβλίο. Ρίξτε μια ματιά.
Περίληψη επεισοδίου: Ο Steve έχει αριθμούς για το άθροισμα που έχουν εισαχθεί σε μια στήλη κειμένου. πολλαπλές γραμμές σε κάθε κελί, διαχωρισμένες με ALT + ENTER. πρέπει να χωρίσετε αυτές τις γραμμές σε σειρές και μετά να αναλύσετε το ποσό του δολαρίου από τη μέση κάθε κελιού. συνοψίστε με COST CENTER; δημιουργήστε έναν πίνακα αναζήτησης. λάβετε σύνολα από τον πίνακα αναζήτησης, χρησιμοποιώντας IFNA για να αγνοήσετε τα σφάλματα στην κενή σειρά. και στη συνέχεια, ένα μπόνους, μακροεντολή στο τέλος, μια μακροεντολή συμβάντος για την ενημέρωση του φύλλου εργασίας όταν αλλάζουν ένα κελί.
Θέλω να ευχαριστήσω τον Steve για την αποστολή αυτής της ερώτησης και χαίρομαι πολύ που έχω μια απάντηση - πριν το ερώτημα για την εξουσία, θα ήταν πραγματικά, πολύ δύσκολο - και θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Podcast2160.xlsm
