Une fonction bien pratique pour fusionner des fichiers de données avec Excel

Dans un article précédent, nous avons vu qu’il n’était pas très compliqué, voire même plutôt agréable, de réaliser des tableaux croisés avec Excel, qui est présent sur la plupart des ordinateurs, et qu’on pouvait donc se passer dans de nombreux cas d’un logiciel d’analyse des données plus puissant, plus compliqué, et aussi… plus cher ! Mais une des limites de l’utilisation d’Excel pour l’analyse des données, c’est que les données doivent être rassemblées dans une seule et même feuille de calcul. Or, notamment quand on souhaite faire de l’analyse secondaire des résultats d’une grande enquête statistique comme celles que l’INSEE et le Centre Quételet par exemple mettent à notre disposition, il arrive très fréquemment que les données soient dispersées dans plusieurs fichiers, reliés entre eux par un identifiant individuel unique pour chaque individu interrogé. Nous allons voir qu’il existe dans Excel une fonction qui s’appelle RECHERCHEV(), et qui permet de fusionner des données provenant de plusieurs fichiers Excel différents.

Télécharger les fichiers

Préparer les fichiers

Fusionner les fichiers

Terminer la fusion

Et alors, les goûts musicaux dépendent-ils de la PCS

Et s’il y a des individus dans le second fichier qui ne sont pas dans le premier ?

Et avec SAS, ce ne serait pas plus simple ?

Liens utiles

Références bibliographiques

 

Les données d’enquête qui se présentent sous la forme de plusieurs fichiers sont en réalité loin d’être rares. Par exemple, dans une enquête sur les pratiques éducatives des familles, on aura les réponses des enfants dans un premier fichier, celles de leurs parents dans un deuxième fichier, les caractéristiques de leur établissement scolaire dans un troisième fichier, etc. Ou bien dans une enquête longitudinale, au cours de laquelle les individus sont interrogés plusieurs fois successivement, par exemple à un an d’intervalle, leurs réponses sont enregistrées dans autant de fichiers qu’il y a de vagues d’enquête.

La « fusion » de deux fichiers de données ne pose aucun problème quand chaque fichier comporte exactement le même nombre d’individus, et les mêmes individus. Dans ce cas là, il suffit de trier les deux fichiers dans le même ordre (par exemple dans l’ordre croissant des identifiants individuels), puis par exemple de coller les cellules du deuxième fichier dans le premier fichier, à droite des cellules déjà remplies, et en vérifiant bien ensuite que les identifiants correspondent ligne par ligne.

 Attention !

On n’insistera jamais assez sur la nécessité absolue d’attribuer un identifiant unique à chaque individu dans une base de données que l’on a constitué soi-même, par une enquête ou un recueil de données d’archives par exemple : en particulier, ne vous contentez jamais du nom de famille de l’individu, qui peut être porté par plusieurs personnes dans l’enquête, sans compter que vous n’avez pas le droit de conserver des données nominatives sans déclaration du fichier auprès de la CNIL… La solution la plus simple, mais indispensable, consistera par exemple à numéroter tous les individus avec un identifiant numérique unique à partir de 1, créé par exemple dans Excel avec la commande « Remplissage à Série… » du menu « Edition ».

Mais il arrive souvent que les deux fichiers ne comportent pas les mêmes individus. Par exemple, les deux fichiers peuvent comporter des renseignements provenant de deux sources différentes, chacune étant en partie incomplète , comme c’est le cas des deux fichiers de Claire Zalc dont la demande a suscité la rédaction de cet article : dans le premier fichier, les données concernant tous les individus, collectées à partir des dossiers de naturalisation ; dans le second fichier, des données concernant seulement certains de ces individus, collectées dans les dossiers de spoliation des biens pendant la Première guerre mondiale. Tous les individus spoliés ne sont pas dans le fichier des demandes de naturalisation, et réciproquement… Ou bien, c’est aussi le cas dans les enquêtes longitudinales, quand il y a eu ce qu’on appelle de « l’attrition », c’est-à-dire que le nombre de répondants à chaque vague diminue à mesure que le temps passe, parce qu’on perd la trace d’un certain nombre d’enquêtés, ou bien parce qu’ils se lassent.

 

Télécharger les fichiers

 

Pour changer un peu, après avoir beaucoup utilisé les données du fichier détail de l’enquête « Histoire de vie 2003 » de l’INSEE dans les articles précédents, nous allons utiliser pour cet article-ci un autre fichier détail de l’INSEE, que vous pouvez aussi télécharger librement et gratuitement sur son site internet : nous allons étudier la liaison entre l’appartenance socioprofessionnelle et les goûts musicaux, à partir du fichier détail de l’enquête « Participation culturelle et sportive 2003 ». Il s’agit d’une « partie variable » de l’enquête permanente sur les conditions de vie des ménages (EPCVM) réalisée chaque année par l’INSEE, dans laquelle 5.626 personnes représentatives de la population de la France âgée de 15 ans et plus ont été interrogées sur leurs pratiques culturelles et sportives.

Remarque

Si vous souhaitez disposer immédiatement des deux fichiers Excel que nous allons fusionner, sans apprendre où et comment les télécharger et les ouvrir dans Excel, ou bien si vous voulez appliquer la « recette » donnée dans cet article à vos propres fichiers Excel, sautez ce qui suit et passez directement à la partie « Préparer les fichiers ».

Les données de l’enquête sont réparties dans quatre fichiers de données au format dBase (.dbf) reliés entre eux par un identifiant unique pour chaque logement où on a tiré un individu au sort pour répondre à l’enquête :

  • Le fichier menage.dbf contient les variables décrivant le ménage auquel appartient l’individu (5.626 enregistrements 83 variables)
  • Le fichier individu.dbf contient les variables décrivant tous les individus de ces ménages (13.740 enregistrements et 35 variables).
  • Le fichier kish1.dbf contient les variables décrivant les pratiques culturelles de l’individu qui a tiré au sort dans le ménage pour répondre aux questions de (5.626 enregistrement et 251 variables)
  • Le fichier kish2.dbf contient les variables décrivant les pratiques culturelles de l’individu qui a tiré au sort dans le ménage pour répondre aux questions de (5.626 enregistrement et 214 variables)

1. Téléchargez et décompressez ces fichiers sur votre ordinateur. Ils sont disponibles en ligne, dans trois archives compressées au format .zip, dans lesquelles vous trouverez aussi des fichiers contenant la liste des variables, et la liste des modalités. Pour télécharger ces fichiers, allez à cette page :

http://www.insee.fr/fr/themes/detail.asp?ref_id=fd-parcul03&page=fichiers_detail/parcul03/telechargement.htm

2. Ouvrez Excel. Et pour plus de commodité dans la suite, il faut utiliser le style de référence L1C1 pour la désignation des cellules. Vérifiez que l’option « Style de référence L1C1 » est bien cochée dans l’onglet « Général » de la commande « Options » du menu « Outils ».

3. A l’aide de la commande « Ouvrir » du menu « Fichier », ouvrez dans Excel les deux fichiers que nous allons fusionner : individu.dbf, qui contient les caractéristiques socio-démographiques de la totalité des individus composant les ménages de l’échantillon, et kish1.dbf, qui contient les réponses à l’enquête des individus tirés au sort parmi eux pour y participer. Plus exactement, kish1.dbf contient leurs réponses aux questions sur leurs pratiques culturelles, tandis que kish2.dbf contient leurs réponses aux questions sur leurs pratiques sportives.

4. En jetant un rapide coup d’œil aux premières colonnes des deux fichiers, vous pouvez constater facilement que nous ne disposons pas encore, avec la seule variable IDENT qui figure dans la première colonne de chaque fichier, d’un identifiant individuel unique, pourtant indispensable pour fusionner les données. En effet, IDENT est l’identifiant du logement, et on voit dans le fichier individu.dbf qu’il y a souvent plusieurs individus par logement, ayant donc le même identifiant. En revanche, les individus sont numérotés à l’intérieur de chaque logement, et la variable NO qui contient ce numéro figure aussi dans les deux fichiers (à la troisième colonne), comme la variable IDENT (en première colonne). Par conséquent, avec IDENT+NO, nous avons notre identifiant individuel unique. Pour le créer, ajoutez une colonne vide à gauche de la colonne IDENT dans chaque fichier, nommez-là IDENTNO dans la première cellule, puis recopiez dans chaque cellule de cette colonne la formule :

=LC(1)&LC(3)

Une fois que c’est fait, ne conservez pas les formules permettant de calculer IDENTNO dans les fichiers, mais seulement leur résultaindividut : pour cela, il suffit de sélectionner la colonne, de cliquer sur « Copier » dans le menu « Edition », puis sur « Collage spécial » avec l’option « Valeurs ».

