Discussion :

[EsmeInParis]

bonsoir,

je fais une étude comparative de contrats de mutuelle.
J'ai listé les différents actes médicaux en les classant par différents critères que j'ai appelés couverture, orientation, typologie de soins, Nature et Secteur.


Ensuite j'affecte une note à chacune des lignes de ce tableau en fonction du montant remboursé.
Au final, les points sont additionnés et j'obtiens un score pour chaque mutuelle.

Les mutuelles ont deux manières de rembourser un acte médical. Elles se basent sur le tarif de convention (BR) de l'Assurance Maladie et rembourse à hauteur de n% de ce tarif (on lit sur leur contrat 100% BR, 200% BR, etc. )
Elles peuvent aussi rembourser un montant forfaitaire qui s'exprime en euros. L'exemple le plus simple sont les verres de lunettes. Elles remboursent 150€, 200€ voire 350€ par verre.
Mais il arrive aussi qu'une mutuelle ne rembourse pas une dépense médicale que nous pourrions avoir. La chambre individuelle en cas d'hospitalisation, l'orthodontie en sont deux exemples. Certaines mutuelles rembourse tout ou partie de la dépense, d'autres ne rembourse rien.

J'ai mis en place un système de notation lorsque la mutuelle rembourse l'acte en fonction du tarif de convention (BR) de l'Assurance maladie.

Mais je suis coincée pour mettre en place le système de notation lorsqu'elle rembourse un forfait. Et j'ai besoin de votre aide.

Je voudrais accorder la note de 12 à la mutuelle qui accorde le forfait le plus élevé pour une Couverture, Orientation, Typologie de soins, Nature des soins et selon le Secteur donnés.
La mutuelle qui rembourse moins bien aura une note proportionnellement moins élevée, etc. Et celle qui ne rembourse pas l'acte aura une note négative qui elle aussi sera proportionnelle.

Conformément aux recommandations des modérateurs, je n'ai pas joint de fichier mais je tiens à diposition un abstract du fichier sur lequel je travaille.

Je vous remercie pour l'aide que vous pourrez m'apporter.
Esme

[Fab_de_chez_PERFORM]

Bonjour
je suis surpris de l'histoire de note négative. Personnellement, j'aurai mis une note de 0 à ce qui ne remboursent pas.
Ainsi, pour attribuer ma note sur 12 sur ce paramètre j'aurai fait

= le montant de la mutuelle analysée / max(colonne des montants) * 12
avec éventuellement un arrondi sans décimale

[EsmeInParis]

bonjour Fab et merci pour votre aide.

Pourquoi je mets une note négative : pour disqualifier les mutuelles qui ne remboursent pas par rapport à celles qui le font et qui le déclarent.
Et je mets la note de 0 à celles qui ne mentionnent pas dans leur tableau de garanties si elles remboursent ou pas. Il s'agira de poser la question au courtier le cas échéant.

