Αντιγράψτε τις τιμές Quick Stats στο Πρόχειρο - Συμβουλές Excel

Πίνακας περιεχομένων

Ποιος είναι ο κωδικός ASCII για μια καρτέλα;

Η ερώτηση ήρθε κατά τη διάρκεια ενός σεμιναρίου Excel στο Tampa: Δεν θα ήταν ωραίο αν μπορούσατε να αντιγράψετε τα στατιστικά στοιχεία από τη γραμμή κατάστασης στο πρόχειρο για να επικολλήσετε αργότερα σε ένα εύρος;

Πίεσα το άτομο που έθεσε την ερώτηση για το πώς ακριβώς πρέπει να λειτουργεί η πάστα. Φυσικά, δεν μπορείτε να επικολλήσετε άμεσα τα στατιστικά στοιχεία, επειδή έχετε επιλέξει πολλά σημαντικά κελιά. Θα πρέπει να περιμένετε, να επιλέξετε ένα άλλο κενό εύρος του υπολογιστικού φύλλου, την επικόλληση (όπως σε Ctrl + V) και τα στατιστικά στοιχεία θα εμφανίζονται σε εύρος 6-σειρών με 2 στήλες. Το άτομο που έκανε την ερώτηση πρότεινε ότι θα ήταν στατικές τιμές.

Δεν προσπάθησα να απαντήσω στην ερώτηση κατά τη διάρκεια του σεμιναρίου, γιατί ήξερα ότι θα ήταν λίγο δύσκολο να το κάνω.

Όμως, ξεκίνησα πρόσφατα μια μακροεντολή για να δω αν θα μπορούσε να γίνει. Η ιδέα μου ήταν να δημιουργήσω μια μεγάλη συμβολοσειρά κειμένου που θα μπορούσε να επικολληθεί. Για να αναγκάσετε τα στοιχεία να εμφανίζονται σε δύο στήλες, η συμβολοσειρά κειμένου θα πρέπει να έχει την ετικέτα για τη στήλη 1 (Άθροισμα) και στη συνέχεια μια καρτέλα και την τιμή για τη στήλη 2. Στη συνέχεια, θα χρειαστείτε μια επιστροφή μεταφοράς, την ετικέτα για σειρά 2, στήλη 1 και, στη συνέχεια, μια άλλη καρτέλα, η τιμή και ούτω καθεξής.

Το ήξερα ότι το Application.WorksheetFunction είναι ένας πολύ καλός τρόπος για να επιστρέψετε τα αποτελέσματα των συναρτήσεων του Excel στο VBA, αλλά ότι δεν υποστηρίζει όλες τις 400+ λειτουργίες του Excel. Μερικές φορές, εάν το VBA έχει ήδη παρόμοια λειτουργία (ΑΡΙΣΤΕΡΑ, ΔΕΞΙΑ, MID), τότε το Application.WorksheetFunction δεν θα υποστηρίζει αυτήν τη λειτουργία. Ενεργοποίησα το VBA με Alt + F11, έδειξα το άμεσο παράθυρο με Ctrl + G και έπειτα πληκτρολόγησα ορισμένες εντολές για να βεβαιωθώ ότι υποστηρίζονται και οι έξι λειτουργίες της γραμμής κατάστασης. Ευτυχώς, και οι έξι τιμές επέστρεψαν που ταιριάζουν με αυτές που εμφανίζονται στη γραμμή κατάστασης.

Για να μειώσετε τη μακροεντολή, μπορείτε να αντιστοιχίσετε το Application.WorksheetFunction σε μια μεταβλητή:

Set WF = Application.WorksheetFunction

Στη συνέχεια, αργότερα στη μακροεντολή, μπορείτε απλά να ανατρέξετε στο WF.Sum (Επιλογή) αντί να πληκτρολογείτε το Application.WorksheetFunction ξανά και ξανά.

Ποιος είναι ο κωδικός ASCII για μια καρτέλα;

Άρχισα να χτίζω τη συμβολοσειρά κειμένου. Επέλεξα μια μεταβλητή MS για MyString.

MS = "Sum:" &

Αυτό είναι το σημείο όπου χρειαζόμουν έναν χαρακτήρα καρτέλας. Είμαι αρκετά geeky για να γνωρίζω μερικούς χαρακτήρες ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), αλλά δεν μπορούσα να θυμηθώ την καρτέλα. Καθώς επρόκειτο να κατευθυνθώ στο Bing για να το αναζητήσω, θυμήθηκα ότι θα μπορούσατε να χρησιμοποιήσετε το vblf στον κωδικό σας για μια γραμμή τροφοδοσίας ή vbcr στον κωδικό σας για επιστροφή μεταφοράς, οπότε πληκτρολόγησα το vbtab με πεζά γράμματα. Στη συνέχεια μετακόμισα σε μια νέα γραμμή για να επιτρέψω στο Excel VBA να κεφαλαιοποιήσει τις λέξεις που κατάλαβε. Ήλπιζα να δω το vbtab να παίρνει ένα κεφάλαιο, και σίγουρα, η γραμμή έγινε κεφαλαία, δείχνοντας ότι η VBA θα μου έδινε έναν χαρακτήρα καρτέλας.

Εάν πληκτρολογήσετε το VBA σας με πεζά γράμματα, όταν πηγαίνετε σε μια νέα γραμμή, θα δείτε ότι όλες οι σωστά γραμμένες λέξεις παίρνουν ένα κεφαλαίο γράμμα κάπου στη λέξη. Στην παρακάτω εικόνα, τα vblf, vbcr, vbtab είναι γνωστά ως vba και κεφαλαιοποιούνται αφού μετακινηθούν σε μια νέα γραμμή. Ωστόσο, το πράγμα που έφτιαξα, το vbampersand δεν είναι γνωστό στο VBA, οπότε δεν γίνεται κεφαλαιοποίηση.

Σε αυτό το σημείο, ήταν θέμα ένωσης 6 ετικετών και 6 τιμών σε μια μεγάλη συμβολοσειρά. Θυμηθείτε στον παρακάτω κώδικα ότι το _ στο τέλος κάθε γραμμής σημαίνει ότι η γραμμή κώδικα συνεχίζεται στην επόμενη γραμμή.

Sub CopyQuickStatsToClipboard1() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr MsgBox MS End Sub

Αφού ένωσα όλες τις ετικέτες και τις τιμές μαζί, ήθελα να θαυμάσω τη δουλειά μου, γι 'αυτό έδειξα το αποτέλεσμα σε ένα MsgBox. Έτρεξα τον κώδικα και λειτούργησε όμορφα:

Νόμιζα ότι ήμουν ελεύθερος στο σπίτι. Αν μπορούσα απλώς να μεταφέρω το MS στο πρόχειρο, θα μπορούσα να ξεκινήσω την εγγραφή του Podcast 1894. Ίσως το MS.Copy να κάνει το κόλπο;

Δυστυχώς, δεν ήταν τόσο εύκολο. Το MS.Copy δεν ήταν έγκυρη γραμμή κώδικα.

Έτσι, πήγα στο Google και έψαξα για "Excel VBA Copy Variable to Clipboard". Ένα από τα κορυφαία αποτελέσματα ήταν αυτή η ανάρτηση στον πίνακα μηνυμάτων. Σε αυτό το post, οι παλιοί μου φίλοι Juan Pablo και NateO προσπαθούσαν να βοηθήσουν το ΕΠ. Η πραγματική συμβουλή, ωστόσο, ήταν εκεί που ο Juan Pablo πρότεινε να χρησιμοποιήσει κάποιον κώδικα από τον ιστότοπο του Excel MVP Chip Pearson. Βρήκα αυτήν τη σελίδα που εξηγούσε πώς να μεταφέρω τη μεταβλητή στο πρόχειρο.

Για να προσθέσετε κάτι στο πρόχειρο, πρέπει πρώτα να μεταβείτε στο μενού Εργαλεία του παραθύρου VBA και να επιλέξετε Αναφορές. Αρχικά θα δείτε μερικές αναφορές που ελέγχονται από προεπιλογή. Η βιβλιοθήκη Microsoft Forms 2.0 δεν θα ελεγχθεί. Πρέπει να το βρείτε στη μεγάλη λίστα και να το προσθέσετε. Ευτυχώς, για μένα, ήταν στην πρώτη σελίδα των επιλογών, για το πού το δείχνει το πράσινο βέλος. Μόλις προσθέσετε το σημάδι επιλογής δίπλα στην αναφορά, μετακινείται στην κορυφή.

Ο κωδικός του τσιπ δεν θα λειτουργήσει εάν δεν προσθέσετε την αναφορά, οπότε μην παραλείψετε το παραπάνω βήμα!

Μόλις προσθέσετε την αναφορά, ολοκληρώστε τη μακροεντολή χρησιμοποιώντας τον κωδικό του τσιπ:

Sub CopyQuickStatsToClipboard() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Πριν από την εγγραφή του podcast, έκανα μια δοκιμή για να βεβαιωθώ ότι λειτουργεί. Σίγουρα, όταν έτρεξα τη μακροεντολή, μετά επέλεξα ένα νέο εύρος και χτύπησα το Ctrl + V για επικόλληση, το πρόχειρο αδειάστηκε σε ένα εύρος στηλών 6 σειρών x 2.

Ωχ! Ετοίμασα την κάρτα τίτλου PowerPoint για το επεισόδιο, ενεργοποίησα το Camtasia Recorder και κατέγραψα τα παραπάνω. Όμως… καθώς επρόκειτο να δείξω τις πιστώσεις κλεισίματος, ένα συναίσθημα γκρίνια μου πέρασε. Αυτή η μακροεντολή επικολλούσε τα στατιστικά στοιχεία ως στατικές τιμές. Τι γίνεται αν αλλάξουν τα υποκείμενα δεδομένα; Δεν θέλετε να ενημερωθεί το επικολλημένο μπλοκ; Υπήρχε μια μακρά παύση στο podcast όπου σκέφτηκα τι να κάνω. Τέλος, έκανα κλικ στο εικονίδιο Camtasia Pause Recording και πήγα να δω αν μπορούσα να βάλω έναν τύπο μέσα στη συμβολοσειρά MS και αν θα επικολλήθηκε σωστά. Σίγουρα, το έκανε. Δεν ολοκλήρωσα ούτε τη μακροεντολή ούτε έκανα περισσότερες από μία δοκιμές όταν ενεργοποίησα ξανά τη συσκευή εγγραφής και μίλησα για αυτήν τη μακροεντολή. Στο podcast, θεωρούσα ότι αυτό δεν θα λειτουργούσε ποτέ για μη συνεχόμενες επιλογές, αλλά σε μεταγενέστερες δοκιμές, λειτουργεί.Εδώ είναι η μακροεντολή για επικόλληση ως τύπους:

Sub CopyQuickStatsAsFormulas() Set WF = Application.WorksheetFunction MA = Selection.Address MS = "Average: " & vbTab & "=AVERAGE(" & MA & ")" & vbCr _ & "Count: " & vbTab & "=CountA(" & MA & ")" & vbCr _ & "Numerical Count: " & vbTab & "=Count(" & MA & ")" & vbCr _ & "Min: " & vbTab & "=Min(" & MA & ")" & vbCr _ & "Max: " & vbTab & "=Max(" & MA & ")" & vbCr _ & "Sum: " & vbTab & "=Sum(" & MA & ")" & vbCr _ ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

After posting the video, regular viewer Mike Fliss asked is there is a way to build the formulas that would constantly update to show the statistics for whatever range is selected. This would require a Worksheet_SelectionChange macro that would constantly update a named range to match the selection. While this is a cool bit of trickery, it forces a macro to run every time you move the cell pointer, and that is going to constantly clear the UnDo stack. So, if you use this macro, it has to be added to every worksheet code pane where you want it to work, and you will have to live without Undo on those worksheets.

First, from Excel, Right-Click on a sheet tab and choose View Code. Then, paste this code in.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Selection.Name = "SelectedData" End Sub

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