Office: (Office 2016) Tabellen zusamenkürzen

Hallo allerseits,

ich muss eine riesige Jahrestabelle mit hunderten Zeilen und eine Spalte pro Tag des Jahres regelmäßig auf bestimmte Inhalte durchsuchen und mir diese Inhalte dann 'rausschreiben. Ich möchte das irgend wie automatisieren.


So ist die Tabelle aufgebaut: Es gibt mehrere Gruppen, die an manchen Tagen im Jahr bestimmte Kurse haben. Hier z.B. "Kurs A" und "Kurs B". Einige dieser Kurse gehen über mehrere Tage.


.........................01.01.2021.....02.01.2021.....03.01.2021.....04.01.2021.....05.01.2021
Gruppe 1..........Kurs A.......................................Kurs B..................................................
Gruppe 2............................................................................................................................
Gruppe 3..............................................................Kurs A...............Kurs A.........................
Gruppe 4...........................................................................................................................
Gruppe 5...........................................................................................Kurs B.........................


Ich würde nun gerne automatisch eine 2 Tabelle erstellen lassen, die nur aus Zeilen mit Gruppen besteht, die irgend wann im Jahr einen der beiden Kurse haben und es sollen nur Spalten angezeigt werden, an denen diese Kurse stattfinden.

So zum Beispiel:

.........................01.01.2021.....03.01.2021.....04.01.2021
Gruppe 1..........Kurs A............Kurs B
Gruppe 3...................................Kurs A...............Kurs A
Gruppe 5...............................................................Kurs B


Ist das irgend wie möglich, und wenn ja, wie? Ich habe schon gegoogelt und mir Videos angeschaut, aber bisher hat mir da nichts weitergeholfen. Vielleicht hat ja hier einer eine Idee. Ich würde mich riesig freuen!

Viele Grüße

 

Hola,

lade bitte eine Exceldatei hoch.

Gruß
steve1da

 

Hallo steve1da,
anbei die gewünschte Tabelle.
Gruß

 

Hi,

ich nehme an, außer deiner Tabelle befinden sich keine weiteren Inhalte im Tabellenblatt? Dann ist der einfachste Weg, das Tabellenblatt zu kopieren und in der Kopie dann die betreffenden Zeilen/Spalten zu löschen. Dazu kannst du folgendes Makro verwenden:

Code:

Sub Zusammenfassen()
    Dim intSpalte As Integer
    Dim lngZeile As Long
    Dim intLetzte As Integer
    Dim lngLetzte As Long
    Application.ScreenUpdating = False
    ActiveSheet.Copy after:=ActiveSheet
    With ActiveSheet
        lngLetzte = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        intLetzte = .Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        For lngZeile = lngLetzte To 2 Step -1
            If Application.CountA(.Range(.Cells(lngZeile, 2), .Cells(lngZeile, intLetzte))) = 0 Then .Cells(lngZeile, 1).ClearContents
        Next lngZeile
        For intSpalte = intLetzte To 2 Step -1
            If Application.CountA(.Range(.Cells(2, intSpalte), .Cells(lngLetzte, intSpalte))) = 0 Then .Cells(1, intSpalte).ClearContents
        Next intSpalte
        .Range(.Cells(2, 1), .Cells(lngLetzte, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Range(.Cells(1, 2), .Cells(1, intLetzte)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    End With
    Application.ScreenUpdating = True
End Sub

---

 

@Beverly
Doch, die ganze Tabelle ist leider voll mit dutzenden Kursen für ca. 300 Gruppen. Mich interessieren aber nur 5 Kurse davon. Ich würde also gerne die Namen der drei Kurse irgend wo eintragen wollen und mir dann eine neue Tabelle erstellen lassen, die mir anzeigt, welche Gruppen wann einen der drei Kurse haben.
Grüße

 

Moin!
Entpivotiere Deine Tabelle mittels Daten, abrufen und transformieren (Power Query).
Diese Tabelle kannst Du dann mittels Datenschnitt oder simplem Filter nach Deinen Wünschen anzeigen lassen.
Datei überarbeitet zurück.

Gruß Ralf

 

Hi,

ausgehend von deiner hochgeladenen Arbeitsmappe (die ich erst gesehen habe als ich meinen letzten Beitrag bereits abgeschickt hatte) macht mein Code genau das was du möchtest - nur eben auf einem neuen Tabellenblatt.

---

 

@RPP63 : Oh jeh, das sind für mich erst mal böhmische Dörfer. Wenn nicht weiter hilft, werde ich mich das aber näher mit befassen. Danke erst mal.

@Beverly : Ah, oki. Weil ich mich mit Macros noch nicht so richtig auskenne: Ich nehme an, ich muss bei
.Cells.Find(What:="*",
das "*" mit meinem Suchtext befüllen. Das teste ich gleich mal. Hab noch nie nen Macro geschrieben, aber irgend wann ist ja immer das erste Mal :-)
Schon mal vielen Dank.

 

@Beverly : Makro erstellen hat geklappt. Beim Ausführen wird auch ein 2. Blatt erstellt, auf dem allerdings nicht sinnvolles steht. Viele Rauten und immer ein #Bezug! - Fehler. Muss ich da vorher dieses von RPP63 genannte "Entpivotieren" durchführen?

 

Wenn ich eine neue Test-Tabelle erzeuge und mit deinem Makro versehe, kommt folgender Fehler:


Ein googeln dieses Fehlercodes brachte mich auf Seiten, die ich nicht verstehe :-)



Anbei meine Test-Tabelle.
Ich fürchte, mein Excel-Wissen reicht für die Art von Aufgabe nicht aus :-(

 

Du hast 1. Veränderungen im Code vorgenommen, die falsch sind, 2. ist deine Tabelle anders aufgebaut als in deiner zuerst hochgeladenen Mappe: dort waren die Zellen leer und es sollten Spalten/Zeilen entfernt werden, die nicht komplett leer sind und 3. soll jetzt nach einem speziellen Kurs gesucht werden - das ist eine völlig andere Aufgabenstellung. Hier der angepasste Code mit einer Abfrage, nach welchem Kurs "gefiltert" werden soll:

Code:

Sub Zusammenfassen()
    Dim intSpalte As Integer
    Dim lngZeile As Long
    Dim intLetzte As Integer
    Dim lngLetzte As Long
    Dim varKurs
    varKurs = Application.InputBox("Bitte Kurs eingeben", "Nach Kurs filtern")
    If varKurs <> "Falsch" And varKurs <> "False" Then
        Application.ScreenUpdating = False
        ActiveSheet.Copy after:=ActiveSheet
        With ActiveSheet
            lngLetzte = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            intLetzte = .Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            For lngZeile = lngLetzte To 2 Step -1
                If Application.CountIf(.Range(.Cells(lngZeile, 2), .Cells(lngZeile, intLetzte)), "Kurs 1") = 0 Then .Cells(lngZeile, 1).ClearContents
            Next lngZeile
            For intSpalte = intLetzte To 2 Step -1
                If Application.CountIf(.Range(.Cells(2, intSpalte), .Cells(lngLetzte, intSpalte)), "Kurs 1") = 0 Then .Cells(1, intSpalte).ClearContents
            Next intSpalte
            .Range(.Cells(2, 1), .Cells(lngLetzte, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            .Range(.Cells(1, 2), .Cells(1, intLetzte)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
        End With
        Application.ScreenUpdating = True
    End If
End Sub

---

 

@Beverly : HA ! :-) Also zumindest in meiner Beispieltabelle klappt es. Morgen früh werde ich es in der Monster-Tabelle ausprobieren.
Danke für deine Mühe !!!

 

Guten Morgen.
Ich probiere seit 06:30 Uhr rum, aber ich kommen nicht weiter. Solltest Du noch etwas Zeit und Nerven haben, würde ich mich freuen.

1)
Die Tabelle wird nur nach "Kurs 1" gefiltert. Egal, was ich eingebe. Ich schätze, weil das in deinem Code auch so verankert ist. Habe versucht, das irgend wie umzuschreiben, bin aber gescheitert.

2)
Gibt es eine Möglichkeit, nicht nur die oberste Zeile und die erste Spalte in der neu erstellten Tabelle anzeigen zu lassen, sondern die obersten 6 Spalten und ersten 5 Zeilen? Die eigentlichen Kurse gehen nämlich erst ab Zelle H6 los.

3)
Ist es möglich, mit dem Makro auch mehrere verschiedene Kurse zu suchen? Dass ich also z.B. sowohl nach Kurs 1 als auch nach Kurs 2 suche? Und in der neuen Tabelle werden mir beide Kurse anzeigt? Das muss nicht zwingend mit einer Abfrage erfolgen. Kann auch fest im Makro verankert sein. Ich suche immer dieselben Kurse.

Wenn Du dafür keinen Nerv mehr hast, dann würde ich das aber auch gut verstehen. Ist halt sehr speziell mein Anliegen :-)

Viele Grüße und einen schönen Tag

 

Moin!
Warum beschäftigst Du Dich nicht kurz mit meiner obigen Lösung, ist ja schließlich keine Hexerei und gerade für einen Anfänger leichter zu verstehen als VBA!
Dein Problem ist, dass Du die Datenhaltung in einer Kreuztabelle KT (ähnlich wie ein Stundenplan) durchführst.
Eine KT eignet sich hervorragend für einen übersichtlichen Ausdruck, als Grundlage für eine Auswertung jedoch (wie Du siehst) eher nicht!
Deshalb:

• markiere irgendeine Zelle in der KT A1:F6
• wähle Daten → Daten abrufen → aus Tabelle
• jetzt öffnet sich der PQ-Editor
• markiere die Spalten 2 bis Ende
• wähle Transformieren → Spalten entpivotieren
• optional: Spalte Attribut → Datentyp auf Datum setzen
• Datei → Schließen und laden

Jetzt hast Du die problemlos auswertbare Tabelle, die ich oben angehängt habe.
(ich hatte zusätzlich Tabellenentwurf → Datenschnitt einfügen → Spalte 1 gewählt)
Ich habe dafür weniger als eine halbe Minute gebraucht.
Ich bin ebenfalls ein Fan von VBA, hätte aber ein vielfaches an Zeit invenstieren müssen.

Gruß Ralf

 

Hi,

möglich ist vieles und dein Anliegen ist auch nicht speziell, sondern das Problem liegt darin, dass du mit deinen hochgeladenen Mappen Angaben geliefert hast, die nicht dem Originaltabellenaufbau entsprechen - lade also eine Mappe mit den TATSÄCHLIEN Bedingungen hoch: genauer Tabellenbaufbau und genaue Angabe wonach gesucht werden soll.

---