Fonction CONCATENER dans Excel - guide d'utilisation avec exemples

De temps en temps, il peut être nécessaire de combiner les valeurs contenues dans différentes cellules en une seule. Le symbole & est généralement utilisé pour cela. Mais sa fonctionnalité est quelque peu limitée car elle ne peut pas concaténer plusieurs chaînes.

Cette fonction simple est remplacée par une version plus fonctionnelle de celle-ci - STsEPIT. En fait, dans les versions modernes de Microsoft Office, cette fonction n'est plus là, elle est complètement remplacée par la fonction ÉTAPE. Il est toujours utilisable pour le moment, il est inclus pour la rétrocompatibilité, mais après un certain temps, il se peut qu'il ne le soit plus. Par conséquent, il est recommandé que dans Excel 2016, en ligne et les versions plus récentes, utilisez la fonction ÉTAPE.

Fonction CONCATENER – description détaillée

Fonction STsEPIT renvoie au texte. Cela signifie qu'il est utilisé pour effectuer des opérations sur des valeurs de texte. En même temps, vous pouvez spécifier des arguments dans différents formats : texte, numérique ou en tant que références de cellule. 

En général, les règles d'utilisation de cette fonctionnalité sont les suivantes :

1. Un point-virgule est utilisé pour séparer les arguments. Si l'utilisateur décide d'utiliser d'autres caractères, alors l'affichage sera le résultat entre guillemets.
2. Si une valeur au format texte est utilisée comme argument de fonction et est entrée directement dans une formule, elle doit être placée entre guillemets. S'il existe une référence à une telle valeur, aucun guillemet n'est requis. Il en va de même pour les valeurs numériques. Si vous devez ajouter un chiffre à une chaîne, le guillemet n'est pas requis. Si vous ne respectez pas ces règles, l'erreur suivante s'affichera : #NOM ?
3. Si vous devez ajouter un espace entre les éléments connectés, il doit être ajouté en tant que chaîne de texte distincte, c'est-à-dire entre guillemets. Comme ça: " " .

Examinons maintenant de plus près la syntaxe de cette fonction. Il est très simple. 

Syntaxe

Donc, en fait, il n'y a qu'un seul argument - c'est une chaîne de texte qui doit être insérée. Chaque argument, comme nous le savons déjà, est séparé par un point-virgule. Vous pouvez spécifier jusqu'à 255 arguments. Ils sont eux-mêmes dupliqués à leur manière. Le premier argument est obligatoire. Et comme nous le savons déjà, vous pouvez spécifier des arguments dans trois formats : texte, nombre et lien. 

Applications de la fonction CONCATENER

Nombre de domaines d'application de la fonction STsEPIT énorme. En fait, il peut être appliqué presque partout. Examinons certains d'entre eux plus en détail :

1. Comptabilité. Par exemple, un comptable est chargé de mettre en surbrillance la série et le numéro de document, puis d'insérer ces données sur une ligne dans une cellule. Ou vous devez ajouter à la série et au numéro du document par lequel il a été délivré. Ou répertoriez plusieurs reçus dans une cellule à la fois. En fait, il existe tout un tas d'options, que vous pouvez lister indéfiniment. 
2. Rapports du bureau. Surtout si vous devez fournir des données récapitulatives. Ou combinez prénom et nom.
3. Gamification. Il s'agit d'une tendance très populaire qui est activement utilisée dans l'éducation, la parentalité, ainsi que dans les programmes de fidélité de diverses entreprises. Par conséquent, dans le domaine de l'éducation et des affaires, cette fonction peut également être utile. 

Cette fonction est incluse dans l'ensemble standard que tout utilisateur d'Excel devrait connaître. 

Fonction CONCATENER inverse dans Excel

En fait, il n'y a pas une telle fonction qui serait complètement opposée à la fonction "CONCATENER". Pour effectuer le fractionnement des cellules, d'autres fonctions sont utilisées, telles que LEVSIMV и DROITainsi que PSTR. Le premier extrait un certain nombre de caractères à gauche de la chaîne. Le second est à droite. MAIS PSTR peut le faire à partir d'un endroit arbitraire et finir dans un endroit arbitraire. 

Vous devrez peut-être également effectuer des tâches plus complexes, mais il existe des formules distinctes pour celles-ci. 

Problèmes courants avec la fonction CONCATENER

A première vue, la fonction STsEPIT assez simple. Mais en pratique, il s'avère que tout un tas de problèmes sont possibles. Regardons-les de plus près. 

1. Les guillemets sont affichés dans la chaîne de résultat. Pour éviter ce problème, vous devez utiliser un point-virgule comme séparateur. Mais, comme mentionné ci-dessus, cette règle ne s'applique pas aux nombres.
2. Les mots sont très proches. Ce problème se produit car une personne ne connaît pas toutes les nuances de l'utilisation de la fonction STsEPIT. Pour afficher les mots séparément, vous devez leur ajouter un espace. Ou vous pouvez l'insérer directement après l'argument de texte (à la fois à l'intérieur de la cellule et si vous entrez le texte séparément dans la formule). Par exemple comme ceci : =CONCATENATE("Bonjour", "cher"). On voit qu'ici un espace a été ajouté à la fin du mot « Hello ». 
3. #NOM? Cela indique qu'aucun guillemet n'a été spécifié pour l'argument de texte. 

Recommandations d'utilisation de la fonction

Pour rendre le travail avec cette fonction plus efficace, vous devez tenir compte de plusieurs recommandations importantes :

1. Utilisez & autant que possible. Si vous n'avez besoin de joindre que deux lignes de texte, il n'est pas nécessaire d'utiliser une fonction distincte pour cela. Ainsi, la feuille de calcul fonctionnera plus rapidement, en particulier sur les ordinateurs faibles avec une petite quantité de RAM. Un exemple est la formule suivante : =A1 et B1. C'est similaire à la formule =SECTION(A1,B1). Surtout la première option est plus facile lors de la saisie manuelle de la formule.
2. S'il est nécessaire de combiner une devise ou une date avec une chaîne de texte, ainsi que des informations dans tout autre format que ceux listés ci-dessus, alors vous devez d'abord les traiter avec la fonction TEXTE. Il est conçu pour convertir des nombres, des dates, des symboles en texte.

Comme vous pouvez le voir, il n'est pas du tout difficile de comprendre ces nuances. Et ils découlent des informations ci-dessus. 

Utilisations courantes de la fonction CONCATENER

Donc la formule générale est : CONCATENER([text2];[text2];…). Insérez votre texte aux endroits appropriés. Il est important de noter que l'exigence pour le texte reçu est la suivante : il doit être inférieur à la longueur du champ dans lequel la valeur est entrée. En tant qu'attributs, vous pouvez utiliser non seulement des valeurs prédéfinies, mais également des informations dans les cellules, ainsi que les résultats de calculs utilisant d'autres formules.

Dans ce plan, il n'y a aucune recommandation obligatoire d'utiliser les données pour la saisie au format texte. Mais le résultat final sera affiché au format "Texte".

Il existe plusieurs manières de saisir une fonction : une manuelle et plusieurs semi-automatiques. Si vous êtes débutant, il est recommandé d'utiliser la méthode de la boîte de dialogue pour saisir les arguments. Les utilisateurs plus expérimentés du programme peuvent également saisir des formules manuellement. Au début, cela semblera peu pratique, mais en fait, rien de plus efficace que la saisie au clavier n'a encore été inventé. 

Soit dit en passant, la recommandation pour l'utilisation d'Excel en général : apprenez toujours les raccourcis clavier. Ils vous feront gagner beaucoup de temps.

Mais tant que vous êtes débutant, vous devrez utiliser une fenêtre spécialement créée.

Alors, comment l'appeler? Si vous regardez la ligne de saisie de la formule, à gauche de celle-ci se trouve un si petit bouton "fx". Si vous appuyez dessus, le menu suivant apparaît. Nous devons sélectionner la fonction souhaitée dans la liste.

Après avoir sélectionné la fonction souhaitée, une fenêtre de saisie d'arguments s'ouvrira. Grâce à cela, vous pouvez définir une plage ou saisir manuellement du texte, un lien vers une cellule. 

Si vous saisissez les données manuellement, la saisie est effectuée en commençant par le signe « égal ». C'est-à-dire comme ceci :

=CONCATENER(D2; ","; E2)

Après toutes les opérations que nous avons effectuées, nous verrons dans la cellule résultante le texte "21.09", qui se compose de plusieurs parties : le numéro 21, qui se trouve dans la cellule indexée comme D2 et la ligne 09, qui se trouve dans la cellule E2 . Pour qu'ils soient séparés par un point, nous l'avons utilisé comme deuxième argument. 

Liaison de nom

Pour plus de clarté, regardons un exemple qui décrit comment lier des noms. 

Disons que nous avons une telle table. Il contient des informations sur le prénom, le nom, la ville, l'état des clients. Notre tâche consiste à combiner le prénom et le nom et à obtenir le nom complet. 

Sur la base de ce tableau, nous comprenons que les références aux noms doivent être données dans la colonne B et les noms de famille - A. La formule elle-même sera écrite dans la première cellule sous le titre «Nom complet».

