Cum se filtrează tabelul pivot pe baza unei valori specifice a celulei în Excel?

În mod normal, filtram datele într-un tabel pivot selectând elemente din lista derulantă, așa cum se arată în captura de ecran de mai jos. De fapt, puteți filtra un tabel pivot pe baza valorii dintr-o anumită celulă. Metoda VBA din acest articol vă va ajuta să rezolvați problema.

Filtrează tabelul pivot pe baza unei valori specifice a celulei cu cod VBA

Filtrează tabelul pivot pe baza unei valori specifice a celulei cu cod VBA

Următorul cod VBA vă poate ajuta să filtrați un tabel pivot pe baza unei valori specifice a celulei în Excel. Vă rugăm să faceți următoarele.

1. Vă rugăm să introduceți în avans o valoare pe care să o filtrați pe tabelul pivot într-o celulă (aici selectez celula H6).

2. Deschideți foaia de lucru conține tabelul pivot pe care îl veți filtra după valoarea celulei. Apoi faceți clic dreapta pe fila foi și selectați Vizualizare cod din meniul contextual. Vedeți captura de ecran:

3. În deschidere Microsoft Visual Basic pentru aplicații fereastră, copiați mai jos codul VBA în fereastra Cod.

Cod VBA: Filtru Pivot Tabel pe baza valorii celulei

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

notițe: În cod,

1) "Sheet1”Este numele fișei de lucru.

2) "Tabel Pivot2”Este numele Tabelului pivot.

3) Câmpul de filtrare din tabelul pivot se numește „Categorii".

4) Valoarea pe care doriți să o filtrați în tabelul pivot este plasată în celulă H6.

Puteți modifica valorile variabilei de mai sus după cum aveți nevoie.

4. apasă pe Alt + Q tastele pentru a închide Microsoft Visual Basic pentru aplicații fereastră.

Apoi, tabelul pivot filtrează pe baza valorii din celula H6, după cum se arată în imaginea de mai jos:

Puteți schimba valoarea celulei cu alte persoane după cum aveți nevoie.

notițe: Valorile pe care le tastați în celula H6 trebuie să se potrivească exact cu valorile din lista derulantă a categoriei din tabelul pivot.

Legate de articole:

Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
Peste 300 de funcții puternice. Suportă Office / Excel 2007-2021 și 365. Acceptă toate limbile. Implementare ușoară în întreprinderea sau organizația dvs. Funcții complete Probă gratuită de 30 de zile. Garanție de returnare a banilor de 60 de zile.

Citeşte mai mult ... Descărcare gratuită... Cumpărare...

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!

Citeşte mai mult ... Descărcare gratuită... Cumpărare...

Comentarii (23)

Încă nu există evaluări. Fii primul care evaluează!

Daniel

în urmă cu aproximativ 4 de ani

#25756

Acest comentariu a fost redus la minimum de moderatorul de pe site

Folosind acest cod (actualizat pentru variabilele mele desigur), la schimbarea câmpului, filtrul se schimbă momentan în cel corect, apoi se șterge aproape imediat. Încercați să vă dați seama de ce face acest lucru (vă întrebați dacă are ceva de-a face cu ClearAllFilters de la sfârșitul sub-ului?)

răspuns

Justin

în urmă cu aproximativ 4 de ani

#26177

Acest comentariu a fost redus la minimum de moderatorul de pe site

Cum ați face acest lucru cu un filtru de raport care are o ierarhie?

răspuns

Ignatia

în urmă cu aproximativ 3 de ani

#30976

Acest comentariu a fost redus la minimum de moderatorul de pe site

Hei! Mulțumesc pentru macro.

