Formules

Vérifier les données saisies, ou comment éviter de saisir des données erronées dans vos cellules

Pour limiter les risques d’erreur lors de l’ajout de données dans une feuille, Excel propose d’appliquer au contenu d’une cellule des critères de validation.

 Par exemple, il est possible d’autoriser uniquement des nombres entiers compris entre deux valeurs, ou encore uniquement une date valide…

  • Sélectionner une cellule ou une plage de cellules
  • Utiliser l’outil “Validation” du Ruban “Données”

Imaginons que vous vouliez restreindre le contenu à une date comprise entre le 1er janvier 2017 et le 31 décembre 2018. Il suffit de sélectionner Date dans la liste déroulante, puis d’entrer les dates de début de fin. Validez en cliquant sur OK.

Pour n’autoriser que que quelques mots ou nombres utilisez “Liste” et saisissez votre liste de mots ou nombres autorisés (ou faites références à un plage de cellules de votre classeur contenant les mots ou nombres autorisés).

 Les onglets “Message de saisie” et “Alerte d’erreur” proposent également l’affichage d’un message à l’instant où la cellule est cliquée (pour indiquer à l’utilisateur quel type d’informations inscrire) et quand les données tapées ne sont pas valides (pour lui signifier l’erreur de saisie).

Trouver des doublons dans une liste de valeurs

Voici une formule, qui combinée a une condition et pour enjoliver a l’élimination des erreurs, nous permet de trouver des doublons entre deux colonnes de données de toutes sortes.

Exemple : dans la colonne A une liste de valeurs (chiffre ou texte) ; dans la colonne B une autre liste de valeurs. On va construire en colonne C une formule qui détectera les valeurs de la colonne B qui sont également présentes dans la colonne A.

Voici sa syntaxe :

=SI(ESTERREUR(RECHERCHEV(B1;$A$1:$A$26;1;FAUX));””;RECHERCHEV(B1;$A$1:$A$26;1;FAUX))

La formule est à construire en C1 et à recopier autant de fois que nécessaire (jusqu’au bas de la liste). Ici la formule recherche dans la colonne de gauche “A” la valeur d’une cellule de la colonne B qui pourrait être présente et l’inscrit dans la troisième colonne (là ou se trouve la formule).

Décortication de la formule : 

Recherche la valeur de “B1”;dans la plage de cellules de la colonne de gauche “A1 à A26” (a noter les $ présents afin de conserver la plage de sélection fixe lors de la recopie de cette formule dans les autres cellules de la colonne “C”); si la valeur de la cellule n’est pas trouvée, n’inscrit pas un code d’erreur mais laisse la cellule de la  colonne C (là ou se trouve la formule) blanche (sans valeur); si la valeur est trouvée (doublon) alors indique la valeur du doublon

Astérix et Obélix au pays d'Excel (des chiffres romains dans vos tableaux)

Pour obtenir l’affichage d’un nombre en chiffre romains, il suffit d’utiliser la formule =ROMAIN()

Exemple

vous tapez le nombre 1963 dans la cellule A1,

en A2 vous écrivez : =ROMAIN(A1)

vous obtenez : MXMLXIII

Des sous totaux dans vos tableaux

La fonction =SOUSTOTAL(no_fonction;réf1;réf2;…) vous permettra d’effectuer tout un tas d’opérations portant sur une série de données. Cette fonction exige 2 arguments :

no_fonction représente le nombre compris entre 1 et 11 indiquant quelle fonction utiliser pour calculer les sous-totaux d’une liste (voir ci-dessous).

réf1réf2, représentent les 1 à 29 plages ou références pour lesquelles vous voulez un sous-total.

La liste des opérations disponible dans le 1er argument est la suivante :

  • 1 MOYENNE
  • 2 NB
  • 3 NBVAL
  • 4 MAX
  • 5 MIN
  • 6 PRODUIT
  • 7 ECARTYPE
  • 8 ECARTYPEP
  • 9 SOMME
  • 10 VAR
  • 11 VAR.P

Par exemple, =SOUSTOTAL(9;A1:A18) fera la somme des cellules A1 à A18. Chose interessante, si ces cellules contiennent elles-même un sous total, il ne sera pas pris en compte (voir ci-dessous).

Remarques

Si d’autres sous-totaux se trouvent à l’intérieur de la plage définie par les arguments réf1, réf2 … (ou sous-totaux imbriqués), ces sous-totaux imbriqués ne sont pas pris en compte afin d’éviter tout comptage en double.

