Mesurez vos compétences en analyse de base de données

Publié le 08 septembre 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Base de données Excel

Mesurez vos compétences en analyse de base de données

Dans le cadre du concours ModelOff 2013, 16 questions ont été posées aux candidats au sujet d’une base de données (en plus de toutes les autres questions portant sur d’autres sujets, évidemment!). Cet article a pour objectif de partager avec vous, deux de ces questions, portant sur cette base de données et surtout, vous fournir les étapes à suivre pour répondre adéquatement à ces questions.

.

Base de données de départ

Pour répondre aux deux questions suivantes, vous devez vous référer à une base de données, dont voici un extrait:

Base de données Excel

.

Télécharger la Base de données.

.

Question 1

En ne considérant que les codes postaux 3013, 3017 et 3031, quels items ont réalisé le plus de profits en février?

 

Résolution Question 1

Pour répondre à cette question, vous devez construire un tableau croisé dynamique, à partir de la base de données ci-haut. De préférence, vous devriez d’abord mettre la base de données sous forme de tableau. Pour savoir comment mettre la base de données sous forme de tableau, vous pouvez relire l’article Excel: Mettre sous forme de tableau. Vous pourriez également utiliser les filtres mais il est fort à parier que dans ce cas, vous mettriez plus de temps à répondre aux questions. Ça demeure néanmoins une option.

.

Étape 1: Comme la base de données ne comprend pas de colonne avec les profits, vous devez l’ajouter. Vous pouvez le faire de deux façons: soit vous insérez un calcul personnalisé dans votre tableau croisé dynamique, soit vous ajoutez une colonne de calculs à votre base de données. Si vous souhaitez insérer un calcul personnalisé dans votre tableau croisé dynamique, je vous invite à relire l’article Insérer une fonction personnalisée dans un tableau croisé dynamique. Sinon, vous pouvez simplement ajouter une colonne à votre base de données, avec le calcul suivant:

    • Ventes = (# unités vendues * prix de vente unitaire)*(1-% de rabais)
    • Coûts = # unités vendues * coût unitaire
    • Profits = Ventes  – coûts

 

Étape 2: Convertissez les jours en mois dans votre tableau croisé dynamique. Pour ce faire, vous devez utiliser la fonction “grouper”. Pour savoir comment utiliser la fonction “grouper”, vous pouvez relire l’article Tableaux croisés dynamiques dans Excel: 3 fonctionnalités sous-utilisées.

 

Étape 3: Une fois que vous avez ajouté votre nouvelle colonne, soit celle des profits, et que vous avez transformé vos jours en mois, vous pouvez construire un tableau croisé dynamique avec les paramètres suivants:

    • Filtre sur les codes postaux: ne retenir que les codes postaux: 3013, 3017 et 3031
    • Filtre sur la date : ne retenir que février
    • Codes d’items dans les lignes du tableau
    • Somme des profits dans la section des valeurs

Tableau croisé dynamique

Étape 4: Vous pouvez trier les données en ordre décroissant et retenir la première valeur ou faire le top 1 des plus grandes valeurs. Dans le premier cas, attention de bien trier les données sur la colonne des profits et non sur la colonne des codes d’items. Dans le deuxième cas, attention de bien choisir le “top” 1 des items selon la somme des profits.

Top x des données.

À la fin, vous devriez obtenir la réponse suivante: Code d’item 17.

Tableau croisé dynamique

 .

Question 2

Placez les vendeurs en ordre décroissant, basé sur le nombre de factures qu’ils ont émises pendant le mois de mai.

.

Résolution Question 2

Attention, cette question est une attrape! Pour obtenir la bonne réponse, il faut faire gaffe aux numéros de factures puisqu’on peut retrouver plus d’une fois le même numéro de facture dans la base de données.

.

Étape 1: Ajoutez une colonne à votre base de données pour indiquer les numéros de factures uniques. Pour ce faire, vous devez utiliser la fonction Nb.si (Countif), imbriquée dans la fonction Si (If), tel qu’illustré dans l’article Nombre de données distinctes dans une base de données.

Étape 2: Construisez le tableau croisé dynamique comme suit:

    • Filtre sur la date : ne conserver que le mois de mai
    • Vendeurs dans les lignes du tableau
    • Somme des valeurs distinctes dans la section valeurs

Tableau croisé dynamique

Étape 3: Triez les données par odre décroissant, sur la somme des valeurs distinctes, pour obtenir:

Tableau croisé dynamique

  .

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

11 réflexions sur “Mesurez vos compétences en analyse de base de données”

  1. Mario-Charly TOBOSSOU

    Bonjour Sophie Marchand
    Dans la question 2 toujours; il y a une erreur dans la formulation du problème : c’est plutôt Mai et non Février.

  2. Mario-Charly TOBOSSOU

    J’adore ce que tu fais, je suis un amoureux fou de la BI aussi.
    LA première chose que je fais le matin une fois au bureau : c’est aller sur le CFO masqué

  3. Mario-Charly TOBOSSOU

    En utilisant systématiquement des colonnes calculées; on introduit un row context qui en matière de performance est moins efficace que la mesure qui elle est plutôt évaluée selon un Filter Context.

    1. Bonjour Mario-Charly,

      Ce serait une préoccupation, en effet, si l’objectif était de mettre en place un outil. Par contre, ici, l’objectif était de répondre à 16 questions en 40 minutes sur une base de données. Dans ce contexte, je crois qu’il est plus rapide d’utiliser l’approche que je propose ci-haut. De plus, c’est ce que suggère ModelOff dans sa question, soit utiliser des TCD et insérer des colonnes de calculs dans la base de données.

      Au plaisir,

      Sophie

  4. bonjour
    je ne trouve pas le même résultat sur le 1er tableaux croisé dynamique et je ne vois pas où je fais l’erreur ?
    je trouve item 44 pour 4398.9

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top