Comment utiliser Excel pour gérer des données ? Comment créer une base Excel, gérer les champs, les enregistrements, créer un formulaire ?…

Comment extraire les données d’une base Excel ? Les filtrer, les trier ?…

Comment et quand utiliser les fonctions de base de données d’Excel ?
Les fonctions somme, écartype, moyenne, nb, mix ?…

Comment utiliser Excel avec une base de données externe ?

Comment extraire les données dans Excel, comment utiliser Microsoft Query ?...

Comment créer un tableau croisé dynamique ? Un graphique croisé dynamique, un cube olap ?...

Comment utiliser Powerpivot, Powerview dans Excel ?... Créer des mesures, des indicateurs de performance ?...

Comment créer une table d’hypothèse ? À une variable, à deux variables, liée à une base ?...

Comment utiliser les fonctions d’Excel ? Les fonctions logiques, de date et d’heure, mathématiques, statistiques, de recherche, texte, d’information ?...

Comment auditer les formules d’Excel ? Pour être sûr du résultat obtenu ou pour rechercher une erreur ?...

Comment utiliser les macro-commandes dans Excel ? Sécuriser les classeurs, afficher l’onglet "développeur", enregistrer une macro, l'exécuter, la modifier ?...

Comment utiliser VBA dans Excel ? Les objets, les propriétés, les méthodes, les événements, les conditions, les boucles, les boucles conditionnelles ?...

Particuliers, vous pouvez vous connecter sur notre plate-forme de E-learning gratuite :

http://www.informatique-bureautique.com

Professionnels, vous pouvez obtenir des licences de groupe de cet ouvrage, acquérir les sources, ou inscrire vos stagiaires à nos cours de E-learning sur notre site :

http://www.ios.fr

Cet ouvrage présente différentes rubriques repérées par une icône

des procédures décrivent la marche à suivre pour effectuer une action :
3 commandes à se rappeler :
<clic g> pour appuyer sur le bouton gauche de la souris avec l'index
<clic d> pour appuyer sur le bouton droit de la souris avec le majeur
<double clic> pour appuyer deux fois de suite très rapidement sur le bouton gauche de la souris avec l'index

des informations viennent compléter ces procédures

des exemples viennent illustrer ces procédures

des conseils vous aident à mettre en pratique vos connaissances

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

des exercices permettent de tester les procédures et d'appliquer vos connaissances. Ils peuvent être téléchargés à l'adresse suivante http://www.ios.fr/public/exosexcel2016bd.exe

des procédures avancées décrivent des actions plus complexes

Souvent, une action peut être effectuée selon plusieurs méthodes :

1ère méthode - 2ème méthode

Une méthode n'est pas meilleure qu'une autre mais l'une ou l'autre peut être mieux adaptée à votre manière de travailler.

Ces actions peuvent être effectuées avec plusieurs moyens :

Avec le bouton gauche de la souris

Avec le bouton droit de la souris

Avec le clavier

Windows demande l’utilisation du bouton gauche et du bouton droit de la souris ainsi que du clavier. L'écran tactile, lui, est manipulé avec les doigts.

Elles peuvent concerner différents endroits de l'écran :

L'angle bas/gauche avec le menu démarrer

L'angle bas/droite pour afficher le bureau

Des emplacements spécifiques (barre de formule, volet droit, notifications, barre des tâches…)

N'importe quelle zone de l'écran (menu contextuel, outils d'analyse rapide…).

I.               RUBAN

Les éléments du ruban peuvent être modifiés en fonction des besoins. Cette personnalisation n'est pas liée à un classeur mais à l'application même.

Le ruban est composé d'onglets, dans lesquels figurent des groupes dans lesquels figurent des outils. Les éléments peuvent être paramétrés aux différents niveaux.

A.              AJOUT D'UN ONGLET

il est possible de rajouter un onglet pour y mettre groupe les outils de son choix.

barre accès rapide

<clic g> sur
<clic g> sur
sélectionner
dans le volet de l'onglet groupe à la suite duquel devra s'afficher le nouveau
<clic g> sur
un onglet et un groupe personnalisé sont ajoutés sous l'onglet sélectionné

<clic g> sur pour renommer le groupe
idem pour renommer l'onglet

B.              AJOUT D'UN GROUPE

Dans tout onglet, il est possible de rajouter un groupe d'outils personnalisé.

barre accès rapide
<clic g> sur
<clic g> sur
sélectionner
dans le volet de droite
développer l'onglet dans lequel créer le groupe avec
<clic g> sur le groupe à la suite duquel devra s'afficher le groupe personnalisé
<clic g> sur
<clic g> sur pour nommer le groupe

Créer après un onglet "mes outils" avec des groupes "format" et "macros" comme ci-après

C.              MODIFICATION DE L'ORDRE

L'ordre des onglets dans le ruban ainsi que des groupes dans les onglets est modifiable. Cependant, l'ordre des outils dans un groupe ne peut être changé que dans un groupe personnalisé.

barre accès rapide
<faire glisser> à la nouvelle position ou utiliser et
un trait indique le futur emplacement

Déplacer l'onglet "mes outils" intervertir ses groupes

D.              AJOUT D'OUTILS

L'ajout d'outils ne peut s'effectuer que dans un groupe personnalisé.

barre accès rapide
<clic g> sur
<clic g> sur
sélectionner
dans le volet de droite, sélectionner le groupe
dans le volet de gauche, sélectionner à priori la catégorie
sélectionner l'outil
<clic g> sur
répéter l'opération autant de fois que nécessaire
pour valider

Compléter l'onglet "mes outils" comme ci-après

<clic g> sur pour rendre aux onglets ou groupes leur état d'origine

Pour créer un groupe d'outils nommé "Mes outils"
je clique sur de la barre d'outils
je clique sur
je clique sur
dans le volet de droite
je sélectionne je clique sur
s'affiche
je clique sur je saisis "format"
je sélectionne
je clique sur je saisis "Mes outils"
je fais glisser pour positionner le groupe
dans le volet de gauche
je sélectionne l'outil  je clique sur
je sélectionne l'outil  je clique sur
je sélectionne l'outil  je clique sur
je ferme la fenêtre par

II.          BASE DE DONNÉES

Une base de données est un ensemble structuré de données, regroupées en un ou plusieurs fichiers ; elle donne l'image à un instant donné des informations relatives à un sujet, est mise à jour régulièrement et autorise 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 structurée 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, 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 ajoutées.

A.              CARACTÉRISTIQUES

Excel est un tableur ; il est donc avant tout conçu pour faire des tableaux de calcul. Cependant, dès lors qu'un certain nombre de données ont été saisies de manière structurée dans des tableaux sous forme de liste, 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.     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 imprimer le résultat de ces opérations(voir base.xlsx).

2.     CHAMPS

Les champs correspondent aux colonnes de données. Ils contiennent les valeurs prises pour chaque type d'information par chaque enregistrement de la base.
Les champs possèdent un nom qui les qualifie et permet leur gestion. Ce nom correspond aux valeurs de 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 calculé ; il intègre alors une formule ou une fonction.

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

3.     ENREGISTREMENTS

Un enregistrement (ou fiche) représente une ligne du champ de données. Cette ligne donne les valeurs (numériques, alphanumériques…) prises par chaque article dans chaque champ.

est un enregistrement

4.     FORMULAIRE

Le formulaire (ou grille) permet de visualiser, modifier, rechercher, ajouter ou supprimer des enregistrements. Un seul apparaît à la fois. Il correspond à la même notion de formulaire ou de masque d'écran des SGBD classiques.

L'outil formulaire ne figure plus dans l'onglet "données". Pour l'utiliser, il faut alors le rajouter dans le ruban pour pouvoir l'utiliser

onglet "données"
groupe "formulaire"
une cellule du tableau étant sélectionnée
<clic g> sur l'outil formulaire
Le premier enregistrement de la base est affiché.
utiliser les boutons de la boîte de dialogue
pour revenir à la feuille de travail
L'ascenseur permet de changer d'enregistrement ; <tab> permet de passer d'un champ à l'autre

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 choix des commandes <suivante> ou <précédente>

Fermeture de la boîte de dialogue.

Ouvrir le classeur "base.xslx" et afficher la première feuille "base de données"
Repérer les champs et les enregistrements
Se déplacer entre les enregistrements avec l'outil "formulaire"
Fermer le classeur

B.              EXTRACTION ENREGISTREMENTS

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, c'est le filtre élaboré qui doit être utilisé.

1.     FILTRE AUTOMATIQUE

Le filtre automatique va permettre d'utiliser les valeurs des champs dans des listes déroutantes.

onglet "données"
groupe "trier et filtrer" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur
des triangles pointés vers le bas correspondant à des listes déroutantes s'affichent près des titres de colonne – cliquer de nouveau sur le filtre pour les enlever

bouton gauche
pointer sur le triangle bas à droite du titre de colonne
<clic g> pour dérouler la zone de liste
<clic g> sur la valeur à prendre en compte
le triangle du champ sélectionné affiche - seuls les enregistrements répondant aux critères restent alors affichés ; les autres sont masqués. Les numéros de ligne des enregistrements sont alors en bleu

dans le ruban,
efface le filtre en cours
annule tous les filtres et réaffiche tous les enregistrements

Ouvrir base.xlsx
Afficher la feuille nommée "base de données"
Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements concernant la région "Ouest"
puis ceux de la région "Ouest" et ceux de la région "Est"

Des critères s'appliquant à deux champs distincts peuvent être combinés

Il est possible d'être plus précis dans la définition des filtres :

filtres textuels affiche les opérateurs de comparaison de texte

filtres numériques affiche les opérateurs de comparaison numériques

L'un ou l'autre s'affiche selon la nature des données de la colonne

Dans base.xlsx,
Pour afficher les enregistrements dont les ventes sont supérieures à 100 k€
je clique sur de
je pointe sur
je clique sur
je saisis 100

Dans base.xlsx, feuille "base de données"
Afficher les enregistrements concernant la région "Ouest"
puis affiner en ne prenant que les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 k€ (voir base terminé.xlsx, feuille "solution1")
Annuler le filtre "ventes" puis annuler le filtre automatique

Ce filtre peut aussi utiliser plusieurs valeurs combinées pour un même champ avec les opérateurs booléens "et", "ou" ainsi que les jokers "?", "*".

operateurs

et intersection - les deux conditions doivent être simultanées

ou 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

Dans base.xlsx, feuille "base de données"
Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Ouest" et de la région "Est" dont les ventes sont inférieures ou égales à 93 k€
(voir base terminé.xlsx, feuille "solution2") puis tout réafficher

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 droit
sélectionner la cellule
<clic d> 
<clic g> sur
seuls les enregistrements possédant pour le champ donné la même valeur que la cellule active restent affichés

Dans base.xlsx, feuille "base de données"
Masquer les enregistrements ne concernant pas la ville de Caen puis tout réafficher

Enfin, il est encore possible de filtrer ou trierles enregistrements en fonction de la couleur de leur police ou de leur remplissage (seules les couleurs utilisées dans le tableau sont proposées) ; c'est là un outil qui vient en complément du format conditionnel.

bouton gauche
pointer sur le triangle bas à droite du titre de colonne
<clic g> sur ou
<clic g> sur la couleur à prendre en compte

Si les couleurs de police ou de cellule sont uniformes, la commande est en grisé

Dans base.xlsx, feuille "base de données"
Affecter une couleur de police différente aux 10 premières lignes
Affecter une couleur de remplissage différente aux 10 lignes suivante
Affecter une couleur de police différente aux 10 lignes suivante
Affecter une couleur de remplissage différente aux 10 lignes suivante
Trier par couleur de police puis de cellule puis annuler le tri

2.     FILTRE ÉLABORE

Le filtre élaboré offre plus de possibilités que le filtre automatique. Il 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)   PLAGE DE DONNÉES

La plage de données correspond à l'ensemble des cellules contenant les informations à traiter. 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.

b)   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.

insérer plusieurs lignes vierges au-dessus des données
recopier la ligne des noms des champs de données comme 1ère ligne
indiquer les critères de recherche sur la deuxième ligne et suivantes

Ne pas mettre sur les mêmes lignes zone de critères et données car si l'une de ces lignes de données se trouve cachée, les critères de la même ligne le seront aussi

c)    EXTRACTION

L'extraction va permettre de n'afficher que les enregistrements correspondant aux critères définis (les lignes des autres enregistrements sont masquées).

onglet "données"
groupe "trier et filtrer" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur
vérifier la plage de données
indiquer la zone de critères
pour valider
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements)
les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 K€ et de la région "Est" dont les ventes sont supérieures à 35 K€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution3")

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

d)   ZONE D'EXTRACTION

C'est la zone de la feuille où vont s'inscrire les enregistrements répondant aux critères spécifiés.

onglet "données"
groupe "trier et filtrer" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur
activer 
vérifier la plage de données
indiquer la zone de critères
<clic g> dans
<clic g> sur la 1ère cellule de destination
pour valider
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de données

Si les données ont reçu une mise en forme de type "tableau", Excel remplace les références "ligne" et "colonne" par le n° du tableau

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

Vous pouvez spécifier les colonnes à inclure dans l’opération de copie
Copiez les noms des colonnes souhaitées vers la première ligne de la zone d'extraction puis lors du filtrage, incluez les références aux noms des colonnes copiées de la zone d'extraction.(Les lignes copiées incluront alors uniquement les colonnes dont vous avez copié les étiquettes)

Dans base.xlsx, feuille "base de données"
Extraire (à partir de la celluleO1 par exemple) les enregistrements de la région ouest dont les ventes sont inférieures à 100 K€ et de la région est dont les ventes sont supérieures à 35 K€ puis annuler l'opération (voir base terminé.xlsx, feuille"solution3")

e)   CRITÈRES D'EXTRACTION

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

