Ich habe also folgende Anforderung:
Blatt1
xxxx
YYYY
ZZZZ
Blatt 2
1994 xxx
1995 xxx
1996 xxx
1994 YYY
1995 YYY
1996 YYY
1994 ZZZ
1995 ZZZ
1996 ZZZ
Im Wesentlichen möchte ich Firmennamen aus Blatt 1 kopieren und sie für jedes angegebene Jahr einfügen und dies für fast 600 Firmen wiederholen.
Gibt es eine Formel oder VBA-Codes, um dies zu erreichen? Ich wäre für jede Hilfe sehr dankbar
Antwort1
Wählen Sie Ihre Unternehmensliste aus und verwenden Sie Daten > Daten abrufen und transformieren > Aus Tabelle/Bereich.
Dadurch wird der Power Query-Editor geöffnet. Sie sehen ungefähr Folgendes:
Verwenden Sie Home>Schließen und Laden
Jetzt nehmen wir uns einen Moment Zeit, um eine dynamische Liste von Jahren zwischen einem Start- und einem Endjahr zu erstellen. Dies ist nützlich, wenn Sie den Start- und den Endzeitpunkt später ändern möchten.
Geben Sie das Start- und das Endjahr in separaten Zellen in Ihrer Arbeitsmappe ein.
Wählen Sie das Startjahr aus und benennen Sie die Zelle, indem Sie einen Namen wie „start_year“ in das Namensfeld eingeben:
Nachdem Sie die Zelle benannt haben, wählen Sie die Zelle aus und verwenden Sie Daten>Daten abrufen und transformieren>Aus Tabelle/Bereich
Nachdem der Power Query-Editor mit einer einzelnen Zelle mit der Spaltenüberschrift „Spalte1“ geöffnet wurde, klicken Sie mit der rechten Maustaste auf diese Zelle und wählen Sie „Drilldown“:
Wenn Sie den Bereich „Abfragen“ auf der linken Seite erweitern, sehen Sie Folgendes:
Verwenden Sie „Start“ > „Schließen und laden in“ und wählen Sie „Nur Verbindung erstellen“ aus.
Wiederholen Sie dies für das letzte Jahr, sodass Sie Folgendes haben:
Auch hier gilt: Schließen und laden in und nur Verbindung erstellen.
Klicken Sie nun in Excel auf Daten > Abfragen und Verbindungen. Sie sollten Folgendes sehen:
Bei mir heißt es „50 Zeilen geladen“, weil ich Dummy-Daten mit 50 Firmennamen erstellt habe. Bei Ihnen kann das anders sein.
Klicken Sie mit der rechten Maustaste auf die Abfrage Table1 und wählen Sie „Bearbeiten“. Dadurch wird der Power Query-Editor erneut geöffnet.
Gehen Sie nun zu Start > Erweiterter Editor. Es sollte so aussehen:
Wir werden etwas Code hinzufügen, um diese Jahresliste zu erstellen.
Fügen Sie diese Zeile zu Zeile zwei hinzu:
years = List.Numbers(start_year,end_year),
So was:
Klicken Sie nun auf Fertig. Es sieht so aus, als hätte sich nichts geändert, aber keine Sorge.
Verwenden Sie „Spalte hinzufügen > Benutzerdefinierte Spalte“ und konfigurieren Sie sie wie folgt:
Wenn Sie auf „OK“ klicken, wird Folgendes angezeigt:
Klicken Sie auf den Doppelpfeil oben in der Spalte „Jahr“ und dann auf „In neue Zeilen erweitern“.
Sie sehen jetzt, dass Sie für jede Kombination aus Unternehmen und Jahr eine Zeile haben:
Verwenden Sie Start > Schließen und Laden, um es wieder in Ihre Arbeitsmappe einzufügen.
Wenn sich Ihre Unternehmensliste oder das Anfangs- und/oder Endjahr ändert, können Sie einfach die Quelldaten ändern und diese Abfrage aktualisieren.
Antwort2
Das ist ganz einfach. Es gibt viele Möglichkeiten, aber die folgende ist die einfachste, die zu dem passt, was Sie gerade beschrieben haben.
Zählen Sie zusammen, wie viele Firmennamen es gibt. (Drücken Sie einfach Ctrl-Down Arrowin die Spalte mit den Namen und sehen Sie, welche Zeile die letzte ist. Verwenden Sie die erste Zeile mit einem der Namen und sehen Sie, wie viele es gibt. Nehmen wir für diese Antwort an, es sind 587.)
Wählen Sie auf dem anderen Blatt eine Spalte aus und geben Sie das erste Jahr ein. Kopieren Sie es dann nach unten, um insgesamt 587 Zellen zu füllen. Nehmen wir an, Sie beginnen in A1 und enden in A587. Geben Sie in Zelle A588 die Formel ein =A1+1. Kopieren Sie diese nach unten, um weitere 587 Zellen zu füllen. Kopieren Sie dazu die Zelle, markieren Sie sie und den Rest der 587 Zellen (A588:A1174) und fügen Sie sie ein. Jetzt haben Sie also alle 587 Zellen markiert und mit der Formel gefüllt. Kopieren Sie diesen Block und drücken Sie Ctrl-Down Arrow, dann Down Arrowerneut, um zur nächsten leeren Zelle (A1175) zu gelangen.
Fügen Sie nun erneut ein und gehen Sie nach unten zur nächsten leeren Zelle. Fahren Sie fort, bis Sie einen Block mit 587 Zellen aller Jahre haben, die Sie auflisten möchten. Markieren Sie die gesamte Spalte mit Einträgen (vielleicht waren es 30 Jahre, sodass Sie die Zellen A1:A17610 markieren). Kopieren Sie den gesamten Eintrag und Paste|Special|Valuesändern Sie ihn in permanenten Text.
Kopieren Sie die 587 Firmennamen und fügen Sie sie in die nächste Spalte ab B1:B587 ein. Wechseln Sie dann zur nächsten leeren Zelle, B588, geben Sie die Formel ein =B1und kopieren Sie die Zelle. Wechseln Sie zur Spalte A und zur allerletzten ausgefüllten Zelle, oder verwenden Sie einfach , F5 GoToum zur Zelle B17610 zu gelangen (die letzte Zelle mit den Jahreszahlen ist A17610, und Sie möchten sowieso in Spalte B sein). Wie auch immer Sie es machen, gelangen Sie zu B17610. Drücken Sie , Shift-Up Arrowum alle diese leeren Zellen abzudecken (selbst und bis zu A588, wohin Sie gelangen ... Sie können A588 überkleben, da es sich um dieselbe Formel handelt).
Nach der Markierung einfügen. Markieren Sie dann alle Zellen in Spalte B wie in Spalte A, kopieren Sie sie und Paste|Special|Valuesmachen Sie sie ebenfalls zu permanentem Text.
Das sieht nur nach einer Stunde und viel Aufwand aus. Tatsächlich würde ich höchstens 2 Minuten erwarten, selbst wenn Sie ein bisschen herumstolpern. Und wenn nicht ... wie auch immer es beim Lesen aussieht, im wirklichen Leben geht es schnell.
Erledigt.