Bugün çılgın bir soru. Parça numaralarından oluşan bir sütununuz var. Parça numarasında 4 ila 7 çizgi vardır. Parça numarasının yalnızca ilk tireden sonraki kısmını ve son tireyi hariç tutmak istersiniz. Bu, düello yapan bir Excel bölümü.
Video izle
- Hedef, ilk ve son çizgiyi bulmak ve aradaki her şeyi tutmaktır
- Buradaki zor kısım son çizgiyi bulmaktır
- Fatura Yöntemi 1: Hızlı Doldurma
- İlk birkaçını manuel olarak doldurun (bazıları farklı sayıda kısa çizgi ile dahil)
- Bunun altındaki boş hücreyi seçin
- Hızlı Doldurmak için Ctrl + E
- Mike Yöntemi 2:
- Power Query kullanın
- Excel 2016'da Power Query, Excel 2016'da Al ve Dönüştür grubundadır
- Excel 2010 ve 2013'te Microsoft'tan Power Query'yi indirin. Şeritte yeni bir Power Query sekmesi oluşturur
- Verilerinizi Ctrl + T kullanarak tabloya dönüştürün
- Power Query'de Verileri Böl'ü kullanın - önce en soldaki tireyle bölmek, ardından en sağdaki tireyle bölmek için
- Fatura Yöntemi 3:
- Son tireyi bulmak için hücrenin sonundan geriye doğru yinelenen VBA İşlevi
- Mike Yöntem 4:
- N. tirenin konumunu bulmak için SUBSTITUTE kullanın
- SUBSTITUTE, bir Örnek numarası belirlemenize izin veren tek metin işlevidir
- Hangi örnek numarasını bulmak için kullanın
=LEN(A2)-LEN(SUBSTITUTE)
Video Metni
Bill: Merhaba. Tekrar hoşgeldiniz. Başka bir Düello Excel podcastinin zamanı geldi. Ben MrExcel'den Bill Jelen. (ExcelIsFun'dan Mike Girvin ile birlikte olacağım. Bu bizim - 00:03) 185 numaralı bölüm: ilkinden sonuncusuna kadar.
Peki. Bugünün sorusu Anvar tarafından YouTube'a gönderiliyor. İlkinden sonuncusuna kadar her şeyi nasıl çıkarabilirim ve burada sahip olduğu bu verilere göz atabilirim. 3, 5, 6, 7 çizgi arasında çok sayıda çizgi var, tamam mı?
Yani, ilk düşüncem, hey, ilkini bulmak gerçekten çok kolay - değil mi? = sol veya = A2 BULUNUN ORTASI ve sonra -, +1 tamam, ama sonuncusuna gelmek için -, bu başımı ağrıtacak, değil mi, çünkü, elimizde kaç tane çizgi var? Kısa çizgileri değiştirerek A2'nin SUBSTITUTE'ını alabilir ve bunun uzunluğunu, orijinal uzunluğunu karşılaştırabiliriz. Bu bana çizgi sayısını söylüyor, ama şimdi hangisini bulacağımı biliyorum - 2., 3., 4., 5., peki FIND'ı mı kullanıyorum?
VBA'ya gitmeye hazırdım değil mi? Bu benim diz çöküşüm tepkim. Bir saniye bekle dedim. Anvar dedim, Excel'in hangi sürümdesin? Excel 2016'dayım diyor. Çok güzel dedim. Excel 2013 veya daha yenisindeyseniz, flash dolgu adı verilen bu harika yeni özelliği kullanabiliriz. Flaş dolgusu ile, ona sadece bir desen vermeliyiz ve ona yeterince bir desen vereceğim, böylece sadece iki tire ile bir tane alıp bunu birkaç kez yapmam değil. Bu şekilde birkaç farklı çizgim olduğundan emin olmak istiyorum. Excel ekibinden Chad ne aradığımı biliyor. Chad, flaş dolgu mantığını yazan adam. Böylece, orada yaklaşık 3 tane buluyorum ve sonra CONTROL + E, DATA ve ardından FLASH FILL kullanmak için kısayol ve elbette, doğru olanı yapmış gibi görünüyor. Pekala, Mike.Bakalım neye sahipsin.
Mike: Teşekkürler MrExcel. Evet. Flash dolgu kazanır. Buradaki özellik, flash dolgu, tek kelimeyle şaşırtıcı olan modern Excel araçlarından biridir. Tek seferlik bir anlaşma ise ve tutarlı bir modeliniz varsa, hey, ben böyle yapardım.
Hey, bir sonraki sayfaya geçelim. Şimdi, flash dolgu kullanmak yerine, aslında güçlü sorgu kullanabiliriz. Şimdi Excel 2016 kullanıyorum, bu yüzden GET & TRANSFORM grubuna sahibim. Bu güçlü sorgu. 2013'ün önceki sürümlerinde (10 - 2:30), aslında ücretsiz güç sorgusu eklentisini indirmeniz gerekiyordu.
Şimdi, güç sorgusunun çalışması için bunun bir Excel tablosuna dönüştürülmesi gerekir. Şimdi, yine, bu tek seferlik bir anlaşma olsaydı, flash dolgu kullanırdım. Güçlü sorguyu ne zaman kullanırsınız? Pekala, gerçekten büyük veriniz varsa veya harici bir kaynaktan geliyorsanız, gitmenin yolu bu olabilir veya bunu flash doldurma için 3 veya 4 örnek yazmaktan daha çok sevebilirsiniz çünkü güçlü sorgu ile, özellikle ilkini bul - ve sonunu bul - deyin.
Şimdi, bunu bir Excel tablosuna dönüştüreceğim. Tek bir hücre seçtim, her yerde boş hücreler var. INSERT, TABLE'a gidiyorum veya klavyeyi kullanıyorsunuz, CONTROL + T. Tamam veya ENTER'a tıklayabilirim. Bu tabloyu adlandırmak istiyorum, bu yüzden MASA ARAÇLARI, TASARIM'a, ÖZELLİKLER'e gideceğim. Bu STARTKEYTABLE ve ENTER'ı arayacağım. Şimdi DATA'ya geri dönebilirim, FROM TABLE düğmesini kullanarak onu power query'ye getirebilirim. Köşem var. Adı var. Çıktı Excel'e aktarılacağı için bu adı korumak istemiyorum ve ona farklı bir isim vermek istiyorum. Bu yüzden ona CLEANEDKEYTABLE adını vereceğim. O DEĞİŞTİRİLMİŞ TİPE ihtiyacım yok. Ben sadece kaynağa bakıyorum. Şimdi sütuna tıklayabilirim ve hemen HOME'da SPLIT düğmesi var. DELIMITER TARAFINDAN SPLIT diyebilirim. Zaten tahmin edilmiş gibi görünüyor. BEN'm SOL-EN ÇOK diyeceğim. Tamam'ı tıklayın.
Şimdi, buraya bakarsam DEĞİŞTİRİLMİŞ TİP görüyorum. Buna ihtiyacım yok, bu yüzden o adımdan kurtulacağım. Yalnızca DELIMITER TARAFINDAN SPLIT COLUMN'um var. Şimdi, bunu tekrar yapacağım ama buradaki SPLIT düğmesini kullanmak yerine, DELIMITER TARAFINDAN SPLIT COLUMN'a sağ tıklayın ve şuna bakın. Onu DOĞRU-EN DELIMITER ile bölmeyi seçebiliriz. Tamam'ı tıklayın. Şimdi, bu iki sütuna ihtiyacım yok, bu yüzden tutmak istediğim sütunu sağ tıklayacağım, DİĞER SÜTUNLARI KALDIR. Aslında bu DEĞİŞTİRİLMİŞ TÜRÜ X çıkaracağım. BUNU SİLMEK İSTEDİĞİNİZDEN EMİN MİSİNİZ? Evet, SİL diyeceğim. Temiz verilerim var.
Şimdi CLOSE & LOAD'a gelebilirim. KAPATIN VE YÜKLEYİN. Bu, yeni İTHALAT iletişim kutusudur. Eskiden LOAD TO yazıyordu ama ben onu bir MEVCUT ÇALIŞMA SAYFASINDA bir masaya yüklemek istiyorum. Daralt düğmesini tıklayın. C1'i seçeceğim, ayrıştıracağım, Tamam'a tıklayacağım ve işte başlıyoruz. Verilerimizi temizlemek ve yalnızca istediğimiz verileri elde etmek için güçlü sorgu. Peki. Onu geri atacağım.
Bill: İşte burada bir nokta var, güçlü sorgudaki harika özelliklerden biri olan DELIMITER TARAFINDAN SPLIT SÜTUNUNDA SAĞ-EN DELIMITER. Bu harika.
Peki. Benim diz çöküşü tepkim - VBA UDF (anlaşılmaz - 05:34) VBA yapmak gerçekten çok kolay. ALT + F11'e geçin. BİR MODÜL TAKIN. Bu modülde bu kodu yazın. Ben (bir - 05:43) yepyeni bir fonksiyon oluşturacağım, buna MIDPART diyeceğim ve ona bir metin ileteceğim ve sonra yapacağım şey şu o hücredeki son karakterden MYTEXT'in uzunluğundan 1, ADIM -1'e dönecek ve o karaktere bakacak. Öyleyse, MYTEXT'in ORTASI, bu i değişkeni, bize 1 uzunluğunda hangi karakteri aradığımızı söyler. Bu bir - mi? Bir - bulduğum anda, karakter i - 1'den başlayarak MYTEXT'in SOLUNU alacağım, bu yüzden sonuncusu için her şeyden kurtulacağım - sonuna kadar ve sonra gitmediğimden emin ol daha fazla çizgi aramaya devam edin, EXIT FOR beni bu (anlaşılmaz - 06:17) döngüden çıkaracak,ve oradan işin kolay kısmı. Sadece MYTEXT'i alacağız, MYTEXT'in ORTASINDA başlayacağız, (burada - 06:26'yı kullanıyorum) ilkini bulmak için FIND işlevini kullanacağız -, bundan 1 tane daha git ve geri dön.
Öyleyse, Excel'e dönmek için ALT + Q'ya geri dönelim. = Bunun MIDPART sekmesi ve çalışıyor gibi görünüyor. Onu kopyalayın. Mike, başka var mı? (= MIDPart (A2))
Mike: Bir tane daha var ama bu uzun bir formül olacak - o UDF kadar kısa değil. Pekala, bir sonraki sayfaya geçelim. Şimdi, bir formül yapacaksak ve bir metne sahipsek ve her zaman farklı sayıda sınırlayıcı varsa, bir şekilde, o son sınırlayıcının konumunu almalıyım.
Şimdi, bu birkaç adım atacak ama ben SUBSTITUTE işleviyle başlayacağım. O metne bakacağım, bulmak istediğim eski metin içinde ”, yani - ve onun yerine veya yerine ne koymak istiyorum? "". Bu hiçbir şey koymaz. Şimdi, eğer ben) ve CONTROL + ENTER ise, bu ne yapacak? (= SUBSTITUTE (A2; "-", ""))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
İyi gidiyorsun. Uğradığınız için herkese teşekkür etmek istiyorum. Bir dahaki sefere başka bir Dueling Excel podcast'i ve ExcelIsFun için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Duel185.xlsm
