Kai kurie iškylantys klausimai yra gana sunkūs. Šiandien mes turime langelių stulpelį. Kiekvienoje langelyje yra keletas žodžių, tada data, tada dar keli žodžiai. Tikslas yra to teksto datos dalį ištraukti į naują stulpelį. Tai dvikovos epizodas su Billo ir Mike'o idėjomis.
Žiūrėti video
- Itin platus Billo požiūris:
- Visus 12 mėnesių sudėkite į atskirus stulpelius
- Norėdami sužinoti, ar šis mėnuo yra pradiniame tekste, naudokite funkciją RASTI
- Norėdami rasti mažiausią pradinę padėtį, naudokite = AGGREGATE (5,6,…
- Keletas papildomų formulių, kad būtų galima rasti skaičių 2 arba 3 prieš mėnesį
- Mike'o požiūris:
- Vietoje RASTI naudokite PAIEŠKĄ. „Rasti“ skiria didžiosios ir mažosios raidės, o „Paieška“ - ne.
- Sukurkite funkcijos argumento masyvo operaciją nurodydami B13: B24 kaip „Find_Text“.
- Formulė grąžina #VALUE! Klaida, bet jei paspausite F2, F9, pamatysite, kad jis grąžina masyvą.
- Pirmosios 13 AGGREGATE funkcijų negali tvarkyti masyvo, tačiau 14-19 funkcijos - masyvą.
- 5 = MIN ir 15 = SMALL (, 1) yra panašūs, tačiau SMALL (, 1) veiks su masyvu.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX ir AGGREGATE gali tvarkyti funkcijų masyvo argumentus be Ctrl + Shift + Enter
- Mike'as buvo protingesnis, ieškodamas, ar 2 simboliai iki „Start“ yra skaičius, ir tada sugriebė 3 simbolius. Papildomą vietą pašalina TRIM ()
- Norėdami gauti pavadinimą, naudokite funkciją PAKEISTI, kad atsikratytumėte C stulpelio teksto „Data“
Vaizdo įrašo nuorašas
Billas Jelenas: Ei, sveiki sugrįžę. Atėjo laikas kitam „Dueling Excel“ tinklui. Aš esu Billas iš. Prie manęs prisijungs Mike'as Girvinas iš „Excel Is Fun“.
Tai yra mūsų dvikova Nr. 170: datų radimas
Ei, pasveikink visus. Man čia kilo toks puikus klausimas ir aš negalėjau jo išspręsti. Bent jau nesugebėjau to lengvai išspręsti, todėl išėjau pas Mike'ą Girviną ir pasakiau: „Mike, ei, ar tu turi būdą tai padaryti?“ Jis pasakė: „Taip, aš turiu būdą tai padaryti. Padarykime dvikovą “.
Taigi kažkas iš „YouTube“ atsiuntė šiuos duomenis ir apskritai kiekviename langelyje yra kažkas panašaus į dokumento pavadinimą, po kurio nurodoma data. Jie norėjo suskirstyti šiuos duomenis į dokumento pavadinimą: kas tai yra, koks daiktas ir kokia data. Tačiau datos yra visiškai blogos. Kaip ir čia, sausio 20 d. bet čia yra dalykų, kur data gali būti po langelio, balandžio 9 d. Gerai, ir nesvarbu, kokiu keliu jis yra, mes norime jį rasti. Kartais būna dvi datos, ir tai yra visiškai siaubinga, ir kad tai yra tik mišri datos situacija ir, kaip įmanoma, net nerodykite datos, gerai. Taigi, čia yra mano bandymas. Dešinėje pusėje padėsiu ieškomus daiktus. Man čia labai patinka, kad jie niekada nesutrumpino mėnesio pavadinimo. Aš tikrai,tikrai vertinu tai. Taigi įveskite sausį, ir aš taip nusitempsiu į gruodį, ir kiekvienai langelei, kurią noriu žinoti, ar galime rasti = RASTI tą sausį. Taigi aš keturis kartus paspausiu F4, kad užfiksuočiau jį tik eilutėje, tekste, esančiame A stulpelyje. Paspausiu F4 vieną, du, tris kartus, kad užfiksuočiau jį prie kolonos, gerai. Čia mums sakoma, kad sausio mėnuo yra 32 pozicijoje, o likusius 11 mėnesių jis mums pasakys, kad jo nėra. Kitaip tariant, dabar gauname vertės klaidą. Ką turiu padaryti, tai turiu rasti, turiu rasti mažiausią vertę, nepaisydamas visų vertės klaidų. Taigi, neslėpkite šios mažos formulės = SUMAIKITE ir pastatykime tai tik nuo nulio, = SUMAŽINTI, ko norime, tai MIN, taigi tai skaičius 5tada nepaisykite klaidos verčių 6 kablelio ir tada visų šių langelių nuo sausio iki gruodžio. Tai, kas mums pasakys, tai pasakys, kur įvyksta mėnuo. Šiuo atveju gausime 0, tarkime, mėnuo visai neįvyksta.
Gerai dabar, atskleiskime visa tai. Taigi, norėdamas išspręsti situaciją, kai čia yra sausio 20 arba lapkričio 1 d., Aš pasakiau, kad pirmas dalykas, kurį aš padarysiu, tai aš pažiūrėsiu, kur prasideda tas mėnuo, ir grįšiu atgal į dvi langelius, dvi langelius, du simbolius , du simboliai. Ir pažiūrėkite, ar tai skaičius, ne taip. Tai mano stulpelis, vadinamas „Adjust2“. Koreguoti2. Štai ką mes darysime. Aš pasakysiu: paimkite A2 MID, pradedant jį G2-2 vietoje, kur ilgis yra 1, pridėkite 0 ir paklauskite, ar tai skaičius, ar ne? Gerai, toks yra ir skaičius. Tada mes taip pat ieškosime situacijos, kai tai yra dviženklė data, taigi sausio 20 d. Tai vadinama „Adjust3“, grįžkite 3 simbolius iš kur. Taigi yra „Kur“, grįžkite atgal tris simbolius, kurių ilgis yra 1, pridėkite 0 ir pažiūrėkite, ar tai “sa skaičius, gerai? Tada mes ketiname koreguoti, o „Sureguliuota vieta“ sako „IF“. JEIGU tai keistas atvejis buvo 0, mes tiesiog nustatysime tikrai didelę vertę 999; priešingu atveju, mes eisime iš G2 ir grįšime atgal 3, jei „Adjust3“ yra teisinga, arba 2 atgal, jei „Adjust2“ yra teisinga, arba jei nė vienas iš jų nėra tikras, tai kur bus mėnuo, kur prasideda. Gerai, dabar, kai žinome, kad tas „Sureguliuotas kur“, dukart spustelėsime, kad nukopijuotume žemyn. Na, o dabar, tai tikrai lengva. Mes tiesiog ketiname - dėl pavadinimo pasakysime, kad paimkite kairę nuo A2, kiek simbolių mes norime. Mes norime D2-1, nes -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.JEIGU tai keistas atvejis buvo 0, mes tiesiog nustatysime tikrai didelę vertę 999; priešingu atveju, mes eisime iš G2 ir grįšime atgal 3, jei „Adjust3“ yra teisinga, arba 2 atgal, jei „Adjust2“ yra teisinga, arba jei nė vienas iš jų nėra tikras, tai kur bus mėnuo, kur prasideda. Gerai, dabar, kai žinome, kad tas „Sureguliuotas kur“, dukart spustelėsime, kad nukopijuotume žemyn. Na, o dabar, tai tikrai lengva. Mes tiesiog ketiname - dėl pavadinimo pasakysime, kad paimkite kairę nuo A2, kiek simbolių mes norime. Mes norime D2-1, nes -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.JEI šis keistas atvejis buvo 0, mes tiesiog nustatysime tikrai didelę vertę 999; priešingu atveju, mes eisime iš G2 ir grįšime atgal 3, jei „Adjust3“ yra teisinga, arba 2 atgal, jei „Adjust2“ yra teisinga, arba jei nė vienas iš jų nėra tikras, tai kur bus mėnuo, kur prasideda. Gerai, dabar, kai žinome, kad tas „Sureguliuotas kur“, dukart spustelėsime, kad nukopijuotume žemyn. Na, o dabar, tai tikrai lengva. Mes tiesiog ketiname - dėl pavadinimo pasakysime, kad paimkite kairę nuo A2, kiek simbolių mes norime. Mes norime D2-1, nes -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.arba jei nė vienas iš jų nėra tiesa, kur prasideda mėnuo. Gerai, dabar, kai žinome, kad tas „Sureguliuotas kur“, dukart spustelėsime, kad nukopijuotume žemyn. Na, o dabar, tai tikrai lengva. Mes tiesiog ketiname - dėl pavadinimo pasakysime, kad paimkite kairę nuo A2, kiek simbolių mes norime. Mes norime D2-1, nes -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.arba jei nė vienas iš jų nėra tiesa, kur prasideda mėnuo. Gerai, dabar, kai žinome, kad tas „Sureguliuotas kur“, dukart spustelėsime, kad nukopijuotume žemyn. Na, o dabar, tai tikrai lengva. Mes tiesiog ketiname - dėl pavadinimo pasakysime, kad paimkite kairę nuo A2, kiek simbolių mes norime. Mes norime D2-1, nes -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.s -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.s -1 yra atsikratyti vietos pabaigoje. Nors spėju, kad TRIM taip pat atsikrato vietos pabaigoje.
Tada datai mes naudosime MID. MID for- MID A2 pradedant nuo D2 sureguliuoto kur ir išeikite 50 ar bet ką, kas, jūsų manymu, gali būti, ir tada TRIM funkciją, ir mes dukart spustelėsime, kad nukopijuotume žemyn.
Gerai, priežastis, dėl kurios kreipiausi į Mike'ą, sakiau: įdomu, ar galiu pakeisti šias 12 stulpelių viena forma, iš tikrųjų šias 13 stulpelių viena forma. Ar galiu tai padaryti kaip nors naudodamas „Array“ formulę? Ir Mike'as, žinoma, parašė tą puikią knygą „Ctrl + Shift + Enter“ ant „Array“ formulių. Išbandžiau kelis skirtingus dalykus ir mintyse niekaip negalėjau to padaryti. Gerai, bet žinok, eikime paklausti eksperto. Taigi, Maike, pažiūrėkime, ką tu turi.
Mike'as Girvinas: Ačiū. Ei, o kalbant apie ekspertą, tai buvo gana kvalifikuotai padaryta. Jūs naudojote „RASTI“, „SUMATYTI“, „ISNUMBER“.
Gerai, aš pereisiu prie šio lapo čia. Pradėsiu išsiaiškinti, kur yra kiekvieno mėnesio pradinė pozicija šioje teksto eilutėje. Dabar, kaip aš tai darysiu, aš naudosiu šią PAIEŠKOS funkciją. Dabar jūs naudojate RASTI, aš - PAIEŠKA. Iš tikrųjų tikriausiai FIND yra geresnė šioje situacijoje, nes FIND skiria didžiosioms ir mažosioms raidėms, o PAIEŠKA - ne. Dabar paprastai tai, ką darome su RASTI arba PAIEŠKA, sakau, ei, eikite RASTI, sausis, kablelis šioje didesnėje teksto eilutėje. Štai kaip mes paprastai naudojame PAIEŠKOS Ctrl + Enter, ir tai skaičiuojama ant piršto: vienas, du, trys , keturi, penki. Sakoma, kad 32-asis personažas yra ten, kur jis rado sausį.
Užuot tai padaręs daugybėje stulpelių, aš paspausiu F2, eik čia ir FIND_TEXT. Atkreipkite dėmesį, kad mes suteikėme 1 elementą, PAIEŠKA davė 1 atsakymą. Bet jei paryškinu visą mėnesio pavadinimų stulpelį, dabar vietoj vieno elemento įdedu daug elementų. Tai funkcijos argumentas. Įdedame elementų masyvą, taigi tai reiškia, kad atliekame funkcijų argumentų masyvo operaciją. Kaskart, kai tai darai, pasakai funkcijai, ei, duok man 12 atsakymų, po 1 kiekvienam mėnesiui. Dabar tai pateiks masyvą, taigi, jei bandysiu įvesti tai ir nukopijuoti žemyn, tai neveiks.
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.
Taigi, noriu padėkoti visiems už tai, kad užsukote. Kitą kartą pamatysime kitą „Dueling Excel Podcast“ iš „Excel Is Fun“.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Duel180.xlsm
