Excel Makros?
Ich habe folgendes Problem in Excel:
Zur Datenanalyse in der Arbeit bekomme ich regelmäßig einen exportierten Exceldatensatz zur Verfügung. Das Ganze ist natürlich viel komplizierter, aber vom Prinzip her sieht das so aus:
Die Exceltabelle enthält Daten, die sich in den Spalten A,B,C und D befinden. Die Anzahl der Zeilen, die mit Daten gefüllt sind, ist aber variabel.
Zur Analyse der Daten will ich nun jedes Mal zwei zusätzliche Spalten (eine z.B. nach A und eine nach C) einfügen. Die Daten in diesen Spalten sollen aus den anderen Spalten errechnet werden, z.B. soll gelten "F=3+8*A" (konkret natürlich dann mit der jeweiligen Zeilennummer dazu, also "F1=3+8*A1", "F2=3+8*A2",..., aber das ist einfach, da muss man die Formel ja nur "runterziehen") usw. Die Formel ist dabei auch jedes Mal dieselbe.
Wie lässt sich das in Excel am einfachsten erreichen, ohne das jedes Mal händisch zu machen? Mutmaßlich mit Makros? Oder irgendwie anders? Bisher habe ich mit Makros noch keinerlei Erfahrung gesammelt.
Sollte noch was unklar sein, gerne rückfragen.
3 Antworten
Ein weiterer Gedanke: dem Datenbereich einen Namen geben und beim Reimport neuer Daten nur einmal den Namen ändern.
Zum Arbeiten mit Namen mag es helfen mit diesem eigentlichen einfachen, aber leistungsfähigen Feature mal in einer leeren Tabelle zu spielen. Dazu mal ein paar Zellen markieren und
a) Name anlegen: links neben der Bearbeitungsleiste einfach eingeben.
b) Name ändern: am schnellsten mit Strg-F3 für den Namensmanager. Name auswählen und auf Bearbeiten gehen
Was es beim Arbeiten mit Namen auch gelegentlich braucht ist die F2-Taste zum Umschalten zwischen Zeigemodus (mit den Pfeiltasten wird ein Bezug eingefügt) und Editiermodus (die Pfeiltasten bewegen den Cursor).
Und damit die Formeln nicht jedes Mal neu eingegeben werden müssen, holen wir die per Formel in eine weitere Hilfstabelle. Am einfachsten macht das die Funktion Bereich.Verschieben(). Langer Name (Offset im Englischen wäre kürzer), mit 5 Argumenten, die aber alle einfach zu handhaben sind. Als da sind
- Ausgangsbezug - wie erwähnt der Name des Bereichs
- um wie viele Zeilen soll der verschoben werden. 0 wäre die Zelle links oben des Ausgangsbereichs/Namens, 1 dann die zweite, usf. Damit die durchläuft könnte man auf die aktuelle Zeile in der Zieltabelle abzielen. Die Funktion Zeile() liefert in A1 dann eben 1. In A2 dann 2, usf.
- Gleiches Spiel bei der Spalte
- Anzahl Zeilen ist immer 1 - ich will ja nur eine Zelle
- Dto bei Spalten: ist fix 1.
Dann würde die erste Formel in der ersten Zielzelle etwa
lauten. Der festgelegte Name in dem Beispiel ist "Datenbereich"; kann beliebig lauten. Die lässt sich nach unten ziehen. Nicht die ganze Spalte ausfüllen, sondern wenn z.B. tausend Datensätze daher kommen können auf zweitausend beschränken. Steht nichts in den Ausgangssätzen, dann eben leer oder Bezugsfehler.
Für die zweite Spalte, dann das dritte Argument von 0 auf 1 setzen. Usf. Und wieder nach unten ziehen.
Damit wäre die einmalige Vorbereitungsarbeit abgeschlossen. Bei jedem neuen Datenimport ist nur einmal der Ausgangsname anzupassen. Genauso lässt sich das Hilfsblatt kopieren und in der Kopie dann per Suchen und Ersetzen etwa der Name austauschen und auf weitere Datenstände zugreifbar machen.
Und wie gesagt: zuerst mit den Namen spielen und wie die sich leicht verändern lassen. Vielleicht kurz Zeile() und Spalte() anschauen. Die Bereich.Verschieben() klingt schlimmer als sie ist.
Viel Erfolg!
Und wenn es funktioniert, wäre eine Rückmeldung schön. Oder Hint, was unklar erklärt wurde.
Sorry Korrektur: in der Beispielformel muss der Teil Zeile() natürlich Zeile()-1 heißen damit es 0 (= keine Zeilenverschiebung bei der ersten Zelle) ergibt.
Gleiches Spiel ginge bei der Spaltenangabe. Im obigen Beispiel die 0 durch Spalte()-1 ersetzen und die Formel lässt sich nach unten, wie nach rechts kopieren ohne weiteres Editieren.
Danke erstmal! Aktuell komm ich nicht dazu, es anzuwenden, weil keine Zeit in der Arbeit ist, aber ggf. bekommst du da dann noch Feedback :)
Das kannst Du per Makro ausführen lassen, wobei sich die Daten aus der Spalte B und D jedes mal um eine Spalte verschieben würde.
F1=3+8*A1", "F2=3+8*A2
Diese Formel kannst Du auch per Makro anlegen.
Mit Makros kenne ich mich bisher nicht wirklich aus, muss mich da wohl mal einarbeiten.
Könntest Du nicht einfach ein Tool bauen mit zwei Blättern: In das erste kopierst Du ganz einfach die erhaltenen Daten rein (mit markieren- copy- paste).
Und das zweite Blatt erstellst Du Dir einmal so, wie Du es gerne hättest (Formeln mit Bezug auf das andere Blatt, Formatierung etc.).
Wenn die Anzahl der Datensätze jedesmal anders ist, ist es ratsam, den Inhalt des ersten Blattes erstmal zu löschen, bevor die neuen Daten reinkopiert werden.