Excel: Wie kann ich eine Formel aus mehreren Bereichen/Zellen zusammensetzen?
Hallo,
Google sagt es mir einfach nicht: Wie kann ich in einer Formel als Bereich anstatt z. B. A1:A5 nur {A1;A3;A5} angeben? Mit genau dieser Syntax funktioniert es nicht!
Mein Anwendungsfall besteht darin, dass mir zwei Tabellendimensionen quasi nicht gereicht haben und ich jetzt für eine feste Anzahl von Zeitwerten damit arbeite, nur in jeder zweiten einen Wert anzugeben und jeweils darüber/darunter seine Relevanz. Ich weiß schon, keine perfekte Lösung, aber ihr werdet sicherlich verstehen, dass ich nicht alles neu überarbeiten mag!
Ich berechne in meinem Falle Mittelwerte, im Falle der Funktion MITTELWERT würde ich ja einfach MITTELWERT(A1;A3;A5) schreiben. Aber mein System erfordert MITTELWERTWENN, sodass ich anstatt von A1:A5 eben nicht einfach A1;A3;A5 schreiben kann - das wertet Excel (natürlich) als folgende Parameter.
Eine Intuition führte mich auch schon zur Funktion INDIREKT. Könnte die mir vielleicht weiterhelfen? Aber damit kenne ich mich gar nicht aus und bekomme immer nur einen Bezugsfehler ... oder geht es vielleicht noch viel einfacher?
Freue mich auf eure Hilfe! :-)
Grüße,
KnorxThieus (♂)
3 Antworten
Das herunterbrechen auf Summewenn/Zählenwenn funktioniert durchaus:. Leider kenne ich Deine Wenn-Bedingung nicht, ich setze einfach mal ="A" als Bedingung, A als Bedingungsspalte und C als Wertespalte.
=(SummeWenn(A1:A3;"A";C1:C3)+SummeWenn(A5:A7;"A";C5:C7))/
(ZählenWenn(A1:A3;"A")+ZählenWenn(A5:A7;"A"))
Das ergibt genau den Mittelwert und zwar mit beliebig vielen (aber je gleich vielen) Summanden
Bei SummewennS müsste man die Bereiche als Matrix fassen, aber da arbeite ich mich jetzt nicht ein.
so, jetzt hab ich Zeit gefunden und die Lösung heißt (Bedingungsspalte A, Bedingung ="ok", Zahlenspalte C):
=(SUMMENPRODUKT((A$1:A$3="ok")*(C$1:C$3<>"")*(C$1:C$3))+SUMMENPRODUKT((A$5:A$7="ok")*(C$5:C$7<>"")*(C$5:C$7)))
/(SUMMENPRODUKT((A$1:A$3="ok")*(C$1:C$3<>""))+SUMMENPRODUKT((A$5:A$7="ok")*(C$5:C$7<>"")))
Der Nenner ist die Anzahl der Zellen in den Teilbereichen C, die nicht leer sind:und denen die Bedingung "ok" (ohne"") in Spalte A gegenübersteht.
Beim Zähler ist zusätzlich die Bedingung <>"" eingefügt. Ein Text würde Fehler ergeben. Das auszuschließen, würde etwas komplizierter, nur bei Bedarf.
Vielleicht erscheint Dir die Lösung etwas umständlich, aber:
Summenprodukt ist m.E. ohnehin übersichtlicher als die Vielzahl der Formeln Summewenn(), Zählenwenn(), beides...S(), Mittelwertwenn(...S), Anzahl, Amzahl2 und vllt noch weitere: sie lassen sich alle mit Summenprodukt formulieren (Bei nur einem Argument müssen die gelieferten WAHR/FALSCH mit *1 zu 1;0 umgesetzt werden, ab 2 Klammern geschieht das automatisch).
Summenprodukt multipliziert Zeile für Zeile die Bedingungen und summiert dann die EinzelProdukte. (auch Spalte für Spalte möglich)
Sind die Abstände regelmäßig, z.B. jede zweite Zeile auslassen? Dann erstell dir eine Hilfsspalte mit der Formel =REST(ZEILE(A1);2) und kopier sie nach unten.
Diese liefert dann abwechselnd 1 und 0. Verwende die Funktion MITTELWERTWENNS und beziehe diese Spalte mit ein.
Also du willst von 3 werten den Mittelwert?
=(A1 + A2 + A3)/3
Summe aller Werte durch Anzahl
Gruß Eric
Hallo,
nicht ganz, ich will/muss ja gerade MITTELWERTWENN verwenden. Das auf SUMMEWENN und ZÄHLENWENN herunterzubrechen, brächte da doch auch nichts.
auf den Fall, dass Du leere Zahlenzellen ignorieren willst, gehe ich morgen noch ein, Das geht mit Summenprodukt.