Office: SVerweis Befehl Problem

Hallo Leute,

bin wirklich am verzweifeln und weiß nimmer weiter.
Weiß das es wohl mit sverweis funktioniert, aber bekomme es einfach nicht hin. Leider konnte ich meine Tabelle hier nich anhängen, bekomme immer eine Fehlermeldung, das die Datei leer ist. Klein genug war sie auch, also hier ein Link zum runterladen:

Die Tabelle

Ich habe 2 Arbeitsblätter
Meine erste heisst Katalog, dort habe ich eine Spalte S mit unseren Katalog Nummern.
Im 2 Arbeitsblatt (OEM Nummern) habe ich nur 2 Spalten. Dort habe ich OEM Nr. + unsere Katalog Nummern.
Jetzt möchte ich die OEM Nummern in mein erstes Arbeitsblatt bekommen in die Spalten Z-AC.
Das Problem ist, dass ich zu meiner Katalognummer teilweise 4 OEM Nummern habe.
Also, wenn er im Arbeitsblatt 2 meine Katalognummer in Arbeitsblatt 1 findet, soll er mir die dazugehörigen OEM Nummern an meine Spalte anhängen.

Hoffe konnte es verständlich rüberbringen und mir kann jemand helfen...

Vielen Dank im voraus

 

http://www.excelformeln.de/formeln.html?welcher=28

 

Hallo,

Du brauchst eigentlich nur die Formel in Z3 eingeben (Array-Formel!), die Formeln in AA3:AC3 erhältst Du durch Kopieren (Hinüberziehen mit der Maus) der Zelle Z3; ferner erhältst Du die Formeln für den Bereich Z4:AC14877, indem Du den Bereich Z3:AC3 markierst und dann in den Zellbereich Z4:AC14877 hinunterziehst (kopierst).

Bemerkung:
Wie Du wahrscheinlich bemerkt hast, sind meine Formeln gegenüber den Formeln in www.excelformeln.de insofern einfacher, als ich keine zusätzliche Funktion um die INDEX(...)-Funktion zum Abfangen von Fehlern (#NV) gebaut habe, sondern lediglich die 3000 als 3.Parameter in der WENN(...)-Funktion eingebaut habe.
Dabei gehe ich bei meinem Datenbestand im Blatt 'OEM Nummern' davon aus, dass sich die eigentlichen Daten im Zellbereich A1:B2999 befinden. Wird dort im Bereich B1:B2999 für die KatalogNr. aus $S3 kein Eintrag gefunden und kann somit kein Wert aus dem Bereich A1:A2999 angezeigt werden, so wird anstatt dessen der Wert aus 'OEM Nummern'!A3000 angezeigt. Dort aber habe ich zB. '--' eingegeben; deshalb '--' in den Zellen AB3 und AC3. Man könnte in A3000 genauso gut ein Leerwert speichern.

Da Du sehr viele Zeilen im Blatt 'Katalog' hast, wirkt sich eine Formelvereinfachung erheblich auf die Geschwindigkeit aus.

	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Katalog Nr.	1	2	3	4	5	6	OEM 1	OEM 2	OEM 3	OEM 14
2
3	102240							130474	60501453	--	--

Zelle	Formel
Z3	{=INDEX('OEM Nummern'!$A$1:$A$3000;KKLEINSTE(WENN('OEM Nummern'!$B$1:$B$2999=$S3;ZEILE($1:$2999);3000);SPALTE(A$1)))}
AA3	{=INDEX('OEM Nummern'!$A$1:$A$3000;KKLEINSTE(WENN('OEM Nummern'!$B$1:$B$2999=$S3;ZEILE($1:$2999);3000);SPALTE(B$1)))}
AB3	{=INDEX('OEM Nummern'!$A$1:$A$3000;KKLEINSTE(WENN('OEM Nummern'!$B$1:$B$2999=$S3;ZEILE($1:$2999);3000);SPALTE(C$1)))}
AC3	{=INDEX('OEM Nummern'!$A$1:$A$3000;KKLEINSTE(WENN('OEM Nummern'!$B$1:$B$2999=$S3;ZEILE($1:$2999);3000);SPALTE(D$1)))}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]

EDIT: Falls Dir die Lösung mit Zelle A3000 unsympathisch ist und da Du ja Excel2007 hast, könntest Du auch die neue Funktion WENNFEHLER(...) einsetzen:
die Formel in Zelle Z3 würde dann lauten (Array-Formel!):
{=WENNFEHLER(INDEX('OEM Nummern'!$A$1:$A$2999;KKLEINSTE(WENN('OEM Nummern'!$B$1:$B$2999=$S3;ZEILE($1:$2999));SPALTE(A$1)));"--")}

 

Vielen Dank für eure Hilfe.

