Interpolations mit Excel und VBA

Computer: Software & Programmierung Office Software
#1

Hallo,
ich würde gern mit Excel und VBA folgendes Problem lösen:

Ich habe eine Datenbank mit x und y-Werten, z.B.
x     y
1    3.4
2    5.6
3    7.1
4    6.5
5    5.9
7    6.8
10  8.9
 Diese Datenbank liegt im Tabellenblatt DataBase

Nun möchte ich in meinem Berechnungs-Tabellenblatt den y-Wert von x abrufen. Als steht dort .B. in A1 der x-Wert 3.5.
In Zelle A2 soll daraufhin automatisch der Linear interpolierte Wert angegeben werden, also hier 6.8

Ich hatte mir das so gedacht, dass man das ganze mit Change macht. Also wenn sich A1 ändert, wird A2 berechnet. Bisher kenne ich es allerdings nur so, dass Change nur im Aktuellen Arbeitsblatt angewendet werden kann. Wie kann ich dann die Daten aus dem Datenblatt DataBase holen.
Dann wollte ich die x-Werte so lange durchgehen, bis x>A2 ist. Mit dem Wert könnte man dann ja durch die umgebenden Zellen y berechnen.
Wie kann ich das Suchen der Zelle x>A2 am besten realisieren und dann die Werte von Zeile und Spalte irgendwo speichern?

Vielen Dank
Stefan

#2

Hallo Stefan
dein Wunsch ist ja das per VBA zu lösen, unmöglich ist es aber auch per Formel nicht.
hier mal ein Beispiel

Eine Anmerkung:
Ich weiß nicht was für ein Excel du benutzt, aber zu mindestens die deutsche Version interpretiert den Punkt als Trennzeichen für ein Datum.
Excel macht dann aus 3.4 den 3.April [2014]
WERT: 41732
Da könnte reichlich murks raus kommen
Ich rate dir dringend das Komma zu benutzen.

Gruß Holger

#3

dein Wunsch ist ja das per VBA zu lösen, unmöglich ist es aber
auch per Formel nicht.
hier mal ein Beispiel

Hallo Holger,

selbst habe ich noch nicht nach Lösungen gesucht da ich vorher Stefan noch was fragen möchte und im Anschluß mache. Je nach Feedback beginne ich dann oder auch nicht wenn keines kommt.

Ich habe dein Beispiel nachgebaut:
http://www.file-upload.net/download-8623678/kwHolger…

Dabei fiel mir auf, bei 3,5 in B4 ergibt es korrekt 6,8.
Bei B4: 8,5 geht es aber schief, es kommt auch 6,8 raus.

Meine Frage an dich und alle, habe ich beim „übertragen“ einen Fehler gemacht? Mehrfach die Formeln usw. überprüft, sehe aber keinen Übertragungsfehler.

Wenn ich nicht den Wald vor lauter Bäumern nicht gesehen habe sagt mein bislang unbestätigtes Bauchgefühl irschendwie hat es damit zu tun daß die y-Werte von Wert zu Wert mal abnehmen mal zunehmen.

Abgesehen davon berechnest du den Mittelwert und interpolierst nicht.

Gruß
Reinhard

#4

Hallo Stefan,

ich würde gern mit Excel und VBA folgendes Problem lösen:

was ist dir lieber, eine Excel-Formel_Fösung oder eine Excel-Vba-Lösung?

Ich hatte mir das so gedacht, dass man das ganze mit Change
macht. Also wenn sich A1 ändert, wird A2 berechnet.

Kommt darauf an durch was sich A1 ändert. Manuelle Werteingabe in A1 löst das Change-Ereignis des Blattes aus. Steht in A1 eine Formel die sich auf andere Zellen bezieht und eine Änderung einer anderen Zelle ändert das Formelergebnis in A1 so wird der Change-Ereignis-Code nicht ausgelöst.

Bisher kenne ich es allerdings nur so, dass Change nur im Aktuellen
Arbeitsblatt angewendet werden kann.

Naja, Definitionssache was das aktive, was das aktuelle Blatt ist. Um manuell in eine Zelle etwas einzugeben muß das Blatt ja aktiviert, quasi aktuell sein. Ändert man per Vba einen Zellwert, völlig egal in welchem Blatt so wird das durchgeführt ohne daß das entsprechend Blatt aktiviert wird.

Wie kann ich dann die
Daten aus dem Datenblatt DataBase holen.

Indem du im Change-Ereignis-Code A1 „überwachen“ läßt, geschieht eine Änderuung in A1 füllt dann der Code A2 aus.
Alternativ schreibe eine SVerweis()-Formel in A2.

Wie kann ich das Suchen der Zelle x>A2 am besten
realisieren

Das kann man dir sagen, egal Vba oder Formeln.

und dann die Werte von Zeile und Spalte irgendwo
speichern?

Das kann man dir auch sagen, vorher mußt du uns aber sagen wohin denn A1 bzw. A2 gespeichert werden sollen u.ä.

Gruß
Reinhard

#5

Hallo Reinhard
hasst vollkommen recht.

Vergleich() in B5 liefert die Position vom Wert,
von 1-5 ist Wert und Position identisch
dann nicht mehr da der nächste Wert 7 und nicht 6 ist :smile:

Abgesehen davon berechnest du den Mittelwert und interpolierst nicht.

Überredet, ich wollte eigentlich nur aufzeigen wie man an die Werte selbst kommt.

Habe Link aktualisiert und meine Exceldatei hier zum Test,
Nachbauen muss ja nicht sein…

Gruß Holger

#6

Grüezi Holger

Eine Anmerkung:
Ich weiß nicht was für ein Excel du benutzt, aber zu
mindestens die deutsche Version interpretiert den Punkt als
Trennzeichen für ein Datum.
Excel macht dann aus 3.4 den 3.April [2014]
WERT: 41732
Da könnte reichlich murks raus kommen
Ich rate dir dringend das Komma zu benutzen.

…vielleicht ist er ja auch in der Schweiz zu Hause, so wie ich, und da ist dann der Punkt als Dezimaltrennzeichen völlig korrekt… :wink:

Deutsch ist hier nicht gleich Deutsch (in den Ländereinstellungen des BS)

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

Habe Link aktualisiert und meine Exceldatei hier zum Test,
Nachbauen muss ja nicht sein…

Hallo Holger,

danke für die xlsx. Auch für den Einbau der „Pfeile“, so kann man locker durchprüfen. Der Anfrager kann sich freuen, Problem gelöst.

Ich selbst habe nicht nach einer Formellösung gesucht da ich ja sah daß du schon eine gezeigt hast. Und wußte so die Sache in sehr professionellen Händen, gerade bei Matrixformeln.

Daß ich überhaupt deine Lösung nachbaute lag einzig an
=(C4+C5)/2
also Mittelwert und keine Interpolation. So begann ich genauer zu prüfen.

Jetzt muß nur noch der Anfrager zu Potte kommen und uns sagen was er sich da wie vorstellt mit seiner A1/A2-Speicherung.

Gruß
Reinhard

#8

Hallo,
sorry für meine späte Rückmeldung. Ich habe mich inzwischen selbst nochmal näher mit vba beschäftigt und eine Lösung in VBA gefunden. Das hat den Vorteil, dass ich z.B. auch mehrere Werte gleichzeitig recht einfach interpolieren kann und das Skript für andere Fälle anpassen. Ich finde in einem Code mit Kommentaren geht das einfacher, als ein einer endlosen Zeile an Formeln.

Hier mal meine Lösung:
Sub y_lin_interpolieren()
    ausgabezeile = 3
    ausgabespalte = 2
    spaltex = 1                             'Spalte, in der x-Werte stehen
    spaltey = 2                             'Spalte, in der y-Werte stehen
    startx_y = 2                            'ab welcher zeile fangen Wertepaare an?
    naechst = startx_y
    tab_datenbank = „DataBase“              'in welchem Tabellenblatt liegen Daten?
    tab_auswertung = „Auswertung“
    x = Sheets(tab_auswertung).Cells(2, 2)  'Wo steht x-Wert im Auswertungsblatt?
    Sheets(tab_datenbank).Activate
    Cells(startx_y, spaltex).Select
    i = 0
    While (ActiveCell.Value „“)
        i = i + 1
        If ActiveCell.Value = x) Then
        ungueltig = 0
    Else
        ungueltig = 1
    End If
    'Berechnung von y
    If ungueltig Then
        Sheets(tab_auswertung).Cells(ausgabezeile, ausgabespalte) = „Außerhalb des Diagramms“
    Else
        If naechst = startx_y Then
           y = Sheets(tab_datenbank).Cells(naechst, spaltey)
       Else
           y1 = Sheets(tab_datenbank).Cells(naechst - 1, spaltey)
           x1 = Sheets(tab_datenbank).Cells(naechst - 1, spaltex)
           m = (Sheets(tab_datenbank).Cells(naechst, spaltey) - Sheets(tab_datenbank).Cells(naechst - 1, spaltey)) / (Sheets(tab_datenbank).Cells(naechst, spaltex) - Sheets(tab_datenbank).Cells(naechst - 1, spaltex))
            y = y1 + (x - x1) * m
        End If
        Sheets(tab_auswertung).Cells(ausgabezeile, ausgabespalte) = y
    End If
End Sub

Momentan muss das noch über einen Button ausgeführt werden. Mal sehen, ob ich das noch automatisiert hinbekomme.

Trotzdem vielen Dank für eure Antworten. Ich finds halt in VBA nur übersichtlicher, wenn man es mal für eine andere Anwendung ändern muss.

Grüße
Stefan