Excel
logo-cristalhygiene logo-bbaembalages

Service informatique

Truc et Astuces pour Excel

Figer les volets

Cette fonction relativement bien connue, mais ceux qui l'ignorent peuvent perdre beaucoup de temps. Figer les volets consiste à afficher, par exemple, la première ligne et la première colonne du tableau (qui généralement comportent les intitulés), où se trouvent généralement les intitulés, quel que soit l'endroit où l'on se trouve dans le document. Pratique dans les grands tableaux, pour savoir à quoi correspond le chiffre de la 15e colonne et de la 169e ligne sans avoir besoin de scroller frénétiquement.

Pour Cela:

  • Se placer dans la cellule située sous la ligne à figer et à droite de la colonne à figer
  • Dans l'onglet Affichage, CLIQUER sur Figer les volets
  • CLIQUER sur Figer les volets
  • Pour annuler, il suffit de se rendre au même endroit et de cliquer sur Libérer les volets.
fermer

Raccouris Clavier

  • Recalculer les données : F9
  • Insérer la date du jour : CTRL + ; (point-virgule)
  • Insérer l’heure : CTRL + : (deux points)
  • Atteindre une cellule ou un tableau : F5
  • Sélectionner toute une colonne active : Ctrl + Espace
  • Sélectionner toute une ligne active : Maj+ Espace
  • Sélectionner toute la feuille Excel : Ctrl + A
  • Faire un collage spécial sur Excel (pour ne coller que les données etc.) : Ctrl + Alt + V
  • Insérer une somme automatique : ALT + =
  • Masquer (et surtout ré-afficher) le ruban des fonctionnalités : Ctrl + F1
  • F2 : pour compléter une cellule
fermer

Créer une suite logique grâce au clique glisser

Au lieu de copier-coller les formules, vous pouvez les dupliquer grâce au cliquer glisser. Il suffit de sélectionner la(les) cellule(s) source, puis placer son curseur en bas à droite de la cellule. Une petite croix va apparaître, permettant de copier-coller facilement la formule (ou une série de formules) vers le côté ou vers le bas. Certaines valeurs, telles que des plages de cellules, seront remplacées automatiquement pour conserver la logique de vos fonctions.

Vous pouvez également créer une suite logique grâce à cette astuce, mais vous devez obligatoirement sélectionner plusieurs cellules. Exemple : si deux cellules adjacentes contiennent deux dates qui se suivent (le 16 octobre et le 17 octobre par exemple), vous pouvez les sélectionner, cliquer sur la petite croix en bas à droite de la cellule et glisser votre curseur sur plusieurs cellules pour afficher le 18 octobre, le 19 octobre, le 20 octobre et ainsi de suite. Ceci marche aussi pour les suites de nombres.

suite logique fermer

Figer la ligne, la colonne ou la cellule dans une fonction Excel

Dans certains cas, on souhaiter « figer » une ligne, une colonne ou une cellule dans une suite de fonctions. Pour toujours diviser un calcul par la même cellule par exemple, quel que soit l’emplacement de la formule. Pour ce faire, lorsque vous saisissez votre formule, il suffit d’appuyer sur la touche F4 lorsque la cellule en question est mentionnée. Par exemple, inscrivez =SOMME(D4, puis appuyez sur F4. Le début de votre fonction va se transformer en =SOMME($D$4. Le symbole dollar indique que l’élément suivant est figé (ici, la colonne D et la ligne 4 sont figés). Il suffit d’appuyer à nouveau sur F4 pour figer uniquement la ligne ou la colonne.

figer une cellulue fermer

Modifier l’affichage d’une cellule selon le type de données

Excel permet de modifier l’affichage d’une cellule selon le type de données. Il peut s’agir d’un texte, d’une valeur, d’une date, d’un prix… Sélectionnez la cellule (ou la plage de cellule de votre choix), faîtes un clic droit, puis choisissez « format de cellule ».

Cette astuce permet aussi de choisir le nombre de chiffres après la virgule. Il suffit de choisir la catégorie « Nombre » puis le nombre de décimales après la virgule. Pratique !

fermer

Modifier la hauteur d'une ligne ou la largeur d'une colonne

Pour modifier la taille d’une ligne ou d’une colonne, il suffit de faire un clic droit sur le numéro de la ligne ou la lettre de la colonne puis choisir « hauteur de ligne » ou « largeur de colonne ». Vous pouvez spécifier la largeur par défaut des colonnes grâce au bouton « Format » de l’onglet Accueil du ruban.

fermer

Gérer plusieurs feuilles Excel sur un classeur

Un document Excel peut être composé de plusieurs onglets. On appelle un classeur le document complet, les onglets sont des feuilles. Vous pouvez ajouter facilement des feuilles grâce au bouton dédié en bas à gauche de la fenêtre (Maj + F11). N’hésitez pas à nommer vos feuilles pour plus lisibilité. Vous pouvez également donner une couleur à un onglet pour mieux le retrouver (clic droit sur l’onglet).

fermer

Trier et filtrer les données d’un tableau Excel

Trier les données sur Excel, c’est très facile : sélectionnez d’abord votre tableau (avec ou sans les étiquettes), puis cliquez sur Trier dans l’onglet Données. Une fenêtre s’ouvre, vous invitant un trier votre tableau selon telle ou telle colonne et de telle ou telle façon (du plus petit au plus grand etc.).

Évidemment, dans ce cas, l’intégralité de la ligne est conservée, seul le tri des lignes est modifié. Si vous le souhaitez, vous pouvez également filtrer les données. C’est pratique pour n’afficher que les données de certains clients par exemple (en filtrant la ville etc.). Il suffit alors de sélectionner votre tableau puis cliquer sur Filtrer, dans l’onglet Données du ruban.

fermer

Sélectionner un tableau en entier ou atteindre la fin d’Excel

Sur Excel, des raccourcis clavier sont particulièrement utiles :

  • Ctrl + Flèche: permet d’atteindre la dernière colonne ou la dernière ligne d’une série de données.
  • Maj + Flèche : permet de sélectionner la cellule suivante.
  • Ctrl + Maj + Flèche permet donc de sélectionner tout un tableau. Il suffit de se placer sur la première cellule d’un tableau, utiliser Ctrl + Maj + Flèche droite, puis Ctrl + Maj + Flèche du bas
fermer

Personnaliser l’impression d’un tableau Excel

Les feuilles Excel sont pratiques pour agréger de nombreuses données. Mais bien souvent, l’impression brute des classeurs Excel donne des résultats peu convaincants. Pour améliorer l’impression des tableaux Excel, vous pouvez passer par l’onglet Mise en Page, puis « Imprimer les titres ». Une fenêtre vous permet alors de choisir une zone d’impression, les titres à imprimer etc. Vous pouvez aussi imprimer le quadrillage, les commentaires Excel et les erreurs générées par vos fonctions.

fermer

Les macros sur Excel

Les macros c'est un enregistrements de vos actions (clic, insertions de donnée...) pendant un laps de temps. C'est une fonction qui est très utiles pour automatiser certaines tâches répétitive. Par exemple vous avez extrait votre fichier client et vous cacher un certains nombre de colonne qui ne vous interesse pas pour le traitement de ce tableau. Au lieu qu'à chaque extraction, vous faites les mêmes actions, grace aux Macros, Excell les réalisera pour vous.

Lors de la création de la macro, il faut faire attention à ne pas faire une étourderie...Car Exceell la répétera a chaque fois que la macro sera lancé, mais une fois qu'elle est bien configuré, c'est un gain de temps indéniable. Je vous conseil, de faire un essai rapide, afin de vous familiariser avec cette fonction.

Pour afficher l'option macro, il suffit d'aller dans fichier > option puis aller dans personnaliser le ruban et cocher la case Développeur puis sur oK. Un nouvel onglet apparait à côter d'affichage.

Pour créer une macro, cliquer sur enregistrer une macro, rentrer les informations demandées puis sur ok. Vous pouvez enregister votre macro seulement pour votre classeur ou pour tout vos documents Excell. A partir du moment ou vous avez cliqué sur Ok, la macro va enregistrer tout ce que vous faites sur Excel jusqu'à ce que vous apuyer sur Arrêter l'enregistrement. Pour utiliser la macro que vous avez créé, il suffit de cliquer sur Macros et choisir la macro souhaitée.

Si vous vous rendez compte que vous avez fait une erreur (se tromper dans la sélection de la cellule...)lors de la création de la macro, pas la peine de tout recommencer, il suffit d'aller dans Macros puis selectionner la macros à modifier et cliquer sur modifier. Une fenêtre s'ouvre (Visual Basic)et votre macro est affiché sous divers ligne de code. Il suffit de modifier la partie que vous souhaité (changer la cellule selectionné....)

fermer

Convertir un csv en fichier Excel

Pour convertir un CSV en fichier Excel :

  • Ouvrie le fichier CSV avec Excel
  • Allez dans l'onglet "Données"
  • Selectionner la colonne qui contient les données à transformer
  • Cliquer sur "convertir"
  • Une fenêtre s’ouvre, c’est l’assistant de conversions, en trois étapes. Dans la première étape : si le séparateur de colonnes est une virgule, ou un point virgule, il faut laisser le bouton radio « Délimiter » coché, puis appuyez sur le bouton « Suivant ». Dans l’étape suivante : il faut choisir le symbole séparateur. Dans la majorité des cas, il faut choisir la virgule ou le point virgule.
  • Appuyer sur « Suivant » pour valider les étapes,il est possible de définir le format des données de chaque colonne (exemple : date, texte, etc.). Si l’on n’a pas d’exigences spécifiques, il suffit simplement de laisser le bouton radio « standard » coché et de terminer l’opération en appuyant sur le bouton « Terminer ».
  • La conversion s’effectue. Il reste ensuite à enregistrer le document. Aller dans « Enregistrer sous », puis dans « classeur Excel ». Dans la fenêtre qui s’ouvre, vous pouvez nommer le document et choisir le format de l’extension comme par exemple « .xls » ou « .xlsx ». Enfin, appuyer sur le bouton « Enregistrer ».
fermer

Supprimer les doublons

Les grands ensembles de données contiennent souvent des doublons. Si vous disposez d'une liste de plusieurs contacts dans une entreprise et que vous souhaitez afficher uniquement le nombre d'entreprises, la fonction de suppression des valeurs en double vous sera très utile.

Pour supprimer les doublons, sélectionnez la ligne ou la colonne que vous souhaitez traiter. Accédez ensuite à l'onglet Données, puis cliquez sur Supprimer les doublons dans le groupe Outils de données.

Une fenêtre contextuelle s'affiche et vous demande de confirmer les données que vous souhaitez traiter. Sélectionnez Supprimer les doublons pour supprimer les valeurs en double.

Vous pouvez également utiliser cette fonctionnalité pour supprimer une ligne entière en fonction d'une valeur de colonne en double. Si trois de vos lignes contiennent les informations de X et que vous ne souhaitez en afficher qu'une seule, vous pouvez sélectionner l'ensemble de vos données puis supprimer les doublons selon l'adresse e-mail. Votre liste ne contiendra plus que des noms uniques.

fermer

Repérez facilement les onglets d'un classeur

Vous pouvez également utiliser cette fonctionnalité pour supprimer une ligne entière en fonction d'une valeur de colonne en double. Si trois de vos lignes contiennent les informations de X et que vous ne souhaitez en afficher qu'une seule, vous pouvez sélectionner l'ensemble de vos données puis supprimer les doublons selon l'adresse e-mail. Votre liste ne contiendra plus que des noms uniques.

fermer

Liste des Erreurs

#DIV/0

Cette erreur signifie que vous tentez d'effectuer une division par zéro, ce qui n'est pas possible, d'où cette erreur.

Par exemple, =50/A1 renverra cette erreur si la cellule A1 contient 0 ou est vide.

#NOM ?

Cette erreur apparaît lorsque le nom de la fonction ou le nom d'une cellule (ou plage de cellules) que vous essayez d'utiliser est mal orthographié. Excel ne peut donc pas trouver la fonction ou le nom demandé et renvoie cette erreur.

Par exemple, =SOME(A1:A3) renverra cette erreur car la fonction SOMME est mal orthographiée. De même, avec =SOMME(nom) vous obtiendrez la même erreur si aucune plage de cellules n'a été nommée "nom".

Notez que si vous oubliez d'ajouter les guillemets à une valeur de texte comme par exemple =SI(A1="";"OUI";NON), vous obtiendrez la même erreur car Excel ne va pas considérer "NON" comme du texte mais va rechercher une fonction ou un nom nommé "NON" et renverra cette erreur s'il ne trouve rien.

#REF!

Cette erreur apparaît lorsque la référence vers une cellule, une plage de cellules ou une feuille n'existe plus.Par exemple, la formule suivante fait référence à une cellule de la feuille 2 ="Résultat : "&Feuil2!A1. Si cette feuille est maintenant supprimée, la référence vers cette feuille n'existera plus, la formule sera remplacée par ="Résultat : "#REF!A1 et renverra l'erreur #REF!.

#N/A

Cette valeur apparaît lorsqu'il n'y a pas de résultat possible, il ne s'agit pas réellement d'une erreur mais plutôt d'une valeur qui signifie "pas de résultat possible dans cette configuration".

C'est une valeur qui apparaît souvent avec les fonctions de recherche telles que RECHERCHEV, RECHERCHEH, EQUIV, etc.

Par exemple, si vous utilisez la fonction RECHERCHEV et que la "Valeur cherchée" n'est pas trouvée dans la plage de cellules, la valeur #N/A sera renvoyée.

Cette valeur peut également apparaître lorsque vous oubliez de renseigner un argument obligatoire (dans ce cas, aucun résultat n'est donc possible).

#VALEUR

Cette erreur apparaît lorsque la valeur entrée ne correspond pas à la valeur attendue.

Par exemple, si vous utilisez la fonction INDEX et qu'à la place du numéro de ligne, vous entrez une valeur qui n'est pas un numéro, vous obtiendrez l'erreur #VALEUR! car cela ne correspond pas au type de valeur attendu par la fonction.

De même, si vous essayez d'additionner un nombre avec un texte =50+"test" vous obtiendrez cette même erreur car une valeur texte ne correspond pas au type de valeur attendu pour une addition.

#NUL

Cette erreur apparaît lorsque 2 plages de cellules n'ont aucune intersection ou plus simplement en cas d'oubli de : ou ;

Par exemple, =SOMME (A1:A2 A3:A4) renverra cette erreur car il n'y a aucune intersection entre les 2 plages de cellules indiquées. Il peut également s'agir ici d'un oubli de ; pour séparer les 2 plages de cellules à additionner.

#NOMBRE !

Cette erreur apparaît lorsque vous essayez d'utiliser des valeurs numériques non valides, elle peut également apparaître si le résultat est un nombre trop grand (par exemple =1000^1000 renverra #NOMBRE !).

######

Cet affichage particulier apparaît généralement lorsque la largeur de la cellule est trop petite pour afficher une valeur numérique, dans ce cas il suffit d'élargir la colonne pour modifier l'affichage.

fermer

Tableaux croisés dynamique

Quelques Conseils :
  • Dans le tableau Excel (celui qui contient les données « sources »), il ne faut pas de colonne et de ligne vide
  • Il faut mettre des titres sur les colonnes du tableau
  • Les données sont saisies dans le tableau

Aller dans Insertion>Graphique Croisé dynamique puis sur le triangle noir et choisir Créer un tableau croisé dynamique

  1. Sélectionnez les cellules à partir desquelles vous voulez créer un tableau croisé dynamique.
  2. Sélectionnez Insertion > Tableau croisé dynamique.
tri croisé dynamique
  1. Sous Choisissez les données à analyser, cliquez sur Sélectionner un tableau ou une plage.
tri croise 2
  1. Dans Tableau/plage, vérifiez la plage de cellules.
  2. Sous Choisissez l’emplacement de votre rapport de tableau croisé dynamique, sélectionnez Nouvelle feuille de calcul pour placer le tableau croisé dynamique dans une nouvelle feuille de calcul Feuille de calcul existante, puis sélectionnez où le tableau croisé dynamique doit apparaître.
  3. Sélectionnez OK.
Conception de tableau croisé dynamique
  1. Pour ajouter un champ à votre tableau croisé dynamique, activez la case à cocher du nom de champ dans le volet Champs de tableau croisé dynamique.
  2. Remarque : Les champs sélectionnés sont ajoutés à leur zone par défaut : les champs non numériques sont ajoutés à Lignes, les hiérarchies de date et d’heure sont ajoutées à Colonnes, et les champs numériques sont ajoutés à Valeurs.
tri croise 3
  1. Pour déplacer un champ d’une zone à une autre, faites-le glisser vers la zone cible.
fermer