Que fait la fonction de transposition dans Excel?
La fonction de transposition dans Excel est utilisée pour faire pivoter les valeurs ou basculer les valeurs des lignes aux colonnes et des colonnes aux lignes, car cette fonction prend un tableau de cellules comme argument, donc pour l'exécuter, nous devons appuyer sur CSE ou Ctrl + Maj + Entrée, sélectionnez d'abord la taille exacte de la plage qui doit être transposée, puis utilisez la formule de transposition.
Syntaxe
Voici la formule TRANSPOSE dans Excel.
Arguments
array: obligatoire. Une plage de cellules que vous souhaitez transposer.
C'est une fonction de tableau .
Comment utiliser la fonction TRANSPOSE dans Excel? (avec exemples)
Vous pouvez télécharger ce modèle Excel de la fonction TRANSPOSE ici - Modèle Excel de la fonction TRANSPOSEExemple 1
Les données originales que vous souhaitez transposer sont en A3: B8.
- Étape 1: Sélectionnez la plage dans laquelle vous voulez votre valeur transposée, ici, D6: I7.
- Étape 2: Maintenant, tapez la formule TRANSPOSE dans la région sélectionnée et appuyez sur CTRL + MAJ + ENTRÉE (ou COMMANDE + MAJ + ENTRÉE sous Mac).
= TRANSPOSE (A3: B7)
Vous obtiendrez la sortie transposée en D6: I7 comme indiqué ci-dessous.
En cela, D3: I4 est la sortie transposée par copier-coller et D6: I7 est la sortie transposée.
Notez que la fonction Excel TRANSPOSE est
{= TRANSPOSE (A3: B7)}
Les accolades {} indiquées ci-dessus indiquent que la fonction a été entrée sous forme de formule matricielle.
Maintenant, si vous devez changer une valeur dans les données d'origine, disons B8 = SUM (B3: B7) la sortie transposée, où vous avez utilisé le TRANSPOSE, sera automatiquement mise à jour, mais pas dans le cas où vous avez utilisé la copie- option de collage.
Il peut également être utilisé avec d'autres fonctions pour obtenir la sortie souhaitée. Voyons quelques exemples de cela.
Exemple # 2
Supposons que vous ayez une liste d'étudiants et leurs notes comme indiqué ci-dessous. Certains étudiants n'ont pas donné d'examens. Par conséquent, la colonne des notes est laissée vide pour ces étudiants.
Vous souhaitez transposer ces données, et lorsque la cellule est vide, vous souhaitez mettre «Absent» dans cette cellule.
Vous pouvez le faire en utilisant Transpose avec la fonction IF dans Excel. Vous pouvez utiliser la formule TRANSPOSE suivante dans Excel pour ce faire:
= TRANSPOSE (SI (B3: D10 = ””, ”ABSENT”, B3: D10))
'IF (B3: D10 = ”“, “ABSENT”' signifie que si une cellule de la plage B3: D10 est vide, elle placera “Absent” à la place. Cela transposera la plage donnée.
Supposons que dans cette liste, si un élève a obtenu moins de 70 points ou ne s'est pas présenté à l'examen est considéré comme ÉCHEC, vous pouvez modifier la formule TRANSPOSE comme suit:
= TRANSPOSE (SI (B3: D10 <70, "FAIL", B3: D10)
et vous obtiendrez la sortie finale comme indiqué ci-dessous.
Exemple # 3
Parfois, il est nécessaire d'ajouter des caractères aux caractères existants avec la transposition. Supposons que vous ayez une liste d'identifiants dans B4: B7 comme indiqué ci-dessous.
Vous souhaitez transposer les identifiants et y ajouter le préfixe «ID». Vous pouvez le faire en utilisant la formule dans Excel:
= TRANSPOSE («ID» & B4: B7)
Cela transposera les données et ajoutera le préfixe à chacun des contenus de cellule.
Vous pouvez également ajouter n'importe quelle valeur comme suffixe en utilisant la formule TRANSPOSE ci-dessus. Cela peut être utile lors de l'utilisation de la fonction Transposer pour concaténer les mots donnés dans différentes lignes dans une seule cellule. Pour ce faire, vous pouvez suivre les étapes suivantes:
- Tapez la syntaxe: CONCATENER (TRANSPOSE (B4: B7 & ”,”). N'appuyez pas sur Entrée.
- Sélectionnez la fonction de transposition Excel comme indiqué ci-dessous.
- Appuyez sur la touche F9.
- Supprimer {} de la formule
- Appuyez sur Entrée.
Il concaténera les cellules dans une seule cellule.
Exemple # 4
Supposons que vous ayez une bibliothèque de données dans laquelle les différents sujets sont disposés sur une étagère et qu'il y ait différentes étagères comme indiqué ci-dessous.
Vous souhaiterez peut-être récupérer l'emplacement de l'un des sujets (donné en H3: H11) en utilisant ces données (B4: E6).
Vous pouvez le faire en utilisant la syntaxe
= INDEX ($ B $ 4: $ B $ 6, MATCH (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLONNE ($ C $ 4: $ E $ 6) ^ 0)), 0))
Voyons comment fonctionne cette formule TRANSPOSE dans Excel.
- - ($ C $ 4: $ E $ 6 =)
Quand est donné en H3, ce sera - ($ C $ 4: $ E $ 6 = H3).
Cela forme un tableau de 1 et 0 pour indiquer la présence ou l'absence de la valeur. Pour la biologie en H3, il formera le tableau comme suit: {1,0,0; 0,0,0; 0,0,0}
- TRANSPOSE (COLONNE ($ C $ 4: $ E $ 6) ^ 0))
Il crée un tableau de 3 lignes dans une colonne, et une puissance de 0 garantit que les nombres sont convertis en 1. La sortie de cette fonction est {1,1,1}
- MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (COLONNE ($ C $ 4: $ E $ 6) ^ 0))
Le MMULT dans Excel multiplie la sortie de A et B.
MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) donne la sortie {1; 0; 0}
- MATCH (1, MMULT (), 0)
Il correspondra à la sortie de C avec 1. MATCH (1, {1; 0; 0}, 0) renvoie la position 1.
- INDEX ($ B $ 4: $ B $ 6, MATCH (, 0))
Il identifiera la valeur de la cellule pour laquelle la fonction de correspondance a spécifié la position. INDEX ($ B $ 4: $ B $ 6, 1) renverra A1.
De même pour «Géographie» comme sujet, ce sera:
- - ($ C $ 4: $ E $ 6 = D6) renvoie {0,0,0; 0,0,0; 0,1,0}
- TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) renvoie {1,1,1}
- MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) renvoie {0; 0; 1}
- MATCH (1, {0; 0; 1}, 0) renvoie 3
- INDEX ($ B $ 4: $ B $ 6, 3) renvoie A3.
Choses à retenir
- L'option copier-coller crée des doublons.
- Cette fonction relie les données à la source.
- Si le nombre de lignes et de colonnes sélectionnées n'est pas égal aux colonnes et aux lignes des données source, cela donnera une erreur #VALUE
- Une fois qu'elle a été saisie, aucune cellule individuelle faisant partie de cette fonction ne peut être modifiée.
- Appuyez sur CTRL + MAJ + ENTRÉE pour entrer la formule dans Excel.