insérer plusieurs lignes vierges au-dessus des données
recopier la ligne des noms des champs de données comme 1ère ligne
indiquer les critères de recherche sur la deuxième ligne et suivantes
Indiquer la valeur de comparaison et l'opérateur logique à partir de la 2ème ligne du champ de critères (format indifférent)

texte

?              remplace un caractère

*              remplace plusieurs caractères

~              précède le ? ou le * considérés comme texte

chiffre

=, <, <=,>,>=…
les opérateurs standard 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ères sont sélectionnés

Pour cerner la valeur d'un champ, ajouter ou 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 cellules de la même ligne
ex :

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Sud" entre 20 et 80 k€ et ceux de la région "Nord" inférieurs à 30 et supérieurs à 75 k€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution3", 2ème champ de critères g1:l4)

En mode "tableau" (utilisation de la mise en forme prédéfinie des tableaux), des problèmes peuvent se poser avec les critères multiples – éviter ce mode sur les lignes de critères

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

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

Dans base.xlsx, feuille "base de données"
Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements dont les ventes sont supérieures à la moyenne
(voir base terminé.xlsx, feuille "solution4")

C.              TRI ET SOUS-TOTAUX

1.     TRI DES ENREGISTREMENTS

Le tri est l'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 en fonction de l'ordre de l'une des colonnes (clé de tri), puis de classer les valeurs identiques en fonction des valeurs d'une autre colonne (2ème clé) et ainsi jusqu'à trois.

onglet "données"
groupe "trier et filtrer" (4ème bloc)
une cellule de la colonne de tri étant sélectionnée
<clic g> sur ou

bouton droit
<clic d>
sur une cellule de la colonne de tri

Les boutons de filtre automatique proposent aussi ces mêmes outils de tri ainsi qu'un tri par couleur

Dans base.xlsx, feuille "base de données"
Trier les enregistrements par "nom" puis par "montant de vente" dans l'ordre croissant

Il est possible de faire un tri sur plusieurs niveaux.

onglet "données"
groupe "trier et filtrer" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur
sélectionner le nom de la 1ère colonne de tri
sélectionner l'objet du tri (valeurs le plus souvent)
choisir le sens du tri (croissant/décroissant)
<clic g> sur pour éventuellement ajouter une deuxième clé…
répéter les opérations précédentes
pour valider

paramètres

1ère clé - clé de tri principale
les données sont 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é sont triées avec cette clé.

3ème clé - clé de tri tertiaire
les données ayant la même valeur pour la 1ère et la 2ème clé sont triées avec cette clé.

Dans base.xlsx, feuille "base de données"
Trier les enregistrements par Région, par ville et par nom dans l'ordre croissant

2.     SOUS TOTAUX

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 selon les sous-totaux désirés est indispensable.

onglet "données"
groupe "plan" (7ème bloc)
Trier les données avec les champs correspondant aux sous-totaux
<clic g> sur
dérouler les noms de champ
choisir le champ, la fonction, le sous-total
pour valider
les données sont affichées en mode plan et un sous total est effectué pour le champ

En mode "tableau" (utilisation de la mise en forme prédéfinie des tableaux), l'outil de sous-total n'est pas actif

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 .
Pour annuler les sous-totaux, utiliser la même boite de dialogue mais <clic g> sur

Dans base.xlsx, feuille "base de données"
Effectuer des sous-totaux par Région (voir base terminé.xlsx, feuille"solution5")
puis à l'intérieur de chaque région, par ville (voir base terminé.xlsx, feuille"solution6")

D.              FONCTIONS BASE DE DONNÉES

Les fonctions de base de données permettent d'obtenir des informations sur les 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 champ concerné par le calcul (le premier champ/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.

onglet "formules"
groupe "bibliothèque de fonctions" (1er bloc)
sélectionner une cellule
<clic g> sur (ou directement sur le groupe de fonction puis la fonction)
sélectionner la catégorie "base de données"
choisir la fonction
désigner la plage de base de données
saisir le n° de la colonne de valeurs
désigner la zone de critères
pour valider
les arguments de la fonction s'affichent

Dans les exemples suivants, plages de base de données et critères portent des noms :
"base de données" correspond à la plage A1:E65
"critères" correspond à la plage H2:L3 où H2:L4

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é.

7.      =BDNBVAL(base_données;champs;critères)

Cette fonction donne le nombre d'enregistrements 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.xlsx
Calculer le nombre d'enregistrements, la vente maximum, la vente minimum, 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 région "Nord"

III.     DONNÉES EXTERNES

Il y a plusieurs manières d'entrer des données dans une feuille de travail Excel.

Les saisir manuellement

Les saisirautomatiquement (série)

Ouvrir un fichier d'une autre application contenant ces données

Créer une liaison entre la feuille Excel et une base contenant les données.

Les coller avec le presse-papiers à partir d’un fichier d’une autre application

A.              OUVERTURE DE FICHIER AUTRES

1.     OUVERTURE 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).

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
sélectionner le fichier
<clic g> sur

Il est aussi toujours possible de choisir "fichiers texte" en cliquant sur de dans la boîte de dialogue d'ouverture d'Excel

L’assistant conversion prend en main les opérations de conversion des données.

indiquer si les champs sont séparés par des espaces ou d'autres signes
<clic g> sur
sélectionner si ce fichier est un fichier d'export de données (type ascii)
ou
sélectionner si les champs sont en colonne
faire alors é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
pour valider
ne pas hésiter à revenir en arrière avec

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

Si le format des nombres décimaux n'est pas correct, aller dans les paramètres avancés de la troisième étape pour les modifier (point au lieu de virgule par exemple).

L'outil de recherche/remplacement d'Excel est aussi très utile ici

Ouvrir le fichier "convers.txt" dans Excel
Afficher les données correctement (notamment la dernière colonne en valeur et non en texte) puis fermer le fichier sans l'enregistrer

2.     CONVERSION DONNÉES

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.

onglet "données"
groupe "outils de données (5ème bloc)
coller les données brutes dans la première colonne
toutes les valeurs sont alors dans cette colonne
libérer les colonnes adjacentes
sélectionner la plage de données
<clic g> sur
l'assistant "conversion" (identique à l'assistant importation précédent) s'affiche

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.xlsx"
Coller les données en A1 de la feuille "exercice"
Les convertir sur les colonnes adjacentes
Vérifier que les valeurs sont bien prises en compte
Modifier éventuellement leur format et effectuer une somme automatique

3.     OUVERTURE FICHIERS XML

Excel ouvre normalement les fichiers au format .xml. Le format .xml est un format d’échange (données et/ou structure) entre applications récentes qui permet de leur conserver la quasi-totalité 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.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
sélectionner
sélectionner le fichier
<clic g>  sur
accepter le mode

Il est aussi toujours possible de choisir "fichiers xml" en cliquant sur de dans la boîte de dialogue d'ouverture d'Excel

B.              EXTRACTION DE DONNÉES

1.     EXTRACTION BASE ACCESS

Access et Excel communiquent et échangent des données très naturellement.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
sélectionner le fichier
<clic g>  sur
accepter le mode
pour accepter l'importation des données dans Excel
Si la base compte plus d'une table, Excel demande de définir la table à importer

Il est aussi toujours possible de choisir "base de données Access" en cliquant sur de dans la boîte de dialogue d'ouverture d'Excel

Excel ouvre les fichiers au format .dbf (dbase:ancien standard d'échange de donnée) ou xml (standard actuel). Pour ouvrir dans Excel un fichier provenant d'une base de données autre qu'Access, utiliser ces formats.

si le fichier à ouvrir est un fichier de base de données mais n'est pas au format "dbf" ou "xml"
ouvrir l'application d'origine des données puis le fichier contenant les données
"enregistrer sous" ce fichier au format "xml" ou "dbf" puis fermer l'application d'origine
Ouvrir le fichier "dbf" sous Excel

Importer les données du fichier "access.accdb"

2.     EXTRACTION AVANCÉE ACCESS

Excel permet d'extraire d'une base les seules données utiles par l'intermédiaire d'une requête effectuée avec un assistant. La requête peut être enregistrée.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
<clic g> sur
"nouvelle source de données" sélectionné
pour valider

a)   CRÉATION SOURCE DE DONNÉES

Elle consiste à enregistrer dans un fichier "source de données" les paramètres de connexion au fichier de base de données par le canal du pilote (driver) "odbc"

fenêtre query
1 saisir le nom de la source de données
2 sélectionner le driver odbc associé
3 établir la connexion avec le fichier de base de données
4 indiquer éventuellement la table ou sont les données recherchées

Un connecteur odbcpermet de lier une ou plusieurs tables de base de données à l'application en cours (ici Excel)

b)   CONNEXION BASE DE DONNÉES

Elle crée un canal (grâce au pilote odbc) entre un fichier de base de données et Excel.

fenêtre query
<clic g> sur
<clic g> sur
désigner le fichier de données Access (accdb)
pour valider le fichier
pour valider la création de la connexion
sélectionner une table par défaut
pour créer la source de données

La connexion est établie.

La source de données est créée.

Elle est maintenant directement accessible dans du groupe "données externes"

c)    CREATION REQUÊTE

L'assistant "requête" prend alors la main et permet alors de sélectionner les champs à prendre en compte ainsi que les critères d'extraction.

  assistant requête
<clic g> sur de la table affiche la liste des champs
sélectionner le champ 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 avec
sélectionner le champ dans la partie droite puis ou pour en modifier l'ordre
<clic g> sur
filtrer et trier éventuellement les enregistrements
<clic g> sur
<clic g> sur
<clic g> sur
confirmer ou modifier les coordonnées de la cellule et la feuille d'insertion
pour valider

Les données peuvent être modifiées dans Excel mais ces modifications ne sont pas répercutées dans la base source ; l'outil réaffiche les données d'origine

Créer un nouveau classeur
Y afficher les enregistrements extraits de la table "clients" de la base access.accdb pour les champs "société", "contact", "adresse" et "cdpostal" puis enregistrer sous le nom "req"

d)   MODIFICATION REQUÊTE

La requête peut être modifiée. L'assistant permet d'utiliser des critères de sélection pour les enregistrements et de trier ces enregistrements.

onglet "données"
groupe "connexions" (3ème bloc)
une cellule des données importées étant sélectionnée
<clic g> sur de
<clic g> sur
onglet
<clic g> sur
la requête s'affiche de nouveau dans l'assistant

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
Trier par "cdpostal" et "société" dans l'ordre croissant

Lorsque qu'un champ est filtré ; il est en gras.
Lors de plusieurs requêtes successives, enlever les filtres utilisés précédemment

e)   ENREGISTREMENT REQUÊTE

À la fin de l'assistant requête, Excel permet l'enregistrement de la requête.

  assistant requête
dans la dernière étape de l'assistant
<clic g> sur
nommer la requête et indiquer le dossier

La requête est enregistrée au format .dqy, le dossier requête de l'utilisateur est alors proposé mais un autre peut être choisi

Enregistrer la requête précédente sous le nom "req1" dans le même dossier que les exercices puis fermer le classeur

3.     EXTRACTION BASE EXTERNE

Excel permet d'extraire des données issues de la plupart des bases avec les drivers odbc. Sont accessibles la plupart des formats du marché si les pilotes adéquats ont été installés. Seule la connexion à la source diffère du cas précédent.

a)   INSTALLATION PILOTE ODBC

Le pilote doit être le plus souvent téléchargé à partir du site web de l'éditeur.

Pour utiliser le pilote odbc de MySql,
Se rendre sur http://www.mysql.fr/downloads/connector/odbc/
Télécharger le pilote Windows (x86, 32-bit), MSI Installer Connector-ODBC
L'installer
Une fois ce pilote installé, vous pourrez intervenir directement et de manière très souple avec Excel(copier/coller, recopie, recherche/remplacement…) sur les données d'une base locale mysql(gérée par Microsoft webMatrix ou WampServer)

b)   CRÉATION SOURCE DE DONNÉES

Elle va consister à enregistrer dans un fichier "source de données" les paramètres du pilote (driver) "odbc"

panneau de configuration

<double clic> sur
onglet
<clic g> sur
sélectionner le driver
<clic g> sur

Pour créer une source de données à partir du pilote odbc MySql
onglet
<clic g> sur
sélectionner le driver (installé précédemment)
paramétrer la connexion
<clic g> sur

c)    CONNEXION BASE DE DONNÉES

Elle crée un canal entre un fichier de base de données et Excel par l'intermédiaire du pilote odbc.

paramétrage de la connexion
saisir un nom pour la source
renseignerle serveur tcpip (si local : localhost)
renseignerl'utilisateur (si local : root)
renseigner éventuellement le mot de passe
indiquer la base de données
<clic g>  sur pour tester le lien

Vous avez une version locale de votre site web qui fonctionne en php avec une base de données Mysql(pour cela, vous utilisez Webmatrix, Wampserver ou Easyphp qui créent un environnement Web local). Vous pouvez extraire des données de cette base et les traiter dans Excel : la fenêtre ci-dessus illustre la création d'une source de données à partir de cette base Mysql locale à l'aide du driver odbc précédemment téléchargé

Access permet de faire la même chose et d'agir directement sur les données source en mode "table", ce qui est très pratique pour modifier globalement les données sans passer par des requêtes ; il suffit alors de rapatrier en ligne les données (restauration Sqlet après vérification)

d)   IMPORTATION DANS EXCEL

Il faut désigner à Excel la source de données créée.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur   ou

sélectionner la source créée
<clic g> sur

Pour trouver le driver odbc d'un type de base de données, interrogez simplement votre moteur de recherche préféré. S'assurer cependant de bien être sur le site de l'éditeur sous peine de télécharger en plus des logiciels non désirés

4.     EXTRACTION AVANCÉE QUERY

Microsoft Query permet d'ouvrir les requêtes enregistrées ou de traiter les données avant de les renvoyer vers Microsoft Excel.

a)   OUVERTURE REQUÊTE

Il faut d'abord désigner la requête, préalablement enregistrée, pour l'ouvrir.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
<clic g> sur
onglet
<clic g> sur
sélectionner la requête
puis
pour valider
dans la dernière étape de l'assistant, choisir

Lors de l'ouverture de la requête, il peut arriver qu'Excel ne trouve plus le chemin de la base ou ait synthétisé les critères au sein d'un seul filtre et n'arrive plus à les lire (l'ouvrir alors directement en sql et modifier)
Modifier alors la requête (éventuellement en la décomposant ou directement en sqlafin qu'Excel la comprenne)

Ouvrir la requête "requete_source1.dqy" créée précédemment avec Query ; modifier les critères de filtre si nécessaire

Si Excel ne trouve plus la base de données (déplacée, pas le même micro…), cliquez sur pour désigner son chemin d'accès

b)   DÉTERMINATION CRITÈRES

La requête ouverte, les critères peuvent être ajoutés ou modifiés.

fenêtre query
indiquer directement le nouveau critère dans la zone de critères

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

les critères s'inscrivent dans un volet de la fenêtre et les valeurs affichées correspondent aux critères

  Plutôt qu'indiquer un critère complexe pour un même champ comme ici :
il vaut mieux 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ù")

Créer une source de données pour les fichiers "accdb" à partir de la base de données "northwind 2016 ios.accdb"
Afficher la table "employes", et extraire :
- les employés habitant une ville dont le code postal est strictement supérieur à 60000 et inférieur ou égal à 74000 exerçant la profession de "représentant commercial"
- ceux exerçant la profession de "Vice-président des ventes" quelle que soit leur ville
- ainsi que les employés exerçant la profession de "représentant commercial" habitant Cherbourg
Enregistrer la requête en cours sous le nom " requete_source2.dqy "

Penser à cliquer éventuellement sur pour actualiser les enregistrements affichés

c)    AJOUTER UNE TABLE

L'opération est simple.

fenêtre query
<clic g> sur
sélectionner la table
<clic g> sur puis

d)   RENVOYER VERS EXCEL

Une fois toutes les manipulations effectuées, il faut renvoyer les données dans Excel.

fenêtre query
<clic g> sur

Renvoyer les données de la requête en cours vers Excel, enregistrer et fermer le classeur

5.     EXTRACTION PAR REQUÊTE

Excel peut charger les données extraites à partir d'une requête portant sur des sources de données très variées.

a)   REQUÊTE IMMÉDIATE

Les données sont alors chargées directement dans Excel.

onglet "données"
groupe "récupérer et transformer" (2ème bloc)
<clic g> sur
<clic g> sur
sélectionner le format de la base
sélectionner le fichier
<clic g> sur
sélectionner la table ou la requête
<clic g> sur pur insérer les données dans Excel

Charger dans un nouveau classeur les données de "vente de produits par catégorie et par date" de Northwind 2016ios.accdb : et les afficher

b)   ÉDITEUR DE REQUÊTES

Il est alors possible de traiter les données dans l'éditeur de requête avant de les charger.

onglet "données"
groupe "récupérer et transformer" (2ème bloc)
<clic g> sur
<clic g> sur
sélectionner le format de la base
sélectionner le fichier
<clic g> sur
sélectionner la table ou la requête
ou
<clic g> sur pour éditer la requête
l'éditeur de requêtes s'affiche
modifier éventuellement la requête
de nombreuses modifications peuvent êtes exécutées ici mais moins simplement que dans Excel (changer les valeurs, modifier les formats, ajouter des colonnes, faire un tableau croisé…)
puis <clic g> sur

Sur une autre feuille du classeur en cours, créer une requête extrayant les données de "vente de produits par catégorie et par date" de Northwind 2016ios.accdb, les afficher dans l'éditeur de requêtes, les charger dans Excel puis enregistrer

Dans Excel, un <double clic> sur la requête dans le volet droit l'affiche dans l'éditeur de requête pour modification

6.     AUTRES SOURCES

Il est aussi possible d'extraire des données à partir du Web ; les données affichées sous forme de "table" sur la page peuvent être récupérées mais les données de la base liée ne peuvent évidemment pas être directement importées.

onglet "données"
groupe "données externes" (1er bloc)
<clic g> sur
saisir l'adresse du site puis
<clic g> sur pour cocher les tables (au sens internet de "tableau")
les tables cochées ont le signe à la place de
<clic g> sur
pour valider

Effectuez auparavant une recherche avec votre navigateur préféré puis copier/coller l'adresse internet dans la boîte de dialogue précédente

Le résultat peut être tout à fait satisfaisant pour les tableaux de type "liste" qu'il suffit alors de mettre en forme mais il peut aussi y avoir un "nettoyage" important à faire…

Importer les données à partir du lien suivant :
http://ark.intel.com/fr/products/family/88393/6th-Generation-Intel-Core-i5-Processors#@Desktop
afficher les propriétés de la connexion avec l'outil
ne laisser que les données utiles
enregistrer

permet de récupérer des données à jour mais fait perdre un éventuel "nettoyage" effectué

C.              LIAISON BASE EXTERNE

Excel permet de créer une liaison avec les données d'un SGBD supportant les liens DDE ou OLE. Les données ne peuvent alors pas être modifiées à partir d'Excel.

presse-papiers
1 - lancer l'application de gestion de base de données
ouvrir la base
sélectionnerla table (volet gauche)ou des lignes de la table (volet principal)
copier dans le presse-papiers (outil  )

2 - lancer  Excel(si ce n'est déjà fait)
se positionner dans la feuille de travail
<clic d> - (se déplacer sur les outils pour visualiser le résultat)
ou

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

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

La liaison n'est possible que si une table ou une requête complète est sélectionnée

Lancer Access puis ouvrir la base de données "Northwind 2016ios.accdb"
Copier la table "employes" dans une feuille Excel en conservant une liaison
Enregistrer le classeur Excel
Modifier la table dans Access (changer un prénom) puis fermer Access
Actualiser le classeur précédent dans Excel

Si les valeurs ne se mettent pas à jour dans Excel, vérifier que vous avez bien changé d'enregistrement dans Access, aller dans Excel et <clic g> sur
Si la mise à jour ne s'est toujours pas effectuée, fermer la base Access sans enregistrer (la modification des données est immédiate et ce ne serait ici que les éventuelles modifications de structure ou de présentation que vous enregistreriez) puis dans Excel <clic g> sur puis de l'onglet - Le délais de mise à jour peut-être plus ou moins long

IV.    TABLEAUX DE DONNÉES

Les tableaux de données permettent de traiter des volumes de données plus ou moins importants selon les modèles, de les présenter de manière synthétique et d'en tirer des informations. À noter que powerpivot est un espace de travail indépendant de Excel.

A.              TABLEAUX CROISES DYNAMIQUES

Les tableaux croisés offrent une présentation synthétique des données d'une liste Excel ou d'une base externe, en fonction des critères choisis par l'utilisateur.

1.     CRÉATION AUTOMATIQUE

Des tableaux tout faits sont proposés.

onglet "insertion"
groupe "tableaux" (1er bloc)
une cellule de données du tableau étant sélectionnée
<clic g> sur
dans le volet de gauche
sélectionner le modèle désiré
il s'affiche dans le volet de droite
pour valider
il s'affiche dans une nouvelle feuille de travail

L'assistant reste assez limité et la création personnalisée reste très simple donc préférer cette seconde solution

2.     CRÉATION PERSONNALISÉE

La création d'un tableau croisé est particulièrement simple puisqu'il suffit de faire glisser les champs concernés en ligne, colonne avec les calculs à l'intersection.

onglet "insertion"
groupe "tableaux" (1er bloc)
une cellule de données du tableau étant sélectionnée
<clic g> sur
vérifier l’origine des données et l’emplacement de création du tableau croisé
pour valider
le volet "office" affiche la structure du tableau croisé
faire glisser les champs à leur position

Si la création du tableau croisé échoue, désactiver dans les options avancées :

Modifier la présentation du volet "Office" en cliquant sur
Le déplacer en le faisant glisser par sa barre de titre sur le document

Le tableau est créé dynamiquement et offre une présentation synthétique et vivante des données au fur et à mesure du positionnement des champs.

Ouvrir le classeur tabcrois.xlsx
Créer à partir des données un tableau croisé permettant de lister les résultats par Région et par Nom
(voir solution : tabcrois terminé.xlsx feuille"solution 1")

3.     MODIFICATION

Un simple <clic g> dans le tableau croisé affiche sa structure dans le volet office ; toute modification est alors possible en temps réel.

volet office
décocher les éléments à ne pas prendre en compte
pour valider

Pour n'afficher que les valeurs correspondant à certains critères des champs ligne ou colonnes, il faut afficher les valeurs avec   et décocher les valeurs autres

coche toutes les valeurs et permet de les choisir

Afficher le classeur tabcrois.xlsx
Modifier le tableau croisé en affichant les ventes par Ville et par Produit
(voir solution : tabcrois terminé.xlsx feuille"solution 2")

4.     CALCULS

Une autre fonction que la somme peut être utilisée pour les champs de valeur.

bouton droit
dans le tableau croisé
<clic d> sur un champ de valeur
<clic g> 
choisir le type de calcul

pour valider

bouton gauche
dans le volet de structure du tableau
<clic g> sur   du champ de valeur
<clic g> 
choisir le type de calcul
pour valider

Afficher le classeur tabcrois.xlsx
Créer un tableau croisé de la moyenne des ventes par Région et par Produit
(voir solution ; tabcrois terminé.xlsx feuille"solution 3")

5.     PRESENTATION

La présentation du tableau croisé peut être optimisée, notamment en n'affichant plus les listes déroulantes de choix.

onglet "analyse"
groupe "afficher" (dernier bloc)
<clic g>  sur pour ne plus les afficher

B.              GRAPHIQUES CROISES DYNAMIQUES

Les graphiques croisés dynamiques sont créés de la même manière que les tableaux, aussi bien à partir d'une liste Excel que d'une base externe.

onglet "insertion"
groupe "graphiques" (4ème bloc)
une cellule de données étant sélectionnée
<clic g> sur   de
choisir avec ou sans tableau
confirmer l’origine des données
spécifier l’emplacement de création du graphique croisé
pour valider
le volet "office" affiche la structure du tableau croisé
faire glisser les champs à leur position

Pour intervertir les champs des axes, modifier dans le volet Office

Afficher le classeur tabcrois.xlsx
Créer un graphique croisé dynamique des ventes par Région et par Produit
Repositionner et mettre en forme ce graphique
(voir solution : tabcrois terminé.xlsx feuille"solution 4")

Le filtre s'affiche en cliquant sur de et permet d'utiliser tous les opérateurs de comparaison texte ou numériques comme dans le "filtre automatique"

Le tableau ou le graphique croisé dynamique peuvent être aussi créés de la même manière à partir de données externes

C.              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.

1.     CRÉER UN TABLEAU CROISÉ

Créer au préalable un tableau croisé dynamique puis transformer celui en cube olap.

onglet "insertion"
groupe "tableaux" (1er bloc)
une cellule de données du tableau étant sélectionnée
<clic g> sur
vérifier l’origine des données et l’emplacement de création du tableau croisé
pour valider
faire glisser les champs à leur position

Ouvrir le classeur "ventes par catégorie et par exercice"
Créer un tableau croisé avec les catégories en colonne

2.     TRANSFORMER EN CUBE OLAP

Créer le cube, c'est créé un système de références en 3 dimensions.

onglet "analyse"
groupe "calcul" (6ème bloc)
<clic g> sur
<clic g> sur
le tableau croisé est converti en cube olap

Les contenus des cellules sont remplacés par une formule faisant référence à leur position dans le cube

Dans le classeur "ventes par catégorie et par exercice"
Transformer le tableau croisé en cube olap
lui affecter une mise en forme simple

Le cube olap ne peut recevoir de mise en forme de type "mettre sous forme de tableau"

D.              POWERPIVOT / POWERVIEW

Powerpivot et Powerview vont permettre de gérer et représenter les données, qu'elles soient structurées sou forme de tableau Excel, de tableau croisé dynamique ou de cube olap.

. Ces fonctions sont des compléments intégrées dans Excel (accessibles par )
Si elles ne sont toujours pas affichées, aller dans la personnalisation du ruban et cocher le onglets

1.     UTILISATION POWERPIVOT

Powerpivot permet d'effectuer une puissante analyse de grandes quantités données, de créer des modèles de données élaborés et de les partager éventuellement dans un espace Sharepoint. Il peut intégrer des données d'Excel ou de tout autre logiciel dans lequel les données sont organisées en tables (Access, SqlServer…). C'est un outil idéal pour créer des tableaux de bord intégrant des données originaires de plusieurs sources.

Pour revenir à Excel, passer par la barre d'outils rapide ou fermer la fenêtre.

barre d'outils rapide
<clic g> sur

powerpivot
<clic g> sur

a)   AJOUTER AU MODÈLE

Il faut d'abord ajouter une première fois les données au modèle "powerpivot".

onglet powerpivot
groupe "tables"(4ème bloc)
une cellule de données sélectionnée
<clic g> sur  
la fenêtre "powerpivot" s'affiche

b)   UTILISER LE MODÈLE

Une fois les données ajoutées au modèle, il est possible de faire des allers/retours ente Excel et Powerpivot.

onglet powerpivot
groupe "modèle de données"(1er bloc)
une cellule de données sélectionnée
<clic g> sur

onglet "données"
groupe "outils de données"(5ème bloc)
une cellule de données sélectionnée
<clic g> sur

Ouvrir le classeur "powerpivot" et créer un modèle à partir de l'ensemble des données

c)    CRÉER UNE MESURE

Le champ calculé utilise des formules comparables à Excel dans leur finalité.

onglet "powerpivot"
groupe "calculs"(2ème bloc)
<clic g> sur
<clic g> sur
saisir le nom du champ et sa description
<clic g> sur et choisirune fonction (noms en anglais)
saisir le début du nom de champ puis <double clic> dans la liste pour choisir
fermer la parenthèse
pour valider
La mesure s'affiche dans le volet bas de la feuille Powerpivot
utiliser les outils de mise en forme

Si la mesure ne s'affiche pas, <clic g> sur pour l'afficher

de permet de créer, modifier, supprimer les champs calculés

Dans le classeur "powerpivot"
Créer une mesure faisant la moyenne des données
L'afficher dans la feuille "powerpivot"

d)   INDICATEUR DE PERFORMANCE

L'indicateur de performance est basé sur une mesure.

onglet "powerpivot"
groupe "calculs"(2ème bloc)
<clic g> sur
<clic g> sur
choisir la mesure
indiquer si elle est basée sur une valeur absolue ou relative
choisir la représentation
pour valider
cet indicateur pourra être affiché dans Powerview

L'indicateur s'affiche avec la mesure servant aux calculs.

Dans le classeur "powerpivot"
Créer un indicateur de performance basé sur la mesure "moyenne" avec une valeur cible de 100

e)   GERER LES DATES

Powerpivot permet de créer une table de dates ou d'utiliser un champ comme table de dates. Ces dernières doivent cependant être uniques.

powerpivot
onglet groupe "calendriers" (4ème bloc)
<clic g> sur pour créer un calendrier
ou <clic g> sur pour marquer une colonne comme table de dates

2.     AFFICHAGE DANS POWERVIEW

C'est dans powerview que s'affichera la représentation des données de powerpivot.

Si la commande ne figure pas dans le ruban, il faut l'ajouter :

a)   AFFICHER LES DONNÉES

Cet affichage passe par la création d'une feuille " powerview " dans Excel.

onglet "insertion"
groupe "rapports"(5ème bloc)
<clic g> sur
une feuille powerview s'affiche avec les données de powerpivot

b)   SELECTIONNER LES DONNÈES

Les données à afficher dans la feuille sont celles de powerpivot ; elles peuvent donc être sélectionnées lors de la création du modèle. Elles peuvent aussi être masquées dans powerpivot pour ne pas être affichées dans powerview.

powerpivot
sélectionner la tête de la colonne à masquer
<clic d>

powerview
<clic g> sur d'un champ de colonne
décocher les éléments à ne pas afficher

permet de les réafficher

c)    STRUCTURER LES DONNÈES

La présentation des données dans powerview s'effectue un peu de la même manière que pour un tableau croisé.

powerview
<faire glisser> les champs à utiliser dans le tableau
le tableau se forme au fur et à mesure
<faire glisser> les champs à utiliser comme filtre dans la zone "filtre"
cocher ou décocher les valeurs à filtrer

Le tableau se crée au fur et à mesure.

bouton gauche
<clic g> sur un entête pour afficher la valeur

Dans le classeur "powerpivot"
Créer un tableau powerview affichant les ventes par ville avec un filtre par type d'outils

d)   CRÉER UN GRAPHIQUE

Les données gagnent à être représentées graphiquement.

onglet conception
groupe "basculer la visualisation"(1er bloc)
<clic g> dans les données powerview
<clic g> sur un type de graphe
adapter la taille du graphe
paramétrer les données dans le volet droit

Dans la feuille powerview, afficher un graphe comme ci-après par ville avec un filtre par type d'outils

e)   CRÉER UNE CARTE

On retrouve ici la possibilité de présenter les données sur des cartes.

onglet conception
groupe "basculer la visualisation"(1er bloc)
<clic g> dans les données powerview
<clic g> sur
une carte s'affiche avec une représentation automatique des données

Dans la feuille powerview, afficher une carte comme ci-après

  et permettent d'agrandir ou de réduire le graphique

"powerpivot" et "powerview" sont des outils puissants et en pleine évolution mais si vous ne les utilisez pas, n'activez pas ces compléments car ils insèrent de nouveaux outils dans le ruban qui pourraient vous perturber

3.     EXERCICE

Ouvrir le classeur "ventes par date et catégorie"
Créer un modèle powerpivot à partir du cube olap
Créer une mesure faisant la moyenne des données
L'afficher dans la feuille "powerpivot"

Dans le classeur "ventes par date et catégorie"
Créer un indicateur de performance basé sur le champ calculé "moyenne" avec une valeur cible de 300

Dans le classeur "ventes par date et catégorie"
Créer un graphique powerview comme ci-dessous

V.         TABLES D'HYPOTHÉSES

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.

A.              HYPOTHÈSE A UNE VARIABLE

Une table d'hypothèse peut concerner une ou plusieurs formules mais avec une seule entrée: Une seule des cellules d'entrée doit alors être renseignée (de préférence, celle des colonnes).

onglet "données"
groupe "prévisions" (6ème bloc)
sélectionner la plage où va s'inscrire la table
<clic g> sur
<clic g> sur
indiquer la cellule d'entrée (ligne ou colonne)
pour valider

paramètres

plage de la table
plage où vont s'inscrire les différentes valeurs incluant la colonne ou la ligne de référence aux formules

cellule d'entrée en ligne ou colonne
cellule définissant la variable à analyser

1.     TABLE NON LIÉE À UNE BASE

Si la table n'est pas liée à une base de données, les paramètres de création sont les suivants :

cellule d'entrée
C'est la variable, base de l'analyse de la formule.

la table
indiquer en première ligne les formules (référence obligatoire à 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

Un représentant a une commission de 3% du chiffre d'affaires encaissé.
L'entreprise, pour calculer son résultat, doit diminuer le chiffre d'affaires de 3200 € de couts fixes ainsi que de la commission du commercial.
Représenter ce problème avec Excel : définir une table donnant pour divers taux (5% à 10%)  la commission payée au représentant puis calculer le résultat afférent pour l'entreprise
(voir problème tabhyp.xlsx, feuille " prob 1 a " et solution  tabhyp terminé.xlsx feuille "hyp1 a")

paramètres

cellule d'entrée colonne : B1

plage sélectionnée : B11.D17

C11 : référence cellule commission soit$ B$5 (à recopier)

D11 : référence cellule résultat soit $B$7 (à recopier)

2.     TABLE LIÉE À UNE BASE

Si la table est liée à une base de données, les paramètres sont les suivants :

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.

Afficher la feuille "prob 1 b" du classeur "tabhyp.xlsx"
A partir de ce tableau, calculer pour chaque région, l'ancienneté moyenne des commerciaux, leurs ventes moyennes ainsi que leur ventes totales
Calculer les résultats à l'aide d'une zone de critères et de la fonction bdmoyenne

Représenter les données à l'aide d'une table de données par région (voir problème tabhyp.xlsx, feuille " prob 1 b " et solution  tabhyp terminé.xlsx feuille "hyp1 b")

paramètres

C21 ancienneté moyenne : =BDMOYENNE(A1:E17;4;A19:A20)(à saisir)
N° De Rubrique/Critère : 4

D21 ventes : =BDSOMME(A1:E17;5;A19:A20) (à saisir)
N° De Rubrique/Critère : 5

E21 ventes moyennes : =BDMOYENNE(A1:E17;5;A19:A20) (à saisir)
N° De Rubrique/Critère : 5

Champs De Données : A1: E17

Cellule d'entrée colonne : A20

Champs De Critères : A19.A20

Sélection : B21 : E25

B.              HYPOTHÈSE A DEUX VARIABLES

Une table d'hypothèse peut concerner une seule formule mais deux entrées. Il faut alors renseigner ces deux cellules.

onglet "données"
groupe "prévisions" (6ème bloc)
sélectionner la plage où va s'inscrire la table
<clic g> sur
<clic g> sur
indiquer la cellule d'entrée (ligne ou colonne)
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 À UNE BASE

Si la table n'est pas liée à une base, les paramètres de création sont les suivants :

cellules d'entrées
Ce sont les variables qui sont à la base de l'analyse de la formule.

la table d'hypothèses
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

formule
l'indiquer dans la cellule située à l'intersection de la colonne et de la ligne des valeurs prises par les rubriques.

les intersections lignes/colonnes sont renseignées par Excel

Afficher la feuille "prob 2 a" du classeur "tabhyp.xlsx"
Définir une table comparant les remboursements en fonction du capital emprunté (de 150.000 à 500.000 € par pas de 50.000 €) et des taux d'emprunt (de 8 à 13,5 % par pas de 0,5%) - (voir problème tabhyp.xlsx, feuille " prob 2 a " et solution  tabhyp terminé.xlsx feuille "hyp 2 a")

paramètres

cellule d'entrée ligne : B4

cellule d'entrée colonne : B11

N3 : formule  =VPM(+($B$11*$B$12/$B$13);+$B$14*$B$13;-$B$4) (à saisir)

O3:V3 : valeurs de capital (à saisir)

N4:N15 : valeurs des taux(à saisir)

Sélection : N3 : V15

table d'hypothèses (N3:V15)

2.     TABLE LIÉE À UNE BASE

Si la table est liée à une base de données, les paramètres de création sont les suivants :

le champs de critères
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
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
l'indiquer dans la cellule vide située à l'intersection de la colonne et de la ligne des valeurs prises par les rubriques.

paramètres

cellule d'entrée ligne : A68

cellule d'entrée colonne : B68

B70 : formule  =BDSOMME(A1:E65;5;A67:B68) (à saisir)
N° De Rubrique/Critère : 5

C70:G70 : nom des produits (à saisir)

B71:B75 : nom des régions (à saisir)

Cellule d'entrée ligne : A68

Cellule d'entrée colonne : B68

Champs De Critères : A67.B68

Sélection : B70 : F74

table d'hypothèses (B70:G75)

Afficher la feuille "prob 2 b" du classeur "tabhyp.xlsx"
Définir une table affichant les ventes totales par région et par catégorie d'outils
(voir problème tabhyp.xlsx, feuille " prob 2 b " et solution  tabhyp terminé.xlsx feuille "hyp2 b")

VI.    AUTRES FONCTIONS

Ces fonctions sont généralement liées à une utilisation avancée d'Excel.

A.              GÉNÉRALITÉS

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.

1.     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 arguments : 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 insérée à partir de la barre de formule ou du ruban ou bien encore écrite telle quelle

onglet "formules"
groupe "bibliothèque des fonctions"(1er bloc)
sélectionner la cellule
<clic g> sur
ou
<clic g> sur une catégorie de fonction
<clic g> sur la fonction
saisir ou désigner les divers arguments
<entrée> pour valider

2.     TYPES DE FONCTION

Les fonctions sont différentes selon les domaines qu'elles recouvrent.

financier (investissements, amortissements, emprunts)

logique ;

mathématique(général ou trigonométrique) ;

statistique(général ou base de données) ;

Elles peuvent aussi être directement liées au contenu des cellules, leur champ d'action est alors :

l'information, la consultation...

la recherche, le remplacement...

la date, l'heure...

3.     ARGUMENTS

Les arguments sont les éléments variables. Ils 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(nombre, formule )

texte (suite de caractères alphanumériques entre guillemets)

valeurs logiques(utilisant les opérateurs logiques)

valeurs d'erreur

Ces catégories d'argument peuvent être indirectement 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.     RÈGLES D'UTILISATION

débuterpar 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 les options d'Excel, sélectionner  (en bas), puis    et cocher les compléments nécessaires

B.              FONCTIONS LOGIQUES

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 condition remplie ;

le troisième argument donne la valeur de la cellule condition non 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)

Que viennent compléter la mise en forme conditionnelle

Qui n'est pas une fonction logique mais qui obéit aux mêmes principes.

1.     FORMAT CONDITIONNEL

Il permet d'afficher une mise en forme différente selon la valeur de la cellule.

a)   CONDITIONS SIMPLES

Des opérateurs logiques sont utilisés pour définir le format.

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer la règle à appliquer
renseigner les conditions d'application et le format correspondant
pour valider

Lorsque vous appelez l'outil de mise en forme conditionnelle sur une plage qui a déjà reçu une mise conditionnelle, les caractéristiques de cette dernière s'ajoutent à la précédente

Vous pouvez aussi appliquer sur une cellule puis

Utiliser l'outil de recopie de mise en forme pour copier la mise en forme conditionnelle sur d'autres cellules et l'outil pour l'effacer

Ouvrir le classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "ventes", afficher les outils selon les critères suivants :
Pour les ventes supérieures à 50000 €, Police verte sur fond vert
Pour les ventes inférieures à 50000 €, Police bleue sur fond bleu

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "outils", afficher les outils selon les critères suivants :
Police jaune/orange pour les marteaux, rouge pour les tournevis, vert pour les scies

b)   VALEURS REMARQUABLES

Excel permet d'attribuer une mise en forme spécifique aux valeurs remarquables (valeurs les plus élevées, les moins élevées, supérieures ou inférieurs à la moyenne).

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer la règle à appliquer
renseigner les conditions d'application et le format correspondant
pour valider

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", afficher les valeurs selon les critères suivants :
Rouge pour les valeurs inférieures à la moyenne
Vert pour les valeurs supérieures à la moyenne

c)    BARRES DE DONNÉES

Elles affichent dans la cellule une barre plus ou moins longue selon la valeur dans la cellule. Elles sont à la fois très esthétiques et très parlantes.

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les barres pour les visualiser
<clic g> sur la barre à utiliser

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", compléter la mise en forme conditionnelle par une barre de données bleu foncé

d)   NUANCES DE COULEURS

La nuance de la couleur utilisée dans le dégradé représente ici la valeur de la cellule.

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les nuances pour les visualiser
<clic g> sur la nuance à utiliser

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Dans la colonne "ventes", utiliser une nuance de couleurs "dégradé"

e)   ICÔNES

Des icônes symbolisent la valeur de la cellule par rapport à l'ensemble des données. Le calcul est différent selon le nombre de symboles du jeu d'icônes.

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les jeux d'icônes pour les visualiser
<clic g> sur le jeu d'icônes à utiliser

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Dans la colonne "ventes", ajouter à la nuance un jeu d'icônes

