Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Polynomische Trendlinie mit Lücken in Zellen
Normale Version: Polynomische Trendlinie mit Lücken in Zellen
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2

WillWissen

27.01.2020, 07:33
Hallo Eric,

dann lad mal eine Beispieltabelle hoch, damit die Helfer ihre Vorschläge testen können. Anhand deines Bildchens in deinem Eröffnungsbeitrag wird niemand etwas nachbauen wollen.

Erische

27.01.2020, 07:59
Ich hoffe, das geht so in Ordnung Blush


Arbeitsblatt mit dem Namen 'Tabelle2'
BCD
2JanuarJanuar interpoliert
31#WERT!
42#WERT!
5324,1#WERT!
6425,8#WERT!
75#WERT!
86#WERT!
97#WERT!
10825,2#WERT!
119#WERT!
1210#WERT!
1311#WERT!
1412#WERT!
1513#WERT!
1614#WERT!
171527#WERT!
181626,4#WERT!
191726,4#WERT!
2018#WERT!
2119#WERT!
2220#WERT!
232126,4#WERT!
2422#WERT!
2523#WERT!
2624#WERT!
2725#WERT!
2826#WERT!
2927#WERT!
302826#WERT!
3129#WERT!
3230#WERT!
3331#WERT!
34
35
36
37
38
39k0#WERT!
40k1#WERT!
41k2#WERT!
42k3#WERT!
43k4#WERT!

ZelleFormel
D3=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D4=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D5=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D6=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D7=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D8=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D9=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D10=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D11=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D12=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D13=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D14=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D15=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D16=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D17=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D18=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D19=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D20=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D21=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D22=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D23=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D24=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D25=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D26=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D27=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D28=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D29=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D30=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D31=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D32=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
D33=$C$39+$C$40*[@[ ]]+$C$41*[@[ ]]^2+$C$42*[@[ ]]^3+$C$43*[@[ ]]^4
C39=INDEX(RGP(Tabelle2[Januar];Tabelle2[[ ]]^{1.2.3.4});1;5)
C40=INDEX(RGP(Tabelle2[Januar];Tabelle2[[ ]]^{1.2.3.4});1;4)
C41=INDEX(RGP(Tabelle2[Januar];Tabelle2[[ ]]^{1.2.3.4});1;3)
C42=INDEX(RGP(Tabelle2[Januar];Tabelle2[[ ]]^{1.2.3.4});1;2)
C43=INDEX(RGP(Tabelle2[Januar];Tabelle2[[ ]]^{1.2.3.4});1)
Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

schauan

27.01.2020, 20:52
Hallo Eric,

schau einer an, c39 ist ja ganz wo anders als ich dachte. Wenn dort schon #WERT steht, kann oben auch nix besseres rauskommen.
Da müsstest Du erst mal versuchen, Deine RGP-Rechnung unten in die Reihe zu bekommen. Die hat wohl Probleme mit den vielen Leerzellen und der INDEX außen rum, was soll der bezwecken? ...

Erische

28.01.2020, 07:23
Guten Morgen,

alsooo...Ich habe jetzt das Problem umgangen.
Zuerst einmal möchte ich euch für die Hilfsbereitschaft Danken!  Blush 

Für diejenigen, die das selbe Problem haben, stelle ich mal meine Lösung rein:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFG
1DatumWertJanuarJanuar interpoliertJanuar neu
2324,101#NV23,85895423,858954
3425,802#NV24,302785224,3027852
4825,20324,124,65904624,1
51527,00425,824,945061825,8
61626,405#NV25,176212225,1762122
71726,406#NV25,365930325,3659303
82126,407#NV25,525703225,5257032
92722,9825,225,665071825,2
109#NV25,79163125,791631
1110#NV25,911029125,9110291
1211#NV26,026968526,0269685
1312#NV26,141205626,1412056
1413#NV26,253550226,2535502
1514#NV26,361866226,3618662
16152726,462071327
171626,426,54813726,4
181726,426,612088526,4
1918#NV26,644005126,6440051
2019#NV26,632019626,6320196
2120#NV26,562318926,5623189
222126,426,419143626,4
2322#NV26,184788126,1847881
2423#NV25,839600625,8396006
2524#NV25,361983225,3619832
2625#NV24,728391824,7283918
2726#NV23,913336323,9133363
282722,922,8893822,9
2928#NV21,627140321,6271403
3029#NV20,095288620,0952886
3130#NV18,260549718,2605497
3231#NV16,087702516,0877025
33
34
35k023,3082804
36k10,61100522
37k2-0,06394904
38k30,00369851
39k4-8,1091E-05

ZelleFormel
E2=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F2=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G2=WENNNV([@Januar];[@[Januar interpoliert]])
E3=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F3=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G3=WENNNV([@Januar];[@[Januar interpoliert]])
E4=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F4=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G4=WENNNV([@Januar];[@[Januar interpoliert]])
E5=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F5=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G5=WENNNV([@Januar];[@[Januar interpoliert]])
E6=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F6=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G6=WENNNV([@Januar];[@[Januar interpoliert]])
E7=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F7=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G7=WENNNV([@Januar];[@[Januar interpoliert]])
E8=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F8=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G8=WENNNV([@Januar];[@[Januar interpoliert]])
E9=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F9=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G9=WENNNV([@Januar];[@[Januar interpoliert]])
E10=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F10=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G10=WENNNV([@Januar];[@[Januar interpoliert]])
E11=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F11=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G11=WENNNV([@Januar];[@[Januar interpoliert]])
E12=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F12=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G12=WENNNV([@Januar];[@[Januar interpoliert]])
E13=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F13=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G13=WENNNV([@Januar];[@[Januar interpoliert]])
E14=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F14=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G14=WENNNV([@Januar];[@[Januar interpoliert]])
E15=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F15=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G15=WENNNV([@Januar];[@[Januar interpoliert]])
E16=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F16=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G16=WENNNV([@Januar];[@[Januar interpoliert]])
E17=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F17=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G17=WENNNV([@Januar];[@[Januar interpoliert]])
E18=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F18=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G18=WENNNV([@Januar];[@[Januar interpoliert]])
E19=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F19=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G19=WENNNV([@Januar];[@[Januar interpoliert]])
E20=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F20=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G20=WENNNV([@Januar];[@[Januar interpoliert]])
E21=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F21=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G21=WENNNV([@Januar];[@[Januar interpoliert]])
E22=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F22=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G22=WENNNV([@Januar];[@[Januar interpoliert]])
E23=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F23=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G23=WENNNV([@Januar];[@[Januar interpoliert]])
E24=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F24=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G24=WENNNV([@Januar];[@[Januar interpoliert]])
E25=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F25=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G25=WENNNV([@Januar];[@[Januar interpoliert]])
E26=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F26=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G26=WENNNV([@Januar];[@[Januar interpoliert]])
E27=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F27=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G27=WENNNV([@Januar];[@[Januar interpoliert]])
E28=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F28=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G28=WENNNV([@Januar];[@[Januar interpoliert]])
E29=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F29=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G29=WENNNV([@Januar];[@[Januar interpoliert]])
E30=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F30=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G30=WENNNV([@Januar];[@[Januar interpoliert]])
E31=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F31=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G31=WENNNV([@Januar];[@[Januar interpoliert]])
E32=SVERWEIS([@[ ]];Tabelle1;2;FALSCH)
F32=$F$35+$F$36*[@[ ]]+$F$37*[@[ ]]^2+$F$38*[@[ ]]^3+$F$39*[@[ ]]^4
G32=WENNNV([@Januar];[@[Januar interpoliert]])
F35=INDEX(RGP(Tabelle1[Wert];Tabelle1[Datum]^{1.2.3.4});1;5)
F36=INDEX(RGP(Tabelle1[Wert];Tabelle1[Datum]^{1.2.3.4});1;4)
F37=INDEX(RGP(Tabelle1[Wert];Tabelle1[Datum]^{1.2.3.4});1;3)
F38=INDEX(RGP(Tabelle1[Wert];Tabelle1[Datum]^{1.2.3.4});1;2)
F39=INDEX(RGP(Tabelle1[Wert];Tabelle1[Datum]^{1.2.3.4});1)
Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Erklärung: Über VBA habe ich eine Abfrage erstellt, welche das Datum und den dazugehörigen Wert in die Tabelle in den Spalten A und B einträgt. Diese könnten auch manuell eingetragen werden.
In der zweiten Tabelle wird in Spalte E abgefragt, ob in Tabelle 1 bei dem zugehörigen Datum bereits ein Wert vorhanden ist. Dieser wird, falls vorhanden, eingetragen. In Spalte F wird dann der gesamt errechnete Wert (Polynom 4. Grades) eingetragen (Die jeweiligen Konstanten werden in F35 bis F39 berechnet). Spalte G drückt dann mein gewünschtes Ergebnis aus: Es wird abgefragt, ob in Spalte E ein #NV ausgegeben wird. Ist das der Fall, wird der Wert aus Spalte F übernommen, anderenfalls der Wert aus Spalte E.

Somit kann ich bei Tagen, welche keinen Wert eingetragen haben, jedoch erahnen, welchen Wert es ergeben könnte. Zudem kann ich Zukunftsvorhersagen treffen (Ab welchen Tag wird theoretisch der Wert X überschritten)

Ich hoffe, es ist halbwegs verständlich beschrieben und es hilft einigen weiter :19: 

LG Eric

schauan

28.01.2020, 19:20
Hallöchen,

schön, dass Du Deine Lösung allen zugänglich machst. Hier mal noch ein Tipp, wie Du auch die "Zusammenfassung" ohne Leerzellen per Formel erreichen kannst.
Den #Zahl-Fehler bekommst Du mit WENNFEHLER außen herum weg. Allerdings hättest Du dann wieder leere Zellen in der Tabelle, und die müsstest Du dann rauslöschen Sad bzw. nicht in die Tabelle1 übernehmen. Man kann diese Sache aber sicher noch verkomplizieren, ohne Tabelle1 arbeiten und mit den Formeln filtern und dafür mit INDEX, VERGLEICH/VERWEIS oder wie auch immer den RGP-Bereich eingrenzen ….

Arbeitsblatt mit dem Namen 'Tabelle1'
IJ
1Januar
2324,1
3425,8
4825,2
51527
61626,4
71726,4
82126,4
92722,9
10#ZAHL!#ZAHL!
11#ZAHL!#ZAHL!

ZelleFormel
I1{=INDEX(Tabelle2[[#Alle];[ ]];KKLEINSTE(WENN((NICHT(ISTNV(Tabelle2[[#Alle];[Januar]])));ZEILE(Tabelle2[[#Alle];[ ]]));ZEILE(A1)))}
J1{=INDEX(Tabelle2[[#Alle];[Januar]];KKLEINSTE(WENN((NICHT(ISTNV(Tabelle2[[#Alle];[Januar]])));ZEILE(Tabelle2[[#Alle];[ ]]));ZEILE(A1)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
Seiten: 1 2
Clever-Excel-Forum > Office-Anwendungen > Excel > Polynomische Trendlinie mit Lücken in Zellen