Thema: SVERWEIS?

Hallo EXCEL-Freunde,
wie kann ich Sortierung erreichen:

Kurzübersicht (2)
  
 
A
B
C
D
E
F
G
H
I
J
K
1
Kombi
Art
C
D
E
 
SORTIERT
 
 
 
 
2
2012Test1
Test1
Anton
Huber1
2012
 
2009Test5
#NV
#NV
#NV
#NV
3
2013Test2
Test2
Berta
Huber2
2013
 
2011Test3
Test3
Cäsar
Huber3
2011
4
2011Test3
Test3
Cäsar
Huber3
2011
 
2012Test1
Test5
Emil
Huber5
2009
5
2015Test4
Test4
David
Huber4
2015
 
2013Test2
Test5
Emil
Huber5
2009
6
2009Test5
Test5
Emil
Huber5
2009
 
2015Test4
Test5
Emil
Huber5
2009
Formeln der Tabelle
A2 : =+E2&B2
G2 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A2));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H2 : =SVERWEIS($G2;$A:$F;2)
I2 : =SVERWEIS($G2;$A:$F;3)
J2 : =SVERWEIS($G2;$A:$F;4)
K2 : =SVERWEIS($G2;$A:$F;5)
A3 : =+E3&B3
G3 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A3));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H3 : =SVERWEIS($G3;$A:$F;2)
I3 : =SVERWEIS($G3;$A:$F;3)
J3 : =SVERWEIS($G3;$A:$F;4)
K3 : =SVERWEIS($G3;$A:$F;5)
A4 : =+E4&B4
G4 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A4));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H4 : =SVERWEIS($G4;$A:$F;2)
I4 : =SVERWEIS($G4;$A:$F;3)
J4 : =SVERWEIS($G4;$A:$F;4)
K4 : =SVERWEIS($G4;$A:$F;5)
A5 : =+E5&B5
G5 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A5));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H5 : =SVERWEIS($G5;$A:$F;2)
I5 : =SVERWEIS($G5;$A:$F;3)
J5 : =SVERWEIS($G5;$A:$F;4)
K5 : =SVERWEIS($G5;$A:$F;5)
A6 : =+E6&B6
G6 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A6));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H6 : =SVERWEIS($G6;$A:$F;2)
I6 : =SVERWEIS($G6;$A:$F;3)
J6 : =SVERWEIS($G6;$A:$F;4)
K6 : =SVERWEIS($G6;$A:$F;5)
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Die Spalten A bis E sind die Ausgangsdaten.
Da sich die Daten immer wieder ändern können, suche ich Formeln, die
in separaten Spalten die Sortierung automatisch bei Änderungen vornehemn.
Die Spalte E enthält die Jahre, die über die Hilfsspalte G aufsteigend
sortiert werden.
Jetzt soll über SVERWEIS (oder...?) in den Spalten H bis K die dazugehörige
Übertragung aus den spalten B bis E vorgenommen werden.

Wie könnte das funktionieren?

Besten Dank für eine Hilfe!

mfg
Erich
http://www.toolex.de


		

Hallo Erich,
nicht ganz klar. Willst Du in G nach Spalte A oder E sortieren? So sollte es funktionieren:
In G2 folgende Formel und nach unten kopieren:
=KKLEINSTE($A$2:$A$6;ZEILE()-1)
Wenn Du nach Spalte A sortierst, kannst Du SVERWEIS für H:K verwenden, wenn Du nach Spalte E sortierst, suche mal auf www.excelformeln.de nach "Sverweis nach links".
Gruß
Martin Beck

Hallo Martin,
danke zunächst; war vielleicht missverständlich.
Deine Formel funktioniert für Sortierung der Spalte A nicht, weil Buchstaben dabei
sind und nicht nur Zahlen; deshalb meine Formel in Spalte G.
Ab Spalte H - K sollen jetzt in abhängigkeit von Spalte G die zur gleichen Zeile
gehörenden Daten aus B - E übertragen werden.
Die Zeile 2 müsste so aussehen:

Kurzübersicht (2)

