Comment fusionner les lignes d'une colonne dans une cellule dans excel?
Par exemple
A1:I
A2:am
A3:a
A4:boy
Je veux les fusionner tous dans une seule cellule "Iamaboy"
Cet exemple montre que 4 cellules fusionnent en 1 cellule mais j'ai beaucoup de cellules (plus de 100), Je ne peux pas les taper une par une en utilisant A1 & A2 & A3 & A4 Que puis-je faire?
8 réponses
Je vous présente ma fonction ConcatenateRange VBA (merci Jean pour le Conseil de nommage!) . Il faudra une gamme de cellules (n'importe quelle dimension, n'importe quelle direction, etc.) et les fusionner en une seule chaîne. En tant que troisième paramètre facultatif, vous pouvez ajouter un séparateur (comme un espace ou des virgules sérérées).
Dans ce cas, vous écrivez ceci pour l'utiliser:
=ConcatenateRange(A1:A4)
Function ConcatenateRange(ByVal cell_range As range, _
Optional ByVal separator As String) As String
Dim newString As String
Dim cell As Variant
For Each cell in cell_range
If Len(cell) <> 0 Then
newString = newString & (separator & cell))
End if
Next
If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(separator)))
End If
ConcatenateRange = newString
End Function
Si vous préférez le faire sans VBA, vous pouvez essayer ce qui suit:
- avoir vos données dans les cellules A1: A999 (ou tel)
- Définir la cellule B1 sur "= A1 "
- Définir la cellule B2 sur "= B1 & A2 "
- Copiez la cellule B2 jusqu'à B999 (par exemple en copiant B2, en sélectionnant les cellules B3:B99 et en collant)
La cellule B999 contiendra maintenant la chaîne de texte concaténée que vous recherchez.
Dans CONCATENATE vous pouvez utiliser TRANSPOSE si vous les développez (F9) puis retirez les environs {}parenthèses comme ce recommande
=CONCATENATE(TRANSPOSE(B2:B19))
Devient
=CONCATENATE("Oh ","combining ", "a " ...)
Vous devrez peut-être ajouter votre propre séparateur à la fin, par exemple créer une colonne C et transposer cette colonne.
=B1&" "
=B2&" "
=B3&" "
Dans les cas simples, vous pouvez utiliser la méthode next qui ne nécessite pas de créer une fonction ou de copier du code dans plusieurs cellules:
-
Dans n'importe quelle cellule, écrivez le code suivant
=Transpose(A1:A9)
Où A1: A9 sont des cellules que vous souhaitez fusionner.
- , Sans quitter la cellule, appuyez sur
F9
Après cela, la cellule contiendra la chaîne:
={A1,A2,A3,A4,A5,A6,A7,A8,A9}
Source: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/
Mise à jour: une partie peut être ambiguë. Sans quitter la cellule signifie avoir votre cellule en mode éditeur. Alternatevly vous pouvez appuyer sur F9 alors que sont dans le panneau de l'éditeur de cellules (normalement, il peut être trouvé au-dessus de la feuille de calcul)
Utilisez la fonction Join existante de VBA. Les fonctions VBA ne sont pas exposées dans Excel, donc j'enveloppe Join dans une fonction définie par l'utilisateur qui expose ses fonctionnalités. La forme la plus simple est:
Function JoinXL(arr As Variant, Optional delimiter As String = " ")
'arr must be a one-dimensional array.
JoinXL = Join(arr, delimiter)
End Function
Exemple d'utilisation:
=JoinXL(TRANSPOSE(A1:A4)," ")
Entré comme une formule de tableau (à l'aide de Ctrl-Maj-Saisissez).
Maintenant, JoinXL n'accepte que des tableaux unidimensionnels en entrée. Dans Excel, les plages renvoient des tableaux bidimensionnels. Ci-dessus exemple, TRANSPOSE convertit le tableau bidimensionnel 4×1 en un tableau unidimensionnel à 4 éléments (c'est le comportement documenté de TRANSPOSE lorsqu'il est alimenté avec un tableau bidimensionnel à une seule colonne).
Pour une plage horizontale, vous devez faire un double TRANSPOSE:
=JoinXL(TRANSPOSE(TRANSPOSE(A1:D1)))
L'intérieur TRANSPOSE convertit le tableau bidimensionnel 1×4 en un tableau bidimensionnel 4×1, que l'extérieur TRANSPOSE convertit ensuite en le tableau unidimensionnel à 4 éléments attendu.
Cette utilisation de TRANSPOSE est un moyen bien connu de convertir des tableaux 2D en tableaux 1D dans Excel, mais cela semble terrible. Une solution plus élégante serait de cacher cela dans la fonction JoinXL VBA.
Pour ceux qui ont Excel 2016 (et je suppose que les versions suivantes), il y a maintenant directement la fonctionCONCAT , qui remplacera la fonctionCONCATENATE .
Donc la bonne façon de le faire dans Excel 2016 est :
=CONCAT(A1:A4)
Qui produira:
Iamaboy
Pour les utilisateurs d'anciens versions d'Excel, les autres réponses sont pertinentes.
Pour Excel 2011 sur Mac, c'est différent. Je l'ai fait comme un processus en trois étapes.
- crée une colonne de valeurs dans la colonne A.
- dans la colonne B, à droite de la première cellule, créez une règle qui utilise la fonction concaténer sur la valeur de la colonne et ",". Par exemple, en supposant que A1 est la première ligne, la formule pour B1 est
=B1. Pour la ligne suivante à la ligne N, la formule est=Concatenate(",",A2). Vous vous retrouvez avec:
QA ,Sekuli ,Testing ,Applitools ,Visual Testing ,Test Automation ,Selenium
- dans la colonne C, créez une formule qui concatène tout les valeurs précédentes. Parce que c'est additif, vous obtiendrez tout à la fin. La formule pour la cellule C1 est
=B1. Pour toutes les autres lignes à N, La formule est=Concatenate(C1,B2). Et vous obtenez:
QA,Sekuli QA,Sekuli,Testing QA,Sekuli,Testing,Applitools QA,Sekuli,Testing,Applitools,Visual Testing QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation,Selenium
La dernière cellule de la liste sera ce que vous voulez. Ceci est compatible avec Excel sur Windows ou Mac.
J'utilise la méthode CONCATENATE pour prendre les valeurs d'une colonne et envelopper les guillemets avec des colonnes entre les deux afin de remplir rapidement la clause WHERE IN () d'une instruction SQL.
J'ai toujours tout type =CONCATENATE("'",B2,"'",","), puis sélectionnez-le et faites-le glisser vers le bas, ce qui crée =CONCATENATE("'",B3,"'",","), =CONCATENATE("'",B4,"'",","), etc. mettez ensuite en surbrillance cette colonne entière, copiez-la dans un éditeur de texte brut et collez-la si nécessaire, effaçant ainsi la séparation des lignes. Il fonctionne, mais encore, comme d'une affaire, ce n'est pas une bonne solution pour quelqu'un qui a besoin de ça tout le temps.