Beiträge zum Thread: Mehrere Blätter in einem Gesamtblatt automatisch ausfüllen

Hi

eine möglichkeit wäre:
1. Packe die Tabellen, die in den Länderseiten unten stehen (1930 Zeile 101-113), in eine Tabelle
mach dazu eine Länderliste mit allen Ländern und und kopiere sie für jede WM untereinander
scheibe das Jahr, bzw den jeweiligen Tabellenblattnamen in eine Spalte.

2. übernehme dann die Formeln der Tabelle, aber referenziere mit Hilfe der der INDIREKT-Funktion auf das jeweilige Blatt, dass in der neuen Spalte angegeben ist. da du hier auch den Zellbereich mit als Text angeben musst, solltet du ihn so groß wählen, dass er für alle Weltmeisterschaften passt.

diese Sammeltabelle kannst du dann im Blatt Gesamt einfach auswerten, weil du die Werte aller WMs in einer Tabelle hat.
https://www.herber.de/bbs/user/180866.xlsx
Wenn weitere WMs hinzukommen, duplizierst du in der Tabelle1 einen Länderblock und passt in Spalte A die Jahreszahl=Tabellenblattnamen an.

Gruß Daniel

Moin, Daniel

komme erst jetzt dazu, Dir zu antworten.
Hab vielen Dank für Deine Mühe!
Für meine Beispieldatei habe ich Deinen Tipp mal für ein weiteres Jahr ausprobiert, funktioniert einwandfrei. Wäre ich nie drauf gekommen.
Meine Originaldatei beinhaltet noch eine Erweiterung. Ich werde es ausprobieren. Wenn es nicht funktioniert, melde ich mich nochmal bei Dir.
Nochmals vielen Dank!

Gruß Bodo

Hallo BodoV,

du hast O2016 angegeben. Bei diesem sperrigen Konstrukt ist ohne dies über den Haufen zu werfen VBA die brauchbarste Lösung. Es werden alle Tabellenblätter außer Blatt „Gesamt“ die benötigten Werte aufsummiert.

Option Explicit



Sub Auswertung()

    Call Ergebnisse(rngAbfrage:=Tabelle4.Range("C4:C23"), Zielzelle:=Tabelle4.Range("D4"))

End Sub



Sub Ergebnisse(rngAbfrage As Range, Zielzelle As Range)

    Dim wks As Worksheet, arrSum(), rngWerte As Range, arrWks(), i&, j&, k&, iST, lZ&, iFund

    ReDim arrSum(1 To rngAbfrage.Rows.Count, 1 To 9)

    For Each wks In ThisWorkbook.Sheets

        If IsNumeric(wks.Name) Then

            iST = Application.Match("Nation", wks.Columns(2), 0)

            If Not IsError(iST) Then Set rngWerte = wks.Range("B" & iST & ":J" & wks.Rows.Count - iST + 1)

            For i = 1 To rngAbfrage.Count

                iFund = Application.Match(rngAbfrage.Cells(i, 1), rngWerte.Columns(1), 0)

                If Not IsError(iFund) And Not IsError(iST) Then

                    For j = LBound(arrSum, 2) To UBound(arrSum, 2)

                        If j <> 7 Then

                            k = k + 1

                            arrSum(i, j) = arrSum(i, j) + rngWerte.Cells(iFund, k + 1)

                        Else

                            arrSum(i, j) = arrSum(i, 5) - arrSum(i, 6)

                        End If

                    Next j

                    k = 0

                End If

            Next i

        End If

    Next

    Zielzelle.Resize(UBound(arrSum), 9) = arrSum

End Sub

https://www.herber.de/bbs/user/180868.xlsm

Gruß Uwe

Moin Uwe,

vielen, vielen Dank für Deine schnelle Antwort!
Ich habe Deine Datei mit weiteren Jahrgängen gefüllt, funktioniert einwandfrei. Das erleichtert die mühevollen Eingaben ungemein.
Allerdings, was mache ich, wenn ich das Gesamtblatt erweitern möchte? Es sind ja im laufe der Jahre mehr als 20 Mannschaften beteiligt. Einfach die Liste vervollständigen, geht nicht. Ich habe keine Ahnung von VBA - hab's mal gehört, das war's aber auch.
Dazu kommt, das die Gesamtliste im Original noch mehr Spalten beinhaltet. Die kann ich auch nicht so ohne weiteres einfügen. Da muß doch Dein VBA sicher umgeschrieben werden.
Meine angehängte Datei sollte auch nur als Beispiel dienen. Ich hätte dann mit entsprechenden Formeln und Anregungen von Euch weiter gemacht. Wenn man die richtige Formel hat, kann man sich ja rein versetzen um diese dann zu nutzen. Aber VBA - muß ich passen.
Sag mir bitte, was ich machen kann (Deine Option würde ich schon gerne nutzen wollen)!

Gruß Bodo

Hallo Bodo,

um die Mannschaften in der Auswerttabelle flexibel zu halten musst du nur den Prozeduraufruf entsprechend anpassen:

Call Ergebnisse(rngAbfrage:=Tabelle4.Range("C4:C" & Tabelle4.Cells(Rows.Count, 3).End(xlUp).Row), Zielzelle:=Tabelle4.Range("D4"))

Was natürlich dann wo noch anders wird kann ich ohne Einblick in die geänderten Daten in den Tabellen nicht aus der Ferne beurteilen.

Gruß Uwe

Moin Uwe,

danke für Deine Rückmeldung.
Ich hatte Dir geschrieben, das im Original noch mehr Spalten vorhanden sind.
Der erste Teil der Gesamttabelle umfaßt die Ergebnisse bis 1990, der zweite Teil alle Ergebnisse ab 1994.
Es kommen über die Jahre auch noch weitere Länder dazu (C...) und WM (AQ3...).
Auch neue Blätter mit 2030, 2034... kommen dazu.
Darf ich Dir die komplette Mappe zuschicken?

Gruß Bodo

Hallo Bodo,

hochladen, kannst du hier im Forum bis zu einer Dateigröße von 5 MB.

Gruß Uwe

Moin Uwe,

gestern habe ich mich mal über VBA etwas belesen, wie man da rein kommt und was man so machen kann.
Dann habe ich Deine Tabelle genommen und den VBA aufgerufen. Da erschien dann Dein Text, den Du mir geschickt hast. Dort habe ich die zweite Zeile mit der, die Du mir mit der nächsten Antwort geschickt hast, ausgetauscht. Ich habe keine Ahnung, was dieser Text im VBA bedeutet, aber es hat funktioniert. Ich konnte meine Länderliste erweitern. Danke vielmals!
Jetzt hänge ich die andere Datei mit den zusätzlichen Spalten mit an. Wundere Dich nicht über die Zahlen und Formeln im Gesamtblatt, habe nicht mehr alles korrigiert. Du änderst das eh alles mit VBA.

Vielen Dank für Deine Mühe!!!
Gruß Bodo

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

Hallo Bodo,

ich habe mir jetzt die neue Datei angesehen. Du hast im Blatt „Ergebnis“ in der Tabelle eine Unterteilung Spiele bis 1990 und Spiele ab 1994 vorgenommen. Der Teil bis 1990 hat 9 Spalten und ab 1994 hat 6 Spalten (es fehlen 3 Spalten).

Dann fehlen in den Jahren 1994,1998,2002 die Minuspunkte (Spalte J).
Baue bitte die Tabelle(n) bitte in Ruhe vollständig und lade die Datei nochmals hoch.

Gruß Uwe

Moin Uwe,

