Microsoft 365 Compilation de plusieurs tableau en 1 seul sans calculs

Anne13 · 22 Octobre 2021

Bonjour,
je cherche à compiler des données de plusieurs onglets dans un même tableau, mais sans faire de calcul. Je souhaite juste reconstituer un tableau à partir de données éparpillées dans d'autres onglets.
Par exemple, j'ai un suivi de paramètres de fabrication de produit toujours présenté à l'identique mais avec un onglet par lot, je souhaite avoir un tableau reprenant les paramètres de fabrication pour tous les lots.
Je pensais passer en vba mais je me dis qu'il y a peut être une fonctionnalité d'excel que je ne connais pas, je ne maitrise pas power query/power pivot et mes données ne sont pas sous forme de tableau excel...
Je vous mets un fichier exemple...
L'objectif final est de pouvoir faire des analyses statistiques de ce paramètres.
Merci pour vos idées !
Bonne Journée
Anne

job75 · 22 Octobre 2021

Bonjour Anne13, sylvanu,

Les données pouvant être un peu partout une solution par formules ne convient pas.

Voyez le fichier joint et cette macro dans le code de la feuille "Compilation" :

VB:

Private Sub Worksheet_Activate()
Dim f, ncol%, d As Object, j%, c As Range, x$, y$, z$, n&, nn&, a(), b(), i&
f = Array("Lot 1", "Lot 2") 'liste des feuilles à traiter
ncol = UBound(f) + 3
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For j = 3 To ncol
    For Each c In Sheets(f(j - 3)).UsedRange
        If IsNumeric(CStr(c)) Then
            x = "": y = ""
            If c.Column > 2 Then x = c(1, -1).MergeArea(1)
            If c.Column > 1 Then y = c(1, 0).MergeArea(1)
            If y = ""...

sylvanu · 22 Octobre 2021

Bonjour Anne,

1- Si des les feuilles Lot vous pouviez copier les valeurs Titres PA et Poids en colonne C et D, une solution simple en PJ avec :

VB:

=RECHERCHEV($C4;INDIRECT("'"&Tableau1[[#En-têtes];[Lot 1]]&"'!C:D");2;FAUX)

Indirect reconstitue une adresse avec comme nom de feuille le titre de la colonne.

2- Sinon dans la formule on peut faire des exceptions pour ces deux paramètres mais cela va compliquer la formule.
A vous de dire si cela est possible ou non.

Anne13 · 22 Octobre 2021

Bonjour sylvanu,
merci pour la réponse.
Les données sont éparpillées un peu partout sur la feuille, je ne vous ai mis qu'un exemple simplifié, et je ne peux pas toucher à cette mise en page ( ni mettre sous forme de tableau grrr)
Par contre en utilisant indirect avec directement l'adresse de la cellule de résultat ça pourrai être une solution... Je vais creuser , je n'utilise que rarement cette fonction, c'est une bonne occasion de regarder son fonctionnement ;-)
Merci

sylvanu · 22 Octobre 2021

Fonction INDIRECT - Support Microsoft

Comment utiliser la fonction INDIRECT dans Excel pour modifier la référence à une cellule dans une formule sans modifier la formule elle-même.

support.microsoft.com

Anne13 · 22 Octobre 2021

je ne trouve pas comment donner une adresse seule sans passer par une recherchev ... Mais de toutes façons il faudrait que je crée au préalable toute les colonnes avec les noms des lots. Je pense que le vba sera plus facile... je crée les colonnes avec le noms des onglets, puis je complète les valeurs en allant les chercher dans chaque onglet à leur place.
je pensais qu'il y aurai des possibilités entre la consolidation de données ou la création de relations ou autre mais je ne vois pas comment.
En tous cas merci sylvanu, je range la fonction indirect dans un coin de mon cerveau, elle me servira un jour c'est sur !

sylvanu · 22 Octobre 2021

On peut le faire avec une fonction perso comme en PJ, la syntaxe est :

Code:

=ChercheValeur(Nom de la feuille, Paramètre)

Mais cela suppose que les paramètres soient exactement comme dans les feuilles, en particulier j'ai remplacé Poids par "grammes " .. avec un espace.
La fonction est :

Code:

Function ChercheValeur(NomFeuille As String, Parametre)
With Sheets(NomFeuille)
    tablo = .[B7:R25].CurrentRegion.Value  ' Plage à adapter
    For i = 1 To UBound(tablo)
        For j = 1 To UBound(tablo, 2)
            If tablo(i, j) = Parametre Then
                Select Case Parametre
                    Case "Titre PA( g/l) UCC": ChercheValeur = tablo(i, j + 2)
                    Case "grammes ": ChercheValeur = tablo(i, j - 1)
                    Case Else: ChercheValeur = tablo(i, j + 1)
                End Select
                Exit Function
            End If
        Next j
    Next i
End With
End Function

L'avantage de la fonction versus une macro est que quand on ajoute une feuille il n'est pas utile de reprendre le code.

job75 · 22 Octobre 2021

Bonjour Anne13, sylvanu,

Les données pouvant être un peu partout une solution par formules ne convient pas.

Voyez le fichier joint et cette macro dans le code de la feuille "Compilation" :

VB:

Private Sub Worksheet_Activate()
Dim f, ncol%, d As Object, j%, c As Range, x$, y$, z$, n&, nn&, a(), b(), i&
f = Array("Lot 1", "Lot 2") 'liste des feuilles à traiter
ncol = UBound(f) + 3
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For j = 3 To ncol
    For Each c In Sheets(f(j - 3)).UsedRange
        If IsNumeric(CStr(c)) Then
            x = "": y = ""
            If c.Column > 2 Then x = c(1, -1).MergeArea(1)
            If c.Column > 1 Then y = c(1, 0).MergeArea(1)
            If y = "" Then y = c(1, 2)
            If x = y Then x = ""
            If x = "" Then x = "finale" 'pourquoi ne pas laisser vide ?
            z = x & y
            If Not d.exists(z) Then
                n = n + 1
                d(z) = n 'mémorise le numéro de ligne
                ReDim Preserve a(1 To ncol, 1 To n) 'tableau transposé
            End If
            nn = d(z) 'récupère le numéro de ligne
            a(1, nn) = x
            a(2, nn) = y
            a(j, nn) = c
        End If
Next c, j
'---transposition---
If n Then
    ReDim b(1 To n, 1 To ncol)
    For i = 1 To n
        For j = 1 To ncol
            b(i, j) = a(j, i)
    Next j, i
End If
'---restitution---
With ListObjects(1).Range
    If .Columns.Count > ncol Then .Cells(1, ncol + 1).Resize(, .Columns.Count - ncol) = ""
    For j = 3 To ncol: .Cells(1, j) = f(j - 3): Next j
    If n Then .Cells(2, 1).Resize(n, ncol) = b
    If .Rows.Count > n + 1 Then .Rows(n + 2).Resize(.Rows.Count - n - 1).ClearContents 'RAZ en dessous
    .ListObject.Resize .Resize(IIf(n, n + 1, 2), ncol) 'redimensionnement du tableau
End With
End Sub

Elle se déclenche quand on active la feuille.

Les valeurs numériques sont recherchées dans les feuille listées dans le tableau f.

Edit : pour tester j'ai recopié les tableaux B8: D19 sur 24 000 lignes.

Chez moi sur Excel 2019 la macro s'exécute en 3 secondes.

A+

Anne13 · 26 Octobre 2021

Bonjour Sylvanu, Job75,
merci pour vos propositions.
Juste une question, vous êtes tous les 2 partis sur une recherche de l'intitulé, moi je serai plutôt partie sur la copie de la valeur avec son adresse, mais c'est peut être une mauvaise idée ...
Je vais rester sur du vba, je suis plus à l'aise qu'avec les fonctions, mais ça m'a encore une fois fait voir d'autres façons de penser, toujours utile !!
Merci de vos réponses,
Bonne journée

VB:

Sub compil()
Dim f As Worksheet
Dim ref, col As String
Dim i As Long
i = 4
For Each f In ActiveWindow.SelectedSheets
    ref = f.Name
    
    
    
  With Worksheets("Compilation")
 
  col = Cells(1, i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  col = CStr(Left(col, Len(col) - 1))
    .Range(col & "3") = ref
    .Range(col & "4") = "='" & ref & "'!d8"
    .Range(col & "5") = "='" & ref & "'!d9"
    .Range(col & "6") = "='" & ref & "'!d10"
    .Range(col & "7") = "='" & ref & "'!d11"
    .Range(col & "8") = "='" & ref & "'!d12"
    .Range(col & "9") = "='" & ref & "'!d13"
    .Range(col & "10") = "='" & ref & "'!d14"
    .Range(col & "11") = "='" & ref & "'!d15"
    .Range(col & "12") = "='" & ref & "'!d16"
    .Range(col & "13") = "='" & ref & "'!d17"
    .Range(col & "14") = "='" & ref & "'!d18"
    .Range(col & "15") = "='" & ref & "'!d19"
    .Range(col & "16") = "='" & ref & "'!i20"
    .Range(col & "17") = "='" & ref & "'!m22"
    
    
   End With
 
    i = i + 1
Next f

End Sub

job75 · 26 Octobre 2021

Bonjour Anne13, le forum,

Anne13 à dit:
Juste une question, vous êtes tous les 2 partis sur une recherche de l'intitulé,

Non, moi je recherche les valeurs numériques, la 9ème ligne de la macro est claire.

A+

Anne13 · 26 Octobre 2021

job75 à dit:
Bonjour Anne13, le forum,

Non, moi je recherche les valeurs numériques, la 9ème ligne de la macro est claire.

A+

oui, c'est très clair, ce que j'avais moins bien compris c'est le fonctionnement pour récupérer l'intitulé. C'est très efficace. Le seul hic c'est que j'ai des données dans l'autre sens (intitulé au dessus et non à coté). Je vais regarder le fonctionnement de ton code. Je viens aussi de découvrir les symboles pour la déclaration de variables. Chaque échange dans ce forum peret d'apprendre encore, merci beaucoup !
Bonne journée

job75 · 26 Octobre 2021

Anne13 à dit:
Le seul hic c'est que j'ai des données dans l'autre sens (intitulé au dessus et non à coté).

Bah c'est nouveau ça, faut pas pousser grand-mère dans les orties, débrouillez-vous.

Si ce que vous voulez récupérer est à 10 km VBA ne le saura jamais

Anne13 · 26 Octobre 2021

job75 à dit:
Bah c'est nouveau ça, faut pas pousser grand-mère dans les orties, débrouillez-vous.

Si ce que vous voulez récupérer est à 10 km VBA ne le saura jamais

Ah mais oui, j'avais bien l'intention de me débrouiller, aucuns souci. J'avais mis un exemple simplifié, la question de base étant de savoir si il y avait d'autres méthodes que le vba ou les fontions pour traiter ce genre de cas.
J'ai ma réponse avec en bonus une piste vers les fonctions que j'utilise peu et une macro super efficace que je vais essayer de m'approprier, le but étant aussi d'apprendre à se débrouiller !
Pour ce qui est des données à 10km je suis bien d'accord il y en a en vertical, en horizontal, avec et sans intitulés ... c'est pas simple mais je n'ai pas de levier sur ça.
Merci pour votre aide et désolé si vous avez cru que je vous demandais une solution clef en main en ne vous donnant que la moitié des infos ...

Microsoft 365 Compilation de plusieurs tableau en 1 seul sans calculs

Anne13

XLDnaute Nouveau

Pièces jointes

job75

sylvanu

XLDnaute Barbatruc

Pièces jointes

Anne13

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Fonction INDIRECT - Support Microsoft

Anne13

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Anne13

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Anne13

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Anne13

XLDnaute Nouveau

Discussions similaires