Cours Excel 2003, niveau 2, base de donnees, fonction si, macros

LE MANUEL IL A ÉTÉ CONÇU POUR RÉPONDRE À PLUSIEURS OBJECTIFS :

situer Excel dans le contexte plus général de la micro-informatique accompagner votre progression dans la connaissance de Excel vous indiquer pour chaque manipulation la procédure à suivre vous aider dans tous les travaux que vous pourrez être amenés à effectuer en tant que guide et conseil CE SUPPORT PRÉSENTE DIFFÉRENTES RUBRIQUES REPÉRÉES PAR UNE ICONE

DES PROCÉDURES DÉCRIVENT LA MARCHE À SUIVRE POUR EFFECTUER UNE ACTION PROCÉDURE

Des informations viennent compléter ces procédures INFORMATION

des exemples viennent illustrer ces procédures EXEMPLE

Des conseils vous aident à mettre en pratique vos connaissances CONSEIL

Des mises en garde vous permettent d'éviter les pièges ou d'en sortir ATTENTION

DES EXERCICES PERMETTENT DE TESTER LES PROCÉDURES ET DE METTRE VOS CONNAISSANCES À L'ÉPREUVE EXERCICE

Des procédures avancées décrivent des actions plus complexes AVANCÉ

des emplacements vous permettent de prendre des notes directement sur le support en bas de chaque page.

NOTES

Edition I O S

I. LA BASE DE DONNÉES Une base de données est un ensemble structuré de données, regroupées en un ou plusieurs fichiers, donnant l'image à un instant donné des informations relatives à un sujet, remis à jour régulièrement et autorisant toutes opérations d'interrogation, de recherche, de tri et de gestion en général des informations stockées.

Les bases de données peuvent avoir une organisation assise sur plusieurs modèles différents dont les principaux sont : le modèle relationnel A priori, dans ce modèle, toute information, dés lors qu'elle existe dans la base, est disponible.

Des relations, créées au sein de la structure de la base, entre des champs de données, permettent un accès particulièrement souple aux données par l'intermédiaire de requêtes faisant appel à l'algèbre de BOOLE (et, ou...). le modèle hiérarchique Dans ce modèle, et comme son nom l'indique, les données sont organisées hiérarchiquement.

Les données se voient attribuer des qualificatifs de Père ou de Fils et une donnée peut ne pas pouvoir, de par sa position dans l'arbre hiérarchique (sa généalogie), être mise en relation avec une autre. Ce modèle, plus ancien, est moins utilisé. le modèle XML Dans ce modèle, les données sont organisées autour d’une structure Xml devenu le standard d’échanges de données. Bien souvent, les bases Xml sont des bases relationnelles avec des fonctionnalités Xml.intégrées ou simplement rajoutées.

A A.. C CA AR RA AC CTTÉ ÉR RIIS STTIIQ QU UE ES S EXCEL est un tableur ; il est donc avant tout conçu pour faire des tableaux de calculs.

Cependant, dés lors qu'un certain nombre de données ont été saisies de manière structurée dans des tableaux, il est possible de considérer ces données comme une base et d'y appliquer les fonctions classiques d'utilisation des bases de données.

1 LA BASE EXCEL Dans EXCEL, on considère qu'une base de données est un ensemble de cellules, organisé en lignes et en colonnes. Il est alors possible de rechercher des données spécifiques, les trier, les extraire en fonction de critères, effectuer des calculs d'analyse statistique et d'imprimer le résultat de ces différentes opérations.(VOIR BASE.XLS) BASE.XLS

NOTES

Edition I O S

page 1

2 LES CHAMPS Les CHAMPS correspondent aux colonnes de données. ils contiennent les valeurs prises pour chaque type d'information par chaque article de la base.

Les CHAMPS possèdent un NOM qui les qualifie et permet leur gestion. Ce nom correspond à la première ligne de la colonne de données . Il qualifie un type ou une famille de données.

Nom, Région, Ville, Outils et Ventes sont des noms de champs. Un champ peut être simple ou calculé ; dans ce dernier cas, il intègre une formule ou une fonction.

Le champ NOM comprend les valeurs "M.JEAN, "M.DUCHEMIN", "M.LENORMAND", "M.DUBOIS"...

INFORMATION

3 LES ENREGISTREMENTS Un enregistrement ou fiche représente une ligne du champ de données. Cette ligne donne les valeurs (numériques ou alphanumériques) prises par chaque article dans chaque champ. est un enregistrement INFORMATION

4 LE FORMULAIRE Le formulaire est une boîte de dialogue permettant de visualiser, modifier, rechercher, ajouter ou supprimer des fiches. Une seule fiche apparaît à la fois. Il correspond à la même notion de formulaire ou de masque d'écran des SGBD classiques.

LE FORMULAIRE : PROCÉDURE

MENU DONNÉES FORMULAIRE UTILISER LES BOUTONS DE LA BOÎTE DE DIALOGUE POUR REVENIR AU TABLEAU

La première fiche de la base est affichée.

L'ascenseur permet de se déplacer relativement entre les fiches ; les flèches bas et haut permettent de passer d'une fiche à la suivante ou à la précédente. <tab> permet de passer d'un champs à l'autre

NOTES

page 2

reproduction réservée

BOUTONS DE COMMANDE création d'une nouvelle fiche à la fin de la base de données suppression de la fiche actuelle chaque ligne d'enregistrement suivant celle supprimée remonte d'un numéro de ligne sans que la taille de la base ou les numéros de ligne des autres données de la feuille soient modifiés.

Annule les modifications d'une fiche modifiée affichage de la fiche précédente affichage de la fiche suivante détermination des critères de recherche seuls des critères de comparaison peuvent être utilisés (=, <, >, >=, >=, <>) ainsi que les caractères génériques.

La recherche s'effectue par le chois des commandes <suivante> ou <précédente> (voir page pour critères d'extraction) fermeture de la boîte de dialogue.

B B.. LLE ES SE EN NTTR RÉ ÉE ES SD DE ED DO ON NN NÉ ÉE ES S Il y a plusieurs manières d'entrer des données dans une feuille de travail EXCEL. Les saisir manuellement Les saisir automatiquement (série) Ouvrir un fichier d'une autre application contenant ces données Les coller à l’aide du presse-papiers à partir d’un fichier d’une autre application Créer une liaison entre la feuille de travail EXCEL et une table contenant les données.

A)

L'OUVERTURE DE FICHIERS TEXTE

EXCEL peut lire des fichiers texte, que ceux ci soient délimités (données séparées par des caractères particuliers) ou non (données non séparées.

FICHIERS TEXTE : PROCÉDURE

<CLIC G> SUR INDIQUER LE TYPE DU FICHIER A OUVRIR SÉLECTIONNER LE FICHIER POUR VALIDER

L’ASSISTANT CONVERSION PREND EN MAIN LES OPÉRATIONS PÔUR VOUS AIDER À CONVERTIR LES DONNÉES

INFORMATION

NOTES

Edition I O S

page 3

ASSISTANT IMPORTATION DE TEXTE : INDIQUER SI LES CHAMPS SONT SÉPARÉS PAR DES ESPACES OU D'AUTRES SIGNES PROCÉDURE

<CLIC G> SUR FAIRE ÉVENTUELLEMENT GLISSER LES SÉPARATEURS DE COLONNE POUR LES REPOSITIONNER

<CLIC G> SUR SÉLECTIONNER UNE PAR UNE CHAQUE COLONNE ET INDIQUER ÉVENTUELLEMENT SON FORMAT

<CLIC G> SUR

le choix proposé par Excel entre données "délimitées" ou "largeur fixe" n’est pas toujours adéquat. Ne pas hésiter à le modifier ATTENTION

OUVRIR LE FICHIER "CONVERS.TXT" DANS EXCEL AFFICHER LES DONNÉES CORRECTEMENT FERMER LE FICHIER SANS L'ENREGISTRER EXERCICE

B)

LA REDISTRIBUTION DES DONNEES

Lorsque les données sont collées à partir d'une application texte, elles ne sont pas toujours directement exploitables : le plus souvent, une ligne de données figure dans une seule cellule sous la forme d'un libellé long ; L'ASSISTANT CONVERSION permet de remédier à cet état de chose.

REDISTRIBUTION : PROCÉDURE

LIBÉRER LES COLONNES ADJACENTES SÉLECTIONNER LA PLAGE DE DONNÉES MENU DONNÉES CONVERTIR

NOTES

page 4

reproduction réservée

OUVRIR AVEC LE BLOC NOTES LE FICHIER "CONVERS.TXT" SÉLECTIONNER LES DONNÉES ET LES COPIER DANS LE PRESSE-PAPIERS OUVRIR DANS EXCEL LE FICHIER CONVERS.XLS COLLER LES DONNÉES EN A1 DE LA FEUILLE EXERCICE EXERCICE LES REDISTRIBUER SUR LES COLONNES ADJACENTES VÉRIFIER QUE LES VALEURS SONT BIEN PRISES EN COMPTE MODIFIER ÉVENTUELLEMENT LEUR FORMAT ET EFFECTUER UNE SOMME AUTOMATIQUE

C'est ici le même assistant conversion que précédemment INFORMATION

C)

L'OUVERTURE DE FICHIERS D'UNE BASE DE DONNEES

EXCEL ouvre normalement les fichiers au format .dbf (format DBASE). Pour ouvrir dans Excel un fichier provenant d'une base de données autre que ACCESS, utiliser ce format.

FICHIERS BASES DE DONNÉES : PROCÉDURE

<CLIC G> SUR INDIQUER LE TYPE DU FICHIER A OUVRIR : "DBASE" SÉLECTIONNER LE FICHIER <OK> POUR VALIDER le fichier est ouvert comme un fichier EXCEL

DBF est un format standard d'échange de données provenant de bases de données INFORMATION

AVANCÉ

Si le fichier à ouvrir est un fichier de base de données mais n'est pas au format " DBF" OUVRIR l'application d'origine des données OUVRIR le fichier contenant les données "ENREGISTRER SOUS" ce fichier au format DBASE (.dbf) FERMER le fichier et l'application d'origine OUVRIR le fichier "DBF" sous Excel OUVRIR SOUS EXCEL LE FICHIER "CLIENT.DBF"

EXERCICE

NOTES

Edition I O S

page 5

D)

L'OUVERTURE DE FICHIERS AU FORMAT XML

EXCEL ouvre normalement les fichiers au format .xml. Le format Xml est un format d’échange de données ou de structure entre applications récentes qui permet de conserver à ces données ou structure la quasitotalité de leurs caractéristiques. Préférer ce format à tout autre si l’application source des données est assez récente pour le permettre.

FICHIERS XML : PROCÉDURE

<CLIC G> SUR INDIQUER LE TYPE DU FICHIER A OUVRIR : "XML" SÉLECTIONNER LE FICHIER <OK> POUR VALIDER le fichier est ouvert comme un fichier EXCEL

E)

L'EXTRACTION DE DONNÉES D'UNE BASE EXTERNE

Excel permet d'extraire d'une base les seules données dont l'utilisateur a besoin par l'intermédiaire d'une requête effectuée avec un assistant. La requête peut être enregistrée. Sont accessibles la plupart des formats du marché si les pilotes adéquats ont été installés.

EXTRACTION DE DONNÉES EXTERNES : MENU DONNÉES PROCÉDURE

DONNÉES EXTERNES

ONGLET BASE DE DONNÉES SÉLECTIONNER

NOTES

page 6

reproduction réservée

PROCÉDURE

CRÉER LA SOURCE DE DONNEES EN 4 ÉTAPES :: 1 SAISIR LE NOM DE LA SOURCE DE DONNÉES 2 SÉLECTIONNER LE DRIVER ODBC ASSOCIÉ 3 ÉTABLIR LA CONNEXION AVEC LA BASE 4 INDIQUER LA TABLE OU SONT LES DONNÉES

Si Excel refuse d'effectuer ces opérations, il peut être nécessaire de rajouter les pilotes ODBC

ATTENTION

ou les filtres adéquats. Cette opération s'effectue dans du panneau de configuration ( OFFICE ) CONNEXION A LA BASE DE DONNÉES:

PROCÉDURE

<CLIC G> SUR <CLIC G> SUR AFFICHER LE DOSSIER CONTENANT LE FICHIER À OUVRIR SÉLECTIONNER LE FICHIER

NOTES

Edition I O S

page 7

SELECTION DE LA TABLE: PROCÉDURE

DÉROULER LA LISTE DES TABLES DE LA BASE DE DONNÉES (ÉTAPE 4) SÉLECTIONNER LE FICHIER

NOTES

page 8

reproduction réservée

L'assistant permet alors de sélectionner les champs à prendre en compte ainsi que les critères d'extraction.

CREATION D'UNE REQUÊTE SIMPLE PAR L'ASSISTANT REQUÊTE: <CLIC G> SUR + DE LA TABLE AFFICHE LA LISTE DES CHAMPS PROCÉDURE

SÉLECTIONNER LE CHAMPS PUIS POUR LE PRENDRE DANS LA REQUÊTE RÉPÉTER L'OPÉRATION POUR TOUS LES CHAMPS À PRENDRE EN COMPTE

les données afférentes peuvent être affichées par SÉLECTIONNER LE CHAMP DANS LA PARTIE DROITE PUIS

POUR EN MODIFIER L'ORDRE

<CLIC G> SUR FILTRER ET TRIER ÉVENTUELLEMENT LES ENREGISTREMENTS

<CLIC G> SUR <CLIC G> SUR

INSERTION DES DONNEES DANS EXCEL : CONFIRMER OU MODIFIER LA CELLULE D'INSERTION DE LA FEUILLE ACTUELLE PROCÉDURE

OU

<CLIC G> SUR <OK> POUR VALIDER

NOTES

Edition I O S

page 9

CRÉER UN NOUVEAU CLASSEUR

EXERCICE

Y AFFICHER LES ENREGISTREMENTS EXTRAITS DE LA BASE ACCESS.MDB POUR LES CHAMPS "SOCIÉTÉ", "CONTACT", "ADRESSE" ET "CODEPOSTAL" ENREGISTRER SOUS LE NOM "REQUETE"

La barre d'outils "données externes" permet de modifier la requête, d'actualiser les données, de INFORMATION

modifier les paramètres de la plage La requête peut encore être modifiée. L'assistant permet aussi d'utiliser des critères de sélection pour les enregistrements ainsi que de trier ces enregistrements.

PROCÉDURE

<CLIC G> DANS LES DONNÉES

<CLIC G> DANS LES DONNÉES <CLIC D>

<CLIC G> SUR

<CLIC G> sur

DE LA BARRE REQUÊTE

NOTES

page 10

reproduction réservée

FILTRER LES DONNÉES: PROCÉDURE

<CLIC G> SUR INDIQUER LES CRITÈRES DE FILTRE

<CLIC G> SUR <CLIC G> SUR

MODIFIER LA REQUÊTE ET NE PRENDRE QUE LES ENREGISTREMENTS DONT LE CODE POSTAL EST SUPÉRIEUR OU ÉGAL À 10000 ET STRICTEMENT INFÉRIEUR À 45000 EXERCICE

Lorsque qu'un champ est filtré ; il est en gras ; lors de plusieurs requêtes successives, enlever le cas échéant les filtre utilisés précédemment ATTENTION

la requête peut être enregistrée en cliquant sur dans le dossier requête de l'utilisateur

, elle l'est alors par défaut

AVANCÉ

NOTES

Edition I O S

page 11

le tri des données s'affiche juste après le filtre.

PROCÉDURE

TRIER LES DONNÉES: ÈRE INDIQUER LA 1 CLÉ DE TRI ET L'ORDRE DE TRI ÈME INDIQUER ÉVENTUELLEMENT LES 2 ET 3 CLÉ DE TRI ET LEUR ORDRE DE TRI <CLIC G> SUR <CLIC G> SUR

MODIFIER LA REQUÊTE ET TRIER PAR CODEPOSTAL ET SOCIÉTÉ DANS L'ORDRE CROISSANT (REQUÊTE)

EXERCICE

NOTES

page 12

reproduction réservée

F)

L'EXTRACTION AVANCÉE DE DONNÉES D'UNE BASE EXTERNE

Excel permet d'extraire d'une base les seules données dont l'utilisateur a besoin par l'intermédiaire d'une requête effectuée avec MICROSOFT QUERY. CREATION D'UNE REQUÊTE PAR QUERY : UTILISER L'ASSISTANT "NOUVELLE REQUÊTE" PUIS À LA FIN AU LIEU DE CHOISIR PROCÉDURE

IL FAUT CHOISIR

(dernière étape)

Lors de la modification d'une requête, il arrive souvent que Excel ait rapatrié les critères au sein d'un seul filtre et n'arrive plus à les lire, il faut alors remodifier la requête afin que Excel la comprenne.

ATTENTION

DÉTERMINATION DES CRITÈRES : INDIQUER DIRECTEMENT LE NOUVEAU CRITÈRE DANS LA ZONE DE CRITÈRES PROCÉDURE

ou MENU CRITÈRES AJOUTER DES CRITÈRE INDIQUER LE CHAMP, L'OPÉRATEUR ET LA VALEUR LORSQUE LE CRITÈRE EST DÉFINI PERMET DE COMBINER DES CRITÈRES LORSQUE TOUS LES CRITÈRES ONT ÉTÉ DÉFINIS

Edition I O S

page 13

Plutôt qu'indiquer un critère complexe pour un même champ comme ceci CONSEIL

il est possible de répéter le champ de critère dans la colonne suivante et d'indiquer le critére sur la même ligne (équivalent à "et") ou sur une ligne différente (équivalent à "où") les critères s'inscrivent dans un volet de la fenêtre et les valeurs affichées correspondent aux critères INFORMATION

REFAIRE LA REQUÊTE PRÉCÉDENTE INDIQUER DANS QUERY LES CRITÈRES SUIVANTS : CODE POSTAL SUPÉRIEUR OU ÉGAL À 10000 ET STRICTEMENT INFÉRIEUR À 45000 EXERCICE

AVANCÉ

Il est possible d'afficher cette requête directement dans Query menu fichier - ouvrir; elle est enregistrée sous le nom du dossier "exosexcbd" ; si query affiche un message d'erreur réseau ou disque, il suffit d'indiquer la base de données "comptoir" du dossier "exosexcbd" en cliquant sur n'y a pas de mot de passe administrateur)

afin que query la retrouve (enlever le log admin, il

NOTES

page 14

reproduction réservée

EXERCICE

AVANCÉ

A PARTIR DE LA BASE DE DONNÉES "COMPTOIR" ET DES TABLES "CLIENTS", "COMMANDES" ET "DÉTAILS COMMANDES", EXTRAIRE LES CHAMPS : SOCIÉTÉ, PAYS, CONTACT, ADRESSE, VILLE, N°COMMANDE, N°EMPLOYÉ, DATE D'ENVOI, RÉFÉRENCE, PRIX UNITAIRE, QUANTITÉ POUR TOUS LES ENREGISTREMENTS DONT LE NOM DE PAYS COMMENCE PAR UNE LETTRE SUPÉRIEUR OU ÉGALE À F ET DONT L'ANNÉE D'ENVOI EST 1995 ENREGISTRER LA REQUÊTE DANS LE DOSSIER OU SE TROUVE LES EXERCICES EXCEL

Il est possible d'afficher cette requête directement dans Query menu fichier - ouvrir; elle est enregistrée sous le nom du dossier "exosexcbd" ; si query affiche un message d'erreur réseau ou disque, il suffit d'indiquer la base de données "comptoir" du dossier "exosexcbd" en cliquant sur n'y a pas de mot de passe administrateur)

afin que query la retrouve (enlever le log admin, il

NOTES

Edition I O S

page 15

AJOUTER UNE TABLE : PROCÉDURE

<CLIC G> SUR SÉLECTIONNER LA TABLE

<CLIC G> SUR <CLIC G> SUR

RENVOYER LES DONNEES VERS EXCEL : PROCÉDURE

EXERCICE

<CLIC G> SUR

RAJOUTER LA TABLE "PRODUITS" PARMI LES ENREGISTREMENTS PRÉCÉDENTS, NE PRENDRE QUE LES COMMANDES CONCERNANT : LE CAMEMBERT PIERROT, LE CÔTE DE BLAYE, LES ESCARGOTS DE BOURGOGNE, LE PATÉ CHINOIS ET LA TARTE AU SUCRE - RENVOYER LES DONNÉES VERS EXCEL

page 16

reproduction réservée

AVANCÉ

Il est possible d'afficher cette requête directement dans Query menu fichier - ouvrir; elle est enregistrée sous le nom du dossier "exosexcbd" ; si query affiche un message d'erreur réseau ou disque, il suffit d'indiquer la base de données "comptoir" du dossier "exosexcbd" en cliquant sur n'y a pas de mot de passe administrateur)

G)

afin que query la retrouve (enlever le log admin, il

LA LIAISON A UNE BASE DE DONNÉES EXTERNE

Excel de créer une liaison avec les données d'un SGBD supportant les liens DDE ou OLE. COPIER LA TABLE / PRESSE-PAPIERS : LANCER ACCESS PROCÉDURE

<CLIC G> SUR INDIQUER LE CHEMIN SÉLECTIONNER LA BASE ("ACCESS.MDB" DE "EXOSBD") <OK> POUR VALIDER SÉLECTIONNER LA TABLE CLIENT

<CLIC G> SUR

POUR METTRE UNE COPIE DE LA TABLE DANS LE PRESSE-PAPIERS

COLLER LA TABLE DANS EXCEL : LANCER EXCEL (si ce n'est déjà fait) PROCÉDURE

SE POSITIONNER DANS LA FEUILLE DE TRAVAIL <CLIC D> - COLLAGE SPÉCIAL <COLLER AVEC LIAISON> FORMAT "EXCEL8" ou <OK> POUR VALIDER

"BIFF5"

les données de la table sont collées dans la feuille de travail sous forme de matrice

Edition I O S

page 17

les données ne peuvent être modifiées que dans Access et non dans Excel ; elles sont automatiquement mises à jour dans Excel INFORMATION

LANCER ACCESS OUVRIR LA BASE DE DONNÉES "COMPTOIR" COPIER LA TABLE PRODUITS DANS UNE FEUILLE "EXCEL" EN CONSERVANT UNE LIAISON ENREGISTRER LE CLASSEUR EXCEL EXERCICE MODIFIER LA TABLE PRODUITS DANS ACCESS FERMER ACCESS OUVRIR LE CLASSEUR PRÉCÉDENT DANS EXCEL

H)

LE CUBE OLAP

Le cube Olap est une structure de base de données intelligente et pluridimensionnelle permettant de travailler sur une partie des données comme dans un sous-ensemble de la base.

CRÉER UN CUBE OLAP : PROCÉDURE

À PARTIR DE QUERY EFFECTUER LA REQUÊTE MENU FICHIER CRÉER UN CUBE OLAP L'ENREGISTRER

NOTES

page 18

reproduction réservée

Il est aussi possible de créer le cube olap directement à partir de Excel et de l'utiliser comme source de données externe.

UTILISER UN CUBE OLAP COMME SOURCE DE DONNÉES EXTERNE : PROCÉDURE

MENU DONNÉES DONNÉES EXTERNES CRÉER UNE REQUÊTE ONGLET CUBE OLAP SÉLECTIONNER LA SOURCE DE DONNÉES SÉLECTIONNER LE FICHIER CUBE PAR

(

DANS EXOSBD)

<CLIC G> SUR la source de données OLAP s'affiche dans la liste LA SÉLECTIONNER <OK> pour valider

<CLIC G> SUR INDIQUER LA POSITION DES DONNÉES DANS EXCEL FAIRE GLISSER LES CHAMPS COMME DANS UN TABLEAU CROISÉ

CRÉER UN CUBE OLAP AYANT LES CARACTÉRISTIQUES SUIVANTES : A PARTIR DE LA BASE DE DONNÉES "COMPTOIR", EXTRAIRE LES CHAMPS : SOCIÉTÉ, PAYS, CONTACT, ADRESSE, VILLE, N°COMMANDE, N°EMPLOYÉ, DATE D'ENVOI, REFÉRENCE, PRIX UNITAIRE, QUANTITÉ, PRODUITS EXERCICE PARMI LES ENREGISTREMENTS PRÉCÉDENTS, NE PRENDRE QUE LES COMMANDES CONCERNANT : LE CAMEMBERT PIERROT, LE CÔTE DE BLAYE, LES ESCARGOTS DE BOURGOGNE, LE PÂTÉ CHINOIS ET LA TARTE AU SUCRE REPRÉSENTER LES DONNÉES SOUS FORME DE TABLEAU CROISÉ DYNAMIQUE

NOTES

Edition I O S

page 19

C C.. LL''E EX XTTR RA AC CTTIIO ON ND D''E EN NR RE EG GIIS STTR RE EM ME EN NTTS S Excel permet de masquer les enregistrements ne répondant pas aux critères spécifiés. Si ces critères sont simples , le filtre automatique peut être utilisé ; s'ils sont complexes ou calculés, le filtre élaboré doit être utilisé.

1 LE FILTRE AUTOMATIQUE Le filtre automatique va permettre d'utiliser les noms de champs comme des listes déroutantes.

FILTRE AUTOMATIQUE (AFFICHER OU ENLEVER): <CLIC G> DANS LES DONNÉES PROCÉDURE

MENU DONNÉES FILTRER FILTRE AUTOMATIQUE

des triangles pointés vers le bas ou sont affichés prés de noms de champ

correspondant à des zones de liste déroutantes

Pour sélectionner un critère, il suffit de dérouler la zone de liste correspondante INFORMATION

SÉLECTIONNER UN CRITÈRE : PROCÉDURE

POINTER SUR LE TRIANGLE BAS À DROITE DU NOM DU CHAMP <CLIC G> POUR DÉROULER LA ZONE DE LISTE <CLIC G> SUR LA VALEUR À PRENDRE EN COMPTE

le triangle du champs sélectionné s'affiche en bleu - seuls les enregistrements répondant aux critères restent affichés ; les autres sont masqués. Les flèches des noms de champs ayant servi au filtre sont en bleu ainsi que les numéros de ligne des enregistrements

des critères s'appliquant à deux champs distincts peuvent être combinés le menu filtrer filtre automatique permet de réafficher tous les enregistrements INFORMATION

OUVRIR BASE.XLS (SOLUTION1 BASE TERMINÉ.XLS) AFFICHER LA FEUILLE NOMMÉE "BASE DE DONNÉES" EXTRAIRE DE CETTE BASE DE DONNÉES (en masquant EXERCICE

les enregistrements ne répondant pas aux

critères) LES ENREGISTREMENTS CONCERNANT LA RÉGION OUEST LES ENREGISTREMENTS DE LA RÉGION OUEST DONT LES VENTES SONT INFÉRIEURES À 100

NOTES

page 20

reproduction réservée

Le filtre peut être personnalisé en déterminant plusieurs valeurs combinées pour un même champ et en utilisant les opérateurs booléens "et" "ou" ainsi que les jokers "?", "*".

FILTRE PERSONNALISÉ : SÉLECTIONNER DANS LA LISTE DÉROULANTE DU FILTRE AUTOMATIQUE PROCÉDURE

INDIQUER LES CRITÈRES

OPERATEURS ET OU

? * ~

intersection - les deux conditions doivent être simultanées union - l'une ou l'autre des conditions est nécessaire et suffisante remplace un caractère (N'IMPORTE LEQUEL) remplace plusieurs caractères précède le ? ou le * considérés comme texte

AFFICHER BASE.XLS (SOLUTION2 BASE TERMINÉ.XLS) EXTRAIRE DE CETTE BASE DE DONNÉES (en masquant

les enregistrements ne répondant pas aux

critères) EXERCICE

LES ENREGISTREMENTS CONCERNANT LA RÉGION OUEST ET LA RÉGION EST DONT LES VENTES SONT INFÉRIEURES À 80

Il est aussi possible de filtrer les données à partir la cellule active ; dans ce cas, la valeur servant au filtre sera la valeur même de cette cellule.

BOUTON FILTRE AUTOMATIQUE : <CLIC G> SUR LA CELLULE QUI SERT A FILTRER (B3) <CLIC G> SUR la base est filtrée. seuls les enregistrements possédant pour le champ donné la même valeur que la cellule active restent affichés : ici seuls les enregistrements appartenant à la région "ouest" restent affichés

PROCÉDURE

Le bouton "filtre automatique" est affiché en personnalisant une barre d'outils et en y ajoutant le bouton figurant dans le groupe de boutons données CONSEIL

NOTES

Edition I O S

page 21

2 LE FILTRE ÉLABORE Le filtre élaboré va offrir plus de possibilités que le filtre automatique mais il est plus complexe et nécessite la détermination de la plage de données, d'une zone de critères (conditions de sélection) et accessoirement d'une zone d'extraction (zone d'affichage).

A)