G H I J K 1 SORTIERT         2 2009Test5 Test5 Emil Huber5 2009 Formeln der Tabelle G2 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A2));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))} H2 : =+B6 I2 : =+C6 J2 : =+D6 K2 : =+E6

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Besten Dank nochmal!
mfg
Erich
http://www.toolex.de

Hallo Erich,
ein Beispiel;

TestNeu

A B C D E F G H I J 114           Sortiert         115 2012Test1 Test1 Anton Huber1 2012 2009Test5 Test5 Emil Huber5 2009 116 2013Test2 Test2 Berta Huber2 2013 2011Test3 Test3 Cäsar Huber3 2011 117 2011Test3 Test3 Cäsar Huber3 2011 2012Test1 Test1 Anton Huber1 2012 118 2015Test4 Test4 David Huber4 2015 2013Test2 Test2 Berta Huber2 2013 119 2009Test5 Test5 Emil Huber5 2009 2015Test4 Test4 David Huber4 2015 120                     121           Formel nach rechts und nach unten kopieren         Formeln der Tabelle F115 : =INDEX(A$115:A$119;VERGLEICH(KKLEINSTE($E$115:$E$119;ZEILE(A1));$E$115:$E$119;0))

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
gruß hermann

mfg
Erich
http://www.toolex.de

.

Hallo Herrmann,
war zu schnell. Die Formel reicht doch noch nicht. Wenn in Spalte E zweimal das gleiche Jahr
vorkommt gibts Probleme; deswegen die KombiSpalte A wegen genauer Identifizierung der
Zeile.
Insofern muss ich auf meine Antwort an Martin zurückkommen und bitte nochmals um Prüfung.
Besten Dank!
mfg
Erich
http://www.toolex.de

Hallo Erich,
hilft das:
http://www.excelformeln.de/formeln.html?welcher=14
Gruß
Martin Beck

Hallo Martin,
leider auch nicht.
mfg
Erich
http://www.toolex.de

Spalte H2:
=SVERWEIS(G2;A2:E6;2;0)
Die genaue Definition der Matrix wars!!
mfg
Erich
http://www.toolex.de

Hallo EXCEL-Freunde,
wie kann ich Sortierung erreichen:

Kurzübersicht (2)
  
 
A
B
C
D
E
F
G
H
I
J
K
1
Kombi
Art
C
D
E
 
SORTIERT
 
 
 
 
2
2012Test1
Test1
Anton
Huber1
2012
 
2009Test5
#NV
#NV
#NV
#NV
3
2013Test2
Test2
Berta
Huber2
2013
 
2011Test3
Test3
Cäsar
Huber3
2011
4
2011Test3
Test3
Cäsar
Huber3
2011
 
2012Test1
Test5
Emil
Huber5
2009
5
2015Test4
Test4
David
Huber4
2015
 
2013Test2
Test5
Emil
Huber5
2009
6
2009Test5
Test5
Emil
Huber5
2009
 
2015Test4
Test5
Emil
Huber5
2009
Formeln der Tabelle
A2 : =+E2&B2
G2 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A2));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H2 : =SVERWEIS($G2;$A:$F;2)
I2 : =SVERWEIS($G2;$A:$F;3)
J2 : =SVERWEIS($G2;$A:$F;4)
K2 : =SVERWEIS($G2;$A:$F;5)
A3 : =+E3&B3
G3 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A3));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H3 : =SVERWEIS($G3;$A:$F;2)
I3 : =SVERWEIS($G3;$A:$F;3)
J3 : =SVERWEIS($G3;$A:$F;4)
K3 : =SVERWEIS($G3;$A:$F;5)
A4 : =+E4&B4
G4 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A4));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H4 : =SVERWEIS($G4;$A:$F;2)
I4 : =SVERWEIS($G4;$A:$F;3)
J4 : =SVERWEIS($G4;$A:$F;4)
K4 : =SVERWEIS($G4;$A:$F;5)
A5 : =+E5&B5
G5 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A5));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H5 : =SVERWEIS($G5;$A:$F;2)
I5 : =SVERWEIS($G5;$A:$F;3)
J5 : =SVERWEIS($G5;$A:$F;4)
K5 : =SVERWEIS($G5;$A:$F;5)
A6 : =+E6&B6
G6 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A6));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))}
H6 : =SVERWEIS($G6;$A:$F;2)
I6 : =SVERWEIS($G6;$A:$F;3)
J6 : =SVERWEIS($G6;$A:$F;4)
K6 : =SVERWEIS($G6;$A:$F;5)
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Die Spalten A bis E sind die Ausgangsdaten.
Da sich die Daten immer wieder ändern können, suche ich Formeln, die
in separaten Spalten die Sortierung automatisch bei Änderungen vornehemn.
Die Spalte E enthält die Jahre, die über die Hilfsspalte G aufsteigend
sortiert werden.
Jetzt soll über SVERWEIS (oder...?) in den Spalten H bis K die dazugehörige
Übertragung aus den spalten B bis E vorgenommen werden.