permet de modifier les critères de comparaison

f)     GESTION DES RÈGLES

Elle permet de combiner les options proposées.

onglet "accueil" 
groupe "style"(5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer la feuille de calcul

effectuer les modifications
pour valider

La gestion des règles permet de récapituler les règles appliquées sur une plage, de les modifier ou les supprimer

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

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Afficher les règles

2.      =SI(test;valeur_si_vrai;valeur_si_faux)

Cette fonction contrôle la valeur affichée par la cellule en fonction d'une condition.

onglet "formules" 
groupe "bibliothèque de fonctions"(1er bloc)
<clic g> sur la première cellule de la plage
<clic g> sur
sélectionner la fonction
indiquer le test
indiquer la valeur si la condition est remplie
indiquer la valeur si la condition n'est pas remplie
pour valider
recopier la formule sur les autres cellules de la plage

Il est plus simple de renseigner d'abord la première cellule de la plage puis, lorsque le résultat est satisfaisant, de recopier ce résultat sur les autres cellules de la plage

Dans le même classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "commissions" calculer la commission des commerciaux pour chaque vente selon les critères suivants :
10% pour les ventes inférieures à 50 000 €
5% pour les ventes supérieures à 50 000 €

Plutôt qu'indiquer des valeurs, indiquer l'emplacement de ces valeurs dans la feuille ; cela donne beaucoup plus de souplesse pour toute modification ou simulation

Dans le même classeur "représentants.xlsx", feuille "commissions"
Faire un tableau des commissions dans la feuille (en I1:K2 par exemple)

Utiliser ce tableau pour calculer les commissions

Une fois la cellule renseignée, la fonction SI s'affiche de la manière suivante : 

=SI(test;condition oui;condition non)

dans lequel :

= égal 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 letest est négatif

; point virgule caractères séparateurs indispensables entre test, condition oui
et condition non.

Il est possible d'imbriquer des fonctions "si" les unes dans les autres de façon à gérer une multitude de cas ; la fonction doit alors être écrite manuellement
Créer d'abord la fonction "si" principale
Dans la condition "non", indiquer une nouvelle fonction "si"
Répéter l'opération pour chaque condition "non"
etc… (jusqu'à 64 niveaux d'imbrication sont possibles)
Penser à fermer avec le nombre de parenthèses adéquates

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)

Écrivez un minimum dans la formule même et faites au maximum référence à des cellules contenant les valeurs, le texte ou les formules afin d'apporter un maximum de souplesse à votre modèle ; vous pourrez ainsi faire toutes les modifications que vous souhaiterez dans ces cellules sans avoir à modifier la formule

Dans le même classeur "représentants.xlsx", feuille "outils"
Faire une colonne "commissions" calculant la commission des commerciaux pour chaque vente selon les conditions suivantes :
10% pour les marteaux / 8% pour les tournevis / 6% pour les pinces / 4% pour les scies

Si les conditions OUI ou NON sont du texte et que vous écrivez la formule, pensez à mettre ce texte entre double guillemets

Pour définir des catégories de valeurs, encadrez ces dernières avec des opérateurs "inférieur" ou "supérieur" successifs. Penser à utiliser selon les besoins "< inférieur" ou "<= inférieur ou égal" et "> supérieur" ou ">= supérieur ou égal"

Pour définir des catégories de 0 à 100 000 par tranche de 20 000
<20 000 définit la première catégorie
<40 000 définit la seconde
(entre 20000 et 40000 puisque ceux inférieurs à 20000 ont déjà été extraits)
<60000 définit la troisième (entre 40000 et 60000)
<80000 définit la troisième (entre 60000 et 80000)
<100000 définit la dernière (entre 80000 et 100000)

  Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Faire une colonne "commissions" calculant la commission des commerciaux, soit :
10% pour la part de chiffre d'affaire réalisé inférieure à 50000 €
8% pour la part comprise entre 50 et 75000 €
6% pour la part comprise entre 75 et 100000 €
4% pour la part supérieure à 100000 €

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 pourrez y revenir plus tard en enlevant les guillemets (pour qu'elle soit de nouveau considérée comme une formule)

Afficher le classeur "emprunt.xlsx"
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é
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.

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, elle 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".

Ouvrir le classeurfonctlogi.xlsx et tester les fonctions

C.              FONCTIONS DE DATE ET D'HEURE

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. (adapter la largeur des colonnes au format retenu -voir foncdate.xls)
Les numéro_de_sériessont 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.

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 recalculdu tableau (touche f9 du clavier).

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.

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é.

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 de l'heure spécifiée comme argument.)

14.  =TEMPSVAL(heure_texte)

Cette fonction transforme une heure mentionnée sous forme de texte en 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.

Ouvrir le classeur "dates.xls"
Sur une base de 360 jours par an et à raison de 30 jours par mois pour les mois pleins (quel que 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.
Calculer, dans un second temps, le nombre de jours ouvrés entre ces deux dates

D.              FONCTIONS MATHÉMATIQUES

Leur argument est une valeur, une formule ou encore l'emplacement des données, que ce soit sous forme d'adresse de cellule ou de nom de plage. (voir foncmath.xlsx)

1.     FONCTIONS GÉNÉRALES

Elles sont d'un usage courant dans la vie professionnelle et, d'une manière générale, dès que l'on manie un peu les chiffres. (voir foncmath.xls)

a)   =ABS(x)

Cette fonction donne la valeur absolue (sans signe) d'un chiffre ou d'une formule.

b)   =ALEA()

Cette fonction génère un nombre aléatoire (au hasard) compris entre 0 et 1 et différent à chaque recalcul de la feuille. Il suffit de multiplier par 10 ou 100 pour obtenir un chiffre entre 1 et 10 ou entre 1 et 100.
Pour afficher le nombre sans décimales, utiliser un format nombre sans décimales ; pour transformer le chiffre décimal en nombre entier, utiliser la fonction =ENT(X) ou =ARRONDI(X;0).

c)    =ALEA.ENTRE.BORNES(inf;sup)

Cette fonction génère un nombre aléatoire (au hasard) compris entre inf et sup, bornes inférieures et supérieures et différent à chaque recalculde la feuille (touche f9 du clavier).

d)   =ARRONDI(x;n)

Cette fonction donne la valeur arrondie de x à la nième décimale supérieure.

e)   =ENT(x)

Cette fonction donne la valeur arrondie à l'entier immédiatement inférieur d'un nombre ou d'une formule.

f)     =PRODUIT(liste)

Cette fonction donne le produit de la multiplication des nombres listés ou séparés par ";".

g)   =RACINE(x)

Cette fonction donne la racine carrée d'un chiffre positif.

h)   =SOMME(liste)

Cette fonction additionne les valeurs de la liste.

i)     =TRONQUE(x;n)

Cette fonction donne la valeur tronquée à n décimales d'un chiffre ou d'une formule. Elle ne diffère de =ENT(), en ce qui concerne l'affichage, que pour les chiffres négatifs.

Ouvrir le classeur "fonctmath.xls"
Calculer les fonctions mathématiques suivantes à partir d'une ou plusieurs cellules de ce tableau : arrondi, tronque, abs, ent, alea, produit, somme, racine

2.     FONCTIONS EXPONENTIELLES

Ce sont les fonctions liées à la notion de logarithme.

a)   =BASE(x;base_cible;précision)

Cette fonction donne sous forme de texte l'équivalent d'un nombre en base 10 dans une autre base.

b)   =EXP(x)

Cette fonction calcule la valeur de e (2,7182) élevé à la puissance X (-227<x<230).

c)    =LN(x)

Cette fonction calcule le logarithme népérien (base e) de X (x>0)

d)   =LOG(x;n)

Cette fonction calcule le logarithme de X en base n.

e)   =LOG10(x)

Cette fonction calcule le logarithme de X en base 10.

f)     =MOD(x;y)

Cette fonction calcule le reste de la division de X par Y (y<>0).

3.     FONCTIONS MATRICIELLES

Ces fonctions permettent d'effectuer des opérations sur les matrices, les matrices pouvant être particulièrement pratiques pour certains types de calculs mathématiques (résolution d'équations voir matrice.xls)

a)   =DETERMAT(matrice)

Cette fonction renvoie le déterminant d'une matrice.

b)   =INVERSEMAT(matrice)

Cette fonction calcule la matrice inverse.

c)    =PRODUITMAT(matrice)

Cette fonction calcule le produit de deux matrices.

d)   =SOMMEPROD(matrice1;matrice2;...)

Cette fonction calcule la somme des produits de deux matrices.

Ouvrir le classeur "matrice.xlsx"
A partir des 2 matrices de base a et b,
Calculer le produit des 2 matrices
Inverser la matrice d14:g17
Calculer la somme des matrices d14:g17 et d19:g22
Calculer le déterminant de la matrice d14:g17

4.     FONCTIONS TRIGONOMÉTRIQUES

Le résultat de ces fonctions correspond à celui des tables trigonométriques. Les principales fonctions trigonométriques existent aussi sous la forme indiquée
ci-dessous, suivie d'un H (acosh, asinh, atanh, cosh, sinh, tanh) ; elles donnent alors la valeur hyperbolique et non plus la valeur standard.

a)   =ACOS(x)

Cette fonction donne la valeur en radians de l'angle dont le cosinus est x (arc cosinus X).  (-1<x<+1).
Le résultat est compris entre 0 et ¶ (PI).

b)   =ASIN(x)

Cette fonction donne la valeur en radians de l'angle dont le sinus est x (arc sinus X). (-1<x<+1).
Le résultat est compris entre -¶/2 et + ¶/2.

c)    =ATAN(x)

Cette fonction calcule la valeur en radians de l'angle dont la tangente est X (arc tangenteX).
(x quelconque). Le résultat est compris entre -¶/2 et +¶/2;

d)   =ATAN2(x,y)

Cette fonction calcule la valeur en radians de l'angle dont la tangente est Y/X (arc tangente de Y surX). (x<>0, Yquelconque). Le résultat est compris entre - ¶ et +¶.

e)   =COS(x)

Cette fonction calcule le cosinus de l'angle X exprimé en radians.

f)     =PI

Cette fonction produit la valeur de ¶ (PI), soit 3,14 avec une précision de 10 décimales. Elle permet aussi de transformer, dans toutes les fonctions trigonométriques, les résultats de radians en degrés :
Pour ce faire : multiplier dans la formule X par l'expression =PI/180.
La fonction =ACOS(X*=PI/180) donne le cosinus de X en degrés.

g)   =SIN(x)

Cette fonction calcule le sinus de l'angle X exprimé en radians.

h)   =TAN(x)

Cette fonction calcule la tangente de l'angle X exprimé en radians.

E.               FONCTIONS STATISTIQUES

Elles figurent dans le groupe "fonctions statistiques" mais aussi dans le groupe "fonctions de compatibilité". Elles permettent d'obtenir des informations quantitatives sur un ensemble de données. Ce sont le plus souvent les références d'une plage de cellules qui désignent la série. Elles permettent d'en extraire le nombre, les extrêmes, la moyenne, l'écart type, la variance.
Les fonctions statistiques de Excel sont nombreuses et correspondent à l'application de techniques statistiques élaborées (khi deux, loi de Fisher, de Pearson, de Poisson, de Student...). Sont étudiées ici les fonctions les plus courantes, liées à une série d'observations.

(Fréquentation et ventes d'un parc d'attraction en fonction de la température)

Lorsqu'il existe une corrélation entre les différents facteurs, il est intéressant de les représenter sous forme de nuages de points et de calculer un trend (tendance) à l'aide d'une droite de régression linéaire dont la pente et la distance à l'ordonnée peuvent être calculés par l'intermédiaire des fonctions appropriées ; cette droite de "tendance" peut aussi être directement insérée dans le graphique en nuage de points en choisissant l'outil de disposition :

(foncstat.xls-graphe visiteurs)

1.      =ECARTYPE.STANDARD(liste)

Cette fonction calcule l'écart type de la liste de données ; c'est à dire la dispersion des points par rapport à la moyenne. La base de l'estimation est un échantillon (celui-ci devant être représentatif).

Plus l'écart type est faible, plus les points sont regroupés autour de la moyenne et donc, plus l'ensemble est homogène.
Plus l'écart type est grand, moins les données sont homogènes. Elles sont alors souvent inexploitables d'un point de vue statistique car on ne peut en retirer aucune règle, aucune tendance

2.     =ECARTYPE.PEARSON(liste)

Cette fonction calcule l'écart type de la liste de données ; c'est à dire la dispersion des points par rapport à la moyenne. La base de l'estimation est la population entière.

3.      =NB(liste)

