Comment séparer une colonne de données Excel en deux colonnes. Comment analyser les données dans Excel.
Regarder la vidéo
- La première méthode de Bill utilisant le texte en colonnes (disponible dans l'onglet Données).
- À l'étape 1, choisissez délimité. À l'étape 2, choisissez un espace. Ignorez l'étape 3 en cliquant sur Terminer.
- Le texte sera divisé à chaque espace, donc tout ce qui contient trois mots se retrouvera dans 3 cellules. Remettez-les ensemble avec
=TEXTJOIN(" ",True,B2:E2)ou - avec
=B2&" "&C2&" "&D2 - La première méthode de Mike utilise Power Query. Power Query est Get & Transform en 2016 ou en téléchargement gratuit pour 2010 ou 2013.
- Commencez par convertir vos données en table à l'aide de Ctrl + T. Puis, dans Power Query, à partir de Table. Colonne divisée, par délimiteur. Sélectionnez Espace, puis au délimiteur le plus à gauche.
- Vous pouvez renommer une colonne en double-cliquant!
- Fermer et charger vers… et choisir un nouvel emplacement sur la feuille de calcul.
- La deuxième méthode de Bill consiste à utiliser Flash Fill. Tapez de nouveaux titres dans A, B et C. Flash Fill ne fonctionnera pas si vous n'en avez pas! Tapez un modèle pour les deux premières lignes.
- Accédez à la première cellule vide de B et appuyez sur Ctrl + E. Répétez pour la colonne C.
- La deuxième méthode de Mike consiste à utiliser ces formules:
- Pour la première partie, utilisez
=LEFT(A2,SEARCH(" ",A2)-1) - Pour la deuxième partie, utilisez
=SUBSTITUTE(A2,B2&" ","")
Transcription vidéo
(La musique)
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. C'est notre
Épisode 182: Diviser les données d'une cellule pour apparaître dans deux cellules.
D'accord, la question d'aujourd'hui est envoyée par Tom. Existe-t-il un moyen de diviser facilement les données dans une cellule pour que les données apparaissent dans deux cellules? Par exemple, 123 Main Street, il veut 123 dans une cellule et Main Street dans une autre cellule; ou, Howard et Howard, puis End. J'ai passé d'innombrables heures à séparer ce type de données. J'apprécierais avoir des nouvelles de votre entreprise, car il existe de nombreuses façons différentes de le faire.
La première chose que je vais faire est de sélectionner toutes les données, Ctrl + Maj + Flèche bas, puis Données, Texte en colonnes. Texte en colonnes à l'étape 1, les données sont délimitées. Il est délimité par un espace, puis cliquez simplement sur Terminer. Maintenant, le problème avec cette méthode est que si vous avez 123 Main Street, il va se retrouver dans 3 cellules au lieu de 2 cellules. Oh, Power Query rendrait cela tellement plus facile, mais nous y sommes. D'accord, alors ce que je vais faire, c'est que je vais sortir loin à droite des données où je sais que là où tout est construit. Si je suis dans Office 365, je vais utiliser TEXTJOIN. TEXTJOIN, ce truc génial, le délimiteur est un espace. Ignorez les cellules vides True, puis les cellules que je veux concaténer ensemble comme ça, et je copie simplement toutes celles-ci vers le bas, Ctrl + V. Je vais copier Ctrl + C puis Accueil, Coller,Coller en tant que valeurs et à ce stade, je peux supprimer ces 3 colonnes supplémentaires.
Ahh, mais personne n'a Office 365, non? Donc, si vous n'avez pas Office 365, vous devez faire = cette chose & "" & cela, puis s'il y avait plus de "" et cela, et s'il y en avait plus, continuez. Dans ce cas, c'est inutile car il n'y a rien de plus en D mais vous voyez l'idée. Ctrl + C, copiez-le jusqu'à la dernière ligne de données, Ctrl + V puis Ctrl + C, Alt + ESV pour créer ces valeurs B. Et nous y voilà, d'accord. Mike voyons ce que vous avez.
Mike Girvin: Merci ,. Hé, vous m'avez lancé une question facile ici parce que vous avez déjà mentionné Get & Transform Power Query, l'ancien Text to Columns vous permet seulement de dire un espace à chaque caractère, non? Eh bien, si nous utilisons Power Query, nous pouvons utiliser ce délimiteur et dire: "Hé, divisez simplement à la première occurrence."
Maintenant, pour obtenir ces données dans l'éditeur de requête, nous devons les convertir en un tableau Excel. Alors je monte dans Insertion, Tableau ou j'utilise Ctrl + T. Mon tableau a des en-têtes, le bouton OK est mis en surbrillance pour que je puisse cliquer dessus avec ma souris ou appuyer simplement sur Entrée. Maintenant, je veux nommer cette table, je vais donc venir ici, OriginalData et Enter. Maintenant, c'est un tableau Excel, nous pouvons arriver aux données et là c'est à partir du tableau. Cela l'amènera d'Excel dans l'éditeur. La colonne est sélectionnée: Onglet du ruban Accueil, on peut dire Diviser la colonne par délimiteur ou venir ici et faire un clic droit, Diviser la colonne par délimiteur. Dans la liste déroulante, nous pouvons dire, hé, utiliser un espace et regarder cela au délimiteur le plus à gauche. Lorsque je clique sur OK, BOOM! Le voilà. Maintenant, je vais nommer ces deux colonnes: double-cliquez sur Part 1 Enter, double-cliquez sur Part 2 et Enter. Maintenant,Je peux venir ici ou Fermer et charger, fermer et charger vers et je peux choisir où mettre cela. Je veux vraiment le vider en tant que tableau, nouvelle feuille de calcul, feuille de calcul existante. Mettez ceci en évidence, cliquez sur le bouton Réduire. Je vais dire D1, cliquez sur OK puis sur Charger. Et voilà, notre sortie Power Query.
Très bien, reviens.
Bill Jelen: Oh, Mike, Power Query est génial! Ouais, c'est une excellente façon de procéder. En voici un autre qui pourrait fonctionner si vous avez Excel 2013 ou plus récent.
Et ce que nous allons faire est de sortir ici et de dire la première partie, puis la deuxième partie. Assurez-vous de mettre ces en-têtes pour que si vous ne mettez pas ces en-têtes, ils ne doivent pas l'être mais ils doivent avoir des en-têtes ou cela ne fonctionnera pas. Je vais mettre 123 et Main Street, puis nous mettrons Howard et End, comme ça. Maintenant que nous avons un joli petit modèle là-bas, sortez ici dans l'onglet Données et Remplissage Flash qui est Ctrl + E, appuyez sur Ctrl + E juste là, puis appuyez sur Ctrl + E juste là. La belle chose est que nous n'avons pas à concaténer des données ensemble comme dans mon exemple. Très bien, Mike, revenons à vous.
Mike Girvin: Ding-ding-ding. C'est le gagnant sans aucun doute. Flash Fill est le moyen d'y arriver. Remarquez que nous n'avons pas eu à le convertir en table ou à ouvrir une boîte de dialogue; juste tapé quelques exemples, puis Ctrl + E.
Bon, eh bien, nous pourrions le faire avec des formules même si Flash Fill serait probablement plus rapide. Eh bien, regardez ceci, le modèle comme cette cellule de liste utilisée dans Flash Fill est tout avant le premier espace, puis tout après. Alors bon, je vais utiliser la fonction GAUCHE, le texte est juste là et combien de caractères à gauche? Eh bien, je vais chercher cet espace - 1 2 3 4 en utilisant la fonction RECHERCHE, Rechercher le texte, l'espace et "", à l'intérieur de cela. Maintenant, notez que Search compterait sur ses doigts 1 2 3 4 et cela arriverait à cet espace que je veux, cet espace donc I -1) Ctrl + Entrée, double-cliquez et envoyez-le vers le bas. Donc, cela obtient toujours tout avant le premier espace.
Maintenant, notez que nous avons déjà le texte ici pour que je puisse utiliser la fonction SUBSTITUTE. Le texte que je vais parcourir est les données complètes, la virgule, l'ancien texte que je veux rechercher, puis le SUBSTITUT. Rien n'est presque 1 2 3. Je veux en fait ajouter l'espace que je viens de retirer dans la formule précédente, de retour. Maintenant, il cherchera 1 2 3, Space et ensuite Howard, Space et ainsi de suite, virgule et ensuite le nouveau texte dans lequel je veux remplacer. Eh bien, pour dire à SUBSTITUTE que vous voulez le remplacer par rien, vous dites «» pas d'espace entre les deux, fermez les parenthèses et cela fonctionnera. Ctrl + Entrée, double-cliquez et envoyez-le vers le bas. Bien? Il suffit de le renvoyer.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Très bien, 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: Duel182.xlsm
