Σήμερα είναι μια τρελή ερώτηση. Έχετε μια στήλη αριθμών μερών. Υπάρχουν οπουδήποτε από 4 έως 7 παύλες στον αριθμό εξαρτήματος. Θέλετε να εξαγάγετε μόνο το τμήμα του αριθμού ανταλλακτικού μετά την πρώτη παύλα και μέχρι, αλλά όχι την τελευταία παύλα. Αυτό είναι ένα επεισόδιο του Dueling στο Excel.
Δες το βίντεο
- Στόχος είναι να βρείτε την πρώτη και τελευταία παύλα και να διατηρήσετε τα πάντα ενδιάμεσα
- Το δύσκολο μέρος είναι να βρεις την τελευταία παύλα
- Μέθοδος λογαριασμού 1: Συμπλήρωση Flash
- Συμπληρώστε με μη αυτόματο τρόπο τα πρώτα (συμπεριλαμβανομένων ορισμένων με διαφορετικούς αριθμούς παύλων)
- Επιλέξτε το κενό κελί κάτω από αυτό
- Ctrl + E στο Flash Fill
- Μέθοδος Mike 2:
- Χρησιμοποιήστε το Power Query
- Στο Excel 2016, το Power Query βρίσκεται στην ομάδα Get & Transform στο Excel 2016
- Στο Excel 2010 & 2013, κατεβάστε το Power Query από τη Microsoft. Δημιουργεί μια νέα καρτέλα Power Query στο Ribbon
- Μετατρέψτε τα δεδομένα σας σε έναν πίνακα χρησιμοποιώντας Ctrl + T
- Χρησιμοποιήστε διαχωρισμένα δεδομένα στο Power Query - πρώτα για διαχωρισμό στην αριστερή παύλα και, στη συνέχεια, για διαχωρισμό στην πιο δεξιά παύλα
- Μέθοδος λογαριασμού 3:
- Λειτουργία VBA που επαναλαμβάνει από το τέλος του κελιού προς τα πίσω για να βρει την τελευταία παύλα
- Μέθοδος Mike 4:
- Χρησιμοποιήστε το SUBSTITUTE για να βρείτε τη θέση της Nth παύλας
- SUBSTITUTE είναι η μόνη λειτουργία κειμένου που σας επιτρέπει να καθορίσετε έναν αριθμό παρουσίας
- Για να βρείτε τον αριθμό παρουσίας, χρησιμοποιήστε το
=LEN(A2)-LEN(SUBSTITUTE)
Μεταγραφή βίντεο
Μπιλ: Γεια. Καλως ορισες πισω. Ήρθε η ώρα για ένα άλλο podcast Dueling Excel. Είμαι ο Bill Jelen από τον MrExcel. (Θα ενωθώ με τον Mike Girvin από το ExcelIsFun. Αυτό είναι το επεισόδιο 185: - 00:03): απόσπασμα από το πρώτο - στο τελευταίο -.
Καλώς. Η σημερινή ερώτηση αποστέλλεται από τον Anvar στο YouTube. Πώς μπορώ να εξαγάγω τα πάντα από το πρώτο - στο τελευταίο - και να δω αυτά τα δεδομένα που έχει εδώ. Υπάρχει ένας τεράστιος αριθμός παύλων, οπουδήποτε από 3, 5, 6, 7 παύλες, εντάξει;
Λοιπόν, η πρώτη μου σκέψη είναι, καλά, είναι πολύ εύκολο να βρεις το πρώτο - σωστά; = αριστερά ή = MID του FIND του A2 και μετά το -, +1 εντάξει, αλλά για να φτάσω στο τελευταίο -, αυτό θα κάνει το κεφάλι μου να πονάει, σωστά, γιατί, λοιπόν, πόσες παύλες έχουμε; Θα μπορούσαμε να πάρουμε το SUBSTITUTE του A2, αντικαθιστώντας τις παύλες και να συγκρίνουμε το μήκος του, το αρχικό μήκος. Αυτό μου λέει τον αριθμό των παύλων, αλλά τώρα ξέρω ποιο - για να βρω, το 2ο, 3ο, 4ο, 5ο, αλλά χρησιμοποιώ το FIND;
Ήμουν έτοιμος να πάω στο VBA, σωστά; Αυτή είναι η αντίδρασή μου. Είπα, περίμενε ένα δευτερόλεπτο. Είπα, Anvar, σε ποια έκδοση του Excel είσαι; Λέει, είμαι στο Excel 2016. Είπα, αυτό είναι όμορφο. Εάν βρίσκεστε στο Excel 2013 ή νεότερο, θα μπορούσαμε να χρησιμοποιήσουμε αυτήν την υπέροχη νέα δυνατότητα που ονομάζεται flash fill. Με τη συμπλήρωση φλας, πρέπει απλώς να του δώσουμε ένα μοτίβο και θα το δώσω αρκετό μοτίβο, οπότε δεν είναι μόνο ότι παίρνω ένα με δύο παύλες και το κάνω αυτό μερικές φορές. Θέλω να βεβαιωθώ ότι έχω μερικές διαφορετικές παύλες με αυτόν τον τρόπο. Το Τσαντ στην ομάδα του Excel ξέρει τι ψάχνω. Ο Τσαντ είναι ο τύπος που έγραψε τη λογική για το flash fill. Λοιπόν, έχω περίπου 3 από αυτούς εκεί και έπειτα το CONTROL + E είναι η συντόμευση για τη χρήση DATA και στη συνέχεια FLASH FILL και, βέβαια, φαίνεται ότι έκανε το σωστό. Εντάξει, Μάικ.Ας δούμε τι έχετε.
Mike: Ευχαριστώ, MrExcel. Ναι. Το Flash γεμίζει. Αυτό το χαρακτηριστικό εκεί, το flash fill, είναι ένα από τα σύγχρονα εργαλεία του Excel που είναι απλώς καταπληκτικό. Αν πρόκειται για μια εφάπαξ συμφωνία και έχετε ένα σταθερό μοτίβο, γεια, έτσι θα το έκανα.
Γεια, ας πάμε στο επόμενο φύλλο. Τώρα, αντί να χρησιμοποιούμε flash fill, μπορούμε πραγματικά να χρησιμοποιήσουμε power query. Τώρα, χρησιμοποιώ το Excel 2016, επομένως έχω την ομάδα GET & TRANSFORM. Αυτό είναι το ερώτημα ενέργειας. Σε παλαιότερες εκδόσεις, 2013 (έως 10 - 2:30), στην πραγματικότητα πρέπει να κάνετε λήψη του δωρεάν πρόσθετου ερωτήματος ενέργειας.
Τώρα, για να λειτουργήσει το ερώτημα ενέργειας, αυτό πρέπει να μετατραπεί σε πίνακα Excel. Τώρα, πάλι, θα χρησιμοποιούσα flash fill εάν ήταν μια μοναδική συμφωνία. Πότε θα χρησιμοποιούσατε το ερώτημα ενέργειας; Λοιπόν, εάν είχατε πραγματικά μεγάλα δεδομένα ή προέρχονταν από εξωτερική πηγή, αυτός θα ήταν ο τρόπος να πάτε, ή ίσως να σας αρέσει αυτό καλύτερα από το να πρέπει να πληκτρολογήσετε 3 ή 4 παραδείγματα για τη συμπλήρωση φλας, επειδή, με το ερώτημα ισχύος, μπορούμε συγκεκριμένα λένε βρείτε το πρώτο - και βρείτε το τελευταίο -.
Τώρα, πρόκειται να το μετατρέψω σε πίνακα Excel. Έχω επιλέξει ένα μόνο κελί, κενά κελιά. Πηγαίνω στο INSERT, TABLE, ή χρησιμοποιείτε το πληκτρολόγιο, CONTROL + T. Μπορώ να κάνω κλικ στο OK ή στο ENTER. Θέλω να ονομάσω αυτόν τον πίνακα, οπότε θα ανέβω στα ΕΡΓΑΛΕΙΑ ΤΡΑΠΕΖΙΟΥ, ΣΧΕΔΙΑΣΜΟΣ, σε ΙΔΙΟΤΗΤΕΣ. Θα ονομάσω αυτό το STARTKEYTABLE και το ENTER. Τώρα μπορώ να επιστρέψω στα ΔΕΔΟΜΕΝΑ, να το φέρω στο ερώτημα ισχύος χρησιμοποιώντας το κουμπί FROM TABLE. Υπάρχει η στήλη μου. Υπάρχει το όνομα. Δεν θέλω να διατηρήσω αυτό το όνομα επειδή η έξοδος θα εξαχθεί στο Excel και θέλω να του δώσω ένα διαφορετικό όνομα. Έτσι, θα το ονομάσω CLEANEDKEYTABLE. Δεν χρειάζομαι τον ΑΛΛΑΓΜΕΝΟ ΤΥΠΟ. Κοιτάω απλώς την πηγή. Τώρα μπορώ να κάνω κλικ στη στήλη και, επάνω στο HOME, υπάρχει το κουμπί SPLIT. Μπορώ να πω SPLIT, BY DELIMITER. Φαίνεται ότι έχει ήδη μαντέψει. ΕΓΩ'πρόκειται να πω ΑΡΙΣΤΕΡΑ-ΠΕΡΙΣΣΟΤΕΡΑ. Κάντε κλικ στο OK.
Τώρα, αν κοιτάξω εδώ βλέπω ΑΛΛΑΓΗ ΤΥΠΟΥ. Δεν το χρειάζομαι, γι 'αυτό θα ξεφορτωθώ αυτό το βήμα. Έχω μόνο SPLIT COLUMN BY DELIMITER. Τώρα, θα το κάνω ξανά, αλλά αντί να χρησιμοποιήσω το κουμπί SPLIT εδώ, κάντε δεξί κλικ στο SPLIT COLUMN, BY DELIMITER και δείτε το. Μπορούμε να επιλέξουμε να το χωρίσουμε με το ΔΕΞΙΟ-ΠΙΟ ΔΙΑΔΙΚΤΥΟ. Κάντε κλικ στο OK. Τώρα, δεν χρειάζομαι αυτές τις δύο στήλες, οπότε θα κάνω δεξί κλικ στη στήλη που θέλω να κρατήσω, ΚΑΤΑΡΓΗΣΗ ΑΛΛΩΝ ΣΤΗΛΩΝ. Πραγματικά πρόκειται να βγάλω αυτόν τον ΑΛΛΑΓΜΕΝΟ ΤΥΠΟ. Θα πει ότι ΣΑΣ ΒΕΒΑΙΖΕΤΕ ΘΕΛΕΤΕ ΝΑ ΔΙΑΓΡΑΦΕΙΤΕ; Θα πω, ναι, ΔΙΑΓΡΑΦΗ. Υπάρχουν τα καθαρά δεδομένα μου.
Τώρα μπορώ να έρθω στο CLOSE & LOAD. ΚΛΕΙΣΙΜΟ & ΦΟΡΤΙΟ. Αυτό είναι το νέο παράθυρο διαλόγου IMPORT. Συνήθιζε να λέει LOAD TO, αλλά θέλω να το φορτώσω σε ένα τραπέζι, σε ένα ΥΠΑΡΧΟΝ ΕΡΓΑΣΤΗΡΙΟ. Κάντε κλικ στο κουμπί σύμπτυξης. Θα επιλέξω C1, ξεμπλοκάρετε, κάντε κλικ στο OK και θα πάμε. Ισχυρό ερώτημα για τον καθαρισμό των δεδομένων μας και τη λήψη των δεδομένων που θέλουμε. Καλώς. Θα το ρίξω πίσω.
Μπιλ: Υπάρχει το σημείο εκεί, το πιο σωστό DELIMITER στο SPLIT COLUMN BY DELIMITER, ένα από τα δροσερά χαρακτηριστικά του power query. Είναι υπέροχο.
Καλώς. Η αντίδρασή μου στο γόνατο - VBA UDF (ακατανόητο - 05:34) πολύ εύκολο να κάνω VBA. Μετάβαση σε ALT + F11. ΕΙΣΑΓΩΓΕΤΕ ΜΟΝΑΔΑ. Σε αυτήν την ενότητα, πληκτρολογήστε αυτόν τον κωδικό. Πρόκειται να (δημιουργήσω ένα - 05:43) ολοκαίνουργια συνάρτηση, θα το ονομάσω MIDPART και θα το διαβιβάσω κάποιο κείμενο και μετά αυτό που πρόκειται να κάνω είναι θα πάμε από τον τελευταίο χαρακτήρα σε αυτό το κελί από το μήκος του MYTEXT πίσω στο 1, ΒΗΜΑ -1 και να δούμε αυτόν τον χαρακτήρα. Έτσι, το MID του MYTEXT, αυτή η μεταβλητή i, μας λέει ποιος χαρακτήρας ψάχνουμε για μήκος 1. Είναι ένα -; Μόλις βρω ένα -, θα πάρω το ΑΡΙΣΤΕΡΟ του MYTEXT ξεκινώντας από το χαρακτήρα i - 1, οπότε ξεφορτώνομαι τα πάντα για αυτό το τελευταίο - όλη τη διέξοδο και, στη συνέχεια, βεβαιωθείτε ότι δεν πάω συνεχίστε να ψάχνετε για περισσότερες παύλες, το EXIT FOR θα με βγάλει από αυτό (ακατάληπτο - 06:17) βρόχο,και από εκεί είναι το εύκολο μέρος. Απλώς πρόκειται να πάρουμε το MYTEXT, ξεκινώντας από το MID του MYTEXT, (όπου χρησιμοποιώ το - 06:26) χρησιμοποιήστε τη συνάρτηση FIND για να βρείτε το πρώτο -, πηγαίνετε 1 περισσότερο από αυτό και επιστρέψτε το πίσω.
So, let's go back, ALT+Q, to return to Excel. = MIDPART tab of that, and it looks like it's working. Copy that down. Mike, do you have another one? (=MIDPart(A2))
Mike: Well, I do have another one,, but it's going to be one long formula -- not as short as that UDF. Alright, let's go over to the next sheet. Now, if we're going to do a formula and we have some text and there are always a different number of delimiters, somehow, I need to get the position of that last delimiter.
Now, this is going to take a few steps but I'm going to start with the SUBSTITUTE function. I'm going to look through that text, , the old text I want to find is in ”, that -, , and what do I want to put in its place or substitute? “”. That will put nothing in. Now, if I ) and CONTROL+ENTER, what is that going to do? (=SUBSTITUTE(A2,“-”,“”))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Λοιπόν, πήγαινε. Θέλω να ευχαριστήσω όλους που σταμάτησαν. Θα σας δούμε την επόμενη φορά για ένα άλλο podcast Dueling Excel από και το ExcelIsFun.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Duel185.xlsm
