Excel - conditions multiples + recherche

[ABCD2008]

Bonjour à tous,

Je bataille depuis plusieurs heures avec un tableau automatique que j'aimerais créer.
Voilà, j'ai une base de données d'une 20taine de colonnes, et dans cette base, 3 colonnes m'intéressent essentiellement : CATEGORIE, COEFFICIENT, SALAIRE.

Je voudrais créer un tableau par coefficient en excluant les populations OUVRIER, APPRENTI, avec :
- le nombre de personnes dans chaque COEFFICIENT (inclus : EMPLOYE, CADRE, AG_MAITRISE, ASSIMILE_CAD)
- le salaire mini suivant le coeff et en excluant ouvrier et apprenti.

Si j'ai ces 2 colonnes, je pense que j'arriverais à me dépatouiller avec le max, la moyenne et la médiane!

Je vous joins un fichier excel.

Je vous remercie par avance de votre aide !!! =)

 

[chris]

Re : Excel - conditions multiples + recherche

Bonjour

Un TCD (Tableau Croisé Dynamique) peut te donner tout cela à l'exception de la médiane.

J'avais travaillé sur des cas de médianes en TCD : je reviens si je trouve une solution.

Je joins déjà le TCD.

Sinon NB.SI.ENS et MOYENNE.SI.ENS peuvent aussi calculer mais il faudra du matriciel pour MAX et MIN et si tu as beaucoup de salariés cela risque de ramer...

 

[chris]

Re : Excel - conditions multiples + recherche

Re

2 ème mouture avec une formule en plus dans la source pour obtenir la médiane dans le TCD.

Cependant n'étant pas spécialiste des stats, médiane à vérifier par les connaisseurs SVP

 

[ABCD2008]

Re : Excel - conditions multiples + recherche

Merci Chris !

Je n'avais pas pensé au tableau croisé dynamique. Bon, je n'y serais pas mieux arrivée même si ça avait été le cas !! =P
Merci, ça à l'air de fonctionner impeccablement !

Le calcul de la médiane à l'air bon aussi. Parfait !

Merci pour ton aide et ton efficacité !! =)

 

[job75]

Re : Excel - conditions multiples + recherche

Bonsoir ABCD2008, chris,

Une solution VBA, la macro est conséquente :

Private Sub Worksheet_Activate()
Dim col1%, col2%, col3%, inclu, t, salaire()
Dim d As Object, i&, n&, a, b, s, c, j&
col1 = 1: col2 = 2: col3 = 3 'n° des colonnes, à adapter
inclu = Array("EMPLOYE", "CADRE", "AG_MAITRISE", "ASSIMILE_CAD")
t = Feuil2.UsedRange 'CodeName de la feuille
ReDim salaire(UBound(t))
Set d = CreateObject("Scripting.Dictionary")
'---liste sans doublon et concaténation des salaires---
For i = 2 To UBound(t)
  If IsNumeric(Application.Match(t(i, col1), inclu, 0)) Then
    salaire(i) = t(i, col3)
    d(t(i, col2)) = d(t(i, col2)) & " " & salaire(i)
  End If
Next
n = d.Count
Application.ScreenUpdating = False
Range("A2:F" & Rows.Count).Delete xlUp 'RAZ
If n = 0 Then Exit Sub
'---tableaux VBA---
a = d.keys: b = d.items
ReDim t(1 To n, 1 To 6)
For i = 1 To n
  s = Split(Mid(b(i - 1), 2))
  ReDim c(UBound(s))
  For j = 0 To UBound(s)
    c(j) = CDbl(s(j)) 'conversion
  Next
  t(i, 1) = a(i - 1)
  With Application
    t(i, 2) = .Count(c): t(i, 3) = .Min(c): t(i, 4) = .Max(c)
    t(i, 5) = .Average(c): t(i, 6) = .Median(c)
  End With
