Supportnet / Forum / Tabellenkalkulation

Hallo zusammen, ich habe ein Problem mit den Verweisfunktionen. Mein Ziel ist es den Wert einer bestimmten Zelle einer Tabelle nach zwei variablen Abzufragen. D.h. in der Spalte A stehen Jahresangaben und in der Zeile 1 stehen Werte. An den Schnittpunkten dieser beiden Felder (z.B. C3 ist der Schnittpunkt für das Jahr 2000 und den Werte 5000)stehen Werte. In C3 steht 50. Diesen Wert 50 möchte ich über zwei Eingabefelder (Eingaben= 2000 und 5000) in eine dritte Zelle auswerten. (Uff, ich hoffe man versteht mich !!!). Ich habe alle Möglichkeiten mit Verweis und Index getestet, aber nicht funktioniert. Kennt jemand eine Lösung ? Gruss, Michael.

Hi Michael,

ich verstehe dich wie folgt:
ab A2 bis z.B. A10 stehen die Jahresangaben.
in B1 bis z.B. H1 stehen die Werte.

In z.B. A12 gibst Du 2000 ein, in A13 5000
Formel:
=SUMMENPRODUKT(($A$2:$A$10=A12)*($B$2:$H$1=A13)*$B$2:$H$10)

Sollte das richtige Ergebnis bringen, wenn diese Kombination nur einmal vorkommt.

CU Aliba

Hoppla, kleiner Schreibfehler:
statt:
($B$2:$H$1=A13)
bitte:
($B$1:$H$1=A13)

CU Aliba

@Michael:

Aliba hat zum einen wieder eine Super-Formel gebaut und zum andern mit seiner Warnung völlig Recht: Sowohl Jahreszahlen als auch die Werte(und das könnte in der Praxis viel gefährlicher sein) dürfen nur ein einziges Mal vorkommen. Ist das nicht der Fall, liefert die Formel eben doch ein (wenn auch falsches!) Ergebnis, was bei einer umfangreichen Tabelle mit vielen Werten nicht unmittelbar auffallen dürfte.

Vielleicht kannst du ja mal ein paar ergänzende Infos zu den Werten in Zeile 1 geben.
Sind sie alle unterschiedlich?
Sind sie ggf. aufsteigend sortiert?
Vielleicht gibt es ja noch eine Möglichkeit, auch die o.g. mögliche Fehlerquelle auszuschliessen.

CU
Klaus

Hallo Klaus,

die Werte in Zeile 1 sind alle in aufsteigenern Reihenfolge und kommen nur 1x vor. Es handelt hierbei um Bandbreiten, d.h. ein nicht genauer Wert in der Abfrage müssten dem niedrigeren Wert in Zeile 1 zugeordnet werden.
Danke für die Hilfe !!!


Gruß, Michael.

@ Michael:

da sgeht mit Alibas Formel leider nicht.
Mir schwebt da eher was mit SVERWEIS und WVERWEIS vor.
Ich arbeite dran, kann aber jetzt auf die schnelle keine Sofortlösung bieten.

Bis gleich
Klaus

Hi Michael,
das ist natürlich was Anderes. Ich setze selbige Bereiche wie oben voraus.

=INDIREKT(ADRESSE(VERGLEICH(A13;A1:A10);VERGLEICH(A12;A1:H1)))

CU Aliba

@Michael,

ZZZZ!!! Das ist vertrackter als ich dachte. Vermutlich kommen auch noch die Funktionen ZEILE und INDEX zum Einsatz, um bei der Funktion WVERWEIS den Zeilenparameter zu ermitteln.

mein Lösungsansatz bisher:

Da der Wert nicht eindeutig in Zeil 1 zu finden ist, kommt hier die Funktion WVERWEIS zum Einsatz. Der Parameter FALSCH darf nicht verwendet werden, damit in der Matrix der jeweils kleinere Wert der Bandbreite genommen wird.

Um den richtigen Wert aus der Matrix auszulesen, möchte ich die Zeilennummer der jahreszahl ewrmitteln. Wenn ich diese dann in die Funktion WVERWEIS übergeben kann, müßte das eigentlich die LÖsung sein.

Momentan scheitere ich an der Übergabe des Parametres für den zeilenindex bei WVERWEIS.

Los Formelexperten, wo seid ihr denn alle?

CU
Klaus

Hi Klaus,

wer wird denn so ungeduldig sein ;-))

Aliba

@Aliba:

ich ;-)))

1.Ich tippe immer wieder nur einfach zu langsam ;-)
2. bei deiner Formel bekomme ich #NV heraus.
3. =INDIREKT(ADRESSE(VERGLEICH(A12;A1:A10);VERGLEICH(A13;A1:H1)))

Ich glaube, A12 und A13 mussten nur getauscht werden, dann geht´s ;-)

CU
Klaus

