Я люблю настраиваемые списки в Excel. Они отлично подходят для дескриптора заполнения и для сортировки данных в другой последовательности. Пользовательские списки должны содержать 254 элемента. Но по какой-то причине читатель сталкивается с ситуацией, когда Excel сохраняет только первые 38 элементов! Мы раскроем эту тайну.
Смотреть видео
- Дон хочет отсортировать по настраиваемому числовому списку!
- Это сработает? Вроде работает!
- Но вы не можете импортировать числовые ячейки в диалоговое окно настраиваемого списка.
- Итак, попробуйте ввести числа в диалоговое окно Custom List…. Вы столкнулись с глупым ограничением в 255 символов при вводе текста.
- WTH это предел? 254 товара? Ага - 254 элемента, но меньше 2000 символов при добавлении невидимой запятой между каждым элементом
- Сделал некоторые математические вычисления с помощью
=SUM(LEN())Ctrl + Shift + Enter иLEN(TEXTJOIN(",",True,Range)) - Решение проблемы с ABS для сортировки в данном случае для Дона
- Но лучший обходной путь … то, что нужно сделать Дону:
Стенограмма видео
Изучите Excel из подкаста, эпизод 2098: ограничения настраиваемого списка.
Это просто странно, когда Дон С. использует Mac 2011, верно, поэтому мы даже не используем настоящую версию Excel. Мы используем поддельную версию Excel, пытаясь выполнить сортировку по настраиваемому списку, но она принимает только первые 38 элементов в списке. И я знаю, что это неправильно, потому что Excel может обрабатывать до 254 элементов в списке или, по крайней мере, так я думал. Хорошо, и у Дона есть имя игрока, количество побед, а затем маржа, вроде того, как далеко было тогда от счета.
Итак, правильная последовательность такова, что идеальный результат равен 0, а затем больше на 1, меньше на 1 и больше на 2, меньше на 2, больше на 3, меньше на 3 и так далее. И Дон пытается отсортировать столбец Margin по этому Custom List. Я никогда не пробовал этого, но, эй, это должно быть легко сделать. Итак, вот правильная последовательность: 0, 1, а затем формула будет минус значение непосредственно перед нами, а затем = число 2 над +1. Хорошо, теперь, когда у меня есть эти две формулы, я смогу пройти 201, слишком далеко, но это нормально. И у нас должна быть точная последовательность, которая нам нужна, от 99 до -99. Итак, вот наш идеальный набор ответов. Я собираюсь скопировать это, чтобы скопировать Ctrl + C, а затем вставить как значения. Вставьте эти значения вот так. Хорошо, я смогу создать собственный список, который справится с этим, верно? Нет проблем.Итак, мы переходим в File, Options, Advanced, прокручиваем 83% до конца, выбираем Edit Custom Lists, и мы импортируем наш список. Какая? Ячейки без простого текста игнорировались. Вам не разрешено иметь собственный список, полный номеров? Но Дон говорит, что это работает для первых 38. Что с этим делать? Ну, не думай об этом некоторое время. Я понял, что Дон, должно быть, не пытался импортировать; он, должно быть, только что вводил эти числа в диалоговое окно.Я понял, что Дон, должно быть, не пытался импортировать; он, должно быть, только что вводил эти числа в диалоговое окно.Я понял, что Дон, должно быть, не пытался импортировать; он, должно быть, только что вводил эти числа в диалоговое окно.
Итак, вот что я собираюсь сделать. Я собираюсь сделать Ctrl + C, чтобы скопировать все это, я собираюсь перейти в Блокнот и вставить в Блокнот, как это Ctrl + V, а затем выделить все: Изменить, Выбрать все и Ctrl + C, вернуться к Excel, Файл, Параметры, Дополнительно, 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, вот так. Теперь это не формулы, поэтому у нас должна быть возможность выбрать «Файл», «Параметры», «Вперед», перейти к «Редактировать пользовательские списки» и мы импортируем этот список, хорошо? Вот он, БАМ! Нет проблем, нет сообщений об ошибках. Все отлично, все - не здорово. Это относится только к пункту 234. Подождите, я знаю, у вас может быть 254.Почему он останавливается на 234? Это странно, это странно. Что с этим?
Итак, мы знаем, что здесь речь идет только о пункте 234. Хорошо, теперь, когда мы набирали Items, в списке было некоторое количество символов. Был предел. Поэтому мне интересно, есть ли здесь ограничение на какое-то количество символов = СУММ (LEN всей этой кучи материала, нажмите CTRL + SHIFT ENTER, и это 1764 символа - 234 элемента. И я знаю, что у вас может быть 254, у меня делал это раньше.
А давайте попробуем что-нибудь более безумное. Хорошо, давай попробуем. Давайте попробуем вместо item, давайте попробуем что-нибудь подольше. Итак, 10 символов ПРОБЕЛ, а затем цифра 1, мы спустимся на 254 строки. И мы попробуем импортировать этот список: так что File, Options, Advanced, Edit Custom Lists, мы импортируем этот список. Сообщение об ошибке отсутствует. Похоже, это сработало, но оно снизилось только до 140. Что, черт возьми, с этим? Какой предел? Я думал, что это может быть 254. Итак, давайте посмотрим, сколько символов у нас будет, если мы сократим его до 140. Хорошо, давайте оставим все остальное после этого, и на самом деле я перейду к этой формуле и скопирую ту же самую формулу над. Хорошо, нет.
На данный момент я очень зол на команду Excel. Что случилось, здесь 1764, а здесь 1852. Эй, Microsoft, какой предел? Какой именно предел? Ах, но вот в чем дело. Они должны хранить это как серию строк с разделителями, хорошо? Итак, они берут все Предметы, а затем добавляют запятую после каждого. Итак, поскольку у нас есть Office 365, мы можем использовать новое присоединение текста, поэтому = TEXTJOIN всех тех, у кого есть запятая между ними. Не знаю, действительно ли это запятая или нет. Игнорируйте это, затем True, запятую и эти элементы. Итак, мы получили это. И на самом деле я просто хочу знать длину всего этого. Итак, длина - 1997 год, а когда я делаю то же самое здесь, 1991 год. О! Итак, ясно, что ограничение должно составлять 2000 символов, включая невидимую запятую между каждым элементом.
Все это довольно странно. Хорошо, я всегда думал, что это 254 элемента, а не 254 элемента. Это 254 Предмета, при условии, что оно меньше 2000 символов, при условии, что Предметы не слишком длинные. Хорошо, чтобы проверить мою теорию, давайте просто воспользуемся пробелом 1, как это, возьмем маркер заливки и перетащим. Они должны быть действительно красивыми и короткими, потому что - И мы спустимся к 255, 254. Давайте перейдем к 255, чтобы проверить это.
Хорошо, теперь с этим, если я спрошу длину текстового соединения, 1421. Никаких проблем. Так что выберите все это и «Файл», «Параметры», «Дополнительно», прокрутите вниз до самого низа, «Редактировать пользовательские списки» и нажмите «Импорт». Хорошо, и выключите полностью до 254. Хорошо, это 254 элемента при условии, что это менее 2000 символов, включая невидимую запятую после каждого элемента. Вот как это работает.
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.
Ну вот и все. Дон, один из самых безумных вопросов, которые я когда-либо слышал в Excel, и мы обнаружили как минимум 2 - ну, определенно 1 ошибку в Excel, в которой диалоговое окно настраиваемого списка не позволяет использовать более 255 символов. А затем эта странная вещь, которая, по-видимому, сортирует пользовательский список по числам, но они не позволяют вам импортировать числа. Хорошо, я назову эту ошибку номер 2. А потом вот этот обходной путь, хорошо?
Итак, привет, я хочу поблагодарить Дона за то, что он прислал вопрос, и, если вы задержитесь здесь так долго, я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2098.xlsm
