Responsive image

Méthodes pour utiliser efficacement excel

Utiliser des noms

Excel offre la possibilité de nommer différents éléments. Par exemple vous pouvez nommer une cellule. Il faut procéder de la façon suivante :

1. Sélectionner la cellule à nommer:

Responsive image

2. Ensuite vous devez cliquer sur le bouton "Définir un nom" de l’onglet "Formule":

Responsive image

3. Indiquer le nom que vous souhaitez attribuer à la cellule "B2"

Responsive image

Une autre solution consste à taper directement le nom dans la zone nom. (liste déroulante à gauche de la barre formule)

Responsive image

L'utilisation des nom permet de'avoir des repère plus parlant qu'une référence de cellule tel que "$B$2".

Le nom "resultat" pourra ensuite avantageusement être utilisé dans le contexte suivant :

Responsive image

Cela équivaut à la formule suivante, avec l'avantage d'^tre beaucoup plus expicite.

Responsive image

Dans les 2 cas le résultat est le suivant :

Responsive image

Création automatique de zones noms

Excel permet de créer automatiquement des zones nom à partir des intitulés d'un tableau

Responsive image

Responsive image

Vous remarquez que la plage de cellules correspondant à la ligne hiver est nommé "Hiver"

Il en sera de même pourla plage de cellules de la colonne min qui sera nommé "min"

Responsive image

La somme des température minimum sera obtenu par la formule suivante : Responsive image

Le résultats suivant s'affiche :

Responsive image

Les intersections des noms

Il est possible de faire référence à l'intersection de 2 plages en utilisant l'opérateur intersection. L'opérateur intersection est en fait le caractère "espace".

Responsive image

Le résultat affiché est effectivement la valeur "12".

Responsive image

Les erreurs de cellules

Il est possible de faire référence à l'intersection de 2 plages en utilisant l'opérateur intersection. L'opérateur intersection est en fait le caractère "espace".

  • #DIV/0!: Excel affiche l’erreur #DIV/0! lorsqu’un nombre est divisé par zéro (0) ou par une cellule qui ne contient aucune valeur.
  • #N/A : Cette erreur indique qu’une valeur n’est pas disponible pour une fonction ou une formule.
  • #NOM? : Cette erreur survient lorsque Microsoft Office Excel ne reconnaît pas le texte dans une formule.
  • #NUL! : Cette erreur survient lorsque vous spécifiez une intersection de deux zones qui, en réalité, ne se coupent pas. L’opérateur d’intersection est un caractère d’espacement entre des références.
  • #NOMBRE!: Cette erreur indique qu’une formule ou une fonction contient des valeurs numériques non valides.
  • #REF! :Cette erreur survient lorsqu’une référence de cellule (référence de cellule : ensemble de coordonnées correspondant à l'emplacement d'une cellule dans une feuille de calcul. Par exemple, la référence de la cellule qui se trouve à l'intersection de la colonne B et de la ligne 3 est B3.) n’est pas valide.
  • #VALEUR! : Cette erreur survient lorsqu'un type d'argument ou d'opérande inapproprié est utilisé.

Fonction ordinaires / fonctions matricielles

Il faut faire la distinction entre les fonctions ordinaires et les fonctions matricielles.

Les fonctions ordinaires ne renvoient qu'un résultat. Pour l'utiliser on sélectionne une cellule qui recevra le résultat de la fonction.
Pour valider il appuyer sur "entrée"

Les fonctions matricielles renvoient une série de nombre. Pour l'utiliser on sélectionne plusieurs cellules qui recevront le résultat de la fonction.
Pour valider il appuyer sur "Ctrl+Maj+entrée"

Responsive image

Les formules nommées

Excel offre la possibilité de nommer des cellules mais aussi de nommer des formules

Pour ce faire, utiliser la démarche suivante :

  • Cliquer sur le bouton "Définir un nom" de l’onglet "Formule":

Responsive image

 

Exemple 1 : Nommer une fonction "Puissance"

Le but de cet exemple est de créer et nommer une fonction "Puissance" qui effectue son calcul à partir de 2 cellule préalablement choisies.

Etape 1 :

  • indiquer le nom de la formule : "Puissance"

Responsive image

 

  • Choisisser les références en cliquant dans un premier temps sur la cellule ou est stocké le nombre

Responsive image

  • Ensuite taper 2 fois la touche accent circonflexe "^" puis sur la touche retour arrière.
  • Choisisser dans un second temps la cellule qui recevra la valeur de l'exposant.

 

Responsive image

 

Etape 2 :

  • Dans la cellule D2 inscriver la formule "=puissance"