LA PLAGE DE DONNÉES

La plage de données correspond à l'ensemble des cellules contenant les informations qui devront être traitées. Elle peut comprendre la totalité ou une partie d'une feuille de calcul. On peut utiliser successivement des plages de données différentes de la feuille de travail.

PLAGE DE DONNEES

B)

LA ZONE DE CRITÈRES

La zone de critères est une zone de la feuille de travail dans laquelle vont être indiquées les différentes conditions de sélection des enregistrements.

CREATION DE LA ZONE DE CRITÈRES : PROCÉDURE

INSÉRER PLUSIEURS LIGNES VIERGES AU DESSUS DES DONNÉES ÈRE RECOPIER LA LIGNE DES NOMS DES CHAMPS DE DONNÉES COMME 1 LIGNE INDIQUER LES CRITÈRES DE RECHERCHE SUR LA DEUXIÈME LIGNE ET SUIVANTES

ZONE DE CRITÈRES

NOTES

page 22

reproduction réservée

C)

L'EXTRACTION

L'extraction va permettre de n'afficher dans la zone d'extraction ou dans la zone d'origine (par masquage) que les enregistrements correspondant aux critères définis.

L'EXTRACTION : <CLIC G> DANS LES DONNÉES PROCÉDURE

MENU DONNÉES FILTRER FILTRE ÉLABORÉ <CLIC G> DANS SÉLECTIONNER LA ZONE DE CRITÈRES <OK> POUR VALIDER

AFFICHER BASE.XLS (SOLUTION3 BASE TERMINÉ.XLS) EXTRAIRE DE CETTE BASE DE DONNÉES (en masquant les enregistrements) LES ENREGISTREMENTS CONCERNANT LA RÉGION OUEST DONT LES VENTES SONT INFÉRIEURES À 100 KF ET LA RÉGION EST DONT LES VENTES SONT SUPÉRIEURES À 35 KF EXERCICE ANNULER L'OPÉRATION

évite d'afficher des enregistrement identiques pour les champs concernés INFORMATION

NOTES

Edition I O S

page 23

D)

LA ZONE D'EXTRACTION

C'est la zone de la feuille de travail où viennent s'inscrire les enregistrements répondant aux critères spécifiés. Si elle ne comporte qu'une ligne, celle des noms de champ, le nombre de fiches extraites ne sera pas limité ; si elle comporte plusieurs lignes, y compris la ligne des noms de champ, l'extraction sera limitée à la taille de la zone ainsi définie..

ZONE D'EXTRACTION

L'EXTRACTION : <CLIC G> DANS LES DONNÉES PROCÉDURE

MENU DONNÉES FILTRER FILTRE ÉLABORÉ <CLIC G> DANS SÉLECTIONNER LA ZONE DE CRITÈRES ACTIVER LA CASE <CLIC G> DANS <CLIC G> SUR LA 1ÈRE CELLULE DE DESTINATION <OK> POUR VALIDER

page 24

reproduction réservée

La zone d'extraction ne peut pas être sur une autre feuille de travail

ATTENTION

AFFICHER BASE.XLS (SOLUTION3 BASE TERMINÉ.XLS) IDEM MAIS EN EXTRAYANT LES ENREGISTREMENTS EN A90 DE LA MÊME FEUILLE

EXERCICE

E)

LES CRITERES D'EXTRACTION

Ils peuvent être beaucoup plus élaborés que dans le cas du filtre automatique.

PROCÉDURE

CRITÈRES DE SÉLECTION TEXTE : ?

REMPLACE UN CARACTÈRE (n'importe lequel) * REMPLACE PLUSIEURS CARACTÈRES ~ PRÉCÈDE LE ? OU LE * CONSIDÉRÉS COMME TEXTE

INFORMATION

CRITÈRES DE SÉLECTION CHIFFRE : INDIQUER LA VALEUR DE COMPARAISON ET ÈME L'OPÉRATEUR LOGIQUE SUR LA 2 LIGNE DU CHAMPS DE CRITÈRES (FORMAT INDIFFÉRENT)

les opérateurs standard Excel sont disponibles

Le lien entre les critères présents sur la première ligne de sélection est automatiquement de type "ET" : les enregistrements satisfaisant à la fois à tous les critères seront sélectionnés.

Le lien entre les critères présents sur des lignes successives est automatiquement de type "OU" : les enregistrements satisfaisant l'un ou l'autre des critère seront sélectionnés Pour cerner la valeur d'un champ, remplacer le nom d'un champ non utilisé dans la zone de critères par celui du champ à cerner et indiquer des formules complémentaires dans les deux

CONSEIL

cellules de la même ligne - ex :

EXERCICE

AFFICHER BASE.XLS (SOLUTION3 2ÉME CHAMPS DE CRITÈRES BASE TERMINÉ.XLS) EXTRAIRE DE CETTE BASE DE DONNÉES (en masquant les enregistrements ne répondant pas aux critères) LES ENREGISTREMENTS DE LA RÉGION SUD ENTRE 20 ET 80 KF ET CEUX DE LA RÉGION NORD INFÉRIEURS À 30 ET SUPÉRIEURS À 75 KF

NOTES

Edition I O S

page 25

Les critères utilisés peuvent aussi faire référence à une formule.

CRITÈRES DE SÉLECTION CALCULE : PROCÉDURE

MODIFIER LE NOM DU CHAMP DE CRITÉRE UTILISÉ (EX : VENTES TOTAUX) INDIQUER LA FORMULE LOGIQUE EN FAISANT RÉFÉRENCE À LA PREMIÈRE CELLULE DE VALEURS DU CHAMP

(EX

AFFICHER BASE.XLS (SOLUTION4 BASE TERMINÉ.XLS) EXTRAIRE DE CETTE BASE DE DONNÉES (en masquant les enregistrements ne répondant critères) LES ENREGISTREMENTS DONT LES VENTES SONT SUPÉRIEURES À LA MOYENNE

pas aux

EXERCICE

D D.. LL''A AN NN NU ULLA ATTIIO ON ND DU U FFIILLTTR RE E Les enregistrements ne correspondant pas aux critères sont masqués. Il suffit de réafficher les lignes par le menu ou avec la souris pour afficher la totalité de la base.

PROCÉDURE

SÉLECTIONNER TOUTES LES LIGNES DE LA BASE POINTER ENTRE 2 NUMÉROS DE LIGNE FORME DU POINTEUR REQUISE : <DOUBLE CLIC>

EXERCICE

MENU DONNÉES FILTRER AFFICHER TOUT OU <CLIC G> sur

AFFICHER BASE.XLS (SOLUTION5 BASE TERMINÉ.XLS) RÉAFFICHER TOUS LES ENREGISTREMENTS EXTRAIRE DE CETTE BASE DE DONNÉES (en utilisant une zone d'extraction ENREGISTREMENTS DONT LES VENTES SONT SUPÉRIEURES À LA MOYENNE

DU FILTRE AUTOMATIQUE

sur une autre feuille) LES

NOTES

page 26

reproduction réservée

E E.. LLE E TTR RII D DE ES SE EN NR RE EG GIIS STTR RE EM ME EN NTTS S Le tri est un des outils de type base de données le plus utile et le plus simple à utiliser.

Il permet d'afficher les données d'une plage dans l'ordre des valeurs des données de l'une des colonnes ou lignes de plage (clé de tri), puis de classer les valeurs identiques en fonction des valeurs d'une autre colonne ou ligne (2ème clé) et ainsi jusqu'à trois.

PROCÉDURE

TRI : <CLIC G> DANS UNE CELLULE DE LA COLONNE DE DONNÉES SERVANT DE BASE AU TRI <CLIC G> SUR

POUR TRIER DANS L'ORDRE CROISSANT

<CLIC G> SUR POUR TRIER DANS L'ORDRE DÉCROISSANT les 2 outils sont dans la barre standard AFFICHER BASE.XLS TRIER LES ENREGISTREMENTS PAR NOM PUIS PAR MONTANT DE VENTE DANS L'ORDRE CROISSANT EXERCICE

Le menu permet de faire un tri plus élaboré.

PROCÉDURE

TRI : <CLIC G> DANS LA PLAGE À TRIER MENU DONNÉES ÈRE

DÉROULER LES NOMS DE CHAMPS DE LA 1 CLÉ <CLIC G> SUR LE NOM DU CHAMP VOULU (RÉGION) CHOISIR LE SENS DU TRI (CROISSANT/DÉCROISSANT) ÈME DÉROULER LES NOMS DE CHAMPS DE LA 2 CLÉ <CLIC G> SUR LE NOM DU CHAMP VOULU (VILLE) CHOISIR LE SENS DU TRI (CROISSANT/DÉCROISSANT) ÈME DÉROULER LES NOMS DE CHAMPS DE LA 3 CLÉ <CLIC G> SUR LE NOM DU CHAMP VOULU (NOM) CHOISIR LE SENS DU TRI (CROISSANT/DÉCROISSANT) <OK> POUR VALIDER

Edition I O S

page 27

PARAMÈTRES 1ÈRE CLÉ CLÉ DE TRI PRINCIPALE les données seront triées avec cette clé.

2ÈME CLÉ CLÉ DE TRI SECONDAIRE les données ayant la même valeur pour la 1ère clé seront triées avec cette clé.

3ÈME CLÉ CLÉ DE TRI TERTIAIRE les données ayant la même valeur pour la 1ère clé et la 2ème clé seront triées avec cette 3ème clé. indique à Excel que la 1 ère ligne n'est pas une ligne de données INFORMATION

AFFICHER BASE.XLS TRIER LES ENREGISTREMENTS PAR NOM ET PAR MONTANT DE VENTE DANS L'ORDRE DÉCROISSANT

EXERCICE

FF.. LLE ES SS SO OU US S TTO OTTA AU UX X Comme dans un état de base de données, il est possible de faire des sous totaux par niveau de rupture. Un tri préalable en fonction des sous totaux désirés est alors indispensable.

SOUS TOTAUX : TRIER LES DONNÉES AVEC LES CLÉS

PROCÉDURE

NÉCESSAIRES MENU DONNÉES SOUS TOTAUX DÉROULER LES NOMS DE CHAMP CHOISIR LE CHAMP CHOISIR LA FONCTION CHOISIR LE CHAMP DE CALCUL

<OK> POUR VALIDER Un sous total pour le champ est effectué et les données sont affichées en mode plan Pour effectuer un second niveau de sous total, effectuer la même procédure en choisissant le champ à comptabiliser et en désactivant la case .

ATTENTION

NOTES

page 28

reproduction réservée

Pour annuler les sous-totaux, utiliser la même boite de dialogue mais <CLIC G> sur INFORMATION

AFFICHER BASE.XLS (SOLUTION6 BASE TERMINÉ.XLS) EFFECTUER DES SOUS-TOTAUX PAR RÉGION ET À L'INTÉRIEUR DE CHAQUE RÉGION, PAR VILLE SUR LA TOTALITÉ DES ENREGISTREMENTS DE LA BASE EXERCICE

NOTES

Edition I O S

page 29

NOTES

page 30

reproduction réservée

II. LES TABLES ET TABLEAUX DE DONNÉES Les tables de données permettent de construire des tableaux donnant le résultat des calculs d'une ou plusieurs formules en fonction d'hypothèses faites sur la valeur des variables qui les composent. Si le calcul s'effectue sur une seule variable, plusieurs formules peuvent être analysées simultanément. S'il s'effectue sur deux variables, une seule formule peut être analysée : Le graphique de type XY est alors le plus adapté à la représentation du tableau. Les tables de données peuvent s'utiliser telles quelles afin d'analyser une formule de calcul ou en aval d'une base de données afin d'en extraire des éléments plus synthétiques. Des petites différences existent alors dans la structure de la table d'hypothèses et la manière de l'utiliser Les tableaux croisés permettent de créer un tableau carré présentant une synthèse des information de la base à partir des données d'une base interne ou externe.

A A.. H HY YP PO OTTH HÈ ÈS SE EA AU UN NE EV VA AR RIIA AB BLLE E Le menu DONNÉES TABLE permet de construire une table d'hypothèse concernant une ou plusieurs formules mais à une seule entrée. Une seule des cellules d'entrée doit alors être renseignée (de préférence, celle des colonnes).

HYPOTHESE 1 VARIABLE : PROCÉDURE

SÉLECTIONNER LA PLAGE DE LA TABLE MENU DONNÉES TABLE INDIQUER LA CELLULE D'ENTRÉE (LIGNE OU COLONNE) <OK> POUR VALIDER

PARAMÈTRES PLAGE DE LA TABLE

plage où vont s'inscrire les différentes valeurs incluant la colonne ou la ligne de valeurs et les formules CELLULE D'ENTRÉE EN LIGNE OU COLONNE cellule définissant la variable à analyser

NOTES

Edition I O S

page 31

1 TABLE NON LIÉE A UNE BASE Si la table n'est pas liée à une base de données, les paramètres de création de la table sont les suivants PARAMÈTRES CELLULE D'ENTRÉE (B1) C'est la variable, base de l'analyse de la formule.

LA TABLE(B11.D17) indiquer en première ligne les formules (référence obligatoire a la cellule d'entrée) indiquer dans la première colonne les valeurs prises par la variable

les intersections ligne/colonnes sont renseignées par EXCEL INFORMATION

TABHYP.XLS : PROB 1 A

EXERCICE

OUVRIR LE CLASSEUR TABHYP.XLS AFFICHER LA FEUILLE "PROB 1 A" DÉFINIR UNE TABLE DONNANT POUR DIVERS TAUX (5% À 10%) LA COMMISSION PAYÉE AU REPRÉSENTANT ET LE RÉSULTAT AFFÉRENT POUR L'ENTREPRISE

NOTES

page 32

reproduction réservée

2 TABLE LIÉE A UNE BASE Si la table est liée à une base de données, les paramètres de création sont les suivants PARAMÈTRES LE CHAMPS DE CRITÈRES (A19.A20) recopier le titre de la rubrique à analyser dans une cellule libre la cellule située en dessous doit être vierge et sera la cellule d'entrée LA TABLE D'HYPOTHÈSES (B21.E25) indiquer en première ligne les formules (référence obligatoire aux cellules d'entrée) indiquer dans la première colonne les valeurs prises par la rubrique à analyser.

les intersections ligne/colonnes sont renseignées par EXCEL INFORMATION

TABHYP.XLS : PROB 1 B

PARAMÈTRES =BDMOYENNE(A1..E17;3;A19..A20) A1.E17 4 A20 A19.A20

Formule C23 Champs De Données N° De Rubrique/Critère Cellule d'entrée colonne Champs De Critères

AFFICHER LA FEUILLE "PROB 1 B" DU CLASSEUR TABHYP.XLS DÉFINIR UNE TABLE DONNANT POUR CHAQUE RÉGION L'ANCIENNETÉ MOYENNE, LE TOTAL DES VENTES ET LES VENTES MOYENNES EXERCICE

NOTES

Edition I O S

page 33

B B.. H HY YP PO OTTH HÈ ÈS SE EA AD DE EU UX XV VA AR RIIA AB BLLE ES S La commande DONNÉES TABLE permet de construire une table d'hypothèse concernant une seule formule mais à deux entrées. Il suffit alors de renseigner ces deux cellules.

HYPOTHÈSE 2 VARIABLES : PROCÉDURE

SÉLECTIONNER LA PLAGE DE LA TABLE MENU DONNÉES TABLE INDIQUER LES CELLULES D'ENTRÉE (LIGNE ET COLONNE) <OK> POUR VALIDER

PARAMÈTRES : PLAGE DE LA TABLE

plage où vont s'inscrire les différentes valeurs incluant la colonne ou la ligne de valeurs et les formules CELLULES D'ENTRÉE EN LIGNE ET EN COLONNE définissant les 2 variables de la formule à analyser

1 TABLE NON LIÉE A UNE BASE Si la table n'est pas liée à une base, les paramètres de création sont les suivants : PARAMÈTRES CELLULES D'ENTRÉES (B4, B11) Ce sont les variables qui sont à la base de l'analyse de la formule.

LA TABLE D'HYPOTHÈSES (N3.V15) indiquer la formule (référence obligatoire aux cellules d'entrée) indiquer dans la première ligne les valeurs prises par la première variable indiquer dans la première colonne les valeurs prises par la seconde variable les intersections lignes/colonnes sont renseignées par EXCEL INFORMATION

TABHYP.XLS : PROB 2 A

page 34

reproduction réservée

EXERCICE

AFFICHER LA FEUILLE "PROB 2 A" DU CLASSEUR TABHYP.XLS DÉFINIR UNE TABLE PERMETTANT DE COMPARER LES REMBOURSEMENTS EN FONCTION DU CAPITAL EMPRUNTE (DE 150.000 À 500.000F PAR PAS DE 50.000F)ET DES TAUX D'EMPRUNT (DE 8 À 13,5 %) PAR PAS DE 0,5%)

2 TABLE LIÉE A UNE BASE Si la table est liée à une base de données, les paramètres de création sont les suivants : PARAMÈTRES LE CHAMPS DE CRITÈRES (A67.B68) recopier les titres des deux rubriques à mettre en correspondance cote à cote les cellules situées en dessous doivent être vierges et seront les cellules d'entrée LA TABLE D'HYPOTHÈSES (B70.F74) indiquer dans la première ligne les valeurs prises par la 1ére rubrique à analyser indiquer dans la première colonne les valeurs prises par la 2éme rubrique à analyser les intersections lignes/ colonnes sont renseignées par EXCEL. FORMULE (B70) l'indiquer dans la cellule vide située à l'intersection de la colonne et de la ligne des valeurs prises par les rubriques.

TABHYP.XLS : PROB 2 B

Edition I O S

page 35

PARAMÈTRES =BDSOMME(A1..E65;5;A67..B68) 5A1.E65 5 A68 B68 A67.B68

Formule B70 Champs De Données N° De Rubrique/Critère Cellule d'entrée ligne Cellule d'entrée colonne Champs De Critères

AFFICHER LA FEUILLE "PROB 2 B" DU CLASSEUR TABHYP.XLS DÉFINIR UNE TABLE PERMETTANT D'OBTENIR LES VENTES TOTALES PAR RÉGION ET PAR CATÉGORIE D'OUTILS EXERCICE

C C.. ..LLE ES S TTA AB BLLE EA AU UX XC CR RO OIIS SE ES S Les tableaux croisés offrent une présentation synthétique d'une base de données interne ou externe, en fonction des critères choisis par l'utilisateur. L'assistant "tableaux croisés" permet de créer simplement ces derniers. Le tableau croisé est créé sur sa propre feuille de travail.

1 CREATION DU TABLEAU CROISE La création du tableau croisé s’effectue à l’aide de "l’assistant tableau croisé".

TABLEAUX CROISES : <OUVRIR> LA FEUILLE COMPORTANT LA BASE DE DONNÉES PROCÉDURE

RENSEIGNER LE CAS ÉCHÉANT LA ZONE DE CRITÈRES MENU DONNÉES CONFIRMER L’ORIGINE DES DONNÉES CONFIRMER L’EMPLACEMENT DES DONNÉES SPÉCIFIER L’EMPLACEMENT DE CRÉATION DU TABLEAU CROISÉ <OK> POUR VALIDER FAIRE ALORS GLISSER LES CHAMPS DE LA BARRE D'OUTILS À LEUR POSITION (PAGE, COLONNE, LIGNE, DONNÉES)

la barre d'outils "tableau croisé" est activée dés que le tableau est créé INFORMATION

BARRE D'OUTILS TABLEAU CROISE

NOTES

page 36

reproduction réservée

2 LES ÉTAPES DE LA CREATION L’assistant tableau croisé obéit aux mêmes règles que les autres assistants de Office.

BOUTONS pour accéder à l'étape suivante ou de revenir à l'étape précédente. pour se rendre instantanément à la dernière étape appelle l’aide de la création de tableau croisé annule l’ensemble de l’opération.

TABCROIS.XLS - BASE

ÉTAPE 1

NOTES

Edition I O S

page 37

ÉTAPE 2

ÉTAPE 3

ÉTAPE 4 FAIRE GLISSER LES CHAMPS À LEUR POSITION (PAGE, COLONNE, LIGNE, DONNÉES)

NOTES

page 38

reproduction réservée

OUVRIR LE CLASSEUR TABCROIS.XLS CRÉER À PARTIR DE LA BASE UN TABLEAU CROISÉ PERMETTANT DE LISTER LES RÉSULTATS PAR RÉGION, PAR VILLE ET PAR REPRÉSENTANT EXERCICE

tableau résultant tabcrois.xls - solution1

NOTES

Edition I O S

page 39

3 UTILISATION DU TABLEAU CROISE Le tableau croisé permet d'afficher les valeurs correspondants à certains critères à l'aide des champs de tableau croisé.

TYPES DE CHAMPS : CHAMP PAGE :

<CLIC G> DANS LA ZONE DE LISTE

CHAMP COLONNE :

<CLIC G> DANS LA ZONE DE LISTE

CHAMP LIGNE :

<CLIC G> DANS LA ZONE DE LISTE

NOTES

page 40

reproduction réservée

Pour n'afficher que les valeurs correspondant à certains critères des champs ligne ou colonnes, il suffit de décocher ces valeurs INFORMATION

PERSONNALISATION : <CLIC G> SUR LE CHAMP PROCÉDURE

<CLIC G> SUR ou <DOUBLE CLIC> SUR LE CHAMP la boite de dialogue ci-dessous est affichée

il est aussi possible à ce niveau dans une présentation avec plusieurs niveaux de rupture de rajouter une formule ou de modifier la formule utilisée INFORMATION

TABCROIS.XLS - SOLUTION 1

NOTES

Edition I O S

page 41

Les valeurs du tableau peuvent être actualisées par rapport aux données de la base.

ACTUALISATION : PROCÉDURE

<CLIC G> SUR

DE LA BARRE D'OUTILS "TABLEAU CROISÉ DYNAMIQUE"

OU

<CLIC D> Pour afficher les enregistrements correspondant à une valeur du tableau, il suffit de faire un <DOUBLE CLIC> sur cette valeur CONSEIL

<double clic> sur cette cellule

Affiche ce tableau dans une nouvelle feuille

AFFICHER LE CLASSEUR TABCROIS.XLS AFFICHER LES ENREGISTREMENT DE LA BASE CORRESPONDANT AU TOTAL DE M.HEBERT (110) EXERCICE

4 MODIFICATION DU TABLEAU CROISE Le tableau croisé créé à l'aide de l'assistant peut être aisément modifié aussi bien en ce qui concerne l'affectation des champs en champ page, champ ligne, champ colonne ou champ donnée qu'en ce qui concerne les formules utilisées. (VOIR TABCROIS.XLS - SOLUTION 3) MODIFICATIONS MODIFIER LA PRÉSENTATION :

<FAIRE GLISSER> le champ du tableau à sa nouvelle position

MODIFIER LA FORMULE :

<DOUBLE CLIC> sur le champ dont la formule est à modifier

CHOISIR UNE AUTRE FORMULE AFFICHER LE CLASSEUR TABCROIS.XLS MODIFIER LE TABLEAU CRÉÉ POUR AFFICHER LES MOYENNES PAR VILLE

EXERCICE

NOTES

page 42

reproduction réservée

III. LES FONCTIONS Les fonctions sont des formules intégrées qui permettent de réaliser des calculs mathématiques, financiers, logiques...etc. sur les données saisies dans EXCEL (nombres, caractères...etc.). L'intégration de la formule évite d'avoir à détailler sa formulation puisqu'il suffit alors de n'indiquer que ses éléments variables. Les formules les plus couramment utilisées dans leur domaine existent sous forme de fonction.

A A.. G GÉ ÉN NÉ ÉR RA ALLIITTÉ ÉS S Ne seront étudiées dans le détail que les fonctions à même d'être souvent utilisées dans une organisation ; les fonctions plus spécifiques (trigonométriques, de chaîne...) ne seront qu'évoquées.

1 LA SYNTAXE Une fonction s'exprime toujours sous la forme :

=FONCTION(ARG1;ARG2;ARGN) dans lequel : = ÉGAL indique à EXCEL qu'une fonction suit ; (...) PARENTHÈSES les parenthèses ouvrantes et fermées encadrent les arguments de la fonction ; ARG1 ARG2...ARGN figurent les différentes variables dont la valeur est à renseigner pour que la fonction puisse s'exécuter ; ; POINT VIRGULE caractères séparateurs indispensables.

Une fonction peut être écrite telle que ci-dessus ou insérée à partir de la barre de formule ou du menu INFORMATION

INSÉRER UNE FONCTION : PROCÉDURE

<CLIC G> SUR ou

DANS LA BARRE DE FORMULE

MENU INSERTION SÉLECTIONNER LE TYPE DE FONCTION SÉLECTIONNER LA FONCTION SAISIR OU DÉSIGNER LES DIVERS ARGUMENTS" <OK> POUR VALIDER

NOTES

Edition I O S

page 43

2 LES TYPES DE FONCTION Les fonctions sont différentes selon les domaines qu'elles recouvrent. (investissements, amortissements, emprunts) ; MATHÉMATIQUE (général ou trigonométrique) ; STATISTIQUE (général ou base de données) ; LOGIQUE. FINANCIER

Elles peuvent être directement liées au contenu des cellules, leur champs d'action est alors : L'INFORMATION, la consultation...

LA RECHERCHE, le remplacement...

LA DATE, l'heure...

NOTES

page 44

reproduction réservée

3 LES ARGUMENTS Les arguments donnent à la fonction les informations nécessaires à la détermination et à l'exécution des calculs. Ils sont entre parenthèses et séparés par un point-virgule.

Ils peuvent être de quatre catégories selon les fonctions: (nombre, formule ) TEXTE (suite de caractères alphanumériques entre guillemets) VALEURS LOGIQUES (utilisant les opérateurs logiques) VALEURS D'ERREUR NOMBRE

Ces catégories d'argument peuvent être induites au moyen de : références (à une cellule, une plage, des plages) absolues, relatives ou mixtes noms (d'une plage de cellules) formules matrices

4 LES RÈGLES D'UTILISATION débuter par le signe = (égal) ; mettre les arguments entre parenthèses ; ne pas laisser d'espaces ; utiliser le point-virgule comme séparateur.

Si des fonctions ne sont pas disponibles dans Excel ou si leur résultat est "erreur", aller dans le menu outils, macros complémentaires et activer les options nécessaires (en particulier ) et les fonctions seront disponibles ATTENTION

B B..

LLE ES S FFO ON NC CTTIIO ON NS SD DE EB BA AS SE ED DE ED DO ON NN NÉ ÉE ES S

Les fonctions de base de données sont comparables aux fonctions de liste mais s'appliquent à une structure de base de données et cela sans qu'aucune extraction ne soit nécessaire. Le premier argument est l'adresse ou le nom de la plage de données ; le second argument est le numéro du champs concerné par le calcul (le premier champs ou 1ère colonne reçoit le numéro zéro) ; le troisième est l'adresse ou le nom de la plage contenant les critères.

FONCTBD.XLS

NOTES

Edition I O S

page 45

1 =BDECARTYPE(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne l'écart type des données du champ correspondant à la sélection des critères. La base de l'estimation est un échantillon.

2 =BDECARTYPEP(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne l'écart-type des données du champ correspondant à la sélection des critères. La base de l'estimation est la population entière.

3 =BDMAX(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la valeur maximale des données du champ correspondant à la sélection des critères.

4 =BDMIN(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la valeur minimale des données du champ correspondant à la sélection des critères.

5 =BDMOYENNE(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la valeur moyenne des données du champ correspondant à la sélection des critères.

6 =BDNB(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne le nombre d'enregistrements de la base correspondant à la sélection des critères et ayant une valeur numérique dans le champ indiqué.

NOTES

page 46

reproduction réservée

7 =BDNBVAL(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne le nombre d'enregistrements de la base correspondant à la sélection des critères et ayant une valeur dans le champ indiqué (cellules non vides).

8 =BDPRODUIT(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne le produit des données du champ correspondant à la sélection des critères.

9 =BDSOMME(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la somme des données du champ correspondant à la sélection des critères.

10 =BDVAR(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la variance des données du champ correspondant à la sélection des critères. La base de l'estimation est un échantillon représentatif.

11 =BDVARP(BASE_DONNÉES;CHAMPS;CRITÈRES) Cette fonction donne la variance des données du champ correspondant à la sélection des critères. La base de l'estimation est la population entière.

OUVRIR LE CLASSEUR FONCTIONBD.XLS CALCULER LE NOMBRE D'ENREGISTREMENTS LA VENTE MAXIMUM LA VENTE MINIMUM EXERCICE LA VENTE MOYENNE LA SOMME DES VENTES LA VARIANCE DES VENTES L'ÉCART TYPE DES VENTES D'ABORD POUR L'ENSEMBLE DE LA BASE PUIS POUR LA RÉGION EST PUIS POUR LA RÉGION EST ET LA REGION NORD

NOTES

Edition I O S

page 47

C C.. LLE ES S FFO ON NC CTTIIO ON NS S LLO OG GIIQ QU UE ES S LES FONCTIONS LOGIQUES SONT DE DEUX TYPES : La fonction conditionnelle =SI() Le premier argument est la condition ; le deuxième argument donne la valeur de la cellule lorsque la condition est remplie ; le troisième argument donne la valeur de la cellule lorsque la condition n'est pas remplie.

Les fonctions de type vrai/faux Ce sont des fonctions =SI limitées.

Elles posent une condition définie et affichent la valeur "vrai" si la condition est remplie et "faux" sinon. ( (voir fonclogi.xls) La mise en forme conditionnelle Qui n'est pas vraiment une fonction logique mais qui obéit aux mêmes principes.

1 LE FORMAT CONDITIONNEL Il permet de présenter une cellule avec une mise en forme différente selon sa valeur.

FORMAT CONDITIONNEL : PROCÉDURE

SÉLECTIONNER LA PLAGE OU <CLIC G> SUR LA CELLULE À METTRE EN FORME MENU FORMAT MISE EN FORME CONDITIONNELLE INDIQUER LA CONDITION EN UTILISANT LES LISTES DÉROULANTES ET LE BOUTON DÉSIGNER UNE CELLULE

POUR

INDIQUER LE FORMAT CORRESPONDANT EN CLIQUANT SUR LE BOUTON POUR AJOUTER UNE NOUVELLE CLASSE DE CONDITION POUR VALIDER

CONSEIL

Lorsque pour une condition remplie, vous ne voulez pas afficher des données, pensez toujours à la possibilité d'utiliser la même couleur (y compris le blanc) pour la police et le fond de la cellule

recopier éventuellement la mise en forme sur d'autres cellules avec l'outil INFORMATION

NOTES

page 48

reproduction réservée

EXERCICE

OUVRIR LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "COMMISSIONS" DANS LA COLONNE "VENTES" AFFICHER LES OUTILS SELON LES CRITÈRES SUIVANTS : POLICE VERT FONCÉ POUR LES VENTES SUPÉRIEURES À 50000

OUVRIR LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "OUTILS" DANS LA COLONNE "OUTILS" AFFICHER LES OUTILS SELON LES CRITÈRES SUIVANTS : EXERCICE BLEU FONCÉ POUR LES MARTEAUX ROUGE FONCÉ POUR LES TOURNEVIS VERT FONCÉ POUR LES SCIES

CONSEIL

Appliquez le plus souvent le principe consistant à désigner une cellule plutôt qu'à écrire la valeur dans la formule : désigner une cellule contenant la valeur "marteaux" plutôt qu'écrire "marteaux" dans la mise en forme conditionnelle

OUVRIR LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "CHIFFRE D'AFFAIRES" DANS LA COLONNE "VENTES" AFFICHER LES VENTES SELON LES CRITÈRES SUIVANTS : EXERCICE POLICE BLEU FONCÉ SUR FOND BLEU CLAIR POUR LES VENTES INFÉRIEURES À 50 000 POLICE BLEU CLAIR SUR FOND BLEU FONCÉ POUR LES VENTES ENTRE 50 ET 75 000 POLICE JAUNE CLAIR SUR FOND ROUGE FONCÉ POUR LES VENTES ENTRE 75 ET 100 000

Edition I O S

page 49

2 =SI(TEST;VALEUR_SI_VRAI;VALEUR_SI_FAUX) Cette fonction permet de contrôler la valeur affichée par la cellule en fonction d'une condition. (exemple : taux de commission par type de produits). sept niveaux d'imbrication sont possibles.

PROCÉDURE

ÉTAPE 1 : <CLIC G> SUR LA CELLULE DEVANT RECEVOIR LA CONDITION <CLIC G> SUR DANS LA BARRE DE FORMULE SÉLECTIONNER LA FONCTION SI <OK> POUR VALIDER

ÉTAPE 2 : PROCÉDURE

INDIQUER LA CONDITION INDIQUER LA VALEUR OU LA FORMULE OU SON EMPLACEMENT SI LA CONDITION EST REMPLIE INDIQUER LA VALEUR OU LA FORMULE OU SON EMPLACEMENT SI LA CONDITION N'EST PAS REMPLIE <OK> POUR VALIDER

page 50

reproduction réservée

la fonction SI s'exprime selon la forme :

=SI(TEST;CONDITION OUI;CONDITION NON)

dans lequel : =

indique à EXCEL qu'une fonction suit ;

SI

est le nom de la fonction

(...) PARENTHÈSES

les parenthèses ouvrantes et fermées encadrent les arguments de la fonction ;

TEST

définit le test logique

CONDITION OUI

valeur que prendra la cellule ou formule qui sera calculée si le test est positif

CONDITION NON

valeur que prendra la cellule ou formule qui sera calculée si le test est négatif

; POINT VIRGULE

caractères séparateurs indispensables entre test, condition OUI et condition NON.

AFFICHER LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "COMMISSIONS" FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : EXERCICE 10% POUR LES VENTES INFÉRIEURES À 50 000 F 5% POUR LES VENTES SUPÉRIEURES À 50 000 F

FONCTIONS SI IMBRIQUÉES : A CE NIVEAU, LA FONCTION DOIT ÊTRE ÉCRITE MANUELLEMENT, L'ASSISTANT FONCTION NE PROCÉDURE

FONCTIONNE PLUS ÉTABLIR D'ABORD LA FONCTION SI PRINCIPALE DANS LA CONDITION NON, METTRE UNE NOUVELLE FONCTION SI ETC (JUSQU'À 7 IMBRICATIONS SONT POSSIBLES)

=SI(D2=$J$1;E2*$K$1;SI(D2=$J$2;E2*$K$2;SI(E2=$J$3;E2*$K$3;E2*$K$4))) La source d'erreur la plus fréquente est un nombre incorrect de parenthèses ; heureusement, Excel vous aide en affichant les parenthèses de différentes couleurs et par paires et même en vous proposant de faire seul la correction (à vérifier cependant) ATTENTION

Toujours travailler de manière logique en définissant des catégories de manière informelle ou mieux de manière formelle dans un tableau auquel vous faites référence CONSEIL

NOTES

Edition I O S

page 51

CONSEIL

Écrivez le moins de choses possibles dans la formule et faites au maximum référence à des valeurs, du texte ou formules contenues dans des cellules ; ainsi, vous pourrez faire toutes les modifications que vous souhaiterez sans modifier la formule Si les contions OUI ou NON sont du texte et que vous écrivez la formule, pensez à mettre ce texte entre double guillemets

ATTENTION

CONSEIL

Pour les formules et les fonctions très complexes, si vous n'y arrivez pas du premier coup, vous pouvez mettre " (guillemets français) devant le signe = (égal) de la formule qui est alors acceptée (mais comme texte) ; vous pouvez y revenir plus tard en enlevant les guillemets (pour qu'elle soit de nouveau considérée comme une formule)

AFFICHER LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "OUTILS" FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : EXERCICE 10% POUR LES MARTEAUX 8% POUR LES TOURNEVIS 6% POUR LES PINCES 4% POUR LES SCIES AFFICHER LE CLASSEUR "REPRÉSENTANTS.XLS" AFFICHER LA FEUILLE "CHIFFRE D'AFFAIRES" FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : EXERCICE 10% POUR LA PART DE CHIFFRE D'AFFAIRE RÉALISÉ INFÉRIEURE À 50000F 8% POUR LA PART COMPRISE ENTRE 50 ET 75000F 6% POUR LA PART COMPRISE ENTRE 75 ET 100000F 4% POUR LA PART SUPÉRIEURE À 100000F AFFICHER LE CLASSEUR "EMPRUNT" CONNAISSANT LE MONTANT DE LA MENSUALITÉ DE REMBOURSEMENT POUR UN EMPRUNT DE 300 000 F SUR 8 ANS AU TAUX DE 8% CALCULER LA PART D'INTÉRÊT ET LA PART DE CAPITAL POUR CHAQUE MENSUALITÉ EXERCICE FAIRE LE CUMUL INTÉRÊT ET CAPITAL DANS LES COLONNES ADÉQUATES. NE PLUS AFFICHER LES RÉSULTATS LORSQUE L'EMPRUNT EST REMBOURSÉ FIGER LES VOLETS DE TITRE ET PROTÉGER VOTRE TRAVAIL.

NOTES

page 52

reproduction réservée

3 =FAUX() Cette fonction renvoie la valeur logique faux.

4 =NON(VALEUR_LOGIQUE) Cette fonction inverse la valeur de l'argument ; si celui-ci est vrai, il renvoie faux et rĂŠciproquement.

5 =OU(VALEUR_LOGIQUE1;VALEUR_LOGIQUE2...)

Cette fonction donne la valeur vrai si au moins un argument est vrai, faux sinon.

6 =VRAI() Cette fonction donne la valeur logique vrai.

NOTES

Edition I O S

page 53

D D.. LLE ES S FFO ON NC CTTIIO ON NS SD DE ED DA ATTE EE ETT D D''H HE EU UR RE E Les dates et les heures sont gérées par EXCEL sous la forme de nombres entiers ou décimaux.

Pour être affichés en tant que date ou heure, ils doivent être saisis comme tels ou faire l'objet d'un formatage particulier. Les fonctions de DATE et d'HEURE permettent leur affichage dans une feuille de travail ainsi que tous les calculs liés au temps. Il est nécessaire que la largeur des colonnes soit adaptée au format retenu. (VOIR FONCDATE.XLS) Les numéro_de_séries sont des nombres entiers compris entre 1 (01/01/1900) et 65380 (31/12/2078). Certaines fonctions transforment une date en numéro_de_série ; d'autre extraient d'un numéro_de_série le jour, le mois ou l'année.

Le nombre-temps est une valeur décimale comprise entre 0,000000 (minuit) et 0,999988 (23:59:59). Certaines fonctions transforment une heure en numéro de série ; d'autre extraient d'un numéro de série l'heure, la minute ou la seconde.

FONCDATE.XLS

1 =AUJOURD'HUI() Cette fonction renvoie le numéro_de_série de la date du jour en format standard.

2 =MAINTENANT() Cette fonction permet l'affichage dans une feuille de travail de la date du jour ainsi que de l'heure . Elle ne nécessite pas d'argument. Elle est mise à jour à chaque recalcul du tableau.

3 =ANNEE(NUMÉRO_DE_SÉRIE) Cette fonction donne l'année correspondant au numéro_de_série.

4 =DATE(ANNÉE;MOIS;JOUR) Cette fonction transforme une date spécifiée sous forme d'argument en un numéro_de_série.

5 =DATEVAL(DATE_TEXTE) Cette fonction transforme une date mentionnée sous forme de texte en un numéro_de_série.

L'argument de cette fonction peut être l'adresse d'une cellule. Cette fonction peut faciliter le traitement de données provenant d'un autre logiciel.

NOTES

page 54

reproduction réservée

6 =JOUR(NUMÉRO_DE_SÉRIE) Cette fonction donne le jour correspondant au numéro_de_série spécifié.

7 =JOURSSEM(NUMÉRO_DE_SÉRIE) Cette fonction donne le numéro du jour de la semaine sur la base d'une semaine comprise entre dimanche (1) et samedi (7).

8 =JOURS360(DATE_DÉBUT;DATE_FIN) Cette fonction calcule le nombre de jours séparant deux dates sur la base d'une année de 360 jours.

9 =MOIS(NUMÉRO_DE_SÉRIE) Cette fonction donne le mois correspondant au numéro_de_série spécifié.

OUVRIR LE CLASSEUR "DATES.XLS" SUR UNE BASE DE 360 JOURS PAR AN ET À RAISON DE 30 JOURS PAR MOIS POUR LES MOIS PLEINS (quelque soit leur nombre réel de jours) ET DU NOMBRE RÉEL DE JOURS POUR LES MOIS NON PLEINS, CALCULER LE NOMBRE DE JOURS THÉORIQUE ENTRE LES DATES DE DÉBUT ET DE FIN. EXERCICE CALCULER DANS UN SECOND TEMPS LE NOMBRE DE JOURS OUVRÉS ENTRE CES DEUX DATES

10 =HEURE(NUMÉRO_DE_SÉRIE) Cette fonction donne l'heure correspondant à un numéro de série.

11 =MINUTE(NUMÉRO_DE_SÉRIE) Cette fonction donne les minutes correspondant à un numéro de série.

12 =SECONDE(NUMÉRO_DE_SÉRIE) Cette fonction donne les secondes correspondant à un numéro de série.

13 =TEMPS(HEURE;MINUTES;SECONDES) Cette fonction calcule le numéro_de_série correspondant à l'heure spécifiée comme argument.)

14 =TEMPSVAL(HEURE_TEXTE) Cette fonction transforme une heure, mentionnée sous forme de texte , en un numéro de série.

L'argument de cette fonction peut être l'adresse d'une cellule. Cette fonction peut faciliter le traitement de données provenant d'un autre logiciel.

NOTES

Edition I O S

page 55

NOTES

page 56

reproduction réservée

IV. LES MACROS COMMANDES Les macro-commandes sont des outils beaucoup moins nécessaires qu'autrefois dans l'utilisation d'un tableur ; cependant, elles vont permettre d'utiliser des boucles afin de tester les valeurs de cellule et d'automatiser des processus en prenant des orientations différentes en fonction du résultat de ces tests.

INFORMATION

La création de macro-commandes dans Excel comme dans les autres modules de la suite OFFICE s'effectue avec le langage VBA (Visual Basic for Applications) qui est un langage de programmation complet mais suffisamment complexe

Il s'agit ici de développement d'applications et l'utilisateur doit alors se plier à un certain nombre de contraintes avant de se lancer dans la programmation : FAIRE UNE ÉTUDE PRÉALABLE ET REPRÉSENTER GRAPHIQUEMENT SA STRUCTURE OBSERVER LES RÈGLES DE L'ART DANS LE DÉVELOPPEMENT DOCUMENTER SON CODE AFIN QU'IL PUISSE ÊTRE REPRIS AVEC UN MINIMUM DE FLOTTEMENT PAR QUELQU'UN D'AUTRE

A A..

LLE ES SM MA AC CR RO OS SS SIIM MP PLLE ES S

1 L'ENREGISTREMENT D'UNE MACRO-COMMANDE Pour enregistrer une macro-commande, il suffit d’activer l’enregistrement et d’indiquer à Excel la suite de commandes à retenir en les exécutant une par une.

PROCÉDURE

DÉMARRER L’ENREGISTREMENT D’UNE MACRO : <CTRL> <ORIGINE> POUR SE POSITIONNER EN A1 MENU OUTILS - MACROS DONNER UN NOM À LA MACRO POUR VALIDER

ou AFFICHER LA BARRE D'OUTILS VISUAL BASIC

<CLIC G> SUR ACCEPTER OU MODIFIER LE NOM PROPOSÉ POUR LA MACRO ASSOCIER ÉVENTUELLEMENT LA MACRO À UNE BARRE D’OUTILS, UN MENU OU UNE COMBINAISON DE TOUCHES DU CLAVIER (Il est préférable de le faire postérieurement) POUR VALIDER

Le curseur prend la forme d’une cassette qui indique le mode enregistrement de macro et la barre d’outils macro s’affiche avec un bouton Pause et un bouton Stop.

EFFECTUER UNE À UNE, TOUTES LES OPÉRATIONS QUI DOIVENT CONSTITUER LA MACRO

Edition I O S

page 57

SUSPENDRE OU STOPPER L’ENREGISTREMENT : PROCÉDURE

Barre d'outils d'enregistrement : <CLIC G> POUR STOPPER L’ENREGISTREMENT DE LA MACRO La macro est maintenant disponible à l’exécution: <CLIC G>

POUR PASSER DES RÉFÉRENCES ABSOLUES À DES RÉFÉRENCES RELATIVES ET RÉCIPROQUEMENT

2 L'AFFECTATION D'UNE MACRO A UN OUTIL Pour accéder facilement à la macro, l'idéal est de rajouter un outil spécifique dans la barre d'outils standard.

CRÉER UN OUTIL SPÉCIFIQUE : <CLIC D> DANS UNE BARRE D'OUTIL PROCÉDURE

PERSONNALISER AFFICHER L'ONGLET CHOISIR LA CATÉGORIE "MACRO" CHOISIR L'OUTIL FAIRE GLISSER L'OUTIL

DANS LA BARRE D'OUTILS

<CLIC D> SUR L'OUTIL NOUVELLEMENT INSÉRÉ DANS LA BARRE

POUR CHANGER L'IMAGE, LE

TEXTE ASSOCIÉ ET AFFECTER LA MACRO AFFECTER UNE MACRO SÉLECTIONNER LA MACRO-COMMANDE POUR VALIDER

NOTES

page 58

reproduction réservée

INFORMATION

Les commandes de personnalisation d'un outil sont accessibles en choisissant la commande personnaliser du menu contextuel de gestion des barres d'outils et en faisant un <CLIC D>sur l'outil à modifier en gardant cette boite de dialogue "personnaliser" ouverte

3 L’EXÉCUTION D’UNE MACRO-COMMANDE Les macro-commandes peuvent donc être rappelées à tout moment par leur nom ou, s'ils ont été paramétrés, par le bouton d’une barre d’outils ou l’option d’un menu auquel la macro est associée.

PROCÉDURE

<CLIC G> SUR LE BOUTON DE LA BARRE D'OUTILS VISUAL BASIC ou <CLIC G> SUR L'OUTIL ASSOCIE

MENU OUTILS MACRO SÉLECTIONNER LA MACRO DÉSIRÉE

Il est souvent préférable de se placer dans un environnement de départ de l'exécution identique à celui de l'enregistrement INFORMATION

CONSEIL

Si le champ d'application de la macro n'est pas limité au classeur ouvert, enregistrer la macro dans le classeur de macros personnelles ; un classeur de macros appelé "perso.xls" est à partir de ce moment automatiquement chargé dans Excel au démarrage ; c'est un classeur caché qui contient l'ensemble des macros personnelles et les rend disponibles à tout moment

NOTES

Edition I O S

page 59

CRÉER UN NOUVEL OUTIL D'IMPRESSION AUQUEL VOUS ATTACHEREZ UNE MACRO QUI :

 MODIFIERA LA MISE EN PAGE DE LA MANIÈRE SUIVANTE : PAYSAGE - 1 PAGE LARGEUR/1 PAGE EXERCICE

HAUTEUR - CENTRER HORIZONTALEMENT ET VERTICALEMENT - MARGES HAUT BAS GAUCHE DROITE À 1,5 CM

 IMPRIMERA  REMETTRA LA MISE EN PAGE DANS L'ÉTAT D'ORIGINE  NOMMER LA MACRO PAYSAGE Tester la macro avec le classeur "dates"

EXERCICE

CRÉER UNE MACRO QUI PERMET D'UTILISER DIRECTEMENT UNE SECONDE IMPRIMANTE OU UN FAX (sans avoir à aller à chaque fois dans la boite de dialogue d'impression et sans avoir à remettre à chaque fois l'imprimante d'origine) NOMMER LA MACRO IMPRESSION

NOTES

page 60

reproduction réservée

B B.. LLE ES SM MA AC CR RO OS SC CO OM MP PLLE EX XE ES S Il est aussi possible, pour les utilisateurs avertis, de programmer des routines plus complexes et même d'utiliser Excel comme base d'une application automatisée.

L'écriture du code s'effectue à l'aide de Visual Basic pour Applications LANCER VISUAL BASIC: PROCÉDURE

ATTENTION

MENU OUTILS MACRO

Il faut ici prendre garde de ne pas faire avec Excel des choses qu'il ne sait pas bien faire sous prétexte que c'est le seul logiciel possible connu par l'utilisateur. Souvent, Access sera un bien meilleur outil de programmation et de structuration des données alors qu'Excel donnera toute sa puissance dans la synthése des données sous forme de tableaux ou de graphiques

1 LES OBJETS Un objet représente un élément de l'application Excel : un classeur une feuille de calcul une cellule un bouton …

PROCÉDURE

FAIRE RÉFÉRENCE À LA FEUIL2: WORKSHEETS(2) ou WORKSHEETS("FEUIL2") Tous les objets de même type forment une collection (toutes les feuilles de calcul d'un classeur). Chaque élément de la collection est alors identifié par son nom ou par un index.

AVANCÉ

Chaque objet peut avoir ses propres objets. Excel est constitué de classeurs eux-mêmes constitués de feuilles elles-mêmes constituées de cellules.

FAIRE RÉFÉRENCE À LA CELLULE A3 DE LA FEUILLE 2 DU CLASSEUR 1 : APPLICATION.W ORKBOOKS(1).W ORKSHEETS("FEUIL2").RANGE("A3") PROCÉDURE

NOTES

Edition I O S

page 61

2 LES PROPRIÉTÉS Une propriété est propre à un l'objet.

La valeur d'une cellule La couleur d'une cellule La taille d'une cellule Les objets sont séparés de leurs propriétés par un point : Cellule.Propriété=valeur : Pour mettre la valeur 1 dans la cellule A3, on peut écrire : Range("A3").Value = 1 EXEMPLE

Une propriété peut faire référence à un état de l'objet.

Pour masquer la feuille de calcul "Feuil2", on peut écrire : Worksheets("Feuil3").Visible = False EXEMPLE

3 LES MÉTHODES Une méthode est une opération que réalise un objet : Ouvrir Fermer Sélectionner Enregistrer Imprimer Effacer…

Les objets sont séparés de leurs méthodes par un point.

SÉLECTIONNER LA FEUILLE DE CALCUL NOMMÉ "FEUIL2" : WORKSHEETS("FEUIL2").SELECT PROCÉDURE

Lorsque l'on fait appel à plusieurs propriétés ou méthodes d'un même objet, on fait appel à l'instruction With Objet Instructions End With..

EXEMPLE

Pour mettre la valeur 1 dans la cellule A3, la police en gras et en italique et copier la cellule : With Worksheets("Feuil2").Range("A1") .Value = 1 .Font.Bold = True .Font.Italic = True .Copy End With

NOTES

page 62

reproduction réservée

4 LES ÉVÈNEMENTS Pour qu'une macro se déclenche, il faut qu'un se produise évènement. Les principaux objets auxquels un événement peut être attaché sont : Un classeur Une feuille de travail Une boite de dialogue

EXEMPLE

Le code suivant lance la procédure "essai" à l'ouverture du classeur : Private Sub Workbook_Open() essai End Sub

A)

LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKBOOK

ACTIVATE

quand le classeur ou une feuille est activé

BEFORECLOSE

avant que le classeur ne soit fermé

BEFOREPRINT

avant l'impression du classeur

BEFORESAVE

avant l'enregistrement du classeur

DEACTIVATE

quand le classeur ou une feuille est désactivé

NEW SHEET

lorsqu'une nouvelle feuille est créée

OPEN

à l'ouverture du classeur

SHEETACTIVATE

lorsqu'une feuille est activée

SHEETBEFOREDOUBLECLICK

lors d'un double-clic

SHEETBEFORERIGHTCLICK

lors d'un clic avec le bouton droit de la souris

SHEETCALCULATE

après le recalcul d'une feuille de calcul

SHEETCHANGE

lors de la modification d'une cellule

SHEETDEACTIVATE

lorsqu'une feuille est désactivée

SHEETFOLLOW HYPERLINK

lors d'un clic sur un lien hypertexte

SHEETSELECTIONCHANGE

lors d'un changement de sélection sur une feuille de calcul

WINDOW ACTIVATE

lorsqu'un classeur est activé

WINDOW DEACTIVATE

lorsqu'un classeur est désactivé

NOTES

Edition I O S

page 63

La création d'une procédure évènementielle liée à une feuille de calcul est identique.

B)

LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKSHEET

ACTIVATE

quand une feuille est activée

BEFOREDOUBLECLICK

lors d'un double-clic

BEFORERIGHTCLICK

lors d'un clic avec le bouton droit de la souris

CALCULATE

après le recalcul de la feuille de calcul

CHANGE

lors de la modification d'une cellule

DEACTIVATE

quand une feuille est désactivée

FOLLOW HYPERLINK

lors d'un clic sur un lien hypertexte

SELECTIONCHANGE

lors d'un changement de sélection

le paramètre "Cancel", peut annuler la procédure;

EXEMPLE

EXEMPLE

Pour empêcher l'impression d'un classeur, on utilisera : Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True End Sub Pour récupérer la valeur d'une cellule modifiée, on utilisera : Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value End Sub

C)

LES CLASSEURS

Un classeur est désigné par "Workbook". Les classeurs peuvent être ouverts, fermés, enregistrés, activés, masqués, supprimés ....

AJOUTER UN NOUVEAU CLASSEUR

Workbooks.Add

FERMER UN CLASSEUR.

Workbooks("NomDuClasseur.xls").Close

FERMER LE CLASSEUR ACTIF.

ActiveWorkbook.Close

OUVRIR UN CLASSEUR.

Workbooks.Open "c:\Chemin\NomDuFichier.xls"

ACTIVER UN CLASSEUR.

Workbooks("NomDuClasseur.xls").Activate

NOTES

page 64

reproduction réservée

D)

LES FEUILLES DE CALCUL

Une feuille de calcul est désignée par "Worksheet".

SÉLECTIONNER UNE FEUILLE

Worksheets("Feuil1").Select

RÉCUPÉRER LE NOM DE LA FEUILLE ACTIVE DANS UNE VARIABLE.

MaFeuille = ActiveSheet.Name

MASQUER UNE FEUILLE.

Worksheets("Feuil1").Visible = False

SUPPRIMER UNE FEUILLE.

Worksheets("Feuil1").Delete

'COPIER LA FEUIL2 DE CLASSEUR.XLS

Workbooks("Classeur.xls").Worsheets("Feuil2").Copy

DANS UN NOUVEAU CLASSEUR

E)

LES CELLULES

Une plage de cellules est désignée par "Range". faire référence à la plage de cellule "A2:C10" : Range("A2:C10") EXEMPLE

Effacer les données et le mise en forme de la plage de cellule "A1:B10" : Range("A2:C10").Clear EXEMPLE

EFFACER LES DONNÉES ET LE MISE EN FORME DE LA PLAGE DE CELLULE "A1:D7" DU CLASSEUR "DATES" ENREGISTRER LA MACRO SOUS LE NOM "MACRO1" EXERCICE

Sélectionner les plages de cellule " A2:C10" et "D3:G20" : Range("A2:C10, D3:G20").Select EXEMPLE

Pour faire référence à une seule cellule, on utilisera l'objet Range("Référence de la cellule) ou Cells(Numéro de ligne, Numéro de colonne).

EXEMPLE

Écrire 3 dans la cellule "A6" : Range("A6").Value = 3 ou Cells(6, 1).Value = 3 ÉCRIRE 9 DANS LES CELLULES "A1 À D7" DU CLASSEUR "DATES" ENREGISTRER LA MACRO SOUS LE NOM "MACRO2"

EXERCICE

NOTES

Edition I O S

page 65

F)

LE BOUTON DE COMMANDE

Une macro peut également être lancée en cliquant sur un élément graphique (image, zone de texte, objet WordArt, rectangle ...).

CRÉER UN BOUTON DE COMMANDE: PROCÉDURE

DESSINER UN BOUTON AVEC L'OUTIL DE LA BARRE "DESSIN METTRE ÉVENTUELLEMENT EN FORME LE BOUTON <CLIC D> SUR LE BOUTON ASSOCIER UNE MACRO

DANS LE CLASSEUR "DATES" CRÉER 2 BOUTONS POUR CHACUNE DES MACROS 1 ET 2 CRÉÉES CIDESSUS

EXERCICE

5 LES CONDITIONS : IF THEN END IF Les conditions déterminent la valeur que prennent les variables : CONDITION IF CONDITION THEN VALEUR VRAI arrêter une procédure appeler une procédure quitter une boucle atteindre une étiquette.

Si la valeur vraie possède plusieurs lignes d'instructions, la syntaxe devient If Condition Then Valeur vraie End If INFORMATION

6 LES BOUCLES : FOR NEXT Les boucles permettent de répéter un nombre de fois défini un bloc d'instructions. Elles utilisent une variable qui est incrémentée ou décrémentée à chaque répétition.

A l'intérieur d'un bloc d'instruction For Next, l'instruction Exit For, peut quitter la boucle avant que la variable n'est atteint sa dernière valeur INFORMATION

NOTES

page 66

reproduction réservée

7 LES BOUCLES CONDITIONNELLES: DO LOOP WHILE La boucle Do While condition Loop exécute un bloc d'instruction tout pendant que la condition est vraie.

Dans la boucle Do Loop While condition, le bloc d'instruction est exécuté une fois avant que la condition soit testée.

Pour sortir d'une boucle, on utilise l'instruction Exit Do INFORMATION

OUVRIR LE CLASSEUR "MATRICE TABLEAU" DU DOSSIER "ÉXOSEXCBD" FAIRE UNE MACROCOMMANDE PERMETTANT DE MENSUALISER PAR DIXIÈME LE TOTAL DE CHAQUE LIGNE FAIRE UNE MACROCOMMANDE PERMETTANT DE MENSUALISER PAR DOUZIÈME LE TOTAL DE CHAQUE EXERCICE LIGNE ASSOCIER CHAQUE MACROCOMMANDE À UN BOUTON

EXERCICE

OUVRIR LE CLASSEUR "MATRICE CONSO" DU DOSSIER "ÉXOSEXCBD" FAIRE UNE MACROCOMMANDE PERMETTANT DE CONSOLIDER LES DONNÉES DE TOUS LES TABLEAUX CRÉÉS À PARTIR DU CLASSEUR "MATRICE TABLEAU" EN EN FAISANT SIMPLEMENT LA LISTE DANS LA FEUILLE "LISTE" DE CE CLASSEUR

NOTES

Edition I O S

page 67

NOTES

page 68

reproduction réservée

V. DOSSIER PÉDAGOGIQUE A A.. C CO ON NV VE ER RS S..TTX XTT

EXERCICE

OUVRIR AVEC LE BLOC NOTES LE FICHIER "CONVERS.TXT" SÉLECTIONNER LES DONNÉES ET LES COPIER DANS LE PRESSE-PAPIERS OUVRIR DANS EXCEL LE FICHIER CONVERS.XLS COLLER LES DONNÉES EN A1 DE LA FEUILLE EXERCICE

B B.. R RE EQ QU UE ETTE E 11 ((..X XLLS S // ..D DQ QY Y))

EXERCICE

AFFICHER DANS UN NOUVEAU CLASSEUR LES ENREGISTREMENTS EXTRAITS DE LA BASE ACCESS.MDB POUR LES CHAMPS "SOCIÉTÉ", "CONTACT", "ADRESSE" ET "CODEPOSTAL MODIFIER LA REQUÊTE ET NE PRENDRE QUE LES ENREGISTREMENTS DONT LE CODE POSTAL EST SUPÉRIEUR OU ÉGAL À 10000 ET STRICTEMENT INFÉRIEUR À 45000 ET TRIER PAR SOCIÉTÉ

Edition I O S

page 69

C C.. R RE EQ QU UE ETTE E 22 ((..X XLLS S // ..D DQ QY Y)) A PARTIR DE LA BASE DE DONNÉES "COMPTOIR", EXTRAIRE LES CHAMPS : SOCIÉTÉ, PAYS, CONTACT, ADRESSE, VILLE, N°COMMANDE, N°EMPLOYÉ, DATE D'ENVOI, REFÉRENCE, PRIX UNITAIRE, QUANTITÉ EXERCICE

POUR TOUS LES ENREGISTREMENTS DONT LE NOM DE PAYS COMMENCE PAR UNE LETTRE SUPÉRIEUR OU ÉGALE À F ET DONT L'ANNÉE D'ENVOI EST 1995 ENREGISTRER LA REQUÊTE SOUS LE NOM "ESCARGOT" DANS LE DOSSIER OU SE TROUVE LES EXERCICES EXCEL

NOTES

page 70

reproduction réservée

D D.. R RE EQ QU UE ETTE E 33 ((..X XLLS S // ..D DQ QY Y))

EXERCICE

RAJOUTER LA TABLE "PRODUITS" PARMI LES ENREGISTREMENTS PRÉCÉDENTS, NE PRENDRE QUE LES COMMANDES CONCERNANT : LE CAMEMBERT PIERROT, LE CÔTE DE BLAYE, LES ESCARGOTS DE BOURGOGNE, LE PÂTÉ CHINOIS ET LA TARTE AU SUCRE - RENVOYER LES DONNÉES VERS EXCEL

NOTES

Edition I O S

page 71

E E.. LLIIA AIIS SO ON NA AC CC CE ES SS S..X XLLS S LANCER ACCESS OUVRIR LA BASE DE DONNÉES "COMPTOIR" COPIER LA TABLE PRODUITS DANS UNE FEUILLE "EXCEL" EN CONSERVANT UNE LIAISON ENREGISTRER LE CLASSEUR EXCEL EXERCICE MODIFIER LA TABLE PRODUITS DANS ACCESS FERMER ACCESS OUVRIR LE CLASSEUR PRÉCÉDENT DANS EXCEL

FF.. R RE EQ QU UE ETTE EC CU UB B ..X XLLS S // CRÉER UN CUBE OLAP AYANT LES CARACTÉRISTIQUES SUIVANTES : A PARTIR DE LA BASE DE DONNÉES "COMPTOIR", EXTRAIRE LES CHAMPS : SOCIÉTÉ, PAYS, CONTACT, ADRESSE, VILLE, N°COMMANDE, N°EMPLOYÉ, DATE D'ENVOI, REFÉRENCE, PRIX UNITAIRE, QUANTITÉ, PRODUITS EXERCICE PARMI LES ENREGISTREMENTS PRÉCÉDENTS, NE PRENDRE QUE LES COMMANDES CONCERNANT : LE CAMEMBERT PIERROT, LE CÔTE DE BLAYE, LES ESCARGOTS DE BOURGOGNE, LE PÂTÉ CHINOIS ET LA TARTE AU SUCRE REPRÉSENTER LES DONNÉES SOUS FORME DE TABLEAU CROISÉ DYNAMIQUE

NOTES

page 72

reproduction réservée

G G.. B BA AS SE E..X XLLS S

EXTRAIRE DE CETTE BASE DE DONNÉES (EN MASQUANT LES ENREGISTREMENTS NE RÉPONDANT PAS AUX CRITÈRES) : 1 - LES ENREGISTREMENTS CONCERNANT LA RÉGION OUEST (SOLUTION 1) EXERCICE

2 - LES ENREGISTREMENTS DE LA RÉGION OUEST DONT LES VENTES SONT INFÉRIEURES À 100 (SOLUTION 1) 3 - LES ENREGISTREMENTS DE LA RÉGION OUEST DONT LES VENTES SONT INFÉRIEURES À 100 ET LES ENREGISTREMENTS DE LA RÉGION EST DONT LES VENTES SONT SUPÉRIEURES À 35 (SOLUTION 2) 4 - LES ENREGISTREMENTS DONT LES VENTES SONT SUPÉRIEURES À LA MOYENNE (SOLUTION 3) 5 - IDEM 4 MAIS EN UTILISANT UNE ZONE D'EXTRACTION AU LIEU DE MASQUER LES ENREGISTREMENTS (SOLUTION 4) 5 - EFFECTUER DES SOUS-TOTAUX PAR RÉGION ET À L'INTÉRIEUR DE CHAQUE RÉGION, PAR VILLE SUR LA TOTALITÉ DES ENREGISTREMENTS DE LA BASE. (SOLUTION 5)

NOTES

Edition I O S

page 73

H H.. FFO ON NC CTTB BD D..X XLLS S

CALCULER LE NOMBRE D'ENREGISTREMENTS, LA VENTE MAXIMUM LA VENTE MINIMUM EXERCICE LA VENTE MOYENNE LA SOMME DES VENTES LA VARIANCE DES VENTES L'ECART TYPE DES VENTES D'ABORD POUR L'ENSEMBLE DE LA BASE PUIS POUR LA RÉGION EST PUIS POUR LA RÉGION EST ET LA REGION NORD

NOTES

page 74

reproduction réservée

II.. TTA AB BH HY YP P –– 11A A

DÉFINIR UNE TABLE DONNANT POUR DIVERS TAUX (5 À 10%) LA COMMISSION PAYÉE AU REPRÉSENTANT ET LE RÉSULTAT AFFÉRENT POUR L'ENTREPRISE EXERCICE

NOTES

Edition I O S

page 75

JJ.. TTA AB BH HY YP P –– 11B B

DÉFINIR UNE TABLE DONNANT POUR CHAQUE RÉGION L'ANCIENNETÉ MOYENNE, LE TOTAL DES VENTES ET LES VENTES MOYENNES

EXERCICE

NOTES

page 76

reproduction réservée

K K.. TTA AB BH HY YP P –– 22A A

DÉFINIR UNE TABLE PERMETTANT DE COMPARER LES REMBOURSEMENTS EN FONCTION DU CAPITAL EMPRUNTE (DE 150.000 À 500.000F PAR PAS DE 50.000F)ET DES TAUX D'EMPRUNT (DE 8 À 13,5 %) PAR PAS DE 0,5%) EXERCICE

NOTES

Edition I O S

page 77

LL.. TTA AB BH HY YP P –– 22B B

DÉFINIR UNE TABLE PERMETTANT D'OBTENIR LES VENTES TOTALES PAR RÉGION ET PAR CATÉGORIE D'OUTILS EXERCICE

NOTES

page 78

reproduction réservée

M M.. TTA AB BC CR RO OIIS S..X XLLS S

EXERCICE

1 CRÉER À PARTIR DE CETTE BASE UN TABLEAU CROISÉ PERMETTANT DE LISTER LES RÉSULTATS PAR RÉGION, PAR VILLE ET PAR REPRÉSENTANT (SOLUTION 1) 2 MODIFIER LE TABLEAU CRÉÉ POUR AFFICHER LES MOYENNES PAR VILLE (SOLUTION 2) 3 AFFICHER LES ENREGISTREMENT DE LA BASE CORRESPONDANT AU TOTAL DE MARSEILLE SOIT 238 (SOLUTION 3)

Edition I O S

page 79

N N.. V VE EN NTTE ES SC CA AFFE E..X XLLS S PRÉSENTER SOUS FORME DE TABLEAU CROISÉ LES VENTES 1995 PAR RÉGION POUR L'EUROPE

EXERCICE

PRÉSENTER SOUS FORME DE TABLEAU CROISÉ LES VENTES 1996 PAR RÉGION POUR L'EUROPE

EXERCICE

NOTES

page 80

reproduction réservée

O O.. R RE EP PR RÉ ÉS SE EN NTTA AN NTTS S -- C CO OM MM MIIS SS SIIO ON NS S

AFFICHER LA FEUILLE "COMMISSIONS" DANS LA COLONNE "VENTES", AFFICHER LES OUTILS SELON LES CRITÈRES SUIVANTS : POLICE VERT FONCÉ POUR LES VENTES SUPÉRIEURES À 50000 FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR EXERCICE CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : 10% POUR LES VENTES INFÉRIEURES À 50 000 5% POUR LES VENTES SUPÉRIEURES À 50 000

NOTES

Edition I O S

page 81

P P.. R RE EP PR RÉ ÉS SE EN NTTA AN NTTS S -- O OU UTTIILLS S

AFFICHER LA FEUILLE "OUTILS" DANS LA COLONNE "OUTILS", AFFICHER LES OUTILS SELON LES CRITÈRES SUIVANTS : BLEU FONCÉ POUR LES MARTEAUX ROUGE FONCÉ POUR LES TOURNEVIS EXERCICE VERT FONCÉ POUR LES SCIES FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : 10% POUR LES MARTEAUX 8% POUR LES TOURNEVIS 6% POUR LES PINCES 4% POUR LES SCIES

NOTES

page 82

reproduction réservée

Q Q.. R RE EP PR RÉ ÉS SE EN NTTA AN NTTS S -- C CH HIIFFFFR RE ED D''A AFFFFA AIIR RE ES S

AFFICHER LA FEUILLE "CHIFFRE D'AFFAIRES" DANS LA COLONNE "VENTES", AFFICHER LES VENTES SELON LES CRITÈRES SUIVANTS : POLICE BLEU FONCÉ SUR FOND BLEU CLAIR POUR LES VENTES INFÉRIEURES À 50 000 F POLICE BLEU CLAIR SUR FOND BLEU FONCÉ POUR LES VENTES ENTRE 50 ET 75 000 F EXERCICE POLICE JAUNE CLAIR SUR FOND ROUGE FONCÉ POUR LES VENTES ENTRE 75 ET 100 000 F FAIRE UNE COLONNE "COMMISSIONS" CALCULANT LA COMMISSION DES REPRÉSENTANTS POUR CHAQUE VENTE SELON LES CONDITIONS SUIVANTES : 10% POUR LA PART DE CHIFFRE D'AFFAIRE RÉALISÉ INFÉRIEURE À 50000F 8% POUR LA PART COMPRISE ENTRE 50 ET 75000F 6% POUR LA PART COMPRISE ENTRE 75 ET 100000F 4% POUR LA PART SUPÉRIEURE À 100000F

NOTES

Edition I O S

page 83

R R.. E EM MP PR RU UN NTT

CONNAISSANT LE MONTANT DE LA MENSUALITÉ DE REMBOURSEMENT POUR UN EMPRUNT DE 300 000 SUR 8 ANS AU TAUX DE 8% CALCULER LA PART D'INTÉRÊT ET LA PART DE CAPITAL POUR CHAQUE MENSUALITÉ FAIRE LE CUMUL INTÉRÊT ET CAPITAL DANS LES COLONNES ADÉQUATES. EXERCICE NE PLUS AFFICHER LES RÉSULTATS LORSQUE L'EMPRUNT EST REMBOURSE FIGER LES VOLETS DE TITRE ET PROTÉGER VOTRE TRAVAIL

NOTES

page 84

reproduction réservée

S S.. D DA ATTE ES S

SUR UNE BASE DE 360 JOURS PAR AN ET À RAISON DE 30 JOURS PAR MOIS POUR LES MOIS PLEINS (Quelque soit leur nombre réel de jours) ET DU NOMBRE RÉEL DE JOURS POUR LES MOIS NON PLEINS, CALCULER LE NOMBRE DE JOURS THÉORIQUE ENTRE LES DATES DE DÉBUT ET DE FIN. EXERCICE

CALCULER DANS UN SECOND TEMPS LE NOMBRE DE JOURS OUVRÉS ENTRE CES DEUX DATES

NOTES

Edition I O S

page 85

TT.. M MA AC CR RO OP PA AY YS SA AG GE E CRÉER UN NOUVEL OUTIL D'IMPRESSION AUQUEL VOUS ATTACHEREZ UNE MACRO QUI :

 MODIFIERA LA MISE EN PAGE DE LA MANIÈRE SUIVANTE : PAYSAGE - 1 PAGE LARGEUR/1 PAGE EXERCICE

HAUTEUR - CENTRER HORIZONTALEMENT ET VERTICALEMENT - MARGES HAUT BAS GAUCHE DROITE À 1,5 CM

 IMPRIMERA  REMETTRA LA MISE EN PAGE DANS L'ÉTAT D'ORIGINE Tester la macro avec le classeur avec le classeur "dates

NOTES

page 86

reproduction réservée

U U.. M MA AC CR RO O IIM MP PR RE ES SS SIIO ON N CRÉER UNE MACRO QUI PERMET D'UTILISER DIRECTEMENT UNE SECONDE IMPRIMANTE OU UN FAX (sans avoir à aller à chaque fois dans la boite de dialogue d'impression et sans avoir à remettre à chaque fois l'imprimante d'origine) EXERCICE

INFORMATION

Sub impression() ' impression Macro Range("A1").Select Application.ActivePrinter = "Acrobat Distiller sur Ne02:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.ActivePrinter = \\SERVEUR2003\Canon LBP-8 III Plus sur Ne03: End Sub

V V.. M MA AC CR RO O11

EXERCICE

INFORMATION

EFFACER LES DONNÉES ET LE MISE EN FORME DE LA PLAGE DE CELLULE "A1:D7" DU CLASSEUR "DATES" ENREGISTRER LA MACRO SOUS LE NOM "MACRO1" ANNULER L'ACTION Sub macro1() ' macro1 Macro Range("A1:D7").Clear End Sub

NOTES

Edition I O S

page 87

W W.. M MA AC CR RO O 22 ÉCRIRE 9 DANS LES CELLULES "A1 À D7" DU CLASSEUR "DATES" ENREGISTRER LA MACRO SOUS LE NOM "MACRO2" ANNULER L'ACTION EXERCICE

INFORMATION

Sub macro2() macro2 Macro Range("A1:D7").Value = 9 End Sub

X X.. M MA AC CR RO OD DIIX XIIÈ ÈM ME E//D DO OU UZZIIÈ ÈM ME E OUVRIR LE CLASSEUR "MATRICE TABLEAU" DU DOSSIER "ÉXOSEXCBD" FAIRE UNE MACROCOMMANDE PERMETTANT DE MENSUALISER PAR DIXIÈME LE TOTAL DE CHAQUE LIGNE FAIRE UNE MACROCOMMANDE PERMETTANT DE MENSUALISER PAR DOUZIÈME LE TOTAL DE CHAQUE EXERCICE LIGNE ASSOCIER CHAQUE MACROCOMMANDE À UN BOUTON

INFORMATION

Sub douziéme() 'détermination de la variable "c" Set c = ActiveCell 'vérification que la cellule sélectionnée est une cellule de total et différente de 0 If ActiveCell.Column = 14 And ActiveCell.Value <> 0 Then 'alors copie la valeur dans le presse-papier Selection.Copy 'remplace la formule de total par la valeur du total Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'boucle sur les 12 cellules de la ligne correspondant au mois For i = 1 To 12 'recule d'une cellule horizontalement et copie le douzième de la valeur enregistrée dans c(le total) et ce 12 fois ActiveCell.Offset(0, -1).Select ActiveCell.Formula = c.Value / 12 Next i 'reécrit une somme dans la cellule de total ActiveCell.Offset(0, 12).Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:RC[-12])" Else MsgBox "sélectionnez la cellule de total à répartir" End If End Sub Sub diziéme() Set c = ActiveCell page 88

reproduction réservée

INFORMATION

If ActiveCell.Column = 14 And ActiveCell.Value <> 0 Then Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False For i = 1 To 4 ActiveCell.Offset(0, -1).Select ActiveCell.Formula = c.Value / 10 Next i ActiveCell.Offset(0, -1).Select ActiveCell.Formula = 0 ActiveCell.Offset(0, -1).Select ActiveCell.Formula = 0 For i = 1 To 6 ActiveCell.Offset(0, -1).Select ActiveCell.Formula = c.Value / 10 Next i ActiveCell.Offset(0, 12).Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:RC[-12])" Else MsgBox "sélectionnez la cellule de total à répartir" End If End Sub

Y Y.. M MA AC CR RO OC CO ON NS SO O

EXERCICE

INFORMATION

OUVRIR LE CLASSEUR "MATRICE CONSO" DU DOSSIER "ÉXOSEXCBD" FAIRE UNE MACROCOMMANDE PERMETTANT DE CONSOLIDER LES DONNÉES DE TOUS LES TABLEAUX CRÉÉS À PARTIR DU CLASSEUR "MATRICE TABLEAU" EN EN FAISANT SIMPLEMENT LA LISTE DANS LA FEUILLE "LISTE" DE CE CLASSEUR

Sub conso() ' conso Macro 'mise à zéro des valeurs Sheets("conso").Select ActiveSheet.Unprotect 'affichage des éventuelles lignes et colonnes cachées Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False 'volets de fenêtre non figé ActiveWindow.FreezePanes = False Range("B3:M12,B13:M20,B22:M25,B27:M37,B39:M50").Select Range("B39").Activate 'Mise à zéro des valeurs du tableau Range("B3:M12,B13:M20,B22:M25,B27:M37,B39:M50").Value = 0 'consolidation Sheets("conso").Select Sheets("liste").Select Range("A1").Select 'boucle For i = 1 To 100 'test pour arrêter à la fin de la liste If Workbooks(1).Worksheets(2).Cells(i, 1).Value <> 0 Then 'memorisation de la valeur de la cellule dans la liste setC1 = Workbooks(1).Worksheets(2).Cells(i, 1).Value 'mémorisation du chemin d'accés

Edition I O S

page 89

setchemin = Workbooks(1).Path setchemin = setchemin & "\" & setC1 Workbooks.Open (setchemin) 'copie dans le presse papier des valeurs de la première zone à consolider Range("B3:M12").Select Selection.Copy 'changement de classeur Workbooks(1).Worksheets(1).Activate 'addition dans le classeur de conso des valeurs des cellules copiées à celles qui existent déjà Range("B3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False 'idem pour autres plages de données Workbooks(2).Worksheets(1).Activate Range("B13:M20").Select Selection.Copy Workbooks(1).Worksheets(1).Activate Range("B13").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False Workbooks(2).Worksheets(1).Activate Range("B22:M25").Select Selection.Copy Workbooks(1).Worksheets(1).Activate Range("B22").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False Workbooks(2).Worksheets(1).Activate Range("B27:M37").Select Selection.Copy Workbooks(1).Worksheets(1).Activate Range("B27").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False Workbooks(2).Worksheets(1).Activate Range("B39:M50").Select Selection.Copy Workbooks(1).Worksheets(1).Activate Range("B39").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False Workbooks(2).Close SaveChanges:=False Else 'sortir End If Next i 'sortir End Sub

NOTES

page 90

reproduction réservée

TABLE DES MATIÈRES I.

LA BASE DE DONNÉES A.

CARACTÉRISTIQUES 1 2 3 4

B.

1

LA BASE EXCEL LES CHAMPS LES ENREGISTREMENTS LE FORMULAIRE

1 2 2 2

LES ENTRÉES DE DONNÉES

3

A) B) C) D) E) F) G) H)

C.

1

L'OUVERTURE DE FICHIERS TEXTE LA REDISTRIBUTION DES DONNEES L'OUVERTURE DE FICHIERS D'UNE BASE DE DONNEES L'OUVERTURE DE FICHIERS AU FORMAT XML L'EXTRACTION DE DONNÉES D'UNE BASE EXTERNE L'EXTRACTION AVANCÉE DE DONNÉES D'UNE BASE EXTERNE LA LIAISON A UNE BASE DE DONNÉES EXTERNE LE CUBE OLAP

L'EXTRACTION D'ENREGISTREMENTS 1 2

LE FILTRE AUTOMATIQUE LE FILTRE ÉLABORE A) B) C) D) E)

LA PLAGE DE DONNÉES LA ZONE DE CRITÈRES L'EXTRACTION LA ZONE D'EXTRACTION LES CRITERES D'EXTRACTION

3 4 5 6 6 13 17 18

20 20 22 22 22 23 24 25

D.

L'ANNULATION DU FILTRE

26

E.

LE TRI DES ENREGISTREMENTS

27

F.

LES SOUS TOTAUX

28

II.

LES TABLES ET TABLEAUX DE DONNÉES A.

HYPOTHÈSE A UNE VARIABLE 1 2

B.

HYPOTHÈSE A DEUX VARIABLES 1 2

C.

TABLE NON LIÉE A UNE BASE TABLE LIÉE A UNE BASE

.LES TABLEAUX CROISES 1 2 3 4

III.

TABLE NON LIÉE A UNE BASE TABLE LIÉE A UNE BASE

CREATION DU TABLEAU CROISE LES ÉTAPES DE LA CREATION UTILISATION DU TABLEAU CROISE MODIFICATION DU TABLEAU CROISE

LES FONCTIONS A.

GÉNÉRALITÉS 1 2 3 4

B.

LA SYNTAXE LES TYPES DE FONCTION LES ARGUMENTS LES RÈGLES D'UTILISATION

LES FONCTIONS DE BASE DE DONNÉES 1 2 3 4

Edition I O S

=BDECARTYPE(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDECARTYPEP(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDMAX(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDMIN(BASE_DONNÉES;CHAMPS;CRITÈRES)

31 31 32 33

34 34 35

36 36 37 40 42

43 43 43 44 45 45

45 46 46 46 46

page 1

5 6 7 8 9 10 11

C.

=BDMOYENNE(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDNB(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDNBVAL(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDPRODUIT(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDSOMME(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDVAR(BASE_DONNÉES;CHAMPS;CRITÈRES) =BDVARP(BASE_DONNÉES;CHAMPS;CRITÈRES)

LES FONCTIONS LOGIQUES 1 2 3 4 5 6

D.

LE FORMAT CONDITIONNEL =SI(TEST;VALEUR_SI_VRAI;VALEUR_SI_FAUX) =FAUX() =NON(VALEUR_LOGIQUE) =OU(VALEUR_LOGIQUE1;VALEUR_LOGIQUE2...) =VRAI()

LES FONCTIONS DE DATE ET D'HEURE 1 2 3 4 5 6 7 8 9 10 11 12 13 14

IV.

=AUJOURD'HUI() =MAINTENANT() =ANNEE(NUMÉRO_DE_SÉRIE) =DATE(ANNÉE;MOIS;JOUR) =DATEVAL(DATE_TEXTE) =JOUR(NUMÉRO_DE_SÉRIE) =JOURSSEM(NUMÉRO_DE_SÉRIE) =JOURS360(DATE_DÉBUT;DATE_FIN) =MOIS(NUMÉRO_DE_SÉRIE) =HEURE(NUMÉRO_DE_SÉRIE) =MINUTE(NUMÉRO_DE_SÉRIE) =SECONDE(NUMÉRO_DE_SÉRIE) =TEMPS(HEURE;MINUTES;SECONDES) =TEMPSVAL(HEURE_TEXTE)

LES MACROS COMMANDES A.

LES MACROS SIMPLES 1 2 3

B.

L'ENREGISTREMENT D'UNE MACRO-COMMANDE L'AFFECTATION D'UNE MACRO A UN OUTIL L’EXÉCUTION D’UNE MACRO-COMMANDE

48 50 53 53 53 53

54 54 54 54 54 54 55 55 55 55 55 55 55 55 55

57 57 57 58 59

61

LES OBJETS LES PROPRIÉTÉS LES MÉTHODES LES ÉVÈNEMENTS

61 62 62 63

A) B) C) D) E) F)

V.

48

LES MACROS COMPLEXES 1 2 3 4

5 6 7

46 46 47 47 47 47 47

LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKBOOK LISTE DES ÉVÈNEMENTS LIES A L'OBJET WORKSHEET LES CLASSEURS LES FEUILLES DE CALCUL LES CELLULES LE BOUTON DE COMMANDE

LES CONDITIONS : IF THEN END IF LES BOUCLES : FOR NEXT LES BOUCLES CONDITIONNELLES: DO LOOP WHILE

DOSSIER PÉDAGOGIQUE

63 64 64 65 65 66

66 66 67

69

A.

CONVERS.TXT

69

B.

REQUETE 1 (.XLS / .DQY)

69

C.

REQUETE 2 (.XLS / .DQY)

70

page 2

reproduction réservée

D.

REQUETE 3 (.XLS / .DQY)

71

E.

LIAISON ACCESS.XLS

72

F.

REQUETECUB .XLS /

72

G.

BASE.XLS

73

H.

FONCTBD.XLS

74

I.

TABHYP – 1A

75

J.

TABHYP – 1B

76

K.

TABHYP – 2A

77

L.

TABHYP – 2B

78

M.

TABCROIS.XLS

79

N.

VENTES CAFE.XLS

80

O.

REPRÉSENTANTS - COMMISSIONS

81

P.

REPRÉSENTANTS - OUTILS

82

Q.

REPRÉSENTANTS - CHIFFRE D'AFFAIRES

83

R. S.

EMPRUNT DATES

84 85

T.

MACRO PAYSAGE

86

U.

MACRO IMPRESSION

87

V. W.

MACRO1 MACRO 2

87 88

X.

MACRO DIXIÈME/DOUZIÈME

88

Y.

MACRO CONSO

89

Edition I O S

page 3