hi Klaus,

hast recht.

Es muß halt zuerst die Zeile ermittelt werden, also den Jahreseintrag und dann die Spalte. Hatte ich einen Flüchtigkeitsfehler drin.

Danke für den Hinweis.

CU Aliba

Hi Klaus,
Hi Aliba,

vielen Dank für die Hilfe. Ich bin begeistert. Werde die Formel kurzfristig testen.

Dank, Michael.

Hi Klaus,
Hi Aliba,

es hat sich beim Test das ein Problem ergeben. Wenn durch meine Abfrage der Wert aus H10 angezeigt werden müsste, erscheint der Wert aus G9.
Was mache ich noch falsch ?

Gruss, Michael.

hi Michael,
schwer zu sagen per Ferndiagnose. Ich tippe mal darauf, dass deine Tabelle vielleicht "in echt" ein wenig anders aussieht als das Beispiel von Aliba und du beim Übertragen der Formel einen kleinen Tippfehler gemacht hast.

Ich habe es grade nochmal selbst bei meiner Beispieltabelle ausprobiert, es funzt einwandfrei.

Also: mach dich auf die suche nach dem Fehler und feiere deinen eigenen Erfolg!
Falls du das ernsthaft versucht, aber dann doch nicht hingekriegt haben solltest, schick mir die Tabelle, ich schau sie mir an
*abervorheraufjedenfallernsthaftselbstversuchen!*

CU
Klaus

Hi Michael,

ändere die Formel dahingehend:

=INDIREKT(ADRESSE(VERGLEICH(A12;A1:A10)+1;VERGLEICH(A13;A1:H1)+1)) Das sollte dann funzen.

Vieleicht kurz zur Formelerklärung:
Ich nehme an Deine Bereiche beginnen nicht wie in meinem Beispiel in jeweils in A1, sondern erst in B1:?1 und die Jahreswerte in A2:A?.
Entweder du änderst die Formel wie oben beschrieben ab, oder änderst die Bereiche , daß beide Bereiche in A1 beginnen.
Trotzdem zum Verständnis die Erklärung:

Vergleich liefert die Position des zu vergleichenden Wertes innerhalb des angegebenen Bereiches. Stehen Dein Trefferwert z.B. in D1 und Dein Abfragebereich ist B1:H1, dann wird der Wert 3 geliefert. Wäre Dein Abfragebereich A1:H1 wäre er 4. Dasselbe ist analog gültig für die Abfrage der Spalte A.
Die Funktion ADRESSE macht folgendes: ADRESSE(2;4) liefert den absoluten Bezug der Zeile 2 und der Spalte 4 als Text , also $D$2
In meiner Formel bin ich nun davon ausgegangen, daß die Bereiche jeweils in A1 beginnen, was zum Ergebnis hätte, dass Vergleich die exakte Zeilennr. und die exakte Spaltennr. für die ADRESS-Funktion geliefert hätte. Beginnen die Bereiche nun nicht in A1, sondern die Jahreszahlen beginnen in Zeile 3 und die Vergleichswerte in Zeile 1 beginnen erst in B1 muß man die Ergebnisse der Vergleiche entsprechend regulieren. Das heißt wir müssen bei der Vergleichsabfrage der Jahreszahlen (beginnend in Zeile 3) das Ergebnis um +2 regulieren, denn wäre nun gleich der erste Eintrag in Zeile 3 unser Suchergebnis, würde Vergleich 1 liefern, wir benötigen jedoch 3 (3. Zeile).
Analog gilt das auch für die Spaltenfindung. Der Abfragebereich beginnt in B , also +1. So erhalten wir dann wieder den richtigen Zellbezug als Text.
INDIREKT ist dann eben eine Funktion, die den Wert aus einem als Text angegebenen Bezug liefert.

So , hoffe alle Klarheiten beseitigt zu haben und wünsche alle einen schönen Tag.

CU ALiba

Nochmal ich.
Da hab ich mich mal wieder kompliziert ausgedrückt und noch einen kleinen Fehler in der Formel. Machen wirs mal einfacher:
Wenn Dein Ergebnis aus G9 geliefert wird, muß Deine Formel momentan so aussehen:

=INDIREKT(ADRESSE(VERGLEICH(A12;A2:A10);VERGLEICH(A13;B1:H1)))

Ändere sie dahingehend:
=INDIREKT(ADRESSE(VERGLEICH(A12;A2:A10)+1;VERGLEICH(A13;B1:H1)+1))

Die Erklärung lt. obigem Posting

CU Aliba

Nochmals Danke, jetzt klappt es.

Gruss, Michael.

• Excel Verweis von Tabelle1 auf Tabelle2 erstellen
• sverweis mit bedingtem lookup
• SVERWEIS Probelm in Excel
• hat jemand ahnung von verweisfunktionen in excel
• Excel tabellen verweis