Discussion :

[ericaa]

Bonjour,

Je reviens sur un problème que lequel je n'ai pas réussi à trouver de réponse.

J'ai un tableau excel de 300 entreprises (numérotées de 1 à 300).
Pour chaque entreprise j'ai les informations suivantes (cf tableau joint)
dépenses, recettes, année de référence (2013, 2014..)

J'ai plusieurs questions, voici la première:

1/ A partir d'une formulaire de saisie (en rouge dans le tableau joint) comprenant deux valeurs ( ex pour l'entreprise 28, année 2013 ), quelle est la fonction la plus adaptée - sur excel et sans macro et sans tris - me permettant de pouvoir mettre dans un tableau de résultats (en jaune dans le tableau joint) les données correspondantes.

La formule que j'ai mis dans mon tableau (en jaune dans le fichier ) de résultats en F12 (=SI($C$13=36;E5)+SI($C$13=28;E3)+SI($C$13=37;E7)) n'est pas adaptée car trop longue a mettre en oeuvre pour 300 entreprise, et par ailleurs ne prend pas en compte l'année de référence.
Je cherche une formule assez simple (et sans macro svp) car j'ai beaucoup de tableaux qui seront associés..




Merci pour vos lumières..

Cdt
Eric

[78chris]

Bonjour

En F13

Code :

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

=INDEX(E3:E9;EQUIV($C$16&$C$13;$B$3:$B$9&$C$3:$C$9;0);1)

à valider en matriciel (CTRL Shift Entrée) et recopier sur G13

On peut aussi le faire par TCD+segments sur 2010 ou par segments seuls sur 2013 si un simple filtre suffit...

[Menhir]

1/ A partir d'une formulaire de saisie (en rouge dans le tableau joint) comprenant deux valeurs ( ex pour l'entreprise 28, année 2013 ), quelle est la fonction la plus adaptée - sur excel et sans macro et sans tris - me permettant de pouvoir mettre dans un tableau de résultats (en jaune dans le tableau joint) les données correspondantes.

As-tu envisagé d'utiliser un filtre (ce qui n'est pas un tri) ?

[ericaa]

Merci Chris , ca marche impeccable !! j 'ai utilisé ta formule (plutot que tcd ou filtre qui ne seront pas adaptés dans le cas présent).

En revanche au total j'ai 1200 lignes pour mon tableau excel de départ car pour chaque entreprises (300 au total ) j'ai des valeurs pour l'année 2011, 2012, 2013, 2014. Donc j'ai 4 lignes par entreprise et en colonne j'ai environ 100 variables..(dépenses recettes, déficit, dotations, ..).. C'est la formule la plus simple à mettre en oeuvre (hormis filtre ou tcd? )

[ericaa]

en réponse à Mehnir,

Les filtres cela ne me parait pas adapté car j'ai absolument besoin de passer par ma case saisie de données (en rouge).. donc je pense que ca ne peux pas marcher avec cette contrainte?

[78chris]

Re

Sur 2013, remplacer la saisie par un filtre par des segments a l'avantage d'être souple et ne pas risquer une erreur en retour si la donnée n'existe pas ou suite à une saisie erronée.

Dans le mesure où les données ne sont pas classées par année ou société, il n'y a pas trop de choix de formule.

Si les données sont classées par sociétés comme ton exemple on peut utiliser, en non matriciel

Code :

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

=RECHERCHEV($C$16;DECALER($B$2;EQUIV($C$13;$C$3:$C$9;0);0;NB.SI($C$3:$C$9;$C$13);5);4;0)

en F13 et

Code :

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

=RECHERCHEV($C$16;DECALER($B$2;EQUIV($C$13;$C$3:$C$9;0);0;NB.SI($C$3:$C$9;$C$13);5);5;0)

en G13

Sur un grand nombre de données la seconde doit être un poil plus rapide.

On peut aussi résoudre par un filtre avancé mais c'est plus long car il faut le relancer à chaque fois.

[ericaa]

merci encore chris pour cette nouvelle contribution. Mais cette formule me parait à vrai dire plus compliquee que la première sur laquelle tu as planché ce matin.. vu le nombre de données j'essaie d'aller au plus simple..

[ericaa]

Bonjour,

J'ai repris la formule de chris (merci encore), mais je n'arrive pas à la copier sur les autres cellule malgré mon CTRL schift entree.. Message d'erreur N/A à chaque fois que je n'arrive pas à résoudre..

=INDEX(E3:E9;EQUIV($C$16&$C$13;$B$3:$B$9&$C$3:$C$9;0);1)

D'ou peut venir l'erreur ..?

[78chris]

Bonjour

Tu veux la copier où ?

Dans ton exemple il n'y a que 2 cellules à valoriser et la copie marche sur G13 en tirant simplement la formule

Si ton cas réel est différent il faut adapter.

[ericaa]

hello ca a marché!! le problème venait des cellules fusionnées. Pour copier la formule bloque si il y a des cellules fusionnées dans le tableau de résultats

j'ai d'autres questions si tu veux bien.. ?

1/ comment calculer la moyenne des recettes de toutes les entreprises de catégorie A année 2013 par ex

2/ plus dur .. est il possible via une nouvelle colonne de mettre le rang ex pour l'année (2014, catégorie A, recettes) l'entreprise 37 sera en rang 1 pour les recettes et l'entreprise 45 en rang 2..

je connais la fonction rang mais dans le cas présent j'y arrive pas..
gracias

[78chris]

Bonjour

Les cellules fusionnées c'est la plaie et dans 95% des cas on peut s'en passer...

Pour la moyenne voir MOYENNE.SI.ENS

Faire un RANG sur une plage discontinue est probablement possible mais assez complexe et c'est le type de formules qui rament...