danke für Deine Antwort.
Folgender Hintergrund steckt hinter diesem ganzen Konstrukt.
Ab 1994 besteht die "Drei-Punkte" Regel. Das heiß, für einen Sieg gibt es 3 Punkte, für ein Unentschieden 1 Punkt. Für eine Niederlage gibt es nichts.
Bis 1990 gab es für einen Sieg 2 Plus-Punkte, für ein Unentschieden je 1 Plus-Punkt und für eine Niederlage 2 Minus-Punkte.
Somit habe ich die Punkte getrennt. Deshalb ist in den Jahresblättern auch keine Spalte „J“. Es gibt nur noch Plus-Punkte.
Die Tore sind davon nicht betroffen. Alle Tore werden weiterhin im Gesamtblatt in Spalte „H und I“ gezählt. Die Differenz ergibt sich automatisch.
Die Spalten „D-G und K, L“ zählen bis 1990, die Spalten „M-Q“ zählen ab 1994. Die Tor-Spalten „H und I“ sollen alle Tore zählen. Sonst müßte ich ab 1994 noch extra Torspalten machen, das wollte ich aber nicht.
Die Spalte „R“ zählt alle Plus-Punkte.

Deine erste Antwort mit dem VBA und der anschließenden Ergänzung funktionieren für die erste Tabelle sehr gut. Nun müßte der zweite Teil (ab 1994) mit eingepflegt werden.

Entschuldige bitte, wenn ich zu viel verlange und es zu kompliziert wird.
Zur Not füge ich noch zusätzliche Tor-Spalten ab 1994 und natürlich noch Gesamttor-Spalten ein.
Das wollte ich allerdings nicht, da die Tabelle in der Breite immer größer und unübersichtlicher wird. Deshalb sollen alle Tore zusammen gezählt werden.

Noch eine Frage: Nach dem Aktualisieren der Gesamttabelle kann ich mit Excel-Bordmitteln (sortieren nach) die Tabelle nach Reihenfolge sortieren oder geht das auch mit VBA?

Vielen Dank für Deine Geduld!
Gruß Bodo

Hallo Bodo,

da die Sache nun doch ganz anders aussieht und ich keine Lust hatte alles von vorn zu bauen habe ich die Selektierung des Auswertungsbereichs mit dem Suchbegriff "VR-Gruppe" angelegt. Schaue bitte in alle Jahresblätter, ob in Spalte A dieser Begriff nur in Spalte A in Höhe der Tabellenköpfe des auszuwertenden Bereichs steht.

Die letzte Spalte kann mit den Formeln so bleiben. Das wäre unsinnig diese in einer weiteren Prozedur zu berechnen.
https://www.herber.de/bbs/user/180879.xlsm

Gruß Uwe

Moin Uwe,

ich könnte mich jetzt schon ärgern, das ich nicht von Anfang an die Tabelle mit allen Spalten eingestellt habe.
Dachte, das Problem ist mit Formeln gelöst und ich hätte den Rest gemacht. Ein bisschen Mitarbeit und Gedächtnistraining meinerseits schadet nicht. Aber mit VBA geht es wirklich einfacher, wenn man es kann. Ich kann es leider nicht.

Ja in Spalte A steht überall die VR-Gruppe (Beinhaltet die Vorrunden-Gruppen - dient nur der Info).

Ich packe nochmal die ergänzte Mappe mit rein.

Gruß Bodo

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

Hi
wo genau hast du denn das Problem mit den Formeln?
prinzipiell sollte sich das ganze nur mit Formeln lösen lassen, so dass du nichts neues programmieren muss.
meiner ansicht nach brauchst du auch die spalten S-Y nicht, du könntest auch die Spalten D-F direkt auswerten.

Was aber den Ablauf stört (egal ob mit Formeln oder VBA) ist dein Strukturwechsel für die Final-Spiele, denn hier stehen die Mannschaftsnamen nicht mehr vor den Ergebnissen, sondern über den Ergebnissen.

