Microsoft 365 recherchev et nb.si ?

[Nico0709]

Bonjour le forum,

J’ai un classeur qui comporte 52 feuilles et dans une 53ème feuille, je souhaite faire un cumul de chaque semaine. Les feuilles sont une liste de noms (non alphabétiques) et de tâches.

Sur ma feuille 53 en colonne A j’ai la liste exhaustive de tous les noms des gens présents sur chaque semaine (dans le désordre) et en ligne A la liste de tâches.

Ma première question est de savoir comment récupérer tous les noms en colonne A sans doublons (peu importe l’ordre) de façon automatique ? Je peux bien sûr les récupérer manuellement et les traiter mais c’est fastidieux.

Ma deuxième question, la principale, est de réussir à calculer combien de fois chaque personne a effectué une tâche. J’ai donc en B2 pour la première personne la formule suivante : =NB.SI('S27'!$C31:AE31;Feuil2!B$1)+ NB.SI('S28'!$C31:AE31;Feuil2!B$1)+ … Existe-t-il un moyen de faire une formule ou un code pour ajouter les 52 semaines sans devoir faire la somme manuellement, sachant que la liste des noms n’est pas dans l’ordre dans chaque feuille et donc je dois changer manuellement la plage à chaque fois. J’ai essayé de cumuler RECHERCHEV et NB.SI ou encore SOMMEPROD et je n’arrive à rien.

Je vous joins un classeur pour l’exemple. Je vous remercie de votre concours.

Nico.

 

[chris]

Bonjour

Avec une structure de semaine aussi peu normalisée, tu te compliques l'existence

Ta formule ne tient pas compte du prénom...

En utilisant PowerQuery, intégré à Excel, après avoir défini une plage nommée par semaine, on peut en tirer quelque chose mais ton tableau récapitulatif a 33 prénoms alors qu'on en liste 138 sur 4 semaines, dont certains sont les mêmes écrits de façon différente...

C'est un peu fouillis tout cela

 

[Nico0709]

Bonjour

Avec une structure de semaine aussi peu normalisée, tu te compliques l'existence

Ta formule ne tient pas compte du prénom...

En utilisant PowerQuery, intégré à Excel, après avoir défini une plage nommée par semaine, on peut en tirer quelque chose mais ton tableau récapitulatif a 33 prénoms alors qu'on en liste 138 sur 4 semaines, dont certains sont les mêmes écrits de façon différente...

C'est un peu fouillis tout cela

Merci de ton retour, c'est un tableau que l'on m'impose, je n'ai pas pu choisir sa structure, même si je vais travailler parallèlement sur un autre fichier pour l'améliorer. J'ai juste oublié dans le récap de retirer les noms pour la confidentialité, c'est raté ! C'est pour cela évidemment que les noms ne correspondent pas, et je n'ai repris les prénoms que de la feuille S27 pour l'exemple.

Malheureusement, je ne connais pas PowerQuery, je n'ai donc aucune idée de ce que je peux en faire. Je peux facilement répertorier les noms des gens sur le récap, même manuellement et par ordre alphabétique si ça aide, mais je ne trouve pas de formule globale qui calculerait le nombre de tâches en fonction de la personne.

 

[chris]

RE

La première chose à faire est pour chaque onglet semaine sélectionner la plage utile
exemple pour S27 :
$A$31:$AF$63
et la nommer avec des noms allant de S_01 à S_27

La synthèse ne reprend pas tous les contenus

Seuls sont concernés les suivants ?

PILOTE

CARISTE

GARANT

Manut

ANOS Rgt

ANOS Tob

AUDIT OLPN

EVAC

Conso 30 MIN

Balai tri 20 MIN

WEB

Reposte ton fichier avec les toutes plages nommées (en MP si PB de confidentialité) et on te donnera la marche à suivre pour PowerQuery

 

[Nico0709]

RE

La première chose à faire est pour chaque onglet semaine sélectionner la plage utile
exemple pour S27 :
$A$31:$AF$63
et la nommer avec des noms allant de S_01 à S_27

La synthèse ne reprend pas tous les contenus

Seuls sont concernés les suivants ?

PILOTE

CARISTE

GARANT

Manut

ANOS Rgt

ANOS Tob

AUDIT OLPN

EVAC

Conso 30 MIN

Balai tri 20 MIN

WEB

Reposte ton fichier avec les toutes plages nommées (en MP si PB de confidentialité) et on te donnera la marche à suivre pour PowerQuery

J'ai refait le fichier avec tous les prénoms en feuille 1 et mis 2 exemples du résultat attendu : certaines personnes sont en S27 et S28, d'autres uniquement sur une des 2 feuilles. J'ai nommé comme demandé les plage S_27 et S_28.

Pour la synthèse, c'est normal que tous les contenus ne soient pas repris, je n'intègre que ceux qui sont pertinents pour moi.

J'espère que le fichier sera plus clair. Merci de ton aide en tout cas.

 

[chris]

re

J’ai un classeur qui comporte 52 feuilles et dans une 53ème feuille, je souhaite faire un cumul de chaque semaine.

des noms allant de S_01 à S_27

J'ai nommé comme demandé les plage S_27 et S_28.

L'idée était de prendre en compte les 52 feuilles, sinon pour 2 noms, je l'avais fait...

Finalement tu veux 52 semaines ou pas ?

 

[Nico0709]

re





L'idée était de prendre en compte les 52 feuilles, sinon pour 2 noms, je l'avais fait...

Finalement tu veux 52 semaines ou pas ?

Désolé je n'avais pas compris comme ça. Je vais préparer le fichier dont j'ai besoin (c'est à partir de la semaine 27 jusque semaine en cours pour 2021). Est-ce que tu peux m'expliquer comment tu as fait ? C'est exactement ce dont j'ai besoin, un grand merci.

 

[chris]

Bonjour

Données, Obtenir des données, Lancer PowerQuery : tu verras les 2 requêtes : l'une qui récupère la liste des Postes à synthétiser, l'autre qui traite les plages nommées dont le nom commence par S_

Tu as les étapes à droite dont tu peux voir le détail dans la barre de formule et en cliquant sur le petit rouage sur la ligne de l'étape.

Toute plage nommée qui commence par S_ sera automatiquement prise en compte.
Dans Excel, Données, Actualiser tout

 

[job75]

Bonjour Nico0709, chris,

Voyez le fichier joint et cette macro dans la feuille "Comptage" :

Private Sub Worksheet_Activate()
Dim d As Object, titre As Range, ncol%, resu(), w As Worksheet, i&, n&, nn&, j%, compte&
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
Set titre = [A1].CurrentRegion.Rows(1).Cells
ncol = titre.Count
ReDim resu(1 To 1000, 1 To ncol) 'hauteur 1000 lignes maximum, à adapter
For Each w In Worksheets
    If w.Name Like "S#*" Then
        If w.FilterMode Then w.ShowAllData 'si la feuille est filtrée
        For i = 6 To w.Range("A" & w.Rows.Count).End(xlUp).Row
            With w.Cells(i, 1)
                If .Value <> "" Then
                    If Not d.exists(.Value) Then
                        n = n + 1
                        d(.Value) = n 'mémorise le n° de ligne
                        resu(n, 1) = .Value
                    End If
                    nn = d(.Value)
                    For j = 2 To ncol
                        compte = Application.CountIf(.EntireRow, titre(j)) 'NB.SI
                        If compte Then resu(nn, j) = resu(nn, j) + compte
                    Next j
                End If
            End With
        Next i
    End If
Next w
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2]
    If n Then
        .Resize(n, ncol) = resu
        .Resize(n, ncol).Sort .Cells(1), xlAscending, Header:=xlYes 'tri alphabétique
        .Resize(n, ncol).Borders.Weight = xlThin 'bordures
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).Delete xlUp 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Elle se déclenche automatiquement quand on active la feuille.

Extrapolée sur 52 feuilles la durée d'exécution chez moi sera de 0,6 seconde.

A+