Gelen sorulardan bazıları oldukça zor. Bugün bir hücre sütunumuz var. Her hücrede bazı kelimeler, sonra bir tarih ve daha sonra birkaç kelime vardır. Amaç, bu metnin tarih kısmını yeni bir sütuna çekmektir. Bu, Bill ve Mike'tan fikirlerin yer aldığı bir düello bölümü.
Video izle
- Bill'in süper geniş yaklaşımı:
- 12 ayın tamamını ayrı sütunlara koyun
- Bu ayın orijinal metinde olup olmadığını görmek için FIND işlevini kullanın
- Minimum başlangıç konumunu bulmak için = AGGREGATE (5,6,…
- Aydan önce 2 veya 3 numaralı pozisyonları aramak için birkaç ekstra formül
- Mike'ın yaklaşımı:
- BUL yerine ARA işlevini kullanın. Bul büyük / küçük harfe duyarlıdır, Arama değildir.
- B13: B24'ü Find_Text olarak belirterek bir işlev bağımsız değişken dizisi işlemi oluşturun.
- Formül # DEĞER! Hata, ancak F2, F9 tuşlarına basarsanız, bir dizi döndürdüğünü göreceksiniz.
- AGGREGATE içindeki ilk 13 işlev bir diziyi işleyemez, ancak işlevler 14-19 bir diziyi işleyebilir.
- 5 = MIN ve 15 = SMALL (, 1) benzer, ancak SMALL (, 1) bir dizi ile çalışacaktır.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX ve AGGREGATE işlev dizisi bağımsız değişkenlerini Ctrl + Shift + Enter olmadan işleyebilir
- Mike, Başlangıçtan önceki 2 karakterin bir sayı olup olmadığına bakarak ve daha önce 3 karakteri kaparak daha akıllıydı. Fazla alan TRIM () tarafından ortadan kaldırılır
- Başlığı almak için, C sütunundaki Tarih metninden kurtulmak için SUBSTITUTE işlevini kullanın.
Video Metni
Bill Jelen: Hey, tekrar hoş geldiniz. Başka bir Düello Excel Podcast zamanı. Ben Bill Jelen'liyim. Ben de Excel Is Fun'dan Mike Girvin'e katılacağım.
Bu Düellomuz # 170: Tarihleri Bulmak
Herkese tekrar hoş geldiniz. Burada çok güzel bir sorum vardı ve çözemedim. En azından kolay çözemedim, bu yüzden Mike Girvin'e gittim ve dedim ki "Mike, hey, bunu yapmanın bir yolu var mı?" “Evet, bunu yapmanın bir yolu var. Hadi bir Düello yapalım. "
Dolayısıyla, YouTube'daki bir kişi bu verileri gönderdi ve genel olarak her hücrede bir belge başlığı ve ardından bir tarih gibi bir şey var. Bu veriyi belge başlığına ayırmak istediler: ne, ne var ve sonra tarih. Ancak tarihler tamamen kötü. Burada olduğu gibi, 20 Ocak; ama aşağıda, tarihin hücreden sonra olabileceği şeyler var, 9 Nisan. Pekala, hangi şekilde olursa olsun onu bulmak istiyoruz. Ve bazen iki tarih vardır ve bu tamamen korkunçtur ve bu çok karışık bir tarih durumu ve mümkün olduğu kadar, bir randevu bile gösterme, pekala. İşte benim girişimim. Sağ tarafa, aradığım şeyleri koyacağım. Burada gerçekten sevdiğim şey, ay adını asla kısaltmamış olmaları. Ben gerçekten,gerçekten minnettarım. O yüzden Ocak ayında yazın ve buraya bu şekilde Aralık'a sürükleyeceğim ve bilmek istediğim her hücre için şunu bulabilir miyiz = BUL o Ocak. Öyleyse F4'e bir, iki kez basarak onu sadece bir satıra kilitleyeceğim, oradaki A Sütunundaki metinde, bunun gibi. Tamam, sütuna kilitlemek için F4'e bir, iki, üç kez basacağım. Ve burada, bize Ocak'ın 32. pozisyonda bulunduğunu söylüyor ve diğer 11 ay boyunca bize hiç bulunmadığını söyleyecek. Başka bir deyişle, şimdi Değer hatasını alıyoruz. Orada yapmam gereken şey bulmam gerekiyor, tüm değer hatalarını göz ardı ederek minimum değeri bulmam gerekiyor. Öyleyse, bu küçük formülü burada gösterin = AGGREGATE ve bunu sıfırdan inşa edelim, = AGGREGATE, istediğimiz şey MIN, yani bu 5 sayısı,ve sonra 6 numaralı virgül hata değerlerini ve ardından Ocak'tan Aralık'a kadar bu hücrelerin tümünü yoksayın. Ve bize anlatacak olan şey, bize ayın nerede olduğunu söyleyecek. Ve bu durumda, 0 alacağız, diyelim ki ay hiç olmaz.
Pekala şimdi, geri kalanını gösterelim. Yani, burada 20 Ocak veya 1 Kasım'ın olduğu durumu halletmek için yapacağım ilk şeyin, o ayın nerede başladığına bakacağım ve iki hücre, iki hücre, iki karakter geriye gideceğimi söyledim. , iki karakter. Ve bunun bir sayı olup olmadığına bakın, öyle değil. Bu benim sütunum Adjust2 olarak adlandırılıyor. Ayarla2. Ve işte yapacağımız şey. Diyorum ki, A2'nin MID'sini al, G2-2'de 1 uzunluğunda olduğu yerden başlayın, ona 0 ekleyin ve bu bir sayı mı değil mi? Pekala, bu da bir sayı. Ve sonra, 2 basamaklı bir tarih olan durumu da arayacağız, yani 20 Ocak. Yani buna Adjust3 deniyor, yerden 3 karakter geriye gidin. Yani burada, 1 uzunluğu için üç karakter geri gidin, ona 0 ekleyin ve bakalım 'sa numara, tamam mı? Sonra ayarlayacağız ve Ayarlanmış Nerede EĞER diyor. Eğer bu garip durum 0 ise, gerçekten büyük bir değer 999 koyacağız; aksi takdirde, G2'den gideceğiz ve ya Ayar3 Doğru ise 3'e ya da Ayar2 Doğru ise 2'ye ya da bunların hiçbiri Doğru değilse, ayın başladığı yer Nerede olacak. Pekala, şimdi Ayarlanmış Nerede olduğunu bildiğimize göre, onu kopyalamak için çift tıklayacağız. Pekala, hey şimdi, gerçekten çok kolay. Sadece - Başlık için A2'nin solundan kaç karakter istediğimizi söyleyeceğiz. D2-1'i istiyoruz çünkü bu -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.Eğer bu garip durum 0 ise, gerçekten büyük bir değer 999 koyacağız; aksi takdirde, G2'den gideceğiz ve ya Ayar3 Doğru ise 3'e ya da Ayar2 Doğru ise 2'ye ya da bunların hiçbiri Doğru değilse, ayın başladığı yer Nerede olacak. Pekala, şimdi Ayarlanmış Nerede olduğunu bildiğimize göre, onu kopyalamak için çift tıklayacağız. Pekala, hey şimdi, gerçekten çok kolay. Sadece - Başlık için A2'nin solundan kaç karakter istediğimizi söyleyeceğiz. D2-1'i istiyoruz çünkü bu -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.Eğer bu garip durum 0 ise, gerçekten büyük bir değer 999 koyacağız; aksi takdirde, G2'den gideceğiz ve ya Ayar3 Doğru ise 3'e ya da Ayar2 Doğru ise 2'ye ya da bunların hiçbiri Doğru değilse, ayın başladığı yer Nerede olacak. Pekala, artık Ayarlanmış Nerede olduğunu bildiğimize göre, onu kopyalamak için çift tıklayacağız. Pekala, hey şimdi, gerçekten çok kolay. Sadece - Başlık için A2'nin solundan kaç karakter istediğimizi söyleyeceğiz. D2-1'i istiyoruz çünkü bu -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.veya bunların hiçbiri Doğru değilse, ayın başladığı yer Nerede olacaktır. Pekala, artık Ayarlanmış Nerede olduğunu bildiğimize göre, onu kopyalamak için çift tıklayacağız. Pekala, hey şimdi, gerçekten çok kolay. Sadece - Başlık için A2'nin solundan kaç karakter istediğimizi söyleyeceğiz. D2-1'i istiyoruz çünkü bu -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.veya bunların hiçbiri Doğru değilse, ayın başladığı yer Nerede olacaktır. Pekala, artık Ayarlanmış Nerede olduğunu bildiğimize göre, onu kopyalamak için çift tıklayacağız. Pekala, hey şimdi, gerçekten çok kolay. Sadece - Başlık için A2'nin solundan kaç karakter istediğimizi söyleyeceğiz. D2-1'i istiyoruz çünkü bu -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.s -1, sonunda boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.s -1, sondaki boşluktan kurtulmaktır. Her ne kadar TRIM de sonunda boşluktan kurtuluyor sanırım.
Ve sonra tarih için, MID'yi kullanacağız. D2'de Ayarlanmış Nereden başlayarak A2'nin ORTASI ORTASI 50 veya olası olduğunu düşündüğünüz her neyse, ardından KIRP işlevi ve bunu aşağıya kopyalamak için çift tıklayacağız.
Pekala şimdi, Mike'a ulaşmamın sebebi, bu 12 sütunu tek bir formla, aslında bu 13 sütunu tek bir formla değiştirmenin bir yolu olup olmadığını merak ediyorum. Bunu bir Dizi formülü kullanarak yapabilmemin bir yolu var mı? Ve Mike, tabii ki, Ctrl + Shift + Enter gibi harika kitabı Array formüllerine yazdı. Birkaç farklı şey denedim ve aklımda bunun yapılmasının hiçbir yolu yoktu. Pekala, ama bilirsin, gidip uzmana soralım. Peki Mike, bakalım neye sahipsin.
Mike Girvin: Teşekkürler. Hey, ve uzmandan bahsetmişken, bu oldukça ustalıkla yapıldı. FIND, AGGREGATE, ISNUMBER kullandınız (ORTA. Şimdi, bu soruyu bana gönderdiğinizde, devam ettim ve çözdüm ve benim çözümümün sizinkine ne kadar benzer olduğu şaşırtıcı.
Pekala, burada bu sayfaya geçeceğim. Bu metin dizesindeki her bir ay için başlangıç konumunun nerede olduğunu bulmakla başlayacağım. Şimdi bunu yapmamın yolu şu, hey, bu ARAMA işlevini kullanacağım. Şimdi, sen FIND'ı kullandın, ben ARAMA kullanıyorum. Aslında bu durumda BUL daha iyidir çünkü FIND büyük / küçük harfe duyarlıdır, ARAMA ise değildir. Şimdi normalde FIND veya SEARCH ile yaptığımız şey, diyorum ki, hey, go FIND, January, bu daha büyük metin dizesi içinde virgül, normalde bu şekilde ARA Ctrl + Enter kullanırız ve parmağında sayar: bir, iki, üç , dört beş. 32. karakterin Ocak bulduğu yer olduğunu söylüyor.
Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.
Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.
The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.
Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.
Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.
Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.
Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.
Bu yüzden uğradığınız için herkese teşekkür etmek istiyorum. Bir dahaki sefere başka bir Dueling Excel Podcast ve Excel Is Fun için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Duel180.xlsm
