Excel 2000 Cours En Ligne


Télécharger Excel 2000 Cours En Ligne

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Excel 2000 document de Cours general

...

La fonction RECHERCHE

Pour illustrer l’utilisation de la fonction RECHERCHE d’Excel, nous allons travailler sur un exemple. Il s’agira d’élaborer une facture.

Pour cela on va commencer par créer un classeur composé de trois feuilles :

  • l’onglet Feuil1 sera renommé « Liste d’articles »
  • l’onglet Feuil2 sera renommé « Barème des remises »
  • l’onglet Feuil3 sera renommé « Facture »
  • l’ensemble du classeur sera renommé sous le nom « FACTURE »

Sur la feuille « Articles », on va saisir le tablea u suivant :

Sur la feuille « Remises », on va saisir le tableau  suivant :

Sur la feuille « Facture », on va saisir le corps d e la facture :

Nommer des plages de cellules

Pour construire notre facture, nous allons utiliser les fonctions de recherche d’Excel. Ces fonctions vont faire référence à des plages de cellules contenant les informations nécessaires. Pour plus de confort, nous allons donner un nom à ces plages de cellules.

La première plage de cellules qui nous intéresse, est celle contenant les articles.

  1. a)            Cliquer sur l’onglet « Liste des articles »
  2. b)           Sélectionner la plage de cellules A1:C8
  3. c)            Cliquer sur le menu Insertion - Nom - Définir
  4. d)           Ecrire « articles »
  5. e)           Valider en cliquant sur OK

La plage de cellules porte maintenant le nom « arti cles »

La deuxième plage de cellules qui nous intéresse, est celle contenant les remises.

  1. a)            Cliquer sur l’onglet « Barème des remises »
  2. b)           Sélectionner la plage de cellules A1:B7
  3. c)            Cliquer sur le menu Insertion - Nom - Définir
  4. d)           Ecrire «remise»
  5. e)           Valider en cliquant sur OK

La plage de cellules porte maintenant le nom « arti cles »

La fonction de calcul RECHERCHEV()

Cette fonction requiert 4 arguments et se présente ainsi :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)

La fonction va rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer. Quand elle aura trouvé cette valeur (nombre ou texte), elle va renvoyer le contenu de la cellule se trouvant sur la même li gne que la valeur trouvée et dans la colonne désignée par no_index_col.

Si la valeur_cherchée n’existe pas dans la table_matrice, la fonction affichera :

  • un message d’erreur, si on choisi le nombre 0 pour l’argument valeur_proche
  • le contenu de la colonne, déterminée par no_index_col, correspondant à la valeur précédente la plus proche de la valeur_cherchée, si on choisi le nombre 1 pour l’argument valeur_proche

Dans notre exemple :

Pour élaborer notre facture, nous allons utiliser la fonction RECHERCHEV(). Nous allons construire cette formule dans la colonne « D ésignation » afin d’afficher automatiquement le nom de l’article en fonction du code article qui sera saisi dans la colonne « Code article ».

La formule, que l’on va construire dans la cellule C5, sera donc la suivante :

  • La valeur_cherchée sera le code de l’article, préalablement saisi en B5.
  • La table_matrice est la plage de cellules que l’on a préalablement nommé

« articles ».

  • no_index_col sera ici le chiffre 2 qui correspond à la deuxième colonne de notre zone « articles », et qui contient le nom des articles.
  • valeur_proche sera ici le chiffre 0 qui permettra d’afficher un message d’erreur si le code article saisi n’existe pas.

la formule sera donc la suivante :

=RECHERCHEV(B5;articles;2;0)

Le résultat sera le suivant :

La fonction construite en C5 renvoie la valeur « Di sque dur ».

En effet :

  1. a)            la fonction recherche la valeur 1, saisie en B5, dans la zone « articles » en se déplaçant VERTICALEMENT dans la première colonnede la zone.
  2. b)           une fois la valeur_cherchée trouvée, la fonction lit la ligne correspondante dans la zone jusqu’à la colonne choisie dans no_index_col.
  3. c)            la fonction renvoie la valeur de cette cellule.

Pour obtenir le prix de l’article, la fonction est la même que la précédente, mis à part le no_index_col qui est ici le chiffre 3 (troisième colonne de la zone « articles »). La fonction construite en E5 est la suivante :

=RECHERCHEV(B5;articles;3;0)

Pour obtenir le montant total, on multiplie la quantité en D5 par le prix obtenu en E5. la formule est la suivante :

=D5*E5

