Πρ. Ο Mark από το Κάνσας έστειλε την ερώτηση Excel αυτής της εβδομάδας:
Οι συγκεντρωτικοί πίνακες του Excel λειτουργούν καλύτερα όταν τα δεδομένα κατανέμονται στις περισσότερες δυνατές εγγραφές, αλλά αυτός δεν είναι πάντα ο πιο διαισθητικός τρόπος φόρτωσης των δεδομένων στο Excel. Για παράδειγμα, είναι διαισθητικό να φορτώσετε δεδομένα όπως το Σχήμα 1, αλλά ο καλύτερος τρόπος για να αναλύσετε τα δεδομένα είναι όπως στο Σχήμα 2.
Πρώτον, θα αντιμετωπίσω τον λιγότερο από τον τέλειο τρόπο αντιμετώπισης του Excel με πολλά πεδία δεδομένων. Δεύτερον, θα δείξω μια απλή και γρήγορη μακροεντολή για να μετατρέψω το σχήμα 1 σε σχήμα 2.
Εάν τα δεδομένα σας είναι όπως το σχήμα 1, κατά τη διάρκεια του βήματος 3 του 4 του Οδηγού Συγκεντρωτικού Πίνακα, μπορείτε να σύρετε και τα πεδία 4 τετάρτων στην περιοχή δεδομένων του συγκεντρωτικού πίνακα, όπως φαίνεται στα δεξιά.
Εδώ είναι το λιγότερο από το τέλειο αποτέλεσμα. Από προεπιλογή, το Excel έχει τα πολλαπλά πεδία δεδομένων που κατεβαίνουν στη σελίδα. Μπορείτε να κάνετε κλικ στο γκρι κουμπί "Δεδομένα" και να το σύρετε προς τα πάνω και δεξιά για να κάνετε τα τέταρτα να διασχίσουν τη σελίδα. Ωστόσο, λείπουν σύνολα για κάθε περιοχή και τα σύνολα τριμήνου θα φαίνονται πάντα εκτός τόπου.
Έτσι, π. Ο Μάρκος χτύπησε το καρφί στο κεφάλι όταν είπε ότι τα δεδομένα πρέπει πραγματικά να έχουν τη μορφή του Σχήματος 2 για να τα αναλύσουν σωστά. Ακολουθεί μια μακροεντολή που θα μεταφέρει γρήγορα δεδομένα με τη μορφή του Σχήματος 1 στο Φύλλο 1 στο Φύλλο2 με τη μορφή του Σχήματος 2. Αυτή η μακροεντολή δεν είναι αρκετά γενική για να λειτουργεί με οποιοδήποτε σύνολο δεδομένων. Ωστόσο, θα πρέπει να είναι σχετικά εύκολο να το προσαρμόσετε στην ιδιαίτερη κατάστασή σας.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Μετά την εκτέλεση αυτής της μακροεντολής, τα δεδομένα θα είναι σε ευκολότερη ανάλυση, όπως φαίνεται στο σχήμα 2, παραπάνω. Τώρα, όταν χρησιμοποιείτε αυτά τα δεδομένα για έναν συγκεντρωτικό πίνακα, έχετε τον πλήρη έλεγχο των δεδομένων όπως το κανονικό.
