SVERWEIS über mehrere Reiter

[Lazzard]

Hallo liebe OpenOffice Gemeinde,

ich arbeite für meine Kunden mit einer Preisliste im Calc Format.
Die Preisliste besteht aus mehreren Reitern/Tabellen für verschiedene Hersteller/Kategorien.
Als letzten Reiter/Tabelle, kommt nun eine Gesamtliste, alle Artikel die später für einen CSV Export genutzt werden sollen.
Ich ziehe mir in diesem letzten Reiter für jedes Produkt die Preise aus den vorangegangenen Reitern. So das, wenn ich
in der "Preisliste" einen Preis ändere, dieser in dem letzten Reiter/Tabelle übernommen wird. Auf Wunsch wird aus dem letzten Reiter/Tabelle eine CSV exportiert.

Aktuell verweise ich für jede Kategorie mit einem eigenen SVERWEIS auf den passenden Reiter und passe jeweils die Matrix dementsprechend an.

Wenn nun neue Artikel hinzukommen, muss ich ja immer wieder mir die Arbeit machen, den passenden SVERWEIS zu basteln.

Gibt es vielleicht eine Möglichkeit, dass ich im SVERWEIS bestimmen kann, dass er auf mehreren Reitern/Tabellen sucht ?
Also sozusagen mehrere Matrix Bereiche angebe ?

Es wäre super, wenn jemand dazu Rat wüsste.

Besten Dank !!

[Gert Seler]

Hallo Lazzard,
das Beste ist, Du registrierst Dich hier im Forum und lädst eine Beispieldatei, mit "Dateianhang hochladen",
siehe Vorlage "Antwort erstellen", hoch.
Wenn Deine "Preisliste" sehr viel "Artikel" beinhaltet, ist es ratsam das ganze mit einer "Datenbank"
z.B. "OpenOffice-Base" zu verwalten.
Siehe :www.ooowiki.de/ÜbersichtBase

mfg
Gert

[Lazzard]

Leider kann ich die Datei nicht hochladen ohne sie komplett zu zerstümmeln.
Ich möchte lediglich, dass auf dem letzten Tabellenblatt alle unter einander stehenden Artikel mit allen vorangegangenen Tabellenblätter
abgeglichen wird und der Preis übernommen wird.
Die Zuordnung des Preises findet über die Artikelnummer statt.

Aktuell heißt es bei mir:
=SVERWEIS(A2;$'Reb.f.Brother'.$B$12:$G$65;6;0)

A2 steht für die Artikelnummer über die abgeglichen wird.
Das Reb.f.Brother steht für das erste Tabellenblatt.
Die 6 steht für die 6te Spalte in der Matrix, dort steht der Preis im Tabellenblatt Reb.f.Brother.

Alle Tabellenblätter sind gleich aufgebaut.

Kann man mit dem SVERWEIS nicht realisieren, dass er mehrere Matrixen gibt bzw mehrere Tabellenblätter durchsucht werden ?

[lorbass]

Leider kann ich die Datei nicht hochladen ohne sie komplett zu zerstümmeln.

Dann erstelle eine Beispieldatei, die den Kern deines Problems darstellt. Ein bisschen Mitarbeit ist doch nicht zu viel verlangt, oder?

Kann man mit dem SVERWEIS nicht realisieren, dass er mehrere Matrixen gibt bzw mehrere Tabellenblätter durchsucht werden ?

Ja!

Gruß
lorbass

[Lazzard]

So, ich habe meine Tabelle mal auf das geringste zusammengeschoben und auf zwei Tabellenblätter reduziert.

Im letzten Tabellenblatt (CSV) sehr ihr meinen SVERWEIS.
Ich hätte nun gerne irgendwie einen Universal SVERWEIS, damit er alle Tabellenblätter nach der Artikelnummer
durchsucht und dann den Preis dazu in das letzte Tabellenblatt überträgt.

sverweis-test.xls

(54.5 KiB) 514-mal heruntergeladen

Vielleicht weis ja jetzt jemand eine Lösung ! Besten Dank !

[Lazzard]

