Cours en ligne Excel 2010 base de données, tableaux croisés
Section outline
-
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 LE FILTRE AUTOMATIQUE
Le filtre automatique va permettre d'utiliser les valeurs des champs dans des listes déroutantes.
ACTION
onglet "données"
groupe "trier et filtrer" (3è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 colonneinfos
<clic g> sur pour enlever les triangles pointés vers le bas près des champs lorsqu'ils ne sont pas utiles
SÉLECTIONNER DES CRITÈRES
ACTION
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 bleuinfos
efface le filtre en cours
annule tous les filtres et réaffiche tous les enregistrementsexercice
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"
avancé
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 :
L'un ou l'autre s'affichent selon la nature des données de la colonnefiltres textuels affiche les opérateurs de comparaison de texte
filtres numériques affiche les opérateurs de comparaison numériques
exempleDans 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 100exercice
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 automatiqueavancé
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
exercice
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 la région "Est" dont les ventes sont inférieures ou égales à 93 k€
(voir base terminé.xlsx, feuille "solution2") puis tout réafficherIl 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.
ACTION
bouton droit
sélectionner la cellule
<clic d> filtrer
<clic g> sur
seuls les enregistrements possédant pour le champ donné la même valeur que la cellule active restent affichésexercice
Dans base.xlsx, feuille "base de données"
Masquer les enregistrements ne concernant pas la ville de Caen
Tout réafficherEnfin, il est encore possible de filtrer les 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.
infos
Si les couleurs de police ou de cellule sont uniformes, la commande est en grisé
exercice
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 tri2 LE 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) LA PLAGE DE DONNÉES
La plage de données correspond à l'ensemble des cellules contenant les informations qui devront être traitées. Elle peut comprendre la totalité ou une partie d'une feuille de calcul. On peut utiliser successivement des plages de données différentes de la feuille de travail.
b) LA ZONE DE CRITÈRES
La zone de critères est une zone de la feuille de travail dans laquelle vont être indiquées les différentes conditions de sélection des enregistrements.
CRÉATION DE LA ZONE DE CRITÈRES
ACTION
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 suivantesATTENTION
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) L'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).
ACTION
onglet "données"
groupe "trier et filtrer" (3è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
si une cellule de données est déjà sélectionnée, Excel détermine seul la plage de donnéesexercice
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 "solution2")infos
évite d'afficher des enregistrements identiques pour les champs concernés
d) LA ZONE D'EXTRACTION
C'est une zone de la feuille de travail où vont s'inscrire les enregistrements répondant aux critères spécifiés.
ACTION
onglet "données"
groupe "trier et filtrer" (3ème bloc)une cellule de données étant sélectionnée
<clic g> sur
cocher
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éesinfos
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
ATTENTION
La zone d'extraction ne peut pas être sur une autre feuille de travail
CONSEIL
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)exercice
Dans base.xlsx, feuille "base de données"
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")e) LES CRITÈRES D'EXTRACTION
Ils peuvent être beaucoup plus élaborés que dans le cas du filtre automatique.
ACTION
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 texteCHIFFRE
=, <, <=,>,>=…
les opérateurs standard sont disponiblesinfos
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ésCONSEIL
Pour cerner la valeur d'un champ, remplacer le nom d'un champ non utilisé dans la zone de critères par celui du champ à cerner et indiquer des formules complémentaires dans les deux cellules de la même ligne
exercice
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)ATTENTION
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.
CRITÈRES DE SÉLECTION CALCULE
ACTION
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
(exexercice
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")3 L'ANNULATION DU FILTRE
Les enregistrements ne correspondant pas aux critères sont masqués. Il suffit de réafficher les lignes par le menu ou avec la souris pour afficher la totalité de la base.
AFFICHER LES ENREGISTREMENTS MASQUES
ACTION
<clic g> sur
éventuellement répéter l'opération pour enlever le filtre automatique -
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.
ACTION
onglet "insertion"
groupe "tableaux" (1er bloc)une cellule de données étant sélectionnée
<clic g> sur
confirmer l’origine des données
spécifier l’emplacement de création du tableau croisé
pour valider
le volet "office" affiche la structure du tableau croisé
faire glisser les champs à leur positioninfos
Modifier la présentation du volet "Office" en cliquant sur
Le déplacer en le faisant glisser par sa barre de titre sur le documentLe tableau est créé dynamiquement et offre une présentation synthétique et vivante des données au fur et à mesure du positionnement des champs.
exercice
Ouvrir le classeur tabcrois.xlsx
Créer à partir des données un tableau croisé permettant de lister les résultats par Région, par Ville et par Nom
(voir solution : tabcrois terminé.xlsx feuille "solution 1")Un simple <clic g> dans le tableau croisé affiche sa structure dans le volet office ; toute modification est lors possible en temps réel.
infos
Pour n'afficher que les valeurs correspondant à certains critères des champs ligne ou colonnes, il suffit de décocher les valeurs autres
MODIFIER LES PARAMÈTRES
ACTION
<clic g> sur
décocher les éléments à ne pas prendre en compte
pour validerinfos
coche toutes les valeurs
exercice
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")Une autre fonction que la somme peut être utilisée pour les champs de valeur.
CALCULS
ACTION
dans le tableau croisé
<clic d> sur un champ de valeur
<clic g>
choisir le type de calcul
pour validerdans le volet de structure du tableau
<clic g> sur du champ de valeur
<clic g>
choisir le type de calcul
pour validerexercice
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")