Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Werte aus Matrix auslesen
30.11.2012 08:19:57
René
Hallo Leute & Experten,
ich habe ein mittelgroßes Problem. Ich möchte aus einer Matrix (B3:K8) vier verschiedene Werte nacheinander auslesen und diese spaltenweise in eine neue Tabelle schreiben (S3:...). Die Werte kommen mehrmals vor, deswegen funktioniert SVERWEIS nicht. Zuerst alle Zellen die eine "1" enthalten, dann alle mit "3", dann alle mit "5" und alle mit "7". Es kommt vor, dass einige Zellen andere Werte enthalten oder leer sind. Diese Zellen sollen gar nicht beachtet werden.
Ich hoffe ihr könnt mir helfen.
Vielen Dank

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Werte aus Matrix auslesen
30.11.2012 10:55:06
Tino
Hallo,
meinst Du so? Die Formeln in A13:K13 einfach nach unten Ziehen.
 ABCDEFGHIJKLM
1Daten Tabelle Such-Werte
2            1
3 1$C$3$D$3$E$3$F$3$G$3$H$3$I$3$J$3  3
4   $D$4$E$4  $H$4$I$4$J$4  5
5 5 a$E$5  $H$5 $J$5  7
6 7$C$6 $E$6$F$6$G$6$H$6 $J$6$K$6  
7 8 $D$7$E$7$F$7$G$7$H$7$I$7 $K$7  
8 7 $D$8$E$8$F$8$G$8$H$8$I$8 $K$8  
9             
10             
11Ausgabe  
12Index            
1331$C$3$D$3$E$3$F$3$G$3$H$3$I$3$J$3   

Formeln der Tabelle
ZelleFormel
A13{=WENN(ISTFEHLER(KKLEINSTE(WENN(ZÄHLENWENN($M$2:$M$5;$B$3:$B$8)>0;ZEILE($B$3:$B$8)); ZEILE(A1))); "";KKLEINSTE(WENN(ZÄHLENWENN($M$2:$M$5;$B$3:$B$8)>0;ZEILE($B$3:$B$8)); ZEILE(A1)))}
B13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(A1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(A1)); ""); "")
C13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(B1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(B1)); ""); "")
D13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(C1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(C1)); ""); "")
E13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(D1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(D1)); ""); "")
F13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(E1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(E1)); ""); "")
G13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(F1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(F1)); ""); "")
H13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(G1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(G1)); ""); "")
I13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(H1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(H1)); ""); "")
J13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(I1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(I1)); ""); "")
K13=WENN($A13<>"";WENN(INDEX($B$1:$K$10;$A13;SPALTE(J1))<>"";INDEX($B$1:$K$10;$A13;SPALTE(J1)); ""); "")
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Hier noch die Testmappe von mir
https://www.herber.de/bbs/user/82827.xls
Gruß Tino

Anzeige
AW: Werte aus Matrix auslesen
30.11.2012 11:51:56
René
Ich hätte gleich die Beispieldatei mit dazu tun sollen. Dann wäre es etwas verständlicher gewesen.
https://www.herber.de/bbs/user/82828.xls
Mein beschriebenes Problem ist erst der Anfang. Die Sache geht noch weiter, wenn du dir das Beispiel anschaust.

AW: Werte aus Matrix auslesen
30.11.2012 12:06:11
René
Ich versuche es nochmal anders zu erklären:
Ich suche eine Formel oder ein Makro welches den Bereich B3-B8 Zeilenweise erst nach allen "1en", dann "3en", dann "5en" und zum Schluss nach allen "7en" durchsucht und diese dann in eine andere Tabelle überträgt. So daß diese Zahlen in einer Zeile stehen.
z.B.: 1 1 1 1 1 1 3 3 5 5 5 5 7 7 7 7 7
Das wäre die erste Zeile meines Problems. Würde mich aber schon sehr viel weiter bringen.

Anzeige
AW: Werte aus Matrix auslesen
30.11.2012 14:17:52
Christian
Hallo Rene,
Vorschlag:
Die Bezüge musst Du dann noch anpassen!
 ABCDEFGHIJKLMNOPQRSTUVW
1d0                      
2xxxxxxxxxxxx AusprägungAnzahl  d12345
3x1111111100x 111  011111
4x3323300000x 34  1     
5x5555555550x 510  2     
6x7775461102x 712  3     
7x7777777600x  37  4     
8x7666710002x     6     
9xxxxxxxxxxxx           

