Saya suka senarai tersuai di Excel. Ini bagus untuk pemegang pengisian dan untuk menyusun data ke urutan yang berbeza. Daftar Tersuai harus membenarkan 254 item. Tetapi atas sebab tertentu, pembaca menghadapi situasi di mana Excel hanya menyimpan 38 item pertama! Kita akan sampai ke dasar misteri ini.
Tonton Video
- Don mahu menyusun mengikut senarai tersuai yang berangka!
- Adakah ini akan berjaya? Nampaknya berjaya!
- Tetapi anda tidak dapat mengimport sel angka ke kotak dialog senarai khusus.
- Oleh itu, cubalah menaip nombor ke dalam kotak dialog Daftar Custom…. Anda terkena had 255 watak bodoh ketika menaip.
- WTH adalah hadnya? 254 item? Aha - 254 item, tetapi kurang daripada 2000 aksara apabila anda menambahkan koma yang tidak kelihatan di antara setiap item
- Adakah beberapa teks matematik dengan
=SUM(LEN())dan Ctrl + Shift + Enter danLEN(TEXTJOIN(",",True,Range)) - Selesaikan dengan ABS untuk menyusun kes tertentu untuk Don
- Tetapi penyelesaian terbaik … perkara yang perlu dilakukan Don:
Transkrip Video
Belajar Excel dari Podcast, Episode 2098: Had Senarai Tersuai.
Ini hanya pelik apabila Don S, menggunakan Mac 2011, betul, jadi kami bahkan tidak menggunakan versi Excel yang sebenar. Kami menggunakan versi Excel palsu, cuba menyusun mengikut Senarai Tersuai tetapi ia hanya menerima 38 item pertama dalam senarai. Dan saya tahu itu salah kerana Excel dapat menangani sehingga 254 item dalam senarai atau sekurang-kurangnya itulah yang saya fikirkan. Baiklah, dan Don mempunyai Nama Pemain, # Kemenangan dan kemudian Margin, seperti jarak dari skor ketika itu.
Oleh itu, urutan yang betul adalah, skor yang sempurna adalah 0 dan kemudian berakhir dengan 1, di bawah oleh 1, dan lebih dari 2, di bawah oleh 2, di atas oleh 3, di bawah oleh 3 dan seterusnya. Dan Don cuba menyusun lajur Margin mengikut Senarai Tersuai ini. Sekarang, saya tidak pernah mencubanya tetapi, hei, semestinya senang dilakukan. Jadi di sini Urutan yang Betul: 0, 1 dan kemudian formula akan menjadi nilai tolak tepat sebelum kita dan kemudian = nombor 2 di atas +1. Baiklah, sekarang saya mempunyai dua formula yang seharusnya saya dapat melalui tahun 201, terlalu jauh di sana tetapi tidak mengapa. Dan kita harus mempunyai urutan yang tepat yang kita perlukan hingga 99 dan -99. Jadi ada sekumpulan jawapan kami yang sempurna. Saya akan menyalinnya sehingga Ctrl + C untuk disalin dan kemudian Tampal sebagai Nilai. Tampalkan nilai-nilai seperti itu. Baiklah, jadi saya harus dapat menyediakan Senarai Tersuai yang akan menangani ini, bukan? Tiada masalah.Oleh itu, kita pergi ke Fail, Pilihan, Lanjutan, tatal 83% ke bawah, pilih Edit Daftar Tersuai dan kami akan Mengimport senarai kami. Apa? Sel tanpa teks ringkas tidak dihiraukan. Anda tidak dibenarkan mempunyai Senarai Tersuai penuh dengan nombor? Tetapi Don mengatakan ini berfungsi untuk 38 yang pertama. Ada apa dengan itu? Baiklah, sudah lama tidak memikirkan perkara ini. Saya menyedari bahawa Don tidak semestinya cuba mengimport; dia pasti baru sahaja menaip nombor tersebut ke dalam Kotak Dialog.Saya menyedari bahawa Don tidak semestinya cuba mengimport; dia pasti baru sahaja menaip nombor tersebut ke dalam Kotak Dialog.Saya menyedari bahawa Don tidak semestinya cuba mengimport; dia pasti baru sahaja menaip nombor tersebut ke dalam Kotak Dialog.
Jadi inilah yang akan saya lakukan. Saya akan melakukan Ctrl + C untuk menyalin semua itu, saya akan pergi ke Notepad dan Tampal di Notepad seperti Ctrl + V ini, dan kemudian pilih semuanya: Edit, Pilih semua, dan Ctrl + C, kembali ke Excel, Fail, Pilihan, Lanjutan, 83% ke bawah, Edit Daftar Tersuai dan saya akan menaip senarai di sini seperti Ctrl + V. Baiklah, dan semuanya berfungsi tetapi kami mengklik Tambah, panjang maksimum untuk Senarai Tersuai telah terlampaui. Hanya 255 aksara pertama yang akan disimpan. Dan apabila anda melihat ini, sudah tentu, mereka membuat Senarai Tersuai yang hanya menjadi 38, -38, 39 dan kemudian BAM! 3 yang terakhir, bukan?
Jadi, ini sangat pelik. Mereka sebenarnya membenarkan saya membuat Senarai Tersuai dengan nombor tetapi mereka tidak membenarkan saya mendapat 255. Maksud saya, ia berjaya. Ia berfungsi dan jika kita benar-benar mencuba dan menyelesaikannya di sini; jadi kita akan mengatakan Data, Susun dan urutkan pada Senarai Tersuai ini, yang hanya mencapai 39 klik OK, klik OK. Nah, jika ada dalam senarai itu disusun dengan betul. Baiklah, jadi +6 positif ditunjukkan sebelum -6; tetapi setelah kita mencapai apa-apa yang melebihi 39, ia hanya akan disusun mengikut urutan yang tidak ada dalam senarai, jadi dari yang terkecil hingga terbesar di sini. Jadi, seseorang yang ketinggalan dengan 67 mata lebih baik daripada seseorang yang ketinggalan +42 mata. Ia betul-betul tidak rata.
Baiklah, dan apa yang berlaku dengan ini hanya akan mencapai 38? Sekarang, pasti ada bilangan lain, jadi, saya rasa ia akan turun ke 30-an. Kita semakin suka di sana, bukan? Atau di sana, salah satu daripada kedua-duanya. Jadi, apa itu? Itu adalah sebanyak 78 item. Dan hei, saya tahu mereka membenarkan - mempunyai 250 forum kerana saya selalu bercakap tentang pelanggan di seminar saya, okey? Anda boleh mempunyai 250 forum, izinkan saya menunjukkan kepada anda. Jadi Item 1, dan tentu saja kita boleh menggunakan Item Pengisian untuk itu. Saya akan turun ke 254, seperti itu. Sekarang ini bukan formula, jadi kita seharusnya dapat melakukan File, Options, Advance, pergi ke Edit Daftar Custom dan kita akan Import senarai itu, baiklah? Itu dia, BAM! Tidak ada masalah, tidak ada mesej ralat. Semuanya hebat, semuanya - Tidak hebat. Itu hanya masuk ke Item 234. Tunggu, saya tahu anda boleh memiliki 254.Mengapa berhenti pada 234? Itu pelik, itu pelik. Ada apa dengan itu?
Jadi di sini, kita tahu bahawa ia hanya akan sampai ke item 234 di sana. Baiklah sekarang, semasa kami menaip Item dalam senarai terdapat sebilangan watak. Ada hadnya. Oleh itu, saya tertanya-tanya jika ada sebilangan watak yang menjadi hadnya di sini = SUM (LEN dari jumlah itu, tekan CTRL + SHIFT ENTER, dan itu 1764 aksara - 234 item. Dan saya tahu bahawa anda boleh memiliki 254, saya sudah melakukan ini sebelum ini.
And let's try something crazier. Alright, let's try this. Let's try instead of item let's try something longer. So 10 characters a SPACE and then number 1, we’ll go down 254 rows. And we'll try and import this list: so File, Options, Advanced, Edit Custom Lists, we’ll Import this list. No error message. Seems like it worked but it only goes down to 140. What the heck is up with that? What is the limit? I thought it could be 254. So let's see, how many characters we have if we get down to 140. Alright, so let's leave everything else after this and in fact I'll come over here to this formula and copy the exact same formula over. Alright, no.
At this point, I'm pretty exasperated with the Excel team. What's up, here 1764 and here 1852. Hey, Microsoft, what is the limit? Exactly what is the limit? Ah, but here's the thing. They must be storing this as a series of delimited strings, alright? So they're taking all the Items and then they're adding a comma after each one. Alright, so here since we have Office 365, we can use the new Text Join, so =TEXTJOIN of all of those with a comma in between. I don’t know if it’s really a comma or not. Ignore that, then True, comma and these items. So, we get that. And actually I just want to know the length to that whole thing. So the length is 1997 and when I do the same thing over here, 1991. Oh! So, clearly the limit must be 2000 characters including an invisible comma between each item.
This is all pretty bizarre. Alright, so I always thought it was 254 Items, it's not 254 Items. It’s 254 Items, provided it’s less than 2,000 characters, provided the Items aren't too long. Alright, so just to test my theory, let's just use A space 1 like that and we’ll grab the Fill Handle and drag. These should be really nice and short because - And we'll go down to 255, 254. Let's go to 255 to test it.
Alright, so now with this, if I ask for the Length of the Text Join, 1421. No problem at all. So select the whole thing and File, Options, Advanced, scroll all the way down to the bottom, Edit Custom Lists, click Import. Alright, and turn off all the way down to 254. Alright, so it's 254 Items provided it’s less than 2,000 characters including a invisible comma after each item is how this works.
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.
Nah, di sana anda pergi. Don, salah satu soalan yang paling gila yang pernah saya dengar di Excel dan kami telah menemui sekurang-kurangnya 2 - Baiklah, pasti 1 pepijat di Excel bahawa Kotak Dialog Daftar Kustom tidak akan membenarkan lebih daripada 255 aksara. Dan perkara pelik ini yang nampaknya akan menyusun Senarai Tersuai mengikut nombor tetapi mereka tidak akan membiarkan anda mengimport nombor. Baiklah, jadi saya akan memanggil nombor bug itu 2. Dan kemudian penyelesaian ini di sini, baiklah?
Jadi hei, saya ingin mengucapkan terima kasih kepada Don kerana telah menghantar pertanyaan dan jika anda bertahan selama ini, saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail contoh di sini: Podcast2098.xlsm
