Manque formule sur fichier

Le 31/08/2017 à 00:28

Bonjour

J'ai un fichier que je n'arrive pas à terminer-impossible de trouver la formule adaptée.

Explication : je dois effectuer un planning de passage pour des élèves avec des professeurs.J'ai 2 feuilles de calculs.Dans une les professeurs avec les élèves et les horaires de passage et dans l'autre les élèves avec les horaires.

Je souhaiterai transposer les nom des professeurs dans la feuille avec les élèves ( afin qu'ils sachent avec quel professeur il doivent passer , le nom s'affcherai dans la ligne de l'élève ) sans à avoir à tout retaper.

Je pense qu'il faut utiliser les formules EQUIV et DECALER voir ESTERREUR) mais je n'y arrive pas.

Est ce que quelqu'un peut m'aider ? et m'apporter des explications aux formules utilisées ?

D'avance merci pour votre aide !!!!!!

Je vous joins mon fichier ,cela sera plus clair....

PS : je dois le rendre dans 48 h !!!!

12planning-de-passage.xlsx (10.97 Ko)
Le 31/08/2017 à 00:45

Bonjour,

tu devrais éditer ton titre.

URGENT et écrire en majuscule (=crier) font plus fuir qu'attirer les bonnes volontés.

Il n'y a rien d'urgent, que des choses en retard...

eric

Le 31/08/2017 à 01:01

Bonjour,

Note l'avis d'Eric qui me paraît tout à fait pertinent ! 

=SI(SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*(LIGNE('planning professeur'!$B$2:$G$9)=COLONNE()-1));INDEX('planning professeur'!$B$1:$G$1;SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*COLONNE('planning professeur'!$B$2:$G$9))-1);"")

Un peu longue à mon goût ! mais à cette heure-ci, je ne chercherai pas à faire mieux !

Cordialement.

8kaissa-planning-de-passage.xlsx (12.70 Ko)
Le 31/08/2017 à 01:09

Bonjour,

à tester,

Sub Transfert_planning()
Set sh1 = Sheets("planning professeur")
Set sh2 = Sheets("planning eleve")
sh2.Range("C2:J24").ClearContents
For Each c In sh1.Range("B2:G9")
    If Not IsError(Application.Match(c, sh2.Range("B:B"), 0)) Then
     rw = Application.Match(c, sh2.Range("B:B"), 0)
     prof = sh1.Cells(1, c.Column)
     heure = Application.Match(sh1.Cells(c.Row, 1), sh2.Range("1:1"), 0)
     sh2.Cells(rw, heure) = prof
    End If
Next
End Sub
7kaissa-planning-de-passage.xlsm (18.51 Ko)
Le 31/08/2017 à 02:12

Bonjour à tous,

Une autre proposition à étudier.

Les explications viendront, si nécessaires.

Cdlt.

15planning-de-passage.xlsm (35.32 Ko)
Le 31/08/2017 à 02:36

Bonjour le fil

Bravo les anciens vous donnez le "très" bon exemple

Titre en MAJUSCULE et URGENT et on lui donne la réponse, c'est parfait... pour lui

Le 31/08/2017 à 10:15
Jean-Eric a écrit :

Bonjour à tous,

Une autre proposition à étudier.

Les explications viendront, si nécessaires.

Cdlt.

bonjour,

merci pour la réponse .je n'avais pas pensé au tableau croisé dynamique.

par contre serait-il possible d'avoir des explications ? notamment pour la colonne "num" et la formule qui s'y rattache.

D'avance merci !!!

BrunoM45 a écrit :

Bonjour le fil

Bravo les anciens vous donnez le "très" bon exemple

Titre en MAJUSCULE et URGENT et on lui donne la réponse, c'est parfait... pour lui

bonjour,

je débute sur ce site ,il est clair que je n'aurai pas du mettre urgent mais après 2 h de recherche et aussi tard ..."on est un peu dépassé

Désolé d'avoir "offusqué" certaines personnes

bien cordialement

bonjour,

merci pour la réponse ! c'est très gentil d'avoir répondu aussi vite. Je n'ai para contre pas saisie la formule.serait-il possible d'avoir quelques explications.

D'avance merci.

bien cordialement

MFerrand a écrit :

Bonjour,

Note l'avis d'Eric qui me paraît tout à fait pertinent ! 

=SI(SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*(LIGNE('planning professeur'!$B$2:$G$9)=COLONNE()-1));INDEX('planning professeur'!$B$1:$G$1;SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*COLONNE('planning professeur'!$B$2:$G$9))-1);"")

Un peu longue à mon goût ! mais à cette heure-ci, je ne chercherai pas à faire mieux !

