Verweis und Verschiebung(?) koppeln

[hullen]

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) 189-mal heruntergeladen

[balu]

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

[balu]

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

[balu]

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?

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

[Helmut_S]

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

[balu]

@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

[mikeleb]

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.

[Helmut_S]

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

[balu]

Hallo

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


@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

[Helmut_S]

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

[Stephan]

Im Gegensatz zum Mod oder Admin werde ich Zicken immer entspr. antworten und nicht klein beigeben.

Ich bin hier wohl einer der angesprochenen Moderatoren und ich gebe nicht klein bei, sondern versuche meiner Aufgabe gerecht zu werden die auch heißt hier im Forum Streit und schlechte Stimmung möglichst zu verhindern, oder zu schlichten.

Ich habe mir die Mühe gemacht den ganzen Thread zu lesen und ich sehe hier zweierlei, einmal einen Streit um inhaltliche Dinge und einmal persönliche Anwürfe.
Inhaltlich will ich mich hier garnicht sehr einmischen, aber die persönlichen 'Spitzen' gehen doch leider, wieder einmal, von Dir (Lupo1) aus. Versuche doch bitte auf diese Dinge zu verzichten.

"klein beigeben" musst Du deswegen nicht, aber man muss nicht weil man eine bessere Antwort zu haben glaubt die Antwort des Gegenübers runtermachen (="Die Excelaner klopfen sich mit vor-lust-schaudernder Wonne auf die Schenkel." oder "Flexibel denkende AOO-Nutzer" und Ähnliches), sondern man sollte versuchen von seiner Antwort zu überzeugen.
Ähnliches gilt für den Streit ob man hier, im konkreten Fall, nur für AOO oder LO und AOO antworten sollte. Einerseits (und das will ich garnicht verschweigen) ist das hier:
"Was Du über das Office des TE weißt, ist Dein Privatvergnügen. Solange es nicht in Sig oder Av steht, antworte ich vollständig zu Recht und ohne einen Zickeneingriff nach dem Lustprinzip. Diese Seite ist für AOO und LO ... schon deshalb, weil sie schon vor der Trennung der beiden (2010) bestand (nämlich 2003)."
nicht falsch, aber wäre ich Balu würde ich mich provoziert fühlen, durch eine solche Formulierung.


Ich will Dir (Lupo1) aber auch sagen: bei Fach-Inhalten brauchst Du keine Zurückhaltung üben, denn gegebene Antworten durch noch bessere Antworten zu ergänzen ist immer willkommen und Eines kann ich persönlich Dir versichern, ich bin hier um den Leuten bei OO/LO zu helfen, ich bin nicht hier um MS Office schlechtzureden oder OO/LO besser darzustellen als sie sind. (ich selbst benutze täglich MS Office und OO/LO)
Du hast also meine Unterstützung wo Du Schwächen von z.B. Calc gegenüber Excel thematisiert. Sollte Dir dabei jemand über den Mund fahren, werde ich Dich jederzeit unterstützen wenn das nötig wird.



Gruß
Stephan

[balu]

Mahlzeit,

ja bin ich hier im Falschen Film, oder was ist los?

Was Du über das Office des TE weißt, ist Dein Privatvergnügen. Solange es nicht in Sig oder Av steht, antworte ich vollständig zu Recht und ohne einen Zickeneingriff nach dem Lustprinzip. Diese Seite ist für AOO und LO ... schon deshalb, weil sie schon vor der Trennung der beiden (2010) bestand (nämlich 2003).

Und: Antworten in einem Forum sind - außerhalb der Sig/Av-Bemerkung - NUR NEBENBEI Antworten für den Fragensteller - wären sie es EXKLUSIV, brauchte es nur E-Mail.

Es stimmt. Das Forum ist jetzt aktuell eigentlich theoretisch für AOO und LO. Aber um genau zu sein ist es auch noch für OO.o, SO und Go-oo da. Und um es verständlich in nicht abgekürzten Worten auszudrücken, hier eine Übersicht der Office Namen die hier unterstüzt wurden und unterstüzt werden:

• OpenOffice.org
• StarOffice
• NeoOffice
• Go-oo (Go OpenOffice)
• OxygenOffice Professional
• ApacheOpenOffice
• LibreOffice
• und fast außer Konkurrenz: 'IBM Lotus Symphony'

Noch eine Version vergessen? Egal, die wichtigsten sind aufgeführt.

Auch wenn nicht mehr alle aufm Markt sind, und oder weiter entwickelt werden, so kann es aber immer noch passieren das hier jemand Hilfe für sein Office braucht welches aktuell nicht mehr weiterentwickelt wird. Dieser jemand aber mit seinem Office (z.B. OO.o) zu frieden ist, und auch nich "Updaten" will.
Frage:
Was bringt ihm das wenn er Lösungsvorschläge bekommt die nur unter aktuelleren LO Versionen funktionieren?
Antwort:
Das bringt ihm reine weg gar nichts!

Selbst wenn das Forum für AOO und LO da ist, so heißt das nicht, das in der Antwort entweder nur LO Vorschläge, oder unübersichtliche AOO und LO Vorschläge kommen.

