Transposer un tableau dans Excel

Comment transposer un tableau dans Excel

Il vous est certainement arrivés d'avoir eu besoin de changer la présentation d’un tableau, d'inverser les lignes avec les colonnes d'un tableau, nous parlerons de transposer un tableau. Excel nous proposent plusieurs techniques pour le faire.

Je vais donc vous apprendre comment transposer un tableau dans Excel : 

Prenons ce tableau comme exemple :

2 méthodes pour transposer ce tableau 

1. Copier et transposer : 

Cette méthode est assez simple, il suffit de copier votre tableau :

• Sélectionner la plage de cellules B3:I6 et appuyer sur Ctrl+C, ensuite se mettre sur la cellule sur laquelle vous voulez transposer ce tableau, par exemple B10.
• Cliquer sur le bouton droit de la souris, cliquer sur Collage spécial/Collage spécial. Dans la nouvelle boite de dialogue, cochez « Transposé » et validez par OK

Et voilà le résultat :

Il faut noter que même les formules dans ce cas ont été copiées. La problématique avec cette méthode est que ces 2 tableaux ne sont pas liés. Si on fait une modification sur le tableau initial, alors le tableau transposé ne changera pas.

2. Utiliser la fonction TRANSPOSE : 

La fonction TRANSPOSE est une fonction matricielle et avec elle les deux tableaux restent liés. Le tableau initial a 4 lignes et 8 colonnes, le tableau transposé devrait donc avoir 8 lignes et 4 colonnes.

• Nous allons donc d’abord sélectionner les 8 lignes et 4 colonnes sur lesquelles nous voulons avoir ce nouveau tableau : Prenons par exemple la plage G10:J17
• Écrire la formule :  =TRANSPOSE(B3:I6) et valider avec Ctrl+ Shift (Maj) + Entrée

Il ne nous reste plus qu’à faire un formatage de notre tableau. L’avantage avec l’utilisation de la fonction TRANSPOSE est le fait qu’une modification sur le tableau initial affecte directement le tableau final.

Faire un email avec une fonction

Faire un email dans Excel avec une fonction

Je vais vous montrer comment faire un mail dans Excel en utilisant la fonction Lien_Hypertexte. Une astuce très intéressante pour ceux qui veulent transformer Excel en intermédiaire pour envoyer les emails.

Déjà comment fonctionne la fonction Lien_Hypertexte avec le net: on peut ouvrir un site internet depuis Excel avec cette fonction. Il suffit d’écrire:

  =LIEN_HYPERTEXTE("http://www.google.com";"Google")

Et lorsque vous cliquez dessus, votre navigateur s’ouvre directement sur la page de votre site préféré. 

Allons un peu plus loin avec cette fonction : lorsqu’on utilise "mailto" alors, la fonction Lien_hypertexte gère les mails.

• Envoyer un email avec un destinataire et l’objet du mail :

=LIEN_HYPERTEXTE("mailto:pierre.martin@gmail.com?subject=Email pour Pierre Martin"; "Envoie email")

• Envoyer un email avec un destinataire, un CC, un Cci et l’objet du message :

=LIEN_HYPERTEXTE("mailto:pierre.martin@gmail.com?cc=adresse1@quelquechose.com&bcc=adress2@quelquechose.com&subject=Email pour Pierre Martin";"Envoie email")

Comme vous pouvez le remarquer, j’utilise le « & » pour séparer les paramètres : cc, bcc et subject.

• Un email avec destinataire, un CC, un Cci, l’objet du message et le corps du
  message :

=LIEN_HYPERTEXTE("mailto:pierre.martin@gmail.com?cc=adresse1@quelquechose.com&bcc=adress2@quelquechose.com&subject=Email pour Pierre Martin&body=Message test";"Envoie email")

Envoyer votre classeur par email

Comment envoyer par émail votre classeur actif en pièce jointe avec un bouton

Il est parfois utile d'envoyer par émail  notre classeur Excel en pièce jointe auprès de notre supérieur hiérarchique.

Je vais donc aujourd’hui vous expliquer comment insérer un bouton qui pourra permettre d’insérer le classeur actif comme pièce jointe dans un email depuis Excel.

Insérer le bouton dans la barre d’accès rapide:

• Aller dans les options du classeur : Onglet Fichier/Options  ou en appuyant sur les raccourci clavier Alt+O+O
• Dans la boite de dialogue Options d’Excel, choisir la commande : Barre d'Outils accès rapide
• Choisir la catégorie  : Commandes non présentes sur le Ruban.
• Choisir le bouton « Courrier électronique » et cliquer sur le bouton Ajouter

Il suffit maintenant de cliquer sur ce bouton dans la barre d’accès rapide pour envoyer votre classeur actif en pièce jointe directement.

Créer une liste déroulante dépendante

Comment afficher une partie d'un tableau avec une liste déroulante dépendante

Nous allons voir comment créer une zone de liste déroulante dépendante permettant d'afficher une partie d'un tableau. Cette méthode est souvent utilisée dans la mise en place de tableau de bord.

Résultat à obtenir : 

Pour y arriver nous allons avoir besoin des outils ci-dessous :

• La fonction SI()
• La fonction SIERREUR()
• La fonction INDEX()
• La fonction NB.SI()
• La fonction EQUIV()
• La fonction DECALER()
• La fonction LIGNE()
• Nommer une plage de manière dynamique
• Mise en forme conditionnelle

Prenons l'exemple d'un commercial qui classe ses clients par zone géographique. Il faut d’abord avoir une plage de cellules dans laquelle nous allons inscrire la liste des zones des clients. Par exemple, sur la plage de cellules L1:L6 nous mettons cette liste :

Nous allons insérer la barre de liste déroulante :

• Dans le ruban, Onglet Développeur/Groupe Contrôle/Insérer/Zone de liste déroulante

• Cliquer quelque part sur la feuille pour positionner cette zone de liste déroulante

Enregistrons maintenant les paramètres de cette zone de liste déroulante :

• Cliquer avec le bouton droit de la souris sur la zone de liste déroulante
• Cliquer sur Format du contrôle
• Remplir la nouvelle boite de dialogue comme ci-dessous :

Votre zone de liste déroulante est maintenant remplie par la liste des zones de clients. Maintenant nous allons faire  apparaître pour chaque choix, la liste des clients et leurs montants.

Le procédé sera de créer une plage nommée dynamique qui dépendra du choix fait dans la zone de liste déroulante.

• Etape1 : retrouver le choix fait dans la liste.  En N2 écrire =INDEX($L$2:$L$6;A1)
• Etape2 : créer un nom dynamique qui variera selon la valeur en N2

Dans le ruban, Onglet Formules/Groupe Nom définis/Définir un nom - dans la boite de dialogue écrire pour Nom : Nom_choisi
- et pour fait référence à, écrire la formule :

=DECALER(Feuil1!$C$2;EQUIV(Feuil1!$N$2;Feuil1!$C$3:$C$19;0);1;NB.SI(Feuil1!$C$3:$C$19;Feuil1!$N$2);2)

Cette formule permet de retrouver la partie du tableau choisie dans la liste déroulante. 

Quelques explications:

• Feuil1!$C$2: cellule à partir de laquelle on décale
• EQUIV(Feuil1!$N$2;Feuil1!$C$3:$C$19;0): permet de retrouver la position de l’élément choisi dans la liste déroulante. On décalera donc C2 de ce nombre de ligne
• 1: je décale C2 d’une colonne et du nombre de ligne retrouvé ci-dessus
• NB.SI(Feuil1!$C$3:$C$19;Feuil1!$N$2): le nombre de ligne que la nouvelle plage de cellule doit avoir qui correspond au nombre de fois qu’on compte l’élément choisi dans la zone
• 2: le nombre de colonne que la nouvelle plage contient.

Nous avons donc créé un nom qui correspond à la plage avec les colonnes Client et CA et qui varie selon le choix dans la zone de liste déroulante. En d’autres termes si je choisi dans ma zone de liste déroulante Zone Client Nord, alors la plage nommée sera C6:E10.

Nous allons maintenant remplir le tableau résultat :

• En H5 écrire : =SIERREUR(INDEX(Nom_choisi;LIGNE()-4;1);" ")
• Copier cette formule jusqu’en H15;
• En I5 écrire : =SIERREUR(INDEX(Nom_choisi;LIGNE()-4;2);" ")
• Copier cette formule jusqu’en I15;
• En G5 écrire : 1
• En G6 écrire: =SI(H6=" ";" ";G5+1) 
• Copier cette formule jusqu’en G15

Pour la mise en forme conditionnelle du tableau résultat, elle est assez simple :

• Sélectionner le tableau: G5:I15
• Dans le ruban, Onglet Accueil/Groupe Style/Mise en forme conditionnelle/ Nouvelle règles….
• Remplir la boite de dialogue comme ci-dessous:

• Cliquer sur Format pour choisir le format du tableau que vous désirez
• Valider par OK

Votre tableau est enfin prêt :

Réaliser un graphique dynamique (Tuto n°3)

Changer les données d'un graphique grâce à des contrôles case à cocher

Lors d'un précédent tutoriel, je vous ai montré comment changer la couleur ou modifier le type d'un graphique à l'aide d'une liste déroulante. Sur le même principe, je vais vous expliquer comment afficher ou non les données d'un graphique à partir de case à cocher.

Le résultat à obtenir est le suivant : 

Voici les données que nous allons utiliser :

1. Créer les données nécessaires aux contrôles des cases à cocher : 

• En C3, D3 et E3 écrire respectivement la valeur  VRAI

2.  Créer les données du graphique :

• En B19:B30 copier les mois de l'année de Janvier à Décembre
• En C18 écrire la formule : =SI(C$3=VRAI;C4;"")
• Incrémenter cette formule jusqu’en E30

Votre tableau actuel devrait ressembler, avec les formules, à celui-ci-dessous : 

3. Insérer les cases à cocher :

• Dans le ruban, Onglet Développeur/Groupe Contrôles/Insérer/Case à cocher
• Cliquer sur la feuille en agrandissant le contrôle de la case à cocher
• Cliquer sur la case à cocher avec le bouton droit de la souris, cliquer sur Format de contrôle…
• Remplir la nouvelle boite de dialogue comme ci-dessous :

• Renommer la case à cocher : Produit 1
• Réaliser les mêmes opérations pour la case à cocher Produit 2 avec pour cellule liée $D$3 et Produit 3 avec pour cellule liée $E$3

4. Créer le graphique :

• Sélectionner la plage de cellules B18:E30, dans le ruban, Onglet Insertion/Groupe Graphiques/Colonne/Histogramme empilé

Votre graphique dynamique est fin prêt. Il ne reste qu'à le personnaliser en sélectionnant votre style de graphique et de cocher ou décocher les cases pour faire apparaître ou disparaître les données du graphique.

Réaliser un graphique dynamique (Tuto n° 2)

Changer le type d'un graphique grâce à une liste déroulante

Lors d'un précédent tutoriel, je vous ai montré comment changer la couleur d'un graphique à l'aide d'une liste déroulante. Sur le même principe, je vais vous expliquer comment modifier non plus la couleur mais le type d'un graphique.

Le résultat à obtenir est le suivant : 

Voici les données que nous allons utiliser :

1. Créer les données nécessaires à la zone de liste déroulante : 

• En B1 écrire Colonne et en B2 Courbe

2. Créer les données du graphique :

