Comment faire une interpolation linéaire dans Excel (avec exemples)

Comprendre l'interpolation linéaire

L'interpolation linéaire est une méthode d'estimation d'une valeur inconnue située entre deux valeurs connues. Elle suppose que la relation entre les valeurs est linéaire, c'est-à-dire qu'elle peut être représentée par une ligne droite. En d'autres termes, elle permet de trouver une valeur intermédiaire en supposant que les valeurs augmentent ou diminuent de manière uniforme entre deux points.

Quand utiliser l'interpolation linéaire ?

L'interpolation linéaire est utile dans de nombreuses situations, notamment :

• Estimation de données manquantes : Si vous avez des données manquantes dans une série de données, vous pouvez utiliser l'interpolation linéaire pour les estimer.
• Prédiction de valeurs : Vous pouvez utiliser l'interpolation linéaire pour prédire des valeurs futures basées sur des données passées.
• Conversion d'unités : Vous pouvez utiliser l'interpolation linéaire pour convertir des unités de mesure.
• Analyse de données : L'interpolation linéaire peut vous aider à mieux comprendre les tendances dans vos données.

Les limites de l'interpolation linéaire

Il est important de noter que l'interpolation linéaire est une approximation. Si la relation entre les valeurs n'est pas linéaire, l'interpolation linéaire peut produire des résultats inexacts. Dans ces cas, des méthodes d'interpolation plus avancées, comme l'interpolation polynomiale ou l'interpolation spline, peuvent être nécessaires.

Méthodes d'interpolation linéaire dans Excel

Excel offre plusieurs façons d'effectuer une interpolation linéaire. Nous allons explorer les méthodes les plus courantes :

1. Utilisation de la formule simple d'interpolation linéaire
2. Utilisation de la fonction PREVISION (FORECAST)
3. Utilisation de la fonction RECHERCHEV (VLOOKUP) avec l'argument VRAI
4. Utilisation de la fonction INTERPOLATE (macro VBA)

1. Formule simple d'interpolation linéaire

La formule d'interpolation linéaire est la suivante :

y = y1 + (x - x1) * (y2 - y1) / (x2 - x1)

Où :

• x est la valeur pour laquelle vous souhaitez estimer y
• x1 et y1 sont les coordonnées du premier point connu
• x2 et y2 sont les coordonnées du deuxième point connu

Exemple pratique :

Supposons que vous ayez les données suivantes :

Vous souhaitez estimer la valeur de Y pour X = 2.

Voici comment appliquer la formule dans Excel :

1. Entrez les données dans les cellules A1:B2.
2. Dans une cellule vide, par exemple C1, entrez la valeur de X pour laquelle vous souhaitez interpoler (2).
3. Dans une autre cellule vide, par exemple D1, entrez la formule suivante :

=B1 + (C1 - A1) * (B2 - B1) / (A2 - A1)

La cellule D1 affichera la valeur interpolée, qui est 15.

Explication de la formule :

• B1 représente y1 (10)
• C1 représente x (2)
• A1 représente x1 (1)
• B2 représente y2 (20)
• A2 représente x2 (3)

2. Utilisation de la fonction PREVISION (FORECAST)

La fonction PREVISION (ou FORECAST dans les versions anglaises d'Excel) est spécialement conçue pour effectuer des prévisions linéaires, et donc des interpolations. Elle est plus simple à utiliser que la formule manuelle.

Syntaxe :

=PREVISION(x; plage_y; plage_x)

Où :

• x est la valeur pour laquelle vous souhaitez estimer y
• plage_y est la plage de cellules contenant les valeurs de y
• plage_x est la plage de cellules contenant les valeurs de x

Exemple pratique (même données que précédemment) :

1. Entrez les données dans les cellules A1:B2.
2. Dans une cellule vide, par exemple C1, entrez la valeur de X pour laquelle vous souhaitez interpoler (2).
3. Dans une autre cellule vide, par exemple D1, entrez la formule suivante :

=PREVISION(C1; B1:B2; A1:A2)

La cellule D1 affichera la valeur interpolée, qui est 15.

3. Utilisation de la fonction RECHERCHEV (VLOOKUP) avec l'argument VRAI

La fonction RECHERCHEV (ou VLOOKUP dans les versions anglaises d'Excel) peut également être utilisée pour l'interpolation linéaire, mais de manière indirecte. Elle nécessite que les valeurs de x soient triées par ordre croissant. L'argument VRAI (ou TRUE) indique à la fonction de rechercher une correspondance approximative. Si la valeur recherchée n'est pas trouvée exactement, la fonction renvoie la valeur correspondante à la plus grande valeur inférieure à la valeur recherchée.

Attention : Cette méthode est moins précise que les deux précédentes et nécessite une préparation des données spécifiques. Elle est surtout utile si vous avez déjà un tableau de données triées et que vous souhaitez effectuer une interpolation rapide.

Exemple pratique (légèrement modifié pour illustrer l'interpolation avec RECHERCHEV) :

1. Entrez les données dans les cellules A1:B3. Notez que B2 contient #N/A car c'est la valeur que nous voulons interpoler.
2. Dans une cellule vide, par exemple C1, entrez la valeur de X pour laquelle vous souhaitez interpoler (2).
3. Dans une autre cellule vide, par exemple D1, entrez la formule suivante (qui calcule l'interpolation manuellement après avoir trouvé les bornes avec RECHERCHEV) :

=SIERREUR(RECHERCHEV(C1;A1:B3;2;VRAI)+(C1-RECHERCHEV(C1;A1:A3;1;VRAI))*(INDEX(B1:B3;EQUIV(C1;A1:A3;1)+1)-RECHERCHEV(C1;A1:B3;2;VRAI))/(INDEX(A1:A3;EQUIV(C1;A1:A3;1)+1)-RECHERCHEV(C1;A1:A3;1;VRAI));"Pas d'interpolation possible")

Cette formule est complexe. Elle utilise RECHERCHEV pour trouver les bornes inférieures et supérieures de la valeur X et effectue ensuite le calcul de l'interpolation linéaire manuellement. L'erreur #N/A dans le tableau est gérée par la fonction SIERREUR.

4. Utilisation de la fonction INTERPOLATE (macro VBA)

Pour une interpolation plus flexible et potentiellement plus complexe, vous pouvez créer une fonction personnalisée en utilisant VBA (Visual Basic for Applications). Cela permet de définir une fonction INTERPOLATE qui prend en entrée la valeur de x, la plage des x et la plage des y et renvoie la valeur interpolée.

Code VBA :

Function INTERPOLATE(x As Double, x_range As Range, y_range As Range) As Variant

    Dim i As Long
    Dim x1 As Double, y1 As Double, x2 As Double, y2 As Double

    If x_range.Count <> y_range.Count Then
        INTERPOLATE = CVErr(xlErrValue)
        Exit Function
    End If

    For i = 1 To x_range.Count - 1
        If x >= x_range(i) And x <= x_range(i + 1) Then
            x1 = x_range(i)
            y1 = y_range(i)
            x2 = x_range(i + 1)
            y2 = y_range(i + 1)

            INTERPOLATE = y1 + (x - x1) * (y2 - y1) / (x2 - x1)
            Exit Function
        End If
    Next i

    INTERPOLATE = CVErr(xlErrNA) ' Return #N/A if x is outside the range

End Function

Comment utiliser la macro :

1. Ouvrez l'éditeur VBA (Alt + F11).
2. Insérez un nouveau module (Insertion > Module).
3. Collez le code VBA dans le module.
4. Fermez l'éditeur VBA.

Vous pouvez maintenant utiliser la fonction INTERPOLATE dans votre feuille de calcul comme n'importe quelle autre fonction Excel.

Exemple pratique (même données que précédemment) :

1. Entrez les données dans les cellules A1:B2.
2. Dans une cellule vide, par exemple C1, entrez la valeur de X pour laquelle vous souhaitez interpoler (2).
3. Dans une autre cellule vide, par exemple D1, entrez la formule suivante :

=INTERPOLATE(C1; A1:A2; B1:B2)

La cellule D1 affichera la valeur interpolée, qui est 15.

Bonnes pratiques et erreurs à éviter

• Vérifiez la linéarité des données : L'interpolation linéaire fonctionne mieux lorsque la relation entre les données est approximativement linéaire. Si la relation est fortement non linéaire, envisagez d'utiliser des méthodes d'interpolation plus avancées.
• Assurez-vous que les données sont triées : Si vous utilisez la fonction RECHERCHEV, assurez-vous que les valeurs de x sont triées par ordre croissant.
• Gérez les valeurs en dehors de la plage : Les méthodes d'interpolation ne sont généralement pas fiables pour extrapoler des valeurs en dehors de la plage des données connues. Soyez prudent lorsque vous utilisez l'interpolation pour prédire des valeurs futures.
• Testez vos résultats : Vérifiez toujours vos résultats en comparant les valeurs interpolées avec les valeurs réelles, si possible.
• Commentez vos formules : Ajoutez des commentaires à vos formules Excel pour expliquer ce qu'elles font. Cela facilitera la compréhension et la maintenance de vos feuilles de calcul.
• Utilisez des noms de plage : Au lieu d'utiliser des références de cellules directes (par exemple, A1:A2), utilisez des noms de plage (par exemple, "PlageX", "PlageY"). Cela rendra vos formules plus lisibles et plus faciles à modifier.

Conclusion

L'interpolation linéaire est un outil puissant pour estimer des valeurs intermédiaires dans Excel. En utilisant les différentes méthodes présentées dans cet article, vous pouvez facilement effectuer des interpolations précises et améliorer la qualité de vos analyses de données. Que vous choisissiez la formule simple, la fonction PREVISION, la fonction RECHERCHEV ou une macro VBA personnalisée, l'important est de comprendre les principes de l'interpolation linéaire et de choisir la méthode la plus appropriée à vos besoins. N'oubliez pas de toujours vérifier la linéarité de vos données et de tester vos résultats pour vous assurer de leur exactitude.