XL 2016 Faire une somme dans un tableau à double entrée

[Leo37]

Bonjour,

Après plusieurs tentative sans succès (index, Sommeprod, si, recherchev), je sollicite votre aide pour une trouver une formule qui serait capable de faire une somme d'après un tableau à double entrée (planning de travaux).

Dans l'onglet PL Jour GR Maille :
- en ligne, j'ai des activité de construction d'un bâtiment.
- en colonne, j'ai des Mois (M1, M2, ....)
- en valeur j'ai uniquement la valeur 1 (pour une journée de travail, dans les cellules rouge)

Je cherche à obtenir la somme des journées de travail par mois par activité. (onglet: tableau de résultat)

Si une âme charitable pouvait me donner un coup de pouce

Par avance Merci !!!
Léo

 

[vgendron]

Hello

la difficulté vient des lignes fusionnées..
il faut définir les plages nommées correctement
notamment TabData qui commence à la ligne 5 tandis que Liste_Libelle à la ligne 4

 

[Leo37]

Hello vgendron,

Merci pour cette solution !!!

il faudrait remonter le "Liste _Libelle" dans la présentation ?
je me suis douté que les cellules fusion devait posé problème et fait des tests en les défusionnent mais rien à y faire

 

[vgendron]

rien à modifier avec ma solution
toutes des "données" commencent en ligne 4
MAIS: du fait de la fusion des libellés sur 3 lignes, SEULE la première contient le libellé==> donc les lignes 4 /7 10 /13.....
le sommeprod va donc donner des true sur ces lignes, et des faux sur les autres
et sur le TabData (si on le fait commencer en ligne 4)== les vrai seront appliqués sur des lignes vides, puisque que sur les 3 lignes qui constituent le libellé, les 1 à addtionner sont sur la 2eme...
==> décaler TabData de 1 ligne pour le faire commencer à la ligne 5 permet de faire correspondre les VRAI aux 1 à additionner

 

[chris]

Bonjour à tous

Une solution PowerQuery, intégré à Excel

Données, actualiser tout quand la source change

 

[Leo37]

rien à modifier avec ma solution
toutes des "données" commencent en ligne 4
MAIS: du fait de la fusion des libellés sur 3 lignes, SEULE la première contient le libellé==> donc les lignes 4 /7 10 /13.....
le sommeprod va donc donner des true sur ces lignes, et des faux sur les autres
et sur le TabData (si on le fait commencer en ligne 4)== les vrai seront appliqués sur des lignes vides, puisque que sur les 3 lignes qui constituent le libellé, les 1 à addtionner sont sur la 2eme...
==> décaler TabData de 1 ligne pour le faire commencer à la ligne 5 permet de faire correspondre les VRAI aux 1 à additionner

Bonjour vgendron,

je suis en train d'essayer d'appliquer votre formule sur un autre fichier mais celle-ci ne fait pas la recherche sur le tableau de base de donnée pourriez m'indiquer comment aller chercher la source avec cette formule ?

Cordialement

 

[Leo37]

Bonjour à tous

Une solution PowerQuery, intégré à Excel

Données, actualiser tout quand la source change

Bonjour Chris,

Merci pour l'information, par contre je suis complétement largué sur l'utilisation .... Je vais faire une recherche sur le forum

Cordialement

 

[chris]

Bonjour

• Une plage nommée Data définie par formule pour trouver la plage utilise
• Un tableau structuré des lots pour conserver l'ordre dans le résultat

Si tu utilises Données, Requêtes et Connexions puis clic droit dans le volet à droite sur la requête Data, Modifier cela ouvre l'interface PowerQuery

A droite il y a toutes les étapes de la requête que l'on crée sans code, simplement en passant par les menus, clics droit et parfois dans la barre de formule

Si on clique sur une étape, on voit le détail dans la barre de formule et en double cliquant sur le petit rouage

Avec un tableau source plus normalisé, le requête aurait moins d'étapes : là il faut récupérer les dates et les valeurs.
Du fait des fusions dans la colonne des lots les valeurs ne sont pas alignées avec les libellés d'où n étapes de restructuration.

 

[vgendron]

Bonjour vgendron,

je suis en train d'essayer d'appliquer votre formule sur un autre fichier mais celle-ci ne fait pas la recherche sur le tableau de base de donnée pourriez m'indiquer comment aller chercher la source avec cette formule ?

Cordialement

Bonjour
Sans ton fichier, je ne peux pas vraiment t'aider..

 

[Leo37]

Bonjour
Sans ton fichier, je ne peux pas vraiment t'aider..

Bonjour,

Voici le fichier (les deux onglet rouge), après j'aurai aimé savoir le faire moi-même pour apprendre, si c'est possible et de façon à ne plus t'embêter avec ce genre de problème.

 

[vgendron]

Hello
pour modifier les plages nommées, il faut aller dans le gestionnaire de noms
PS ton fichier est super "lourd".. meme s'il n'est pas gros, la quantité de formules fait qu'il met des plombes à s'ouvrir chez moi
un conseil, il faudrait revoir la structure du fichier avec des tableaux structurés, et du power query: il me semble qu'une solution t'as été proposée

 

[vgendron]

Je me permet une remarque sur ton fichier;.
il semble y avoir des incohérences...
exemple sur la feuille "Pré Rosé Ratio"
1) colonne D: il y a une liste de validation alimentée par la plage A4:A8 ==> pourquoi ne pas prendre A9.
et dans la colonne D, de nombreuses lignes sont inscrites avec des valeurs NON autorisées par la liste de validation
plutot que mettre une liste dans ta feuille, utiliser une feuille spécifique "Listes" avec des tables structurées==> ca permet de regrouper les "paramètres" du fichier au même endroit
et pouvoir répercuter toute modif de cette liste dans tout le fichier

2) colonnes N==> S: formule à rallong avec des Si
tu pourrais avoir une formule a base de recherchev ou index equiv
(ca pourrait donner ca =G19/RECHERCHEV($D19;Tab_RefSurf_PréRosé;COLONNES($N$18:N$18)+2;0) pour peu que tu utilises des tableaux structurés)

l'incohérence est sur la colonne N (Batiment) qui divise le Total (colonne M) plutot que le montant de la colonne G (Batiment)
alors que les autres colonnes "O à S" divisent bien les montants des colonnes associée (I à L)

3) Colonne M
il s'agit apparemment de la somme des colonnes G à L
SAUF pour la ligne 37 qui elle a droit à un diviser par 2

en PJ une proposition avec tab structurés
l'avantage des tableaux structurés, c'est que par la suite, un traitement par PQ sera surement très efficace et pourrait t'affranchir de nombreuses formules dans ton planning qui rame..

 

[vgendron]

Dans la PJ, je me suis permis de passer tous tes tableaux en Table Structuré
j'ai modifié les formules (et en ai supprimé beaucoup.. notamment dans les colonnes A==> F qui étaient masquées

le planning est devenu à son tour une table structuré==> et sa mise à jour est maintenant beaucoup plus rapide..