Next
'---restitution---
With [A2].Resize(n, 6)
  .Value = t
  .Sort .Columns(1), xlAscending, Header:=xlNo 'tri
  .Cells(n + 2, 1) = "Total"
  .Cells(n + 2, 1).Font.Bold = True 'gras
  .Cells(n + 2, 2) = Application.Count(salaire)
  .Cells(n + 2, 3) = Application.Min(salaire)
  .Cells(n + 2, 4) = Application.Max(salaire)
  .Cells(n + 2, 5) = Application.Average(salaire)
  .Cells(n + 2, 6) = Application.Median(salaire)
End With
End Sub

Fichier joint.

Noter que pour les coefficients 285 et 305 je n'obtiens pas les mêmes médianes que chris.

Edit : je viens de vérifier, mes résultats sont justes pour les 2 médianes (66000 et 43250).

Bonne nuit.

 

[job75]

Re : Excel - conditions multiples + recherche

Bonjour ABCD2008, chris, le forum,

Si un salaire est vide ou n'est pas numérique (Feuil2 colonne C) la macro précédente beugue.

Il suffit alors de compléter cette ligne :

If IsNumeric(s(j)) Then c(j) = CDbl(s(j)) 'conversion

Fichier (2).

Bonne journée.

 

[ABCD2008]

Re : Excel - conditions multiples + recherche

Bonjour job75,

Merci pour la macro, ça marche aussi parfaitement !!

Par contre, le fichier que je vous ai envoyé est un fichier fictif, et lorsque j'essaye d'adapter la macro dans mon fichier excel, impossible....

Dans mon fichier source, les données sont dans une feuille nommée "Avril 2016", la catégorie (employé, cadre, etc.) est colonne 14, le coefficient en colonne 15 et le salaire en colonne 20.

Y a-t-il la possibilité de modifier la macro pour que ça marche avec ces données ?
Et comment faire ensuite pour installer la macro dans mon fichier source ? (j'ai essayé de copier les onglets de ton fichier : "déplacer/copier" les onglets directement) mais la macro ne marche plus comme ça....

Une solution ? =)
Encore merci à vous !!

 

[job75]

Re : Excel - conditions multiples + recherche

Re,

3 choses à bien comprendre.

1) Adapter les numéros des colonnes sources :

col1 = 14: col2 = 15: col3 = 20 'n° des colonnes, à adapter

2) Dans t = Feuil2.UsedRange 'CodeName de la feuille remplacer Feuil2 par le CodeName de la feuille source.

Dans VBA il s'agit du nom de la feuille qui se trouve devant le nom entre parenthèses.

