Я люблю користувацькі списки в Excel. Вони чудово підходять для ручки заповнення та для сортування даних в іншій послідовності. Спеціальні списки повинні містити 254 елементи. Але чомусь читач стикається з ситуацією, коли Excel зберігає лише перші 38 елементів! Ми дійдемо до суті цієї таємниці.
Переглянути відео
- Дон хоче сортувати за спеціальним числовим списком!
- Чи вдасться це? Здається, це працює!
- Але ви не можете імпортувати числові комірки в діалогове вікно користувацького списку.
- Отже, спробуйте набрати цифри у діалоговому вікні «Спеціальний список»…. Під час набору тексту вас вражає нерозумне обмеження в 255 символів.
- WTH - це межа? 254 предмети? Ага - 254 елементи, але менше 2000 символів, коли ви додаєте невидиму кому між кожним елементом
- Провів математичну математику за допомогою
=SUM(LEN())і Ctrl + Shift + Enter іLEN(TEXTJOIN(",",True,Range)) - Вирішення проблеми з АБС для сортування в цьому конкретному випадку для Дона
- Але найкраще обхідне рішення … що Дон повинен зробити:
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2098: Спеціальні обмеження списку.
Це просто химерно, коли Don S, використовуючи Mac 2011, правильно, тому ми навіть не використовуємо реальну версію Excel. Ми використовуємо підроблену версію Excel, намагаючись сортувати за спеціальним списком, але приймаємо лише перші 38 елементів у списку. І я знаю, що це неправильно, тому що Excel може обробляти до 254 елементів у списку, або принаймні я так думав. Гаразд, і у Дона є Ім'я гравця, кількість перемог, а потім маржа, наприклад, як далеко були від рахунку тоді.
Отже, правильна послідовність полягає в тому, що досконала оцінка дорівнює 0, а потім більше на 1, під 1 і знову на 2, під 2, понад 3, під 3 і так далі. І Дон намагається відсортувати стовпець Margin за цим користувацьким списком. Зараз я ніколи цього не пробував, але, привіт, це повинно бути легко зробити. Отже, тут Правильна послідовність: 0, 1, а потім формула буде мінус значення безпосередньо перед нами, а потім = число 2 вище +1. Гаразд, тепер, коли у мене є ці дві формули, я мав би змогу пройти крізь 201, занадто далеко, але це нормально. І ми повинні мати точну послідовність, яка нам потрібна, до 99 та -99. Отже, є наш ідеальний набір відповідей. Я збираюся скопіювати це, щоб Ctrl + C скопіювати, а потім вставити як значення. Вставте ці значення так. Гаразд, тож я мав би змогу створити спеціальний список, який би це впорався, так? Без проблем.Отже, ми переходимо до Файл, Параметри, Додатково, прокручуємо 83% до кінця, вибираємо Редагувати власні списки, і ми імпортуємо наш список. Що? Клітини без простого тексту були проігноровані. Вам не дозволено мати спеціальний список, повний чисел? Але Дон каже, що це працює для перших 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. Гаразд, коли ми вводили елементи у списку, існує певна кількість символів. Була межа. Тому мені цікаво, чи існує якась кількість символів, яка тут обмежена = SUM (LEN цілої цієї кількості речей, натисніть CTRL + SHIFT ENTER, і це 1764 символи - 234 елементи. І я знаю, що у вас може бути 254, я робив це раніше.
А давайте спробуємо щось шаленіше. Добре, давайте спробуємо це. Давайте спробуємо замість пункту спробуємо щось довше. Отже, 10 символів ПРОБІЛ, а потім номер 1, ми опустимося на 254 рядки. І ми спробуємо імпортувати цей список: тому Файл, Параметри, Додатково, Редагувати власні списки, ми імпортуємо цей список. Немає повідомлення про помилку. Здається, це спрацювало, але воно падає лише до 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 символів, включаючи невидиму кому після кожного елемента, як це працює.
Знаєте, так - але повернімось до проблеми Дона тут. Напевно дратує те, що якщо в діалоговому вікні ми просто заходимо і починаємо друкувати щось у діалоговому вікні, а не маємо 2000 символів, у ньому є 255 символів. Добре, тому Дон не має можливості ввести цю річ, і коли ми намагаємося імпортувати номери, він відмовляється імпортувати номери. Там сказано, що угоди немає. Все, що не є простим текстом, не буде працювати, добре?
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
