Interdire les doublons

Comment interdire la saisie de doublon

Il est parfois utile, lorsque l'on saisie des enregistrements dans une table de données, de mettre des contrôles qui empêche qu'une valeur soit saisie en double. 

Prenons l'exemple suivant : 

Vous l'aurez compris, dans l'exemple ci-dessus, nous allons faire en sorte que si nous saisissons un numéro de matricule qui existe déjà dans notre tableau, un message d'alerte nous en informera.

Pour réaliser cela, nous allons utiliser l'outil de Validation des données et saisir la formule comme suit : 

La fonction utilisée est : NB.SI() qui compte le nombre de valeur non vide dans une plage de cellule.  

Et saisir le message d'alerte d'erreur comme suit : 

Voilà, désormais il ne sera plus possible de saisir deux fois le même numéro de matricule.

La fonction DECALER() - Suite

Utiliser la fonction Decaler() pour additionner des valeurs 

Je vous ai présenté il y a quelques semaines, l'utilisation de la fonction Decaler() en vous promettant de vous donner des exemples d'utilisation de cette fonction. 

Et bien voici une autre possibilité d'utiliser cette fonction. 

Prenons l'exemple ci-dessous : 

L'objectif est de réaliser le total cumulé en fonction de la liste déroulante située en cellule P3 représentant les numéros des mois de l'année. 

Tout d'abord, il faut créer une liste déroulante en cellule P3 mentionnant les 12 mois de l'année comme suit : 

Ensuite, taper la formule suivante en cellule N2 : =SOMME(DECALER(B2;;;;$P$3))

Copier cette formule jusqu'en cellule N6

Voilà, le cumul des ventes est réalisé en fonction du numéro du mois afficher en cellule P3.

La fonction REPT()

Réaliser une représentation graphique avec une formule

Prenons l'exemple ci-dessous : 

Si nous désirons réaliser cette représentation graphique, il y a plusieurs façon de réaliser cette dernière :

• Soit par une Mise en forme conditionnelle sous format de Barre de données 
• Soit par l'utilisation de Graphiques Sparkeline

Mais il y a une troisième possibilité, c'est l'utilisation de la fonction REPT().

Cette fonction permet de répéter un texte un certain nombre de fois. Sa syntaxe est la suivante :

=REPT(texte;no_fois) 

• texte  :  représente le texte à répéter.
• no_fois : représente un nombre positif indiquant le nombre de répétitions de la chaîne de texte.

Dans notre exemple, la fonction à saisir en cellule D26 est :  = Rept("|";C26/2000)

Choisir la Police Roman, taille 10 et gras

Copier la formule jusqu'en D33.

Ci-dessous d'autres utilisations de la fonction Rept() : 

• Graphique de type Gantt :

• Représentation sous format barre de progression : 

Liste déroulante

Comment changer la valeur d'une liste déroulante

L'objectif sera de changer la valeur d'une liste déroulante juste en tapant la valeur 1 ou 2.

Prenons l'exemple ci-dessous : 

Si nous tapons la valeur 1 en cellule C2, la liste déroulante située en B2 affichera les jours de la semaine. Si nous tapons la valeur 2 en cellule C2, dans ce cas, la liste déroulante affichera les mois de l'année.

Tout d'abord nous allons nommer les zones : 

• Sélectionner E2:E8 : Dans la barre des noms écrire Jours et valider
• Sélectionner F2:F13 : Dans la barre des noms écrire Mois et valider

Puis :

• Ecrire par exemple en C2 : 1
• Sélectionner B2, dans le ruban, Onglet Données/Groupe Outils de données/Validations de données, remplir comme ci-dessous:

Et voilà, vous pouvez désormais afficher dans la liste de données des valeurs différentes en fonction de la saisie d'une valeur 1 ou 2. 

Gérer les erreurs avec la fonction SIERREUR

Comment afficher un message au lieu d'une erreur comme résultat d'une formule

Dans vos rapports Excel, bien souvent lorsqu’il s’agit juste d’un manque de données, afficher l’erreur n’est pas très intéressante. L’astuce est d’afficher un texte comme par exemple « Données absentes »

Au lieu d’avoir l’erreur #DIV/0 affiché, il serait plus pertinent d’avoir « Données absentes ». Pour le faire on pourra utiliser certaines fonctions :

• La fonction SIERREUR :

Se positionner en D11 (Taux de Marge), au lieu d’écrire  =(B11-C11)/B11), on pourra écrire :

=SIERREUR((B11-C11)/B11) ; « Données absentes »)

Cette formule pourra être incrémentée jusqu’en D15

• La fonction ESTERREUR() imbriquée avec la fonction SI

On écrira dans ce cas :

=SI(ESTERREUR((B11-C11)/B11) ;« Données absentes »;(B11-C11)/B11)

