Saya suka daftar kustom di Excel. Mereka bagus untuk pegangan isian dan untuk menyortir data ke dalam urutan yang berbeda. Daftar Kustom harus memungkinkan 254 item. Tetapi untuk beberapa alasan, pembaca menghadapi situasi di mana Excel hanya menyimpan 38 item pertama! Kami akan sampai ke dasar misteri ini.
Menonton video
- Don ingin mengurutkan berdasarkan daftar kustom yang numerik!
- Akankah ini berhasil? Sepertinya berhasil!
- Tapi Anda tidak bisa mengimpor sel numerik ke kotak dialog daftar kustom.
- Jadi, coba ketik angka ke dalam kotak dialog Daftar Kustom…. Anda dipukul dengan batas 255 karakter yang bodoh saat mengetik.
- WTH adalah batasnya? 254 item? Aha - 254 item, tetapi kurang dari 2000 karakter saat Anda menambahkan koma tak terlihat di antara setiap item
- Melakukan matematika teks dengan
=SUM(LEN())dan Ctrl + Shift + Enter danLEN(TEXTJOIN(",",True,Range)) - Solusi dengan ABS untuk menyortir dalam kasus khusus ini untuk Don
- Tapi solusi terbaik… hal yang perlu dilakukan Don:
Transkrip Video
Pelajari Excel dari Podcast, Episode 2098: Batas Daftar Kustom.
Ini hanya aneh ketika Don S, menggunakan Mac 2011, benar, jadi kami bahkan tidak menggunakan versi Excel yang sebenarnya. Kami menggunakan versi palsu Excel, mencoba mengurutkan menurut Daftar Kustom tetapi hanya menerima 38 item pertama dalam daftar. Dan saya tahu itu salah karena Excel dapat menangani hingga 254 item dalam daftar atau setidaknya itulah yang saya pikirkan. Baiklah, dan Don memiliki Nama Pemain, # Kemenangan lalu Margin, seperti seberapa jauh dari skor tadi.
Jadi urutan yang tepat adalah, skor sempurna 0 dan kemudian di atas 1, di bawah 1, dan di atas 2, di bawah 2, di atas 3, di bawah 3, dan seterusnya. Dan Don mencoba mengurutkan kolom Margin menurut Daftar Kustom ini. Sekarang, saya belum pernah mencobanya tapi, hei, itu seharusnya mudah dilakukan. Jadi di sini Urutan Yang Tepat: 0, 1 dan kemudian rumusnya akan dikurangi nilai sebelum kita dan kemudian = angka 2 di atas +1. Baiklah, sekarang saya memiliki dua formula itu, saya seharusnya bisa meluncur turun melalui 201, terlalu jauh tapi tidak apa-apa. Dan kita harus memiliki urutan yang tepat yang kita butuhkan hingga 99 dan -99. Jadi, inilah jawaban kami yang sempurna. Saya akan menyalinnya jadi Ctrl + C untuk menyalin dan kemudian Tempel sebagai Nilai. Tempel nilai-nilai itu seperti itu. Baiklah, jadi saya harus bisa menyiapkan Daftar Kustom yang akan menangani ini, bukan? Tidak masalah.Jadi kita pergi ke File, Options, Advanced, gulir 83% ke bawah, pilih Edit Daftar Kustom dan kami akan Mengimpor daftar kami. Apa? Sel tanpa teks sederhana diabaikan. Anda tidak boleh membuat Daftar Kustom penuh dengan angka? Tapi Don bilang ini berhasil untuk yang pertama 38. Ada apa dengan itu? Nah, sudah lama tidak memikirkan hal ini. Saya menyadari bahwa Don pasti tidak mencoba mengimpor; dia pasti baru saja mengetik angka-angka itu ke dalam Kotak Dialog.Saya menyadari bahwa Don pasti tidak mencoba mengimpor; dia pasti baru saja mengetik angka-angka itu ke dalam Kotak Dialog.Saya menyadari bahwa Don pasti tidak mencoba mengimpor; dia pasti baru saja mengetik angka-angka itu 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 Tempel di Notepad seperti ini Ctrl + V, lalu pilih semuanya: Edit, Pilih semua, dan Ctrl + C, kembali ke Excel, File, Options, Advanced, 83% ke bawah, Edit Daftar Kustom dan saya akan mengetik daftar itu di sini seperti Ctrl + V. Baiklah, dan semuanya berfungsi tetapi kami mengklik Tambah, panjang maksimum untuk Daftar Kustom telah terlampaui. Hanya 255 karakter pertama yang akan disimpan. Dan saat Anda melihat ini, tentu saja, mereka membuat Daftar Kustom yang hanya berjumlah 38, -38, 39, dan kemudian BAM! 3 terakhir, bukan?
Jadi, ini sangat aneh. Mereka sebenarnya mengizinkan saya membuat Daftar Kustom dengan angka tetapi mereka tidak mengizinkan saya mendapatkan 255. Maksud saya, itu berhasil. Ini berhasil dan kemudian jika kita benar-benar mencoba dan memilah di sini; jadi kita akan mengatakan Data, Sortir dan urutkan pada Daftar Kustom ini, yang hanya 39 klik OK, klik OK. Nah, jika ada dalam daftar itu diurutkan dengan benar. Baiklah, jadi positif +6 muncul sebelum -6; tapi setelah kita turun ke angka di atas 39, itu hanya akan menyortir urutan yang tidak ada dalam daftar, jadi dari yang terkecil ke terbesar di sini. Jadi, seseorang yang melewatkan 67 poin lebih baik daripada seseorang yang melewatkan +42 poin. Ini benar-benar gila.
Baiklah, dan ada apa dengan ini hanya menjadi 38? Sekarang, pasti ada angka lain jadi, Anda tahu, saya kira itu akan turun ke 30-an. Kami mulai menyukai di sana, bukan? Atau di sana, salah satu dari keduanya. Jadi apa itu? Itu total 78 item. Dan hei, saya tahu mereka mengizinkan - memiliki 250 forum karena saya berbicara tentang pelanggan sepanjang waktu dalam seminar saya, oke? Anda dapat memiliki 250 forum, mari saya tunjukkan. Jadi Item 1, dan tentunya kita bisa menggunakan Filling Item untuk itu. Saya akan menyeret ke 254, seperti itu. Sekarang itu bukan rumus jadi kita harus bisa melakukan File, Options, Advance, pergi ke Edit Custom List dan kita akan mengimpor daftar itu, oke? Itu dia, BAM! Tidak ada masalah, tidak ada pesan kesalahan. Semuanya bagus, semuanya - Tidak bagus. Ini hanya berlaku untuk Item 234. Tunggu, saya tahu Anda dapat memiliki 254.Mengapa berhenti di 234? Itu aneh, itu aneh. Ada apa dengan itu?
Jadi di sini, kita tahu bahwa itu hanya turun ke item 234 di sana. Oke sekarang, saat kita mengetik Item di daftar ada beberapa karakter. Ada batasnya. Jadi saya bertanya-tanya apakah ada sejumlah karakter yang menjadi batasnya di sini = SUM (LEN dari banyak hal itu, tekan CTRL + SHIFT ENTER, dan itu 1764 karakter - 234 item. Dan saya tahu Anda dapat memiliki 254, saya sudah melakukan ini sebelumnya.
Dan mari kita coba sesuatu yang lebih gila. Baiklah, ayo coba ini. Mari kita coba alih-alih item, mari kita coba sesuatu yang lebih lama. Jadi 10 karakter SPACE dan kemudian nomor 1, kita akan turun 254 baris. Dan kami akan mencoba dan mengimpor daftar ini: jadi File, Opsi, Tingkat Lanjut, Edit Daftar Kustom, kami akan Mengimpor daftar ini. Tidak ada pesan kesalahan. Sepertinya itu berhasil tetapi hanya turun menjadi 140. Ada apa dengan itu? Apa batasannya? Saya pikir itu bisa jadi 254. Jadi mari kita lihat, berapa banyak karakter yang kita miliki jika kita turun menjadi 140. Baiklah, jadi mari kita tinggalkan yang lainnya setelah ini dan sebenarnya saya akan datang ke sini untuk rumus ini dan menyalin rumus yang sama persis lebih. Baiklah, tidak.
Pada titik ini, saya cukup jengkel dengan tim Excel. Ada apa, di sini 1764 dan di sini 1852. Hai, Microsoft, berapa batasnya? Tepatnya apa batasnya? Ah, tapi ini masalahnya. Mereka pasti menyimpan ini sebagai rangkaian string yang dibatasi, oke? Jadi mereka mengambil semua Item dan kemudian mereka menambahkan koma setelah masing-masing item. Baiklah, jadi di sini karena kita memiliki Office 365, kita bisa menggunakan Text Join baru, jadi = TEXTJOIN dari semua itu dengan koma di antaranya. Saya tidak tahu apakah itu benar-benar koma atau tidak. Abaikan itu, lalu True, koma dan item ini. Jadi, kami mengerti. Dan sebenarnya saya hanya ingin tahu panjangnya semua itu. Jadi panjangnya 1997 dan ketika saya melakukan hal yang sama di sini, 1991. Oh! Jadi, jelas batasnya harus 2000 karakter termasuk koma tak terlihat di antara setiap item.
Ini semua sangat aneh. Baiklah, jadi saya selalu mengira itu 254 Item, bukan 254 Item. Ini adalah 254 Item, asalkan kurang dari 2.000 karakter, asalkan Item tersebut tidak terlalu panjang. Baiklah, jadi hanya untuk menguji teori saya, mari kita gunakan spasi 1 seperti itu dan kita akan ambil Fill Handle dan seret. Ini seharusnya sangat bagus dan pendek karena - Dan kita akan turun ke 255, 254. Mari kita ke 255 untuk mengujinya.
Baiklah, sekarang dengan ini, jika saya meminta Panjang Gabung Teks, 1421. Tidak masalah sama sekali. Jadi pilih semuanya dan File, Options, Advanced, gulir ke bawah ke bawah, Edit Daftar Kustom, klik Impor. Baiklah, dan matikan sampai 254. Baiklah, jadi itu 254 Item asalkan kurang dari 2.000 karakter termasuk koma tak terlihat setelah setiap item adalah cara kerjanya.
Anda tahu, jadi - tapi kembali ke masalah Don di sini. Benar-benar menjengkelkan bahwa Kotak Dialog, jika kita masuk dan mulai mengetik sesuatu di Kotak Dialog alih-alih memiliki elemen 2.000 karakter, ia memiliki 255 karakter di atasnya. Baiklah, jadi Don tidak memiliki cara untuk mengetikkan hal ini dan ketika kami mencoba dan Mengimpor nomor, ia menolak untuk Mengimpor nomornya. Dikatakan tidak ada kesepakatan. Apa pun yang bukan teks biasa tidak akan berfungsi, oke?
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, begitulah. Don, salah satu pertanyaan paling gila yang pernah saya dengar di Excel dan kami telah menemukan setidaknya 2 - Ya, pasti ada 1 bug di Excel yang Kotak Dialog Daftar Kustom tidak akan mengizinkan lebih dari 255 karakter. Dan kemudian hal aneh ini yang tampaknya akan mengurutkan Daftar Kustom berdasarkan angka tetapi mereka tidak mengizinkan Anda mengimpor angka. Baiklah, jadi saya akan menyebut bug itu nomor 2. Dan kemudian solusi ini di sini, oke?
Jadi, hei, saya ingin berterima kasih kepada Don karena telah mengirimkan pertanyaan dan jika Anda bertahan selama ini, saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2098.xlsm
