Zellbezug aus Zelleninhalt übernehmen

[mgtfahrer]

   
Hallo!

Ich brauche dringend Hilfe bei einer Excel-Aufgabe.
Wie kann ich z.B. in der "Zählenwenn" Funktion den Spalten und Zeilen Bereich dynamisch gestalten?

Ich möchte in Zelle D17 die Formel =ZÄHLENWENN('Übersicht Wachstum'!L2:L71,1) einfügen und diese dann nach unten ziehen, so dass sich jeweils der Buchstabe (Spaltenbezeichnung) um 1 erhöht und die Ziffern (Zeilenbezug) gleich bleibt.

das ganze sollte so funktionieren:

D17:  =ZÄHLENWENN('Übersicht Wachstum'!L2:L71,1)
D18:  =ZÄHLENWENN('Übersicht Wachstum'!M2:M71,1)
D19:  =ZÄHLENWENN('Übersicht Wachstum'!N2:N71,1)
...
...
D32:  =ZÄHLENWENN('Übersicht Wachstum'!AA2:AA71,1)
...
...
usw.

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Ich habe mir bereits in Spalte A eine Hilfsspalte mit den Buchstaben (Spaltenbezeichnung) und z.B. in den Zellen D1 und D2 die Zahlen (Zeilennummern) eingefügt, weiß jedoch nicht wie ich die Buchstaben und Zahlen nach dem Ausdruck =ZÄHLENWENN('Übersicht Wachstum' kombinieren muss, dass ich die Formel nach unten ziehen kann.

Hoffe sehr, dass jemand von euch eine Lösung für mein Problem hat.

Vielen Dank!

Martin

[shift-del]

Moin Martin

Wie so häufig wird die falsche Datenstruktur verwendet (Kreuztabelle).
Nimm lieber eine Datensatzliste. Das macht das Leben einfacher.

Code:

=ZÄHLENWENNS(Tabelle1[Datum];B17;Tabelle1[W];1)

[theTroother]

Oder du tauschst den Bereich:

Code:

L2:L71

mit folgender Formel aus und ziehst sie runter

Code:

=INDEX($2:$71;1;ZEILE(A13))

[Fred11]

Hallo mgtfahrer,

mit der Funktion INDIREKT kann aus einem Zelleninhalt ein Zellbezug erzeugt werden.
Allerdings ist INDIREKT eine volatile Funktion und sollte daher wenn möglich vermieden werden.
Siehe hier:
https://www.tabellenexperte.de/excel-im-...unktionen/

In deinem Fall kannst du stattdessen die Funktion INDEX verwenden, so wie von theTroother vorgeschlagen.
Da brauchst du dann auch keine Hilfsspalte mit den Spaltenbuchstaben.
Allerdings hat sich bei der Formel von theTroother ein Fehler eingeschlichen
Die 1 muss eine 0 sein:
=INDEX($2:$71;0;ZEILE(A13))

Wenn bei der Funktion INDEX der Zeilenindex mit 0 angegeben (oder weggelassen) wird, gibt INDEX die komplette Spalte (also eine Matrix) zurück.
Siehe auch hier:
https://www.herber.de/excelformeln/pages...ieren.html

Beispiel:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K
1		Montag	Dienstag	Mittwoch	Donnerstag	Freitag			von Zeile	2
2	Hans	0	2	2	2	0			bis Zeile	6
3	Uwe	0	0	1	0	1
4	Peter	2	2	0	2	0				mit INDEX	mit INDIREKT
5	Karl	2	1	1	0	0		B	Montag	1	1
6	Suse	1	2	2	2	1		C	Dienstag	1	1
7								D	Mittwoch	2	2
8								E	Donnerstag	0	0
9								F	Freitag	2	2

Zelle	Formel
J5	=ZÄHLENWENN(INDEX($B$2:$F$6;0;ZEILE(A1));1)
K5	=ZÄHLENWENN(INDIREKT(H5&$J$1&":"&H5&$J$2);1)

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Gruß
Fred

[BoskoBiati]

Hi Fred,

Allerdings hat sich bei der Formel von theTroother ein Fehler eingeschlichen
Die 1 muss eine 0 sein:

Das ist so nicht korrekt. Wenn ich mich nur auf eine einzelne Zelle beziehen will, dann ist die 1 korrekt, wenn ich sie als Spillformel nehme, dann kann die 0 die Zahl der Wahl sein.

Wenn bei der Funktion INDEX der Zeilenindex mit 0 angegeben (oder weggelassen) wird, gibt INDEX die komplette Spalte (also eine Matrix) zurück.

Auch das ist so nicht korrekt, denn 
1. funktioniert es nicht, wenn ich eine ganze Spalte angebe und die Formel nicht in Zeile 1 steht.
2. Wenn ich den Zeilenindex weglasse, wird nur der Wert der einzelnen Zelle zurückgegeben, es sei denn, die Zelle, die ich als Indexwert nehme, ist leer.


 Siehe Bild:
   

[RPP63]

Moin!
Da der TE über MS 365 verfügt, werfe ich noch eine Spillformel in den Ring:
=MTRANS(NACHSPALTE(B2:F6;LAMBDA(x;ZÄHLENWENN(x;1))))

	A	B	C	D	E	F	G	H	I
1		Montag	Dienstag	Mittwoch	Donnerstag	Freitag			Spill
2	Hans	0	2	2	2	0		Montag	1
3	Uwe	0	0	1	0	1		Dienstag	1
4	Peter	2	2	0	2	0		Mittwoch	2
5	Karl	2	1	1	0	0		Donnerstag	0
6	Suse	1	2	2	2	1		Freitag	2

Zelle	Formel
I2	=MTRANS(NACHSPALTE(B2:F6;LAMBDA(x;ZÄHLENWENN(x;1))))

Gruß Ralf

[Fred11]

Hallo Edgar,

deine Bemerkungen zu meiner Antwort verstehe ich ehrlich gesagt nicht so ganz.

die Formelkorrektur (0 statt 1) und die Aussagen zur Funktion INDEX in meiner Antwort bezogen sich auf die von mgtfahrer beschriebene Problemstellung bzw. den Lösungsvorschlag von theTroother.
Und in diesem ganz konkreten Anwendungsfall gehört da halt eine 0 hin und keine 1.
(oder bist du da anderer Meinung ?)

Und auch meine zweite Aussage bezog sich auf den (hier vorliegenden) Fall, dass das erste Argument der INDEX-Funktion ein zweidimensionaler Zellbereich ist, der aus mehreren Zeilen und mehreren Spalten besteht.
(Siehe die kleine Beispieltabelle in meinem obigen Beitrag)
Und da verhält es sich genau so, wie ich geschrieben habe.
Wobei das mit dem "weglassen" vielleicht nicht ganz präzise ausgedrückt war.
Mit weglassen meinte ich
=INDEX($B$2:$F$6;;ZEILE(A1)) - so wie in dem Beispiel bei …Excelformeln...
und nicht
=INDEX($B$2:$F$6;ZEILE(A1)) - das gibt nämlich (bei zweidimensionaler Matrix) einen #Bezug-Fehler.

https://support.microsoft.com/de-de/offi...6b061328bd

Wenn sich Matrix über mehrere Zeilen und Spalten erstreckt und nur row_num oder column_num verwendet wird, gibt INDEX eine Matrix der gesamten Zeile oder Spalte von Matrix zurück

Die Beispiele in deinem Bild finde ich (bis auf das erste in E4) nicht so sehr sinnvoll.
Ich finde es etwas befremdlich bzw. nicht sinnvoll, bei einer Matrix, die nur aus einer einzigen Spalte besteht, sowohl den Zeilen- als auch den Spaltenindex anzugeben....

Gruß
Fred

(Ich möchte hier aber jetzt ungern eine Grundsatzdiskussion zu INDEX entfachen....)

[BoskoBiati]

Hi Fred,

Und in diesem ganz konkreten Anwendungsfall gehört da halt eine 0 hin und keine 1.
(oder bist du da anderer Meinung ?)

Du hast recht, 1 ist falsch. Entweder 0 oder weglassen. Ich hatte die Indexformel an sich betrachtet, ohne den Zusammenhang mit Zählenwenn, mein Fehler.

Im Übrigen müsste die korrekte Formel so lauten:

Code:

=ZÄHLENWENN(INDEX('Übersicht Wachstum'!L2:AA71,,ZEILE(A1)),1)
=ZÄHLENWENN(INDEX('Übersicht Wachstum'!L2:AA71,0,ZEILE(A1)),1)