Responsive image

  • Dans les cellule B2 et C2 inscrivez les valeurs que vous souhaitez, le résultat de la puissance sera calculer automatiquement

Responsive image

 

Exemple 2 : Nommer une fonction "Nomfichier"

Le but de cet exemple est de créer une fonction qui retourne le nom du fichier.

  • Dans la fenètre "Nouveau nom" indiquer le nom de la formule : "Nomfichier"
  • Dans la zone "fait référence à" indiquer la formule =CELLULE("filename";Feuil2!$A$1)

 

Responsive image

 

  • Dans la cellule B2 inscriver la formule "=Nomfichier", poiur obtenir le résuultat ci-dessous.

Responsive image

Les fonctions matricielles - Approche par l'exemple

Les résultats d'une formule peuvent être contenu dans plus d'une cellule. C'est le cas pour les cellules matricielles.

Exemple n°1 : Multiplication de colonnes entre elles

Imaginons que nous souhaitions multiplier les valeurs d'une colonne de 5 cellules à une autre colonne de 5 cellules tout en souhaitant que les résultats apparaîssent dans une 3ème colonnes. On pourra alors procéder ainsi :

Etape 1 : Sélectionner les cellules de la colonne résultat

Responsive image

Etape 2 :

  • Taper le signe "="
  • Sélectionner les cellules de la colonne 1

Responsive image

Etape 2 :

  • Taper le signe "*"
  • Sélectionner les cellules de la colonne 2

Responsive image

Etape 3 :

  • Appuyer sur les touches :"Ctrl+Maj+entrée"

Responsive image

Les résultats s'affichent dans les 5 cellules résultats, vous remarquerez aussi les accolades qui sont apparues de chaque côté de la formule. ( Excel signale de cette manière qu'il s'agit d'une formule maticielle)

 

Exemple n°2 : Comptabilisation du nombre de caractères

Imaginons que nous souhaitions compter le nombre de caractères présent dans un groupe de cellules contenant du texte.

Responsive image

Résultat :

Responsive image

L'avantage des formule matricielle prend ici toute sa place. En effet si ce travail avait été réalisé par des formules standards il aurait été nécessaire d'écrire la formule NBCAR() autant de fois q'il y a de cellules sistinctes. Puis il aurait fallu en faire la somme.

 

Exemple n°3 : Recherche d'une chaîne de caractères

Dans cet exemple nous allons voir comment crééer un outil pour recherchercher un pays parmis une liste de pays.

Etape n° 1 :

  • Nommer le groupe de cellules contenant la liste de pays par "ListePays"

Responsive image

Etape n° 2 :

  • Nommer la cellule contenant le pays que l'utilisateur va souhaiter rechercher "PaysRecherche"

Responsive image

Etape n° 3 :

  • Inscrire la formule suivante dans la cellule de réponse (C15)

"=SI(OU(PaysRecherche=ListePays);"Lepays est dans la liste";"Le pays n'est pas dans la liste")"

  • N'oublier pas de terminer la saisie en appuyant sur les touches :"Ctrl+Maj+entrée"

Responsive image

Etape n° 4 :

  • Utiliser l'outil créé en indiquant un nom de pays dans la cellule bleu

Responsive image

 

Exemple n°4 : Recherche d'une chaîne de caractères

Dans cet exemple, nous allons voir comment interroger un tableau à l'aide de formules matricielles à travers l'exemple d'un tableau représenntant les ventes de parapluies.

Responsive image

  • Question n° 1 : Combien de parapluies ont été vendus au mois de janvier ?

Pour répondre à cette question, par l'approche classique nous sélectionnerions les quantités correspondantes au mois de janvier et nous en ferions la sommes. Cependant cette approche atteint ses limite lorsque que le tableau devient volumineux et que les tris ne sont plus possibles.

Responsive image

La technique consiset donc à faire la somme des quantités de la colonne D mais uniquement pour les cellules pour lesquelles le mois associé ets le mois de janvier.

Responsive image

 

  • Question n° 2 : Combien de parapluies ont été vendus au mois de janvier en afrique?

Responsive image

  • Question n° 3 : Combien de parapluies ont été vendus au mois de février autre part qu'en asie ?

Responsive image

 

  • Question n° 4: Quel est le nombre de continents dans lesquels des ventes ont été réalisées au mois de mars ?

Responsive image

 

Responsive image

 

Question n° 5 : Quel est le nombre de ventes pour lesquelles le volume a été supérieur à 200 000 unités ?

 

Responsive image

 

Responsive image

 

Question n° 6 : Quel est la sommes des ventes qui ont un total supérieur à 100 000 unités ?

Responsive image

 

Responsive image

