Acest tutorial va arăta cum să utilizați filtrul tabelului pivot în VBA.
Tabelele pivot sunt un instrument de date extrem de puternic al Excel. Tabelele pivot ne permit să analizăm și să interpretăm cantități mari de date prin gruparea și rezumarea câmpurilor și rândurilor. Putem aplica filtre tabelelor noastre pivot pentru a ne permite să vedem rapid datele relevante pentru noi.
În primul rând, trebuie să creăm un tabel pivot pentru datele noastre. (Faceți clic aici pentru Ghidul nostru pentru tabelul pivot VBA).
Crearea unui filtru pe baza valorii unei celule
Puteți filtra într-un tabel pivot folosind VBA pe baza datelor conținute într-o valoare a celulei - putem filtra fie pe câmpul Pagină, fie pe un câmp Rând (de exemplu în câmpul Furnizor de mai sus sau câmpul Oper care se află în coloana Etichete rânduri ).
Într-o celulă goală din dreapta tabelului pivot, creați o celulă pentru a ține filtrul, apoi tastați datele în celula pe care doriți să filtrați tabelul pivot.
Creați următoarea macro VBA:
| 1234567 | Sub FilterPageValue ()Dim pvFld Ca PivotFieldDim strFilter As StringSetați pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Furnizor")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.CurrentPage = strFilterSfârșitul Sub |
Rulați macrocomanda pentru a aplica filtrul.
Pentru a șterge filtrul, creați următoarea macro:
| 12345 | Sub ClearFilter ()Dim pTbl Ca tabel pivotSetați pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersSfârșitul Sub |
Filtrul va fi apoi eliminat.
Apoi putem modifica criteriile de filtrare pentru a le filtra pe un rând din tabelul pivot, mai degrabă decât în pagina curentă.
Tastarea următoarei macrocomenzi ne va permite apoi să filtrăm pe rând (rețineți că câmpul pivot pe care să îl filtrați este acum Oper, mai degrabă decât Furnizor).
| 1234567 | Sub FilterRowValue ()Dim pvFld Ca PivotFieldDim strFilter As StringSetați pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.PivotFilters.Add2 xlCaptionEquals,, strFilterSfârșitul Sub |
Rulați macrocomanda pentru a aplica filtrul.
Utilizarea mai multor criterii într-un filtru pivot
Putem adăuga la filtrul Row value de mai sus adăugând criterii suplimentare.
Cu toate acestea, deoarece filtrul standard ascunde rândurile care nu sunt necesare, trebuie să parcurgem criteriile și să le arătăm pe cele care sunt solicitate, ascunzându-le în același timp pe cele care nu sunt necesare. Acest lucru se realizează prin crearea unei variabile Array și folosirea a două bucle în cod.
| 1234567891011121314151617181920212223 | Sub FilterMultipleRowItems ()Dim vArray As VariantDim i Integer, j As IntegerDim pvFld Ca PivotFieldSetați pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = Range ("M4: M5")pvFld.ClearAllFiltersCu pvFldPentru i = 1 Pentru pvFld.PivotItems.Countj = 1Faceți În timp ce j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Dacă pvFld.PivotItems (i) .Name = vArray (j, 1) AtuncipvFld.PivotItems (pvFld.PivotItems (i) .Name) .Vizibil = TrueIeșiți DoAltfelpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Vizibil = FalseEnd Ifj = j + 1BuclăApoi euSe termina cuSfârșitul Sub |
Crearea unui filtru pe baza unei variabile
Putem folosi aceleași concepte pentru a crea filtre pe baza variabilelor din codul nostru, mai degrabă decât valoarea dintr-o celulă. De data aceasta, variabila de filtrare (strFilter) este completată în codul propriu-zis (de exemplu: Hard-coded în macro).
| 1234567 | Sub FilterTextValue ()Dim pvFld Ca PivotFieldDim strFilter As StringSetați pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Furnizor")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterSfârșitul Sub |
