Probleme avec SOMMEPROD

[KIM]

Bonjour le Forum,
Bonjour le forum,
J'ai passé toute la journée d'hier et la soirée sans résultat. Je bloque sur une formule simple SOMMEPROD qui me donne le resultat #VALEUR!
Je fais appel comme d'habitude au forum et vous en remercie d'avance.

Je recupère un fichier de plus de 25000 lignes. Dans ce fichier de plusieurs colonnes j''en utilise 3:
1ere col: Code_du_DEP (normalement du Texte)
2° col: montant commande (normalement numérique)
3° col Code du projet (normalement TEXTE)

Je dois faire un récapitulatif selon une liste de projet qu'on me donne:
Ce tableau recap possède :
1° col: Liste des codes projets
2° col: Récap dépense globale par projet et les autres colonnes : Dépenses globale par projet et par DEP

Je me suis rendu compte que dans le fichier Origine il existe des cellules Texte dans la 2° col des montants commande.
Dans mon tableau recap j'utilise:
=SOMMEPROD(($B5:$B1153)*($C5:$C1153=$F16)*($A5:$A1153=J$4))
j'ai essayé aussi
=SOMMEPROD(CNUM($B5:$B115)*TEXTE($C5:$C115;'')=TEXTE($F16;''))

Je copie une centaine de lignes de mon fichier, je supprime les cellules contenant du TEXTE dans la 2° COl
et les formules sont correctes,
Je fais la meme chose sur le fichier origine et j'applique à tout mon fichier les formules
et là j'ai toujours l'erreur #VALEUR!
ci-joint une extraction du fichier d'origine
MERCI DE VOTRE AIDE
Amicalement
KIM [file name=prKIM1.zip size=23794]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM1.zip[/file]

 

[Jocelyn]

re,

bon je suis désolé j'ai du m'absenter

mais voila un nouvel essai avec des plage nommées dynamiques toujours ma colonne masquées

et dans le tableau de droite en colonne G une extraction par Formule de tous les code présents dans la colonne D sans doublons

Voila

Merci Dan d'avoir pris la relève

Jocelyn [file name=prKIM11_20051019161137.zip size=46939]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM11_20051019161137.zip[/file]

concernant le fait que tu veuille transformé la colonne D en Texte je ne l'ai pas fait mais sur la colonne d'a coté tu peux mettre la formule :
=SI(ESTERREUR(CTXT(D5;0));D5;CTXT(D5;0))

Message édité par: Jocelyn, à: 19/10/2005 16:29

Message édité par: Jocelyn, à: 19/10/2005 16:33

 

[KIM]

Re,
Merci Jocelyn et vous tous pour les infos,
Dan,
J'ai prepare 2 fichiers :
1- prKIM21 contient 2 feuilles CDE et FAC dans le meme formet de reception, les colonnes en couleurs sont les colonnes à utiliser.
2- code projet: 2 feuilles
Liste Projets telle que je la recois et
la feuille RECAP pour le résultat final.
En final, quelque soit la methode utilisée pour l'automatisation des taches, je dois arriver
Pour chaque RESPONSABLE, à faire un récap de ses projets
ACTIVITE A; ACTIVITE B; PROJET du montant total CDE, FAC, Global et par DEP.
Il y a plus de 150 projets, 20 DEP et 10 RESPONSABLES.
QU'en penses-tu? Est-ce faisable?
Merci d'avance
Bien amicalement
KIM [file name=prKIM21.zip size=47814]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM21.zip[/file]

 

[KIM]

SUITE

Liste Projets telle que je la recois et
la feuille RECAP pour le résultat final.

Merci d'avance
KIM

 

[KIM]

SUITE

Liste Projets telle que je la recois et
la feuille RECAP pour le résultat final.

Merci d'avance
KIM [file name=codeprojet.zip size=26535]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/codeprojet.zip[/file]

 

[Dan]

Message édité par: Dan, à: 20/10/2005 00:05

 

[Dan]

Bonsoir Kim,

Si je comprends bien tu reçois 3 feuilles, à savoir :
- La feuille Liste projets
- La feuille CDE
- La feuille FAC

Toutes trois te sont données dans le même fichier.

La feuille RECAP est celle que tu ajoutes chaque fois au fichier pour récupérer les infos. Par ailleurs, d'ou vient le nom des responsables, activités etc .. qui sont placés en couleur dans ton fichier codeprojet - feuille RECAP. Qui les encode ?

Est-ce que chaque responsable t'envoie ou non un fichier tel que celui que tu m'as donné. Cela fait bien sur une différence puisque soit on travaille sur un seul fichier ou sur 20.
Dans le deuxième cas, cela risque de se corser pour trouver une solution.

L'idéal est de constituer une base de données avec toutes les infos nécessaires et ce, avant de penser au résultat que tu attends.

Merci de tes explications car cela me parait etre un gros projet là et donc essentiel de bien comprendre tout ce qui se passe tant au niveau des responsables que du tien.

 

[KIM]

Bonsoir Dan,
Merci de t'interesser à mon projet.
Je reçois 3 fichiers (mais on peut les mettre tous dans un seul classeur en 3 feuilles differentes si la simplification l'impose.), à savoir :
- un fichier Liste projets qui contient la liste des noms ACTIVITES A, des noms Activites B (sous activites de A), des noms des Projets, des codes PROJETS , TYPE (P ou A) , des noms des Responsables de Projets, etc... Je le recois deja rempli avec toutes ces informations.
- un fichier CDE
- un fichier FAC

Je les ai mis dans un même fichier pour creer mon recap avec sommeprod d'excel.

En final, je dois arriver à creer un tableau des Depenses de la forme suivante:
Pour chaque RESPONSABLE, recap par
ACTIVITE A; ACTIVITE B; PROJET du montant total CDE, FAC, Global et par DEP.
c-a-d 5 niveaux d'imbrication: RESPONSABLE/ACTIVITE A/ ACTIVITE B/DEP

Concernant le codage, normalement le code projet est sur 3 caracteres
ex: AT0 projet initial de AT
AT1 1er sous projet de AT
AT2 2eme sous projet de AT
Suite à des problemes d'homogeneisation de la codification projets dans ces 3 fichiers, le tableau des depenses (recap) ne se fera que sur les projets et non les sous projets. c-a-d à n'utiliser que les 2 premiers carc du codage projets, on va donc consolider les 3 fichiers selon les 2 premiers car du codage projets (Col CODE du fichier Liste Projets et Col CODE MATIERE des fichiers CDE et FAC).
Merci d'avance
Bien amicalement
KIM

 

[Dan]

Bonjour Kim,

A la lecture de tes explications et de tes fichiers, j'ai reconstitué un fichier qui contient maintenant les feuilles FAC, CDE, LISTE PROJET, RECAP

Je vois un premier problème pour la RECAP.
En effet, tu parles de Code projet et code matière qui serviraient de référence pour compléter ta feuille RECAP.
Ce que je constate c'est qu'il n'y a pas pas de lien entre les trois feuilles et que là cela va poser un problème pour compléter la feuille RECAP à partir des autres.

Sachant que les données à compléter dans la feuille RECAP :
- Responsable doit venir de la feuille LISTE PROJETS - Colonne H
- Activités A et B doivent venir de la feuille LISTE PROJETS - colonne A & B
- Projet vient de la feuille LISTE PROJETS - Colonne C
- Code vient de la feuille LISTE PROJETS - Colonne E
- Type vient de la feuille LISTE PROJETS - Colonne G
- Cde globale vient de la feuille CDE - Colonne ? et quel et le lien avec la feuille LISTE PROJET
- FAC globale vient de la feuille FAC - Colonne ? et quel et le lien avec la feuille LISTE PROJET

Bref, tu vois qu'il manque un lien pour reprendre les infos. Peux-tu apporter des précisions.

 

[KIM]

Re, Dan,
Le lien avec les 3 fichiers que je recois est le code projet :
dans les 2 feuilles CDE (col D) et FAC (col F) la colonne PROJET s'appelle CODE MATIERE et dans la feuille 'Liste Projets' c'est la colonne CODE (Col E). Dans ces colonnes il y a des données différentes d'une feuille à une autre mais ce qui nous interesse ce sont les codes Projets de la feuille LISTE PROJET. Normalement je dois retrouver ces codes en 3 car dans les colonnes adéquates des 2 feuilles CDE et FAC. Pour faire un TDB des depenses pour les projets seulements sans les sous-projets nous allons travaillé sur les 2 premiers caracteres des 3 colonnes de ces 3 fichiers. C'est le lien entre ces 3 fichiers.
Ainsi à partir du code 2 car du projet, je peux identifier le RESPONSABLE (colonne H de le feuille LISTE PROJET ainsi que le NOM du PROJET correspondant (col C), le nom de l'ACTIVITE B (col B) et le nom de l'ACTIVITE A (col A) toujours dans le meme feuille LISTE PROJET
Les Depenses sont commandes (CDE col L) et factures (FAC col H) qui concernent les départements DEP (col A de CDE et FAC)
N'hesites pas à me poser d'autres questions si necessaire
Bien amicalement et merci encore
KIM

 

[Dan]

Bonjour,

Sur base de tes explications j’ai regardé ton projet qui me paraît assez difficile à réaliser notamment en raison des codes d’identification qui sont à traiter pour pouvoir retrouver les données en provenance de chaque feuille.

Dans l’état actuel, je pense que le traitement via TCD se complique dans la mesure où il devient difficile de rapatrier les données dans la feuille RECAP. Les codes sont en effet souvent identiques après traitement et comme tu l’as surement compris la collecte des données est problématique.
Donc soit on reprend ta solution de départ par formule comme d’autres l’ont proposé dans ce fil ou on se dirige vers la solution ci-après.

Proposition : utiliser la feuille LISTE PROJETS pour rapatrier les infos venant des feuilles FAC et CDE et de construire la feuille RECAP à partir de celle là.

Dans ce cas, il faudrait dans la feuille LISTE PROJETS

- Dupliquer la feuille LISTE PROJET
- Compléter les cellules vides dans les colonnes A, B et C.
- Supprimer les colonnes non nécessaires
- adapter le code projet (AT0, AT1, … deviennent AT) dans la formule d’importation ou créer une colonne avec le code correct (impératif pour collecter les données)
- si nécessaire supprimer les doublons dans le code projet
- Importer les données nécessaires des feuilles FAC et CDE et ce, en fonction du code projet.
- Création de la feuille RECAP globale ou RECAP pour facture et RECAP pour commande

Tout ceci implique bien entendu que les feuilles sont toutes encodées de la même manière !.

Ce que je n’ai pas compris c’est si tu veux faire une RECAP pour chaque feuille Facture et commande ou une RECAP pour les deux. Dans ce dernier cas (plus complexe encore), comment tu traites les données.

Est-ce possible et merci de ton commentaire là dessus.

 

[KIM]

Bonjour Dan et le forum,

Effectivement, j'ai déjà accompli manuellement les tâches que tu viens de citer.
- Feuille recap = copie de la feuille LISTE PROJET OK
- Compléter les cellules vides dans les colonnes A, B et C. OK
- Supprimer les colonnes non nécessaires OK
Dans les 3 feuilles (CDE, FAC, recap) creer une colonne avec seulement les 2 car du code projet des colonnes CODE MATIERE et CODE (AT0, AT1, … deviennent AT).

Avce ces modifications j'ai pu avec SOMMEPROD creer une RECAP generale des depenses (CDE et FAC sur une la meme feuille) par projet (2 car) et par DEP.
J'ai au moins 50 col (20 col pour les CDE par DEP; 20 col pour les FAC par DEP, 1 col pour la CDE globale par projet seulement et 1 pour la FAC) + les colonnes concernant les RESPONSABLES, ACTIVITES A et B, noms projets et CODE projet(2car).

il me reste la creation d'une recap par RESPONSABLE
à partir de la 1ere recap generale.
Je me pose aussi des questions sur la presentation pour qu'elle soit lisible.

Voilà la situation actuelle.
J'ai d'autres interrogations et merci d'avance de ton aide:
1- Prenons par exemple la 1ere colonne de la feuille CDE ou FAC qui concerne les DEP. Comment faire pour recuperer la liste des DEP de la feuille active sans doublons et creer des titres de lignes ou de colonnes à partir d'une cellule determinée (ci joint un exemple)
Merci d'avance Dan
KIM

[file name=prKIM12.zip size=12077]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM12.zip[/file]

 

[Dan]

re,

Ta question au point 1 -> le TCD que je t'ai placé sur ce fil dans le fichier le 19/10/05 fait cela non ?

 

[KIM]

Re,
Je souhaite seulement les titres car apres j'appliquerai mes formules.
En dehors du TCD, Le but de cette question est de pouvoir selectionner une colonne ( n'importe), recuperer la liste sans doublons et les retranscrire en col ou ligne selon le besoin à partir de la cellule active.
Ex. dans la feuille RECAP, à partir de la cellule I8 (celuule activée) et sur la ligne 8 ecrire automatiquement la liste des DEP (sans doublons et triée) à partir de la colonne A de la feuille CDE (je dois retrouver les memes dans la colonne A de FAC), je pourrais lancer la macro une 2eme fois pour les inscrire une 2eme fois à la suite le but est d'avoir la RECAP par DEP des CDE et à la suite la recap par DEP des FAC.
Selon le besin je pourrais aussi faire la meme chose pour les codes projets, ou les responsables.
Merci d'avance
Si j'arrive à automatiser cette etape, je gagnerai beaucoup de temps.
Bien amicalement
KIM

 

[Dan]

Bonjour,

Heu, je ne vois pas pourquoi tu ouvres un deuxième fil sur ce sujet. Si je ne me trompe il s'agit bien de la même demande non ?

Lien supprimé

Merci de confirmer.

Sinon, je me perds un peu dans ta demande à vrai dire.

En prenant un exemple avec I8 dans ton dernier fichier, peux-tu me dire quelles sont les valeurs que tu dois trouver dans ton tableau. Tu dois avoir des chiffres.

Message édité par: Dan, à: 23/10/2005 15:29

 

[KIM]

Bonsoir Dan et le forum,
Dan, effectivement j'y ai pensé mais le but du nouveau fil etait de marquer par un x les projets concernant un DR, question que je n'ai pas encore posée dans ce fil pour ne pas l'alourdir.
J'espère que ma réponse est recevable.
Si je prends le fichier prKIM12.xls, je cherche à automatiser la liste des DEP trouvée dans la colonne A et retranscrire sans doublons dans les cellules G5, H5, I5, etc
de meme une liste unique sans doublons des codes projets de la colonne C et les afficher dans les cellules F6, F7; F8, etc...
c-a-d à partir de la cellule active F5,
creer à droite de cette cellule (G5, H5, ...) la liste des DEP trouvée dans la colonne A, et vers le bas (F6, F7,...) la liste des projets trouvée dans la col C.
Merci d'avance Dan et avec mes excuses.
Bien cordialement
KIM