Recherche sur plusieurs onglets et récap dynamique

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion Chubby
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
Chubby

Chubby

XLDnaute Impliqué
Bonjour à tous,

Me revoilà en proie avec un petit problème dont je n'arrive pas à me sortir.
En quelques mots j'ai des onglets reprenant un "état de cave" par date. Je voudrais en tirer une feuille récapitulative soit par cuve, soit par code.
Les fonctions "recherchev(indirect" sont apparemment les plus appropriées, sinon que j'obtiens alors une seule donnée (la 1ère rencontrée par "recherchev".
Il doit y avoir une manière dynamique de faire suivre les lignes au cours des dates (des onglets).
Si vous pouviez me mettre sur une trace. Merci à vous.
Je vous joins un bout de fichier pour mieux comprendre ce que je recherche.

Bonne après midi.
N
 

Pièces jointes

Trier par date Trier par votes
Re : Recherche sur plusieurs onglets et récap dynamique

Bonjour

Tu peux compiler tes données via une requête MSQuery
Code: 
SELECT *, '20.10'
FROM `T:\TEMP\EssaiOnglets.xlsx`.`'20#10$'` `'20#10$'`
where Dénomination  <>null
union all SELECT *, '21.10'
FROM `T:\TEMP\EssaiOnglets.xlsx`.`'21#10$'` `'21#10$'`
where Dénomination  <>null
union all 
SELECT *, '23.10'
FROM `T:\TEMP\EssaiOnglets.xlsx`.`'23#10$'` `'23#10$'`
where Dénomination  <>null

Puis ensuite filtrer par filtre auto ou avancé

ou inclure le filtre dans la requête...
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Bonjour Chris,

Merci pour cette solution. C'est certainement un super outil ce MS Query. Je crains cependant de ne pas trop le maitriser. Au delà ça veut dire qu'il me faut entrer tous les noms d'onglets "a mano".
Je vais quand même essayer de creuser.
Merci à toi encore pour ta réactivité et ton aide.
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Bonjour Chubby, chris,

Voyez le fichier joint avec ces 2 macros :

Code: 
Private Sub Worksheet_Activate()
Dim ncol%, colref%, x$, w As Worksheet, t, i&, n&, rest(), j%
ncol = 10 'nombre de colonnes des tableaux source
colref = IIf([B1] = "Cuve", 1, 7)
x = IIf([B1] = "", "", IIf([B1] = "Cuve", "C" & [B2], "*" & [B3] & "*"))
Application.ScreenUpdating = False
Rows("2:3").Hidden = False
If [B1] = "Cuve" Then Rows(3).Hidden = True _
  Else: If [B1] = "Code" Then Rows(2).Hidden = True
Rows("6:" & Rows.Count).Delete 'RAZ
For Each w In Worksheets
  If w.Name <> Me.Name Then
    t = w.[A1].CurrentRegion.Resize(, ncol).Value2
    For i = 2 To UBound(t)
      If x = "" Or t(i, colref) Like x Then
        n = n + 1
        ReDim Preserve rest(1 To ncol + 1, 1 To n)
        rest(1, n) = w.Name
        For j = 1 To ncol
          rest(j + 1, n) = t(i, j)
        Next j
      End If
    Next i
  End If
Next
'---restitution, tri et bordures---
If n = 0 Then Exit Sub
With [A6].Resize(n, ncol + 1)
  .Value = Application.Transpose(rest)
  .Sort .Columns(1), xlAscending, Header:=xlNo
  For j = 7 To 10
    .Borders(j).Weight = xlMedium 'contour
  Next j
  .Borders(xlInsideVertical).Weight = xlThin
  .Borders(xlInsideHorizontal).Weight = xlHairline
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1:B3]) Is Nothing Then Worksheet_Activate
End Sub
Choisir d'abord en B1 ce qui est recherché.

Noter que la colonne A est au format Texte.

A+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Re,

Je crois comprendre que les noms des onglets sont formatés <jour>.<mois>.

Ce n'est pas très bon pour le classement, il vaudrait mieux <mois>.<jour> comme ceci :

01.01 01.02 01.03 ... 10.21. 10.22 10.23 ... 12.29 12.30 12.31

A+
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

salut

chris à dit:
Re
Sinon il serait plus simple de faire le contraire : tout saisir dans un onglet puis éclater par date si besoin (un filtre peut suffire)
.
Cliquez pour agrandir...

C'est ce que j'aurais fait Chris 😀 !

Si non avec ce que j'ai compris* et un peu de VBA ...

* Pas eu le temps d'analyser ta proposition Job mais on n'obtient pas la même chose 😱. Désolé si je me suis trompé.
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Re, hello Si...

Une solution avec le filtre automatique et des tableaux Excel dans toutes les feuilles :

Code: 
Private Sub Worksheet_Activate()
Dim P As Range, lig&, w As Worksheet, h&, col%, critere$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set P = ListObjects(1).Range
P.AutoFilter
P.Offset(1).ClearContents 'RAZ
lig = 2
'---transfert des valeurs des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then
    With w.ListObjects(1).DataBodyRange
      h = .Rows.Count
      P(lig, 1).Resize(h) = w.Name
      P(lig, 2).Resize(h, .Columns.Count) = .Value
      lig = lig + h
    End With
  End If
Next
'---cellules B1 B2 B3---
Rows("2:3").Hidden = False
If [B1] = "Cuve" Then Rows(3).Hidden = True _
  Else If [B1] = "Code" Then Rows(2).Hidden = True
col = IIf([B1] = "Cuve", 2, 8)
critere = IIf([B1] = "Cuve", "C" & [B2], "*" & [B3] & "*")
'---mise en forme du tableau et filtrage---
ListObjects(1).Resize P.Resize(lig - 1) 'redimensionnement
With ListObjects(1).Range
  .Sort .Columns(1), xlAscending, Header:=xlYes 'tri
  .AutoFilter 1
  If [B1] <> "" And (col = 2 And [B2] <> "" Or col = 8 And [B3] <> "") _
    Then .AutoFilter col, critere
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1:B3]) Is Nothing Then Worksheet_Activate
End Sub
Elle doit être moins rapide que la solution de mon post #5 qui utilise des tableaux VBA.

Fichier joint.

Edit : notez qu'on peut sans problème déplacer les tableaux.

A+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

re

Salut Job, ma remarque du # 7 n'a plus lieu d'être 😉.

Dans la foulée j'avais transformé le fichier en faisant mienne la remarque de Chris 🙂.
J'ai pensé au nombre croissant de feuilles nécessaires pour chaque mise à jour.
Dans cette nouvelle configuration, un seul onglet suffit, celui où les saisies seront effectuées. A la rigueur, on pourrait en ajouter un pour isoler les jours mais un filtre selon les dates pourrait faire l'affaire. En plus la macro est très légère.
Code: 
Private Sub Worksheet_Change(ByVal R As Range)
  If R = "" Then [T].AutoFilter: Exit Sub
  If R.Address = "$B$1" Then [B2] = "": [T].AutoFilter 2, "C" & R
  If R.Address = "$B$2" Then [B1] = "": [T].AutoFilter 8, R & "*"
End Sub
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Re,

Par curiosité j'ai créé 1000 feuilles (identiques) nommées 15.01.01 15.01.02 ... 17.09.26.

Sur Win 8 - Excel 2013, la macro de mon post #8 (filtre automatique) s'exécute en 14 secondes.

Celle de mon post #5 (tableaux VBA) s'exécute entre 0,6 seconde (filtrage de 91) et 1,6 seconde (aucun filtrage).

A+
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Bonjour à tous, et à vous les experts,

Bonjour et un grand merci à vous les zexperts. Un peu tardifs mes remerciements, je pensais mon sujet résolu et j'essayais de gratter dans mon coin ... et en vain, sur la MS Query.
Je regarde cet après midi vos posts. Je les ai juste survolé. Je reviens vite vers vous pour vous dire ce qu'il en est.
Mais déjà je crois que c'est un gros boulot que vous avez fait là. Les macros ne sont pas trop ma tasse de thé je dois dire mais s'il faut passer par là plutôt que par des formules, je m'en satisferais bien entendu.