La fonction SOUS.TOTAL ne prend pas en compte les lignes masquées suite à un filtrage. Le sous-total ne porte que sur les données visibles résultant du filtrage d’une liste.

Si l’une des références est une référence 3D, la fonction SOUS.TOTAL renvoie la valeur d’erreur #VALEUR!

Arrondir un montant aux 5 centimes les plus proches

Etant donné un nombre dans la cellule A1 ,Pour lequel on souhaite obtenir un arrondi aux 5 centimes les plus proches.

la formule est la suivante : =ARRONDI(A1*2;1)/2

Arrondir un montant aux 50 centimes les plus proches

Etant donné un nombre dans la cellule A1 ,Pour lequel on souhaite obtenir un arrondi aux 50 centimes les plus proches.

la formule est la suivante : =ARRONDI(A1*2;0)/2

Masquer les messages d'erreur affichés dans les cellules

Il arrive que, en fontion des données de votre feuille de calcul, vos formules affichent des messages d’erreur.

Cela peut se produire notamment lorsque les formules sont préparées à l’avance et que les cellules utilisées dans une formule sont vides. Par exemple dans la cellule A3 on tape la formule : =A1/A2. Cette formule affichera #DIV/0! si A2 est vide

Pour masquer ce message d’erreur, on peut utiliser la mise en forme conditionnelle d’Excel.

  1. Sélectionner la cellule contenant la formule fournissant éventuellement un message d’erreur, (dans notre exemple la cellule A3),
  2. Utiliser l’outil “Mise en forme conditionnelle” du Ruban “Accueil”
  3. Chosir “Utiliser une formule pour déterminer pour quelles cellules…”
  4. Dans le champ prévu écrivez : =ESTERREUR(A3)
  5. Cliquez sur le bouton “Format” et dans l’onglet “Police” on choisissez le blanc comme couleur de police,
  6. Validez en cliquant sur OK pour les 2 boîtes de dialogue.

Si A3 est correct, le résultat du calcul apparaît, sinon la cellule semble vide.

Arrondir un nombre au quart de point supérieur

Si le nombre se trouve dans la cellule A1, la formule sera la suivante : =PLAFOND(A1;0.25)

NB : Attention, le séparateur décimal peut être soit la virgule, soit le point. A définir dans les paramétrages de Windows.

Changer une référence d'absolue vers relative et inversement

La fonction audit permet de voir toutes les cellules qui entrent directement ou indirectement dans un calcul ..

  • Vous voulez repérer les cellules prises en compte dans un calcul ? : Utilisez l’outil du Ruban “Formules”,  “Repérer les antécédents”.
  •  Vous voulez savoir si une cellule est utilisée dans un calcul ? :  Utilisez l’outil du Ruban “Formules”, Repérer les dépendants“.
  •  Vous voulez supprimez les flêches générées par le menu “Outils-Audit” ? :  Utilisez l’outil du Ruban “Formules”, “Supprimez les flèches.”

Rechercher le nombre de valeurs différentes dans une plage de cellules

Rechercher le nombre de valeurs différentes dans une plage :

Formule matricielle {=SOMME(1/NB.SI(Maplage;Maplage))}

Saisir : =SOMME(1/NB.SI(Maplage;Maplage)) et valider avec touches Ctrl-Maj-Entrée

Comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule

L’indirection ou comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule.

A2 contient le nom d’un classeur (“Exefac.xlsx” par exemple).

A3 contient la formule: =INDIRECT(A2&”B6″) 

Donc A3 aura la valeur contenu dans la cellule B6 de Exefac.xlsx (qui doit être ouvert, bien entendu)

Visualiser, et imprimer, toutes les formules de calcul de votre feuille

Insérer l’heure : CTRL : (Touche “Contrôle” + “double point” )

Insérer la date du jour : CTRL ; (Touche “Contrôle” + ” point virgule” )

La fonction CHOISIR() à la place de la fonction SI()

Exemple avec des taux de remise variables en fonction d’un code (1,2 ou 3) affichant des remises de 5%, 10% ou 15 % :

La cellule A1 contient le code d’escompte (1, 2 ou 3) que vous saisissez au clavier.

La formule en B1 permet d’afficher le taux de remise en fonction du code saisi en cellule A1.

La syntaxe est : =CHOISIR(A1;5%;10%;15%)

où Si le code de remise est “1” la valeur de B1 sera 5%, si le code de remise est “2” la valeur de B1 sera 10% et si le code d’escompte est “3”, la valeur de B1 sera 15%.

La formule peut contenir autant de variables que vous le désirez.