BD d'après excel

d'après une collègue

OBJECTIFS

- Saisir des données avec la grille standard

- Trier une liste

- Utiliser la fonction SOMMEPROD                                                               

- Utiliser la fonction RANG

- Utiliser les fonctions Bases de données

CAS NELONI

M. NELONI, professeur de musique souhaite calculer ses moyennes semestrielles sur tableur. Il vous charge de la mise en place du tableau, et vous décidez d’utiliser le mode grille de saisie pour enregistrer les noms des élèves ainsi que les notes des deux premiers DS.

Les objectifs du cas reposent sur :

- l’utilisation de la grille de saisie

- la révision sur les fonctions SOMMEPROD, SOMME.SI et RANG

- l’utilisation des fonctions de bases de données BDSOMME, BDNB qui permettent d’effectuer des calculs conditionnels mettant en œuvre plusieurs critères

Vous disposez des documents suivants :

·      Annexe 1 : Tableau des notes du 1er semestre à saisir

·      Annexe 2 : Table des coefficients du 1er semestre

ACTIVITE 1 : Préparation des tableaux

1) Table des coefficients :

a) saisissez la table des coefficients conformément à l’annexe 2

b) Définissez le nom de la zone B3:F3  : COEFF1 (on se limitera à 5 devoirs par semestre)

c) Réalisez l’encadrement si vous le souhaitez

d) Renommez la feuille COEFFICIENT

2) Feuille de notes du 1er semestre

Cette feuille va se mettre à jour automatiquement grâce à la grille de saisie (comme vous pourrez le constater) -

AUSSI NE SAISISSEZ PAS TOUS LES ENREGISTREMENTS MAINTENANT - CE SERAIT STUPIDE ET LE CAS N’AURAIT AUCUN INTERET - DONC SUIVEZ SCRUPULEUSEMENT LES INSTRUCTIONS QUI SUIVENT

a) Saisissez les noms de champ de la 1ère ligne

b) Modifiez les largeurs de colonnes (colonne A : 16, Colonne B : Standard, colonne C à G : 6,71 environ)

c) Créez un encadrement pour les cellules des lignes 1 et 2 Et mettez les cellules A1 à H1 en couleur gris clair (menu format - Cellules - motifs)

Vous devez obtenir ceci :

d) Implantation de la formule de la moyenne en cellule H2

le principe de calcul serait le suivant :

(Note DS1 X COEF DS1 + NOTE DS2 X COEFF DS 2 +......+ NOTE DS5 XCOEF DS 5)

--------------------------------------------------------------------------------------------------------------

Somme des coefficients

La fonction SOMMEPROD, simplifie grandement le problème. De plus, en conjonction avec la fonction SOMME.SI, il est possible de prendre  en compte les absences aux devoirs (lorsqu’un élève est absent à un devoir, la cellule contenant sa note est vide).

FORMULE EN H2 : =

e) Renommez la feuille en SEMESTRE1

ACTIVITE 2 Saisie des données avec la grille

1) Mettez en surbrillance la zone A1:H2

2) Activez le menu Données... Grille

3) saisissez les enregistrements du tableau de l’annexe 1

Le premier enregistrement donne ceci

Remarque : pour se déplacer dans les champs utiliser la souris ou la touche Tab

ACTIVITE 3 : Tri par ordre alphabétique

On souhaiterait que le tableau des notes soit trié par ordre alphabétique des noms et prénoms

1) Mettre en surbrillance le tableau avec les noms des champs (en principe A1:H16)

2) Activez la commande Données...- Trier

     Dans le champ 1ère clé, sélectionnez le champ Nom

     Dans le champ 2ème clé, sélectionnez le champ Prénom

Indique que la première ligne contient les titres des colonnes (champs) et n’est donc pas à trier

EXCEL va trier le tableau par ordre de noms et par prénom si deux personnes ont le même nom.

TRES IMPORTANT : POUR TRIER, IL FAUT TOUJOURS SELECTIONNER LA TOTALITE DU TABLEAU FAUTE DE QUOI LES NOTES POURRAIENT NE PLUS CORRESPONDRE AUX ELEVES.

ACTIVITE 3 Classer les élèves

M. NELONI souhaiterait classer  chaque élève en fonction de sa moyenne.

Une première solution consisterait à attribuer un rang « manuellement »   à chaque élève  (environ 10 mn)

La deuxième solution consiste à utiliser la fonction suivante :

=RANG(Nombre;référence;ordre)

Cette fonction donne le rang d’un nombre par rapport à la liste de nombre défini en référence. Ordre est facultatif.

APPLICATION :

1) En cellule I1, saisissez « CLASSEMENT »

2) En cellule I2, implantez la formule suivante avec ou sans l’assistant fonction :

=RANG (

3) recopiez la formule précédente jusqu’en I16

ACTIVITE 4  :  Statistiques

M. NELONI est déjà pleinement satisfait de votre travail, mais il voudrait davantage d’infomations pour lui préparer son conseil de classe,  il voudrait avoir :

la moyenne de chaque DS

La moyenne de la classe

La note minimale et maximale de chaque DS et des moyennes 

Ces éléments sont à implanter à partir de la ligne 17

Que se passe-t-il pour le calcul des moyennes des DS 3, 4 et 5 ? Pour éliminer le problème, n’hésitez pas à utiliser la fonction SI combinée avec une fonction d’information

ACTIVITE 5 Utilisation de la fonction BDNB

Vraiment ébahi par vos capacités et par la puissance d’EXCEL, M. NELONI vous demande d’établir les statistiques suivantes :

·      Nombre d’élèves dont la moyenne est < 8 (en nombre et en pourcentage)

·      Nombre d’élèves dont la moyenne est supérieur ou égale à  8 et inférieure strictement à 12

·      Nombre d’élèves dont la moyenne est supérieure ou égale à 12

Il faut ici recourir à une fonction base de données d’EXCEL :

= BDNB (Table;Champ;zone de critères)

Cette fonction permet de compter le nombre d’enregistrements dont la valeur du champ répond aux critères définis.

·      la zone table correspond dans l’exemple à la zone A1:H16

·      la zone Champ correspond à « Moyenne »

·      la zone de critère est à définir dans la feuille, dans une zone vide :

Pour définir un critère, on définit le nom du champ (dans l’exemple : Moyenne) et sur la ligne du dessous on entre le critère proprement dit

Il y a 3 critères à définir :

Pour faciliter l’écriture des formules, définissez les noms suivants :

·      K2:K3 : CRITERE1

·      K6:L7 : CRITERE2

·      K10:K11 : CRITERE3

Ce critère signifie :

Moyenne >=8 ET Moyenne<12

Implantation des formules de calcul dans la feuille de notes : ajouter le tableau suivant

ATTENTION : Les cellules C22 à D25 sont calculées

Les formules à implanter en cellules C22 à C24 sont :

C22 : =BDNB (

C23 : =BDNB (

C24 : =BDNB (

Implantez les autres formules.

Au final, vous devez obtenir le tableau de l’annexe 3

A retenir :

Les fonctions bases de données permettent de faire des calculs sur des cellules répondant à un ou plusieurs critères. La syntaxe de ces fonctions est :

=BD (Table ;champ,zone de critère)

Les fonctions bases de données d’Excel comprennent :

BDNB :

BDNBVAL : comptage conditionnel de valeurs numériques ou alphanumériques

BDSOMME : pour une addition avec critères

BDMIN

BDMAX

BDMOYENNE

ANNEXE 1 Tableau des notes du 1er semestre

ANNEXE 2 Table des coefficients

ANNEXE 3 TABLEAU FINAL