5. Les deux fichiers sont pour l’instant toujours au format .dbf. Nous allons les enregistrer au format Excel, en sélectionnant ensuite « Enregistrer sous… » dans le menu « Fichier », puis « Classeur Microsoft Office Excel » comme type de fichier.

 

Préparer les fichiers

 

Si vous avez suivi les étapes précédentes, vous avez à votre disposition les deux fichiers qui vont nous servir d’exemple dans la suite. Sinon, vous pouvez appliquer ce qui suit à vos propres fichiers, ou bien télécharger directement (sans avoir à les créer comme dans ce qui précède) les deux fichiers Excel que nous allons utiliser, en cliquant sur les liens ci-dessous :

individu.xls

kish1.xls

Pour pouvoir fusionner des données provenant de deux fichiers Excel différents, nous allons utiliser la fonction RECHERCHEV() de Excel, qui suppose que les deux fichiers à fusionner soient convenablement structurés. C’est ce que nous allons commencer par vérifier en suivant les étapes suivantes :

1. Ouvrez les deux fichiers Excel, et faites immédiatement une copie de ces deux fichiers en les enregistrant à nouveau sous un nom différent (par exemple en rajoutant copie à la suite du nom d’origine). C’est une sage précaution : comme nous allons ensuite beaucoup les modifier, vous aurez toujours la possibilité de revenir aux fichiers originaux si le résultat ne vous satisfait pas. Dans notre exemple, nous allons ainsi les renommer individucopie.xls et kish1copie.xls, et désormais travailler sur ces deux copies de fichiers.

3. Tout d’abord, il est plus facile de travailler sur de grands fichiers Excel quand les titres des lignes et des colonnes restent toujours visibles. Aussi, nous vous recommandons d’ajouter un « volet » horizontal contenant les deux premières lignes, et un « volet » vertical contenant la première colonne du fichier, et de « figer » ces volets à l’aide de la commande correspondante dans le menu « Fenêtre ».

4. Ensuite, vérifiez que vous allez avoir la place de fusionner toutes vos données dans un même fichier : en effet, dans les précédentes versions d’Excel, jusqu’à Excel 2003 (que j’utilise ici), le nombre de colonnes est très limité : vous n’avez droit qu’à 256 colonnes. Mais dans la dernière version, Excel 2007, vous pouvez avoir jusqu’à 16.384 colonnes. Par conséquent, si vous n’utilisez pas la dernière version de Excel, vous ne pourrez pas fusionner toutes les colonnes de deux fichiers dont le nombre réuni de colonnes dépasse 256. C’est le cas dans notre exemple : le fichier kish1copie.xls comporte déjà 252 colonnes, on ne pourrait en ajouter que 4 supplémentaires, alors que individucopie.xls en comporte 36. Et puis il faut ajouter que même si vous restez dans les limites autorisées par Excel, vous risquez fort de « planter » votre ordinateur si vous lui demandez de fusionner de trop gros fichiers. Par conséquent, pour faire un peu de place et éviter le plantage, et puisque nous voulons seulement étudier les goûts musicaux, nous n’allons garder que les 135 premières colonnes du fichier kish1copie.xls, en supprimant les suivantes, à l’aide de la commande « Supprimer » du menu « Edition ».

5. Vérifiez ensuite que dans chaque fichier, les identifiants des individus figurent dans la première colonne, et les noms des variables dans la première ligne. Dans notre exemple, c’est le cas, la variable IDENTNO figure dans la première colonne. Mais si ce n’est pas le cas dans vos fichiers, déplacez les colonnes des identifiants jusqu’à la première position.

6. Il n’est pas absolument nécessaire que dans les deux fichiers, les individus soient classés dans le même ordre. Néanmoins, vous arriverez plus facilement à manipuler vos données et à vérifier le bon déroulement des opérations si dans les deux fichiers, vous avez trié les données dans l’ordre croissant des identifiants, et il se peut aussi que cela réduise le temps pris par le calcul de la fonction RECHERCHEV().

