Verweis und Verschiebung(?) koppeln

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

hullen
***
Beiträge: 70
Registriert: Mi, 20.07.2011 20:54

Verweis und Verschiebung(?) koppeln

Beitrag von hullen » Di, 30.10.2018 10:58

Guten Tag,
ich möchte mit einem wVerweis arbeiten.
Allerdings besteht die Bezugszelle aus zwei zusammengefügten Zellen. Von ihr ausgehend erhalte ich immer nur die Daten aus der ersten Spalte - wie komme ich an die Daten der zweiten Spalte heran? Ich vermute, dass es mit Verschiebung, Adresse, Indirekt oder so geht, aber ich verlaufe mich in der Abstraktheit dieser Formeln. Kann jemand helfen?

lg Hahu

PS: Beispieltabelle anbei
Wverweis mit Verschiebung.ods
(10.03 KiB) 25-mal heruntergeladen

Lupo1

                                                                                                   

Beitrag von Lupo1 » Di, 30.10.2018 13:25

                                                                                                   
Zuletzt geändert von Lupo1 am Fr, 02.11.2018 12:12, insgesamt 1-mal geändert.

Benutzeravatar
balu
********
Beiträge: 3664
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Verweis und Verschiebung(?) koppeln

Beitrag von balu » Di, 30.10.2018 13:55

Hallo Lupo,

deine Formel funktioniert NICHT in AOO. Es kommt dabei zu einem #REF! Fehler.

Und es geht hier in diesem Falle um AOO.



Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D

Benutzeravatar
balu
********
Beiträge: 3664
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Verweis und Verschiebung(?) koppeln

Beitrag von balu » Di, 30.10.2018 14:00

Hallo hullen,

na Du! Auch mal wieder da? ;-)
ich möchte mit einem wVerweis arbeiten.
Ich aber nicht. Denn ich gehe das anders an.

Allerdings besteht die Bezugszelle aus zwei zusammengefügten Zellen.
Kein Problem. Wenn deine Oroginale Datei genau so aufgebaut ist, dann kann man einen Korrekturfaktor einbauen.

Ich vermute, dass es mit Verschiebung, Adresse, Indirekt oder so geht, aber ich verlaufe mich in der Abstraktheit dieser Formeln. Kann jemand helfen?
Ja, das stimmt, das man sich manchmal da verlaufen kann. =ADRESSE() und INDIREKT() sind schon mal gar nicht so verkehrt. Jedoch arbeite ich noch zusätzlich mit =VERGLEICH() und =SVERWEIS().
Und ja, ich kann dir mit einem Vorschlag helfen.

Also, der Reihe nach.
Als erstes brauchen wir einen Spaltenindex für das Datum. Auch wenn zwei Zellen verbunden sind, so ist die Adressierung dieser Zelle immer die erstere der beiden Zellen. Wenn A4 und B4 verbunden sind, und in A4 etwas drin steht, so ist das Ansprechen der verbundenen Zellen mit A4 möglich.

Nun müssen wir also den Spaltenindes des Datums ermitteln, was ganz einfach hiermit geht.

Code: Alles auswählen

=VERGLEICH(B18;A2:D2;0)
Damit die spätere Hauptformel nicht zu unübersichtlich wird, lagern wir diese aus. Das soll heißen, sie bekommt irgendwo einen eigenen Platz/Zelle. Nehmen wir jetzt mal beispielsweise die Zelle A9.


Nun wird es etwas interessanter, denn wir wollen ja einen Datenbereich erstellen, der abhängig vom Datum ist. Wir können also keinen festen Datenbereich a-la A4:D6 vorgeben. Und zu diesem Zweck kommt jetzt =ADRESSE() zum Zuge.

Code: Alles auswählen

=ADRESSE(4;A9;4)&":"&ADRESSE(4;A9+1;4)
Die wichtigsten Infos dazu kannst Du ja in der Hilfe nachlesen.
Durch das &":"& werden die zwei Adressen mit einander zusammengefügt.
Ich sprach ja eben von einem Korrekturfaktor, und der wird jetzt in der zweiten ADRESSE eingesetzt, und zwaar mit +1. Und deshalb steht in der zweiten Adresse nicht einfach A9, so wie in der ersten, sondern A9+1.

Wenn du jetzt also diese Formel beispielsweie in A10 eingibst, und in B18 das Datum 01.02.18 steht, dann wird folgender Text angezeigt: C4:D4.

Okay, das war jetzt nur zum allgemeinen Verständnis, für die gleich noch folgende Hauptformel.
Jedoch haben wir jetzt noch keinen Mehrzeiligen Datenbereich, sondern nur eine Einzeilige Datenzeile. Und damit können wir nichts anfangen. das ändert sich jetzt aber sofort.

Natürlich könntest Du auch bei der zweiten Adresse die letzte benutzte Zeile von Hand eingeben, und das würde dann so aussehen.

Code: Alles auswählen

=ADRESSE(4;A9;4)&":"&ADRESSE(10;A9+1;4)
Und das bleibt auch dir überlassen ob Du die Hauptformel dementsprechend umstellst, und ich wäre dir auch deswegen nicht böse.

Jedoch kann man sich auch die letzte benutzte Zeile automatisch ermitteln lassen. Und dafür wäre diese Formel gut.

Code: Alles auswählen

=MAX(ZEILE(A4:A8)*(A4:A8<>""))
Diese muss aber als Matrixformel mit "Strg" + "Shit" + "Enter" angeschlossen werden.

Sagen wir jetzt mal das wir diesen automatismus einbauen wollen, dann müsste die Formel in A10 mit den beiden Adressen wie folgt aussehen.

Code: Alles auswählen

=ADRESSE(4;A9;4)&":"&ADRESSE(MAX(ZEILE(A4:A8)*(A4:A8<>""));A9+1;4)
Da wir jetzt aber die eben vorgestellte Matrixformel eingebaut haben, muss diese hier jetzt auch als Matrixformel abgeschlossen werden.
Und als Ergebnis, wenn in B18 das Datum 01.02.18 steht, dann wird folgender Text angezeigt: C4:D6.

Na Prima!
Jetzt haben wir einen echten Mehrzeiligen Datenbereich.

Und nun kommt die Hauptformel in B21.

Code: Alles auswählen

=SVERWEIS(A21;INDIREKT(ADRESSE(4;A$9;4)&":"&ADRESSE(MAX(ZEILE(A4:A8)*(A4:A8<>""));A$9+1;4));2;0)
Auch diese Formel musst Du als Matrixformel abschließen.
Das ist also mein Vorschlag mit =SVERWEIS.

Und der Beweiss das dies auch funktioniert, ist im Anhang.

Die Formel in A10 kannst Du getrost löschen. Sie soll dir nur dazu dienen das ganze etwas leichter zu verstehen.

Ich hoffe das Du damit zu recht kommst. Ansonsten frag halt einfach nach.



Gruß
balu
Dateianhänge
Wverweis mit Verschiebung_v2.ods
(12.78 KiB) 15-mal heruntergeladen
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D

Lupo1

                                                                                                   

Beitrag von Lupo1 » Di, 30.10.2018 14:09

                                                                                                   
Zuletzt geändert von Lupo1 am Fr, 02.11.2018 12:12, insgesamt 1-mal geändert.

Benutzeravatar
balu
********
Beiträge: 3664
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Verweis und Verschiebung(?) koppeln

Beitrag von balu » Di, 30.10.2018 14:41

Danke für Deine Rückmeldung:
Gern geschehn.

a. Hast Du zufällig auch LO (neben AOO)?
Ich habe OO.o, Go-oo, AOO und auch LO.

