Responsive image

Et si on...

progressait avec excel...

Découvrez les fontions de base d'excel

Selectionner les cellules

Il existe 3 façons de sélectionner des cellules dans Excel :

  • Utilisation du clic de souris seul. Il permet de sélectionner une cellule.
  • Utilisation du clic de souris et de la touche Ctrl en même temps. Cette manipulation permet de sélectionner plusieurs cellules en même temps.
  • Utilisation du clic de souris et de la touche Maj . En cliquant sur une cellule en début de plage en haut à gauche et sur une seconde cellule en fin de plage en bas à droite, cela permet de sélectionner l'ensemble des cellules entre ces 2 positions.

Saisir la même valeur dans une plage de cellules

Pour saisir des données dans une seule cellule nous procédons de la façon suivante :

1. Selectionnez la cellule

Responsive image

2. Saisissez des caractères "xxx"

3. Appuyez sur la touche " entrée "

Responsive image

Astuce : Une question régulièrement : comment revenir à la ligne dans une cellule ?
Pour passer à la ligne au sein d'une cellule il faut presser sur les boutons "Alt" +" entrée "

Copier le contenu d'une cellule active dans toutes les cellules consécutives

Pour copier le contenu d'une cellule active dans toutes les cellules consécutives procéder de la façon suivante :

1. Sélectionner de la cellule

2. Déplacer de la poignée de recopie vers le bas. ( La poignée est le petit carré présent dans le coin inférieur droit des sélections.) Le bouton gauche de la souris doit être maintenu enfoncé.

3. Relacher le bouton lorsque vous avez le nombre de cellule souhaité

 

  • Pour incrémenter le contenu d'une cellule active dans toutes les cellules consécutives :

1. Sélectionner de la cellule

2. Déplacer de la poignée de recopie vers le bas en maintenant le bouton "Ctrl" enfoncé et le bouton gaucche de la souris.

3. Relacher le bouton lorsque vous avez le nombre de cellule souhaité

Responsive image

Formater les données

  • Fraction

Pour saisir une fraction il faut taper "0" suivi d'un espace puis de la fraction en utilisant la barre oblique.

  • Date courante

Pours saisir auomatiquement la date courante il faut appuyer sur les boutons "Ctrl"+";" et appuyer sur "Ctrl"+":" si l'on veut saisir l'heure actuelle.

Verrouiller le type de données qui peut être saisi

  • Verrouillage du type de données saisies

Pour paramétrer une cellule afin qu'elle ne permette de contenir qu'un type donnée il faut avoir recours au menu "donnée" puis "Validation des données".

Responsive image

 

Dans l'exemple ci-dessous, la cellule ne peut contenir que des nombres entier entre 1 et 5.

Responsive image

 

Si l'utilisateur rentre la valeur "6" le message d'erreur suivant s'affiche :

Responsive image

 

Ce message d'erreur est paramétrable dans l'onglet "alerte d'erreur".

Protéger les cellules

Excel propose un système de protection des cellules. Il est ainsi possible d'empêcher les utilisateurs de modifier une formule...

Note : Par défaut l'ensemble des cellules du classeur sont verrouillées. Cependant ce réglage n'a pas d'effet tant que la feuille n'est pas protégée.

La démarche consiste donc dans un premier temps à sélectionner les cellules sur lesquelles on permettra à l'utilisateur d'agir (déverrouillage des cellules) et ensuite de de protéger la feuille afin d'empêcher les modifications eventuelles des autres cellules.

1. Sélectionner les cellules qui doivent être déverrouillées

2.Cliquer sur le bouton "Format de cellule" de l’onglet "Acceuil":

3. Dans l'onglet "protection" décocher la case verrouilée.

4. Ensuite vous devez, protéger la feuille en cliquant sur le bouton "Protéger la feuille" de l’onglet "Révision":

Verrouiller la modification de la struture du classeur

Il possible d'empêcher la modification de la structure du classeur en cliquant sur le bouton "Protéger le classeur" de l’onglet "Révision":

Responsive image

Protéger un classeur avec un mot de passe

Il possible d'empêcherla lecture et/ou la modification d'un classeur avec avec un mote de passe.

1. Cliquer sur "Enregistrer sous" puis sur le bouton "outils"

<img src="excel/excelprotection9.jpg" width="400" height="212" />

2. Ajouter un mot de passe pour la protection désirée

Responsive image

 

Personnaliser l'affichage

Afin de rendre plus visible le contenu des feuille de calculs, Excel permet d'afficher la page en plein écran. Cette option se trouve dans le menu affichage et plein écran.

Pour revenir à l'affichage normal il suffit d'appuyer sur "Echap"

Quoique peu utilisé ce mode permet un confort de saisi et de lecture accru.

Utiliser intelligement les référence de cellules

Si l'on veut utiliser les formule de calcule il est important de bien comprendre comment sont référencer les cellules. Il existe 4 type de références

  • Référence relative
  • Référence absolue
  • ligne absolue
  • colonne absolue

Nous allons détaillé chacun de ces types de référence. Je vais volontairement expliquer dans le détail le fonctionnement du référencement des cellules car cette base est indispensable pour utiliser efficacement excel.

  • Référence relative : La référence de la cellule est relative. Si l'on fait un copier/coller d'une formule contenant cette référence ( Par exemple A1 ) alors la référence de la formule s'ajustera en fonction du nouvel emplacement.

EXEMPLE

1. Entrer dans la cellule A1 le texte comme indiquer ci-dessous :

Responsive image

2. Utiliser dans la cellule A2 la formule MAJUSULE qui a pour fonction de transformer le texte en minuscule en texte en majuscule.

Responsive image

La référence utilisé dans la formule majuscule doit être "A1"

Ensuite appuyer sur entrée Le résultat obtenu doit être le suivant :.

Responsive image

3. Inscriver dans la cellule A2 le texte suivant :

Responsive image

4.Sélectionner et copier la cellule B1

Responsive image

4.Coller la formule dans la cellule B2

Responsive image

Le résultat obtenu est le suivant :

Responsive image

Quelle remarques pouvons nous faire ?

En faisant un copier/coller nous n'obtenons pas le meme contenu dans la cellule B1 que celui de la cellule B2. Pourquoi car seul la formule a été copier tel quelle. La référence, elle , a changé.

Dans la cellule copié la référence était A1. Dans la cellule collé la référence est A2.

Responsive image

  • Référence absolue : La référence est entièrement absolue. Si l'on fait un copier/coller d'une formule contenant cette référence ( Par exemple $A$1 ) alors la référence de la formulene changera pas.Le symbole dollars "$" permet de transformer les références relative en référence absolue.

Responsive image

Ainsi si l'on répète le copier coller comme dans lexemple ci-dessus alors le résultat sera le suivant :

Responsive image

On remarque que la référence est la même dans la cellule B1 et B2. Dans les deux cas la formule "=MAJUSCULE($A$1)" utilise la référence "$A$1"

Responsive image

Astuce : Lorsque vous avez saisi le code suivant : "=MAJUSCULE(A1), en appuyant sur la touche F4 vous obtiendrez automatiquement le résultat suivant "=MAJUSCULE($A$1)"

  • absolue
  • ligne absolue : Dans ce cas la référence est partielment absolue, seule la ligne est figée. La colonne reste relative au nouvel emplacement. Une référence avec ligne absolue s'écrit de la façon suivante "A$1"
  • colonne absolue : Dans ce cas la référence est partielment absolue, seule la colonne est figée. La ligne reste relative au nouvel emplacement. Une référence avec colonne absolue s'écrit de la façon suivante "$A1"

Note : Vous remarquerez que le signe dollars se place juste en avant ce qui doit resté figé. Avant le"1" si l'on veut que la référence reste figé sur la ligne "1". Juste avant le "A" si l'on veut que la référence reste figée sur la colonne "A". Et devant la référence de ligne et de colonne si l'on veut qu'il y est une référence absolue sur une cellule.

Astuce :

  • En appuyant 1 fois sur la touche F4 vous obtiendrez une référence absolue
  • En appuyant 2 fois sur la touche F4 vous obtiendrez une ligne absolue
  • En appuyant 3 fois sur la touche F4 vous obtiendrez une colonne absolue

Utiliser des noms

Excel offre la pôssibilité de nommer différents éléments. Par exemple vous pouvez nommer une cellule. Il 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.