Introduction aux Systèmes

d’Information et aux Bases

de Données

LA GESTION DES PETITES BASES DE

D O N N É E S AV E C E X C E L
( PA R T I E I )

L3 GESTION CLASSIQUE

U N I V E R S I T É PA R I S O U E S T N A N T E R R E L A D É F E N S E
SUR LA BASE DU COURS DE SANA BEN HAMIDA
 Objectif

La gestion des données en fichier par Excel

 Création de la base de données: saisie et

validation des données
 Interrogation et affichage des données
 Analyse des données : Tri, filtre automatique,

filtre élaboré, sous-totaux

 Traitement des données par les fonctions de

Bases de Données d’Excel

 Protection des données
Structure de données sous Excel

· Une base de données est composée d'enregistrements.

Chaque enregistrement est subdivisé en champs.
· Dans Excel, les enregistrements se présentent en lignes
et les champs en colonnes.
 Structure de données sous Excel

Tout tableau, dans Excel, est

automatiquement considéré comme une base
de données s'il respecte quelques règles
simples :
 Une ligne d'en-tête : un champ par colonne et une
colonne par champ
 Un seul type de données par champ (textes,
nombres ou dates…)
 Une ligne par enregistrement et un
enregistrement par ligne
 Pas de ligne ou de colonne vierge
 Des données cohérentes
Saisie et validation
des données
 Accès direct aux plages de cellules

Sélectionner les cellules via la zone Nom

Ou par le nom si la plage est nommée

 Masque de saisie (formulaire)

Au lieu d'entrer, de modifier et de retirer les

données directement dans les cellules d'une
feuille de calcul, Excel vous permet de gérer vos
listes de données en utilisant un formulaire.

 Cette fonction est masquée dans Excel 2007 et 2010.

Pour la faire apparaître, cliquez sur le bouton placé à
droite de la barre d'outils Accès rapide et
choisissez Autres commandes. Déroulez la
liste Commandes courantes et choisissez Toutes les
commandes. Sélectionnez Formulaire et cliquez
sur Ajouter>>, puis sur OK.
Masque de saisie (formulaire)
Masque de saisie (formulaire)
 Masque de saisie (formulaire)

Exemple
 Masque de saisie (formulaire)

Nouvelle Ajouter un enregistrement à la liste de données. Cet

enregistrement sera placé à la fin de la liste.
Supprimer Supprimer un enregistrement de la liste de données.
Excel va demander une confirmation avant de
procéder.
Rétablir Permets de rétablir les valeurs d'un enregistrement
avant des modifications que vous avez apportées.
Doit être utilisé avant de changer d'enregistrement.
Précédente Passer à l'enregistrement précédent.
Suivante Passer à l'enregistrement suivant.
Critères Permets d'établir des critères de recherche et
afficher seulement les enregistrements requis.
Fermer Fermer la grille pour revenir à la feuille de calcul.
 Liste fermée

Pour faciliter la saisie des données ou pour limiter

les entrées aux éléments que vous définissez, créez
une liste déroulante d’entrées valides compilées à
partir de cellules situées ailleurs dans le classeur.

Exemple: limiter la saisie d’un emplacement à trois villes:

Paris, Lyon, Toulouse.
1. Ecrire cette liste dans une plage de cellules distincte;
2. Sélectionnez le(s) champ(s) à contrôler;
3. Ouvrez la fenêtre Validation des données. Dans la
liste Autoriser, choisissez Liste. Cliquez sur le petit
point rouge à droite de Source, sélectionnez la liste que
vous venez de taper.
Liste fermée
 Validation des données

Limiter le nombre de caractères à saisir

1. Sélectionnez une ou plusieurs cellules à valider.
2. Sous l’onglet Données, dans le groupe Outils de
données, cliquez sur la commande Validation des
données. Les utilisateurs qui ne
respecteront pas les
contraintes établies verront
une
boîte de dialogue
d’avertissement les
informant que leur saisie ne
correspond pas aux critères
 Validation des données

Supprimer des cellules

vides non contiguës
1. Sélectionnez la plage dans
laquelle vous voulez supprimer
les cellules vides.
2. Sous l’onglet Accueil, dans le
groupe Édition, cliquez sur
Rechercher & sélectionner et
cliquez sur la commande
Sélectionnez les cellules.
3. Dans le groupe Cellules, cliquez
sur la commande Supprimer les
cellules.
 Interrogation et
affichage des données
 Recherche des données

Recherche simple

Recherche Avancée
(avec les options)
 Recherche des données par l’index

 Index(matrice;no_lig;no_col)
 La fonction Index renvoie la donnée située à l'intersection
de la ligne et de la colonne de la matrice (plage) de
recherche.
 matrice Obligatoire. Représente une plage de cellules ou
une constante matricielle.
 no_lig Obligatoire. Sélectionne la ligne de la matrice dont
une valeur doit être renvoyée. Si l’argument no_lig n’est pas
spécifié, l’argument no_col est obligatoire.
 no_col Facultatif. Sélectionne la colonne de la matrice dont
une valeur doit être renvoyée. Si l’argument no_col n’est pas
spécifié, l’argument no_lig est obligatoire.

 Exemple Index(A1:D8;3;2) retourne la valeur de la cellule

de troisième ligne et deuxième colonne c’est-à-dire B3
Recherche des données par l’index

Exemple 1
Recherche des données par l’index

Exemple 2
 Recherche des données par
l’équivalence

 EQUIV(valeur_cherchée;tableau_recherche;typ
e)
Renvoie la position relative de la valeur_cherchée
dans le tableau. Type est le nombre -1, 0 ou 1 qui
indique comment Excel doit procéder pour la
comparaison:
1 la fonction EQUIV trouve la valeur la plus élevée qui
est inférieure ou égale à la valeur_cherchée.
0 fonction EQUIV trouve la première valeur
exactement équivalente à celle de l'argument
valeur_cherchée
-1 la fonction EQUIV trouve la plus petite valeur qui est
supérieure ou égale à celle de l'argument
valeur_cherchée
 Recherche des données par
l’équivalence

 Remarques:
 La fonction EQUIV renvoie la position de la valeur
équivalente dans l’argument tableau_recherche et
non la valeur en elle-même. Par exemple, EQUIV("b";
{"a","b","c"};0) renvoie 2, c’est-à-dire la position
relative de « b » dans Le tableau{"a","b","c"}.

 La fonction EQUIV ne distingue pas les majuscules

des minuscules.

 Si la fonction EQUIV ne trouve pas de valeur

équivalente, elle renvoie la valeur d’erreur #N/A.
 Recherche des données par
l’équivalence

 Exemple:

EQUIV(10000;B2:B12;0)

EQUIV(10000;B2:B12;1)

11

EQUIV(10000;B2:B12;-1)

1
 Recherche des données: La fonction
RechercheV
la fonction RECHERCHEV permet
d’effectuer une recherche dans la première
colonne d’une plage (plage : deux cellules au
minimum d’une feuille de calcul. Une plage
peut contenir des cellules adjacentes ou non
adjacentes.) de cellules, puis obtenir la valeur
d’une cellule appartenant à la même ligne .
Syntaxte:

RECHERCHEV(valeur_cherchée;
table_matrice; no_index_col;
[valeur_proche])
 Recherche des données: La fonction
RechercheV
 valeur_cherchée Obligatoire. La valeur à chercher dans la
première colonne du tableau ou de la plage. L’argument
valeur_cherchée peut être une valeur ou une référence.
 table_matrice Obligatoire. Plage de cellules qui contient les
données. Vous pouvez utiliser une référence à une plage (par
exemple, A2:D8) ou un nom de plage. Les données peuvent être
du texte, des nombres ou des valeurs logiques. La fonction ne
fait pas de distinction entre les majuscules et les minuscules.
 no_index_col Obligatoire. Numéro de la colonne de
l’argument table_matrice dont la valeur correspondante doit
être renvoyée.
 valeur_proche Facultatif. Représente une valeur logique
indiquant si vous souhaitez que la fonction RECHERCHEV
recherche une valeur exacte ou voisine de celle que vous avez
spécifiée
 Recherche des données: La fonction
RechercheV
 Exemple

Renvoie d’une valeur provenant d'une

plage
Dans la plage A1:B6 :
-Colonne 1 : nom
-Colonne 2 : note

echerche la ligne de Benoit dans cette plage et donne la note

= RECHERCHEV ( ’’Benoit’’ ; A1:B6 ; 2 ; FAUX)

Analyse des données
 Tri des données

La fonction Trier des données est une partie

intégrante de l’analyse des données.
Trier du texte : -ordre alphabétique
- ordre alphabétique inverse
Tri personnalisé
 Le filtrage des données

Le filtrage des données constitue un moyen

rapide et aisé de rechercher et de manipuler
un sous-ensemble de données d’une plage de
cellules ou d’un tableau.
Par exemple, vous pouvez effectuer un
filtrage pour n’afficher que les valeurs que
vous spécifiez, pour afficher les valeurs
supérieure ou inférieure ou pour afficher
rapidement les valeurs en double.
 Le filtrage des données

Pour savoir si un filtre est appliqué, regardez

l’icône qui figure dans l’en-tête de la
colonne :

Une liste déroulante de filtrage indique que

le filtrage est activé, mais non appliqué.

Un bouton Filtre indique qu’un filtre est

appliqué.
 Le filtrage des données

Exemple simple: Sélectionner que les

cadres de la liste des employés :
 Activer le filtre

 Puis, on clique sur la colonne STATUT, et on choisit

cadre. On obtient alors
 Le filtrage des données

Exemple 2: Sélectionner que les salariés ayant un

salaire supérieur à 3000
 sélectionner filtre
numérique
 Le filtrage des données

Exemple 2: Sélectionner que les salariés ayant un

salaire supérieur à 3000
 Ecrire la valeur

 Résultat
 Le filtrage des données

On peut créer des filtres complexes et

afficher le résultat dans la liste elle-même ou
l'afficher dans une nouvelle feuille
Le principe du filtre élaboré repose sur 3
plages,
 la plage contenant la liste à élaborer,
 la plage contenant les critères: comprend une ligne
avec les noms de champs (sur lesquels vous établissez
les critères) et au minimum 1 ligne de critère
 la plage de destination.
 Le filtrage des données
Critères des filtres élaboré (exemple)
 Le filtrage des données

 On veut créer un filtre élaboré pour le tableau des

salariés afin d’avoir les cadres Femmes dont le
salaire n’a pas atteint 3000.
Le filtrage des données
Le filtrage des données
 Les sous-totaux

il est possible de faire des calculs sur les sous

parties d’un même tableau
 Exemple :
Nous souhaitons calculer les ventes
totales pour chaque entreprise…

1. Il faut trier les données

2. sélectionner « Sous-totaux » dans le menu
« Donnée »
 Les sous-totaux
 Le champ « A chaque changement
de : » on sélectionne sur quel champs
nous souhaitons un sous total. Ici par
entreprise.
 « Utiliser la fonction » : plusieurs
fonctions sont à notre disposition;
dans ce cas c’est la fonction
« Somme »
 « Ajouter un sous-total à : » ici, il nous
faudra cocher « Vente » pour totaliser
les ventes par entreprise

 « Remplacer les sous-totaux

existants : il est préférable de laisser
cette case cochée. Si vous avez déjà
utilisé la fonction Sous-totaux pour
obtenir d'autres résultats, ceux-ci
seront donc effacés au bénéfice des
nouveaux calculs.
 Les sous-totaux

 Saut de page entre les groupes :

cette option permet d'obtenir,
automatiquement, une page par
entreprise. A éviter en présence
d’un grand nombre de valeurs.
 Synthèse sous les données : les
résultats sont groupés à chaque
changement d'entreprise
Tableau croisé
dynamique
Tableau croisé dynamique - Définition

Un tableau croisé dynamique permet de

synthétiser, analyser, explorer et présenter
une synthèse des données d'une feuille de
calcul.
Il se sert de données bidimensionnelles pour
créer un tableau à trois dimensions, à partir de
conditions multiples possédant des points
d'intersection.
Il est particulièrement utile lorsque vous disposez
d'une vaste plage de données susceptible d'être
examinée selon diverses perspectives.
Tableau croisé dynamique - Définition

Utilisation d'une interface spécialisée...

– Pour création
– Pour modification
Création
– Spécifier la liste (ou le tableau) à utiliser
– et définir l'organisation des données
dans le tableau croisé dynamique
Modification
– Réorganiser les données en faisant
glisser les champs
Tableau croisé dynamique - Définition

Exemple:
A partir d'un tableau recensant toutes les
commandes de l'année, les tableaux croisés
dynamiques permettent d'obtenir des
tableaux statistiques comme:
 le nombre ou le montant des commandes par client
ou type de produits (en valeur absolue ou bien
même en %) ;
 le montant ou le nombre de commandes traitées
par employé ;
 le montant généré par chaque produit vendu, soit
dans l'année, soit par mois ou par trimestre.
Tableau croisé dynamique - Démarche

Soit le tableau de salariés suivant

 Attention, pour créer un tableau croisé dynamique, toutes les

colonnes doivent être nommées et toutes les lignes suivantes
doivent contenir des données
Tableau croisé dynamique - Démarche

