Excel-Formeln zur Lösung einer kubischen Gleichung

Eingestellt: 10.10.2011
Letzte Änderung: 03.06.2013


 

Kubische Gleichung (Gleichung 3. Grades)

Die allgemeine Form der kubischen Gleichung lautet

(#1)

Division durch A führt auf die Normalform

(#2)

wobei

(#3)

ist.

Durch Substitution

(#4)

erhält man die reduzierte Form

(#5)

wobei

(#6)

und

(#7)

ist.

In Abhängigkeit vom Vorzeichen der Diskriminante, welche dem Ausdruck

(#8)

entspricht[1], ergibt sich:

  • für eine positive Diskriminante gibt es eine reelle Lösung und zwei kunjugiert komplexe Lösungen (Fall I),
  • für eine Diskriminante mit dem Wert null gibt es entweder eine dreifache reelle Lösung (Fall II) oder eine einfache reelle Lösung und eine doppelte reelle Lösung (Fall III),
  • für eine negative Diskriminante gibt es drei verschiedene reelle Lösungen (Fall IV) , die sich auf goniometrischen Wege errechnen lassen (irreduzierter Fall, casus irreducibilis).
  • Mit Hilfe der Cardanischen Lösungsformel für die reduzierte Gleichung lässt sich bei einer positiven Diskriminante die reelle Lösung errechnen mit

    (#9)

    wobei

    (#10)

    und

    (#11)

    ist.

    Nimmt die Diskriminante den Wert null an, so lässt sich die einfache reelle Lösung bestimmen mit

    (#12)

    und die doppelte reelle Lösung mit

    (#13)

    Ist die Diskrimininante negativ (irreduzierter Fall, casus irreducibilis), errechnen sich die drei verschiedenen Lösungen mit

    (#14)

    sowie

    (#15)

    und

    (#16)

     

    Excel-Formeln zur Lösung einer kubischen Gleichung

    Zur Lösung einer kubischen Gleichung sollen Excel-Formeln aufgestellt werden, wobei diese schrittweise aus dem oben beschriebenen, formalen Weg abgeleitet sind. Darüber hinaus sollen die Excel-Formeln mit den Lösungen den Fehlerwert #ZAHL! liefern, sofern keine weitere (verschiedene) reelle Lösung vorliegt. Auf eine Zusammenfassung der einzelnen Excel-Formeln zu einer einzigen wird bewußt verzichtet.

    Werden für die verwendeten Zellen Namen entsprechend ihres Gliedes in der Normalform definiert[2] , so lässt sich schreiben für p

    p:=b-a^2/3

    und für q:

    q: =2/27*a^3-a*b/3+c

    Die Diskriminante d errechnet sich aus

    d: =q^2/4+p^3/27

    Für die Werte u und v kann geschrieben werden

    u: =(-q/2+WURZEL(d))^(1/3)

    bzw.

    v: =(-q/2-WURZEL(d))^(1/3)

    Für den casus irreducibilis werden die Nebenrechnungen

    rho: =(ABS(p)/3)^0,5

    und

    phi: =ARCCOS((-q/2)/(ABS(p)^3/27)^0,5)

    eingeführt, so dass die Lösungen für eine kubische Gleichung nach Resubstitution lauten:

    x1: =WENN(d>0;u+v;WENN(UND(d=0;p=0;q=0);0;
    WENN(d=0;2*u;2*rho*COS(phi/3))))-a/3
    x2: =WENN(d>0;#ZAHL!;WENN(UND(d=0;p=0;q=0);#ZAHL!;
    WENN(d=0;-u;-2*rho*COS(phi/3-PI()/3))))-a/3
    x3: =WENN(d<0;-2*rho*COS(phi/3+PI()/3)-a/3;#ZAHL!)
     

    Benutzerdefinierte Funktion zur Lösung einer kubischen Gleichung

    Auch bei der folgenden benutzerdefinierten Funktion CubicEq(dblA,dblB,dblC,dblD) zur Lösung einer Gleichung 3. Grades wird eine Fallunterscheidung anhand des Vorzeichens der Diskriminante vorgenommen.

    Zusätzlich stellt die Funktion CubicEq(dblA,dblB,dblC,dblD) wahlweise die Lösungen aus der allgemeinen Form und aus der Normalform bereit, wobei die Berechnungen einer dritten Wurzel als auch des in #14 bis #16 stehenden Arkuskosinus-Ausdrucks in separate benutzerdefinierte Funktionen ausgegliedert sind.

    Function CubicEq(ByVal dblA As Double, _
                     ByVal dblB As Double, _
                     ByVal dblC As Double, _
            Optional ByVal dblD As Variant) As Variant
    
        Dim a                               As Double
        Dim b                               As Double
        Dim c                               As Double
        Dim p                               As Double
        Dim q                               As Double
        Dim d                               As Double
        Dim u                               As Double
        Dim v                               As Double
        Dim rho                             As Double
        Dim phi                             As Double
        Dim arr(0 To 2, 0 To 0)             As Variant
        
        Const pi         As Double = 3.14159265358979
        Const intDecimal As Integer = 10
        
        If IsMissing(dblD) Then
            a = dblA
            b = dblB
            c = dblC
        Else
            If dblA = 0 Then
                arr(0, 0) = CVErr(xlErrDiv0)
                arr(1, 0) = CVErr(xlErrDiv0)
                arr(2, 0) = CVErr(xlErrDiv0)
                CubicEq = arr
                Exit Function
            Else
                a = dblB / dblA
                b = dblC / dblA
                c = dblD / dblA
            End If
        End If
    
        p = b - a ^ 2 / 3
        q = 2 / 27 * a ^ 3 - a * b / 3 + c
        
        d = q ^ 2 / 4 + p ^ 3 / 27
    
        Select Case Round(d, intDecimal)
            Case Is > 0
                u = Sqr3(-q / 2 + Sqr(d))
                v = Sqr3(-q / 2 - Sqr(d))
                arr(0, 0) = u + v - a / 3
                arr(1, 0) = CVErr(xlErrNum)
                arr(2, 0) = CVErr(xlErrNum)
            Case 0
                p = Round(p, intDecimal)
                q = Round(q, intDecimal)
                If p = 0 And q = 0 Then
                    arr(0, 0) = -a / 3
                    arr(1, 0) = CVErr(xlErrNum)
                Else
                    arr(0, 0) = 3 * q / p - a / 3
                    arr(1, 0) = -3 * q / (2 * p) - a / 3
                End If
                arr(2, 0) = CVErr(xlErrNum)
            Case Is < 0
                rho = Sqr(Abs(p) / 3)
                phi = Arccos((-q / 2) / Sqr(Abs(p) ^ 3 / 27))
                arr(0, 0) = 2 * rho * Cos(phi / 3) - a / 3
                arr(1, 0) = -2 * rho * Cos(phi / 3 - pi / 3) - a / 3
                arr(2, 0) = -2 * rho * Cos(phi / 3 + pi / 3) - a / 3
        End Select
        
        CubicEq = arr
           
    End Function

     

    Function Arccos(ByVal dblX As Double) As Double
        
        Arccos = Atn(-dblX / Sqr(-dblX * dblX + 1)) + 2 * Atn(1)
    
    End Function

     

    Function Sqr3(ByVal dblX As Double) As Double
    
        Select Case dblX
            Case 0
                Sqr3 = 0
            Case Is > 0
                Sqr3 = (dblX) ^ (1 / 3)
            Case Is < 0
                Sqr3 = -(Abs(dblX)) ^ (1 / 3)
        End Select
            
    End Function

     

    Beispiel

    Abbildung 1 zeigt, dass bei Betrachtung des Zusammenhangs zwischen dem Vorzeichen der Diskriminante und der Anzahl der Nullstellen sich vier Fälle unterscheiden lassen. Nimmt die Diskriminante einen positiven Wert an, so gibt es eine reelle Lösung (Fall I). Ist die Diskriminante null, so gibt es entweder eine dreifache reelle Lösung (Fall II) oder eine doppelte reelle Lösung und eine einfache reelle Lösung (Fall III). Bei einer negativen Diskriminante gibt drei verschiedene reelle Lösungen (Fall IV).

    Die Lösungen werden sowohl mit den vorgestellten Excel-Formeln als auch der benutzerdefinierten Funktion CubicEq(dblA,dblB,dblC,dblD) errechnet.


    Formeln in der Tabelle:
    -Normalform:
    B10: =C5/$B$5
    C10: =D5/$B$5
    D10: =E5/$B$5
    -Reduzierte Form:
    B15: =C10-B10^2/3
    C15: =2/27*B10^3-B10*C10/3+D10
    -Diskriminante:
    C19: =C15^2/4+B15^3/27
    -Nebenrechnungen:
    C23: =(-C15/2+WURZEL(C19))^(1/3)
    C24: =(-C15/2-WURZEL(C19))^(1/3)
    C26: =(ABS(B15)/3)^0,5
    C27: =ARCCOS((-C15/2)/(ABS(B15)^3/27)^0,5)
    -Lösungen nach Resubstitution:
    C31: =WENN(C19>0;C23+C24;WENN(UND(C19=0;B15=0;C15=0);0;
    WENN(C19=0;2*C23;2*C26*COS(C27/3))))-B10/3
    C32: =WENN(C19>0;#ZAHL!;WENN(UND(C19=0;B15=0;C15=0);#ZAHL!;
    WENN(C19=0;-C23;-2*C26*COS(C27/3-PI()/3))))-B10/3
    C33: =WENN(C19<0;-2*C26*COS(C27/3+PI()/3)-B10/3;#ZAHL!)
    bzw.
    C31:C33: {=CubicEq(B5;C5;D5;E5)}
    bzw.
    C31:C33: {=CubicEq(B10;C10;D10)}

    Abbildung 1: Excel-Formeln zur Lösung kubischer Gleichungen.

     

    [1] Bitte beachten Sie, dass sich in der Literatur auch davon abweichende Definitionen der Diskriminante finden.

    [2] Bitte beachten Sie, dass ab Excel 2007 der Name c nicht mehr zulässig ist. Wählen Sie in diesem Fall anstelle von c einen anderen, gültigen Namen bspw. c_.

     

    Haben Sie Fragen oder Anregungen zu diesem Artikel?

    Dann senden Sie einfach eine Nachricht an:

    E-Mail:

     

    Lesen Sie auch:
    ___________________________________________________________________

    Excel-Formeln zur Lösung einer quadratischen Gleichung