• En D4 et E4 écrire respectivement Colonne, Courbe
• En D5 écrire la formule :  =SI($A$1=1;C5;"")
• Incrémenter cette formule jusqu’en D16
• En E5 écrire : =SI($A$1=2;C5;"")
• Incrémenter la formule jusqu’en E16

Votre tableau actuel devrait ressembler à celui-ci-dessous : 

 

3. Insérer la liste déroulante :

• Dans le ruban, Onglet Développeur/Groupe Contrôles/Insérer/Zone de liste déroulante
• Cliquer sur la feuille en agrandissant la forme de la zone de liste déroulante
• Cliquer sur la zone de liste déroulante avec le bouton droit de la souris, cliquer sur Format de contrôle…
• Remplir la nouvelle boite de dialogue comme ci-dessous :

4. Créer le graphique :

• Sélectionner la colonne C avec le bouton droit de la souris et cliquer sur Masquer
• Sélectionner la plage de cellules B4:E16, dans le ruban, Onglet Insertion/Groupe Graphiques/Colonne/Histogramme groupé

Votre graphique dynamique est fin prêt.

Réaliser un graphique dynamique (Tuto n°1)

Comment réaliser un graphique dynamique

Il est très facile de rendre un graphique dynamique. Je vais vous montrer comment changer rapidement la couleur d'un graphique en partir d'une liste déroulante. 

Le résultat à obtenir est celui-ci : 

Les données :

Le principe est assez simple. Nous allons créer autant de série de données que de couleurs. Les valeurs de chaque série de données sera "OK" ou "#N/A" selon que nous aurons choisi dans la liste déroulante la couleur en question.

1. Nommer toutes les plages de cellules nécessaires :

• En A11:A14 nous introduisons les couleurs : Rouge, Vert, Bleu, Orange
• Sélectionner la plage de cellules A11:A14, dans la barre de nom écrire Couleurs et puis valider par Entrée

Nous avons créé et nommé les éléments qui seront dans notre liste déroulante. Nous allons continuer maintenant en créant les éléments qui contrôleront notre liste déroulante.

• Sélectionner la cellule A1 et dans la zone de nom écrire Ctrl_couleur et valider par Entrée. A1 sera la cellule liée à notre liste déroulante
• En cellule A2 écrire =INDEX(Couleurs;Ctrl_Couleur) , cette formule permettra d’afficher le choix de la couleur faite depuis la zone de liste déroulante. Noter bien qu’elle n’a pas encore été insérée.

2. Mettre à jour les données :

• Dans la plage de cellules E2:H2, écrire respectivement Rouge, Vert, Bleu, Orange
• En cellule E3 écrire la formule  =SI($A$2=E$2;$D3;NA()). Cette formule permet de tester si on a choisi dans la liste déroulante la couleur Rouge (E3), si oui alors on met la valeur, sinon on met la fonction NA() qui a pour résultat "#N/A"
• Copier cette formule jusqu’à H7

Normalement vous devriez avoir un tableau comme celui-ci-dessous :

3. Insérer la liste déroulante :

• Dans le ruban, Onglet Développeur/Groupe Contrôles/Insérer/Zone de liste déroulante
• Glisser le contrôle sur la feuille en question
• Cliquer sur la zone de la liste déroulante avec le bouton droit de la souris et choisir Format du contrôle...
• Remplir la nouvelle boite de dialogue comme ci-dessous :

4. Pour finir réaliser le graphique dynamique :

• Nous allons commencer par masquer les données initiales. Cliquer sur la colonne D avec le bouton droit de la souris, cliquer sur Masquer
• Sélectionner la plage de cellules  C2:H7, dans le Ruban,Onglet Insertion/Groupe Graphiques/Colonnes/Histogramme 2D empilé
• Sélectionner chaque couleur dans la liste déroulante et faire un remplissage selon la couleur choisie dans le graphique.

Et voilà votre graphique est prêt :