Am încercat să-l folosesc pentru mai mult de un tabel pivot în aceeași pagină, dar nu funcționează. Am scris asa:

Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
Dim xPTable1 ca tabel pivot
Dim xPFile1 ca PivotField
Dim xStr1 ca șir
La data de eroare CV următoare
Dacă Intersect(Target, Range("D7")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable1 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA1")
Setați xPFile1 = xPTable1.PivotFields("ETAPA1")
xStr1 = Țintă.Text
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = Adevărat

Dim xPTable2 ca tabel pivot
Dim xPFile2 ca PivotField
Dim xStr2 ca șir
La data de eroare CV următoare
Dacă Intersect(Target, Range("G7")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable2 = Worksheets("BUSCADOR").PivotTables("PV_ETAPA2")
Setați xPFile2 = xPTable2.PivotFields("ETAPA2")
xStr2 = Țintă.Text
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = Adevărat

End Sub

Poate mă poți ajuta!

Multumesc in avans!

răspuns

michael Ignatia

în urmă cu aproximativ 2 de ani

#30977

Acest comentariu a fost redus la minimum de moderatorul de pe site

Hi

multumesc pentru Macro

Încerc același lucru, dar nu îl pot face să funcționeze pe 2 mese. amândoi se uită la aceeași celulă doar 2 tabele pivot diferite

Mulțumiri

răspuns

Oksana michael

despre 1 an în urmă

#30978

Acest comentariu a fost redus la minimum de moderatorul de pe site

Trebuie să schimbați numele tabelului pivot. Fiecare tabel pivot are un nume diferit. pentru a obține asta, faceți clic dreapta pe pivot și selectați setările tabelului pivot, numele va fi în partea de sus

răspuns

Ambrose Oksana

aproximativ 1 lună în urmă

#42326

Acest comentariu a fost redus la minimum de moderatorul de pe site

bonjour,

Je ne comprends pas comment ajouter le nom du second TCD în macro pentru că aceasta funcționează sur les deux.
Pourriez-vous m'aider?

mulțumesc

răspuns

Aravind

în urmă cu aproximativ 2 de ani

#32775

Acest comentariu a fost redus la minimum de moderatorul de pe site

Bună, din anumite motive, această macrocomandă după ce a intrat în pagina de bază vizuală, nu apare deloc. Nu pot activa/executa această macrocomandă, am verificat toate setările centrului de încredere, dar nu se întâmplă nimic, vă rog să mă ajutați

răspuns

buruiană

în urmă cu aproximativ 2 de ani

#33165

Acest comentariu a fost redus la minimum de moderatorul de pe site

Bună, se pare că nu pot face asta să funcționeze. Celula la care vreau să mă refer este extrasă dintr-o formulă - acesta ar fi motivul pentru care filtrul nu o poate găsi deoarece se uită la formulă și nu la valoarea pe care o returnează formula? Mulțumesc anticipat Heather McDonagh

răspuns

Sabrina buruiană

despre 1 an în urmă

#33166

Acest comentariu a fost redus la minimum de moderatorul de pe site

Salut Heather, ai gasit o solutie? Am exact aceeași problemă.

răspuns

AKTaylor

în urmă cu aproximativ 2 de ani

#33770

Acest comentariu a fost redus la minimum de moderatorul de pe site

Am reușit să modific/filtrez 3 pivoți diferiți care se află pe aceeași filă. Am adăugat și un rând în setul meu de date „No Data Found”, în caz contrar, a lăsat filtrul la „ALL” ceea ce nu am vrut. Cele de mai sus a fost de mare ajutor pentru a-mi câștiga Felicitari cu managementul, așa că am vrut să împărtășesc. Rețineți că (Toate) sunt sensibile la majuscule și minuscule mi-a luat puțin să-mi dau seama.
Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
'Test
Dim xPTable ca PivotTable
Dim xPFile ca PivotField
Dim xStr As String

Dim x2PTable ca tabel pivot
Dim x2PFile ca PivotField
Dim x2Str As String

Dim x3PTable ca tabel pivot
Dim x3PFile ca PivotField
Dim x3Str As String

La data de eroare CV următoare
Dacă Intersect(Target, Range("a2:e2")) nu este nimic, atunci ieșiți din sub

Application.ScreenUpdating = Fals

'tbl-1
Set xPTable = Foi de lucru(„Grafic”).PivotTables(„PivotTable1”)
Set xPFile = xPTable.PivotFields(„MR Department - Department”)
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Dacă xPFile.CurrentPage = „(Toate)” atunci xPFile.CurrentPage = „Nu s-au găsit date”

'tbl-2
Set x2PTable = Foi de lucru(„Grafic”).PivotTables(„PivotTable2”)
Set x2PFile = x2PTable.PivotFields(„MR Department - Department”)
x2Str = Target.Text
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str
Dacă x2PFile.CurrentPage = „(Toate)” atunci x2PFile.CurrentPage = „Nu s-au găsit date”

'tbl-3
Set x3PTable = Foi de lucru(„Grafic”).PivotTables(„PivotTable3”)
Set x3PFile = x3PTable.PivotFields(„MR Department - Department”)
x3Str = Target.Text
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str
Dacă x3PFile.CurrentPage = „(Toate)” atunci x3PFile.CurrentPage = „Nu s-au găsit date”

Application.ScreenUpdating = Adevărat

End Sub

răspuns

Drew

în urmă cu aproximativ 2 de ani

#34435

Acest comentariu a fost redus la minimum de moderatorul de pe site

Este posibil acest lucru cu Google sheets? Dacă da, cum?

răspuns

Vibhor Drew

despre 1 an în urmă

#34436

Acest comentariu a fost redus la minimum de moderatorul de pe site

Foi de calcul Google nu va necesita nici un tabel pivot. puteți efectua direct prin funcția de filtrare

răspuns

sdas34

în urmă cu aproximativ 2 de ani

#34609

Acest comentariu a fost redus la minimum de moderatorul de pe site

Aș dori să folosesc mai multe coduri de modificare a foii de lucru în aceeași foaie de lucru. Cum să faci asta? Codul meu este ca mai jos:
Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
„Filtrul de tabel pivotant bazat pe valoarea celulei
Dim xPTable ca PivotTable
Dim xPFile ca PivotField
Dim xStr As String
La data de eroare CV următoare
Dacă Intersect(Target, Range("D20:D21")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable = Foi de lucru(„Sheet1”).PivotTables(„PivotTable2”)
Set xPFile = xPTable.PivotFields(„Desemnare”)
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = Adevărat
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
„Filtrul tabelului pivot pe baza valorii celulei 2
Dim xPTable ca PivotTable
Dim xPFile ca PivotField
Dim xStr As String
La data de eroare CV următoare
Dacă Intersect(Target, Range("H20:H21")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable = Foi de lucru(„Sheet1”).PivotTables(„PivotTable2”)
Setați xPFile = xPTable.PivotFields(„Ofertă”)
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = Adevărat
End Sub

răspuns

Report

Fernando

despre 1 an în urmă

#35024

Acest comentariu a fost redus la minimum de moderatorul de pe site

Olá, gostaria de saber se quisesse filtrar mais de uma categoria ca poderia ser?

răspuns

Oksana

despre 1 an în urmă

#35785

Acest comentariu a fost redus la minimum de moderatorul de pe site

Ce se întâmplă dacă vreau să conectez celula de selecție la o altă filă? Acesta este codul meu de până acum
Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
Dim xPTable1 ca tabel pivot
Dim xPFile1 ca PivotField
Dim xStr1 ca șir
La data de eroare CV următoare
Dacă Intersect(Target, Range("B1")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable1 = Foi de lucru(„SM_SKU PIVOTS”).PivotTables(„PivotTable1”)
Setați xPFile1 = xPTable1.PivotFields(„Geografie”)
xStr1 = Țintă.Text
xPFile1.ClearAllFilters
xPFile1.CurrentPage = xStr1
Application.ScreenUpdating = Adevărat

Dim xPTable2 ca tabel pivot
Dim xPFile2 ca PivotField
Dim xStr2 ca șir
La data de eroare CV următoare
Dacă Intersect(Target, Range("B1")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable2 = Foi de lucru(„SM_SKU PIVOTS”).PivotTables(„PivotTable4”)
Setați xPFile2 = xPTable2.PivotFields(„Geografie”)
xStr2 = Țintă.Text
xPFile2.ClearAllFilters
xPFile2.CurrentPage = xStr2
Application.ScreenUpdating = Adevărat

Dim xPTable3 ca tabel pivot
Dim xPFile3 ca PivotField
Dim xStr3 ca șir
La data de eroare CV următoare
Dacă Intersect(Target, Range("B1")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable3 = Foi de lucru(„SM_SKU PIVOTS”).PivotTables(„PivotTable8”)
Setați xPFile3 = xPTable3.PivotFields(„Geografie”)
xStr3 = Țintă.Text
xPFile3.ClearAllFilters
xPFile3.CurrentPage = xStr3
Application.ScreenUpdating = Adevărat

End Sub

răspuns

Alex

în urmă cu aproximativ 7 luni

#38505

Acest comentariu a fost redus la minimum de moderatorul de pe site

Buna ziua!

Sunt nou cu VBA și aș dori să am un cod pentru a selecta un filtru pivot pe baza unui interval de celule.
Cum pot schimba „CurrentPage” să fie o valoare de interval?
Multumesc!!
-------------------------------------------------- -----------------------------------------
Sub PrintTour()

ActiveSheet.PivotTables(„PivotTable1”).PivotFields( _
„[Bereich 1].[Tur].[Tur ]”). _
Ștergeți toate filtrele
ActiveSheet.PivotTables(„PivotTable1”).PivotFields( _
„[Bereich 1].[Tur].[Tur]”). _
CurrentPage = „[Bereich 1].[Tour lt. Anlieferungstag].&[4001-01]”
End Sub

răspuns

lance

în urmă cu aproximativ 6 luni

#38840

Acest comentariu a fost redus la minimum de moderatorul de pe site

Mulțumesc mult pentru acest cod! L-am făcut să funcționeze după ce m-am ajustat pentru a corespunde câmpurilor mele, dar după formatarea unor modificări pe foaia mea acum nu funcționează! L-am mutat de la A1 la B1, am schimbat unele formatări ale celulelor pentru a o scoate în evidență etc. Nimic prea nebunesc, dar acum nu se actualizează când schimb textul în B1. Are cineva idei?

Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
'Test
Dim xPTable ca PivotTable
Dim xPFile ca PivotField
Dim xStr As String

Dim x2PTable ca tabel pivot
Dim x2PFile ca PivotField
Dim x2Str As String

Dim x3PTable ca tabel pivot
Dim x3PFile ca PivotField
Dim x3Str As String

La data de eroare CV următoare
Dacă Intersect(Target, Range("b1")) nu este nimic, atunci ieșiți din sub

Application.ScreenUpdating = Fals

'tbl-1
Set xPTable = Foi de lucru(„Line Report”).PivotTables(„PivotTable7”)
Set xPFile = xPTable.PivotFields(„Utopia Source”)
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr

'tbl-2
Set x2PTable = Foi de lucru(„Line Report”).PivotTables(„PivotTable2”)
Setați x2PFile = x2PTable.PivotFields(„Utopia Source”)
x2Str = Target.Text
x2PFile.ClearAllFilters
x2PFile.CurrentPage = x2Str

'tbl-3
Set x3PTable = Foi de lucru(„Line Report”).PivotTables(„PivotTable3”)
Setați x3PFile = x3PTable.PivotFields(„Utopia Source”)
x3Str = Target.Text
x3PFile.ClearAllFilters
x3PFile.CurrentPage = x3Str

Application.ScreenUpdating = Adevărat

End Sub

răspuns

cristal lance

în urmă cu aproximativ 6 luni

#38901

Acest comentariu a fost redus la minimum de moderatorul de pe site

Bună Lance,
Am testat codul tău și funcționează bine în cazul meu. Modificarea formatului celulei nu afectează funcționarea codului.

răspuns

Report

în urmă cu aproximativ 5 luni

#39652

Acest comentariu a fost redus la minimum de moderatorul de pe site

Cum funcționează cu Power Pivot, când utilizați mai multe tabele? Am înregistrat macro modificarea valorii în filtru. Am făcut câteva modificări pentru ca codul de mai sus să funcționeze. Dar aruncă o eroare de nepotrivire tip. Nu conteaza ce fac.

răspuns

cristal DK

în urmă cu aproximativ 5 luni

#39718

Acest comentariu a fost redus la minimum de moderatorul de pe site

Bună DK,
Metoda nu funcționează pentru Power Pivot. Îmi pare rău pentru neplăcerile create.

răspuns

Report

Ambrose

aproximativ 1 lună în urmă

#42309

Acest comentariu a fost redus la minimum de moderatorul de pe site

bonjour,
Vă mulțumesc foarte mult pentru acele explicații.

J'aimerai use un filtre (1 cell) en F4 par exemple qui filtrerait deux TCD qui sont sur la même feuille.

Cela fonctionne très bien avec un TCD mais dès que j'essaye de combiner le second, ça ne marche pas.
M-ai putea ajuta?

Merci beaucoup
Ambrose

răspuns

Ambrose

aproximativ 1 lună în urmă

#42324

Acest comentariu a fost redus la minimum de moderatorul de pe site

bonjour,

Merci beaucoup pentru această explicație care marchează perfect.
În revanche, j'aimerais pouvoir folosi ce cod pentru pouvoir filtrer deux tableaux croisés dynamiques en même temps, care sont sur la même feuille. La seule petite différence entre les deux, c'est qu'ils n'utilisent pas les mêmes sources. En revanche, le filtre sur lequel se bazează aceste TDC este le même.

Pourriez-vous m'aider à faire évoluer ce code afin que cela fonctionne ?

Acesta este codul utilizat când marșă cu un TCD:

Subsol de lucrări private_Change (țintă ByVal ca rază de acțiune)
'Actualizare până la Extendoffice 20180702
Dim xPTable ca PivotTable
Dim xPFile ca PivotField
Dim xStr As String
La data de eroare CV următoare
Dacă Intersect(Target, Range("G4")) nu este nimic, atunci ieșiți din sub
Application.ScreenUpdating = Fals
Set xPTable = Worksheets("Cadrage").PivotTables("Tableau croisé dynamique7")
Setați xPFile = xPTable.PivotFields("N°PROJET")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = Adevărat
End Sub

Merci beaucoup

răspuns

cristal Ambrose

acum aproximativ 4 saptamani

#42387

Acest comentariu a fost redus la minimum de moderatorul de pe site

Bună Ambroise,

Ne pare rău, este greu să modificați acest cod pentru a răspunde nevoilor dvs. Dacă doriți să filtrați mai multe tabele pivot cu un singur filtru, metodele din acest articol de mai jos vă pot ajuta:
Cum se conectează un singur slicer la mai multe tabele pivot în Excel?

răspuns

Report

Nu există comentarii postate aici încă

Lăsa comentarii

Postare ca invitat

Conectare

Nume (obligatoriu)

E-mail (Obligatoriu)

Evaluează această postare:

Reseteaza

0 Caractere

Sunt de acord cu termeni și condiții