All posts in excel-pratique

Le format personnalisé utilisé dans la section précédente ajoute un espace et le mot ventes à chaque cellule. Supposons qu’une cellule contienne la valeur 0 ou 1. Il faudrait ajouter le mot vente au singulier et non au pluriel. Pour cela, vous utiliserez une formule légèrement plus complexe :

[<2]Standard" vente"; Standard" ventes"

Si la valeur est inférieure à 2, le mot vente précédé d’un espace est ajouté à la cellule. Dans le cas contraire, le mot ventes précédé d’un espace est ajouté à la cellule :

Il est très simple d’ajouter une unité dans une ou plusieurs cellules. Commencez par sélectionner les cellules concernées. Basculez sur l’onglet Accueil du ruban, puis cliquez sur l’icône Format de nombre dans le groupe Nombre. Vous pouvez utiliser le raccourci Contrôle + Majuscule + 1 pour parvenir au même résultat. La boîte de dialogue Format de cellule s’affiche.

Sous l’onglet Nombre, sélectionnez Personnalisé dans la zone de liste Catégorie et ajoutez l’unité dans la zone de texte Type en vous aidant de la zone de prévisualisation :

Voici le résultat :

Cet article va vous montrer comment extraire des données dans une plage en fonction de la valeur d’une cellule. Pour cela, vous utiliserez les fonctions INDEX() et EQUIV().

La fonction INDEX()

La fonction INDEX() retourne la valeur d’une cellule dans une plage en fonction de ses coordonnées ligne et colonne. Voici sa syntaxe :

=INDEX(plage; ligne; colonne)

Où :

• plage est la plage de cellules concernée.
• ligne et colonne sont la ligne et la colonne de la cellule dont on veut connaître la valeur.

Voici un exemple d’utilisation :

La formule utilisée pour remplir la cellule C12 est :

=INDEX(A1:E10;4;5)

La plage de cellules examinée est A1:E10. La valeur à l’intersection de la quatrième ligne et de la cinquième colonne dans cette plage est 827 952.

Remarque

• Si la plage contient une seule ligne ou une seule colonne, la ligne ou la colonne est facultative.
• Si la plage comporte plusieurs lignes et plusieurs colonnes et que la ligne ou la colonne est omise, la fonction INDEX() retourne une plage qui correspond à la ligne ou à la colonne de la plage.

La fonction EQUIV()

La fonction EQUIV() retourne la position d’une valeur dans une plage de cellules. Voici sa syntaxe :

=EQUIV(recherche; plage; type de recherche)

Où :

• recherche est la valeur recherchée.
• plage est la plage de cellules examinée.
• type de recherche peut prendre l’une des valeurs suivantes :
  • 0 pour rechercher la première correspondance exacte. Si aucune correspondance exacte n’est trouvée, la position de la valeur supérieure la plus proche est retournée.
  • 1 pour rechercher la valeur inférieure ou égale la plus proche de recherche. Les valeurs de la plage doivent être placées en ordre croissant, sans quoi cette fonction produira une erreur.
  • -1 pour rechercher la valeur supérieure ou égale la plus proche de recherche. Les valeurs de la plage doivent être placées en ordre décroissant, sans quoi cette fonction produira une erreur.

Voici un exemple d’utilisation :

La formule utilisée pour remplir la cellule D12 est :

=EQUIV(2014;A1:A10;0)

La valeur 2014 est recherchée de façon exacte (0 en troisième paramètre) dans la plage A1:A10. La septième cellule contient la valeur recherchée. C’est donc le résultat retourné par la fonction EQUIV().

Il est parfois nécessaire de construire une sous-liste à partir d’une liste existante. Pour illustrer ce principe, nous allons raisonner sur un exemple. Ici, la liste de droite va être complétée en parcourant les données de la liste de gauche :

Pour remplir les cellules de la deuxième liste, vous utiliserez la fonction RECHERCHEV() :