Jede Antwort war bis jetzt IMMER für das jeweilige Office vorgesehen. Nicht mehr, und nicht weniger!

Es kann ja wohl nicht sein, dass wenn hier jemand z.B. nach einer Problemlösung in AOO sucht, Antworten bekommen wo andauernd irgendwelche LO/Excel Vorschläge kommen, die nicht in AOO funktionieren oder erst noch irgendwie umgeschrieben werden müssen damit sie in AOO funktionieren.
Wie soll beispielsweise ein unwissender Anfänger wissen wie man eine LO/Excel Formel umschreiben muss, damit sie z.B. in AOO funktioniert? Soll dieser jetzt auch noch so nebenbei Excel lernen, um die Formel anzupassen?

Wir waren eigentlich bis jetzt immer sehr gut damit gefahren, das sich die Antworten nur auf das verwendete Office bezog. Wenn jemand Probleme in LO hatte, dann wurden halt passende Vorschläge für LO unterbreitet. Und wenn jemand Probleme in AOO hatte, dann wurden halt passende Vorschläge für AOO unterbreitet. Aber NIE beides zusammen! Besonders dann nicht, wenn LO Vorschläge nicht in AOO laufen.

Antworten in einem Forum sind [...] NUR NEBENBEI Antworten für den Fragensteller

Bis jetzt waren die Antworten immer für den Fragesteller! Denn schließlich hat er ein Problem und braucht dafür Hilfe um dies Problem für sich aus der Welt zu schaffen. Das dies natürlich später auch anderen helfen kann, ist doch Prima.


Ich habe nix dagegen wenn ich durch andere Lösungsvorschläge auch noch etwas dazu lernen kann, denn schließlich ist niemand allwissend.
Aber die Antworten sollten schon immer direkt für das verwendete Office sein. Und wenn der Hilfesuchende kein Office angibt, dann kann man entweder nachfragen, oder aber einen Vorschlag unterbreiten der in AOO und LO funktioniert.

Sachlich geführte Diskussionen, oder meinetwegen auch Sachliche Unterhaltungen, finde ich nicht verkehrt. Aber auf persönliche Angriffe, die nicht nur unter die Gürtellinie gehen, sondern auch noch Beleidigend sind, sind ja so was von total und vollkommen daneben, das ich mich fragen muss was dieser Scheiß soll. Das gehört nicht in ein öffentliches Forum.



balu

[Stephan]

Hallo Lupo1,

schön das wir "Im wesentlichen d'accord" sind, der Rest Deines Posts sagt aber anderes.

aber meine Lösung war 1. zuerst da und 2. wurde sie von ihm runtergemacht

Ja, Deine Lösung war zuerst da, aber Balu hat diese nur kritisiert. Wenn sein Tonfall nicht freundlich war dann hat das wohl auch mit Deinem Tonfall in früheren Diskussionen zu tun, und da Du diesen Tonfall auch hier im Thread wieder pflegst wirst Du wohl auch Balus Tonfall aushalten müssen.

Was die Frage betrifft zu welchem Programm die Antwort sein sollte, sage ich:
Ich weiß nicht welches Programm de ursprüngliche Frager nutzt. Ich bin bereit Balu zu glauben um welches Programm es sich handelt, ABER ich sehe deshalb allein noch keinen Grund zu kritisieren das Du auch für andere Programme/Programmversionen Antworten formuliert hast.
Was Du jetzt aber schreibst (Motto: es interessiert Dich aus Prinzip garnicht was andere meinen, weil es wohl irgendwie Dein Recht ist hier zu schreiben was Du willst) kann es nun aber auch nicht sein.

Ich lege Wert darauf hier möglichst in Konsens mit den Forumsteilnehmern zu moderieren und versuche zu überzeugen und nicht 'Gesetze' zu exekutieren.
Unsere Hinweise (viewtopic.php?f=3&t=54568) heißen "Hinweise" und nicht Regeln (o.ä.), weil wir gerne möchten das sie befolgt werden, weil ihre Zweckmäßigkeit verstanden wird und nicht weil man sie befolgen muss.
Für mich persönlich heißt das auch sie nicht strikt auszulegen, sondern tolerant. Und Toleranz habe ich wahrlich geübt, u.a. fällt mir ein das Du gleich in Deinen ersten Posts (2012/2013) hier im Forum es nicht bleiben lassen konntest den Moderatoren erklären zu wollen wie sie zu moderieren hätten, damals habe ich darüber nicht ein Wort verloren, weil ich nicht jedes Wort auf die Goldwaage lege ... und das mir das jetzt in den Sinn kommt, ist nur der Verärgerung geschuldet die ich beim Lesen des Threads insgesamt empfinde.

Wenn Du "Gesetze" brauchst, dann schau Dir die Nutzungsregeln an denen Du bei Registrierung zugestimmt hast (melde Dich an, versuche Dich testweise neu zu registrieren, dann werden die Dir angezeigt). Kurzfassung von mir: Du darfst hier Beiträge schreiben, alles Weitere entscheidet der Betreiber nach seinem Ermessen <Punkt>


Gruß
Stephan