[gelöst] SVERWEIS bei variablem Bereich verwenden

[eve]

Hallo,

Ich habe da mal wieder ein Problem bei dem ich einfach nicht weiter komme. Im Prinzip ist es eine Weiterführung von dieser, bereits gelösten Frage:
viewtopic.php?f=2&t=47855

Wie dort beschrieben besteht die Tabelle in der Spalte A aus Datumswerten und in der Spalte B aus den zugehörigen Meßwerten.
A B
1.3.2011 3
2.3.2011 10
3.3.2011 8
4.3.2011 15
5.3.2011 3
6.3.2011 4
7.3.2011 6

In zwei anderen Feldern E1 und E2 wird jeweils das Start- und Enddatum des zu untersuchenden Zeitraumes eingegeben und es werden dann die höchsten und niedrigsten Werte aus diesem Zeitraum aus Spalte B ausgegeben.

Jetzt soll aber zusätzlich zu dem Meßwert auch das zu dem Wert zugehörige Datum ausgegeben werden. Gibt man jetzt z.B. 1.3.2011 und 4.3.2011 in E1 und E2 ein werden 3 und 15 als Werte ausgegeben. Mit SVERWEIS ist es dann ganz einfach zu den Werten 3 und 15 auch noch das jeweils zugehörige Datum ausgeben zu lassen. Wenn ich jetzt aber z.B. in dem Datumsbereich von 5.3.2011 bis 7.3.2011 suche dann werden zwar als Werte 3 und 6 ausgegeben, jedoch wird als zugehöriges Datum für die Zahl 3 der 1.3.2011 angegeben, da SVERWEIS ja von Anfang der Tabelle an zu suchen beginnt. Wie muß ich die Formel aufbauen, daß SVERWEIS erst ab 5.3.2011 zu suchen beginnt?

Gruß eve

[eve]

Der Vorschlag funktioniert, allerdings nur wenn der Wert in dem Suchbereich auch wirklich nur einmal vorkommt.

Also war ich mit SVEWEIS ganz auf dem falschen Dampfer. Liegt wohl daran, dass ich die Arbeitsweise der Funktion SUMMENPRODUKT noch immer nicht so ganz verstanden habe. .
Nur mal so aus Interesse: Wenn der Wert mehrmals innerhalb des Suchbereiches vorkommt, gäbe es dann irgendeine Möglichkeit, sich alle dazu gehörigen Datumswerte ausgeben zu lassen?

Gruß eve

[echo]

Hallo zusammen,
ich würde mir vermutlich eine kleine Hilfsspalte erstellen in der der relevante Bereich durch zeile() markiert wird.
Da der Bereich immer zwischen der niedrigsten und höchsten zeile() liegt lässt sich dieser Bereich dann recht einfach in Formeln definieren.
Die Spalte kann man ja später einfach ausblenden.

Noch einfacher wird es mit den Formeln dann, wenn du dem Bereich einen Namen verpasst, diesen kannst du dann in den Formeln verwenden.

Gruß Holger

[hiob]

hi @all, ich verstehe nicht, wenn man den zeitraum vorgibt und sich daraus das minimum und maximum ausgeben läßt, was übrigens ohne matrixformel und ohne hilfsspalte geht, warum man sich dann wieder die datumswerte zu den min- und maxwerten ausgeben lassen will... das ist doch vorgegeben..!?!

[echo]

hi @all, ich verstehe nicht, wenn man den zeitraum vorgibt und sich daraus das minimum und maximum ausgeben läßt, was übrigens ohne matrixformel und ohne hilfsspalte geht, warum man sich dann wieder die datumswerte zu den min- und maxwerten ausgeben lassen will... das ist doch vorgegeben..!?!

Vielleicht solltest du besser mal ein Beispiel zeigen um die Unterscheide aufzuzeigen.
Die vorgegebenen Datum beschreiben einen Bereich [zB. eine Woche] aus dem der MIn- und MAX-Wert und jeweils das dazu gehörende Datum gesucht wird.
Dieses Datum ist nicht vorgegeben sondern befindet sich nur in der vorgegeben Woche.

Gruß Holger

