1. Update v. 09.08.2020:
- Enthält-Filter ergänzt
2. Update v. 14.08.2020:
- Der Enthält-Filter funktioniert nur für max. 2 Kriterien und ist nur für Text anwendbar (entsprechend angepasst)
- Tabs am Beginn oder Ende eines Kriteriums werden ignoriert (Idee von Jakobus)
3. Update v. 20.08.2020:
- Trennung der Filter auf 2 Hotkeys: Strg+E => "Enthält" / Strg+R => "Ist gleich" / Strg+Shift+R => Rücknahme eines Spaltenfilter bleibt für alle Filterarten gleich
- Das Auswahlmenü entfällt, da unergonomisch
- Bei mehr als 2 Kriterien und Hotkey Strg + E ("Enthält"), wird eine Abfrage angezeigt, ob stattdessen mit "Ist Gleich" gefiltert werden soll.
- Mehrere Tabs und Leerzeichen zum Beginn / Ende der Kriterien werden ignoriert
4. Update v. 31.10.2020:
- Bug entfernt, wodurch eine Hinweis-Msgbox immer an der falschen Stelle erschien
- Überflüssigen Ausnahmehinweis bei Strg+Shift+R (=Filter für aktive Spalte löschen) entfernt. (Dieser erschien z.B. falls man den Cursor ausserhalb des Filterbereiches plaziert und dann diesen Hotkey gedrückt hatte.)
5. Update v. 15.11.2020:
- ListObjects Unterstützung (=Formatierung als Tabelle) hatte nicht mehr funktioniert
- Excel Schnellassistent, welcher manchmal angezeigt wurde, wird am Ende mit 2 x ESC geschlossen
- kleine Verbesserung, Clipboard wird sofort in Variable gespeichert, was für mehr Zuverlässigkeit sorgt
dieses Skript nutze ich schon seit einiger Zeit und möchte es gern teilen, da ich es sehr praktisch finde
Es ermöglicht einen Filter in Excel für die in der Zwischenablage befindlichen Werte zu setzen.
Filter setzen:
Hierzu kopiert man zunächst Werte in die Zwischenablage und klickt in eine Zelle der Spalte für die man diese Werte als Filter einstellen möchte. Mit dem Hotkey Strg+R, werden nur exakte Treffer (Ist gleich) und mit Strg+E alle Treffer (Enthält) angezeigt. (Achtung: Der Enthält-Filter funktioniert nur mit max. 2 Kriterien als Textfilter. Werden mehr als 2 Kriterien in die Zwischenablage kopiert, wird eine Abfrage angezeigt, ob stattdessen mit "Ist Gleich" gefiltert werden soll.) Kopiert man erneut Werte in die Zwischenablage, lassen sich auf die gleiche Weise weitere Spalten filtern.
Filter löschen:
- Zurücknehmen lässt sich der Filter für pro Spalte einfach, indem man wieder in eine Zelle der Spalte klickt und dann den Hotkey Strg+Shift+R drückt.
- Alle Filter lassen sich über den Excel Shortcut Strg+Shift+L ein-/ und ausschalten. (Danke an Jakobus für diesen Hinweis)
Einschränkungen:
- Hat man die Werte bereits in der Zwischenablage und führt in der Zieltabelle noch andere Operation durch, wie z.B. das Löschen eines anderen bereits gesetzten Filters, so muss man die Daten erneut in die Zwischenablage kopieren, weil Excel die Zwischenablage verwirft.
- Probleme gibt es, wenn ein Filter nur für bestimmte Spalten aktiviert ist und man aber über andere Spalte filtern möchte, für die der Filter nicht aktiviert ist. -> Am sichersten ist es daher, wenn in der Zieltabelle entweder noch keine Filter oder für alle Spalten Filter aktiviert sind.
Ich konnte bisher auch in großen Listen mit ca. 100.000 Einträgen keine Einschränkungen entdecken.
Viel Spaß...
Code: Select all
;Update Ver. 5
#IfWinActive ahk_class XLMAIN
^e:: ;;Autofilter in Excel <Enthält> falls max. 2 Kriterien in der Zwischenablage. (Funktioniert nur für Zellen, die als Text interpretiert werden)
Exact_InStr := 2 ;=Enthält
GoSub Filter_Enthaelt
return
^r:: ;;Autofilter in Excel <Ist gleich> mit mehreren Daten setzen, die in der Zwischenablage gespeichert sind.
Exact_InStr := 1 ;=Ist gleich
Filter_Enthaelt:
setbatchlines, -1
xl := Excel_Get()
if !IsObject(xl)
{
msgbox,4096,, Es konnte keine Verbindung zu Excel aufgebaut werden.
return
}
MyClipBoard := Clipboard
;ermittle die Anzahl der Zeilen in der Zwischenablage
Anz_Zeilen := 0
Loop, parse, MyClipBoard, `n, `r
{
If (A_LoopField = "")
continue
Anz_Zeilen++
}
If (Anz_Zeilen = 0 or Trim(MyClipBoard) = "") ;falls keine Zeile aktiv
{
msgbox,4096,, Um für bestimmte Daten einen Autofilter einzustellen, bitte diese vorher in die Zwischenablage kopieren, dann in die zu filternde Spalte klicken, falls diese abweicht und dann Strg + R um den Autofilter zu aktivieren.`n`nMit Strg+Shift+R lässt sich der Autofilter für die aktive Spalte wieder löschen.
xl := Anz_Zeilen := MyClipBoard := ""
return
}
FilterAlreadySet := True
If (xl.ActiveSheet.AutoFilterMode = 0 && !isObject(xl.ActiveCell.ListObject)) ;wenn Autofilter nicht aktiviert
{
try
{
xl.ActiveSheet.UsedRange.AutoFilter ;:= true ;Aktiviere Autofilter
FilterAlreadySet := False
}
}
r := xl.ActiveSheet.AutoFilter.Range
If (r.Column = "") ;falls ggf. ListObject und noch kein Autofilter aktiviert
{
try xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Range.AutoFilter ;Autofilter aktivieren
try r := xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Autofilter.Range
}
;Check, ob aktive Zelle im Filterbereich steht
If !(xl.ActiveCell.Column >= r.Column && xl.ActiveCell.Column <= r.Column + r.Columns.Count - 1)
{
If FilterAlreadySet
msgbox,,, Um einen Autofilter zu setzen, bitte eine Zelle in dem Autofilterbereich anklicken oder den Autofilter erst deaktivieren!
else
msgbox,,, Um einen Autofilter zu setzen, bitte eine Zelle in dem Autofilterbereich anklicken!
xl := r := Anz_Zeilen := FilterAlreadySet := MyClipBoard := ""
return
}
col := (xl.ActiveCell.Column - xl.ActiveSheet.Autofilter.Range.Column + 1) ;ermittelt die Spalte für den Autofilterbereich wo der Filter gesetzt werden soll
If (col = "")
col := xl.ActiveCell.Column - xl.ActiveSheet.Usedrange.Column + 1 ;ermittelt die Spalte falls kein Autofilterbereich existiert
; Exact = Ist gleich (1), InStr = Enthält (1->Defaultwert, falls Menu mit Esc abgebrochen wird o. mehr als 2 Kriterien)
If (Anz_Zeilen >2 && Exact_InStr = 2) ;Filter "enthält" funktioniert nur, bis max. 2 Kriterien.
{
Exact_InStr := 1
MsgBox,% 4097+48,, Bei mehr als 2 Suchkriterien ist nur Filter "Ist gleich" möglich.`n`nDiesen jetzt anwenden?
IfMsgBox, cancel
{
xl := r := Anz_Zeilen := FilterAlreadySet := MyClipBoard := ""
return
}
}
;Array definieren für die Anzahl der Zeilen
arr := ComObjArray(VT_VARIANT:=12, Anz_Zeilen * Exact_InStr)
index := 0
If (Exact_InStr = 2) ;Wenn enthält gewählt wurde, muss der Suchbegriff in Wildcards "*Suchbegriff*"
{
If (Anz_Zeilen = 1)
{
arr[0] := "*" Trim(ClipBoard, " `t`r`n") "*"
If isObject(xl.ActiveCell.ListObject) ;wenn es sich um einen als Tabelle formatierten Bereich handelt, ist es ein ListObjekt
try xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Range.AutoFilter(col, arr[0], 1) ;Field :=col, Criteria1:=arr[0], Operator 1 (=xlAnd)
else
try xl.ActiveSheet.Usedrange.AutoFilter(Field :=col, Criteria1:=arr[0], Operator:=1)
catch e
MsgBox, 16,, % "Zum Filtern von Daten müssen diese in die Zwischenablage kopiert und dann eine Zelle in der gewünschten Spalte angeklickt werden.`n`n`nNachricht: " e.message "`nZusatz: " e.extra
}
else ;if Anz_Zeilen = 2
{
Loop, parse, MyClipBoard, `n, `r
{
If (A_LoopField = "")
continue
index++
arr[index - 1] := "*" Trim(A_LoopField, " `t") "*"
}
If isObject(xl.ActiveCell.ListObject) ;wenn es sich um einen als Tabelle formatierten Bereich handelt, ist es ein Objekt
try xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Range.AutoFilter(col, arr[0], 2, arr[1]) ;Field :=col, Criteria1:=arr[0], Operator 2 (=xlOr), Criteria2:=arr[1]
else
try xl.ActiveSheet.Usedrange.AutoFilter(col, arr[0], 2, arr[1])
catch e
MsgBox, 16,, % "Zum Filtern von Daten müssen diese in die Zwischenablage kopiert und dann eine Zelle in der gewünschten Spalte angeklickt werden.`n`n`nNachricht: " e.message "`nZusatz: " e.extra
}
}
else ;bei genauer (Ist gleich) Suche ohne Wildcards
{
Loop, parse, MyClipBoard, `n, `r
{
If (A_LoopField = "")
continue
index++
arr[index - 1] := Trim(A_LoopField, " `t")
}
If isObject(xl.ActiveCell.ListObject) ;wenn es sich um einen als Tabelle formatierten Bereich handelt, ist es ein Objekt
{
;msgbox,4096,, % col "`n" xl.ActiveCell.ListObject.name
xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Range.AutoFilter(col, arr, 7) ;Field :=col, Criteria1:=arr, Operator 7 (=xlFilterValues)
}
else
try xl.ActiveSheet.Usedrange.AutoFilter(col, arr, 7)
catch e
MsgBox, 16,, % "Zum Filtern von Daten müssen diese in die Zwischenablage kopiert und dann eine Zelle in der gewünschten Spalte angeklickt werden.`n`n`nNachricht: " e.message "`nZusatz: " e.extra
}
ControlSend, Excel71, {esc 2},ahk_class XLMAIN ;Schnellanalysetool ggf. schließen
xl:= r := arr := col := Anz_Zeilen := index := FilterAlreadySet := MyClipBoard := ""
return
#IfWinActive
#Ifwinactive ahk_class XLMAIN
^+r:: ;;Excel Autofilter in der aktiven Spalte löschen
setbatchlines, -1
xl := Excel_Get()
col := xl.ActiveCell.Column - xl.ActiveSheet.Autofilter.Range.Column + 1 ;ermittelt die Spalte falls ein Autofilterbereich existiert wo der Filter gelöscht werden soll
If (col = "")
col := xl.ActiveCell.Column - xl.ActiveSheet.Usedrange.Column + 1 ;ermittelt die Spalte falls kein Autofilterbereich existiert
If (col = 0)
{
msgbox,4096,, Um für einen Autofilter zu löschen, muss in eine Zelle der gewünschten Spalte geklickt werden.
xl := col := ""
return
}
If isObject(xl.ActiveCell.ListObject) ;wenn es sich um einen als Tabelle formatierten Bereich handelt, ist es ein Objekt
try xl.ActiveSheet.ListObjects(xl.ActiveCell.ListObject.name).Range.AutoFilter(Field :=col)
else
try xl.ActiveSheet.Range("A1").End(-4121).AutoFilter(Field :=col)
;catch e ;nicht hilfreicher Ausnahmehinweis auskommentiert /31.10.2020
; MsgBox, 16,, % "Ausnahme ausgelöst!`n`nWas: " e.what "`nDatei: " e.file "`nZeile: " e.line "`nNachricht: " e.message "`nZusatz: " e.extra
xl := col := ""
return
;liefert ein COM-Objekt für das aktive Excel-Fenster
Excel_Get(WinTitle="ahk_class XLMAIN") { ; by Sean and Jethrow, minor modification by Learning one
ControlGet, hwnd, hwnd, , Excel71, %WinTitle%
if !hwnd
return
Window := Acc_ObjectFromWindow(hwnd, -16)
Loop
try
WinApplication := Window.Application
catch
ControlSend, Excel71, {esc}, %WinTitle%
Until !!WinApplication
return WinApplication
}
; Teile aus Acc.ahk Standard Library
; by Sean
; Updated by jethrow:
; Modified ComObjEnwrap params from (9,pacc) --> (9,pacc,1)
; Changed ComObjUnwrap to ComObjValue in order to avoid AddRef (thanks fincs)
; Added Acc_GetRoleText & Acc_GetStateText
; Added additional functions - commented below
; Removed original Acc_Children function
; last updated 2/25/2010
;------------------------------------------------------------------------------
Acc_Init(){
Static h
If Not h
h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
}
Acc_ObjectFromWindow(hWnd, idObject = -4){
Acc_Init()
If DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", idObject&=0xFFFFFFFF, "Ptr", -VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
Return ComObjEnwrap(9,pacc,1)
}