Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1108to1112
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Index oder SVERWEIS ?

Index oder SVERWEIS ?
wulfjack
Hallo,
ich suche eine Lösung für folgendes Problem:
In Tabelle1 stehen für bestimmte Artikel (Spalte A) Mengen in Spalte B.
In Tabelle2 stehen für diese Artikel (Spalte A) für definierte Staffelmengen (Spalte B) entsprechende Staffelpreise (Spalte C).
Ich möchte nun in Spalte C der Tabelle1 zu jedem Artikel den jeweils gültigen Staffelpreis (bezogen auf die Menge in Tabelle1 der nächst niedrige bzw. der entsprechende, wenn die Menge gleich ist)
Beispiel:
Tabelle1
___A______B_______C__
Artikel A____7_____26,00
Artikel B____5_____25,00
Artikel C____5_____90,00
Tabelle2
___A______B_______C__
Artikel A____3_____28,00
Artikel A____5_____26,00
Artikel A___10_____24,00
Artikel B____1_____35,00
Artikel B____3_____30,00
Artikel B____5_____25,00
Artikel C____3_____90,00
Artikel C____6_____68,00
Ich hoffe, da kann mir jemand helfen.... ich komme weder mit INDEX, VERGLEICH oder SVERWEIS nicht weiter.
AW: Index oder SVERWEIS ?
22.10.2009 16:00:47
jockel
hi unbekannte(r), das geht so..:
Tabelle1
 ABC
1Artikel A726
2Artikel B525
3Artikel C590

verwendete Formeln
Zelle Formel
C1 {=KKLEINSTE(WENN((Tabelle2!$A$1:$A$8=A1)*(Tabelle2!$B$1:$B$8<=B1);Tabelle2!$C$1:$C$8);1)}
C2 {=KKLEINSTE(WENN((Tabelle2!$A$1:$A$8=A2)*(Tabelle2!$B$1:$B$8<=B2);Tabelle2!$C$1:$C$8);1)}
C3 {=KKLEINSTE(WENN((Tabelle2!$A$1:$A$8=A3)*(Tabelle2!$B$1:$B$8<=B3);Tabelle2!$C$1:$C$8);1)}
{} Matrixformel mit Strg+Umschalt+Enter abschließen



Tabelle2
 ABC
1Artikel A328
2Artikel A526
3Artikel A1024
4Artikel B135
5Artikel B330
6Artikel B525
7Artikel C390
8Artikel C668

Tabellendarstellung in Foren Version 4.27



cu jörg eine rückmeldung wäre ganz reizend
Anzeige
das funktioniert hier nur zufällig
22.10.2009 16:28:36
WF
Hi Jörg,
such mal nach Artikel A und Menge 9 - dann erhältst Du jetzt richtigerweise 26 bei Menge 5.
Ändere jetzt aber die Menge 26 in z.B. 50, dann erhältst Du 28 bei Menge 3 - es müsste aber 50 sein, denn Menge 5 ist bleibt ja nun mal die dichteste unter Menge 9.
Salut WF
AW: das funktioniert hier nur zufällig
22.10.2009 16:36:04
jockel
hi WF, wo du recht hast, hast du recht, ich hatte das nicht getestet (schande über mich!) ich hatte mich nur nach der starren vorgabe gerichtet... ... mein fehler... glücklicherweise gibts ja eine funktionierende lösung! cu jörg
AW: Index oder SVERWEIS ?
22.10.2009 16:22:06
Tino
Hallo,
geht es so?
 ABCDEFGH
2A726,00 €  A328,00 €
3B525,00 €  A526,00 €
4C590,00 €  A1024,00 €
5     B135,00 €
6     B330,00 €
7     B525,00 €
8     C390,00 €
9     C668,00 €

