Niektoré otázky, ktoré prichádzajú, sú dosť ťažké. Dnes máme stĺpec buniek. Každá bunka má niekoľko slov, potom dátum a potom ďalších slov. Cieľom je vytiahnuť časť s dátumom tohto textu do nového stĺpca. Toto je súbojová epizóda s nápadmi od Billa a Mika.
Pozeraj video
- Billov superširoký prístup:
- Uveďte všetkých 12 mesiacov v samostatných stĺpcoch
- Pomocou funkcie NÁJDETE, či je tento mesiac v pôvodnom texte
- Ak chcete zistiť minimálnu východiskovú pozíciu, použite = AGGREGATE (5,6, …
- Niekoľko ďalších vzorcov na hľadanie pozícií číslo 2 alebo 3 pred mesiacom
- Mikeov prístup:
- Namiesto VYHĽADÁVANIA použite VYHĽADÁVANIE. Pri vyhľadávaní sa rozlišujú veľké a malé písmená.
- Vytvorte operáciu poľa argumentov funkcie zadaním B13: B24 ako Find_Text.
- Vzorec vráti #HODNOTA! Chyba, ale ak stlačíte F2, F9, uvidíte, že vracia pole.
- Prvých 13 funkcií v AGGREGATE nedokáže spracovať pole, ale funkcie 14-19 môžu pracovať s poľom.
- 5 = MIN a 15 = SMALL (, 1) sú podobné, ale SMALL (, 1) bude pracovať s poľom.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX a AGGREGATE dokážu spracovať argumenty funkčných polí bez Ctrl + Shift + Enter
- Mike bol múdrejší, keď zisťoval, či sú 2 znaky pred začiatkom číslo, a potom chytil 3 znaky predtým. Dodatočný priestor eliminuje TRIM ()
- Ak chcete získať názov, použite funkciu SUBSTITUTE a zbavte sa textu dátumu v stĺpci C
Prepis videa
Bill Jelen: Hej, vitaj späť. Je čas na ďalší podcast Dueling Excel. Som Bill Jelen z. Pripojí sa ku mne Mike Girvin z programu Excel Is Fun.
Toto je náš Duel č. 170: Nájdenie dátumov
Ahoj, vitajte späť všetci. Mal som tu takú skvelú otázku a nemohol som ju vyriešiť. Aspoň som to nedokázal ľahko vyriešiť, tak som šiel za Mikeom Girvinom a povedal som: „Mike, hej, máš spôsob, ako to urobiť?“ Povedal: „Áno, mám spôsob, ako to urobiť. Poďme na duel. “
Niekto na YouTube teda odoslal tieto údaje a každá jednotlivá bunka má všeobecne niečo ako názov dokumentu a za ním dátum. Chceli rozdeliť tieto údaje do názvu dokumentu: čo to je, o čo ide a potom o dátum. Ale dáta sú úplne zlé. Ako tu, je 20. januára; ale tu dole sú veci, kde môže byť dátum po cele, 9. apríla. Dobre, a nech už to bude akokoľvek, chceme to nájsť. A niekedy sú tu dva rande a to je proste úplne hrozné a že je to taká zmiešaná situácia v rande a pokiaľ je to možné, nemajte ani rande, dobre. Takže, tu je môj pokus. Na pravú stranu idem dať veci, ktoré hľadám. Čo sa mi tu veľmi páči, nikdy neskrátili názov mesiaca. Ja naozaj,to si naozaj vážim. Takže zadajte január a ja sa tak pretiahnem sem do decembra a pre každú bunku, ktorú chcem vedieť, nájdeme = NÁJDI ten január. Takže stlačím F4 jeden, dvakrát, aby som to uzamkol iba na riadok, v texte tam v stĺpci A, takhle. Stlačím F4 jeden, dva, trikrát, aby som to uzamkol na stĺpec, dobre. A tu nám hovorí, že január sa nachádza na pozícii 32 a ďalších 11 mesiacov nám hovorí, že sa vôbec nenachádza. Inými slovami, teraz dostávame chybu hodnoty. Čo tam musím urobiť je, že musím nájsť, musím nájsť minimálnu hodnotu ignorujúcu všetky chyby hodnoty. Takže tu odkryte tento malý vzorec = AGGREGATE a poďme ju zostaviť úplne od nuly, = AGGREGATE, čo chceme, je MIN, takže to je číslo 5,a potom ignorujte chybové hodnoty číslo 6, čiarku a potom všetky tieto bunky od januára do decembra. A to, čo nám povie, bude, že nám povie, kde sa mesiac stane. A v tomto prípade dostaneme 0, povedzme, že mesiac sa nestane vôbec.
Dobre, teraz poďme odkrývať zvyšok toho. Takže, aby som zvládol situáciu, keď tu máme 20. januára alebo 1. novembra, povedal som, že prvá vec, ktorú urobím, je pozrieť sa na to, kde ten mesiac začína, a vrátiť sa o dve bunky, dve bunky, dva znaky , dva znaky. A uvidíte, či to je číslo, nie je to tak. Toto je môj stĺpec, ktorý sa tu volá, Adjust2. Upraviť2. A tu je to, čo urobíme. Poviem, vezmeme MID z A2 a začneme ho tam, kde v G2-2 na dĺžku 1, pridám k tomu 0 a spýtam sa, je to číslo alebo nie? Dobre, také je číslo. A potom tiež vyhľadáme situáciu, keď ide o dvojciferný dátum, teda 20. januára. Takže to sa volá Adjust3, vráťte sa o 3 znaky dozadu odkiaľ. Takže je tu Kam, vráťte sa o tri znaky o dĺžku 1, pridajte k tomu 0 a uvidíte, či to jesa číslo, dobre? Potom to upravíme a Upravené kde hovorí IF. AK je tento čudný prípad 0, dáme skutočne veľkú hodnotu 999; v opačnom prípade pôjdeme z G2 a buď sa vrátime späť 3, ak je Adjust3 True, alebo sa vrátime späť 2, ak je Adjust2 True, alebo ak žiaden z nich nie je True, Kde bude tam, kde začína mesiac. Dobre, teraz, keď vieme, že to Upravené kde sme, dvojitým kliknutím to skopírujeme. No ahoj, teraz je to naozaj ľahké. Len sa chystáme - pri nadpise povieme, že odbočte doľava od A2, koľko znakov chceme. Chceme D2-1, pretože to je -1, je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.AK je tento čudný prípad 0, dáme skutočne veľkú hodnotu 999; v opačnom prípade pôjdeme z G2 a buď sa vrátime späť 3, ak je Adjust3 True, alebo sa vrátime späť 2, ak je Adjust2 True, alebo ak žiadny z nich nie je True, Kde bude tam, kde začína mesiac. Dobre, teraz, keď vieme, že to Upravené kde sme, dvojitým kliknutím to skopírujeme. No ahoj, teraz je to naozaj ľahké. Len sa chystáme - pri nadpise povieme, že odbočte doľava od A2, koľko znakov chceme. Chceme D2-1, pretože to je -1, je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.AK je tento čudný prípad 0, dáme skutočne veľkú hodnotu 999; v opačnom prípade pôjdeme z G2 a buď sa vrátime späť 3, ak je Adjust3 True, alebo sa vrátime späť 2, ak je Adjust2 True, alebo ak žiadny z nich nie je True, Kde bude tam, kde začína mesiac. Dobre, teraz, keď vieme, že to Upravené kde sme, dvojitým kliknutím to skopírujeme. No ahoj, teraz je to naozaj ľahké. Len sa chystáme - pri nadpise povieme, že odbočte doľava od A2, koľko znakov chceme. Chceme D2-1, pretože to je -1, je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.alebo ak nič z toho nie je pravda, Kde bude tam, kde začína mesiac. Dobre, teraz, keď vieme, že to Upravené kde sme, dvojitým kliknutím to skopírujeme. No ahoj, teraz je to naozaj ľahké. Len sa chystáme - pri nadpise povieme, že odbočte doľava od A2, koľko znakov chceme. Chceme D2-1, pretože to je -1, je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.alebo ak nič z toho nie je pravda, Kde bude tam, kde začína mesiac. Dobre, teraz, keď vieme, že to Upravené kde sme, dvojitým kliknutím to skopírujeme. No ahoj, teraz je to naozaj ľahké. Len sa chystáme - pri nadpise povieme, že odbočte doľava od A2, koľko znakov chceme. Chceme D2-1, pretože to je -1, je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.s -1 je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.s -1 je zbaviť sa medzery na konci. Aj keď si myslím, že aj TRIM sa na konci zbaví priestoru.
A potom pre dátum použijeme MID. MID for- MID of A2 starting at the Adjusted Where in D2 and go out 50 or anything along you think it could could be, and then the TRIM function, and we double double to copy that down.
Teraz som v poriadku, preto som povedal, že som povedal, že som zvedavý, či existuje spôsob, ako by som mohol nahradiť týchto 12 stĺpcov jediným formulárom, vlastne týchto 13 stĺpcov jediným formulárom. Existuje nejaký spôsob, ako by som to mohol urobiť pomocou vzorca Array? A Mike samozrejme napísal túto skvelú knihu Ctrl + Shift + Enter o vzorcoch Array. A skúsil som niekoľko rôznych vecí a v duchu som neexistoval spôsob, ako by sa to dalo urobiť. Dobre, ale viete, poďme sa spýtať odborníka. Takže Mike, pozrime sa, čo máš.
Mike Girvin: Vďaka ,. Hej, a keď už hovoríme o odborníkovi, toto bolo urobené dosť odborne. Použili ste FIND, AGGREGATE, ISNUMBER (MID. Teraz, keď ste mi poslali túto otázku, pokračoval som v riešení a je úžasné, aké podobné je moje riešenie s vašim.
Dobre, prejdem tu k tomuto hárku. Začnem zistením, kde je začiatočná pozícia v tomto textovom reťazci pre každý konkrétny mesiac. Teraz sa chystám urobiť to, že použijem túto funkciu VYHĽADÁVANIE. Teraz ste použili NÁJDIŤ, ja používam VYHĽADÁVANIE. Pravdepodobne je v tejto situácii FIND lepší, pretože FIND rozlišuje veľké a malé písmená, SEARCH nie. Teraz, čo normálne robíme s FIND alebo SEARCH, hovorím, hej, choď NAJDIŤ, január, čiarka v tomto väčšom textovom reťazci, takto bežne používame SEARCH Ctrl + Enter a počíta sa to na jeho prste: jeden, dva, tri , štyri, päť. Hovorí sa, že 32. znak je miestom, kde sa našiel január.
Teraz, namiesto toho, aby som to robil v mnohých bunkách naprieč stĺpcami, narazím na F2, prídem sem a FIND_TEXT. Všimnite si, že sme dali 1 položku, VYHĽADÁVANIE nám dalo 1 odpoveď. Ale ak zvýrazním celý stĺpec s názvami mesiacov, teraz som tam namiesto jednej položky vložil veľa položiek. Toto je argument funkcie. Vkladáme množstvo položiek, a to znamená, že robíme operáciu Function Argument Array. Kedykoľvek to urobíte, poviete funkcii, hej, dajte mi 12 odpovedí, 1 za každý mesiac. Teraz to prinesie pole, takže ak sa pokúsim zadať toto a skopírovať to, nebude to fungovať.
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.
Chcem sa teda všetkým poďakovať, že sa zastavili. Uvidíme sa nabudúce pri ďalšom Dueling Excel Podcastu z a Excelu je zábava.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Duel180.xlsm
