ISNA Funktion und wie es verwenden, um die Fehler in Vlookup Formel zu ignorieren
zu ignorieren Die Funktionen wie VLOOKUP, COUNTIF, SUMIF werden als Arbeitsblattfunktionen bezeichnet. Im Allgemeinen sind die Funktionen, die in Excel vordefiniert und für die Verwendung in einem Arbeitsblatt bereit sind, Arbeitsblattfunktionen. Sie können den Code hinter diesen Funktionen in VBA nicht ändern oder sehen.
Andererseits sind die benutzerdefinierten Funktionen und Funktionen, die für VBA spezifisch sind, wie MsgBox oder InputBox, VBA-Funktionen.
Wir alle wissen, wie man VBA-Funktionen in VBA verwendet. Aber was ist, wenn wir VLOOKUP in einem VBA verwenden möchten? Wie machen wir das? In diesem Artikel werden wir genau das untersuchen.
Verwenden von Arbeitsblattfunktionen in VBA
Für den Zugriff auf die Arbeitsblattfunktion verwenden wir eine Anwendungsklasse. Fast alle Arbeitsblattfunktionen sind in der Application.Worksheet-Funktionsklasse aufgeführt. Mit dem Punktoperator können Sie auf alle zugreifen.
Schreiben Sie in ein beliebiges Sub Application.Worksheet Function. Schreiben Sie den Namen der Funktion. VBAs Intellisense zeigt den Namen der verfügbaren Funktionen an. Sobald Sie den Funktionsnamen ausgewählt haben, werden Sie wie bei jeder Excel-Funktion nach den Variablen gefragt. Sie müssen jedoch Variablen im verständlichen VBA-Format übergeben. Wenn Sie beispielsweise einen Bereich A1: A10 übergeben möchten, müssen Sie ihn als Bereichsobjekt wie Range („A1: A10“) übergeben.
Verwenden wir also einige Arbeitsblattfunktionen, um es besser zu verstehen.
Verwendung der VLOOKUP-Funktion in VBA
Um zu demonstrieren, wie Sie eine VLOOKUP-Funktion in VBA verwenden können, habe ich hier Beispieldaten. Ich muss den Namen und den Ort der angegebenen Anmelde-ID in einem Meldungsfeld mit VBA anzeigen. Die Daten sind im Bereich A1: K26 verteilt.
Drücken Sie ALT + F11, um VBE zu öffnen und ein Modul einzufügen.
Siehe den folgenden Code.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Wenn Sie diesen Code ausführen, erhalten Sie dieses Ergebnis.
Sie können sehen, wie schnell der VBA das Ergebnis in ein Meldungsfeld druckt. Lassen Sie uns nun den Code untersuchen.
Wie funktioniert es?
1.
Dim loginID As String Dim name, city As String Zuerst haben wir zwei Variablen vom Typ string deklariert, um das von der VLOOKUP-Funktion zurückgegebene Ergebnis zu speichern. Ich habe Zeichenfolgentypvariablen verwendet, da ich sicher bin, dass das von VLOOKUP zurückgegebene Ergebnis ein Zeichenfolgenwert ist. Wenn von Ihrer Arbeitsblattfunktion erwartet wird, dass sie den Werttyp Nummer, Datum, Bereich usw. zurückgibt, verwenden Sie diese Art von Variable, um das Ergebnis zu speichern. Wenn Sie nicht sicher sind, welche Art von Wert von der Arbeitsblattfunktion zurückgegeben wird, verwenden Sie Variablentypvariablen.
2.
loginID = „AHKJ_1-3357042451“
Als Nächstes haben wir die Variable loginID verwendet, um den Suchwert zu speichern. Hier haben wir einen fest codierten Wert verwendet. Sie können auch Referenzen verwenden. Beispielsweise.
Sie können Range („A2“). Value verwenden, um den Suchwert aus Bereich A2 dynamisch zu aktualisieren.
3.
name = Application.WorksheetFunction.VLookup (LoginID, Bereich („A1: K26“), 2, 0)
Hier verwenden wir die VLOOKUP-Funktion, um zu erhalten. Wenn Sie nun die Funktion korrigieren und die Klammer öffnen, werden die erforderlichen Argumente angezeigt, die jedoch nicht so aussagekräftig sind wie in Excel. Überzeugen Sie sich selbst.
Sie müssen sich merken, wie und welche Variable Sie verwenden müssen. Sie können jederzeit zum Arbeitsblatt zurückkehren, um die Details der beschreibenden Variablen anzuzeigen.
Hier ist der Suchwert Arg1. Für Arg1 verwenden wir loginID. Die Nachschlagetabelle ist Arg2. Für Arg2 haben wir Range („A1: K26“) verwendet. Beachten Sie, dass wir A2: K26 nicht direkt wie in Excel verwendet haben. Der Spaltenindex ist Arg3.
Für Arg3 haben wir 2 verwendet, da der Name in der zweiten Spalte steht. Der Nachschlagetyp ist Arg4. Wir haben 0 als Arg4 verwendet.
city = Application.WorksheetFunction.VLookup (Anmelde-ID, Bereich („A1: K26“), 4, 0)
Ebenso erhalten wir den Städtenamen.
4.
MsgBox („Name:“ & Name & vbLf & „Stadt:“ & Stadt)
Schließlich drucken wir Name und Stadt mit Messagebox.
Warum die Arbeitsblattfunktion in VBA verwenden? Die Arbeitsblattfunktionen verfügen über immense Rechenleistung, und es ist nicht klug, die Leistungsfähigkeit von Arbeitsblattfunktionen zu ignorieren. Wenn wir beispielsweise die Standardabweichung eines Datensatzes möchten und Sie dafür den gesamten Code schreiben möchten, kann dies Stunden dauern. Wenn Sie jedoch wissen, wie Sie die Arbeitsblattfunktion STDEV.P in VBA verwenden, um die Berechnung auf einmal zu erhalten.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Verwenden mehrerer Arbeitsblattfunktionen VBA
Angenommen, wir müssen eine Indexübereinstimmung verwenden, um einige Werte abzurufen. Wie würden Sie nun die Formel in VBA schreiben? Ich denke, Sie werden Folgendes schreiben:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Das ist nicht falsch, aber es ist langwierig. Der richtige Weg, mehrere Funktionen zu verwenden, ist die Verwendung eines With-Blocks. Siehe das folgende Beispiel:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Wie Sie sehen können, habe ich With block verwendet, um VBA mitzuteilen, dass ich die Eigenschaften und Funktionen von Application.WorksheetFunction verwenden werde. Ich muss es also nicht überall definieren. Ich habe gerade den Punktoperator verwendet, um auf die Funktionen INDEX, MATCH, VLOOKUP und STDEV.P zuzugreifen. Sobald wir die End With-Anweisung verwenden, können wir nicht mehr auf Funktionen zugreifen, ohne vollständig qualifizierte Funktionsnamen zu verwenden.
Wenn Sie also mehrere Arbeitsblattfunktionen in VBA verwenden müssen, verwenden Sie mit Block.
Nicht alle Arbeitsblattfunktionen sind über Application.WorksheetFunction verfügbar. Einige Arbeitsblattfunktionen können direkt in VBA verwendet werden. Sie müssen das Application.WorksheetFunction-Objekt nicht verwenden.
Zum Beispiel Funktionen wie Len (), mit denen die Anzahl der Zeichen in einer Zeichenfolge, links, rechts, Mitte, Trimmen, Versatz usw. ermittelt wird. Diese Funktionen können direkt in VBA verwendet werden. Hier ist ein Beispiel.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Hier haben wir die LEN-Funktion verwendet, ohne das Application.WorksheetFunction-Objekt zu verwenden.
Ebenso können Sie andere Funktionen wie left, right, mid, char usw. verwenden.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub When you run the above sub, it will return:
|
5 He o ll |
Hoffe, dieser Artikel über die Verwendung von Arbeitsblattfunktionen wie VLOOKUP in VBA in Excel ist erklärend. Weitere Artikel zu VBA-Formeln und verwandten Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns unter [email protected].
Verwandte Artikel:
link: / excel-makros-und-vba-was-ist-csng-funktion-in-excel-vba [Was ist CSng-Funktion in Excel VBA]: Die SCng-Funktion ist eine VBA-Funktion, die jeden Datentyp in a konvertiert Gleitkommazahl mit einfacher Genauigkeit („vorausgesetzt, es handelt sich um eine Zahl“). Ich benutze meistens die CSng-Funktion, um textformatierte Zahlen in tatsächliche Zahlen umzuwandeln.
link: / vba-wie-man-die-text-nummer-in-umgekehrt-durch-vba-in-microsoft-excel erhält [wie man text & nummer in umgekehrt über VBA in Microsoft Excel erhält]: Um die Nummer und umzukehren Text verwenden wir Schleifen und Mid-Funktion in VBA. 1234 wird in 4321 konvertiert, „du“ wird in „uoy“ konvertiert. Hier ist der Ausschnitt.
link: / formatieren-in-vba-format-tabelle-abhängig-von-nummer-formaten-mit-vba-in-microsoft-excel [Daten mit benutzerdefinierten Nummernformaten mit VBA in Microsoft Excel formatieren]: Zum Ändern der Nummer Format für bestimmte Spalten in Excel Verwenden Sie dieses VBA-Snippet. Es verdeckt das Zahlenformat von angegebenem zu angegebenem Format mit einem Klick.
link: / tips-using-worksheet-change-event-to-run-macro, wenn eine Änderung vorgenommen wird [Verwenden des Arbeitsblatt-Änderungsereignisses zum Ausführen eines Makros, wenn eine Änderung vorgenommen wird]: So führen Sie Ihr Makro aus Bei jeder Aktualisierung des Arbeitsblatts verwenden wir die Arbeitsblattereignisse von VBA.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Makro ausführen, wenn Änderungen am Blatt im angegebenen Bereich vorgenommen wurden]: Zum Ausführen Ihres Makrocodes bei dem Wert Verwenden Sie in einem bestimmten Bereich Änderungen diesen VBA-Code. Es erkennt alle Änderungen, die im angegebenen Bereich vorgenommen wurden, und löst das Ereignis aus.
link: / events-in-vba-einfachster-vba-code-zum-hervorheben-der-aktuellen-zeile-und-spalte-mit [Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit]: Verwenden Sie dieses kleine VBA-Snippet zum Hervorheben die aktuelle Zeile und Spalte des Blattes.
Beliebte Artikel:
link: / tips-if-condition-in-excel [Verwendung der IF-Funktion in Excel]: Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung TRUE ist, oder gibt einen anderen bestimmten Wert zurück, wenn FALSE .
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.
link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.