XL 2019 Estimation % à partir d'une valeur

[FMDCC]

Bonjour à vous,

En utilisant les données Filosofi INSEE sur les revenus des ménages et les montants par déciles disponibles, j'aimerai savoir comment estimer le pourcentage pour un montant défini.

Par exemple, à l'échelle de la France Métropolitaine, voici les revenus déclarés des ménages propriétaires :

1er décile 2e décile 3e décile 4e décile Médiane 6e décile 7e décile 8e décile 9e décile
13 420 € 17 170 € 19 960 € 22 550 € 25 250 € 28 300 € 32 190 € 37 900 € 49 060 €
Quels calculs faut-il faire pour déterminer ou estimer le pourcentage de ménages propriétaires ayant au moins 15 000€ ?

 

[sylvanu]

Bonjour FMDCC,
Deux méthodes possibles en PJ :
1- en faisant des extrapolations linéaires entre chaque décile, puis en appliquant les coefficients avec le montant désiré par extraction des bons coefficients :

=INDEX(C1:C11;EQUIV(H5;B1:B12;1))*H5+INDEX(D1:D11;EQUIV(H5;B1:B12;1))

2- En faisant le graphique et la courbe de tendance polynomiale. En extrayant la fonction de la courbe de tendance on calcule le décile désiré.

 

[FMDCC]

Bonjour sylvanu,

Merci pour cette réponse très complète et en plus un exemple très parlant.

L'une des problématiques étant de définir la forme de la courbe qui existerait si l'on pouvaient représenter l'ensemble des revenus.

Les données Filosofi nous indique également :
- Rapport interdécile D9/D1 : 3,7
- S80/S20 : 5
- Indice de Gini : 0.322

Ces éléments peuvent ils nous aider à mieux estimer ?

 

[sylvanu]

Je ne pense pas qu'on puisse se servir de ces données :
Rapport interdécile D9/D1 : Lien
S80/S20 donne le rapport entre 80% et 20% de la population, le fameux 80-20.
Indice de Gini : Lien
Ces trois valeurs ne servent qu'à caractériser la courbe de façon synthétique.
On voit qu'entre 10% et 70%, soit entre 13420 et 32190€, la courbe peut être assimilée à une droite, donc dans cet intervalle l'erreur sera faible.

 

[FMDCC]

Pas évident tout cela !

Je travaille dans le domaine du logement et j'aimerai pouvoir estimer le nombre de ménages propriétaires étant éligibles aux aides de l'Anah (Agence nationale de l’habitat).
Ainsi, chaque année, un plafond de revenus est défini selon qu'il s'agisse de ménages en Ile-de-France ou dans une autre région. Et l'on distingue également 2 plafonds, celui des ménages dits "très modestes" (TM) et celui des "modestes" (M).

Mén. TM : IdF = 21 123 € | Autres régions = 15 262 €
Mén. M : IdF = 25 714 € | Autres régions = 19 565 €

Mon objectif est de pouvoir déterminer, dans les communes, agglomérations, départements, régions et France métropolitaine, les pourcentages de ménages se situant sous ces plafonds.
Ensuite par croisement avec le nombre de ménages propriétaires occupants d'après le dernier recensement de l'INSEE (2019), pouvoir estimer le nombre de ménages concernés.

Ci-joint un exemple des données dont je dispose pour la France métrop., les régions et les départements.

 

[sylvanu]

Peut être une solution en VBA avec :

Sub Calcul()
    TMIDF = [R2]: TMAR = [R3]: MIDF = [R4]: MAR = [R5]
    Pourcentage 14, TMIDF, TMAR
    Pourcentage 15, MIDF, MAR
End Sub
Sub Pourcentage(Colonne, Seuil1, Seuil2)
    For L = 2 To Range("A65500").End(xlUp).Row
        ' Détermination des seuils vs IDF ou non
        If Cells(L, 2) = "Oui" Then Seuil = Seuil1 Else Seuil = Seuil2
        For C = 4 To 12
            If Cells(L, C) > Seuil Then             ' Trouver intervalle contenant le seuil
                Ind = C: Exit For
            End If
        Next C
        X1 = Cells(L, Ind - 1): X2 = Cells(L, Ind)  ' Lecture des valeurs dY et dX
        Y2 = (Ind - 3) / 10: Y1 = Y2 - 0.1
        a = (Y2 - Y1) / (X2 - X1)                   ' Calcul des coef de linérisation a et b
        b = Y1 - a * X1
        Cells(L, Colonne) = a * Seuil + b           ' Détermination du % et rangement valeur
    Next L
End Sub

Toujours par linéarisation entre deux points.
A vérifier .

 

[FMDCC]

Merci pour cette nouvelle solution qui utilise donc l'extrapolations linéaires entre chaque décile pour déterminer les valeurs recherchées.

La seconde méthode avec l'utilisation de la courbe de tendance polynomiale serait elle applicable de la même manière ?

Pour ma problématique, laquelle des 2 méthodes serait la meilleure ?
J'aimerai pouvoir comparer les résultats entre les 2.

 

[sylvanu]

Faire une tendance polynomiale en VBA n'est pas simple.
D'autant qu'on voit sur les courbe que la tendance polynomiale en dessous de 10% n'est plus valable, tout du moins avec une équation du 5eme degré.
Attention cependant à ne pas rechercher une précision trop grande qui ne serait qu'illusoire. Le seul moyen d'affiner la précision est .... d'avoir plus d'échantillons. Ce qui ne peut être le cas ici.

J'aimerai pouvoir comparer les résultats entre les 2.

On voit sur la méthode 2 qu'en dessous de 10% l'approche est fausse.
Ensuite pour 15000€ par ex on a le choix entre 14.21% et 13.89%, soit un delta de 0.33%.
Avec seulement 10 points sur votre courbe, je ne pense pas qu'on puisse aller au delà.

 

[FMDCC]

Merci beaucoup pour ces précisions.