Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1552to1556
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Ermittlung von nächst gelegenen Zahlen

Ermittlung von nächst gelegenen Zahlen
02.05.2017 19:26:29
nächst
Hallo allseits,
ich habe eine Excel-Tabelle mit verschiedenen Werten. Einige Werte davon sind vorgegeben, andere aber nicht. Die fehlenden Werte will ich näherungsweise bestimmten.
Meine Formel dafür: =B4+(B7-B4)*(A5-A4)/(A7-A4) = 1,86667.
Das möchte ich jetzt für alle fehlenden Werte machen. Aber leider sind es ganz viele, sodass ich es über eine Formel lösen möchte (ggf. mit Hilfsspalte). In der Formel müsste Excel z.B. von einer Zelle ausgehen und automatisch die nächsten Zahlen nehmen. Also im Beispiel oben, erkennen, dass es mit 2 und 1,6 rechnen muss.
Danach müsste Excel dann 1,6 und 1,4 nehmen, um die Lücke zwischen den Werte zu schließen.
A1: 01.01 B4: 2
A2: 02.01 B5: 1,86667
A3: 03.01 B6:
A4: 04.01 B7: 1,6
A5: 05.01 B8:
A6: 06.01 B9
A7: 07.01 B10: 1,4
A8: 08.01 B11:
A9: 09.01 B12:
A10:10.01 B13: 2
A11:11.01 B14
[...]
Hätte jemand dazu einen Vorschlag? Habe es mit einer Verschachtelung von Wenn-Funktionen probiert, aber es sind zu viele Werte. Ich bräuchte eine Formel, ohne VBA.
Vielen Dank für jeden Input!

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ermittlung von nächst gelegenen Zahlen
02.05.2017 19:28:48
nächst
A4: 01.01 B4: 2
A5: 02.01 B5: 1,86667
A6: 03.01 B6:
A7: 04.01 B7: 1,6
A8: 05.01 B8:
A9: 06.01 B9
A10: 07.01 B10: 1,4
A11: 08.01 B11:
A12: 09.01 B12:
A13: 10.01 B13: 2
A14: 11.01 B14
[...]
AW: Lösungsformel ohne Hilfsspalte möglich ...
02.05.2017 20:38:47
...
Hallo Denver,
... und zwar mit einer benannten Formel, um die Formellänge noch einigermaßen überschaubar zu halten und gleichzeitig auch Zellenwerte für ungleichen Zeiträumen ohne Daten ermitteln zu können.
Im Namensmanager weise folgende Formel z.B. den Namen Z_nxt zu:
=WENN(INDEX(!$A:$A;ZEILE()-1)="";"";WENNFEHLER(VERGLEICH(1;INDEX(--(INDEX(!$B:$B;ZEILE()+1):INDEX(!$B:$B;ZEILE()+99)""););)+ZEILE();VERWEIS(9^9;INDEX(Tabelle1!$A:$A;ZEILE()):INDEX(!$A:$A;ZEILE()+99);ZEILE(INDEX(Tabelle1!$A:$A;ZEILE()):INDEX(!$A:$A;ZEILE()+99)))))
Dann markiere B4:B## (bis zur letzten Datumszeile) danach aktiviere [F5] dort "Inhalte" und danach die Option "Leerzellen" und dann "ok".
Damit sind alle gesuchten Datenwerte als Leerzellen markiert. Gib nun in der Eingabezeile folgende Ergebnisformel ein:
=WENN(INDEX(A:A;ZEILE())="";"";INDEX(B:B;ZEILE()-1)+(INDEX(B:B;Z_nxt)-INDEX(B:B;ZEILE()-1))*(INDEX(A:A;ZEILE())-INDEX(A:A;ZEILE()-1))/(INDEX(A:A;Z_nxt)-INDEX(A:A;ZEILE()-1)))
und betätige danach die Tastenkombination [Strg] + [Enter] und Du hast damit alle gesuchten Ergebniswerte
Gruß Werner
.. , - ...
Anzeige
AW: Lösungsformel ohne Hilfsspalte möglich ...
02.05.2017 21:19:16
Denver
Das ist wirklich eine große Formel. Ich wollte die Formel direkt in den Zellen hinterlegen. Also sowas wie Wenn(Nicht(Istfehler(SVerweis([...] und wenn er nichts findet, bestimmt er es näherungsweise.
Wüsstest du eine einfachere Lösung, evtl. mit Hilfsspalte?
AW: die Lösung dafür hatte ich aufgezeigt ...
02.05.2017 21:24:09
...
Hallo Denver,
... und damit Deinen Wunsch: "ich wollte die Formel direkt in den Zellen hinterlegen" realisiert.
Und es sind genau genommen zwei Formeln und nicht nur eine.
Hast Du denn meinen Vorschlag getestet?
Ich gehe jetzt für heute gleich offline. Könnte also auf eine evtl. weitere nachfrgae erst Morgen wieder antworten.
Gruß Werner
.. , - ...
Anzeige
AW: die Lösung dafür hatte ich aufgezeigt ...
02.05.2017 23:07:55
Denver
Hi, danke, ich habe deine Lösung getestet.
Ich habe alles in eine Wenn dann-Funktion eingegeben: hier habe ich es in eine Excel geschrieben: https://www.file-upload.net/download-12476388/Test-Kopie.xlsx.html
Soweit klappt es, aber ich habe eine Zirkelbezugswarnung, wenn ich die Formel nach unten ziehe;
01.01.2017 2
02.01.2017 1,866666667
03.01.2017 Zirkelbezugswarnung
04.01.2017 1,6
Das geht dann auch bei den anderen Lücken so. Wenn ich auf iterative Berechnung umstelle, geht es. Aber kann ich das auch ohne Zirkelbezugswarnung rechnen. Oben würde dann statt der Zirkelbezugswarnung auch 1,866666667 stehen.
Anzeige
AW: die Lösung dafür hatte ich aufgezeigt ...
02.05.2017 23:08:31
Denver
Ups, gerade gesehen, dass man hier direkt Uploads machen kann...
AW: Aktivierung Option "iterative Berechnung"...
03.05.2017 08:19:26
...
Hallo Denver,
... wäre in dem Fall Pflicht (sorry, hatte gestern Abend vergessen darauf hinzuweisen), wenn Du ohne Hilfsspalte auskommen willst und keine Zirkelbezugswarnung erhalten willst.
Ohne die Option kannst Du die Ergebnisse nur in einer Ergebnisspalte ermitteln. Z.B. in Spalte C
In C4 und dann nach unten kopieren:
=WENN(A4="";"";WENN(B4;B4;C3+(WENNFEHLER(INDEX(B:B;VERGLEICH(1;INDEX(--(B5:B36""););)+ZEILE());C3)-C3)
*(A4-A3)/(WENNFEHLER(INDEX(A:A;VERGLEICH(1;INDEX(--(B5:B18""););)+ZEILE());A4)-A3)))

Gruß Werner
.. , - ...
Anzeige
AW: Aktivierung Option "iterative Berechnung"...
03.05.2017 15:21:05
Denver
Danke, ich habe es jetzt mal probiert. Ein Problem ist, dass die Werte erst negativ sind und dann positiv werden. Also zuerst z.B. -1,6 dann -1,4. Dann bestimmt er die Zwischenwerte näherungsweise. Die nächsten gegebenen Zahlen sind aber positiv und da ermittelt er negative Werte zwischen. Gibt es dafür noch eine Lösung?
AW: kann ich nur bedingt nachvollziehen ...
03.05.2017 15:54:13
...
Hallo,
... hier mein entsprechende Beispieldaten:
 ABC
401.01.2017-1,6-1,600
502.01.2017 -1,533
603.01.2017 -1,467
704.01.2017-1,4-1,400
805.01.2017 -0,267
906.01.2017 0,867
1007.01.201722,000
1108.01.2017 0,333
1209.01.2017 -1,333
1310.01.2017-3-3,000
1411.01.2017 -3,000
15   

Formeln der Tabelle
ZelleFormel
C4=WENN(A4="";"";WENN(B4;B4;C3+(WENNFEHLER(INDEX(B:B;VERGLEICH(1;INDEX(--(B5:B36<>""); ); )+ZEILE()); C3)-C3)*(A4-A3)/(WENNFEHLER(INDEX(A:A;VERGLEICH(1;INDEX(--(B5:B18<>""); ); )+ZEILE()); A4)-A3)))

Gruß Werner
.. , - ...
Anzeige
AW: kann ich nur bedingt nachvollziehen ...
03.05.2017 16:29:24
Denver
Bei meinen 15.000 Zeilen ist das irgendwie so. Ich poste gleich mal was. Vllt. liegt es daran, dass bei mir das erste Datum nicht der 01.01.2017 ist, sondern sich ändert. In deinem Beispiel bekomme ich Werte Fehler
AW: dazu an- bzw. vorbemerkt ...
03.05.2017 16:53:35
...
Hallo,
... der Interpolationsberechnung ist es egal mit welchem Datum Deine Werte beginnen, sie setzt aber momentan voraus, dass kein Datumswert ausgelassen wurde und es nicht mehr als 30 Tage ohne Werte in Spalte B gibt.
Gruß Werner
.. , - ...
AW: dazu an- bzw. vorbemerkt ...
03.05.2017 18:48:01
Denver
Hallo Werner,
anbei ein Beispiel. Die Grafik zeigt die Punkte und die Berechnungen. Ab 01.05.2027 sollten die Punkten und damit die Gerade positiv sein, aber ab 01.05.2027 wird es negativ. Aber ab da an ist es positiv.
Edit: Ich kann hier keine Dateien hochladen. https://www.file-upload.net/download-12477611/Test-Kopie.xlsx.html
In der Grafik sind die hohen Punkte gegeben. Die Berechnung dazwischen müsste ja auch positiv sein...
Anzeige
AW: nachgefragt ...
03.05.2017 19:43:21
...
hallo,
... hast Du einen Mac im Einsatz? Sorry, aber ich lade keine Dateien von einem anderen Server. Vielleicht ja jemand Anderes.
Gruß Werner
.. , - ...
AW: nachgefragt ...
03.05.2017 21:46:35
Denver

Die Datei https://www.herber.de/bbs/user/113291.xlsx wurde aus Datenschutzgründen gelöscht


Ok, sie war wohl einfach zu groß
AW: nun nachvollziehbar und erkennbar ...
04.05.2017 11:29:46
...
Hallo Denver,
... Du hattest meine Hinweise nicht beachtet. Zwischen Deinen gesetzten Datenwerten waren viel mehr Leerzellen als nur 30 und Du hattest auch noch die Formel mit dem Zirkelbezug in Spalte B zu stehen.
In Tabelle1!B4 überschreibe die dortige falsche Formel mit der auf Deine neuen Verhältnisse (Daten in Tabelle2 mit großen Lücken wie folgt:
=WENN(Tabelle2!A4="";"";WENN(Tabelle2!B4;Tabelle2!B4;Tabelle1!B3+(WENNFEHLER(INDEX(Tabelle2!B:B;VERGLEICH(1;INDEX(--(Tabelle2!B5:B333""); ); )+ZEILE());B3)-B3)*(Tabelle2!A4-Tabelle2!A3)/(WENNFEHLER(INDEX(Tabelle2!A:A;VERGLEICH(1;INDEX(--(Tabelle2!B5:B333""); ); )+ZEILE());Tabelle2!A4)-Tabelle2!A3)))
und kopiere diese nach unten und lösche die Spalte C in beiden Tabellen.
Gruß Werner
.. , - ...
Anzeige
AW: nun nachvollziehbar und erkennbar ...
04.05.2017 14:02:23
Denver
Vielen Dank! Nur 1 Problem habe ich noch. In Sheet Tabelle 2 sind die Werte gegeben. Da wo keine Werte drin sind, ist auch kein Datum. Also 14.01 die Zelle danach ist der 31.01. Dann müsste ich die Formel ja noch anpassen?
AW: neu nachgefragt ...
04.05.2017 15:28:08
...
Hallo Denver,
... und wie liegen Dir die Daten der Tabelle2 nun wirklich vor?
Die fehlenden Datumswerte kann man sicher generieren.
Gruß Werner
.. , - ...
AW: neu nachgefragt ...
04.05.2017 19:42:52
Denver
Hallo, in dieser Form sind die Daten https://www.herber.de/bbs/user/113321.xlsx
Ich könnte wahrscheinlich ein Extra-Sheet machen und dort einen SVerweis machen...
Anzeige
AW: dazu folgendes ...
04.05.2017 20:19:17
...
Hallo Denver,
... verschiebe Deine Daten aus Tabelle2!A:B nach z.B.: Tabelle2!H1:I8
In Tabelle2!A4 schreibe =H1 und in Tabelle2!A5: =WENN(MAX(A$4:A4)=MAX(H:H);"";A4+1)
Letzteres Formel kopiere die Formel weit genug nach unten.
In B4: =WENNFEHLER(SVERWEIS(A4;H:I;2;);"") und Formel ebenfalls nach unten kopieren.
In Tabelle1!B4 ergänze in der Formel lediglich "" (nachfolgend fett markiert)
=WENN(Tabelle2!A4="";"";WENN(Tabelle2!B4"";Tabelle2!...
und nach unten kopieren
Gruß Werner
.. , - ...
AW: dazu folgendes ...
04.05.2017 21:49:48
Denver
Ok, vielen Dank für deine Hilfe! Das funktioniert.
Anzeige
AW: damit ist der thread auch abgeschlossen owT
05.05.2017 11:41:45
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige