Excel'deki özel listeleri seviyorum. Doldurma tutamacı ve verileri farklı bir sıraya göre sıralamak için harikadırlar. Özel Listeler 254 öğeye izin vermelidir. Ancak bazı nedenlerden dolayı, bir okuyucu Excel'in yalnızca ilk 38 maddeyi tuttuğu bir durumla karşılaşıyor! Bu gizemin derinliklerine ineceğiz.
Video izle
- Don, sayısal olan özel bir listeye göre sıralamak istiyor!
- Bu işe yarayacak mı? İşe yarıyor gibi görünüyor!
- Ancak, sayısal hücreleri özel liste iletişim kutusuna aktaramazsınız.
- Bu nedenle, Özel Liste iletişim kutusuna sayılar yazmayı deneyin…. Yazarken aptalca bir 255 karakter sınırlamasıyla karşılaşıyorsunuz.
- Limit WTH nedir? 254 öğe? Aha - 254 öğe, ancak her öğe arasına görünmez virgül eklediğinizde 2000 karakterden az
=SUM(LEN())Ctrl + Shift + Enter ile bazı metin hesaplamaları yaptım veLEN(TEXTJOIN(",",True,Range))- Don için bu özel durumda sıralama için ABS ile geçici çözüm
- Ancak en iyi çözüm… Don'un yapması gereken şey:
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2098: Özel Liste Sınırları.
Bu, Don S'nin Mac 2011'i kullandığında tuhaf bir durum, bu yüzden Excel'in gerçek sürümünü bile kullanmıyoruz. Excel'in sahte sürümünü kullanıyoruz, Özel Listeye göre sıralamaya çalışıyoruz, ancak listedeki yalnızca ilk 38 öğeyi kabul ediyor. Bunun yanlış olduğunu biliyorum çünkü Excel listedeki 254'e kadar öğeyi işleyebilir veya en azından ben öyle düşündüm. Pekala, Don'un Oyuncu Adı, Galibiyet Sayısı ve ardından Marj var, örneğin o zaman skordan ne kadar uzakta olduğu gibi.
Yani doğru sıra, mükemmel puan 0 ve sonra 1 üst, 1 alt ve 2, 2 altı, 3 üst, 3 altı vb. Ve Don, Margin sütununu bu Özel Listeye göre sıralamaya çalışıyor. Şimdi, bunu hiç denemedim ama, hey, yapması kolay olmalı. İşte burada Uygun Sıra: 0, 1 ve sonra formül eksi bizden hemen önceki değer ve sonra = + 1'in üzerindeki 2 sayısı. Pekala, şimdi bu iki formüle sahip olduğuma göre, 201'de çok uzağa gidebilirim ama sorun değil. 99 ve -99'a kadar ihtiyacımız olan tam sıraya sahip olmalıyız. İşte mükemmel cevaplarımız var. Bunu kopyalayacağım, böylece Ctrl + C kopyalayacak ve ardından Değerler Olarak Yapıştır. Bu değerleri böyle yapıştırın. Pekala, bu yüzden bunu halledecek bir Özel Liste ayarlayabilmeliyim, değil mi? Sorun değil.Bu yüzden Dosya, Seçenekler, Gelişmiş'e gidiyoruz,% 83'ü tamamen aşağı kaydırıyoruz, Özel Listeleri Düzenle'yi seçiyoruz ve listemizi İçe Aktaracağız. Ne? Basit metin içermeyen hücreler göz ardı edildi. Sayılarla dolu bir Özel Listenizin olmasına izin verilmiyor mu? Ama Don bunun ilk 38 için çalıştığını söylüyor. Bununla ne alakası var? Bunu bir süredir düşünmedim. Don'un ithal etmeye çalışmadığını anladım; bu numaraları İletişim Kutusuna yazmış olmalı.Don'un ithal etmeye çalışmadığını anladım; bu numaraları İletişim Kutusuna yazmış olmalı.Don'un ithal etmeye çalışmadığını anladım; bu numaraları İletişim Kutusuna yazmış olmalı.
İşte yapacağım şey şu. Hepsini kopyalamak için Ctrl + C yapacağım, bu Ctrl + V gibi Not Defteri'ne gidip Not Defteri'ne Yapıştır ve ardından her şeyi seçeceğim: Düzenle, Tümünü seç ve Ctrl + C, geri dön Excel, Dosya, Seçenekler, Gelişmiş,% 83 tamamen aşağı, Özel Listeleri Düzenle ve bu listeyi Ctrl + V gibi buraya yazacağım. Pekala, hepsi işe yarıyor ama Ekle'yi tıkladık, Özel Liste için maksimum uzunluk aşıldı. Yalnızca ilk 255 karakter kaydedilecektir. Ve buna baktığınızda, kesinlikle, sadece 38, -38, 39 ve ardından BAM'a inen bir Özel Liste oluşturuyorlar! Son 3, değil mi?
Yani bu çok tuhaf. Aslında sayılarla bir Özel Liste oluşturmama izin veriyorlar ama 255 almama izin vermiyorlar. Yani işe yarıyor. İşe yarıyor ve sonra gerçekten burada dener ve sıralarsak; Bu nedenle, bu Özel Listede Veri, Sırala ve Sırala diyeceğiz, yalnızca 39'a gidecek olan Tamam'a tıklayın, Tamam'a tıklayın. Listedeyse, doğru sıralar. Pekala, yani pozitif +6 şovu -6'dan önce; ama sonra 39'un ötesinde bir şeye indiğimizde, listede olmayan sıralar halinde sıralanacak, yani burada en küçükten en büyüğe gidiyor. Yani 67 puanla kaçırılan bir kişi +42 puanla kaçırılan birinden daha iyidir. Bu tamamen saçma.
Pekala, bu sadece 38'e gidiyor mu? Şimdi, eminim diğer sayılar da var, bu yüzden, sanırım 30'lara kadar inecek. Orayı sevmeye başlıyoruz, değil mi? Veya orada, o ikisinden biri. Peki bu nedir? Bu toplam 78 maddedir. Ve hey, izin verdiklerini biliyorum - 250 forum var çünkü seminerlerimde sürekli müşterilerden bahsediyorum, tamam mı? 250 forumunuz olabilir, size göstereyim. Öyleyse Öğe 1 ve tabii ki bunun için Doldurma Öğesini kullanabiliriz. Bunun gibi 254'e sürükleyeceğim. Şimdi bunlar formül değil, bu yüzden Dosya, Seçenekler, İlerleme yapabilmeliyiz, Özel Listeleri Düzenle'ye devam etmeliyiz ve bu listeyi içe aktaracağız, tamam mı? İşte orada, BAM! Sorun yok, hata mesajı yok. Her şey harika, her şey - Harika değil. Sadece Madde 234'e gidiyor. Bekle, 254 alabileceğini biliyorum.Neden 234'te duruyor? Bu tuhaf, bu tuhaf. Buna ne oluyor?
Yani burada, sadece 234. maddeye kadar gideceğini biliyoruz. Tamam şimdi, listedeki Öğeleri yazarken birkaç karakter var. Sınır vardı. Bu yüzden burada sınır olan birkaç karakter var mı merak ediyorum = TOPLA (tüm bu şeylerin UZUNLUĞU, CTRL + SHIFT ENTER tuşlarına basın ve 1764 karakter - 234 öğe. Ve 254'e sahip olabileceğinizi biliyorum. bunu daha önce yaptım.
Ve daha çılgın bir şey deneyelim. Pekala, bunu deneyelim. Öğe yerine deneyelim, daha uzun bir şey deneyelim. Yani 10 karakter bir SPACE ve sonra 1 numara, 254 satır aşağı ineceğiz. Ve bu listeyi içeri aktarmayı deneyeceğiz: bu yüzden Dosya, Seçenekler, Gelişmiş, Özel Listeleri Düzenle, bu listeyi İçe Aktaracağız. Hata mesajı yok. İşe yarıyor gibi görünüyor ama sadece 140'a düşüyor. Bunda ne var? Limit nedir? 254 olabileceğini düşündüm. 140'a düşersek kaç karakterimiz olduğunu görelim. Pekala, bundan sonra her şeyi bırakalım ve aslında buraya bu formüle gelip aynı formülü kopyalayacağım. bitmiş. Pekala, hayır.
Bu noktada, Excel ekibinden oldukça bıktım. Ne haber, burada 1764 ve burada 1852. Hey, Microsoft, sınır nedir? Tam olarak sınır nedir? Ah, ama olay şu. Bunu bir dizi sınırlandırılmış dizge olarak saklıyor olmalılar, tamam mı? Yani tüm Öğeleri alıyorlar ve her birinin arkasına virgül ekliyorlar. Pekala, burada Office 365'e sahip olduğumuz için, yeni Metin Birleştirme özelliğini kullanabiliriz, yani arada virgül olanların hepsinden TEXTJOIN. Gerçekten virgül olup olmadığını bilmiyorum. Bunu, ardından Doğru, virgül ve bu öğeleri göz ardı edin. Yani, bunu anlıyoruz. Ve aslında sadece tüm bunların uzunluğunu bilmek istiyorum. Yani uzunluk 1997 ve burada aynı şeyi yaptığım zaman, 1991. Oh! Dolayısıyla, açıkça sınırın her öğe arasında görünmez bir virgül dahil olmak üzere 2000 karakter olması gerekir.
Bunların hepsi oldukça tuhaf. Pekala, bu yüzden her zaman 254 Öğe olduğunu düşündüm, 254 Öğe değil. Öğeler çok uzun değilse, 2.000 karakterden az olması koşuluyla 254 Öğedir. Pekala, teorimi test etmek için, bunun gibi bir boşluk 1 kullanalım ve Dolgu Tutamacını tutup sürükleyeceğiz. Bunlar gerçekten güzel ve kısa olmalı çünkü - Ve 255, 254'e ineceğiz. Test etmek için 255'e gidelim.
Pekala, şimdi bununla, Metin Birleştirme Uzunluğunu sorarsam, 1421. Hiç sorun değil. Her şeyi ve Dosya, Seçenekler, Gelişmiş'i seçin, en alta doğru kaydırın, Özel Listeleri Düzenle, İçe Aktar'ı tıklayın. Pekala, ve 254'e kadar tamamen kapatın. Pekala, her öğeden sonra görünmez bir virgül dahil olmak üzere 2.000'den az karakter olması koşuluyla 254 Öğedir.
Biliyorsun, yani - ama burada Don'un sorununa dönelim. Diyalog Kutusunun 2.000 karakterlik bir eleman yerine Dialog Box'a bir şeyler yazmaya başlasak, üzerinde 255 karakter olması kesinlikle can sıkıcıdır. Pekala, Don'un bu şeyi yazmanın bir yolu yok ve sayıları içe aktarmayı denediğimizde, sayıları içe aktarmayı reddediyor. Anlaşma yok diyor. Düz metin olmayan hiçbir şey işe yaramayacak, tamam mı?
Bu yüzden, Don'a önerdiğim tek şey alternatif bir çözüm. Diyorum ki, hey, hadi buraya gelip bir Yardımcı Sütun ekleyelim ve bu Yardımcı Sütun - eğer bu sayının Mutlak Değeri, pekala. Ve bunu düşürmek için çift tıklayacağız ve sonra yapacağın şey sadece Azalan Mutlak Değere göre sıralayacaksınız, bir ayar Mutlak Değer, pekala. Ve sonra 4, 6 ve sonra -6, tamam, bunların hepsi bir araya getirildi, biliyorsun. Bu yüzden fena değil, sanırım gerçekten yapabileceğiniz şey, Yardımcıya göre sıralayabilir ve ardından Bir Seviye Ekleyebilir ve ardından En Büyükten En Küçüğe doğru azalan Marjin'e göre Sıralayabilir, Tamam'a tıklayıp Don'un aradığı şeyi elde edebilirsiniz. Yani, +6'nın tamamı -6'dan önce ve sonra 8 ve sonra -11, tamam. Biliyorsun, ama bu bir güçlük. Merhaba, Microsoft.Neden bu kadar uğraşmak zorundayız? Neden İletişim Kutusuna 2.000 karakter yazmamıza izin veriyorsunuz veya daha da iyisi, Görünüşe göre Özel Listede numaralar olması için çalışıyor, yani burada çalışıyor, neden içeri aktarmamıza izin vermiyorsunuz?
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.
İyi gidiyorsun. Don, Excel'de duyduğum en çılgın sorulardan biri ve en az 2 - Kesinlikle Excel'de, Özel Liste İletişim Kutusu'nun 255 karakterden fazlasına izin vermeyeceği konusunda 1 hata. Ve sonra görünüşe göre bir Özel Listeyi numaralara göre sıralayacak, ancak sayıları içe aktarmanıza izin vermeyecekleri bu tuhaf şey. Pekala, bu hatayı 2 olarak arayacağım. Ve sonra bu geçici çözüm, tamam mı?
Hey, soruyu gönderdiği için Don'a teşekkür etmek istiyorum ve bu kadar uzun süre takılırsan uğradığın için teşekkür ederim. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2098.xlsm