1. Placer le curseur sur une cellule du tableau;

2. Dans menu « Données », sélectionnez l’option Rapport de
tableau croisé dynamique.

3. Une fenêtre s’affiche

Vérifier la
plage du
tableau
Choisir
l’emplacement
(de préférence
une nouvelle
page)
Tableau croisé dynamique - Démarche

Un tableau croisé dynamique vide est inséré avec un volet à la

droite pour créer les champs calculés.
Tableau croisé dynamique - Démarche
 Tableau croisé dynamique - Démarche

Exemple 1: Somme des salaires par titre et par sexe

1. Faire glisser « Titre » dans les étiquettes de lignes,
« sexe » dans les étiquettes de colonnes et « salaire » dans
les valeurs.
Tableau croisé dynamique - Démarche

Exemple 1: Somme des salaires par titre et par sexe  Résultat

Le tableau dispose des filtres pour les lignes et les colonnes

 Tableau croisé dynamique - Démarche

Exemple 2: On peut aussi changer la fonction de synthèse des

valeurs; par exemple, on souhaite avoir le salaire moyen par
titre et par sexe.
1. Visualiser la liste dans la zone des valeurs et sélectionner
« Paramètres des champs des valeurs ».

2. Dans la fenêtre qui s’affiche, choisir la fonction souhaitée

(fonction « Moyenne » pour l’exemple).
Tableau croisé dynamique - Démarche

Résultat
 Graphique croisé dynamique

 Excel permet aussi de représenter les données sous forme

d’un graphique croisé dynamique (en 3D). On peut changer
les champs de la même façon que pour un tableau.

 Exemple:
4500

4000

3500

3000

2500
F
2000
M
1500

1000

500

0
Adminis- Ouvrier Secrétaire Vendeur
trateur
 Traitement des
données:
Les Fonctions de BD
 Les fonctions de Base de données

 Les fonctions de base de données de s’applique que

sur les plages de cellules de type « Bases de
donné »
 Rappel: « Une base de données est une liste de
données liées dans laquelle les lignes
d’informations liées sont des enregistrements et les
colonnes de données sont des champs. La première
ligne de la liste contient les étiquettes de chaque
colonne ».
 Les fonctions de Base de données

 Les fonctions de bases de données débutent toutes

par =BD (en français)
Syntaxe générale:

BDFonction(base de données, champ, critères)

 La structure des formules est toujours la même :
 1 – Plage de données de référence

 2 – Colonne de la plage de données sur laquelle l’opération

sera effectuée
 3 – Critères à respecter
Les fonctions de Base de données
 Les fonctions de Base de données

 Exemple:
 Les fonctions de Base de données

 Interrogation des données : Fonction BDLIRE

retourne une valeur unique
Attention, les critères
doivent être organisés en
colonne!
=BDLIRE($A$1:$G$16;$B$1;$I$1:$I$2)

=BDLIRE($A$1:$G$16;$E$1;$I$1:$I$2)

=BDLIRE($A$1:$G$16;$F$1;$I$1:$I$2)
 Les fonctions de Base de données

 Calcul statistique: fonction NBVAL

 Nombre de vendeurs de catégorie 4:

=BDNBVAL($A$1:$G$16;B1;I3:O4)

Plage de la base
Champ à
Zone des critères
compter (nom)
 Les fonctions de Base de données

 Calcul statistique: fonction MAX

 Salaire maximal des secrétaires femmes:

=BDMAX($A$1:$G$16;$F$1;$I$1:$O
$2))
Plage de la base
Champ pour
Zone des critères
calculer le
maxi (salaire)
 Les fonctions de Base de données

 Calcul statistique: fonction ECARTYPE

 Ecartype des salaires des administrateurs:

=BDECARTYPE($A$1:$G$16;$F$1;$I$1:$O$
2))
 Protection des données

Protection d’une plage de cellule

Il est possible d'écraser par mégarde des données
importantes dans une colonne en tapant un autre
contenu à la place. Pour éviter ce désagrément,
vous pouvez protéger le contenu de la base.
Pour cela:

1. sélectionnez toutes les cellules dont vous voulez

interdire la modification. Activez le menu
Accueil, cliquez sur Format et choisissez
Verrouiller la cellule.
Protection des données
 Protection des données

2. Cliquez de nouveau sur le bouton Format et

choisissez Protéger la feuille

3. Cochez les actions

que vous autorisez
sur la feuille
verrouillée
Désormais, toute tentative d'écriture dans
une cellule protégée se soldera par un
message d'erreur