Table des matières
Hier au marathon 30 fonctions Excel en 30 jours nous avons compté le nombre de colonnes dans la plage à l'aide de la fonction COLONNES (NUMBERCOLUMN), et il est maintenant temps pour quelque chose de plus demandé.
Au 13e jour du marathon, nous nous consacrerons à l'étude de la fonction TRANSPOSER (TRANSP). Avec cette fonction, vous pouvez faire pivoter vos données en transformant les zones verticales en zones horizontales et vice versa. Avez-vous un tel besoin ? Pouvez-vous le faire en utilisant un insert spécial? D'autres fonctions peuvent-elles le faire ?
Passons donc aux informations et exemples sur la fonction TRANSPOSER (TRANSP). Si vous avez des informations supplémentaires ou des exemples, merci de les partager dans les commentaires.
Fonction 13 : TRANSPOSER
Fonction TRANSPOSER (TRANSPOSE) renvoie une plage horizontale de cellules sous la forme d'une plage verticale, ou vice versa.
Comment utiliser la fonction TRANSPOSE ?
Fonction TRANSPOSER (TRANSP) peut modifier l'orientation des données, ainsi que travailler en conjonction avec d'autres fonctions :
- Modifiez la disposition horizontale des données en verticale.
- Afficher les meilleurs salaires totaux de ces dernières années.
Pour modifier l'orientation des données sans créer de liens vers les données d'origine :
- Utilisez pâte spéciale (Pâte spéciale) > Transposer (Transposer).
Syntaxe TRANSPOSER (TRANSP)
Fonction TRANSPOSER (TRANSPOSER) a la syntaxe suivante :
TRANSPOSE(array)
ТРАНСП(массив)
- tableau (tableau) est le tableau ou la plage de cellules à transposer.
Pièges TRANSPOSE (TRANSPOSE)
- Fonction TRANSPOSER (TRANSPOSE) doit être saisi sous forme de formule matricielle, en appuyant sur Ctrl + Maj + Entrée.
- La plage qui résultera de la transformation par la fonction TRANSPOSER (TRANSPOSE) doit avoir le même nombre de lignes et de colonnes que la plage d'origine a respectivement des colonnes et des lignes.
Exemple 1 : Transformer des données horizontales en données verticales
Si les données sont horizontales dans une feuille Excel, vous pouvez appliquer la fonction TRANSPOSER (TRANSPOSER) pour les convertir en position verticale, mais à un endroit différent sur la feuille. Par exemple, dans le tableau final des benchmarks, une disposition verticale serait plus pratique. Utilisation de la fonction TRANSPOSER (TRANSPOSER), vous pouvez référencer les données horizontales d'origine sans modifier leur emplacement.
Pour transposer la plage horizontale 2 × 4 dans la plage verticale 4 × 2:
- Sélectionnez 8 cellules où vous souhaitez placer la plage verticale résultante. Dans notre exemple, ce seront les cellules B4:C7.
- Entrez la formule suivante et transformez-la en une formule matricielle en cliquant sur Ctrl + Maj + Entrée.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Des accolades seront automatiquement ajoutées au début et à la fin de la formule pour indiquer qu'une formule matricielle a été saisie.
Au lieu de TRANSPOSER (TRANSPOSE), Vous pouvez utiliser une autre fonction pour transformer les données, par exemple, INDEX (INDICE). Il ne vous oblige pas à saisir une formule matricielle et vous n'avez pas à sélectionner toutes les cellules de la zone cible lors de la création de la formule.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Exemple 2 : Modifier l'orientation sans liens
Si vous souhaitez simplement modifier l'orientation de vos données sans conserver de référence aux données d'origine, vous pouvez utiliser Collage spécial :
- Sélectionnez les données source et copiez-les.
- Sélectionnez la cellule en haut à gauche de la zone où vous souhaitez placer le résultat.
- Dans l'onglet Avancé Accueil (Accueil) cliquez sur le menu déroulant des commandes paste (Insérer).
- Выберите Transposer (Transposer).
- Supprimez les données d'origine (facultatif).
Exemple 3 : Meilleur salaire total des années précédentes
Fonction TRANSPOSER (TRANSP) peut être utilisé en combinaison avec d'autres fonctionnalités, comme dans cette formule étonnante. Il a été publié par Harlan Grove dans le Excel News Bloc lors d'une discussion sur le calcul du meilleur salaire total des 5 dernières années (de suite !).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Comment pouvez-vous utiliser la figure de la forme de course – cette formule est massive. Feuille A5 Numéro et в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных стовательных стова. Реззльтаты проверки (1 или 0) умножаюются на значения яеек, ччобы полчить слмарныйый.
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий пть к тому же рзлльтататаmant, что предыыщаеторousse ол ай c!
