valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis ;)

TomTomNavigator

XLDnaute Nouveau
Bonjour tout le monde et merci d'avoir ouvert ce topic.
pr valeurs uniques excel.jpg
J'ai un problème pour lequel j'ai passé deux jours sans trouver de solutions...Je souhaite solliciter la communauté pour trouver la solution. Messieurs les sages ;) écarquillez bien vos yeux, je me lance:

J'ai une base de données simple mais longue (plus de 20000 lignes et 2 colonnes)

Dans une colonne se trouve des n° de séries
Dans une autre colonne se trouve les dates de sorties associées aux n° de séries

Un n° de série peut avoir plusieurs sorties le même jour.

Je souhaite comptabiliser le nombre de jours où il y a eu au moins une sortie par n° de série par mois.

Comment puis-je faire?

J'ai essayé des sommeprod mais je n'arrive pas à les mêler avec une fonction fréquence
j'ai essayé une fonction frequence mais je n'arrive pas à associer des conditions de plages
j'ai essayé la fonction nb.si mais même problème.

Le pire c'est que je pense que c'est simple mais, je n'ai pas encore la logique Excel bien en tete. Il faudrait m'expliquer le fonctionnement pour que je puisse apprendre par la compréhension.

Merci de vos réponses.
 

Pièces jointes

  • fichier exemple.xlsx
    9.6 KB · Affichages: 49
  • fichier exemple.xlsx
    9.6 KB · Affichages: 54
  • fichier exemple.xlsx
    9.6 KB · Affichages: 53

piga25

XLDnaute Barbatruc
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonjour,

Pour la première partie de la question un essai avec ceci (début de réponse) :
=SOMMEPROD(($F$2:$F$24=$A2)*(MOIS($G$2:$G$24)=MOIS(B$1))*(ANNEE($G$2:$G$24)=ANNEE(B$1)))

Autre essai avec formule matricielle mais reste à diférencier avec le mois.
=NB(1/FREQUENCE(SI(C$2:C$12=F2;EQUIV(A$2:A$12;A$2:A$12;0));LIGNE(INDIRECT("1:"&LIGNES(A$2:A$12)))))
à valider par CTRL+MAJ+ENTREE
 

Pièces jointes

  • fichier exemple piga25.xlsx
    10.9 KB · Affichages: 35
Dernière édition:

Dranreb

XLDnaute Barbatruc
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonjour
Numéroter les sorties de chaque jour dans une colonne H supplémentaire et ne compter que les 1, peut être ?
C'est en tout cas la direction que j'ai suivi.
En H2
Code:
=SOMMEPROD(N($F$2:$F2=$F2);N($G$2:$G2=$G2))
En B2
Code:
=SOMMEPROD(N($F$2:$F$24=$A2);N($G$2:$G$24>=B$1);N($G$2:$G$24<=FIN.MOIS(B$1;0));N($H$2:$H$24=1))
À +
 

TomTomNavigator

XLDnaute Nouveau
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonjour piga25,

Je ne sais pas pourquoi mais ta formule ne fonctionne pas. Elle Affiche l'erreur #NOM!

J'avais trouvé une première solution pour ça en écrivant la formule comme suit

=SOMMEPROD(($F$2:$F$24=$A7)*($G$2:$G$24>=B$1)*($G$2:$G$24<C$1))
matrice 1 va chercher le n° de série
matrice 2 les dates supérieurs ou égale au mois en cours
matrice 3 les dates strictement inférieurs au début du mois suivant.

Et comme ça je trouve le nombre de sortie par n° de série par mois.

est-ce qu'à partir de cette formule on peut ajouter une formule frequence pour ne cherchez que les jours différents ou il y a une sortie?

Je n'ai aps réussi à imbriquer cette formule dans une formule frequence...
 

TomTomNavigator

XLDnaute Nouveau
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Autre essai avec formule matricielle mais reste à diférencier avec le mois.

=NB(1/FREQUENCE(SI(C$2:C$12=F2;EQUIV(A$2:A$12;A$2:A$12;0 ));LIGNE(INDIRECT("1:"&LIGNES(A$2:A$12)))))
à valider par CTRL+MAJ+ENTREE

je comprends pas ce que tu as fait dans la partie matrice intervalle de la fonction frequence. Peux tu m'expliquer stp?...

Pourquoi ça ne marche pas si on met {B1-1,C1} dans la partie matrice intervalle? comme ça il irait chercher dynamiquement les valeurs pour les mois. Me comprends-tu?
 

R@chid

XLDnaute Barbatruc
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonjour @ tous,
En B2,
Code:
=NB(1/FREQUENCE(SI(($F$2:$F$24=$A2)*(MOIS($G$2:$G$24)=MOIS(B$1));EQUIV($G$2:$G$24;$G$2:$G$24;0));LIGNE(INDIRECT("1:"&LIGNES($G$2:$G$24)))))
@ valider par Ctrl+Maj+Entree
@ tirer vers le bas et vers la droite
@ micalement
 

TomTomNavigator

XLDnaute Nouveau
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Merci R@chid! C'est exactement ce dont j'avais besoin, merci à tous d'avoir participer.
En revanche, est-ce que tu peux m'expliquer pourquoi ça fonctionne, je ne maîtrise pas les fonctions ligne et indirect.
Si tu connais un bon tuto. Merci de me l'indiquer.
 

TomTomNavigator

XLDnaute Nouveau
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Dernière news,

J'ai fait planter mon fichier car celui-ci contient 20 000 lignes. Il ne supporte pas le calcul de cette formule.

Pensez-vous que le mieux soit un tableau croisé dynamique ou du copier/coller traditionnel?
 

piga25

XLDnaute Barbatruc
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonjour à tous

J'ai retrouvé une fonction créée par David84 qui repond en partie au problème. Il ne manque que la condition du mois année à inclure:
VB:
'Function créée par David84
Function ItemsDifferentsCritere(champ, champcritere, critere)
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champ
  b = champcritere
  For i = 1 To champ.Count
    If b(i, 1) = critere And a(i, 1) <> "" Then
       temp = a(i, 1)
       MonDico(temp) = temp
    End If
  Next i
  ItemsDifferentsCritere = MonDico.Count
End Function
 

david84

XLDnaute Barbatruc
Re : valeurs uniques d'une plage: à l'attention des utilisateurs qui aiment les défis

Bonsoir,
pas sûr d'avoir compris mais ci-joint une fonction personnalisée à tester :
Code:
Function Numero_serie(champserie As Range, critereserie As Integer, _
champdate As Range, criteredate As Date) As Integer
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champserie
  b = champdate
  For i = LBound(a) To UBound(a)
    If a(i, 1) = critereserie And Month(b(i, 1)) = Month(criteredate) _
    And Year(b(i, 1)) = Year(criteredate) Then
       MonDico(a(i, 1) & " " & b(i, 1)) = MonDico(a(i, 1) & " " & b(i, 1))
    End If
  Next i
  Numero_serie = MonDico.Count
End Function
Entrer en B2 et tirer vers le bas et la droite :
Code:
=Numero_serie($F$2:$F$24;$A2;$G$2:$G$24;B$1)
A+
 

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 943
Membres
101 849
dernier inscrit
florentMIG