Avant de saisir une formule, rappelez-vous que la fonction n'associera pas plus d'informations que l'utilisateur n'en a spécifié. Par conséquent, si vous avez besoin d'ajouter des délimiteurs, des points d'interrogation, des points, des tirets, des espaces, ils doivent être saisis en tant qu'arguments distincts.

Dans notre exemple, nous devons séparer le nom et le prénom par un espace. Il faut donc saisir trois arguments : l'adresse de la cellule contenant le prénom, un espace (n'oubliez pas de le mettre entre guillemets), et l'adresse de la cellule contenant le nom de famille. 

Après avoir défini les arguments, nous les écrivons dans la formule dans l'ordre approprié. 

Il est très important de faire attention à la syntaxe de la formule. Nous le commençons toujours par un signe égal, après quoi nous ouvrons les crochets, listons les arguments en les séparant par un point-virgule, puis fermons les crochets.

Parfois, vous pouvez mettre une virgule entre les arguments. Si la version anglaise d'Excel est utilisée, une virgule est insérée. Si la version -language, alors un point-virgule. Après avoir appuyé sur Entrée, la version fusionnée apparaîtra.

Il ne reste plus qu'à utiliser le marqueur de remplissage automatique pour insérer cette formule dans toutes les autres cellules de cette colonne. En conséquence, nous avons le nom complet de chaque client. Mission accomplie.

De la même manière, vous pouvez connecter l'état et la ville.

Relier les chiffres et le texte

Comme nous le savons déjà, en utilisant la fonction STsEPIT nous pouvons concaténer des valeurs numériques avec des valeurs textuelles. Disons que nous avons une table avec des données sur l'inventaire des marchandises dans un magasin. Pour le moment, nous avons 25 pommes, mais cette rangée est répartie sur deux cellules. 

Nous avons besoin du résultat final suivant.

Dans ce cas, nous avons également besoin de trois arguments, et la syntaxe est toujours la même. Mais essayons de terminer la tâche d'une complexité légèrement accrue. Supposons que nous ayons besoin d'écrire la chaîne complexe "Nous avons 25 pommes". Par conséquent, nous devons ajouter une ligne supplémentaire "Nous avons" aux trois arguments existants. Le résultat final ressemble à ceci.

=CONCATENER("Nous avons ";F17;" ";F16)

S'il le souhaite, l'utilisateur peut ajouter presque autant d'arguments qu'il le souhaite (dans la limite ci-dessus).

Connexion VLOOKUP et CONCATENATE

Si vous utilisez des fonctions VPR и STsEPIT ensemble, cela peut s'avérer être une combinaison très intéressante et, surtout, fonctionnelle. Utilisation de la fonction VPR on effectue une recherche verticale sur la table selon un certain critère. Ensuite, nous pouvons ajouter les informations trouvées à la ligne déjà existante.

Alors, disons que nous avons une telle table. Il décrit les marchandises qui se trouvent actuellement dans les premier et deuxième entrepôts. 

Nous devons trouver le prix d'un certain article dans un certain entrepôt. Pour cela, la fonction est utilisée VPR. Mais avant de l'utiliser, il faut d'abord préparer un peu la table. VPR génère des données à gauche, vous devez donc insérer une colonne supplémentaire à gauche du tableau avec les données d'origine. 

Après cela, nous concaténons les données. 

Cela peut être fait soit avec cette formule:

=B2&»/»&C2

Ou tel.

=CONCATENER(B2;”/”;C2)

Ainsi, nous avons concaténé deux colonnes ensemble, en utilisant une barre oblique comme séparateur entre les deux valeurs. Ensuite, nous avons transféré cette formule dans toute la colonne A. Nous obtenons un tel tableau.

Ensuite, nous prenons le tableau suivant et le remplissons avec des informations sur le produit sélectionné par le visiteur. Nous devons obtenir des informations sur le coût des marchandises et le numéro d'entrepôt à partir du premier tableau. Cela se fait à l'aide de la fonction VPR.

Ensuite, sélectionnez une cellule K2, et écrivez-y la formule suivante. 

{=ВПР(G2&»/»&H2;A2:E6;5;0)}

Ou il peut être écrit via la fonction STsEPIT.

{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}

La syntaxe dans ce cas est similaire à la façon dont la combinaison d'informations sur le numéro et l'entrepôt a été effectuée. 

Vous devez inclure une fonction VPR via la combinaison de touches "Ctrl" + "Maj" + "Entrée".

Comme vous pouvez le voir, tout est simple.