Formeln in mehreren Zellen gleichz. ändern

Bild

Betrifft: Formeln in mehreren Zellen gleichz. ändern
von: Debian
Geschrieben am: 19.07.2015 20:26:18

(auch Excel 2011 für Mac)
Hallo zusammen :-)
In nachfolgender Tabelle möchte ich im Blatt "Ferienkontrolle" im Zellbereich AJ9:AJ20 die enthaltenen Formeln gleichzeitig ändern können.
https://www.herber.de/bbs/user/98926.xlsm
Folgender Grund:
Im betreffenden Bereich werden für jeden Monat die Anzahl an Ruhetagen angezeigt-. Da dies von der Länge des Monats abhängt und die Ruhetage nicht von Wochenenden abhängen, ist das jeweils eine unrunde Zahl. Die Formel lautet: Anzahl der Tage/7*2
Da die Monate eben nicht alle gleich sind, habe ich mir mit der Hilfstabelle in AP18:AR20 beholfen. Eine elegantere Lösung, welche die tatsächliche Anzahl Tage eines jeden Monats berechnet, wäre mir aber lieber (besonders für den Februar).
Die Formeln in AJ9:AJ20 sind aber noch etwas umfassender. Sie berechnen sollen nicht nur den Anspruch an Ruhetagen im jeweiligen Monat anzeigen, sondern ihn auch gleich reduzieren, sollten im selben Monat Ferien bezogen worden sein. Je 7 Ferientagen (in der Schweiz rechnet man mit ganzen 7-Tagewochen) sollen 2 Ruhetage vom ursprünglichen Anspruch abgezogen werden, weil während Ferienbezug keine neuen Ruhetagsansprüche entstehen. Das funktioniert bereits zufriedenstellend. Die selbe Tabelle verursacht aber Probleme, wenn man sie in Deutschland einsetzt, wo man nicht zum Beispiel 5 Wochen Ferien (also gesamt 35 Tage) im Vertrag stehen hat sondern nur 25 Tage (auch 5 Wochen, jedoch à 5 Tagen mit 2 Ruhetagen).
Für Juli nach schweizer Methode schaut die Formel bislang so aus:
=WENN(ZÄHLENWENN(B15:AF15;" ")=31;0;$AR$20-AH15/7*2-AI15/7*2)
Für Deutschland müsste die Formel entsprechend so aussehen:
=WENN(ZÄHLENWENN(B15:AF15;" ")=31;0;$AR$20-AI15/7*2)
Wie kann ich die Formeln in AJ9:AJ20 elegant auf einmal abändern, damit die Tabelle unkompliziert vom schweizerischen aufs deutsche System umstellt?
Ich hatte mir erst vorgestellt, das ich den entsprechenden Formelbereich in eine andere Zelle schreibe und in der eigentlichen Formel nur als Zellbezug angebe, den ich zum Beispiel via Dropdownliste austauschen kann. Da aber die Monate unterschiedliche Längen haben und der Februar alle vier Jahre aus der Reihe tanzt, geht das ja nicht.
Auch wäre es schön, die blauen Füllungen am Ende der Monate zum Beispiel Februar AD9:AF9 mittels Formel oder bedingter Formatierung zu Färben, anstatt jedes mal, wenn der Februar in ein Schaltjahr fällt, die Farbe manuell rauszunehmen.
Fällt jemandem von euch dazu etwas ein?
Vielen Dank im Voraus :-)
Debian

Bild

Betrifft: AW: Formeln in mehreren Zellen gleichz. ändern
von: Daniel
Geschrieben am: 19.07.2015 23:13:29
Hi
schreibe in eine freie Zelle (z.B. A1) ein D, wenn das deutsche System angewendet werden soll und ein S wenn das Schweizer System dran sein soll.
die Formel wäre dann folgende:
der erste Teil ist für beide Länder gleich, nur für die Schweiz muss zusätzlich noch der Betrag AH15/7*2 abgezogen werden, daher dann die zusätzliche Wenn-Funktion:
=WENN(ZÄHLENWENN(B15:AF15;" ")=31;0;$AR$20-AI15/7*2-Wenn($A$1^="S";AH15/7*2;0))
wenn die Funktionen zwischen beiden Ländern start unterschiedlich sind, dann natürlich nach diesem Schema:
=Wenn($A$1="S";schweizer Formel;Wenn($A$1="D";deutsche Formel;"???"))
zum wechsel zwischen beiden Ländern brauchst du dann nur ein "D" oder "S" in die Zelle A1 eintragen.
Gruß Daniel

Bild

Betrifft: AW: Formeln in mehreren Zellen gleichz. ändern
von: Debian
Geschrieben am: 19.07.2015 23:43:27
Guten Abend Daniel :-)
Anbei das Resultat, wenn ich die Formel so verwende, wie von Dir beschrieben. Ich habe mal den Pfad zum Fehler anzeigen lassen. Einzige Änderung meinerseits war, von Zelle A1 auf AQ24 zu wechseln (A1 ist belegt).
Userbild
Hat die Formel bei Dir mit der Tabelle funktioniert, die ich hier hochgeladen hab?
Grüsse.
Debian

Bild

Betrifft: AW: Formeln in mehreren Zellen gleichz. ändern
von: Daniel
Geschrieben am: 19.07.2015 23:55:01
hi
hab ich jetzt nicht probiert.
im Prinzip gehts darum, dass du die Formeln gleich so schreibst, dass sie im Prinzip für beide Länder funktionieren und abhängig von einem Eintrag in einer von dir gewählten Zelle (D oder S) dann der jeweilige Teil der Formel verwendet wird.
Das wollte ich dir zeigen, funktionierende Formeln passend für deine Datei darfst du dir selber schreiben, es werden ja sicherlich noch mehr Formeln länderspezifisch sein.
Gruß Daniel

Bild

Betrifft: AW: Formeln in mehreren Zellen gleichz. ändern
von: Daniel
Geschrieben am: 19.07.2015 23:55:02
hi
hab ich jetzt nicht probiert.
im Prinzip gehts darum, dass du die Formeln gleich so schreibst, dass sie im Prinzip für beide Länder funktionieren und abhängig von einem Eintrag in einer von dir gewählten Zelle (D oder S) dann der jeweilige Teil der Formel verwendet wird.
Das wollte ich dir zeigen, funktionierende Formeln passend für deine Datei darfst du dir selber schreiben, es werden ja sicherlich noch mehr Formeln länderspezifisch sein.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formeln in mehreren Zellen gleichz. ändern"