Microsoft 365 Une formule la plus courte

LEPATOCHE

XLDnaute Occasionnel
Bonsoir le forum

je voudrais une formule la plus courte possible en F21 .
donc dans la ligne 21 il y a 5 numeros et dans chaque colonnes il y a 20 numeros ,
donc en tout la formule me renverra 2 par ce que il y a 2 colonne qui ont 5 numeros identique par rapport a la ligne 21
merci de votre aide
 

job75

XLDnaute Barbatruc
Bonsoir LEPATOCHE, Bruno,

On ne sait pas si vous voulez les nombres 1 2 3 4 5 dans l'ordre ou dans le désordre.

Dans le désordre c'est le plus simple à comprendre, formule en F21 :
VB:
=(SOMMEPROD(SIGNE(NB.SI(F1:F20;A21:E21)))=5)+(SOMMEPROD(SIGNE(NB.SI(G1:G20;A21:E21)))=5)+(SOMMEPROD(SIGNE(NB.SI(H1:H20;A21:E21)))=5)+(SOMMEPROD(SIGNE(NB.SI(I1:I20;A21:E21)))=5)+(SOMMEPROD(SIGNE(NB.SI(J1:J20;A21:E21)))=5)
Il ne faut pas de doublon dans la plage A21:E21.

A+
 

Pièces jointes

  • 5.xlsx
    9.1 KB · Affichages: 4

LEPATOCHE

XLDnaute Occasionnel
merci job75 cela marche bien ! mais j'aurais voulu une formule beaucoup moins longue
attention ne pas nommer des plages car tu vois mon cher job75 apres je vais recopier ta nouvelle formule super courte vers la droite si tu desir trouver une reponse bonne soiree a vous .
 

job75

XLDnaute Barbatruc
Dans l'ordre il faut utiliser JOINDRE.TEXTE qui existe depuis Excel 2016 :
VB:
=ESTNUM(TROUVE(JOINDRE.TEXTE(" ";VRAI;A21:E21);JOINDRE.TEXTE(" ";VRAI;F1:F20)))+ESTNUM(TROUVE(JOINDRE.TEXTE(" ";VRAI;A21:E21);JOINDRE.TEXTE(" ";VRAI;G1:G20)))+ESTNUM(TROUVE(JOINDRE.TEXTE(" ";VRAI;A21:E21);JOINDRE.TEXTE(" ";VRAI;H1:H20)))+ESTNUM(TROUVE(JOINDRE.TEXTE(" ";VRAI;A21:E21);JOINDRE.TEXTE(" ";VRAI;I1:I20)))+ESTNUM(TROUVE(JOINDRE.TEXTE(" ";VRAI;A21:E21);JOINDRE.TEXTE(" ";VRAI;J1:J20)))
Les doublons en A21:E21 ne posent pas de problème.

Bonsoir mapomme.
 

Pièces jointes

  • 5(1).xlsx
    9.1 KB · Affichages: 3

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Avec une fonction personnalisée, on simplifie l'écriture en F21 :
VB:
=Ncolonne(F1:I20;A21:E21)

nota 1 : s'il y a un doublon dans les valeurs à chercher, alors dans la colonne il doit aussi y figurer le doublon.
nota 2 : on peut chercher une valeur "vide"

Code de la fonction dans module1 :
VB:
Function Ncolonne(plage As Range, valeurs As Range) As Long
Dim col, t, n&, x, i&, res&
   For Each col In plage.Columns
      t = col.Value: n = valeurs.Count
      For Each x In valeurs
         For i = 1 To UBound(t)
            If Not IsNull(t(i, 1)) Then
               If t(i, 1) = x Then n = n - 1: t(i, 1) = Null: Exit For
            End If
         Next i
      Next x
      If n = 0 Then res = res + 1
   Next col
   Ncolonne = res
End Function
 

Pièces jointes

  • LEPATOCHE- nombres dans colonnes- v2.xlsm
    16.8 KB · Affichages: 10

job75

XLDnaute Barbatruc
Bonjour LEPATOCHE, Bruno, mapomme, le forum,

La formule de mon post #8 ne va pas, il faut encadrerl les 10 textes par des espaces :
VB:
=ESTNUM(TROUVE(" "&JOINDRE.TEXTE(" ";VRAI;A21:E21)&" ";" "&JOINDRE.TEXTE(" ";VRAI;F1:F20)&" "))+ESTNUM(TROUVE(" "&JOINDRE.TEXTE(" ";VRAI;A21:E21)&" ";" "&JOINDRE.TEXTE(" ";VRAI;G1:G20)&" "))+ESTNUM(TROUVE(" "&JOINDRE.TEXTE(" ";VRAI;A21:E21)&" ";" "&JOINDRE.TEXTE(" ";VRAI;H1:H20)&" "))+ESTNUM(TROUVE(" "&JOINDRE.TEXTE(" ";VRAI;A21:E21)&" ";" "&JOINDRE.TEXTE(" ";VRAI;I1:I20)&" "))+ESTNUM(TROUVE(" "&JOINDRE.TEXTE(" ";VRAI;A21:E21)&" ";" "&JOINDRE.TEXTE(" ";VRAI;J1:J20)&" "))
A+
 

Pièces jointes

  • 5(2).xlsx
    9.1 KB · Affichages: 1

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @job75 :),
@mapomme au post #10 ta formule est validée matriciellement, pourquoi ?

Message #10 : aucune raison de valider matriciellement la formule
Message #6 : en 365 (et sans doute en Excel 2019) une validation normale suffit, pour las autres versions je pense qu'il faut valider matriciellement.

En ce moment, ayant Office 365 qui ne nécessite plus la validation matricielle, et suivant la version Excel du demandeur, j'oublie la validation matricielle quand c'est nécessaire.
Manifestement, je la mets aussi quand ce n'est pas nécessaire.

==> Je ne sais pas, je ne sais plus

edit : Bonjour @Usine à gaz ;).
 
Dernière édition:

job75

XLDnaute Barbatruc
Autre remarque : l'utilisation de Null nécessite un test supplémentaire.

Perso je préfère utiliser Chr(1) avec ce code :
VB:
Function Ncolonne(plage As Range, valeurs As Range) As Long
Dim nval&, col, t, n&, x, i&
nval = valeurs.Count
For Each col In plage.Columns
    t = col: n = 0
    For Each x In valeurs
        For i = 1 To UBound(t)
            If t(i, 1) = x Then n = n + 1: t(i, 1) = Chr(1): Exit For
    Next i, x
    If n = nval Then Ncolonne = Ncolonne + 1
Next col
End Function
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 909
Membres
101 836
dernier inscrit
karmon