Fonction de transposition dans Excel

De temps en temps, un utilisateur d'Excel peut avoir la tâche de transformer une plage de données qui a une structure horizontale en une verticale. Ce processus est appelé transposition. Ce mot est nouveau pour la plupart des gens, car dans le travail normal sur PC, vous n'avez pas à utiliser cette opération. Cependant, ceux qui doivent travailler avec de grandes quantités de données doivent savoir comment s'y prendre. Aujourd'hui, nous parlerons plus en détail de la façon de l'exécuter, avec quelle fonction, et nous examinerons également en détail d'autres méthodes.

Fonction TRANSPOSE - transposer des plages de cellules dans Excel

L'une des méthodes de transposition de tableau les plus intéressantes et fonctionnelles dans Excel est la fonction TRANSP. Avec son aide, vous pouvez transformer une plage de données horizontale en une plage verticale ou effectuer l'opération inverse. Voyons comment travailler avec.

Syntaxe de la fonction

La syntaxe de cette fonction est incroyablement simple : TRANSPOSE(tableau). Autrement dit, nous n'avons besoin d'utiliser qu'un seul argument, qui est un ensemble de données qui doit être converti en une vue horizontale ou verticale, selon ce qu'il était à l'origine.

Transposer des plages verticales de cellules (colonnes)

Supposons que nous ayons une colonne avec la plage B2: B6. Ils peuvent contenir à la fois des valeurs prêtes à l'emploi et des formules qui renvoient des résultats à ces cellules. Ce n'est pas si important pour nous, la transposition est possible dans les deux cas. Après avoir appliqué cette fonction, la longueur de la ligne sera la même que la longueur de la colonne de la plage d'origine.

La séquence d'étapes pour utiliser cette formule est la suivante :

1. Sélectionnez une ligne. Dans notre cas, il a une longueur de cinq cellules.
2. Après cela, déplacez le curseur sur la barre de formule et entrez-y la formule =TRANSP(B2:B6).
3. Appuyez sur la combinaison de touches Ctrl + Maj + Entrée.

Naturellement, dans votre cas, vous devez spécifier la plage typique de votre table.

Transposer des plages de cellules horizontales (lignes)

En principe, le mécanisme d'action est presque le même que le paragraphe précédent. Supposons que nous ayons une chaîne avec les coordonnées de début et de fin B10:F10. Il peut également contenir directement des valeurs et des formules. Faisons-en une colonne, qui aura les mêmes dimensions que la ligne d'origine. La séquence d'actions est la suivante :

1. Sélectionnez cette colonne avec la souris. Vous pouvez également utiliser les touches du clavier Ctrl et la flèche vers le bas, après avoir cliqué sur la cellule la plus haute de cette colonne.
2. Après cela, nous écrivons la formule =TRANSP(B10:F10) dans la barre de formule.
3. Nous l'écrivons sous forme de formule matricielle en utilisant la combinaison de touches Ctrl + Maj + Entrée.

Transposer avec Collage spécial

Une autre option de transposition possible consiste à utiliser la fonction Collage spécial. Ce n'est plus un opérateur à utiliser dans les formules, mais c'est aussi l'une des méthodes populaires pour transformer les colonnes en lignes et vice versa.

Cette option se trouve dans l'onglet Accueil. Pour y accéder, vous devez trouver le groupe "Presse-papiers", et y trouver le bouton "Coller". Après cela, ouvrez le menu situé sous cette option et sélectionnez l'élément "Transposer". Avant cela, vous devez sélectionner la plage que vous souhaitez sélectionner. En conséquence, nous obtiendrons la même gamme, seul le contraire est reflété.

3 façons de transposer un tableau dans Excel

Mais en fait, il existe bien d'autres façons de transformer des colonnes en lignes et vice versa. Décrivons 3 méthodes par lesquelles nous pouvons transposer un tableau dans Excel. Nous en avons discuté deux ci-dessus, mais nous donnerons quelques exemples supplémentaires afin que vous ayez une meilleure idée de uXNUMXbuXNUMXbcomment effectuer cette procédure.

Méthode 1 : Collage spécial

Cette méthode est la plus simple. Il suffit d'appuyer sur quelques boutons et l'utilisateur reçoit une version transposée du tableau. Donnons un petit exemple pour plus de clarté. Supposons que nous disposions d'un tableau contenant des informations sur le nombre de produits actuellement en stock, ainsi que sur leur coût total. La table elle-même ressemble à ceci.

