Uwielbiam listy niestandardowe w programie Excel. Doskonale nadają się do uchwytu wypełniania i do sortowania danych w innej kolejności. Listy niestandardowe powinny dopuszczać 254 pozycje. Ale z jakiegoś powodu czytelnik napotyka sytuację, w której Excel przechowuje tylko pierwsze 38 pozycji! Dojdziemy do sedna tej tajemnicy.
Obejrzyj wideo
- Don chce sortować według niestandardowej listy liczbowej!
- Czy to zadziała? Wydaje się, że działa!
- Nie można jednak importować komórek numerycznych do okna dialogowego listy niestandardowej.
- Spróbuj więc wpisać liczby w oknie dialogowym Lista niestandardowa…. Podczas pisania uderza cię głupi limit 255 znaków.
- WTH to limit? 254 elementów? Aha - 254 elementy, ale mniej niż 2000 znaków, jeśli dodasz niewidoczny przecinek między każdym elementem
- Zrobiłem trochę matematyki tekstowej za pomocą
=SUM(LEN())Ctrl + Shift + Enter iLEN(TEXTJOIN(",",True,Range)) - Obejście problemu z ABS do sortowania w tym konkretnym przypadku dla Dona
- Ale najlepsze obejście… rzecz, którą Don musi zrobić:
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2098: Limity listy niestandardowej.
To po prostu dziwne, gdy Don S używa Mac 2011, prawda, więc nie używamy nawet prawdziwej wersji programu Excel. Używamy fałszywej wersji programu Excel, próbujemy sortować według listy niestandardowej, ale akceptujemy tylko pierwsze 38 pozycji z listy. Wiem, że to źle, ponieważ Excel może obsłużyć do 254 pozycji na liście, a przynajmniej tak myślałem. W porządku, a Don ma imię gracza, liczbę zwycięstw, a następnie margines, tak jak daleko od wyniku.
Tak więc właściwa sekwencja jest taka, że doskonały wynik to 0, a następnie wynik poniżej 1, poniżej 1 i powyżej 2, poniżej 2, powyżej 3, poniżej 3 i tak dalej. Don próbuje posortować kolumnę Margines według tej listy niestandardowej. Nigdy tego nie próbowałem, ale hej, powinno być łatwe. Więc tutaj Sekwencja właściwa: 0, 1, a następnie wzór będzie minus wartość tuż przed nami, a następnie = liczba 2 powyżej +1. W porządku, teraz, gdy mam te dwie formuły, powinienem być w stanie przejść przez 201, za daleko, ale to jest w porządku. Powinniśmy mieć dokładną sekwencję, której potrzebujemy, do 99 i -99. Mamy więc nasz doskonały zestaw odpowiedzi. Mam zamiar to skopiować, więc Ctrl + C, aby skopiować, a następnie Wklej jako wartości. Wklej te wartości w ten sposób. W porządku, więc powinienem być w stanie skonfigurować listę niestandardową, która to załatwi, prawda? Nie ma problemu.Przechodzimy więc do Plik, Opcje, Zaawansowane, przewijamy 83% w dół, wybieramy Edytuj listy niestandardowe i zaimportujemy naszą listę. Co? Komórki bez prostego tekstu zostały zignorowane. Nie możesz mieć listy niestandardowej pełnej liczb? Ale Don mówi, że to działa dla pierwszych 38. O co chodzi? Cóż, nie myśl o tym przez chwilę. Zrozumiałem, że Don nie musiał próbować importować; musiał właśnie wpisywać te liczby w oknie dialogowym.Zrozumiałem, że Don nie musiał próbować importować; musiał właśnie wpisywać te liczby w oknie dialogowym.Zrozumiałem, że Don nie musiał próbować importować; musiał właśnie wpisywać te liczby w oknie dialogowym.
Oto, co zamierzam zrobić. Zamierzam zrobić Ctrl + C, aby skopiować je wszystkie, przejdę do Notatnika i wklejam w Notatniku w ten sposób Ctrl + V, a następnie zaznaczam wszystko: Edytuj, Zaznacz wszystko i Ctrl + C, wróć do Excel, plik, opcje, zaawansowane, 83% do końca, edytuj listy niestandardowe i mam zamiar wpisać tę listę tutaj, jakby była to Ctrl + V. W porządku i to wszystko działa, ale kliknęliśmy Dodaj, maksymalna długość listy niestandardowej została przekroczona. Zostanie zapisane tylko pierwszych 255 znaków. A kiedy na to spojrzysz, z pewnością tworzą listę niestandardową, która obejmuje tylko 38, -38, 39, a następnie BAM! Ostatnie 3, prawda?
Więc to jest takie dziwne. W rzeczywistości pozwalają mi utworzyć listę niestandardową z numerami, ale nie pozwalają mi uzyskać 255. To znaczy, działa. To działa, a jeśli rzeczywiście spróbujemy posortować tutaj; więc powiemy Dane, sortowanie i sortowanie na tej liście niestandardowej, tej, która prowadzi tylko do 39 kliknij OK, kliknij OK. Cóż, jeśli jest na liście, sortuje poprawnie. W porządku, więc dodatnie +6 pokazuje przed -6; ale kiedy już dojdziemy do czegokolwiek powyżej 39, to po prostu posortuje w sekwencjach, których nie ma na liście, więc tutaj przechodzi od najmniejszej do największej. Tak więc ktoś, kto stracił 67 punktów, jest lepszy niż ktoś, kto stracił +42 punkty. To jest po prostu kompletnie szalone.
W porządku, a o co chodzi z tym idącym tylko do 38? Teraz, na pewno jest co druga liczba, więc wiesz, myślę, że spadnie do 30 lat. Zaczynamy się tam polubić, prawda? Albo jeden z tych dwóch. Więc co to jest? To łącznie 78 pozycji. I hej, wiem, że pozwolili - mają 250 forów, ponieważ cały czas mówię o klientach na moich seminariach, dobrze? Możesz mieć 250 forów, pokażę ci. Tak więc punkt 1 i oczywiście możemy do tego użyć elementu wypełnienia. Przeciągnę do 254, o tak. Teraz to nie są formuły, więc powinniśmy być w stanie zrobić Plik, Opcje, Przenieś, przejść do Edytuj listy niestandardowe i zaimportujemy tę listę, dobrze? Oto jest, BAM! Żadnych problemów, żadnego komunikatu o błędzie. Wszystko jest super, wszystko jest - to nie jest świetne. To idzie tylko do pozycji 234. Czekaj, wiem, że możesz mieć 254.Dlaczego zatrzymuje się na 234? To dziwne, to dziwne. O co w tym chodzi?
Więc tutaj wiemy, że sprowadza się to tylko do pozycji 234. Okej, kiedy wpisywaliśmy pozycje na liście, jest pewna liczba znaków. Był limit. Więc zastanawiam się, czy jest jakaś liczba znaków, która jest tutaj limitem = SUMA (LEN całej tej masy, naciśnij CTRL + SHIFT ENTER, a to 1764 znaki - 234 elementy. Wiem, że możesz mieć 254, mam zrobiłem to wcześniej.
Spróbujmy czegoś bardziej szalonego. W porządku, spróbujmy tego. Spróbujmy zamiast przedmiotu spróbujmy czegoś dłużej. Więc 10 znaków SPACJA, a następnie numer 1, zejdziemy w dół o 254 wiersze. Spróbujemy zaimportować tę listę: więc Plik, Opcje, Zaawansowane, Edytuj listy niestandardowe, zaimportujemy tę listę. Brak komunikatu o błędzie. Wygląda na to, że zadziałało, ale spada tylko do 140. Co do cholery z tym jest? Jaki jest limit? Pomyślałem, że to może być 254. Więc zobaczmy, ile znaków mamy, jeśli zejdziemy do 140. W porządku, więc zostawmy wszystko inne po tym i faktycznie przejdę tutaj do tej formuły i skopiuję dokładnie tę samą formułę nad. W porządku, nie.
W tym momencie jestem dość zirytowany zespołem Excela. Co słychać, tutaj 1764, a tutaj 1852. Hej, Microsoft, jaki jest limit? Dokładnie jaki jest limit? Ach, ale w tym rzecz. Muszą przechowywać to jako serię oddzielonych ciągów, dobrze? Więc biorą wszystkie elementy, a następnie dodają przecinek po każdym z nich. W porządku, więc ponieważ mamy Office 365, możemy użyć nowego łączenia tekstowego, więc = TEXTJOIN wszystkich z przecinkiem między. Nie wiem, czy to naprawdę przecinek, czy nie. Zignoruj to, a następnie True, przecinek i te elementy. Więc rozumiemy. Właściwie to po prostu chcę poznać długość tego wszystkiego. Więc długość to rok 1997, a kiedy robię to samo tutaj, to rok 1991. Och! Zatem oczywiście limit musi wynosić 2000 znaków, w tym niewidoczny przecinek między każdą pozycją.
To wszystko jest dość dziwne. W porządku, więc zawsze myślałem, że to 254 pozycje, a nie 254 pozycje. To 254 przedmioty, pod warunkiem, że zawiera mniej niż 2000 znaków, pod warunkiem, że przedmioty nie są zbyt długie. W porządku, więc żeby przetestować moją teorię, użyjmy po prostu takiej spacji 1, a weźmiemy uchwyt do napełniania i przeciągniemy. Powinny być naprawdę ładne i krótkie, ponieważ - Zejdziemy do 255, 254. Przejdźmy do 255, aby to przetestować.
W porządku, więc teraz z tym, jeśli poproszę o długość połączenia tekstowego, 1421. Żaden problem. Więc wybierz całość i Plik, Opcje, Zaawansowane, przewiń do samego dołu, Edytuj listy niestandardowe, kliknij Importuj. W porządku, wyłącz aż do 254. W porządku, więc jest to 254 pozycji, pod warunkiem, że zawiera mniej niż 2000 znaków, w tym niewidoczny przecinek po każdym elemencie, tak to działa.
Wiesz, więc - ale wracając do problemu Dona tutaj. Z pewnością denerwujące jest to, że okno dialogowe, jeśli po prostu wejdziemy i zaczniemy pisać rzeczy w oknie dialogowym zamiast mieć element 2000 znaków, ma na nim 255 znaków. W porządku, więc Don nie ma możliwości wpisania tego czegoś, a kiedy próbujemy zaimportować liczby, odmawia zaimportowania liczb. Mówi, że nie ma umowy. Wszystko, co nie jest zwykłym tekstem, nie zadziała, dobrze?
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.
Cóż, proszę bardzo. Don, jedno z bardziej szalonych pytań, jakie kiedykolwiek słyszałem w programie Excel, i odkryliśmy co najmniej 2 - Cóż, zdecydowanie 1 błąd w programie Excel, którego okno dialogowe Lista niestandardowa nie pozwala na więcej niż 255 znaków. A potem ta dziwna rzecz, która najwyraźniej posortuje listę niestandardową według numerów, ale nie pozwoli ci zaimportować liczb. W porządku, więc zadzwonię na ten błąd numer 2. A potem to obejście tutaj, w porządku?
Więc hej, chcę podziękować Donowi za przesłanie pytania, a jeśli będziesz kręcić się tak długo, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2098.xlsm