@Paljass
Vielen vielen Dank. Deine Formel klappt...aber...jetzt kommt das "aber"
Irgendwie nimmt er nicht jeden Reiter. Bei einigen Produkten zeigt er #NV an.
Gibt es irgendwelche Kriterien, warum er die Werte dort nicht übernimmt ?

[balu]

Hallo

Bei einigen Produkten zeigt er #NV an.

Und das sagt uns, dass das Suchkriterium NichtVorhanden (NV) ist.

Gibt es irgendwelche Kriterien, warum er die Werte dort nicht übernimmt ?

Als Grundregel gilt: Überprüfe erst ob das Suchkriterium überhaupt vorhanden ist!


So, und nun zu der Formel die wohl auch bei dir funktionieren müsste, mir aber persönlich nicht so wirklich gefällt.

Code: Alles auswählen

=WENN(ISTFEHLER(INDIREKT("'Reb.f."&B2&"'.A1"));"";WENN(ZÄHLENWENN(INDIREKT("'Reb.f."&B2&"'.B12:B1000");A2);SVERWEIS(A2;INDIREKT("'Reb.f."&B2&"'.B12:G1000");6;0);""))

Sie ist nun um einiges größer geworden, aber dafür führt sie zusätzliche Überprüfungen durch, damit keine Fehlerwerte wie #NV, oder ähnliches angezeigt wird.

Code: Alles auswählen

=WENN(ISTFEHLER(INDIREKT("'Reb.f."&B2&"'.A1"));""

Wenn die Adressierung des Tabellenblatts, die ja durch INDIREKT konstruiert wird, nicht vorhanden ist, dann wird nichts gemacht.
Die Zelle A1 ist ohne Bedeutung, genau so gut könnte da auch AC65000 stehen. Aber um zu überprüfen ob eine Adressierung auch korrekt geschrieben ist, muss sie halt vollständig sein. Und eine vollständige Blattübergreifende Adressierung sieht nun mal halt z.B. so aus.

Code: Alles auswählen

=Tabelle.A1

Falls jetzt irgendjemand meint, dass diese zusätzliche Überprüfung überflüssig ist, der sollte mal folgendes in der Beispieldatei von Lazzard machen.
- Im Blatt CSV in B2 einfach Epson eingeben.
- Und nun in I2 einfach die Formel von paljass eingeben.

Code: Alles auswählen

=SVERWEIS(A2;INDIREKT("$'Reb.f."&B2&"'.$B$12:$G$1000");6;0)

Ergebnis?
JA! Es werden Zahlen angezeigt.
Aber ist das auch wirklich korrekt?
Andersrum gefragt. Darf dort überhaupt in diesem Falle eine Zahl angezeigt werden?
Antwort.
N E I N !
Und warum nicht?
Ganz einfach. Weil folgende Adressierung (Zellbereich) ungültig ist!

Code: Alles auswählen

='Reb.f.Epson'.B12:G1000

(mal ohne störende Dollarzeichen)
Es gibt kein Tabellenblatt mit dem Namen: Reb.f.Epson
Aber warum Calc dennoch eine Zahl anzeigt, ist mir ein Rätsel.
Ich habe jetzt bewusst Zahl gesagt, da ich nicht weiß woher Calc den Wert holt. Es ist mir ein Rätsel wo der Wert herkommt, genauer gesagt, aus welchem Blatt kommt der Wert?

Und nun gibt mal in I2 folgende Formel ein.

Code: Alles auswählen

=INDIREKT("'Reb.f."&B2&"'.A1")

Das Ergebnis ist doch ziemlich eindeutig. Es wird ein Fehlerwert ausgegeben.
Err: 502 (Ungültiges Argument)
Da ja aber B3 wohl einen vorhandenen existierenden Namen beinhaltet, zieht einfach mal die Formel in I2 nach I3 runter.
Ergebnis in I3 müsste dann jetzt wohl eine Null (0) sein. Vorausgesetzt, dass Nullwerte anzeigen in den Optionen nicht ausgeschaltet ist.

Und damit dürfte jetzt wohl doch so ziemlich eindeutig sein, dass die zusätzliche Blattüberprüfung nicht überflüssig ist. Oder!?


Weiter gehts mit dem nächstem Teil "meiner" Formel.

Code: Alles auswählen

WENN(ZÄHLENWENN(INDIREKT("'Reb.f."&B2&"'.B12:B1000");A2)

Wenn das vorhin überprüfte Tabellenblatt vorhanden ist, dann wird nun mit diesem Teil überprüft ob überhaupt das Suchkriterium vorhanden ist.

Code: Alles auswählen

SVERWEIS(A2;INDIREKT("'Reb.f."&B2&"'.B12:G1000");6;0);""))

Und wenn das vorhanden ist, dann tritt die SVERWEIS in Aktion. Sollte aber das Suchkriterium nicht vorhanden sein, dann wird halt nichts gemacht

Testumgebung:
WinXP
OOo 3.2.1

Noch irgendwelche Fragen


Gruß
balu

[lorbass]

@paljass,

Mal sehen, ob lorbass noch mehr als ein "Ja!" auf deine Frage zu melden hat. So wie ich ihn kenne, lautet die Antwort "Ja!".

Nööö…

Kann man mit dem SVERWEIS nicht realisieren, dass er mehrere Matrixen gibt [das Verb »gibt« habe ich gedanklich durch »verwendet« ersetzt; künsterische Freiheit ] bzw mehrere Tabellenblätter durchsucht werden ?

Ich bleibe erst mal bei meiner ursprünglichen Antwort: Ja, man kann mit dem SVERWEIS nicht realisieren, dass er – wer auch immer »er« sei – mehrere Matrixen gleichzeitig verwendet bzw. mehrere Tabellenblätter durchsucht werden.

Die Anforderung nach gleichzeitiger Verwendung mehrerer (2-dimensionaler) Matrizen in einem SVERWEIS-Aufruf entnehme ich der Fragestellung im ersten Post, insbesondere der zweiten Frage:

Gibt es vielleicht eine Möglichkeit, dass ich im SVERWEIS bestimmen kann, dass er auf mehreren Reitern/Tabellen sucht ?
Also sozusagen mehrere Matrix Bereiche angebe ?

Knackpunkt ist also die Frage, ob die sukzessive Verwendung mehrerer 2-dimensionale Matrizen in ein und demselben SVERWEIS-Aufruf gefordert ist oder die gleichzeitige Verwendung mehrerer 2-dimensionalen Matrizen (also einer 3-dimensionalen).

Ich habe habe mich für die Gleichzeitigkeit entschieden und meine Antwort kurz, aber eindeutig formuliert. Die alternative Interpretation erscheint mir allerdings durchaus möglich, und wenn Lazzard damit zufrieden ist, ist ja alles in Butter.

allerdings ist m.E. das Problem klar beschrieben

Das stimmt offensichtlich nicht.

Gruß
lorbass

[Lazzard]

@Balu

Vielen Dank für deine neue Formel und die ausführliche Erklärung.
Ich habe deine Formel sofort eingesetzt und ausprobiert.

Leider liefert er für 3 Tabellenblätter kein Ergebnis. Wo vorher #NV stand, ist nun ein leeres Feld.
Es betrifft Produkte aus 3 Tabellenblätter. Ich kann jedoch keinen Unterschied zwischen den Tabellenblätter sehen.
Diese 3 Tabellenblätter lässt er komplett aus.

Irgendwie bin ich jetzt verwirrt !?!?!

[balu]

Hallo Lazzard,

Vielen Dank für deine neue Formel und die ausführliche Erklärung.

Gern geschehn.

Leider liefert er für 3 Tabellenblätter kein Ergebnis. Wo vorher #NV stand, ist nun ein leeres Feld.

Und nun musst Du dich fragen, liegt das am Namen des Tabellenblattes oder an dem nicht vorhanden sein des Suchkriterium? Das kannst Du aber selber versuchen herauszubekommen.
Mit der noch eingesetzten Formel bekommst Du ja nicht die Info ob das Blatt, oder das Suchkriterium nicht da ist, da ja in beiden Fällen eine leere Zelle angezeigt wird. Du musst also nur die Formel etwas ändern, damit Du nun schlauer wirst. Beispielsweise so.

