Oft stoßen Sie auf einen Excel-Datensatz, bei dem nur eine Zelle mit Daten gefüllt ist und die darunter liegenden Zellen bis zum nächsten Wert leer sind.
Wie nachfolgend dargestellt:
Während dieses Format für einige Leute funktioniert, besteht das Problem mit dieser Art von Daten darin, dass Sie damit keine Pivot-Tabellen erstellen oder in Berechnungen verwenden können.
Es ist einfach zu beheben!
In diesem Tutorial zeige ich Ihnen wieZellen schnell bis zum nächsten Füllwert in Excel füllen.
Sie können dies ganz einfach mit einfachen Go-To-Spezialdialogtechniken, VBA oder Power Query tun.
Also lasst uns anfangen!
Inhalte
Methode 1 – Füllen Sie mit der Formel Go To Special + aus
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Daten in die Spalten A und B eingeben.
In Spalte B besteht das Ziel darin, den "Drucker" bis zur letzten leeren Zelle darunter zu füllen, dann, wenn der "Scanner" startet, dann den "Scanner" in der Zelle darunter auszufüllen, bis die Zelle leer ist.
Die folgenden Schritte verwenden Gehe zu Spezial, um alle leeren Zellen auszuwählen und mit einer einfachen Formel auszufüllen:
- Wählen Sie die zu füllenden Daten aus (A1:D21 in unserem Beispiel)
- Gehen Sie auf die Registerkarte "Startseite".
- Klicken Sie in der Gruppe „Bearbeiten“ auf das Symbol „Suchen und auswählen“ (dadurch werden weitere Optionen im Dropdown-Menü angezeigt).
- Klicken Sie auf die Option „Los“.
- Klicken Sie im sich öffnenden Dialogfeld „Gehe zu“ auf die Schaltfläche „Spezial“.Dies öffnet den Dialog "Gehe zu Spezial".
- Klicken Sie im Dialogfeld „Gehe zu Spezial“ auf „Leer“.
- OK klicken
Mit den obigen Schritten werden alle leeren Zellen im Datensatz ausgewählt (siehe unten).
Unter den ausgewählten leeren Zellen werden Sie feststellen, dass eine Zelle heller ist als der Rest der Auswahl.Diese Zelle ist die aktive Zelle, in die wir die Formel eingeben möchten.
Machen Sie sich keine Gedanken über die Position der Zelle in der Auswahl, da unsere Methode in jedem Fall funktioniert.
Hier sind nun die Schritte zum Ausfüllen der Daten in die ausgewählten leeren Zellen:
- Klicken Sie auf Ihrer Tastatur auf das Gleichheitszeichen (=).Dadurch wird ein Gleichheitszeichen in die aktive Zelle eingefügt
- Drücken Sie die Pfeiltaste nach oben.Dadurch wird der Zellbezug der Zelle über der aktiven Zelle eingefügt.In unserem Fall ist beispielsweise B3 die aktive Zelle, und wenn wir diese beiden Schritte ausführen, wird =B2 in die Zelle eingegeben
- Halten Sie die Strg-Taste gedrückt und drücken Sie die Eingabetaste
Die obigen Schritte fügen automatisch alle leeren Zellen mit den obigen Werten ein.
Auch wenn dies wie zu viele Schritte erscheinen mag, können Sie, sobald Sie den Dreh raus haben, in Sekundenschnelle Daten in Excel eingeben.
Nun, es gibt zwei weitere Dinge, die Sie beachten müssen, wenn Sie diese Methode verwenden.
Formel in Wert umwandeln
Die erste besteht darin, sicherzustellen, dass Sie Formeln in Werte umwandeln (damit Sie statische Werte haben und es nicht vermasseln, wenn Sie die Daten in Zukunft ändern).
Datumsformat ändern
Wenn Sie Datumsangaben in Ihren Daten verwenden (wie ich es in meinen Beispieldaten getan habe), werden Sie feststellen, dass die ausgefüllten Werte in der Datumsspalte Zahlen statt Datumsangaben sind.
Wenn die Ausgabe in der Datumsspalte das gewünschte Datumsformat hat, dann sind Sie gut und brauchen nichts weiter zu tun.
Wenn diese Daten jedoch nicht im richtigen Format vorliegen, ist eine Feinabstimmung erforderlich.Während Sie den richtigen Wert haben, müssen Sie nur das Format ändern, sodass es als Datum in der Zelle angezeigt wird.
Hier sind die Schritte dazu:
- Wählen Sie Spalten mit Datumsangaben aus
- Klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Zahlen auf das Dropdown-Menü Format und wählen Sie ein Datumsformat aus.
Wenn Sie diese Polsterung von Zeit zu Zeit machen müssen, empfehle ich diese Go-To Spezial- und Formeltechnik.
Obwohl es einige Schritte umfasst, ist es einfach und liefert Ihnen Ergebnisse im Datensatz.
Wenn Sie dies jedoch häufig tun müssen, empfehle ich Ihnen, sich VBA und die als nächstes beschriebene Power Query-Methode anzusehen
Methode 2 - Mit einfachem VBA-Code ausfüllen
Sie können einen sehr einfachen VBA-Makrocode verwenden, um Zellen in Excel schnell bis zum letzten leeren Wert zu füllen.
Sie müssen VBA-Code nur einmal zu einer Datei hinzufügen, dann können Sie diesen Code problemlos mehrmals in derselben Arbeitsmappe oder sogar in mehreren Arbeitsmappen auf Ihrem System wiederverwenden.
Hier ist der VBA-Code, der jede Zelle in der Auswahl durchläuft und alle leeren Zellen ausfüllt:
Sub FillDown() Für jede Zelle in der Auswahl If cell = "" Then cell.FillDown End If Next End Sub
Der obige Code verwendet eine For-Schleife, um jede Zelle in der Auswahl zu durchlaufen.
In der For-Schleife verwende ich eine Wenn-Dann-Bedingung, um zu prüfen, ob die Zelle leer ist.
Wenn die Zelle leer ist, wird sie mit dem Wert aus der darüber liegenden Zelle gefüllt, wenn nicht, ignoriert die for-Schleife die Zelle und springt zur nächsten Zelle.
Nachdem Sie nun den VBA-Code haben, möchte ich Ihnen zeigen, wo Sie diesen Code in Excel einfügen:
- Wählen Sie die auszufüllenden Daten aus
- Klicken Sie auf den Entwickler-Tab
- Klicken Sie auf das Visual Basic-Symbol (oder verwenden Sie die Tastenkombination ALT+F11).Dadurch wird der Visual Basic-Editor in Excel geöffnet
- Im Visual Basic-Editor auf der linken Seite haben Sie den Projekt-Explorer.Wenn Sie es nicht sehen, klicken Sie im Menü auf die Option Ansicht und dann auf Projekt-Explorer
- Wenn Sie mehrere Excel-Arbeitsmappen geöffnet haben, zeigt der Projekt-Explorer alle Arbeitsmappennamen an.Suchen Sie den Namen der Arbeitsmappe, in der Sie die Daten haben.
- Klicken Sie mit der rechten Maustaste auf ein beliebiges Objekt in der Arbeitsmappe, gehen Sie zu Einfügen und klicken Sie auf Modul.Dadurch wird ein neues Modul für diese Arbeitsmappe eingefügt
- Doppelklicken Sie auf das „Modul“, das Sie gerade in den obigen Schritten eingefügt haben.Es öffnet das Codefenster für dieses Modul
- Kopieren Sie den VBA-Code und fügen Sie ihn in das Codefenster ein
- Platzieren Sie den Cursor an einer beliebigen Stelle im Code und führen Sie den Makrocode aus, indem Sie auf die grüne Schaltfläche in der Symbolleiste klicken oder die Tastenkombination F5 verwenden
Die obigen Schritte führen den VBA-Code aus und Ihre Daten werden ausgefüllt.
Wenn Sie diesen VBA-Code später erneut verwenden möchten, müssen Sie diese Datei als makrofähige Excel-Arbeitsmappe (Erweiterung .XLSM) speichern.
Sie können dieses Makro auch zu Ihrer Symbolleiste für den Schnellzugriff hinzufügen, die immer sichtbar ist, und Sie können mit nur einem Klick auf dieses Makro zugreifen (in einer Arbeitsmappe mit Code im Backend).
Wenn Sie also das nächste Mal Daten zum Ausfüllen benötigen, können Sie einfach eine Auswahl treffen und auf die Makroschaltfläche in der Symbolleiste für den Schnellzugriff klicken.
Sie können dieses Makro auch zu Ihrer persönlichen Makro-Arbeitsmappe hinzufügen und es dann in jeder Arbeitsmappe auf Ihrem System verwenden.
Klicken Sie hier, um mehr über Ihre persönliche Makro-Arbeitsmappe und das Hinzufügen von Code zu erfahren.
Methode 3 – Ausfüllen mit Power Query
Power QueryVerfügt über eine integrierte Funktionalität, mit der Sie es mit einem einzigen Klick ausfüllen können.
Es wird empfohlen, wenn Sie immer noch Power Query verwenden, um Daten zu transformieren oder Daten aus mehreren Arbeitsblättern oder mehreren Arbeitsmappen zu kombinieren.
Als Teil eines vorhandenen Workflows können Sie mithilfe der Option „Fill“ in Power Query schnell leere Zellen füllen.
Zur Verwendung von Power Query wird empfohlen, dass Ihre Daten im Excel-Tabellenformat vorliegen.Wenn Sie die Daten nicht in eine Excel-Tabelle konvertieren können, müssen Sie einen benannten Bereich für die Daten erstellen und diesen benannten Bereich dann in Power Query verwenden.
Unten habe ich einen Datensatz, der in eine Excel-Tabelle konvertiert wurde.Sie können dies tun, indem Sie den Datensatz auswählen, zur Registerkarte Einfügen gehen und auf das Symbol Tabelle klicken.
Hier sind die Schritte zum Verwenden von Power Query zum Ausfüllen der Daten bis zum nächsten Wert:
- Wählen Sie eine beliebige Zelle im Datensatz aus
- Klicken Sie auf die Registerkarte Daten
- Klicken Sie in der Gruppe Daten abrufen und transformieren auf Aus Arbeitsblatt.Dadurch wird der Power Query-Editor geöffnet.Beachten Sie, dass leere Zellen in Power Query den Wert „null“ anzeigen
- Wählen Sie im Power Query-Editor die Spalte aus, die Sie ausfüllen möchten.Halten Sie dazu die Strg-Taste gedrückt und klicken Sie auf die Überschrift der Spalte, die Sie auswählen möchten.In unserem Beispiel sind dies die Datums- und Produktspalten.
- Klicken Sie mit der rechten Maustaste auf einen ausgewählten Titel
- Gehen Sie zur Option Füllen und klicken Sie auf Nach unten.Dadurch werden alle leeren Zellen mit Daten gefüllt
- Klicken Sie auf die Registerkarte Datei und dann auf Schließen und laden.Dadurch wird ein neues Arbeitsblatt mit der Ergebnistabelle in die Arbeitsmappe eingefügt
Während dieser Ansatz übertrieben klingen mag, besteht einer der großen Vorteile der Verwendung von Power Query darin, dass Sie die resultierenden Daten schnell aktualisieren können, wenn sich die ursprünglichen Daten ändern.
Wenn Sie beispielsweise weitere Datensätze zu den Originaldaten hinzufügen, müssen Sie nicht alle oben genannten Schritte wiederholen.Sie können einfach mit der rechten Maustaste auf die resultierende Tabelle klicken und auf Aktualisieren klicken.
Obwohl die Power Query-Methode gut funktioniert, ist es am besten, sie zu verwenden, wenn Sie Power Query bereits in Ihrem Workflow verwenden.
Wenn Sie nur einen Datensatz mit leeren Zellen zum Ausfüllen haben, ist es bequemer, die spezielle Methode Gehe zu oder die VBA-Methode (oben beschrieben) zu verwenden.
So können Sie diese drei einfachen Methoden anwendenFüllen Sie leere Zellen bis zum nächsten Wert in Excel.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
