Comment lier plusieurs feuilles de calcul ensemble ?

Comment lier plusieurs feuilles de calcul ensemble ?
Dernière mise à jour le 05/02/2024
Temps de lecture : 4 minutes

Comment construire une feuille de synthèse qui va lier les données de plusieurs feuilles de calcul

  1. Analyse de la construction du classeur

    Il est très important de bien analyser comment les données sont structurées avant de commencer le travail

  2. Comprendre chaque composante d'une référence

    Chaque partie d'une référence va avoir un rôle à jouer.

  3. Construire la fonction INDIRECT

    L'utilisation de la fonction INDIRECT est indispensable pour faire la liaison entre les différentes parties d'une référence.

Prenons comme exemple un classeur qui contient la feuille des temps pour 5 salariés (une feuille par salarié). Une première chose très importante à noter c'est que toutes les feuilles de calcul ont la même structure.

  • Les informations du lundi sont en ligne 4, le mardi en ligne 5, ....
  • Tous les jours sont en colonne A
  • Les heures travaillées sont toujours en colonne B
Feuille des temps sur plusieurs onglets

Maintenant, la question c'est de savoir comment nous allons pouvoir remplir la feuille de synthèse à partir des 5 autres feuilles. Comment lier la feuiller de synthèse à plusieurs feuilles de calcul ?

Comment remplir toutes les cellules de la feuille des temps

Étape 1 : Écrire la première liaison

Nous allons écrire la première liaison entre la feuille synthèse et la feuille du salarié Pierre pour la journée du Lundi

  1. Dans la cellule B3, saisissez le signe =
  2. Puis sélectionner la cellule qui contient le temps de Pierre (cellule B4)

=Pierre!B4

Liaison avec la feuille Pierre pour la journee du lundi

Étape 2 : Analysons la formule

La formule se décompose en 4 catégories

  1. Le nom de la feuille (Pierre)
  2. Le séparateur entre le nom de la feuille et la référence de la cellule (le point d'exclamation)
  3. La référence de la colonne (B)
  4. La référence de la ligne (4)

Étape 3 : Écrire les liaisons du lundi pour les autres salariés

Ensuite, nous allons refaire la même opération pour les autres salariés

Liaison pour tous les salaries pour la journee du lundi

Et en utilisant la technique pour afficher les formules dans les cellules, nous avons le résultat suivant

Formule des 5 liaisons

REMARQUE IMPORTANTE. Entre toutes ces formules, la seule chose qui change, c'est le nom de la feuille de calcul. Tout le reste est identique (!B4). Or le nom des employés est présent dans les cellules B2, C2, D2, E2 et F2. Donc l'idée c'est

  • Construire une référence vers les autres feuilles de calcul en utilisant les cellules B2, C2, ...

Étape 4 : Utiliser la fonction INDIRECT

La fonction INDIRECT permet d'interpréter une référence écrite sous forme de chaînes de caractères. Donc déjà, nous allons simplement écrire une des références en tant qu'argument de la fonction INDIRECT.

=INDIRECT("Pierre!B4")

Englober la reference dans la fonction INDIRECT

Étape 5 : Construire la référence avec une cellule

C'EST LA QUE TOUT SE JOUE ! Nous allons transformer la formule précédente pour utiliser les valeurs de la ligne 2 en tant que référence.

Donc, nous allons

  1. Supprimer le nom de feuille de calcul de la référence
  2. Puis, se positionner EN DEHORS de la chaîne de caractères
  3. Sélectionner la cellule B2
  4. Lier les 2 parties avec le symbole &

=INDIRECT(B2&"!B4")

Utiliser le nom dune cellule dans la reference

C'EST PARFAIT ! La fonction INDIRECT retourne bien le résultat de la cellule B2 de la feuille de calcul Pierre. Si l'écriture de la référence n'est pas parfaite, la fonction renverra une erreur.

Ensuite, nous allons recopier la formule. Quand vous recopiez la formule pour le lundi uniquement, nous voyons la référence de la colonne se décaler (c'est parfait). Mais ensuite, quand nous allons faire la recopie pour les autres jours de la semaine, il faut toujours rester sur la ligne 2. C'est pour ça que nous avons mis un dollar juste avant la référence de la ligne.

Recopie de la fonction INDIRECT

Étape 6 : Et pour les autres jours ?

A ce stade, seul le nom des feuilles de calcul est utilisé comme variable. Mais nous devons aussi transformer le numéro de ligne à retourner pour ne pas toujours rester sur la cellule B4.

Ce qu'il faut faire, c'est transformer le 4 par une formule qui va renvoyer 5, 6, 7, 8 pour les autres. Pour cela, nous allons utiliser la fonction LIGNE()

=INDIRECT(B$2&"!B"&LIGNE()+1)

Reference de la ligne variable

L'écriture LIGNE()+1 s'explique par la différence de ligne entre la feuille "Recap" et les feuilles des salariés

Le fichier de cet exemple se trouve ici.

Simplifier le travail avec Power Query

Une autre façon de procéder serait de regrouper toutes les feuilles de calcul dans une seule feuille. Ce travail peut se faire facilement avec Power Query comme c'est expliqué dans la vidéo. Ainsi, le travail d'analyse pourra se faire plus simple à construire avec un tableau croisé dynamique par exemple.

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Comment lier plusieurs feuilles de calcul ensemble ?

Reading time: 4 minutes
Dernière mise à jour le 05/02/2024

Comment construire une feuille de synthèse qui va lier les données de plusieurs feuilles de calcul

  1. Analyse de la construction du classeur

    Il est très important de bien analyser comment les données sont structurées avant de commencer le travail

  2. Comprendre chaque composante d'une référence

    Chaque partie d'une référence va avoir un rôle à jouer.

  3. Construire la fonction INDIRECT

    L'utilisation de la fonction INDIRECT est indispensable pour faire la liaison entre les différentes parties d'une référence.

Prenons comme exemple un classeur qui contient la feuille des temps pour 5 salariés (une feuille par salarié). Une première chose très importante à noter c'est que toutes les feuilles de calcul ont la même structure.

  • Les informations du lundi sont en ligne 4, le mardi en ligne 5, ....
  • Tous les jours sont en colonne A
  • Les heures travaillées sont toujours en colonne B
Feuille des temps sur plusieurs onglets

Maintenant, la question c'est de savoir comment nous allons pouvoir remplir la feuille de synthèse à partir des 5 autres feuilles. Comment lier la feuiller de synthèse à plusieurs feuilles de calcul ?

Comment remplir toutes les cellules de la feuille des temps

Étape 1 : Écrire la première liaison

Nous allons écrire la première liaison entre la feuille synthèse et la feuille du salarié Pierre pour la journée du Lundi

  1. Dans la cellule B3, saisissez le signe =
  2. Puis sélectionner la cellule qui contient le temps de Pierre (cellule B4)

=Pierre!B4

Liaison avec la feuille Pierre pour la journee du lundi

Étape 2 : Analysons la formule

La formule se décompose en 4 catégories

  1. Le nom de la feuille (Pierre)
  2. Le séparateur entre le nom de la feuille et la référence de la cellule (le point d'exclamation)
  3. La référence de la colonne (B)
  4. La référence de la ligne (4)

Étape 3 : Écrire les liaisons du lundi pour les autres salariés

Ensuite, nous allons refaire la même opération pour les autres salariés

Liaison pour tous les salaries pour la journee du lundi

Et en utilisant la technique pour afficher les formules dans les cellules, nous avons le résultat suivant

Formule des 5 liaisons

REMARQUE IMPORTANTE. Entre toutes ces formules, la seule chose qui change, c'est le nom de la feuille de calcul. Tout le reste est identique (!B4). Or le nom des employés est présent dans les cellules B2, C2, D2, E2 et F2. Donc l'idée c'est

  • Construire une référence vers les autres feuilles de calcul en utilisant les cellules B2, C2, ...

Étape 4 : Utiliser la fonction INDIRECT

La fonction INDIRECT permet d'interpréter une référence écrite sous forme de chaînes de caractères. Donc déjà, nous allons simplement écrire une des références en tant qu'argument de la fonction INDIRECT.

=INDIRECT("Pierre!B4")

Englober la reference dans la fonction INDIRECT

Étape 5 : Construire la référence avec une cellule

C'EST LA QUE TOUT SE JOUE ! Nous allons transformer la formule précédente pour utiliser les valeurs de la ligne 2 en tant que référence.

Donc, nous allons

  1. Supprimer le nom de feuille de calcul de la référence
  2. Puis, se positionner EN DEHORS de la chaîne de caractères
  3. Sélectionner la cellule B2
  4. Lier les 2 parties avec le symbole &

=INDIRECT(B2&"!B4")

Utiliser le nom dune cellule dans la reference

C'EST PARFAIT ! La fonction INDIRECT retourne bien le résultat de la cellule B2 de la feuille de calcul Pierre. Si l'écriture de la référence n'est pas parfaite, la fonction renverra une erreur.

Ensuite, nous allons recopier la formule. Quand vous recopiez la formule pour le lundi uniquement, nous voyons la référence de la colonne se décaler (c'est parfait). Mais ensuite, quand nous allons faire la recopie pour les autres jours de la semaine, il faut toujours rester sur la ligne 2. C'est pour ça que nous avons mis un dollar juste avant la référence de la ligne.

Recopie de la fonction INDIRECT

Étape 6 : Et pour les autres jours ?

A ce stade, seul le nom des feuilles de calcul est utilisé comme variable. Mais nous devons aussi transformer le numéro de ligne à retourner pour ne pas toujours rester sur la cellule B4.

Ce qu'il faut faire, c'est transformer le 4 par une formule qui va renvoyer 5, 6, 7, 8 pour les autres. Pour cela, nous allons utiliser la fonction LIGNE()

=INDIRECT(B$2&"!B"&LIGNE()+1)

Reference de la ligne variable

L'écriture LIGNE()+1 s'explique par la différence de ligne entre la feuille "Recap" et les feuilles des salariés

Le fichier de cet exemple se trouve ici.

Simplifier le travail avec Power Query

Une autre façon de procéder serait de regrouper toutes les feuilles de calcul dans une seule feuille. Ce travail peut se faire facilement avec Power Query comme c'est expliqué dans la vidéo. Ainsi, le travail d'analyse pourra se faire plus simple à construire avec un tableau croisé dynamique par exemple.

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.