Code: Alles auswählen

=WENN(ISTFEHLER(INDIREKT("'Reb.f."&B2&"'.A1"));"Blatt existiert nicht!";WENN(ZÄHLENWENN(INDIREKT("'Reb.f."&B2&"'.B12:B1000");A2);SVERWEIS(A2;INDIREKT("'Reb.f."&B2&"'.B12:G1000");6;0);"Suchkriterium existiert nicht!"))

Wenn Blatt existiert nicht ausgegeben wird, dann stimmt etwas mit dem Blattnamen nicht.
Und wenn Suchkriterium existiert nicht ausgegeben wird, dann liegt der Fehler bei dem Suchkriterium.

Bei Blattfehler, liegt vielleicht ein schreibfehler vor. Das könnte z.B. daher kommen das in der Zelle Bx (B2 bis B????) vor oder hinter dem Namen ein Leerzeichen steht. Wenn vor dem Namen, dann würde die Adressierung in etwa so aussehen.

Code: Alles auswählen

='Reb.f. Brother'.A1

Wenn hinter dem Namen, dann in etwa so.

Code: Alles auswählen

='Reb.f.Brother '.A1

Und in beiden Fällen würde die Formel nicht das Blatt finden. Da es falsch zusammengestellt wurde.
Es kann aber auch sein, dass das Blatt selber falsch geschrieben ist. Und damit meine ich den Namen unten im Tabellenregister.


Wenn Suchkriteriumfehler, dann kann es schon etwas schwerer werden den Fehler herauszufinden. Es gibt da mindestens 2 Fehlerquellen.

1.
Das Suchkriterium aus der Spalte A welches Du in den einzelnen Tabellenblättern dort in der Spalte B suchst, ist dort kein Wert sondern Text. Und Text erkennst Du daran, dass ein Hochkomma ( ' ) vorangestellt ist. Oder aber die Zelle ist als Text formatiert. Oder aber abwechselnd beides.

2.
Das Suchkriterium existiert wirklich nicht! Weil es
a)
einfach irgendwie falsch geschrieben ist,
b)
oder weil es wirklich nicht in den einzelnen Tabellenblättern existiert.


Und sollte trotzdem nichts angezeigt werden, also leere Zelle ist zu sehen, dann stimmt vielleicht etwas mit der Spalte G in den einzelnen Tabellenblättern nicht. Vielleicht steht dort einfach nichts drin.

Und nun noch ein paar extrem wichtige Fragen!
- Betriebssystem?
- welche OOo/LO Version?
- Muss die Datei unter Excel (welche Version) UND unter OOo/LO laufen?
- Warum hattest Du eine xls-Datei (Excel) angehängt, anstatt eine ods-Datei (Calc)?


Gruß
balu

[Lazzard]

Hi Balu,

wieder kann ich nur besten Dank sagen, für deine Unterstützung.
Dank deiner Formel, konnte ich die Fehler etwas eingrenzen.

Wenn ich es richtig verstanden habe, gleicht die Formel den Hersteller unter CSV Spalte B mit dem Namen des Rechenblattes ab oder ?
Das Problem ist natürlich, dass z.B. das Rechenblatt Epson heißt, jedoch Minolta dort mit aufgelistet ist, da dies auch entfernt zusammengehört.
Somit kann darüber nicht abgeglichen werden.

Als ich nämlich anstatt Minolta, in der CSV Epson eingetragen habe, zog er sich sofort den Preis.
Zusätzlich ist ein Rechenblatt vorhanden, welches gemischt ist, da es nur vereinzelte Hersteller sind, wo sich keine komplette Seite lohnt.
Somit passt dies auch nicht. Die Daten in der CSV können sich auch in Schreibweise und Angaben unterscheiden, nur die Artikelnummer ist immer gleich.

Dank deiner Formel habe ich nun alle Fehlermeldungen gesehen
Blatt existiert nicht! - Daran habe ich das mit dem Namen des Rechenblattes festgestellt.
Suchkriterium existiert nicht! - Verstehe ich nicht, da es sich hierbei um ein gesamtes Rechenblatt handelt, welches nicht anders als die anderen ist. Jedenfalls aus meiner Sicht.