c. Es kann ja nicht sein, dass Deine sehr lange Formel nur aufgrund der OO-Dialektitis statt meiner gelten soll.
Meine ist keinem Dialekt unterworfen.
Und was hatte ich geschrieben?
balu hat geschrieben: ich kann dir mit einem Vorschlag helfen.
b. Wie würdest Du diese in LO getestete Formel in AOO schreiben? VERSCHIEBUNG (eigentlich: BEREICH.VERSCHIEBEN) und die korrekte Ansteuerung verbundener Zellen mit VERGLEICHsfunktionen sollte es doch eigentlich auch kennen. Möglicherweise müssen in AOO nur MEHR oder ALLE der 5 Argumente von VERSCHIEBUNG tatsächlich (nicht: optional) besetzt sein. Laut dem Link jedoch nicht!
Auch in Excel ist es nicht immer empfehlenswert bei bestimmten Funktionen, oder Funktionen in Formeln, komplette Spalten oder Zeilen durch die dort typischen abkürzungen zu schreiben. Und hier müsste es jetzt eigentlich bei dir schon "KLICK" machen.

Aber keine Angst, hier kommt die einfache und doch banale Lösung.

Code: Alles auswählen

=SVERWEIS(A21;VERSCHIEBUNG($A$4:$B$6;;VERGLEICH($B$18;A$2:D$2;0)-1);2)
Und!?
Siehst du meine Änderungen!?
Und das funktioniert auch.


Natürlich kann meine Hauptformel auch gekürzt werden, worauf ich ja schließlich hingewiesen habe.
Und dann würde aus.

Code: Alles auswählen

=SVERWEIS(A21;INDIREKT(ADRESSE(4;A$9;4)&":"&ADRESSE(MAX(ZEILE(A4:A8)*(A4:A8<>""));A$9+1;4));2;0)
Ganz einfach das hier werden.

Code: Alles auswählen

=SVERWEIS(A21;INDIREKT(ADRESSE(4;A$9;4)&":"&ADRESSE(10;A$9+1;4));2;0)
Natürlich bliebe dann noch die Sache mit der =VERGLEICH().
Aber es führen halt viele Wege nach Rom.


Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D

Lupo1

                                                                                                   

Beitrag von Lupo1 » Di, 30.10.2018 15:04

                                                                                                   
Zuletzt geändert von Lupo1 am Fr, 02.11.2018 12:13, insgesamt 1-mal geändert.

Helmut_S
***
Beiträge: 54
Registriert: So, 29.10.2017 11:29

Re: Verweis und Verschiebung(?) koppeln

Beitrag von Helmut_S » Di, 30.10.2018 15:30

Hallo,
@balu, ich kann deine Aussage nicht bestätigen. Bei mir -unter Win 64 mit AOO 4.1.5 funktioniert Lupos Formel.
Habe noch einen Monat hinzugefügt und die Formel entsprechend dafür angepasst. Alles paletti.
Gruß Helmut
openSUSE Leap 15.0 / Win 10-64
LO 6.1.2, AOO 4.1.5

Benutzeravatar
balu
********
Beiträge: 3664
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Verweis und Verschiebung(?) koppeln

Beitrag von balu » Di, 30.10.2018 15:49

@Lupo1
Wie vermutet.
Und warum fragst Du mich dann erst?

Hättest Du ja auch gleich bringen können
Ja wenn kein Office "offiziel" angegeben ist, dann gibts halt einen Vorschlag von mir der in AOO und auch in LO funktioniert. Und nicht wie Du der gleich mit einem LO/Excel Vorschlag daher kommt ohne zu überlegen um was für ein Office es sich denn handelt, oder handeln könnte.

$A$2:$D$2 statt $2:$2 ist aber ziemlich schlaff für AOO. Da hört es bei mir auf mit dialektübergreifend.
Da kann ich doch nix für, das AOO sich halt anders verhällt als LO.


@Helmut
ich kann deine Aussage nicht bestätigen. Bei mir -unter Win 64 mit AOO 4.1.5 funktioniert Lupos Formel.
Wenn Du diese Formel

Code: Alles auswählen

=SVERWEIS(A21;VERSCHIEBUNG($A$4:$B$6;;VERGLEICH($B$18;$2:$2;0)-1);2)
meinst, dann kann ich dir das nicht glauben.

Aber zur allgemeinen Info.
Ich weiss das es sich beim Office von hullen um AOO 3.4.1 handelt. Und da funktiniert die Formel definitiv nicht!

Habe noch einen Monat hinzugefügt und die Formel entsprechend dafür angepasst.
Na dann zeig doch mal deine Anpassung, bitte schön.



Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D

