Χωρίζοντας ένα τραπέζι σε φύλλα

Το Microsoft Excel διαθέτει πολλά εργαλεία για τη συλλογή δεδομένων από πολλούς πίνακες (από διαφορετικά φύλλα ή από διαφορετικά αρχεία): άμεσες συνδέσεις, λειτουργία ΕΜΜΕΣΟΣ (ΕΜΜΕΣΟΣ), πρόσθετα Power Query και Power Pivot, κ.λπ. Από αυτήν την πλευρά του οδοφράγματος, όλα φαίνονται καλά.

Αλλά αν αντιμετωπίσετε ένα αντίστροφο πρόβλημα – διάδοση δεδομένων από έναν πίνακα σε διαφορετικά φύλλα – τότε όλα θα είναι πολύ πιο λυπηρά. Αυτή τη στιγμή, δεν υπάρχουν πολιτισμένα ενσωματωμένα εργαλεία για τέτοιο διαχωρισμό δεδομένων στο οπλοστάσιο του Excel, δυστυχώς. Επομένως, θα πρέπει να χρησιμοποιήσετε μια μακροεντολή στη Visual Basic ή να χρησιμοποιήσετε τον συνδυασμό εγγραφής μακροεντολών + Power Query με λίγη "βελτίωση αρχείου" μετά.

Ας ρίξουμε μια πιο προσεκτική ματιά στο πώς μπορεί να εφαρμοστεί αυτό.

Διατύπωση του προβλήματος

Έχουμε ως αρχικά δεδομένα έναν τέτοιο πίνακα με μέγεθος άνω των 5000 σειρών για πωλήσεις:

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

Προετοιμάστε

Για να μην περιπλέκεται ο κώδικας μακροεντολής και να γίνεται όσο το δυνατόν πιο κατανοητός, ας εκτελέσουμε μερικά προπαρασκευαστικά βήματα.

Πρώτον, η δημιουργήστε έναν ξεχωριστό πίνακα αναζήτησης, όπου μια στήλη θα αναφέρει όλες τις πόλεις για τις οποίες θέλετε να δημιουργήσετε ξεχωριστά φύλλα. Φυσικά, αυτός ο κατάλογος μπορεί να μην περιέχει όλες τις πόλεις που υπάρχουν στα δεδομένα πηγής, αλλά μόνο εκείνες για τις οποίες χρειαζόμαστε αναφορές. Ο ευκολότερος τρόπος για να δημιουργήσετε έναν τέτοιο πίνακα είναι να χρησιμοποιήσετε την εντολή Δεδομένα – Κατάργηση διπλότυπων (Δεδομένα — Κατάργηση διπλότυπων) για αντίγραφο στήλης Πόλη ή λειτουργία UNIK (ΜΟΝΑΔΙΚΟΣ) – εάν διαθέτετε την πιο πρόσφατη έκδοση του Excel 365.

Δεδομένου ότι τα νέα φύλλα στο Excel δημιουργούνται από προεπιλογή πριν (στα αριστερά) του τρέχοντος (προηγούμενου), είναι επίσης λογικό να ταξινομούνται οι πόλεις σε αυτόν τον κατάλογο με φθίνουσα σειρά (από το Ω στο Α) – και μετά τη δημιουργία, η πόλη Τα φύλλα θα ταξινομηθούν αλφαβητικά.

Δεύτερον, η пμετατρέψτε και τους δύο πίνακες σε δυναμικό ("έξυπνος") για να διευκολύνετε τη συνεργασία μαζί τους. Χρησιμοποιούμε την εντολή Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα) ή συντόμευση πληκτρολογίου Ctrl+T. Στην καρτέλα που εμφανίζεται Κατασκευαστής (Σχέδιο) ας τους φωνάξουμε tablProdaji и TableCity, αντίστοιχα:

Μέθοδος 1. Μακροεντολή για διαίρεση με φύλλα

Στην καρτέλα Για προχωρημένους προγραμματιστή (Προγραμματιστής) κάντε κλικ στο κουμπί Visual Basic ή χρησιμοποιήστε συντόμευση πληκτρολογίου άλλος+F11. Στο παράθυρο του προγράμματος επεξεργασίας μακροεντολών που ανοίγει, εισαγάγετε μια νέα κενή μονάδα μέσω του μενού Εισαγωγή – Ενότητα και αντιγράψτε τον παρακάτω κώδικα εκεί:

Sub Splitter() Για κάθε κελί στο εύρος ("таблГорода") Εύρος ("таблПродажи"). Πεδίο AutoFilter:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Αντιγραφή Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Επόμενο κελί φύλλα εργασίας("Данные").ShowAllData End Sub	  

Εδώ με βρόχο Για Κάθε… Επόμενο υλοποίησε το πέρασμα μέσα από τα κελιά του καταλόγου TableCity, όπου για κάθε πόλη φιλτράρεται (μέθοδος Αυτόματο φίλτρο) στον αρχικό πίνακα πωλήσεων και, στη συνέχεια, αντιγράψτε τα αποτελέσματα στο φύλλο που δημιουργήθηκε πρόσφατα. Στην πορεία, το φύλλο που δημιουργήθηκε μετονομάζεται στο ίδιο όνομα της πόλης και η αυτόματη προσαρμογή του πλάτους των στηλών για ομορφιά είναι ενεργοποιημένη σε αυτό.

Μπορείτε να εκτελέσετε τη μακροεντολή που δημιουργήσατε στο Excel στην καρτέλα προγραμματιστή κουμπί Μακροεντολές (Προγραμματιστής — Μακροεντολές) ή συντόμευση πληκτρολογίου άλλος+F8.

Μέθοδος 2. Δημιουργήστε πολλαπλά ερωτήματα στο Power Query

Η προηγούμενη μέθοδος, παρ' όλη τη συμπαγή και απλότητά της, έχει ένα σημαντικό μειονέκτημα - τα φύλλα που δημιουργούνται από τη μακροεντολή δεν ενημερώνονται όταν γίνονται αλλαγές στον αρχικό πίνακα πωλήσεων. Εάν είναι απαραίτητη η άμεση ενημέρωση, τότε θα πρέπει να χρησιμοποιήσετε το πακέτο VBA + Power Query, ή μάλλον, να δημιουργήσετε χρησιμοποιώντας μια μακροεντολή όχι μόνο φύλλα με στατικά δεδομένα, αλλά ενημερωμένα ερωτήματα Power Query.

Η μακροεντολή σε αυτή την περίπτωση είναι εν μέρει παρόμοια με την προηγούμενη (έχει επίσης έναν κύκλο Για Κάθε… Επόμενο για επανάληψη σε πόλεις στον κατάλογο), αλλά μέσα στον βρόχο δεν θα υπάρχει πλέον φιλτράρισμα και αντιγραφή, αλλά δημιουργία ερωτήματος Power Query και αποστολή των αποτελεσμάτων του σε νέο φύλλο:

Sub Splitter2() Για κάθε κελί στο εύρος ("πίνακας πόλεων") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Πηγή , {{""Κατηγορία"", πληκτρολογήστε κείμενο}, {""Όνομα"", πληκτρολογήστε κείμενο}, {""Πόλη"", πληκτρολογήστε κείμενο}, {""Διαχειριστής"", πληκτρολογήστε κείμενο}, {""Συμφωνία" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Σειρές με εφαρμογή φίλτρου"" = Table.Se " & _ "lectRows(#""Αλλαγμένος τύπος"", κάθε ([Πόλη] = """ & κελί.Τιμή & """))" & Chr(13) & "" & Chr(10) & "σε " & Chr(13) & "" & Chr(10) & " #""Σειρές με εφαρμογή φίλτρου""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Πηγή δεδομένων=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertwordSeleveseCells. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = κελί.  

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

Με οποιεσδήποτε αλλαγές στα δεδομένα προέλευσης, θα αρκεί να ενημερώσετε τον αντίστοιχο πίνακα με το δεξί κουμπί του ποντικιού – την εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω) ή ενημερώστε όλες τις πόλεις ταυτόχρονα μαζικά χρησιμοποιώντας το κουμπί Ενημέρωση όλων αυτί ημερομηνία (Δεδομένα — Ανανέωση όλων).

• Τι είναι οι μακροεντολές, πώς να τις δημιουργήσετε και να τις χρησιμοποιήσετε
• Αποθήκευση φύλλων βιβλίου εργασίας ως ξεχωριστά αρχεία
• Συλλογή δεδομένων από όλα τα φύλλα του βιβλίου σε έναν πίνακα