Волим прилагођене листе у Екцелу. Изврсне су за ручку за попуњавање и за сортирање података у другачији редослед. Прилагођене листе треба да дозволе 254 предмета. Али из неког разлога, читалац се сусреће са ситуацијом у којој Екцел задржава само првих 38 предмета! Доћи ћемо до дна ове мистерије.
Погледајте видео
- Дон жели да сортира по прилагођеној листи која је нумеричка!
- Хоће ли ово успети? Изгледа да успева!
- Али не можете да увезете нумеричке ћелије у дијалошки оквир прилагођене листе.
- Дакле, покушајте да укуцате бројеве у дијалошки оквир Прилагођена листа …. Добијате глупо ограничење од 255 знакова приликом куцања.
- ВТХ је ограничење? 254 предмета? Аха - 254 предмета, али мање од 2000 знакова када додате невидљиву запету између сваке ставке
- Да ли се неки математички обрачунавао са
=SUM(LEN())и Цтрл + Схифт + Ентер иLEN(TEXTJOIN(",",True,Range)) - Заобилазно решење са АБС-ом за сортирање у овом случају за Дон
- Али најбоље решење … ствар коју Дон треба да уради:
Видео транскрипт
Научите Екцел из Подцаста, епизода 2098: Ограничења прилагођених спискова.
Ово је само бизарно када Дон С, користећи Мац 2011, тачно, па не користимо ни праву верзију Екцела. Користимо лажну верзију програма Екцел, покушавајући да сортирамо по прилагођеној листи, али прихвата само првих 38 ставки на листи. И знам да је то погрешно, јер Екцел може да обради до 254 ставке на листи или сам бар тако мислио. Добро, и Дон има име играча, број победа, а затим и маргину, на пример колико су тада били удаљени од резултата.
Дакле, правилна секвенца је, савршени резултат је 0, а затим преко 1, испод 1 и преко 2, испод 2, преко 3, испод 3 и тако даље. И Дон покушава да сортира колону Маргин по овој Прилагођеној листи. Сад то никада нисам пробао, али, хеј, то би требало бити лако учинити. Дакле, овде Правилна секвенца: 0, 1 и тада ће формула бити минус вредност непосредно пре нас и тада = број 2 изнад +1. У реду, сад кад имам те две формуле, могао бих да пређем кроз 201, предалеко, али то је у реду. И требали бисмо имати тачан редослед који нам треба до 99 и -99. Дакле, ту је наш савршени скуп одговора. Копираћу то тако да Цтрл + Ц копирам, а затим налепи као вредности. Залепите те вредности тако. У реду, па бих требао бити у могућности да поставим прилагођену листу која ће то решити, зар не? Нема проблема.Дакле, идемо на Филе, Оптионс, Адванцед, померимо 83% до краја, изаберемо Едит Цустом Листс и увеземо нашу листу. Шта? Ћелије без једноставног текста су занемарене. Није вам дозвољено да имате прилагођену листу препуну бројева? Али Дон каже да ово ради за првих 38. Шта је с тим? Па, не размишљајте о овоме већ неко време. Схватио сам да Дон сигурно није покушавао да увози; мора да је управо укуцао те бројеве у дијалог.Схватио сам да Дон сигурно није покушавао да увози; мора да је управо укуцао те бројеве у оквир за дијалог.Схватио сам да Дон сигурно није покушавао да увози; мора да је управо укуцао те бројеве у дијалог.
Па ево шта ћу да радим. Урадићу Цтрл + Ц да копирам све оне, идем у Нотепад и Пасте у Нотепад попут овог Цтрл + В, а затим изаберите све: Едит, Селецт алл и Цтрл + Ц, вратите се на Екцел, Филе, Оптионс, Адванцед, 83% до краја, Едит Цустом Листс и укуцаћу ту листу као да је Цтрл + В. У реду, и да све функционише, али смо кликнули Додај, премашена је максимална дужина за прилагођену листу. Биће сачувано само првих 255 знакова. А кад ово погледате, сигурно праве прилагођену листу која се своди само на 38, -38, 39, а затим на БАМ! Последња 3, зар не?
Дакле, ово је тако бизарно. Они ми заправо дозвољавају да направим прилагођену листу са бројевима, али ми не дозвољавају да добијем 255. Мислим, то функционише. Успева и ако покушамо да сортирамо овде; па ћемо рећи Дата, Сортирај и сортирај на овој прилагођеној листи, оној која иде само на 39, кликните ОК, кликните ОК. Па, ако је на листи, сортираће се тачно. У реду, дакле позитивни +6 показују пре -6; али онда када се спустимо на било шта дуже од 39, то ће само сортирати у секвенцама којих нема на листи, па ће овде ићи од најмањег до највећег. Дакле, неко ко је промашио за 67 поена је бољи од некога ко је промашио за +42 поена. Једноставно је потпуно зезнут.
У реду, а шта је са овим само на 38? Сад, сигурно да постоји још сваки број, па, знате, претпостављам да ће се спустити у 30-те. Долазимо да волимо тамо, зар не? Или тамо, једно од то двоје. Па, шта је то? То је укупно 78 предмета. И хеј, знам да су дозволили - имају 250 форума јер стално говорим о купцима на својим семинарима, у реду? Можете имати 250 форума, да вам покажем. Дакле, ставка 1, и наравно за то можемо користити ставку за попуњавање. Превући ћу на 254, тако. Сада то нису формуле, тако да бисмо могли да радимо Филе, Оптионс, Адванце, идемо на Едит Цустом Листс и ми ћемо увести ту листу, у реду? Ето га, БАМ! Нема проблема, нема поруке о грешци. Све је супер, све је - Није сјајно. Односи се само на ставку 234. Чекај, знам да можеш имати 254.Зашто се зауставља на 234? То је бизарно, то је бизарно. Шта је са тим?
Дакле, овде знамо да се тамо своди само на тачку 234. У реду сада, док смо куцали ставке на листи, постоји одређени број знакова. Постојала је граница. Па се питам да ли постоји неки број знакова који је овде ограничен = СУМ (ЛЕН целе те гомиле ствари, притисните ЦТРЛ + СХИФТ ЕНТЕР, а то је 1764 знака - 234 предмета. И знам да можете имати 254, ја имам учинио ово пре.
И хајде да покушамо нешто луђе. У реду, пробајмо ово. Покушајмо уместо предмета покушајмо нешто дуже. Дакле, 10 знакова РАЗМАК, а затим број 1, спустићемо се за 254 реда. И ми ћемо покушати да увеземо ову листу: тако да Датотека, Опције, Напредно, Уреди прилагођене листе, увеземо ову листу. Нема поруке о грешци. Изгледа да је успело, али пада само на 140. Шта је дођавола с тим? Која је граница? Мислио сам да би могло бити 254. Па да видимо, колико имамо знакова ако се спустимо на 140. У реду, па оставимо све остало након овога и заправо доћи ћу овде до ове формуле и копирати потпуно исту формулу готово. У реду, не.
У овом тренутку сам прилично огорчен са Екцел тимом. Шта има овде, 1764, а овде 1852. Хеј, Мицрософт, која је граница? Тачно која је граница? Ах, али ево у чему је ствар. Сигурно ово чувају као низ одвојених низова, у реду? Дакле, узимају све ставке, а затим додају зарез након сваке. У реду, па овде, пошто имамо Оффице 365, можемо да користимо ново удруживање текста, дакле = ТЕКСТЈОИН свих оних са зарезом између. Не знам да ли је то заиста зарез или не. Занемарите то, затим Тачно, зарез и ове ставке. Дакле, схватили смо. И заправо само желим да знам дужину целе те ствари. Дакле, дужина је 1997, а када радим исту ствар овде, 1991. Ох! Дакле, ограничење мора бити 2000 знакова, укључујући невидљиву зарез између сваке ставке.
Све је ово прилично бизарно. У реду, па сам увек мислио да је то 254 предмета, није 254 предмета. То је 254 предмета, под условом да има мање од 2000 знакова, под условом да ставке нису предуге. У реду, само да бисмо тестирали моју теорију, само искористимо размак 1 и ухватићемо ручку за попуњавање и повући. Ово би требало да буде заиста лепо и кратко јер - И ми ћемо се спустити на 255, 254. Идемо на 255 да га тестирамо.
У реду, па сада са овим, ако тражим дужину удруживања текста, 1421. Уопште нема проблема. Дакле, одаберите целу ствар и Датотека, Опције, Напредно, померите се све до дна, Уредите прилагођене листе, кликните Увези. У реду, и искључите скроз доле на 254. У реду, значи 254 предмета под условом да има мање од 2000 знакова, укључујући невидљиву зарез након сваке ставке како то функционише.
Знате, па - али вратимо се на Донов проблем овде. Засигурно је досадно што у дијалошком оквиру, ако само уђемо и почнемо да куцамо ствари у дијалошком оквиру, уместо да имамо 2.000 знаковних елемената, на њему је 255 знакова. Добро, тако да Дон нема начина да ово откуца и када покушамо да увежемо бројеве, он одбија да их увози. Каже да нема договора. Све што није обичан текст неће успети, у реду?
Дакле, једина ствар коју сам Дону предложио је алтернативно решење. Кажем, хеј, хајде само да изађемо овде и додамо помоћну колону и ова помоћна колона ће бити - ако је Апсолутна вредност тог броја, у реду. И двапут ћемо кликнути да то спустимо, а онда ћете само сортирати Силазно према Апсолутној вредности, поставку од Апсолутне вредности, у реду. А онда 4, 6, а затим -6, у реду, све се то само сортира, знате. Дакле, није лоше, претпостављам да бисте стварно могли да сортирате помоћника, а затим додате ниво, а затим сортирате по маргини, спуштајући се од Највећег до Најмањег, кликните на дугме У реду и то ће добити оно што Дон тражи. Дакле, сви +6 ће се појавити пре -6, а затим 8 и онда -11, у реду. Знате, али ово је гњаважа. Као, хеј, Мицрософт.Зашто морамо ићи на све ове гњаваже? Зашто бисте нам дозволили да упишемо 2.000 знакова у оквир за дијалог или још боље, јер очигледно ради тако што бројеви имају прилагођену листу, мислим да овде ради, зашто нам не допуштате да га увозимо?
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.
Па, ето ти. Дон, једно од луђих питања која сам икад чуо у Екцелу и открили смо најмање 2 - Па, дефинитивно 1 грешку у програму Екцел да дијалошки оквир Прилагођена листа неће дозволити више од 255 знакова. А онда бизарна ствар која ће очигледно сортирати прилагођену листу по бројевима, али вам неће дозволити да увезете бројеве. У реду, па ћу назвати ту грешку број 2. А онда ово решење овде, у реду?
Па, хеј, желим да захвалим Дону што је послао питање и ако се толико задржавате, желим да вам захвалим што сте навратили. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2098.клсм
