Cas d'école - donner ses classes à un professeur

[jbdumaine]

Bonjour à tous

Je suis principal-adjoint et, dans un tableau qui recense mes équipes pédagogiques par niveau, j'aimerais mettre chaque professeur en face de ses classes.
Soit donc un tableau ayant la structure suivante :
Matière | 6e1 | 6e2 | 6e3 |
maths |M. X |MmeY|M. X|
français|M. A |M. A |MelleB|
HG |M. C| MmeD|MmeD|

Quelle formule pourrais-je utiliser pour avoir, en bas de ce tableau, les classes de chaque professeur sous la forme suivante :
M. X | 6e1 ; 6e3 |
etc.

J'espère avoir été assez clair. Je vous remercie à l'avance de vos réponses.
Bien à vous

JB DUMAINE

 

[job75]

Re : Cas d'école - donner ses classes à un professeur

Bonjour jbdumaine, bienvenue sur XLD,

Joli problème monsieur le Principal.

Par formule Excel ça paraît bien compliqué...

Alors cette fonction macro VBA dans Module1 (Alt+F11) :

Function CLASSE(plage As Range, prof As String, numero As Byte) As String
Dim i As Byte, j As Byte, n As Byte
prof = LCase(Application.Trim(prof)) 'fonctions MINUSCULE et SUPPRESPACE
With plage
For i = 1 To .Rows.Count
  For j = 1 To .Columns.Count
    If LCase(Application.Trim(.Cells(i, j))) = prof Then
      n = n + 1
      If n = numero Then CLASSE = .Cells(1, j): Exit Function
    End If
  Next
Next
End With
End Function

Elle est utilisée dans la formule en B8 :

=CLASSE($B$1:$D$4;$A8;COLONNES($B8:B8))

A tirer à droite et vers le bas.

Fichier joint.

A+

 

[david84]

Re : Cas d'école - donner ses classes à un professeur

Bonsoir, salut Job,
une solution par formule matricielle à valider par Ctrl, Maj et entrée (testée sur le fichier de Job après avoir enlevé les espaces présents après les noms) :

=SI(COLONNES($A:A)<=NB.SI(profs;$A8);INDIRECT(ADRESSE(LIGNE(classes);MOD(PETITE.VALEUR(SI(profs=$A8;LIGNE(profs)*10^5+COLONNE(profs));COLONNES($A:A));10^5)));"")

A+

 

[job75]

Re : Cas d'école - donner ses classes à un professeur

Bonjour jbdumaine, salut David

Je n'avais pas bien lu : vous voulez les classes dans une seule cellule.

Alors la fonction VBA :

Function CLASSE(plage As Range, prof As String) As String
Dim i As Byte, j As Byte
prof = LCase(Application.Trim(prof)) 'fonctions MINUSCULE et SUPPRESPACE
With plage
For i = 1 To .Rows.Count
  For j = 1 To .Columns.Count
    If LCase(Application.Trim(.Cells(i, j))) = prof Then _
      CLASSE = CLASSE & IIf(CLASSE = "", "", " ; ") & .Cells(1, j)
  Next
Next
End With
End Function

Fichier (2).

A+

 

[job75]

Re : Cas d'école - donner ses classes à un professeur

Re,

Et pour éviter de se fatiguer, cette macro lancée par un bouton :

Sub Liste()
Dim ref As Range, derlig&, dercol%, plage1 As Range, plage2 As Range
Dim d As Object, cel As Range, txt$
Set ref = [A1] 'à adapter, 1ère cellule du tableau
derlig = ref.End(xlDown).Row
dercol = ref.End(xlToRight).Column
Set plage1 = ref.Offset(, 1).Resize(derlig - ref.Row + 1, dercol - ref.Column)
Set plage2 = plage1.Offset(1)
'---liste des professeurs---
Set d = CreateObject("Scripting.Dictionary") 'pour liste sans doublons
For Each cel In plage2
  txt = Application.Trim(cel)
  If txt <> "" Then d(txt) = txt
Next
Rows(derlig + 3 & ":65536").ClearContents 'effacement préalable
If d.Count = 0 Then Exit Sub
With Cells(derlig + 3, ref.Column).Resize(d.Count) '3 lignes après la dernière
  .Value = Application.Transpose(d.Keys)
  .Sort ref, xlAscending, Header:=xlNo 'tri alphabétique
'---liste des classes avec fonction CLASSE---
  With .Offset(, 1)
    .FormulaR1C1 = "=CLASSE(" & plage1.Address(, , xlR1C1) & "," & "RC[-1])"
    .Value = .Value 'suppression des formules (facultatif)
  End With
End With
End Sub

Elle crée une liste des professeurs triée sans doublons et entre la formule.

Pour l'adapter à n'importe quel tableau il sufit de modifier cette ligne :

Set ref = [A1] 'à adapter, 1ère cellule du tableau

Fichier (3).

A+

 

[JONEY76]

Re : Cas d'école - donner ses classes à un professeur

On peut aussi créer une listbox avec paramètre de recherche, cela affichera toutes les lignes correspondant au nom

 

[job75]

Re : Cas d'école - donner ses classes à un professeur

Bonjour JONEY76,

C'est bien d'avoir des idées mais il faut en donner un exemple.

Perso je vois mal comment une ListBox répondrait au problème posé.

Je reviens sur ma version (3).

Pour la liste des professeurs, les doublons ne sont pas supprimés si la casse n'est pas respectée.

Alors utiliser cette version (4) avec Application.Proper, c'est la fonction NOMPROPRE :

txt = Application.Proper(Application.Trim(cel))

A+

 

[jbdumaine]

Re : Cas d'école - donner ses classes à un professeur

Chers Tout le monde
Merci beaucoup de vos contributions. La macro associée au bouton, j'aime beaucoup.
Ça donne envie de bosser pour arriver à maîtriser l'outil...
Je vous salue bien tous et vous remercie bien encore une fois, vous les samaritains du Net.
Au plaisir

Bien à vous et très cordialement

JB DUMAINE