Faire la somme des min en fonction de variables communes

Bonjour à tous,

Je souhaiterais sans VBA et sans ajout de colonne supplémentaire faire la somme des min en fonction de l'identification des variables communes à deux listes.

Plus qu'un long discours, vous trouverez en PJ un exemple.

En jaune les variables communes sur la Feuil1 et 2 et en vert leur minimum qui doivent s'ajouter et donner 4%.

J'arrive à faire la 1ere étape avec une sommeprod(equiv mais l'introduction des minimums me pose souci.

Merci pour votre aide,

27exemple-xls.xlsx (9.21 Ko)

Bonjour Sly_1, jmd, le forum,

tu a écrit :

Je souhaiterais sans VBA...

ah, ben alors, c'est un exo tout trouvé pour jmd, notre spécialiste des solutions 0% VBA ! il arrête pas de ressasser à l'infini que VBA est inutile et ne sert à rien, qu'on peut tout faire sans VBA ! et crois-moi, il s'y connaît : c'est son expérience de 35 ans qui parle ! (selon ses propres dires) ; je le cite texto : « en 35 ans, jamais eu besoin de VBA » ; alors, excuse-moi du peu : on peut dire qu'il en a, de la bouteille, notre ami jmd !


mais tiens donc, comme c'est bizarre ! ton post date d'hier à 14:42 et pourtant, t'as encore reçu aucune réponse !

que s'passe-t-il, jmd ? t'as pas réussi à résoudre cet exo avec ton merveilleux Power BI Desktop dont tu nous rabats les oreilles à longueur de temps ? ni avec Power Query ou un TCD dont tu nous parles tout aussi fréquemment ? le tout en prenant bien soin de dénigrer copieusement VBA ! c'est même ton dada favori, de dévaloriser VBA ! (il t'a rien fait, pourtant, le pauvre VBA ! à vrai dire, il sait même pas qu'tu existes ! )

oui, j'sais bien qu'tu as dit aussi que Microsoft a tenté d'abandonner VBA, mais qu'il y a finalement renoncé ; et pourquoi donc, d'après toi ? ah ben, c'est p't'être que finalement, il s'est aperçu que VBA est indispensable et pas si inutile que ça ! donc que c'était une folie d'avoir pensé ne serait-ce qu'un instant qu'il eût pu abandonner le langage qu'il a lui même pris le temps et la peine de créer !


alors, Sly_1, t'es pas trop déçu d'avoir reçu aucune réponse, ni de notre vétéran jmd, ni de quiconque ? ah si, quand même, hein ? comme je te comprends, mon ami ! et pourquoi donc refuses-tu VBA ? serait-ce (comme pour jmd) à cause d'une aversion innée contre VBA ? ou peut-être une allergie subite ? hélas, je connais aucun vaccin contre ce genre de phobie irraisonnée !

mais si tu acceptes de mettre de côté ton antipathie naturelle à l'égard de VBA, ne serait-ce qu'un moment, peut-être que la solution que je vais te proposer de suite va te convenir (sait-on jamais) ? ne vaut-il pas mieux une solution avec ce pauvre VBA si détesté que pas de solution du tout ? tu vas pouvoir te rendre compte, de façon très concrète, que là où toutes les autres méthodes classiques ont échoué (y compris les outils favoris de jmd), VBA permet d'aller plus loin encore !


voici donc ma solution 100% VBA, sans Power BI Desktop, ni Power Query, ni TCD (garantie sans OGM, quoi !) :

12exemple.xlsm (15.71 Ko)

note bien que la cellule F2 est vide : y'a ni formule, ni tableau structuré, ni TCD, rien de rien ! pas l'ombre d'un Power BI Desktop ou d'un Power Query à l'horizon ! exact, n'est-ce pas ?

fais Ctrl e ; eh oui, le miracle VBA a opéré : ta cellule F2 contient bien ton résultat si attendu : 4,00% ; alors, c'est pas beau, ça ? VBA est inutile et doit être relégué aux oubliettes ? quelle bonne blague !


Alt F11 pour apercevoir l'ignoble monstre tant haï et détesté (le code VBA), puis revenir avec grand soulagement sur Excel

alors tu vois ? t'es encore vivant ! l'horrible monstre VBA ne t'a pas mordu, ni avalé tout cru !


Lao-Tseu a écrit :

Ceux qui savent ne parlent pas, ceux qui parlent ne savent pas.