7. Quand l’individu d’un fichier existe dans l’autre fichier, mais qu’une variable particulière n’est pas renseignée et donc que la cellule correspondante dans le second fichier est vide (par exemple en cas de non-réponse), la fonction RECHERCHEV() va renvoyer la valeur « 0 », alors que vous auriez voulu une cellule vide, ou bien un code de non-réponse. Vous pourriez y remédier en remplaçant tous les « 0 » dans le résultat final par des vides, ou bien par votre code de non-réponse, mais vous risquez, ce faisant, de faire passer aussi pour des non-réponses des valeurs « 0 » qui ont un sens différent : par exemple, dans une variable « Nombre d’enfants », les individus sans enfant vont devenir des individus pour lesquels le nombre d’enfants est inconnu. La solution consiste alors à utiliser dans vos fichiers un code de non-réponse spécifique, au lieu de laisser les cellules vides. Vous pouvez par exemple utiliser le point, « . », qui est le code usuel de non-réponse dans SAS. Pour cela, il vous suffit de sélectionner toute la plage de données du second fichier, puis de cliquer sur « Remplacer » dans le menu « Edition », de rechercher les vides (le contenu de la rubrique « Rechercher » doit être vide) et de les remplacer par des « . » en cliquant sur « Remplacer tout ».

Attention !

En fonction de la taille de votre fichier, l’opération peut prendre plusieurs secondes…

 

Fusionner les fichiers

 

On va se placer, pour expliquer la fusion de données dans Excel, dans le cas où l’on dispose de deux fichiers excel « emboîtés », c’est-à-dire dans le cas où tous les individus de l’un des fichiers à fusionner sont présents dans l’autre (reste le cas, que nous envisagerons rapidement à la fin de l’article, où des individus présents dans l’un ne sont pas dans l’autre et réciproquement). Dans ce cas, il y a deux manières de procéder :

  • Soit vous voulez conserver tous les individus, qu’ils soient communs ou non aux deux fichiers. Dans ce cas, il faut «ajouter» les données du plus petit fichier, qu’on appellera «fichier de départ» dans la suite, à celles du plus grand fichier, qu’on appellera «fichier d’arrivée»;
  • Soit vous voulez conserver seulement les individus présents dans les deux fichiers. Dans ce cas, il faut au contraire «ajouter» les données du plus grand fichier à celles du plus petit.

Nous allons, dans notre exemple, nous placer dans le second cas de figure : nous allons ajouter aux données du premier fichier sur les pratiques culturelles des 5.626 individus ayant répondu à l’enquête (le fichier kish1copie.xls), celles de leurs caractéristiques socio-démographiques puisées dans le second fichier, celui des 13.740 individus des ménages de l’enquête (fichier individucopie.xls). Par conséquent, dans ce qui suit, si vous suivez notre exemple, individucopie.xls sera le « fichier de départ », et kish1copie.xls sera le « fichier d’arrivée ».

Pour « fusionner » les deux fichiers, et réunir sur une même ligne toutes les informations concernant un individu donné, la solution n’est pas très compliquée : il faut utiliser la fonction RECHERCHEV() de Excel.

La fonction RECHERCHEV()

La fonction RECHERCHEV(), utilisée dans une formule Excel, permet de chercher une valeur donnée dans la colonne située à l’extrême gauche d’une plage de données, et renvoie une valeur située à l’intersection de la ligne où cette valeur a été trouvée, et de la colonne de votre choix.

La fonction RECHERCHEV() s’écrit de la façon suivante :

=RECHERCHEV(valeur_cherchée;table_matrice;numéro_colonne;valeur_proche)

Avec :

valeur_cherchée : la valeur à trouver dans la première colonne de la plage de données du fichier de départ. Attention : la fonction RECHERCHEV() ne fait pas de distinction entre les majuscules et les minuscules.

table_matrice : la plage de données dans laquelle est exécutée la recherche de la valeur dans le fichier de départ.

numéro_colonne : le numéro de la colonne de la plage de données dans laquelle il faut prendre la valeur affichée en résultat. Si numéro_colonne est égal à 1, la fonction renvoie la valeur dans la première colonne de table_matrice ; si numéro_colonne est égal à 2, la fonction renvoie la valeur située dans la deuxième colonne de table_matrice, etc.

valeur_proche indique si vous souhaitez que la fonction RECHERCHEV recherche une valeur exacte ou voisine de celle que vous avez spécifiée. Vous devez choisir la valeur FAUX, de façon à interdire à Excel de chercher une ligne avec une valeur approchante de valeur_cherchée.

