XL 2016 Consolidation de données de plusieurs onglets en évitant somme.si

[Merlin258413]

Bonjour
j'ai ce fichier avec plusieurs onglets je désire faire une consolidation de chaque onglet (sauf celui intitulé congés) par secteur dans l'onglet secteur.

En colonne A de chaque onglet vous avez le secteur et je veux consolider les chiffres de la colonne O à Z dans l'onglet secteur.

Est ce qu'il aurait un moyen autre que de faire autant de somme.si que d'onglet ?

En vous remerciant pour votre aide

Excellente journée

 

[chris]

Bonjour

Pense à mettre ton profil à jour sui tu 2016 et plus 2013

Ci-joint 2 solutions :

• l'une par formules sans modifier ton ficher à part ajouter une liste nommée des onglets à consolider
• l'autre utilisant les possibilités de 2016 (qu'il est dommage d'utiliser comme Excel 95...) avec :
  • mise sous forme de tableaux structurés des plages concernées et nommage du tableau identique à l'onglet (un peu de lecture sur les tableaux Ce lien n'existe plus)
    
  • consolidation auto de tous les tableaux structurés du classeur via PowerQuery (intégré à Excel 2016)
    
  • TCD utilisant la consolidation

 

[Merlin258413]

Bonjour Chris Merci pour les réponses
Concernant la possibilité avec PowerQuery elle est fabuleuse mais je ne sais pas comment l'installer j'ai power pivot mais je trouve pas query

Merci

 

[chris]

RE

PowerQuery est déjà intégré dans 2016 : rien à installer. Pas d'onglet spécifique car on y accède via Données.

Pour voir le détail de la requête bouton "Afficher les requêtes" de l'onglet Données puis double clic sur la requête...

 

[Merlin258413]

Super merci je vais essayer de trouver un tuto pour comprendre la création de cette requête

 

[job75]

Bonjour Merlin258413, chris,

Une solution classique en VBA.

Clic droit sur l'onglet SECTEUR => Visualiser le code puis collez ces 2 macros :

Private Sub Worksheet_Activate()
Worksheet_Change [A:A]
End Sub

Private Sub Worksheet_Change(ByVal R As Range)
Set R = Intersect(R, [A9].CurrentRegion.Columns(1))
If R Is Nothing Then Exit Sub
Dim x$, a(), mois%, w As Worksheet
For Each R In R 'si entrées ou effacement multiples
    x = R
    If x <> "" Then
        ReDim a(1 To 12) 'tableau, plus rapide
        For mois = 1 To 12
            For Each w In Worksheets
                If w.Name <> Me.Name And w.Name <> "Conges" Then _
                a(mois) = a(mois) + Application.SumIf(w.Columns(1), x, w.Columns(2 + mois)) + Application.SumIf(w.Columns(1), x, w.Columns(14 + mois))
        Next w, mois
        R(1, 2).Resize(, 12) = a 'restitution
    End If
Next
End Sub

Comme on le voit la fonction SOMME.SI est utilisée.

La 1ère macro s'exécute quand on active la feuille, tout le tableau est recalculé.

La 2ème macro s'exécute quand on modifie ou valide des cellules en colonne A de la feuille.

Fichier joint.

A+

 

[Merlin258413]

Juste pour bien comprendre et si vous voulez bien repondre une fois mes tableaux nommés
Quelles sont les étapes pour intégrés ces tableaux dans power query
Je suppose :
1- Nouvelle requête
2- A partir d'un fichier excel ...

 

[Merlin258413]

MErci à vous 2 et bonne journée

 

[Merlin258413]

job 75 si maintenant je veux changer la somme ou la consolidation des colonnes C à N qu'est qui va changer dans la macro
je suppose que cela se passe
" a(mois) = a(mois) + Application.SumIf(w.Columns(1), x, w.Columns(2 + mois)) + Application.SumIf(w.Columns(1), x, w.Columns(14 + mois))
Je suis désolé mais je suis novice en vba
merci

 

[chris]

Re à tous

Juste pour bien comprendre et si vous voulez bien repondre une fois mes tableaux nommés
Quelles sont les étapes pour intégrés ces tableaux dans power query
Je suppose :
1- Nouvelle requête
2- A partir d'un fichier excel ...

Ici comme on veut automatiser pour prendre automatiquement l'ensemble des tableaux sans avoir à les déclarer si on en ajoute au fil du temps, on part d'une requête vierge :

• Données, nouvelle requête, A partir d'autres sources, Requête Vide. Cela ouvre l'interface PowerQuery.
• Il y a une barre de formule : taper
  =Excel.CurrentWorkbook()
  cela affiche 2 colonnes Content et Name.
  
• Cliquer sur la double flèche près du titre de Content : on a la synthèse
  
• Supprimer les colonnes Type et Name
• Sélectionner les colonnes Janvier à Décembre : Onglet Transformer, Dépivoter les colonnes
  puis renommer "Attribut" en "Période"
• Sortir et valider par le 1er bouton en haut à gauche de Accueil : Fermer et Charger dans, choisir Connexion seulement.

Ne reste plus qu'à construire le TCD en indiquant comme source la requête comme source externe.

Il suffira d'utiliser la bouton Données, Actualiser tout pour que tout soit à jour au fil du temps sachant qu'on peut aussi régler la requête et le TCD pour une MAJ auto à l'ouverture...

 

[Merlin258413]

Bonjour je vous remercie pour l'aide que vous m'avez apporté. Les réponses sont toutes parfaites et cela me donne envie d'explorer et exploiter power query
Excellent we

 

[Merlin258413]

Re à tous

Ici comme on veut automatiser pour prendre automatiquement l'ensemble des tableaux sans avoir à les déclarer si on en ajoute au fil du temps, on part d'une requête vierge :

• Données, nouvelle requête, A partir d'autres sources, Requête Vide. Cela ouvre l'interface PowerQuery.
• Il y a une barre de formule : taper
  =Excel.CurrentWorkbook()
  cela affiche 2 colonnes Content et Name.
  
• Cliquer sur la double flèche près du titre de Content : on a la synthèse
  
• Supprimer les colonnes Type et Name
• Sélectionner les colonnes Janvier à Décembre : Onglet Transformer, Dépivoter les colonnes
  puis renommer "Attribut" en "Période"
• Sortir et valider par le 1er bouton en haut à gauche de Accueil : Fermer et Charger dans, choisir Connexion seulement.

Ne reste plus qu'à construire le TCD en indiquant comme source la requête comme source externe.

Il suffira d'utiliser la bouton Données, Actualiser tout pour que tout soit à jour au fil du temps sachant qu'on peut aussi régler la requête et le TCD pour une MAJ auto à l'ouverture...

 

[Merlin258413]

Re à tous

Ici comme on veut automatiser pour prendre automatiquement l'ensemble des tableaux sans avoir à les déclarer si on en ajoute au fil du temps, on part d'une requête vierge :

• Données, nouvelle requête, A partir d'autres sources, Requête Vide. Cela ouvre l'interface PowerQuery.
• Il y a une barre de formule : taper
  =Excel.CurrentWorkbook()
  cela affiche 2 colonnes Content et Name.
  
• Cliquer sur la double flèche près du titre de Content : on a la synthèse
  
• Supprimer les colonnes Type et Name
• Sélectionner les colonnes Janvier à Décembre : Onglet Transformer, Dépivoter les colonnes
  puis renommer "Attribut" en "Période"
• Sortir et valider par le 1er bouton en haut à gauche de Accueil : Fermer et Charger dans, choisir Connexion seulement.

Ne reste plus qu'à construire le TCD en indiquant comme source la requête comme source externe.

Il suffira d'utiliser la bouton Données, Actualiser tout pour que tout soit à jour au fil du temps sachant qu'on peut aussi régler la requête et le TCD pour une MAJ auto à l'ouverture...

bonjour Chris je ne vois pas le bouton qui permet de faire dépivoter les colonnes
Enfin peux tu me dire comment supprimer les lignes qui sont en vide 'null"

Merci

 

[chris]

Re

Selon la largeur de l'écran le bouton n'affiche par forcément les libellés : au milieu de l'onglet Transformer , en haut à gauche de la zone intitulée (sur 2010) N'importe quelle colonne

Null veut dire "cellule" vide donc n'apparaitra pas à l'utilisation de la requête.
Normalement il n'y a pas de ligne entièrement en null... sauf si tu as mal configuré les tableaux sources , ce qui doit être corrigé en amont...

Pour rappel un tableau structuré ne doit jamais contenir de ligne vide : il s'agrandit automatiquement en cours de saisie...

 

[Merlin258413]

ok merci cela fonctionne
Concernant les lignes entièrement nulle c'est pour laisse de la place pour d'éventuelle saisie et j'ai donc mis une mise en forme tableau avec une plage trop grande
Merci Beaucoup