Paneldaten- Aufbereitung Datensatz

[Felixk]

Hallo liebe Clever-Excel-Community  ,

wie dem Anhang zu entnehmen habe ich es mit einem größeren Paneldatensatz zu tun (hier ist nur ein exemplarischer Ausschnitt hochgeladen). Durch verschiedene Filterfunktionen konnte ich bisher einen erheblichen Anteil an Daten reduzieren. Ich habe folgende Fragen:

- Zelle AOZ: Wie kann ich den ausgegebenen Namen (z.B. "ADMC Absolute Return Strategie LP" in Spalte B suchen lassen und in einer neuen Spalte ausgeben in welcher Zelle in Spalte B sich dieser befindet?

- Missing Values: in F4:TN8 sind die monatlichen Renditen 5 verschiedener Aktien dargestellt. Diese sind nicht vollständig, lückenhaft und sollten unter bestimmten Voraussetzungen folgend bearbeitet werden:

•  Wenn nur ein Wert in einer Zeitreihe (z.B. F4:TN4) fehlt, soll der fehlende Wert durch den Durchschnitt aller anderen Aktien in diesem Monat gebildet werden.
  
• Wenn mehr als X Werte fehlen, soll die Zeitreihe gelöscht oder farblich kodiert werden
  

-  Löschen der ersten 12 Beobachtungen (12 Monate) wenn der Fonds anfängt zu "berichten" (wenn die ersten Werte auftauchen) wie z.B. in Zeile 7: hier fängt der Fonds in Spalte MX an zu berichten, die Werte MX7:NV7 sind jedoch nicht erwünscht und sollten gelöscht werden.

- In der exemplarischen Excel-File gibt es mehrere leere Spalten, welche nicht mehr benötigt werden. Wie kann man diese löschen?


Mir ist bewusst, dass diese Fragen wahrscheinlich einzeln in verschiedenen Foren zu stellen sind und unter einem Themenbegriff "Paneldaten" möglicherweise keinen Sinn machen. Ich beschäftige mich mit oben genannten Problemen im Rahmen einer wissenschaftlichen Arbeit und möchte den Datensatz aufbereiten bevor ich mit einem statistischen Auswerteprogramm State weiterverarbeiten kann. Der vollständige Datensatz meiner Arbeit beinhaltet ca. 11000 Zeilen bei gleicher Anzahl von Spalten. Ich würde mich von meinem Excel-Wissen als "Intermediate" bezeichnen. Das Posten eines Makros würde mir sehr wahrscheinlich nicht weiterhelfen- zumindest nicht ohne Erklärung.


Ich wäre sehr sehr dankbar, wenn mir jemand zum Teil oder im Idealfall zu allen Problemstellungen eine erste Hilfe anbieten könnte- Ich verlange keine Komplettlösung der geschilderten Probleme sondern probiere mich selbstverständlich selbst daran bevor ich weitere Fragen stelle.

Danke schon einmal für die Aufnahme im Forum und eure Hilfe.

[Fennek]

Hallo,

ohne die Daten gesehen zu haben, die angefragten "data cleaning" sollten per Makro vergleichsweise einfach zu machen sein, aber jemandem ohne Makro-Kenntnisse einen Code zu schicken, geht oft daneben. Frage, möchtest du dich in VBA-Makros so weit einarbeiten, damit du mit vorgeschlagenen Codes arbeiten kannst, einschließlich kleinerer Änderungen und debugging?

mfg

[gbr]

Hallo,

der erste Teil ist simpel:   =ADRESSE(VERGLEICH(AOZ5;B:B;0);2)
In AOZ5 ist der gesuchte Text und in der Spalte B (B:B) wird danach gesucht.

Dann wird's schwierig:
Die nächsten Punkte sind wahrscheinlich nur über Makros zu lösen.


Leere, nicht mehr benötigte Spalten:

Du könntest in einer Hilfszeile die Leerzellen zählen:   =WENN(ANZAHLLEEREZELLEN(GV4:GV19)=16;"";ANZAHLLEEREZELLEN(GV4:GV19))
Wenn die Zellen alle leer sind bekommst du "" zurück.
Wenn du die Zeile markierst und über "Suchen und Auswählen,   Gehe zu...   Inhalte...   Leerzellen   gehst, werden alle Zellen ohne Inhalt markiert.
Du kannst jetzt die Spalten löschen.

Über ein Makro geht auch das "vornehmer"

Ansonsten müsstest du die Kriterien, welche Spalten überflüssig sind, genauer definieren.
Ich würde auch die ganzen Leeren Spalten (Wahrscheinlich als "Abstandshalter" gedacht) löschen.

Hinweis:
Wenn du die Daten der ersten 12 Monate löscht musst du sicherstellen, dass die nächsten 12 Monate nicht beim nächsten Durchlauf des Makros gelöscht werden.

Gruß Günter

[Felixk]

Hallo,

ohne die Daten gesehen zu haben, die angefragten "data cleaning" sollten per Makro vergleichsweise einfach zu machen sein, aber jemandem ohne Makro-Kenntnisse einen Code zu schicken, geht oft daneben. Frage, möchtest du dich in VBA-Makros so weit einarbeiten, damit du mit vorgeschlagenen Codes arbeiten kannst, einschließlich kleinerer Änderungen und debugging?

mfg

Hallo Fennek,

vielen Dank für deine Antwort. Ich möchte und ich muss. 

Grüße,

Felix

[Fennek]

Hallo,

um die leeren Spalten zu löschen, hilft dieser Code:

Code:

Sub test()
Dim LastCell As Range
With ActiveSheet.UsedRange
Set LastCell = .SpecialCells(11)
SP = LastCell.Column
LR = LastCell.Row
For c = SP To 1 Step -1
AB = WorksheetFunction.CountA(Range(Cells(1, c), Cells(LR, c)))
   If AB = 0 Then
   Cells(9, c).Interior.Color = vbYellow
   'Columns(c).EntireColumn.Delete 'erst nach dem Test aktivieren
   End If
Next c
End With
End Sub

Die zu löschenden Spalten werden zuerst farblich markiert, wenn alles geprüft ist, kann der Lösch-Befehl aktiviert werden.

mfg

PS: Die Datenstruktur ist ... (ungünstig). Hedge-Fonds sollten aber für solche Aufgaben einen Dienstleister beauftragen und nicht auf kostenlose Unterstützung setzen, so sympathisch sind "Heuschrecken" auch nicht.

[Felixk]

Über ein Makro geht auch das "vornehmer"

Ansonsten müsstest du die Kriterien, welche Spalten überflüssig sind, genauer definieren.
Ich würde auch die ganzen Leeren Spalten (Wahrscheinlich als "Abstandshalter" gedacht) löschen.

Hinweis:
Wenn du die Daten der ersten 12 Monate löscht musst du sicherstellen, dass die nächsten 12 Monate nicht beim nächsten Durchlauf des Makros gelöscht werden.

Gruß Günter

Hallo Günter,

Danke für die erste Formel- hat super geklappt. Hier muss ich dann im nächsten Schritt in der ausgegebenen gefundenen Zelle ( hier: B6 bestimmte Zeitreihenwerte  zu einem bestimmten  Zeitpunkt löschen. Auch hier wird ein Makro Abhilfe schaffen. 

Zu deinem nächsten Punkt : Diese Formel müsste praktisch in der untersten Zeile eingepflegt werden und von F4:TN8 für jede Spalte von F - TN , richtig?

Gruß,

Felix

[Felixk]

Die zu löschenden Spalten werden zuerst farblich markiert, wenn alles geprüft ist, kann der Lösch-Befehl aktiviert werden.

mfg

PS: Die Datenstruktur ist ... (ungünstig). Hedge-Fonds sollten aber für solche Aufgaben einen Dienstleister beauftragen und nicht auf kostenlose Unterstützung setzen, so sympathisch sind "Heuschrecken" auch nicht.

Ich werde das Makro heute Mittag sofort ausprobieren und es versuchen nachzuvollziehen. Was meinst du mit der Datenstruktur? Wäre dein Vorschlag, die monatlichen Renditen zu transponieren? Ich habe ja 12000 verschiedene Hedge Funds und dachte es ist günstiger wenn die Matrix nach unten wächst. Ich arbeite im Rahmen einer wissenschaftlichen Arbeit an diesem Thema. Symphatisch sind die nicht-da hast du Recht.aber interessant!