On en revient à la simplicité des TCD pour traiter ce genre de cas... depuis 2010 le rang peut être affiché dans le TCD.

[ericaa]

re

la MOYENNE.SI.ENS je vois bien mais comment l'articuler avec mes conditions de départ selectionnés (année dans le cas présent). Mon exemple du tableau il sagit bien de faire la moyenne des catégorie A pour les données de l'année sélectionnées dans la case rouge (2013 par ex)

[78chris]

Re

C'est la B A BA de ce type de fonctions

Code :

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

=MOYENNE.SI.ENS($E$3:$E$9;$D$3:$D$9;"A";$B$3:$B$9;$C$16)

La catégorie pourrait aussi être dans une cellule modifiable comme l'année...

[ericaa]

merci chris

en effet ca y est j'ai resolu le problème. merci
Je recherche maintenant la maniere de faire intervalle sur une serie de données (les recettes par ex) de maniere a catégoriser ensuite les entreprises ayant moinsde X recettes, celles entre Y et Z recettes, celles de plus de Z recettes.. y a il une fonction pour cela stp?

[78chris]

Bonjour

A nouveau, je ne saisi pas pourquoi tu ne veux pas utiliser les TCD...

Tu peux construire une table des tranches et associer la tranche adéquate à ta recette/sté avec RECHERCHEV.

[ericaa]

hello Chris,

je reprend mon ouvrage, j'ai ete pris sur autre chose. Les tcd à vrai dire je ne vois pas comment je peux les coupler avec ma recherche par année et par num d'entreprise en amont ..

[78chris]

Bonjour

J'avais déjà mis un exemple avec des segments : je l'ai complété avec la moyenne, des tranches de CA...

Pourquoi s'enquiquiner avec des formules pour faire ce que les TCD font nativement...

[ericaa]

re bonjour Chris,
soit je suis obtu, soit j'arrive pas à expliquer .. sur la question des tcd. Ce serait effectivement plus simple mais je ne pense pas que cela puisse répondre à mon problème. En fait il y a une condition essentielle :

1/ Il faut impérativement que les mes tableaux de résultats ), puissent ressortir pour l'entreprise sélectionnée et pour l'année sélectionnée et seulement pour c(ex année 2013 entreprise 28.
Dans une page de garde on sélectionne le num de l'entreprise et l'année (ce que j'avais matérialisé en rouge dans mon avec "données à saisir"; ) et ne ressortent que les tableaux pour l'entreprise et l'année concernés (au total j'en aurai plus de 400 par entreprise et par année car j'ai beaucoup de variables avec beaucoup de comparaisons avec moyenne ensemble des entreprises, ..et beaucoup de ratios complémentaires ).

2/ j'ai par ailleurs bcp de calculs similaires à reprendre sur des indicateurs différents (dépense par ex)

Pour reprendre l'exemple, pour l'entreprise 28 année 2013 (ou une autre entreprise ou année) il me faudra au final dans un même tableau :
l'indicateur 1 (recette),
-montant des recettes de 'entreprise 28 (année 2013)
- moyenne des recettes pour toutes les entreprises confondues pour l"année 2013
- moyenne des recettes pour toutes les entreprises de la meme catégorie que l'entreprise 28 (A ds l'exemple)
- moyenne pour toutes les entreprises de meme strate ( entre 200 et 500)
- rang de l'entreprise 28 /toutes les entreprises confondues pour l"année 2013
- rang de l'entreprise 28 / toutes les entreprises de la meme catégorie que l'entreprise 28 (A ds l'exemple)
-...
- Historique des recettes pour l'entreprise 28 année 2012 -2013 -2014
- Historique des recettes pour toutes les entreprises confondues lannée 2012 -2013 -2014
- poids des recettes (indicatur 1) / poids des dépenses (indicateur 2) pour l'année 2013 entreprise 28
- idem pour la moyenne de toutes les entreprises



IDem pour l'indicateur 2 , 3 , ..->100

J'ai beaucoup de calculs similaires pour chacun des indicateurs + des ratios spécifiques à chaque indicateur (ex - poids des recettes (indicatur 1) / poids des dépenses (indicateur 2) .

voila si le tcd répond à cette problématique ce serait le top..

[78chris]

Bonjour

C'est ce qu'on appelle un reporting ou tableau de bord selon les entreprises...

Dans tes différents post tu parles parfois d'une entreprise et une autre fois d'une classe d'entreprise donc plusieurs entreprises. Pour des ratios sans doute...

Pour ma part je ferais 1 à 3 TCD incluant tous les calculs (ou la majorité si certains sont trop tordus pour un TCD mais on a déjà de quoi faire avec tous les ratios, et écarts calculables dont la plupart de ceux que tu cites) et je les exploiterais pour une entreprise donnée avec la fonction LIREDONNEESTABCROISDYNAMIQUE

J'ai fait plusieurs modèles sur ce principe pour des clients : c'est souple, cela limite les formules matricielles qui rament vite...

Les TCD offrent tellement de possibilités depuis le version 2010, que je les utilise dans des cas où je n'y aurais même pas songé il y a quelques années...

[ericaa]

merci pour ce retour


et est ce que dans ce cas il est possible à l'utilisateur final (une des 300 entreprises ), d avoir accès à l'ensemble de ses données préparées en amont , en saisissant uniquement son num d'entreprise et l'année souhaitée et surtout sans qu'elle ai à faire aucune autre manip.

En clair je souhaiterai que le reporting (préparé en amont ) se fasse uniquement en saisissant ces deux données sans rien faire d'autre. Pour les formules il n'y a pas de problème, mais pour les tcd je ne sais pas...