Recherche somme plusieurs onglets

H

hoerwind

XLDnaute Barbatruc

• 19 Juillet 2011

• #2

Re : Recherche somme plusieurs onglets

Bonjour,

En Conso!B2 : =SI($A2="";"";SOMME(dec10:mars11!B2))
formule à copier vers le bas et vers la droite

 

Vote positif 0 Vote négatif

Manu62

XLDnaute Impliqué

• 19 Juillet 2011

• #3

Re : Recherche somme plusieurs onglets

Bonjour,

Merci pour l'amélioration de la formule.

Peux t'on ajouter le critère faire la somme en fonction du nom et du type.

Ex: faire la somme de salarié 1 de tous les onglets et du type CP.

Il se peut que par la suite au fil des mois..., avec des noms réels, le calcul soit faussé, car les salariés n'auront pas forcément le même emplacement car en conso, il y aura un tri par salarié.

Merci d'avance

Manu

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 19 Juillet 2011

• #4

Re : Recherche somme plusieurs onglets

Bonjour

Une posssibilité en pièce jointe (cf. fichier somme), pas très belle, mais bon... La formule est longue pour tenir compte des "inversions" possibles dans la liste des salariés (cf. un exemple en rouge sur la feuille dec10). Autrement, on aurait pu faire avec une somme 3D. C'est tellement plus simple.
On peut mettre des noms à la place de certaine partie de la formule pour éviter les répétitions et la rendre plus lisible (cf. fichier Somme bis).

@ plus

 

Pièces jointes

• somme bis.xls

  53.5 KB · Affichages: 46
• somme.xls

  100.5 KB · Affichages: 52
• somme.xls

  100.5 KB · Affichages: 57
• somme.xls

  100.5 KB · Affichages: 54

Dernière édition: 19 Juillet 2011

Vote positif 0 Vote négatif

Manu62

XLDnaute Impliqué

• 19 Juillet 2011

• #5

Re : Recherche somme plusieurs onglets

Salut,

La formule est très bien sauf que lorsque je la recopie dans mon fichier, elle fait rien...

Peux tu stp me definir un peu la formule, je pense avoir un probleme avec le 0 et 1.

Merci de ton retour

Manu

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 19 Juillet 2011

• #6

Re : Recherche somme plusieurs onglets

Bonjour

La formule est composée de plusieurs parties presqu'identiques, une par feuille, du style, en M2 :

Code:

SI(ESTNUM(DECALER(dec10!$A$1;EQUIV($A2;dec10!$A$2:$A$20;0);EQUIV(M$1;$A$1:$K$1;0)-1));DECALER(dec10!$A$1;EQUIV($A2;dec10!$A$2:$A$20;0);EQUIV(M$1;$A$1:$K$1;0)-1);0)

DECALER(dec10!$A$1;EQUIV($A2;dec10!$A$2:$A$20;0);EQUIV(M$1;$A$1:$K$1;0)-1) (= DEC dans le fichier bis) va chercher dans la feuille dec10 la cellule sur la ligne du salarié concerné (avec EQUIV($A2;dec10!$A$2:$A$20;0), puisque A2 contient le nom du salarié concerné) et dans la bonne colonne (avec EQUIV(M$1;$A$1:$K$1;0)-1, puisque M$1 contient le type du jour).

Ainsi le DECALER ci-dessus donne DECALER(dec10!$A$1;1;1) (décalage d'une ligne et d'une colonne), c'est à dire le contenu de la cellule dec10!B2.

La partie ci-dessus est donc de la forme SI(ESTNUM(DEC);DEC;0).
On fait de même avec les feuilles suivantes, ce qui donne pour janvier 2011 :

Code:

+SI(ESTNUM(DECALER(janv11!$A$1;EQUIV($A2;janv11!$A$2:$A$20;0);EQUIV(M$1;$A$1:$K$1;0)-1));DECALER(janv11!$A$1;EQUIV($A2;janv11!$A$2:$A$20;0);EQUIV(M$1;$A$1:$K$1;0)-1);0)

(= JANV dans le fichier bis).

La formule complète est de la forme SI(ESTNUM(DEC);DEC;0) + SI(ESTNUM(JANV);JANV;0) + SI(ESTNUM(FEV);FEV;0)...

En effet, comme tous les noms des salariés ne se trouvent pas dans toutes les feuilles (par ex, salarié 8 n'est pas dans la feuille dec10), si on ne mettait directement DEC+JANV+FEV... on aurait des #NA lorsque la formule ne trouve pas, dans certaines feuilles, le nom du salarié.

Cette formule n'est pas super puisqu'il faut rajouter un +SI(ESTNUM(X);X;0) pour chaque nouvelle feuille. Pour autant, ce n'est pa très compliqué à faire.

@ plus

 

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 19 Juillet 2011

• #7

Re : Recherche somme plusieurs onglets

Bonjour,

Faisable en 3D, sauf erreur

Code:

=SOMMEPROD(SOMME.SI(INDIRECT(ListOnglets&"!A2:A20");$A2;DECALER(INDIRECT(ListOnglets&"!A2:A20");;COLONNE()-1)))

 

Pièces jointes

• Somme3D-Manu62.xls

  38.5 KB · Affichages: 64

Vote positif 0 Vote négatif

Manu62

XLDnaute Impliqué

• 20 Juillet 2011

• #8

Re : Recherche somme plusieurs onglets

Bonjour Monique,
Bonjour Cisco,

Merci à tous les deux pour vos retours. J'ai adopter la version de Monique.

J'ai juste modifier la formule en mettant à la fin .......(ListOnglets&"!A2:A100");;37)))

car mes colonnes ne sont pas placés dans le fichier original... j'ai fait des modif depuis hier....


Merci à vous.

Manu

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 20 Juillet 2011

• #9

Re : Recherche somme plusieurs onglets

Bonjour à tous

Comme d'habitude, Monique nous a trouvé une solution plus astucieuse 🙂. Intéressant : C'est du matriciel camouflé (SOMMEPROD) qui permet de faire du 3D. Méthode à mémoriser. Il ni a même pas besoin de traiter les #NA possibles... Vraiment bien vu.

@ plus

 

Dernière édition: 20 Juillet 2011

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 20 Juillet 2011

• #10

Re : Recherche somme plusieurs onglets

Bonjour,

Ce n'est pas de moi, c'est du Boisgontier pur et dur
Mais il manque un truc pour obtenir la liste des onglets sauf le dernier (ou sauf le premier)
sans passer par une liste dans la feuille

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 20 Juillet 2011

• #11

Re : Recherche somme plusieurs onglets

Bonjour

En reprenant l'idée de Boisgontier citée par Monique, une petite, toute petite amélioration, avec une feuille supplémentaire appelée vide.

@ plus

 

Pièces jointes

• Somme3D-Manu62-Moniquebis.xls

  39 KB · Affichages: 50

Dernière édition: 20 Juillet 2011

Vote positif 0 Vote négatif

Manu62

XLDnaute Impliqué

• 20 Juillet 2011

• #12

Re : Recherche somme plusieurs onglets

Bonjour,

Merci de vos retours

Manu

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 20 Juillet 2011

• #13

Re : Recherche somme plusieurs onglets

Bonjour à tous

Une variante avec une colonne A en Feuil1 ne contenant pas les noms des salariés.

@ plus

 

Pièces jointes

• Somme3D-Manu62-Moniquesuite.xls

  38.5 KB · Affichages: 59

Vote positif 0 Vote négatif

M

Monique

Nous a quitté

Repose en paix

• 20 Juillet 2011

• #14

Re : Recherche somme plusieurs onglets

Bien !

Je me suis cassé la tête à trouver une solution, rien trouvé.
Là, au moins, c'est net : pas une feuille en trop, pas une liste à traîner sur une feuille

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 21 Juillet 2011

• #15

Re : Recherche somme plusieurs onglets

Bonjour à tous, bonjour Monique.

Oui, Monique, mais cela serait quand même intéressant si on trouvait une astuce pour ne "ramener" qu'une partie de la liste donnée par LIRE.CLASSEUR(1)... J'ai essayé, sans succès, avec par exemple, pour ne ramener que les 4 premiers noms, INDEX(NomFeuilles;;{1;2;3;4}). Le "pire", c'est que certains essais me donnent le bon résultat dans la fenêtre d'évaluation pas à pas, mais pas dans la cellule, et que la formule que tu as proposée ne le fait pas juqu'au bout dans la fenêtre d'évaluation (affichage de nombreux #VALEUR), mais donne le résultat dans la cellule...

@ plus

 

Dernière édition: 21 Juillet 2011

Vote positif 0 Vote négatif