Discussion :

[Romain_dbs]

Bonjour à tous,

Je fais appel à vous car je bloque depuis quelques jours sur une requête. Je voudrais calculés le nombre de jours qu'un achat a été réalisé consécutivement. Pour cela, j'ai recodé la variable Achat en 0/1. ce que je souhaiterais c'est faire la somme de tous les 1 tant qu'il n'y a pas de 0 apparaissant. Je voudrais grouper cela par acteur. Je ne sais pas si cela a été clair ou pas. Ainsi, je vous propose un fichier Excel avec mon fichier de base et les résultats souhaités pour que vous puissiez mieux comprendre.
exemple.xlsx

Merci d'avance pour votre retour.
Cordialement.

[Menhir]

Une solution simple est de créer une nouvelle colonne comptant incrémentant un compteur tant que la colonne Achat est à 1 et revenant à 0 sinon.
Par exemple, si "Achat" est en colonne A et commence à la ligne 1, mettre en B2 =SI(A2=1;B1+1;0) et copier B2 dans les lignes du dessous.
Il suffit ensuite d'une fonction MAX() pour connaitre la taille de la suite la plus longue.

[Romain_dbs]

Bonjour,

merci pour votre retour. Mais comment puis-je faire pour que cela se groupe par acteur ? Et pour afficher la date dans les tableaux de mes résultats souhaités ?

[Menhir]

Il suffit de faire une colonne par acteur et de rajouter une condition dans la colonne du SI.

[Romain_dbs]

Je suis désolée, je ne vois pas très bien ce que vous voulez dire. Pourriez-vous me fournir un exemple de formule via un fichier Excel si cela ne vous dérange pas ?

[BENNASR]

Bonjour
j'ai lu sur le net que des nouvelles fonctions telle que MIN.SI.ENS et MAX.SI.ENS disponible avec Excel 2016 / Office 365 je crois que ces fonctions réponds en partie à tes besoins..
Pour ton cas j'ai essayer de bricolé un code
à tester avec prudence sur une copie de ton fichier...je suis qu'un autodidacte qui supporte aucune responsabilité en cas de perte des données
code plein de bricolage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub test()
Application.ScreenUpdating = False
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("Feuil2")
Set F2 = Sheets("RESULTAT")
  F2.Cells.ClearContents
Dim unique As New Collection
Dim i As Integer
On Error Resume Next
For Each cel In F1.Range("A2:A" & [A65000].End(xlUp).Row)
If F1.Cells(cel.Row, 2) <> 0 Then
    unique.Add cel.Value, CStr(cel.Value)
    End If
Next cel
 F2.Cells(1, 1).Resize(1, 4) = Array("Acteur", "periode Debut", "Periode Fin", "Nombre de validation")
ligne = 2
On Error GoTo 0
For i = 1 To unique.Count
F2.Cells(ligne, 1) = unique(i)
ligne = ligne + 1
Next i
 
Dim derlig As Long
Dim lngNumLigne As Long
derlig = F2.Cells(Rows.Count, 1).End(xlUp).Row
For L = 2 To derlig
F2.Cells(L, 4) = WorksheetFunction.CountIfs(F1.Columns("A"), F2.Cells(L, 1), F1.Columns("C"), 1)
Next L
For J = 2 To derlig
F1.Range("A1:C" & derlig).AutoFilter Field:=1, Criteria1:=F2.Cells(J, 1)
 Dim Lig As Long
Lig = F1.Range("A2:A" & derlig).SpecialCells(xlCellTypeVisible).Row
F2.Cells(J, 2) = F1.Cells(Lig, 2)
For Each cell In F1.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
    lngNumLigne = cell.Row
Next cell
F2.Cells(J, 3) = F1.Cells(lngNumLigne, 2)
F1.ShowAllData
Next J
Application.ScreenUpdating = True
End Sub

[Romain_dbs]

Bonjour Bennasr,

Je m'excuse pour le retard de la réponse.
Cela me semble bon, sauf que j'aurais souhaités avoir pour un acteur par exemple toutes les fois ou celui-ci a réalisé des études consécutivement et pas seulement son maximum.

Cordialement,

[78chris]

Bonjour à tous

Quelle version Excel utilises-tu ?

On demande de le préciser dans l'en-tête du fil car les solutions possibles en dépendent

A noter que ton exemple est erroné ou les règles sont à compléter car basé sur l'ordre des lignes mais si on analyse par personne et par date, Yves a 5 validations du 28/11/2018 au 07/01/2019...

Est-il normal de voir Clotilde et Clhotilde ?

[Romain_dbs]

Bonjour,

J'utilise la version Excel 2010.

Ah oui, c'est un exemple et j'avais mal renseigné la valeur pour Yves. Je corrige ça de suite.
Merci pour la remarque.

exemple.xlsx

[ARTURO83]

Bonjour,

Exemple en I8 à tirer vers le bas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS($A$2:$A$2000;$F8;($D$2:$D$2000);">="&$G8;($D$2:$D$2000);"<="&$H8;$C$2:$C$2000;1)

Pour une question de clarté et de simplicité j'ai créer une colonne qui reprend les dates seules (sans les heures), les calculs se font sur cette colonne. Attention, dans la liste des noms, plusieurs d'entr'eux comportent des espaces à la fin ce qui risque de donner de faux résultats.
Pièce jointe 539178

Le fichier
Pièce jointe 539182

Cdlt

[78chris]

RE à tous

Et pour Clhotilde ?

Faisable par PowerQuery intégré à partir de 2016, en add on à partir de 2010.

Exemple


Peux-tu télécharger l'add on chez Microsoft et l'installer ?

@Arturo : il faut calculer les dates de chaque groupe de jours consécutifs...

[Romain_dbs]

Bonjour @78chris,

Je ne savais pas que l'on pouvait installer Power Query sur Excel 2010. Une fois que je l'ai installé comment est-il possible d'obtenir le résultat souhaité ?

Merci @ARTURO83, je regarde votre fichier de suite, et je vous fait un retour si cela fonctionne.

[78chris]

Bonjour

• Supprimer les lignes 1302, 1304 à 1314
• Mettre la source sous forme de tableau structuré, nommé Data (en remplacement de Tableau1)
• Activer, si nécessaire la ligne des Totaux du Tableau
• Puis onglet PowerQuery, A partir d'un tableau : ce qui ouvre PowerQuery : a gauche les requêtes, Data pour l'instant, au centre les données, à droite les étapes de la requête en cours.
  Il y en a deux affichées, Source et Type Modifié
• Sélectionner la colonne date, onglet Accueil, Type de données, Date : confirmer le remplacement
• Si la ligne des Totaux du tableau est activée : filtrer la colonne Acteur, Filtre textuel, non égal à Total
• Trier par Acteur et date
• Onglet Ajouter une colonne, Colonne d'Index
• A nouveau Onglet Ajouter une colonne, Colonne d'Index à dérouler, A partir de 1
• Accueil, Fusionner les requêtes : en seconde requête sélectionner Data, dans le première sélectionner la colonne Index, dans la seconde Index.1, type de jointure Externe Gauche
• Cliquer sur la double flèche près du titre de la dernière colonne : décocher tout sauf Acteur et Etude Oui/Non
• Onglet Ajouter une colonne, Colonne personnalisée : nom Periode, formule
  

  Code :

  Sélectionner tout - Visualiser dans une fenêtre à part

  1
  2
  if [#"Etude Oui/Non"]=1 and [Acteur.1]=null then 1 
  else if [Acteur]=[Acteur.1] and [#"Etude Oui/Non"]=1 and [#"Etude Oui/Non.1"]=0 then [Index] else if [#"Etude Oui/Non"]=0 then 0 else null

• sélectionner la colonne Periode, clic doit sur le titre, Remplir, vers le bas
• filtrer la colonne Periode pour ne pas afficher 0
• sélectionner les colonnes Acteur, Periode onglet Transformer, Regrouper par
  Paramétrer les 3 opérations suivantes :
  - Nombre de validations, somme, Etude Oui/Non
  - Periode debut, Min, date
  - Periode fin, Max, date
• trier par Periode début
• supprimer la colonne Periode
• glisser/déplacer les colonnes comme souhaitées
• sortir par Fermer et charger dans, Table et choisir l'emplacement voulu