Jotkut esiin tulevista kysymyksistä ovat melko vaikeita. Tänään meillä on sarake soluja. Jokaisessa solussa on joitain sanoja, sitten päivämäärä ja sitten lisää sanoja. Tavoitteena on vetää kyseisen tekstin päivämääräosa uuteen sarakkeeseen. Tämä on kaksintaistelujakso, jonka ideoita on kirjoittanut Bill ja Mike.
Katso video
- Billin erittäin laaja lähestymistapa:
- Laita kaikki 12 kuukautta erillisiin sarakkeisiin
- Löydä -toiminnolla voit tarkistaa, onko tämä kuukausi alkuperäistekstissä
- Löydät pienimmän lähtöpaikan käyttämällä = AGGREGATE (5,6,…
- Muutama ylimääräinen kaava etsimään numero 2 tai 3 sijaintia ennen kuukautta
- Miken lähestymistapa:
- Käytä HAKU etsinnän sijaan. Haku on kirjainkokoinen, Haku ei.
- Luo funktion argumenttiryhmäoperaatio määrittämällä B13: B24 Find_Textiksi.
- Kaava palauttaa #ARVO! Virhe, mutta jos painat F2, F9, näet, että se palauttaa matriisin.
- AGGREGATEn ensimmäiset 13 toimintoa eivät pysty käsittelemään taulukkoa, mutta toiminnot 14-19 voivat käsitellä taulukkoa.
- 5 = MIN ja 15 = PIENI (, 1) ovat samanlaisia, mutta PIENI (, 1) toimii taulukon kanssa.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX ja AGGREGATE voivat käsitellä funktioryhmä argumentteja ilman Ctrl + Vaihto + Enter
- Mike oli älykkäämpi katsomalla, onko 2 merkkiä ennen Startia numero, ja tarttumalla sitten 3 merkkiin ennen. TRIM eliminoi ylimääräisen tilan ()
- Saadaksesi otsikon, käytä SUBSTITUTE-funktiota päästäksesi eroon päivämäärätekstistä sarakkeessa C
Videon transkriptio
Bill Jelen: Hei, tervetuloa takaisin. On aika uudelle Dueling Excel Podcastille. Olen Bill Jelen. Minuun liittyy Mike Girvin Excel Is Fun -sovelluksesta.
Tämä on meidän kaksintaistelu # 170: Päivämäärien löytäminen
Hei, tervetuloa kaikki takaisin. Minulla oli täällä niin suuri kysymys, enkä pystynyt ratkaisemaan sitä. Ainakin en voinut ratkaista sitä helposti, joten menin ulos Mike Girvinin luokse ja sanoin: "Mike, hei, onko sinulla tapa tehdä tämä?" Hän sanoi: ”Kyllä, minulla on tapa tehdä se. Tehdään kaksintaistelu. "
Joten joku YouTubessa lähetti nämä tiedot ja jokaisella solulla yleensä on jotain asiakirjan otsikkoa, jota seuraa päivämäärä. He halusivat jakaa nämä tiedot asiakirjan otsikkoon: mikä se on, mikä on asia ja sitten mikä päivämäärä on. Mutta päivämäärät ovat täysin pahoja. Kuten täällä, on 20. tammikuuta; mutta täällä täällä on asioita, joissa päivämäärä saattaa olla solun jälkeen 9. huhtikuuta. Selvä, ja riippumatta siitä, miten se on, haluamme löytää sen. Ja joskus siellä on kaksi päivämäärää, ja tämä on aivan täysin kamalaa, ja että se on niin vain sekoitettu päivämäärien tilanne ja koska mahdollista, heillä ei edes ole päivämäärää. Joten, tässä on yritykseni. Oikealle puolelle laitan etsimäni asiat. Pidän todella siitä, että he eivät koskaan lyhennä kuukauden nimeä. Minä todella,todella arvostan sitä. Joten kirjoita tammikuussa ja vedän tänne joulukuuhun tuolla tavalla, ja löydämme jokaisesta solusta, jonka haluan tietää = ETTÄ tuo tammikuu. Joten aion painaa F4 yksi, kaksi kertaa lukitaksesi sen vain riville, sarakkeen A tuollaiseen tekstiin. Lukitsen sen sarakkeeseen painamalla OK F4 yksi, kaksi, kolme kertaa. Ja tässä se kertoo meille, että tammikuu löytyy sijainnista 32 ja loput 11 kuukautta se kertoo meille, että sitä ei löydy lainkaan. Toisin sanoen saamme nyt arvovirheen. Minun on tehtävä se, että minun on löydettävä, minun on löydettävä vähimmäisarvo huomioimatta kaikki arvovirheet. Joten, piilota tämä pieni kaava = KOKOONPANO ja rakennetaan tämä vain tyhjästä, = KOKOONPANO, mitä haluamme, on MIN, joten se on numero 5,ja sitten sivuuttaa virhearvot numero 6 pilkku ja sitten kaikki nämä solut tammikuusta joulukuuhun. Ja mitä se kertoo meille, se kertoo meille, missä kuukausi tapahtuu. Ja tässä tapauksessa saamme nollan, sanotaan, että kuukautta ei tapahdu ollenkaan.
Selvä nyt, piilotetaan loput tästä. Joten, käsitelläkseni tilannetta, jossa meillä on 20. tammikuuta tai 1. marraskuuta, sanoin, että ensimmäinen asia, jonka teen, on, että aion tarkastella, mistä kuukausi alkaa, ja palata takaisin kahteen soluun, kahteen soluun, kahteen merkkiin , kaksi merkkiä. Ja katso onko se numero, ei niin. Se on sarakkeeni nimeltä nimeltä Adjust2. Säädä 2. Ja tässä aiomme tehdä. Aion sanoa, että otan A2: n MID: n aloittaen siitä missä G2-2: ssä 1 pituuden, lisää siihen 0 ja kysy, onko se numero vai ei? Selvä, niin on myös luku. Ja sitten etsimme myös tilannetta, jossa päivämäärä on kaksinumeroinen, siis 20. tammikuuta. Joten sitä kutsutaan Adjust3, palaa 3 merkkiä takaisin mistä. Joten siellä on Missä, palaa kolme merkkiä taaksepäin yhden pituiseksi, lisää siihen 0 ja katso onko se 'sa numero, okei? Sitten aiomme säätää ja Säädetty missä sanoo JOS. JOS tämä outo tapaus oli 0, asetamme vain todella suuren arvon 999; muuten aiomme siirtyä G2: stä ja joko palata takaisin 3, jos Adjust3 on tosi, tai palata 2, jos Adjust2 on tosi, tai jos mikään näistä ei ole totta, missä Missä tulee olemaan, josta kuukausi alkaa. Selvä, nyt kun tiedämme, että tämä mukautettu paikka, kaksoisnapsautamme kopioidaksemme sen alas. No, hei nyt, se on todella helppoa. Olemme vain menossa - Otsikon osalta sanomme, että otamme A2: n vasemmalle, kuinka monta merkkiä haluamme. Haluamme D2-1: n, koska se on -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.JOS tämä outo tapaus oli 0, asetamme vain todella suuren arvon 999; muuten aiomme siirtyä G2: stä ja joko palata 3: een, jos Adjust3 on tosi, tai palata 2, jos Adjust2 on tosi, tai jos mikään niistä ei ole totta, missä Missä tulee olemaan, josta kuukausi alkaa. Selvä, nyt kun tiedämme, että tämä mukautettu paikka, kaksoisnapsautamme kopioidaksemme sen alas. No, hei nyt, se on todella helppoa. Olemme vain menossa - Otsikon osalta sanomme, että otamme A2: n vasemmalle, kuinka monta merkkiä haluamme. Haluamme D2-1: n, koska se on -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.JOS tämä outo tapaus oli 0, asetamme vain todella suuren arvon 999; muuten aiomme siirtyä G2: stä ja joko palata takaisin 3, jos Adjust3 on tosi, tai palata 2, jos Adjust2 on tosi, tai jos mikään näistä ei ole totta, missä Missä tulee olemaan, josta kuukausi alkaa. Selvä, nyt kun tiedämme, että tämä mukautettu paikka, kaksoisnapsautamme kopioidaksemme sen alas. No, hei nyt, se on todella helppoa. Aiomme vain - Otsikon osalta sanomme, että otamme A2: n vasemmalle, kuinka monta merkkiä haluamme. Haluamme D2-1: n, koska se on -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.tai jos mikään näistä ei ole totta, missä aiot olla, mistä kuukausi alkaa. Selvä, nyt kun tiedämme, että tämä mukautettu paikka, kopioimme sen alas kaksoisnapsauttamalla. No, hei nyt, se on todella helppoa. Aiomme vain - Otsikon osalta sanomme, että otamme A2: n vasemmalle, kuinka monta merkkiä haluamme. Haluamme D2-1: n, koska se on -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.tai jos mikään näistä ei ole totta, missä Missä tulee olemaan, josta kuukausi alkaa. Selvä, nyt kun tiedämme, että tämä mukautettu paikka, kopioimme sen alas kaksoisnapsauttamalla. No, hei nyt, se on todella helppoa. Aiomme vain - Otsikon osalta sanomme, että otamme A2: n vasemmalle, kuinka monta merkkiä haluamme. Haluamme D2-1: n, koska se on -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.s -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.s -1 on päästä eroon lopussa olevasta tilasta. Vaikka luulen, että TRIM on myös erossa lopputilasta.
Ja sitten päivälle käytämme MID: tä. MID for- MID A2 alkaen D2: n mukautetusta sijainnista ja mene ulos 50 tai mitä tahansa, mitä luulet sen olevan, ja sitten TRIM-toiminto, ja kaksoisnapsauttamalla kopioimme sen alas.
Selvä, syy miksi otin yhteyttä Mikeen, sanoin, ihmettelen, onko mahdollista korvata nämä 12 saraketta yhdellä lomakkeella, itse asiassa nämä 13 saraketta yhdellä lomakkeella. Voinko jollain tavalla tehdä tämän käyttämällä Array-kaavaa? Ja Mike kirjoitti tietysti tuon suuren kirjan, Ctrl + Vaihto + Enter, Array-kaavoista. Ja kokeilin muutamia erilaisia asioita, eikä mielessäni ollut mitään keinoa tehdä niin. Selvä, mutta tiedät, kysytään asiantuntijalta. Joten Mike, katsotaanpa mitä sinulla on.
Mike Girvin: Kiitos. Hei, ja puhuen asiantuntijasta, tämä tehtiin melko ammattitaitoisesti. Käytit FIND, AGGREGATE, ISNUMBER (MID. Nyt, kun lähetit tämän kysymyksen minulle, jatkoin ratkaisua ja on hämmästyttävää, kuinka samanlainen ratkaisuni on sinun.
Selvä, menen tälle sivulle. Aloitan selvittämällä, missä tämän tekstimerkkijonon aloituspaikka on jokaiselle kuukaudelle. Nyt tapa, jolla aion tehdä sen, aion käyttää hei, käytä tätä HAKU-toimintoa. Nyt käytit Etsit, minä HAKU. Todellisuudessa todennäköisesti ETSI on parempi tässä tilanteessa, koska FIND on kirjainkokoinen, HAKU ei. Normaalisti nyt, mitä teemme joko HAKU tai HAKU, sanon, hei, mene ETSI, tammikuu, pilkulla tässä suuremmassa tekstimerkkijonossa, näin käytämme normaalisti HAKU Ctrl + Enter, ja se laskee sormellaan: yksi, kaksi, kolme , neljä viisi. Siinä sanotaan, että 32. merkki on se, josta se löysi tammikuun.
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.
Joten haluan kiittää kaikkia pysähtymisestä. Nähdään ensi kerralla uudelle Dueling Excel Podcastille ja Excel on hauskaa.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Duel180.xlsm
