Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1636to1640
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt (?) anhand Spaltenüberschriften

Summenprodukt (?) anhand Spaltenüberschriften
10.08.2018 11:42:47
Joerschi
Liebes Forum,
folgendes Problem möchte ich per Formel lösen (Screenshot & Musterdatei zuerst, unten Beschreibung):
Userbild
Musterdatei:https://www.herber.de/bbs/user/123255.xlsx
Es gibt einen Datenbereich H3:T85, wobei H2:T2 die Überschriften je Spalte darstellt.
Weiterhin gibt es einen Vorgabebereich (für die zu ermittelnde Berechnung) in B4:D4 als globale Vorgabe und B6:D9 wieder zeilenweise (jede dieser Spalten nimmt sich immer die globale Vorgabe von B4:D4).
Gesucht eine Formel für die Zelle(n) E6:E9.
Was genau soll errechnet werden?
Am besten am Beispiel der Vorgaben in den Zellen B6:D6 (Ergebnisformel gehört dazu in E6):
Es soll die Anzahl aller Datensätze ermittelt werden, wo die gleichzeitigen Vorgaben zutreffen:
~ Alle Werte der Spalte mit der Überschrift 3 (Vorgabe aus B6), die kleinergleich Zahlenwert 1 sind (Vorgabe B4)
UND
~ Alle Werte der Spalte mit der Überschrift 2 (Vorgabe aus c6), die kleinergleich Zahlenwert 1 sind (Vorgabe c4)
UND
~ Alle Werte der Spalte mit der Überschrift 3 (Vorgabe aus D6), die kleinergleich Zahlenwert 1 sind (Vorgabe D4)
UND
~ Alle Werte der Spalte V mit "ja" (aus Vorgabe C3)
Manuell gezählt wäre das Ergebnis daraus 11.
Außerdem zu beachten: Wenn in einer Überschriftsvorgabe eine leere Zelle ist, dann zählen nur die Vorgaben, wo etwas ausgefüllt ist.
(Als "Krücke" könnte da aber genausogut ein Hilfswert wie "X" stehen, falls es die Formellösung erleichtert)
Die Farbenwahl von Blau und Rot ist nur zur Übersicht gedacht, welche Inhalte miteinander in Beziehung stehen.
Normalerweise könnte man so etwas vielleicht mit Summenprodukt oder Zählenwenns lösen, aber durch die Bedingung der Miteinbeziehung der Spaltenüberschriften komme ich da lösungstechnisch nicht weiter... :-(
Ich würde mich freuen, wenn jemand einen Tipp hätte.
Vielen Dank und viele Grüße
Joerschi

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
10.08.2018 14:12:54
neopa
Hallo Joerschi,
... normalerweise hast Du doch sonst immer zusätzlich zu Deinem Bild eine Exceldatei eingestellt. Warum diesmal nicht? Für heute gehe ich aber gleich offline. Ich könnte also frühestens morgen nochmal schauen.
Gruß Werner
.. , - ...
Der Link ist doch vorhanden...
10.08.2018 14:14:27
{Boris}
Hi Werner,
nur nicht als HL formatiert:
Musterdatei:https://www.herber.de/bbs/user/123255.xlsx
VG, Boris
AW: hab ich übersehen, bin aber am abschalten owT
10.08.2018 14:21:23
neopa
Gruß Werner
.. , - ...
AW: hab ich übersehen, bin aber am abschalten owT
10.08.2018 22:24:05
Jerschi
Hi Werner,
wie Du es schaffst.
Vor abends komme ich voraussichtlich bestimmt auch nicht zum Antworten.
Vorab auf jeden Fall vielen Dank für Dein Engagement.
VG; Joerschi
Anzeige
AW: geht schon mit SUMMENPRODUKT(), allerdings ...
11.08.2018 09:01:56
neopa
Hallo Joerschi,
... bedarf es dazu zusätzlicher Datenaufbereitung und (in meinem momentanen Lösungsvorschlag) Datenwert freie Zellen G3:G85,was in Deinem Beispiel ja gewährleistet ist. Kann man sicherlich auch noch anders berechnen, aber so erschien es mir am einfachsten:
In E6:
=SUMMENPRODUKT((INDEX(G$3:T$85;;1+B7)

und Formel nach unten kopieren
Gruß Werner
.. , - ...
AW: Dankeschön
11.08.2018 09:17:19
Jerschi
Hallo Werner,
vielen Dank für die Lösung.
Sie funktioniert gut (Du bist bei der Formel in den Vorgaben in Zeile 6 und 7 verrutscht, aber kann man ja leicht ändern).
Muss ich mir mal anschauen, weil ich den Ausdruck

;;1+
innert der Formeln noch nicht kannte.
Beste Grüße und nochmals Dank an Dich
Joerschi
Anzeige
AW: bitte; ja, eingestellt war Formel E7 owT
11.08.2018 09:56:33
neopa
Gruß Werner
.. , - ...
Ergänzungsfrage - Verknüpfung mit ODER
11.08.2018 11:59:06
Jerschi
Hallo Werner,
ich bastle die ganze Zeit an einer ODER-Verknüpfung Deiner Lösung, aber bekomme es nicht ganz zusammen.
Und zwar suche ich eine Formel - direkt an obigen Beispiel - , wo ich herausfinde, in wie vielen Datensätzen die Bedingung z. B. der ersten Überschriftenvorgaben (oben 3,2,1) ODER jene der dritten Überschriftvorgaben (oben 8,9) eintreten.
Also die Anzahl der Datensätze, wo mindestens eines der beiden Überschriftenvorgaben eintritt.
Könntest Du da nochmal helfen?
Danke Dir vorab und Beste Grüße
Joerschi
AW: Teilformeln add., deren Teilergebn. prüfen ...
11.08.2018 15:42:20
neopa
Hallo Joerschi,
... so:
=SUMMENPRODUKT((((INDEX(G$3:T$85;;1+B6)0)*(V$3:V$85=C$3))
Gruß Werner
.. , - ...
Anzeige
AW: Teilformeln add., deren Teilergebn. prüfen ...
11.08.2018 20:17:26
Jerschi
Vielen herzlichen Dank Werner :-)))
AW: Weitere Ergänzungsfrage bzgl MITTELWERT
13.08.2018 14:06:43
Joerschi
Hallo Werner und alle Excel-Freunde,
ich habe eine weitere ergänzende Frage (und denke, die ist an dieser Stelle wegen der Sachnähe und Zusammenhang viel besser aufgehoben als in einem neuen Thema).
Und zwar suche ich für die letztgenannte Formel (Ermittlung Anzahl mindestens eine der Vorgabeüberschriften tritt ein) noch eine Erweiterungs-Lösung, wenn es zusätzlich hypothetisch eine Spalte "X" gibt, wo Zahlenwerte drin stehen.
Für diese Zahlenwerte soll der Mittelwert derjenigen Werte gebildet werden, aus deren Datensätzen(Zeilen) bisher die Anzahl ermittelt wurde (ergo nicht die Anzahl ausgegeben, sondern deren Mittelwert der Zahlen in Spalte "X" - vermutlich eine Lösung mit vorangestellten MITTELWERT() ).
Beispielhaft habe ich diese hypothetischen Werte in einer Spalte X in der Beispieldatei ergänzt.
Das sähe dann so aus:
Userbild
Musterdatei: https://www.herber.de/bbs/user/123305.xlsx
Ich würde mich sehr freuen, wenn wieder jemand helfen kann.
Beste Grüße
Joerschi
Anzeige
AW: da ist wohl eine klassische Matrixformel ...
13.08.2018 15:12:44
neopa
Hallo Jörschi,
... diesmal die die einfachste Lösungsformel, denn ohne die wird die Formel zu lang.
In E6:

{=MITTELWERT(WENN((INDEX(G$3:T$85;;1+B6)
und Formel nach unten kopieren.
Gruß Werner
.. , - ...
AW: da ist wohl eine klassische Matrixformel ...
13.08.2018 16:18:08
Jerschi
Hi Werner,
vielen Dank wiederum.
Die Formel nehme ich natürlich auch (für andere Zwecke :-) ), aber ich meinte eine Lösung für die von Dir gepostete Lösung vom 11.08.2018 15:42:20 (also die ODER-Variante).
Hättest Du dafür einen Ansatz?
Liebe Grüße
Joerschi
AW: nach "Schema F" ...
13.08.2018 17:01:00
neopa
Hallo Jörschi,
... und zwar ungetestet so:
{=MITTELWERT(WENN([bisherige Formelteil innerhalb von SUMMENPRODUKT()];X$3:X$85))}
Gruß Werner
.. , - ...
Anzeige
AW: hmmm.... scheinbar berechnet es immer...
13.08.2018 17:50:10
Jerschi
den MITTELWERT der gesamten Liste aller Werte - unabhängig von den gesetzten Bedingungen.
Hier mal als Musterdatei mit den unten beschriebenen konkreten Beispielen: https://www.herber.de/bbs/user/123311.xlsx
(als Bedingung wurde vorgegeben, dass entweder in allen Spalten mit "1" oder "13" eine 1 steht sowie in Spalte V ein "ja")
Formel in E17 lautet aktuell:

=MITTELWERT(WENN(SUMMENPRODUKT((((INDEX(G$3:T$85;;1+B7)0)*(V$3:V$85=C$3));X$3:X$85)) 

Manuell ausgerechnet beträgt der Mittelwert in Zelle E18 40,65 (das ist der gesuchte richtige Wert) und in Zelle E17 die adaptierte Formel von Dir oben, wo immer der Mittelwert von 42,00 steht, welcher aus Spalte X ermittelt wird.
An irgendeiner Kleinigkeit hängt es noch....
Liebe Grüße
Joerschi
Anzeige
AW: ... so, ja, aber ...
13.08.2018 18:22:55
neopa
Hallo Joerschi,
... SUMMENPRODUKT() selbst sollte nicht mit in die Formel übernommen werden, sondern lediglich das was innerhalb SUMMENPRODUKT() berechnet wird (deshalb "Schema F", also wie in der vorherigen MITTELWERT()-Formel).
Ich hab mir jetzt Deine Datei nicht heruntergeladen. Aber ich denke, dass Du jetzt zum erwünschten Ergebnis kommen solltest. Oder?
Gruß Werner
.. , - ...
AW: ... ui - hätte ich mal lieber genauer gelesen
13.08.2018 18:32:50
Jerschi
Danke - so funktioniert es super!
Liebe Grüße
Joerschi

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige