Médiaforma

All posts in excel2016

Excel 2016 est en mesure d’analyser des données provenant de bases de données relationnelles. Pour illustrer cette fonctionnalité, nous allons utiliser la base de données exemple Northwind. Et plus précisément, les tables Orders et Order Details de cette base de données. Ces deux tables sont reliées par le champ RéfCommande.

Basculez sur l’onglet Données dans le ruban, puis cliquez sur l’icône Access dans le groupe Données externes. Cette action affiche la boîte de dialogue Sélectionner la source de données. Sélectionnez le fichier Northwind et cliquez sur Ouvrir pour l’ouvrir. Cochez la case Activer la sélection de plusieurs tables et choisissez les tables Orders et Order Details.

Cliquez sur OK. Une nouvelle boîte de dialogue apparaît. Sélectionnez Rapport de tableau croisé dynamique et validez en cliquant sur OK.

Après un petit moment, un tableau croisé dynamique est inséré dans la feuille de calcul.

À titre d’exemple, nous allons ajouter les champs suivants dans le tableau croisé dynamique :

  • OrderID et UnitPrice de la table Order Details ;
  • CustomerID de la table Orders.

Comme vous pouvez le voir, OrderID apparaît en tant que somme dans la feuille de calcul. Pour le transformer en un simple champ, il suffit de faire glisser l’entrée Somme de OrderID de la zone Ʃ VALEURS dans la zone LIGNES.


Les tableaux croisés dynamiques sont utilisés pour synthétiser rapidement un ensemble d’informations. Considérons le tableau suivant qui indique le nombre de ventes de plusieurs objets par deux vendeurs nommés Michel et Marc.

Pour synthétiser ces données, vous allez définir un tableau croisé dynamique. Sélectionnez une cellule dans la plage A1:C9. Basculez sur l’onglet Insertion dans le ruban, puis cliquez sur l’icône Tableau croisé dynamique dans le groupe Tableaux. Cette action affiche la boîte de dialogue Créer un tableau croisé dynamique. Toutes les données de la feuille sont automatiquement sélectionnées. Choisissez l’emplacement où doit être créé le tableau croisé dynamique, puis cliquez sur OK.

Choisissez les champs à inclure dans le tableau. Le résultat est immédiatement affiché dans la partie gauche de la feuille.


Supposons que vous placiez 10 000 € sur un compte qui rapporte 6% par an. Pour savoir au bout de combien de temps cette somme aura doublé, vous utiliserez la fonction NPM d’Excel :

= NPM(6%; 0; 10000; -20000; 0)

La fonction NPM indique qu’il faudra un peu moins de 12 années pour doubler l’investissement.

Supposons que vos besoins changent. Vous voulez atteindre la valeur 25 000 €, toujours en 11,89 ans. Quel taux devez-vous viser pour arriver à vos fins ?

Pour résoudre ce problème, vous allez définir une valeur cible. Entrez la valeur à atteindre (25 000 €) dans la cellule C2. Sélectionnez la cellule D5, qui contient le résultat de la fonction NPM. Basculez sur l’onglet Données dans le ruban. Cliquez sur l’icône Analyse scénarios et sélectionnez Valeur cible dans le menu. Ici, la valeur à atteindre est 11,89 et la cellule à modifier est A2.

Un clic sur OK, et le solveur montre qu’il vous faut un placement qui rapporte 8% :


Pour regrouper les données issues de plusieurs feuilles de calcul, vous utiliserez la technique dite de consolidation.

Par exemple, supposons que vous souhaitiez ajouter les chiffres d’affaires mensuels des filiales d’une entreprise. Chacune des filiales vous fournit une feuille de calcul ayant la même structure : Corp1, Corp2 et Corp3. Les données seront consolidées dans la feuille de calcul Corp qui contient la même structure que les autres feuilles, mais aucune donnée.

Ouvrez les quatre feuilles de calcul et réorganisez-les pour qu’elles se partagent l’espace de travail d’Excel. Pour ce faire, sélectionnez l’onglet Affichage dans le ruban, cliquez sur Réorganiser tout, sélectionnez Mosaïque et validez en cliquant sur OK.

Sélectionnez la plage de cellules dans laquelle doit être effectuée la consolidation. Ici, la plage B2:D4 de la feuille Feuil1 dans le classeur Corp.

Sélectionnez l’onglet Données dans le ruban, puis cliquez sur l’icône Consolider dans le groupe Outils de données. Cette action affiche la boîte de dialogue Consolider. La valeur Somme est automatiquement sélectionnée dans la liste déroulante Fonction.

Cliquez sur l’icône à l’extrême droite de la zone Référence, sélectionnez la plage B2:D4 dans le classeur Corp1, appuyez sur la touche Entrée du clavier, puis cliquez sur Ajouter. Répétez ce processus pour les classeurs Corp2 et Corp3.

Un clic sur OK et les sommes des valeurs des données des trois filiales apparaissent dans la feuille Feuil1 du classeur Corp :


En quelques clics souris, Excel permet de mettre à jour un ensemble de cellules numériques. Supposons par exemple que vous désiriez augmenter de 5% les valeurs stockées dans un groupe de cellules :

  1. Tapez 105% dans une cellule.
  2. Copiez le contenu de cette cellule dans le presse-papiers en cliquant sur l’icône Copier dans le groupe Presse-papiers.
  3. Sélectionnez les cellules qui contiennent les valeurs que vous voulez mettre à jour.
  4. Cliquez sur la flèche sous l’icône Coller puis choisissez Collage spécial dans le menu.
  5. Sous Coller, sélectionnez Valeurs.
  6. Sous Opération, sélectionnez Multiplication.
  7. Cliquez sur OK pour mettre à jour les valeurs.


Avec Excel 2016, il n’a jamais été aussi simple de se connecter à une base de données, d’effectuer une requête pour obtenir des données et de modifier les données obtenues pour qu’elles s’adaptent à vos besoins. A titre d’exemple, nous allons établir une connexion avec une table Access et modifier les données de cette table pour ne conserver que certaines d’entre elles. Nous utiliserons la base de données exemple de Microsoft Northwind.

Basculez sur l’onglet Données. Cliquez sur l’icône Nouvelle requête dans le groupe Récupérer et transformer, pointez A partir d’une base de données et cliquez sur A partir d’une base de données Microsoft Access. Désignez la base de données à utiliser puis cliquez sur Importer. Au bout de quelques instants, la boîte de dialogue Navigateur donne accès à toutes les tables et requêtes de la base de données. A titre d’exemple, nous allons utiliser la requête Product Sales for 1995. Les données correspondantes apparaissent dans la partie droite de la boîte de dialogue.

Si vous cliquez sur Charger, elles sont incorporées dans la feuille en cours. Ici, nous allons plutôt cliquer sur Modifier pour charger une partie seulement de ces données. Cette action provoque l’ouverture de l’éditeur de requêtes d’Excel. A titre d’exemple, nous n’allons conserver que les enregistrements pour lesquels le champ CategoryName vaut Beverages ou Condiments. Pour cela, nous cliquons sur la flèche qui suit l’en-tête CategoryName, nous décochons toutes les cases à l’exception de Beverages et Condiments et nous cliquons sur OK pour confirmer.

Supposons maintenant que vous vouliez supprimer la colonne CategoryName. Sélectionnez la colonne CategoryName en cliquant sur son en-tête, puis cliquez sur l’icône Supprimer les colonnes dans le groupe Gérer les colonnes. La colonne est immédiatement supprimée.

Remarquez le volet Paramètres d’une requête. Toutes les étapes qui ont conduit aux données actuelles y sont listées par ordre chronologique. Pour supprimer l’une d’entre elles, il suffit de cliquer sur la croix qui la précède. Ici, par exemple, nous supprimons la dernière étape afin de retrouver la colonne CategoryName.

Pour insérer les données dans la feuille courante, il suffit de cliquer sur l’icône Fermer et charger dans le groupe Fermer.


En établissant une connexion avec un fichier de données externe (une table Microsoft Access, un fichier texte, un tableau SQL Server ou une source OLE ou ODBC par exemple), vous pouvez analyser périodiquement ces données dans Excel, sans avoir à les copier dans une feuille de calcul. La feuille de calcul est mise à jour automatiquement lorsque la source de données change.

A titre d’exemple, nous allons lier une table issue d’une base de données Access dans une feuille de calcul Excel.

  1. Sélectionnez l’onglet Données dans le ruban.
  2. Cliquez sur Access dans le groupe Données externes. Cette action affiche la boîte de dialogue Sélectionnez la source de données.
  3. Sélectionnez le fichier Northwind (librement téléchargeable sur la page http://msdn.microsoft.com/fr-fr/library/ab7xs839(VS.80).aspx) et la table Employees puis cliquez sur OK.
  4. Excel demande des précisions sur l’importation des données. Ici, nous choisissons d’insérer les données dans un tableau. L’insertion se fera dans la feuille de calcul en cours d’édition, à partir de la cellule A1. Cliquez sur OK pour procéder à l’importation.