Partons d'un tableau donnant le chiffre d'affaires journalier par région en fonction de la météo :
Sous-totaux
Pour calculer la moyenne des ventes par région, on peut trier la liste par région puis calculer des moyennes :
Sélectionner une cellule du tableau, Données / trier / Trier par … région / OK
Données / Sous-totaux / à chaque changement de …région / utiliser la fonction … Moyenne / OK
Au bas de chaque plage contenant une région, la moyenne du CA et de la température est indiquée :
Si maintenant on souhaite avoir le CA moyen en fonction du temps et de la région, il faut faire un double tri :
Données / Trier / Trier par… région / puis par… temps
Données / Sous-totaux / à chaque changement de …temps / utiliser la fonction … Moyenne / OK
Le tableau devient un peu difficile à lire.
Si on voulait ajouter une troisième dimension (par exemple le jour de la semaine), il serait inextricable.
Le tableau croisé dynamique permet de résoudre ce problème de présentation (cf. plus bas).
L'utilisation de sous-totaux nécessite de trier préalablement la base de données, ce qui peut présenter des inconvénients.
Les fonctions de bases de données permettent de calculer des moyennes, des sommes, des nombres d'occurrence, des maximums… sans modifier la base.
On peut utiliser des zones de critères combinées :
Pour les jours de soleil en région est dont la température est comprise entre 10 et 12°C :
|
temps |
région |
température |
température |
|
soleil |
est |
>10 |
<=12 |
Pour les jours de neige ou de pluie de la région sud :
|
temps |
région |
|
neige |
sud |
|
pluie |
sud |
On peut utiliser BDSOMME, BDMAX, BDMIN, BDMOYENNE, BDPRODUIT, BDNB (nombre de cellules contenant un nombre), BDNBVAL (nombre de cellules non vides), BDLIRE (valeur correspondant aux critères, si elle est unique).
Pour les critères simples, on peut également utiliser les fonctions NB.SI(plage; critère) et SOMME.SI(plage1;critère;plage2) qui présentent l'avantage d'être très faciles à utiliser.
En pratique, les fonctions de bases de données sont assez peu utilisées. Il est souvent plus efficace d'utiliser des fonctions matricielles qui offrent de plus vastes possibilités.
Les fonctions de bases de données présentent toutefois l'avantage d'être calculées plus rapidement, dans les grands fichiers notamment.
L'utilisation des sous-totaux ne permet pas de présenter de manière lisible des consolidations selon plusieurs variables (par exemple moyenne de CA selon le temps et la région). Le tableau croisé dynamique permet de remédier à ce problème de présentation en mettant par exemple le temps en ligne et la région en colonne. Il présente en outre l'avantage de ne pas nécessiter de tri préalable de la base :
Sélectionner une cellule de la base.
Données / Rapport de tableau croisé dynamique
Déplacer à la souris les champs proposés (date, temps, température…) pour les placer à l'endroit souhaité, par exemple temps en colonne, région en ligne et CA en donnée à (moyenner).
Le tableau croisé est construit par défaut avec la somme des chiffres d'affaires (au lieu de la moyenne).
En double-cliquant sur "Somme CA", on peut passer à la moyenne (ou bien Max, Min, Ecart Type, nombre…).
On peut modifier le tableau avec : clic droit sur le tableau / Assistant…
Rien n'empêche de mettre plusieurs données différentes (CA et température).
On peut facilement ajouter une troisième dimension au tableau qui peut se présenter sur plusieurs "pages", par exemple une page par région (on peut afficher une "page" par feuille Excel par clic droit sur le tableau / afficher les pages / afficher toutes les pages).
Dans le tableau ci-dessus, les températures sont groupées de 5°C en 5°C.
Pour obtenir ce résultat, clic droit sur le bouton "température", Grouper et créer un plan / grouper / par 5.
Utile pour faire des pyramides des âges par exemple.
Le tableau croisé dynamique peut être construit à partir de données externes (Access, SQL…) en utilisant Microsoft Query (cf. plus bas).
Attention, si les données changent, le tableau croisé dynamique n'est pas mis à jour automatiquement, il faut sélectionner une cellule du tableau croisé dynamique puis données / actualiser.
L'outil Données / Consolider permet de rapprocher plusieurs tableaux éventuellement dans des fichiers différents en calculant la somme, la moyenne, le maximum, l'écart-type… des données de même nature. Il peut aussi bien concerner des tableaux ayant exactement la même structure (donnes de même nature aux mêmes positions) ou bien des tableaux dans lesquels les données sont repérées par des titres de lignes et colonnes.
On peut par exemple utiliser cette technique pour consolider les comptes de plusieurs sociétés,
ou pour repérer les évolutions d'une semaine sur l'autre des BPA, PER et capitalisation boursière d'un grand nombre d'actions présentées dans le désordre (et dans deux feuilles Excel différentes).
MICROSOFT QUERY sert à accéder depuis Excel à des bases de données externes (dBASE, Access, FoxPro, SQL Server, texte) via SQL (Structured Query Language).
Une requête émise à partir d'une feuille Excel permet simplement d'extraire des données d'une base de donnée Access, même fermée.
La procédure est simple : Données / données externes / créer une requête / Bases de données
Double cliquer sur "Nouvelle source de données"
Type de base de données (par exemple Microsoft Access) / connexion / sélectionner la base de donnée (fichier Access) / OK / OK
Microsoft Query propose les colonnes de la base Access à retenir puis demande si on souhaite filtrer les données (en indiquent des critères sur ces colonnes), avant de renvoyer les données traitées dans Excel.
Remarque, la requête SQL est un simple fichier texte précisant l'endroit où se trouve la source et les critères. Elle peut être enregistrée séparément du fichier Excel si on souhaite la réutiliser.
(pour mettre à jour le résultat de la requête, se placer dans la base xl puis données / actualiser les données).
Une requête Web est une commande qui ordonne l'ouverture sous Excel d'une page Web. La requête reste liée au fichier Excel, ce qui permet des mises à jour faciles.
On doit écrire les quelques mots de la requête dans un logiciel de texte, de préférence dans le bloc-notes (ou à la rigueur dans Word avec enregistrement en format "texte seulement").
Trois lignes suffisent pour une requête Web (on peut même omettre les deux premières) :
|
WEB |
|
1 |
|
http://www.boursorama.com/ |
Le fichier texte est ensuite enregistré avec l'extension ".iqy", par exemple sous le nom req.iqy dans le répertoire de votre choix.
Dans Excel, dans le fichier où l'on souhaite exécuter la requête,
Données / Données externes / requête sur le Web / sélectionner req.iqy / lire données.
La requête affiche la page Web (souvent quelque peu déformée) dans une page Excel :
La présentation est généralement déplorable, mais les données numériques sont tout à fait exploitables dans des calculs.
Dans l'exemple ci-dessous de Boursorama, une autre page Excel liée à celle-ci et présentée avec soin sera mise à jour avec la page Web.
Attention toutefois, la page Excel conserve en mémoire la requête (on peut d'ailleurs supprimer le fichier texte initial), mais n'est pas liée en permanence avec la page Web. Pour effectuer une mise à jour, il suffit de positionner le curseur dans la page puis Données / Mise à jour.
On peut faire des requêtes un peu plus sophistiquées avec par exemple une invite d'entrée de paramètre :
Quand on exécute la requête ci-dessus, Excel demande de saisir le code sicovam pour ouvrir la page Web correspondante.
Remarque, avec une requête Internet, on ouvre une page Web sous Excel.
Une requête n'est pas indispensable, et on peut très bien ouvrir directement une page Web sous Excel comme un fichier normal :
Fichier / Ouvrir / http://www.boursorama.com/ permet effectivement d'ouvrir la page d'accueil de Boursorama.
L'avantage d'une requête est qu'elle permet des mises à jour faciles de la page Excel (éventuellement par VBA).
télécharger le fichier exemple
Autre point de rencontre possible entre Excel et Internet, les liens hypertexte.
Un lien hypertexte placé sur un texte ou sur une image permet, par un simple clic de souris, d'accéder à un fichier, à un endroit précis d'un fichier, voire de lancer un programme.
Pour créer un lien hypertexte, sélectionner une cellule, contenant du texte ou non, ou un objet, une image.
Insertion / lien Hypertexte puis préciser l'adresse vers laquelle doit pointer le lien : adresse de fichier (par exemple C:\mes documents\fichier.xls) ou URL (par exemple
http://www.boursorama.com/) et/ou localisation dans le fichier (par exemple B5, ou bien Feuil2!A4).Un simple clic sur le lien permet d'ouvrir le fichier et de se placer à l'endroit voulu.
Autre lien avec l'extérieur, la fonction SendMail permet d'envoyer n'importe quel fichier Office (notamment Word ou Excel) par E-Mail :
Il suffit, après enregistrement du fichier de cliquer sur Fichier / Envoyer vers… pour provoquer l'ouverture du logiciel de messagerie et y créer un message e-mail auquel est déjà joint le fichier à envoyer.