Lupo1

                                                                                                   

Beitrag von Lupo1 » Di, 30.10.2018 15:51

                                                                                                   
Zuletzt geändert von Lupo1 am Fr, 02.11.2018 12:13, insgesamt 1-mal geändert.

mikeleb
******
Beiträge: 731
Registriert: Fr, 09.12.2011 16:50

Re: Verweis und Verschiebung(?) koppeln

Beitrag von mikeleb » Di, 30.10.2018 17:41

Hallo hullen,

wenn du A21:B23 die Punkte und Namen zu dem entsprechenden Datum haben willst, kannst du auch einfach

Code: Alles auswählen

=VERSCHIEBUNG(A4;0;VERGLEICH(B18;A2:E2;0)-1;3;2)
als Matrixformel in A21 eingeben (Abschluss mit Shift-Strg-Enter) und fertig.
Gruß,
mikeleb

Helmut_S
***
Beiträge: 54
Registriert: So, 29.10.2017 11:29

Re: Verweis und Verschiebung(?) koppeln

Beitrag von Helmut_S » Di, 30.10.2018 17:42

Hallo, bittesehr @balu, anbei die AOO-Tabelle mit der Formel von Lupo und auch deiner. Seltsam - habe deine Formel von der Forenseite kopiert und eingefügt, da kommt das von dir bemerkte #REF!,
Könnte das vielleicht einen Einfluss gehabt haben, dass ich die Tabelle zuerst in LO geöffnet habe??
Ich habe nur die bereits vom TE erstellte Formel in A21 um 4 Spalten bis H6 erweitert um zu sehen, wie sich die Formel verhält wenn man mehrere Daten eingeben möchte.
Also habt ihr irgendwie beide recht, schöne Grüße Helmut
Dateianhänge
Wverweis mit Verschiebung.ods
(50.73 KiB) 16-mal heruntergeladen
openSUSE Leap 15.0 / Win 10-64
LO 6.1.2, AOO 4.1.5

Benutzeravatar
balu
********
Beiträge: 3664
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Verweis und Verschiebung(?) koppeln

Beitrag von balu » Di, 30.10.2018 18:05

Hallo

@mikeleb
Na Prima!
Passt, wackelt und hat Luft. Mal wieder etwas dazu gelernt.
Und ich geh mich mal ein klein wenig schämen. :oops:


@Helmut.
Jaaa! Jetzt versteh ich das ganze.

Wenn du die Beispieldatei von hullen erst in LO geöffnet hast, und dann die Formel von Lupo1 eingibst, und speicherst, dann ist ganz klar das AOO automatisch dahergeht und die typischen Excel-Abkürzungen beim öffnen dieser Datei in die andere Schreibweise umwandelt. Das ist ganz normal.

Aber wenn Die besagte Formel hier rauskopiert wird, und in AOO direkt eingefügt wird, dann kann der Umwandlungsmechanismus nicht zum tragen kommen, das Ergebniss ist dann der besagte Fehler.

Danke für deine Antwort und Beispieldatei.



Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D

Helmut_S
***
Beiträge: 54
Registriert: So, 29.10.2017 11:29

Re: Verweis und Verschiebung(?) koppeln

Beitrag von Helmut_S » Di, 30.10.2018 18:29

Hallo nochmal, also nun habe ich es geprüft und festgestellt. Öffnen der vom TE angefügten Datei in AOO und Einfügen von Lupos Formel ergibt #REF! - wie von Balu richtig bemerkt.
Wenn ich Lupos Formel aber in der in LO geöffneten Datei eingebe, klappt es. Anschließendes Speichern und Öffnen unter AOO klappt auch - wie von mir festgestellt.
Gruß Helmut
openSUSE Leap 15.0 / Win 10-64
LO 6.1.2, AOO 4.1.5

Lupo1

                                                                                                   

Beitrag von Lupo1 » Di, 30.10.2018 18:31

                                                                                                   
Zuletzt geändert von Lupo1 am Fr, 02.11.2018 12:14, insgesamt 1-mal geändert.

Antworten