Formeln der Tabelle
ZelleFormel
S3{=WENN(SPALTE(A1)>SUMMENPRODUKT((REST($B$3:$K$8;2)=1)*($B$3:$K$8>=1)*($B$3:$K$8<=7)); "";KKLEINSTE(WENN((REST($B$3:$K$8;2)=1)*($B$3:$K$8>=1)*($B$3:$K$8<=7); $B$3:$K$8); SPALTE(A1)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Anzeige
AW: Werte aus Matrix auslesen
04.12.2012 12:27:55
René
Hallo Christian,
interessante Formel. Funktioniert auch so wie ich es mir gedacht habe.
Dummerweise habe ich gedacht der Rest wird ein Klacks.
Ich wollte mit der Formel "BEREICH.VERSCHIEBEN" den Wert 10 Zeilen weiter unter (in Abhängigkeit von den Werten aus S3 - ....)ausgeben lassen.
z.Bsp: In S3 steht der Wert aus B3 -- in S4 soll der Wert aus B13 stehen.
In AA3 steht der Wert aus H6 -- in AA4 soll der Wert aus H16 stehen.
MfG René

AW: Werte aus Matrix auslesen
04.12.2012 12:59:07
René
Hallo Christian,
interessante Formel. Funktioniert auch so wie ich es mir gedacht habe.
Dummerweise habe ich gedacht der Rest wird ein Klacks.
Ich wollte mit der Formel "BEREICH.VERSCHIEBEN" den Wert 10 Zeilen weiter unter (in Abhängigkeit von den Werten aus S3 - ....)ausgeben lassen.
z.Bsp: In S3 steht der Wert aus B3 -- in S4 soll der Wert aus B13 stehen.
In AA3 steht der Wert aus H6 -- in AA4 soll der Wert aus H16 stehen.
MfG René

Anzeige
AW: Werte aus Matrix auslesen
04.12.2012 13:16:56
Christian
Hallo Rene,
ein Ansatz:
Tabelle1

 ABCDEFGHIJKLMNOPQRSTUVWXY
1d0                        
2xxxxxxxxxxxx AusprägungAnzahl  d1234567
3x1111111100x 111  01111111
4x3323300000x 34  11135777
5x5555555550x 510  23      
6x7775461102x 712  31      
7x7777777600x  37  4115    
8x7666710002x     61      
9xxxxxxxxxxxx             
10                         
11d1                        
12xxxxxxxxxxxx AusprägungAnzahl          
13x10220430  x 12          
14x58 76     x 31          
15x13116910871112 x 51          
16x712138  41  x 73          
17x119101616610   x  7          
18x18   174    x             
19xxxxxxxxxxxx             

Formeln der Tabelle
ZelleFormel
S3{=WENN(SPALTE(A1)>SUMMENPRODUKT((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7)); "";KKLEINSTE(WENN((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7); ber_flex); SPALTE(A1)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
S3ber_flex=INDEX(Tabelle1!$B:$B;3+((ZEILE(Tabelle1!A1)-1)*10)):INDEX(Tabelle1!$K:$K;8+((ZEILE(Tabelle1!A1)-1)*10))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Anzeige
AW: Werte aus Matrix auslesen
04.12.2012 14:44:59
René
Hallo Christian,
danke erstmal für den interessanten Ansatz. Das kannte ich bisher auch noch nicht. Ich glaube ich habe es verstanden. Es macht nur nicht ganz das, was ich möchte.
https://www.herber.de/bbs/user/82885.xls
Ich habe nochmal das abgeänderte Beispiel eingefügt und als Beispiel mal eine Tabelle darunter eingefügt "Das soll drinstehen". Vielleicht kannst du so nachvollziehen, wie ich es meine aber nicht richtig umschreiben kann.
MfG René

Nachfrage!
04.12.2012 15:19:50
Christian
Hallo Rene,
verstehe ich Dich richtig, das Du den Bereich d0 als Referenz nimmst, und dann die Werte entsprechend diesem Bereich aus den anderen Bereichen ausgeben willst?
Das wird mit Formeln schwierig, da müssen die VBA-Profis ran.
MfG Christian

Anzeige
AW: Nachfrage!
04.12.2012 15:30:15
René
Hallo Christian,
"verstehe ich Dich richtig, das Du den Bereich d0 als Referenz nimmst, und dann die Werte entsprechend diesem Bereich aus den anderen Bereichen ausgeben willst?
"
genau so ist es.
Ich habe mir das schon fast gedacht, dass das mit Formeln nicht zu lösen ist. Obwohl es schon recht nah dran war.
Vielen Dank erstmal.

mit Hilfszeilen
04.12.2012 16:27:41
Christian
Hallo Rene,
ein Ansatz mit Hilfszeilen:
Tabelle1

 ABCDEFGHIJKLMNOPQRSTUVWX
1d0                       
2xxxxxxxxxxxx AusprägungAnzahl  d123456
311111111100x 111  0111111
423323300000x 34  1102204
535555555550x 510  22 28 16
647775461102x 712  31 215 41
757777777600x     41 228 152
867666710002x     60 2132 1500
9xxxxxxxxxxxx            
10                        
11d1                       
12xxxxxxxxxxxx      111111
13x10220430  x      123456
14x58 76     x      123456

Formeln der Tabelle
ZelleFormel
S3{=WENN(SPALTE(A1)>SUMMENPRODUKT((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7)); "";KKLEINSTE(WENN((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7); ber_flex); SPALTE(A1)))}
T3{=WENN(SPALTE(B1)>SUMMENPRODUKT((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7)); "";KKLEINSTE(WENN((REST(ber_flex;2))*(ber_flex>=1)*(ber_flex<=7); ber_flex); SPALTE(B1)))}
S4=WENN(S$3="";"";WENN(INDEX(ber_flex;S$12;S$13)="";"";INDEX(ber_flex;S$12;S$13)))
T4=WENN(T$3="";"";WENN(INDEX(ber_flex;T$12;T$13)="";"";INDEX(ber_flex;T$12;T$13)))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
S3ber_flex=INDEX(Tabelle1!$B:$B;3+((ZEILE(Tabelle1!A1)-1)*10)):INDEX(Tabelle1!$K:$K;8+((ZEILE(Tabelle1!A1)-1)*10))
T3ber_flex=INDEX(Tabelle1!$B:$B;3+((ZEILE(Tabelle1!B1)-1)*10)):INDEX(Tabelle1!$K:$K;8+((ZEILE(Tabelle1!B1)-1)*10))
S4ber_flex=INDEX(Tabelle1!$B:$B;3+((ZEILE(Tabelle1!A2)-1)*10)):INDEX(Tabelle1!$K:$K;8+((ZEILE(Tabelle1!A2)-1)*10))
T4ber_flex=INDEX(Tabelle1!$B:$B;3+((ZEILE(Tabelle1!B2)-1)*10)):INDEX(Tabelle1!$K:$K;8+((ZEILE(Tabelle1!B2)-1)*10))
Namen verstehen


Tabelle1

 ST
1211
1312
1412

Formeln der Tabelle
ZelleFormel
S12{=WENN(S3="";"";KKLEINSTE(WENN($B$3:$K$8=S3;ZEILE($1:$6)); ZÄHLENWENN($S$3:S$3;S$3)))}
T12{=WENN(T3="";"";KKLEINSTE(WENN($B$3:$K$8=T3;ZEILE($1:$6)); ZÄHLENWENN($S$3:T$3;T$3)))}
S13{=WENN(S3="";"";KKLEINSTE(WENN(INDEX($B$3:$B$8;S12):INDEX($K$3:$K$8;S12)=S3;SPALTE($A:$J)); S14))}
T13{=WENN(T3="";"";KKLEINSTE(WENN(INDEX($B$3:$B$8;T12):INDEX($K$3:$K$8;T12)=T3;SPALTE($A:$J)); T14))}
S14=WENN(S12="";"";1)
T14=WENN(T3="";"";WENN(ODER(T3<>S3;T12<>S12); 1;S14+1))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Anzeige
AW: mit Hilfszeilen
04.12.2012 16:49:46
René
Hallo Christian,
ich muss mich da erst mal durcharbeiten, um das zu verstehen. Aber gehe ich recht in der Annahme, dass die Matrix "S12-X14" eine Art Hilfstabelle ist?
MfG René

AW: mit Hilfszeilen
04.12.2012 17:05:53
Christian
Hallo Rene,
ja, der Bereich S12:BZ14 sind die Hilfszeilen.
MfG Christian

AW: mit Hilfszeilen
06.12.2012 09:52:27
René
Hallo Christian,
nur mal so eine Frage. Würde es das Ganze einfacher machen, wenn die Ausgabetabelle anders aufgebaut wäre? siehe Beispiel
https://www.herber.de/bbs/user/82917.xls

360 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige