Termine finden - Excel-Tipps

Einige der Fragen, die eingehen, sind ziemlich schwierig. Heute haben wir eine Zellensäule. Jede Zelle enthält einige Wörter, dann ein Datum und dann weitere Wörter. Ziel ist es, den Datumsteil dieses Textes in eine neue Spalte zu ziehen. Dies ist eine Duell-Episode mit Ideen von Bill und Mike.

Schau Video

• Bills superweiter Ansatz:
• Tragen Sie alle 12 Monate in separate Spalten ein
• Verwenden Sie die FIND-Funktion, um festzustellen, ob dieser Monat im Originaltext enthalten ist
• Verwenden Sie = AGGREGATE (5,6,…
• Einige zusätzliche Formeln, um vor dem Monat nach Positionen 2 oder 3 zu suchen
• Mikes Ansatz:
• Verwenden Sie SEARCH anstelle von FIND. Bei der Suche wird zwischen Groß- und Kleinschreibung unterschieden, bei der Suche nicht.
• Erstellen Sie eine Funktionsargument-Array-Operation, indem Sie B13: B24 als Find_Text angeben.
• Die Formel gibt #VALUE zurück! Fehler, aber wenn Sie F2, F9 drücken, sehen Sie, dass ein Array zurückgegeben wird.
• Die ersten 13 Funktionen in AGGREGATE können kein Array verarbeiten, die Funktionen 14-19 können jedoch ein Array verarbeiten.
• 5 = MIN und 15 = SMALL (, 1) sind ähnlich, aber SMALL (, 1) funktioniert mit einem Array.
• LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX und AGGREGATE können Funktionsarray-Argumente ohne Strg + Umschalt + Eingabetaste verarbeiten
• Mike war schlauer, als er suchte, ob 2 Zeichen vor dem Start eine Zahl sind, und dann 3 Zeichen zuvor abnahm. Der zusätzliche Platz wird durch TRIM () beseitigt
• Verwenden Sie die Funktion SUBSTITUTE, um den Datumstext in Spalte C zu entfernen, um den Titel abzurufen

Video-Transkript

Bill Jelen: Hey, willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von. Ich werde von Mike Girvin von Excel Is Fun begleitet.

Dies ist unser Duell # 170: Termine finden

Hey, willkommen zurück alle. Ich hatte hier eine großartige Frage und konnte sie nicht lösen. Zumindest konnte ich es nicht einfach lösen, also ging ich zu Mike Girvin und sagte: "Mike, hey, hast du eine Möglichkeit, das zu tun?" Er sagte: „Ja, ich habe einen Weg, es zu tun. Lass uns ein Duell machen. “

Jemand auf YouTube hat diese Daten gesendet, und jede einzelne Zelle im Allgemeinen hat so etwas wie einen Dokumenttitel, gefolgt von einem Datum. Sie wollten diese Daten in den Dokumenttitel aufteilen: Was es ist, was das Ding ist und was das Datum ist. Aber die Daten sind völlig böse. Wie hier ist es der 20. Januar; Aber hier unten gibt es Dinge, bei denen das Datum nach der Zelle, dem 9. April, liegen könnte. In Ordnung, und egal wie es ist, wir wollen es finden. Und manchmal gibt es zwei Daten und das ist einfach völlig schrecklich und dass es sich nur um eine durcheinandergebrachte Situation von Daten handelt und wie möglich nicht einmal ein Datum angezeigt wird, in Ordnung. Also, hier ist mein Versuch. Auf der rechten Seite werde ich die Dinge platzieren, nach denen ich suche. Was ich hier wirklich mag, ist, dass sie den Monatsnamen nie abgekürzt haben. Ich (…) wirklich,schätze das wirklich. Geben Sie also Januar ein und ich ziehe hier so bis Dezember, und für jede Zelle, die ich wissen möchte, können wir diesen Januar finden = FINDEN. Also drücke ich zweimal F4, um es auf eine Zeile im Text dort in Spalte A zu beschränken. Ich drücke F4 ein, zwei, drei Mal, um es an der Säule zu befestigen. Und hier sagt es uns, dass der Januar auf Position 32 steht und für die anderen 11 Monate wird es uns sagen, dass er überhaupt nicht gefunden wird. Mit anderen Worten, wir erhalten jetzt den Wertfehler. Was ich dort tun muss, ist, dass ich finden muss, ich muss den Mindestwert finden, wobei alle Wertfehler ignoriert werden. Also, verstecke diese kleine Formel hier = AGGREGATE und lass uns diese einfach von Grund auf neu erstellen, = AGGREGATE, was wir wollen, ist die MIN, das ist also die Nummer 5,und dann Ignorieren Sie die Fehlerwerte Nummer 6 Komma und dann alle diese Zellen von Januar bis Dezember. Und was uns das sagen wird, ist, dass wir wissen, wo der Monat passiert. Und in diesem Fall erhalten wir 0, sagen wir, der Monat passiert überhaupt nicht.

Okay, jetzt lass uns den Rest davon einblenden. Um die Situation zu bewältigen, in der wir hier den 20. Januar oder den 1. November haben, sagte ich, dass ich als erstes schauen werde, wo dieser Monat beginnt, und zwei Zellen, zwei Zellen, zwei Zeichen zurückgehen werde , zwei Zeichen. Und sehen Sie, ob das eine Zahl ist, nicht so. Das ist meine Kolumne hier mit dem Namen Adjust2. Anpassen2. Und hier ist was wir tun werden. Ich werde sagen, nimm die MID von A2 und beginne sie an der Stelle in G2-2 für eine Länge von 1, addiere 0 dazu und frage, ist das eine Zahl oder nicht? Okay, das ist eine Nummer. Und dann werden wir auch nach der Situation suchen, in der es sich um ein zweistelliges Datum handelt, also um den 20. Januar. Also das heißt Adjust3, gehe 3 Zeichen von wo zurück. Also gibt es das Wo, gehe drei Zeichen für die Länge von 1 zurück, füge 0 hinzu und schau, ob das 'Eine Nummer, okay? Dann werden wir uns anpassen und das angepasste Wo sagt WENN. WENN es dieser seltsame Fall 0 ist, werden wir nur einen wirklich großen Wert von 999 setzen; Andernfalls gehen wir von G2 zurück und gehen entweder 3 zurück, wenn Adjust3 True ist, oder gehen 2 zurück, wenn Adjust2 True ist, oder wenn keines davon True ist, wird das Wo dort sein, wo der Monat beginnt. Okay, jetzt, da wir wissen, dass das angepasst wurde, doppelklicken wir, um das nach unten zu kopieren. Nun, hey, jetzt ist es wirklich einfach. Wir werden nur - für den Titel sagen wir links von A2, wie viele Zeichen wir wollen. Wir wollen D2-1, weil das -1 ist, um den Platz am Ende loszuwerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.WENN es dieser seltsame Fall 0 ist, werden wir nur einen wirklich großen Wert von 999 setzen; Andernfalls gehen wir von G2 zurück und gehen entweder 3 zurück, wenn Adjust3 True ist, oder gehen 2 zurück, wenn Adjust2 True ist, oder wenn keines davon True ist, wird das Wo dort sein, wo der Monat beginnt. Okay, jetzt, da wir wissen, dass das angepasst ist, doppelklicken wir, um das nach unten zu kopieren. Nun, hey, jetzt ist es wirklich einfach. Wir werden nur - für den Titel sagen wir links von A2, wie viele Zeichen wir wollen. Wir wollen D2-1, weil das -1 ist, um den Platz am Ende loszuwerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.WENN es dieser seltsame Fall 0 ist, werden wir nur einen wirklich großen Wert von 999 setzen; Andernfalls gehen wir von G2 zurück und gehen entweder 3 zurück, wenn Adjust3 True ist, oder gehen 2 zurück, wenn Adjust2 True ist, oder wenn keines davon True ist, wird das Wo dort sein, wo der Monat beginnt. Okay, jetzt, da wir wissen, dass das angepasst wurde, doppelklicken wir, um das nach unten zu kopieren. Nun, hey, jetzt ist es wirklich einfach. Wir werden nur - für den Titel sagen wir links von A2, wie viele Zeichen wir wollen. Wir wollen D2-1, weil das -1 ist, um den Platz am Ende loszuwerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.oder wenn keines davon wahr ist, wird das Wo sein, wo der Monat beginnt. Okay, jetzt, da wir wissen, dass das angepasst wurde, doppelklicken wir, um das nach unten zu kopieren. Nun, hey, jetzt ist es wirklich einfach. Wir werden nur - für den Titel sagen wir links von A2, wie viele Zeichen wir wollen. Wir wollen D2-1, weil das -1 ist, um den Platz am Ende loszuwerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.oder wenn keines davon wahr ist, wird das Wo sein, wo der Monat beginnt. Okay, jetzt, da wir wissen, dass das angepasst wurde, doppelklicken wir, um das nach unten zu kopieren. Nun, hey, jetzt ist es wirklich einfach. Wir werden nur - für den Titel sagen wir links von A2, wie viele Zeichen wir wollen. Wir wollen D2-1, weil das -1 ist, um den Platz am Ende loszuwerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.s die -1 soll den Platz am Ende loswerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.s Die -1 soll den Platz am Ende loswerden. Obwohl ich denke, dass der TRIM am Ende auch den Platz los wird.

Und dann werden wir für das Datum die MID verwenden. MID für MID von A2 beginnend mit dem angepassten Wo in D2 und gehen Sie 50 oder was auch immer entlang, von dem Sie denken, dass es möglicherweise sein könnte, und dann die TRIM-Funktion, und wir werden doppelklicken, um das nach unten zu kopieren.

Okay, der Grund, warum ich mich an Mike gewandt habe, ist, dass ich gesagt habe, ich frage mich, ob es eine Möglichkeit gibt, diese 12 Spalten durch ein einzelnes Formular zu ersetzen, tatsächlich diese 13 Spalten durch ein einzelnes Formular. Gibt es eine Möglichkeit, dies mit einer Array-Formel zu tun? Und Mike hat natürlich dieses großartige Buch, Strg + Umschalt + Eingabetaste, über Array-Formeln geschrieben. Und ich habe ein paar verschiedene Dinge ausprobiert und in meinen Gedanken gab es keine Möglichkeit, dies zu tun. Okay, aber Sie wissen, lassen Sie uns den Experten fragen. Also Mike, mal sehen, was du hast.

Mike Girvin: Thanks,. Hey, and speaking of expert, this was pretty expertly done. You used FIND, AGGREGATE, ISNUMBER(MID. Now, when you sent this question over to me, I went ahead and solved it and it is amazing how similar my solution is to yours.

Alright, I'm going to go over to this sheet here. I'm going to start with figuring out where the start position in this text string is for each particular month. Now the way I'm going to do it is I'm going to, hey, use this SEARCH function. Now, you used FIND, I use SEARCH. Actually probably FIND is better in this situation because FIND is case-sensitive, SEARCH is not. Now normally what we do with either FIND or SEARCH, I say, hey, go FIND, January, comma within this larger text string, that's how we normally use SEARCH Ctrl+Enter, and it counts on its finger: one, two, three, four, five. It says the 32nd character is where it found January.

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.

Deshalb möchte ich mich bei allen für ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Duell-Excel-Podcast von und Excel macht Spaß.

Download-Datei

Laden Sie die Beispieldatei hier herunter: Duel180.xlsm