Office: Excel einsortieren

Guten Tag,
folgendes Problem hat sich bei mir ergeben.

Ich habe 3 Dateien mit Artikelnummern, Preisen und Vergleichsnummern. Die 4. Datei ist die Ursprungsdatei, auf welche sich auch die Vergleichsnummern beziehen. Nun möchte ich die einzelnen Positionen der 3 Dateien entsprechend der Vergleichsnummer in der 4. Datei zugeordnet haben, kopiert werden sollte die Artikelnummer und der Preis in eine extra Spalte. Somit ich am Ende 1 Datei habe mit 1 "relevanten" Nummer wo sich dann die Artikelnummern und Preise der anderen Dateien eingeordnet/zugeordnet haben.

Es ist sehr wichtig, Danke

MFG DOBZ

 

Hallo,

dein genauer Tabellenaufbau ist aus der Beschreibung heraus nicht zu erkennen.

Du solltest dir aber die Funktion Sverweis() oder Index und Vergleich näher ansehen.

 

Hallo,

Ich habe 3 Dateien von verschiedenen Herstellern, jeweils mit Artikelnummer und Preis sowie einer relevanten Vergleichsnummer (Die 4. Datei ist die Datei wo die Artikelnummer=Vergleichsnummer aus den anderen Dateien ist. Ich möchte somit also die Artikelnummern und Preise den Artikelnummer=Vergleichsnummern in einer Datei zusammenfassen und zuordnen!

MFG
DOBZ

 

Hallo,

wir sind immer noch bei Sverweis oder bei Index/Vergleich.

Sverweis kannst du dann nehmen wenn die Vergleichsnummer links steht und die übrigen Werte rechts davon.

Wenn die Vergleichsnummer rechts steht und die übrigen Werte links davon dann musst du mit Index/Vergleich arbeiten.

 

Ich krieg das nicht gebacken... kann mir wer irgendwie die Funktion INDEX erklären?

 

Hallo DOBZ,

das Prinzip der INDEX-Funktion ist einfach:
Stell Dir in Deinem Arbeitsblatt einen Bereich vor zB. die Zellen B15:D16. Dieser Beispielbereich hat 2 Zeilen und 3 Spalten. Will man den Inhalt der Zelle, die sich in diesem Bereich in der 2. Zeile und 3. Spalte befindet, verwendet man folgende Formel:
=INDEX(B15:D16;2;3)
also man muss zuerst den Bereich angegeben, auf den sich die Zeilen- bzw. Spaltenzählung beziehen soll (in diesem Fall B15:D16), dann die Zeilennummer innerhalb dieses Bereichs (zB. 2 für die 2. Zeile) und zuletzt die Spaltennummer innerhalb dieses Bereichs (zB. 3 für die Spalte). In voriger Formel bekommst Du im Endeffekt den Inhalt der Zelle D16.
Das ist im Prinzip alles.

 

Hallo DOBZ,

ich habe versucht, zu ermitteln, was Du präzise benötigst. Ich habe Dir eine Excel-Beispieldatei beigelegt. In den Arbeitsblättern 'Tabelle1', 'Tabelle2' und 'Tabelle3' sind jeweils die Spalten 'ArtNr', 'Preis' und 'VerglNr' (=Vergleichsnummern) vorhanden.
Über den Spalten mit den VerglNr habe ich der Einfachhheit halber 3 benannte Bereiche 'VerglNr1', 'VerglNr2' und 'VerglNr3' angelegt.
Im Arbeitsblatt 'Tabelle4' sind Deine Gesamtdaten enthalten. In der SpalteC ist wiederum die Vergleichsnummer (VerglNr) vorhanden, über die Du die 3 Detailblätter abfragst (gelb).
Als erstes wird die Spalte E (Blatt) berechnet, sobald in SpalteC eine Vergl.Nr. eingegeben wurde.
In ihr steht die Formel: =WENN(ISTNV(VERGLEICH($C2;VerglNr1;0));WENN(ISTNV(VERGLEICH($C2;VerglNr2;0));3;2);1)
Sie liefert die Nr. des Detailarbeitsblattes, in der der Artikel gefunden wurde (oder auch 3, falls nicht).
in Spalte F (Zeile) steht die Zeilennummer des Arbeitsblattes, in der der Artikel gefunden wurde (oder aber auch #NV, falls er nicht gefunden wurde). Formel: =VERGLEICH($C2;WAHL(E2;VerglNr1;VerglNr2;VerglNr3);0)
In den Spalten A und B (ArtNr und Preis) steht eine Array-Formel (-> geschweifte Klammer);
sie lautet: =BEREICH.VERSCHIEBEN(WAHL($E2;VerglNr1;VerglNr2;VerglNr3);$F2-1;-2;1;2)
Um diese einzugeben, bitte zuerst die Zellen A2:B2 markieren, die Formel in die Zelleditorzeile kopieren, die Tasten Strg+Umschalt+Eingabe drücken.
Die Formel das Bereichs A2:B2 in die Zeilen darunter kopieren, ebenso die Formeln des Bereichs E2:F2.
Das war's, was zu tun ist mit den Arbeitsblättern der Echtdaten, falls ich Dich recht verstanden habe.

 

erstmal vielen lieben dank für die beispieldatei :)
hilft mir auf jeden fall erstmal weiter, ist es möglich das er die relevante art.nr. + preis aus zB. tabellenblatt 2 in tabellenblatt 4 reinkopiert? oder ist excel damit überfordert?


*edit*
hab mal eine beispieldatei angehangen, wie ich das ca. haben möchte... es ist alles mit der hand reinkopiert, daher keine Formeln in den Zellen

 

Hallo DOBZ,

da ich seit gestern keine Attachment-Dateien mehr hochladen kann (Fehlermeldung: 'Tried to upload empty file'), muss ich Dir im Detail posten, wie ich mir das vorstelle:
Ausgehend von Deiner Beispieldatei vom 16.12.2008:
1) In den Arbeitsblättern 'Tabelle1', 'Tabelle2' und 'Tabelle3' zur Vereinfachung jeweils folgende benannte Bereiche anlegen:
für Spalten A:B die Namen 'ArtNrPreis1', 'ArtNrPreis2', 'ArtNrPreis3'
für Spalte C die Namen 'VerglNr1','VerglNr2','VerglNr3'.
2) Im Arbeitsblatt 'Zusammenfassung':
a) In die Zeile 12 habe ich die Überschriften aus Zeile 1 herunterkopiert, ferner in Zelle J12 die Überschrift 'VerglNr' gegeben.
b) In Zelle J13 habe ich Deinem Wunsch gemäß die Formel eingegeben: '=Tabelle2!$C2' um die Verleichsnummer aus 'Tabelle2' hereinzukopieren.
c) Im Zellbereich A13:B13 habe ich die Array-Formel zur Abfrage der ArtNr. und des Preises aus Blatt 'Tabelle1' für die Vergl.Nr. aus Zelle J13 erstellt: =WENN(ISTNV(VERGLEICH($J13;VerglNr1;0));$J$11;INDEX(ArtNrPreis1;VERGLEICH($J13;VerglNr1;0);SPALTE($1:$2)))
im Zellbereich D13:E13 die analoge Array-Formel für Blatt 'Tabelle2':
=WENN(ISTNV(VERGLEICH($J13;VerglNr2;0));$J$11;INDEX(ArtNrPreis2;VERGLEICH($J13;VerglNr2;0);SPALTE($1:$2)))
im Zellbereich G13:H13 die entsprechende Array-Formel für Blatt 'Tabelle3':
=WENN(ISTNV(VERGLEICH($J13;VerglNr3;0));$J$11;INDEX(ArtNrPreis3;VERGLEICH($J13;VerglNr3;0);SPALTE($1:$2)))
d) Die Formeln aus Zeile 13 in mindest ca. 10 darunterliegenden Zeilen kopieren.
e) Ferner in Zelle J11 zB. die Zeichen 'xx' eingeben; diese sind als Ersatzwerte für Abfragezellen gedacht, für die in bestimmten Blättern eine bestimmte Vergleichsnr. nicht existiert.
f) Wie man Array-Formeln eingibt, habe ich vor ein paar Tagen bereits erwähnt.