RECHERCHEV(valeur recherchée; zone de recherche; numéro de colonne à insérer)

Où :

• valeur recherchée représente la cellule recherchée (ici une cellule de la colonne I).
• zone de recherche représente la plage dans laquelle la valeur sera recherchée.
• numéro de colonne à insérer représente le numéro de la colonne dans la zone de recherche de la valeur à retourner.

Voici la formule utilisée pour calculer la cellule J4 :

=RECHERCHEV(I4;E4:G12;3)

Le contenu de la cellule I4 est recherché dans la plage E4:G12, et lorsque cette valeur est trouvée, la troisième colonne de cette plage est retournée.

Il ne reste plus qu’à dupliquer cette formule sur les cellules J5 et J6 en utilisant le carré de recopie pour obtenir le résultat recherché :

Deux remarques pour terminer :

Si vous modifiez les valeurs de la plage de recherche, les cellules calculées par la fonction RECHERCHEV() sont automatiquement mises à jour.

Si les données de la plage de recherche sont disposées horizontalement et non verticalement, vous utiliserez la fonction RECHERCHEH() à la place de la fonction RECHERCHEV().

Supposons qu’un classeur liste un certain nombre de tâches à faire. Une des colonnes (dans cet exemple, ce sera la colonne D) contient la date d’échéance de chaque tâche. Le but du jeu est de colorer les cellules de la colonne D dont la date du jour est proche de la date d’échéance.

A titre d’exemple :

• La cellule sera colorée en orange si la différence entre la date du jour et la date d’échéance est inférieure ou égale à 5 jours ouvrés.
• La cellule sera colorée en rouge si la date du jour est la même que la date d’échéance.

La capture d’écran ci-après a été réalisée le 15/08/2018 :

La cellule D1 n’est pas colorée car la date d’échéance est supérieure à 5 jours ouvrés. La cellule D2 est colorée en orange car la date d’échéance est inférieure à 5 jours ouvrés. Enfin, la cellule D3 est colorée en rouge car la date d’échéance est la date du jour.

Pour arriver à ce résultat, commencez par sélectionner la colonne D. Basculez sur l’onglet Accueil du ruban. Dans le groupe Styles, cliquez sur l’icône Mise en forme conditionnelle puis cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s’affiche. Dans la zone Sélectionnez un type de règle, cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

Nous allons commencer par la formule qui colore la cellule en rouge, car c’est la plus simple. Dans la zone de texte Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie, tapez :

=$D1=AUJOURDHUI()

La mise en forme sera appliquée si la cellule $D1 contient la date du jour. Remarquez le signe $ devant la lettre D. L’adressage de la colonne est donc absolu. Pour chaque ligne analysée, la colonne D sera invariablement testée. Etant donné que le numéro de ligne n’est pas précédé du signe $, il s’agit d’un adressage relatif, il s’adaptera donc à chaque ligne analysée.

Cliquez sur l’icône Format. La boîte de dialogue Format de cellule s’affiche. Sous l’onglet Remplissage, sélectionnez la couleur rouge et validez en cliquant sur OK. La boîte de dialogue Format de cellule doit maintenant ressembler à ceci :

Validez en cliquant sur OK.

Vous allez maintenant définir la règle qui colore les cellules de la colonne D en orange si la différence entre la date du jour et la date de la cellule est inférieure ou égale à 5 jours ouvrés.

Assurez-vous que la colonne D est toujours sélectionnée. Sous l’onglet Accueil du ruban, cliquez sur l’icône Mise en forme conditionnelle puis cliquez sur Nouvelle règle. La boîte de dialogue Nouvelle règle de mise en forme s’affiche. Dans la zone Sélectionnez un type de règle, cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.

Dans la zone de texte Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie, tapez :

=ET(NB.JOURS.OUVRES(AUJOURDHUI();D1)<=5;$D1<>AUJOURDHUI();$D1<>"")

Cette formule appliquera la mise en forme si les trois conditions définies dans la fonction ET() sont vérifiées.