Leider habe ich länger nicht intensiv mit Excel gearbeitet und mit der neuen Version komme ich auch nicht wirklich klar.
Jedesmal wenn ich eine Funktion über Paste einfüge, bekomme ich eine Fehlermeldung, auch bei den Beispielen aus der Excel Hilfe. Wenn ich eine Formel über den Funktions Wizard mache, klappt alles ohne Probleme. Weiß jemand, woran es liegt?
Hier mal ein Screenshot:

 

Hallo,

das kann ich leider aufgrund dieses Screenshots kaum beurteilen, wenngleich ich einen Verdacht habe:
Was passiert, wenn Du die aktive Zelle zB. in Zelle A3 stellst, dort ein '=' Zeichen eingibst, dann die aktive Zelle nach B3 verschiebst und dann die Eingabe-Taste drückst?
Welche Formel steht dann in A3?
Steht dort '=B3' oder steht dort '=ZS(1)' ?
Falls es die 2. Variante wäre, müsstest Du in die Excel-Optionen gehen, Formeln auswählen, in der Checkbox 'Z1S1-Bezugsart' das Häkchen entfernen.

 

nein, das ist es leider nicht, war auch kein Haken gesetzt.
Ich habe doch im ersten Beitrag meine Tabelle, kannst Du dort mal versuchen, die Formel per Paste einzufügen. Würde mich mal interessieren ob es nur bei mir der Fall ist...

 

Hallo,

ich habe es versucht mit Deiner Datei (und natürlich meinem Excel2007) per Copy und Paste meine Array-Formel aus dem Forumsposting ins Excel-Arbeitsblatt einzufügen, es war kein Probelm.

