Video ansehen - In Excel ausfüllen
Oft können Sie auf einen Datensatz stoßen, bei dem nur eine Zelle mit Daten gefüllt ist und die Zellen darunter bis zum nächsten Wert leer sind.
Etwas wie unten gezeigt:
Während dieses Format für einige Leute funktioniert, besteht das Problem bei dieser Art von Daten darin, dass Sie es nicht zum Erstellen von Pivot-Tabellen oder in Berechnungen verwenden können.
Und das hat eine einfache Lösung!
In diesem Tutorial zeige ich dir, wie es schnell geht Füllen Sie die Zellen in Excel bis zum nächsten gefüllten Wert aus.
Sie können dies ganz einfach mit einer einfachen Go-To-Spezialdialogfeldtechnik, VBA oder Power Query tun.
Also lasst uns anfangen!
Methode 1 - Ausfüllen mit Gehe zu Spezial + Formel
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten Daten in Spalte A und Spalte B eingeben.
In Spalte B ist das Ziel, „Drucker“ bis zur letzten leeren Zelle darunter zu füllen, und dann, wenn „Scanner“ startet, dann „Scanner“ in die darunter liegenden Zellen füllen, bis die Zellen leer sind.
Unter den zu verwendenden Schritten gehen Sie zu speziell, um alle leeren Zellen auszuwählen und mit einer einfachen Formel auszufüllen:
- Wählen Sie die Daten aus, die Sie ausfüllen möchten (A1:D21 in unserem Beispiel)
- Gehen Sie zum Tab „Startseite“
- Klicken Sie in der Gruppe Bearbeiten auf das Symbol „Suchen & Auswählen“ (dies zeigt einige weitere Optionen im Dropdown-Menü an).
- Klicken Sie auf die Option „Gehe zu“
- Klicken Sie im sich öffnenden Dialogfeld Gehe zu auf die Schaltfläche Spezial. Dadurch wird das Dialogfeld „Gehe zu Special“ geöffnet
- Klicken Sie im Dialogfeld Gehe zu Spezial auf Leerzeichen
- OK klicken
Die obigen Schritte würden alle leeren Zellen im Datensatz auswählen (wie unten gezeigt).
In den ausgewählten leeren Zellen würden Sie feststellen, dass eine Zelle heller ist als der Rest der Auswahl. Diese Zelle ist die aktive Zelle, in die wir eine Formel eingeben.
Machen Sie sich keine Gedanken darüber, wo sich die Zelle in der Auswahl befindet, da unsere Methode auf jeden Fall funktionieren würde.
Im Folgenden sind die Schritte zum Ausfüllen der Daten in den ausgewählten leeren Zellen aufgeführt:
- Drücken Sie die Gleichheitstaste (=) auf Ihrer Tastatur. 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 würden automatisch alle leeren Zellen mit dem Wert darüber einfügen.
Dies mag zwar nach zu vielen Schritten aussehen, aber sobald Sie diese Methode beherrschen, können Sie innerhalb weniger Sekunden Daten in Excel schnell ausfüllen.
Nun gibt es zwei weitere Dinge, die Sie bei der Verwendung dieser Methode beachten müssen.
Formeln in Werte umwandeln
Die erste besteht darin, sicherzustellen, dass Sie Formeln in Werte umwandeln (damit Sie statische Werte haben und die Dinge nicht durcheinander kommen, falls Sie in Zukunft Daten ändern).
Unten finden Sie ein Video, das Ihnen zeigt, wie Sie Formeln in Excel schnell in Werte umwandeln.
Ändern Sie das Datumsformat
Falls Sie Datumsangaben in Ihren Daten verwenden (wie ich in den Beispieldaten), werden Sie feststellen, dass die ausgefüllten Werte in der Datumsspalte Zahlen und keine Datumsangaben sind.
Wenn die Ausgabe in der Datumsspalte im gewünschten Datumsformat vorliegt, sind Sie gut und müssen nichts weiter tun.
Wenn diese jedoch nicht im richtigen Datumsformat vorliegen, müssen Sie eine kleine Anpassung vornehmen. Während Sie die richtigen Werte haben, müssen Sie nur das Format ändern, damit es als Datum in der Zelle angezeigt wird.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie die Spalte mit den Daten aus
- Klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Zahlen auf das Dropdown-Menü Format und wählen Sie dann das Datumsformat aus.
Wenn Sie dieses Auffüllen ab und zu tun müssen, empfehle ich Ihnen, diese Go-To-Spezial- und Formeltechnik zu verwenden.
Obwohl es nur wenige Schritte umfasst, ist es einfach und liefert Ihnen das Ergebnis direkt in Ihrem Datensatz.
Falls Sie dies jedoch häufig tun müssen, schlage ich vor, dass Sie sich die als nächstes behandelten VBA- und Power Query-Methoden ansehen
Methode 2 - Mit einem einfachen VBA-Code ausfüllen
Sie können einen wirklich einfachen VBA-Makrocode verwenden, um Zellen in Excel schnell bis zum letzten leeren Wert zu füllen.
Sie müssen den VBA-Code nur einmal zur Datei hinzufügen und können den Code dann problemlos mehrmals in derselben Arbeitsmappe oder sogar in mehreren Arbeitsmappen auf Ihrem System wiederverwenden.
Unten ist der VBA-Code, der jede Zelle in der Auswahl durchläuft und alle leeren Zellen ausfüllt:
'Dieser VBA-Code wird von Sumit Bansal von TrumpExcel.com Sub FillDown() für jede Zelle in der Auswahl erstellt 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.
Innerhalb der For-Schleife habe ich eine If-Then-Bedingung verwendet, die überprüft, ob die Zelle leer ist oder nicht.
Falls die Zelle leer ist, wird sie mit dem Wert aus der obigen Zelle gefüllt, und falls sie nicht leer ist, ignoriert die for-Schleife die Zelle einfach und geht zur nächsten über.
Nachdem Sie nun den VBA-Code haben, möchte ich Ihnen zeigen, wo Sie diesen Code in Excel einfügen:
- Wählen Sie die Daten aus, die Sie ausfüllen möchten
- Klicken Sie auf die Registerkarte Entwickler
- 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 befindet sich der Projekt-Explorer. Wenn Sie es nicht sehen, klicken Sie im Menü auf die Option „Ansicht“ und dann auf Projekt-Explorer
- Falls Sie mehrere Excel-Arbeitsmappen geöffnet haben, zeigt Ihnen der Projekt-Explorer alle Arbeitsmappennamen an. Suchen Sie den Arbeitsmappennamen, in dem Sie die Daten haben.
- Klicken Sie mit der rechten Maustaste auf eines der Objekte in der Arbeitsmappe, gehen Sie zu Einfügen und klicken Sie dann auf Modul. Dadurch wird ein neues Modul für diese Arbeitsmappe eingefügt
- Doppelklicken Sie auf das ‚Modul‘, das Sie gerade im obigen Schritt eingefügt haben. Es öffnet das Codefenster für dieses Modul
- Kopieren Sie den VBA-Code in das Codefenster
- Platzieren Sie den Cursor an einer beliebigen Stelle im Code und führen Sie dann den Makrocode aus, indem Sie auf die grüne Schaltfläche in der Symbolleiste klicken oder die Tastenkombination F5 verwenden
Die obigen Schritte würden den VBA-Code ausführen und Ihre Daten würden aufgefüllt.
Wenn Sie diesen VBA-Code in Zukunft wieder verwenden möchten, müssen Sie diese Datei als makrofähige Excel-Arbeitsmappe (mit der Erweiterung .XLSM) speichern.
Sie können dieses Makro auch zu Ihrer Symbolleiste für den Schnellzugriff hinzufügen, die immer sichtbar ist, und Sie haben mit einem einzigen Klick Zugriff auf dieses Makro (in der Arbeitsmappe, in der Sie den Code im Backend haben).
Wenn Sie also das nächste Mal Daten haben, die Sie ausfüllen müssen, müssen Sie nur die Auswahl treffen und dann auf die Makroschaltfläche in der Symbolleiste für den Schnellzugriff klicken.
Sie können dieses Makro auch der persönlichen Makroarbeitsmappe hinzufügen und es dann in einer beliebigen Arbeitsmappe auf Ihrem System verwenden.
Klicken Sie hier, um mehr über persönliche Makro-Arbeitsmappen zu erfahren und wie Sie ihnen Code hinzufügen.
Methode 3 – Ausfüllen mit Power Query
Power Query verfügt über eine integrierte Funktion, mit der Sie mit einem einzigen Klick ausfüllen können.
Die Verwendung von Power Query wird empfohlen, wenn Sie es trotzdem verwenden, um Ihre Daten zu transformieren oder Daten aus mehreren Arbeitsblättern oder mehreren Arbeitsmappen zu kombinieren.
Als Teil Ihres vorhandenen Workflows können Sie die Ausfülloption in Power Query verwenden, um die leeren Zellen schnell auszufüllen.
Für die Verwendung von Power Query wird empfohlen, dass Ihre Daten in einem Excel-Tabellenformat vorliegen. Wenn Sie Ihre 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 den Datensatz, den ich bereits in eine Excel-Tabelle umgewandelt habe. Sie können dies tun, indem Sie den Datensatz auswählen, zur Registerkarte Einfügen gehen und dann auf das Tabellensymbol klicken.
Nachfolgend die Schritte zur Verwendung von Power Query, um Daten bis zum nächsten Wert auszufüllen:
- 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 Blatt“. Dadurch wird der Power Query-Editor geöffnet. Beachten Sie, dass die leeren Zellen in Power Query den Wert „null“ anzeigen würden
- Wählen Sie im Power Query-Editor die Spalten aus, die Sie ausfüllen möchten. Halten Sie dazu die Strg-Taste gedrückt und klicken Sie dann auf die Kopfzeile der Spalten, die Sie auswählen möchten. In unserem Beispiel wären dies die Spalten Datum und Produkt.
- Klicken Sie mit der rechten Maustaste auf eine der ausgewählten Überschriften
- Gehen Sie zur Option „Ausfüllen“ und klicken Sie dann auf „Nach unten“. Dies würde die Daten in allen leeren Zellen ausfüllen
- Klicken Sie auf die Registerkarte Datei und dann auf "Schließen & Laden". Dadurch wird ein neues Arbeitsblatt in die Arbeitsmappe mit der resultierenden Tabelle eingefügt
Obwohl diese Methode etwas übertrieben klingen mag, besteht ein großer Vorteil der Verwendung von Power Query darin, dass Sie die resultierenden Daten schnell aktualisieren können, falls sich Ihre ursprünglichen Daten ändern.
Wenn Sie beispielsweise den Originaldaten weitere Datensätze 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 dann 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 haben, in dem Sie die leeren Zellen ausfüllen möchten, ist die Verwendung der speziellen Methode Gehe zu oder der VBA-Methode (oben beschrieben) bequemer.
Dies sind also drei einfache Möglichkeiten, mit denen Sie Füllen Sie leere Zellen bis zum nächsten Wert in Excel aus.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