[eve]

Hallo,

@ paljass
Vielen Dank für das Beispiel. Es gab zwar beim herunterladen wohl einen Fehler, da erst mal in dem Kästchen nichts angezeigt wurde. Beim draufklicken habe ich aber dann erkannt, daß da in den Zellen Formeln hinterlegt sind und konnte die Matrix dann einfach nachvollziehen. Beim Summenprodukt werden also nicht die Werte der Matrix selbst miteinander multipliziert sondern nur 1 bzw. 0 für "gefunden" bzw. "nicht gefunden". Jetzt weiß ich endlich mal wie diese Matrix zum berechnen aufgebaut ist, vielen Dank für die Mühe.

Da wurde man was kompliziertes mit Verweis() und Vergleich() hinkriegen können, wenn denn die Suchspalte - also die Spalte mit den Zahlen - aufsteigend sortiert wäre

Leider ist aber das Datum und nicht die Zahlenspalte aufsteigend sortiert und das umgekehrt zu machen wäre dann wohl etwas chaotisch.

@echo
Danke für den weiteren Lösungsvorschlag, ich finde es immer interessant verschiedene Lösungsmöglichkeiten anzuschauen, da man dann mehr Flexibilität hat, wenn man mal wieder auf ein ähnliches Problem stößt.

hi @all, ich verstehe nicht, wenn man den zeitraum vorgibt und sich daraus das minimum und maximum ausgeben läßt, was übrigens ohne matrixformel und ohne hilfsspalte geht, warum man sich dann wieder die datumswerte zu den min- und maxwerten ausgeben lassen will... das ist doch vorgegeben..!?!

Aber das ist es ja gerade, es ist nur der Zeitraum vorgegeben und nicht das konkrete Datum der Min- und Maxwerte. Das Beispiel hier ist recht kurz und überschaubar. Aber wenn die Liste sich über ein Jahr mit entsprechend vielen Werten erstreckt, und man nur die Min/Maxwerte aus einem bestimmten Zeitraum haben will, dann ist es schon einfacher, wenn das Datum automatisch mit ausgegeben wird.

Gruß eve

[hiob]

okay, jetzt habe ich verstanden... ... ich hätte ohne hilfsspalte und ohne matrix-formel anzubieten... für das minimum:

Code: Alles auswählen

=MIN(INDEX(B1:B100;VERGLEICH(E1;A1:A100;0)):INDEX(B1:B100;VERGLEICH(E2;A1:A100;0)))

für das maximum selbe formel nur aus MIN() mach MAX()

zurück nach dem selben prinzip nur hier wird's lang..:

Code: Alles auswählen

=INDEX(INDEX(A$1:A$100;VERGLEICH($E$1;A$1:A$100;0)):INDEX(A$1:A$100;VERGLEICH($E$2;A$1:A$100;0));VERGLEICH(G1;INDEX(B$1:B$100;VERGLEICH($E$1;A$1:A$100;0)):INDEX(B$1:$B100;VERGLEICH($E$2;A$1:A$100;0));0))

aber wenn man Namen vergibt wird's sooooo schön kurz...:

für den datumsbereich Datum..:

Code: Alles auswählen

INDEX($A$1:$A$100;VERGLEICH($E$1;$A$1:$A$100;0)):INDEX($A$1:$A$100;VERGLEICH($E$2;$A$1:$A$100;0))

für den werte bereich Wert..:

Code: Alles auswählen

INDEX($B$1:$B$100;VERGLEICH($E$1;$A$1:$A$100;0)):INDEX($B$1:$B$100;VERGLEICH($E$2;$A$1:$A$100;0))

das gibt dann..:

Code: Alles auswählen

=MIN(Wert)

bzw.:

Code: Alles auswählen

=MAX(Wert)

,

Code: Alles auswählen

=INDEX(Datum;VERGLEICH(G1;Wert;0))

[eve]

Das ist auch ein interessanter Ansatz ohne Matrixformel, den werde ich mir bestimmt bei Gelegenheit auch noch genauer anschauen.
Vielen Dank mal wieder an Euch alle für die Hilfe.

Gruß eve