Frage von gauloises1991, 70

2 Tabellen anhand von Artikelnummern vergleichen und den neuen Preis aus Tabelle 1 in die zweite zur entspr. Artikel-Nr einfügen in einer weiteren Spalte?

Hallo, habe folgendes Excelproblem: Es gibt 2 Preislisten. Eine von Firma X mit einem Verkaufspreis eines Artikels und eine vom Kunden, wo auch die Artikel/Waren von Firma X aufgeführt werden mit exakt der gleichen Artikelnummer. Jetzt hat die Fa. X ab 2016 neue Preise und nun will ich die eine Tabelle der Firma mit der anderen Tabelle vom Kunden abgleichen und beim Kunden in einer weiteren Spalte den neuen Preis eintragen lassen von Excel. In einer zusätzlichen Spalte könnte dann auch noch die Differenz stehen und das I-Tüpfelchen wäre noch eine weitere Spalte mit der Differenz als Prozent. Bekommt das einer hin? Das wäre super. Ich hänge mal ein Bild mit an, auf dem das gut zu sehen ist, was ich möchte. Die Formeln wären jetzt konkret mein Ziel. Danke Euch :-)))

Expertenantwort
von Iamiam, Community-Experte für Excel, 36

überhaupt kein Problem: Kopiere das Blatt des Kunden (vermutlich das kleinere) als Ganzes in ein Blatt Deiner Datei und benenne es KuPr

in beiden Dateien sei die Artikelnr in Spalte A (ansonsten schieb sie dahin) Der Preis in beiden Dateien stehe in Spalte G(wie Geld), falls nicht, ordne sie um oder füge leere Spalten ein.In Deinem Blatt Spalte H  (ggf neu einfügen ud später ummordnen) in H2 die Formel:

=Index(KuNr!H:H;Vergleich(A2;KuNr!A:A;0))

und runterziehen (wenn Du es umgekehrt haben willst, Blattnamen ändern und Formel in H2 des Kundenblattes. Dann aber auch ggf Basis der folgenden Vergleichsformel ändern).

Die beiden Preise stehen nun in G (Dein alter) und H (der neue vom Kunden)

Vergleich der Preise: (in I2): =(H2-G2)/G2, %-Format. Das ergibt die prozentuale Änderung, Basis DEIN ALTER Preis (wenn im Kundenblatt: zu =(G2-H2)/H2 verändern)

Du kannst I2 noch bedingt formatieren, zB Werte: <-10%; <-5%, <0%; (>10% vermutlich nicht nötig?); >5%; >0%

Die Bedingte F'atierung könnte etwas Schwierigkeiten machen, wenn eine Bedingung die andere "zudeckt", sollte aber so gehen. evtl "anhalten" wählen, weiß ich jetzt nicht auswendig)

Alles runterziehen über die gesamte Liste,

FERTIG

Kommentar von ANSuck ,

Inwiefern unterscheidet sich die genannte Prozedur funktionell vom Befehl SVERWEIS?

Kommentar von Iamiam ,

in Deinem Fall dürfte es keinen Unterschied geben, generell aber: sie kann viel mehr:

  1. Du ersiehst aus index(KuNr!G:G [da hab ich mich bei der Formel oben verschrieben! nicht H:H, sondern G:G] sofort, aus welcher Spalte der Wert gezogen wird. >>Wichtig bei vielspaltigen Tabellen und spaltenversetzten Bereichen. (bei G kann man das 7 ja noch an den Fingern abzählen, bei mehr Spalten würde das mühsam: ich hatte im Beruf Tabellen, bei denen die für mich relevanten Spalten BK, BL, BM und BO waren)
  2. Du kannst auch aus Spalten links von der Leitspalte was auslesen 
  3. Du brauchst keinen Bereich zu definieren und Dir so eine oft vorkommende Fehlerquelle ($-Setzung) sparen
  4. Du kannst mit ....Vergleich(..)-1 den Wert aus der Zeile drüber oder  mit +2 aus zwei Zeilen drunter auslesen, Du kannst also bei Namenslisten zB prüfen, drunter nochmals der gleiche Name kommt und in den sonstigen Werten, was dort steht (ob zB der Name drunter auch die gleiche Adresse etc. hat)
  5. Du kannst $A:$A (Artikelnummern) und $A2(Vergleichsargument) absolut setzen und dann durch Kopieren nach rechts/links einfach die Spalte daneben auslesen. Beim SVerweis bleibt diesae Spalte beim Kopieren konstant

Beide Formeln können Wildcards (*,?) verwerten

Nachteile kenne ich nur einen

: wenn der Verweisbereich nicht in Zeile 1 beginnt

und

oberhalb der Vergleichsspalte (bei Dir Artikelnr) das Vergleichsargument nochmals vorkommt(zB eingegeben werden soll). Dann muss man zB die Vergleichsspalte mit zB $M$4:$M$9999 formulieren und zu Vergleich()+3 dazuzählen (oder -bei benannten VerweisBereichen +Zeile(Name)-1).

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten