[Excel 2007] Ricostruzione, partendo da tabella Pivot, dei dati filtrati III - Suite Microsoft Office

Benvenuto Ospite

Aiutamici Forum » Computer & Internet » Suite Microsoft Office » [Excel 2007] Ricostruzione, partendo da tabella Pivot, dei dati filtrati III

[Excel 2007] Ricostruzione, partendo da tabella Pivot, dei dati filtrati III

Opzioni

Topic Precedente · Topic Sucessivo

aetio

Inviato: Friday, December 10, 2010 9:36:30 AM

Rank: AiutAmico

Iscritto dal : 5/10/2010
Posts: 723

Ciao,
sono alle prese con un altro problema che riguarda le mie tabelle, ma per spiegarlo in breve e senza confusione devo ricostruirne il percorso con una premessa, usando anche alcune immagini:

- in foglio2 (rif. il file-prova) abbiamo la TABELLA2 che elenca in varie colonne (H:AG) dei codici e la colonna AH che elenca un codice interno
- filtrando di volta in volta il cod. di una colonna della TABELLA2 e il cod. di col.AH otteniamo dei dati, come in questa immagine:

- mediante una macro abbiamo fatto effettuare queste operazioni congiunte di filtro in tutta la TABELLA2 e col.AH , macro che ci riporta il tutto in una tabella Pivot (nel foglio TabPivot) in cui vengono evidenziati, come in queste immagini:

a) col.B e U (idem C e V, nascoste): i cod. di col.AH di foglio2
b) col.D e W: tutti i codici di TABELLA2
c) da col.E a col.Q e da col.X a col.AJ: il numero di righe compilate, riscontrate durante ciascuna operazione congiunta di filtro TABELLA2/ col.AH; ad esempio cella I29=10, ottenuto filtrando in foglio2 il cod.23 di col.L con il cod.1 di col.AH che evidenzia appunto 10 righe

Ora, per altri fini di statistica e monitoraggio dei dati devo, grazie a questa stupenda tabella Pivot, evidenziare in un foglio (che in pratica è la copia del foglio2 senza celle bordate e colorate) la cella del codice che nel ns. esempio di cui sopra ha generato il valore 10 di cella I29, che come vediamo è il cod.23 di col.L di riga 569. Avendo sperimentato che il sistema della bordatura delle celle da evidenziare è assai valido, vorrei poterlo applicare anche in questo caso con l’accortezza di usare un diverso colore (che imposterò di volta in volta nella macro) a seconda del valore che sto isolando: ad esempio il valore 10 (righe) ha il rosso, l’11 (righe) ha il blu etc. come nell'immagine d'insieme relativa alla tabella preparata per il valore 10

da cui si ricava che:
col.J riga 4, il cod.117 filtrato con il cod.6 di col.AH ha generato una tabella di 10 righe
col.P riga 5, il cod.64 filtrato con il cod.5 di col.AH ha generato una tabella di 10 righe
col.M riga 6, il cod.72 filtrato con il cod.4 di col.AH ha generato una tabella di 10 righe
col.AA riga 6, il cod.45 filtrato con il cod.4 di col.AH ha generato una tabella di 10 righe
etc.

Per comodità riporto in sequenza le macro, adattate alle mie tabelle:

-Confronto tra tabella2 e col. AH e compilazione tabella Pivot

Code:

Sub confronta_Tabelle2()
Application.ScreenUpdating = False

With Sheets("TabPivot")
.Cells.Clear
End With
Intab2 = 8
Ftab2 = 20
Pr = 1
For Tab2 = 1 To 2
Range("AK2:BH" & Rows.Count).ClearContents
'Range("AK2:bH65536").ClearContents
uriga = Range("H" & Rows.Count).End(xlUp).Row
Riga = 2
col = 37
n = 34
For A = Intab2 To Ftab2
For i = 3 To uriga
Cells(Riga, col).Value = Cells(i, n).Value
Cells(Riga, col + 1) = n
Cells(Riga, col + 2) = A
Cells(Riga, col + 3) = Cells(i, A).Value
ctrl = True
If ctrl Then
Riga = Riga + 1
Else
Riga = Riga
End If
ctrl = False
Next i
Next A
A = 33
Call CreaPivot2
Pr = Pr + 1
Sheets("foglio2").Select
col = col
Riga = 2
Intab2 = 21
Ftab2 = 33
Next Tab2

Application.ScreenUpdating = True
End Sub

Sub CreaPivot2()
Select Case Tab2
Case 1
colp = 3
Rtab = Sheets("TabPivot").Cells(Rows.Count, colp).End(xlUp).Row + 3
Case 2
colp = 22
Rtab = Sheets("TabPivot").Cells(Rows.Count, colp).End(xlUp).Row + 3
End Select
Set Rng = Cells(1, col).CurrentRegion
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Rng.Address).CreatePivotTable TableDestination:=Sheets("TabPivot").Cells( _
Rtab, colp), TableName:="Tabella_pivot" & Pr
Sheets("TabPivot").Select
ActiveSheet.PivotTables("Tabella_pivot" & Pr).SmallGrid = False
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Numero")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Compon.")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Numero")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Somma di Numero")
.Function = xlCount
.Caption = "conteggio di numero"
End With
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Tab2")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabella_pivot" & Pr).PivotFields("Tab1")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tabella_pivot" & Pr).ColumnGrand = False
ActiveSheet.PivotTables("Tabella_pivot" & Pr).RowGrand = False
Set Rng = Nothing
End Sub

- Ricerca della sogente in foglio2

Code:

Sub CercaSorgente_AH()
With Sheets("TabPivot")
Riga = ActiveCell.Row
col = ActiveCell.Column
Select Case col
Case 5 To 17
Y = 2
Z = 4
Case 24 To 36
Y = 21
Z = 23
End Select
NrTab1 = 34
Nrtab2 = .Cells(6, col).Value
Nr = .Cells(Riga, Y).Value
Comp = .Cells(Riga, Z).Value
End With
With Sheets("Foglio2")
Set area = .Range("AH2", .Range("AH2").End(xlDown))
For Each cl In area
If cl.Value = Nr And Comp = .Cells(cl.Row, Nrtab2).Value Then
CellRif = .Cells(cl.Row, Nrtab2).Address
MsgBox ("Il Valore selezionato è riferito" & vbCrLf _
& "alla Cella " & CellRif & " del Foglio2 ")
Exit For
End If
Next
End With
Sheets("foglio2").Select
If ActiveSheet.FilterMode = True Then
Selection.AutoFilter
End If
Range("A1:AH1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=NrTab1, Criteria1:=Nr
Selection.AutoFilter Field:=Nrtab2, Criteria1:=Comp
End Sub

Certo è che di macro che ruotano intorno alle mie tabelle, carissimo a10n11, me ne hai scritte davvero tante e mai e poi mai mi sarei immaginato di poter trovare ad ogni mio problema (spesso assai difficile, se non impossibile, da comprendere per un Osservatore esterno) soluzione più eccellente di quella da te propostami… posso quidi affermare che di sicuro chiuderò quest’anno- per molti aspetti assai difficile- in super-bellezza…Grazie solo a te, e di questo te ne sarò grato in Eterno ;)))
Grazie infinite, saluti
eZio

Torna in alto

Sponsor

Inviato: Friday, December 10, 2010 9:36:30 AM

Torna in alto

a10n11

Inviato: Friday, December 10, 2010 12:55:52 PM

Rank: AiutAmico

Iscritto dal : 5/29/2003
Posts: 1,694

salve
partendo dall'immagine che segue:

tu vorresti che nella tabella generale del foglio2 vengano evidenziati tutte e 5 le ricorrenze del codice 18 (componente) che abbia come riferimento il numero 3 (colonna AH) e presente nella colonna 2 di Tabella 2 (che nella tabella Pivot è indicata come colonna 9 ?

Se è questo che chiedi, serve creare un ciclo di confronto non difficle da realizzare ma potrebbe diventare piuttosto corposo come elaborazione vista la mole di dati che hai nelle tabelle.
Non so se riesco a farlo in giornata al massimo ti proporrò una soluzione entro lunedì.

Approffitto per ricollegarmi alla domanda posta nell'altro 3D.
L'aumento smisurato del volume del file, non è genrato dalla macro, Nella macro in questione non è definita nessuna area e comunque servirebbe solo a svuotare la memoria e non modificare le dimensioni del file.
Se hai fatto delle duplicazioni dei fogli, probabilmente questo è imputabile ad esso. Dovresti provare a ripulire i fogli
selezionando le righe e colonne apparentemente vuote ed eliminarle per vedere se il tuo file si riduce.
saluti
Giap

Torna in alto

aetio

Inviato: Friday, December 10, 2010 1:35:48 PM

Rank: AiutAmico

Iscritto dal : 5/10/2010
Posts: 723

Ciao,
... nessun problema di tempo, ci mancherebbe altro!! :)
Dunque:

Commenta:

non tutte e 5, solo quella che nella TABELLA2 filtrata del foglio 2 sta più in alto (la più recente). Prendendo ad esempio l'immagine

solo la cella del cod.23 di col.L di riga 569 (la cella L569), non di tutte e 10 le ricorrenze con riferimento cod.1 (col.AH), ricorrenze che comunque fanno parte indissolubile di quella circostanza, ma che qui non mi serve evidenziare; quelle 10 righe sono state infatti generate dalla cella L569 in concerto con la cella AH569 (altrimenti le righe sarebbero state, ovvio, solo 9 e non 10)

Nella tua immagine, quindi, solo la cella della prima delle 5 ricorrenze di cod.18 della 2^ colonna di TABELLA2 di foglio2 che abbia come riferimento il cod.3 (colonna AH), alla quale la tabella Pivot fà riferimento.
Grazie infinite, saluti
eZio

Nota:

Commenta:

Se hai fatto delle duplicazioni dei fogli, probabilmente questo è imputabile ad esso. Dovresti provare a ripulire i fogli
selezionando le righe e colonne apparentemente vuote ed eliminarle per vedere se il tuo file si riduce.

Dato che ripulire il file ogni volta è piuttosto laborioso e spesso quei duplicati servono comunque, preferisco fare come Odisseo: cavallo di Troia :)))
lavoro in una copia del file, copio nel file originale i dati elaborati che mi servono, e cestino la copia senza salvarne le modifiche :)
Grazie per la spiegazione delle cause di sì tanta lievitazione!

Torna in alto

a10n11

Inviato: Saturday, December 11, 2010 11:15:00 AM

Rank: AiutAmico

Iscritto dal : 5/29/2003
Posts: 1,694

salve
a meno che non prenda una cantonata, in base alle spiegazioni date, il lavoro (salvo alcune piccole modifiche)
lo hai già nelle macro "CopiaFiltrati" e "CercaSorgente"

Le due nuove macro frutto della modifica, lavorano come segue:
La macro Sub evidenziaInTabella() , ti filtra le due tabelle pivot con il criterio che ti verrà richiesto dalla Inputbox effettuerà la sua elaborazione e lancerà in automatico la macro
Sub EvidenziaSorgente_AH() che cerca nel foglio2 la sorgente del valore cercato.
NB. io ho utilizzato il foglio2 come tabella da evidenziare con la bordatura, se tu hai copiato la tabella origiale su un altro foglio cambia il riferimento del foglio di destinatzione in questa istruzione: (segnata in rosso)

