Discussion :

[Aletchou]

Bonjour à tous,

je m'adresse à vous car je rencontre un soucis qui dépasse mes compétences en Excel, je vais essayer d'être le plus explicite possible mais n'hésitez pas à me dire si je m'exprime mal:

Je gère aujourd'hui plusieurs fichier Excel dans lesquels j'ai dans un la nomenclature de mes produits; dans l'autre j'utilise la nomenclature pour calculer mes prix de revient (coût des différents composants); dans un autre j'utilise la nomenclature pour faire mon calcul des besoins nets en fonction de mon stock; un autre pour calculer mes tailles de lots; etc. Tout ces fichier utilisent ma nomenclature mais pour cela je fais des copier coller dans chaque fichier. Viens alors un problème dès que j'ai une mise à jour de nomenclature à faire, je suis obliger de corriger tout mes fichiers or pour certains je les utilise 1 à 2 fois par an maxi et j'en oublie toujours.

Idéalement je pense qu'il faut un outil de base de donnée type Access mais je ne sais pas l'utiliser et je pense que c'est peut être faisable avec Excel. J'ai alors essayé de créer quelquechose comme ça:

Mon fichier matrice nomenclature avec en colonne mes produit finis et en ligne mes composants:

Composant / Produit finis	P-3000	P-1000	P-5000
Oreille Guide	4	0	4
Oreille Race	0	4	0
Platine Guide bleue	0	0	2
Ancien Picot	0	2	0
Nouveau Picot	2	0	2

Voici mon fichier des couts de revient par composant:

composant	prix
Oreille Guide	2€
Oreille Race	3€
Platine Guide bleue	2€
Ancien picot	1.5€
Nouveau picot	2€

Je souhaite maintenant faire un tableau avec le prix de revient par produit fini en fonction des composant de ce produits finis, c'est à dire que en face de chaque composant de la liste suivante il me fasse la somme du produit de chaque composant avec la quantité par ce type de produit finis

Produit fini	Prix
P-3000	=2€*4+2€*2=12€
P-1000	=4*3€+2*1.5€=15€
P-5000	=4*2€+2*2€+2*2€=16€

Et dans l'idée dès que je modifie un prix quelque part ou une ligne de nomenclature il faut que ça se mette à jour partout.

Puis-je faire ça sans Macro ?
Connaissez vous les formules pour résoudre le calcul en rouge? cela ressemble à "somme.prod" mais cela inclue des "recherchev" et "rechercheh" et je voit pas comment faire ça.

Merci d'avance pou votre aide.

[papouclo]

Bonjour,
Dans le classeur joint, les trois tableaux du message d'Aletchou sont respectivement dans l'une des trois feuilles nommées : Nomenclature , Coûts , Prix Revient.
Dans la feuille Nomenclature, la plage B1: D1 a été nommée Produits. Ce nom est défini dynamiquement par :

Code :

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

=DECALER(Nomenclature!$B$1;;;1; NBVAL(Nomenclature!$A$1:$IV$1)-1)

ce qui permettra de rajouter ultérieurement de nouveaux produits sans avoir à redéfinir le nom.
Dans la feuille Coûts, la plage B2 : B6 a reçu le nom Prix défini dynamiquement par :

Code :

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

=DECALER(Coûts!$B$2;;;NB(Coûts!$B$1:$B$1000);1)

Dans la feuille Prix revient, la formule en B2 est :

Code :

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

=SOMMEPROD(DECALER(Nomenclature!$A$1;1;EQUIV('Prix revient'!A2;Produits;0);NB(prix);1)*prix)

à recopier vers le bas autrant que nécessaire.
Une seule obligation : les deux listes de composants doivent obligatoirement être ordonnées de la même façon en A2:A6 sur la feuille Nomenclature
et en A2:A6 sur la feuille Coûts.
Cordialement
Claude

[Daniel.C]

Bonjour,

Si tu veux le faire sans VBA, à mon avis, tu devrais utiliser MSQuery.

[Aletchou]

Merci à toi Papouclo, je suis en train d'essayer de comprendre tes formules que je ne connaissait pas. Je vois que pour simplifier tes formules tu nomme tes plages de données, je savait que c'est faisable mais je ne sais pas comment faire, peut tu me l'expliquer ?

[Aletchou]

J'ai beau lire les aides excel ainsi que ton message dans tous les sens, je ne comprend pas exactement la fonction EQUIV ni la fonction DECALER. La fonction DECALER je comprend que tu t'en sert pour nommer les plages de données et faire que si j'ajoute une ligne elle soit ajoutée partout, mais je ne comprend pas comment elle fonctionne.

[Aletchou]

En fouillant un peu sur le net j'ai trouver les explications des formules EQUIV et DECALER et ça y est je comprend les formules, merci encore.

[Aletchou]

Salut à tous,

je suis toujours sur le même fichier ou je souhaite faire un nouveau calcul semblable, mais j'ai beau tourner la formule dans tous les sens je ne trouve pas.

Dans un onglet j'ai ma liste des produits finis avec leur qté de besoin annuel:

PF besoin annuel
PF1 2000
PF2 3000
PF3 5500
PF4 1200

Dans un autre onglet j'ai ma nomenclature qui me dit combien j'ai de chaque composants dans chaque produits finis

PF PF1 PF2 PF3 PF4
composant désignation Produit fini 1 Produit fini 2 Produit fini 3 Produit fini 4
A composant A 1 2 0 1
B composant B 0 1 8 2
C composant C 3 2 2 0
D composant D 2 1 0 0
F composant F 0 2 6 1

ce que je souhaite c'est calculer dans un nouvel onglet la quantité du besoin annuel pour chaque composant

réf besoin annuel
A 9200
B 49400
C 23000
D 7000
F 40200

Pouvez vous m'aider à trouver ce resulat de manière automatique s'il vous plait ?

[papouclo]

Bonjour,
Voir une proposition dans le classeur ci joint
Attention! Les formules de la feuille Besoins sont des formules matricielles à valider par Ctrl + Maj + Entrée
Cordialement
Claude

[Aletchou]

Merci encore une fois de ta réponse rapide, je vais regarder ça est essayer de tout comprendre.

[Aletchou]

J'ai réussi à comprendre la formule et à l'appliquer à mon fichier, merci encore.

Par contre je n'ai pas trop compris le pourquoi du calcul matriciel et qu'est ce qui fait que ça marche pas si je valide pas la formule comme calcul matriciel avec ctrl + maj + entrée ?