Mon souci avec la solution que vous proposez est que je dois préciser des conditions. Si je n'en mets pas, la formule compare des actes d'hospitalisation avec des actes d'optique, ce qui n'est pas logique. Je voudrais comparer ces forfaits dans les conditions rélles de comparaison.
Pensez-vous à une formule comme celle-ci (12*G3/MAX($G$3:$G$1300) ?
si oui, comment puis-je inclure des conditions dans une telle formule ?
merci

[Fab_de_chez_PERFORM]

Bonjour
voici un tableur en exemple

j'ai inventé 5 mutuelles avec 4 forfaits (Remb Chambre, Verres, Orthodontie, Divers)
Ces intitulés de forfaits sont liés à une table A11 à C15. La table du bas permet de positionner des coefficients aux catégories (admettons que vous vouliez attribuer un plus gros impact à la présence du remboursement des lunettes dans votre note sur 12) et permet de donner la note négative dont vous parliez.
Une petite vérif. se trouve en B16 pour vérifier que les coeffs font bien 100% au total.
Nota : les intitulés en A12 à A15 doivent être impérativement identiques à ceux en B2 à E2 (pour ma recherchev -> ouh le vilain )

Dans la zone en jaune, à masquer, j'ai mis en place une formule qui permet de situer le forfait de la mutuelle parmi ses consœurs ou d'attibuer une note négative s'il y a écrit NON
ex :

Code :

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

=SI(B3="NON"; RECHERCHEV(B$2;$A$12:$C$15;3;FAUX);B3/MAX(B$3:B$7)*RECHERCHEV(B$2;$A$12:$C$15;2;FAUX))

~ s'il y a écrit non va me chercher la note négative en 3e colonne, sinon calcule ma place et affecte mon coefficient

La note sur 12 est donc obtenue comme suit :

Code :

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

=ARRONDI(SOMME.SI(F3:I3;">0";F3:I3)*12+SOMME.SI(F3:I3;"<0";F3:I3)/12;1)

~ là c'est soumis à interprétation : autant le /12 de la fin ne doit pas être fait ? peut être faudrait il affecter les notes négatives aux coeffs ?

Avec ca vous devriez pouvoir affiner votre idée et réussir à faire votre formule.

[EsmeInParis]

Waoooh Fab, merci beaucoup.

Mon tableau aujourd'hui est transposé par rapport au tien : les prestations en ordonnées, les mutuelles en abscisses. Difficile à transposer les formules ?

Peux tu m'expliquer pourquoi tu passes par un tableau des coefficients ?

merci beaucoup
Esmé

[Fab_de_chez_PERFORM]

Re
voici le tableur avec la transpo (feuil2)

J'ai mis des coeffs mais ca n'est pas obligatoire
en fait je me suis dit qu'il pouvait être intéressant de donner plus ou moins d'importance à des critères
ex : si tu estimes qu'etre remboursé pour des lunettes est + important que pour la TV dans la chambre d'hopital alors tu peux jouer avec ces coeff

si on prend mon tableur
actuellement, les 4 criteres sont égaux et donc à 25% -> la meilleure note est pour la mutuelle 5 avec 6.2 / 12
si je dis que mes critères ne sont pas égaux alors je les bouge en mettant 5%, 5%, 75% et 15% (gros avantage pour l'orthodontie) -> la meilleure mutuelle devient la 1, avec 8.8 sur 12.

[EsmeInParis]

Hello Fab,

merci bcp pour la transposition. J'aurais mis 100 ans à le faire !!

comment faire pour enlever les coeffs de la formule =IF(B3="NON"; VLOOKUP($A3;$A$14:$C$17;3;FALSE);B3/MAX($B3:$F3)*VLOOKUP($A3;$A$14:$C$17;2;FALSE))

Je voudrais donner la note de 12 à la mutuelle qui rembourse le mieux une prestation, sans lui donner de poinds (coeff). Dans le cas des lunettes, l'ordre est Mutuelle3, Mutuelle 5, Mutuelle 4 et enfin la 2. Et enfin la Mutuelle 1 doit avoir une note négative qui devrait être -12 car je veux la pénaliser par rapport aux mutuelles qui les rembourse.
Mais l'écart de points entre la 3 et la 5 est moins important que'entre la 4 (194€) et la 2 (69€).

merci bcp pour ton aide

[Fab_de_chez_PERFORM]

Bonjour

du coup je propose de virer les lignes 11 à 18 de ma feuil2 et de mettre en B7 :

Code :

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

=SI(B2="NON";-12;ARRONDI(B2/MAX($B2:$F2)*12;1))

j'ai mis un arrondi à 1 chiffre après la virgule sinon il pourrait y avoir une note de 12 alors que le montant n'est pas le meilleur
(ex sur mutuelle 5 des lunettes où le montant est sensiblement le même que la meilleure mutuelle)

explication de la formule
si ma valeur est non alors la note est de -12 sinon effectuer un arrondi à 1 décimale sur la proportion que représente ma valeur par rapport au max (le tout multiplié par 12 pts)

désolé pour les 1er jets, je pensais que la note finale tout paramètre confondu serait sur 12 d'où mes coeffs.