Nous voyons que nous avons un en-tête et une colonne avec des numéros de produits. Dans notre exemple, l'en-tête contient des informations sur le produit, son coût, sa quantité en stock et le coût total de tous les produits liés à cet article qui sont en stock. Nous obtenons le coût selon la formule où le coût est multiplié par la quantité. Pour rendre l'exemple plus visuel, rendons l'en-tête vert.

Notre tâche est de nous assurer que les informations contenues dans le tableau sont situées horizontalement. C'est-à-dire que les colonnes deviennent des lignes. La séquence d'actions dans notre cas sera la suivante:

1. Sélectionnez la plage de données que nous devons faire pivoter. Après cela, nous copions ces données.
2. Placez le curseur n'importe où sur la feuille. Cliquez ensuite sur le bouton droit de la souris et ouvrez le menu contextuel.
3. Cliquez ensuite sur le bouton « Collage spécial ».

Après avoir terminé ces étapes, vous devez cliquer sur le bouton "Transposer". Cochez plutôt la case à côté de cet élément. Nous ne modifions pas les autres paramètres, puis cliquez sur le bouton "OK".

Après avoir effectué ces étapes, nous nous retrouvons avec le même tableau, seules ses lignes et ses colonnes sont disposées différemment. Notez également que les cellules contenant les mêmes informations sont surlignées en vert. Question : qu'est-il advenu des formules qui étaient dans la gamme d'origine ? Leur emplacement a changé, mais eux-mêmes sont restés. Les adresses des cellules ont simplement changé pour celles qui ont été formées après la transposition.

Presque les mêmes actions doivent être effectuées pour transposer des valeurs, pas des formules. Dans ce cas, vous devez également utiliser le menu Collage spécial, mais avant cela, sélectionnez la plage de données contenant les valeurs. Nous voyons que la fenêtre Collage spécial peut être appelée de deux manières : via un menu spécial sur le ruban ou le menu contextuel.

Méthode 2. Fonction TRANSP dans Excel

En fait, cette méthode n'est plus utilisée aussi activement qu'au tout début de l'apparition de ce tableur. En effet, cette méthode est beaucoup plus compliquée que l'utilisation de Collage spécial. Cependant, il trouve son utilité dans l'automatisation de la transposition de table.

De plus, cette fonction est dans Excel, il est donc impératif de la connaître, même si elle n'est presque plus utilisée. Plus tôt, nous avons examiné la procédure, comment travailler avec elle. Nous allons maintenant compléter ces connaissances avec un exemple supplémentaire.

1. Tout d'abord, nous devons sélectionner la plage de données qui sera utilisée pour transposer le tableau. Il vous suffit de sélectionner la zone au contraire. Par exemple, dans cet exemple, nous avons 4 colonnes et 6 lignes. Il faut donc sélectionner une zone aux caractéristiques opposées : 6 colonnes et 4 lignes. La photo le montre très bien.
2. Après cela, nous commençons immédiatement à remplir cette cellule. Il est important de ne pas supprimer accidentellement la sélection. Par conséquent, vous devez spécifier la formule directement dans la barre de formule.
3. Ensuite, appuyez sur la combinaison de touches Ctrl + Maj + Entrée. N'oubliez pas qu'il s'agit d'une formule matricielle, car nous travaillons avec un grand ensemble de données à la fois, qui seront transférées vers un autre grand ensemble de cellules.

Après avoir saisi les données, nous appuyons sur la touche Entrée, après quoi nous obtenons le résultat suivant.

On voit que la formule n'a pas été transférée dans la nouvelle table. Le formatage a également été perdu. Poéto

Tout cela devra être fait manuellement. De plus, rappelez-vous que ce tableau est lié à celui d'origine. Ainsi, dès que certaines informations sont modifiées dans la plage d'origine, ces ajustements sont automatiquement effectués sur la table transposée.

Par conséquent, cette méthode est bien adaptée dans les cas où vous devez vous assurer que la table transposée est liée à celle d'origine. Si vous utilisez un insert spécial, cette possibilité ne sera plus.

tableau récapitulatif

Il s'agit d'une méthode fondamentalement nouvelle, qui permet non seulement de transposer le tableau, mais aussi de réaliser un grand nombre d'actions. Certes, le mécanisme de transposition sera quelque peu différent par rapport aux méthodes précédentes. La séquence d'actions est la suivante :

1. Faisons un tableau croisé dynamique. Pour ce faire, vous devez sélectionner le tableau que nous devons transposer. Après cela, allez à l'élément "Insérer" et recherchez "Tableau croisé dynamique" là-bas. Une boîte de dialogue comme celle de cette capture d'écran apparaîtra.
2. Ici, vous pouvez réaffecter la plage à partir de laquelle il sera fait, ainsi que faire un certain nombre d'autres paramètres. Nous nous intéressons maintenant principalement à la place du tableau croisé dynamique – sur une nouvelle feuille.
3. Après cela, la mise en page du tableau croisé dynamique sera automatiquement créée. Il est nécessaire d'y marquer les éléments que nous utiliserons, puis ils doivent être déplacés au bon endroit. Dans notre cas, nous devons déplacer l'élément "Produit" vers "Noms de colonne" et "Prix par pièce" vers "Valeurs".
4. Après cela, le tableau croisé dynamique sera finalement créé. Un bonus supplémentaire est le calcul automatique de la valeur finale.
5. Vous pouvez également modifier d'autres paramètres. Par exemple, décochez la rubrique "Prix par pièce" et cochez la rubrique "Coût total". En conséquence, nous aurons un tableau contenant des informations sur le coût des produits. Cette méthode de transposition est beaucoup plus fonctionnelle que les autres. Décrivons quelques-uns des avantages des tableaux croisés dynamiques :

1. Automatisation. À l'aide de tableaux croisés dynamiques, vous pouvez résumer automatiquement les données et modifier arbitrairement la position des colonnes et des colonnes. Pour ce faire, vous n'avez pas besoin d'effectuer d'étapes supplémentaires.
2. Interactivité. L'utilisateur peut modifier la structure des informations autant de fois qu'il en a besoin pour effectuer ses tâches. Par exemple, vous pouvez modifier l'ordre des colonnes, ainsi que regrouper les données de manière arbitraire. Cela peut être fait autant de fois que l'utilisateur en a besoin. Et cela prend littéralement moins d'une minute.
3. Données faciles à formater. Il est très facile d'organiser un tableau croisé dynamique de la manière dont une personne le souhaite. Pour ce faire, il suffit de faire quelques clics de souris.
4. Obtenir des valeurs. Le nombre écrasant de formules qui sont utilisées pour créer des rapports se trouvent dans l'accessibilité directe d'une personne et sont faciles à intégrer dans un tableau croisé dynamique. Ce sont des données telles que la sommation, l'obtention de la moyenne arithmétique, la détermination du nombre de cellules, la multiplication, la recherche des valeurs les plus grandes et les plus petites dans l'échantillon spécifié.
5. Possibilité de créer des tableaux récapitulatifs. Si les tableaux croisés dynamiques sont recalculés, leurs graphiques associés sont automatiquement mis à jour. Il est possible de créer autant de graphiques que nécessaire. Tous peuvent être modifiés pour une tâche spécifique et ils ne seront pas interconnectés.
6. Possibilité de filtrer les données.
7. Il est possible de créer un tableau croisé dynamique basé sur plusieurs ensembles d'informations sources. Par conséquent, leur fonctionnalité deviendra encore plus grande.

Certes, lors de l'utilisation de tableaux croisés dynamiques, les restrictions suivantes doivent être prises en compte :

1. Toutes les informations ne peuvent pas être utilisées pour générer des tableaux croisés dynamiques. Avant de pouvoir être utilisées à cette fin, les cellules doivent être normalisées. En termes simples, faites les choses correctement. Exigences obligatoires : présence d'une ligne d'en-tête, remplissage de toutes les lignes, égalité des formats de données.
2. Les données sont mises à jour semi-automatiquement. Pour obtenir de nouvelles informations dans le tableau croisé dynamique, vous devez cliquer sur un bouton spécial.
3. Les tableaux croisés dynamiques prennent beaucoup de place. Cela peut entraîner une certaine perturbation de l'ordinateur. De plus, le fichier sera difficile à envoyer par e-mail à cause de cela.

De plus, après avoir créé un tableau croisé dynamique, l'utilisateur n'a pas la possibilité d'ajouter de nouvelles informations.