Ich hoffe, dieser Vorschlag kommt Deinen Wünschen nahe. Die Anpassung an Deine Echt-Daten sollte dann nicht allzu schwer sein.

 

Funktioniert das dann auch für knapp 15000 Artikelnummern?
Ich bin ein ziemlicher Anfänger was EXCEL angeht ;)

 

Hallo DOBZ,

Du schreibst, dass Du Excel2002 verwendest. Dort sind, so weit ich weiß, 256^2-1=65535 Zeilen im Arbeitsblatt möglich.
Ein gewisses Problem, das optimierungsfähig ist, sind die Array-Formeln in den Spalten A:B, D:E bzw. G:H. Ich musste sie wegen Excel2002 so kompliziert machen, damit die benutzerdefinierten 'xx' für fehlende Artikel-Nr./Preis eingeblendet werden. Wenn Du jedoch auf dieses spezielle Feature verzichten kannst und mit dem Symbol '#NV' (=Fehlerwert für 'nicht verfügbar') zufrieden bist, kann die Array-Formel im Zellbereich A13:B13 stark vereinfacht werden zu:
=INDEX(ArtNrPreis1;VERGLEICH($J13;VerglNr1;0);SPALTE($1:$2))
analog dann auch für D13:E13 bzw. G13:H13.
Wie Du siehst, verwende ich Excel2007: ab dieser Version gibt es eine neue praktische Funktion, deren Syntax lautet:
=WENNFEHLER(Wert;WertFallsFehler).
Diese Funktion erwartet 2 Parameter: der 1. ist der normale Wert, der auch von der Funktion wieder zurückgegeben wird; enthält dieser jedoch einen Fehlerwert, gibt die Funktion den Wert des 2. Parameters zurück.
Mit dieser Funktion würde sich die ursprüngliche Array-Formel auch stark vereinfachen, ohne auf die 'xx' verzichten zu müssen, zu:
=WENNFEHLER(INDEX(ArtNrPreis1;VERGLEICH($J13;VerglNr1;0);SPALTE($1:$2));$J$11)
Nicht nur die Formel ist vereinfacht, sondern auch die Ausführungszeit zB. für 15000 Werte würde sich erheblich verkürzen.
Um dieses Manko von Excel2002 wettzumachen, stelle ich im Folgenden ein VBA-Makro zur Verfügung, das genau das Gleiche macht:

