Μου αρέσουν οι προσαρμοσμένες λίστες στο Excel. Είναι ιδανικά για τη λαβή πλήρωσης και για ταξινόμηση δεδομένων σε διαφορετική ακολουθία. Οι προσαρμοσμένες λίστες πρέπει να επιτρέπουν 254 στοιχεία. Αλλά για κάποιο λόγο, ένας αναγνώστης αντιμετωπίζει μια κατάσταση όπου το Excel διατηρεί μόνο τα πρώτα 38 στοιχεία! Θα φτάσουμε στο κάτω μέρος αυτού του μυστηρίου.
Δες το βίντεο
- Ο Ντον θέλει να ταξινομήσει μια προσαρμοσμένη λίστα που είναι αριθμητική!
- Θα λειτουργήσει; Φαίνεται να λειτουργεί!
- Αλλά δεν μπορείτε να εισαγάγετε αριθμητικά κελιά στο πλαίσιο διαλόγου προσαρμοσμένης λίστας.
- Έτσι, προσπαθήστε να πληκτρολογήσετε αριθμούς στο πλαίσιο διαλόγου Προσαρμοσμένη λίστα…. Χτυπάτε με ένα ανόητο όριο 255 χαρακτήρων κατά την πληκτρολόγηση.
- WTH είναι το όριο; 254 αντικείμενα; Aha - 254 στοιχεία, αλλά λιγότεροι από 2000 χαρακτήρες όταν προσθέτετε το αόρατο κόμμα μεταξύ κάθε στοιχείου
- Κάναμε μαθηματικά κειμένου με
=SUM(LEN())και Ctrl + Shift + Enter καιLEN(TEXTJOIN(",",True,Range)) - Λύση με ABS για ταξινόμηση σε αυτή τη συγκεκριμένη περίπτωση για τον Don
- Αλλά η καλύτερη λύση … το πράγμα που πρέπει να κάνει ο Ντον:
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2098: Custom Limits Limits.
Αυτό είναι παράξενο όταν το Don S, χρησιμοποιώντας το Mac 2011, σωστά, οπότε δεν χρησιμοποιούμε καν την πραγματική έκδοση του Excel. Χρησιμοποιούμε την ψεύτικη έκδοση του Excel, προσπαθούμε να ταξινομήσουμε κατά μια προσαρμοσμένη λίστα, αλλά δέχεται μόνο τα πρώτα 38 στοιχεία στη λίστα. Και ξέρω ότι είναι λάθος επειδή το Excel μπορεί να χειριστεί έως και 254 στοιχεία στη λίστα ή τουλάχιστον αυτό σκέφτηκα. Εντάξει, και ο Ντον έχει όνομα παίκτη, # νίκες και στη συνέχεια Margin, όπως πόσο μακριά ήταν το σκορ τότε.
Έτσι, η σωστή ακολουθία είναι, το τέλειο σκορ είναι 0 και στη συνέχεια πάνω από 1, κάτω από 1, και πάνω από 2, κάτω από 2, πάνω από 3, κάτω από 3 και ούτω καθεξής. Και ο Don προσπαθεί να ταξινομήσει τη στήλη Περιθώριο από αυτήν την προσαρμοσμένη λίστα. Τώρα, δεν το έχω δοκιμάσει ποτέ, αλλά, πρέπει να είναι εύκολο να γίνει. Εδώ λοιπόν Proper Sequence: 0, 1 και στη συνέχεια ο τύπος θα είναι μείον την τιμή λίγο πριν από εμάς και μετά = ο αριθμός 2 παραπάνω +1. Εντάξει, τώρα που έχω αυτές τις δύο φόρμουλες, θα έπρεπε να μπορέσω να φτάσω μέχρι το 201, πολύ μακριά, αλλά αυτό είναι εντάξει. Και πρέπει να έχουμε την ακριβή ακολουθία που χρειαζόμαστε έως τα 99 και -99. Υπάρχει λοιπόν το τέλειο σύνολο απαντήσεων. Θα το αντιγράψω έτσι ώστε Ctrl + C να το αντιγράψω και μετά να επικολλήσω ως τιμές. Επικολλήστε αυτές τις τιμές έτσι. Εντάξει, οπότε θα πρέπει να μπορώ να δημιουργήσω μια προσαρμοσμένη λίστα που θα το χειριστεί, έτσι; Κανένα πρόβλημα.Έτσι πηγαίνουμε στο Αρχείο, Επιλογές, Για προχωρημένους, μετακινηθείτε στο 83% μέχρι κάτω, επιλέξτε Επεξεργασία προσαρμοσμένων λιστών και θα εισαγάγουμε τη λίστα μας. Τι? Τα κελιά χωρίς απλό κείμενο αγνοήθηκαν. Δεν επιτρέπεται να έχετε μια προσαρμοσμένη λίστα με αριθμούς; Αλλά ο Ντον λέει ότι αυτό λειτουργεί για τα πρώτα 38. Τι συμβαίνει με αυτό; Λοιπόν, δεν το σκέφτηκα για λίγο. Συνειδητοποίησα ότι ο Don δεν θα προσπαθούσε να εισαγάγει. Πρέπει απλά να πληκτρολογούσε αυτούς τους αριθμούς στο πλαίσιο διαλόγου.Συνειδητοποίησα ότι ο Don δεν θα προσπαθούσε να εισαγάγει. Πρέπει απλά να πληκτρολογούσε αυτούς τους αριθμούς στο πλαίσιο διαλόγου.Συνειδητοποίησα ότι ο Don δεν θα προσπαθούσε να εισαγάγει. Πρέπει απλά να πληκτρολογούσε αυτούς τους αριθμούς στο πλαίσιο διαλόγου.
Λοιπόν, εδώ θα κάνω. Θα κάνω Ctrl + C για να αντιγράψω όλα αυτά, θα πάω στο Σημειωματάριο και Επικόλληση στο Σημειωματάριο όπως αυτό το Ctrl + V και έπειτα επιλέξτε τα πάντα: Επεξεργασία, Επιλογή όλων και Ctrl + C, επιστρέψτε στο Excel, File, Options, Advanced, 83% μέχρι κάτω, Επεξεργασία προσαρμοσμένων λιστών και θα πληκτρολογήσω αυτήν τη λίστα εδώ όπως είναι Ctrl + V. Εντάξει, και ότι όλα λειτουργούν, αλλά κάναμε κλικ στην επιλογή Προσθήκη, έχει ξεπεραστεί το μέγιστο μήκος για την προσαρμοσμένη λίστα. Θα αποθηκευτούν μόνο οι πρώτοι 255 χαρακτήρες. Και όταν το κοιτάξετε αυτό, αρκετά σίγουρα, δημιουργούν μια προσαρμοσμένη λίστα που μειώνεται μόνο σε 38, -38, 39 και στη συνέχεια BAM! Τα τελευταία 3, σωστά;
Αυτό είναι τόσο περίεργο. Στην πραγματικότητα μου επιτρέπουν να δημιουργήσω μια προσαρμοσμένη λίστα με αριθμούς, αλλά δεν μου επιτρέπουν να λάβω 255. Εννοώ, λειτουργεί. Λειτουργεί και, στη συνέχεια, αν προσπαθήσουμε πραγματικά και ταξινομήσουμε εδώ? οπότε θα πούμε Δεδομένα, Ταξινόμηση και ταξινόμηση σε αυτήν την Προσαρμοσμένη Λίστα, αυτή που πηγαίνει μόνο σε 39, κάντε κλικ στο OK, κάντε κλικ στο OK. Λοιπόν, αν είναι στη λίστα ταξινομείται σωστά. Εντάξει, οπότε το θετικό +6 εμφανίζεται πριν από το -6. αλλά όταν φτάσουμε σε οτιδήποτε πέρα από το 39, απλά πρόκειται να ταξινομήσουμε τις ακολουθίες που δεν περιλαμβάνονται στη λίστα, οπότε πηγαίνει από το μικρότερο στο μεγαλύτερο εδώ. Άρα, κάποιος που έχασε 67 πόντους είναι καλύτερο από κάποιον που έχασε +42 πόντους. Είναι απλά εντελώς βρώμικο.
Εντάξει, και τι συμβαίνει με αυτό που φτάνει μόνο στα 38; Τώρα, σίγουρα υπάρχει κάθε άλλος αριθμός, έτσι, ξέρετε, υποθέτω ότι θα πέσει στα 30. Μας αρέσει εκεί, έτσι; Ή εκεί, ένα από αυτά τα δύο. Λοιπόν, τι είναι αυτό; Αυτό είναι συνολικά 78 αντικείμενα. Και γεια, ξέρω ότι επέτρεψαν - έχουν 250 φόρουμ γιατί μιλάω για πελάτες όλη την ώρα στα σεμινάρια μου, εντάξει; Μπορείτε να έχετε 250 φόρουμ, επιτρέψτε μου να σας δείξω. Έτσι, το στοιχείο 1, και φυσικά μπορούμε να χρησιμοποιήσουμε το στοιχείο πλήρωσης για αυτό. Θα μεταφερθώ στο 254, έτσι. Τώρα αυτοί δεν είναι τύποι, οπότε θα πρέπει να μπορούμε να κάνουμε File, Options, Advance, να συνεχίσουμε να επεξεργαζόμαστε προσαρμοσμένες λίστες και θα εισαγάγουμε αυτήν τη λίστα, εντάξει; Ορίστε, BAM! Κανένα πρόβλημα, κανένα μήνυμα σφάλματος. Όλα είναι υπέροχα, όλα είναι - Δεν είναι υπέροχα. Πηγαίνει μόνο στο σημείο 234. Περιμένετε, ξέρω ότι μπορείτε να έχετε 254.Γιατί σταματά στο 234; Αυτό είναι παράξενο, είναι περίεργο. Τι συμβαίνει με αυτό?
Εδώ, γνωρίζουμε ότι πηγαίνει μόνο στο σημείο 234 εκεί. Εντάξει τώρα, όταν πληκτρολογούσαμε τα στοιχεία στη λίστα, υπάρχει κάποιος αριθμός χαρακτήρων. Υπήρχε το όριο. Αναρωτιέμαι λοιπόν αν υπάρχει κάποιος αριθμός χαρακτήρων που είναι το όριο εδώ = SUM (LEN αυτού του πλήθους, πατήστε CTRL + SHIFT ENTER και είναι 1764 χαρακτήρες - 234 αντικείμενα. Και ξέρω ότι μπορείτε να έχετε 254, έχω το έκανα πριν.
Και ας δοκιμάσουμε κάτι πιο τρελό. Εντάξει, ας το δοκιμάσουμε. Ας δοκιμάσουμε αντί για αντικείμενο, ας δοκιμάσουμε κάτι περισσότερο. Έτσι, 10 χαρακτήρες ένα SPACE και στη συνέχεια τον αριθμό 1, θα κατεβούμε 254 σειρές. Και θα προσπαθήσουμε να εισαγάγουμε αυτήν τη λίστα: έτσι Αρχείο, Επιλογές, Για προχωρημένους, Επεξεργασία προσαρμοσμένων λιστών, θα εισαγάγουμε αυτήν τη λίστα. Δεν υπάρχει μήνυμα σφάλματος. Φαίνεται ότι λειτούργησε αλλά μειώνεται μόνο στα 140. Τι συμβαίνει με αυτό; Ποιο είναι το όριο; Νόμιζα ότι θα μπορούσε να είναι 254. Λοιπόν, ας δούμε, πόσους χαρακτήρες έχουμε αν φτάσουμε στους 140. Εντάξει, οπότε ας αφήσουμε τα υπόλοιπα μετά από αυτό και στην πραγματικότητα θα έρθω εδώ σε αυτόν τον τύπο και θα αντιγράψω τον ίδιο ακριβώς τύπο πάνω από. Εντάξει, όχι.
Σε αυτό το σημείο, είμαι πολύ εξοργισμένος με την ομάδα του Excel. Τι συμβαίνει, εδώ 1764 και εδώ 1852. Γεια, Microsoft, ποιο είναι το όριο; Ποιο είναι ακριβώς το όριο; Α, αλλά εδώ είναι το πράγμα. Πρέπει να το αποθηκεύουν ως μια σειρά οριοθετημένων χορδών, εντάξει; Λοιπόν, παίρνουν όλα τα αντικείμενα και μετά προσθέτουν κόμμα μετά από κάθε ένα. Εντάξει, οπότε εδώ, επειδή έχουμε το Office 365, μπορούμε να χρησιμοποιήσουμε τη νέα Συμμετοχή Κειμένου, έτσι = TEXTJOIN όλων αυτών με κόμμα στο μεταξύ. Δεν ξέρω αν είναι πραγματικά κόμμα ή όχι. Αγνοήστε ότι, στη συνέχεια, True, κόμμα και αυτά τα στοιχεία. Λοιπόν, το καταλαβαίνουμε. Και στην πραγματικότητα θέλω απλώς να μάθω το μήκος σε όλο αυτό το πράγμα. Έτσι το μήκος είναι το 1997 και όταν κάνω το ίδιο πράγμα εδώ, 1991. Ω! Έτσι, σαφώς το όριο πρέπει να είναι 2000 χαρακτήρες, συμπεριλαμβανομένου ενός αόρατου κόμμα μεταξύ κάθε στοιχείου.
Όλα αυτά είναι πολύ περίεργα. Εντάξει, έτσι πάντα πίστευα ότι ήταν 254 αντικείμενα, δεν ήταν 254 αντικείμενα. Είναι 254 αντικείμενα, υπό την προϋπόθεση ότι είναι λιγότεροι από 2.000 χαρακτήρες, υπό την προϋπόθεση ότι τα στοιχεία δεν είναι πολύ μεγάλα. Εντάξει, οπότε απλώς για να δοκιμάσουμε τη θεωρία μου, ας χρησιμοποιήσουμε ένα διάστημα 1 σαν αυτό και θα πιάσουμε τη λαβή πλήρωσης και θα σύρουμε Αυτά πρέπει να είναι πολύ ωραία και σύντομα γιατί - Και θα πάμε στα 255, 254. Ας πάμε στο 255 για να το δοκιμάσουμε.
Εντάξει, λοιπόν τώρα με αυτό, αν ζητήσω το Μήκος του κειμένου Συμμετοχή, 1421. Κανένα πρόβλημα. Επιλέξτε λοιπόν το σύνολο και επιλέξτε Αρχείο, Επιλογές, Για προχωρημένους, μετακινηθείτε μέχρι κάτω προς τα κάτω, Επεξεργασία προσαρμοσμένων λιστών, κάντε κλικ στο Εισαγωγή. Εντάξει, και απενεργοποιήστε μέχρι το 254. Εντάξει, οπότε είναι 254 αντικείμενα υπό την προϋπόθεση ότι είναι λιγότεροι από 2.000 χαρακτήρες, συμπεριλαμβανομένου ενός αόρατου κόμμα, αφού κάθε στοιχείο λειτουργεί.
You know, so - but back to Don’s problem here. It sure is annoying that the Dialog Box, if we just go in and start typing things in the Dialog Box instead of having a 2,000 character element, it has a 255 character on it. Alright, so Don has no way to type this thing in and when we try and Import numbers, it refuses to Import the numbers. It says no deal. Anything that's not plain text is not going to work, alright?
So, the one thing I suggested to Don is an alternate solution. I say, hey, let's just come out here and add a Helper Column and this Helper Column is going to be the - if the Absolute Value of that number, alright. And we'll double click to shoot that down and then what you’re going to do is you're just going to sort Descending by the Absolute Value, a setting by the Absolute Value, alright. And then the 4, 6 and then -6, alright, these are all just get sorted together, you know. So it's not bad, I guess what you could really do is you could sort by the Helper and then Add a Level and then Sort by the Margin, descending Largest to Smallest, click OK and that will get what Don is looking for. So, all of the +6 will show up before the -6, and then 8 and then -11, alright. You know, but this is a hassle. Like, hey, Microsoft. Why do we have to go to all this hassle? Why would you let us type 2,000 characters into the Dialog Box or even better yet, since it apparently works to have numbers in the Custom List, I mean it is working here, why won't you let us import it?
Alright now, here is the workaround. And I'm sorry that took this freaking long to get to the end but this is what I had to go through to figure all this out. So, I'm going to add this Workaround Column, alright. And this Workaround Column is going to be the Text, the text of that number in “”, or “0”) like that. And we're going to copy that down. Alright, so now we're taking these numbers here - see the numbers are right justified and we are left justifying it by using the Text Function. Unfortunately though, you can't use formulas in the Custom List. If I would come here to File, Options, Advanced, go down to Edit Custom Lists and we'll Import that - no! Cells without simple texts were ignored. Okay, fine. Getting really annoyed now, right?
Now, what I'm going to do is I want to select the Workaround Column. I'm going to use the Home tab and I'm going to copy it and then I'm going to paste and paste Values. If you don't do this step, it's not going to work. And here's how to prove to yourself that it works. Alright, if those are really numbers, when you do the SUM function it's going to give you an answer like this. That's going to be 5, alright? But if I do the SUM of the Workaround Column, I'm going to get O, alright. And the reason is, is the SUM can't sum texts. Alright, so these are real numbers, these are real texts. You have to get it to be real text. Once you have it as real text, select the whole thing. Go to File, Options, Advanced, Edit Custom Lists. We'll get rid of this old Custom List here that we don't need. We'll get rid of this list that we don't need. We'll get rid of this list that we don't need. We'll even get rid of this list that only goes down to 38 and 39; and then finally, we'll Import the whole list. And these whole lists goes from 0 all the way down to 99 and -99 and presumably store it as text, alright? But here's the cool, awesome, amazing thing. Let's just come back up and we'll sort this data by name: Data, A-Z, and now everything is just completely random there. And then, we will Sort by Margin and the order is going to be a Custom List. The custom list is going to be this sequence, click OK and click OK. And it works: the 4, the 6s, the -6, the 8, the -11 and so on.
Now, the beautiful thing about this is once you get it set up, alright, it's a hassle to put these numbers over here in the- off to the side, change them to text, Copy, Paste Special Values and then Import that list. But the beautiful thing is, once that's imported then forevermore on this computer we're going to be able to sort correctly by margin. So Don, geez! I think that it's worth it to go through that whole thing.
617 Excel mystery solved. Now, this is a new mystery why this Custom List wouldn't work but this book is full of stuff like this video, just the bizarre, the arcane and so on. Click that “i” on the top right-hand corner to buy the book.
Alright, Don wants to sort by Custom List that is numeric, will it work? It seems to work. It seems to work for the first 38 numbers but you can't import numeric cells into the Custom List Dialog Box. Ah, so try and type the custom numbers or actually, I guess I pasted to Notepad and then from Notepad I paste it. But then there's this crazy 255 character limit when typing. That must be left over from Excel 2003, right? Because the limit is more than that now. You can have 254 items. Is it 254 items? Ah, it’s 254 items less than 2000 characters when you have the invisible column, comma between each item.
Now we did some Math here, text math with =SUM(LEN) then CTRL+SHIFT ENTER and then the LEN(TEXTJOIN). If you have Office 365 and are on the latest version - even came up with this workaround with the Absolute Value for sorting which will help in this particular case for Don. But I think what Don needs to do, the best workaround, and this is what I tell them to do on the YouTube video. Put those numbers: 0, 1, -1, in A1:A200. And then in the column next to that, =TEXT(A1, “0”). That's going to be converted to text. Copy that formula all the way down and then select that range of formulas Copy and then after you Copy go to the Paste dropdown. So Copy, Paste and Paste as Values. And if you're successful, when you do the SUM of that whole thing, it’s going to sum to a number that's not or it’s going to sum to 0. It's going to sum to 0. Be careful. Don't choose all of your numbers because your numbers do happen to sum to 0. Now the B contains text numbers you can import because of Custom Lists.
Λοιπόν, πήγαινε. Don, μία από τις πιο τρελές ερωτήσεις που έχω ακούσει ποτέ στο Excel και έχουμε ανακαλύψει τουλάχιστον 2 - Λοιπόν, σίγουρα 1 σφάλμα στο Excel ότι το πλαίσιο διαλόγου Custom List δεν θα επιτρέπει περισσότερους από 255 χαρακτήρες. Και τότε αυτό το παράξενο πράγμα που προφανώς θα ταξινομήσει μια προσαρμοσμένη λίστα με αριθμούς, αλλά δεν θα σας επιτρέψει να εισαγάγετε αριθμούς. Εντάξει, οπότε πρόκειται να καλέσω αυτό το σφάλμα νούμερο 2. Και έπειτα αυτός ο τρόπος αντιμετώπισης εδώ, καλά
Γεια σου, θέλω να ευχαριστήσω τον Ντον για την αποστολή της ερώτησης και αν μείνεις εδώ και τόσο καιρό, θέλω να σε ευχαριστήσω που σταμάτησες. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2098.xlsm
