Niektóre z pojawiających się pytań są dość trudne. Dziś mamy kolumnę komórek. Każda komórka zawiera kilka słów, następnie datę, a następnie kilka słów. Celem jest przeniesienie części tego tekstu z datą do nowej kolumny. To odcinek pojedynków z pomysłami Billa i Mike'a.
Obejrzyj wideo
- Bardzo szerokie podejście Billa:
- Umieść wszystkie 12 miesięcy w osobnych kolumnach
- Użyj funkcji ZNAJDŹ, aby sprawdzić, czy ten miesiąc jest w tekście oryginalnym
- Aby znaleźć minimalną pozycję początkową, użyj = AGREGAT (5,6,…
- Kilka dodatkowych formuł do wyszukiwania pozycji numer 2 lub 3 przed miesiącem
- Podejście Mike'a:
- Użyj SZUKAJ zamiast ZNAJDŹ. W wyszukiwaniu rozróżniana jest wielkość liter, wyszukiwarka nie.
- Utwórz operację tablicy argumentów funkcji, określając B13: B24 jako Find_Text.
- Formuła zwraca #ARG! Błąd, ale jeśli naciśniesz F2, F9, zobaczysz, że zwraca tablicę.
- Pierwsze 13 funkcji AGGREGATE nie obsługuje tablicy, ale funkcje 14-19 mogą obsługiwać tablicę.
- 5 = MIN i 15 = MAŁE (, 1) są podobne, ale MAŁE (, 1) będzie działać z tablicą.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX i AGGREGATE mogą obsługiwać argumenty tablicy funkcji bez Ctrl + Shift + Enter
- Mike był mądrzejszy, sprawdzając, czy 2 znaki przed startem to liczba, a następnie chwytając 3 znaki wcześniej. Dodatkowa przestrzeń jest eliminowana przez TRIM ()
- Aby uzyskać tytuł, użyj funkcji SUBSTITUTE, aby pozbyć się tekstu Data w kolumnie C.
Transkrypcja wideo
Bill Jelen: Hej, witaj z powrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z. Dołączy do mnie Mike Girvin z Excel Is Fun.
To jest nasz pojedynek # 170: Znajdowanie dat
Hej, witajcie z powrotem. Miałem tu takie świetne pytanie i nie mogłem go rozwiązać. Przynajmniej nie mogłem go łatwo rozwiązać, więc poszedłem do Mike'a Girvina i powiedziałem: „Mike, hej, czy masz sposób, aby to zrobić?” Powiedział: „Tak, mam sposób, aby to zrobić. Zróbmy pojedynek ”.
Tak więc ktoś z YouTube przesłał te dane i ogólnie każda komórka ma coś w rodzaju tytułu dokumentu, po którym następuje data. Chcieli rozbić te dane w tytule dokumentu: co to jest, co to jest i jaka jest data. Ale daty są całkowicie złe. Tak jak tutaj jest 20 stycznia; ale tutaj na dole są rzeczy, w których data może być po celi, 9 kwietnia. W porządku, bez względu na to, w jaki sposób chcemy to znaleźć. A czasami są dwie daty i to jest po prostu okropne i że to taka pomieszana sytuacja dat i, o ile to możliwe, nawet nie mam daty, w porządku. Więc oto moja próba. Po prawej stronie umieszczę rzeczy, których szukam. Najbardziej podoba mi się tutaj to, że nigdy nie skracali nazwy miesiąca. Ja naprawdę,naprawdę to doceniam. Więc wpisz styczeń, a przeciągnę tutaj do grudnia w ten sposób i dla każdej komórki, którą chcę wiedzieć, czy możemy znaleźć = ZNAJDŹ ten styczeń. Więc mam zamiar nacisnąć F4 jeden, dwa razy, aby zablokować to tylko w wierszu, w tekście w kolumnie A, o tak. Nacisnę F4 raz, dwa, trzy razy, żeby zablokować go na kolumnie, dobrze. I tutaj mówi nam, że styczeń znajduje się na pozycji 32, a przez pozostałe 11 miesięcy będzie nam mówił, że w ogóle go nie znaleziono. Innymi słowy, teraz otrzymujemy błąd wartości. Muszę tam znaleźć, znaleźć minimalną wartość, ignorując wszystkie błędy wartości. Więc odkryj tę małą formułę tutaj = AGREGATUJ i zbudujmy to od podstaw, = AGREGUJ, chcemy MIN, więc to jest liczba 5,a następnie Ignoruj wartości błędów numer 6 przecinek, a następnie wszystkie te komórki od stycznia do grudnia. A to nam powie, to powie nam, gdzie wydarzy się miesiąc. W tym przypadku otrzymamy 0, powiedzmy, że miesiąc w ogóle się nie wydarza.
W porządku, odkryjmy resztę. Tak więc, aby poradzić sobie z sytuacją, w której mamy tutaj 20 stycznia lub 1 listopada, powiedziałem, że pierwszą rzeczą, którą zrobię, jest sprawdzenie, gdzie zaczyna się ten miesiąc, i cofnę się o dwie komórki, dwie komórki, dwa znaki , dwa znaki. I zobacz, czy to liczba, nie tak. To moja kolumna o nazwie Adjust2. Dostosuj 2. Oto, co zamierzamy zrobić. Powiem, weź MID A2 zaczynając od gdzie w G2-2 na długość 1, dodaj 0 i zapytaj, czy to liczba, czy nie? W porządku, więc to jest liczba. A potem poszukamy również sytuacji, w której jest to data dwucyfrowa, czyli 20 stycznia. Więc to się nazywa Adjust3, cofnij się o 3 znaki z miejsca, w którym się znajdujesz. Więc jest Gdzie, cofnij się o trzy znaki o długości 1, dodaj do niego 0 i zobacz, czy to 'numer, w porządku? Następnie dostosujemy się, a Dostosowane Gdzie mówi JEŻELI. JEŚLI to dziwny przypadek to 0, po prostu wstawimy naprawdę dużą wartość 999; w przeciwnym razie przejdziemy z G2 i albo cofniemy się o 3, jeśli Adjust3 ma wartość True, albo cofniemy się o 2, jeśli Adjust2 ma wartość True, lub jeśli żadna z nich nie jest True, to gdzie rozpocznie się miesiąc. W porządku, teraz, gdy wiemy, że skorygowano miejsce, klikniemy dwukrotnie, aby skopiować to. Cóż, hej teraz, to naprawdę łatwe. Chcemy tylko - w przypadku tytułu powiemy, że skręć w lewo od A2, ile znaków chcemy. Chcemy D2-1, ponieważ to jest -1, aby pozbyć się spacji na końcu. Chociaż wydaje mi się, że TRIM także pozbywa się przestrzeni na końcu.JEŚLI to dziwny przypadek to 0, po prostu wstawimy naprawdę dużą wartość 999; w przeciwnym razie przejdziemy z G2 i albo cofniemy się o 3, jeśli Adjust3 ma wartość True, albo cofniemy się o 2, jeśli Adjust2 ma wartość True, lub jeśli żadna z nich nie jest True, to gdzie rozpocznie się miesiąc. W porządku, teraz, gdy wiemy, że skorygowano miejsce, klikniemy dwukrotnie, aby skopiować to. Cóż, hej teraz, to naprawdę łatwe. Chcemy tylko - w przypadku tytułu powiemy, że skręć w lewo od A2, ile znaków chcemy. Chcemy D2-1, ponieważ to jest -1, aby pozbyć się spacji na końcu. Chociaż wydaje mi się, że TRIM także pozbywa się przestrzeni na końcu.JEŚLI to dziwny przypadek to 0, po prostu wstawimy naprawdę dużą wartość 999; w przeciwnym razie przejdziemy z G2 i albo cofniemy się o 3, jeśli Adjust3 ma wartość True, albo cofniemy się o 2, jeśli Adjust2 ma wartość True, lub jeśli żadna z nich nie jest True, to gdzie rozpocznie się miesiąc. W porządku, teraz, gdy wiemy, że skorygowano miejsce, klikniemy dwukrotnie, aby skopiować to. Cóż, hej teraz, to naprawdę łatwe. Chcemy tylko - w przypadku tytułu powiemy, że skręć w lewo od A2, ile znaków chcemy. Chcemy D2-1, ponieważ to jest -1, aby pozbyć się spacji na końcu. Chociaż wydaje mi się, że TRIM pozbywa się również miejsca na końcu.lub jeśli żadne z nich nie jest Prawdą, to Gdzie będzie, gdzie zaczyna się miesiąc. W porządku, teraz, gdy wiemy, że skorygowano miejsce, klikniemy dwukrotnie, aby skopiować to. Cóż, hej teraz, to naprawdę łatwe. Chcemy tylko - w przypadku tytułu powiemy, że skręć w lewo od A2, ile znaków chcemy. Chcemy D2-1, ponieważ to jest -1, aby pozbyć się spacji na końcu. Chociaż wydaje mi się, że TRIM pozbywa się również miejsca na końcu.lub jeśli żadne z nich nie jest Prawdą, to Gdzie będzie, gdzie zaczyna się miesiąc. W porządku, teraz, gdy wiemy, że skorygowano miejsce, klikniemy dwukrotnie, aby skopiować to. Cóż, hej teraz, to naprawdę łatwe. Chcemy tylko - w przypadku tytułu powiemy, że skręć w lewo od A2, ile znaków chcemy. Chcemy D2-1, ponieważ to jest -1, aby pozbyć się spacji na końcu. Chociaż wydaje mi się, że TRIM także pozbywa się przestrzeni na końcu.s -1 oznacza usunięcie spacji na końcu. Chociaż wydaje mi się, że TRIM także pozbywa się przestrzeni na końcu.s -1 oznacza usunięcie spacji na końcu. Chociaż wydaje mi się, że TRIM także pozbywa się przestrzeni na końcu.
A na koniec użyjemy MID. MID dla - MID dla A2 zaczynając od Adjusted Where w D2 i wyjdź 50 lub cokolwiek wzdłuż, jak myślisz, że to mogłoby być, a następnie funkcja TRIM, a my dwukrotnie klikniemy, aby to skopiować.
W porządku, powód, dla którego skontaktowałem się z Mike'iem, jest taki, że powiedziałem: zastanawiam się, czy istnieje sposób, aby zastąpić te 12 kolumn jednym formularzem, a właściwie te 13 kolumn jednym formularzem. Czy istnieje sposób, w jaki mógłbym to zrobić za pomocą formuły tablicowej? A Mike napisał oczywiście tę wspaniałą książkę, Ctrl + Shift + Enter, o formułach tablicowych. Spróbowałem kilku różnych rzeczy i myślę, że nie było sposobu, aby to zrobić. W porządku, ale wiesz, chodźmy zapytać eksperta. Więc Mike, zobaczmy, co masz.
Mike Girvin: Dzięki. Hej, a mówiąc o ekspertach, zrobiono to całkiem fachowo. Użyłeś ZNAJDŹ, AGREGUJ, NUMER (ŚREDNIA. Teraz, kiedy wysłałeś do mnie to pytanie, poszedłem dalej i rozwiązałem je i to niesamowite, jak podobne jest moje rozwiązanie do twojego.
W porządku, przejdę do tego arkusza tutaj. Zacznę od ustalenia, gdzie jest pozycja początkowa w tym ciągu tekstowym dla każdego miesiąca. Teraz zamierzam to zrobić, hej, skorzystam z funkcji SZUKAJ. Teraz użyłeś ZNAJDŹ, ja używam SZUKAJ. Właściwie prawdopodobnie funkcja FIND jest lepsza w tej sytuacji, ponieważ funkcja FIND rozróżnia wielkość liter, a funkcja SEARCH nie. Teraz normalnie to, co robimy za pomocą ZNAJDŹ lub SZUKAJ, mówię, hej, idź ZNAJDŹ, styczeń, przecinek w tym większym ciągu tekstowym, tak zwykle używamy SZUKAJ Ctrl + Enter i liczy się na palcu: jeden, dwa, trzy , cztery pięć. Mówi, że 32. znak jest tam, gdzie znalazł się w styczniu.
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.
Tak więc chcę podziękować wszystkim za zatrzymanie się. Do zobaczenia następnym razem w kolejnym podcastie Dueling Excel od i Excel Is Fun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel180.xlsm
