Formule conditionnelle SI est > ou <>

[excelnoober]

Bonjour
je reviens avec ma question un peu plus aboutie enfin j espere
j ai en colonne M la formule suivante
=SI(ESTNUM(CHERCHE("5E2";A82));C82;"")
idem en colonne N et en colonne O
au dessus de chaque colonne j ai un effectif chiffre en M1 en N1 en O1
ex de ma feuille pour M effectif de 10
....................................N ...................4
....................................O...................21
je souhaiterais pour eviter les doublons (sachant que je ne peux les effacer) que la formule citée plus haut tienne compte de cet effectif.
Dans ma feuille exemple ligne 385 "MARNISSI" apparait dans les 3 colonnes puisqu il repond aux 3 critères.

QUESTION :
est il possible par un jeu de formule d affecter "MARNISSI" après comparaison des effectif 10 ; 4 ; 21 sur la colonne ayant l 'effectif le moins élevé ?

 

[JBARBE]

Re,
Nécessaire Fait avec le bouton de tri situé a cote de ma variable d ajustement "S3" !
Bonne soirée !

 

[excelnoober]

Merci JBARBE
C est parfait.
Merci aussi a job75
question pour j Barbe
si tu m avais donné le code texte j aurais fais l erreur de le placer soit sur worksheet sois dans un module genere a partir de ma feuille "intervenant ap"
j ai pu constater que tu avais cree un module 12.
mais j ai pas trouvé le declencheur? j ai bie essayer de le chercher sur la macro de mon bouton de tri mais en vain. Peux tu m expliquer s il te plait?

 

[JBARBE]

Re,
Le module 12 existait déjà sans macro, je l'ai donc utilisé !
Dés l'instant où le titre de la macro ( ici doublons ) est mis dans une autre macro cette dernière déclenche la macro insérée !
Bonne nuit !

 

[excelnoober]

MERCI pour ta reponse eclairee
bonne nuit

 

[job75]

Bonjour excelnoober, JBARBE,

Le problème n'est pas du tout résolu et je vous mets dans le même sac tous les deux...

Ce que vous ne comprenez pas c'est que pour remplir les colonnes M N O (en éliminant les doublons) ma macro utilise uniquement les noms en colonnes C D E (la colonne B ne sert à rien).

Vous, vous voulez maintenir les formules existantes en colonne M N O et ça c'est totalement incohérent car vous avez entré ces formules manuellement sur des critères qui n'apparaissent nulle part.

Par ailleurs dans ces formules le ESTNUM(CHERCHE(x;y)) est inutile puisque toujours VRAI !!!

Si vous voulez qu'un élève n'apparaisse pas en colonnes C D E il faut introduire un critère dans les formules, ce critère pouvant se trouver par exemple dans la feuille de classe de l'élève.

Bonne journée.

 

[JBARBE]

Bonjour excelnoober, JBARBE,

Le problème n'est pas du tout résolu et je vous mets dans le même sac tous les deux...
Vous, vous voulez maintenir les formules existantes en colonne M N O et ça c'est totalement incohérent car vous avez entré ces formules manuellement sur des critères qui n'apparaissent nulle part.
Bonne journée.

Bonjour Job,
Mon post #2 précise cette particularité inutile !
Néanmoins, j'ai souhaité accomplir les exigences de excelnoober sans me poser de question !
Je suis nullement responsable comme tu le souligne des exigences de excelnoober !
Ce n'est pas la 1ére fois que l'on voit des choses bizarres sur ce forum ! Pas toi Job !
Bonne journée !

 

[job75]

Re,

Pour terminer, si l'on accepte l'idée de remplir manuellement les colonnes (M N O et les autres) :

- des formules sont inutiles comme l'a bien dit JBARBE au post #2

- à moins d'être handicapé pourquoi entrerait-on des doublons en colonnes M N O ?

A+

 

[excelnoober]

Bonjour JOB75, JBARBE, et le forum
effectivement tu as raison sur le critere mais ne sachant pas comment le mettre en place j ai prefere tout saisir a la main et la c est tres fastidieux.
le critere en question c est la mise en relation des emplois du temps eleves et intervenants.
j ai bien pensé a une recherche V sur une matrice située en dessous du tableau mais il y avait plusieurs entrées, et jusque la je n'ai fait que des tableaux a 2 entrees.