Würdest du hier die Mannschaftsnamen ebenfalls in die gleiche Zeile bringen und vor die Ergebnisse stellen wie den Gruppen- und Finalspielen, könntest du das per Formel (ZählenWenns) auswerten und bräuchtest dann die zusätzlichen Spalten nicht.

ich würde dann auch eher die Zusammenfassungstabelle, die du jetzt unterhalb der Ergebnisliste stehen hast, neben diese stellen (also die ab Spalte S), das hätte für dich den Vorteil, dass du die Formeln einfacher schreiben kannst (Zählen- und SummeWenns mit ganzen Spalten als Bezug) und dass diese Tabelle immer an der selben Stelle steht, was dir die Zusammenfassung erleichtern würde.

Gruß Daniel

Moin Daniel,

die Variante mit VBA finde ich sehr interessant, nur habe ich keine Ahnung davon.
Ich werde Deine Anregungen mal durchspielen, vielleicht komme ich dann weiter.
Hab vielen Dank erstmal.

Gruß Bodo

Moin Daniel,

auch an Dich nochmals meinen Dank für Deine Bemühungen!
Ich habe mir die Datei nochmal angeschaut und alles neu gemacht.
Alle Endtabellen in den Blättern bis 2022 sind aktuell. Ich habe auch die Spiele um Platz 3 und das Endspiel angepaßt. Das Gesamtblatt ist für die ersten 20 Mannschaften aktuell.
Die Eingaben von gew/unent/verl im Gesamtblatt für viele weitere Mannschaften ist sehr mühselig. Wenn Du auf eine der Zellen gehst, siehst Du was ich meine.
Klar, kann man machen, aber für noch mehr Mannschaften und auch Folgeblätter (2026, 2030...) wird der Aufwand immer größer.
Daher meine Anfrage am Anfang, ob es nicht einfacher und automatischer geht.
Vielleicht kannst Du mit der neuen Datei mehr anfangen! Danke.

Gruß Bodo

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

Hallo Bodo,

Das was ich dir zuletzt eingebaut hatte scheint also zu passen. Die Formel Blatt "Gesamt" in Zelle D5 (Spiele bis 1990) gibt die Summe aller Spiele aus (korrekt wäre 9 Spiele statt 30). Dies widerspricht der Überschrift.

Da du keine Probleme mit der Prozedur gemeldet hast, gehe ich davon aus, dass es so passt.

Gruß Uwe

Moin Uwe,

so wie Du den VBA vom 3.7. und der Ergänzung vom 5.7. programmiert hast, ist alles topp.
Ich kann weitere Länder (Spalte B) im Gesamtblatt hinzufügen und auch neue Jahresblätter. Nach dem "Tabelle aktualisieren" wird alles automatisch ausgefüllt - bis 1990.
Ab 1994 funktioniert es nicht mehr. Die Spalten M-R fehlen in Deiner Programmierung. Hatte ich Dir geschrieben wegen der 3-Punkte Regel.
Vielleicht kannst Du die mit einbauen, ohne alles von neuem zu machen.

In Deiner Antwort vom 7.7. hast Du in der Datei 180879.xlsm was programmiert? Ich habe sie geöffnet, da fehlt wahrscheinlich der Button "Tabelle aktualisieren".
Ich kann nichts machen, die Datei ist so, wie ich sie hochgeladen habe.

Gruß Bodo

Habe die Letzte Datei um die Jahre 1930-1938 ergänzt. Auch habe ich die Spalte D im Gesamtblatt aktualisiert.
https://www.herber.de/bbs/user/180888.xlsx

Hallo Bodo,

ich habe anhand von Brasilien (Zeile 5) die Ausgaben in deiner zuletzt hochgeladene Datei im Blatt "Gesamt" mit der Ausgabe via VBA verglichen. Einzig Tore+ (Spalte H) und Tore- (Spalte I) werden per VBA bis < 1994 ausgegeben, was ja wohl aus deinen ursprünglichen Aussagen korrekt wäre und in deinen Formeln falsch abgebildet wird.
Ansonsten wird der Bereich >= 1994 (Werte an Spalte M:Q) korrekt ausgegeben (identische Ausgaben). In Spalte R hatte ich ja geschrieben wird einfach die Formel von dir weiterverwendet.