Remarque : La fonction RECHERCHEV() ne sert pas seulement à fusionner des fichiers de données, elle peut avoir de nombreuses autres applications, y compris dans l’analyse des données en sciences sociales. Par exemple, si vous avez des données concernant les revenus des individus sur une longue période, vous pouvez vous servir de cette fonction pour les transformer en francs constants en utilisant un fichier Excel contenant une matrice qui donne l’évolution de l’indice des prix, comme celle proposée pour le XXe siècle par Thomas Piketty dans les annexes disponibles en ligne des Hauts revenus en France (2001). On pourra aussi s’en servir, si l’on dispose des tables de correspondance qui le permettent, pour récupérer la PCS à partir de la profession, la taille de la commune de résidence à partir du code postal, le sexe à partir du prénom, etc., etc.

Voici la marche à suivre :

1. Placez-vous dans le fichier d’arrivée. Dans notre exemple, il s’agit donc de kish1copie.xls.

2. Comme la fonction RECHERCHEV() a besoin d’un numéro de colonne pour récupérer les données à fusionner (voir encadré ci-dessus), nous allons ajouter une ligne au-dessus des données du fichier d’arrivée, kish1copie.xls, qui comportera ces numéros de colonnes. Donc, commencez par ajouter une ligne vide au-dessus de la première ligne qui contient les noms des variables. Placez-vous ensuite dans cette première ligne, au-dessus de la première colonne vide du fichier, immédiatement à droite de la colonne contenant les valeurs de la dernière variable de ce fichier. Puis à partir de cette position, dans cette première ligne, numérotez les colonnes de 1 à n, n étant le nombre de variables du fichier de départ. Dans notre exemple, le fichier de départ, individu.xls, comporte 36 variables (y compris les variables d’identifiant), on va donc numéroter 36 colonnes vides à droite de la plage de données du fichier d’arrivée, de 1 à 36. Pour y parvenir, il faut mettre la valeur 1 dans la cellule du haut de la première colonne vide, puis sélectionner les 36 colonnes suivantes à droite de cette première cellule, et utiliser la commande « Remplissage à Série… » du menu « Edition », avec 1 comme « valeur du pas ».

3. On va ensuite saisir la fonction RECHERCHEV() dans toutes les cellules de ces 36 nouvelles colonnes. Pour cela, placez-vous dans la première cellule vide de cette nouvelle plage de données, en dessous du « 1 » de la première colonne vide, et saisissez la formule suivante :

=RECHERCHEV(LC1;[individucopie.xls]individu!L1C1:L13741C36;L1C;FAUX)

Pour celles et ceux qui ne sont pas familiers avec la manipulation des formules dans Excel, la façon la plus simple pour y parvenir est de cliquer sur la cellule en question, puis de sélectionner « Fonction… » dans le menu « Insertion ». Tapez ensuite RECHERCHEV dans la fenêtre de recherche des fonctions, puis sélectionnez RECHERCHEV dans la liste des résultats, et cliquez sur « OK ». La fenêtre suivante apparaît :

Dans la rubrique « Valeur_cherchée », entrez « LC1 » pour indiquer qu’il faut rechercher la valeur contenue dans la colonne 1 de la même ligne (donc, l’identifiant de l’individu correspondant à cette ligne) : ainsi, la fonction va chercher, dans le fichier de départ, la ligne dont la première colonne contient la même valeur d’identifiant que celle qui figure au début de cette ligne du fichier d’arrivée, et donc récupérer les données de cette ligne, qui sont les données correspondant au même individu que celui du fichier d’arrivée.

Dans « Table_matrice », il faut désigner la plage de données à récupérer dans le fichier de départ. Après avoir cliqué dans la zone de saisie correspondante, vous pouvez aller la sélectionner directement dans le fichier de départ (individucopie.xls dans notre exemple) et elle s’affichera dans cette fenêtre, et dans la formule, ou bien vous pouvez cliquez sur l’icône , puis sélectionner le fichier de départ, et à l’intérieur du fichier sélectionner toute la plage contenant les données, de son coin supérieur gauche à son coin inférieur droit.

Dans la rubrique « No_index_col », entrez « L1C » pour indiquer qu’il faudra afficher la valeur trouvée dans la colonne de la plage de données (du fichier de départ) dont le numéro est indiqué dans la cellule qui figure dans la première cellule de la même colonne.

Dans la rubrique « Valeur_proche », entrez « FAUX », de façon à interdire à Excel de chercher une ligne avec une valeur approchante de valeur_cherchée.

Cela doit donner ceci :

Quand c’est fait, cliquez sur « OK ».

Attention !

Ensuite, dans certains cas, il va falloir modifier un peu cette fonction. En effet, chaque fois que l’individu du fichier d’arrivée n’existe pas dans le fichier de départ, les nouvelles cellules vont contenir « #N/A », alors que vous préférez sans doute une cellule vide. Ce n’est pas le cas dans notre exemple, puisque tous les individus de kish1copie.xls figurent dans individucopie.xls. Mais si vous avez fusionné dans l’autre sens (voir plus haut, au début de la partie « Fusionner les fichiers »), en prenant le plus petit fichier comme fichier de départ et le plus gros comme fichier d’arrivée, cela va forcément arriver. Pour résoudre ce problème, il suffit de modifier un peu la formule à copier dans toutes les cellules, en utilisant la fonction logique SI() et la fonction d’information « ESTNA() », ce qui donne :

=SI(ESTNA(RECHERCHEV(LC1;[individucopie.xls]individu!L1C1:L13741C36;L1C;FAUX)); ». »;RECHERCHEV(LC1;[individucopie.xls]individu!L1C1:L13741C36;L1C;FAUX))

Ce qui veut dire : si la valeur renvoyée par la fonction RECHERCHEV() est « #N/A », alors afficher un code de non-réponse (« . »), et sinon afficher la valeur renvoyée par la fonction.

4. Enfin, il ne reste plus qu’à recopier cette formule dans toutes les cellules des colonnes numérotées, qui sont destinées à accueillir les données du fichier de départ. Pour cela, recopiez d’abord la formule vers le bas dans toute la première colonne vide, puis recopiez ces formules vers la droite dans tout le reste de chaque ligne, avec les options correspondantes de la commande « Remplissage » du menu « Edition » (CTRL+B et CTRL+D).

Attention !

La copie de la formule dans toutes les cellules peut prendre entre plusieurs secondes et quelques minutes, en fonction de la taille de vos fichiers. Dans notre exemple, cela a pris environ 8 minutes ! Pour patienter, vous pouvez suivre la progression du pourcentage de « recalcul » des formules dans le coin inférieur gauche de la fenêtre d’Excel.

 

Terminer la fusion

 

Si tout a marché correctement, vous remarquerez quand même un certain nombre de différences par rapport aux valeurs d’origine dans le fichier de départ, que vous pouvez facilement corriger pour terminer la fusion :

1. La formule a copié les valeurs dans le fichier d’arrivée, mais elle n’a pas copié les formats des cellules, par exemple les formats de dates. Si dans le fichier de départ, les formats d’une colonne donnée sont identiques, vous pouvez alors facilement les copier dans le fichier d’arrivée. Pour cela, sélectionnez les colonnes du fichier de départ, cliquez sur « Copier » dans le menu « Edition », puis sélectionnez les colonnes du fichier d’arrivée, cliquez sur « Collage spécial » dans le menu « Edition » et cochez l’options « Formats ».

2. Une fois que le résultat obtenu vous semble satisfaisant, je vous conseille de ne pas conserver les formules dans le fichier d’arrivée (qui prennent beaucoup de mémoire et ralentissent considérablement la manipulation des fichiers), mais seulement leur résultat : pour cela, il vous suffit de sélectionner la plage des données fusionnées dans le fichier d’arrivée, de cliquer sur « Copier » dans le menu « Edition », puis sur « Collage spécial » avec l’option « Valeurs ».

3. Enfin, la première ligne du fichier d’arrivée, contenant les numéros des colonnes ajoutées, ne vous sert plus à rien : vous pouvez la supprimer.

Voilà, vous devriez à ce stade être parvenu à fusionner des données provenant de deux fichiers différents !

 

Et alors, les goûts musicaux dépendent-ils de la PCS ?

 

Eh bien, vous avez le moyen de répondre à cette question, maintenant, en réalisant un tableau à deux entrées croisant la variable TYPMUSI1, qui contient le premier genre musical préféré, et la variable PCS8, qui contient le niveau agrégé de la catégorie socioprofessionnelle. Pour mémoire, nous vous rappelons que la liste des modalités de chacune de ces deux variables, est disponible dans les fichiers varmod_individu.DBF et varmod_kish.DBF, que vous avez téléchargés si vous avez suivi les indications au début de l’article, dans la partie « Télécharger les fichiers ».

Vous ne savez pas comment faire un tableau croisé avec Excel ? Pas de problème, il vous suffit de lire ce précédent article de QUANTI :

On peut faire des tableaux croisés avec Excel !

Et voici le résultat :

On retrouve ici des résultats tout-à-fait conformes à ceux obtenus dans les enquêtes précédentes du Ministère de la Culture sur « les pratiques culturelles des Français » (Donnat, 1998, pp. 160-161) : le palmarès des genres musicaux est exactement le même, avec la variété française loin devant la variété internationale, et la musique classique en troisième position ; et comme dans l’enquête du Ministère de la Culture, le goût pour la musique classique est surreprésenté dans les catégories favorisées et parmi les retraités, et celui pour la variété française est surreprésenté parmi les agriculteurs.

 

Et s’il y a des individus dans le second fichier qui ne sont pas dans le premier ?

 

Nous n’avons pas traité le cas plus général dans lequel des individus peuvent être présents dans le premier fichier et pas dans le second, ET d’autres peuvent au contraire être présents dans le second fichier et pas dans le premier. Dans ce cas-là, une façon de résoudre le problème, pourra consister à créer un troisième fichier, qui sera le fichier de destination de toutes les informations, et dans la première colonne duquel figurera la liste complète des identifiants de tous les individus qui figurent dans le premier fichier OU dans le second fichier, puis de procéder comme ci-dessus, en deux fois, pour ajouter les données provenant des deux fichiers sources. Comment faire cette liste, me direz-vous ? Par exemple, en collant les uns à la suite des autres, dans une même colonne d’une nouvelle feuille de classeur, les identifiants provenant des deux fichiers, puis en utilisant la création de tableaux croisés dans Excel pour faire un « tri à plat » de cette colonne : les doublons disparaîtront ainsi, et vous aurez votre liste…

 

Et avec SAS, ce ne serait pas plus simple ?

 

Bien sûr, les quantitativistes aguerris m’objecteront qu’Excel n’est quand même pas vraiment fait pour ça, et qu’on aura beaucoup de difficultés à fusionner des fichiers de grande taille, comme vous avez d’ailleurs peut-être pu le vérifier vous-mêmes si vous avez suivi notre exemple : c’est long, et un peu pénible, non ?… Et donc on me fera remarquer que tout cela quand même beaucoup plus facile à faire avec un outil adapté. Et on aura raison ? Dans SAS, une fois les fichiers .dbf importés, tout ce qui précède prendra moins de 10 secondes en utilisant MERGE, et s’écrit, en gros :

proc sort data=kish1copie;
  by IDENT NO;
  run;

proc sort data=individucopie;
  by IDENT NO;
  run;

data individucopie;
  merge
kish1copie(in=in1)
individucopie(in=in2);
  by IDENT NO;
  if (in1 & in2);
run;

proc freq data=individucopie;
  table TYPMUSI1*PCS8 / nofreq nopercent norow;
  run;

Alors, pour finir, mon conseil est évidemment le suivant : tant que vos fichiers ne sont pas très gros, et que vos usages de la fusion de données sont occasionnels, vous pouvez utiliser Excel, et faire l’économie de l’apprentissage d’un outil plus puissant comme SAS. Mais autrement, il ne va pas falloir trop tarder à passer à la vitesse supérieure… Alors on vous parle bientôt de SAS bien sûr, mais aussi de R, qui a sur le précédent l’avantage d’être gratuit !

 

Liens utiles

La Commission nationale informatique et libertés (CNIL)

L’enquête « Participation culturelle et sportive 2003 » de l’INSEE

 

Références bibliographiques

Donnat Olivier (1998), Les pratiques culturelles des Français. Enquête 1997, Paris, Ministère de la Culture, Documentation française

Piketty Thomas (2001), Les hauts revenus en France au XXe siècle : inégalités et redistribution, 1901-1998, Paris, Grasset, 807 p.


Vous aimerez aussi...

