info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Tabelle Pivot: Filtro e VBA
634 views
Skip to first unread message
casanmaner
Feb 22, 2018, 10:56:45 AM2/22/18
to
Questa volta faccio io una domanda :)
Premesso che non conosco bene l'oggetto PivotTables perché non ho mai utilizzato molto questo strumento.
Ma visto che mi è comodo per una certa esigenza stavo provando a lavorarci un po'.
In particolare mi interessava capire il funzionamento del filtro.
In particolare si tratta di una serie di dati associati a delle date.
Il filtro è impostato sul campo data.
L'utilizzo in "manuale" del filtro non è particolarmente agevole perché o si imposta il filtro per un elemento alla volta o, impostando l'opzione "Seleziona più elementi", occorre andare selezionare/deselezionare i singoli elementi (non potendo filtrare ad es. per "gruppi di elementi".
Allora la mia idea era quella di prevedere due celle dove inserire il numero di mese iniziale e/o finale ed eseguire un filtro tramite VBA.
Ho provato a registrare la macro per vedere come il filtro viene applicato a livello di macro e la macro ha registrato prima un comando che renederebbe visibili tutti gli "item" del campo data.
Poi va a rendere non visibili gli "item" delle date non selezionate.
La particolarità che ho riscontrato è che se poi lancio la medesima macro il comando che dovrebbe impostare come tutti selezionati gli item non modifica la selezione.
Ma, cosa che trovo ancora più strana, è che poi viene dato errore in corrispondenza del primo item di cui si cerca di impostare la proprietà Visible=False.
Viene dato il debug
Errore di run-time 1004
Impossibile trovare la proprietà PivotItems per la classe PivotField.
Per intenderci il registratore ha registrato questi comandi:
ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data")
.PivotItems("02/01/2018").Visible = False
.PivotItems("03/01/2018").Visible = False
.PivotItems("04/01/2018").Visible = False
End With
La prima riga di comando non riporta il filtro su "(Tutto)".
La parte che dovrebbe nascondere gli Items "deselezionati" dà errore in corrispondenza di:
.PivotItems("02/01/2018").Visible = False
Qualcuno sa darmi una spiegazione del "fenomeno"?
Grazie e ciao
Premesso che non conosco bene l'oggetto PivotTables perché non ho mai utilizzato molto questo strumento.
Ma visto che mi è comodo per una certa esigenza stavo provando a lavorarci un po'.
In particolare mi interessava capire il funzionamento del filtro.
In particolare si tratta di una serie di dati associati a delle date.
Il filtro è impostato sul campo data.
L'utilizzo in "manuale" del filtro non è particolarmente agevole perché o si imposta il filtro per un elemento alla volta o, impostando l'opzione "Seleziona più elementi", occorre andare selezionare/deselezionare i singoli elementi (non potendo filtrare ad es. per "gruppi di elementi".
Allora la mia idea era quella di prevedere due celle dove inserire il numero di mese iniziale e/o finale ed eseguire un filtro tramite VBA.
Ho provato a registrare la macro per vedere come il filtro viene applicato a livello di macro e la macro ha registrato prima un comando che renederebbe visibili tutti gli "item" del campo data.
Poi va a rendere non visibili gli "item" delle date non selezionate.
La particolarità che ho riscontrato è che se poi lancio la medesima macro il comando che dovrebbe impostare come tutti selezionati gli item non modifica la selezione.
Ma, cosa che trovo ancora più strana, è che poi viene dato errore in corrispondenza del primo item di cui si cerca di impostare la proprietà Visible=False.
Viene dato il debug
Errore di run-time 1004
Impossibile trovare la proprietà PivotItems per la classe PivotField.
Per intenderci il registratore ha registrato questi comandi:
ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data")
.PivotItems("02/01/2018").Visible = False
.PivotItems("03/01/2018").Visible = False
.PivotItems("04/01/2018").Visible = False
End With
La prima riga di comando non riporta il filtro su "(Tutto)".
La parte che dovrebbe nascondere gli Items "deselezionati" dà errore in corrispondenza di:
.PivotItems("02/01/2018").Visible = False
Qualcuno sa darmi una spiegazione del "fenomeno"?
Grazie e ciao
Norman Jones
Feb 22, 2018, 11:21:43 AM2/22/18
to
On 22/02/2018 15:56, casanmaner wrote:
> Questa volta faccio io una domanda :)
> Premesso che non conosco bene l'oggetto PivotTables perché non ho mai utilizzato molto questo strumento.
> Ma visto che mi è comodo per una certa esigenza stavo provando a lavorarci un po'.
> In particolare mi interessava capire il funzionamento del filtro.
> In particolare si tratta di una serie di dati associati a delle date.
> Il filtro è impostato sul campo data.
> L'utilizzo in "manuale" del filtro non è particolarmente agevole perché o si imposta il filtro per un elemento alla volta o, impostando l'opzione "Seleziona più elementi", occorre andare selezionare/deselezionare i singoli elementi (non potendo filtrare ad es. per "gruppi di elementi"..
> Questa volta faccio io una domanda :)
> Premesso che non conosco bene l'oggetto PivotTables perché non ho mai utilizzato molto questo strumento.
> Ma visto che mi è comodo per una certa esigenza stavo provando a lavorarci un po'.
> In particolare mi interessava capire il funzionamento del filtro.
> In particolare si tratta di una serie di dati associati a delle date.
> Il filtro è impostato sul campo data.
> Allora la mia idea era quella di prevedere due celle dove inserire il numero di mese iniziale e/o finale ed eseguire un filtro tramite VBA.
>
> Ho provato a registrare la macro per vedere come il filtro viene applicato a livello di macro e la macro ha registrato prima un comando che renederebbe visibili tutti gli "item" del campo data.
> Poi va a rendere non visibili gli "item" delle date non selezionate.
>
> La particolarità che ho riscontrato è che se poi lancio la medesima macro il comando che dovrebbe impostare come tutti selezionati gli item non modifica la selezione.
> Ma, cosa che trovo ancora più strana, è che poi viene dato errore in corrispondenza del primo item di cui si cerca di impostare la proprietà Visible=False.
> Viene dato il debug
> Errore di run-time 1004
> Impossibile trovare la proprietà PivotItems per la classe PivotField.
>
> Per intenderci il registratore ha registrato questi comandi:
>
> ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data").CurrentPage = _
> "(All)"
>
>
> With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data")
> .PivotItems("02/01/2018").Visible = False
> .PivotItems("03/01/2018").Visible = False
> .PivotItems("04/01/2018").Visible = False
> End With
>
> La prima riga di comando non riporta il filtro su "(Tutto)".
>
> La parte che dovrebbe nascondere gli Items "deselezionati" dà errore in corrispondenza di:
> ..PivotItems("02/01/2018").Visible = False
>
> Ho provato a registrare la macro per vedere come il filtro viene applicato a livello di macro e la macro ha registrato prima un comando che renederebbe visibili tutti gli "item" del campo data.
> Poi va a rendere non visibili gli "item" delle date non selezionate.
>
> La particolarità che ho riscontrato è che se poi lancio la medesima macro il comando che dovrebbe impostare come tutti selezionati gli item non modifica la selezione.
> Ma, cosa che trovo ancora più strana, è che poi viene dato errore in corrispondenza del primo item di cui si cerca di impostare la proprietà Visible=False.
> Viene dato il debug
> Errore di run-time 1004
> Impossibile trovare la proprietà PivotItems per la classe PivotField.
>
> Per intenderci il registratore ha registrato questi comandi:
>
> ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data").CurrentPage = _
> "(All)"
>
>
> With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Data")
> .PivotItems("02/01/2018").Visible = False
> .PivotItems("03/01/2018").Visible = False
> .PivotItems("04/01/2018").Visible = False
> End With
>
> La prima riga di comando non riporta il filtro su "(Tutto)".
>
> La parte che dovrebbe nascondere gli Items "deselezionati" dà errore in corrispondenza di:
>
> Qualcuno sa darmi una spiegazione del "fenomeno"?
Ho letto la tua domanda molto in fretta perche' sono in giro. Tuttavia,
> Qualcuno sa darmi una spiegazione del "fenomeno"?
in attesa di una eventuale risposta più approfondita, ti farei notare
che, come accade molto spesso, il codice del registratore possa non
essere efficiente o opportuno.
Per togliere tutti i filtri di un PivotField, prova qualcosa del genere:
'=========>>
Option Explicit
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Const sFoglio As String = "Foglio1"
Const sPivotField As String = "Pippo"
Set WB = ThisWorkbook
Set SH = WB.Sheets(sFoglio)
Set PT = SH.PivotTables(1)
Set PF = PT.PivotFields(sPivotField)
PF.ClearAllFilters
End Sub
'<<=========
===
Regards,
Norman
casanmaner
Feb 22, 2018, 11:31:44 AM2/22/18
to
Ciao Norman,
ti rigranzio e sono consapevole dei limiti del registratore.
Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
Diciamo che il problema principale è dato dall'applicare il filtro.
In realtà, visto come aveva agito il registratore, nascondendo gli "Items" non selezionati, per fare una prova, e senza aver prima riprovato a lanciare la macro registrata, avevo scritto una cosa del genere (dove nel caso specifico andavo a nascondere tutti gli elementi il cui mese era diverso da 1):
Sub TestFiltro()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Set pvtTable = Foglio2.PivotTables("Tabella_pivot1")
Set pvtField = pvtTable.PivotFields("Data")
With pvtTable
.ClearAllFilters
End With
With pvtField
.EnableMultiplePageItems = True
For Each pvtItem In .PivotItems
If Month(CDate(pvtItem)) <> 1 Then
pvtItem.Visible = False
End If
Next
End With
End Sub
Ma dava errore in corrispondenza di " pvtItem.Visible = False".
A quel punto ho registrato nuovamente una macro per simulare di nuovo il filtro e ho lanciato poi la stessa macro rilevando che anche con essa veniva generato l'errore.
Nota che se in luogo di pvtItem.Visible = False inserisco ad es.
Debug.Print pvtItem.Name
nella finestra immediata viene "stampato" il nome dell'item(es. 02/01/2018).
Quindi l'item viene individuato correttamente ma non è possibile impostare la proprietà Visible, come invece il registratore di macro farebbe supporre possibile.
ti rigranzio e sono consapevole dei limiti del registratore.
Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
Diciamo che il problema principale è dato dall'applicare il filtro.
In realtà, visto come aveva agito il registratore, nascondendo gli "Items" non selezionati, per fare una prova, e senza aver prima riprovato a lanciare la macro registrata, avevo scritto una cosa del genere (dove nel caso specifico andavo a nascondere tutti gli elementi il cui mese era diverso da 1):
Sub TestFiltro()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Set pvtTable = Foglio2.PivotTables("Tabella_pivot1")
Set pvtField = pvtTable.PivotFields("Data")
With pvtTable
.ClearAllFilters
End With
With pvtField
.EnableMultiplePageItems = True
For Each pvtItem In .PivotItems
If Month(CDate(pvtItem)) <> 1 Then
pvtItem.Visible = False
End If
Next
End With
End Sub
Ma dava errore in corrispondenza di " pvtItem.Visible = False".
A quel punto ho registrato nuovamente una macro per simulare di nuovo il filtro e ho lanciato poi la stessa macro rilevando che anche con essa veniva generato l'errore.
Nota che se in luogo di pvtItem.Visible = False inserisco ad es.
Debug.Print pvtItem.Name
nella finestra immediata viene "stampato" il nome dell'item(es. 02/01/2018).
Quindi l'item viene individuato correttamente ma non è possibile impostare la proprietà Visible, come invece il registratore di macro farebbe supporre possibile.
Norman Jones
Feb 22, 2018, 12:15:15 PM2/22/18
to
Ciao Casanmaner,
On 22/02/2018 16:31, casanmaner wrote:
> ti rigranzio e sono consapevole dei limiti del registratore.
> Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
Ti assicuro che non avevo nessuna intenzione di denigrarti! Certo che
questo e ovvio a te! Comunque, quando si risponde ad una domanda, è
spesso necessario considerare la più vasta platea del forum! Dobbiamo
pensare anche a coloro che non hanno mai toccato le tabelle pivot
nell'ambito VBA.
Riprenderò la discussione appena che ritrovo mio pc!
===
Regards,
Norman
On 22/02/2018 16:31, casanmaner wrote:
> ti rigranzio e sono consapevole dei limiti del registratore.
> Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
questo e ovvio a te! Comunque, quando si risponde ad una domanda, è
spesso necessario considerare la più vasta platea del forum! Dobbiamo
pensare anche a coloro che non hanno mai toccato le tabelle pivot
nell'ambito VBA.
Riprenderò la discussione appena che ritrovo mio pc!
===
Regards,
Norman
casanmaner
Feb 22, 2018, 12:26:15 PM2/22/18
to
Il giorno giovedì 22 febbraio 2018 18:15:15 UTC+1, Norman Jones ha scritto:
> Ciao Casanmaner,
>
> On 22/02/2018 16:31, casanmaner wrote:
> > ti rigranzio e sono consapevole dei limiti del registratore.
> > Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
>
> Ti assicuro che non avevo nessuna intenzione di denigrarti! Certo che
> questo e ovvio a te!
Norman, naturalmente non vi avevo letto alcun intento denigratorio :)
> Ciao Casanmaner,
>
> On 22/02/2018 16:31, casanmaner wrote:
> > ti rigranzio e sono consapevole dei limiti del registratore.
> > Per quanto riguarda la "pulizia" dei filtri avevo già "scoperto" la possibilità data dal metodo ".ClearAllFilters".
>
> Ti assicuro che non avevo nessuna intenzione di denigrarti! Certo che
> questo e ovvio a te!
> Comunque, quando si risponde ad una domanda, è
> spesso necessario considerare la più vasta platea del forum! Dobbiamo
> pensare anche a coloro che non hanno mai toccato le tabelle pivot
> nell'ambito VBA.
Le ho utilizzate poco e per finalità circoscritte (es. prepararmi delle tabelle per le dichiarazioni iva partendo dai riepiloghi annuali IVA rilasciati dai gestionale dei clienti esterni) per poi avere l'elenco dei righi iva da compilare.
>
> Riprenderò la discussione appena che ritrovo mio pc!
>
>
casanmaner
Feb 22, 2018, 1:25:28 PM2/22/18
to
Credo di aver individuato il problema.
Nell'origine dati in realtà le date sono presenti in una colonna con formato generale.
E anche se il campo ha caption e name come "data" evidentemente non riconosce il valore.
Se nell'origine dati imposto il formato data per quella colona gli item vengono nascosti senza l'errore.
C'è da dire che però il metodo è parecchio lento perché ogni volta la tabella viene aggiornata.
Adesso devo scoprire se c'è un metodo per inibilre l'aggiornamento ed eseguirlo solo alla fine.
Nell'origine dati in realtà le date sono presenti in una colonna con formato generale.
E anche se il campo ha caption e name come "data" evidentemente non riconosce il valore.
Se nell'origine dati imposto il formato data per quella colona gli item vengono nascosti senza l'errore.
C'è da dire che però il metodo è parecchio lento perché ogni volta la tabella viene aggiornata.
Adesso devo scoprire se c'è un metodo per inibilre l'aggiornamento ed eseguirlo solo alla fine.
Norman Jones
Feb 22, 2018, 5:34:39 PM2/22/18
to
Ciao Casanmaner,
Sono appena tornato alla base - dopo una buona cena inaspettata con gli
amici ed il calcio.
Vedo che hai compiuto buoni progress! Credo che quello che si impara da
solo si capisce meglio e si ricorda sempre. Inoltre, ho notato altrove
che impari molto velocemente e molto assiduamente!
Detto ciò, se potesse servirti una mano, farò quel poco che posso per
auitarti.
===
Regards,
Norman
amici ed il calcio.
Vedo che hai compiuto buoni progress! Credo che quello che si impara da
solo si capisce meglio e si ricorda sempre. Inoltre, ho notato altrove
che impari molto velocemente e molto assiduamente!
Detto ciò, se potesse servirti una mano, farò quel poco che posso per
auitarti.
===
Regards,
Norman
buonoc...@gmail.com
Feb 22, 2018, 5:52:13 PM2/22/18
to
In particolare prova a impostare l'aggiornamento della tabella pivot a manuale
pv.ManualUpdate = True (dove pv è una variabile pivot table) oltre a mettere sia lo screenupdating a false che il calcolo automatico a manuale. Anche la mia esperienza nella manipolazione VBA delle tabelle pivot è limitata
https://stackoverflow.com/questions/19424926/why-is-this-pivotitem-visible-call-throwing-a-typemismatch-error
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
https://social.technet.microsoft.com/Forums/en-US/01cb61c7-5e68-4a45-aeff-a70c6dbfe00f/excel-2007-accessing-pivotitemvisible-gives-error-if-the-field-item-value-is-a-date?forum=exceldev
Ciao Elio
casanmaner
Feb 22, 2018, 6:52:55 PM2/22/18
to
Ciao Norman e Elio,
grazie ad entrambi.
La gestione delle date in effetti è particolare perché se le celle nel foglio di lavoro sono formattate come data gli Items della tabella pivot, nel VBA, assumono il formato d/mm/yyyy se il formato numerico impostato per il campo è il formato data dd/mm/aaaa.
Questo comporta che ad es. le date come 02/09/2018 nel foglio risultano errate in quanto vengono lette in VBA come 9/02/2018 americano e cioè 2 SETTEMBRE 2018.
Quindi il filtro non risulta corretto.
Se però, sempre con le date in formato gg/mm/aaaa nel foglio, imposto come formato numerico del campo il formato generale il vba dà errore perché non riesce a impostare la proprietà Visible degli items.
Ho impostato il formato numerico del campo data come testo (o come numero senza decimali e separatore di migliaia) e, anche se nell'elenco del filtro vengono visualizzati i numeri in luogo delle date, gli items vengono filtrati correttamente in base al numero di mese.
Altra cosa che ho notato è che ogni volta che un item viene nascosto l'update della tabella parte in automatico.
Avevo anche provato a impostare ManualUpdate=True ma, anche se in effetti si guadagnano pochi decimi, comunque l'update si ha ugualmente.
Ho inserito l'evento Worksheet_PivotTableUpdate a cui ho associato un semplice comando Debug.Print "PivotUpdate" e per ogni data filtrata nella finestra immediata viene effettuato il print.
Quindi il filtro in questa maniera è "macchinoso" e con l'aumentare delle date risulterà sempre più lento.
Ho pensato quindi che la soluzione più veloce sarebbe quella di inviduare gli intervalli entro cui sono presenti le date di un mese (o di più mesi) e impostare la "cache" in base a quell'intervallo.
Comunque se volete dare un'occhiata agli esperimenti metto il link ad un file "laboratorio":
https://www.dropbox.com/s/d664qk0rmg6ply7/Vendite%20Tabella%20Pivot%202018.xlsm?dl=0
Nel modulo3 c'è la sub FiltroMesi che imposta i filtri sulla base dei valori presenti nelle celle con sfondo azzurro con intestazioni di riga "Mese Iniziale" e "Mese Finale".
ciao e buonanotte :)
grazie ad entrambi.
La gestione delle date in effetti è particolare perché se le celle nel foglio di lavoro sono formattate come data gli Items della tabella pivot, nel VBA, assumono il formato d/mm/yyyy se il formato numerico impostato per il campo è il formato data dd/mm/aaaa.
Questo comporta che ad es. le date come 02/09/2018 nel foglio risultano errate in quanto vengono lette in VBA come 9/02/2018 americano e cioè 2 SETTEMBRE 2018.
Quindi il filtro non risulta corretto.
Se però, sempre con le date in formato gg/mm/aaaa nel foglio, imposto come formato numerico del campo il formato generale il vba dà errore perché non riesce a impostare la proprietà Visible degli items.
Ho impostato il formato numerico del campo data come testo (o come numero senza decimali e separatore di migliaia) e, anche se nell'elenco del filtro vengono visualizzati i numeri in luogo delle date, gli items vengono filtrati correttamente in base al numero di mese.
Altra cosa che ho notato è che ogni volta che un item viene nascosto l'update della tabella parte in automatico.
Avevo anche provato a impostare ManualUpdate=True ma, anche se in effetti si guadagnano pochi decimi, comunque l'update si ha ugualmente.
Ho inserito l'evento Worksheet_PivotTableUpdate a cui ho associato un semplice comando Debug.Print "PivotUpdate" e per ogni data filtrata nella finestra immediata viene effettuato il print.
Quindi il filtro in questa maniera è "macchinoso" e con l'aumentare delle date risulterà sempre più lento.
Ho pensato quindi che la soluzione più veloce sarebbe quella di inviduare gli intervalli entro cui sono presenti le date di un mese (o di più mesi) e impostare la "cache" in base a quell'intervallo.
Comunque se volete dare un'occhiata agli esperimenti metto il link ad un file "laboratorio":
https://www.dropbox.com/s/d664qk0rmg6ply7/Vendite%20Tabella%20Pivot%202018.xlsm?dl=0
Nel modulo3 c'è la sub FiltroMesi che imposta i filtri sulla base dei valori presenti nelle celle con sfondo azzurro con intestazioni di riga "Mese Iniziale" e "Mese Finale".
ciao e buonanotte :)
casanmaner
Feb 22, 2018, 6:54:46 PM2/22/18
to
Il giorno venerdì 23 febbraio 2018 00:52:55 UTC+1, casanmaner ha scritto:
> assumono il formato d/mm/yyyy
Qui in realtà volevo scrivere mm/d/yyyy
> assumono il formato d/mm/yyyy
Qui in realtà volevo scrivere mm/d/yyyy
casanmaner
Feb 22, 2018, 7:00:25 PM2/22/18
to
Il giorno venerdì 23 febbraio 2018 00:52:55 UTC+1, casanmaner ha scritto:
> Questo comporta che ad es. le date come 02/09/2018 nel foglio
E qui l'esempio voleva essere 09/02/2018 :)
Si vede che è ora di andare a letto :)
Bruno Campanini
Feb 23, 2018, 6:59:26 AM2/23/18
to
casanmaner has brought this to us :
Falla vedere anche a noi la tua tabella pivot, che in Excel ne ho
viste proprio poche... e fatte nessuna.
Una volta si chiamavano tabelle a doppia entrata.
Bruno
viste proprio poche... e fatte nessuna.
Una volta si chiamavano tabelle a doppia entrata.
Bruno
casanmaner
Feb 23, 2018, 7:27:19 AM2/23/18
to
https://www.dropbox.com/s/d664qk0rmg6ply7/Vendite%20Tabella%20Pivot%202018.xlsm?dl=0
Ho sistemato un po' anche se non ho avuto molto tempo da dedicare questa mattina.
Nel Modulo3 trovi la macro FiltroMesi.
Se vuoi renderti conto delle problematiche con le date imposta per il campo "Data" il formato numerico della data "gg/mm/aaaaa", aggiorna la tabella e inserisci 1 nella cella Mese Iniziale.
Poi verifica nel campo filtro e potrai notare che alcune date di gennaio invece di essere visibili risultano "filtrate".
Questo perché in VBA la data viene letta "americana" e la verifica sul mese invece di leggere 1 legge, ad es. 9, 10, ecc.
Vedi questo screen:
https://www.dropbox.com/s/ez271w20ws6brp4/Screenshot%202018-02-23%2013.21.49.png?dl=0
Se, invece, nelle impostazioni del campo Data si impostasse il formato Generale (a fronte di date inserite nel foglio in formato gg/mm/aaaa) ci sarebbe un errore VBA, allo stato attuale del codice viene gestito annullando i filtri, perché la proprietà Visible degli Items non risulterebbe accessibile.
Per ovviare al problema ho impostato il formato Testo per il campo Data.
Nel filtro risultano visibili i valori numerici ma inserendo ad es. 1 per gennaio si vede che tutti i valori corrispondenti a date comprese tra 1 e 31 gennaio 2018 sono visibili.
Vedi questo screen:
https://www.dropbox.com/s/5eikj3rift11bn8/Screenshot%202018-02-23%2013.26.07.png?dl=0
ciao
Bruno Campanini
Feb 23, 2018, 10:50:49 AM2/23/18
to
casanmaner wrote:
>> Falla vedere anche a noi la tua tabella pivot, che in Excel ne ho
>> viste proprio poche... e fatte nessuna.
>>
>> Una volta si chiamavano tabelle a doppia entrata.
>>
>> Bruno
>
> Ciao Bruno avevo messo il link al file:
> https://www.dropbox.com/s/d664qk0rmg6ply7/Vendite%20Tabella%20Pivot%202018.xlsm?dl=0
>
> Ho sistemato un po' anche se non ho avuto molto tempo da dedicare questa
> mattina.
>
> Nel Modulo3 trovi la macro FiltroMesi.
> Se vuoi renderti conto delle problematiche con le date imposta per il campo
> "Data" il formato numerico della data "gg/mm/aaaaa", aggiorna la tabella e
> inserisci 1 nella cella Mese Iniziale. Poi verifica nel campo filtro e potrai
> notare che alcune date di gennaio invece di essere visibili risultano
> "filtrate". Questo perché in VBA la data viene letta "americana" e la
> verifica sul mese invece di leggere 1 legge, ad es. 9, 10, ecc. Vedi questo
> screen:
> https://www.dropbox.com/s/ez271w20ws6brp4/Screenshot%202018-02-23%2013.21.49.png?dl=0
In FiltroMesi() ho sostituito i tre Month(pvtItem) con
>> Falla vedere anche a noi la tua tabella pivot, che in Excel ne ho
>> viste proprio poche... e fatte nessuna.
>>
>> Una volta si chiamavano tabelle a doppia entrata.
>>
>> Bruno
>
> Ciao Bruno avevo messo il link al file:
> https://www.dropbox.com/s/d664qk0rmg6ply7/Vendite%20Tabella%20Pivot%202018.xlsm?dl=0
>
> Ho sistemato un po' anche se non ho avuto molto tempo da dedicare questa
> mattina.
>
> Nel Modulo3 trovi la macro FiltroMesi.
> Se vuoi renderti conto delle problematiche con le date imposta per il campo
> "Data" il formato numerico della data "gg/mm/aaaaa", aggiorna la tabella e
> inserisci 1 nella cella Mese Iniziale. Poi verifica nel campo filtro e potrai
> notare che alcune date di gennaio invece di essere visibili risultano
> "filtrate". Questo perché in VBA la data viene letta "americana" e la
> verifica sul mese invece di leggere 1 legge, ad es. 9, 10, ecc. Vedi questo
> screen:
> https://www.dropbox.com/s/ez271w20ws6brp4/Screenshot%202018-02-23%2013.21.49.png?dl=0
Month(Format(pvtItem, "mm/dd/yyyy")), col che nella pivot
table cambia qualcosa.
Cosa ne dici?
>
>
> Se, invece, nelle impostazioni del campo Data si impostasse il formato
> Generale (a fronte di date inserite nel foglio in formato gg/mm/aaaa) ci
> sarebbe un errore VBA, allo stato attuale del codice viene gestito annullando
> i filtri, perché la proprietà Visible degli Items non risulterebbe
> accessibile.
>
> Per ovviare al problema ho impostato il formato Testo per il campo Data.
> Nel filtro risultano visibili i valori numerici ma inserendo ad es. 1 per
> gennaio si vede che tutti i valori corrispondenti a date comprese tra 1 e 31
> gennaio 2018 sono visibili. Vedi questo screen:
> https://www.dropbox.com/s/5eikj3rift11bn8/Screenshot%202018-02-23%2013.26.07.png?dl=0
Di questa seconda parte non mi sono occupato.
>
> Se, invece, nelle impostazioni del campo Data si impostasse il formato
> Generale (a fronte di date inserite nel foglio in formato gg/mm/aaaa) ci
> sarebbe un errore VBA, allo stato attuale del codice viene gestito annullando
> i filtri, perché la proprietà Visible degli Items non risulterebbe
> accessibile.
>
> Per ovviare al problema ho impostato il formato Testo per il campo Data.
> Nel filtro risultano visibili i valori numerici ma inserendo ad es. 1 per
> gennaio si vede che tutti i valori corrispondenti a date comprese tra 1 e 31
> gennaio 2018 sono visibili. Vedi questo screen:
> https://www.dropbox.com/s/5eikj3rift11bn8/Screenshot%202018-02-23%2013.26.07.png?dl=0
Bruno
casanmaner
Feb 23, 2018, 11:12:29 AM2/23/18
to
Il giorno venerdì 23 febbraio 2018 16:50:49 UTC+1, Bruno Campanini ha scritto:
> casanmaner wrote:
> In FiltroMesi() ho sostituito i tre Month(pvtItem) con
> Month(Format(pvtItem, "mm/dd/yyyy")), col che nella pivot
> table cambia qualcosa.
> Cosa ne dici?
Sì, mannaggia!!!!
> In FiltroMesi() ho sostituito i tre Month(pvtItem) con
> Month(Format(pvtItem, "mm/dd/yyyy")), col che nella pivot
> table cambia qualcosa.
> Cosa ne dici?
Eh si che altre volte ho usato la stessa cosa per "convertire" numeri in formato americano.
Come perdersi in un bicchier d'acqua a volte!
Grazie! :)
Bruno Campanini
Feb 23, 2018, 11:26:48 AM2/23/18
to
on 23-02-18, casanmaner supposed :
Senectus ipsa morbus... lol
Bruno
Bruno
casanmaner
Feb 23, 2018, 12:05:27 PM2/23/18
to
casanmaner
Feb 23, 2018, 1:02:00 PM2/23/18
to
Ho voluto provare con un po' più di dati (in particolare con 342 date) e i tempi di filtraggio, per il singolo mese, aumentano parecchio.
Nel mio PC circa 14"/15".
Credo che per rendere efficiente l'operazione di filtraggio sia opportuno prevedere una procedura che, considerato che le date sono sempre ordinate (ordinamento dalla più recente alla meno recente), individui l'intervallo di celle entro cui sono contenute le date di un mese (o di più mesi "consecutivi") e assegnare alla cache quell'intervallo di riferimento.
Nel mio PC circa 14"/15".
Credo che per rendere efficiente l'operazione di filtraggio sia opportuno prevedere una procedura che, considerato che le date sono sempre ordinate (ordinamento dalla più recente alla meno recente), individui l'intervallo di celle entro cui sono contenute le date di un mese (o di più mesi "consecutivi") e assegnare alla cache quell'intervallo di riferimento.
casanmaner
Feb 23, 2018, 3:31:39 PM2/23/18
to
Il giorno venerdì 23 febbraio 2018 16:50:49 UTC+1, Bruno Campanini ha scritto:
> casanmaner wrote:
>
> casanmaner wrote:
>
> In FiltroMesi() ho sostituito i tre Month(pvtItem) con
> Month(Format(pvtItem, "mm/dd/yyyy")), col che nella pivot
> table cambia qualcosa.
> Cosa ne dici?
> >
Giusto per "cultura" se prima di effettuare la verifica con
> Month(Format(pvtItem, "mm/dd/yyyy")), col che nella pivot
> table cambia qualcosa.
> Cosa ne dici?
> >
Month(pvtItem)
il formato numero del campo viene impostato in VBA con formato "dd/mm/yyyy"
pvtField.NumberFormat = "dd/mm/yyyy"
a quanto pare poi anche i singoli Items si impostano definitivamente in formato italiano.
E questo anche se successivamente a meno che dall'interfaccia utente non venga impostata il formato data "gg/mm/aaaaa".
In tal caso quando si aggiorna la tabella, sempre da interfaccia, poi in VBA il "formato" riprende ad essere americano e occorre nuovamente impostare il NumberFormat in formato "italiano".
casanmaner
Feb 25, 2018, 4:41:05 AM2/25/18
to
Visto che questa mattina c'è troppo vento per godersi un'uscita in bici (c'è il rischio di essere spostati dalle raffiche in mezzo alla strada!!!!) ho cercato di rendere più veloce l'esposizione dei dati nella tabella pivot in base ai mesi desiderati.
L'utilizzo della proprietà Visible dei singoli Items è poco efficiente.
Anche facendo in modo che, mantendendo il precedente filtro, vengano nascosti i soli Items eventualmente visibili, comunque la procedura per un numero elevato di date impiega, da questo pc, qualche secondo. Se si parte da tutti gli Items visibili fino anche a 15".
Avevo provato a leggere l'articolo che Elio ha gentilmente indiato:
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
ma evidentemente richiede qualche componente che io non ho (forse powerpivot).
Alcune variabili dichiarate nella cartella di lavoro di esempio dal mio pc vengono indicate come "Tipo definito dall'utente" (es. Dim sc As SlicerCache) e quindi, anche ove fossi riuscito a comprendere esattamente cosa veniva fatto non sarebbe stato utilizzabile.
Allora ho pensato ad una soluzione "casareccia".
Una procedura che in base ai mesi inseriti crei una matrice, (ho utilizzato una funzione esterna alla routine che esegue il "filtro") di dati relativi solo a quelle date relative a quei mesi, riporti questi dati, con le relative identiche intestazioni in un'altra sezione del foglio (o volendo anche in un apposito foglio dedicato) questi dati e assegni quell'intervallo alla PivotCache.
Se la cella dedicata al primo mese è vuoto viene utilizzato l'intervallo originario per impostare la cache della tabella pivot.
Se viene compilata al cella del solo mese iniziale viene creato un nuovo intervallo con le sole date di quel mese.
Se viene compilata anche la cella del mese finale viene creato un nuovo intervallo con le date che vanno dal mese iniziale a quello finale.
Ho anche cercato di replicare il "comportamento" dei filtri della tabella pivot nel caso tutte le voci del campo Autore, a cui corrisponde un dettaglio relativo al Tipo di documento acquistato, siano compresse o "espanse" o solo alcune compresse e altre espanse.
Per fare questo mi sono affidato ad una dictionary dove memorizzo gli Items degli Autori prima di modificare la Cache e ad una funzione "esterna" che verifica se tutte le voci sono compresse o meno.
Il tutto più o meno in meno di mezzo secondo che quindi è molto più accettabile, soprattutto rispetto ai quasi 15" nel caso di primo filtro rispetto alla situazione di nessun filtro.
Qui, per eventuale vostra curiosità, il file con le ultime modifiche:
https://www.dropbox.com/s/1sdhvw59wn8feij/Vendite%20Tabella%20Pivot%202018%233.xlsm?dl=0
Nel Modulo4 sono presenti le routine e le function utilizzate per gestire il "filtro".
Nel Modulo5 una routine che imposta la tabella pivot in automatico partendo dalla "pulizia" della tabella alla reipostazione dei campi e formati da me desiderati per questa tabella (così ne ho approfittato per studiare un po' gli oggetti della tabella pivot in vba).
Riporto di seguito il codice relativo alla gestione del filtro presenti nel modulo4:
'---
Public Const iColonnaDateAutori As Long = 1
Public Const sNomeWsDatiVendite As String = "Dati Vendite Documenti"
Public Const sPrimaCellaDatiVendite = "A1"
Public Const sNomeWsPivot As String = "Pivot Vendite Documenti"
Public Const sNomeWsFiltroPivot As String = sNomeWsDatiVendite '"FiltroPivot"
Public Const sPrimaCellaFiltroPivot As String = "AA1"
Sub ImpostaPivotCacheConFiltroMesi()
'Dim iTimer As Double: iTimer = Timer
Dim Twb As Workbook
Dim WsDatiVendite As Worksheet
Dim PrimaCellaDatiVendite As Range
Dim IntervalloDatiVendite As Range
Dim IntervalloIntestazioni As Range
Dim iColonneDatiVendite As Long
Dim WsPivot As Worksheet
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim StatoItemsShowDetail As Object
Dim cMeseIniziale As Range
Dim cMeseFinale As Range
Dim WsFiltroPivot As Worksheet
Dim PrimaCellaFiltroPivot As Range
Dim iMeseIniziale As Long
Dim iMeseFinale As Long
Dim arrDatiVendite As Variant
Dim arrDatiFiltrati As Variant
Dim sSourceData As String
Dim bAggiornaPivotCache As Boolean
Dim bAggiornaPivotCacheII As Boolean
Dim bpvtFieldShowDetail As Boolean
Set Twb = ThisWorkbook
With Twb
Set WsDatiVendite = .Worksheets(sNomeWsDatiVendite)
Set WsPivot = .Worksheets(sNomeWsPivot)
Set WsFiltroPivot = .Worksheets(sNomeWsFiltroPivot)
End With
Set PrimaCellaDatiVendite = WsDatiVendite.Range(sPrimaCellaDatiVendite)
With WsPivot
Set cMeseIniziale = .Range("MeseIniziale")
Set cMeseFinale = .Range("MeseFinale")
iMeseIniziale = cMeseIniziale.Value
iMeseFinale = cMeseFinale.Value
Set pvtTable = .PivotTables(1)
End With
With WsFiltroPivot
Set PrimaCellaFiltroPivot = .Range(sPrimaCellaFiltroPivot)
PrimaCellaFiltroPivot.CurrentRegion.Clear
End With
With PrimaCellaDatiVendite
With .CurrentRegion
If .Rows.Count >= 2 Then
Set IntervalloDatiVendite = .Cells
Set IntervalloIntestazioni = .Rows(1)
iColonneDatiVendite = IntervalloIntestazioni.Columns.Count
arrDatiVendite = .Cells.Offset(1, 0).Resize(.Rows.Count - 1).Value2
bAggiornaPivotCache = True
End If
End With
End With
If bAggiornaPivotCache Then
If iMeseIniziale = 0 Then
sSourceData = IntervalloDatiVendite.Address(True, True, xlA1, True)
If cMeseFinale.Value > 0 Then cMeseFinale.ClearContents
bAggiornaPivotCacheII = True
ElseIf iMeseIniziale > 0 Then
If iMeseFinale = 0 Then
arrDatiFiltrati = ArrFiltroPivot(arrDatiVendite, iMeseIniziale)
ElseIf iMeseFinale > 0 Then
arrDatiFiltrati = ArrFiltroPivot(arrDatiVendite, iMeseIniziale, iMeseFinale)
End If 'iMeseFinale
If Not IsEmpty(arrDatiFiltrati) Then
bAggiornaPivotCacheII = True
With PrimaCellaFiltroPivot
.Offset(0, 0).Resize(, iColonneDatiVendite).Value = IntervalloIntestazioni.Value
With .Offset(1, 0).Resize(UBound(arrDatiFiltrati, 1), iColonneDatiVendite)
.Value = arrDatiFiltrati
.Columns(1).NumberFormat = "dd/mm/yyyy"
'.IndentLevel = 1
End With
sSourceData = .CurrentRegion.Address(True, True, xlA1, True)
End With
End If 'Not IsEmpty(arrDatiFiltrati)
End If 'iMeseIniziale
If bAggiornaPivotCacheII Then
bpvtFieldShowDetail = bItemsShowDetail
Set StatoItemsShowDetail = CreateObject("Scripting.Dictionary")
With pvtTable
For Each pvtItem In .PivotFields("Autore").PivotItems
With pvtItem
StatoItemsShowDetail.Add .Name, .ShowDetail
End With
Next pvtItem
.ManualUpdate = True
.ClearAllFilters
.ChangePivotCache Twb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sSourceData, _
Version:=xlPivotTableVersion12)
With .PivotCache
.RefreshOnFileOpen = True
.MissingItemsLimit = xlMissingItemsNone
.Refresh
End With
.RefreshTable
For Each pvtItem In .PivotFields("Autore").PivotItems
With pvtItem
If Not StatoItemsShowDetail.Exists(.Name) Then
.ShowDetail = bpvtFieldShowDetail 'False
'.ShowDetail = False
End If
End With
Next pvtItem
.ManualUpdate = False
Set StatoItemsShowDetail = Nothing
End With 'pvtTable
Else
cMeseIniziale.ClearContents
cMeseFinale.ClearContents
Call ImpostaPivotCacheConFiltroMesi
End If 'bAggiornaPivotCacheII
End If 'bAggiornaPivotCache
'Debug.Print Timer - iTimer
End Sub
Function ArrFiltroPivot(arrDati As Variant, _
MeseIniziale As Long, _
Optional MeseFinale As Long = 0) As Variant
Dim i As Long, j As Long, cont As Long
Dim arrTmp() As Variant
Dim arrTrasposta() As Variant
For i = 1 To UBound(arrDati, 1)
If MeseFinale = 0 Then
If Month(arrDati(i, iColonnaDateAutori)) = MeseIniziale Then
cont = cont + 1
For j = 1 To UBound(arrDati, 2)
ReDim Preserve arrTmp(1 To UBound(arrDati, 2), 1 To cont)
arrTmp(j, cont) = arrDati(i, j)
Next j
End If
Else
If Month(arrDati(i, iColonnaDateAutori)) >= MeseIniziale And _
Month(arrDati(i, iColonnaDateAutori)) <= MeseFinale Then
cont = cont + 1
For j = 1 To UBound(arrDati, 2)
ReDim Preserve arrTmp(1 To UBound(arrDati, 2), 1 To cont)
arrTmp(j, cont) = arrDati(i, j)
Next j
End If
End If 'MeseFinale
Next i
If cont > 0 Then
ReDim arrTrasposta(1 To UBound(arrTmp, 2), 1 To UBound(arrTmp, 1))
For i = 1 To UBound(arrTmp, 2)
For j = 1 To UBound(arrTmp, 1)
arrTrasposta(i, j) = arrTmp(j, i)
Next j
Next i
ArrFiltroPivot = arrTrasposta
End If 'cont > 0
End Function
Function bItemsShowDetail() As Boolean
Dim pvtItem As PivotItem
Dim bShowDetail As Boolean
bShowDetail = True
With ThisWorkbook.Worksheets(sNomeWsPivot)
With .PivotTables(1).PivotFields("Autore")
bShowDetail = False
Exit For
End If
Next pvtItem
End With
End With
bItemsShowDetail = bShowDetail
End Function
'---
Diciamo che meglio di così non saprei come ottenere il risultato dei filtri in maniera piuttosto veloce.
Forse ci sarà da effettuare qualche aggiustamento nel caso in cui l'archivio dei dati sia vuoto per evitare errori ma eventualmente con l'uso verranno fuori le magagne e cercherò di sistemare :)
Buona domenica ... ventosissima!!!
L'utilizzo della proprietà Visible dei singoli Items è poco efficiente.
Anche facendo in modo che, mantendendo il precedente filtro, vengano nascosti i soli Items eventualmente visibili, comunque la procedura per un numero elevato di date impiega, da questo pc, qualche secondo. Se si parte da tutti gli Items visibili fino anche a 15".
Avevo provato a leggere l'articolo che Elio ha gentilmente indiato:
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
ma evidentemente richiede qualche componente che io non ho (forse powerpivot).
Alcune variabili dichiarate nella cartella di lavoro di esempio dal mio pc vengono indicate come "Tipo definito dall'utente" (es. Dim sc As SlicerCache) e quindi, anche ove fossi riuscito a comprendere esattamente cosa veniva fatto non sarebbe stato utilizzabile.
Allora ho pensato ad una soluzione "casareccia".
Una procedura che in base ai mesi inseriti crei una matrice, (ho utilizzato una funzione esterna alla routine che esegue il "filtro") di dati relativi solo a quelle date relative a quei mesi, riporti questi dati, con le relative identiche intestazioni in un'altra sezione del foglio (o volendo anche in un apposito foglio dedicato) questi dati e assegni quell'intervallo alla PivotCache.
Se la cella dedicata al primo mese è vuoto viene utilizzato l'intervallo originario per impostare la cache della tabella pivot.
Se viene compilata al cella del solo mese iniziale viene creato un nuovo intervallo con le sole date di quel mese.
Se viene compilata anche la cella del mese finale viene creato un nuovo intervallo con le date che vanno dal mese iniziale a quello finale.
Ho anche cercato di replicare il "comportamento" dei filtri della tabella pivot nel caso tutte le voci del campo Autore, a cui corrisponde un dettaglio relativo al Tipo di documento acquistato, siano compresse o "espanse" o solo alcune compresse e altre espanse.
Per fare questo mi sono affidato ad una dictionary dove memorizzo gli Items degli Autori prima di modificare la Cache e ad una funzione "esterna" che verifica se tutte le voci sono compresse o meno.
Il tutto più o meno in meno di mezzo secondo che quindi è molto più accettabile, soprattutto rispetto ai quasi 15" nel caso di primo filtro rispetto alla situazione di nessun filtro.
Qui, per eventuale vostra curiosità, il file con le ultime modifiche:
https://www.dropbox.com/s/1sdhvw59wn8feij/Vendite%20Tabella%20Pivot%202018%233.xlsm?dl=0
Nel Modulo4 sono presenti le routine e le function utilizzate per gestire il "filtro".
Nel Modulo5 una routine che imposta la tabella pivot in automatico partendo dalla "pulizia" della tabella alla reipostazione dei campi e formati da me desiderati per questa tabella (così ne ho approfittato per studiare un po' gli oggetti della tabella pivot in vba).
Riporto di seguito il codice relativo alla gestione del filtro presenti nel modulo4:
'---
Public Const iColonnaDateAutori As Long = 1
Public Const sNomeWsDatiVendite As String = "Dati Vendite Documenti"
Public Const sPrimaCellaDatiVendite = "A1"
Public Const sNomeWsPivot As String = "Pivot Vendite Documenti"
Public Const sNomeWsFiltroPivot As String = sNomeWsDatiVendite '"FiltroPivot"
Public Const sPrimaCellaFiltroPivot As String = "AA1"
Sub ImpostaPivotCacheConFiltroMesi()
'Dim iTimer As Double: iTimer = Timer
Dim Twb As Workbook
Dim WsDatiVendite As Worksheet
Dim PrimaCellaDatiVendite As Range
Dim IntervalloDatiVendite As Range
Dim IntervalloIntestazioni As Range
Dim iColonneDatiVendite As Long
Dim WsPivot As Worksheet
Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim StatoItemsShowDetail As Object
Dim cMeseIniziale As Range
Dim cMeseFinale As Range
Dim WsFiltroPivot As Worksheet
Dim PrimaCellaFiltroPivot As Range
Dim iMeseIniziale As Long
Dim iMeseFinale As Long
Dim arrDatiVendite As Variant
Dim arrDatiFiltrati As Variant
Dim sSourceData As String
Dim bAggiornaPivotCache As Boolean
Dim bAggiornaPivotCacheII As Boolean
Dim bpvtFieldShowDetail As Boolean
Set Twb = ThisWorkbook
With Twb
Set WsDatiVendite = .Worksheets(sNomeWsDatiVendite)
Set WsPivot = .Worksheets(sNomeWsPivot)
Set WsFiltroPivot = .Worksheets(sNomeWsFiltroPivot)
End With
Set PrimaCellaDatiVendite = WsDatiVendite.Range(sPrimaCellaDatiVendite)
With WsPivot
Set cMeseIniziale = .Range("MeseIniziale")
Set cMeseFinale = .Range("MeseFinale")
iMeseIniziale = cMeseIniziale.Value
iMeseFinale = cMeseFinale.Value
Set pvtTable = .PivotTables(1)
End With
With WsFiltroPivot
Set PrimaCellaFiltroPivot = .Range(sPrimaCellaFiltroPivot)
PrimaCellaFiltroPivot.CurrentRegion.Clear
End With
With PrimaCellaDatiVendite
With .CurrentRegion
If .Rows.Count >= 2 Then
Set IntervalloDatiVendite = .Cells
Set IntervalloIntestazioni = .Rows(1)
iColonneDatiVendite = IntervalloIntestazioni.Columns.Count
arrDatiVendite = .Cells.Offset(1, 0).Resize(.Rows.Count - 1).Value2
bAggiornaPivotCache = True
End If
End With
End With
If bAggiornaPivotCache Then
If iMeseIniziale = 0 Then
sSourceData = IntervalloDatiVendite.Address(True, True, xlA1, True)
If cMeseFinale.Value > 0 Then cMeseFinale.ClearContents
bAggiornaPivotCacheII = True
ElseIf iMeseIniziale > 0 Then
If iMeseFinale = 0 Then
arrDatiFiltrati = ArrFiltroPivot(arrDatiVendite, iMeseIniziale)
ElseIf iMeseFinale > 0 Then
arrDatiFiltrati = ArrFiltroPivot(arrDatiVendite, iMeseIniziale, iMeseFinale)
End If 'iMeseFinale
If Not IsEmpty(arrDatiFiltrati) Then
bAggiornaPivotCacheII = True
With PrimaCellaFiltroPivot
.Offset(0, 0).Resize(, iColonneDatiVendite).Value = IntervalloIntestazioni.Value
With .Offset(1, 0).Resize(UBound(arrDatiFiltrati, 1), iColonneDatiVendite)
.Value = arrDatiFiltrati
.Columns(1).NumberFormat = "dd/mm/yyyy"
'.IndentLevel = 1
End With
sSourceData = .CurrentRegion.Address(True, True, xlA1, True)
End With
End If 'Not IsEmpty(arrDatiFiltrati)
End If 'iMeseIniziale
If bAggiornaPivotCacheII Then
bpvtFieldShowDetail = bItemsShowDetail
Set StatoItemsShowDetail = CreateObject("Scripting.Dictionary")
With pvtTable
For Each pvtItem In .PivotFields("Autore").PivotItems
With pvtItem
StatoItemsShowDetail.Add .Name, .ShowDetail
End With
Next pvtItem
.ManualUpdate = True
.ClearAllFilters
.ChangePivotCache Twb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sSourceData, _
Version:=xlPivotTableVersion12)
With .PivotCache
.RefreshOnFileOpen = True
.MissingItemsLimit = xlMissingItemsNone
.Refresh
End With
.RefreshTable
For Each pvtItem In .PivotFields("Autore").PivotItems
With pvtItem
If Not StatoItemsShowDetail.Exists(.Name) Then
.ShowDetail = bpvtFieldShowDetail 'False
'.ShowDetail = False
End If
End With
Next pvtItem
.ManualUpdate = False
Set StatoItemsShowDetail = Nothing
End With 'pvtTable
Else
cMeseIniziale.ClearContents
cMeseFinale.ClearContents
Call ImpostaPivotCacheConFiltroMesi
End If 'bAggiornaPivotCacheII
End If 'bAggiornaPivotCache
'Debug.Print Timer - iTimer
End Sub
Function ArrFiltroPivot(arrDati As Variant, _
MeseIniziale As Long, _
Optional MeseFinale As Long = 0) As Variant
Dim i As Long, j As Long, cont As Long
Dim arrTmp() As Variant
Dim arrTrasposta() As Variant
For i = 1 To UBound(arrDati, 1)
If MeseFinale = 0 Then
If Month(arrDati(i, iColonnaDateAutori)) = MeseIniziale Then
cont = cont + 1
For j = 1 To UBound(arrDati, 2)
ReDim Preserve arrTmp(1 To UBound(arrDati, 2), 1 To cont)
arrTmp(j, cont) = arrDati(i, j)
Next j
End If
Else
If Month(arrDati(i, iColonnaDateAutori)) >= MeseIniziale And _
Month(arrDati(i, iColonnaDateAutori)) <= MeseFinale Then
cont = cont + 1
For j = 1 To UBound(arrDati, 2)
ReDim Preserve arrTmp(1 To UBound(arrDati, 2), 1 To cont)
arrTmp(j, cont) = arrDati(i, j)
Next j
End If
End If 'MeseFinale
Next i
If cont > 0 Then
ReDim arrTrasposta(1 To UBound(arrTmp, 2), 1 To UBound(arrTmp, 1))
For i = 1 To UBound(arrTmp, 2)
For j = 1 To UBound(arrTmp, 1)
arrTrasposta(i, j) = arrTmp(j, i)
Next j
Next i
ArrFiltroPivot = arrTrasposta
End If 'cont > 0
End Function
Function bItemsShowDetail() As Boolean
Dim pvtItem As PivotItem
Dim bShowDetail As Boolean
bShowDetail = True
With ThisWorkbook.Worksheets(sNomeWsPivot)
With .PivotTables(1).PivotFields("Autore")
For Each pvtItem In .PivotItems
If Not pvtItem.ShowDetail Then
bShowDetail = False
Exit For
End If
Next pvtItem
End With
End With
bItemsShowDetail = bShowDetail
End Function
'---
Diciamo che meglio di così non saprei come ottenere il risultato dei filtri in maniera piuttosto veloce.
Forse ci sarà da effettuare qualche aggiustamento nel caso in cui l'archivio dei dati sia vuoto per evitare errori ma eventualmente con l'uso verranno fuori le magagne e cercherò di sistemare :)
Buona domenica ... ventosissima!!!
casanmaner
Feb 25, 2018, 4:48:25 AM2/25/18
to
Ah, ho dimenticato di dire che il tutto, naturalmente, viene gestito tramte l'evento Change del foglio dove si trova la tabella pivot (ma quella è la parte meno elaborata :)):
In particolare è presente il seguente codice dove inibisco il calcolo, blocco lo schermo, blocco gli eventi e lancio la procedura se vengono modificate le celle del meseiniziale o finale:
'---
Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Select Case Target.Address
Case Range("MeseIniziale").Address, _
Range("MeseFinale").Address
Call ImpostaPivotCacheConFiltroMesi
End Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
'---
In particolare è presente il seguente codice dove inibisco il calcolo, blocco lo schermo, blocco gli eventi e lancio la procedura se vengono modificate le celle del meseiniziale o finale:
'---
Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Select Case Target.Address
Case Range("MeseIniziale").Address, _
Range("MeseFinale").Address
Call ImpostaPivotCacheConFiltroMesi
End Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
'---
buonoc...@gmail.com
Feb 25, 2018, 10:11:06 AM2/25/18
to
Hai fatto un ottimo lavoro. Di dovrebbe però valutarne la portata ed eventuale utilizzo alla luce degli strumenti attuali delle versioni più recenti di Excel. Ti propongo in video, visto che mi pare tu non abbia Excel 2013, quello che si può fare senza VBA con lo strumento sequenza temporale e con uno Slicer (oggetto delle pivot normali e non solo di quelle Power Pivot oltre che delle Tabelle) sulla tua base dati interrogata sul primo trimestre in comparazione con la tua macro.
https://www.dropbox.com/s/jqbnytk5hsqtwjf/Demo%20sequenza%20temporale%20e%20slicer.wmv?dl=0
Ciao
Elio
https://www.dropbox.com/s/jqbnytk5hsqtwjf/Demo%20sequenza%20temporale%20e%20slicer.wmv?dl=0
Ciao
Elio
casanmaner
Feb 25, 2018, 10:20:50 AM2/25/18
to
Il giorno domenica 25 febbraio 2018 16:11:06 UTC+1, Elio Buonocore ha scritto:
> Hai fatto un ottimo lavoro. Di dovrebbe però valutarne la portata ed eventuale utilizzo alla luce degli strumenti attuali delle versioni più recenti di Excel. Ti propongo in video, visto che mi pare tu non abbia Excel 2013, quello che si può fare senza VBA con lo strumento sequenza temporale e con uno Slicer (oggetto delle pivot normali e non solo di quelle Power Pivot oltre che delle Tabelle) sulla tua base dati interrogata sul primo trimestre in comparazione con la tua macro.
> https://www.dropbox.com/s/jqbnytk5hsqtwjf/Demo%20sequenza%20temporale%20e%20slicer.wmv?dl=0
>
Nei PC fissi in studio ho anche Excel2013.
> Hai fatto un ottimo lavoro. Di dovrebbe però valutarne la portata ed eventuale utilizzo alla luce degli strumenti attuali delle versioni più recenti di Excel. Ti propongo in video, visto che mi pare tu non abbia Excel 2013, quello che si può fare senza VBA con lo strumento sequenza temporale e con uno Slicer (oggetto delle pivot normali e non solo di quelle Power Pivot oltre che delle Tabelle) sulla tua base dati interrogata sul primo trimestre in comparazione con la tua macro.
> https://www.dropbox.com/s/jqbnytk5hsqtwjf/Demo%20sequenza%20temporale%20e%20slicer.wmv?dl=0
>
Quando ho un po' di tempo tranquillo voglio verificare questa funzionalità.
Mi pare di capire che si possa decidere, sulla base dei dati della tabella pivot, un arco temporale (vedendo il video) con possibilità di ulteriori filtri (ho visto dal video che hai inserito lo studio di registrazione ma suppongo si possano impostare anche gli altri campi, tipo Autore o anche Titolo documento).
Grazie per la segnalazione :)
buonoc...@gmail.com
Feb 25, 2018, 11:14:07 AM2/25/18
to
casanmaner
Feb 26, 2018, 12:54:55 PM2/26/18
to
Veramente comoda con possibilità di decidere gli intervalli temporali anno, trimestre, mese e giorni.
Mi pare di capire che sia disponibile da Excel 2013 vero?
buonoc...@gmail.com
Feb 26, 2018, 6:40:51 PM2/26/18
to
https://support.office.com/en-us/article/create-a-pivottable-timeline-to-filter-dates-d3956083-01be-408c-906d-6fc99d9fadfa
0 new messages