Ich habe es dann hinterher in Deinem Screenshot gesehen, dass nicht die Z1S1-Schreibweise die Ursache sein kann (es müssten nämlich auch die Spaltenköpfe statt A, B, C,... dann 1, 2, 3, ... lauten.

Oder hast Du eventuell überprüft, ob eine widersprüchliche Spracheinstellung die Ursache ist?
zB. eine englische Einstellung: Komma statt Strichpunkt ?
Mir ist auch nicht klar in Deinem Screenshot, warum er den 2.Teil einer simplen absoluten Bereichsangabe $A$1:$A$3000 bei Dir als fehlerhaft bekritelt.

Andererseits erkennt er die Klammersetzung völlig korrekt (Klammerfarben).

 

liegt es vielleicht an den unterschiedlichen Bereichen 1x 1:3000 und einmal 1:2999

 

Hallo,

kann es eventuell daran liegen, dass Du nicht die aktuellen Updates (zB. SP2) installiert hast, denn wie ich da zufällig in einem Microsoft-Blog lese, gab es tatsächlich einmal Probleme vor ein paar Monaten:

in mid December an Excel security fix (MS08-074) broke paste from Excel inside Access. Users would get the following error message:
The data on the Clipboard is damaged, so Microsoft Office Access can't paste it. There may be an error in the Clipboard, or there may not be enough free memory. Try the operation again.
There is a hot fix now available that will fix the issue. Luke Chung has written about how you can work around the issue if you don’t have permissions to install the hot fix (http://support.microsoft.com/kb/967699/)


http://blogs.msdn.com/access/archive/2009/03/18/paste-from-excel-hot-fix-available.aspx

 

Hallo @Hajo,

die unterschiedlich großen Bereiche sind nicht das Problem, ich habe es natürlich auf meinem PC (Versionen siehe unten) vorher ausprobiert und da funktioniert es problemlos.

 

Also, das scheint wirklich ein Excel Problem zu sein. Was bin ich da im Laufe der Zeit schon dran verzweifelt und hat mich ne menge Zeit gekostet.
Leider kann ich hier jetzt kein Update ausführen, weil ich daheim nur über D1 Edge ins Netz komme und es verdammt langsam ist. Allerdings wenn ich die Formel per Hand eingebe, passiert das selbe!

Jetzt habe ich mich mal über Teamviewer in der Firma eingeloggt, dort habe ich SP2 installiert. Habe dort die Formel über Paste eingefügt und bekomme dort aber in
Z3 0
AA3 #ZAHL
AB3 #ZAHL
AC3 #ZAHL

 

Hallo,

Du bist jetzt auf dem richtigen Weg, aber noch nicht ganz!
In meinem ursprünglichen Posting, in dem ich die Formeln angeführt hatte, habe ich mehrmals darauf hingewiesen, dass es sich um Array(oder Matrix)formeln handelt.

Wenn Du in den genannten 4 Zellen nachsiehst, wirst Du jeweils eine Formel vorfinden, die nicht in geschweiften Klammern eingeschlossen ist - aber genau das müssten sie sein für eine korrekte Funktion.

Einfach in Zelle Z3 gehen; dadurch wird die Formel in der Formeleditierzeile angezeigt; dann setzt Du den Eingabe-Cursor zB. ans Ende dieser Formel.
Und dann kommt der entscheidende Schritt: diese Formeleingabe nicht bloß mit der Enter-Taste (= Eingabe-Taste) abschließen, sondern mit der Tastenkombination Strg+Umschalt+Eingabe (=Ctrl+Shift+Enter).
Danach wird die Formel in geschweiften Klammern eingeschlossen angezeigt und es müsste ein korrektes Resultat ohne Fehler angezeigt werden.

Das Ganze dann für AA3, AB3, AC3 wiederholen und den Bereich Z3:AC3 in die darunterliegenden Zeilen hinunter ziehen (=kopieren).

 

Vielen, vielen Dank!
Habt mir sehr geholfen.
Für Arrays war mein kleines wissen nicht ausreichend. Aber jetzt weiß ich, wie ich es richtig behandeln muß. Für den es noch interessant ist oder ähnliche Probleme hat:

Habe die Formel per Fernwartung in einer Zeiler eingegeben und aktiviert.
Dann habe ich es gespeichert und zu mir übertragen. Habe die Datei geöffnet und die Spalten Z3 bis AC3 kopiert. Dann habe ich sie in meiner richtigen Tabelle wieder eingefügt und dann auf die darunterliegenden Zeilen kopiert.
Das hat er alles richtig gemacht. Also besteht das Problem einer Formel bei mir nur, wenn ich es direkt ins Formel Feld einfügen möchte. Wobei ich nicht verstehe, warum er es nicht gemacht hat, als ich es per Hand einzeln reingeschrieben habe.

Etwas ist mir noch aufgefallen und verstehe ich nicht, wenn ich mir die Zeile 23 anschaue mit der Katalog Nr. 102222 gibt er mir bei dem Wert OEM1 die Zahl 1,16382E+11 aus. Der richtige Wert wäre allerdings 116382201401. In OEM2 steht der Wert 162102201401/00, da hätte ich eher ein Problem verstanden. Scheint aber nur in der Zelle vorzukommen.

Wie kann ich es denn lösen, dass er mir statt dem Wert 0 nichts einträgt oder oder False etc..? Hatte es so verstanden, dass er sich den Wert aus z.B. A3000 nimmt, macht er aber nicht.
Habs jetzt mal über die andere Formel gelöst...

 

Bei der zweiten Variante kommt folgende Fehlermeldung, wäre auch zu schön gewesen.



Ok, dann werde ich die Formeln in Werte umwandeln.

 

Hallo,

dieser numerische Wert in wissenschaftl. Schreibe ist mir seinerzeit, als ich die Formeln erstellte, auch aufgefallen und habe in der Zelle nachgeschaut, was dort drinnen steht. Wie erwartet, steht dort tatschächlich keine Zeichenkettenkonstante, sondern ein numerischer Wert, der wegen seiner Größe dann so dargestellt wird.
Anmerkung1:
Seinerzeit hatte ich mich mit Lotus1-2-3 in die Tabellenkalkulation eingearbeitet. Als ich dann auf Excel umstieg und viele Dateien nach Excel übernahm, behielt ich eine Lotus-Einstellung in Excel bei: sie nennt sich in Excel 'Alternative Bewegungstasten' (einstellbar über: Excel-Optionen, Erweitert, Lotus-Kompatibilität): man kann mit dieser Einstellung Zahlenkonstante und Zeichenkettenkonstante ganz leicht unterscheiden: letztere haben nur bei der Anzeige in der Formeleditorzeile als 1.Zeichen vor der angezeigten Konstante: ' (für Linksausrichtung), ^ (für Zentrierung), " (für Rechtsausrichtung)
Am einfachsten stellt man so etwas wie folgt um:
man formatiert zuerst die Zelle als Text, dann schreibt einen Buchstaben vor die 1.Ziffer (mit abschließendem Enter); dadurch wird Excel gezwungen, den Inhalt tatsächlich als Text zu akzeptieren, dann entfernt man wieder den Buchstaben.
Anmerkung2:
Formeln kann man in Excel2007 durch Werte wie folgt ersetzen:
1) man markiert die Zelle (den Zellbereich)
2) man kopiert die Zelle/Zellbereich in die Zwischenablage
3) Rechtsklick mit der Maus auf den markierten Bereich, im Kontextmenü den Eintrag 'Inhalte einfügen...' auswählen
4) Im 'InhalteEinfügen'-Fenster den Radiobutton 'Werte' auswählen, unter Vorgang den Radiobutton 'Keine' auswählen
5) 'Ok'-Schalter klicken.