Formeln der Tabelle
ZelleFormel
C2{=INDEX($H$2:$H$9;MAX(WENN(($F$2:$F$9=A2)*($G$2:$G$9<=B2); ZEILE($1:$8))))}
C3{=INDEX($H$2:$H$9;MAX(WENN(($F$2:$F$9=A3)*($G$2:$G$9<=B3); ZEILE($1:$8))))}
C4{=INDEX($H$2:$H$9;MAX(WENN(($F$2:$F$9=A4)*($G$2:$G$9<=B4); ZEILE($1:$8))))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Gruß Tino
Anzeige
das funktioniert hier nur zufällig - wie oben
22.10.2009 16:59:43
WF
Hi Tino,
such mal nach Artikel B und Menge 4 - da kommt jetzt das richtige aus.
Ändere nun die Reihenfolge von den Mengen des Artikels B (3 vor 1) - dann gibt's Murks.
Salut WF
Sortierreihenfolge?
22.10.2009 17:15:35
Tino
Hallo,
liegt wohl an der Sortierreihenfolge von Zahlen und Text & Zahlen
Vielleicht geht es mit einer Hilfsspalte nach der man die Liste Sortiert.
 ABCDEFGHI
1        Hilfsspalte
2A726,00 €  A1024,00 €A10
3B525,00 €  A328,00 €A3
4C590,00 €  A526,00 €A5
5     B135,00 €B1
6     B330,00 €B3
7     B525,00 €B5
8     C390,00 €C3
9     C668,00 €C6

Formeln der Tabelle
ZelleFormel
C2{=INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A2&B2); ZEILE($1:$8))))}
I2=F2&G2
C3{=INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A3&B3); ZEILE($1:$8))))}
I3=F3&G3
C4{=INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A4&B4); ZEILE($1:$8))))}
I4=F4&G4
I5=F5&G5
I6=F6&G6
I7=F7&G7
I8=F8&G8
I9=F9&G9
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Gruß Tino
Anzeige
funktioniert mit INDEX und Doppel-VERGLEICH
22.10.2009 17:22:23
WF
Hi ihr 3,
testet das mal - hab keinen Fehler gefunden (natürlich, wenn es keinen Wert <= gibt) aber, wer weiß:
{=INDEX(Tabelle2!C:C;VERGLEICH(A1&MAX((Tabelle2!A1:A19=A1)*(Tabelle2!B1:B19<=B1)*(Tabelle2!B1:B19) );Tabelle2!A1:A19&Tabelle2!B1:B19;0)) }
Salut WF
AW: funktioniert mit INDEX und Doppel-VERGLEICH
22.10.2009 18:23:04
wulfjack
Hi Tino,
dein letzter Vorschlag ist wohl schon ziemlich nah dran. Aber was wenn du z.B. statt Artikel A G eingibst?
Oder bei Artikel C 2 Stück?
Meinst Du WF oder mich?
22.10.2009 18:28:05
Tino
Hallo,
Antworten tust Du auf WF ansprechen aber mich?
Gruß Tino
hab ich doch geschrieben
22.10.2009 18:30:19
WF
wenn etwas nicht vorkommt, gibt's logischerweise die Fehlermeldung #NV
WF
(nicht Tino)
Anzeige
AW: funktioniert mit INDEX und Doppel-VERGLEICH
24.10.2009 18:43:17
Wolfgang
WF,
warum habe ich das nicht gelesen? Muss wohl vor "lauter Bäumen den Wald nicht gesehen haben".
Deine Lösung ist super, wenn es unterhalbe der kleinsten Menge ist, oder den Artikel nicht gibt, heisst's #NV, aber das ist absolut okay.
Vielen Dank!
AW: Index oder SVERWEIS ?
22.10.2009 18:36:55
wulfjack
Hi Tino,
dein letzter Vorschlag kommt schon ziemlich nah an die Lösung, aber probier mal folgendes:
Gib mal statt Artikel A G ein, oder ändere mal bei Artikel C die Stückzahl auf 2....
gewünschte Ergebnisse?
22.10.2009 20:12:06
Erich
Hi Vorname,
spannende Frage:
Was wäre denn deiner Meinung nach bei Artikel G das richtige Ergebnis?
Und was sollte bei Artikel C, 2 Stück herauskommen?
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Index oder SVERWEIS ?
22.10.2009 21:13:35
Tino
Hallo,
in der Hoffnung dass Du mich meinst und nicht WF habe ich mal noch etwas experimentiert.
Besser bekomme ich es jetzt auf die Schnelle nicht hin.
Geht es so?
 ABCDEFGHI
1        Hilfsspalte
2G7   A1024A10
3B525  A328A3
4C590  A526A5
5G3,5   B135B1
6B2,135  B330B3
7B135  B525B5
8B0   C390C3
9     C668C6

