Suchergebnisse (54 Treffer, 1 Begriff) 
Kategorien: Filter/Sortieren und Tabelle ▸ Matrix
Ich möchte in einer Spalte die Anzahl eines bestimmten Begriffes. Dies funktioniert mit "Zählenwenn". Wenn ich aber Filter setze, ändert sich die Anzahl nicht.
Die 3 steht für Anzahl2, in C2 bis C6 befinden sich die zu zählenden Daten:
=TEILERGEBNIS(3;C2:C6)
Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics
Application-Ereignisse gelten für die gesamte Anwendung, für alle Fenster, Arbeitsmappen und Tabellenblätter. Um mit Ereignissen des Application-Objekts zu arbeiten, mu� man zuvor eine öffentliche Variable der Objektklasse in einem Klassenmodul definieren und danach ein Objekt der neuen Klasse und darin wieder ein Objekt der Klasse. Letzteres wird wiederum in einem einfachen Modul erstellt.
Was sich zunächst ein wenig kompliziert anhört, ist im Grunde recht einfach zu verwirklichen.
Sub ObjektZuordnen() Set Anwendungsobjekt.Anwendung = Application End Sub
Ab sofort können alle Ereignisse des Application-Objektes empfangen werden, wenn diese Routine ausgeführt wurde. Damit das neue Objekt immer zur Verfügung steht, sollte der letzte Code in Workbook_Open() der Mappe.
Wir können nun zum Besipiel jeden Blattwechsel in jeder offenen Mappe abfangen (also nicht nur in der mit dem Code), indem wir in das Klassenmodul eintragen:
Private Sub Anwendung_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name End Sub
Hinweis:
Um Application-Ereignisse zu deaktivieren, setzt man einfach den Verweis auf das "Anwendungsobjekt" auf Nothing, also Set Anwendungsobjekt.Anwendung = Nothing
Die Application-Ereignisse haben feste Parameter, die mit übergeben werden (im vorigen Code-Beispiel ist das schon an ByVal Sh As Object zu sehen):
| Wb: | Stellt die aktive Arbeitsmappe dar. |
| Sh: | Steht für das aktive Tabellenblatt. |
| Target: | Bezieht sich auf den aktiven Zellenbereich. |
| Cancel: | Hat den Wert False. Wird er in der Ereignisprozedur (z. B. bei BeforeSave) auf True gesetzt, wird das Ereignis nicht ausgeführt, sprich es wird nicht gespeichert. U. a. lässt sich so auch gut das Schlie�en einer Mappe abfangen. |
Am einfachsten wählt man die natürlich über das Dropdown im Klassenmodul, wie es in der obigen Abbildung dargestellt ist. Hier eine kleine �bersicht:
Kategorie: Steuerelemente ▸ ActiveX
Wie kann ich ein Kombinationsfeld füllen und den gewählten Wert an eine Zelle übergeben?
Es geht um das ActiveX-Kombinationsfeld auf einem Tabellenblatt:
Private Sub ComboBox1_Change() Range("A1") = ComboBox1.Text End Sub
Kategorie: Arrays ▸ Arrayformeln
Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?
=SUMMEWENN(B2:B13;"Juni";C2:C13)
Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.
Der grö�te Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:
=A2:C13
Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:
Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.
Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.
Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:
=WENN(C2:C13>400;B2:B13;"")
In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge grö�er als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.
Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:
=WENN(B2:B13="Juni";C2:C13;"")
Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie grö�er als 400 sind:
=SUMME(WENN(C2:C13>400;C2:C13))
In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten
Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.
Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.
Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äu�ere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.
Möglicherweise haben Sie schon die Meldung bekommen:
Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.
Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.
Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:
=WENN(C2:C13>400;C2:C13;"")
Sie liefert, wie wir gesehen haben, alle Werte, die grö�er als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:
=@WENN(C2:C13>400;C2:C13;"")
Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.
Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:
=@WENN(C2:C$13>400;C2:C$13;"")
Kategorie: Arrays ▸ Arrayformeln
Können Ergebnisse von Arrayformeln in VBA verwendet werden?
Integrierte Möglichkeiten von Excel (Funktionen, Methoden) können um ein Vielfaches schneller sein, als wenn man diese selbst per VBA erstellt bzw. nachgestaltet. So war es schon immer ein gro�er Unterschied, ob man die Find-Methode von Excel nutzte oder eine Schleife über viele Zellen laufen lie�, um einen bestimmten Wert zu finden. Insofern kann auch in Betracht gezogen werden, mit VBA Arrayformeln (bzw. integrierte Arrayfunktionen) zu nutzen und deren Ergebnisse zu verarbeiten.
Vor den Erläuterungen zu den Arrayfunktionen in VBA eine Vorbemerkung zu EVALUATE(), einer Methode, die recht unbekannt, hier aber oft nützlich ist.
Von anderen Sprachen kennt man eval() is evil - in Excel/VBA macht die Methode genau das, was hinter dem Spruch steckt, ist aber bei richtiger Verwendung ungefährlich. Eine Beschreibung ist bei Microsoft zu finden; wir nutzen diese Methode hier, um Formeln/Funktionen interpretieren zu lassen und zu Ergebnissen zu führen.
Dieser Hinweis war wichtig, weil hier in der Folge eine Formel ohne Funktion (also auch ohne Application.WorksheetFunction) verwendet wird, um Arrayformeln in VBA möglichst einfach zu demonstrieren:
Basis zum Verwenden von Arrayformeln in VBA ist, dass sie Arrays zurückgeben. Schlie�lich ist dies auch beim Einsatz auf dem Tabellenblatt der Fall, nur dass wir dort die Ergebnisse in Zellen sehen. Entsprechend ist auch wichtig zu wissen, dass Bereiche in Arrayergebnissen zwei Dimensionen haben können, strukturiert in Zeilen und Spalten sind.
Die Bereiche in den Arrays aus Arrayformeln sind grundsätzlich in Zeilen gegliedert, sie sind die erste Dimension. Wenn wir nun also in C2:C7 Einträge haben und mit arr = Evaluate("=C2:C7") (statt Evaluate wäre auch Range("C2:C7") möglich) darauf zugreifen, wird als Ubound folgerichtig die 6 ausgegeben. Entsprechend kann mit einer Schleife For intI = 1 To UBound(arr) über die einzelnen Elemente des Arrays gegangen werden.
Allerdings muss jetzt beachtet werden, dass die einzelnen Werte in der ersten Spalte des Arrays stehen - das erste (und in diesem Beispiel einzige) Element in der zweiten Dimension. Ein Zugriff auf arr(intI) würde einen Fehler erzeugen, da hier die Angabe der Spalte fehlt. Erst mit dieser Angabe wird der gewünschte Eintrag geliefert: arr(intI, 1).
Der Aufbau des Codes wäre also:
Sub Test() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=C2:C7") MsgBox "Einträge: " & UBound(arr) For intI = 1 To UBound(arr) strTemp = arr(intI, 1) ' Einträge in Zeilen! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub
Im Beispiel standen die Werte untereinander in den Zellen. Was nun aber, wenn die Werte nebeneinander stehen, zum Beispiel in D1:I1?
In diesem Fall besteht der Array nur aus einer Zeile, der Ubound wird somit 1 sein.
Um an die einzelnen Elemente zu gelangen, gibt es nun die erste Möglichkeit, per Schleife die Elemente dieser einen Zeile zu liefern, wobei allerdings bekannt sein muss, wie viele Elemente die Zeile hat:
For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next
Eine Alternative dazu ist, den Array zu transponieren. Dies geht mit der Funktion MTRANS() in der englischen Schreibweise bereits beim Ã?bergeben an die Variable:
arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1"))
Damit sind die ehemaligen Spalten nun die Zeilen und der Array kann wie vorher mit Schleife bis zum Ubound (der jetzt im Beispiel wieder 6 ist) durchlaufen werden.
Der Code zum Testen:
Sub Test1() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=D1:I1") MsgBox "Einträge: " & UBound(arr) ' 1, weil nur 1 Zeile For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub Sub Test2() Dim arr, intI As Integer, strTemp As String arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1")) MsgBox "Einträge: " & UBound(arr) For intI = 1 To 6 strTemp = arr(intI, 1) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub
Kategorie: Arrays ▸ Arrayformeln
Wie kann ich die Funktion EINDEUTIG() (in VBA) nutzen?
Ab Excel 365 gibt es neben der Möglichkeit, Duplikate zu entfernen, auch eine Funktion zum Einsatz in einer Formel: EINDEUTIG(). Die Funktion sucht in einer Tabelle nach doppelten Datensätzen und gibt in der einfachen Variante jeden nur einmal aus. Weitere Informationen zu Parametern der Formel gibt es bei Microsoft: EINDEUTIG-Funktion.
Im Beispiel ist zu sehen, dass die Monate Februar und zweimal Mai im Ergebnis nur jeweils einmal erscheinen, weil diese Monate auch jeweils die gleichen Zahlen haben. Der Juni ist jedoch zweimal im Ergebnis enthalten, weil diese Datensätze unterschiedliche Zahlen haben und somit insgesamt unterschiedlich sind.
Auch mit VBA kann diese Funktion doppelte Datensätze ausfiltern, indem die englische Schreibweise zum Einsatz kommt:
Application.WorksheetFunction.Unique(Array)
Im Beispiel wird die Tabelle aus der Abbildung im Bereich A2:E15 verwendet. Hier sind die Datensätze bei Frau Linz identisch und zwei Datensätze bei Frau Herzig. Aus dieser Tabelle erstellen wir den Array:
arr = Range("A2:E15")
Zum Herausfiltern der doppelten Datensätze wird die Funktion eingesetzt:
arr = Application.WorksheetFunction.Unique(arr)
Das Ergebnis ist nun in der Variablen arr der Array mit den eindeutigen Datensätzen. Dabei gibt es jedoch zwei verschiedene mögliche Fälle:
Es können (wie im Beispiel) mehrere Zeilen sein. Dann kann der Array von 1 bis zum Ubound (der hier die Anzahl der Zeilen im Ergebnis ist) mit arr(Zeile, Spalte) durchlaufen werden:
For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next
Hier würde für jede Zeile eine MsgBox mit Anrede Name, Vorname erscheinen.
Es kann aber auch der Fall eintreten, dass im Ergebnis nur eine Zeile übrig bleibt, die nun als Array vorliegt. Hier enthält der Array jedoch nicht die einzelne Zeile als Arrayelement der ersten Dimension, sondern bereits die einzelnen Elemente in der ersten Ebene. In dem Fall würde ein Zugriff mit arr(Zeile, Spalte) zu einem Fehler führen, weil das Auslesen nur mit arr(Spalte) erfolgen darf.
Wenn wir im VBA-Code also beide Fälle berücksichtigen wollen, müssen wir prüfen, ob der Array aus mehreren Zeilen zu mehreren Spalten oder nur aus mehreren Spalten ohne Zeile besteht. Dazu bietet sich an, die Anzahl aller Elemente des Arrays festzustellen:
intAnzahlEl = Application.WorksheetFunction.CountA(arr)
Wenn diese Zahl gleich dem Ubound des Arrays ist, muss es sich um einen eindimensionalen handeln, weil das dann die einzelnen Spalten sind. Wenn nicht, handelt es sich um einen mehrzeiligen Array, weil es dann immer mehr Elemente als der Ubound sind (Zeilen * Spalten = intAnzahlEl).
Und so können wir in unserem Code gut die Weiche stellen - an der Stelle der MsgBoxen müsste die eigentliche Verarbeitung der Daten rein:
Sub Eindeutig_vba() Dim arr, intI As Integer, intAnzahlEl As Integer arr = Range("A2:E15") 'Zur Ausgabe von mehreren Zeilen 'arr = Range("A2:E2") 'Zur Testausgabe einer Zeile arr = Application.WorksheetFunction.Unique(arr) 'Anzahl aller(!) Elemente im Array: intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(1) & " " & arr(2) & ", " & arr(3) Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next End If End Sub
Tipp - Sortieren:
Wenn das Ganze sortiert werden soll, kann das auch gleich am Anfang mit der integrierten Funktion erledigt werden:
arr = Application.WorksheetFunction.Unique(arr) arr = Application.WorksheetFunction.Sort(arr, 2)
In dem Beispiel wäre der frische Array nach den Namen sortiert.
