Sind variable Pulldown Menüs möglich?

[balu]

Also weiter gehts. Jetzt wird es aber ein klein wenig schwieriger.

Nur zur Erinnerung, noch mal die Formeln aus meinem letzten Text.
In BO14

Code: Alles auswählen

=WENN(ISTFEHLER(VERGLEICH(1;AI28:AI44;0));"x";VERGLEICH(1;AI28:AI44;0))
 

Und in BI14

Code: Alles auswählen

=WENN(BO14="x";"-";INDEX(C28:C44;BO14))
 

Beide Formeln haben so wie sie da stehen, einen - nein - sogar 2 Nachteile.

1.
Würde man sie nach unten "ziehen" beziehungsweise nach unten kopieren, dann ändern sich alle Zellbezüge, seis für eine einzelne oder für einen Zellbereich.

2.
Durch Punkt 1 würden da auch nicht nachvollziehbare Ergebnisse dabei rauskommen.

Was wir brauchen ist eine möglichkeit das sich die Zellbereiche automatisch so anpassen, das nach dem ersten Treffer dieser nicht mehr mitgezählt wird, und sich gleichzeitig der Zellbereich "Dynamisch anpasst".

Würde man anstatt

Code: Alles auswählen

VERGLEICH(1;AI28:AI44;0)

diesen Teil so schreiben

Code: Alles auswählen

VERGLEICH(1;AI$28:AI$44;0)

dann müsste klar sein, das beim runterkopieren der Formel immer nur der erste Treffer ausgegeben wird.
Was wir aber bräuchten wäre etwas in der Art, das in der nächsten Zelle unterhalb dieser Formel der Zellbereich sich automatisch ändert, was theoretisch so aussehen würde.

Code: Alles auswählen

VERGLEICH(1;AI32:AI44;0)

Aber wie könnte man dies bewerkstelligen?

Nun, da kommt jetzt die INDIREKT zum Einsatz.
In BO14 steht ja als Beispiel die 3.
Und diese 3 ist die Basis für die neue indirekte-Zelladressierung.
Mit INDIREKT kann man nicht nur eine Zelle, sondern auch einen Zellbereich anders adressieren.
Und das bauen wir jetzt mal wegen der Verständlichkeit stückchenweise auf.

Nehmen wir mal etwas ganz leichtes, für einen Zellbereich.
In irgendeinem Tabellenblat an irgendeiner Stelle einfach mal das hier eingeben.

Code: Alles auswählen

="AI"&3&":AI44"

Es müsste dann folgendes zu lesen sein: AI3:AI44
Das ist jetzt eine stark vereinfachte Textausgabe, die absolut reine weg gar nichts macht.
Setzen wir jetzt aber INDIREKT davor, also so

Code: Alles auswählen

=INDIREKT("AI"&3&":AI44")

dann wird irgendetwas ausgegeben, je nach dem wo man diese Formel eingegeben hat. In einem absolut leeren Tabellenblatt kommt dann natürlich 0 dabei raus.

Wir können aber auch innerhalb von INDIREKT eine Berechnung durchführen, z.B. so

Code: Alles auswählen

=INDIREKT("AI"&SUMME(B1:B2)&":AI44")

Und wenn z.B. in B1 die 5, und in B2 die 11 steht, dann würde ja 5 + 11 gleich 16 ergeben. Lösen wir mal diese Formel in eine Textausgabe auf

Code: Alles auswählen

="AI"&SUMME(B1:B2)&":AI44"

so müsste dann folgender Text zu lesen sein: AI16:AI44 (vorausgesetzt in B1 und B2 stehen die eben gennanten Zahlen).
Dieser Text, welcher ja eigentlich ein Zellbereich darstellt, ist für uns Menschen natürlich zu lesen. Aber Calc kann damit gar nichts anfangen, und deshalb wird dieser "Text" an INDIREKT übergeben.

Eins muss man sich aber noch merken.
Bei INDIREKT("AI"...":AI44") wird alles was in doppelten Anführungszeichen "" steht, beim kopieren egal in welcher Richtung nicht von Calc geändert/angepasst. Und so wird aus ":AI44" NIE UND NIMMER ":AI66" wenn man die Formel wo anders hinkopiert (nur mal so als Beispiel).
Jedoch der Teil &SUMME(B1:B2)& der passt sich automatisch an, da es sich um eine Calc-Funktion handelt und sie nicht zwischenn Anführungszeichen steht (würde dort auch keinen Sinn ergeben).


Und eigentlich ist damit schon der Grundstein für einen "Dynamischen Zellbereich" gelegt.

Da wir ja in BO14 ja wie schon erwähnt eine Zahl stehen haben, die 3, beziehen wir uns jetzt auch genau auf diese Zelle. Und deshalb geben wir jetzt im Blatt *Crew* in der Zelle BO16 diese Formel ein.

Code: Alles auswählen

=VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0)

Etwas anders als wie bisher beschrieben sieht sie ja schon aus, aber das ist eigentlich schnell erklärt.

Code: Alles auswählen

&SUMME(BO$14:BO14)

Beim runterkopieren passt sich der Zellbezug automatisch an, so dass in der nächsten Zelle in BO18 das dann so aussieht.

Code: Alles auswählen

&SUMME(BO$14:BO16)

Aber noch sind wir ja in BO16.

Und dort steht noch etwas in der Formel was vielleicht irretiert, und zwar: +28
Nun, wenn BO14 die 3 ausgibt, dann kann man wohl damit via INDIREKT einen Zellbezug aufbauen, der aber einen falschen Bereich ergibt. Denn der würde nämlich dann wie folgt lauten: AI3:AI44

Okay, es ist wohl die gleiche Spalte, was schon mal sehr wichtig ist, aber das Ergebnis wäre Falsch.
Angenommen BO14 würde nicht die 3, sondern die 2 ausgeben, dann würde die Suchspalte von AI2:AI44 gehen und VERGLEICH würde auf der Suche nach der 1 auch einen Treffer finden. ABER dieser Teffer wäre die Zelle AI2, weil dort in AI2 auch eine 1 steht. Und genau aus diesem Grund müssen wir einen Korrekturfaktor einbauen, so das der für uns wichtige Zellbereich wieder da anfängt wo er anfangen soll. Und dieser Korrekturfaktor ist die +28.

Und wenn man jetzt wieder sagt, das in BO14 die 3 steht, so ergibt BO14 + Korrekturfaktor (3 + 28) gleich 31. Und schon ergibt

Code: Alles auswählen

="AI"&SUMME(BO$14:BO14)+28&":AI44"

gleich AI31:AI44.
Und wenn wir diese Formel

Code: Alles auswählen

=VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0)

"kürzen", käme dann dabei folgendes heraus.

Code: Alles auswählen

=VERGLEICH(1;INDIREKT("AI31:AI44");0)

Und die Formel müsste dann eigentlich in BO16 eine 1 ausgeben.

Jetzt müsste eigentlich verständlicher sein wie der "Dynamische Zellbereich" sich aufbaut, hoffe ich zumindest

So, und nun müssen wir wieder einen Grundstein für die Fehlerüberprüfung einbauen, das dann so aussieht.

Code: Alles auswählen

=WENN(ISTFEHLER(VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0));"x";VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0))

Okay, auf zum nächsten Punkt, beziehungsweise Zelle.
Das war ja jetzt nur die Zellkoordinate. Jetzt wollen wir aber auch einen passenden Namen zu dieser Koordinate haben.
Den Namen wollen wir uns ja jetzt in BI16 ausgeben lassen, was dann wie folgt aussieht.

Code: Alles auswählen

=WENN(BO16="x";"-";INDEX(INDIREKT("C"&SUMME(BO$14:BO14)+28&":C44");BO16))

Dazu muss ich ja wohl nix mehr sagen, da alles schon beschrieben wurde. Einzig der Hinweis, wenn man für VERGLEICH via INDIREKT einen neuen Zellbereich aufbauen kann, so kann man das auch für INDEX machen.

Die beiden Formeln in BI16 und BO16 können nun nach BI18 und BO18 kopiert werden.

Ich gebs ja zu, das es fürs erste mal sich alles sehr schwer anhört, aber wenn ich daran denke was sich alles für Leute in einem anderen Forum an so einer ähnlichen Problematik beteiligt hatten, denn der Grund war ich der wissen wollte wie man das lösen kann, und das diese Problematik auch nicht an einem Tag erledigt war, so ist das jetzt hier ein schöner kleiner Crash-Kurs geworden.


Und nun lege ich mich ganz gemütlich zurück, und warte mal auf Eure Reaktionen.



Gruß
balu

[mikele]

Hallo balu,

es hat ein paar Tage gedauert bis ich es verdaut habe - nein, Spaß beiseite, ich hatte zu viel zu tun ...
Dynamische Zellbereiche funktionieren gut und sind ein gutes Mittel.
Gestatte mir dennoch ein paar Anmerkungen:
1) Persönklich arbeite ich lieber mit Verschiebung() als mit Indirekt(). Insofern sähe deine Formel bei mir so aus:

Code: Alles auswählen

=WENN(ISTFEHLER(VERGLEICH(1;Verschiebung($AI$28;SUMME(BO$14:BO14);0;17;1);0));"x";VERGLEICH(1;Verschiebung($AI$28;SUMME(BO$14:BO14);0;17;1);0))

2) Die Vergleich()-Funktion kann leider nur mit eindimensionalen Bereichen arbeiten (Zeilen oder Spalten). Insofern versagt die Formel, wenn der Suchwert nicht in der 1. Spalte steht.
3) Beim Kopieren der Formeln in einen anderen Block muss der Suchwert angepasst werden.
4) Meine bekloppte Formel (SPALTE()-61)/20+ABRUNDEN((ZEILE()-14)/26;0)+1 berechnet die Blocknummer, mus allerdings bei eine Layoutänderung wieder angepasst werden.
"Meine" Grundformel

Code: Alles auswählen

=VERSCHIEBUNG($C$1;KKLEINSTE(WENN($AI$28:$AM$44=1;ZEILE($AI$28:$AM$44)-1;100);1);0) & WENN(AKTUELL()="";"-";"")

lässt sich auch noch vereinfachen

Code: Alles auswählen

=VERSCHIEBUNG($C$1;KGRÖSSTE(($AI$28:$AM$44=1)*(ZEILE($AI$28:$AM$44)-1);1);0) & WENN(AKTUELL()="";"-";"")

Ob man den Suchwert und die Nr. des Auftretens manuell einträgt oder per Formel bestimmt hängt davon ab, wie oft die Formel auftaucht und wie einfach (v0.5.5) oder schwierig (v0.6.6) das Tabellenlayout ist.

Fazit: Viele Wege führen zum Ziel und haben ihre Vor- und Nachteile.

[balu]

Hallo mikele,

siehste, da sind wir uns in 2 Dingen einig.

1.
Jeder mag seine Formel.

2.

Fazit: Viele Wege führen zum Ziel

oder nach Rom, oder nach Hamburg, oder nach Hause



Gruß
balu