• Modificare in automatico l’origine dei dati di una tabella Pivot in Excel 2007

    Avevamo parlato della possibilità di poter creare una tabella Pivot in Excel 2007 che, però, non permette di aggiungere righe all’origine dei dati qualora questa cambi.

    Quando la procedura manuale (Seleziona la tabella Pivot -> Opzioni -> Cambia origine dati) non fosse per noi abbastanza comoda, possiamo ovviare al problema scrivendo una macro in VBA che lo faccia automaticamente.

    Per prima cosa apriamo l’editor VBA cliccando Sviluppo -> Visual Basic e creiamo un modulo (Inserisci -> Modulo). Adesso inseriamo all’interno:


    Sub Aggiungi_riga_pivot()
    End Sub

    Nella Sub appena scritta aggiungiamo il codice seguente e valorizziamo le variabili in base alle nostre esigenze:


    strNomeFoglioDati = "Nome_foglio_dati"
    strNomeTabellaPivot = "Nome_tabella_Pivot"
    strPrimaColonna = "Lettera_prima_colonna"
    strUltimaColonna = "Lettera_ultima_colonna"

    Successivamente aggiungiamo il codice:


    'calcolo della riga di inizio/fine e conversione delle colonne di inizio/fine
    intPrimaColonna = Asc(strPrimaColonna) (64
    intUltimaColonna = Asc(strUltimaColonna)) 64
    intPrimaRiga = FirstRow(Worksheets(strNomeFoglioDati), intPrimaColonna)
    intUltimaRiga = LastRow(Worksheets(strNomeFoglioDati), intPrimaColonna, intPrimaRiga)
    'creazione della nuova cache della tabella Pivot
    Set NuovaCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=Application.ActiveWorkbook.Path & "[" & Application.ActiveWorkbook.Name & "]" & strNomeFoglioDati & "!R" & CStr(intPrimaRiga) & "C" & CStr(intPrimaColonna) & ":R" & CStr(intUltimaRiga) & "C" & CStr(intUltimaColonna))
    'aggiornamento della tabella Pivot
    ActiveSheet.PivotTables(strNomeTabellaPivot).ChangePivotCache (NuovaCache)

    Il codice appena scritto genera e associa la nuova cache alla tabella Pivot.

    Le prossime sono invece le funzioni da copiare (fuori dalla Sub) per la ricerca della prima e ultima riga:


    Function FirstRow(Foglio As Worksheet, intColonna) As Integer
    FirstRow = 1
    While Foglio.Cells(FirstRow, intColonna).Value = ""
    FirstRow = FirstRow + 1
    Wend
    End Function
    Function LastRow(Foglio As Worksheet, intColonna, ByRef intCellaInizio) As Integer
    LastRow = intCellaInizio
    While Foglio.Cells(LastRow + 1, intColonna).Value <> ""
    LastRow = LastRow + 1
    Wend
    End Function

    Queste funzioni permettono di posizionare i dati partendo da una riga arbitraria.

    Salviamo il tutto con l’estensione “.XLSM“, che permette la memorizzazione di macro VBA, e andiamo nel foglio in cui è presente la tabella Pivot. Inseriamo un pulsante tramite Sviluppo -> Inserisci -> Pulsante (controllo modulo), assegnandogli la macro VBA appena scritta.

    Basterà così cliccare il pulsante per aggiungere all’origine dei dati della tabella Pivot le ultime righe inserite nella tabella di riferimento.

    Tags:

    Se vuoi aggiornamenti su Modificare in automatico l’origine dei dati di una tabella Pivot in Excel 2007 inserisci la tua e-mail nel box qui sotto:


    Ho letto e acconsento l'informativa sulla privacy

    Si No

    Acconsento al trattamento dei dati personali di cui al punto 3 dell'informativa sulla privacy

    Si No

    Commenti

    1. Manuel dice:

      Salve, per rendere dinamici i dati di origine della tabella pivot c’è un modo molto più semplice e pratico.
      Utilizzare un nome tabella che raggruppa i dati da analizzare, cioè formattare i dati in una tabella (selezionare i dati-formatta come tabella), cambiare il nome tabella con un nome a piacere (formule -gestione nomi) e poi quando si crea la tabella pivot inserire il nome tabella come origine dati.

      Manuel

      • Andrea dice:

        Buongiorno ho creato un file che contiene un foglio DB che nel tempo a furia di aggiungere campi e righe di dati è diventato molto consistente.
        Oltre a questo mi sono anche sbizzarrito nel creare molte pivot collegate e reporting di vario genere.
        Ora il file è diventato molto elevato ed avrei pensato di separare il foglio DB tenendolo distinto dalla parte di reporting fatta con le pivot.
        Esiste un modo per evitare di rifare tutte le pivot (lavoro estremamente oneroso e pericoloso visti tutti i filtri e collegamenti) visto che ora dovrebbero puntare verso il file esterno DB?
        Ho Excel 2013; ma non ho le PowerPivot ammesso che queste possano essere risolutrici del problema?
        Grazie dell’attenzione
        Andrea

    2. Giulio Vito de Musso dice:

      Salve Manuel

      Non avevo pensato a questa alternativa molto più semplice e diretta e ti ringrazio per averla segnalata. Adesso i lettori di questo post potranno scegliere come fare la stessa cosa in modi diversi.

      Grazie
      Giulio

    Commenta

    Your email address will not be published. Required fields are marked *