La première condition teste si le nombre de jours ouvrés compris entre la date du jour et la date de la cellule $D1 est inférieur à 5 :

NB.JOURS.OUVRES(AUJOURDHUI();D1)<=5

La deuxième condition teste si la date de la cellule $D1 n’est pas égale à la date du jour. Ceci afin d’éviter d’écraser la règle qui colore les cellules en rouge :

$D1<>AUJOURDHUI()

Enfin, la troisième condition vérifie que la cellule $D1 est renseignée. Ceci pour éviter de colorer les cellules non renseignées :

$D1<>""

Cliquez sur l’icône Format. La boîte de dialogue Format de cellule s’affiche. Sous l’onglet Remplissage, sélectionnez la couleur orange et validez en cliquant sur OK. La boîte de dialogue Format de cellule doit maintenant ressembler à ceci :

Validez en cliquant sur OK. La mise en forme conditionnelle est entièrement opérationnelle.

Les utilisateurs ne font pas toujours ce qu’on attend d’eux. Avec l’expérience, je pourrais même dire qu’ils font rarement ce que l’on attend d’eux ! Si vous définissez des feuilles de calcul qui seront remplies par quelqu’un d’autre que vous, il est important de valider les données saisies.

Nous allons raisonner sur un exemple simpliste dans lequel l’utilisateur doit saisir des années de naissance :

Sélectionnez la cellule C4. Basculez sur l’onglet Données, puis cliquez sur l’icône Validation des données dans le groupe Outils de données. La boîte de dialogue Validation des données s’affiche. Sous l’onglet Options, sélectionnez Nombre entier dans la liste déroulante Autoriser et comprise entre dans la liste déroulante Données. Tapez 1920 dans la zone de texte Minimum et 2018 dans la zone de texte Maximum. Ainsi, la saisie des années de naissance ne sera valide qu’entre 1920 et 2018 :

Basculez sur l’onglet Message de saisie. Tapez Année de naissance dans la zone de texte Titre et Entre 1920 et 2018 dans la zone de texte Message de saisie :

Basculez sur l’onglet Alerte d’erreur. Tapez Saisie non valide dans la zone de texte Titre et Attention, l’année de naissance doit être comprise entre 1920 et 2018 ! dans la zone de texte Message d’erreur :

Validez en cliquant sur OK.

Sélectionnez les cellules dans lesquelles la saisie de la date de naissance doit être validée, puis cliquez sur l’icône Validation des données, sous l’onglet Données. Une boîte de dialogue vous demande si vous voulez étendre la validation des données aux cellules sélectionnées. Confirmez en cliquant sur Oui :

La boîte de dialogue Validation des données s’affiche. Confirmez le réglage en cliquant sur OK.

Désormais, lorsque vous saisirez une année de naissance incorrecte, un message d’erreur s’affichera et la saisie ne sera pas acceptée :

Il est parfois nécessaire de mettre en évidence les différences entre deux blocs de données quasiment similaires. Par exemple, deux tableaux qui traduisent l’évolution des ventes d’un ensemble de produits.

Nous allons partir de ces données :

Ici, le nombre de données est assez limité et il est facile de voir quelles sont les valeurs qui ont évolué, mais la technique qui va être décrite pourrait tout aussi bien s’appliquer à des milliers de lignes…

Sélectionnez le premier bloc de données. Ici, les cellules A4 à B14. Maintenez la touche Contrôle du clavier enfoncée et sélectionnez le deuxième bloc de données. Ici, les cellules D4 à E14.

Sous l’onglet Accueil, dans le groupe Styles, cliquez sur l’icône Mise en forme conditionnelle, pointez l’entrée Règles de mise en surbrillance des cellules puis cliquez sur Valeurs en double :

Les doublons sont colorés et la boîte de dialogue Valeurs en double s’affiche. Pour faciliter la lecture, sélectionnez uniques dans la liste déroulante et cliquez sur OK :