Cette fonction calcule le nombre de données de la liste (le nombre de cellules occupées d'une plage).

4.      =MAX(liste)

Cette fonction extrait la valeur maximale de la liste.

5.      =MEDIANE(liste)

Cette fonction extrait la valeur qui partage la liste en deux sous-ensembles égaux.

6.      =MIN(liste)

Cette fonction extrait la valeur minimale de la liste.

7.      =MOYENNE(liste)

Cette fonction extrait la valeur moyenne de la liste. La base de l'estimation est un échantillon et non la population entière.

8.      =VARP(liste)

La variance est le carré de l'écart type. La base de l'estimation est ici la population entière.

9.      =CENTILE.INCLURE(LISTE,K)

Un centile est chacune des 99 valeurs qui divisent les données triées en 100 parts égales, de sorte que chaque partie représente 1/100 de l'échantillon ou de la population. Cette formule renvoie le kième centile des valeurs d'une plage où k se trouve compris dans la plage de 0 à 1.

10.  =INTERVALLE.CONFIANCE.NORMAL (precision,ecart type, taille)

Un intervalle de confiance permet d'évaluer la précision de l'estimation d'un paramètre statistique sur un échantillon. La formule renvoie l'intervalle de confiance pour la moyenne d'une population, basé sur la loi normale..

11.  =INTERVALLE.CONFIANCE.PEARSON (precision,ecart type, taille)

La formule renvoie l'intervalle de confiance pour la moyenne d'une population, basé sur la loi de Pearson.

12.  =ORDONNEE.ORIGINE(X connu;Yconnu)

Cette fonction détermine la distance entre l'origine et le point ou la courbe de tendance coupe cette origine, ce qui équivaut au coefficient b de la droite de régression : y=ax + b.

13.  =DROITEREG(X connu;Yconnu)

Cette fonction détermine la pente de la courbe de tendance, ce qui équivaut au coefficient a de la droite de régression :  y=ax + b.

Ouvrir le classeur " foncstat.xlsx"
Calculer pour la colonne visiteurs les principales fonctions statistiques (minimum, maximum, moyenne, somme, médiane, variance, écart type).
Représenter ces données graphiquement et calculer le trend (tendance)

14. ANALYSE

Un utilitaire d'analyse avancé est disponible sous Excel.

S'il n'est pas affiché :
Aller dans le menu "fichier" et dans les options d'Excel, dans le volet gauche "compléments"
Les options d'analyse sont alors disponibles dans l'onglet "données", bloc "analyse"

onglet "données"
groupe "analyse"(dernier bloc)
<clic g> sur
indiquer le type d'analyse
sélectionner la plage de données
pour valider

Ouvrir le classeur " foncstat2.xlsx"
Effectuer une analyse de variance à un facteur puis à deux facteurs sans répétition d'expérience puis de corrélation sur des feuilles séparées

F.               FONCTIONS DE CONSULTATION

Les fonctions de consultation permettent d'afficher des informations concernant une cellule ou le contenu de la cellule. Des fonctions spécifiques permettent d'obtenir les mêmes résultats dans l'utilisation des macro-commandes (voirfoncspec.xlsx).

1.      =ADRESSE(no_ligne;no_colonne)

Cette fonction crée une adresse de cellule sous forme de texte, selon les numéros de ligne et de colonne spécifiés.

2.      =COLONNES(matrice)

Cette fonction compte le nombre de colonnes du champ spécifié.

3.      =INDIRECT(référence)

Cette fonction indique le contenu de la cellule indiquée par sa référence dans une autre cellule.

4.      =LIGNES(matrice)

Cette fonction compte le nombre de lignes du champ spécifié.

5.      =CHOISIR(no_index;Val1.Val2.Val3...)

Cette fonction affiche le contenu de la cellule dont la position est indiquée par un numéro et faisant partie d'une liste de cellules dont les adresses sont séparées par un point-virgule.

6.      =INDEX(matrice;no_ligne;no_colonne)

Cette fonction affiche le contenu de la cellule aux positions lignes et colonnes spécifiées de la matrice indiquée, ces positions étant calculées par rapport à l'origine de cette matrice.

Ouvrir le classeur "fonctspec.xlsx"
A l'aide de la fonction "indirect" renseigner le tableau pour pouvoir calculer la commission pour un article donné
Calculer le résultat des principales fonctions évoquées ci-dessus

G.              LES FONCTIONS TEXTE

Les fonctions de chaîne réalisent des opérations sur des chaînes de caractères (libellés, formules, nombres précédés d'un guillemet...). Leur utilisation reste peu fréquente (traitement de données provenant d'un autre logiciel…).

1.     CARACTÈRES LICS

Les caractères lics correspondent aux caractères internationaux et symboles spéciaux ; Ils sont créés par l'utilisation de la touche <alt> ou la fonction =car.

a)   =CAR(nombre)

Cette fonction génère le caractère correspondant au nombre saisi.

b)   =CODE(texte)

Cette fonction donne le code LICS du premier caractère de la chaîne.

c)    =EPURAGE(texte)

Cette fonction supprime les caractères de mise en forme pouvant subsister après l'importation d'un document d'une autre application.

Ouvrir le classeur "fonctext.xlsx".
Calculez les fonctions ci-dessus

2.     AFFICHAGE LIBELLES

Les fonctions suivantes vont permettre d'afficher, partiellement ou en totalité, une chaîne de caractères précisée comme argument ou dont l'adresse est précisée comme argument.

a)   =DROITE(texte;nb caractères)

Cette fonction extrait les n derniers caractères d'une chaîne.

b)   =GAUCHE(texte;nb caractères)

Cette fonction extrait les n premiers caractères d'une chaîne.

c)    =STXT(texte;nb_départ;nb caractères)

Cette fonction extrait les n premiers caractères d'une chaîne à partir de la position donnée.

Ouvrir le classeur "fonctext.xlsx".
Calculez les fonctions ci-avant

3.     INFORMATIONS LIBELLES

Les fonctions suivantes donnent des informations sur des libellés contenus dans des cellules.

a)   =CHERCHE(texte_cherché;texte_départ)

Cette fonction recherche dans la chaîne 2 la position de la chaîne 1 en commençant au nième caractère et admet les caractères génériques.

b)   =NBCAR(texte)

Cette fonction compte le nombre de caractères de la chaîne.

c)    =TROUVE(texte_cherché;texte_départ)

Cette fonction recherche dans la chaîne 2 la position de la chaîne 1 en commençant au nième caractère. La position des caractères recherchés étant connue, il est possible de les modifier par la fonction =remplacer. Cette fonction opère la distinction minuscules/majuscules.

Dans le classeur "fonctext.xlsx".
Calculez les fonctions ci-avant

4.     MODIFICATION LIBELLES

Les fonctions suivantes permettent de modifier des chaînes de caractères.

a)   =REMPLACER (ancien_texte;no_départ;no_car;nouveau_texte)

Cette fonction remplace dans la référence l'ancien texte par le nouveau à partir du no de caractère donné et pour le nombre de caractères donné.

b)   =REPT(texte;nombre_de_fois)

Cette fonction répète n fois la chaîne de caractères spécifiée comme argument.

c)    =SUBSTITUE (référence;ancien_texte;nouveau_texte;occurrences)

Cette fonction remplace dans la référence l'ancien texte par le nouveau à la position de l'occurrence indiquée.

d)   =SUPPRESPACE(texte)

Cette fonction supprime les espaces de la chaîne (utile dans les importations de texte).

Dans le classeur "fonctext.xlsx".
Calculez les fonctions ci-avant

5.     MAJUSCULES/minuscules

Les fonctions suivantes permettent de modifier les combinaisons de majuscules et de minuscules d'une chaîne de caractères.

a)   =MAJUSC(texte)

Cette fonction met en majuscules tous les caractères de la chaîne.

b)   =MINUSC(texte)

Cette fonction met en minuscules tous les caractères de la chaîne.

c)    =NOMPROPRE(chaîne)

Cette fonction met en majuscules le premier caractère des mots de la chaîne et les autres en minuscules.

Dans le classeur "fonctext.xlsx".
Calculez les fonctions ci-avant

6.     CONVERSION

Les fonctions suivantes permettent de convertir des valeurs en libellés et réciproquement.

a)   =CNUM(texte)

Cette fonction convertit en valeur numérique le nombre entré comme libellé.

b)   =DEVISE(nombre;décimales)

Cette fonction convertit un nombre en texte au format indiqué (selon formats de la boîte de dialogue "format de cellule - nombre").

c)    =T(valeur)

Cette fonction renvoie le texte de l'argument si celui-ci est du texte, sinon "".

d)   =TEXTE(valeur;format_texte)

Cette fonction convertit la valeur numérique x ou dont l'adresse est x en un texte affichant n décimales.

Dans le classeur "fonctext.xlsx".
Calculez les fonctions ci-avant

7.     TESTS

Les fonctions suivantes permettent de tester des chaînes de caractères.

a)   =EXACT(texte1;texte2)

Cette fonction compare la chaîne 1 à la chaîne 2, affiche vrai si les chaînes sont identiques et faux si elles ne le sont pas.

Dans le classeur "fonctext.xlsx".
Calculez la fonction ci-avant

H.              FONCTIONS D'INFORMATION

Les fonctions spéciales permettent d'obtenir des informations sur les cellules ou les champs. Des fonctions spécifiques permettent d'obtenir les mêmes résultats dans l'utilisation des macro-commandes (voirfonclogi.xlsx).

1.     FONCTIONS D'ERREUR

Elles permettent d'afficher dans une cellule les messages d'erreur ou de non disponibilité.

a)   =NA()

Cette fonction génère la valeur NA (non disponible). Elle est le plus souvent utilisée en conjonction avec la fonction =SI.

2.     FONCTIONS LOGIQUES

a)   =ESTERR(valeur)

Cette fonction affiche la valeur VRAI si la cellule a une valeur # (erreur) et FAUX dans le cas contraire sauf valeur NA (non disponible).

b)   =ESTERREUR(valeur)

Cette fonction affiche la valeur "vrai" si la cellule a une valeur # (erreur) et "faux" dans le cas contraire y compris valeur NA (non disponible). Les valeurs d'erreur sont : #n/a, #valeur, #ref, #div/0, #nombre, #nom, #.

c)    =ESTLOGIQUE(valeur)

Cette fonction affiche la valeur "vrai" si la cellule a une valeur logique et "faux" dans le cas contraire.

d)   =ESTNA(valeur)

Idem avec la valeur NA (non disponible).

e)   =ESTNUM(valeur)

Cette fonction affiche la valeur "vrai" si le contenu de la cellule est une valeur numérique et "faux" si le contenu est différent (chaîne de caractères par exemple).

f)     =ESTREF(valeur)

Cette fonction affiche la valeur "vrai" si le contenu de la cellule est une référence et "faux" si le contenu de la cellule est différent (nombre, texte...).

g)   =ESTTEXTE(valeur)

Cette fonction affiche la valeur "vrai" si le contenu de la cellule est une chaîne de caractères et "faux" si le contenu de la cellule est différent(valeur numérique par exemple ou # + message en cas d'erreur).

h)   =ESTVIDE(valeur)

Cette fonction affiche la valeur "vrai" si la cellule est vide et "faux" dans le cas contraire.

3.     FONCTIONS D'INFORMATION

Elles permettent d'afficher les messages d'erreur ou de non disponibilité. (voirfoncspec.xlsx)

a)   =CELLULE(type_info;référence)

Cette fonction affiche la valeur de l'attribut indiqué de la cellule désignée.

attributs possibles (entre  guillemets)

adresse  largeur type

ligne  préfixe format

colonne  couleur nomfichier

contenu  

Ouvrir le classeur "fonctspec.xlsx"
Calculer les divers arguments de la fonction "cellule"

b)   =INFORMATIONS(no_type)

Cette fonction donne des informations sur l'environnement.

Attributs possibles (entre  guillemets)

répertoire mode de calcul actif version

nombre de feuilles actives type de système d'exploitation versionse

référence de la cellule active 

c)    =N(valeur)

Cette fonction renvoie une valeur convertie en nombre (nombre, numéro_série, o, 1).

d)   =TYPE(valeur)

Cette fonction renvoie le type de valeur de la cellule désignée (1, 2, 4, 16, 64).

Dans le classeur "fonctspec.xlsx"
Calculer les divers arguments de la fonction "info"

VII.                        AUDIT

A.              SIGNIFICATION INDICATEURS

Les triangles affichés dans les coins des cellules indiquent des erreurs de formules, des commentaires ou des options de balise active (données reconnues et identifiées d'un type particulier : contact, nom du destinataire d'un message électronique).

Un triangle vert affiché dans le coin supérieur gauche d'une cellule indique une erreur possible dans la cellule.

sélectionner la cellule
pointer pour lire l'erreur
<clic g> sur pour afficher la liste des options
<clic g> sur une option

Un triangle rouge situé dans le coin supérieur droit d'une cellule indique qu'un commentaire est associé à cette dernière. Placer le curseur de la souris sur le triangle pour afficher le texte du commentaire.

Un triangle violet situé dans le coin inférieur droit d'une cellule indique la présence d'une balise active.

Placer le curseur de la souris sur ce triangle, le bouton Actions des balises actives Image du boutons'affiche.
Cliquez sur le triangle bas de ce bouton pour obtenir la liste des options de la balise active

Ce qu'Excel signale comme erreur est pour lui une incohérence mais peut être pour l'utilisateur tout à fait justifié

Vérifier les indicateurs du classeur "FONCTEXT.XLSX"

B.              VÉRIFICATION FORMULES

L'audit de formules permet à l'utilisateur de vérifier la cohérence de feuilles de travail complexes.

onglet "formules"
groupe "vérification des formules"(3éme bloc)
<clic g> sur l'outil approprié

repère par des flèches les cellules sources

repère par des flèches les cellules dépendantes

supprime les flèches

affiche les formules au lieu de leur résultat

lance la vérification des erreurs

évalue chaque élément de la formule

L'outil "espion" affiche dans une fenêtre la valeur d'une ou plusieurs cellules au fur et à mesure des changements effectués dans la feuille de travail

Ouvrir le classeur "formules.xlsx"
Afficher antécédents et dépendants du chiffre d'affaires puis effacez le flèches
Afficher la fenêtre "espion" et mettre un espion sur le chiffre d'affaires
Effectuer quelques modifications

VIII.                   MACROS

Les macro-commandes vont permettre d'automatiser des procédures et 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.
Il s'agit ici de développement d'application 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 même

Documenter son code afin qu'il puisse être repris avec un minimum de problème par quelqu'un d'autre

A.              SÉCURITÉ DES MACROS

Les macros étant des programmes, elles peuvent être employées à des fins néfastes. Pour prévenir ce type d'utilisation, deux moyens sont mis en œuvre :

Les classeurs contenant des macros ont une extension particulière qui permet de les reconnaître.

Dans l'environnement de travail standard, les macros sont désactivées.

1.     CLASSEURS DE MACROS

Excel refuse d'enregistrer au format standard un classeur contenant des macros. Un type particulier de classeur prend en charge les macros.

menu fichier

dans "type", sélectionner
<clic g> sur

