Verwenden des erweiterten Filters in Excel zur Lösung des Problems von Mort. Obwohl reguläre Filter leistungsfähiger geworden sind, gibt es immer noch Zeiten, in denen der erweiterte Filter einige Tricks ausführen kann, die andere nicht können.
Schau Video
- Der erweiterte Filter ist "erweiterter" als der reguläre Filter, weil:
- 1) Es kann in einen neuen Bereich kopiert werden
- 2) Sie können komplexere Kriterien wie Feld 1 = A oder Feld 2 = A erstellen
- 3) Es ist schnell
- Mort versucht, 100.000 Zeilen in VBA zu verarbeiten, indem er Datensätze durchläuft oder ein Array verwendet
- Die Verwendung integrierter Excel-Funktionen ist immer schneller als das Schreiben eines eigenen Codes.
- Sie benötigen einen Eingabebereich und dann einen Kriterienbereich und / oder einen Ausgabebereich
- Für den Eingabebereich: einzelne Überschriftenreihe über den Daten
- Fügen Sie eine temporäre Zeile für Überschriften hinzu
- Für den Ausgabebereich: Eine Reihe von Überschriften für die Spalten, die Sie extrahieren möchten
- Für den Kriterienbereich: Überschriften in Zeile 1, Werte ab Zeile 2
- Komplikation: In älteren Versionen von Excel konnte der Ausgabebereich nicht auf einem anderen Blatt angezeigt werden
- Wenn Sie ein Makro schreiben, das möglicherweise 2003 ausgeführt wird, verwenden Sie einen benannten Bereich, um den Eingabebereich zu umgehen
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2060: Erweiterter Excel-Filter
Hey, willkommen zurück bei Netcast, ich bin Bill Jelen. Die heutige Frage von Mort. Mort, er hat 100.000 Datenzeilen und interessiert sich für die Spalten A, B und D, in denen Spalte C einem bestimmten Jahr entspricht. Also möchte er, dass eine Person ein Jahr eingibt und dann die Spalten A, B und D erhält. Und Mort hat eine VBA, in der er Arrays verwendet, und ich sagte: „Warte eine Sekunde, weißt du, der erweiterte Filter würde dies tun viel besser." Okay, und jetzt, nur um es zu überprüfen, ging ich zurück und schaute meine Videos noch einmal durch. Ich habe den erweiterten Filter schon lange nicht mehr behandelt, daher sollten wir darüber sprechen.
Der erweiterte Filter erfordert einen Eingabebereich und dann mindestens einen davon: einen Kriterienbereich oder einen Ausgabebereich. Obwohl wir heute beide verwenden werden. Okay, der Eingabebereich sind also Ihre Daten und Sie müssen Überschriften über den Daten haben. Also, Mort hat keine Überschriften und deshalb werde ich hier oben vorübergehend eine Zeile einfügen und einfach Feld 1 mögen. Mort weiß, was seine Daten sind, und so könnte er dort echte Überschriften einfügen. Und wir verwenden keine so genannten Daten in den Spalten E bis O, also muss ich dort keine Überschriften hinzufügen, okay? So wird jetzt A1 bis D, 100000 mein Eingabebereich. Und dann der Ausgabebereich und der Kriterienbereich - Nun, der Ausgabebereich ist nur eine Liste der Überschriften, die Sie möchten. Also werde ich den Ausgabebereich hier einstellen und wir brauchen kein Feld 3, also bin ich 'Ich nehme das einfach zur Seite. In diesem Bereich hier werden A1 bis C1 zu meinem Ausgabebereich, der Excel mitteilt, welche Felder ich aus dem Eingabebereich auswählen möchte. Und sie könnten in einer anderen Reihenfolge sein, wenn Sie die Dinge neu anordnen möchten, z. B. wenn ich zuerst Feld 4 und dann Feld 1 und dann Feld 2 möchte. Und wieder wären dies echte Überschriften wie die Rechnungsnummer. Ich weiß nur nicht, wie Mort's Daten aussehen.
Und dann ist der Kriterienbereich eine Überschrift und welcher Wert Sie wollen. Nehmen wir also an, ich habe im Jahr 2014 versucht, etwas zu bekommen. Dies wird zu einem solchen Kriterienbereich. Okay, hier nur ein Wort der Vorsicht. Ich bin in Excel 2016 und es ist möglich, einen erweiterten Filter zwischen zwei Blättern in Excel 2016 durchzuführen, aber wenn Sie weit zurückgehen und ich mich nicht erinnere, was zurück ist, vielleicht 2003, bin ich mir nicht sicher. In der Vergangenheit war es früher so, dass Sie keinen erweiterten Filter von einem Blatt zu einem anderen Blatt durchführen konnten, sodass Sie hierher kommen und Ihren Eingabebereich benennen mussten. Sie müssten hier einen Namen erstellen. Mein Name oder so, okay? Und auf diese Weise könnten Sie dies in Ordnung bringen. Nicht unbedingt in Excel 2016, aber ich 'Ich bin mir nicht sicher, ob Mort dies in älteren Versionen der Daten ausführen wird.
Okay, also hier bei Data gehen wir zu Advanced Filter, okay. Und wir werden an einen anderen Ort kopieren, der dort unseren Ausgabebereich ermöglicht. Okay, also der Listenbereich, wo sind die Daten? Da ich in Excel 2016 bin, werde ich auf die Daten verweisen, anstatt den Namensbereich zu verwenden. Das ist also mein Eingabebereich. Der Kriterienbereich sind die Zellen genau dort und dann, wo wir sie ausgeben werden, werden nur diese drei Zellen dort sein. Und dann klicken wir auf OK. Okay und BAM! So schnell ist es. Und wenn wir ein anderes Jahr wollten? Wenn wir ein anderes Jahr wollten, würden wir die Ergebnisse löschen, 2015 einfügen und dann erneut einen erweiterten Filter durchführen. An einen anderen Speicherort kopieren, auf OK klicken und alle Datensätze für 2015 anzeigen. Blitzschnell.
Okay, jetzt, wo ich ein Fan von erweiterten Filtern in regulärem Excel bin, war ich ein großer Fan von erweiterten Filtern in VBA, okay, weil VBA den erweiterten Filter wirklich, wirklich, wirklich einfach macht. Okay, also werden wir hier einen Code für Mort schreiben, vorausgesetzt, Mort's Daten haben keine Überschriften und wir müssen die Überschriften vorübergehend hinzufügen, okay? Also werde ich zu VBA, Alt + F11 wechseln und dies aus dem Arbeitsblatt ausführen, das die Daten enthält. Also: WS als Arbeitsblatt dimmen, WS = ActiveSheet setzen. Fügen Sie dann Zeile 1 ein und fügen Sie einfach einige Überschriften hinzu: A, B, Jahr und D. Stellen Sie fest, wie viele Datenzeilen wir heute haben, und beginnen Sie ab Zelle A1 mit 4 Spalten bis zur letzten Zeile Eingabebereich sein. Okay, und dann ist dies tatsächlich Mort's Code hier, wo er nach der InputBox gefragt hat.bekommt das Jahr, das sie wollen und dann fragt er, welches Jahr oder wie sie das neue Blatt benennen wollen, in Ordnung. Es wird also tatsächlich ein Blatt im laufenden Betrieb einfügen und dann ein neues Blatt, WSN, als ActiveSheet I-dimensionieren. Ich weiß also, dass WS das Originalblatt ist, WSN das neue Blatt, das gerade hinzugefügt wurde. Geben Sie auf dem neuen Blatt den Kriterienbereich so ein, dass unter Spalte E die Überschrift angezeigt wird, die dieser Überschrift entspricht, und dann wird die Antwort, die sie uns gegeben haben, in E2 angezeigt. Der Ausgabebereich werden meine anderen drei Überschriften sein: A, B und D. Und wieder, wenn Sie oder Mort diese in echte Überschriften ändern, ist dies wahrscheinlich eine bessere Sache als A, B, D und Sie würden es auch tun Ändern Sie diese in echte Überschriften, in Ordnung? All dies ist hier also nur ein bisschen Vorarbeit. Diese eine großartige Codezeile erledigt den gesamten erweiterten Filter. So,Aus dem InputRange machen wir einen AdvancedFilter, den wir kopieren werden. Das ist unser Auswahlfilter oder Kopie. Der CriteriaRange ist E1 bis E2, der CopyToRange ist A bis C. Eindeutige Werte - Nein, wir wollen alle Werte. Okay, diese eine Codezeile dort macht die ganze Magie des Durchlaufens aller Datensätze oder ersetzt das Durchlaufen aller Datensätze oder das Ausführen der Arrays. Und wenn wir fertig sind, löschen wir den Kriterienbereich und löschen Zeile 1 wieder im ursprünglichen Arbeitsblatt.Und wenn wir fertig sind, löschen wir den Kriterienbereich und löschen dann Zeile 1 wieder im ursprünglichen Arbeitsblatt.Und wenn wir fertig sind, löschen wir den Kriterienbereich und löschen dann Zeile 1 wieder im ursprünglichen Arbeitsblatt.
Okay, also lassen Sie uns hier zu unseren Daten zurückkehren. Wir werden es einfach machen, dies auszuführen, also: Einfügen, eine Form und diesen Filter aufrufen, Start, Mitte, Mitte, Größer, Größer, Größer, Rechtsklick, Makro zuweisen und MacroForMort zuweisen. Okay, also los geht's. Wir werden einen Test machen. Um zu sehen, ob wir uns im Datenblatt befinden, klicken Sie auf Filter. Welches Jahr möchten wir? Wir wollen 2015. Wie möchte ich es nennen? Ich möchte es 2015 nennen, okay. Und BAM! Da ist es geschafft. So schnell ist das, so schnell ist das.
Da Mort's Originaldaten keine Überschriften hatten, sollten diese Daten möglicherweise keine Überschriften haben. Gehen wir also Alt + F11, hier wollen wir den Kriterienbereich löschen. Wir werden auch Zeilen (1). Löschen. Okay, jetzt, wenn wir das nächste Mal dabei sind, werden diese Überschriften entfernt. Und lassen Sie uns einfach - Anstatt das Ganze schnell laufen zu lassen, werfen wir hier einen Blick auf das Jahr 2014. Also werde ich eine Zelle in den Daten auswählen, Alt + F11, und ich möchte nur bis zu dem Punkt laufen, an dem wir das tun erweiterter Filter. So können wir sehen, was das gesamte Makro hier tut. Also klicken wir auf Ausführen und ich möchte 2014. 2014, in Ordnung. Drücken Sie also F8, um den erweiterten Filter auszuführen. Wir können hier zu Excel zurückkehren und sehen, was passiert ist.
First thing that's happened- Now, first thing that’s happened is we've added a new temporary row with the headings. Inserted this worksheet, built a criteria range with a heading and what year they input, chose the fields that we want to do and then back in VBA, I'll run the next line of codes, that's F8 that does the advanced filter right there. It's incredibly fast and you'll see that that has actually now brought us all the records. From there, it's just a bit of cleanup, delete this, delete this. I'll go back to the data and delete Row 1 and we will be good to go. So I'll just let the rest of that run, remove that breakpoint, alright? So there's the VBA. For me, this is I think the fastest way, fastest way to go.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Okay, da haben Sie es. Ich möchte Mort dafür danken, dass er diese Frage eingeschickt hat. Ich möchte Ihnen dafür danken, dass Sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2060.xlsm
