Office: Formelerweiterung - Jeweils 6 Zeilen überspringen

Seltsam, ich das jetzt war auch ändern. Das Arbeitsblatt "Blanco" bleibt aber leer in den entsprechenden Zellen.
Also wenn ich beispielsweise Menü 4 in der selben Zeile in test2 ändere. Verschwindet bei Blanco beides.

Seltsam...

Könnt ihr den Fehler reproduzieren?

 

selbst ein =test in die 1.Woche(B2) eingetragen erzeugt bei mir kein Name

 

Nö, kann ich nicht

 

Hatte übersehen das die Zellen als TEXT formatiert sind, dann kann man natürlich eintragen was man will.
Bei Standard formatierten Zellen passiert das bei =test

 

Welche Excelversion benutzt du ? Ich muss los kann erst später nochmal schauen.

 

Hallo,

ich habe dir wie im April im Arbeitsblatt "Blanco" in den Spalten K und L (Sp1 und Sp2) je Menüzeile eine Array-Funktion eingefügt:
z.B. in K9:L9: {= AGGREGAT(15;6;SPALTE($C:$G)/(INDEX(Jahresüberblick!$C:$G;$J9;0)<>"");{1.2})}
Falls du Excel2021 oder Excel365 hast, ginge das auch ohne äußere geschweifte Klammern.
Die wahrscheinlich überflüssigen Spalten H und I in diesem Arbeitsblatt wirst du ja selber entfernen können.

 

Super, vielen vielen Dank :-). Bei der letzten Tabelle hattest du eine Matrix-Formel mit "Wenn" erstellt und hier ein Aggregat.
Hast du Zeit dazu, mir das genauer zu erklären? Dann komme ich beim nächsten Mal etwas besser zurecht.

 

Hallo,

es gibt in der Programmiererei und auch bei Excel-Formeln meist nicht den einen Weg, sondern häufig mehrere, die zum Ziel führen.
Ich wollte diesmal zuerst nur die bisherige Formel anpassen. Da aber diesmal 1 Zelle mehr als Leerzelle zu berücksichtigen war, wurde diese Formel um einiges umfangreicher - und dies bei jeder der enthaltenen Zeilen. So kam ich auf die Idee, die AGGREGAT-Funktion einzusetzen, die es seit Excel2010 gibt.

Ziel meiner beiden Formeltypen (in den Spalten Sp1, Sp2,(Sp3)) war/ist es, die Spaltennummer zu ermitteln, in der im Arbeitsblatt "Jahresüberblick" ein Menütext enthalten ist. Zusammen mit der Zeilennummer (Spalte J) kann man dann mittels INDEX-Funktion den Zellinhalt aus einem Zellbereich ermitteln: INDEX(Zellbereich, ZeileNr, SpalteNr).

Die heutige AGGREGAT-Formel setzt sich aus mehreren Teilen zusammen:
1) SPALTE($C:$G) liefert ein Array mit den Werten {3.4.5.6.7}, wobei jede dieser Zahlen eine Spaltennummer im Arbeitsblatt "Jahresüberblick" sein soll.

2) INDEX(Jahresüberblick!$C:$G;$J9;0)<>"": In $J9 ist die Zeilennummer derjenigen Zeile aus Jahresüberblick enthalten, die gerade untersucht werden soll. Dabei gibt die Formel INDEX(Jahresüberblick!$C:$G;$J9;0) den Zellbereich zurück, der sich in den Spalten C bis G in der Zeile J9 befindet, wobei die 0 am Ende bedeutet, dass alle Zellspalten in diesem Bereich ermittelt werden sollen. Wenn in J9 eine 2 enthalten ist, werden alle Zellen des Bereiches Jahresüberblick!C2:G2 ermittelt. Und dann wird am Schluss noch jede dieser Zellen überprüft, ob sie ungleich "" ist, womit der anfangs angeführte Formelteil als Wert das Array {WAHR.FALSCH.FALSCH.WAHR.FALSCH} ergibt. Die beiden WAHR-Werte deuten damit an, dass in der 1.Zelle und in der 3.Zelle des Bereichs Jahresüberblick!C2:G2 ein Textinhalt vorhanden ist, während die 3 FALSCH-Werte 3 leere Zellen signalisieren.

3) Dividiert man Ausdruck 1) durch Ausdruck 2): SPALTE($C:$G)/(INDEX(Jahresüberblick!$C:$G;$J9;0)<>"") muss man wissen, dass dabei die WAHR-Werte wie eine 1 behandelt werden, während die FALSCH-Werte wie eine 0. Mit dem Ausdruck werden somit folgende 5 Divisionen ausgeführt: {3.4.5.6.7}/{1.0.0.1.0} was als Resultat {3.#DIV/0!.#DIV/0!.6.#DIV/0!} ergibt. Wie du siehst, bleiben die beiden Spaltennr. mit Inhalt (3 bzw. 6) erhalten, während die 3 Leerspalten einen #DIV/0! (=Division durch 0 Fehler) ergeben.

4) Dieser Formelteil von 3) wird schlussendlich eingebettet in die AGGREGAT-Funktion:
=AGGREGAT(15;6;SPALTE($C:$G)/(INDEX(Jahresüberblick!$C:$G;$J9;0)<>"");{1.2})
Dabei bedeutet der 2.Parameter (=6): Ignoriere im Bereich aus 3) die Werte mit Fehlerwert, dh. die AGGREGAT-Funktion tut so, als ob das Resultat aus 3) lauten würde {3.6}
Der 1.Parameter (=15) bedeutet: Sortiere die Werte der Größe nach, beginnend beim Kleinsten, also {3.6}
Der 4.Parameter (={1.2}) bedeutet: Ermittle zuerst den (erst)kleinsten Wert und danach den zweitkleinsten Wert aus dem Array {3.6}.
D.h. aber, dass die AGGREGAT-Funktion 2 Werte zurückgibt, somit 2 Resultatzellen benötigt, wobei in der ersten Zelle eine 3 und in der 2.Zelle daneben eine 6 erscheint, wenn man bei dem Beispiel von vorhin bleibt.

5) Die Formel liefert also eine Array-Formel. Bis Excel2019 muss man für solche Formeln einen festen Bereich festlegen, in dem das Formelresultat enthalten sein muss. Und man muss bei der Formeleingabe darauf achten, dass man sie nicht bloß mit der EINGABE-Taste abschließt, sondern mit der Tastenkombination Strg+UMSCHALT+EINGABE. Daraufhin erscheint die Formel in geschweiften Klammern eingeschlossen.
Ab Excel2021 bzw. Excel365 wird der Resultatbereich der Formel von Excel dynamisch ermittelt. Somit braucht man ab diesen Versionen keine Bereichfestlegung und keine spezielle Formeleingabe. Man muss nur genügend freien Zellbereich zur Verfügung stellen.

Ich hoffe, ich konnte die Details der Formel(n) hinreichend genau erläutern und genügend Stoff zum Selbsterarbeiten liefern.

 

Mega lieb von euch allen und vielen vielen Dank für die Erklärung Exl121150.
Ich bin euch sehr dankbar, ihr wart mir eine große Hilfe :-)