Ich liebe benutzerdefinierte Listen in Excel. Sie eignen sich hervorragend für den Füllgriff und zum Sortieren von Daten in einer anderen Reihenfolge. Benutzerdefinierte Listen sollten 254 Elemente zulassen. Aus irgendeinem Grund stößt ein Leser jedoch auf eine Situation, in der Excel nur die ersten 38 Elemente behält! Wir werden diesem Rätsel auf den Grund gehen.
Schau Video
- Don möchte nach einer benutzerdefinierten Liste sortieren, die numerisch ist!
- Ob das funktioniert? Es scheint zu funktionieren!
- Sie können jedoch keine numerischen Zellen in das Dialogfeld für benutzerdefinierte Listen importieren.
- Versuchen Sie also, Zahlen in das Dialogfeld Benutzerdefinierte Liste einzugeben. Beim Tippen stößt man auf eine dumme Beschränkung von 255 Zeichen.
- WTH ist die Grenze? 254 Artikel? Aha - 254 Elemente, aber weniger als 2000 Zeichen, wenn Sie das unsichtbare Komma zwischen den einzelnen Elementen einfügen
- Habe etwas Textmathematik mit
=SUM(LEN())und Strg + Umschalt + Eingabetaste undLEN(TEXTJOIN(",",True,Range)) - Problemumgehung mit ABS zum Sortieren in diesem speziellen Fall für Don
- Aber die beste Lösung… das, was Don tun muss:
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2098: Benutzerdefinierte Listenbeschränkungen.
Dies ist nur bizarr, wenn Don S Mac 2011 verwendet, richtig, also verwenden wir nicht einmal die echte Version von Excel. Wir verwenden die gefälschte Version von Excel und versuchen, nach einer benutzerdefinierten Liste zu sortieren, akzeptieren jedoch nur die ersten 38 Elemente in der Liste. Und ich weiß, dass das falsch ist, weil Excel bis zu 254 Elemente in der Liste verarbeiten kann, oder zumindest dachte ich das. In Ordnung, und Don hat den Spielernamen, die Anzahl der Siege und dann den Rand, wie weit von der Punktzahl entfernt.
Die richtige Reihenfolge ist also, dass die perfekte Punktzahl 0 ist und dann über 1, unter 1 und über 2, unter 2, über 3, unter 3 und so weiter. Und Don versucht, die Margin-Spalte nach dieser benutzerdefinierten Liste zu sortieren. Nun, das habe ich noch nie versucht, aber hey, es sollte einfach sein. Also hier drüben Richtige Reihenfolge: 0, 1 und dann wird die Formel minus dem Wert kurz vor uns sein und dann = die Zahl 2 über +1. Okay, jetzt, wo ich diese beiden Formeln habe, sollte ich in der Lage sein, durch 201 zu fahren, zu weit dort, aber das ist okay. Und wir sollten die genaue Reihenfolge haben, die wir brauchen, bis auf 99 und -99. Es gibt also unsere perfekten Antworten. Ich werde das kopieren, also Strg + C kopieren und dann als Werte einfügen. Fügen Sie diese Werte so ein. Okay, also sollte ich in der Lage sein, eine benutzerdefinierte Liste einzurichten, die das erledigt, oder? Kein Problem.Also gehen wir zu Datei, Optionen, Erweitert, scrollen 83% nach unten, wählen Benutzerdefinierte Listen bearbeiten und importieren unsere Liste. Was? Zellen ohne einfachen Text wurden ignoriert. Sie dürfen keine benutzerdefinierte Liste voller Zahlen haben? Aber Don sagt, das funktioniert für die ersten 38. Was ist damit los? Nun, ich habe eine Weile nicht darüber nachgedacht. Mir wurde klar, dass Don nicht versucht haben musste zu importieren; Er muss gerade diese Zahlen in das Dialogfeld eingegeben haben.Mir wurde klar, dass Don nicht versucht haben musste zu importieren; Er muss gerade diese Zahlen in das Dialogfeld eingegeben haben.Mir wurde klar, dass Don nicht versucht haben musste zu importieren; Er muss gerade diese Zahlen in das Dialogfeld eingegeben haben.
Also hier ist was ich tun werde. Ich mache Strg + C, um all diese zu kopieren. Ich gehe zu Notepad und füge in Notepad wie folgt Strg + V ein und wähle dann alles aus: Bearbeiten, Alles auswählen und Strg + C, zurück zu Excel, Datei, Optionen, Erweitert, 83% ganz unten, Benutzerdefinierte Listen bearbeiten und ich werde diese Liste hier wie Strg + V eingeben. Okay, und das alles funktioniert, aber wir haben auf Hinzufügen geklickt. Die maximale Länge für die benutzerdefinierte Liste wurde überschritten. Es werden nur die ersten 255 Zeichen gespeichert. Und wenn Sie sich das ansehen, erstellen sie sicher eine benutzerdefinierte Liste, die nur in 38, -38, 39 und dann in BAM unterteilt ist! Die letzten 3, richtig?
Das ist so bizarr. Sie lassen mich tatsächlich eine benutzerdefinierte Liste mit Zahlen erstellen, aber sie erlauben mir nicht, 255 zu erhalten. Ich meine, es funktioniert. Es funktioniert und dann, wenn wir tatsächlich versuchen, hier zu sortieren; Also sagen wir Daten, Sortieren und Sortieren in dieser benutzerdefinierten Liste, die nur bis 39 reicht. Klicken Sie auf OK, klicken Sie auf OK. Nun, wenn es in der Liste ist, sortiert es richtig. Okay, also die positiven +6 zeigen vor den -6; Aber wenn wir erst einmal über 39 hinaus sind, werden nur die Sequenzen sortiert, die nicht in der Liste enthalten sind, also wird hier von klein nach groß gewechselt. Jemand, der um 67 Punkte verfehlt wurde, ist besser als jemand, der um +42 Punkte verfehlt wurde. Es ist einfach total verrückt.
Okay, und was ist damit los, dass es nur 38 werden? Nun, sicher gibt es jede andere Zahl, also wird es bis in die 30er Jahre gehen. Wir werden es dort mögen, oder? Oder dort einer dieser beiden. Was ist das denn? Das sind insgesamt 78 Artikel. Und hey, ich weiß, dass sie es erlaubt haben - 250 Foren zu haben, weil ich in meinen Seminaren die ganze Zeit über Kunden spreche, okay? Sie können 250 Foren haben, lassen Sie sich von mir zeigen. Also Punkt 1, und natürlich können wir den Füllpunkt dafür verwenden. Ich werde so auf 254 runterziehen. Nun, das sind keine Formeln, also sollten wir in der Lage sein, Datei, Optionen, Erweitern, Benutzerdefinierte Listen bearbeiten und diese Liste zu importieren, okay? Da ist es, BAM! Keine Probleme, keine Fehlermeldung. Alles ist großartig, alles ist - es ist nicht großartig. Es geht nur zu Punkt 234. Warten Sie, ich weiß, Sie können 254 haben.Warum hört es bei 234 auf? Das ist bizarr, das ist bizarr. Was ist damit?
Hier wissen wir also, dass es genau dort nur um Punkt 234 geht. Okay, als wir die Elemente in die Liste eingaben, gab es einige Zeichen. Da war die Grenze. Ich frage mich also, ob es hier eine bestimmte Anzahl von Zeichen gibt, die das Limit ist = SUMME (LEN von dieser ganzen Menge, drücken Sie STRG + UMSCHALTTASTE, und es sind 1764 Zeichen - 234 Elemente. Und ich weiß, dass Sie 254 haben können, habe ich habe das schon mal gemacht.
Und probieren wir etwas Verrückteres. Okay, lass es uns versuchen. Lassen Sie uns versuchen, anstelle von Artikel versuchen wir etwas länger. Also 10 Zeichen pro Leerzeichen und dann Nummer 1, wir gehen 254 Zeilen runter. Und wir werden versuchen, diese Liste zu importieren: Also Datei, Optionen, Erweitert, Benutzerdefinierte Listen bearbeiten, wir werden diese Liste importieren. Keine Fehlermeldung. Scheint, als hätte es funktioniert, aber es geht nur bis 140. Was zum Teufel ist damit los? Was ist die Grenze? Ich dachte, es könnte 254 sein. Also mal sehen, wie viele Zeichen wir haben, wenn wir auf 140 kommen. Okay, also lassen wir alles andere danach und tatsächlich werde ich hier zu dieser Formel kommen und genau dieselbe Formel kopieren Über. Okay, nein.
Zu diesem Zeitpunkt bin ich ziemlich verärgert über das Excel-Team. Was ist los, hier 1764 und hier 1852. Hey, Microsoft, was ist die Grenze? Was genau ist die Grenze? Ah, aber hier ist die Sache. Sie müssen dies als eine Reihe von begrenzten Zeichenfolgen speichern, okay? Also nehmen sie alle Gegenstände und fügen nach jedem ein Komma hinzu. Okay, da wir hier Office 365 haben, können wir den neuen Text Join verwenden, also = TEXTJOIN von allen mit einem Komma dazwischen. Ich weiß nicht, ob es wirklich ein Komma ist oder nicht. Ignorieren Sie das, dann True, Komma und diese Elemente. Also, wir verstehen das. Und eigentlich möchte ich nur die Länge dieser ganzen Sache wissen. Die Länge ist also 1997 und wenn ich hier das Gleiche mache, 1991. Oh! Das Limit muss also eindeutig 2000 Zeichen betragen, einschließlich eines unsichtbaren Kommas zwischen den einzelnen Elementen.
Das ist alles ziemlich bizarr. Okay, also dachte ich immer, es wären 254 Gegenstände, es sind nicht 254 Gegenstände. Es sind 254 Gegenstände, vorausgesetzt, es sind weniger als 2.000 Zeichen, vorausgesetzt, die Gegenstände sind nicht zu lang. Okay, um meine Theorie zu testen, verwenden wir einfach so ein Leerzeichen 1 und wir greifen nach dem Füllgriff und ziehen. Diese sollten wirklich nett und kurz sein, weil - und wir gehen zu 255, 254 über. Gehen wir zu 255, um sie zu testen.
Okay, also jetzt damit, wenn ich nach der Länge des Text-Joins frage, 1421. Überhaupt kein Problem. Wählen Sie also das Ganze aus und klicken Sie auf Datei, Optionen, Erweitert, ganz nach unten scrollen, Benutzerdefinierte Listen bearbeiten und auf Importieren. In Ordnung, und schalten Sie es bis auf 254 aus. In Ordnung, es sind also 254 Elemente, vorausgesetzt, es sind weniger als 2.000 Zeichen, einschließlich eines unsichtbaren Kommas, nachdem jedes Element funktioniert.
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.
Na siehst du. Don, eine der verrückteren Fragen, die ich jemals in Excel gehört habe, und wir haben mindestens 2 entdeckt - Nun, definitiv 1 Fehler in Excel, dass das Dialogfeld "Benutzerdefinierte Liste" nicht mehr als 255 Zeichen zulässt. Und dann diese bizarre Sache, dass die anscheinend eine benutzerdefinierte Liste nach Zahlen sortiert, aber Sie keine Zahlen importieren können. Okay, also werde ich diesen Fehler Nummer 2 nennen. Und dann diese Problemumgehung hier, okay?
Also hey, ich möchte Don dafür danken, dass er die Frage eingeschickt hat, und wenn Sie so lange rumhängen, möchte ich Ihnen dafür danken, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2098.xlsm