pour ta recherche basée sur CDE le fait que les eleves aient des emploi du temps du temps differents selon leur classe cela pose un probleme. D ou ma saisie manuelle sur la plage (G:R) afin de mettre en place les disponiblites des eleves et d ou mon besoin de travailler sur les colonnes M N et O.
j ai mis en piece jointe le tableau qui reprend les emploi du temps du temps eleves intervenant ce qui pourra peut etre plus t eclairer que es propos.

par la suite en colonne A figurent les classes, nous travaillons par periode
1 de septembre a novembre
2 de novembre a janvier
3 de fevrier a avril
4 de mai a juin

pour a la periode 1 et 2 seules doivent apparaitres les classes de 6e 5e
pour a periode 3 et 4 les classes de 4e et de 3e

actuellement j ai fait des macro automatiques qui filtrent la colonne A et la colonne de l'intervenant
mais j avoue que c est tres spartiate.
si vous aviez une meilleure solution? j ai eu l occasion de travailler en juin sur une feuille qu un membre du forum m avait realisé " un user form" reprenant les données et permettant d effectuer des recherches ciblees. Mais la c est une autre question.

je voulais juste que vous ayez toutes les informations pour comprendre le raisonnement de ce tableau

 

[excelnoober]

bonsoir
Job75, JCBARBE, le forum
le fichier n est pas disponible ,je ne peux donc voir vos modifcations. Pourriez le renvoyer svp?

 

[job75]

Bonsoir excelnoober, JBARBE, le forum,

Maintenant que l'on connaît les classes disponibles on peut travailler sérieusement :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, i&, r As Range, mini&
If Intersect(Target, [S5]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'si aucune SpecialCell
[S5] = Val(CStr([S5])) 'au cas où...
Set P = [A6:R600] 'plage adaptable
P.Columns(7).Resize(, 12) = "" 'RAZ
For i = 1 To P.Rows.Count
  If P(i, 2) <> "" Then
    Range("G2:R2") = "=SEARCH(""" & P(i, 1) & """,G1)" 'analyse des disponibilités
    '---M.Armant (on commence par lui car c'est le moins disponible)---
    If P(i, 5) <> "" Then
      Set r = Nothing
      Set r = Range("G2,N2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 5): Exit For
        Next r
      End If
    End If
  End If
    '---M.Yuste---
    If P(i, 3) <> "" Then
      If P(i, 14) = P(i, 3) Then Range("M2") = "" 'neutralise la colonne M si doublon
      Set r = Nothing
      Set r = Range("H2:M2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 3): Exit For
        Next r
      End If
    End If
    '---Mme Querat---
    If P(i, 4) <> "" Then
      If P(i, 13) = P(i, 4) Or P(i, 14) = P(i, 4) Then Range("O2") = "" 'neutralise la colonne O si doublon
      Set r = Nothing
      Set r = Range("O2:R2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 4): Exit For
        Next r
      End If
    End If
Next i
'[G2:R2] = "" 'facultatif, mettre en commentaire pour voir les formules
Application.EnableEvents = True 'réactive les évènements
End Sub

La macro s'exécute automatiquement quand on valide ou modifie la cellule S5.

Bien noter que j'ai ajouté 2 lignes auxiliaires en haut de la feuille.

J'ai aussi amélioré la macro du bouton dans Module2 :

Sub FilterColonneB()
Dim i As Variant, mes$
[A5:R600].AutoFilter Field:=2, Criteria1:="<>"
i = Application.Match("X", [B:B], 0)
If IsNumeric(i) Then ActiveWindow.ScrollRow = i
For i = 6 To 600 'limites adaptables
  If Cells(i, 2) <> "" Then
    If Cells(i, 3) <> "" Then If Application.CountIf(Cells(i, 8).Resize(, 6), Cells(i, 3)) = 0 Then mes = mes & vbLf & Cells(i, 3)
    If Cells(i, 4) <> "" Then If Application.CountIf(Cells(i, 15).Resize(, 4), Cells(i, 4)) = 0 Then mes = mes & vbLf & Cells(i, 4)
    If Cells(i, 5) <> "" Then If Cells(i, 7) <> Cells(i, 5) And Cells(i, 14) <> Cells(i, 5) Then mes = mes & vbLf & Cells(i, 5)
  End If
Next
If mes = "" Then Exit Sub
With UserForm2
  .TextBox1 = "ATTENTION !!! Les élèves suivants n'ont pas d'intervenant à cause de leur EDT :" & mes
  .Show 0 'non modal
  .TextBox1.SetFocus
  .TextBox1.SelStart = 0: .TextBox1.SelLength = 0
End With
End Sub

Fichier joint.

A+

 

[excelnoober]

RE bonsoir job75
je pense qu il y a un soucis quelque part, n ayant pas génére la macro je ne peux te dire ou mais en saisissant manuellement les affectations j avais 23 eleves repartis sur MN e O
je n avais qu un seul eleve sans intervenant.
D autre part le code qui est utiliser pour trouver les eleves sans intervenant annonce des eleves qui sont pourtant face a un intervnant donc il doit aussi y avoir un soucis. Alors qu avant je navais qu "ADRAOUI" qui effectivememtn n avait pas d intervenant la meme les eleves placés apparaissent
je suis en train de chercher les erreurs

jene comprends pas les intitules de G2 a R2
=CHERCHE("6E6";N1) pourquoi il cherche seulement les 6e6?

 

[job75]

Re,

Validez S5 et vérifiez : toutes les affectations sont bonnes et cohérentes avec les disponibilités en ligne 1.

Bien sûr les formules qui restent en G2:R2 sont celles utilisées pour la dernière valeur en colonne A.

A+

 

[excelnoober]

RE, ok j essai de comprendre ce que vous avez realisé... cela va me prendre quelques temps.
Merci en tout cas.
je reviendrais avec des questions certainement demain
mai smerci

 

[job75]

Bonjour excelnoober, le forum,

J'ai amélioré la macro du bouton en ajoutant les noms des professeurs concernés :

Sub Filtrer_ColonneB()
Dim i As Variant, n%, x$, y$, z$, mes$
[A5:R600].AutoFilter
[A5:R600].AutoFilter Field:=2, Criteria1:="<>"
i = Application.Match("X", [B:B], 0)
If IsNumeric(i) Then ActiveWindow.ScrollRow = i
n = [MAX(LEN(C6:E600))] + 1 'nombre maximum de caractères
For i = 6 To 600 'limites adaptables
  If Cells(i, 2) <> "" Then
    x = Cells(i, 3): y = Cells(i, 4): z = Cells(i, 5)
    If x <> "" Then If Application.CountIf(Cells(i, 8).Resize(, 6), x) = 0 Then mes = mes & vbLf & x & String(n - Len(x), " ") & "(Mr Yuste)"
    If y <> "" Then If Application.CountIf(Cells(i, 15).Resize(, 4), y) = 0 Then mes = mes & vbLf & y & String(n - Len(y), " ") & "(Mme Querat)"
    If z <> "" Then If Cells(i, 7) <> z And Cells(i, 14) <> z Then mes = mes & vbLf & z & String(n - Len(z), " ") & "(Mr Armant)"
  End If
Next
If mes = "" Then Exit Sub
With UserForm2
  .TextBox1 = "ATTENTION !!! Les élèves suivants n'ont pas d'intervenant à cause de leur EDT :" & mes
  .Show 0 'non modal
  .TextBox1.SetFocus
  .TextBox1.SelStart = 0
  .CMD_OK.SetFocus
End With
End Sub

Pour obtenir un bon cadrage la TextBox est dotée d'une police à chasse fixe : Consolas.

Fichier (2), j'y ai fait aussi un peu de ménage.

Bonne journée.

 

[job75]

Re,

Dans la Worksheet_Change du fichier (2) j'ai ajouté :

If FilterMode Then ShowAllData 'si la feuille est filtrée

C'est indispensable pour que la RAZ s'effectue correctement.

A+