Tôi thích danh sách tùy chỉnh trong Excel. Chúng rất tuyệt vời cho xử lý điền và để sắp xếp dữ liệu thành một chuỗi khác. Danh sách tùy chỉnh sẽ cho phép 254 mục. Nhưng vì lý do nào đó, một độc giả gặp phải tình huống Excel chỉ giữ 38 mục đầu tiên! Chúng ta sẽ đi đến tận cùng của bí ẩn này.
Xem video
- Don muốn sắp xếp theo danh sách tùy chỉnh là số!
- Điều này sẽ hoạt động? Nó dường như hoạt động!
- Nhưng bạn không thể nhập các ô số vào hộp thoại danh sách tùy chỉnh.
- Vì vậy, hãy cố gắng nhập số vào hộp thoại Danh sách tùy chỉnh…. Bạn bị giới hạn 255 ký tự khi nhập.
- WTH là giới hạn? 254 mặt hàng? Aha - 254 mục, nhưng ít hơn 2000 ký tự khi bạn thêm dấu phẩy ẩn giữa mỗi mục
- Đã làm một số phép toán văn bản với
=SUM(LEN())và Ctrl + Shift + Enter vàLEN(TEXTJOIN(",",True,Range)) - Giải pháp với ABS để phân loại trong trường hợp cụ thể này cho Don
- Nhưng cách giải quyết tốt nhất… điều mà Don cần làm:
Bản ghi video
Học Excel từ Podcast, Tập 2098: Giới hạn danh sách tùy chỉnh.
Đây chỉ là một điều kỳ lạ khi Don S, sử dụng Mac 2011, đúng vậy, vì vậy chúng tôi thậm chí không sử dụng phiên bản thực của Excel. Chúng tôi đang sử dụng phiên bản giả mạo của Excel, cố gắng sắp xếp theo Danh sách tùy chỉnh nhưng nó chỉ chấp nhận 38 mục đầu tiên trong danh sách. Và tôi biết điều đó là sai vì Excel có thể xử lý tới 254 mục trong danh sách hoặc ít nhất đó là những gì tôi nghĩ. Được rồi, và Don có Tên người chơi, Số trận thắng và sau đó là Biên, giống như khoảng cách so với điểm số lúc đó.
Vì vậy, trình tự thích hợp là, điểm hoàn hảo là 0 và sau đó trên 1, dưới 1, và hơn 2, dưới 2, trên 3, dưới 3, v.v. Và Don đang cố gắng sắp xếp cột Lề theo Danh sách Tùy chỉnh này. Bây giờ, tôi chưa bao giờ thử điều đó nhưng, này, nó sẽ dễ dàng thực hiện. Vì vậy, ở đây Trình tự thích hợp: 0, 1 và sau đó công thức sẽ trừ đi giá trị ngay trước chúng ta và sau đó = số 2 ở trên +1. Được rồi, bây giờ tôi đã có hai công thức đó, tôi có thể đi đến năm 201, quá xa đó nhưng không sao. Và chúng ta sẽ có chuỗi chính xác mà chúng ta cần xuống 99 và -99. Vì vậy, có một bộ câu trả lời hoàn hảo của chúng tôi. Tôi sẽ sao chép nó để Ctrl + C để sao chép và sau đó Dán làm Giá trị. Dán các giá trị đó như vậy. Được rồi, tôi sẽ có thể thiết lập Danh sách tùy chỉnh để xử lý việc này, phải không? Không vấn đề gì.Vì vậy, chúng tôi đi đến Tệp, Tùy chọn, Nâng cao, cuộn hết 83% xuống dưới, chọn Chỉnh sửa Danh sách Tùy chỉnh và chúng tôi sẽ Nhập danh sách của mình. Gì? Các ô không có văn bản đơn giản đã bị bỏ qua. Bạn không được phép có Danh sách tùy chỉnh đầy số? Nhưng Don nói rằng điều này đang hoạt động cho 38 đầu tiên. Có gì với điều đó? Chà, đã không nghĩ về điều này trong một thời gian. Tôi nhận ra rằng Don hẳn đã không cố gắng nhập khẩu; chắc hẳn anh ấy vừa nhập những số đó vào Hộp thoại.Tôi nhận ra rằng Don hẳn đã không cố gắng nhập khẩu; chắc hẳn anh ta vừa nhập những số đó vào Hộp thoại.Tôi nhận ra rằng Don hẳn đã không cố gắng nhập khẩu; chắc hẳn anh ấy vừa nhập những số đó vào Hộp thoại.
Vì vậy, đây là những gì tôi sẽ làm. Tôi sẽ thực hiện Ctrl + C để sao chép tất cả những thứ đó, tôi sẽ đi tới Notepad và Dán trong Notepad như thế này Ctrl + V, sau đó chọn mọi thứ: Chỉnh sửa, Chọn tất cả và Ctrl + C, quay lại Excel, Tệp, Tùy chọn, Nâng cao, giảm 83%, Chỉnh sửa Danh sách Tùy chỉnh và tôi sẽ nhập danh sách đó vào đây giống như Ctrl + V. Được rồi, tất cả đều hoạt động nhưng chúng tôi đã nhấp vào Thêm, độ dài tối đa cho Danh sách tùy chỉnh đã bị vượt quá. Chỉ 255 ký tự đầu tiên sẽ được lưu. Và khi bạn nhìn vào điều này, chắc chắn, họ đang tạo một Danh sách tùy chỉnh chỉ giảm xuống 38, -38, 39 và sau đó BAM! 3 cuối cùng, phải không?
Vì vậy, điều này thật kỳ lạ. Họ thực sự cho phép tôi tạo Danh sách tùy chỉnh với các con số nhưng họ không cho phép tôi lấy 255. Ý tôi là, nó hoạt động. Nó hoạt động và sau đó nếu chúng tôi thực sự thử và sắp xếp ở đây; vì vậy chúng tôi sẽ nói Dữ liệu, Sắp xếp và sắp xếp trên Danh sách tùy chỉnh này, danh sách chỉ chuyển đến 39 nhấp OK, nhấp OK. Chà, nếu nó nằm trong danh sách thì nó được sắp xếp chính xác. Được rồi, vì vậy +6 dương hiển thị trước -6; nhưng sau đó khi chúng ta xuống bất kỳ thứ gì ngoài 39, nó sẽ chỉ sắp xếp theo các trình tự không có trong danh sách, vì vậy nó sẽ từ nhỏ nhất đến lớn nhất ở đây. Vì vậy, ai đó bỏ lỡ 67 điểm tốt hơn ai đó bỏ lỡ +42 điểm. Nó chỉ hoàn toàn vặn vẹo.
Được rồi, có chuyện gì xảy ra khi chỉ có con số 38? Bây giờ, chắc chắn rằng có mọi con số khác, bạn biết đấy, tôi đoán nó sẽ giảm xuống những năm 30. Chúng ta bắt đầu thích ở đó, phải không? Hoặc ở đó, một trong hai. Vậy, đó là gì? Đó là tổng số 78 mặt hàng. Và này, tôi biết họ đã cho phép - có 250 diễn đàn vì tôi luôn nói về khách hàng trong các cuộc hội thảo của mình, được chứ? Bạn có thể có 250 diễn đàn, hãy để tôi chỉ cho bạn. Vì vậy, Mục 1, và tất nhiên chúng ta có thể sử dụng Mục Điền cho việc đó. Tôi sẽ kéo xuống 254, như vậy. Bây giờ đó không phải là công thức, vì vậy chúng ta có thể thực hiện Tệp, Tùy chọn, Nâng cao, tiếp tục Chỉnh sửa Danh sách Tùy chỉnh và chúng ta sẽ Nhập danh sách đó, được chứ? Đây rồi, BAM! Không có vấn đề, không có thông báo lỗi. Mọi thứ đều tuyệt vời, mọi thứ đều - Nó không tuyệt vời. Nó chỉ chuyển đến Mục 234. Chờ đã, tôi biết bạn có thể có 254.Tại sao nó lại dừng ở 234? Thật kỳ lạ, thật kỳ lạ. Có chuyện gì vậy?
Vì vậy, ở đây, chúng tôi biết rằng nó chỉ đi xuống mục 234 ngay tại đó. Được rồi, bây giờ khi chúng ta nhập các Mục trong danh sách, có một số ký tự. Đã có giới hạn. Vì vậy, tôi tự hỏi nếu có một số ký tự là giới hạn ở đây = SUM (LEN trong số toàn bộ thứ đó, nhấn CTRL + SHIFT ENTER, và đó là 1764 ký tự - 234 mục. Và tôi biết rằng bạn có thể có 254, tôi đã làm điều này trước đây.
Và hãy thử điều gì đó điên rồ hơn. Được rồi, hãy thử cái này. Hãy thử thay vì mục, hãy thử một cái gì đó lâu hơn. Vì vậy, 10 ký tự một SPACE và sau đó là số 1, chúng ta sẽ đi xuống 254 hàng. Và chúng tôi sẽ thử và nhập danh sách này: vì vậy Tệp, Tùy chọn, Nâng cao, Chỉnh sửa Danh sách Tùy chỉnh, chúng tôi sẽ Nhập danh sách này. Không có thông báo lỗi. Có vẻ như nó đã hoạt động nhưng nó chỉ giảm xuống 140. Cái quái gì vậy? Giới hạn là gì? Tôi nghĩ nó có thể là 254. Vậy hãy xem, chúng ta có bao nhiêu ký tự nếu chúng ta giảm xuống 140. Được rồi, hãy để mọi thứ khác sau đó và thực tế là tôi sẽ quay lại đây với công thức này và sao chép chính xác công thức đó kết thúc. Được rồi, không.
Tại thời điểm này, tôi khá bực tức với nhóm Excel. Có gì ở đây, 1764 và đây 1852. Này, Microsoft, giới hạn là gì? Chính xác thì giới hạn là bao nhiêu? À, nhưng đây là vấn đề. Họ phải lưu trữ cái này dưới dạng một chuỗi các chuỗi được phân tách, được chứ? Vì vậy, họ đang lấy tất cả các Mục và sau đó họ thêm dấu phẩy sau mỗi mục. Được rồi, ở đây vì chúng tôi có Office 365, chúng tôi có thể sử dụng Nối văn bản mới, vì vậy = TEXTJOIN của tất cả những cái có dấu phẩy ở giữa. Tôi không biết nó có thực sự là dấu phẩy hay không. Bỏ qua điều đó, sau đó là True, dấu phẩy và các mục này. Vì vậy, chúng tôi nhận được điều đó. Và thực sự tôi chỉ muốn biết độ dài của toàn bộ điều đó. Vì vậy, độ dài là 1997 và khi tôi làm điều tương tự ở đây, 1991. Ồ! Vì vậy, rõ ràng giới hạn phải là 2000 ký tự bao gồm dấu phẩy ẩn giữa mỗi mục.
Tất cả đều khá kỳ lạ. Được rồi, vì vậy tôi luôn nghĩ đó là 254 Mục, không phải 254 Mục. Đó là 254 Mục, với điều kiện nó ít hơn 2.000 ký tự, miễn là các Mục không quá dài. Được rồi, vậy để kiểm tra lý thuyết của tôi, hãy sử dụng A khoảng trắng 1 như vậy và chúng ta sẽ lấy Fill Handle và kéo. Chúng phải thực sự đẹp và ngắn gọn bởi vì - Và chúng ta sẽ đi xuống 255, 254. Hãy chuyển đến 255 để kiểm tra nó.
Được rồi, bây giờ với điều này, nếu tôi yêu cầu Độ dài của Nối văn bản, 1421. Không có vấn đề gì cả. Vì vậy, chọn toàn bộ và Tệp, Tùy chọn, Nâng cao, cuộn xuống dưới cùng, Chỉnh sửa Danh sách Tùy chỉnh, nhấp vào Nhập. Được rồi, và tắt tất cả xuống 254. Được rồi, vì vậy, đó là 254 Mục với điều kiện nó ít hơn 2.000 ký tự bao gồm dấu phẩy ẩn sau mỗi mục là cách hoạt động.
Bạn biết đấy - nhưng quay lại vấn đề của Don ở đây. Thật khó chịu khi Hộp thoại, nếu chúng ta chỉ vào và bắt đầu nhập mọi thứ vào Hộp thoại thay vì có phần tử 2.000 ký tự, thì nó có 255 ký tự trên đó. Được rồi, vì vậy Don không có cách nào để nhập thứ này vào và khi chúng tôi thử và Nhập số, nó sẽ từ chối Nhập số. Nó nói không có thỏa thuận. Mọi thứ không phải là văn bản thuần túy sẽ không hoạt động, được chứ?
Vì vậy, một điều tôi đề nghị với Don là một giải pháp thay thế. Tôi nói, này, hãy ra đây và thêm Cột người trợ giúp và Cột người trợ giúp này sẽ là - nếu Giá trị tuyệt đối của số đó, được thôi. Và chúng tôi sẽ nhấp đúp để bắn nó xuống và sau đó những gì bạn sẽ làm là bạn sẽ sắp xếp Giảm dần theo Giá trị tuyệt đối, một cài đặt theo Giá trị tuyệt đối, được thôi. Và sau đó là 4, 6 và sau đó -6, được rồi, tất cả đều được sắp xếp lại với nhau, bạn biết đấy. Vì vậy, nó không tệ, tôi đoán những gì bạn thực sự có thể làm là bạn có thể sắp xếp theo Người trợ giúp, sau đó Thêm cấp độ và sau đó Sắp xếp theo Lề, giảm dần Lớn nhất đến Nhỏ nhất, nhấp vào OK và điều đó sẽ nhận được những gì Don đang tìm kiếm. Vì vậy, tất cả +6 sẽ hiển thị trước -6, sau đó là 8 và sau đó là -11, được thôi. Bạn biết, nhưng đây là một rắc rối. Giống như, này, Microsoft.Tại sao chúng ta phải đi đến tất cả những rắc rối này? Tại sao bạn lại để chúng tôi nhập 2.000 ký tự vào Hộp thoại hoặc thậm chí tốt hơn nữa, vì nó dường như hoạt động khi có số trong Danh sách tùy chỉnh, ý tôi là nó đang hoạt động ở đây, tại sao bạn không cho chúng tôi nhập nó?
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.
Vâng, bạn đi rồi. Don, một trong những câu hỏi điên rồ hơn mà tôi từng nghe trong Excel và chúng tôi đã phát hiện ra ít nhất 2 - Chà, chắc chắn 1 lỗi trong Excel khiến Hộp thoại Danh sách Tùy chỉnh không cho phép nhiều hơn 255 ký tự. Và điều kỳ lạ là dường như sẽ sắp xếp Danh sách tùy chỉnh theo số nhưng họ không cho phép bạn nhập số. Được rồi, tôi sẽ gọi lỗi đó là số 2. Và sau đó giải pháp này ở đây, được chứ?
Vì vậy, tôi muốn cảm ơn Don đã gửi câu hỏi đến và nếu bạn lo lắng về vấn đề này, tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2098.xlsm
