Zwei Exel Tabellen abgleichen und Werte übernehmen

[PetLe]

Hallo Zusammen

Ich habe folgendes Problem. Ich habe eine Tabelle mit Art.Nummern und Preisen die ich mir zusammengestellt habe.
Von Lieferanten bekomme ich exel Listen ohne Preisen.
Also muß exel die Tabellen abgleichen und bei übereinstimmen der Art. Nummern die Preise aus meiner Tabelle in die Lieferantenlisten übernehmen.
Das Problemm ligt noch daran das die Listen unsortiert sind.
Also brauche ich folgendes:
Tabelle1 SpalteA2 abgleich mit Tabelle2 Spate A2:A1000, bei übereinstimmen den Preis aus I(???)Tabelle2 in die Spalte I2 Tabelle der Tabelle 1 zu übernehmen. Dann kann ich hoffentlich die Formel einfach runterziehen.

Ich hoffe das ich mich verständlich ausgedrückt habe, und hoffe auf Ihre hilfe.
DANKE

 

[blablub1212]

Entweder mit SVERWEIS: https://support.office.com/de-de/article/sverweis-funktion-0bbc8083-26fe-4963-8ab8-93a18ad188a1

Oder halt mittels Join und Pivot-Tabelle.

 

[ella_one]

Warum nicht die Tabellen in eine Microsoft Access DB importieren? Auswertungen jeder Art sind mit Access kinderleicht zu realisieren!

 

[Makkaroni]

Ich würde es wie vorgeschlagen mit sverweis (vlookup) versuchen. Wenn du einen Auszug der Spalten mit Daten hier postest können es dir bestimmt viele zeigen.

 

[hroessler]

@ella_one Vielen Dank für diesen Beitrag. Ich kann nicht verstehen warum man krampfhaft versucht Excel als Datenbank zu missbrauchen!

greetz
hroessler

 

[PetLe]

Tabelle1 (Matrix)
Tabelle2 (Zieltabelle)

 

[Bixol]

keine ahnung wie die funktion auf deutsch heisst, aber mit dem Cerca.vert (vlookup ?) geht das ganz easy.

wenn du probleme hast dass das endresultat ein fehler ist wenn der artikel nicht erscheint, dann gibt es dafür auch wieder ein formel die das ausgelicht.

 

[PetLe]

Ich würde es wie vorgeschlagen mit sverweis (vlookup) versuchen. Wenn du einen Auszug der Spalten mit Daten hier postest können es dir bestimmt viele zeigen.

 

[Makkaroni]

So sieht der Sverweis aus. Ich habe eine englische Version hier heisst der Befehl VLookup.
Die Preise befinden sich in der Range: Tabelle1!$A$1:$I$10 . Innerhalb dieser Range vergleicht Vlookup den Zellenwert A2 (Material ohne Preis) in Tabelle1 mit dem Wert der ersten Spalte (Material mit Preis) in Tabelle2. Wenn eine Übereinstimmung besteht, wird der Wert aus Spalte 9 zurückgegeben. Wird der Wert nicht gefunden, gibt VLookup ein unschönes #N/A zurück.

Daher habe ich die Funktion IfError noch dazugenommen um die #N/V nicht anzuzeigen.

Beim Sverweis musst du darauf achten die Zelladressen absolut also mit $ anzugegen, da du sonst beim "Runterziehen" die Range änderst, was du in diesem Fall nicht willst.

Noch eine Anmerkung zu dem Vorschlag die Daten erst nach MS Access zu importieren: Lass das bitte.
In diesem Fall ist die Lösung sehr einfach u. der Zeitaufwand durch ein Import nach MS Access mit den entsprechenden SQL-Abfragen deutlich größer.

 

[GunMum]

@ Datenbank-Fans
[Sarkasmus] Für 1.000 Zeilen aber mindestens einen SQL-Server, DB2, Oracle, ... [/Sarkasmus]
Wobei ich die Datenbank-Funktionalität des Power Query sehr schätze, ich würde sie hier wegen fehlender Klimmzüge bei nicht vorhandenen Verweisen gerne nutzen.

 

[PetLe]

Danke für die schnelle Hilfe
Leider gibt er mir (mit Ausnahme von 1 Wert) nur NV raus.

 

[GunMum]

Moin,
nimm Power Query, damit geht es prima ...
... und die ganzen Leerspalten habe ich mir verkniffen

 

[PetLe]

Moin,
nimm Power Query, damit geht es prima ...
... und die ganzen Leerspalten habe ich mir verkniffen

Moin,Moin
Besten Dank.
Nur bin ich in exel nicht so gewappt, und der Begriff "Power Query" sagt mir überhaupt nichts.
In der Tabelle sieht man keine Formel die ich in andere Produktgruppen übernehmen kann.
Also muss ich mich da erst durchlesen

 

[GunMum]

Moin Moin,
da ist auch keine Formel drin. Alles per Mausklick!
Wenn du Excel 2010/2013 hast, dann brauchst du ein kostenloses Add-In v on MS, in neueren Versionen ist es bereits integriert. Schau mal hier nach ...

 

[Makkaroni]

Danke für die schnelle Hilfe
Leider gibt er mir (mit Ausnahme von 1 Wert) nur NV raus.

Du hast die Range nicht an deine Daten in Tabelle 1 angepasst. Du suchst nur in den ersten 10 Zeilen hast aber 297. Verstehen solltest du das kommando schon. Versuch das mal selbstständig anzupassen.

 

[PetLe]

Moin Moin,
da ist auch keine Formel drin. Alles per Mausklick!
Wenn du Excel 2010/2013 hast, dann brauchst du ein kostenloses Add-In v on MS, in neueren Versionen ist es bereits integriert. Schau mal hier nach ...

Moin
Mir kocht die Birne schon, anscheinend bin ich dafür zu Doof.
Habe das Add-In Power Query instaliert
Das mit Intelligente Tabelle auch verstanden.
Wenn ich aber mit PQ die Datei auslese, hollt er mit die komplette Preisliste als Tabelle3 runter.
In der Tabelle wo die Preise reingehören fehlen die immer noch.
Und einen Reiter vergleichen & verfollständigen gibt es nicht.
Muss ich im Bereich wo die Preise reingehören doch eine Formel setzen ???

 

[GunMum]

Moin,
na ja, ich habe auch einige Zeit gebraucht, um mich in Power Query einzuarbeiten ...
Ich melde mich nachher noch einmal, bis dahin kannst du dich ja ein wenig in die Materie einlesen. Beispielsweise hier oder hier oder auch sehr ausführlich ...

Und in meiner Musterlösung kannst du im rechten Seitenfenster die einzelnen Schritte nachvollziehen, im linken Seitenfenster gut zwischen den Abfragen wechseln.

 

[GunMum]

So, weiter geht's ...
Ich gehe einfach einmal von deinen Vorgabe-Daten in Beitrag #11 aus und lasse auch die Leerspalten bestehen.

Blatt Tabelle1​

• A2, Strg-Shift-Ende
• Als Tabelle formatieren (hat keine Überschriften)
• Power Query-Von Tabelle
• Im Abfrage-Editor
  • Datei-Schließen & laden in...
  • Nur Verbindung erstellen
  • Bei Eigenschaften (rechtes Seitenfenster) Name ändern: RawData

Blatt Tabelle2​

• Spalte_I (mit den falschen Ergebnissen) löschen
• A2, Strg-Shift-Ende
• Als Tabelle formatieren (hat keine Überschriften)
• Power Query-Von Tabelle
• Im Abfrage-Editor
  • Datei-Schließen & laden in...
  • Nur Verbindung erstellen
• 2. Abfrage durch Doppelklick rechtes Seitenfenster (oder wie auch immer) öffnen
  • Bei Eigenschaften (rechtes Seitenfenster) Name ändern: Destination
  • Register Start-Kombinieren-Abfragen zusammenführen erweitern
  • Abfragen als neue Abfrage zusammenführen
  • Im mittleren (kleinen) Textfeld die Abfrage Quelle wählen
  • In beiden großen Feldern in die Daten Spalte1 klicken, damit beide markiert sind, [OK]
  • in der neuen Spalte Quelle in der Überschrift auf den Doppelpfeil klicken
  • Alle Häkchen AUSSER Spalte9 (Preis) löschen/entfernen
  • Datei-Schließen & laden

Im neu erstellten Tabellenblatt die Preis-Spalte als Währung formatieren.
Bei Änderung der RawData im Arbeitsblatt Daten-Alle aktualisieren.

FEDDISCH

 

[PetLe]

So, weiter geht's ...
Ich gehe einfach einmal von deinen Vorgabe-Daten in Beitrag #11 aus und lasse auch die Leerspalten bestehen.

