Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 1 livre papier acheté 
= la version en ligne automatiquement offerte. Cliquez ici
  1. Livres et vidéos
  2. Analysez efficacement vos données
  3. Gestion des données sources
Extrait - Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
Extraits du livre
Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
2 avis
Revenir à la page d'achat du livre

Gestion des données sources

Introduction

De la bonne utilisation de vos données sources dépend la pertinence de vos résultats. Excel vous permet d’utiliser plusieurs types de données sources :

  • Une feuille de calcul Excel dont la plage est fixe.

  • Une feuille de calcul Excel dont la plage est définie dynamiquement.

  • Une sélection discontinue Excel (plusieurs tableaux situés dans la même feuille, plusieurs feuilles d’un même classeur, ou plusieurs classeurs).

  • Une base de données externe (autre classeur Excel, une table Access, une table SQL Server...).

Connaissances nécessaires

Techniques de conception des tableaux croisés dynamiques

Consolidation des acquis et nouveaux apports

Créer des plages sources dont les dimensions s’ajustent automatiquement

Modifier la plage source

Utiliser des sources multiclasseurs

Utiliser des données en provenance d’autres logiciels

Modifier une plage source

Un tableau croisé dynamique utilise pour effectuer une synthèse les données qui lui ont été spécifiées lors de sa création.

Vous aurez peut-être besoin de modifier ces sources dans certains cas :

  • Vous n’avez pas spécifié de plage dynamique et le nombre de lignes ou de colonnes de la source a augmenté.

  • La synthèse doit être réalisée sur une plage de données différente.

Afin de mettre en pratique cette fonctionnalité, nous allons travailler sur le classeur DépensesMénage.xlsx. Ce classeur est composé de deux feuilles (Depenses2015 et Depenses2016), chacune listant l’ensemble des dépenses annuelles d’un foyer.

Deux tableaux croisés vont être créés, chacun faisant référence aux dépenses d’une année. 

Les premières lignes de chaque feuille sont représentées ci-dessous.

images/c7-01.PNG

Feuille Depenses2015

images/c7-02.PNG

Feuille Depenses2016

Conception du premier tableau croisé dynamique

Pour obtenir un suivi en temps réel de nos dépenses tout au long de l’année, il est judicieux de concevoir une plage dynamique pour chaque année.

 Dans l’onglet Formules, groupe Noms définis, cliquez sur le bouton Gestionnaire de noms, ou bien utilisez le raccourci-clavier...

Utiliser une plage de données discontinue

Il est possible que vous ayez parfois besoin de consolider des informations en provenance de plusieurs tableaux. Ces données peuvent être :

  • Des tableaux non contigus situés à l’intérieur d’une même feuille.

  • Des tableaux situés sur plusieurs feuilles d’un même classeur.

  • Des tableaux situés sur des feuilles de plusieurs classeurs.

Si ces informations existent dans d’autres classeurs, on considère que ce sont des données externes. Excel est capable d’effectuer une synthèse à partir de ces informations, cependant nous vous conseillons d’organiser la structure de ces données de manière identique.

Nous allons considérer ici que nous possédons deux sites de ventes de livres. Chacun de ces sites nous adresse par e-mail chaque fin de semaine un classeur Excel incluant le détail des ventes effectuées depuis le début de l’année, ceci uniquement pour les ouvrages d’un éditeur particulier. Notre objectif consiste à établir, à partir de ces deux classeurs, une statistique comparative des ventes des deux sites pour cet éditeur.

Pour pouvoir effectuer ce travail, vous disposez de deux classeurs : VentesLyon.xlsx et VentesValence.xlsx.

 Ouvrez ces deux classeurs.

Chaque classeur contient une feuille nommée Ventes dont la structure est présentée ci-dessous. Les ventes ont été saisies jusqu’au 30/11/2016.

images/c7-10.PNG
images/c7-11.PNG

VentesLyon.xlsx

VentesValence.xlsx

Afin de vous faciliter la tâche, et avant de fournir les fichiers vierges à chaque site, vous avez paramétré une plage de données dynamique de deux colonnes, dans chaque fichier.

Dans chacun des classeurs, la plage source dynamique a été nommée ListeVentes, et est définie par la formule : =DECALER(Ventes!$A$1;;;NBVAL(Ventes!$A$1:$A$5000);3)

Activer l’Assistant Tableau croisé dynamique

Nous...

Utiliser une source de données externe

Excel est un formidable outil de calcul et d’analyse. Il permet, comme avec un SGBDR (Système de Gestion de Base de données Relationnel), de gérer les relations entre des tables. Si vous avez un système de gestion conçu sur Access, vous pouvez effectuer vos analyses avec Excel à partir d’une connexion à une base de données Access.

Afin de pouvoir illustrer la technique d’utilisation de sources externes, nous utiliserons une première base de données Access nommée BDFormations.accdb.

Cette base est relative aux formations réalisées par les salariés d’une entreprise et est composée de trois tables liées entre elles : Employés, Formations et FormationsEmp.

La fenêtre des relations d’Access est présentée ci-dessous.

images/C7-28.PNG

Nous vous présentons ci-après les premiers enregistrements de chacune des tables.

Table Employes :

images/C7-29.PNG

Table Formations :

Un enregistrement correspond à une formation.

images/C7-30.PNG

Table FormationsEmp :

Pour chaque formation, cette table mémorise les employés qui ont suivi les formations.

images/C7-31.PNG

Après cette présentation des données sources, nous allons construire le tableau croisé dynamique.

Notre objectif est de connaître le nombre d’heures CPF réalisées cette année par chaque salarié et le coût total pour l’entreprise des formations par domaine de formation.

Base de données Access - Méthode 1

Une mauvaise technique serait de copier/coller les informations des tables Access dans une feuille de calcul Excel, ceci pour deux raisons :

  • Tout d’abord cette méthode ne crée pas une liaison dynamique entre Access et Excel. Lorsque vous souhaiterez mettre à jour votre tableau croisé, ceci vous obligerait à ouvrir la base de données dans Access puis à effectuer un copier/coller des nouvelles informations dans la feuille pour disposer des données actualisées.

  • Ensuite cette méthode alourdit le traitement puisque les informations seraient stockées dans Excel.

Nous allons vous présenter ici une technique permettant de pallier ces deux problèmes.

Dans notre exemple, la base de données a été sauvegardée dans un dossier...