Formeln der Tabelle
ZelleFormel
C2{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A2&B2); ZEILE($1:$8)))); LÄNGE(A2))=A2)*(A2<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A2&B2); ZEILE($1:$8)))); "")}
I2=F2&G2
C3{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A3&B3); ZEILE($1:$8)))); LÄNGE(A3))=A3)*(A3<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A3&B3); ZEILE($1:$8)))); "")}
I3=F3&G3
C4{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A4&B4); ZEILE($1:$8)))); LÄNGE(A4))=A4)*(A4<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A4&B4); ZEILE($1:$8)))); "")}
I4=F4&G4
C5{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A5&B5); ZEILE($1:$8)))); LÄNGE(A5))=A5)*(A5<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A5&B5); ZEILE($1:$8)))); "")}
I5=F5&G5
C6{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A6&B6); ZEILE($1:$8)))); LÄNGE(A6))=A6)*(A6<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A6&B6); ZEILE($1:$8)))); "")}
I6=F6&G6
C7{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A7&B7); ZEILE($1:$8)))); LÄNGE(A7))=A7)*(A7<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A7&B7); ZEILE($1:$8)))); "")}
I7=F7&G7
C8{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A8&B8); ZEILE($1:$8)))); LÄNGE(A8))=A8)*(A8<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A8&B8); ZEILE($1:$8)))); "")}
I8=F8&G8
C9{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A9&B9); ZEILE($1:$8)))); LÄNGE(A9))=A9)*(A9<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A9&B9); ZEILE($1:$8)))); "")}
I9=F9&G9
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Gruß Tino
Anzeige
AW: Index oder SVERWEIS ?
23.10.2009 14:27:20
wulfjack
Hallo Tino,
sorry, dass ich noch nicht geantortet habe. Deine Lösung gefällt mir schon recht gut. Allerdings gibt es noch ein kleines Problem mit dem Hilfsfeld.... wie Du siehst ist A10 kleiner als A3, daher stimmt's noch nicht so ganz.
Gruss
Wolfgang
noch eine umwandlung Buchstabe
24.10.2009 10:35:35
Tino
Hallo,
ok. noch eine Version, hier wandle ich die Buchstaben in die Codezahl um und verwende diese.
Funktioniert aber nur für einen Buchstaben A-Z und a-z, AG z.Bsp. geht nicht.
Wieder Hilfsspalte und nach dieser sortieren.
 ABCDEFGHI
1        Hilfsspalte
2G7   A32865000000003
3B525  A52665000000005
4C590  A102465000000010
5G3,5   B13566000000001
6B2,135  B33066000000003
7B135  B52566000000005
8B0   C39067000000003
9     C66867000000006

Formeln der Tabelle
ZelleFormel
C2{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A2)*10^9+B2)*($I$2:$I$100<>""); ZEILE($1:$99))))=A2;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A2)*10^9+B2)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D2{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A2)*10^9); $I$2:$I$9))>=CODE(A2)*10^9;1;"")}
I2=CODE(F2)*10^9+G2
C3{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A3)*10^9+B3)*($I$2:$I$100<>""); ZEILE($1:$99))))=A3;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A3)*10^9+B3)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D3{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A3)*10^9); $I$2:$I$9))>=CODE(A3)*10^9;1;"")}
I3=CODE(F3)*10^9+G3
C4{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A4)*10^9+B4)*($I$2:$I$100<>""); ZEILE($1:$99))))=A4;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A4)*10^9+B4)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D4{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A4)*10^9); $I$2:$I$9))>=CODE(A4)*10^9;1;"")}
I4=CODE(F4)*10^9+G4
C5{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A5)*10^9+B5)*($I$2:$I$100<>""); ZEILE($1:$99))))=A5;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A5)*10^9+B5)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D5{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A5)*10^9); $I$2:$I$9))>=CODE(A5)*10^9;1;"")}
I5=CODE(F5)*10^9+G5
C6{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A6)*10^9+B6)*($I$2:$I$100<>""); ZEILE($1:$99))))=A6;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A6)*10^9+B6)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D6{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A6)*10^9); $I$2:$I$9))>=CODE(A6)*10^9;1;"")}
I6=CODE(F6)*10^9+G6
C7{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A7)*10^9+B7)*($I$2:$I$100<>""); ZEILE($1:$99))))=A7;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A7)*10^9+B7)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D7{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A7)*10^9); $I$2:$I$9))>=CODE(A7)*10^9;1;"")}
I7=CODE(F7)*10^9+G7
C8{=WENN(INDEX($F$2:$F$100;MAX(WENN(($I$2:$I$100<=CODE(A8)*10^9+B8)*($I$2:$I$100<>""); ZEILE($1:$99))))=A8;INDEX($H$2:$H$100;MAX(WENN(($I$2:$I$100<=CODE(A8)*10^9+B8)*($I$2:$I$100<>""); ZEILE($1:$99)))); "")}
D8{=WENN(MAX(WENN($I$2:$I$9<=(CODE(A8)*10^9); $I$2:$I$9))>=CODE(A8)*10^9;1;"")}
I8=CODE(F8)*10^9+G8
C9{=WENN((LINKS(INDEX($I$2:$I$9;MAX(WENN(($I$2:$I$9<=A9&B9); ZEILE($1:$8)))); LÄNGE(A9))=A9)*(A9<>""); INDEX($H$2:$H$9;MAX(WENN(($I$2:$I$9<=A9&B9); ZEILE($1:$8)))); "")}
I9=CODE(F9)*10^9+G9
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Gruß Tino
Anzeige
habe die nicht probiert, aber Wolfgang bestimmt oT
24.10.2009 12:23:36
Tino

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige