Όταν κάνω ένα ζωντανό σεμινάριο Power Excel, προσφέρω ότι αν κάποιος στο δωμάτιο έχει ποτέ ένα περίεργο πρόβλημα στο Excel, μπορεί να μου το στείλει για βοήθεια. Έτσι ήρθα να λάβω αυτό το πρόβλημα καθαρισμού δεδομένων. Κάποιος είχε ένα συνοπτικό φύλλο εργασίας που μοιάζει με αυτό:
Ήθελαν να αναδιαμορφώσουν τα δεδομένα έτσι ώστε:
Μια ενδιαφέρουσα ένδειξη για αυτά τα δεδομένα: Το 18 σε G4 φαίνεται να είναι ένα σύνολο των H4: K4. Είναι δελεαστικό να αφαιρέσετε τις στήλες G, L και ούτω καθεξής, αλλά πρώτα πρέπει να εξαγάγετε το όνομα του υπαλλήλου από το G3, L3 και ούτω καθεξής.
Ήταν στις 4 π.μ. την Κυριακή 9 Φεβρουαρίου όταν ενεργοποίησα τη συσκευή εγγραφής βίντεο και ηχογράφησα μερικά αδέξια βήματα στο Power Query για να λύσω το πρόβλημα. Δεδομένου ότι ήταν Κυριακή, μια μέρα που συνήθως δεν κάνω βίντεο, ζήτησα από τους ανθρώπους να στείλουν τις ιδέες τους για το πώς να λύσουν το πρόβλημα. Έχουν σταλεί 29 λύσεις.
Κάθε λύση προσφέρει κάποια δροσερή νέα βελτίωση στη διαδικασία μου. Το σχέδιό μου είναι να ξεκινήσω μια σειρά άρθρων που δείχνουν τις διάφορες βελτιώσεις στη μέθοδο μου.
Δες το βίντεο
Πριν ξεκινήσω αυτήν τη διαδικασία, σας καλώ να δείτε τη λύση μου:
Και ο κωδικός M που δημιούργησε το Power Query για μένα:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Πριν ξεκινήσουμε να μπαίνουμε στις λύσεις, ας εξετάσουμε πολλά κοινά σχόλια:
- Μερικοί από εσάς είπατε ότι θα πηγαίνατε προς τα πίσω για να καταλάβετε γιατί τα δεδομένα εμφανίζονται σε αυτήν τη μορφή για να ξεκινήσετε. Εκτιμώ αυτά τα σχόλια. Όλοι που είπαν ότι είναι καλύτερο άτομο από εμένα. Έχω μάθει όλα αυτά τα χρόνια ότι όταν ρωτάς "Γιατί;" Η απάντηση συνήθως περιλαμβάνει αυτόν τον πρώην υπάλληλο που ξεκίνησε πριν από 17 χρόνια και όλοι συνεχίζουν να το χρησιμοποιούν με αυτόν τον τρόπο, καθώς όλοι έχουμε συνηθίσει τώρα.
- Επίσης - πολλοί από εσάς - είπατε ότι η τελική λύση πρέπει να είναι ένας ψηλός κάθετος πίνακας και στη συνέχεια να χρησιμοποιήσετε έναν περιστρεφόμενο πίνακα για να παράγετε τα τελικά αποτελέσματα. Ο Jonathan Cooper συνοψίζει αυτό το καλύτερο: "Συμφωνώ επίσης με ορισμένα από τα άλλα σχόλια του YouTube ότι ένα κατάλληλο σύνολο δεδομένων δεν θα είχε" Σύνολο "και δεν θα χρειαζόταν να περιστραφεί στο τέλος. Αλλά αν ο χρήστης θέλει πραγματικά ένα απλό παλιό τραπέζι τότε τους δίνετε ό, τι θέλουν. " Βλέπω πραγματικά και τις δύο πλευρές αυτού. Μου αρέσει ένας περιστρεφόμενος πίνακας και το μόνο πράγμα πιο διασκεδαστικό από το Power Query είναι το Power Query με έναν ωραίο περιστρεφόμενο πίνακα στην κορυφή. Αλλά αν μπορούμε να κάνουμε το όλο πράγμα στο Power Query, τότε ένα λιγότερο πράγμα να σπάσουμε.
Εδώ είναι οι υπερσυνδέσεις με διάφορες τεχνικές
-
Τεχνικές Power Query
- Αρίθμηση ομάδων αρχείων
- Εξαγωγή αριστερά δύο χαρακτήρων
- Συνολική στήλη
- Διαφορετικά εάν υπάρχουν ρήτρες
- Πολλές ίδιες κεφαλίδες στο Power Query
- Τι να διαγράψετε
- Διαχωρισμός με Q
- Ταξινόμηση στοιχείων γραμμής
- Power Query Solutions από Excel MVPs
-
Μετακίνηση πέρα από τη διεπαφή ερωτήματος ενέργειας
- Πίνακας. Διάσπαση
- Ο κόσμος του Bill Szysz
-
Λύσεις τύπων
- Ένας τύπος δυναμικής σειράς
- Στήλες βοηθού παλιού σχολείου
- Λύσεις τύπων
-
Σύνθετο όλων των ιδεών από το παραπάνω και το τελικό βίντεο
- Σύνθετο από τις καλύτερες ιδέες από όλους
