Microsoft Excel

Herbers Excel/VBA-Archiv

Interpolation (nicht linear)

Betrifft: Interpolation (nicht linear) von: Frida
Geschrieben am: 14.08.2014 14:25:19

Hallo zusammen,

nach längerer Recherche, bin ich zum Interpolieren von Werten auf folgende Formel gestoßen:

=TREND(INDIREKT("B"&(VERGLEICH(C2;A2:A11)+1)&":B"&VERGLEICH(C2;A2:A11)+2);INDIREKT("A"&(VERGLEICH(C2; A2:A11)+1)&":A"&VERGLEICH(C2;A2:A11)+2);C2)

Meine Frage ist nun, ob diese Formel auch mit Bezug auf ein anderes Arbeitsblatt funktioniert. Und zwar sind die Datenbereiche in Arbeitsblatt2 und der Wert den ich Suche in Arbeitsblatt 1. In Arbeitsblatt 1 steht auch die Formel.

Jemand eine Idee?

  

Betrifft: AW: Interpolation (nicht linear) von: Daniel
Geschrieben am: 14.08.2014 14:34:54

Hi
geht schon, aber verwende nicht
INDIREKT("B"&(VERGLEICH(C2;A2:A11)+1)&":B"&VERGLEICH(C2;A2:A11)+2)
sondern stattdessen:
Index(B:B;VERGLEICH(C2;A2:A11)+1):Index(B:B;VERGLEICH(C2;A2:A11)+2)
(im zweiten Teil dann entsprechend)
hier sollte dann auch klar sein, wie du die Bezüge auf ein anderes Blatt erstellst.

Gruß Daniel


  

Betrifft: AW: Interpolation (nicht linear) von: Frida
Geschrieben am: 14.08.2014 15:16:22

Hallo Daniel,

vielen Dank für deine schnelle Hilfe! Leider wird jetzt ein Bezugsfehler angezeigt. Die Formel sieht angepasst so aus. Hab ich etwas übersehen? Oder vielleicht könntest du mir die Formel kurz erklären, da ich sie eigentlich nicht wirklich verstehe.

=TREND(INDEX(A:A;VERGLEICH(BB252;'Arbeitsblatt2'!B1:B1655)+0):INDEX(A:A;VERGLEICH(BB252; 'Arbeitsblatt2'!B1:B1655)+1);(INDEX(B:B;VERGLEICH(BB252;'Arbeitsblatt2'!B1:B1655)+0):INDEX(B:B; VERGLEICH(BB252;'Arbeitsblatt2'!B1:B1655)+1);BB252))


  

Betrifft: AW: Interpolation (nicht linear) von: Daniel
Geschrieben am: 14.08.2014 15:54:06

Hi

die Funktion INDEX ist ganz einfach:

INDEX(Zellbereich;Zeilennummer;Spaltennummer)

und gibt dir einen Bezug zu der Zelle aus dem angegebenen Zellbereich mit der Zeilennummer und Spaltennummer (im Prinzip die Koordinaten)
bei einem Einspaltigen Zellbezug kann man die Spaltennummer weglassen.
bei den Zeilen- und Spaltennummern muss man beachten, dass man hier nicht die absoluten Zeilen- und Spaltennummern des Excelblattes verwenden muss, sondern die relativen, bezogen auf den angegebenen Zellbereich.
Beispiel:
Index(J34:M100;2;3) ist die Zelle L35.

die Vergleichsfunktion liefert die Zeilennummer, an der sich der Suchbegriff in der Suchmatrix befindet.
Auch hier gilt, es wird die relative Zeilennummer zum Zellbereich verwendet und nicht die absolute des Excelblatts.

Nur wenn man mit ganzen Zeilen/Spalten arbeitet oder die Zellbereiche in Zeile1/SpalteA beginnen, ist absoluter und relativer Zellbezug gleich.



kleiner Tip noch zur Fehlersuche:
wenn du herausfinden willst, welcher Formelteil den Fehler verursacht, kannst du die Zelle aktivieren und in der Bearbeitungszeile einzelne Formeln markieren.
du musst darauf achten, dass du die einzelne Funktion auch vollständig, dh bis zur abschließenden Klammer markierst.
Dann drückst du F9, Excel ersetzt dann die Formel durch ihr Ergebnis.
Achtung beim Verlassen der Zelle, immer mit ESC verlassen und nicht mit ENTER oder Cursor.
Sonst wird das Ergebnis in die Gesamtformel übernommen und die Formel passt nicht mehr.

Gruß Daniel


Gruß Daniel


  

Betrifft: AW: Interpolation (nicht linear) von: Frida
Geschrieben am: 15.08.2014 09:23:08

Hi Daniel,

ja, das hat jetzt schon einiges geholfen. Dankeschön dafür!
Wo genau liegt denn der Unterschied zu Indirekt? Wenn ich die beiden Formeln eingebe, bekomme ich zwei unterschiedliche Werte. Könnte man sagen, dass eine davon "genauer" ist? Wenn ich das ganze in einem Diagramm darstelle, liegt der Wert über Indirekt berechnet auf der Geraden, während der andere darüber liegt (zumindest an dieser Stelle)

Wert mit Indirekt: 845,2808012
Wert mit Index: 847,2622373


  

Betrifft: AW: Interpolation (nicht linear) von: Daniel
Geschrieben am: 15.08.2014 11:15:25

Hi
Indirekt hat den Nachteil, dass es die Formel volatil macht.
das bedeutet, dass die Formel bei JEDER Änderung in Excel neu berechnet wird, egal wo diese passiert (dh auch bei einer Änderung auf einem andern Blatt oder in einer anderen Datei).
Normalerweise berechnet Excel eine Formel nur dann neu, wenn sich in dem Zellbereich, den die Formel verwendet, eine Änderung ergibt.

Deswegen machen volatile Funktionen eine Exceldatei träge und langsam aufgrund der häufigen Neuberechnungen.
Das gilt in erster Linie dann, wenn man diese Formel in grosser Zahl einsetzt und Zeitaufwendige Berechnugen damit verbunden sind (SummeWenn, SVerweis, Matrixformeln usw).
Kommt das nicht vor, kann man auch ohne Probleme mit INDIREKT arbeiten.

wenn du zu unterschiedlichen Ergbnissen kommst, dann hast du da noch unterschiede in der Berechnung der Zellbereiche drin, das hat mit "Genauigkeit" nichts zu tun, weil sowohl Indirekt als auch Index dir nur den Bezug auf eine bestimmte Zelle geben.

Beachten muss man folgendes:
Indirekt("B" & 1) verweist auf die Zelle B1
Index(B:B;1) verweist ebenfalls auf die Zelle B1
Index(B2:B10;1) jedoch verweist auf die Zelle B2, weil Zelle B2 die erste Zelle des angegebenen Zellbereichs ist.


dh wenn man die Funktionen mit der VERGLEICHs-Funtkion kombiniert um die Zeilennummer zu ermitteln, dann muss man beachten, dass bei INDIREKT die Suchmatrix in der Vergleichsfunktion immer in er Zeile 1 beginnen muss: Index("B" & Vergleich("xxx";C1:C10;0))
ein Index("B" & Vergleich("xxx";C2:C11;0))
führt zu einem falschen Ergebnis, weil die Vergleichsfunktion nicht die Absolute Zeilennummer ermittelt, sondern immer die Position innerhalb des angebenen Zellbereichs (dh das Ergbnis von Vergleich("xxx";C2:C100;0) ist 1, wenn sich der Wert xxx in der Zelle C2 befindet.

bei Index muss der Suchbereich der Vergleichsfunktion nicht unbedingt in der ersten Zeile beginnen, er muss aber in der Zeile beginnen, in der der Zellbereich für die Index-Funktion beginnt, sonst hat man auch hier einen Versatzt.

gruß Daniel

Gruß Daniel


  

Betrifft: AW: Interpolation (nicht linear) von: Frida
Geschrieben am: 15.08.2014 14:12:42

Vielen Dank nochmal für die Hilfe! Ich werde die Zellenbereiche nochmal kontrollieren.


 

Beiträge aus den Excel-Beispielen zum Thema "Interpolation (nicht linear)"