Μερικές από τις ερωτήσεις που έρχονται είναι αρκετά δύσκολες. Σήμερα, έχουμε μια στήλη κελιών. Κάθε κελί έχει μερικές λέξεις, μετά μια ημερομηνία, και μερικές ακόμη λέξεις. Ο στόχος είναι να τραβήξετε το τμήμα ημερομηνίας αυτού του κειμένου σε μια νέα στήλη. Αυτό είναι ένα επεισόδιο μονομαχίας με ιδέες από τον Bill και τον Mike.
Δες το βίντεο
- Η εξαιρετικά ευρεία προσέγγιση του Μπιλ:
- Τοποθετήστε και τους 12 μήνες σε ξεχωριστές στήλες
- Χρησιμοποιήστε τη συνάρτηση FIND για να δείτε εάν αυτός ο μήνας είναι στο αρχικό κείμενο
- Για να βρείτε την ελάχιστη αρχική θέση, χρησιμοποιήστε = AGGREGATE (5,6,…
- Μερικές επιπλέον φόρμουλες για να αναζητήσετε μια θέση 2 ή 3 θέσεις πριν από το μήνα
- Η προσέγγιση του Mike:
- Χρησιμοποιήστε το SEARCH αντί για το FIND. Η εύρεση είναι διάκριση πεζών-κεφαλαίων, η Αναζήτηση δεν είναι.
- Δημιουργήστε μια λειτουργία συστοιχίας ορίσματος συνάρτησης καθορίζοντας το B13: B24 ως Find_Text.
- Ο τύπος επιστρέφει # VALUE! Σφάλμα, αλλά αν πατήσετε F2, F9, θα δείτε ότι επιστρέφει έναν πίνακα.
- Οι πρώτες 13 συναρτήσεις στο AGGREGATE δεν μπορούν να διαχειριστούν έναν πίνακα, αλλά οι συναρτήσεις 14-19 μπορούν να χειριστούν έναν πίνακα.
- 5 = MIN και 15 = SMALL (, 1) είναι παρόμοια, αλλά το SMALL (, 1) θα λειτουργεί με έναν πίνακα.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX και AGGREGATE μπορούν να χειριστούν ορίσματα πίνακα λειτουργιών χωρίς Ctrl + Shift + Enter
- Ο Mike ήταν πιο έξυπνος ψάχνοντας να δει αν 2 χαρακτήρες πριν από την Έναρξη είναι ένας αριθμός και έπειτα έπιασε 3 χαρακτήρες πριν. Ο επιπλέον χώρος αποβάλλεται από το TRIM ()
- Για να αποκτήσετε τον Τίτλο, χρησιμοποιήστε τη συνάρτηση SUBSTITUTE για να απαλλαγείτε από το κείμενο Ημερομηνία στη στήλη Γ
Μεταγραφή βίντεο
Bill Jelen: Γεια σας, καλώς ήλθατε πίσω. Ήρθε η ώρα για ένα άλλο Dueling Excel Podcast. Είμαι ο Μπιλ Τζέλεν από. Θα ενωθώ από τον Mike Girvin από το Excel Is Fun.
Αυτή είναι η μονομαχία μας # 170: Εύρεση ημερομηνιών
Γεια, καλωσορίστε όλους. Είχα μια τόσο μεγάλη ερώτηση εδώ και δεν μπορούσα να το λύσω. Τουλάχιστον δεν μπορούσα να το λύσω εύκολα, έτσι βγήκα στον Mike Girvin και είπα: «Mike, hei, έχετε έναν τρόπο να το κάνετε αυτό;» Είπε, «Ναι, έχω έναν τρόπο να το κάνω. Ας κάνουμε μια μονομαχία. "
Άρα, κάποιος στο YouTube που έστειλε αυτά τα δεδομένα και κάθε κελί γενικά έχει κάτι σαν τίτλο εγγράφου ακολουθούμενο από ημερομηνία. Ήθελαν να χωρίσουν αυτά τα δεδομένα στον τίτλο του εγγράφου: τι είναι, ποιο είναι το θέμα και μετά ποια είναι η ημερομηνία. Αλλά οι ημερομηνίες είναι εντελώς κακές. Όπως εδώ, είναι 20 Ιανουαρίου. αλλά κάτω, υπάρχουν πράγματα που η ημερομηνία μπορεί να είναι μετά το κελί, 9 Απριλίου. Εντάξει, και ανεξάρτητα από τον τρόπο που θέλουμε να το βρούμε. Και μερικές φορές υπάρχουν δύο ημερομηνίες και αυτό είναι απολύτως φρικτό και ότι είναι μια τόσο απλή κατάσταση των ημερομηνιών και, όσο είναι δυνατόν, μην εμφανιστεί καν μια ημερομηνία, εντάξει. Λοιπόν, εδώ είναι η προσπάθειά μου. Στη δεξιά πλευρά, θα βάλω τα πράγματα που ψάχνω. Αυτό που μου αρέσει εδώ είναι ότι δεν συντόμευσαν ποτέ το όνομα του μήνα. Εγώ στ 'αλήθεια,πραγματικά το εκτιμώ αυτό. Οπότε πληκτρολογήστε τον Ιανουάριο και θα μεταφερθώ εδώ μέχρι τον Δεκέμβριο έτσι, και για κάθε κελί που θέλω να ξέρω μπορούμε να βρούμε = ΒΡΕΙΤΕ τον Ιανουάριο. Θα πατήσω λοιπόν το F4 ένα, δύο φορές για να το κλειδώσω σε μια σειρά, στο κείμενο εκεί πάνω στη στήλη Α, έτσι. Θα πατήσω το F4 μία, δύο, τρεις φορές για να το κλειδώσω στη στήλη, εντάξει. Και εδώ, μας λέει ότι ο Ιανουάριος βρίσκεται στη θέση 32 και για τους άλλους 11 μήνες, θα μας πει ότι δεν βρέθηκε καθόλου. Με άλλα λόγια, λαμβάνουμε το σφάλμα Value τώρα. Αυτό που πρέπει να κάνω εκεί πρέπει να βρω, πρέπει να βρω την ελάχιστη τιμή αγνοώντας όλα τα λάθη αξίας. Λοιπόν, εμφανίστε αυτήν τη μικρή φόρμουλα εδώ = AGGREGATE και ας φτιάξουμε αυτό από το μηδέν, = AGGREGATE, αυτό που θέλουμε είναι το MIN, οπότε αυτός είναι ο αριθμός 5,και, στη συνέχεια, Αγνοήστε τις τιμές σφάλματος με αριθμό 6 κόμμα και στη συνέχεια όλα αυτά τα κελιά από τον Ιανουάριο έως τον Δεκέμβριο. Και αυτό που θα μας πει είναι ότι θα μας πει πού συμβαίνει ο μήνας. Και σε αυτήν την περίπτωση, θα πάρουμε 0, ας πούμε ότι ο μήνας δεν συμβαίνει καθόλου.
Εντάξει τώρα, ας καταργήσουμε το υπόλοιπο. Έτσι, για να χειριστώ την κατάσταση που έχουμε εδώ στις 20 Ιανουαρίου ή την 1η Νοεμβρίου, είπα ότι το πρώτο πράγμα που θα κάνω είναι να κοιτάξω από πού ξεκινά αυτός ο μήνας και να επιστρέψω δύο κελιά, δύο κελιά, δύο χαρακτήρες , δύο χαρακτήρες. Και δείτε αν αυτός είναι ένας αριθμός, όχι έτσι. Αυτή είναι η στήλη μου που ονομάζεται εδώ, Adjust2. Προσαρμογή2. Και εδώ θα κάνουμε. Πρόκειται να πω, πάρτε το MID του A2 ξεκινώντας εκεί όπου στο G2-2 για μήκος 1, προσθέστε 0 σε αυτό και ρωτήστε, είναι αυτός ο αριθμός ή όχι; Εντάξει, έτσι είναι ένας αριθμός. Και στη συνέχεια, θα αναζητήσουμε επίσης την κατάσταση όπου είναι διψήφια ημερομηνία, δηλαδή στις 20 Ιανουαρίου. Έτσι λέγεται Adjust3, επιστρέψτε 3 χαρακτήρες από το πού. Υπάρχει λοιπόν το Where, επιστρέψτε τρεις χαρακτήρες για μήκος 1, προσθέστε 0 σε αυτό και δείτε αν αυτόένας αριθμός, εντάξει; Τότε θα προσαρμόσουμε και το Adjusted Where λέει IF. ΕΑΝ είναι ότι αυτή η περίεργη περίπτωση ήταν 0, απλώς πρόκειται να βάλουμε μια πολύ μεγάλη τιμή 999. Διαφορετικά, θα πάμε από το G2 και είτε θα επιστρέψουμε 3, εάν το Adjust3 είναι True είτε θα επιστρέψουμε 2 εάν το Adjust2 είναι True, ή εάν κανένα από αυτά δεν είναι True, το πού θα είναι όπου ξεκινά ο μήνας. Εντάξει, τώρα που γνωρίζουμε ότι το Adjusted Where, θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Λοιπόν, τώρα είναι πολύ εύκολο. Απλώς πρόκειται - για τον Τίτλο, θα πούμε στα αριστερά του A2, πόσους χαρακτήρες θέλουμε. Θέλουμε το D2-1 γιατί αυτό είναι το -1 για να απαλλαγούμε από το κενό στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.ΕΑΝ είναι ότι αυτή η περίεργη περίπτωση ήταν 0, απλώς πρόκειται να βάλουμε μια πολύ μεγάλη τιμή 999. Διαφορετικά, θα πάμε από το G2 και είτε θα επιστρέψουμε 3, εάν το Adjust3 είναι True είτε θα επιστρέψουμε 2 εάν το Adjust2 είναι True, ή εάν κανένα από αυτά δεν είναι True, το πού θα είναι όπου ξεκινά ο μήνας. Εντάξει, τώρα που γνωρίζουμε ότι το Adjusted Where, θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Λοιπόν, τώρα είναι πολύ εύκολο. Απλώς πρόκειται - για τον Τίτλο, θα πούμε στα αριστερά του A2, πόσους χαρακτήρες θέλουμε. Θέλουμε το D2-1 γιατί αυτό είναι το -1 για να απαλλαγούμε από το κενό στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.ΕΑΝ είναι ότι αυτή η περίεργη περίπτωση ήταν 0, απλώς πρόκειται να βάλουμε μια πολύ μεγάλη τιμή 999. Διαφορετικά, θα πάμε από το G2 και είτε θα επιστρέψουμε 3, εάν το Adjust3 είναι True είτε θα επιστρέψουμε 2 εάν το Adjust2 είναι True, ή εάν κανένα από αυτά δεν είναι True, το πού θα είναι όπου ξεκινά ο μήνας. Εντάξει, τώρα που γνωρίζουμε ότι το Adjusted Where, θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Λοιπόν, τώρα είναι πολύ εύκολο. Απλώς πρόκειται - για τον Τίτλο, θα πούμε στα αριστερά του A2, πόσους χαρακτήρες θέλουμε. Θέλουμε το D2-1 γιατί αυτό είναι το -1 για να απαλλαγούμε από το κενό στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.ή αν κανένα από αυτά δεν είναι αληθινό, το πού θα είναι εκεί που ξεκινά ο μήνας. Εντάξει, τώρα που γνωρίζουμε ότι το Adjusted Where, θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Λοιπόν, τώρα είναι πολύ εύκολο. Απλώς πρόκειται - για τον Τίτλο, θα πούμε στα αριστερά του A2, πόσους χαρακτήρες θέλουμε. Θέλουμε το D2-1 γιατί αυτό είναι το -1 για να απαλλαγούμε από το κενό στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.ή αν κανένα από αυτά δεν είναι αληθινό, το πού θα είναι εκεί που ξεκινά ο μήνας. Εντάξει, τώρα που γνωρίζουμε ότι το Adjusted Where, θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Λοιπόν, τώρα είναι πολύ εύκολο. Απλώς πρόκειται - για τον Τίτλο, θα πούμε στα αριστερά του A2, πόσους χαρακτήρες θέλουμε. Θέλουμε το D2-1 γιατί αυτό είναι το -1 για να απαλλαγούμε από το κενό στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.s -1 είναι να απαλλαγούμε από το χώρο στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.s -1 είναι να απαλλαγούμε από το χώρο στο τέλος. Αν και υποθέτω ότι το TRIM απαλλάσσει επίσης το χώρο στο τέλος.
Και μετά για την ημερομηνία, θα χρησιμοποιήσουμε το MID. MID for- MID του A2 ξεκινώντας από το Adjusted Where in D2 και βγείτε 50 ή οτιδήποτε άλλο πιστεύετε ότι θα μπορούσε να είναι, και στη συνέχεια η λειτουργία TRIM, και θα κάνουμε διπλό κλικ για να το αντιγράψουμε.
Εντάξει τώρα, ο λόγος που έφτασα στον Mike είναι ότι, αναρωτιέμαι αν υπάρχει τρόπος να αντικαταστήσω αυτές τις 12 στήλες με μία φόρμα, στην πραγματικότητα αυτές τις 13 στήλες με μία φόρμα. Υπάρχει κάποιος τρόπος που θα μπορούσα να το κάνω χρησιμοποιώντας έναν τύπο Array; Και ο Mike, φυσικά, έγραψε αυτό το υπέροχο βιβλίο, Ctrl + Shift + Enter, στους τύπους Array. Και δοκίμασα μερικά διαφορετικά πράγματα και στο μυαλό μου, δεν υπήρχε τρόπος να γίνει. Εντάξει, αλλά ξέρετε, ας ρωτήσουμε τον ειδικό. Λοιπόν, Mike, ας δούμε τι έχετε.
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.
Θέλω λοιπόν να ευχαριστήσω όλους που σταμάτησαν. Θα σας δούμε την επόμενη φορά για ένα άλλο Dueling Excel Podcast και το Excel Is Fun.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Duel180.xlsm
