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.