Spezielles Problem mit "fehlenden Daten"

Hallo!
Habe ein ganz spezielles Problem. Ich habe eine riesige Datenmenge einer Statistikseite, aus der ich Daten grafisch aufbereiten will.
Die Tabelle ist wie folget aufgebaut:
Spalte 1: Kalenderwochen seit dem Jahr 2000 (Format: KALW-JJJJWW)
Spalte 2: Bundesländercode für 9 verschiedene Bundesländer (Format: B00-X; X steht für 1 bis 9)
Spalte 3: Altercode für 20 Altersgruppen (Format: ALTER5-X; X steht für 1 bis 20)
Spalte 4: Geschlechtscode für männlich und weiblich (Format: C11-X; X steht für 1 (männlich) oder 2 (weiblich)
Spalte 5: ein Wert (Zahl zwischen 1 und maximal 90)
Mein Problem: Die Daten sind so gemacht, dass nur für die Gruppen eine Zeile angelegt ist, bei denen ein Wert der über 0 vorhanden ist. Theoretisch müsste es pro alenderwoche und Bundesland 40 (20 Altersgruppen mal zwei Geschlechter) Zeilen geben.
Um Grafiken zu erstellen, brauche ich auch für jede einzelne dieser Alters- und Geschlechtergruppen eine Zeile.
Mir fehlen aber ganz viele Zeilen, weil ganz oft in einzelnen Altersbereichen oder auch manchmal nur bei einem geschlecht davon die Zeile fehlt, da es eine mit dem Wert "0" wäre.
Kann ich das irgendwie beheben? Derzeit sind das Ganze 234.560 Zeilen - es fehlen also ganz ganz viele, da es insgesamt über 374.000 sein müssten (52 Wochen mal 9 mal 20 mal 2, dazu kommen noch einige wenige Jahre mit 53 Kalenderwochen).
Freu mich über Hilfen - ich kann, wenn es das vereinfacht, die einzelnen Bundesländer auch gleich in einzelnen Tabellenblättern anlegen, da ich sie nicht zusammen brauche sondern einzeln.
Hoffe, das war verständlich so...
P.S.: Verwende die neueste Excel 365 Version Version 16.0.13426.20270...

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi
naja, das war nicht eine Klammer zu wenig, sondern eine zuviel.
hier mal die vollständige Lösung:
als Vorbereitung
1. trenne wie von dir vorgeschlagen die Liste nach einzelnen Bundesländern auf
2. ermittle den Donnerstag der ersten Kalenderwoche der Auswertung
3. berechne dir, wieviele Zeilen pro KW benötigt werden (Geschlechtergruppen * Altersgruppen) = 40
4. berechne dir, wieviele Zeilen jede Tabelle insgesamt benötigt (Anzahl KWs * Geschlechtergruppen * Altersgruppen)
die Anzahl der KWs kannst du dir so berechnen lassen : =(Enddatum - Startdatum) / 7
Lege dir jetzt die Dummyliste an.
mit folgenden Formeln:
A1: mit 40 als Anzahl der Zeilen pro KW und als Datum den Donnerstag der erstn KW (2x eintragen)

="KALW"&JAHR(DATUM(2000;1;6)+7*QUOTIENT(ZEILE(A1)-1;40))&TEXT(KALENDERWOCHE(DATUM(2000;1;6) +7*QUOTIENT(ZEILE(A1)-1;40);21);"00")

B1: das Bundesland als Fixwert
C1:


="Alter5-"&Quotient(Rest(Zeile(A1)-1;40);2)+1

D1:


="C11-"&REST(ZEILE(A1)-1;2)+1

E1: Festwert 0
diese Formeln ziehst du dann soweit wie benötigt nach unten.
zur Überarbeitung der einzelnen Bundeslandtabellen gehst du dann so vor:
1. Trage in Spalte B den richtigen Bundesland-code ein (in erste Zelle eintragen und Doppelklick auf rechte untere Ecke der Tabelle)
2. kopiere diese Tabelle und füge sie ALS WERT unterhalb der jeweiligen Bundesland-Tabelle ein
3. markiere die gesamte Tabelle und wende die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN auf die ganze Tabelle an, verwende die Spalten A-D als Kriterium
nach diesem Schritt sollten im unteren Teil der Tabelle alle Zeilen gelöscht werden die im oberen Teil vorhanden sind und die die im oberen Teil fehlen, bleiben erhalten
4. ggf durch Sortieren in die richtige Reihenfolge bringen.
das ganze würde auch für die gesamttabelle mit allen Bundesländern funktionieren, aus der Formel von C1 kannst du dir ableiten, wie die Formel für B1 aussehen muss.
Gruß Daniel

Sub F_en() Dim DD As Object: Set DD = CreateObject("Scripting.Dictionary") Data = Cells(1, 1).CurrentRegion ReDim IX(UBound(Data)) 'Index For i = 2 To UBound(Data) Alter = Split(Data(i, 3), "-")(1) IX(i - 2) = Right(Data(i, 1), 6) & Right(Data(i, 2), 1) & _ Format(Alter, "00") & Right(Data(i, 4), 1) Next i 'nur zur Prüfung 'Cells(2, "G").Resize(UBound(IX)) = Application.Transpose(IX) 'Schleife über alle Möglichkeiten For Y = 2000 To 2000 '2020 For KW = 1 To 52 For BL = 1 To 9 For Alt = 1 To 20 For Gn = 1 To 2 all = all + 1 iIndex = Y & Format(KW, "00") & BL & Format(Alt, "00") & Gn If IsError(Application.Match(iIndex, IX, 0)) Then DD.Item(iIndex) = _ vbNullString Next Gn Next Alt Next BL Next KW Next Y Debug.Print "alle", all, "Dic.count", DD.Count, all - DD.Count Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(DD.Count) = Application.Transpose(DD.keys) Set DD = Nothing End Sub

Option Explicit ' Im Beispielcode wird nur ein Bundesland und nur das Jahr 2000 verarbeitet. ' Mit weiteren Schleifen und einer Berechnung der Kalenderwochen (52 bzw. 53) ' kann das Makro ausgebaut werden. Private Sub Daten_anordnen() Dim Daten() As Variant, vohandeneDaten() As Variant Dim anzDaten As Long, n As Long, xJahr As Long, Zähler As Long Dim lzDaten As Long, xWoche As Long, xAlter As Long, xGeschlecht As Long Dim TMP As String ' Alle Daten aus 'Kopie von OGD_rate_kalwo_GEST_K' With Sheets("Kopie von OGD_rate_kalwo_GEST_K") ' Letzte Zeile mit Daten der Spalte A lzDaten = .Cells(.Rows.Count, 1).End(xlUp).Row ' Bereich in Array 'alleDaten' einlesen, Erstes Element ist dann vohandeneDaten(1,1) vohandeneDaten = .Range("A2:E" & CStr(lzDaten)).Value End With ' Wieviele Daten sind vorhanden anzDaten = UBound(vohandeneDaten) - LBound(vohandeneDaten) + 1 ' Größe des benötigten Arrays festlegen ( 52 (Wochen) x 20 (Alter) x 2 (Geschlecht) ) ' Spalte A bis Spalte E ReDim Daten(1 To 2080, 1 To 5) ' Jahr festlegen xJahr = 2000 For xWoche = 1 To 52 ' 52 Wochen eventuell auf 53 ändern For xAlter = 1 To 20 ' Alter von 1 bis 20 For xGeschlecht = 1 To 2 ' Geschlecht 1 oder 2 Zähler = Zähler + 1 ' Daten erezugen, auch wenn sie nicht vorhanden sind, Anzahl auf 0 srtellen If xWoche

Beliebteste Forumthreads (12 Monate)

Spezielles Problem mit "fehlenden Daten"

23
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Spezielles Problem mit "fehlenden Daten"

23 Beiträge zum Forumthread Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

23
Beiträge zum Forumthread

Beiträge zu diesem Forumthread