Excel – Importer des données provenant d’une page Web

Contexte

Vous utilisez Excel sur Mac (ça ajoute du piment à cet article…) et vous souhaitez importer des données provenant d’une page Web dans une feuille Excel. Vous aimeriez que ces données se mettent à jour régulièrement dans votre feuille Excel. Vous avez fait une tentative sur Excel 2011 qui s’est soldée par un échec. Vous êtes étonné car l’onglet Données contient bel et bien un bouton Actualiser permettant d’actualiser les données provenant d’une source externe.

Solution

  • Faites-vous d’abord un ami ayant un PC et prêt à vous rendre service 😉
  • Une fois cette première et délicate étape franchie, ouvrez Excel 2013 sur le PC
  • Allez dans l’onglet Données > bouton Web
  • Dans la boîte de dialogue Nouvelle requête sur le Web, tapez l’URL dont vous souhaitez importer les données
  • Cochez la petite case verte en face de la page Web (ou en face du cadre de la page)

actu unil

  • Validez en cliquant sur le bouton Importer
  • Choisissez la cellule à partir de laquelle vous souhaitez faire l’importation (A1 dans mon exemple)
  • Sauvegardez le classeur et ouvrez-le sur votre Mac (Excel 2011)
  • Cliquez sur le bouton Activer le contenu dans le bandeau jaune de sécurité
  • Allez dans l’onglet Données et cliquez sur le bouton Actualiser

Il existe une autre possibilité sur PC qui consiste à atteindre une page Web à l’aide d’Internet Explorer, de cliquer avec le bouton droit de la souris sur la page et de choisir Exporter vers Microsoft Excel. Excel s’ouvre et la boîte de dialogue Nouvelle requête sur le Web permet de choisir la zone de la page Web à importer.

Excel – Créer une liste déroulante – Méthode N° 2

Contexte

Vous avez créé un document Excel qui va être utilisé par plusieurs utilisateurs. Vous souhaitez insérer, dans une cellule, une liste déroulante contenant des valeurs à choix. Les valeurs en question sont situées dans une plage d’un autre onglet de votre classeur.

Solution

  • Allez dans l’onglet contenant les valeurs et sélectionnez la plage
  • Donnez un nom à cette plage de cellules par ex. type (en cliquant dans la zone située tout à gauche de la barre de formule)
  • Allez ensuite dans l’onglet où se trouve la cellule dans laquelle vous souhaitez insérez la liste déroulante et cliquez sur la cellule en question
  • Allez dans l’onglet Données > Bouton Validation des données
  • Dans l’onglet Paramètres, faites les choix suivants; veillez à taper le signe égal avant de saisir le nom de la plage

liste

Les utilisateurs qui cliqueront sur la cellule se verront proposer la liste que vous avez définie. Bien entendu, vous pouvez recopier vers le bas les paramètres de la cellule à l’aide de la poignée de recopie.

Remarquez que la boîte de dialogue Validation des données permet également d’ajouter un message de saisie ainsi qu’un message d’erreur personnalisés.

Excel – Comparer le contenu de deux plages de cellules

Contexte

Vous devez comparer, dans une feuille Excel, les contenus de deux colonnes (A et B) dans lesquelles ont été saisis par exemple des noms de personnes. Vous devez trouver si des noms ont été saisis dans les deux colonnes, sachant qu’ils n’ont pas été saisis dans le même ordre (par ex. mon nom a été saisi en A4 dans la colonne A et en B2 dans la colonne B). Si Excel trouve une correspondance de noms entre la colonne A et la colonne B, vous aimeriez qu’il vous indique, dans la colonne C, le nom de la personne trouvée à double.

EquivExcel

Solution

  • Entrez la formule suivante dans une cellule vide (dans mon exemple en C1)
    =SI(ESTERREUR(EQUIV(A1;$B$1:$B$42;0)); ?pas trouvé?; A1)
  • A l’aide de la poignée de recopie, recopiez cette formule jusqu’en C42.

Excel va vérifier, dans la plage allant de B1 à B42, si il trouve le même contenu que dans la cellule A1. S’il y a équivalence entre le contenu de la cellule A1 et la plage de cellules B1:B42, Excel va indiquer le contenu (autrement dit le prénom de la personne) qu’il a trouvé en A1. S’il n’a pas trouvé d’équivalence, Excel écrit le texte « pas trouvé ».
Le 0 figurant dans la formule a son importance car il indique à la fonction EQUIV que la recherche doit se faire n’importe où dans la plage allant de B1:B42.
Les dollars $ ont également leur importance car vous allez recopier cette formule jusqu’en C42. Si vous omettez les $, les adresses seront relatives (elles vont donc s’adapter lors de la recopie) alors qu’il est absolument nécessaire que les adresses de la plage soient absolues (car les adresses B1 à B42 doivent rester fixes).

