Formule excel afficher du texte avec sommeprod ou index.equiv

Y compris Power BI, Power Query et toute autre question en lien avec Excel
M
Mustapha92
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 19 septembre 2019
Version d'Excel : 2010

Message par Mustapha92 » 19 septembre 2019, 16:07

Bonjour à tous,

Je souhaite obtenir de l'aide sur une formule sur Excel....

Pour résumé, j'ai une liste de collaborateurs, qui ont travaillés dans différents "Dept=> Departement" de Janvier à Juin... et à des Coût Journalier différents(TJM) en fonction des mois(=> là j'ai réussi avec 1 formule somme.prod)....

Mon souhait c'est d'automatisé le Dept(colonne AA) dans l'onglet "Détails CRA OSA _2019_09_18" en fonction de la période(mois) qui se trouve dans l'onglet "Table mapping ressources".
=> le problème c'est que ma formule actuelle recherchev... m'affiche que "Production" normal et je connais pas de formule à part somme.prod... pour prendre en compte le critère de la période(qui a marché pour la colonne AC "TJM"... mais ne marche pas avec du texte.

je joints le fichier.. vous comprendrez mieux ce que je veux dire

Merci pour votre aide.
TEST FORMULE.xlsx
(121.11 Kio) Téléchargé 6 fois
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'207
Appréciations reçues : 651
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 19 septembre 2019, 18:27

Bonjour, et bienvenue

ton fichier est tellement complexe qu'on a dû mal à s'y retrouver

je n'ai pas compris quelle valeur texte tu souhaitais !

quoiqu'il en soit, si tu veux un résultat en texte, tu peux combiner sommeprod et index en faisant en sorte que dans sommeprod tu utilises ligne ou colonne, par exemple
=SOMMEPROD(('TABLE MAPPING ressources'!$A$4:$A$5=$Y3)*('TABLE MAPPING ressources'!$H$2:$S$2=$D3)*COLONNE('TABLE MAPPING ressources'!$H$4:$S$5))
tu as donc un n° de colonne que tu peux utiliser dans un index ensuite.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
M
Mustapha92
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 19 septembre 2019
Version d'Excel : 2010

Message par Mustapha92 » 20 septembre 2019, 11:49

Merci Steelson

Malheureusement ça a pas marché, quand j'ai tapé ta formule... ça m'a affiché "16"...
Or moi je voulais qu'il m'affiche "Archi & Secu" car le collaborateur a travaillé de JAnvier à Mai au département "Archi & Secu" et en Juin au département "Production".

et j'ai aussi essayé ensuite d imbriquer la formule avec "index et message d'erreur..

N'hésites pas si t'as d'autres solutions
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'207
Appréciations reçues : 651
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 20 septembre 2019, 12:08

Mustapha92 a écrit :
20 septembre 2019, 11:49
Or moi je voulais qu'il m'affiche "Archi & Secu" car le collaborateur a travaillé de JAnvier à Mai au département "Archi & Secu" et en Juin au département "Production".
Ben voilà, c'est l'info qu'il manquait !
Steelson a écrit :
19 septembre 2019, 18:27
je n'ai pas compris quelle valeur texte tu souhaitais !
je t'ai donc répondu sur le titre "afficher du texte avec sommeprod" en utilisant ligne ou colonne pour en avoir un indice.

je vais regarder maintenant que je sais ce que tu cherches

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'207
Appréciations reçues : 651
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 20 septembre 2019, 12:50

NOTA : valable que si une seule activité dans le mois.
=INDEX('TABLE MAPPING ressources'!$B$4:$B$5;SOMMEPROD(('TABLE MAPPING ressources'!$A$4:$A$5=$Y3)*('TABLE MAPPING ressources'!H$2:S$2=$D3)*('TABLE MAPPING ressources'!$H$4:$S$5<>"")*LIGNE('TABLE MAPPING ressources'!$B$4:$B$5))-3)
sachant qu'à intérieur de cette formule
SOMMEPROD(('TABLE MAPPING ressources'!$A$4:$A$5=$Y3)*('TABLE MAPPING ressources'!H$2:S$2=$D3)*('TABLE MAPPING ressources'!$H$4:$S$5<>"")*LIGNE('TABLE MAPPING ressources'!$B$4:$B$5))
te donne la ligne dans laquelle se trouve la réponse
il suffit alors d'encapsuler la formule avec INDEX

Après je te donnerai mon point de vue quant à la simplification de ton fichier.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
M
Mustapha92
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 19 septembre 2019
Version d'Excel : 2010

Message par Mustapha92 » 20 septembre 2019, 15:24

Merci beaucoup,

ça marche.

par contre par curiosité... pourquoi tu rajoutes à la fin de ta formule "-3".... ?

et j'ai pas compris pourquoi "valable que si une seule activité dans le mois." ?

Merci pour ton retour
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'207
Appréciations reçues : 651
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 20 septembre 2019, 18:32

1-
Mustapha92 a écrit :
20 septembre 2019, 15:24
par contre par curiosité... pourquoi tu rajoutes à la fin de ta formule "-3".... ?
Parce que l'index commence à la 4ème ligne $B$4, donc 3 lignes pour rien ! L'index 1 correspond donc à la ligne 4 (4-3).
INDEX('TABLE MAPPING ressources'!$B$4:$B$5
On aurait pu mettre
INDEX('TABLE MAPPING ressources'!$B:$B
en prenant toute la colonne, et dans ce cas le -3 était inutile. L'index 1 dans ce cas correspond à la première ligne. Pas d'écart.

2-
Mustapha92 a écrit :
20 septembre 2019, 15:24
et j'ai pas compris pourquoi "valable que si une seule activité dans le mois." ?
à cause du
SOMMEPROD(...............*LIGNE('TABLE MAPPING ressources'!$B$4:$B$5))
s'il y avait plusieurs lignes concernées, il additionnerait les numéros de lignes et donnerait un "autre" résultat complètement "à l'ouest". Il faudrait une formule matricielle trop complexe ...

3- Tu ne sembles pas du tout intéressé par cela :
Steelson a écrit :
20 septembre 2019, 12:50
Après je te donnerai mon point de vue quant à la simplification de ton fichier.
Tu pars d'un tableau, déjà pas facile à remplir, pour construire un autre tableau complexe tout aussi compliqué en formules !

Il suffirait de construire une base de données simple et d'avoir toutes les informations sous forme de TCD. Aucun calcul !
On peut même construire un onglet assez sympa pour renseigner les activités des consultants.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
M
Mustapha92
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 19 septembre 2019
Version d'Excel : 2010

Message par Mustapha92 » 20 septembre 2019, 23:45

MERCI beaucoup pour toutes tes explications et ton aide. C'est très clair et surtout ça a parfaitement fonctionné.je dormirai moins "con" cette nuit :)

Je comprends mieux pourquoi "Fanatique d'Excel" ;;) .

Pour ce qui est ...
"Après je te donnerai mon point de vue quant à la simplification de ton fichier." => bien entendu je suis intéressé... mais je t'avouerai que mon souhait n°1 c'est que ça fonctionne car après la qualité de mon reporting(une partie de mon job) résultera de ce tableau et que toutes ces formules fonctionnent....
Pour t'expliquer l'onglet "table mapping ressources" c'est un tableau que je suis chaque jour... et chaque fin de mois je sorts une extraction depuis un logiciel qui me sort le détails sur quoi ont imputés et travaillés les collaborateurs... y en a énormément... et chaque mois encore plus de lignes....ça commence à la colonne A et termine à la colonne X"charges" l'extraction dans l'onglet"Détails CRA OSA _2019_09_18"(je saisie rien à la main là...heureusement d'ailleurs)...mais après cette extraction brute est incomplète ...pour faire mon reporting...il faut que je tienne compte d'autres variantes(jours transformés en€, ventilé sur les projets, distinguer les internes/externes...)....et c'est pour ça que j'ai rajouté tout un tas de formule de la colonne Y à AD et y'a d'autres formules encore après la colonne AD lol.....qui viennent chercher une partie des informations dont j'ai besoin dans l'onglet "TABLE MAPPING ressources"....tout ça pour dire lorsque tout est complété ...à la fin je m'en sert comme une énorme base de données et fais des tableaux croisés dynamiques(comme tu m'as recommandé) dans tous les sens lol... pour répondre au mieux aux besoins de mon chef....lol. et des autres collaborateurs financiers.

Donc je suis preneur si t'as du temps pour me faire une proposition ... je regarderai et verrai si ça répond à toutes les attentes de mon boss lol.... et te remercie par avance.

bonne soirée et bon weekend
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 12'207
Appréciations reçues : 651
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 25 septembre 2019, 09:48

Mustapha92 a écrit :
20 septembre 2019, 23:45
si t'as du temps pour me faire une proposition ... je regarderai et verrai si ça répond à toutes les attentes de mon boss lol....
Donc là, c'est déjà très mal barré ! On a eu beaucoup de cas d'intervention de boss qui justement imposaient l'objectif et aussi la forme sans connaître excel, juste "a priori", façon de montrer "qui sait" et "qui décide".
Donc je ne ferai rien car c'est voué à la poubelle, si ce n'est et montrer un exemple déjà fait.

Et dans le domaine des consultants, je trouve cela vraiment dommage.

Au lieu d'avoir une feuille par agent, les informations sont regroupées dans une base de données, et une seule feuille suffit à renseigner celle-ci, et afficher les résultats.
formulaire_onglet.xlsm
(49.23 Kio) Téléchargé 5 fois

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
M
Mustapha92
Nouveau venu
Nouveau venu
Messages : 5
Inscrit le : 19 septembre 2019
Version d'Excel : 2010

Message par Mustapha92 » 8 octobre 2019, 10:21

Salut Steelson,

Avec un peu de retard, car ton mail s'est perdu dans "mes indésirables".... :)

merci pour ta proposition.... c'est intéressant... mais ça correspond pas tout à fait à mon besoin, je pense que ça correspondrait plus à des services RH ou contrôleur de gestion sociale....

Heureusement que je suis pas tous les consultants externes d'ailleurs à cette maille là, j'y passerai des jours et des jours...

Quoi qu'il en soit ta formule m'a beaucoup aidé, et m'a permis d'automatiser mon reporting avec un niveau de justesse à 100%... :good: .... Merci Encore et à bientôt. bonne journée
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message