[Gert Seler]

Hallo Lazzard,
Du hast doch die "Testdatei" im " xls-Format" hier eingestellt !
Ist Dir bekannt, das der "SVERWEIS" in MS_Excel so dargestellt wird =SVERWEIS(Tabelle1! A11
und in OOo_Calc =SVERWEIS(Tabelle1. A11.
Das sind so die kleinen Unterschiede zwischen den beiden.
Ich gehe davon aus, das Balu hier von "OpenOffice_Calc" spricht.

mfg
Gert

[balu]

Hallo Leute,

@Lazzard
Warum beantwortest Du nicht meine letzten Fragen?

- Betriebssystem?
- welche OOo/LO Version?
- Muss die Datei unter Excel (welche Version) UND unter OOo/LO laufen?
- Warum hattest Du eine xls-Datei (Excel) angehängt, anstatt eine ods-Datei (Calc)?

Die hatte ich nicht aus Spaß an der Freud gestellt. Denn davon kann es abhängen wie es weiter geht, ob easy-einfach für Calc, oder kompliziert wegen Excel.

Dank deiner Formel, konnte ich die Fehler etwas eingrenzen.

Na das ist doch schon mal was wert.

Wenn ich es richtig verstanden habe, gleicht die Formel den Hersteller unter CSV Spalte B mit dem Namen des Rechenblattes ab oder ?

Korrekt.

Das Problem ist natürlich, dass z.B. das Rechenblatt Epson heißt, jedoch Minolta dort mit aufgelistet ist, da dies auch entfernt zusammengehört.
Somit kann darüber nicht abgeglichen werden.

Das stimmt, und macht die Sache natürlich gleich komplizierter.

Zusätzlich ist ein Rechenblatt vorhanden, welches gemischt ist, da es nur vereinzelte Hersteller sind, wo sich keine komplette Seite lohnt.

Nicht gut! Zumindest nicht bei der jetzt eingesetzten Formeltechnik.

Die Daten in der CSV können sich auch in Schreibweise und Angaben unterscheiden, nur die Artikelnummer ist immer gleich.

Nun hoffe ich, dass ich kein Denkproblem habe.
Das einzige was wirklich einmalig und individuell ist, ist die Artikel-Nummer (Art-Nr) in der Spalte A im Blatt CSV. Stimmt doch, oder!?

Suchkriterium existiert nicht! - Verstehe ich nicht, da es sich hierbei um ein gesamtes Rechenblatt handelt, welches nicht anders als die anderen ist. Jedenfalls aus meiner Sicht.

Diese Meldung kommt bei der letzten Formel von mir nur dann, wenn folgende Situation herrscht.
ZÄHLENWENN sucht die Art-Nr aus der Spalte A in dem anderen Tabellenblatt in der Spalte B. Und wenn diese Art-Nr nicht gefunden wird, dann gibt es keine Übereinstimmung und folglich die Meldung.



@Gert
Diesmal hast Du wirklich recht, mit deinem Hinweis bezüglich der richtigen schreibweise der Adressierung.

Ich gehe davon aus, das Balu hier von "OpenOffice_Calc" spricht.

Auch das stimmt! Und deshalb hatte ich extra wegen Excel nachgefragt.



Gruß
balu

[Lazzard]

Hallo Gert,

ich arbeite mit OpenOffice 3.3.0 unter Windows XP.
Die Preislisten werden von mir immer als xls abgespeichert, damit ein Kunde die besser öffnen kann.
Es gibt Kunden, die die Preisliste als xls bekommen möchten, normalerweise wird mein druckbarer Bereich als PDF exportiert.
Die Preisliste ist umfangreicher als meine Testdatei, da es noch einen nichtdruckbaren Bereich hinter den Preise gibt
wo Kalkulationen usw drinnen stehen, die dem Kunden natürlich nix angehen.
Im Normalfall wird aus allen druckbaren Bereichen eine PDF exportiert und per Mail versendet.
Der CSV Bereich soll für den Datenimport bei Shop-Betreibern vorgesehen sein.