Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 12:58:59
Daten einfügen aus anderem Sheet
19.04.2024 12:23:24
Wenn-Formel anpassen
19.04.2024 12:09:19
Unique (Application.WorksheetFunction.Unique)
19.04.2024 11:51:37
Nächsten identischen Treffer suchen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1700to1704
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1700to1704
1700to1704
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel richtig, dennoch ....

Formel richtig, dennoch ....
07.07.2019 14:52:14
Fred
Hallo Excel-Profis,
ich ermittel die erzielten Treffer eines Teams (Team = "K3") aus den vorherigen 20 Spielen. _ Ursprünglich habe ich die Formel in "AD38" eingetragen und dann "hoch kopiert". 

=WENNFEHLER((SUMMEWENN(F18:F37;$K$3;R18:R37)+SUMMEWENN(G18:G37;$K$3;X18:X37));0)

Nun geht mir in den oberen Zeilen der Bezug verloren.
Mir ist aber eine korrekte Formel in der ersten Zeile wichtig, da diese nie gelöscht wird und ggf bis zur letzten Zeile runter kopiert werden soll.
Ich habe zur Verdeutlichung eine einfach gehaltene Mappe angefügt.
https://www.herber.de/bbs/user/130768.xlsx
Kann mir bitte jemand die "verbesserte" Formel schreiben?
Mit freundlichen Gruß
Fred Neumann

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel richtig, dennoch ....
07.07.2019 14:56:40
Hajo_Zi
Hallo Fred,
die Datei ist beschädigt un kann nicht geöffnet werden.

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
AW: Formel richtig, dennoch ....
07.07.2019 15:03:25
Hajo_Zi
Hallo Fred,
ich bin dann raus, auch die läst sich nicht öffnen.
Gruß Hajo
Anzeige
ist ja auch logisch
07.07.2019 15:10:16
WF
Hi,
in den "oberen Zeilen" gibt es ja auch keine 20 Spiele sondern weniger.
Das Ergebnis null entsprecht Deiner Fragestellung.
Wenn Du fragst: "die letzten Spiele bis maximal 20" ist das was anderes - willst Du das ?
WF
AW: ist ja auch logisch
07.07.2019 15:13:01
Fred
Hallo WF,
ich möchte, dass die Formel in der obersten Zeile korrekt "geschrieben" ist,- da diese iR runter kopiert werden soll.
Gruß
Fred Neumann
AW: ist ja auch logisch
07.07.2019 15:14:19
Fred
Hallo WF,
Ja, ich will :-)
Gruß
Fred
Anzeige
rückwärts kopiert ist's einfach
07.07.2019 17:39:41
WF
Hi,
in AD38:
=WENNFEHLER(SUMMEWENN(F18:F37;$K$3;R18:R37)+SUMMEWENN(G18:G37;$K$3;X18:X37);SUMMEWENN(F$1:F37;$K$3; R$1:R37)+SUMMEWENN(G$1:G37;$K$3;X$1:X37))
nach oben kopieren
Umgekehrt geht dann natürlich nicht, da #BEZUG! mitgeschleppt wird.
WF
AW: rückwärts kopiert ist's einfach
07.07.2019 21:34:18
Fred
Hallo WF,
danke für deine Mühe.
Allerdings macht deine Formel im Ergebnis auch nichts anderes als meine.
Vielleicht habe ich das Problem nur nicht deutlich genug erklärt.
Ich möchte in der Zelle "AD5" eine Formel, die im Ergebnis
die Summe der Treffer
in den ersten 15 Min.
aus den letzten vorherigen 20 Spielen anzeigt.
Ich möchte die Formel in "AD5", da diese entsprechend der Anzahl aller Spiele, entsprechend runter kopiert werden soll.
https://www.herber.de/bbs/user/130776.xlsb
Gruß
Fred
Anzeige
Aufgabe nicht klar
07.07.2019 22:42:01
Josef
Hallo Fred
Du hast durch das kopieren der Formel von Zeile 5 bis 20 den Fehler #BEZUG.
Wenn wir endlich einmal von dir erfahren würden, was die Formel in Zelle "AD5" wirklich berechnen soll,
ist es anschliessend für die Helfer einfacher, daraus eine "kopierbare" Formel zu machen.
Erstelle - und zeige uns doch einmal eine Formel die nur in Zeile 5 funktioniert, und das Selbe für Zeile 20
Gruss Sepp
AW: Aufgabe nicht klar
08.07.2019 08:57:17
Fred
Hallo Sepp,
in de genannten Spalte wird die Trefferzahl der vorherigen 20 Spiele ermittelt (bei Fehler: 0)
Erklärung:
Ich habe in einer Spalte eine Liste von Teams, die in einer Schleife "abgearbeitet" werden. Bei jeden dieser Teams werden Makros aufgerufen, u.a. dies, welches im genannten Tabellenblatt
die vorhandenen Daten ab Zeile 6 löscht,
die Spiele des Teams einfügt,
und u.a. die Zelle "AD5" markiert und bis zur letzten Zeile "runter zieht".
So wie derzeit die Formel in "AD5" steht, bringt das runter ziehen nix.
Was für eine Formel ich in "AD5" brauche:
1. Feststellen, ob sich die Formel in "AD25" oder danach befindet
2. wenn Ja: die Summewenn-Formel
3. Wenn Nein: 0
https://www.herber.de/bbs/user/130777.xlsb
Gruß
Fred
Anzeige
AW: Aufgabe nicht klar
08.07.2019 15:55:31
Josef
Hallo Fred
Ich würde das mit einer Hilfsspalte lösen, Hier ein Beispiel mit Hilfspalte AG
In AG5 folgende Formel: =(F5=$K$3)*R5+(G5=$K$3)*X5 und nach unten kopieren.
Dann in AD5
=WENN(ZEILE(C5)<25;0;SUMME(INDEX(AG:AG;ZEILE(C5)-20):AG4))
Gruss Sepp
hier eine Lösung ohne Hilfsspalte
08.07.2019 21:09:22
Josef
Hallo
Formel in AD5
=WENN(ZEILE(Z5)<25;0;SUMMENPRODUKT(((F4:F$5=$K$3)*R4:R$5+(G4:G$5=$K$3)*X4:X$5)*(ZEILE(Z4) -ZEILE(Z4:Z$5)<20)))
Gruss Sepp
AW: hier eine Lösung ohne Hilfsspalte
09.07.2019 09:18:09
Fred
Hallo Sepp,
danke für deine Geduld und für die "Formel - Lösung",- passt und übernehme ich!
Gruß
Fred
AW: z.B. mit SUMMENPRODUKT() von MMULT() ...
08.07.2019 15:59:40
MMULT()
Hallo Fred,
... in AD5:

=WENN((ANZAHL(H$5:H5)&lt20)+(A5="");"";SUMMENPRODUKT(MMULT(R$5:AC5*(RECHTS(R$4:AC$4;3)=RECHTS(AD$3;3) )*((F$5:F5=K$3)+(G$5:G5=K$3));ZEILE(A$1:A$12)^0)))
und nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: und nur die letzten 20 Spiele ausgewertet...
09.07.2019 07:39:04
neopa
Hallo Fred,
... dazu muss ich ich meine Formel natürlich noch ergänzen. Das hatte ich gestern aus dem Blick verloren.
Nachfolgende Formel AD5 ist so definiert, dass Du auch die anderen Summen mit dieser ermitteln kannst (dazu Formel einfach auch nach rechts ziehend kopieren).
Dazu hab ich untenstehend in AE5:AI5 die Zeitbereiche (ohne beginnendes "HT-", weil Du ja auch die die Tore als Gast mit dazurechnen willst) ergänzt.
In nachstehender Tabelle sind einige Spalten und Zeilen ausgeblendet.
Arbeitsblatt mit dem Namen 'Tabelle1'
 AFGKRWXACADAI
3   AUGSBURG    Tore 0-15Tore 75-90+
4IDHeimTeamGastTeam(Hz) AHT-Tore 0-15HT-Tore 75-90+GT-Tore 0-15GT-Tore 75-90+SummeSumme
514DUSSELDORFAUGSBURG0   1  
622AUGSBURGB. MONCHENGLADBACH01     
17124STUTTGARTAUGSBURG0      
18130BAYER LEVERKUSENAUGSBURG0 1    
19139AUGSBURGSCHALKE01     
20149HERTHA BERLINAUGSBURG2  1   
21163AUGSBURGWOLFSBURG2   1  
22166AUGSBURGDUSSELDORF1   1  
23175B. MONCHENGLADBACHAUGSBURG0 2    
24190AUGSBURGMAINZ01   720
25200WERDER BREMENAUGSBURG011  820
26201AUGSBURGBAYERN MUNICH21  1821
27214FREIBURGAUGSBURG011  919
28219AUGSBURGDORTMUND0   1918
29233RB LEIPZIGAUGSBURG0    917
30238AUGSBURGHANNOVER1 21 1018
31250NURNBERGAUGSBURG0 2  1016
32262AUGSBURGHOFFENHEIM1  111117
33271EINTRACHT FRANKFURTAUGSBURG21  21119
34273AUGSBURGSTUTTGART01   1118
35282AUGSBURGBAYER LEVERKUSEN11 111318
36299SCHALKEAUGSBURG0    1217
37300AUGSBURGHERTHA BERLIN01  21319
38317WOLFSBURGAUGSBURG0 2 11321
39          

ZelleFormel
AD5=WENN((ANZAHL($A$5:$A5)&lt20)+($A5="");"";SUMMENPRODUKT(MMULT($R$5:$AC5*(RECHTS($R$4:$AC$4;3)=RECHTS(AD$3;3))*(($F$5:$F5=$K$3)+($G$5:$G5=$K$3))*(ZEILE()-ZEILE(A$5:A5)&lt20);ZEILE(A$1:A$12)^0)))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: ergänzend angemerkt ...
09.07.2019 08:05:37
neopa
Hallo,
... in meiner Auswertung ist momentan das jeweils aktuelle Spielergebnis für "die letzten 20 Spiele" mit eingerechnet.
Gruß Werner
.. , - ...
AW: ergänzend angemerkt ...
09.07.2019 09:19:02
Fred
Hallo Werner,
eine ganz anders geschriebene Formel und dem "Einbezug" des aktuellen Ergebnisses. Werde ich für ein anderes Blatt unbedingt brauchen!
Danke für die Lösung!
Gruß
Fred
AW: dazu nun ...
09.07.2019 10:33:50
neopa
Hallo Fred,
... beachte jedoch meinen Beitrag den ich eben als Antwort auf den letzten Beitrag von WF geschrieben habe. Wenn nur die Tore im jeweiligen Zeitabschnitt der Mannschaft in K3 ermittelt werden sollen, muss meine Formel noch entsprechend angepasst werden.
Gruß Werner
.. , - ...
Anzeige
AW: dazu nun ...
09.07.2019 10:44:14
Fred
Hallo Werner,
einfach nur kopieren und einfügen mache ich schon lange nicht mehr und habe das schon erkannt.
Was mir allerdings bei Sepp seiner Formel ganz besonders gut gefallen hat; das "der Bereich 20 Spiele" als Zahl angegeben ist. Wenn ich diese Zahl durch einen Bezug auf einen Zelleninhalt ersetze, ist das eine ganz ganz flexible Lösung.
Danke nochmals für den Nachtrag, Werner!
Gruß
Fred
er wollte was anderes
09.07.2019 08:23:07
WF
Am 07.07. um 15:10 Uhr fragte ich:
in den "oberen Zeilen" gibt es ja auch keine 20 Spiele sondern weniger.
Das Ergebnis null entspricht Deiner Fragestellung.
Wenn Du fragst: "die letzten Spiele bis maximal 20" ist das was anderes - willst Du das ?
Er antwortete 4 Minuten später:
Hallo WF,
Ja, ich will :-)
Anzeige
AW: ja, allertdings ...
09.07.2019 10:30:59
neopa
Hallo WF,
... hat dies nichts mit dem zu tun was Du schreibst. Ich hatte da die letzten 20 Spiele ausgewertet. Jedoch erfasst meine angeführte Auswertung alle Tore im Zeitabschnitt beider Mannschaften im Spiel der Mannschaft die in K3 angegeben ist und nicht die dieser. Das war mein Interpretationsfehler.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige