PDA

Visualizza versione completa : Contare numero occorrenze data


Morpheus-89
10-10-2017, 16.44.13
Ciao,
ho un problema da risolvere su excel, ho una colonna nella quale ho migliaia di date nel formato gg/mm/aa
es:
24/11/14
24/11/14
24/11/14
15/12/14
15/12/14
17/12/14
17/12/14
17/12/14
18/12/14
18/12/14
30/12/14
30/12/14

mi servirebbe sapere quante volte compare la data 24/11/14, quante la data 15/12/14, quante 17/12/14 ecc... così per tutte quelle presenti nella colonna, per poi creare un grafico che mi indichi la quantità di dati scambiati nelle varie date.

Riferendomi all'esempio 24/11/14 3 volte, 15/12/14 2 volte, 17/12/14 3 volte ecc...

Mi sapere aiutare nel risolvere il problema?

Grazie ;)

borgata
10-10-2017, 17.57.43
Prova a dare un'occhiata al supporto di office (https://support.office.com/it-it/article/Contare-valori-univoci-tra-duplicati-8d9a69b3-b867-490e-82e0-a929fbc1e273?ui=it-IT&rs=it-IT&ad=IT).
Non mi pare affronti direttamente il problema ma magari le funzioni usate ti possono aiutare a capire come procedere.

Ad ogni modo non so se la cosa possa essere gestita semplicemente con le funzioni di excel, magari devi farlo con le macro.
Il modo di procedere non mi sembra complesso:
- filtri i valori per ottenere una lista di valori unici
- per ogni valore unico, conti le occorrenze

Non dimenticare di specificare versione del software, che può essere rilevante.

LoryOne
10-10-2017, 18.10.14
Usa:
=CONTA.SE(Range;Valore)

Morpheus-89
10-10-2017, 19.56.06
Sto usando excel versione 2013, anche io sono giunto alla conclusione che serva una macro, solo che non le ho mai usate, come procedimento logico ero arrivato anche io alla tua idea borgata grazie mille proverò a studiarmi un po le macro.

Lory purtroppo "non posso" usare il CONTA.SE perchè dovrei specificare a mano il valore, è il file è di circa 57000 righe

Alexsandra
10-10-2017, 20.39.27
Sarebbe possibile eseguire un Conta.se in un intervallo di date?
se andiamo a leggere la data nella 1° riga e poi saltiamo all'ultima riga e leggiamo sempre la data, credo (non mi ricordo i criteri del conta.se) credo che sia possibile usare una formula.

In alternativa possiamo fare una macro, prima si ordinano i dati in base alla data (credo che questo non ti dia fastidio) e poi andiamo a scorrere tutta la colonna fino all'ultima riga.

che dici?

borgata
11-10-2017, 00.28.03
Lui vorrebbe un conteggio separato per ogni data.
Quindi abbiamo bisogno prima di tutto di avere una lista univoca delle date per cui fare il calcolo e, successivamente, calcolare le ricorrenze di ognuna di esse.

La funzione CONTA.SE è probabilmente la più adatta al conteggio, ma non è certo sufficiente per svolgere tutto il compito.

Piuttosto vorrei capire come si intende visualizzare i risultati.
Si vuole mettere il risultato affianco ad ogni data nell'elenco principale?
O solo alla prima ricorrenza?
Si vuole creare una nuova lista univoca con affianco i risultati?

LoryOne
11-10-2017, 08.43.50
...Quindi abbiamo bisogno prima di tutto di avere una lista univoca delle date ...

Ok, quindi macro per forza.
Una volta ottenuto l'array, si usa conta.se di ogni elemento su range fisso: A questo punto, si ottengono le ascisse e le ordinate per il grafico.

Morpheus-89
11-10-2017, 09.31.46
Lui vorrebbe un conteggio separato per ogni data.
Quindi abbiamo bisogno prima di tutto di avere una lista univoca delle date per cui fare il calcolo e, successivamente, calcolare le ricorrenze di ognuna di esse.

borgata ha riassunto un due righe quello che mi servirebbe

Anche secondo me servirebbe una macro, vista comunque la quantità abbastanza elevata di dati e che non so quali date siano presenti all'interno della colonna.

Per quanto riguarda la rappresentazione dei dati mi servirebbe sapere quante occorrenze di ogni data sono presenti nel file, e poi rappresentarle su un grafico o a righe o a barre.

In un grafico a righe come questo

https://support.content.office.net/it-it/media/24180dc7-6a7a-4282-9379-0f70f4693a14.gif

pensavo ad esempio di mettere in ascisse le date e nelle ordinate il numero di occorrenze in modo da visualizzare l'andamento a colpo d'occhio

LoryOne
11-10-2017, 11.37.08
Inserisci un modulo con all'interno il codice seguente:


Public mArray() As String

Public Sub s_Array_Univoco(ByVal mRange As String)
Dim Yt As Long, Tt As Long, Yy As Long
Dim s_Data As Variant, s As String
ReDim mArray(1 To 1) As String

Yt = 1
range(mRange).Select
For Each s_Data In Selection
Yy = 0: s = s_Data.Value
For Tt = 1 To Yt
If s <> mArray(Tt) Then Yy = Yy + 1
Next
If Yt = Yy Then
mArray(Yy) = s
Yt = Yt + 1
ReDim Preserve mArray(1 To Yt)
End If
Next
If Yt > 1 Then ReDim Preserve mArray(1 To Yt - 1)
End Sub


l'array che contiene valori univoci è mArray.

Poi ho aggiunto un pulsante ed immesso:

Private Sub CommandButton1_Click()
s_Array_Univoco "A1:A65535"
MsgBox UBound(mArray)
End Sub

Morpheus-89
11-10-2017, 12.51.17
Stasera lo provo grazie Lory

LoryOne
11-10-2017, 12.55.51
Figurati, per così poco.
Prestazionalmente fa schifo, volendo si potrebbe implementare un algoritmo più efficiente, ma:
1 - VBA non è performante di suo.
2 - Non ne vale la pena, anche se potrebbe funzionare su un range vastissimo in tempi accettabili se ben programmato.

borgata
11-10-2017, 15.10.06
Si potrebbe migliorare la formula ottenendo preventivamente la posizione dell'ultima cella contenente valori utili.

LoryOne
11-10-2017, 16.08.03
Bisognerebbe indicizzare ogni elemento di un array dinamico, in modo da avere anche il numero di occorrenze per ogni elemento in un'unica passata, un po come avviene con il conteggio delle parole in un testo.
Se l'occorrenza è 1 lo mantiene, altrimenti se >1 è duplicato e non deve essere considerato...Un SELECT DISTINCT[ROW] per intenderci. ;)

LoryOne
11-10-2017, 17.20.51
Sinceramente, mi son perso in un bicchiere d'acqua. DEVO ANDARE IN PENSIONE a 40 anni :eek:
Supponendo che la tua lista di date si trovi in A1, a te basta:
1 - Scegliere dal menu Dati -> Filtro Avanzato
2 - Non impostare intervallo criteri
3 - Impostare Copia univoca dei valori.
Il resto è facile ed intuitivo
Supponendo di avere l'array univoco in B, in C1 imposti =CONTA.SE(A:A;B1) e trascini fino in fondo.
Missione compiuta.
Adesso hai in B e C quello che ti serve per il grafico.

Lost in a glass of water.

Alexsandra
11-10-2017, 21.10.03
si ma ha 57000 righe di dati, salta fuori un file mostruoso di formule.
io lo farei con una macro.

LoryOne
11-10-2017, 21.51.17
Si, ma non sono 57000 le date prese una sola volta.
In ogni caso, le matrici per le ascisse e le ordinate sono identiche in quantità di elementi ognuna.
Certo che con una query SELECT ed un COUNT(Colonna) AS Conteggio potrebbe far presto a ricopiare il recordset da un'altra parte...

Alexsandra
11-10-2017, 23.52.29
Ho fatto una macro che dovrebbe fare quello che chiedi, questo il risultato

https://lh3.googleusercontent.com/viVmt0vCHF3hqwRZLWfT-o0ahiBdxCkCkHQEGyuD_YEjmrHw4dA-R5HCYEkAfACu_IvmKEDyK0BZMuzFwYCnIU1bgCyZjTuIU4V0EN eSal4U6ByFHmuEEk_H2NRCmDKNkkO_GuHi_1Fly3pgL6cQwGaY 3_eMs86IQWdL_rwWP3Z5xJoX5hwa03Hp3VzrxfVIL736xHKBuB pyPREr5YOEy_Fo3K_pzHTxU-wG0jQIn5MthXXm-CSp3rKlCTNSoz418sXKhx5DC8LgcTU3rzcOESgPLdv7GZ9Rv6b e-RngkxviEMZ1Rmq87Jw7PNm65sAtY28ETIJIYMHYegYg_fIHiWr L3w4GOu1bQ2yQhVp302PIoMW_HoiHeMrTUBrF1LBzjHc8wf0o7 HJnFyPrIi-t5cmaBW0D_TqOj5RHnShF2Mr_jHARlYv9CEGcrhii2HpYBEpqb iECCtipsDRa6qqR9OdjCnr45NVITWuskGwjRjM6Mbref5wL4OY yRGC6BbzeFP5z7I4iOjYDr67qrmvqBlrSmrJMSu4ErBxMwqbEY vNqOLy-IK6qPlD_RC3YkOV9r8olAq6pR0ZLiF0XXa-6vdjxFzOsxhhtIlGwC4vTGAHjS_yeGT0EXlA-6FBOn26rkxwOGKy7nDyppY5CQci0SiHClLaMjnEIxXY=w670-h508-no

Ho ristretto le colonne per fare l'immagine.
In pratica esegue un ordinamento dei dati nella colonna A e poi nelle colonna P e Q ho estrapolato i dati.

Ho visto sulla guida vba usare il dizionario (poco usato) per fare un confronto di grandi moli di dati, per cui ho creato una matrice e ........ tutto il resto.

Ti lascio il codice che copierai in un nuovo modulo del tuo file, prova il tutto e vediamo come va.


Sub prova1()
Dim ultimaR, ty As Integer
Dim matri As Variant
Dim conF As Object

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
'ordina i dati nella colonna A
.Range("A1").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
End With

With ThisWorkbook.ActiveSheet
'cancella i dati da precedenti esecuzione macro nella colonna P
.Range("P1").Resize(1, 2).EntireColumn.ClearContents
ultimaR = .Cells(.Rows.Count, "A").End(xlUp).Row 'ultima riga colonna A
matri = .Range("A2:A" & ultimaR).Value 'matrice valori colonna A

Set conF = CreateObject("Scripting.dictionary")
conF.CompareMode = vbBinaryCompare
For ty = 1 To UBound(matri, 1)
conF.Item(matri(ty, 1)) = matri(ty, 1)
Next
.Range("P1") = "Date"
matri = conF.Items
.Range("P2").Resize(conF.Count, 1).Value = Application.Transpose(matri)
Set conF = Nothing

Range("Q1").Value = "Quantità"
Dim intA, intB As String

With .Range("Q2").Resize(UBound(matri) + 1)
.Formula = "=COUNTIF(A$2:A$" & ultimaR & ",P$2:P$" & UBound(matri) + 2 & " )"
.Value = .Value
intA = .Address
intB = .Offset(0, -1).Address
End With

If .ChartObjects.Count = 0 Then ActiveSheet.Shapes.AddChart.Select
.ChartObjects(1).Activate
ActiveChart.SetSourceData Source:=Range(intA)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).XValues = Range(intB)
ActiveCell.Select
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Morpheus-89
12-10-2017, 16.09.31
Ho provato il codice Alexsandra, con il Dizionario è velocissimo, ho fatto solo una piccolissima modifica, dichiarato ty come Long al posto che come Integer perchè dava overflow.

Funziona tutto alla perfezione e crea anche il grafico :cool: solo che ha qualche problemino con alcune date che non riesco a capire...

riporto una piccola parte del risultato ottenuto nelle colonne P e Q

https://s20.postimg.org/h6ryzg24p/Screenshot_20171012_160158.png (https://postimg.org/image/h6ryzg24p/)

la data nella colonna P 12/01/2014 con quantità 0 in realtà nella colonna A è 01/12/2014 e sono 9 righe, la stessa cosa sembra accadere con tutte le date che iniziano con lo 0.

https://s20.postimg.org/43wcg6bwp/Screenshot_20171012_160726.png (https://postimg.org/image/43wcg6bwp/)

https://s20.postimg.org/syfua8wqx/Screenshot_20171012_160846.png (https://postimg.org/image/syfua8wqx/)

Possibile che excel stia invertendo mese e giorno quando le date iniziano con lo 0?

Ho provato a cambiare il formato delle celle mettendo la data nel formato italiano ma non porta a una soluzione, avete qualche idea?

Grazie

LoryOne
12-10-2017, 18.57.21
Controlla bene il range di COUNTIF...Alexsandra ha saltato A1.
Prova a formattare correttamente le colonne: Deve agire su testo, non su date.
Buona scelta l'utilizzo del dizionario poichè indicizza.
Io, visto che hai il 2013, ti esorto ad utilizzare le queries SQL utilizzando come base dati lo stesso foglio di Excel.
Cerca su internet, poi crea una macro col registratore di macro (che anche Alex ha utilizzato) e sfrutta tutta la potenza di SQL.
In un unico passaggio, una query del genere:
SELECT Colonna1, COUNT(Colonna1) AS Contatore FROM Sheet1 GROUP BY Colonna1 ORDER BY Colonna1 ASC, ti crea un recordset contenente elementi univoci ordinati nella prima colonna e le occorrenze di ognuno nella seconda. ;)
SQL internamente sfrutta i migliori algoritmi di addressing, indexing e sorting dei dati, traducendosi in performances di notevole impatto.

Alexsandra
12-10-2017, 22.49.21
E' vero che c'è un problema con le date che iniziano con 0
nel riepilogo nella colonna P inverte il mese.
da 01/10 riepiloga in 10/01 per cui inserisce lo 0 nella colonna Q
e anche modificando il parametro di ricerca in vbTextCompare la situazione non cambia. provo a vedere cosa può essere.

Comunque usare SQL come dice Lory è certamente una soluzione ad Hoc sia per affidabilità, semplicità e precisione nell'estrazione.
Sistemato il problema della data è sicuramente un argomento da approfondire.

Ahh avevo iniziato dalla riga 2 considerando che nella riga 1 c'erano le intestazioni, anche se poi ho ordinato i dati dalla riga 1.

Alexsandra
14-10-2017, 18.31.38
Il formato data delle celle crea non pochi problemi .. non pochi.
Ho parzialmente risolto con una conversione dei dati (seriale) e una formattazione *al volo* delle celle.

.... ma questo mi crea dei problemi col grafico :devil:

Per il momento ho tolto il grafico e ti posto il codice, così puoi provare a fare le elaborazioni dei tuoi dati. intanto vedo di sistemare anche il grafico

Sub prova1()
Dim ultimaR As Integer
Dim matri As Variant
Dim conF As Object

With Application
.ScreenUpdating = False
.Range("A2").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
End With

With ThisWorkbook.ActiveSheet
.Range("P1").Resize(1, 2).EntireColumn.ClearContents
ultimaR = .Cells(.Rows.Count, "A").End(xlUp).Row 'ultima riga colonna A
matri = .Range("A2:A" & ultimaR).Value 'matrice valori colonna A

Set conF = CreateObject("Scripting.dictionary")
conF.CompareMode = vbTextCompare

For ty = 1 To UBound(matri, 1)
conF.Item(matri(ty, 1)) = CLng(matri(ty, 1))
Next

.Range("P1") = "Data"
matri = conF.Items
.Range("P2").Resize(conF.Count, 1).Value = Application.Transpose(matri)
Set conF = Nothing

.Range("P:P").NumberFormat = "dd-mm-yy"
Range("Q1").Value = "Quantità"

With .Range("Q2").Resize(UBound(matri) + 1)
.Formula = "=COUNTIF(A$2:A$" & ultimaR & ",P$2:P$" & UBound(matri) + 2 & " )"
.Value = .Value
End With
End With

With Application
.ScreenUpdating = True
End With

End Sub

Alexsandra
14-10-2017, 23.21.35
Ho fatto un po' di ritocchi, e sistemato anche il grafico, così dovrebbe andare.
Sub prova1()
Dim ultimaR As Integer
Dim matri As Variant
Dim conF As Object

With Application
.ScreenUpdating = False
.Range("A2").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
.Range("A:A").NumberFormat = "dd-mm-yy"
End With

With ThisWorkbook.ActiveSheet
.Range("P1").Resize(1, 2).EntireColumn.ClearContents
ultimaR = .Cells(.Rows.Count, "A").End(xlUp).Row 'ultima riga colonna A
matri = .Range("A2:A" & ultimaR).Value 'matrice valori colonna A

Set conF = CreateObject("Scripting.dictionary")
conF.CompareMode = vbTextCompare

For ty = 1 To UBound(matri, 1) 'leggo tutte le date colonna 1
conF.Item(matri(ty, 1)) = CLng(matri(ty, 1))
Next

.Range("P1") = "Data"
matri = conF.Items
.Range("P2").Resize(conF.Count, 1).Value = Application.Transpose(matri)

Set conF = Nothing
.Range("P:P").NumberFormat = "dd-mm-yy"
Range("Q1").Value = "Quantità"

With .Range("Q2").Resize(UBound(matri) + 1)
.Formula = "=COUNTIF(A$2:A$" & ultimaR & ",P$2:P$" & UBound(matri) + 2 & " )"
.Value = .Value
intervA = .Address
intervB = .Offset(0, -1).Address
End With
End With

If ActiveSheet.ChartObjects.Count > 0 Then ActiveSheet.ChartObjects.Delete

ActiveSheet.Shapes.AddChart xl3DColumnClustered, 100, 200
ActiveSheet.Shapes(1).Select
ActiveChart.SetSourceData Source:=Range(intervA, intervB), PlotBy:=xlRows

ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = "Riepilogo Frequenze"
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = "Elenco Date"
.AxisTitle.Orientation = 0
End With

With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = "Quantità"
.AxisTitle.Orientation = 90

End With
ScreenUpdating = True
End Sub

Morpheus-89
18-10-2017, 19.54.49
grazie mille Alexandra, il nuovo codice funziona :tie: ha solo qualche problema con la creazione del grafico con un overflow, c'è qualche variabile, non sono riuscito a capire quale, che accetta come valore massimo 255 e non è sufficiente.
Però escludendo la parte che fa il grafico effettua il conteggio delle occorrenze di ogni data in modo perfetto, poi ho fatto il grafico sui dati calcolati dalla macro.

Grazie

Alexsandra
18-10-2017, 20.32.26
Si in effetti avevo notato che nella creazione del grafico c'era qualche problema, ma non avevo capito dove.
comunque leggo che hai risolto :) meglio così

ciao