Voici le résultat :

La fonction EQUIV()

La fonction Equiv()

La fonction EQUIV() renvoie la position d’un élément dans une colonne ou une ligne. Ce qu’il faut noter est que cette fonction ne renvoie pas la valeur mais bel et bien la position de cette valeur d’un tableau (colonne ou ligne).

Cette fonction est utile lorsque l'on veut savoir la position d’un élément. Présentée individuellement, cette information peut paraître inintéressante, mais imbriqué dans d’autres fonctions, notamment la fonction Index() elle transforme toutes vos recherches.

La syntaxe de cette fonction :    EQUIV(valeur_cherchée, matrice_recherche, [type])

• Valeur_cherchée : c’est la valeur pour laquelle on cherche la position, elle peut être un nombre, un texte ou une valeur logique
• Matrice_recherche : c’est la ligne ou la colonne dans laquelle on doit faire la recherche
• Type : pour préciser la manière de rechercher. (Type peur avoir 3 valeurs différentes)

* 1 ou omis : pour spécifier à la fonction de rechercher la position de la valeur la plus élevée qui est inférieure ou égale à celle qu’on recherche. La colonne ou la ligne dans laquelle on fait la recherche doit être classée dans l’ordre croissant.
* 0 : pour spécifier à la fonction de rechercher la position de la première valeur exactement équivalente à celle que l’on recherche
* -1 : pour spécifier à la fonction de rechercher la position de la plus petite valeur qui est supérieure ou égale à celle qu’on recherche. La colonne ou la ligne dans laquelle on fait la recherche doit être classée dans l’ordre décroissant.

Prenons le tableau ci-dessous comme exemple avec les prix triés par ordre croissant.

L’objectif est d'afficher  : 
- Sur quelle ligne est positionner l'article "Chemise"; la réponse sera écrite en F5
- Savoir le nombre d’article dont le prix est inférieur à 30€. La réponse sera écrite en G5.

• Sélectionner la cellule F5
• Ecrire = EQUIV("Chemise";C2:C7;1)
• puis Valider et la réponse est 3

• Sélectionner la cellule G5
• Ecrire = EQUIV(30;D2:D7;1)
• puis Valider et la réponse est 4 

Le résultat est : 

La fonction INDEX()

La fonction Index()

Descriptif : La fonction Index() affiche une valeur contenue dans un tableau en fonction d'un numéro de ligne et de colonne. 

Syntaxe : INDEX(tableau;no_ligne;no_col) 

•  tableau correspond à la plage de cellule dans laquelle la recherche sera effectuée. 
•  no_lig correspond à la ligne du tableau dont une valeur doit être renvoyée. 
• no_col correspond à la colonne du tableau dont une valeur doit être renvoyée.

Note : Si l'argument no_lig est omis, l'argument no_col est obligatoire (et inversement). 

Prenons l'exemple ci-dessous pour illustrer la fonction :

En F5 nous souhaitons avoir le mois mars s'afficher 

En G5 afficher le nom du produit correspondant au mois de mars

En H5 le montant de la vente correspondant au mois de mars 

Se positionner en F5 

Taper :  =Index(B2:D14;4;1)

B2:D14 représente la plage de cellule correspondant à notre tableau

4  : correspond à la 4ème ligne de notre tableau B2:D14 ; la ligne ou se trouve le mois de mars

1 : correspond à la 1ère colonne ou se trouve les mois

Pour les autres cellules : 

Se positionner en G5

Taper : = index(B2:D14;4;2)

Se positionner en H5

Taper : = index(B2:D14;4;3)

Le résultat sera : 

Utiliser les formats personnalisés

Astuces concernant les formats personnalités

Il arrive parfois dans excel d'avoir besoin de personnaliser des valeurs en fonction de notre besoin. Je vais vous montrer quelques exemples de formats personnalisés. 

1. Masquer les nombres dans un tableau :

Il peut être intéressant de masquer l'affichage des nombres dans une plage de cellule et montrer uniquement les textes.

Prenons l'exemple du tableau de données ci-dessous :

Pour masquer les nombres et laisser afficher les textes, il suffit de : 

• sélectionner la plage de cellule B31:D34
• clic droit de la souris et choisir Format de cellule  
• dans l'onglet Nombre, choisir la catégorie Personnalisée
• dans le champ Type, écrire  ;;  
• puis valider

Nous avons le résultat suivant :

2. Additionner des heures au delà des 24H :

Prenons l'exemple du tableau ci-dessous dans lequel nous souhaitons réaliser en F46  la somme des horaires de la semaine.

Si nous saisissons en F46 la formule : =somme(F42:F45), le résultat obtenu sera 19:46:00. Ce n'est pas le résultat que nous désirons. Pour cela il faut indiquer à Excel que nous souhaitons additionner les heures au-delà des 24H.  

