Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 14:48:50
"Eingabefeld" zwischen zwei Zeilen geht nicht weg.
24.04.2024 14:40:10
Zeilenumbrüche in Textbox zählen
24.04.2024 14:06:20
Gesamte Column löschen, wenn Column-Bereich leer
24.04.2024 13:44:05
Bezeichnung Tabellenblatt aus Zelle für Formel auslesen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1864to1868
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1864to1868
1864to1868
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Indirekter Bezug

Indirekter Bezug
15.01.2022 14:33:10
Jürgen
Hallo,
die folgende Formel soll den Mittelwert einer Zelle über der letzten 5 Jahre liefern und funktioniert wie gewünscht
(Die Jahreszahlen sind auch die Namen der Tabellenblätter): 

=MITTELWERT('2017:2021'!ZS14)
Nun würde ich gerne eine universelle Formel haben wollen, die das Enddatum aus der Zelle Z1S1 holt. Die folgende Formel: 


=("'"&Z1S1-4&":"&Z1S1&"'!ZS14")
liefert zwar: 


'2017:2021'!ZS14
leider funktioniert das nun aber nicht indirekt. Also: 


=MITTELWERT(INDIREKT("'"&Z1S1-4&":"&Z1S1&"'!ZS14";FALSCH))
liefert leider: #BEZUG!
Kann mir jemand einen Tipp geben wo hier der Denkfehler ist?
Vielen Dank

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Indirekter Bezug
15.01.2022 14:41:17
SF
Hola,
ungetestet:

=MITTELWERT(INDIREKT("'"&Z1S1-4&":"&Z1S1&"'!ZS14"))
Wo zauberst du denn auf einmal das FALSCH her und warum?
Gruß,
steve1da
Viell hat er ja doch Xl auf A1-Adressen ...
15.01.2022 15:53:19
Luc:-?
…eingestellt, Steve,
oder es soll auch bei diesen fktionieren. Dann ist FALSCH erforderlich. Allerdings bezweifle ich, dass die Fml so auch international fktioniert, denn im Ggsatz zum Verweis auf die BlattNamen ist der DatenZellVerweis als Text angegeben. Der wird natürlich nicht übersetzt und ist somit in anderem lokalen Xl-Varianten unverständlich. Hierbei sollte mit ADRESSE gearbeitet wdn, um die internationale Übertragbarkeit zu gewährleisten.
Gruß, Luc :-?
Anzeige
AW: Viell hat er ja doch Xl auf A1-Adressen ...
15.01.2022 16:17:21
Jürgen
Vielen Dank erstmal, allerdings ist es jetzt erstmal egal was ich mit diesem Parameter mache, ob weglassen, 0 oder 1, das Ergebnis bleibt: #Bezug! und das würde ich jetzt erstmal gerne verstehen wollen. (Es ist eine deutsche Installation und die Tabelle ist in den Optionen unter "arbeiten mit Formeln" auf Z1S1 eingestellt)
Jürgen
AW: Indirekter Bezug
15.01.2022 14:56:20
Jürgen
Hallo steve1da,
deser Wahrheitswert sollte von Indirekt bei der Z1S1 Schreibweise gefordert sein, oder? Jürgen
AW: Indirekter Bezug
15.01.2022 16:55:48
{Boris}
Hi Jürgen,
=MITTELWERT(N(INDIREKT("'"&Z1S1-5+ZEILE(Z1:Z5)&"'!ZS14";)))
In Deiner Excelversion noch als Arrayformel eingeben: Formelabschluss nicht mit Enter allein, sondern mit gleichzeitig Strg+Shift+Enter.
VG, Boris
Anzeige
Und falls Nullwerte...
15.01.2022 17:13:50
{Boris}
Hi,
...und Leerzellen nicht berücksichtigt werden sollen:
=MITTELWERT(WENN(N(INDIREKT("'"&Z1S1-5+ZEILE(Z1:Z5)&"'!ZS14";))0;N(INDIREKT("'"&Z1S1-5+ZEILE(Z1:Z5) &"'!ZS14";))))
Ebenfalls als ARRAYformel.
VG, Boris
AW: Und falls Nullwerte...
15.01.2022 17:25:42
Jürgen
Vielen herzlichen Dank Boris, funktioniert natürlich super. Zum Verstehen werde ich mich wohl noch ein bisschen mit Arrayformeln beschäftigen müssen.
Jürgen
Die Funktion N() ....
15.01.2022 17:39:11
{Boris}
Hi Jürgen,
...ist in der Formel übrigens nicht erforderlich - zumindest nicht in der Excelversion 365. Ich meine, dass das in früheren Versionen anders war - aber ich kann das gerade nicht testen.
Demnach reicht auch (zumindest in xl365):
=MITTELWERT(INDIREKT("'"&Z1S1-5+ZEILE(Z1:Z5)&"'!ZS14";))
Arrayformel.
Musst Du mal bei Dir testen, ob das auch ohne N() läuft.
VG, Boris
Anzeige
AW: Die Funktion N() ....
16.01.2022 10:38:02
Jürgen
Hi Boris,
nur kurz zur Bestätigung, dass die Funktion N() in Excel 2013 wohl erforderlich ist. Wenn ich die weg lasse, wird nur der erste Wert als Mittelwert angezeigt, genauso als wenn ich die {} vergessen hätte. Noch mal vielen Dank für Deine Hilfe und dass du mich durch Deine Lösung angespornt hast mehr mit Matrizen zu arbeiten. Jürgen
Die Xl-Funktion N 'normalisiert' hierbei ...
16.01.2022 11:41:57
Luc:-?
…das Ergebnis von INDIREKT, Jürgen (& Boris),
das sonst so nicht von (diesen) Xl(-Versionen) darstellbar wäre. Das mag an einer unüblichen Ausrichtung der ErgebnisMatrix (ggf Tensor) liegen, mit der manche Xl-Fktt aber zurechtkommen und sie in die übliche transformieren. Es könnte natürlich sein, dass das in den neuesten Xl-Versionen bereits vom FmlText-Interpreter berücksichtigt wird, denn der musste ja auch an das sog Spilling angepasst wdn.
Gruß, Luc :-?
Anzeige
So ist es dann wohl...
16.01.2022 12:40:33
{Boris}
Hi Luc,
Es könnte natürlich sein, dass das in den neuesten Xl-Versionen bereits vom FmlText-Interpreter berücksichtigt wird
Denn in xl365 braucht man die Funktion N() nicht, um das INDIREKT-Array "zum Leben zu erwecken".
Dass es vorher so war, hatte ich also korrekt in Erinnerung.
VG, Boris
AW: Und falls Nullwerte...
16.01.2022 13:40:05
Kulo
Hallo Boris,
ich habe mal versucht, das aufzudröseln.
Ich komme aber damit nicht zurecht.
Wäre es möglich, diese Formel für mich in "A1"-Schreibweise zu formulieren?
Würde mich freuen.
Viele Grüße
Kulo
Du musst doch nur...
16.01.2022 13:52:53
{Boris}
Hi,
...in den Exceloptionen - Formeln - die Z1S1-Schreibweise eben aktivieren, die Formel in eine Zelle (nicht A1!) einsetzen und anschließend die Z1S1-Schreibweise wieder deaktivieren. Damit kommst Du schon mal so weit:
=MITTELWERT(WENN(N(INDIREKT("'"&$A$1-5+ZEILE($1:$5)&"'!ZS14";))0;N(INDIREKT("'"&$A$1-5+ZEILE($1:$5) &"'!ZS14";))))
Den Bezugsstring ZS14 (bedeutet: selbe Zeile wie die Formel und (fix) Spalte 14 = N) kann man natürlich auch noch auf die A1-Schreibweise anpassen:
=MITTELWERT(WENN(N(INDIREKT("'"&$A$1-5+ZEILE($1:$5)&"'!N"&ZEILE()))0; N(INDIREKT("'"&$A$1-5+ZEILE($1:$5) &"'!N"&ZEILE()))))
Der letzte Parameter bei INDIREKT entfällt dann - bei der von mir gewählten Formulierung ist das nicht direkt offensichtlich:
Vorher: INDIREKT("'"&$A$1-5+ZEILE($1:$5) &"'!ZS14";)
Nachher: INDIREKT("'"&$A$1-5+ZEILE($1:$5) &"'!N"&ZEILE())
Das letzte Semikolon muss weg, damit INDIREKT weiß, dass es nun die A1-Schreibweise ist.
VG, Boris
Anzeige
AW: Du musst doch nur...
16.01.2022 16:43:04
Kulo
Hallo Boris,
Vielen Dank für den Anstupser.
Beim Einfügen der Formel hatte Excel was von ZS11 erzählt, deswegen meine Nachfrage, es zu übersetzen.
Ich bin dann noch bei "$A$1-5+ZEILE($1:$5)" hängen geblieben - aber jetzt ist wieder alles okay. ;-)
Vielen Dank und noch einen schönen Abend.
Viele Grüße
Kulo
Aber finde ich gut...
16.01.2022 18:45:57
{Boris}
Hi,
...dass Du Dich mit Forumsfragen-/Beiträgen und den zugehörigen Lösungen beschäftigst, um offensichtlich dazu zu lernen :-)
VG, Boris
Meinen Hinweis zu ADRESSE ...
17.01.2022 01:34:18
Luc:-?
…hat er aber nicht aufgegriffen, Boris!
Morhn, Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige