Нека питања која долазе су прилично тешка. Данас имамо колону ћелија. Свака ћелија има неке речи, затим датум, па још неке речи. Циљ је извући датумски део тог текста у нову колону. Ово је двобојна епизода са идејама Била и Микеа.
Погледајте видео
- Биллов суперширок приступ:
- Ставите свих 12 месеци у посебне колоне
- Користите функцију ФИНД да бисте видели да ли је овај месец у оригиналном тексту
- Да бисте пронашли минималну почетну позицију, користите = АГРЕГАТЕ (5,6,…
- Неколико додатних формула за тражење броја 2 или 3 позиције пре месеца
- Мајков приступ:
- Користите СЕАРЦХ уместо ФИНД. Финд разликује велика и мала слова, а Сеарцх не.
- Створите операцију поља аргумената функције тако што ћете одредити Б13: Б24 као Финд_Тект.
- Формула враћа #ВАЛУЕ! Грешка, али ако притиснете Ф2, Ф9, видећете да враћа низ.
- Првих 13 функција у АГРЕГАТЕ-у не могу да обрађују низ, али функције 14-19 могу да обрађују низ.
- 5 = МИН и 15 = СМАЛЛ (, 1) су слични, али СМАЛЛ (, 1) ће радити са низом.
- ЛООКУП, СУМПРОДУЦТ, ЦХИСК.ТЕСТ, ИНДЕКС и АГГРЕГАТЕ могу да обрађују аргументе низа функција без Цтрл + Схифт + Ентер
- Мике је био паметнији гледајући да ли су 2 знака пре Старт броја, а затим је зграбио 3 знака раније. Додатни простор уклања ТРИМ ()
- Да бисте добили наслов, користите функцију СУБСТИТУТЕ да бисте се ослободили текста Дате у колони Ц.
Видео транскрипт
Билл Јелен: Хеј, добродошао назад. Време је за још један Дуел Екцел подцаст. Ја сам Билл Јелен из. Придружиће ми се Мике Гирвин из Екцел-а је забавно.
Ово је наш двобој # 170: Проналажење датума
Хеј, добродошли свима. Овде сам имао тако сјајно питање и нисам могао да га решим. Бар то нисам могао лако да решим, па сам отишао до Микеа Гирвина и рекао сам, „Мике, хеј, имаш ли начина да то урадиш?“ Рекао је, „Да, имам начин да то учиним. Хајде да направимо двобој “.
Дакле, неко на ИоуТубе-у је послао ове податке и свака појединачна ћелија уопште има нешто попут наслова документа праћеног датумом. Желели су да разбију ове податке у наслов документа: шта је то, шта је ствар и који је датум. Али датуми су потпуно зли. Као и овде, 20. јануара је; али овде доле постоје ствари где датум може бити после ћелије, 9. априла. У реду, и без обзира на који начин желимо да га пронађемо. А понекад постоје два датума, а то је потпуно ужасно и да је таква помешана ситуација са датумима и што је више могуће, чак се и не појављује датум, у реду. Дакле, ево мог покушаја. Са десне стране ћу ставити ствари које тражим. Оно што ми се овде заиста свиђа је што никад нису скраћивали назив месеца. Ја стварно,заиста ценим то. Укуцајте тако јануар, а ја ћу се тако повући овде до децембра, а за сваку ћелију коју желим знати можемо ли наћи = ПРОНАЂИ тај јануар. Па ћу притиснути Ф4 један, два пута да га закључам на само ред, у тексту тамо у колони А, тако. Притиснућу Ф4 један, два, три пута да га закључам на колону, у реду. И ево, говори нам да се јануар налази на позицији 32, а осталих 11 месеци ће нам рећи да уопште није пронађен. Другим речима, сада добијамо грешку Валуе. Оно што тамо морам да урадим је да морам да пронађем, морам да пронађем минималну вредност игноришући све грешке у вредности. Дакле, откријте ову малу формулу овде = АГРЕГАТЕ и хајде да је направимо од почетка, = АГГРЕГАТЕ, оно што желимо је МИН, па је то број 5,а затим занемарите вредности грешке број 6 зарез, а затим све ове ћелије од јануара до децембра. А оно што ће нам рећи, то ће нам рећи где се месец догађа. А у овом случају добићемо 0, рецимо да се месец уопште не дешава.
Добро, откријемо остатак овога. Дакле, да бих решио ситуацију када овде имамо 20. јануара или 1. новембра, рекао сам да ћу прво да погледам одакле почиње тај месец и да се вратим две ћелије, две ћелије, два знака , два карактера. И погледајте да ли је то број, не баш тако. То је моја колона овде, Адјуст2. Адјуст2. И ево шта ћемо учинити. Рећи ћу, узмите МИД од А2, започињући га где у Г2-2 за дужину од 1, додајте му 0 и питајте, да ли је то број или није? У реду, и то је број. А онда ћемо такође потражити ситуацију када је двоцифрени датум, дакле 20. јануара. Дакле, то се зове Адјуст3, вратите се 3 знака одакле. Дакле, ту је Где, вратите се три знака дужине 1, додајте јој 0 и погледајте да ли је то 'са бројем, у реду? Тада ћемо се прилагодити и Прилагођено где каже АКО. АКО је овај чудан случај 0, поставићемо заиста велику вредност 999; у супротном ћемо се вратити са Г2 и вратити се назад 3, ако је Адјуст3 тачно или 2, ако је Адјуст2 тачно, или ако ниједно од њих није тачно, Где ће бити место где месец почиње. Добро, сада када знамо да је то Прилагођено место, двапут ћемо кликнути да то копирамо. Па, хеј сад, заиста је лако. Само ћемо - за наслов, рећи ћемо лево од А2, колико знакова желимо. Желимо Д2-1 јер је то -1 да се на крају ослободимо простора. Иако се ваљда и ТРИМ на крају ослобађа простора.АКО је овај чудан случај 0, поставићемо заиста велику вредност 999; у супротном ћемо се вратити са Г2 и вратити се назад 3, ако је Адјуст3 тачно или 2, ако је Адјуст2 тачно, или ако ниједно од њих није тачно, Где ће бити место где месец почиње. Добро, сада када знамо да је то Прилагођено место, двапут ћемо кликнути да то копирамо. Па, хеј сад, заиста је лако. Само ћемо - за наслов, рећи ћемо лево од А2, колико знакова желимо. Желимо Д2-1, јер је то -1 да се на крају ослободимо простора. Иако се ваљда и ТРИМ на крају ослобађа простора.АКО је овај чудан случај 0, поставићемо заиста велику вредност 999; у супротном ћемо се вратити са Г2 и вратити се назад 3, ако је Адјуст3 тачно или 2, ако је Адјуст2 тачно, или ако ниједно од њих није тачно, Где ће бити место где месец почиње. Добро, сада када знамо да је то Прилагођено место, двапут ћемо кликнути да то копирамо. Па, хеј сад, заиста је лако. Само ћемо - за наслов, рећи ћемо лево од А2, колико знакова желимо. Желимо Д2-1 јер је то -1 да се на крају ослободимо простора. Иако се ваљда и ТРИМ на крају ослобађа простора.или ако ниједно од њих није Тачно, Где ће бити место где месец почиње. Добро, сада када знамо да је то Прилагођено место, двапут ћемо кликнути да то копирамо. Па, хеј сад, заиста је лако. Само ћемо - за наслов, рећи ћемо лево од А2, колико знакова желимо. Желимо Д2-1 јер је то -1 да се на крају ослободимо простора. Иако се ваљда и ТРИМ на крају ослобађа простора.или ако ниједно од њих није Тачно, Где ће бити место где месец почиње. Добро, сада када знамо да је то Прилагођено место, двапут ћемо кликнути да то копирамо. Па, хеј сад, заиста је лако. Само ћемо - за наслов, рећи ћемо лево од А2, колико знакова желимо. Желимо Д2-1 јер је то -1 да се на крају ослободимо простора. Иако се ваљда и ТРИМ на крају ослобађа простора.с -1 је ослободити се простора на крају. Иако се ваљда и ТРИМ на крају ослобађа простора.с -1 је ослободити се простора на крају. Иако се ваљда и ТРИМ на крају ослобађа простора.
А онда ћемо за датум користити МИД. МИД фор - МИД од А2, почевши од Прилагођеног места у Д2, па изађите 50 или шта већ мислите да би то могло бити, а затим функција ТРИМ, а ми ћемо двапут кликнути да то копирамо.
У реду, разлог због којег сам контактирао Микеа је тај што се питам да ли постоји начин да заменим ових 12 колона једним обрасцем, заправо ових 13 колона једним обрасцем. Да ли постоји неки начин да то учиним помоћу формуле Арраи? А Мике је, наравно, написао ону сјајну књигу, Цтрл + Схифт + Ентер, о Арраи формулама. Покушао сам неколико различитих ствари и у мојим мислима то никако није могло да се уради. У реду, али знате, идемо питати стручњака. Па Мике, да видимо шта имаш.
Мике Гирвин: Хвала ,. Хеј, а кад смо већ код стручњака, ово је прилично стручно урађено. Користили сте ФИНД, АГРЕГАТЕ, ИСНУМБЕР (МИД. Сад, кад сте ми послали ово питање, ја сам наставио и решио га, и невероватно је колико је моје решење слично вашем.
У реду, прећи ћу на овај лист овде. Почећу са откривањем где је почетна позиција у овом текстуалном низу за сваки одређени месец. Е сад, начин на који ћу то урадити је да ћу, хеј, користити ову функцију СЕАРЦХ. Сада сте користили ФИНД, ја користим СЕАРЦХ. Заправо је вероватно ФИНД бољи у овој ситуацији јер је ФИНД осетљив на велика и мала слова, СЕАРЦХ није. Сада нормално оно што радимо било са ФИНД или СЕАРЦХ, кажем, хеј, иди ФИНД, Јануари, зарез у оквиру овог већег текстуалног низа, тако ми обично користимо СЕАРЦХ Цтрл + Ентер и броји на прст: један, два, три , четири, пет. Каже да је 32. лик тамо где је пронађен јануар.
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.
Дакле, желим да захвалим свима што су навратили. Видећемо се следећи пут за још један двобојни Екцел подцаст од и Екцел је забаван.
Скини докуменат
Преузмите датотеку узорка овде: Дуел180.клсм