Remarquez qu’Excel ne tient pas compte de la casse dans la recherche d’équivalent.

Remarquez aussi que cette formule fonctionnerait dans le cas où le texte recherché est lui-même le fruit d’une concaténation de prénom et de nom effectuée à l’aide de la formule suivante : =CONCATENER(F1;? ?;G1).

Excel – Imprimer les indicateurs de commentaires

Contexte

Vous avez inséré des commentaires dans votre tableau Excel. Vous avez trouvé comment afficher les commentaires (Onglet Révision > bouton Afficher tout), vous avez trouvé comment imprimer les commentaires (Fichier > Imprimer > bouton Mise en page > onglet Feuille > Options Commentaires)… mais vous n’avez pas trouvé comment imprimer les petits triangles rouges (appelés indicateurs) figurant au coin des cellules possédant des commentaires. Normal ! cette fonctionnalité n’existe pas. Pour combler cette lacune, nous devons créer deux macros, la 1ère pour imprimer les indicateurs et la 2ème pour ne plus les imprimer.

Note : Ces macros fonctionnent aussi bien sur Mac que sur PC.

Solution

1. Créer une macro pour imprimer les indicateurs de commentaires

  • Allez dans l’onglet Développeur > bouton Macro
  • Dans la boîte de dialogue Macro, donnez un nom (dans notre cas, vous pouvez écrire n’importe quoi) et cliquez sur le bouton Créer
  • Dans la fenêtre Module, supprimez Sub() et End Sub
  • Copiez-collez tel quel le code ci-dessous :

Sub ImprimerIndicateursCommentaires()
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double ‘shape width
Dim shpH As Double ‘shape height

Set ws = ActiveSheet
shpW = 6
shpH = 4

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left – shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 10 ‘Red
’12=Blue, 57=Green
.Fill.Visible = msoTrue
.Fill.Solid
.Line.Visible = msoFalse
End With
Next cmt
End Sub

  • Fermez la fenêtre Module
  • Pour testez votre macro, allez dans l’onglet Développeur > bouton Macro et exécutez la macro appelée ImprimerIndicateursCommentaires
  • Faites un aperçu avant impression pour vérifier la présence des petits triangles rouges

2. Créer une macro pour ne plus imprimer les indicateurs de commentaires

  • Allez dans l’onglet Développeur > bouton Macro
  • Donnez un nom (dans notre cas, vous pouvez écrire n’importe quoi) à cette 2ème macro et cliquez sur le bouton Créer
  • Dans la fenêtre Module, en dessous de la 1ère macro, supprimez Sub() et End Sub
  • Copiez-collez tel que le code ci-dessous :

Sub NepasImprimerIndicateursCommentaires()
Dim ws As Worksheet
Dim shp As Shape

Set ws = ActiveSheet

For Each shp In ws.Shapes
If Not shp.TopLeftCell.Comment Is Nothing Then
If shp.AutoShapeType = _
msoShapeRightTriangle Then
shp.Delete
End If
End If
Next shp
End Sub

  • Fermez la fenêtre Module
  • Pour testez votre macro, allez dans l’onglet Développeur > bouton Macro et exécutez la macro appelée NepasImprimerIndicateursCommentaires
  • Faites un aperçu avant impression pour vérifier l’absence des petits triangles rouges

Le détail qui fait boutique : associer aux macros un raccourci clavier (à l’aide du bouton Options de la boîte de dialogue Macro) ou un bouton dans la barre d’outils (Affichage > Barres d’outils > Personnaliser les barres d’outils et les menus > onglet Commandes > catégorie Macro).

Excel – Utiliser l’index

Contexte

Entre le cappuccino et la galette des rois, quelqu’un me dit « c’est bien dommage que dans Excel il n’y ait pas la possibilité de disposer instantanément d’un index comme dans FileMaker… » (oui, nous avons de curieuses discussions à la pause…). Cette personne faisait référence à la possibilité d’obtenir dans une cellule – appelons-la A21 – la liste de toutes les données saisies dans la colonne A.

Solution

  • Cliquez sur une cellule (dans mon exemple A21) située dans une colonne contenant du texte (Titre, Prénom, Localité…) ou des données alphanumériques (NPA, No plaque de voiture…)
  • Appuyez sur les touches Alt + Flèche en bas*; une liste s’affiche vous permettant 1° de consulter la liste des entrées classées par ordre alphabétique et 2° si souhaité d’y choisir une entrée au lieu de la taper

index

*Ce raccourci-clavier correspond au clic-droit > liste de choix… pratique non ?

Excel – Aérer les formules de calcul

Contexte

Vous avez ouvert un tableau Excel et certaines formules de calcul vous semblent difficile à déchiffrer (surtout si elles ont été faites par quelqu’un d’autre). Vous aimeriez simplement rendre ces formules plus lisibles en séparant les éléments qui les constituent.

Solution : aérez vos formules

  • Tout d’abord, agrandissez la zone dédiée à la barre de formule (nécessaire seulement sur Mac)
  • Placez votre curseur dans la formule à l’endroit souhaité
  • Appuyez sur les touches Cmd + Ctrl + Enter (Mac) ou sur les touches Alt + Enter (PC); vous obtenez alors un saut de ligne
  • Poursuivez ainsi en insérant des saut de ligne aux endroits souhaités
  • Insérez éventuellement des espaces blancs pour séparer les parenthèses :

La formule :

=SI(SOMME(D23-C23;F23-E23;H23-G23)=0;””;SOMME(D23-C23;F23-E23;H23-G23))

devient :

=SI(
SOMME(D23-C23;F23-E23;H23-G23)
=0;””;
SOMME(D23-C23;F23-E23;H23-G23) )

Les sauts de ligne et les espaces blancs sont sans effets secondaires indésirables sur le résultat de la formule.

Excel – Utiliser la fonction SI(ESTVIDE)

Contexte

Vous disposez d’un fichier Excel contenant par exemple des adresses de contacts internes (facultés, services, unités…). Dans votre tableau, une colonne est prévue pour contenir les URL des contacts (colonne B). Or, pour certains d’entre eux les URL sont inconnues et vous aimeriez, dans ce cas, qu’une URL « générique » soit utilisée (par ex. www.unil.ch). Il faut donc insérer dans une nouvelle colonne (colonne C), une fonction qui complèterait automatiquement les URL inexistantes par l’URL générique (se trouvant en A1).

Solution : utiliser la fonction SI(ESTVIDE)

  • Créez une colonne supplémentaire par ex. URL_complété
  • En C3, saisissez la fonction suivante : =SI(ESTVIDE(B3);T($A$1);T(B3)) autrement dit si B3 est vide, alors insérer le texte se trouvant en A1, sinon insérer le texte se trouvant en B3. Remarquez que j’ai transformé A1 en une adresse absolue (à l’aide des $)
  • Recopiez cette fonction sur les autres cellules de la colonne C

SiEstVide

Excel – Copier-coller uniquement les cellules visibles

Contexte

Vous travaillez sur une feuille de calcul dont vous avez masqué quelques colonnes. Vous aimeriez faire un copier-coller du tableau (par exemple pour l’insérer dans un nouveau classeur). Vous ne souhaitez pas inclure les colonnes masquées car celles-ci ne doivent pas figurer sur la copie (info confidentielle, note perso… à vous de voir pourquoi). En résumé, vous n’aimeriez copier que les colonnes visibles du tableau.

Solution

  • Sélectionnez le tableau à copier (en conservant les colonnes masquées)
  • Appuyez sur la touche F5 (laquelle ouvre la boîte de dialogue Atteindre)
  • Cliquez sur le bouton Cellules puis choisissez l’option Cellules visibles uniquement
  • Cliquer sur OK
  • Appuyez sur Cmd + C
  • Cliquez sur la cellule où vous souhaitez coller le tableau (dans le même classeur, dans un autre onglet ou dans un nouveau classeur peu importe)
  • Appuyez sur Cmd + V

Excel – Copier plusieurs paragraphes dans une seule cellule

Contexte

Vous disposez d’un texte dans Word que vous aimeriez coller dans Excel. Ce texte (par ex. des commentaires ou une procédure…) est composé de plusieurs paragraphes séparés par des Enter.

Vous avez bien tenté de faire un copier-coller ordinaire mais vous constatez qu’Excel change de cellule à chaque fois qu’un Enter a été tapé dans Word alors que vous aimeriez en fait que le texte soit contenu dans une seule cellule.

Solution : éditer la cellule avant de coller le texte

  • Copiez les paragraphes contenus dans Word
  • Dans Excel, double-cliquez sur la cellule de destination pour l’éditer (votre curseur clignote dans la cellule)
  • Collez le texte dans la cellule et appuyez sur Enter
  • Si nécessaire, redimensionnez la colonne
  • Supprimez le Enter inutile figurant à la fin du texte

Excel – Coller et multiplier en une seule opération

Contexte

Vous avez reçu d’un fournisseur une feuille de calcul contenant des valeurs en euro. Vous aimeriez rapidement faire apparaître des francs suisses à la place des euros. Vous n’avez pas envie de toucher à la structure du tableau (genre ajouter des colonnes pour y faire des calculs de conversion….). Vous connaissez le taux de change, par ex. 1.2061.

Solution

  • Dans l’importe quelle cellule vide du tableau, tapez le taux de change 1.2061
  • Appuyez sur Cmd + C pour copier ce taux de change
  • Sélectionnez la(les) cellules(s) contenant les valeurs en euro
  • Ouvrez le menu Edition > Collage spécial
  • Choisissez Multiplication et validez