En analyse de base de données, des outils pas mal puissants (SPSS, Tableau, Zoho Analytics par exemple) sont disponibles. Cependant, ces outils peuvent nécessiter un certain nombre d’heures de formation et même des connaissances particulières, notamment en statistiques… Ce que j’appelle maîtriser la force.
Vous ne le savez pas, mais en analyse de base de données, vous avez tous et toutes à portée de souris un outil d’analyse très puissant; fort probablement plus puissant que vous ne pouvez l’imaginer ! Et c’est pour cette raison que je fais ici une mise à jour améliorée de mon billet écrit il y a quelque temps sur le sujet.
Voici quelques trucs de plus pour vous aider à analyser efficacement une base de données (analyse des ventes par exemple) avec Excel.
Bien organiser sa base de données
Pour vous faciliter la vie, regroupez l’information sous une seule feuille Excel. Aussi, identifiez clairement chacune de vos colonnes pour vous y retrouver facilement.
Pour pouvoir utiliser efficacement Excel en analyse de données, utilisez cette rigueur :
- 1 enregistrement par ligne
- 1 information par colonne
Par exemple : vous désirez analyser les factures de l’année en cours. Voici un exemple de base de données à ne pas faire :
| ID facture | Date | Produit vendu | Total | client |
| 20001 | 16 janvier 2020 | 4 XYZ | 400$ | Cie ABC inc. |
| 3 DEF | 900$ | |||
| 20003 | 23 mars 2020 | 6 ABX | 1200$ | Cie ZZZ inc. |
| 1 STU | 200$ | |||
| … | … | … | … | … |
Lorsque vous tenterez de faire un tableau croisé dynamique, vous ne pourrez pas mettre ces résultats dans le temps, ni savoir la quantité par produit, et l’analyse par facture n’est pas possible.
Voyons maintenant ce que vous devriez avoir :
| A | B | C | D | E | F | G | H | |
| 1 | ID facture | Mois | Annee | Produit vendu | QTÉ | Prix unitaire | Total | client |
| 2 | 20001 | 01 | 2020 | XYZ | 4 | 100$ | 400$ | Cie ABC inc. |
| 3 | 20001 | 01 | 2020 | DEF | 3 | 300$ | 900$ | Cie ABC inc. |
| 4 | 20003 | 03 | 2020 | ABX | 6 | 200$ | 1200$ | Cie ZZZ inc. |
| 5 | 20003 | 03 | 2020 | STU | 1 | 200$ | 200$ | Cie ZZZ inc. |
| … | … | … | … | … | … | … |
Un vœu pieux d’arriver à une telle structure? Mais non, Luke…
Les formules Excel : la source de la force
Excel vous offre tout plein de formules pour vous permettre d’y arriver sans taper le tout à la main. Voici quelques exemples :
- Comment séparer la quantité dans le champ Produit :
- Créez une nouvelle colonne
- Dans cette nouvelle colonne, saisissez la formule : (=gauche(A2;2))
- Ce qu’elle fait : on dit à Excel d’aller chercher dans la cellule A2 les 2 premiers caractères. Vous pouvez facilement répliquer la formule à toute votre base de données en sélectionnant la cellule avec le coin inférieur droit et glisser vers le bas
- Comment séparer le champ date :
- Il arrive parfois qu’au lieu d’avoir 16 janvier 2020, vous ayez une série de chiffre du genre d’affiché : 42385. Laissez-moi vous montrer quelque chose d’awesome :
- Dans une cellule, saisissez la formule =annee(A2). Voilà; vous avez la date correspondante! Essayez avec la formule =mois(A2) ou =jour(A2), ça fonctionne aussi!
- Séparer de l’information :
- Une autre fonction peut vous aider! Supposons que vous désirez séparer en 2 colonnes une information qui se structure toujours de la même façon : ex : 4 xyz, 3 abc. C’est constant : la quantité est toujours séparée par un espace. Sélectionnez vos données que vous désirez mettre sur 2 colonnes. Aller dans Données : convertir. Choisissez l’option délimitée, suivant, puis cochez espace. Voilà, vos données se séparent automatiquement sur 2 colonnes !
- Rechercher, remplacer :
- Cette fonction est magique! Supposons que vous désirez remplacer dans toute votre colonne des points par des virgules, simple comme comme suit : sélectionnez vos données, aller dans l’onglet accueil puis rechercher remplacer. Entrez la valeur que vous désirez rechercher (ici les « . ») puis entrez la valeur que vous désirez voir apparaître à la place (ici les « , »). That’s it !
- Une fois terminé, si vous désirez convertir vos formules en valeur, sélectionnez vos cellules, faites copier, puis clic droit. Choisissez « Collage spécial », puis dans le menu choisissez « valeur ».
Utiliser la puissance des tableaux croisés dynamiques
Lorsque votre base de données est prête, sélectionnez l’ensemble des données puis allez dans l’onglet, insertion. Tableau croisé dynamique. Faites « Ok » puis vous verrez une feuille Excel se rajouter. Dans la fenêtre de droite, vous pouvez afficher les valeurs que vous souhaitez en ligne ou en colonne.
Vous pouvez donc facilement afficher en ligne et colonne, par exemple, des ventes par segments / produits et ainsi voir vos couples produits marchés.
Avec ces tableaux, vous pouvez même transposer le tout en graphique. Pas mal pour vos prochaines présentations ?
…et Google Sheet?
Envie de partager l’info en temps réel avec vos collègues padawan ou sith ? Google Sheet, par exemple, vous permet d’importer les données dans une feuille et ainsi faciliter la collaboration.
…et pourquoi pas, un dashboard?
Vous avez besoin de présenter de façon visuel un rapport ? Essayez Google Data Studio. Outil gratuit, il vous permet de créer un tableau de bord (dashboard) avec plusieurs sources de données en temps réel (ex. : Google Analytics, Google Spreadsheet, certains médias sociaux, etc.).
Truc du pro : cherchez how to…
Pour avoir l’air d’un pro, lorsque vous cherchez une solution avec une formule dans Excel, Google sheet ou encore dans Google Data Studio… cherchez dans Google ! Ex. :
- Comment convertir un champs date dans Excel
- Comment faire XYZ dans Excel
- How to…
La communauté « d’Excelleurs » est pas mal créative. Et vous aussi, allez finir par maîtriser Excel comme un maître !