PS: je ne peux pas mettre les données à la queue leu leu sur la meme feuille. Ce serait plus simple pour l'exploitation au travers d'un filtre certainement mais moins pratique pour une lecture journalière.

Merci encore, et je reviens vous dire
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Bonjour à tous,

Chris, Job75, Si...Juste un super bravo. Surenchère de matière grise et en plus ça marche à tous les niveaux. Vraiment sympa de votre part d'avoir planché sur mon problème.
Je me sens quand même un peu penaud devant ces Rolls.
D'une part parce que je n'ai pas été présent et puis vous tapez vraiment haut, trop haut pour que je puisse maitriser le sujet. En cas de bug je me vois mal à le réparer puisque je suis dépassé.
Vous allez finir par croire que je pleure, non, non, pas du tout. Je vais essayer de mettre ça en forme dans le fichier dont je vous ai extrait quelques lignes et colonnes.
Si vous avez une idée par formules, je suis preneur bien sûr.
Merci encore à vous les excelents Exceliens. Un grand merci.
 
Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Re,

La macro du post #8 consolide en feuille "Récap" toutes les autres feuilles.

C'est je pense la meilleure solution car ainsi on peut faire ce qu'on veut dans cette feuille.

Et pour diminuer la durée d'exécution, il suffit de nouveau d'utiliser des tableaux VBA :

Code: 
Private Sub Worksheet_Activate()
Dim P As Range, ncol%, w As Worksheet, t, i&, n&, rest(), j%, col%, critere$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set P = ListObjects(1).Range
ncol = P.Columns.Count
P.AutoFilter 'RAZ
P.Offset(1).ClearContents 'RAZ
'---transfert des valeurs des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then
    t = w.ListObjects(1).Range.Resize(, ncol - 1).Value2
    For i = 2 To UBound(t)
      n = n + 1
      ReDim Preserve rest(1 To ncol, 1 To n)
      rest(1, n) = w.Name
      For j = 2 To ncol
        rest(j, n) = t(i, j - 1)
    Next j, i
  End If
Next w
'---cellules B1 B2 B3---
Rows("2:3").Hidden = False
If [B1] = "Cuve" Then Rows(3).Hidden = True _
  Else If [B1] = "Code" Then Rows(2).Hidden = True
col = IIf([B1] = "Cuve", 2, 8)
critere = IIf([B1] = "Cuve", "C" & [B2], "*" & [B3] & "*")
'---mise en forme du tableau et filtrage---
P.Offset(1).Resize(n) = Application.Transpose(rest) 'restitution
ListObjects(1).Resize P.Resize(n + 1) 'redimensionnement
With ListObjects(1).Range
  .Sort .Columns(1), xlAscending, Header:=xlYes 'tri
  .AutoFilter 1
  If [B1] <> "" And (col = 2 And [B2] <> "" Or col = 8 And [B3] <> "") _
    Then .AutoFilter col, critere
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1:B3]) Is Nothing Then Worksheet_Activate
End Sub
Avec 1000 feuilles la durée d'exécution est maintenant 0,98 seconde (0,89 seconde sans filtrage).

Fichier joint.

A+
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Recherche sur plusieurs onglets et récap dynamique

Re,

Il faut savoir que Application.Transpose ne peut pas traiter plus de 65536 lignes.

On l'évite donc avec ce code où rest() est un tableau 1048576 x 11 :

Code: 
Private Sub Worksheet_Activate()
Dim P As Range, ncol%, rest(), w As Worksheet, t, i&, n&, j%, col%, critere$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set P = ListObjects(1).Range
ncol = P.Columns.Count
ReDim rest(1 To Rows.Count, 1 To ncol)
P.AutoFilter 'RAZ
P.Offset(1).ClearContents 'RAZ
'---transfert des valeurs des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then
    t = w.ListObjects(1).Range.Resize(, ncol - 1).Value2
    For i = 2 To UBound(t)
      n = n + 1
      rest(n, 1) = w.Name
      For j = 2 To ncol
        rest(n, j) = t(i, j - 1)
    Next j, i
  End If
Next w
'---cellules B1 B2 B3---
Rows("2:3").Hidden = False
If [B1] = "Cuve" Then Rows(3).Hidden = True _
  Else If [B1] = "Code" Then Rows(2).Hidden = True
