All posts in excel-pratique

Cette section va vous montrer comment renseigner une cellule lorsqu’une condition est vérifiée. Nous allons partir de cette feuille de calcul :

Le but est de recopier dans la colonne C les valeurs de la colonne B lorsqu’elles sont positives, et dans la colonne D les valeurs opposées de la colonne B lorsqu’elles sont négatives. Voici le résultat désiré :

Pour arriver à ce résultat, nous allons utiliser la fonction SI() dont voici la syntaxe :

=SI(test;valeur si vrai;valeur si faux)

Où test est un test logique, valeur si vrai est la valeur à afficher dans la cellule si le test est vrai et valeur si faux est la valeur à afficher dans la cellule si le test est faux.

Cliquez dans la cellule C2, puis entrez la formule suivante :

=SI(B2>0;B2;"")

Si la cellule B2 est positive, cette valeur est affichée dans la cellule C2. Sinon, c’est une chaîne vide qui est affichée.

Cliquez dans la cellule D2 et entrez la formule suivante :

=SI(B2<0;ABS(B2);"")

Si la cellule B2 est négative, sa valeur absolue est affichée dans la cellule D2. Sinon, c’est une chaîne vide qui est affichée.

Il ne vous reste plus qu’à utiliser la poignée de recopie pour copier la formule de la cellule C2 jusqu’à la cellule C10, puis la formule de la cellule D2 jusqu’à la cellule D10.

Pour aller plus vite, vous pouvez également sélectionner les cellules C2 et D2 et utiliser la poignée de recopie jusqu’à la cellule D10.

Il existe de nombreuses fonctions dédiées aux chaînes de caractères dans Excel. Vous les utiliserez pour remplir des cellules à partir d’autres cellules existantes. En voici quelques-unes :

Pour illustrer ces fonctions, nous allons raisonner sur cette feuille de calcul :

Les colonnes E à H sont calculées à partir des colonnes A à D :

• La colonne E est obtenue en assemblant les données de la colonne C et de la colonne D mise en majuscules.
• La colonne F est obtenue en remplaçant la lettre « A » par la lettre « a » dans les données de la colonne A.
• La colonne G recherche la sous-chaîne « er » dans les cellules de la colonne B. Elle affiche la position de cette sous-chaîne ou un code d’erreur si la sous-chaîne n’est pas trouvée.
• La colonne H est une amélioration de la colonne G. Elle affiche trouvé si la sous-chaîne « er » est trouvée dans les cellules de la colonne B. Elle affiche non trouvé dans le cas contraire.

Voici les formules utilisées :

• Cellule E2: =CONCATENER(C2;" ";MAJUSCULE(D2))

• Cellule F2: =SUBSTITUE(A2;"A";"a")

• Cellule G2: =CHERCHE("er";B2)

Comme vous le voyez, un code d’erreur s’affiche dans la colonne G lorsque la chaîne « er » n’est pas trouvée dans les cellules de la colonne B. Pour améliorer le comportement de la fonction CHERCHE(), vous allez tester la valeur renvoyée par cette fonction dans la fonction ESTERR(). Si une erreur est retournée (#VALEUR!), la chaîne « non trouvé » sera affichée. Sinon, la chaîne « trouvé » sera affichée. Rien de plus simple grâce à la fonction SI(). Voici la formule utilisée pour calculer la cellule H2 :

=SI(ESTERR(CHERCHE("er";B2));"non trouvé";"trouvé")

Pour compléter les cellules des lignes 3 à 92, sélectionnez les cellules E2 à H2 et utilisez la poignée de recopie jusqu’à la ligne 92.

Vous devez faire des calculs sur des dates ? Pour éviter que cela ne tourne au cauchemar, vous avez tout intérêt à utiliser les fonctions dédiées d’Excel. En voici quelques-unes :

Pour bien comprendre l’utilisation de ces fonctions, nous allons raisonner sur un exemple :

La cellule E1 contient la date du jour. Elle est automatiquement mise à jour à l’ouverture du classeur grâce à la fonction AUJOURDHUI() :

Les cellules des colonnes D, E et F contiendront respectivement le numéro de la semaine de la colonne C, le nombre de jours entre la date courante et les cellules de la colonne C, et le nombre de semaines entre la date courante et les cellules de la colonne C.

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

=NO.SEMAINE(C4)

La cellule E4 doit contenir le nombre de jours entre la cellule E1 et la cellule C4. Pour pouvoir recopier rapidement la formule sur les cellules de la colonne E, vous devez utiliser une référence absolue à la cellule E1 :

=JOURS($E$1;C4)

La cellule F4 doit contenir le nombre de semaines entre la cellule E1 et la cellule C4. Tout comme pour la cellule E4, vous devez utiliser une référence absolue à la cellule E1 :

=TRONQUE(($E$1-C4)/7)

Comme il n’existe aucune fonction pour déterminer le nombre de semaines entre deux dates, un calcul est nécessaire. En soustrayant les deux dates, on obtient une différence en jours. En divisant cette valeur par 7, on obtient une valeur en semaines. Enfin, en prenant la troncature de cette valeur on obtient un nombre entier de semaines.

La cellule D4 est recopiée jusqu’à la cellule D12, la cellule E4 est recopiée jusqu’à la cellule E12, et la cellule F4 est recopiée jusqu’à la cellule F12. Voici le résultat :

De nombreuses fonctions mathématiques sont disponibles dans Excel. Voici les plus courantes :

Pour voir comment utiliser ces fonctions, nous allons partir de cette feuille de calcul :

A titre d’exemple, nous allons calculer la somme des ventes mensuelles pour l’ensemble des commerciaux et la moyenne des ventes par trimestre pour chacun des commerciaux.

La somme des ventes mensuelles sera calculée dans les cellules C16, C17 et C18.

Sélectionnez les cellules C5 à C15. L’icône Analyse rapide s’affiche dans la partie inférieure droite de la sélection. Cliquez dessus :

Un panneau comportant cinq onglets s’affiche. Sélectionnez l’onglet Totaux et cliquez sur l’icône Somme :

La somme des cellules C5 à C15 s’affiche dans la cellule C16. Si vous cliquez sur cette cellule, la barre de formule contient ceci :

=SOMME(C5:C15)

La plage C5:C15 ayant été définie de façon relative dans la formule, vous pouvez recopier la formule dans les cellules D16 et E16. Pointez le carré noir en bas et à droite de la cellule C16. Le pointeur de la souris se transforme en un signe plus. Maintenez le bouton gauche de la souris enfoncé puis déplacez la souris vers la cellule E16 :

Relâchez le bouton gauche de la souris pour recopier la formule. Voici le résultat :

Vous allez maintenant calculer la moyenne des ventes par trimestre pour chacun des commerciaux. Sélectionnez les cellules C5 à E5. L’icône Analyse rapide s’affiche à droite de la sélection. Cliquez dessus. Un panneau comportant cinq onglets s’affiche. Sélectionnez l’onglet Totaux. Pointez l’icône Moyenne. Comme vous pouvez le voir, Excel calcule la moyenne des cellules sélectionnées par colonnes et l’affiche sur la ligne inférieure, ce qui n’est pas du tout l’effet recherché :

Cliquez sur la flèche à droite des six icônes. Cette fois-ci l’icône Moyenne donne bien le résultat attendu. Cliquez dessus :

Si vous cliquez sur la cellule F5, vous verrez qu’elle contient la formule suivante :

=MOYENNE(C5:E5)

Les cellules étant référencées de façon relative, vous pouvez utiliser la poignée de recopie pour obtenir les moyennes des autres commerciaux :

Dans la section précédente, vous avez calculé un prix TTC dans la cellule D2 avec la formule suivante :

=B2*(1+C2)

Ici, la référence aux cellules B2 et C2 est faite de façon relative.

Si vous copiez la cellule D2 dans la cellule D3 avec un simple copier-coller, la formule s’adaptera pour faire référence à la ligne 3. Cliquez sur la cellule D2 et cliquez sur l’icône Copier, dans le groupe Presse-papiers, sous l’onglet Accueil du ruban. Cliquez dans la cellule D3 et cliquez sur l’icône Coller, dans le groupe Presse-papiers, sous l’onglet Accueil du ruban. Comme vous le voyez, la formule s’adapte à la ligne 3 : les cellules B2 et C2 sont transformées en B3 et C3 :

Dans certains cas, il est important qu’une formule fasse référence aux cellules de façon absolue. Si cette formule est déplacée dans une autre cellule, elle restera inchangée. Selon l’effet recherché, il suffit d’insérer un $ devant les noms des colonnes et/ou des lignes dans la formule.

Raisonnons sur la formule relative de l’exemple précédent :

=B2*(1+C2)

Pour faire référence de façon absolue aux cellules dans la formule, vous utiliserez cette syntaxe :

=$B$2*(1+$C$2)

Ici, les colonnes B et C et la ligne 2 sont accédées de façon absolue.

Remarque

Lorsque vous êtes en train de définir une formule, vous pouvez appuyer sur la touche de fonction F4. Les signes $ seront automatiquement ajoutés à la référence en cours de frappe. Supposons que vous ayez saisi B2 dans la formule. Appuyez sur F4, la référence devient $B$2. Appuyez à nouveau sur F4. La référence devient B$2. Appuyez une troisième fois sur F4. La référence devient $B2. Appuyez une quatrième et dernière fois sur F4. La référence devient B2.

Avec un peu d’habitude, vous trouverez certainement la touche F4 très pratique.

Comme vous le voyez, le résultat du calcul est le même que précédemment :

Par contre, si vous copiez-collez la formule de la cellule D2 dans la cellule D3, elle ne s’adapte pas à la ligne 3. Le résultat est donc bien différent :

Pour simplifier les formules, vous pouvez nommer les lignes et/ou les colonnes d’une feuille de calcul. Dans cet exemple, nous allons donner un nom aux colonnes B et C qui représentent respectivement le prix HT et la TVA.

Commençons par la colonne B. Cliquez sur l’en-tête de la colonne B (1) et entrez son nom dans la Zone Nom (2) :

Faites la même opération pour la colonne C :

Pour calculer le prix TTC dans la cellule D2, cliquez sur la cellule D2 et entrez cette formule :

=prixht*(1+tva)

Le calcul de la cellule D2 peut être étendu à la cellule D3 en utilisant la poignée de recopie :

Comme il faut bien commencer par le commencement, nous allons raisonner sur un exemple très simple afin de montrer comment insérer des formules dans une feuille de calcul (rassurez-vous, nous irons beaucoup, beaucoup plus loin dans la suite).

Dans cet exemple, on connait le prix HT de deux objets et le taux de TVA. Nous allons utiliser des formules pour calculer le prix TTC de ces objets :

Cliquez sur la cellule D2 et tapez =B2*(1+C2). Le signe = indique que la suite est une formule, et les cellules qui entrent dans le calcul sont identifiées par leur numéro de colonne et leur numéro de ligne accolés. Validez la saisie en cliquant sur l’icône Entrer, à gauche de la barre de formule. Le contenu de la cellule est remplacé par le résultat de la formule et la barre de formule affiche (on pouvait s’y attendre) la formule que vous venez de saisir :

Remarques

Pour saisir plus rapidement et sans risque d’erreur de frappe les références aux cellules dans une formule, vous pouvez cliquer sur les cellules concernées au lieu d’entrer leur colonne et leur ligne. Par exemple, plutôt que d’écrire B2 dans une formule, vous pouvez simplement cliquer sur la cellule B2.

Bien entendu, vous auriez pu appuyer sur la touche Entrée ou Tabulation pour valider la formule, mais dans ce cas, la cellule active aurait changé, et vous auriez dû cliquer sur la cellule D2 pour voir que la cellule et la barre de formule étaient différentes.

En utilisant la même technique, vous allez calculer le prix TTC dans la cellule D3. La formule à utiliser est =B3*(1+C3). Voici le résultat :

Pour terminer, vous allez limiter les résultats dans les cellules D2 et D3 à deux chiffres après la virgule. Sélectionnez les cellules D2 et D3, puis cliquez sur l’icône Réduire les décimales dans le groupe Nombre, sous l’onglet Accueil du ruban :

Un peu plus loin dans cet ouvrage, vous verrez comment faciliter la saisie des formules. Le but de cette section était de vous montrer comment saisir une formule simple dans une cellule.