Les classeurs contenant des macros sont des classeurs particuliers au format .xlsm

2.     ACTIVATION CONTENU

L'ouverture d'un classeur contenant des macros affiche un message d'alerte, lors de la première ouverture. Vous pouvez, à partir de ce message, activer les macro-commandes si vous êtes sûr de leur origine.

à la suite du message
<clic g> sur

3.     GESTION SECURITE

Le paramétrage par défaut de la sécurité des macros documents est tout à fait satisfaisant. Les options peuvent être modifiées mais aux risques et périls de l'utilisateur.

menu fichier
dans le volet de gauche, <clic g> sur
dans le volet de gauche,
<clic g> sur
dans le volet de gauche,
<clic g> sur
pour activer globalement l'exécution des macro-commandes
dans le volet de gauche,
<clic g> sur pour ajouter un dossier
les macros ne seront pas bloquées dans les classeurs de cet emplacement

Il est très risqué de désactiver le blocage des macro-commandes hormis dans un environnement connu et sécurisé

Sans modifier la gestion de la sécurité, l'approbation d'un classeur par ne s'effectue que la première fois qu'il est ouvert ; il est conseillé de laisser les choses ainsi

dans le volet de gauche,
<clic g> sur pour réinitialiser la liste de ces documents approuvés

B.              MACROS SIMPLES

Une macro-commande est une suite de tâches regroupées en une seule commande, créée et sauvegardée afin d’en disposer pour des travaux répétitifs.

1.     ONGLET DÉVELOPPEUR

Cet onglet, normalement masqué, doit être affiché pour pouvoir enregistrer des macros.

menu fichier
dans le volet gauche, <clic g> sur
dans le volet gauche
<clic g> sur
dans le volet droit
cocher
pour valider
L'onglet "développeur" s'affiche vers la fin du ruban

Afficher l'onglet "développeur"

2.     ENREGISTREMENT

Pour créer une macro-commande, il faut lancer l’enregistrement, effectuer successivement les actions à enregistrer puis arrêter l'enregistrement.

onglet "développeur"
groupe "code"(1er bloc)
tout préparer pour le début de l'enregistrement
(les conditions doivent être exactement les mêmes que lorsque l'enregistrement sera "joué")
<clic g> sur
saisir son nom
indiquer le classeur lié
saisir une description courte
pour valider
effectuer les actions
<clic g> sur

Créer une macrocommande permettant d'enregistrer un document au format .pdf
(commande "imprimer", )

Il est intéressant d'afficher les macros avec une icône dans la barre d'outils "accès rapide"

barre "accès rapide"
<clic g> sur
<clic g> sur
dans le volet gauche, choisir la catégorie "macros"

dans le volet droit choisir

ou le classeur en cours
sélectionner la macro
<clic g> sur
la macro-commande s'affiche dans le volet droit
sélectionner la macro
<clic g> sur
saisir un nom
choisir une image
pour valider

bouton droit
pointer sur la barre d'outils "accés rapide"
<clic d>
dans le volet gauche, choisir la catégorie "macros"

dans le volet droit choisir

ou le classeur en cours
sélectionner la macro
<clic g> sur
la macro-commande s'affiche dans le volet droit
sélectionner la macro
<clic g> sur
saisir un nom
choisir une image
pour valider

Ajouter la macro "pdf" dans la barre d'outils "accès rapide" avec une icône spécifique

3.     EXÉCUTION

Les macro-commandes peuvent être lancées par l'outil "macros", avec le bouton de la barre d’outils "accès rapide" ou éventuellement un raccourci clavier.

barre accès rapide
<clic g> sur l'icône de la barre associée à une macro-commande

"développeur"
groupe "code"(1er bloc)
<clic g> sur
sélectionner la macro
<clic g> sur

Bien se placer dans un environnement de départ d'exécution identique à celui de l'enregistrement

Tester la macro-commande créée

4.     MODIFICATION

Le langage utilisé pour la macro-commande est assez lisible et peut, avec un peu d'expérience, être modifié dans le code.

"développeur"
groupe "code"(1er bloc)
<clic g> sur
sélectionner la macro
<clic g> sur 
c'est ce code complet qu'il faut recopier pour copier une macro d'un classeur à l'autre

C.              MACROS COMPLEXES - VB

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.

onglet "développeur"
groupe "code"(1er bloc)
<clic g> sur

la macro-commande s'affiche dans une fenêtre Visual basic

<alt> f11 ouvre une fenêtre Visual Basic

Il faut ici prendre garde de ne pas faire avec Excel des choses que sa structure ne lui permet pas de bien faire. 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.     OBJETS

Un objet représente un élément de l'application Excel :

un classeur

une feuille de calcul une cellule

un bouton …

Pour 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.

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.

Pour faire référence à la cellule a3 de la feuille 2 du classeur 1
Application.Workbooks(1).Worksheets("Feuil2").Range("A3")

2.     PROPRIÉTÉS

Une propriété est propre à un objet. Ce peut être :

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 :
Range("A3").Value = 1

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

Pour masquer la feuille de calcul "Feuil2" :
Worksheets("Feuil3").Visible = False

3.     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.

Pour sélectionner  la feuille de calcul nommé "feuil2"
Worksheets("Feuil2").Select

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.

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

4.     É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

Pour lancer la procédure "essai" à l'ouverture du classeur :
Private Sub Workbook_Open()
essai
End Sub

a)   LIES A 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é

newsheet  : 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

sheetfollowhyperlink : Lors d'un clic sur un lien hypertexte

sheetselectionchange : Lors d'un changement de sélection sur une feuille de calcul

windowactivate : Lorsqu'un classeur est activé

windowdeactivate : Lorsqu'un classeur est désactivé

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

b)   LIES A 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

followhyperlink : 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

Pour empêcher l'impression d'un classeur :
Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Cancel = True
End Sub

Pour récupérer la valeur d'une cellule modifiée :
Private Sub Worksheet_Change(ByVal Target As Range)
   MsgBox Target.Value
End Sub

c)    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

d)   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 dans un nouveau classeur Workbooks("classeur.xls").worsheets
("feuil2").copy 

e)   CELLULES

Une plage de cellules est désignée par "Range".

Pour faire référence à la plage de cellule "A2:C10" :
Range("A2:C10")

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

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

Ouvrir le classeur "dates.xlsx"
Enregistrer comme macro sous le nom "macro1" les actions suivantes :
Effacer les données et la mise en forme de la plage de cellule "A1:D7"

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).

Pour écrire 3 dans la cellule "A6" :
Range("A6").Value = 3
ou
Cells(6, 1).Value = 3

Dans le classeur "dates.xlsx",
Enregistrer comme macro sous le nom "macro2" les actions suivantes :
Écrire 9 dans les cellules "A1 à D7"

f)     CONTRÔLE

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

onglet "développeur"
groupe " contrôles "(3ème bloc)
<clic g> sur
<clic g> sur et dessiner le bouton
associer une macro
mettre éventuellement en forme le bouton
la macro-commande s'affiche dans une fenêtre Visual basic

Dans le classeur "dates.xlsx"
Créer 2 boutons, un pour chacune des macros "pdf" et "effacer" créées ci-avant

5.     CONDITIONS

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

6.     BOUCLES

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.

À 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

7.     BOUCLES CONDITIONNELLE

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

Ouvrir le classeur "base.xlsx"
Créer une macro-commande permettant de mensualiser par dixième le total de chaque ligne
Créer une macro-commande permettant de mensualiser par douzième le total de chaque ligne
Associer chaque macro-commande à un bouton
Enregistrer le classeur comme classeur de macros (.xlsm)
Comparer avec l'un des classeurs "ville" du dossier "macros"

Exemple de fonctionnement :
saisir une valeur en janvier pour les ventes de la 1ère ligne
le total de la ligne se met à jour
sélectionner ce total
Cliquer sur un bouton pour qu'il soit mensualisé :

Sub diziéme()

Set c = ActiveCell

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

À partir du classeur "base.xlsm", enregistrer un classeur "conso.xlsm"
Créer une macro-commande permettant de consolider les données de tous les tableaux créés à partir du classeur "base" en en faisant simplement la liste dans la feuille "liste" de ce classeur

Sub conso()

'

' conso Macro

' Macro enregistrée le 27/09/2005 par joel GREEN

'mise à zéro des valeurs
    Sheets("conso").Select
    ActiveSheet.Unprotect
'supression des alertes
    Application.DisplayAlerts = False
'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
    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éja
    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
'affichage des alertes
    ActiveSheet.Protect
    Application.DisplayAlerts = True
End Sub

Le code des macros ci-dessus peut être copié/collé de Word dans Visual Basic

IX.      EXERCICES

A.              BASE

Afficher la feuille nommée "base de données"
Afficher les enregistrements concernant la région "Ouest"
puis affiner en ne prenant que les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 k€ (voir base terminé.xlsx, feuille"solution1")