Blatt Tabelle1​

• A2, Strg-Shift-Ende
• Als Tabelle formatieren (hat keine Überschriften)
• Power Query-Von Tabelle
• Im Abfrage-Editor
  • Datei-Schließen & laden in...
  • Nur Verbindung erstellen
  • Bei Eigenschaften (rechtes Seitenfenster) Name ändern: RawData

Blatt Tabelle2​

• Spalte_I (mit den falschen Ergebnissen) löschen
• A2, Strg-Shift-Ende
• Als Tabelle formatieren (hat keine Überschriften)
• Power Query-Von Tabelle
• Im Abfrage-Editor
  • Datei-Schließen & laden in...
  • Nur Verbindung erstellen
• 2. Abfrage durch Doppelklick rechtes Seitenfenster (oder wie auch immer) öffnen
  • Bei Eigenschaften (rechtes Seitenfenster) Name ändern: Destination
  • Register Start-Kombinieren-Abfragen zusammenführen erweitern
  • Abfragen als neue Abfrage zusammenführen
  • Im mittleren (kleinen) Textfeld die Abfrage Quelle wählen
  • In beiden großen Feldern in die Daten Spalte1 klicken, damit beide markiert sind, [OK]
  • in der neuen Spalte Quelle in der Überschrift auf den Doppelpfeil klicken
  • Alle Häkchen AUSSER Spalte9 (Preis) löschen/entfernen
  • Datei-Schließen & laden

Im neu erstellten Tabellenblatt die Preis-Spalte als Währung formatieren.
Bei Änderung der RawData im Arbeitsblatt Daten-Alle aktualisieren.

FEDDISCH

Moin Moin
Je mehr ich lese in Foren um so weniger ferstehe ich.
Ist da vielleicht in Grundeinstellungen bei PQ was zu beachten?
In exel -Start-Als Tabelle formatieren, komme ich weiter.
Im PQ Editor Datei-Schließen & laden habe ich nur eine möglichkeit, das "Nur verbindung erstellen" ist grau hinterlegt und man kann das nicht nutzen.
Ohne, aber wird kein Fenster rechts aufgemacht.
Und ohne rechten Fenster" wie in deinnen Beispiel" komme ich auch nicht weiter.
Ich brauche die Lösung noch vor dem Urlaub, da ich auch im Urlaub arbeiten muß und da will ich nicht Tage lang vor dem Laptop sitzen.
Wenn wir das bis Weinachten hinkriegen, dann brauche ich nur deine Paypal adresse, und ich zeige meine Dankbarkeit.
Ich bin 61 und nie was mit Computer gelernt, und beim exel habe ich nur aus NOT die Grundkentnisse selbs angelernt, viele Begriffe sind mir fremd.
Schreib mir wenn du Zeit hast(tel. Nr), ich rufe an und wir können vielleicht Step-by-Step durchgehen.
Gruß

 

[Makkaroni]

Du hast ein relativ einfaches Problem, welches in Excel leicht mit der Funktion Sverweis erledigt werden kann.
Der Weg über Powerpivot ist dagegen viel anspruchsvoller u. ich würde Dir davon abraten.

In der Formel für den SVerweis die ich weiter oben gepostet habe, muss nur der Wert 10 durch 297 ersetzt werden. Da die Tabelle1 297 Zeilen umfasst. Du kannst hier auch jeden grösseren Wert eintragen, falls absehbar ist, dass weitere Zeilen hinzukommen.

Trotzdem solltest du verstehen, was du machst und lernen wo man Excel Funktionen nachschaut.
Hier mal die Eklärung zur Funktion SVerweis anhand des Beispiels von oben.
SVerweis(A2; Tabelle1!A1:I10; 9; false)
Das bedeutet: Suche den Wert aus Zelle A2 (Material Tabelle2) im Bereich Tabelle1!A1:I10. Dieser Bereich stellt die 10 Zeilen aus Tabelle 1 dar, wo deine Materialien mit Preisen sind. Der dritte Parameter "9" in der Funktion besagt, dass die 9. Spalte zurückgegeben werden soll. Das ist die Spalte I mit den Preisen. Der letzte Parameter sagt aus wie verglichen werden soll (ähnlich oder gleich). Ich setzte den nahezu immer auf false (d.h. auf Gleichheit vergleichen).