Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

TP Bilan - Excel et publipostage (corrigé) Exercice 1 1. Blanc sur fond noir. B

TP Bilan - Excel et publipostage (corrigé) Exercice 1 1. Blanc sur fond noir. Barre d’outils « couleur de remplissage » et « couleur de police ». Figure 1 : couleur de remplissage Figure 2 : couleur de police On obtient : 2. a. Insérer une nouvelle colonne juste avant la moyenne et l’appeler « MCC » (moyenne des contrôles continus). Sélectionner la colonne F, puis menu « Insertion » puis « Colonnes ». b. Rappel sur les fonctions ET, OU et SI. La fonction ET Regardons l’exemple suivant : La formule : ET(A1="a" ; B1="a") Se comprend « A1="a" et B1="a" » et signifie : si la cellule A1 contient un « a » et la cellule B1 contient un « a », alors on renvoi VRAI, sinon on renvoi FAUX. En d’autres termes, la phrase « A1="a" et B1="a" » est vraie si et seulement si la case A1 contient effectivement un "a" et la case B1 contient effectivement un "b". Si on étend (en faisant défiler la croix noire) cette formule aux autres cases de la colonne on obtient : La fonction OU Regardons l’exemple suivant : La formule : ET(A1="a" ; B1="a") Se comprend « A1="a" ou B1="a" » et signifie : Si au moins l’un des cas suivant est avéré (A1="a" ou B1="a") alors on renvoi vrai, sinon on renvoi faux. Si on étend (en faisant défiler la croix noire) cette formule aux autres cases de la colonne on obtient : Récapitulons : ET(Test1 ; Test2) : les deux test Test1 et Test2 doivent être vérifier. OU(Test1 ; Test2) : au moins un des deux tests doit être vérifier. La fonction SI Regardons l’exemple suivant : La formule : =SI(OU(A1="a";B1="a"); "chat"; "chien") Se comprend : si la case A1 contient « a » ou si la case B1 contient « a » (ou a le sens précédemment défini), alors on écrit « chat », dans le cas contraire on écrit « chien ». Si on étend (en faisant défiler la croix noire) cette formule aux autres cases de la colonne on obtient : **** Revenons à l’énoncé : Colonne « Assiduité » : si l’étudiant n’est jamais absent, cette colonne contiendra « Assidu ». Dans le cas contraire, la colonne contiendra «Absent ». On peut procéder de deux manières selon que l’on utilise la fonction ET ou OU. Première solution : « l’étudiant n’est jamais absent » se traduit par : ET(C2<>"Abs";D2<>"Abs";E2<>"Abs") Où <> signifie « différent de » . Dans la cellule F2 écrire la formule : =SI(ET(C2<>"Abs";D2<>"Abs";E2<>"Abs");"Assidu";"Absent") Pour comprendre plus facilement le sens de cette formule, écrivons-la de manière arborescente : =SI( ET( C2<>"Abs"; D2<>"Abs"; E2<>"Abs"; ) ; "Assidu"; "Absent"; ) C’est-à-dire : SI la cellule C2 ne contient pas le mot "Abs" et la cellule D2 ne contient pas le mot "Abs" et la cellule E2 ne contient pas le mot "Abs" Alors On écrit (dans F2) le mot "Assidu" Sinon On écrit le mot "Absent". Deuxième solution La formule à écrire doit exprimer l’énoncé suivant : si l’étudiant n’est jamais absent, cette colonne contiendra « Assidu ». Dans le cas contraire, la colonne contiendra «Absent ». qui est équivalent à l’énoncé : si l’étudiant est absent au moins une fois, alors la colonne contient « Absent », sinon la colonne contient « Assidu ». On exprime ce deuxième énoncé par : =SI(OU(C2="Abs";D2="Abs";E2="Abs");"Absent" ;"Assidu") c. Colonne « Moyenne » : si l’étudiant est assidu, alors cette colonne contient la moyenne de l’étudiant (le devoir compte coefficient 2 , sinon la colonne contient le mot « A voir ». Dans la cellule G2 on écrit la formule : =SI(F2="Assidu" ; (C2+D2+E2*2)/4 ; "A voir") Qui se traduit par : si la cellule F2 contient le mot « Assidu » alors on calcule (C2+D2+E2*2)/4, ie la moyenne de l’étudiant avec le devoir qui a un coefficient double, et l’on écrit le résultat dans G2. Dans le cas contraire (la cellule F2 ne contient pas le mot « Assidu », on écrit « A voir ». d. Informations globale : écrire des formules permettant de calculer la plus haute moyenne et la moyenne des moyennes. Dans la cellule I3 (plus haute moyenne) : =MAX(G2:G26) Dans la cellule I4 (moyenne des moyennes) : =MOYENNE(G2:G26) Vous n’êtes pas obliger de taper « G2:G26 » à la main, vous pouvez sélectionner les cellules correspondantes à la souris. Remarque : dès que vous tapez « = » dans une cellule vous entrez dans le mode formule, on vous propose alors des fonctions des fonctions à utiliser. En appuyant sur la flèche noire qui pointe vers le bas, vous avez accès à d’autres fonctions. e. Dans la ligne 30, si Sophie a eut un 14, alors on écrit « Sophie a eu un 14 ». Sélectionner la ligne 30 et taper dans la « barre de formule », la formule suivante : =SI(OU(C17=14;D17=14;E17=14);"Sophie a eu un 14";) Qui se traduit par : SI la case C17 contient un 14 ou si la case D17 contient un 14 ou si la case E17 contient un 14 alors on écrit dans la ligne 30 « Sophie a eu un 14 ». Rappel : La « barre de formule » est le champ à remplir qui se situe après le « fx » : En appuyant sur « fx » on vous propose des fonctions : Exercice 2 Sur la page web, il vous est fournit le fichier « commandes.xls ». Enregistrez-le dans votre dossier. Dans le menu « Données », choisissez « Filtrer » puis « Filtres automatiques ». Des menus déroulants apparaissent au niveau des titres des colonnes : Choisissez alors les commandes pour « Toulouse » (clique sur la flèche à côté de « Ville »). Puis choisissez des prix inférieurs à 1500 euros (cliquez sur la flèche à côté de « PrixTotal » puis choisissez « Personnalisez » et trouvez les bonnes options). Puis calculez le sous-total de des ventes par employés (dans le menu « Donnée », sélectionnez « Sous-totaux » puis trouvez les bonnes options). Attention, pour ce calcul, il vous faut tout d’abord trier par ordre croissant les numéros d’employés (clic sur « employés » puis choisir « trier »). Sur ma machine, je n’obtiens pas le résultat attendu. Pour comprendre l’intérêt des sous-totaux voici un exemple tiré du cours : Exercice 3 Désactiver « filtre automatique ». « Données » puis « Filtrer » puis désactiver « filtre automatique ». Faîtes les tableaux croisés suivants (voir le cours pour savoir comment faire) : Sélectionner toute la feuille puis faire menu « Données » puis « rapport de tableaux croisés dynamiques ». Puis faire « Suivant ». Si la zone contenu dans le champ « Plage » n'est pas remplie, recommencer en sélectionnant une case du tableau. Faire « Suivant ». Appuyer sur « Disposition ». • Nombre de produits commandés pour un employé pour ce produit donné Ajouter « N° employé » en colonne et « Nom du produit » en ligne (ou le contraire). Ajouter « Nom du produit » dans « Données », la fonction « Somme » est appliquée par défaut. Puis appuyer sur « OK » et « Terminer ». • Pour chaque date de commande, pour chaque ville, la commande maximale (en prix) Ajouter « Date commande » en colonne et « Ville » en ligne (ou le contraire). On ajoute « Prix Total » dans « Données », la fonction « Somme » est appliquée par défaut, ce qui nous donne : Double-cliquer sur « Somme de PrixTotal » et choisir la fonction « Max ». Explication sur le résultat (dans le deuxième cas) : Le 22/08/1994, la commande maximale pour Albuquerque était de 1800 euros. Remarque : On peut avoir plusieurs paramètres en ligne et en colonne. Exercice 4 Ecrire, en utilisant le publipostage, une lettre aux individus contenus dans le fichier « destinataires.xls ». (Je reprends les captures d’écran du cours.) Lancez Word, puis faire : menu « Outils », puis « Lettres et publipostage », puis « Fusion et publipostage ». Dans la fenêtre « Fusion et publipostage », choisir « lettre » puis passez à l’étape suivante. Choisir le document actuel, puis passez à l’étape suivante. Choisir une liste existante puis faire « parcourir ». Naviguez dans l’arborescence pour choisir le fichier « destinataires.xls ». Ensuite, appuyez sur « ok » quand on vous le demande. Puis passez à l’étape suivante. On écrit la lettre, et à chaque fois que l’on souhaite écrire le nom, le prénom, la société, l’adresse ou la ville du destinataire, on fait « autre élément » et on choisi le champ correspondant. On appuis ensuite sur « insérer » et on ferme la fenêtre « Insérer un champ de fusion ». On passe à l’étape suivante. On peut ensuite visualiser les lettres pour vérifier que tout va bien et cliquant sur les «<< » ou sur « >> ». On passe à l’étape suivante. On peut alors maintenant imprimer les uploads/s3/ bilan2-corrige 1 .pdf