kopieren einer Matrixformel

Forumthread: kopieren einer Matrixformel

13.06.2007 17:40:00

Drossart

Liebe Excelprofis,
die nachfolgende Matrixformel möchte ich so gestalten, dass beim Kopieren aus Zelle B71die Zelle B72 usw. wird:
=SUMME(ZÄHLENWENN(INDIREKT("ka"&ZEILE($1:$32)&"!B71");"R"))
zur Erläuterung: Ich suche in 32 Tabellenblättern (die haben die Namen ka1 bis ka32) jeweils in Zelle B71, ob dort der Buchstabe R steht.
Vielen Dank für Eure Hilfe und alles Liebe und Gute
Wilhelm Drossart

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: kopieren einer Matrixformel

13.06.2007 18:24:00

WF
noch ein paar Möglichkeiten

13.06.2007 21:18:00

FP
Du bist im falschen Beitrag

13.06.2007 23:06:00

WF
Upps,

14.06.2007 02:21:20

FP
AW: Du bist im falschen Beitrag

14.06.2007 02:26:00

Drossart
=ADRESSE(Zeilenzahl;Spaltenzahl)

14.06.2007 09:56:00

WF

AW: kopieren einer Matrixformel

13.06.2007 18:24:00

Hi Wilhelm,
{=SUMME(ZÄHLENWENN(INDIREKT("ka"&ZEILE($1:$32)&"!"&ADRESSE(ZEILE(A71);2));"R"))}
Salut WF

noch ein paar Möglichkeiten

13.06.2007 21:18:00

Hallo Wilhelm,
C15 bzw. C16 kann einfach nach rechts kopiert werden

Matrix

	B	C	D	E	F	G	H	I	J
5		1	2	3	fp	5	6	7	8
6	1
7	2
8	3
9	FP
10	5
11	6
12	7
13	8
14
15	das geht so	wahr	wahr	wahr	wahr	wahr	wahr	wahr	wahr
16	oder so	WAHR	WAHR	WAHR	WAHR	WAHR	WAHR	WAHR	WAHR
17	alles ident	FALSCH
18	nicht ident	fp	F5

Formeln der Tabelle

C15 : =WENN(C5=INDEX($B6:$B13;SPALTE(A22)); "wahr";"falsch")
C16 : =C5=INDEX($B6:$B13;SPALTE(A22))
C17 : {=UND(IDENTISCH(C5:J5;MTRANS(B6:B13)))}
C18 : {=WENN(C17;"";VERWEIS(2;1/NICHT(IDENTISCH(C5:J5;MTRANS(B6:B13))); C5:J5))}
D18 : =WENN(C18="";"";ADRESSE(5;VERGLEICH(C18;5:5;0); 4))
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Bedingte Formatierungen der Tabelle

Zelle	Nr.: / Bedingung	Format
C5	1. / Formel ist =NICHT(IDENTISCH(C5;INDEX($B6:$B13;SPALTE(A1))))	Abc
D5	1. / Formel ist =NICHT(IDENTISCH(D5;INDEX($B6:$B13;SPALTE(B1))))	Abc
E5	1. / Formel ist =NICHT(IDENTISCH(E5;INDEX($B6:$B13;SPALTE(C1))))	Abc
F5	1. / Formel ist =NICHT(IDENTISCH(F5;INDEX($B6:$B13;SPALTE(D1))))	Abc
G5	1. / Formel ist =NICHT(IDENTISCH(G5;INDEX($B6:$B13;SPALTE(E1))))	Abc
H5	1. / Formel ist =NICHT(IDENTISCH(H5;INDEX($B6:$B13;SPALTE(F1))))	Abc
I5	1. / Formel ist =NICHT(IDENTISCH(I5;INDEX($B6:$B13;SPALTE(G1))))	Abc
J5	1. / Formel ist =NICHT(IDENTISCH(J5;INDEX($B6:$B13;SPALTE(H1))))	Abc
B6	1. / Formel ist =NICHT(IDENTISCH(B6;INDEX(C$5:J$5;;ZEILE(A1))))	Abc
B7	1. / Formel ist =NICHT(IDENTISCH(B7;INDEX(C$5:J$5;;ZEILE(A2))))	Abc
B8	1. / Formel ist =NICHT(IDENTISCH(B8;INDEX(C$5:J$5;;ZEILE(A3))))	Abc
B9	1. / Formel ist =NICHT(IDENTISCH(B9;INDEX(C$5:J$5;;ZEILE(A4))))	Abc
B10	1. / Formel ist =NICHT(IDENTISCH(B10;INDEX(C$5:J$5;;ZEILE(A5))))	Abc
B11	1. / Formel ist =NICHT(IDENTISCH(B11;INDEX(C$5:J$5;;ZEILE(A6))))	Abc
B12	1. / Formel ist =NICHT(IDENTISCH(B12;INDEX(C$5:J$5;;ZEILE(A7))))	Abc
B13	1. / Formel ist =NICHT(IDENTISCH(B13;INDEX(C$5:J$5;;ZEILE(A8))))	Abc

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
if C17 wird nur überprüft, ob alle Zellen identisch sind
inf C18 steht der nicht idente Eintrag, in D18 wo sich dieser befindet
Ausserdem siehst Du, wie mit einer bedingten Formatierung optisch die nicht identen Einträge hervorgehoben werden können.
Servus aus dem Salzkammergut
Franz

Du bist im falschen Beitrag

13.06.2007 23:06:00

Upps,

14.06.2007 02:21:20

Hallo Walter,
Danke für den Hinweis, mir kam es doch gleich so komisch vor, dass die Anfrage von Wilhelm und nicht von Esther gekommen war ;-)
Servus aus dem Salzkammergut
Franz

AW: Du bist im falschen Beitrag

14.06.2007 02:26:00

Drossart

Hallo W.F.
vielen lieben Dank für die Hilfe. Hat super geklappt. Hast mir sehr geholfen. Falls es Deine/Ihre Zeit erlaubt, wüsste ich gerne, warum hinter der A 71 noch eine 2 steht.
Herzliche Grüße und eine gute Nacht
W. Drossart

=ADRESSE(Zeilenzahl;Spaltenzahl)

14.06.2007 09:56:00

Hi Wilhelm,
=ADRESSE(71;2) ergibt $B$71
=ADRESSE(ZEILE(A71);2) ist dasselbe nur, daß für 71 der dynamische Platzhalter ZEILE(A71) eingesetzt wird, der beim Runterkopieren 72 - 73 ... usw. ergibt.
Salut WF

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Formel erstellen: Beginne mit der Matrixformel, die Du verwenden möchtest:
```
=SUMME(ZÄHLENWENN(INDIREKT("ka"&ZEILE($1:$32)&"!B71");"R"))
```
Formel anpassen: Um die Formel so zu gestalten, dass sie beim Kopieren die Zeile anpasst, modifiziere sie wie folgt:
```
{=SUMME(ZÄHLENWENN(INDIREKT("ka"&ZEILE($1:$32)&"!"&ADRESSE(ZEILE(A71);2));"R"))}
```
Diese Formel verwendet die ADRESSE-Funktion, um den Bezug dynamisch zu halten.
Matrixformel eingeben: Um die Formel als Matrixformel einzugeben, musst Du STRG + SHIFT + RETURN drücken, anstatt nur die Enter-Taste.
Kopieren: Nachdem Du die Formel eingegeben hast, kannst Du sie nach unten kopieren (z.B. von B71 nach B72). Excel wird automatisch die Zeilenreferenzen anpassen.

Häufige Fehler und Lösungen

Fehler: #BEZUG!: Dieser Fehler tritt auf, wenn die Zielzelle nicht gefunden werden kann. Überprüfe die Blattnamen und stelle sicher, dass sie korrekt sind (z.B. ka1, ka2 usw.).
Fehler: Falsches Ergebnis: Wenn die Ergebnisse nicht wie erwartet sind, überprüfe die Bedingungen in der ZÄHLENWENN-Funktion. Stelle sicher, dass die Suchkriterien (in diesem Fall "R") korrekt definiert sind.

Alternative Methoden

Verwendung von INDEX: Du kannst auch INDEX verwenden, um die Zellen dynamisch zu referenzieren. Zum Beispiel:
```
=SUMME(ZÄHLENWENN(INDEX(ka1:ka32!B:B; ZEILE(A71));"R"))
```
Diese Methode ist nützlich, wenn Du eine bestimmte Spalte über mehrere Tabellenblätter hinweg analysieren möchtest.
VBA: Für komplexere Aufgaben kannst Du VBA verwenden, um Matrixformeln zu erstellen und diese automatisch auf mehrere Zellen anzuwenden.

Praktische Beispiele

Beispiel für einfache Matrixformel: Angenommen, Du hast 32 Tabellen, die ka1 bis ka32 heißen. Du möchtest wissen, wie oft der Buchstabe "R" in den Zellen B71 bis B100 vorkommt:
```
=SUMME(ZÄHLENWENN(INDIREKT("ka"&ZEILE($1:$32)&"!B71:B100");"R"))
```
Beispiel für bedingte Formatierung: Du kannst auch eine bedingte Formatierung einrichten, um Zellen hervorzuheben, die "R" enthalten. Gehe zu Start > Bedingte Formatierung > Neue Regel und wähle "Formel zur Ermittlung der zu formatierenden Zellen verwenden".

Tipps für Profis

Formelüberprüfung: Verwende die Formelüberprüfung, um sicherzustellen, dass Deine Matrixformel korrekt funktioniert. Dies kann helfen, Fehler frühzeitig zu erkennen.
Verwende Namensbereiche: Anstatt feste Zellbezüge zu verwenden, kannst Du auch Namensbereiche definieren, um die Formeln lesbarer und einfacher zu warten.
Datenvalidierung: Setze Datenvalidierung ein, um sicherzustellen, dass nur die gewünschten Werte (z.B. "R") in die Zellen eingegeben werden.

FAQ: Häufige Fragen

1. Warum muss ich STRG + SHIFT + RETURN verwenden?
Matrixformeln erfordern diese Eingabemethode, um Excel zu signalisieren, dass die Formel als Matrix behandelt werden soll.

2. Was ist der Unterschied zwischen ADRESSE und INDIREKT?
ADRESSE gibt die Adresse einer Zelle als Text zurück, während INDIREKT eine Textadresse in einen Zellbezug umwandelt. Dies ist besonders nützlich, wenn Du dynamische Bezüge benötigst.

Beliebteste Forumthreads (12 Monate)