Schau dir dies in Ruhe an.

Falls die Spalte H und I für alle Jahre gelten soll, muss ich es entsprechend umbauen.

Wichtig ist um solche Sachen aus der Ferne korrekt bauen zu können sind vollumfänglich aussagefähige Beispieldaten (gilt auch für Ergebnisse) + klare Aussagen, was nicht stimmig ist.
Das war und ist auch das Problem an deinen Beispieldateien gewesen, was mich leider eine Lösung raten/vermuten lässt (siehe Spalte D Spiele bis 1990).

Gruß Uwe

Moin Uwe,

danke für Deine Ausführungen.
Ich schau mir das alles nochmal in Ruhe an.
Das nächste Mal sende ich Dir die vollständige überarbeitete Datei zu.

Gruß Bodo

Hallo Bodo,

anbei VBA-Lösung.

Ich habe in jeder Tabelle (Jahre) in Spalte A am Kopf des auszuwertenden Bereichs den Begriff "Auswertung" gesetzt. Dies ist erforderlich, da es kein brauchbares einmaliges Merkmal mehr in den Jahren gab. Das musst du beim Erzeugen eines neuen Tabellenblattes an der Stelle stets setzen.

Des Weiteren habe ich noch eine UDF für die Tabelle ab Gesamt!T5 eingebaut. Somit braucht es keine angepassten Formeln mehr. Es reicht rüber und runterziehen.

Option Explicit



Function TeilnahmeImWMJahr(Jahr As String, rng_Werte As String, Land As String) As Long

    TeilnahmeImWMJahr = Application.WorksheetFunction.CountIf(ThisWorkbook.Worksheets(Jahr).Range(rng_Werte), Land)

End Function



Sub Ergebnisse()

    Dim wks As Worksheet, arrSum(), rngWerte As Range, rng_Land As Range, i&, j&, k&, l&, iST, iFund

    Set rng_Land = Tabelle15.Range("C5:C" & Tabelle15.Cells(Rows.Count, 1).End(xlUp).Row)

    ReDim arrSum(1 To rng_Land.Rows.Count, 1 To 15)

    For Each wks In ThisWorkbook.Sheets

        If IsNumeric(wks.Name) Then

            iST = Application.Match("Auswertung", wks.Columns(1), 0)

            If Not IsError(iST) Then Set rngWerte = wks.Range("B" & iST & ":J" & wks.Cells(Rows.Count, 2).End(xlUp).Row)

            For i = 1 To rng_Land.Count

                j = j + 1

                iFund = Application.Match(rng_Land.Cells(i, 1), rngWerte.Columns(1), 0)

                If Not IsError(iFund) And Not IsError(iST) Then

                    If wks.Name < "1994" Then

                        arrSum(j, 1) = arrSum(j, 1) + rngWerte.Cells(iFund, 2)

                        arrSum(j, 2) = arrSum(j, 2) + rngWerte.Cells(iFund, 3)

                        arrSum(j, 3) = arrSum(j, 3) + rngWerte.Cells(iFund, 4)

                        arrSum(j, 4) = arrSum(j, 4) + rngWerte.Cells(iFund, 5)

                        arrSum(j, 5) = arrSum(j, 5) + rngWerte.Cells(iFund, 6)

                        arrSum(j, 6) = arrSum(j, 6) + rngWerte.Cells(iFund, 7)

                        arrSum(j, 7) = arrSum(j, 5) - arrSum(j, 6)

                        arrSum(j, 8) = arrSum(j, 8) + rngWerte.Cells(iFund, 8)

                        arrSum(j, 9) = arrSum(j, 9) + rngWerte.Cells(iFund, 9)

                    Else

                        arrSum(j, 10) = arrSum(j, 10) + rngWerte.Cells(iFund, 2)

                        arrSum(j, 11) = arrSum(j, 11) + rngWerte.Cells(iFund, 3)

                        arrSum(j, 12) = arrSum(j, 12) + rngWerte.Cells(iFund, 4)

                        arrSum(j, 13) = arrSum(j, 13) + rngWerte.Cells(iFund, 5)

                        arrSum(j, 5) = arrSum(j, 5) + rngWerte.Cells(iFund, 6)

                        arrSum(j, 6) = arrSum(j, 6) + rngWerte.Cells(iFund, 7)

                        arrSum(j, 7) = arrSum(j, 5) - arrSum(j, 6)

                    End If

                End If

            Next i

            j = 0

            k = 0

            l = 0

        End If

    Next

    For i = LBound(arrSum) To UBound(arrSum)

        arrSum(i, 14) = (arrSum(i, 11) * 3) + arrSum(i, 12)

        arrSum(i, 15) = arrSum(i, 8) + arrSum(i, 14)

    Next i

    Tabelle15.Cells(5, 4).Resize(UBound(arrSum), 15) = arrSum

