Certaines des questions qui se posent sont assez difficiles. Aujourd'hui, nous avons une colonne de cellules. Chaque cellule a quelques mots, puis une date, puis d'autres mots. Le but est d'extraire la partie date de ce texte dans une nouvelle colonne. Il s'agit d'un épisode de duel avec des idées de Bill et Mike.
Regarder la vidéo
- L'approche super large de Bill:
- Mettez les 12 mois dans des colonnes séparées
- Utilisez la fonction FIND pour voir si ce mois est dans le texte d'origine
- Pour trouver la position de départ minimale, utilisez = AGGREGATE (5,6,…
- Quelques formules supplémentaires pour rechercher un numéro 2 ou 3 positions avant le mois
- L'approche de Mike:
- Utilisez SEARCH au lieu de FIND. La recherche est sensible à la casse, la recherche ne l'est pas.
- Créez une opération de tableau d'arguments de fonction en spécifiant B13: B24 comme Find_Text.
- La formule renvoie #VALUE! Erreur, mais si vous appuyez sur F2, F9, vous verrez qu'il renvoie un tableau.
- Les 13 premières fonctions d'AGGREGATE ne peuvent pas gérer un tableau, mais les fonctions 14 à 19 peuvent gérer un tableau.
- 5 = MIN et 15 = SMALL (, 1) sont similaires, mais SMALL (, 1) fonctionnera avec un tableau.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX et AGGREGATE peuvent gérer les arguments de tableau de fonctions sans Ctrl + Maj + Entrée
- Mike était plus intelligent en cherchant à voir si 2 caractères avant le début sont un nombre, puis en saisissant 3 caractères avant. L'espace supplémentaire est éliminé par le TRIM ()
- Pour obtenir le titre, utilisez la fonction SUBSTITUTE pour supprimer le texte Date dans la colonne C
Transcription vidéo
Bill Jelen: Salut, bon retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen. Je serai accompagné de Mike Girvin d'Excel Is Fun.
Ceci est notre Duel # 170: Trouver des dates
Hé, bienvenue à tous. J'avais une si bonne question ici et je n'ai pas pu la résoudre. Au moins, je ne pouvais pas le résoudre facilement, alors je suis allé voir Mike Girvin et j'ai dit: «Mike, hé, avez-vous un moyen de le faire?» Il a dit: «Oui, j'ai un moyen de le faire. Faisons un duel.
Ainsi, quelqu'un sur YouTube a envoyé ces données et chaque cellule en général a quelque chose comme un titre de document suivi d'une date. Ils voulaient diviser ces données dans le titre du document: ce que c'est, ce qu'est la chose et ensuite quelle est la date. Mais les dates sont complètement mauvaises. Comme ici, c'est le 20 janvier; mais ici, il y a des choses où la date pourrait être après la cellule, le 9 avril. D'accord, et peu importe de quelle manière nous voulons le trouver. Et parfois, il y a deux dates et c'est tout simplement horrible et que c'est juste une situation mélangée de dates et que possible, ne pas même avoir de rendez-vous, d'accord. Alors, voici ma tentative. Sur le côté droit, je vais mettre les choses que je recherche. Ce que j'aime vraiment ici, c'est qu'ils n'ont jamais abrégé le nom du mois. Vraiment,vraiment apprécier cela. Alors tapez janvier et je vais traîner ici jusqu'en décembre comme ça, et pour chaque cellule que je veux savoir, pouvons-nous trouver = TROUVER ce janvier. Je vais donc appuyer sur F4 une, deux fois pour le verrouiller à juste une ligne, dans le texte là-bas dans la colonne A, comme ça. Je vais appuyer sur F4 une, deux, trois fois pour le verrouiller dans la colonne, d'accord. Et là, ça nous dit que janvier se trouve en position 32 et que pour les 11 autres mois, ça va nous dire qu'on ne le trouve pas du tout. En d'autres termes, nous obtenons maintenant l'erreur Value. Ce que je dois faire, c'est que je dois trouver, je dois trouver la valeur minimale en ignorant toutes les erreurs de valeur. Donc, affichez cette petite formule ici = AGGREGATE et construisons ceci juste à partir de zéro, = AGGREGATE, ce que nous voulons, c'est le MIN donc c'est le nombre 5,puis Ignorez les valeurs d'erreur numéro 6 virgule, puis toutes ces cellules de janvier à décembre. Et ce que ça va nous dire, c'est que ça va nous dire où se passe le mois. Et dans ce cas, nous allons obtenir 0, disons que le mois ne se produit pas du tout.
Très bien maintenant, dévoilons le reste. Donc, pour gérer la situation où nous avons ici le 20 janvier ou le 1er novembre, j'ai dit que la première chose que je vais faire est de regarder où ce mois commence et de revenir en arrière deux cellules, deux cellules, deux caractères , deux personnages. Et voyez si c'est un nombre, non. C'est ma colonne ici appelée, Adjust2. Ajuster 2. Et voici ce que nous allons faire. Je vais dire, prenez le MID de A2 en commençant par où dans G2-2 pour une longueur de 1, ajoutez-y 0 et demandez, est-ce un nombre ou non? Très bien, c'est donc un nombre. Et puis, nous chercherons également la situation où il s'agit d'une date à 2 chiffres, donc le 20 janvier. Donc, cela s'appelle Adjust3, remontez de 3 caractères à partir de où. Donc, il y a le Où, remontez de trois caractères pour une longueur de 1, ajoutez-y 0 et voyez si cela 'un numéro, d'accord? Ensuite, nous allons ajuster et le Où ajusté dit IF. SI c'est ce cas étrange était 0, nous allons juste mettre une valeur vraiment grande 999; sinon, nous allons passer de G2 et soit revenir en arrière 3, si Adjust3 est True, soit revenir en arrière 2 si Adjust2 est True, ou si aucun de ceux-ci n'est True, le Où va être le début du mois. Très bien, maintenant que nous savons que cet endroit ajusté, nous allons double-cliquer pour le copier. Eh bien, hé maintenant, c'est vraiment facile. Nous allons juste - pour le titre, nous allons dire prendre la gauche de A2, combien de caractères nous voulons. Nous voulons D2-1 car c'est le -1 qui consiste à se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.SI c'est ce cas étrange était 0, nous allons juste mettre une valeur vraiment grande 999; sinon, nous allons passer de G2 et soit revenir en arrière 3, si Adjust3 est True, soit revenir en arrière 2 si Adjust2 est True, ou si aucun de ceux-ci n'est True, le Où va être le début du mois. Très bien, maintenant que nous savons que cet endroit ajusté, nous allons double-cliquer pour le copier. Eh bien, hé maintenant, c'est vraiment facile. Nous allons juste - pour le titre, nous allons dire prendre la gauche de A2, combien de caractères nous voulons. Nous voulons D2-1 car c'est le -1 qui consiste à se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.SI c'est ce cas étrange était 0, nous allons juste mettre une valeur vraiment grande 999; sinon, nous allons passer de G2 et soit revenir en arrière 3, si Adjust3 est True, soit revenir en arrière 2 si Adjust2 est True, ou si aucun de ceux-ci n'est True, le Où va être le début du mois. Très bien, maintenant que nous savons que cet endroit ajusté, nous allons double-cliquer pour le copier. Eh bien, hé maintenant, c'est vraiment facile. Nous allons juste - pour le titre, nous allons dire prendre la gauche de A2, combien de caractères nous voulons. Nous voulons D2-1 car c'est le -1 qui consiste à se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.ou si aucun de ceux-ci n'est vrai, le Où va être là où le mois commence. Très bien, maintenant que nous savons que cet endroit ajusté, nous allons double-cliquer pour le copier. Eh bien, hé maintenant, c'est vraiment facile. Nous allons juste - pour le titre, nous allons dire prendre la gauche de A2, combien de caractères nous voulons. Nous voulons D2-1 car c'est le -1 qui consiste à se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.ou si aucun de ceux-ci n'est vrai, le Où va être là où le mois commence. Très bien, maintenant que nous savons que cet endroit ajusté, nous allons double-cliquer pour le copier. Eh bien, hé maintenant, c'est vraiment facile. Nous allons juste - pour le titre, nous allons dire prendre la gauche de A2, combien de caractères nous voulons. Nous voulons D2-1 car c'est le -1 qui consiste à se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.s le -1 est de se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.s le -1 est de se débarrasser de l'espace à la fin. Bien que je suppose que le TRIM se débarrasse également de l'espace à la fin.
Et puis pour la date, nous allons utiliser le MID. MID pour MID de A2 en commençant à l'endroit ajusté dans D2 et en sortant 50 ou tout ce que vous pensez que cela pourrait être, puis la fonction TRIM, et nous double-cliquerons pour copier cela vers le bas.
Très bien maintenant, la raison pour laquelle j'ai contacté Mike est que j'ai dit, je me demande s'il existe un moyen de remplacer ces 12 colonnes par un seul formulaire, en fait ces 13 colonnes par un seul formulaire. Existe-t-il un moyen de le faire en utilisant une formule Array? Et Mike, bien sûr, a écrit ce grand livre, Ctrl + Maj + Entrée, sur les formules Array. Et j'ai essayé plusieurs choses différentes et dans mon esprit, il n'y avait aucun moyen de le faire. D'accord, mais vous savez, allons demander à l'expert. Alors Mike, voyons ce que vous avez.
Mike Girvin: Merci ,. Hé, et en parlant d'expert, cela a été fait de manière assez experte. Vous avez utilisé FIND, AGGREGATE, ISNUMBER (MID. Maintenant, lorsque vous m'avez envoyé cette question, je suis allé de l'avant et je l'ai résolue et il est étonnant de voir à quel point ma solution est similaire à la vôtre.
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.
Donc, je tiens à remercier tout le monde d'être passé. Nous vous reverrons la prochaine fois pour un autre podcast Dueling Excel de et Excel Is Fun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel180.xlsm