Annuler le filtre "ventes" puis annuler le filtre automatique
Afficher (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Ouest" et la région "Est" dont les ventes sont inférieures ou égales à 93 k€
(voir base terminé.xlsx, feuille"solution2") puis tout réafficher

Extraire (en masquant les enregistrements)
les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 K€ et de la région "Est" dont les ventes sont supérieures à 35 K€ puis annuler l'opération
Extraire (en extrayant les enregistrements en O1 par exemple)
les enregistrements de la région "Ouest" dont les ventes sont inférieures à 100 K€ et de la région "Est" dont les ventes sont supérieures à 35 K€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution3" 1erchamp de critère)
Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements de la région "Sud" entre 20 et 80 k€ et ceux de la région "Nord" inférieurs à 30 et supérieurs à 75 k€ puis annuler l'opération
(voir base terminé.xlsx, feuille "solution3", 2èmechamp de critères)

Extraire (en masquant les enregistrements ne répondant pas aux critères) les enregistrements dont les ventes sont supérieures à la moyenne
(voir base terminé.xlsx, feuille"solution4")

Trier les enregistrements par nom puis par montant de vente dans l'ordre croissant
Trier les enregistrements par région, par ville et par nom dans l'ordre croissant
Effectuer des sous-totaux par région (voir base terminé.xlsx, feuille"solution5")

Effectuer des sous-totaux par région puis à l'intérieur de chaque région, par ville (voir base terminé.xlsx, feuille"solution6")

B.              FONCTBD

Calculer le nombre d'enregistrements
la vente maximum, la vente minimum, 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 région "Nord"

C.              CONVERS

Afficher les données correctement (notamment la dernière colonne en valeur et non en texte) puis fermer le fichier sans l'enregistrer
Ouvrir le fichier avec le bloc-notes
Sélectionner les données et les copier dans le presse-papiers
Ouvrir Excel le fichier "convers.xlsx"
Coller les données en A1 de la feuille exercice
Les convertir sur les colonnes adjacentes
Vérifier que les valeurs sont bien prises en compte
Modifier éventuellement leur format et effectuer une somme automatique

D.              REQUETE (.XLSX / .DQY)

Créer un nouveau classeur
Y afficher les enregistrements extraits de la base access.accdb pour les champs "société", "contact", "adresse" et "cdpostal"
Enregistrer sous le nom "req"

E.               REQUETE 1 (.XLSX)

Modifier "req" et ne prendre que les enregistrements dont le code postal est supérieur ou égal à 10000 et strictement inférieur à 45000
Trier par "cdpostal" et "société" dans l'ordre croissant
Enregistrer la requête sous le nom "req1" dans le même dossier que les exercices

F.               REQUETE 2 (.XLSX)

Créer une source de données pour les fichiers "accdb" à partir de la base de données "Northwind 2016 ios.accdb"
Afficher la table "employes", et extraire les employés habitant une ville dont le code postal est strictement supérieur à 60000 et inférieur ou égal à 74000 exerçant la profession de "représentant commercial", ceux exerçant la profession de "Vice-président des ventes" quelle que soit leur ville ainsi que les employés exerçant la profession de "représentant commercial" habitant "Cherbourg"
Renvoyer les données vers Excel

G.              TABHYP–1A

Un représentant a une commission de 3% du chiffre d'affaires encaissé.
L'entreprise, pour calculer son résultat, doit diminuer le chiffre d'affaires de 3200 € de couts fixes ainsi que de la commission de ce représentant.
Représenter ce problèmes avec Excel, définir une table donnant pour divers taux (5% à 10%) la commission payée au représentant puis calculer le résultat afférent pour l'entreprise (voir solution  tabhyp terminé.xlsx feuille "hyp1 a")

H.              TABHYP–1B

Afficher la feuille "prob 1 b" du classeur "tabhyp.xlsx"
A partir de ce tableau, calculer pour chaque région, l'ancienneté moyenne des commerciaux, leurs ventes moyennes ainsi que leur ventes totales
Calculer les résultats à l'aide d'une zone de critères et de la fonction bdmoyenne
Représenter les données à l'aide d'une table de données par région
(voir solution  tabhyp terminé.xlsx feuille "hyp 1 b")

I.                  TABHYP–2A

Afficher la feuille "prob 2 a" du classeur "tabhyp.xlsx"
Définir une table permettant de comparer les remboursements en fonction du capital emprunte (de 150.000 à 500.000 € par pas de 50.000 €) et des taux d'emprunt (de 8 à 13,5 % par pas de 0,5%) (voir solution  tabhyp terminé.xlsx feuille "hyp2 a")

J.                TABHYP–2B

Afficher la feuille "prob 2 b" du classeur "tabhyp.xlsx"
Définir une table permettant d'obtenir les ventes totales par région et par catégorie d'outils(voir solution  tabhyp terminé.xlsx feuille "hyp 2 b")

K.              TABCROIS

Afficher la feuille "base"
Créer à partir des données un tableau croisé permettant de lister les résultats par Région et par Nom (voir solution feuille "solution 1")

Modifier le tableau croisé en affichant les ventes par Ville et par Produit
(voir solution feuille "solution 2")

Créer un tableau croisé de la moyenne des ventes par Région et par Produit
(voir solution feuille"solution 3")

Créer un graphique croisé dynamique des ventes par Région et par Produit
Repositionner et mettre en forme ce graphique (voir solution feuille"solution 4")

L.               VENTES CAFE

Présenter sous forme de tableau croisé les ventes 2014 par région pour l'Europe

Présenter sous forme de tableau croisé les ventes 2015 par région pour l'Europe

M.             POWERPIVOT

Ouvrir le classeur "powerpivot"
Installer si nécessaire "powerpivot"
Créer un modèle à partir de l'ensemble des données
Créer une mesure faisant la moyenne des données
L'afficher dans la feuille "powerpivot"
Créer un indicateur de performance basé sur la mesure "moyenne" avec une valeur cible de 100

Créer un tableau powerview affichant les ventes par ville avec un filtre par type d'outils
Afficher un graphe comme par ville avec un filtre par type d'outils

Afficher une carte avec la proportion d'outils vendus par ville

N.              VENTES DATE-CATÉGORIE

Créer un tableau croisé avec les catégories en colonne

Transformer le tableau croisé en cube olap
lui affecter une mise en forme simple

Créer un modèle powerpivot à partir du cube olap
Créer une mesure faisant la moyenne des données
L'afficher dans la feuille "powerpivot"
Créer un indicateur de performance basé sur la mesure "moyenne" avec une valeur cible de 300

Créer un graphique powerview comme ci-dessous

O.              REPRÉSENTANTS-Commissions

Dans le classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "commissions" calculer la commission des commerciaux pour chaque vente selon les critères suivants :
10% pour les ventes inférieures à 50 000 €
5% pour les ventes supérieures à 50 000 €
Faire un tableau des commissions dans la feuille (en I1:K2 par exemple)
Utiliser ce tableau pour calculer les commissions
Dans la colonne "ventes", afficher les outils selon les critères suivants :
Pour les ventes supérieures à 50000 €, Police verte sur fond vert
Pour les ventes inférieures à 50000 €, Police bleue sur fond bleu

P.               REPRÉSENTANTS–Outils

Afficher la feuille "outils"
Dans la colonne "outils", afficher les outils selon les critères suivants :
jaune/orange pour les marteaux, rouge pour les tournevis, vert pour les scies
Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", afficher les valeurs selon les critères suivants :
Rouge pour les valeurs inférieures à la moyenne
Vert pour les valeurs supérieures à la moyenne
Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", compléter par une barre de données bleu foncé
Dans le même classeur "représentants.xlsx", feuille "outils"
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

Q.              REPRÉSENTANTS-CA

Afficher la feuille "chiffre d'affaires"
Dans la colonne "ventes", utiliser une nuance de couleurs dégradé
Dans la colonne "ventes", ajouter à la nuance un jeu d'icônes
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 à 50000€, 8% pour la part comprise entre 50 et 75000€, 6% pour la part comprise entre 75 et 100000€ et 4% pour la part supérieure à 100000€

R.              EMPRUNT

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.
Ne plus afficher les résultats lorsque l'emprunt est remboursé
Figer les volets de titre et protéger votre travail.

S.               DATES

Sur une base de 360 jours par an et à raison de 30 jours par mois pour les mois pleins (quel que 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.
Calculer dans un second temps le nombre de jours ouvrés entre ces deux dates

T.               FONCDATE

Sur une base de 360 jours par an et à raison de 30 jours par mois pour les mois pleins (quel que 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.
Calculer dans un second temps le nombre de jours ouvrés entre ces deux dates

U.              FONCTMATH

Calculer les fonctions mathématiques suivantes à partir d'une ou plusieurs cellules de ce tableau : arrondi, tronque, abs, ent, alea, produit, somme, racine

V.               MATRICE

À partir des 2 matrices de base a et b,
Calculer le produit des 2 matrices
Inverser la matrice d14:g17
Calculer la somme des matrices d14:g17 et d19:g22
Calculer le déterminant de la matrice d14:g17

W.            FONCSTAT

Calculer pour la colonne visiteurs les principales fonctions statistiques (minimum, maximum, moyenne, somme, médiane, variance, écart type, centriles, intervalles de confiance).
Représenter ces données graphiquement et calculer le trend (tendance)

X.               FONCTSTAT2

Effectuer une analyse de variance à un facteur, à deux facteurs d'expérience sans répétition puis de corrélation sur des feuilles séparées

Y.               FONCSPEC

À l'aide de la fonction "indirect" renseigner le tableau pour pouvoir calculer la commission pour un article donne
utiliser les divers arguments de la fonction "cellule" et de la fonction "info"
calculer les fonctions choisir, colonnes ,lignes ;index, type ,adresse

Z.               FONCTEXT

Calculer les diverses fonctions texte indiquées

AA.       DATES

Créer une macrocommande "pdf" permettant d'enregistrer un document au format .pdf
(commande "imprimer", )
L'ajouter à la barre d'outils "accès rapide" avec une icône spécifique
Enregistrer comme macro sous le nom "macro1" les actions suivantes :
Effacer les données et la mise en forme de la plage de cellule "A1:D7"
Enregistrer comme macro sous le nom "macro2" les actions suivantes :
Écrire 9 dans les cellules "A1 à D7"
Créer 2 boutons, un pour chacune des macros "pdf" et "macro 2" créées

CONSO FRANCE

Créer une macro-commande permettant de mensualiser par douzième le total de chaque ligne
Associer la macro-commande à un bouton

Créer une macro-commande permettant de mensualiser par dixième le total de chaque ligne
Associer la macro-commande à un bouton

À partir du classeur "BASE.XLSM", enregistrer un classeur "CONSO.XLSM"
Créer une macro-commande permettant de consolider les données de tous les tableaux créés à partir du classeur "BASE" en en faisant simplement la liste dans la feuille "liste" de ce classeur

SOMMAIRE

I.              RUBAN

A.              AJOUT D'UN ONGLET

B.              AJOUT D'UN GROUPE

C.              MODIFICATION DE L'ORDRE

D.              AJOUT D'OUTILS

II.              BASE DE DONNÉES

A.              CARACTÉRISTIQUES

1.              BASE EXCEL

2.              CHAMPS

3.              ENREGISTREMENTS

4.              FORMULAIRE

B.              EXTRACTION ENREGISTREMENTS

1.              FILTRE AUTOMATIQUE

2.              FILTRE ÉLABORE

C.              TRI ET SOUS-TOTAUX

1.              TRI DES ENREGISTREMENTS

2.              SOUS TOTAUX

D.              FONCTIONS BASE DE DONNÉES

1.              =BDECARTYPE

2.              =BDECARTYPEP

3.              =BDMAX

4.              =BDMIN

5.              =BDMOYENNE

6.              =BDNB

7.              =BDNBVAL

8.              =BDPRODUIT

9.              =BDSOMME)

10.              =BDVAR

11.              =BDVARP

III.              DONNÉES EXTERNES

A.              OUVERTURE DE FICHIER AUTRES

1.              OUVERTURE FICHIERS TEXTE

2.              CONVERSION DONNÉES

3.              OUVERTURE FICHIERS XML

B.              EXTRACTION DE DONNÉES

1.              EXTRACTION BASE ACCESS

2.              EXTRACTION AVANCÉE ACCESS

3.              EXTRACTION BASE EXTERNE

4.              EXTRACTION AVANCÉE QUERY

5.              EXTRACTION PAR REQUÊTE

6.              AUTRES SOURCES

C.              LIAISON BASE EXTERNE

IV.              TABLEAUX DE DONNÉES

A.              TABLEAUX CROISES DYNAMIQUES

1.              CRÉATION AUTOMATIQUE

2.              CRÉATION PERSONNALISÉE

3.              MODIFICATION

4.              CALCULS

5.              PRESENTATION

B.              GRAPHIQUES CROISES DYNAMIQUES

C.              CUBE OLAP

1.              CRÉER UN TABLEAU CROISÉ

2.              TRANSFORMER EN CUBE OLAP

D.              POWERPIVOT / POWERVIEW

1.              UTILISATION POWERPIVOT

2.              AFFICHAGE DANS POWERVIEW

3.              EXERCICE

V.              TABLES D'HYPOTHÉSES

A.              HYPOTHÈSE A UNE VARIABLE

1.              TABLE NON LIÉE À UNE BASE

2.              TABLE LIÉE À UNE BASE

B.              HYPOTHÈSE A DEUX VARIABLES

1.              TABLE NON LIÉE À UNE BASE

2.              TABLE LIÉE À UNE BASE

VI.              AUTRES FONCTIONS

A.              GÉNÉRALITÉS

1.              SYNTAXE

2.              TYPES DE FONCTION

3.              ARGUMENTS

4.              RÈGLES D'UTILISATION

B.              FONCTIONS LOGIQUES

1.              FORMAT CONDITIONNEL

2.              =SI(test;valeur_si_vrai;valeur_si_faux)

3.              =FAUX()

4.              =NON(valeur_logique)

5.              =OU(valeur_logique1;valeur_logique2...)

6.              =VRAI()

C.              FONCTIONS DE DATE ET D'HEURE

1.              =AUJOURD'HUI()

2.              =MAINTENANT()

3.              =ANNEE(numéro_de_série)

4.              =DATE(année;mois;jour)

5.              =DATEVAL(date_texte)

6.              =JOUR(numéro_de_série)

7.              =JOURSSEM(numéro_de_série)

8.              =JOURS360(date_début;date_fin)

9.              =MOIS(numéro_de_série)

10.              =HEURE(numéro_de_série)

11.              =MINUTE(numéro_de_série)

12.              =SECONDE(numéro_de_série)

13.              =TEMPS(heure;minutes;secondes)

14.              =TEMPSVAL(heure_texte)

D.              FONCTIONS MATHÉMATIQUES

1.              FONCTIONS GÉNÉRALES

2.              FONCTIONS EXPONENTIELLES

3.              FONCTIONS MATRICIELLES

4.              FONCTIONS TRIGONOMÉTRIQUES

E.              FONCTIONS STATISTIQUES

1.              =ECARTYPE.STANDARD(liste)

2.              =ECARTYPE.PEARSON(liste)

3.              =NB(liste)

4.              =MAX(liste)

5.              =MEDIANE(liste)

6.              =MIN(liste)

7.              =MOYENNE(liste)

8.              =VARP(liste)

9.              =CENTILE.INCLURE

10.              =INTERVALLE.CONFIANCE.NORMAL

11.              =INTERVALLE.CONFIANCE.PEARSON

12.              =ORDONNEE.ORIGINE

13.              =DROITEREG

14.              ANALYSE

F.              FONCTIONS DE CONSULTATION

1.              =ADRESSE(no_ligne;no_colonne)

2.              =COLONNES(matrice)

3.              =INDIRECT(référence)

4.              =LIGNES(matrice)

5.              =CHOISIR(no_index;Val1.Val2.Val3...)

6.              =INDEX(matrice;no_ligne;no_colonne)

G.              LES FONCTIONS TEXTE

1.              CARACTÈRES LICS

2.              AFFICHAGE LIBELLES

3.              INFORMATIONS LIBELLES

4.              MODIFICATION LIBELLES

5.              MAJUSCULES/minuscules

6.              CONVERSION

7.              TESTS

H.              FONCTIONS D'INFORMATION

1.              FONCTIONS D'ERREUR

2.              FONCTIONS LOGIQUES

3.              FONCTIONS D'INFORMATION

VII.              AUDIT

A.              SIGNIFICATION INDICATEURS

B.              VÉRIFICATION FORMULES

VIII.              MACROS

A.              SÉCURITÉ DES MACROS

1.              CLASSEURS DE MACROS

2.              ACTIVATION CONTENU

3.              GESTION SECURITE

B.              MACROS SIMPLES

1.              ONGLET DÉVELOPPEUR

2.              ENREGISTREMENT

3.              EXÉCUTION

4.              MODIFICATION

C.              MACROS COMPLEXES - VB

1.              OBJETS

2.              PROPRIÉTÉS

3.              MÉTHODES

4.              ÉVÈNEMENTS

5.              CONDITIONS

6.              BOUCLES

7.              BOUCLES CONDITIONNELLE

IX.              EXERCICES

A.              BASE

B.              FONCTBD

C.              CONVERS

D.              REQUETE (.XLSX / .DQY)

E.              REQUETE 1 (.XLSX)

F.              REQUETE 2 (.XLSX)

G.              TABHYP–1A

H.              TABHYP–1B

I.              TABHYP–2A

J.              TABHYP–2B

K.              TABCROIS

L.              VENTES CAFE

M.              POWERPIVOT

N.              VENTES DATE-CATÉGORIE

O.              REPRÉSENTANTS-Commissions

P.              REPRÉSENTANTS–Outils

Q.              REPRÉSENTANTS-CA

R.              EMPRUNT

S.              DATES

T.              FONCDATE

U.              FONCTMATH

V.              MATRICE

W.              FONCSTAT

X.              FONCTSTAT2

Y.              FONCSPEC

Z.              FONCTEXT

AA.              DATES

Pour des raisons de lisibilité, il est employé dans cet ouvrage les termes de Windows pour Microsoft ™ Windows ®, Word pour Microsoft ™ Word ®, Excel pour Microsoft ™ Excel ®, Access pour Microsoft ™ Access ®, Powerpoint pour Microsoft ™ Powerpoint ® , Outlook pour Microsoft ™ Outlook ®, Edge pour Microsoft ™ Edge ® .
Par ailleurs, toutes les copies d'écrans, images et icones ont été reproduites avec l'aimable autorisation de Microsoft ™

Tous les efforts ont été faits, et le temps nécessaire consacré, pour que cet ouvrage soit aussi fiable et rigoureux que possible. Cependant, ni l'auteur, ni l'éditeur ne sauraient être tenus pour responsable des conséquences de son utilisation ou des atteintes au droit des brevets ou des personnes qui pourraient résulter de cette utilisation.