13 réponses

  1. charlouff dit :

    Bonjour,

    tres jolie tuto et tres bien fait meme pour un neofit d excel comme moi.
    cependant malgré avoir relu plusieurs fois le tuto et avoir fait plusieurs fois le manips, je n arrive pas a adapter le tuto a mon probleme.
    je dispose de 2 fichier excel d inventaires de postes informatique.
    la seule colonne qu ils ont en commun est « nom de machine » et je n arrive pas a fusionner les 2 fichiers pour que les informations se complete.
    pourrais je avoir un petit coup de pouce ?
    je peux fournir les fichiers si besoin car il n y a rien de confidentiel
    merci par avance

  2. Virginie Gros dit :

    Merci !
    Malgré l’ancienneté de ce tuto et les mises à jour excel, j’ai pu l’adapter à ma situation et résoudre en moins d’une heure mon problème !
    Bravo !

  3. Elijoli dit :

    Wow!!!
    J’adore excel, merci pour ce fantastique tuto!

  4. Jacq dit :

    Excelent tutorial!! bravo

  5. F CHAUDRÉ dit :

    Bonjour, je rencontre un cas de figure que je n’arrive pas à résoudre. Mon fichier excell, regroupe onglets fiches techniques reprenant des produits et des qtés variables ; un onglet (récap achat) reprend toutes les références des FT et fait le cumul des besoins en utilisant la formule : =SI(ESTNA(RECHERCHEV….
    Mon Pb : si dans une FT apparaît plusieurs fois le même produit, dans mon cumul je n’aurai que la 1er ligne qui sera cptabilisée.
    A votre disposition pour vous donner plus de détails.
    Dans l’attente de vous lire,
    Cdt,
    FC

  6. Pascal Cyr dit :

    Bonjour,

    j’ai un proplème avec la formule. Lorsque j’écris L1C, Excel m’indique que c’est non valide. Pourquoi et comment je peux rectifier la situation.

    Merci

    • Pierre Mercklé dit :

      C’est probablement que votre Excel n’est pas encore paramétré pour accepter ce style de référence pour les cellules ? Comme indiqué dans le point 2 de la partie « Télécharger les fichiers », ouvrez votre Excel, et vérifiez que l’option « Style de référence L1C1 » est bien cochée dans l’onglet « Général » de la commande « Options » du menu « Outils »…

  7. Julien dit :

    Bonjour,

    Votre article vaut de l’or!

    Il est très très pédagogique et parfaitement clair. BRAVO

    Et surtout merci beaucoup

  8. Michel dit :

    Bonjour,

    merci pour votre article. En lieu et place de recherchev(), j’utilise maintenant le couple index equiv qui est plus puissant.
    Intérêt : la matrice de recherche n’a pas besoin d’être triée comme pour recherchev. Dans Recherchev, la colonne de recherche doit être à gauche de la colonne de résultat
    EQUIV Donne l’emplacement d’une valeur dans une colonne ou une ligne
    INDEX Renvoie une donnée d’une liste (en ligne ou colonne) à partir de sa position dans la liste
    index(matrice de recherche;equiv(valeur recherchée; matrice de recherche (une colonne); recherche de la valeur exacte(-1,0 ou 1)); numéro de la colonne contenant la valeur recherchée (de 1, à n, dépendant de la première matrice de recherche))
    cela nous sonne quelque chose comme :
    index($A$2:$K$33;equiv(B5;$D2:$D33);0);5)
    Renvoie la valeur de la 5ème colonne de la matrice de recherche $A$2:$K$33, correspondant à la valeur de la cellule B5 recherchée dans la matrice $D2:$D33 (correspondance exacte = 0)

    Bien cordialement

  9. Georges dit :

    Bonjour

    autre avantage du couple EQUIV et INDEX, c’est qu’il gère les ex-aequo (doublons) parmi les valeurs à récupérer et fonctionne même si la clef de jointure est dupliquée dans la table de destination.

    Sinon, je rectifie car il y a une petite erreur dans la formule de Michel. Il y a une parenthèse en trop.

    La bonne formule est la suivante : index($A$2:$K$33;equiv(B5;$D2:$D33;0);5)

    A utiliser sans modération

    Cordialement

    • Michel dit :

      Merci Georges pour la correction. Je crois que l’on peut aussi l’utiliser, sous une forme différente, pour justement repérer ces doublons.
      Cordialement

  10. Eric dit :

    Bonjour
    Je n’arrive pas à avoir accès à ce fichier. Lorsque je cliques sur ce lien, je me trouve à la page « Statistiques » du site et pas de fichier à télécharger.
    Par contre je pensais qu’après avoir cliqué sur ledit lien, une boîte de dialogue devrait apparaitre et nous proposer directement de télécharger.

    Que faire pour mon pb?
    Cdt.

    • Pierre Mercklé dit :

      L’INSEE a refait son site, et effectivement, je n’arrive plus à localiser les « fichiers détail », qui étaient pourtant bien utiles ! Si quelqu’un arrive à trouver où ils sont passés, ou à convaincre l’INSEE de les remettre en ligne…

Laisser un commentaire

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