XL 2016 Recherche de formule

[karakoman1]

Bonjour le forum,

J'ai un tableau qui contient 30 journées de sport, avec pour chaque journée 4 participants pour chaque date.
Je voudrais dans un autre tableau trouver une formule qui me permettrais de voir en un coup d'œil le nombre de fois que chaque participant rencontre un autre.

Je joint un petit fichier exemple pour ne pas trop embrouiller les esprits 😉

Merci à qui pourra me dépanner.

Excellente journée à tous

 

[Victor21]

Bonjour.

Essayez en N23, à recopier dans tout le tableau :

=NB.SI.ENS($C$3:$C$32;$M23;$E$3:$E$32;N$22)+NB.SI.ENS($C$3:$C$32;$M23;$G$3:$G$32;N$22)+NB.SI.ENS($C$3:$C$32;$M23;$I$3:$I$32;N$22)
+NB.SI.ENS($E$3:$E$32;$M23;$G$3:$G$32;N$22)+NB.SI.ENS($E$3:$E$32;$M23;$I$3:$I$32;N$22)
+NB.SI.ENS($G$3:$G$32;$M23;$I$3:$I$32;N$22)

Tournois inclus (Attention aux noms cachés par un format perso).

 

[R@chid]

Bonjour @ tous,
Salut mon cher Victor21,
en N23 :

=SI($M23=N$22;"";SOMMEPROD(N(NB.SI(DECALER($C$2:$I$2;LIGNE(INDIRECT("1:"&NB($B$3:$B$32))););$M23)*NB.SI(DECALER($C$2:$I$2;LIGNE(INDIRECT("1:"&NB($B$3:$B$32))););N$22)=1)))

@ tirer ver le bas et vers la droite

Attention à ce qu'il a signalé Victor21.


Cordialement

 

[R@chid]

Re,
plus jolie comme ça :

=SI(LIGNES($23:23)<=COLONNES($M:M);"";SOMMEPROD(N(NB.SI(DECALER($C$2:$I$2;LIGNE(INDIRECT("1:"&NB($B$3:$B$32))););$M23)*NB.SI(DECALER($C$2:$I$2;LIGNE(INDIRECT("1:"&NB($B$3:$B$32))););N$22)=1)))

Cordialement

 

[job75]

Bonjour karakoman1, Patrick, R@chid,

Cette fonction VBA permet de ne pas trop se casser la tête :

Function ComptePaire(x1$, x2$, plage As Range)
If x1 = x2 Then Exit Function
Dim ncol%, i&, j%, test1 As Boolean, test2 As Boolean
ncol = plage.Columns.Count
For i = 1 To plage.Rows.Count
    test1 = False: test2 = False
    For j = 1 To ncol Step 2
        If plage(i, j).Text = x1 Then test1 = True
        If plage(i, j).Text = x2 Then test2 = True
    Next j
    If test1 And test2 Then ComptePaire = ComptePaire + 1
Next i
End Function

Fichier joint.

PS : pas bien génial le format ;;;"Tournoi" en C13:I13 et C21:I21.

Il oblige à utiliser la propriété .Text sur la plage.

A+

 

[karakoman1]

Bonjour le forum, Victor21, R@chid, Job75

Merci pour toutes vos formules, ou macros qui comme d'habitude fonctionnent toujours très bien 🙂

J'aurais aimé vous demander si il était possible de mettre en couleur, les cellules du tableau avec les joueurs lorsque je me trouve dans une intersection du tableau ou se trouvent vos formules afin de les visualiser plus facilement.?

Pour ce qui est des noms cachés par le format perso "Tournoi", j'avoue que ca m'embête un peu, mais… il fait partie d'une macro du programme que je me fabrique depuis assez longtemps et il est "je pense"trop tard pour s'en passer sans risque de ne plus pouvoir faire le faire fonctionner.

j'ai déjà quelques formules pour d'autres calculs concernant ce tableau qui arrivent à les "ignorer".
Ce serait peut être plus simple d'arriver à modifier celles que vous m'avez envoyées ou la macro pour Job75 que de modifier tout le reste dans mon programme?

Autrement, pour changer le format perso dans les macros, si quelqu'un se sent l'âme d'y jeter un œil pour pouvoir s'en passer, je veux bien le poster en complet, mais ca risque d être compliquer pour s'expliquer 😛

Bonne fin de journée à tous

 

[job75]

Re,

Solution par formule en N23 du fichier joint :

=SOMMEPROD(($M23<>N$22)*(ESTNUM(CHERCHE($M23&"*"&N$22;MConcat))+ESTNUM(CHERCHE(N$22&"*"&$M23;MConcat))))

Bien sûr j'ai viré le format personnalisé ;;;"Tournoi"...

A+

 

[job75]

Re,

J'aurais aimé vous demander si il était possible de mettre en couleur, les cellules du tableau avec les joueurs lorsque je me trouve dans une intersection du tableau ou se trouvent vos formules afin de les visualiser plus facilement.?

On peut utiliser cette macro pour mettre en couleur jaune :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, x$, y$, i As Variant, j As Variant
Set r = [N23:W32] 'plage à adapter
If Intersect(ActiveCell, r) Is Nothing Then Exit Sub
x = ActiveCell.Offset(22 - ActiveCell.Row)
y = ActiveCell.Offset(, 13 - ActiveCell.Column)
r.Interior.ColorIndex = xlNone 'RAZ
If x <> y Then ActiveCell.Interior.ColorIndex = 6 'jaune
Set r = [C3:I32] 'plage à adapter
For Each r In r.Rows
    If r.NumberFormat <> ",,,""Tournoi""" Then
        r.Interior.ColorIndex = xlNone 'RAZ
        If x <> y Then
            i = Application.Match(x, r, 0)
            j = Application.Match(y, r, 0)
            If IsNumeric(i) And IsNumeric(j) Then _
                Union(r.Cells(i), r.Cells(j)).Interior.ColorIndex = 6 'jaune
        End If
    End If
Next
End Sub

Fichier (2).

A+

 

[karakoman1]

Bonsoir le forum, Job75
Je rentre à l'instant d'un petit Week-end en famille et je viens de tester ta dernière macro.
C'est exactement ce qu'il fallait, mais, après avoir recopié les macros dans mon fichier aux emplacements prévus, la macro plante dans mon fichier réel au niveau de la ligne 4 (en bleu),parce que j'ai plusieurs feuilles dans mon fichier et qu'au moment ou la macro ci dessous s'exécute, je ne suis pas dans la feuille en question.
Chez moi, la feuille avec le tableau des joueurs se trouve sur la feuille2 appelée "Tableau à imprimer" alors qu'au moment ou les données sont importées dans ce tableau via une macro lancée par un bouton de commande, je me trouve dans la feuille1 appelée "Tableau".
Ce qui fait planter la macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, x$, y$, i As Variant, j As Variant
Set r = [N23:W32] 'plage à adapter
If Intersect(ActiveCell, r) Is Nothing Then Exit Sub
x = ActiveCell.Offset(22 - ActiveCell.Row)
y = ActiveCell.Offset(, 13 - ActiveCell.Column)
r.Interior.ColorIndex = xlNone 'RAZ
If x <> y Then ActiveCell.Interior.ColorIndex = 6 'jaune
Set r = [C3:I32] 'plage à adapter
For Each r In r.Rows
If r.NumberFormat <> ",,,""Tournoi""" Then
r.Interior.ColorIndex = xlNone 'RAZ
If x <> y Then
i = Application.Match(x, r, 0)
j = Application.Match(y, r, 0)
If IsNumeric(i) And IsNumeric(j) Then _
Union(r.Cells(i), r.Cells(j)).Interior.ColorIndex = 6 'jaune
End If
End If
Next
End Sub

En tout cas un grand merci pour la patience et le temps passé à m'aider.
Je te souhaite une très bonne fin de soirée

 

[job75]

Bonsoir karakoman1,

On peut facilement éviter ce bug en ajoutant une ligne :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, x$, y$, i As Variant, j As Variant
If ActiveCell.Parent.Name <> Me.Name Then Exit Sub
'--------

A+

 

[karakoman1]

Bonsoir Job75
On y est presque, ça fonctionne presque.
les noms des différents joueurs du tableau sont importés via la feuille2 "Tableau" via une formule qui est
En M23, la formule est: =Tableau!S3
En N22: =M23
Ce qui me fait une erreur #VALEUR! dans les cases ou se trouvent tes formules =ComptePaire($M23;N$22;$C$3:$I$32)

PS :Je mets mon fichier en pièce jointe , ce sera plus simple

A+

 

[karakoman1]

Re,
J'ai posté mon fichier dans le message précédent
A+

 

[job75]

Re,

Il suffit de compléter la macro Figer avec cette ligne de code :

      .[N23:W32] = "=ComptePaire($M23,N$22,$C$3:$I$32)"

Fichier (2).

Bonne nuit.

 

[karakoman1]

Bonsoir le forum, Job75

Je viens de tester la dernière version de ta macro, ca fonctionne bien, sauf à partir du moment ou j'efface le tableau des joueurs via le bouton vert.
Il n'y a que des erreurs de valeur dans toutes les cellules vides au lieu d'avoir des zéros

Si j'essaye de masquer l'erreur par formules, elle s'efface dès que je " FIGE" un nouveau tableau.
J'ai essayé également par mise en forme conditionnelle, ça fonctionne (pas le zéro, mais masquer #valeur), mais ce n'est pas très joli comme résultat

Ne faut il pas gérer cette erreur de valeur via la macro?

Bonne soirée

 

[job75]

Bonsoir karakoman1,

Je viens de tester la dernière version de ta macro, ca fonctionne bien, sauf à partir du moment ou j'efface le tableau des joueurs via le bouton vert.
Il n'y a que des erreurs de valeur dans toutes les cellules vides au lieu d'avoir des zéros

Cela n'a vraiment aucune importance mais puisque vous manquez d'imagination :

Sub Effacer_tableau_des_joueurs()
[C3:I32].ClearContents
[N23:W32] = "=ComptePaire($M23,N$22,$C$3:$I$32)"
End Sub

Fichier (3).

A+