Macro Sub evidenziaInTabella()
...........................
Call EvidenziaSorgente_AH
With Sheets("Foglio2").Range(Cellrif)
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = colore
End With
...................................

macro Sub EvidenziaSorgente_AH()
.................................
With Sheets("Foglio2")
Set area2 = .Range("AH2", .Range("AH2").End(xlDown))
For Each cl In area2
...........................................

quelle che seguono sono le due macro modificate:

Code:

Public Cellrif As String
Sub evidenziaInTabella()
Dim Area As Range
Application.ScreenUpdating = False
Ur = Range("D" & Rows.Count).End(xlUp).Row
criterio = InputBox("Criterio Filtro")
Select Case criterio
Case 10
colore = 3
Case 11
colore = 4
Case 12
colore = 7
End Select
col = 5
Colfin = 17
Riga = 1
For n = 1 To 2
Range(Cells(Riga, col), Cells(Ur, Colfin)).Select
Selection.AutoFilter
For i = 1 To 13
Selection.AutoFilter Field:=i, Criteria1:=criterio
On Error Resume Next
Set Area = Range(Cells(7, col), Cells(Ur, col)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Area Is Nothing Then
GoTo Fine
End If
For Each cl In Area
cl.Select
Call EvidenziaSorgente_AH
With Sheets("Foglio2").Range(Cellrif)
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = colore
End With
Next cl
Fine:
Range("a1").Select
Selection.AutoFilter Field:=i
col = col + 1
Set Area = Nothing
Next i
Ur = Range("U" & Rows.Count).End(xlUp).Row
col = 22
Colfin = 34
Next n
Application.ScreenUpdating = True
End Sub

Sub EvidenziaSorgente_AH()
With Sheets("TabPivot")
Riga = ActiveCell.Row
colTP = ActiveCell.Column
Select Case colTP
Case 5 To 17
Y = 2
z = 4
Case 22 To 34
Y = 19
z = 21
End Select
NrTab1 = 34
Nrtab2 = .Cells(6, colTP).Value
Nr = .Cells(Riga, Y).Value
Comp = .Cells(Riga, z).Value
End With
With Sheets("Foglio2")
Set area2 = .Range("AH2", .Range("AH2").End(xlDown))
For Each cl In area2
If cl.Value = Nr And Comp = .Cells(cl.Row, Nrtab2).Value Then
Cellrif = .Cells(cl.Row, Nrtab2).Address
Exit For
End If
Next
End With
Set area2 = Nothing
End Sub

Ulteriore annotazione:

nell'istruzione che segue:

.....
Select Case criterio
Case 10
colore = 3
Case 11
colore = 4
Case 12
colore = 7
End Select
..................

è stato prevista la bordatura per i valori uguali a 10-11-12 rispettivamente con i colori 3-4-7
puoi estendere e modificare a piacimento.

provale e vedi se ho centrato il problema.
saluti
Giap

Torna in alto

aetio

Inviato: Saturday, December 11, 2010 3:40:25 PM

Rank: AiutAmico

Iscritto dal : 5/10/2010
Posts: 723

Ciao,
esito: semplicemente... STU-PE-FA-CEN-TE
Con le variazioni necessarie per adattare le macro alle mie tabelle funziona tutto a meraviglia. Non ho parole... praticamente in un batter d'occhio risolve il lavoro di ore di ricerca.
Confessalo (almeno a me...): sei un Angelo del Paradiso... :))))
Grazie infinite, saluti
eZio

Torna in alto

aetio

Inviato: Thursday, January 30, 2014 8:45:30 AM

Rank: AiutAmico

Iscritto dal : 5/10/2010
Posts: 723

Ciao,
sto riguardando questo vecchio 3d, in particolare la 1^ macro del code incluso nel quote:

a10n11 ha scritto:

Code:

Public Cellrif As String
Sub evidenziaInTabella()
Dim Area As Range
Application.ScreenUpdating = False
Ur = Range("D" & Rows.Count).End(xlUp).Row
criterio = InputBox("Criterio Filtro")
Select Case criterio
Case 10
colore = 3
Case 11
colore = 4
Case 12
colore = 7
End Select
col = 5
Colfin = 17
Riga = 1
For n = 1 To 2
Range(Cells(Riga, col), Cells(Ur, Colfin)).Select
Selection.AutoFilter
For i = 1 To 13

On Error Resume Next
Set Area = Range(Cells(7, col), Cells(Ur, col)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Area Is Nothing Then
GoTo Fine
End If
For Each cl In Area
cl.Select
Call EvidenziaSorgente_AH
With Sheets("Foglio2").Range(Cellrif)
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = colore
End With
Next cl
Fine:
Range("a1").Select
Selection.AutoFilter Field:=i
col = col + 1
Set Area = Nothing
Next i
Ur = Range("U" & Rows.Count).End(xlUp).Row
col = 22
Colfin = 34
Next n
Application.ScreenUpdating = True
End Sub

Sub EvidenziaSorgente_AH()
With Sheets("TabPivot")
Riga = ActiveCell.Row
colTP = ActiveCell.Column
Select Case colTP
Case 5 To 17
Y = 2
z = 4
Case 22 To 34
Y = 19
z = 21
End Select
NrTab1 = 34
Nrtab2 = .Cells(6, colTP).Value
Nr = .Cells(Riga, Y).Value
Comp = .Cells(Riga, z).Value
End With
With Sheets("Foglio2")
Set area2 = .Range("AH2", .Range("AH2").End(xlDown))
For Each cl In area2
If cl.Value = Nr And Comp = .Cells(cl.Row, Nrtab2).Value Then
Cellrif = .Cells(cl.Row, Nrtab2).Address
Exit For
End If
Next
End With
Set area2 = Nothing
End Sub

in cui la macro in questione, previa inserimento dell'altezza della tabella filtrata, restituisce la tabella con la cella bordata il cui valore genera quella tabella di altezza definita

ma, ahimè, non riesco a mettere a fuoco il significato dell'istruzione
For i = 1 To 13
a cui fa seguito l'istruzione Selection.AutoFilter Field:=i, Criteria1:=criterio

So di fare una figuraccia, ma sono altrettanto consapevole che per imparare (non v'è limite al Sapere) bisogna sempre essere umili...
Grazie assai anche per la comprensione, buona giornata
eZio

.

Torna in alto

a10n11

Inviato: Thursday, January 30, 2014 9:50:27 AM

Rank: AiutAmico

Iscritto dal : 5/29/2003
Posts: 1,694

salve
devi partire da questi valori:
col = 5
Colfin = 17

che sarebbero le colonne dipartenza e fine della tabella da analizzare (13 colonne).
pertanto:
l'istruzione : For i = 1 To 13

esegue la scansione colonna per colonna della tabella

saluti
Giap

Torna in alto

aetio

Inviato: Thursday, January 30, 2014 9:56:10 AM

Rank: AiutAmico

Iscritto dal : 5/10/2010
Posts: 723

Ciao,
non avevo considerato questo importante dettaglio... (!!)
Grazie assai, buona giornata
eZio

Torna in alto

Utenti presenti in questo topic

Guest

Aiutamici Forum » Computer & Internet » Suite Microsoft Office » [Excel 2007] Ricostruzione, partendo da tabella Pivot, dei dati filtrati III

Salta al Forum

Aggiunta nuovi Topic disabilitata in questo forum.
Risposte disabilitate in questo forum.
Eliminazione tuoi Post disabilitata in questo forum.
Modifica dei tuoi post disabilitata in questo forum.
Creazione Sondaggi disabilitata in questo forum.
Voto ai sondaggi disabilitato in questo forum.

Invia topic via Email

RSS Feed

Watch this topic

Stampa topic

Normale

Threaded