Sverweis mit mehr als einem Kriterium

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

ich habe ein Problem wofür ich so etwas ähnliches wie einen Sverweis mit mehr als einem Kriterium bräuchte.

Ich habe zwei Excel Tabellen. In der Preisliste habe ich in
Spalte A: Artikelnummern,
Spalte B: Stückzahl; Spalte C: Preis

Untereinander kann beispielsweise 10 mal der gleiche Artikel in verschiedenen Stückzahlen und verschiedenen Preisen stehen.

In der zweiten Liste steht in Spalte A die Artikelnummer und in Spalte B die Stückzahl.

Nun möchte ich in Spalte C der zweiten Liste den gültigen Preis für die in Spalte B angezeigte Stückzahl wiedergeben lassen. Excel müsst also zuerst die Werte aus beiden Listen für die Artikelnummer vergleichen und dann noch die Werte der Stückzahlen vergleichen und den Wert der Spalte C wiedergeben.

Zusätzliche Schwierigkeit ist noch, dass man bei der Stückzahl nach größer/gleich suchen müsste.
Da in Tabelle 1 die Preise beispielsweise so angegeben sind:

Art. 1145 ; Stückzahl 1; Preis 14 €
Art. 1145 ; Stückzahl 100; Preis 12 €
Art. 1145 ; Stückzahl 1000; Preis 10 €

In Tabelle zwei steht jetzt beispielsweise Art. 1145 ; Stückzahl 112
jetzt müsste in Spalte C der zweiten Tabelle der Preis 12 € für ab 100 Stück angegeben werden.

Vielen Dank schon mal für die Vorschläge.

#2

Hey Sandro,

lad doch bitte die Excel Datei hoch. (siehe FAQ)

dann ist es einfacher das Problem zu lösen.

Gruß
Fronk

#3

Hallo Fronk,

die Originaltabelle kann ich aus Datenschutzgründen leider nicht hochladen, aber ich habe mal eine Beispieltabelle aufgebaut.

Mit roter Schrift habe ich beschrieben, welchen Wert ich in Spalte C erwarte. Im ersten Tabellenblatt findet man die Preisliste, also die Quelltabelle.

Danke schon mal für die Hilfe.

Beispieltabelle.xls

VG
Sandro

[MOD] Download-Link korrigiert, Gruß Holger

#4

Hallo
ich habe mal einige Funktionen angewendet um das Ergebnis zu bekommen.
geht möglicherweise noch kürzer, mal schauen.

Hier die Formeln
Beachte den Hinweis zur Matrixformel in Spalte C

Gruß Holger

1 Like
#5

Nun möchte ich in Spalte C der zweiten Liste den gültigen
Preis für die in Spalte B angezeigte Stückzahl wiedergeben
lassen. Excel müsst also zuerst die Werte aus beiden Listen
für die Artikelnummer vergleichen und dann noch die Werte der
Stückzahlen vergleichen und den Wert der Spalte C wiedergeben.

Hallo Sandro,

Holger hat dir ja schon Formellösungen gezeigt.
Ich habe auch eine:

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Beispieltabelle.xls]!Zielliste
 │ A │ B │ C │
──┼────────────┼───────────┼───────┤
1 │ Artikelnr. │ Stückzahl │ Preis │
──┼────────────┼───────────┼───────┤
2 │ 115684 │ 280 │ 9 │
──┼────────────┼───────────┼───────┤
3 │ 156412 │ 1012 │ 20 │
──┼────────────┼───────────┼───────┤
4 │ 187542 │ 95 │ 24 │
──┴────────────┴───────────┴───────┘
Benutzte Formeln:
C2: =SVERWEIS(B2;INDIREKT("Artikel"&A2);2;1)
C3: =SVERWEIS(B3;INDIREKT("Artikel"&A3);2;1)
C4: =SVERWEIS(B4;INDIREKT("Artikel"&A4);2;1)

Festgelegte Namen:
Artikel115684 : =Preisliste!$B$2:blush:C$7
Artikel156412 : =Preisliste!$B$17:blush:C$21
Artikel187542 : =Preisliste!$B$8:blush:C$16

A1:C4
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Die Lösung hat einen nachteil, sie funktioniert wie du siehst mit festgelegten
Namen. Sehr aufwendig die manuell bei vielen Artikeln anzulegen.
Deshalb das nachstehende Makro, es erledigt das.
Brauchst es nur nach Änderungen in der Preisliste.

Gruß
Reinhard

Alt+F11, Einfügen Modul, Code reinkopieren. Editor schließen.
Zum Ausführen des Makros: Alt+F8, NamenVergeben auswählen…

Option Explicit

Sub NamenVergeben()
Dim Zei As Long, Von As Long
With Worksheets("Preisliste")
 .Range("A1").Select
 For Zei = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
 Von = Zei
 While .Cells(Zei + 1, 1).Value = .Cells(Von, 1).Value
 Zei = Zei + 1
 Wend
 ThisWorkbook.Names.Add Name:="Artikel" & .Cells(Von, 1).Value, \_
 RefersTo:="=Preisliste!$B$" & Von & ":blush:C$" & Zei
 Next Zei
End With
End Sub
#6

Grüezi zusammen

Ich habe auch noch ein wenig gebastelt und kann eine Lösung ohne Matrixformeln anbieten:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │ F │ G │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 1 │ Artikelnr. │ Stückzahl │ Preis │ │ Artikelnr. │ Stückzahl │ Preis │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 2 │ 115684 │ 280 │            9,00 € │ │ 115684 │ 1 │         12,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 3 │ 156412 │ 1012 │         20,00 € │ │ 115684 │ 20 │         11,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 4 │ 187542 │ 95 │         24,00 € │ │ 115684 │ 50 │         10,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 5 │ │ │ │ │ 115684 │ 100 │            9,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 6 │ │ │ │ │ 115684 │ 1000 │            8,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 7 │ │ │ │ │ 115684 │ 2000 │            7,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 8 │ │ │ │ │ 187542 │ 1 │         29,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
 9 │ │ │ │ │ 187542 │ 30 │         27,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
10 │ │ │ │ │ 187542 │ 40 │         26,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
11 │ │ │ │ │ 187542 │ 80 │         24,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
12 │ │ │ │ │ 187542 │ 120 │         23,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
13 │ │ │ │ │ 187542 │ 150 │         21,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
14 │ │ │ │ │ 187542 │ 200 │         19,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
15 │ │ │ │ │ 187542 │ 500 │         15,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
16 │ │ │ │ │ 187542 │ 1000 │         12,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
17 │ │ │ │ │ 156412 │ 1 │         30,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
18 │ │ │ │ │ 156412 │ 100 │         28,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
19 │ │ │ │ │ 156412 │ 500 │         26,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
20 │ │ │ │ │ 156412 │ 1000 │         20,00 € │
───┼────────────┼───────────┼───────────────────┼───┼────────────┼───────────┼───────────────────┤
21 │ │ │ │ │ 156412 │ 5000 │         18,00 € │
───┴────────────┴───────────┴───────────────────┴───┴────────────┴───────────┴───────────────────┘
Benutzte Formeln:
C2 : =SVERWEIS($B2;BEREICH.VERSCHIEBEN($E$1;VERGLEICH($A2;$E:blush:E;0)-1;1;ZÄHLENWENN($E:blush:E;$A2);2);2;1)

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

die Originaltabelle kann ich aus Datenschutzgründen leider
nicht hochladen, aber ich habe mal eine Beispieltabelle
aufgebaut.
http://www.file-upload.net/download-7933342/Beispiel…

Hallo Sandro,

bezogen auf deine Beispieltabelle, stell dich in Zielliste!C2 wenn du die Namensformel
eingibst.
Die Preisliste muß nach Artikelnummer sortiert sein.

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Beispieltabelle.xls]!Zielliste
 │ A │ B │ C │
──┼────────────┼───────────┼───────┤
1 │ Artikelnr. │ Stückzahl │ Preis │
──┼────────────┼───────────┼───────┤
2 │ 115684 │ 280 │ 9 │
──┼────────────┼───────────┼───────┤
3 │ 156412 │ 1012 │ 20 │
──┼────────────┼───────────┼───────┤
4 │ 187542 │ 95 │ 24 │
──┴────────────┴───────────┴───────┘
Benutzte Formeln:
C2: =SVERWEIS(B2;INDIREKT(Preise);2;1)
C3: =SVERWEIS(B3;INDIREKT(Preise);2;1)
C4: =SVERWEIS(B4;INDIREKT(Preise);2;1)

Festgelegte Namen:
Preise: ="Preisliste!$B"&VERGLEICH(A2:stuck\_out\_tongue\_winking\_eye:reisliste!A:A;0)&":blush:C"&VERGLEICH(A2:stuck\_out\_tongue\_winking\_eye:reisliste!A:A;1)

A1:C4
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard

#8

Hi zusammen,

vielen Dank für die Antworten. Hat alles super geklappt.

VG
Sandro