On voit à travers ces exemples que l'on peu interroger un tableau de la même façon que l'on pourrait le faire avec une base de données.

ASTUCE : Faire des calculs de dates avec excel

Votre besoin

Vous souhaitez créer faire des calculs de date avec Excel, voici la solution !

Télécharger le fichier exemple

Lien : datesethoraires.xlsx

Connaissance du fonctionnement d'Excel

Travailler avec des dates

Dans Excel, il est possible de formater les cellules au format date. De nombreux formats sont disponibles dont les types 1 et types 2 de l'exemple ci-dessous.

 

Dans la dernière ligne vous voyez la même date que celle présente dans cellules au-dessus, mais cette fois dans un format nombre. Il s'agit du nombre de jour écoulé depuis une date initiale.

Excel a choisi par défaut la date initiale au 1 janvier 1900.

 

Vous pouvez voir les correspondances ci-dessous et ainsi comprendre le mécanisme.

Responsive image

Travailler avec des heures

Responsive image

Le nombre, en dernière ligne, correspond à la fraction d'une journée de 24H. Ainsi 12:00 correspond bien à la moitié d'une journée de 24h.

 

La méthode pour appliquer le format date

1) Sélectionner les cellules

Responsive image

2) Modifier le format de cellule

Dans l'onglet "Accueil" et la zone "Nombre" une liste déroulante vous permet de choisir le type de format de cellule que vous souhaitez appliquer.

Responsive image

 

Si vous avez des besoins précis concernant la mise en forme de la date, vous pouvez selectionner " Autre format numérique" .

Responsive image

Une nouvelle boîte de dialogue s'ouvre.

Dans la zone catégorie, vous pouvez choisir "Date" puis dans "Type :" le type de date souhaité.

Responsive image

 

Calculer le nombre de jours entre 2 dates

1) Inscrire les dates dans 2 cellules différentes
2) Faire la soustraction

Dans une troisième cellule taper "=" suivi des adresses de la cellule de la première date, puis le signe "-" puis l'adresses de la cellule de la seconde date. Vous pouvez suivre l'exemple indiqué ci-dessous.

Responsive image

Chaque ligne comporte les mêmes dates dans des formats différents, vous remarquerez que le résultat du calcul est le même quelque soit le format.

Responsive image

Il y a donc 11818 jours d'écart entre les 2 dates.

Calculer une durée

La logique est identique au calcul effectué sur les dates.

Le résultat en dernière colonne correspond à une durée :

Responsive image

 

Créer une liste à choix avec excel

Votre besoin

Vous souhaitez créer une liste à choix pour saisir plus rapidement vos valeurs nominales, voici la solution !

Télécharger le fichier exemple

Lien : listeachoix.xlsx

Préparer la liste

1) Créer 2 onglets
  • 1 onglet dédié au tableau que vous souhaitez remplir
  • 1 onglet avec la liste à choix

Vous devez obtenir le résultat ci-dessous :

Responsive image

2) Créer la liste dans l'onglet "Liste"

Responsive image

3) Sélectionner la zone de liste et lui donner un nom

Responsive image

Taper dans la zone appelée "zone nom" le nom de la liste que vous souhaitez ; ici "listesituation" et terminer la saisie par la touche entrer.

4) Créer le tableau de saisie dans l'onglet ""Tableau"

Responsive image

Sélectionnez la première ligne

Responsive image

5) Sélectionner l'onglet "Donnée"

Responsive image

6) Sélectionner " Validation de donnée"

Responsive image

7) La boîte de dialogue s'affiche

Responsive image

8) Cliquer sur la liste de "Autoriser" et sélectionner "Liste"

Responsive image

 

9) Choisir votre liste

Responsive image

Dans la zone source taper le signe "=" suivi du nom que vous aviez donner à votre liste, dans notre exemple : "listesituation"

10) Tester la liste à choix en utilisant le triangle

Responsive image

11) Etendre la liste à choix en utilisant la poignée de recopie

Sélectionner et supprimer les valeurs inutiles avec la touche "supr"

Responsive image

 

12) Votre tableau est prêt

Responsive image

 

Fonction NB(),NBVAL(),NB.VIDE, NB.SI()

La fonction NB() renvoie le nombre de cellules qui contiennent des nombres (exclusivement.

Fonction NB(),NBVAL(),NB.VIDE, NB.SI()

La fonction NB() renvoie le nombre de cellules qui contiennent des nombres (exclusivement.

Restons en contact !






Ce site doit aussi progresser pour combler au mieux vos attentes, alors je suis à votre écoute si vous souhaitez me communiquer vos commentaires à l'adresse : commentprogresser@gmail.com.