Hi Excel-Freunde,
ich werde bald verrückt. Bin zwar kein Excel-Neuling mehr, aber hier komme ich an meine Grenzen ohne Makro (was ich gern vermeiden möchte - was sicher auch geht, aber meine Kreativität fehlt).
Bitte entnehmt meine Vorstellung der angehängten Excel-Datei. Die Formatierung muss aufgrund globaler Verwendung unbedingt so bleiben.
Ich möchte wie folgt auswerten:
E2 = wieviele Anfragen habe ich im Juni 2017 für Kopf erhalten
F2 = wie hoch ist der Potentielle Umsatz für Kopf im Juni 2017
Meine Teillösung sieht wie folgt aus:
E2 = SUMMENPRODUKT((MONAT($A$2:$A$6)=6)*(JAHR($A$2:$A$6)=2017))
Jetzt bekomme ich schonmal die Antwort auf "wieviele Anfragen habe ich im Juni 2017 bekommen".
Folgende Probleme:
1) Ich habe noch nicht die Bedingung "Kopf" abgefragt
2) wenn ich jetzt eine Zeile Oberhalb A2 einfüge (hätte ich gern, damit die letzte Anfrage immer oben steht) verschiebt sich mein absoluter Bezug nach unten, also auf $A$3:$A$7
3) jetzt habe ich die potenziellen Umsätze noch nicht hinzugelesen in z.B. F2 -> SUMMEWENNS??
Problem 2) habe ich umgangen indem ich die Überschrift mit eingebunden habe, als $A$1:$A$6. Das funktioniert für alle anderen (sind zig in der Datei) Formeln hervorragend, aber eben nicht für das Summenprodukt.
Fragen die euch helfen mein Problem zu lösen, immer her. Mir fällt echt nichts mehr ein.
Könnt ihr mir helfen?
E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)
oder für Excel seit 2007 auch:
E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
Hallo
Ich würde es keinesfalls mit einer Pivot-Tabelle auswerten.
Auf jeden Fall nicht "Datum der Anfrage" in den Zeilenbereich ziehen und nach Jahre und Monate gruppieren.
Auf jeden Fall nicht "Kategorie" Spaltenbereich ziehen.
Auf jeden Fall nicht "Potentieller Umsatz" und "Kategorie" in den Wertebereich ziehen.
Und keinesfalls "Σ Werte" in den Zeilenbereich ziehen.
(12.07.2017, 16:37)lupo1 schrieb: [ -> ]E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)
oder für Excel seit 2007 auch:
E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
hier auch nochmal vielen Dank!
magst du mir folgendes erklären? Also wofür
{-1.1} oder &{6.7}
E2:
=SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2:
=SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
Gib mal
=6&-17
und dann
=--(6&-17)
selbst ein. Danach formatierst Du die 42887 als Datum.
Mit {-1.1} ziehe ich - nach dem obigen Strickmuster - ALLE Daten vor Juni von ALLEN vor Juli 2017 ab. Was resultiert daraus? Der Juni.
Vorteil der zweiten Schreibweise:
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{4.7}&-17;C$2:C$6;"Kopf")) ergibt April bis Juni
F2: =SUMME({-1.1.-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{1.3.5.7}&-17;C$2:C$6;"Kopf")) ergibt Jan/Feb/Mai/Jun
(12.07.2017, 16:37)lupo1 schrieb: [ -> ]E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)
oder für Excel seit 2007 auch:
E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
zu früh gefreut... habe mich für folgende Formel entschieden
=SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
funktioniert hervorragend, ABER jetzt bleibt noch mein ursprüngliches Problem:
wenn ich oberhalb $A$2:... eine Zeile einfüge ändert sich der Bezug auf $A$3:...
Excel müsste bei $A$1:... beginnen. dann aber wirft Excel mir #NV aus.
Lösung möglich?
A$2:A$6 ersetzst Du durch INDEX(A:A;2):A$6 und die anderen entspr.
Mit
F2: =SUMMENPRODUKT(--(TEXT(A$1:A$6;"JMM")="1706");--(C$1:C$6="Kopf");B$1:B$6)
ginge es aber auch (Doppelminus und Semikola). Ist sogar 1,3mal schneller, als mit *.
Hi FaChJu,
nimm, wie shift-del geschrieben hat, eine Pivot-Tabelle, dann kannst Du ganz ohne Formeln alles total einfach auswerten.
Tabelle1 | A | B | C | D | E | F | G | H | I | J |
| 1 | Datum der Anfrage | Potenzieller Umsatz | Kategorie | | | | | | | |
| 2 | 12-Jun-17 | 10000 | Kopf | | 3 | | | | | |
| 3 | 19-Jun-17 | 20000 | Hals | | | | | | | |
| 4 | 26-Jun-17 | 30000 | Brust | | | | Zeilenbeschriftungen | Summe von Potenzieller Umsatz | Anzahl von Kategorie | |
| 5 | 3-Jul-17 | 40000 | Hand | | | | '2017 | | | |
| 6 | 10-Jul-17 | 50000 | Fuß | | | | Jun | 60000 | 3 | |
| 7 | 11-Jul-17 | 50001 | Fuß | | | | Brust | 30000 | 1 | |
| 8 | | | | | | | Hals | 20000 | 1 | |
| 9 | | | | | | | Kopf | 10000 | 1 | |
| 10 | | | | | | | Jul | 140001 | 3 | |
| 11 | | | | | | | Fuß | 100001 | 2 | |
| 12 | | | | | | | Hand | 40000 | 1 | |
| 13 | | | | | | | Gesamtergebnis | 200001 | 6 | |
| 14 | | | | | | | | | | |
| 15 | | | | | | | | | | |
| verwendete Formeln | |
| Zelle | Formel | Bereich | N/A |
| E2 | =SUMMENPRODUKT((MONAT($A$2:$A$6)=6)*(JAHR($A$2:$A$6)=2017)) | | |
| Excel-Inn.de |
| Hajo-Excel.de |
| XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007 |
| Add-In-Version 19.08 einschl. 64 Bit |
Wenn Du dann noch vorher die belegten Bereiche Spalte A bis C in eine intelligente Tabelle umwandelst (mit STRG-L), passt sich der Pivot-Datenbereich automatisch an.
(13.07.2017, 08:56)Rabe schrieb: [ -> ]nimm, wie shift-del geschrieben hat, eine Pivot-Tabelle, dann kannst Du ganz ohne Formeln alles total einfach auswerten.
Nein, nein, nein. Ich habe klar geschrieben dass keine Pivot-Tabelle verwendet soll.
Wenn es möglich wäre dann würden Admins diese Funktion sofort abschalten. Viel zu effizient.
Hi Detlef,
(13.07.2017, 09:05)shift-del schrieb: [ -> ]Nein, nein, nein. Ich habe klar geschrieben dass keine Pivot-Tabelle verwendet soll.
Wenn es möglich wäre dann würden Admins diese Funktion sofort abschalten. Viel zu effizient.
nu sei doch nicht so ironisch. Das versteht nicht jeder.
:19: :21: