Discussion :

[droog]

Bonjour à tous,

J'ai un petit souci avec un feuille de calcul, dans laquelle j'affiche des données provenant d'une base SQL.
La requête me renvoie 4 colonnes. J'y ai ajouté 3 colonnes supplémentaires E, F , G dans lesquelles je fait un cumul pour chacune des colonnes B, C, D



Mon problème est que lorsque je récupère des données supplémentaires, les cellules de mes 3 dernières colonnes ne reprennent pas automatiquement mon calcul. Il faut que j'effectue manuellement une recopie des cellules jusqu'à la fin du tableau, ce qui est génant et pas trés souple.

Est-ce qu'il y a une solution pour que les formules soient automatiquement appliquées peu importe la taille du tableau ?

merci d'avance pour vos tuyaux.

droog

[Daniel.C]

Bonjour,

Essaie :

Code :

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

=SI($A40<>"";formule;"")

que tu reproduis sur un nombre conséquent de lignes. Sinon, une solution par macro.

[droog]

Bonjour Daniel

Merci pour ta réponse. J'ai testé ta solution qui est tout a fait logique, mais le problème dans ce cas, est que même si je reproduis les formules de manière anticipée, aprés rafraichissement du lien avec la base de données, les cellules des 3 dernières colonnes situées au delà de la fin du tableau sont réinitialisées et donc plus de formule..

Avant:


Aprés rafraichissement du lien:



Je ne sais pas si il y a un moyen de renforcer le "lien" avec les 3 dernières colonnes pour qu'elles soient réellement synchronisées avec le tableau.

[Daniel.C]

Bonjour,

Hyper bizarre. Et les formules au dessus de L77 ne sont pas effacées ? Il va falloir procéder par macro. Est-ce que tu peux me donner les formules à ajouter ?

[danixdb]

Bonjour droog, Le Forum

Ne serait-il pas possible d'ajouter dans la requête SQL les trois colonnes de calcul dont tu as besoin ?

Slts

[78chris]

Bonjour

Tu ne précises pas la version Excel utilisée.

Depuis 2007, les données de requêtes liées sont importées dans un tableau.

Dans un tableau, les formules des colonnes adjacentes s'adapteront

Pour que les formules glissantes s'adaptent correctement, il faut utiliser INDIRECT ou DECALER plutôt que l'adressage classique

Code :

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

=SOUS.TOTAL(9;INDIRECT("$B$2:$B"&LIGNE()))

NB : la taille du tableau doit être ajustée automatiquement à celle des données : cela ne semble pas être le cas sur ta 1ère image.

[droog]

Bonjour
Daniel, les formules des cellules de mes 3 dernières colonnes correspondant à des données existantes dans les 4 premières colonnes ne sont pas effacées.

C'est vrai que c'est bizarre, mais j'imagine que le contenu des colonnes que j'ai rajoutées ne peuvent pas être automatiquement ajustées.

Je fais un test en ce moment :
Je supprime les données au delà du 2/09, et je rafraîchis j'obtiens ceci :

Si je réinsère les données dans la table et que je rafraîchis, non seulement mes 3 colonnes ne sont pas alimentées, mais la dernière cellule contenant la formule fait référence à la dernière cellule de mon tableau... :


Pour répondre à danixdb, oui j'avais déjà pensé à cela, et je l'avais fait directement dans ma requête, sauf que le besoin est de pouvoir filtrer par période dans Excel, et d'avoir le cumul sur cette période sélectionnée..

Pour la version Excel c'est effectivement 2007, et ta remarque est intéressante 78chris, cela demande à être testé sur mon tableau.
Je fais les tests et je reviens

[droog]

J'ai utilisé dans ma formule INDIRECT, et effectivement c'est plus efficace, je ne rencontre pas de souci au niveau de mes dernières lignes. Par contre j'ai toujours mon problème de réplication des formules dans les nouvelles lignes.

Les options de mon tableau sont :

[Daniel.C]

Il faudrait peut-être entrer des formules structurées (les formules qui vont bien avec les tableaux). Pour cela, j'aurais besoin des entêtes du tableau.

[droog]

[droog]

J'ai d'ailleurs eu la confirmation sur le site de Microsoft que les colonnes adjacentes devaient être automatiquement mises à jour.. En insérant une colonne ça fonctionne, mais si de nouvelles données sont disponibles via la requête, pas de formules dans les nouvelles cellules des colonnes adjacentes...
J'en perds mon latin..

[Daniel.C]

Et moi, j'ai l'air malin avec mes formules structurées qui ne fonctionnent que si les données utilisées sont sur la même ligne ou occupent une colonne entière... Reste la solution macro :

Dans une cellule inoccupée de la feuille, mets :

Code :

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

=SOUS.TOTAL(109;A:A)

Cette formule va servir de déclencheur à la macro suivante (à mettre dans le module de la feuille :

Code :

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

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Calculate()
    Dim Ligne As Long
    Application.EnableEvents = False
    Ligne = Cells(Rows.Count, 1).End(xlUp).Row
    [E2].Formula = "=SUBTOTAL(109,B2:B" & Ligne & ")"
    [F2].Formula = "=SUBTOTAL(109,C2:C" & Ligne & ")"
    [G2].Formula = "=SUBTOTAL(109,D2:D" & Ligne & ")"
    [E2:G2].AutoFill Range("E2:G" & Ligne)
    Application.EnableEvents = True
End Sub

[78chris]

Re

Tu enregistres bien au format xlsx ?

Tes colonnes sont bien incluses dans le tableau ?

[droog]

Oui j'enregistre bien au format xlsx, et mes colonnes sont bien incluses dans le tableau. Je les ai d'ailleurs supprimées puis recréées et lorsque je met ma formule dans la première cellule d'une colonne, les données sont automatiquement calculées jusqu'à la fin de la colonne.
Etrange que ça ne fonctionne pas correctement.

J'ai testé la solution de Daniel, et c'est plutôt efficace, même si c'est dommage de passer par une solution plus complexe. Le seul problème est que je ne peux pas fournir de fichier avec macros.
De plus, les formules sont bien répliquées sur l'ensemble des colonnes, mais la formule n'est pas appliquée correctement. Il faut que je clique dans la première cellule et que je valide avec Entrée pour que le calcul se fasse sur toute la colonne.

Merci encore pour votre aide. Je continue mes tests

[Daniel.C]

Bonjour,

Code :

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

mais la formule n'est pas appliquée correctement

C'est une question de format de cellule, ça peut s'arranger facilement, mais si tu n'as pas droit aux macros, pas la peine de se fatiguer