col = IIf([B1] = "Cuve", 2, 8)
critere = IIf([B1] = "Cuve", "C" & [B2], "*" & [B3] & "*")
'---mise en forme du tableau et filtrage---
P.Offset(1).Resize(n) = rest 'restitution
ListObjects(1).Resize P.Resize(n + 1) 'redimensionnement
With ListObjects(1).Range
  .Sort .Columns(1), xlAscending, Header:=xlYes 'tri
  .AutoFilter 1
  If [B1] <> "" And (col = 2 And [B2] <> "" Or col = 8 And [B3] <> "") _
    Then .AutoFilter col, critere
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1:B3]) Is Nothing Then Worksheet_Activate
End Sub
C'est même plus rapide : 0,80 seconde sur 1000 feuilles.

Fichier (2).

A+
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

J
  • Question Question
XL 2016 Formule de recherche de valeurs sur deux onglets en entête et en ligne
2
Réponses
22
Affichages
467
Jaykerz
J
E
  • Question Question
LibreOffice Calc Recherche sur plusieurs onglets pour constituer un tableau
Réponses
0
Affichages
243
evindi
E
J
  • Question Question
XL 2019 index equiv avec fonction ou sur plusieurs feuilles
Réponses
11
Affichages
829
job75
job75
didier09
  • Résolu(e)
XL 2021 Formule ou VBA pour récupération données sur plusieurs onglets
Réponses
11
Affichages
279
sylvanu
sylvanu
A
  • Résolu(e)
XL 2013 Récap de plusieurs onglets sur un seul
2 3
Réponses
34
Affichages
2 K
Staple1600
Staple1600
D
  • Question Question
XL 2021 Liste déroulante et RECHERCHEV
Réponses
5
Affichages
568
Dubuyz1
D
jeanmi
  • Résolu(e)
XL 2013 recherche de données sur plusieurs fichiers
Réponses
13
Affichages
813
dysorthographie
dysorthographie
W
  • Résolu(e)
XL 2019 Envoi de mails avec l'adresse secondaire et non l'adresse principale d'Outlook
2
Réponses
17
Affichages
583
TooFatBoy
TooFatBoy
F
  • Question Question
Microsoft 365 Problème RECHERCHEV avec plusieurs correspondances possibles pour une même valeur
2
Réponses
15
Affichages
759
floflatch22
F
G
  • Résolu(e)
Microsoft 365 Je dois chercher sur 2 onglets excel le prénom et nom de famille en mettant une formuel
Réponses
2
Affichages
421
job75
job75
A
  • Question Question
Microsoft 365 recherche multicritère de la valeur suivante
2
Réponses
29
Affichages
1 K
alexga78
A
S
  • Question Question
XL 2013 TCD sur plusieurs onglets
Réponses
6
Affichages
852
WTF
WTF
D
  • Question Question
XL 2016 Vba 😱 récap des relances indiquées dans plusieurs onglets de plusieurs tableaux
Réponses
2
Affichages
553
sylvanu
sylvanu
R
  • Résolu(e)
XL 2019 macro couper-coller sur plusieurs onglets
Réponses
9
Affichages
921
job75
job75
Jelose
  • Question Question
XL 2021 Dupliquer un tableau sur différents onglets pour un mois donné
Réponses
2
Affichages
711
Jelose
Jelose
P
  • Question Question
XL 2019 somme de plusieurs colonnes suivant un critère sur plusieurs onglets
Réponses
9
Affichages
998
patmarchi
P
Y
  • Résolu(e)
XL 2016 Excel - décaler/recherche/max sur plusieurs onglets
Réponses
2
Affichages
696
YanCad
Y
P
  • Question Question
XL 2013 Gestion de tableau à rallonge ou sur plusieurs onglets?
Réponses
3
Affichages
725
punk_sportif
P
T
  • Question Question
XL pour MAC Recherche de mots clefs dans une cellule Texte
Réponses
2
Affichages
1 K
Tsr75a
T
B
  • Question Question
XL 2013 Automatiser une formule sur les nouvelles lignes insérées en fonction d'une recherche sur les enregistrements
Réponses
9
Affichages
785
bedley
B
Retour