Excel - Conseils pour la structuration d'une base de données

Bonjour à tous,

J'utilise Excel 365 FR. Je rencontre un problème concernant la structure d'une base de données (i.e. la manière de présenter les données pour permettre leur analyse plus tard). J'espère trouver ici des conseils de personnes bien plus expérimentées que moi pour m'aider à y voir plus clair.

Je mets en pièce jointe un fichier Excel pour illustrer mon propos. Pour faire simple, supposons que j'étudie l'exemple d'une base de données, dans laquelle j'ai des cours (Maths, Méca, Physique, etc.), des durées (2h, 4h, etc.), des étudiants (Groupe1, Groupe2, etc.), des profs (Marc, Julie, Jean, etc.), des salles (Salle1, Salle2, Salle3, etc.). Intuitivement, je propose une base de données avec une colonne cours, étudiants, prof, salle. Jusque-là tout va bien et je peux analyser mes données via Tableau Croisé Dynamique (TCD) comme je l'entends. Par exemple, je peux connaître le nombre heures qui est assuré par un prof, le taux de charge d'une salle, vérifier si les groupes d'étudiants ont eu le même nombre d'heures de cours, présenter une maquette des cours avec les heures associées, etc.

Le problème se pose lorsque pour un même cours (de Maths par exemple), j'ai plusieurs données associées (comme plusieurs groupes d'étudiants qui assistent à ce cours, ou bien plusieurs profs qui donnent le cours, ou encore plusieurs salles nécessaires pour assurer ce cours). Dans ce cas de figure, mon TCD ne dissocie pas les données (ce qui me semble normal). Par conséquent, je n'arrive pas avoir la bonne granulométrie dans mon analyse. Je n'arrive plus à connaitre le nombre d'heures qui est assuré par un prof, puisque je vais avoir les heures réalisées par le groupe de profs. Même chose pour les autres colonnes. Du coup, je suis perdu.

J'ai beau essayé de proposer une structure de mes données dans divers sens possibles (ce sont les 3 feuilles du classeur Excel), je n'arrive pas à trouver une représentation qui me convient et qui me semble pertinente/résiliente/dynamique/évolutive/maintenable. On peut supposer des centaines de groupes d'étudiants, de profs, de salles, etc. Bref des données évolutives in fine.

Ce type de problème se retrouve, me semble-t-il, régulièrement dans nos contextes respectifs. Quels conseils me donneriez-vous pour m'aider à structurer ce type de base de données ; voir même de prendre le problème sous un autre angle pourquoi pas ?

Un grand merci d'avance à ceux que ça inspire !

Bonjour,

Je dois avouer que votre fichier d'exemple n'est, pour moi, pas très parlant par rapport au problème évoqué...

Cependant je pense que les limites que vous atteignez sont tout autant celles d'Excel que de la structure meme des données. Excel n'est pas un outil de BDD, il n'en a pas tous les outils "de base". Pour citer vraiment quelques limites basiques : https://www.webanalytix.fr/pourquoi-ne-pas-utiliser-excel-comme-une-base-de-donnees/

Mais bonne nouvelle pour vous, il existe quelques contournements. Tout d'abord regardez du cote de PowerQuery, c'est un outil beaucoup plus fin/précis/complexe pour l'analyse de données. Complètement intégré à Excel.

Enfin je vois de la redondance dans vos données. Je pense qu'il serait judicieux de prendre le temps d'établir un diagramme relationnel et ensuite, si possible (ie. relations linéaires simples), séparer vos données en plusieurs tables distinctes PUIS faire les regroupements via PowerQuery.

Bonjour,

Merci d'avoir pris le temps de me répondre.

Pour aider concernant le fichier Excel posté :

Le fichier Excel que j'ai posté contient 3 feuilles. Chaque feuille présente les mêmes données mais structurées d'une manière différente. Je cherche justement la façon de présenter les données qui soit la plus correcte possible. Pour le moment, aucune des 3 feuilles ne me permet d'avancer correctement. La feuille n°1 me semble la plus correcte, mais comme il est possible d'avoir plusieurs données dans une même cellule...ce qui me bloque jsutement.

Merci pour le lien. Je me doute qu'Excel n'est pas idéal comme outil de base de données, ni de stockage, mais plus adapté pour la partie calculatoire. Cependant, j'ai le sentiment que mon problème de structure/présentation des données devrait trouver une solution de présentation/analyse simple. Mais je me trompe sans doute. On est peut-être dans les limites de l'outil malgré tout.

Je connais un tout petit peu PowerQuery. Je vais regarder de plus près comment cela pourrait m'aider à structurer les données d'entrées. J'ai du mal à voir là tout de suite comment. Je vais essayer d'établir un diagramme relationnel comme conseillé.

Pour poursuivre les échanges tout de même :

Mettons que j'ai une table qui liste les cours (Maths, Méca, etc.), et une table qui liste les profs (Marc, Julie, Jean, etc.). Comment traiter le cas d'un cours assuré par 2 profs ? Je ne pense pas traiter ce cas sur 2 lignes (avec un prof unique sur le même cours) car cela signifierait que j'ai 2 lignes, et que la durée du cours doublerait ce qui n'est pas correct.

Je vais creuser encore en attendant d'autres conseils éventuels.

Bonjour à tous,

Le temps de réflexion depuis hier m'amène à proposer la solution décrite ci-dessous. Je ne pense pas que ce soit la meilleure, mais j'ai le sentiment que ça pourra faire l'affaire. Pour les curieux, je partage le fichier de présentation de la structure des données en pièce jointe, pour les autres, je précise la démarche ici :

Dans ma base de données Excel, certaines colonnes contiennent des données liées. Par exemple dans la feuille "Table planning", la colonne Elève contient (Groupe1, Groupe2), la colonne Prof (Marc, Julie), la colonne Salle (Salle3, Salle4). J'aimerais que cette base de données contiennent des données uniques de sorte que je puisse réaliser des TCD pertinents. Par exemple, la colonne Elève (Groupe1 sur une ligne puis Groupe2 sur une autre ligne), la colonne Prof (Marc sur une ligne puis Julie sur une autre ligne), la colonne Salle (Salle3 sur une ligne puis Salle4 sur une autre ligne).

La solution que je retiens à ce stade est de dupliquer ma base de données initiale autant de fois qu'il y a de colonnes qui présentent des données liées. Dans mon exemple précédent, les colonnes Elève, Prof et Salle contiennent toutes les 3 des données liées. Je vais donc dupliquer ma base initiale 3 fois. Ce qui donne les feuilles Excel "Table Elève", "Table Prof" et "Table Salle". Pour chaque nouvelle base de données, je m'occupe uniquement d'une seule colonne contenant des données liées. Les autres colonnes contenant des données liées ne changent pas dans cette feuille, mais dans les autres. Et donc je vais ajouter autant de nouvelles lignes qu'il y a de données liées.

Par exemple, la feuille "Table Elève" s'occupe de la colonne Elève. Le cours de physique est réalisé par le groupe1 et le groupe 2 (Groupe1, Groupe2). Je vais donc copier coller cette ligne. Une ligne contiendra Groupe1, l'autre ligne contiendra Groupe2. Je pourrais ainsi analyser cette colonne Elève finement. Je fais pareil pour les autres feuilles. C'est une macro VBA qui s'occupera de créer les bases de données "Slave" contenant des données non-liées, à partir de la base de donnée initiale "Master" contenant toutes les données éventuellement liées.

Cette méthode est loin d'être parfaite mais elle me permet de répondre à mon besoin : analyser finement des données qui se retrouvent liées dans un tableau Excel.

Si certains ont des conseils pour faire mieux (car dupliquer des bases de données, multiplier des données, augmenter le stockage, augmenter le risque d'erreurs, complexifier la structure de données, augmenter les temps de calculs, complexifier la maintenance, etc.). Autant d'arguments pour ne pas utiliser cette approche.

Belle après-midi à tous.