Microsoft Excel

Herbers Excel/VBA-Archiv

Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen

Betrifft: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen von: Dierick
Geschrieben am: 25.09.2020 11:03:36

Hallo, ich habe folgedendes Problem:

Generell: Ich möchte zwei Formeln abwechselnd auf Spalten anwenden mit jeweils unterschiedlichen Bezügen.


Speziell: Ich habe zwei Tabellenblätter. Auf dem einen Tabellenblatt "Tage" sind spaltenweise Werte für einen Tag. Zudem sind noch gewisse Konditionen in Spalten angegeben, die den Wert 1 und 0 annehmen können.


Im zweiten Tabellenblatt "Auswertung" möchte ich diese Werte tagesweise unter Berücksichtigung der Konditionen zusammenfassen. Dabei sollen in einer Spalte konditionale Summen mit Bezug auf das Blatt "Tage" sein und in der darauffolgenden Spalte, sollen diese Summen ausgewertet bzw. deren Anteile ermittelt werden. Das geht alternierend weiter für die Tage, d.h. konditionale Summe und prozentuale Auswertung wechseln sich ab.


Der Bezug auf Tabellenblatt 1 für die konditionale Summe ist jedoch fortlaufend, d.h. der Bezug soll für jeden Tag sein und nicht Spalten überspringen. Der Bezug der Prozent Formel ist jedoch wieder nur auf jede zweite Spalte.


Das klingt vermutlich alles sehr verwirrend und ich habe ein Beispiel angehängt. Da ich sehr viele Tage habe, wäre ich sehr dankbar über Lösungsvorschläge, wo ich die Formeln nicht abwechselnd reinkopieren muss. Meine Google Recherche deutet auf irgendwas mit INDEX bei den Bezügen und ggf. REST bei den Formeln hin, aber leider kriege ich das für mein Beispiel nicht zusammen.


https://www.herber.de/bbs/user/140427.xlsx

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: ChrisL
Geschrieben am: 25.09.2020 11:11:06

Hi

Hier mal ein Ansatz/Beispiel:

=WENN(REST(SPALTE(A1);2);"Formel1";"Formel2")

cu
Chris

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: Dierick
Geschrieben am: 25.09.2020 11:17:48

Danke für deinen Input, Chris! Das funktioniert gut, um die ersten zwei Spalten zu füllen, allerdings nicht im weiteren Verlauf wenn ich die Formel "rüberziehe", da die Bezüge dann nicht korrekt sind. Die müsste ich, wie oben beschrieben, leider noch anpassen. Hast du da einen Ansatz?

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: SF
Geschrieben am: 25.09.2020 11:22:59

Hola,

=WENN(REST(SPALTE();2)=0;SUMMENPRODUKT((Tage!$B$1:$G$1=RECHTS(B$1;5))*(Tage!$B$2:$G$8));B2/B$2)

=WENN(REST(SPALTE();2)=0;SUMMENPRODUKT((Tage!$B$1:$G$1=RECHTS(B$1;5))*(Tage!$E$2:$E$8=1) *(Tage!$F$2:$F$8=1)*(Tage!$G$2:$G$8=0)*(Tage!$B$2:$G$8));B3/B$2)

Die Formel für Kond 3 bekommst du jetzt selber hin.

Gruß,
steve1da

Betrifft: Danke, das klappt super!
von: Dierick
Geschrieben am: 25.09.2020 11:51:34

Super, das klappt wie gewünscht, auch für Kond 3! Vielen Dank!

Das mit dem RECHTS ist echt super! In meinem Original-File stehen Daten, statt "Tag 1", da das ist dann noch einfacher und mein Beispiel war entsprechend etwas unnötig verkompliziert. Aber so habe ich noch wasextra dazugelernt! :)

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: Dierick
Geschrieben am: 25.09.2020 13:18:27

Oh, es ist doch ein kleines Problem aufgetreten. Wenn ich die Formeln da einfüge, wo eigentlich meine Werte standen, kriege ich Zirkelbezüge-Fehler, da sich (vermutlich) die Werte der Prozente auf die gerade beschriebene Zelle beziehen. Hättest du dafür vielleicht noch eine Lösung?

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: SF
Geschrieben am: 25.09.2020 13:19:45

Hola,

dann ist die Beispieldatei anders aufgebaut wie das Original?

Gruß,
steve1da

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: Dierick
Geschrieben am: 25.09.2020 13:54:41

Hallo, ok hab das Sample noch mal angepasst und es ist vermutlich doch kein Zirkelbezug, sondern, dass er sich bei den Prozent nicht auf die Werte der letzten Spalte bezieht, sondern auf den aktuellen.

Im Blatt "Auswertung" habe ich deine Lösung jetzt (mit angepasstem Datum) drunterkopiert, wie es wunderbar funktioniert.

In Blatt "Auswertung 2", wenn ich quasi meine händisch eingegebenen Werte ersetze, ist der Prozentbezug leider nicht mehr richtig, während die Summen stimmen.

https://www.herber.de/bbs/user/140435.xlsx

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: SF
Geschrieben am: 25.09.2020 14:44:48

Hola,

japp, der Zirkelbezug kommt von mir :)
Ändere in der ersten Formel den Dann-Teil zu:
.....);A2/A$2)
Gruß,
steve1da

Betrifft: Perfekt, vielen Dank!!
von: Dierick
Geschrieben am: 25.09.2020 14:55:34

Klappt wie gewünscht, vielen Dank und ein schönes Wochenende!

Betrifft: Danke, dir auch!
von: SF
Geschrieben am: 25.09.2020 14:58:12



Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: Rudi Maintaire
Geschrieben am: 25.09.2020 11:18:02

Hallo,
Summe Tag1:
=SUMME(INDEX(Tage!$2:$8;;SPALTE()/2+1))

Die restlichen Formeln entsprechend.

Gruß
Rudi

Betrifft: AW: Formeln abwechselnd auf Spalten anwenden mit unterschiedlichen Bezügen
von: Daniel
Geschrieben am: 25.09.2020 11:23:28

Hi
in B2 auf Auswertung:

=WAHL(REST(SPALTE();2)+1;SUMME(INDEX(Tage!$B$2:$D$8;0;(SPALTE()-1)/2+1));A2/A$2)

Gruß Daniel

Beiträge aus dem Excel-Forum zum Thema "Formeln abwechselnd auf Spalten anwenden mit unterschiedlich"