End Sub

UDF Aufruf in T5 (runter- und rüberziehen):

=TeilnahmeImWMJahr(T$3;"$AA$1:$AA$102";$C5)

https://www.herber.de/bbs/user/180915.xlsm

Gruß Uwe

Moin Uwe,

ich bin sprachlos und völlig begeistert. So habe ich mir die ganze Sache vorgestellt.
Ich habe weitere Mannschaften durch kopieren hinzugefügt. Selber reinschreiben in die Zellen geht nicht, dann stürzt Excel ab. Ist aber ok, dann kopiere ich halt.
Hinten die Jahre durch runter- und rüber ziehen ergänzt, vorne die Teilnahmen runter gezogen und dann aktualisiert - TOPP.

Hab vielen, vielen Dank für Deine Bemühungen, Deine Hilfe und Deine Zeit.
Alleine hätte ich das nie hinbekommen.

Vielleicht schreibt man sich mal wieder!
Wünsche Dir einen schönen Sommer und eine angenehme Zeit!

Gruß Bodo

Hallo Bodo,

wo fügst du weitere Mannschaften ein, so dass Excel abschmiert?
Ich habe mal in Blatt "Gesamt" in Spalte C eine weitere Mannschaft (Bolivien) händisch eingefügt und es schmiert nichts ab.

Gruß Uwe

Moin Uwe,

im "Gesamtblatt" unter C.
Habe es eben auch nochmal probiert, ist nicht's abgeschmiert.
Ich beobachte das mal, wenn dann melde ich mich noch mal.

Gruß Bodo

Moin Uwe,

so, ich habe mir die Datei nochmal angeschaut und alles neu gemacht.
Alle Endtabellen in den Blätter bis 2022 sind aktuell. Das Gesamtblatt ist für die ersten 20 Mannschaften aktuell. Die Eingaben von gew/unent/verl für viele weitere Mannschaften ist sehr mühselig. Wenn Du auf eine der Zellen gehst, siehst Du was ich meine.
Klar, kann man machen, aber für noch mehr Mannschaften und auch Folgeblätter (2026, 2030...) wird der Aufwand immer größer.
Daher meine Anfrage am Anfang, ob es nicht einfacher und automatischer geht.
Ich finde die Sache mit VBA nicht schlecht, es vereinfacht vieles.
Entschuldige nochmals, das meine erste Datei nicht vollständig war!

Danke für Deine Bemühungen und Deine Zeit!

Gruß Bodo

PS.: Die VR-Gruppen habe ich alle entfernt. Sie dienten nur zur Info für mich, welche Mannschaft in welcher Vorrundengruppe gespielt hat.


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