Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

[Johnito]

Salut à toutes et tous,

Je suis un grand débutant en excel (je ne manipule vraiment que depuis les dernière 48h) et pour apprendre je suis parti d'un petit projet perso qui me sert à lister les objets que j'ai. J'entre à chaque fois:

• Le nom (Logitech Z-5500...)
• La date d'achat (14/07/2010)
• Le coût initial (250€)
• La catégorie (ameublement, sports ...)

A partir de là, j'essaye de générer automatiquement un maximum de stats (plus ou moins intéressantes) grâce à des formules qui me permettent de prendre la main de manière personnalisée. Mais j'ai des difficultés avec les formules conditionnelles utilisant plusieurs cellules. Et plutôt que d'entrer dans un long discours, je vous joins mon fichier excel qui est clair sur ce que je veux faire: remplir tous les champs de "statistiques globales" automatiquement grâce à des formules. Si possible sans macro. Les champs vides sont ceux que je n'ai pas réussi à faire grâce aux recherches forum + google.

Si vous pouviez jeter un œil juste pour voir si tout vous semble bien et si vous aviez une idée pour les champs vides !
Merci beaucoup.

 

[Victor21]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonjour, Johnito.

Pas tout vérifié, mais en M24, à recopier vers le bas, essayez :

=SOMMEPROD((ListeProduction[COUT])*(ANNEE(ListeProduction[ACHAT])=1*DROITE(I24;4)))

Ps : Méfiez-vous de Datedif qui a parfois un comportement étrange...
Effectuez une recherche dans ce forum à l'aide de la loupe (en haut à droite) pour voir les fils sur ce sujet, et les alternatives possibles.

Edit : Bonjour, misange

 

[Misange]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonjour et bienvenue dans le monde d'excel

Il y a un outil que tu aurais tout intérêt à découvrir tout de suite : les tableaux croisés dynamiques
Ce lien n'existe plus
je t'ai fait un petit exemple de base qui ne fait que répondre à la question : quel cout par catégorie et par année mais en faisant simplement lisser les étiquettes et le cas échéant en modifiant la façon dont tu synthétises tes données (par somme, nombre de, moyenne,...), tu peux obtenir beaucoup beaucoup d'autres infos. Tu peux trier, filtrer, ne montrer que les plus faibles et plus fortes valeurs.

Sinon côté formules, une formule indispensable pour répondre aux questions que tu poses : sommeprod
Ce lien n'existe plus

tu auras aussi très vite besoin de la combinaison index+equiv
Ce lien n'existe plus

Mais j'insiste ++ : commence par essayer de répondre avec un tableau croisé dynamique (TCD) et ne vient aux formules que si le TCD ne répond pas à la question. C'es't l'outil le plus puissant d'excel à mon avis.

edit coucou Patrick

 

[Modeste]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonjour Johnito, bienvenue ... et bon rétablissement!
Salut à Flo et Patrick

Une petite pierre supplémentaire à "Lady Fisse": dans les colonnes J, K, L et M -pour les moyennes et coûts- plutôt que modifier la formule à chaque ligne, pour y indiquer la catégorie, tu peux remplacer le texte que tu as saisi, par la référence de la cellule contenant le nom de la catégorie.

De cette manière, tes formules s'autocopient dans la colonne entière ... et tu n'as plus ren à faire d'autre (pour ce tableau-là, du moins )

Et j'insiste++ moi aussi: n'écoute pas Misange tout le temps: les TCD sont devenus une véritable drogue pour elle ... Crois-moi: ne suis pas cette pente savonneuse!

 

[Guest]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

bonjour,

une proposition avec des Somme.Si.Ens (M24 à M31) et des formules à base de index(equiv( )) dont deux matricielles conjointes à MAX () en M17 et M19.

Rappel: une fonction matricielle se valide par CTRL+MAJ+ENTER.

A+
et grand coucou aux amis Misanges et Victor
sans oublier Modeste qui nous rejoint

 

[Johnito]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Très sincèrement, merci beaucoup à vous tous !

@Hasco wow, c'est tout rempli ! Merci mille fois: c'est exactement ce que je voulais, et maintenant il ne me reste plus qu'à piger certaines des fonctions écrites parce-que là tout de suite :

=INDEX(Moyennes[CATEGORIE];EQUIV(MAX(SI(Moyennes[CATEGORIE]<>"DIVERS";Moyennes[COUT GLOBAL]));Moyennes[COUT GLOBAL];0))

...Je pige pas tout lol. Mais c'était un peu le but de la démarche. Je ne te pose même pas de questions de suite, je ferais mes petites recherches demain pour voir si tout ne s'explique pas à ce moment là. Je me donne d'ailleurs toute la journée pour maîtriser les bases d'INDEX et des fonctions matricielles. J'ai l'impression que ça va être du taf dejà, surtout si je fais plusieurs essais pour que ça se grave bien.

@Misange Je ne savais même pas qu'Excel pouvait faire ça ! Bizarrement ça m'a l'air moins compliqué à cerner que la fonction au dessus ... Intéressant aussi, on a donc plusieurs façons d'arriver au même résultat de façon radicalement différente avec ce type de tableau. Je regarderais bien comment tu as pu faire tout ça lundi et le principe des TCD, et je reviendrais sur ce topic si j'ai vraiment besoin que tu m'expliques un truc voir j'ouvrirais un nouveau post

@Victor21 yep ça marche bien pour celle-là ! Je sais maintenant utiliser DROITE.

@Modeste merci pour le conseil !

 

[Xwprft]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonsoir Johnito, et les autres,

Dès que tu as une base de données, le TCD te permet de faire des analyses dans tous les sens.
Voilà par exemple comment répondre à tes stats avec uniquement des TCD.
(la drogue c'est pas le TCD, c'est le LSD)
A+

 

[Regueiro]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonsoir Le Forum, Johnito
Sur ton Onglet "Feuille1"
Pour trouver les Investissements recensés pour l'année XXX
en M24

=SOMMEPROD((ANNEE(ListeProduction[ACHAT])=CNUM(DROITE(I24;4)))*ListeProduction[COUT])

Tu peux étirer la formule jq M31
A+

 

[Misange]

Re : Coûts de fonctionnement ...Quelques bonnes formules, et puis ça coince !

Bonjour
Oui les TCD sont très simples à mettre en place. Je ne comprends pas du tout pourquoi ils déclenchent une réaction de rejet chez certains. Je ne dis pas du tout qu'ils sont l'unique façon de faire. En revanche, avec pas mal de km de cellules dans les pattes, je sais que le plus rapide et le plus efficace est de commencer par regarder si un TCD peut répondre à la question posée. Si oui, je fonce, si besoin je complète avec des formules, et si encore besoin, je complète avec une macro. MAis je ne fais jamais les choses en sens inverse. La raison est simple : les TCD effectuent leurs opérations en langage machine, qui est extrêmement rapide, beaucoup plus que toutes les formules qui font des calculs sur des plages de cellules (matricielles dont sommeprod fait partie).
De toute façon, je te conseille de toujours séparer la saisie des données et leur analyse. Ce que tu as très bien commencé à faire. Autrement dit, pas de ligne vide dans un tableau, pas de sous total manuel, pas de calculs sur une partie du tableau.