3) Copier la macro dans la feuille de destination (clic droit sur l'onglet et Visualiser le code).

A+

 

[chris]

Re : Excel - conditions multiples + recherche

Bonjour

Merci à Job75 pour le contrôle des médianes. J'avais un gros doute sur ma formule.
On peut la modifier pour obtenir la bonne médiane dans le TCD.

Mais comme vu lors d'essais précédents, comme le TCD ne sait pas faire les médianes, ce type de calcul limite l'usage du TCD car il faut que la formule et le TCD soient toujours cohérents, alors que l'un des intérêts du TCD, est de jouer sur les filtres pour changer l'angle d'analyse très facilement.

Je donne néanmoins la formule modifiée mais elle ne marche correctement que si le filtre du TCD est cohérent ( mêmes catégories exclues et ventilation par coeff uniquement) :

=SI(OU([@CATEGORIE]="OUVRIER";[@CATEGORIE]="APPRENTI");"exclu du calcul";SIERREUR(MEDIANE(SI(([COEFFICIENT]=[@COEFFICIENT])*([CATEGORIE]<>"OUVRIER")*([CATEGORIE]<>"APPRENTI");[Salaire]));"exclu du calcul"))

à valider en matriciel

A noter également que le total dans le TCD n'a pas de sens pour cette colonne donc à masquer par une MFC.

Cela donne 2 solutions à ABCD 2008...

Edit : légère modification de la formule pour que toutes les lignes exclues soient bien mentionnées.

 

[ABCD2008]

Re : Excel - conditions multiples + recherche

Bonjour à vous deux !
Merci pour le complément d'info !
Je suis bien arrivée à modifier les colonnes, c'était le nom de la feuille qui bloquait, je n'avais pas vu qu'il y avait un nom "VBA", je m'évertuais à mettre "Avril 2016" et évidemment, il n'était pas content....!

J'ai eu un soucis pour le Total en dessous, mais j'ai appliqué le conseil de job75 sur le fait d'avoir des nombres (ma colonne salaire était en mode comptabilité) et du coup, tout fonctionne !

Un grand merci les gars pour votre aide !!

 

[job75]

Re : Excel - conditions multiples + recherche

Re,

J'ai eu un soucis pour le Total en dessous, mais j'ai appliqué le conseil de job75 sur le fait d'avoir des nombres (ma colonne salaire était en mode comptabilité) et du coup, tout fonctionne !

Ah oui, j'avais oublié, utilisez la propriété .Value2 :

t = Feuil2.UsedRange.Value2 'CodeName de la feuille

Fonctionne que la colonne"Salaire" soit formatée ou pas.

Fichier (3).

A+

 

[job75]

Re : Excel - conditions multiples + recherche

Re,

J'ai recopié le tableau de Feuil2 jusqu'à la ligne 84001.

La macro s'exécute chez moi (Win 10 - Excel 2013) en 1,7 seconde, c'est très bien.

[Edit] 50 secondes pour un tableau de 840.000 lignes (fichier de 16 Mo).

Mais la ligne "Total" ne donnait pas le bon résultat.

En fait il faut que la matrice salaire soit un tableau a 2 dimensions :

ReDim salaire(1 To UBound(t), 1 To 1)

Utilisez donc cette macro :

Private Sub Worksheet_Activate()
Dim col1%, col2%, col3%, inclu, t, salaire()
Dim d As Object, i&, n&, a, b, s, c, j&
col1 = 1: col2 = 2: col3 = 3 'n° des colonnes, à adapter
inclu = Array("EMPLOYE", "CADRE", "AG_MAITRISE", "ASSIMILE_CAD")
t = Feuil2.UsedRange.Value2 'CodeName de la feuille
ReDim salaire(1 To UBound(t), 1 To 1)
Set d = CreateObject("Scripting.Dictionary")
'---liste sans doublon et concaténation des salaires---
For i = 2 To UBound(t)
  If IsNumeric(Application.Match(t(i, col1), inclu, 0)) Then
    salaire(i, 1) = t(i, col3)
    d(t(i, col2)) = d(t(i, col2)) & " " & salaire(i, 1)
  End If
Next
n = d.Count
Application.ScreenUpdating = False
Range("A2:F" & Rows.Count).Delete xlUp 'RAZ
If n = 0 Then Exit Sub
'---tableaux VBA---
a = d.keys: b = d.items
ReDim t(1 To n, 1 To 6)
For i = 1 To n
  s = Split(Mid(b(i - 1), 2))
  ReDim c(UBound(s))
  For j = 0 To UBound(s)
    If IsNumeric(s(j)) Then c(j) = CDbl(s(j)) 'conversion
  Next
  t(i, 1) = a(i - 1)
  With Application
    t(i, 2) = .Count(c): t(i, 3) = .Min(c): t(i, 4) = .Max(c)
    t(i, 5) = .Average(c): t(i, 6) = .Median(c)
  End With
Next
'---restitution---
With [A2].Resize(n, 6)
  .Value = t
  .Sort .Columns(1), xlAscending, Header:=xlNo 'tri
  .Cells(n + 2, 1) = "Total"
  .Cells(n + 2, 1).Font.Bold = True 'gras
  .Cells(n + 2, 2) = Application.Count(salaire)
  .Cells(n + 2, 3) = Application.Min(salaire)
  .Cells(n + 2, 4) = Application.Max(salaire)
  .Cells(n + 2, 5) = Application.Average(salaire)
  .Cells(n + 2, 6) = Application.Median(salaire)
End With
End Sub

Fichier (4), je pense qu'on a vu tous les problèmes.

A+

 

[klin89]

Re : Excel - conditions multiples + recherche

Bonjour le fil,

Comme je m'étais penché sur l'exercice, je poste.
A tester sur le fichier du post #1

Option Explicit
Sub test()
Dim a, b(), x(), w(), i As Long, n As Long, e
    With Sheets("Feuil2").Range("a1").CurrentRegion
        a = .Value
        ReDim b(1 To UBound(a, 1), 1 To 6)
        b(1, 1) = "Coefficient": b(1, 2) = "Nbre": b(1, 3) = "Mini"
        b(1, 4) = "Maxi": b(1, 5) = "Moyenne": b(1, 6) = "Médiane"
        n = 1
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If a(i, 1) <> "OUVRIER" And a(i, 1) <> "APPRENTI" Then
                    If Not .exists(a(i, 2)) Then
                        n = n + 1
                        ReDim w(1 To 2)
                        ReDim x(1 To 1): x(1) = a(i, 3)
                        w(1) = n: w(2) = x
                        .Item(a(i, 2)) = w
                    Else
                        w = .Item(a(i, 2))
                        x = w(2)
                        ReDim Preserve x(1 To UBound(x) + 1)
                        x(UBound(x)) = a(i, 3)
                        w(2) = x
                        .Item(a(i, 2)) = w
                    End If
                End If
            Next
            For Each e In .keys
                w = .Item(e)
                b(w(1), 1) = e
                b(w(1), 2) = Application.Count(w(2))
                b(w(1), 3) = Application.Min(w(2))
                b(w(1), 4) = Application.Max(w(2))
                b(w(1), 5) = Application.Average(w(2))
                b(w(1), 6) = Application.Median(w(2))
            Next
        End With
        Application.ScreenUpdating = False
        With Sheets("Feuil3").Range("a1")
            .CurrentRegion.Clear
            .Resize(n, 6).Value = b
            With .CurrentRegion
                .Sort .Columns(1), xlAscending, Header:=xlYes
                .Font.Name = "calibri"
                .Font.Size = 10
                .VerticalAlignment = xlCenter
                .BorderAround Weight:=xlThin
                .Borders(xlInsideVertical).Weight = xlThin
                .Columns(5).NumberFormat = "0"
                With .Rows(1)
                    .Interior.ColorIndex = 44
                    .BorderAround Weight:=xlThin
                    .HorizontalAlignment = xlCenter
                End With
            End With
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Pour bien comprendre le cheminement :

L'item associé aux différentes clés du dictionnaire est un tableau (w) de 2 éléments (soit w(1) et w(2))
le 1er élément contient le N° de ligne (n)
le 2ème élément est un tableau à une dimension (x) contenant tous les salaires de la clé concernée.

klin89

 

[job75]

Re : Excel - conditions multiples + recherche

Bonjour klin89,

Oui l'idée est bonne mais j'ai testé ta macro sur 84.000 lignes => 11,8 secondes (la mienne 1,7 seconde comme déjà dit).

Sur 840.000 lignes j'ai abandonné au bout de 10 minutes.

Par ailleurs la colonne Salaire en Feuil2 doit être au format Standard.

A+

 

[klin89]

Re : Excel - conditions multiples + recherche

Salut job75

Effectivement, je ne teste jamais la rapidité de mes macros.
Je me doutais bien que ReDim Preserve plomberait la rapidité de l'exécution de la macro ainsi que la déclaration du dictionnaire.
Au moins, j'apprends ce qu'il faut éviter de faire, mais j'ai du mal à me débarrasser de certaines manies.

Je sais aussi que certains ont beaucoup de difficultés à manipuler les dictionnaires.
Pour l'exercice, je voulais leur montrer que l'item associé à la clé, pouvait être une variable tableau contenant une valeur et une autre variable tableau.

J'ai testé ta macro en mode débogage comme souvent.
J'en profite aussi pour te remercier d'afficher tes codes en ligne, c'est toujours un plaisir de les lire et de les décrypter.

Au plaisir de te croiser
klin89