transposé ici, ça donne : « ceux qui connaissent et aiment VBA en parlent en bien ; ceux qui parlent en mal de VBA ne le connaissent pas » ; alors peut-être que jmd ferait mieux d'étudier VBA et de l'apprendre sérieusement, au lieu de le décrier sans arrêt ! (mais bien sûr, ça demande des efforts, qu'il n'est peut-être pas prêt à faire ? une chose est sûre, jmd, c'est que ça vient pas tout seul, donc faut pas espérer qu'un beau matin, tu vas te lever en connaissant VBA sans avoir cherché à l'apprendre ! mais c'est tellement plus facile de critiquer c'qu'on connaît pas, ou c'qui nous fait peur ! j'espère que tu te rends compte, n'est-ce pas, que si je t'avais écouté et que j'avais abandonné VBA, j'aurais jamais pu proposer à Sly_1 cette solution 100% VBA ! voilà pourquoi je trouve inadmissible ta litanie incessante à l'encontre de VBA, qui est mon langage de programmation préféré ! et je sais que beaucoup d'autres VBAistes de ce forum aiment aussi VBA ! mais si t'as passé 35 ans à critiquer sans relâche VBA, j'comprends qu'il te soit difficile de changer tes habitudes, pas vrai ? )


alors, Sly_1, que dis-tu de ma macro VBA ? elle est suffisamment efficace pour ton goût, ou pas ?

bien sûr, il va de soi que tu dois télécharger mon fichier pour pouvoir en juger ! mon code VBA n'est pas miraculeux au point de passer de mon PC au tien juste en y pensant, ou en claquant des doigts, ni par l'opération du Saint-Esprit !

si besoin, tu peux demander une adaptation.

merci de me dire si ça te convient.


comme tu as Excel 2013 et non un Excel limité sans VBA (comme sur iPad), y'a aucune raison que tu boudes VBA, pas vrai ? si ? peut-être alors que ça vient d'une hantise de ton service informatique : leur degré paranoïaque de la sécurité fait qu'ils refusent tout code VBA, même si ça pourrait leur être utile ! j'ai aucune solution contre ça, mais dis-leur que s'ils ont peur de s'couper un doigt, qu'ils n'utilisent jamais de couteau pour couper le bon steack qui est dans leur assiette ! ils peuvent aussi retourner à l'âge de pierre, si ça leur chante ! mais ça serait plus productif et efficace qu'ils équipent tous les PC avec le meilleur anti-virus qu'ils puissent trouver !

si vraiment y'a rien à faire et que vous êtes absolument contre VBA, alors pourquoi ne pas utiliser plutôt LibreOffice, qui est gratuit ? c'est une suite sous licence libre qui comporte le tableur LibreOffice Calc ; plus d'infos ici :

https://fr.wikipedia.org/wiki/LibreOffice

il y a aussi le tableur Google Sheets (gratuit aussi, et sans VBA).

dhany

Bonjour dhany,

Merci pour la bienvenue et pour ton message, en fait pour tout te dire j'ai utilisé le forum il y a quelques années en arrière, si bien que j'en ai oublié mes id...

En tout cas ton message m'a bien fait rire : une grande histoire d'amour entre toi et jmd, j'attends la réplique avec impatience

Pourquoi pas le vba ?

Tout simplement car je souhaite utiliser cette formule sur plusieurs classeurs et que leur forme n'est pas identique. Je te rejoins toutefois sur VBA : un bel outil pratique dans certains cas mais je rejoins aussi jmd car inutile dans d'autres et même dangereux quand tu travailles en équipe (modification des emplacements des cellules, code illisible...). Et quand un néophyte découvre le code il est vite tenté pour en mettre partout

Ma maxime est donc (je pense que Lao-Tseu aurait pu l'écrire !) : quand tu peux faire sans (VBA), abstiens toi

Après il m'arrive de marier les deux. Pas toi et jmd mais VBA et les formules, si si c'est possible

En fait tu m'as fait avancer car je pense que s'il n'y pas de solution Excel sans VBA, on va pouvoir créer une formule VBA et ainsi elle sera dynamique en fonction des variables que l'utilisateur va lui rentrer.

J'ai même déjà son nom : Overlap

J'attends quand même de voir si une simple formule hors VBA peut suffire, tu ne veux pas essayer dhany

tu a écrit :

Après il m'arrive de marier les deux. Pas toi et jmd mais VBA et les formules, si si c'est possible

ah ben, ton message m'a bien fait rire aussi ! un anti-VBA et un pro-VBA, ça peut faire que des étincelles !

pour faire par formule c'que j'ai fait par VBA : désolé, c'est trop compliqué pour moi ! et en plus, j'sais même pas si c'est possible ! si un pro des formules y arrive, chapeau bas ! les paris sont ouverts ! qui sera l'heureux gagnant ? (le lot à gagner est une version limitée d'Excel sans VBA, offerte par jmd)

dhany

Bonjour,

Avec formule et sans colonne supplémentaire, ça parait vraiment compliqué ! Quel serait le soucis avec une colonne supplémentaire ? Si elle est masquée, toujours pas possible ?

bonjour

un essai pour t'inspirer

25sly.xlsx (9.23 Ko)

cordialement

Salut l'équipe,

Béotien des formules, je ne sais pas si c'est impressionnant mais, bravo, Tulipe!

Je me le dis depuis des lustres mais il faudra bien que je me colle à l'étude et la logique de ces de formules!

A+

Bonjour à tous,

@Pedro22 : je ne souhaite pas surcharger Excel surtout que le problème des colonnes supplémentaires est de ne pas oublier de glisser les formules si des champs sont ajoutés.

@tulipe_4 : merci de ta contribution, malheureusement il doit manquer des éléments. J'ai pris un exemple plus compliqué et je ne retrouve pas le bon résultat. Peut-être que je n'ai pas réussi à l'adapter. Dis-moi.

En attendant, je vous ai mis un exemple plus long et j'y ai ajouté ma formule Excel VBA. A mon avis on peut certainement mieux faire mais au moins j'arrive à mon résultat. Reste à savoir si c'est possible sans VBA

6sly-v2.xlsm (21.77 Ko)

Bravo tulipe_4,

chapeau bas ! t'as gagné le lot offert par jmd : une version limitée d'Excel sans VBA j'suis pas doué pour faire ce genre de formule, et pour moi, c'est plus simple de faire un programme VBA, même si c'est un peu plus long.

je n'ai vu que 2 petits défauts à ta méthode :

1) même si c'est une formule matricielle, si des nombres et pourcentages sont ajoutés, je ne crois pas qu'ils seront pris en compte automatiquement (alors que c'est le cas pour mon code VBA)

2) tu as recopié en "Feuil1" les données de "Feuil2" ; pourquoi ? serait-ce que ta méthode empêche de faire autrement ? et là où Sly_1 avait écrit qu'il ne voulait pas de colonne supplémentaire, tu ajoutes 2 colonnes de plus !

cependant, et c'est pour cela que j'ai parlé de petits défauts, je crois que ces 2 inconvénients peuvent être tous deux résolus par l'utilisation de noms définis (que j'utilise personnellement très peu).

d'autre part, même si ta méthode n'est pas parfaite, tu as quand même le grand mérite d'avoir su proposer une méthode par formule, ce qui est un avantage inappréciable pour le demandeur, puisqu'il l'avait demandé dans son énoncé ; et accessoirement, je crois que ça plaira aussi beaucoup à jmd !

dhany

@Sly_1

j'espère que tulipe_4 te trouvera une solution

qui marche comme tu veux ; bonne chance !

dhany

h-sourire56

@Sly_1

j'espère que tulipe_4 te trouvera une solution

qui marche comme tu veux ; bonne chance !

dhany

h-sourire56

j'y vaque

j'avais recopié la 2 sur la 1 pour pouvoir faciliter la visualisation en direct ; ensuite on adapte au reel

re

j'ai vaqué

14sly-v22.xlsm (20.32 Ko)

meme presentation pour analyse

cordialement

Bonjour,

je ne souhaite pas surcharger Excel surtout que le problème des colonnes supplémentaires est de ne pas oublier de glisser les formules si des champs sont ajoutés.

Ca n'est plus un problème depuis que tu peux mettre des plages en Tableau (menu Insertion).

Formules et formats sont reportés dès que tu ajoutes une ligne.

eric

Hello,

Je viens de regarder ton fichier tulipe_44 (et merci au passage), et je n'arrive toujours pas au bon résultat. J'ai par exemple modifié le 2200 avec 1% (cf.PJ), on doit de ce fait trouver 7% et ton calcul reste à 8%.

@eriiic : oui c'est une piste intéressante mais ça nécessite la création de tableau, et mon idée est de pouvoir dupliquer la formule dans plusieurs classeurs et de faire donc au plus vite et au plus court.

J'y crois encore...

8sly-v22-3.xlsm (20.68 Ko)

Bonjour Sly_1,

si tu saisis 1% en Feuil2!B4 : ça reste 8% ; tu dois saisir 1% en Feuil1!M4 pour que ça passe à 7%

(ceci est par rapport au fichier de ton post de 10:53)

dhany

Bonjour dhany,

Comme tu le verras dans mon dernier exemple j'ai modifié la cellule B6 de 2% à 1%. De ce fait le minimum pour la référence 2200 devient 1% et non plus 2%. Et donc la somme globale des minimums des références communes passe de 8% à 7%. Mais de mon côté, la formule de tulipe reste à 8%.

ton résultat, tu le regardes sur Feuil1!E8 ou sur Feuil1!F1 ?

ton B6, c'est celui de Feuil1 ou Feuil2 ? y'a les deux !

c'est bien pour le fichier de ton post de 10:53 ?

@Sly_1

tu précises pas assez ! j'suis même pas sûr qu'on parle du même fichier ! alors indique si c'est le fichier de ton post de 10:53 ou un autre ; et si oui, lequel ? de plus, quand tu parles des cellules, indique sur quelle feuille c'est, car y'a des mêmes données sur les 2 feuilles ! donc comment savoir desquelles tu parles ? précise aussi la référence de la cellule résultat.

dhany

Oui c'est bien celui de 10h53 et j'ai modifié la donnée sur la Feuil1.

Je remets mon fichier en PJ.

Le résultat de tulipe en Feuil1!E8 : 8%

Le résultat attendu Feuil1!J1 : 7% qui est la somme des cellules vertes en Feuil1! : 0%+1%+3%+3%=7%.

J'espère que cette fois c'est plus clair.

Merci,

4sly-v22-3.xlsm (20.68 Ko)

merci pour tes précisions ! en Feuil1!J1, j'ai bien 7,00% :

screen

donc c'est bon, puisque tu as écrit : « Le résultat attendu Feuil1!J1 : 7% »

Rechercher des sujets similaires à "somme min fonction variables communes"