Wie könnte das funktionieren?

Besten Dank für eine Hilfe!

mfg
Erich
http://www.toolex.de

		

Hallo Erich,
nicht ganz klar. Willst Du in G nach Spalte A oder E sortieren? So sollte es funktionieren:
In G2 folgende Formel und nach unten kopieren:
=KKLEINSTE($A$2:$A$6;ZEILE()-1)
Wenn Du nach Spalte A sortierst, kannst Du SVERWEIS für H:K verwenden, wenn Du nach Spalte E sortierst, suche mal auf www.excelformeln.de nach "Sverweis nach links".
Gruß
Martin Beck

Hallo Martin,
danke zunächst; war vielleicht missverständlich.
Deine Formel funktioniert für Sortierung der Spalte A nicht, weil Buchstaben dabei
sind und nicht nur Zahlen; deshalb meine Formel in Spalte G.
Ab Spalte H - K sollen jetzt in abhängigkeit von Spalte G die zur gleichen Zeile
gehörenden Daten aus B - E übertragen werden.
Die Zeile 2 müsste so aussehen:

Kurzübersicht (2)

G H I J K 1 SORTIERT         2 2009Test5 Test5 Emil Huber5 2009 Formeln der Tabelle G2 : {=INDEX($A$2:$A$6;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);ZEILEN(A$2:A2));ZÄHLENWENN(A$2:A$6;"<"&A$2:A$6);0))} H2 : =+B6 I2 : =+C6 J2 : =+D6 K2 : =+E6

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Besten Dank nochmal!
mfg
Erich
http://www.toolex.de

Hallo Erich,
ein Beispiel;

TestNeu

A B C D E F G H I J 114           Sortiert         115 2012Test1 Test1 Anton Huber1 2012 2009Test5 Test5 Emil Huber5 2009 116 2013Test2 Test2 Berta Huber2 2013 2011Test3 Test3 Cäsar Huber3 2011 117 2011Test3 Test3 Cäsar Huber3 2011 2012Test1 Test1 Anton Huber1 2012 118 2015Test4 Test4 David Huber4 2015 2013Test2 Test2 Berta Huber2 2013 119 2009Test5 Test5 Emil Huber5 2009 2015Test4 Test4 David Huber4 2015 120                     121           Formel nach rechts und nach unten kopieren         Formeln der Tabelle F115 : =INDEX(A$115:A$119;VERGLEICH(KKLEINSTE($E$115:$E$119;ZEILE(A1));$E$115:$E$119;0))

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
gruß hermann

mfg
Erich
http://www.toolex.de

.

Hallo Herrmann,
war zu schnell. Die Formel reicht doch noch nicht. Wenn in Spalte E zweimal das gleiche Jahr
vorkommt gibts Probleme; deswegen die KombiSpalte A wegen genauer Identifizierung der
Zeile.
Insofern muss ich auf meine Antwort an Martin zurückkommen und bitte nochmals um Prüfung.
Besten Dank!
mfg
Erich
http://www.toolex.de

Hallo Erich,
hilft das:
http://www.excelformeln.de/formeln.html?welcher=14
Gruß
Martin Beck

Hallo Martin,
leider auch nicht.
mfg
Erich
http://www.toolex.de

Spalte H2:
=SVERWEIS(G2;A2:E6;2;0)
Die genaue Definition der Matrix wars!!
mfg
Erich
http://www.toolex.de