Ces formules sont à recopier sur les lignes suivantes.

Le total hors taxes de la facture s’obtient en faisant la somme des lignes de la facture :

=SOMME(F5:F10)

Le montant de la remise sera fonction du total hors taxes de la facture situé en E11. On applique ici un barème par tranches. Par exemple, entre 5000 F et 7000 F, on applique 5% de remise. Dès que le montant hors taxes dépasse 7000 F et jusqu’à ce qu’il atteigne 10000 F, le taux de remise sera de 7%, etc.

Dans notre tableau des remises, tous les cas de figure ne sont pas prévus. Seuls les seuils sont représentés. la fonction de recherche se basera donc sur ces seuils pour renvoyer le taux de remise à appliquer. Si le total de la facture est différent du montant d’un des seuils (cas général), la fonction renverra le taux de remise correspondant au seuil inférieur le plus proche.

La fonction (construite dans notre exemple en F12) sera donc la suivante : =RECHERCHEV(F11;remise;2;1)*F11

  • F11 est la cellule contenant le total hors taxes qui est la valeur_cherchée dans la table_matrice.
  • remise est le nom de la table_matrice (ou plage de cellules) contenant le barème des remises où la recherche sera effectuée.
  • 2 est le numéro de la colonne de la table_matrice « remise » qui contient le taux de remise à appliquer.
  • 1 signifie qu’en l’absence de la valeur_cherchée dans la table_matrice, la valeur inférieure la plus proche sera renvoyée.
  • Le résultat de la recherche est multiplié par le montant hors taxes en F11 (*F11) pour obtenir le montant de la remise.

Pour un total hors taxes de 6325 F le taux de remise applicable sera donc 5%

Utilisation de listes de données

Remarques préalables sur les listes de données

Tout tableau contenant une série d’informations peut-être utilisé comme une base de données afin d’en extraire des informations et des statistiques.

Les informations pourront être extraites à l’aide d es fonctions de recherche (voir chapitre précédent) ou bien à l’aide de filtres ou de formules de calcul appropriées.

La seule contrainte, pour pouvoir utiliser les filtres, est de donner un format différent à            la première ligne du tableau. Cette ligne sera ainsi reconnue automatiquement comme contenant les noms de champs (ou en-têtes de colonnes).

Nous allons travailler sur un exemple de liste contenant le fichier des membres d’une entreprise.

Dans ce tableau nous allons utiliser des formats de nombres particuliers et des formules de calculs élaborées qui sont résumés surla page suivante. Ces formats et ces formules sont présentés pour faire connaître quelques possibilités du logiciel et ne sont pas indispensable pour l’utilisation ce tableau comme base de données.

Sur la page suivante est représenté le tableau sur lequel vont être fondés les exemples qui vont suivre.

...

Mettre la liste à jour

Pour ajouter ou supprimer des fiches supplémentaires dans une liste, on peut procéder de deux manières :

  • Saisir directement une nouvelle fiche à la suite des autres selon la méthode classique de saisie sur Excel.
  • Saisir par l’intermédiaire du menu Données - Grille. Cette méthode présente l’avantage de rendre la liste dynamique pour son utilisation dans des calculs statistiques (voir page 19) et de tenir compte des formats et des formules de calculs créés. Dans notre exemple, l’utilisation du menu Données - Grille fera apparaître la boîte de dialogue suivante :
  • Les champs calculés ne sont pas autorisés en saisie.
  • Pour se déplacer dans la liste on utilisera les boutons ou  .
  • Pour entrer une nouvelle fiche on cliquera sur le bouton  .
  • Pour supprimer la fiche affichée on cliquera sur le bouton 

Filtrer la liste en choisissant des critères



Choix de critère pour un seul champ

Pour afficher uniquement les enregistrement désirés on utilise le menu Données - filtre - filtre automatique. Une flèche apparaît alors à droite de chaque nomde champ.

Par défaut, toutes les lignes de la liste sont affichées. Si l’on désire voir s’afficher uniquement les fiches désirées, on va indiquer un critère.

Par exemple, si l’on souhaite obtenir uniquement la liste des personnes du service commercial :

  1. a)            Choisir le menu Données - filtre - filtre automatique
  2. b)           Cliquer sur la flèche à droite du champ « service »
  3. c)            Dans la liste des services existants cliquer sur « COM »

La liste du personnel du service « commercial » app araît :

On constate que dans la liste qui apparaît en cliquant sur la flèche, existent d’autres options

L’option « (Tout) »