Cordialement.

Le 31/08/2017 à 10:31

bonjour,

merci pour cette réponse. je constate que certains on un sacré niveau .

Il s'agit je pense de VBA et la je ne maitrise pas du tout le sujet.

encore merci !!!

bien cordialement

sabV a écrit :

Bonjour,

à tester,

Sub Transfert_planning()
Set sh1 = Sheets("planning professeur")
Set sh2 = Sheets("planning eleve")
sh2.Range("C2:J24").ClearContents
For Each c In sh1.Range("B2:G9")
    If Not IsError(Application.Match(c, sh2.Range("B:B"), 0)) Then
     rw = Application.Match(c, sh2.Range("B:B"), 0)
     prof = sh1.Cells(1, c.Column)
     heure = Application.Match(sh1.Cells(c.Row, 1), sh2.Range("1:1"), 0)
     sh2.Cells(rw, heure) = prof
    End If
Next
End Sub
Le 31/08/2017 à 11:09

Bonjour,

Quelques informations dans le fichier joint.

J'ai normalisé tes données (VBA) pour créer le TCD.

Chaque professeur à un numéro distinct qui le caractérise (num).

Le TCD synthétise avec la somme des num (uniques).

J'applique ensuite une MFC au champ de valeurs du TCD. Soit 1=CAMARA, 2 = DUPOIS, ..., 6=VIAL.

A te relire.

Cdlt.

10planning-de-passage.xlsm (158.89 Ko)
Le 31/08/2017 à 11:37

Bonjour,

Une version un peu plus courte pour l'occasion ! 

=SIERREUR(INDEX('planning professeur'!$B$1:$G$1;SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*(LIGNE('planning professeur'!$B$2:$G$9)=COLONNE()-1)*COLONNE('planning professeur'!$B$2:$G$9))-1);"")

Note que les horaires occupent les lignes 2 à 9 sur le planning prof et les colonnes 3 à 10 (C à J) sur le planning élèves. Les lignes du premier correspondent aux colonnes du second -1...

Par ailleurs la formule utilise INDEX sur le vecteur B1:G1 du planning prof, listant les noms de profs associés à chaque élève du tableau. S'agissant d'un vecteur en ligne, seul l'index colonne est nécessaire : le vecteur commençant colonne B, l'index correspondra au numéro de colonne dans laquelle figure l'élève -1.

La formule a donc la structure : =INDEX(PlageLigneNomsProfs;indexColonneElève-1)

L'indexColonneElève va être renvoyé par SOMMEPROD effectuant une somme conditionnelle du produit de matrices. Les matrices sont de taille égale et correspondent à la plage B2:G9 du planning élève. On y cherche :

  • Le nom de l'élève figurant en colonne B de la ligne où on place la formule du planning élève : une seule cellule reverra VRAI (chaque élève ne figure qu'une fois), soit 1 dans le calcul, les autres FAUX (soit 0)
  • La correspondance horaire, c'est à dire que la ligne de la matrice corresponde à la colonne (décalée de -1 comme on l'a vu) où l'on place la formule : seule la ligne où se trouve l'élève renverra VRAI, et seule la cellule de cette ligne où se trouve son nom cumulera 2 VRAI, soit 1*1 = 1 (pour les autres : 0)
  • En multipliant cette matrice composée de 0 et un seul 1 par le numéro de colonne de chaque cellule, on obtient pour la cellule qui renvoyait 1 son numéro de colonne, que SOMMEPROD renvoie, un seul nombre pouvant être différent de 0 (ou aucun, dans ce cas SOMMEPROD renvoie 0).

On diminue donc le résultat renvoyé par SOMMEPROD de 1 pour ajuster l'index de colonne...

Mais si l'on n'a pas le nom de l'élève cherché par la formule selon son emplacement dans le planning élèves, la formule renverra une erreur #VALEUR! [=INDEX(PlageLigneNomsProfs;-1) ne peut pas fournir de résultat...]

On élimine l'affichage de cette valeur d'erreur en plaçant la formule précédente dans une fonction SIERREUR, permettant de ne rien afficher en cas d'erreur).

je débute sur ce site ,il est clair que je n'aurai pas du mettre urgent mais après 2 h de recherche et aussi tard ..."on est un peu dépassé

Ainsi que l'a suggéré Eric, tu peux éditer ton titre et le modifier ! Mieux que des excuses en laissant les choses en l'état !

J'ai pour ma part pris en compte qu'il s'agissait de ton premier post sur le Forum... ce qui n'arrive qu'une fois !

Cordialement.

Le 31/08/2017 à 15:17
Jean-Eric a écrit :

Bonjour,

Quelques informations dans le fichier joint.

J'ai normalisé tes données (VBA) pour créer le TCD.

Chaque professeur à un numéro distinct qui le caractérise (num).

Le TCD synthétise avec la somme des num (uniques).

J'applique ensuite une MFC au champ de valeurs du TCD. Soit 1=CAMARA, 2 = DUPOIS, ..., 6=VIAL.

A te relire.

Cdlt.

Bonjour

merci pour les explications . par contre j'ai 2 questions : que signifie TCD et MFC ? c'est surement des questions bêtes... mais bon je les posent tout de même.

Bien cordialement

Le 31/08/2017 à 17:08

Bonjour Kaissa

KAISSA a écrit :

Bonjour

merci pour les explications . par contre j'ai 2 questions : que signifie TCD et MFC ? c'est surement des questions bêtes... mais bon je les posent tout de même.Bien cordialement

TCD = Tableau Croisé Dynamique

MFC = Mise en Forme Conditionnelle

Voili

Le 31/08/2017 à 22:19

bonsoir

merci pour les explications.j'avoue que c'est complexe mais je vais tout reprendre pas à pas.

Tu as un super niveau sur excel! comme beaucoup de personnes sur le site...

encore merci pour ton aide .

si cela n'est pas clair...il est possible que je te pose des questions , si cela ne te déranges pas .

cordialement

MFerrand a écrit :

Bonjour,

Une version un peu plus courte pour l'occasion ! 

=SIERREUR(INDEX('planning professeur'!$B$1:$G$1;SOMMEPROD(('planning professeur'!$B$2:$G$9=$B2)*(LIGNE('planning professeur'!$B$2:$G$9)=COLONNE()-1)*COLONNE('planning professeur'!$B$2:$G$9))-1);"")

Note que les horaires occupent les lignes 2 à 9 sur le planning prof et les colonnes 3 à 10 (C à J) sur le planning élèves. Les lignes du premier correspondent aux colonnes du second -1...

Par ailleurs la formule utilise INDEX sur le vecteur B1:G1 du planning prof, listant les noms de profs associés à chaque élève du tableau. S'agissant d'un vecteur en ligne, seul l'index colonne est nécessaire : le vecteur commençant colonne B, l'index correspondra au numéro de colonne dans laquelle figure l'élève -1.

La formule a donc la structure : =INDEX(PlageLigneNomsProfs;indexColonneElève-1)

L'indexColonneElève va être renvoyé par SOMMEPROD effectuant une somme conditionnelle du produit de matrices. Les matrices sont de taille égale et correspondent à la plage B2:G9 du planning élève. On y cherche :

  • Le nom de l'élève figurant en colonne B de la ligne où on place la formule du planning élève : une seule cellule reverra VRAI (chaque élève ne figure qu'une fois), soit 1 dans le calcul, les autres FAUX (soit 0)
  • La correspondance horaire, c'est à dire que la ligne de la matrice corresponde à la colonne (décalée de -1 comme on l'a vu) où l'on place la formule : seule la ligne où se trouve l'élève renverra VRAI, et seule la cellule de cette ligne où se trouve son nom cumulera 2 VRAI, soit 1*1 = 1 (pour les autres : 0)
  • En multipliant cette matrice composée de 0 et un seul 1 par le numéro de colonne de chaque cellule, on obtient pour la cellule qui renvoyait 1 son numéro de colonne, que SOMMEPROD renvoie, un seul nombre pouvant être différent de 0 (ou aucun, dans ce cas SOMMEPROD renvoie 0).

On diminue donc le résultat renvoyé par SOMMEPROD de 1 pour ajuster l'index de colonne...

Mais si l'on n'a pas le nom de l'élève cherché par la formule selon son emplacement dans le planning élèves, la formule renverra une erreur #VALEUR! [=INDEX(PlageLigneNomsProfs;-1) ne peut pas fournir de résultat...]

On élimine l'affichage de cette valeur d'erreur en plaçant la formule précédente dans une fonction SIERREUR, permettant de ne rien afficher en cas d'erreur).

je débute sur ce site ,il est clair que je n'aurai pas du mettre urgent mais après 2 h de recherche et aussi tard ..."on est un peu dépassé

Ainsi que l'a suggéré Eric, tu peux éditer ton titre et le modifier ! Mieux que des excuses en laissant les choses en l'état !

J'ai pour ma part pris en compte qu'il s'agissait de ton premier post sur le Forum... ce qui n'arrive qu'une fois !

Cordialement.

Rechercher des sujets similaires à "manque formule fichier"