Code:

Option Explicit
Public Function WENNFEHLER(Wert As Variant, WertFallsFehler As Variant) As Variant
  If IsError(Wert) Then
    WENNFEHLER = WertFallsFehler
  Else
    WENNFEHLER = Wert
  End If
End Function

Zum Eingeben dieses VBA-Codes wie folgt vorgehen:
Den VBA-Code mit der Maus markieren, die Tasten Strg+C drücken, um ihn zu kopieren; in ein Excel-Arbeitsblatt wechseln, die Tasten Alt+F11 drücken, um in den VBA-Editor zu wechseln. Im Menü des VBA-Editors 'Einfügen - Modul' anklicken, ins große Code-Eingabefenster hineinklicken; die Tasten Strg+V drücken, um den kopierten Code einzufügen. Ist er im Codefenster zu sehen, wieder zurück ins Arbeitsblatt 'Zusammenfassung' wechseln und letztgenannte Array-Formel für Bereich A13:B13 eingeben (analog ebenfalls für D13:E13 und G13:H13) und das Ganze in die Folgezeilen kopieren.

Mit vorgenannten Optimierungen kann man sicher erhebliche Rechenzeit einsparen. Der Rest ist natürlich hardware-abhängig (Größe des Arbeitsspeichers, Geschwindigkeit des Prozessors, etc.).
Aber von Excel2002 her spricht sicher nichts gegen die Machbarkeit bei dieser Datenmenge.

 

Hallo DOBZ,

ein 1. Nachtrag zur Nachricht von gestern:
Man kann in diesem speziellen Fall (die Vergl.Nr. von 'Tabelle2' wird in die Spalte J von Arbeitsblatt 'Zusammenfassung' kopiert), da die Zeilen von 'Tabelle2' in ihrer ursprünglichen Reihenfolge im Blatt 'Zusammfassung' stehen, eine weitere Optimierung durchführen:
Formel in Zelle D13: =Tabelle2!$A2
Formel in Zelle E13: =Tabelle2!$B2
Beide Zellen in die darunterliegenden Zeilen kopieren.

Ein 2. Nachtrag zur Nachricht von gestern:
Excel ist ein Tabellenkalkulationsprogramm und kein Datenbankprogramm. Es kann daher Datenbankfunktionalitäten nur mehr oder weniger (in)effizent nachahmen. Wenn ich die Array-Formel für Bereich A13:B13 hernehme, so sind im Wesentlichen 3 Funktionen ineinander verschachtelt:
a) zu innerst ist die VERGLEICH-Funktion: dieser wird das Suchkriterium aus Spalte J übergeben, daraufhin macht sie einen linearen Suchlauf durch vorerst alle 15000 Zeilen von 'Tabelle1', bis sie die Zeile in Spalte C findet, die mit Spalte J übereinstimmt. Das ergibt also für jeden Aufruf der VERGLEICH-Funktion im Mittel 7500 Vergleichsschritte, somit insgesamt nur für Tabelle1 7500*15000=112,5 Millionen Vergleichsschritte.
b) die VERGLEICH-Funktion ist umgeben von der INDEX-Funktion: sie übernimmt die Zeilennummer und stellt daraufhin die Werte aus Spalte A bzw. B dieser Zeile von 'Tabelle1' ins Blatt 'Zusammenfassung', ergibt nochmals 2*15000=30000 INDEX-Operationen.
c) zu äußerst ist gegebenenfalls noch die WENNFEHLER-Funktion, die natürlich auch auf 30000 Aktivitäten kommt. Dadurch ist es leicht verständlich, warum sich eine Optimierung, wie sie im 1.Nachtrag enthalten (insgesamt 30000 Aktivitäten), lohnt.
d) Wesentlich effizienter würde dieses typische Datenbankproblem natürlich ein Datenbankprogramm erledigen (vgl. Indizierung von Tabellen).
Davon gibt es welche, für die muss man löhnen, und solche, die gibt es kostenlos zum Download (zB. 'Base' aus OpenOffice; ferner eine leicht abgemagerte Version des 'SQL-Server's von Microsoft, etc.)