Excel DÜŞEYARA tüm sonuçları döndürüp arada virgülle birleştirebilir mi?
Video izle
- Amaç, bir DÜŞEYARA'daki tüm metin yanıtlarını birleştirmektir.
- Bill'in yöntemi: GetAll adlı bir VBA İşlevi kullanın
- Yinelenenleri Kaldır kullanılarak benzersiz liste
- Mike'ın yöntemi:
- Gelişmiş Filtre kullanan benzersiz liste
- Office 365'e METİNBİRLEŞTİR işlevi eklendi
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- IF işlevi nedeniyle, formülü her düzenlediğinizde formül Ctrl + Shift + Enter gerektirir
- Alt AQOR Enter, Gelişmiş Filtreyi yeniden çalıştıracaktır!
Video Metni
Bölüm 183: Tüm DÜŞEYARA Maçlarına Katılın
Bill Jelen: Hey, tekrar hoş geldiniz. Başka bir Düello Excel Podcast zamanı. Ben Bill Jelen'den, Excel Is Fun için Mike Girvin'e katılacağım. Bu, Bölüm 183: Tüm DÜŞEYARA Maçlarına Katılın.
(Müzik)
Pekala, Matt'ten bugünün sorusu. DÜŞEYARA tüm sonuçları döndürebilir ve her biri arasında virgül boşluk bırakarak bunları birleştirebilir. Örneğin, buradaki bu ikisi 109876, Low Oil virgül boşluğu döndürebilir mi, Checked on 12/12. Ve tabii ki daha fazlası olsaydı, daha çok geri dönerdi. Pekala, buradaki çözümüm biraz VBA kullanıyor olacak. Pekala, xlsm olarak kaydedildiğinden emin olun veya VBA veya xlsb'yi çalıştıramazsınız, ancak xlsx'i çalıştıramazsınız - xlsx, VBA'yı çalıştıramayan tek dosyadır. Alt + F11 tuşlarına basacağız, Dual183'te veya çalışma kitabınızın adı her neyse üzerinde olduğunuzdan emin olacağız. Modülü boş modüle ekleyin ve bu kodu yapıştıracağız, pekala.
GetAll fonksiyonuna bir göz atalım ve işte aradığımız kimlik numarası ve sonra bakmak istediğimiz aralık. Ve başlayalım, GetAll adında bir değişken döndüreceğiz, bu yüzden bunun boş boşluğa eşit olmasıyla başlarız. Aralığımdaki her hücre için, Aradığımız Hücre Değeri ise GetAll = GetAll & “” ve ardından Cell.Offset (0 satır, 1 sütun), başka bir deyişle değeri alacağız. bu kimlik numarasının hemen yanında çünkü VBA'ya geri döndüğünüzde, işte kimlik numarası. Eşleşen ID numarasını bulursak 1 sütun üzerine gitmek isteriz. Şimdi, ya 2 sütun veya 3 sütun üstüne gitmek isteseydiniz, o zaman bu 0 satırı ve 1 sütunu 2 olacak şekilde değiştirirsiniz. Pekala, ayrıca kontrol edin - eğer bu ise virgül boşluğu koymazsak ilki.Öyleyse GetAll değişkeni şu anda "" ise virgül boşluğunu koymayacağız, tamam mı?
Şimdi burada bu işleve sahip olduğumuza göre, Matt'in problemini çözmenin ne kadar kolay olduğuna bakın. Buraya geleceğiz ve onun kimliklerini, Ctrl + C alalım ve Ctrl + V'yi bu şekilde yapıştıralım. Veriler, Yinelenenleri Kaldır, Tamam'ı tıklayın. Yani benzersiz bir kimlik listesi var ve sonra = getall demek istiyoruz ve bu değeri E2 virgülle arıyoruz. Buradaki aralığa bakınca F4'e basacağım. F4, normal bir işlev gibi çalışır. Ve yine Matt'in sorusunu ortadan kaldırarak, bunu düşürmek için çift tıklayın. Çalışacak.
Ve deneyelim, burada çılgınca bir şey deneyelim. Hadi bir İfade 1 yapalım ve sadece İfade 1'den 10'a kadar bir grup koyalım. Tüm bunları 109999'a imzalayacağız. Yapıştır ve sonra buraya yapıştır. Bu formülü aşağıya kopyalayın, formülü düzenleyin, böylece elbette sonuna kadar gidecek. Evet. Ve tüm bu İfadeleri döndürecektir. Pekala, bu benim çözümüm, VBA, orada küçük bir işlev. Mike, bakalım neyin var.
Mike Girvin: Teşekkürler. GetAll, bu harika bir VBA işlevi. Pekala, tam şuradaki sayfaya geçeceğim. Bunu zaten bir Excel tablosuna dönüştürdüm, böylece aşağıya kayıt eklediğimizde, umarım işler güncellenir.
Şimdi bunu iki parça halinde yapacağım ilk şey. Burada benzersiz bir liste çıkarmak için bir formül yapabilirim ancak başka bir seçeneğe bakmak istiyorum: Gelişmiş filtrede benzersiz bir liste ayıklama seçeneği vardır ve güncellenebilir. Gelişmiş Filtrenin üzerine sadece Kimlik sütun verilerini vurgulayacağım veya Alt, A, Q klavyesini kullanacağım. Şimdi, Filtre listesi yerinde, hiçbir şekilde. Onu başka bir yere kopyalamak istiyorum. Yalnızca A sütununu aldı ve daha sonra genişleyecek bir Excel tablosu olduğu için. Herhangi bir kriterim yok, bunu D1'e kopyalamak ve sadece Benzersiz kayıtları kontrol etmek istiyorum. Tamam'ı tıklayın.
Şimdi buraya geleceğim, Tüm Yorumlar girecek ve sadece Excel 2016 Office 365'te çalışan bir işlev kullanacağım: = METİNBİRLEŞTİR işlevi. Bu işlev tek başına Excel'in en son sürümünü almaya değer. Bu, insanların yapmak istediği, birçok şeyi bir araya getirdiği ortak bir görevdir. Şimdi "," içindeki sınırlayıcımız ve bu işlevle ilgili harika olan şey, ona boş hücreleri yoksay diyebilmemizdir. Şimdi, DOĞRU, 1 veya Bırak, Atlayabilirim. Öyleyse, bırakacağım, Atlayacağım. Ve işte metnimize ihtiyacımız olan yer. EĞER işlevini filtrelemek ve yalnızca istediğimiz öğeleri elde etmek için kullanacağız. Burada bu sütunun tamamına bakın diyeceğim: Tablo Adı ve sonra Alan adı () içinde, herhangi biriniz = bu göreceli hücre referansına göre, bu mantıksal testtir. Bunu tıklayıp değerlendirmek için F9 tuşuna basarsam,şu anda sadece 2 TRUES'e sahip olduğumuzu görebiliyordunuz, Ctrl + Z şimdi virgül yazıyorum ve Trues and Falses dizisiyle şimdi seçmesi gereken öğeleri verebilirim. Şimdi, bu aralıktan sadece burada DOĞRU olan öğeleri seçeceğiz. Virgül ve ben "" - bu, METİNBİRLEŞTİRME'deki ikinci bağımsız değişkenle ilgili olarak boş bir hücre olarak görünecek.
Now, I'm going to close parenthesis and now the IF function will create that string of Trues and Falses, the actual items from this range will be picked up if it sees it True and all the other items will have that empty cell. And guess what? TEXTJOIN will totally ignore all of those empty cells and return just the items that match this ID, and then join it with that delimiter. Now this is definitely an Array formula that requires the special keystroke Ctrol+Shift+Enter. The logical test argument holds our Array operation and that argument cannot calculate this Array operation correctly unless we use the keyboard Ctrl+Shift+Enter. Now I'm going to close parentheses. Actually we could prove 1 right here in Text 1 if I F9 all this, we could see we get the 2 items, the rest of those empty cells will be ignored. Ctrl+Z. Now, let's enter this into the cell with Ctrl+Shift+Enter. Immediately look up to the Formula Bar. Those curly brackets are Excel telling you it understood and calculated this as an Array formula. Now I can double-click and send it down. That is looking good.
I'm going to go to the last cell and hit F2 to verify that all the ranges are looking correctly. Now what I don't want to do is I don't want to hit Enter because that formula after we put it in Edit Mode will only calculate correctly if we use Ctrl+Shift+Enter; or, because we already entered the formula, we can just use the Esc key to revert back to whatever's in the cell before we put it in Edit Mode.
Now, let's test this. I'm going to click in the last cell down here and hit Tab and then type a new ID, Tab, Tab. Another new record, Tab, and I can already see I didn't have enough work here. I am, we're going to put - Perfect and then Enter. Now, this isn't going to automatically update like if we have a bunch of formulas that we’re counting unique items and then extracting unique items, but no problem. Watch this. We can update this list of unique records because we used Advanced Filter and it doesn't matter what cell you start from either because when Advanced Filter is invoked, it memorizes the extract range and the ranges it was originally looking at. You can click on Advanced Filter or use the keyboard Alt+A+Q. We do have to select Copy to another location, but look at that. It totally remembered and expanded to A13 because of the Excel Table feature. It remembered the extract range. I do have to check Unique records only but click OK.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh hey, uğradıkları için herkese teşekkür etmek istiyorum. Bir dahaki sefere başka bir Dueling Excel podcast'i ve Excel Is Fun'da görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Duel183.xlsm