• se positionner sur la cellule F46
• clic droit de la souris et choisir Format de cellule 
• dans l'onglet Nombre, choisir la catégorie Heure
• dans le champ Type, sélectionner la valeur  37:30:55  
• puis valider

Nous obtenons désormais le résultat : 43:46:00

Comment réaliser des mises en forme dans un tableau

Faire varier la couleur, d’une cellule, d’une ligne ou d’une colonne selon la valeur d’une cellule

Je vais vous montrer comment réaliser différentes mises en forme afin d'afficher dans un tableau des valeurs que l'on souhaite mettre en importance.

1. Changer la couleur d'une cellule selon sa valeur :

Prenons l'exemple ci-dessous : 

Le but est de mettre toutes les cellules dont les ventes sont inférieures à 20.000 en rouge.

La solution est simple:

• Sélectionner la plage de cellule E3:E12
• Dans le ruban, Onglet Accueil/Groupe Style/Mise en forme conditionnelle/Règle de surbrillance/Inférieur à…
• Dans la boite de dialogue, choisir texte rouge et mettre la valeur 20.000, comme ci-dessous :

Et voici le résultat :

2. Changer la couleur d'une ligne par rapport à la valeur d'une cellule :

Maintenant on va plutôt changer la couleur de la ligne et non plus la cellule en question. Voici comment procéder:

• Sélectionner le tableau B3:E13
• Dans le ruban, Onglet Accueil/Groupe Style/Mise en forme conditionnelle/ Nouvelle règle
• Dans la boite de dialogue, choisir « utiliser une formule pour déterminer… »
• Dans l’espace formule écrire =$E3<20000 (faites attention au dollar !!!)

• Cliquer sur Format et choisir simplement couleur de la police Rouge.

Voici le résultat :

Le principe est le même pour la mise en forme d'une colonne.

3. Faire une mise en forme conditionnelle dynamique :

Ici il s’agit d’avoir une cellule totalement à l’extérieur du tableau qui fera varier les couleurs à l’intérieur du tableau. 

Le but sera par exemple de retrouver toutes les ventes faites par Pierre, Paul...

• On crée une cellule avec liste déroulante en G3 avec la liste des noms des vendeurs.
• Sélectionner le tableau B3:E12
• Dans le ruban, Onglet Accueil/Groupe style/Mise en forme conditionnelle/Nouvelle règle
• Dans la boite de dialogue, choisir « utiliser une formule… » et dans l’espace formule écrire =$C3=$G$3 (attention aux dollars une fois de plus !!!)
• Cliquer sur format et mettre le format de votre choix et puis Valider

 

Voici le résultat :

Enregistrer des données grâce à un formulaire

Comment saisir des données dans Excel en utilisant un formulaire

Pour ceux qui n’aime pas naviguer dans les cellules d’Excel et préfère les boite de dialogue, je vous présente ici une technique originale pour enregistrer les données dans un tableau, faire des recherche et bien sûr gagner du temps.

On utilisera un formulaire (ne vous inquiétez pas : même pas besoin de taper du code VBA !!!)

La 1ère étape est de mettre les entêtes de son tableau comme ci-dessous :

Lorsque c’est fait, il faut ajouter dans la barre d’accès rapide le bouton formulaire.

• Dans le ruban, Onglet Fichier/Option
• Dans la nouvelle boite de dialogue, cliquer sur « Barre d’outils Accès rapide », pour Excel 2007 c’est « Personnalisé »
• Dans le côté droit, choisir « commandes non présente sur le ruban », sélectionner formulaire et cliquer sur « ajouter » et valider.

Maintenant le bouton ajouter formulaire est présent dans la barre d’outils d’accès rapide.

• Sélectionner vos entêtes
• Cliquer sur le bouton formulaire, ensuite cliquer sur Ok sur la boite de message qui apparaît
• Votre formulaire est prêt pour enregistrer vos données, plus besoin de cellules juste ce formulaire.

Il n’y a plus qu’à saisir les données dans le formulaire afin de compléter le tableau.

Mettre en évidence des valeurs dans un tableau

Mettre en évidence toutes les valeurs supérieures à la moyenne

Lorsque l'on a un rapport à faire, il est parfois intéressant de pouvoir rapidement mettre en évidence les montants (des ventes par exemples) qui sont supérieures ou même inférieurs à la moyenne. 

Source des données :

Pour mettre en évidence les montants qui sont supérieurs à la moyenne, il suffit juste de : 

• Sélectionner la plage de cellule : C3:C10
• Dans le ruban, Onglet Accueil/Groupe Style/Mise en forme conditionnelle/Règles de valeurs plus ou moins élevées/Valeurs supérieures à la moyenne… 

• Choisissez le format de votre choix 

Voici le résultat :