Permet d’afficher l’intégralité des fiches pour le champ concerné. Dans notre exemple, toutes les fiches quel que soit le service.

L’option « (Vides) »

Permet d’afficher les fiches qui ne comportent rien dans le champ choisi. Dans notre exemple, les fiches des personnes n’appartenant à aucun service (aucune fiche).

L’option « (Non vides) »

Permet d’afficher les fiches comportant un texte quelconque dans le champ choisi.

Dans notre exemple, toutes les fiches.

 L’option « (Personnalisé...) »

Permet d’affiner la sélection en multipliant les critères. Lors du choix de cette option une boîte de dialogue s’ouvre :

Il est possible de travailler sur des fourchettes de critères à l’aide des signes opérateurs (plus grand que, plus petit que,...) et de les combiner (et, ou).

Par exemple, si l’on souhaite avoir la liste des personnes du service commercial ET du service administratif :

On demande que le premier critère soit « COM » ET que le second soit « ADM ».

Ajouter un critère d’un autre champ

Si on désire la liste des hommes du service commercial et du service administratif, on combinera le critère service avec le critère sexe.

  • Pour le service on reprendra l’exemple ci-dessus.
  • Pour le sexe, on clique sur la flèche SEXE puis sur « homme ».

Supprimer un critère

Pour supprimer le critère SEXE :

  1. a)            Cliquer sur la flèche SEXE.
  2. b)           Cliquer sur (tout) pour annuler le critère

Annuler le filtre automatique

  1. a)            Choisir le menu Données - Filtre
  2. b)           L’option filtre automatique est précédée d’une oche
  3. c)            Cliquer sur Filtre automatique

Rechercher plus de deux valeurs pour un même champ

Dans ce cas il va falloir construire une table de critères. Pour plus de facilité, cette table de critères sera construite sur une feuille séparée.

Règles à respecter :

  • Les en-têtes de colonnes (noms de champs) de la zo ne de critère doivent être identiques à ceux de la liste.
  • Lorsque les critères que vous souhaitez sont de nature : ET (un critère ET un autre critère, par exemple service COM ET service ADM), la table de critères doit être construite de sorte que les valeurs cherc hées soit sur une même ligne.
  • Lorsque les critères que vous souhaitez sont de nature : OU (un critère OU un autre critère, par exemple service COM OU service ADM), la table de critères doit être construite de sorte que les vale urs cherchées soit sur des lignes différentes.
  • Une table de critères ne doit jamais contenir de lignes ou de cellules vides.

Construction de la table de critères

Cette table va être construite sur une autre feuill e que celle contenant la liste.

Exemple : On va extraire de notre liste, les fiches correspondant aux hommes des services COM et ADM.

  1. a)            Cliquer sur l’onglet d’une nouvelle feuille.
  2. b)           Construire un tableau reprenant les noms de champs retenus pour le choix des critères d’extraction, et les valeurs désiréespour chacun d’eux.

La table de critères se présentera comme suit :

ATTENTION ! Les noms de champs doivent avoir la mêm e syntaxe que dans la liste.

Les critères ET se trouvent sur la même ligne :

  • A la fois « Homme »  ET du service « COM »
  • A la fois « Homme »  ET du service « ADM »

Les critères OU se trouvent sur des lignes différentes. Dans le résultat de mon extraction je veux :

  • Des « Hommes du service « COM »  OU des « Hommes » du service « ADM »

Extraction des fiches désirées

Après avoir construit la table de critères, on va ancerl l’extraction des fiches désirées :

  1. a)            Cliquer sur une cellule quelconque de la feuille contenant la table de critères.
  2. b)           Cliquer sur le menu Données - Filtre - Filtre élaboré.
  3. c)            Si nécessaire, dans la boîte de dialogue qui apparaît, cliquer dans la zone « Plages ».
  4. d)           Cliquer sur l’onglet de la feuille contenant la liste.
  5. e)           Sélectionner sur la feuille la plage contenant la liste (dans notre exemple A1:L22).
  6. f)            La référence de la feuille et de la plage de celules sélectionnées apparaît.
  7. g)            Cliquer dans la zone « Zone de critères » de la boîte de dialogue.
  8. h)           Sélectionner la table de critères préalablementconstruite (dans notre exemple A1:B3).
  9. i)             La référence de la feuille et de la plage de celules sélectionnées apparaît.
  10. j)             Cliquer sur l’option « Copier vers un autre empl acement » de la boîte de dialogue.
  11. k)            Cliquer dans la zone « Destination » de la boîte de dialogue.
  12. l)             Cliquer sur une cellule qui sera l’angle supérieur gauche du tableau qui va apparaître.
  13. m) Cliquer sur OK pour lancer l’extraction.

La boîte de dialogue se présente ainsi :

Extraction sélective sur certains champs

Dans l’exemple précédent, toutes les colonnes (ou champs) de la liste sont extraits, dans l’ordre de la liste. Il est néanmoins possible de n’extraire que les colonnes (ou champs) désirées, et ce, dans l’ordre désiré.

Exemple : En reprenant l’exemple ci-dessus, on désire avoir le NOM, le PRENOM et l’AGE des « Hommes » des services « COM » et « ADM ».

Sur une nouvelle feuille construire la table de critères correspondante (voir page 15). Puis, sur la ligne désirée (ligne 5 dans notre exemple), taper le nom des colonnes désirées en respectant la syntaxe de la liste.

  • En A6, taper Nom
  • En B6, taper Prénom
  • En C6, taper Age
  • En D6, taper Service

La procédure d’extraction va être la même que dans l’exemple précédent. Seule la destination de l’extraction va changer :

  1. a)            Cliquer sur une cellule quelconque de la feuille contenant la table de critères.
  2. b)           Cliquer sur le menu Données - Filtre - Filtre élaboré.
  3. c)            Si nécessaire, dans la boîte de dialogue qui apparaît, cliquer dans la zone « Plages ».
  4. d)           Cliquer sur l’onglet de la feuille contenant la liste.
  5. e)           Sélectionner sur la feuille la plage contenant la liste (dans notre exemple A1:L22).
  6. f)            La référence de la feuille et de la plage de celules sélectionnées apparaît.
  7. g)            Cliquer dans la zone « Zone de critères » de la boîte de dialogue.
  8. h)           Sélectionner la table de critères préalablement construite (dans notre exemple A1:B3).
  9. i)             La référence de la feuille et de la plage de celules sélectionnées apparaît.
  10. j)             Cliquer sur l’option « Copier vers un autre empl acement » de la boîte de dialogue.
  11. k)            Cliquer dans la zone « Destination » de la boîte de dialogue.
  12. l)             Sélectionner les cellules contenant le nom des colonnes (dans notre exemple A6:D6).
  13. m) Cliquer sur OK pour lancer l’extraction.

 La boîte de dialogue se présente ainsi :



Après extraction, le résultat est le suivant :

Calculs statistiques

On peut obtenir une série de calculs statistiques sur la plupart des informations de la liste. Ces calculs peuvent porter sur l’ensemble de la liste ou bien sur une seule partie de celle-ci. Si l’on souhaite effectuer des statistiques sur une partie seulement de la liste, il faudra définir un ou plusieurs critères.

Statistiques sur un critère

Dans l’exemple ci-dessous nous allons réaliser un certain nombre de calculs. Ces calculs porteront sur une partie seulement de la liste. Le critère retenu sera le service, c’est à dire que les calculs porteront sur les fiches correspondant au service désiré.

Définition de la plage de cellule contenant la liste

Il est préférable de donner un nom à la plage de cellule contenant la liste. Ce nom sera de préférence « Base_de_données ». Si on utilise le nom

«             Base_de_données », et que l’on prend la précaution de mettre à jour la liste par l’intermédiaire du menu Données - Grille (voir page 10), les calculs porteront sur la dernière version de la liste et seront donc toujours à jour.

Pour nommer la plage de cellules contenant la liste :

  1. a)            Cliquer sur l’onglet de la feuille de calcul contenant la liste.
  2. b)           Sélectionner la liste (en-têtes de colonnes comp ris).
  3. c)            Choisir le menu Insertion - Nom - Définir
  4. d)           Ecrire Base_de_données en respectant exactement la syntaxe (accents et tirets).
  5. e)           Valider en cliquant sur OK

Définition de la plage de cellule contenant le critère

La zone contenant le critère se compose de deux cellules :

  • Une cellule contenant le nom de l’en-tête de colon ne (ou nom de champ) désiré dans la liste (respecter la syntaxe).
  • Une cellule, située sous la première, dans laquelle on écrira le critère désiré.

Cette zone peut être placée n’importe où sur la feu ille de calcul. Pour plus de clarté, il est préférable de donner un nom à cette zone. Dans notre exemple nous l’appellerons « Critères ».

  1. a)            Choisir une cellule et écrire le nom de champ souhaité (on écrit «Service» en A1 dans notre exemple)
  2. b)           Sélectionner un plage de cellules comprenant la cellule contenant le nom du champ ainsi que la cellule située immédiatement dessous (ici A1:A2).
  3. c)            Choisir le menu Insertion - Nom - Définir
  4. d)           Ecrire Critères en respectant exactement la syntaxe (accents).
  5. e)           Valider en cliquant sur OK

Construction des formules statistiques

Les formules de calculs du type BD....() vont requérir trois arguments :

  • La plage de cellules contenant la liste (dans notre exemple cette zone est nommée Base_de_données).
  • Le numéro de colonne contenant les données à traiter (par exemple le salaire brut).
  • La zone contenant le critère (dans notre exemple cette zone est nommée Critères).

Les formules peuvent être de plusieurs types et con cerner plusieurs champs de la liste. Dans notre exemple nous allons calculer :

Calcul    Formule              Numéro de la colonne désiré    employée           utilisée dans la liste

La somme des salaires bruts      BDSOMME()      Brut (colonne 7)

La moyenne des salaires bruts  BDMOYENNE() Brut (colonne 7)

L’ancienneté la plus élevée        BDMAX()            Ancienneté (colonne 9)

Le salaire le plus faible  BDMIN()             Brut (colonne 7)

L’âge moyen des salariés            BDMOYENNE() Age (colonne 1 1)

 Choix du critère

Le résultat affiché sera fonction du critère choisiet écrit dans la zone de critères.

Dans notre exemple dans la cellule A2.

  • Si la zone de critère est vide (rien dans la cellule A2), les calculs s’effectuerons sur la totalité de la liste.
  • Si un critère est saisi dans la zone de critère (dans la cellule A2), les calculs s’effectuerons uniquement sur les fiches de la liste répondant à ce critère.

Dans notre exemple nous avons souhaité obtenir des statistiques portant sur le personnel du service TEChnique. La zone de critères (A1:A2) comporte :

En A1 l’en-tête de colonne de la liste correspondan t au service : « Service ».

En A2 le critère à retenir dans la colonne Service de la liste : « TEC ».

Les calculs vont porter uniquement sur les membres du service TEChnique.

 Statistiques selon plusieurs critères

Dans l’exemple ci-dessous nous allons réaliser un certain nombre de calculs. Ces calculs porteront sur une partie seulement de la liste. Les critères retenus seront le sexe et le service, c’est à dire que les calculs porteront sur les fiches correspondant au sexe et au service désiré.

Définition de la plage de cellule contenant la liste

Il est préférable de donner un nom à la plage de cellule contenant la liste. Ce nom sera de préférence « Base_de_données ». Si on utilise le nom

«             Base_de_données », et que l’on prend la précaution de mettre à jour la liste par l’intermédiaire du menu Données - Grille (voir page 10), les calculs porteront sur la dernière version de la liste et seront donc toujours à jour.

Pour nommer la plage de cellules contenant la liste (si ce n’est déjà fait) :

  1. a)            Cliquer sur l’onglet de la feuille de calcul contenant la liste.
  2. b)           Sélectionner la liste (en-têtes de colonnes comp ris).
  3. c)            Choisir le menu Insertion - Nom - Définir
  4. d)           Ecrire Base_de_données en respectant exactement la syntaxe (accents et tirets).
  5. e)           Valider en cliquant sur OK

Définition de la plage de cellule contenant les critères

La zone contenant les critères se compose de plusieurs cellules (4 au total pour notre exemple):

  • Deux cellules côte à côte contenant chacune le nomde l’en-tête de colonne (ou nom de champ) désiré dans la liste (respecter al syntaxe).
  • Deux cellules, situées sous les première, dans lesquelles on écrira le critère désiré.

Cette zone peut être placée n’importe où sur la feu ille de calcul. Pour plus de clarté, il est préférable de donner un nom à cette zone. Dans notre exemple nous l’appellerons « Critères2 » (le nom « Critères » ay ant déjà été donné dans le chapitre précédent, voir page 20).

  1. a) Choisir un emplacement et écrire les noms de champs souhaités (on écrit « Sexe » en B1 et « Service » en C1 dans notre exem ple)
  2. b)           Sélectionner un plage de cellules comprenant les cellules contenant les noms de champs ainsi que les cellules situées immédiatement dessous (dans notre exemple B1:C2).
  3. c)            Choisir le menu Insertion - Nom - Définir
  4. d)           Ecrire Critères2 en respectant exactement la syntaxe (accents).
  5. e)           Valider en cliquant sur OK



51