Indicare numero con presenza decrescente

Versione Completa   Stampa   Cerca   Utenti   Iscriviti     Condividi : FacebookTwitter
BG66
00lunedì 12 giugno 2017 10:46
Ciao a tutti,
vorrei ottenere una graduatoria dei 5 numeri più presenti (NON il numero di presenze) in un elenco definito ma dinamico.
il dato atteso per T9 è:
22 - 15 - 2 - 14 - 26.

Non necessito di particolari sofismi.In presenza di numeri con identica presenza l'uno vale l'altro (nell'esempio ho escluso il n°13.....e non sono scaramantico)

Grazie in anticipo

federico460
00lunedì 12 giugno 2017 12:12
ciao


ti direi


=INDICE(AM$3:AM$17;CONFRONTA(GRANDE(FREQUENZA(AM$3:AM$17;AM$3:AM$17);RIF.RIGA(A1));FREQUENZA(AM$3:AM$17;AM$3:AM$17);0))



matriciale

ma hai troppi doppioni che non vengono letti
BG66
00lunedì 12 giugno 2017 14:16
Ciao Federico,
effettivamente ci sei andato vicino. Peccato il ripetersi del 2!!



dodo47
00lunedì 12 giugno 2017 16:08
Ciao
questo perché hai detto:
"....In presenza di numeri con identica presenza l'uno vale l'altro ...".
Avresti dovuto specificare che volevi il 2° e poi il 3° ecc.

A parte ciò, se in una colonna hai:
5 volte 21
5 volte 22
5 volte 23

cosa vorresti nelle 5 posizioni da 1 a 5 ?

saluti
BG66
00lunedì 12 giugno 2017 17:48
Ciao Domenico,
mi basterebbe che i primi tre posti fossero occupati dai n° 21-22-23 indipendentemente dalla sequenza.

Effettivamente era meglio specificare che l'unico vincolo era l'assenza di doppioni (che era il limite del suggerimento di Federico [che ringrazio])


BG66
00martedì 13 giugno 2017 16:11
Ciao a tutti,
per superare l'empasse, si potrebbe bypassare il lato formule e con vba dirgli di elencare TUTTI i codici (ovviamente per colonna impianto) tenendo conto del numero di presenza dello stesso nella tabella di destra?

locatevaresino
10martedì 13 giugno 2017 22:08
ciao

tanto per passare il tempo

una sub() o macro costruita col creatore e modificata in sequito in vari parametri
inserita nel modulo del foglio da te postato


Option Explicit
Sub Macro_col_creatore()
Dim cont As String
Dim i As Long
Dim cBB As Long
cBB = 54
Dim Nriga As Long

For i = 39 To 50
Range(Cells(3, i), Cells(17, i)).Select
Selection.Copy
Cells(3, cBB).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range(Cells(2, cBB), Cells(17, cBB)).RemoveDuplicates Columns:=1, Header:= _
xlYes

Nriga = Cells(Rows.Count, cBB).End(xlUp).Row + 1 ''dimenticato
Cells(3, cBB + 1).Select
cont = "=COUNTIF(R3C" & i & ":R17C" & i & ",RC[-1])"
ActiveCell.FormulaR1C1 = cont
Selection.AutoFill Destination:=Range(Cells(3, cBB + 1), Cells(Nriga, cBB + 1)), Type:=xlFillDefault
Range(Cells(3, cBB), Cells(Nriga, cBB + 1)).Select
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Add Key:=Cells(3, cBB + 1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Helper").Sort
.SetRange Range(Cells(2, cBB), Cells(Nriga, cBB + 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Add Key:=Range(Cells(3, cBB + 1), Cells(Nriga, cBB + 1)) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Helper").Sort
.SetRange Range(Cells(2, cBB), Cells(Nriga, cBB + 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(8, cBB), Cells(Nriga, cBB + 1)).ClearContents
cBB = cBB + 2
Next i
End Sub





posto anche il file con la sub inserita

PS ho dimenticato un + 1 nella sub al calcolo della riga inseririto
in questa , manca nel file
Nriga = Cells(Rows.Count, cBB).End(xlUp).Row + 1
BG66
00mercoledì 14 giugno 2017 05:15
Re:
Ciao Locate,
locatevaresino, 13/06/2017 22.08:

ciao
una sub() o macro costruita col creatore e modificata in sequito in vari parametri
inserita nel modulo del foglio da te postato



...ritengo che tu abbia fatto ben più di una modifica ( [SM=g27811] ) per ottenere uno script cosi composto. Avevo provato anch'io con il registratore con zero risultato.

Tornando a bomba, dalle prove fatte sembra che il limite sia la dinamicità.
In pratica in T9 ho modificato tutti i valori sostituendoli con il nr.22 e la macro reagiva:

poi ho ipotizzato la comparsa di un nuovo numero e la macro non si è "adattata" completamente:


E sistemabile? Come?

Grazie in anticipo.

federico460
00mercoledì 14 giugno 2017 09:44
ciao

ciao Locate

ho trovato un vecchi post su questo forum

su un modulo


' Procedure : uModa
' Author : Scossa
' Date : 26/02/2012
' Purpose :
'---------------------------------------------------------------------------------------


Public Function uModa2(ByVal rng As Range, Optional ByVal nPos As Integer = 1) As Variant
Dim nCnt As Long
Dim nMax As Long
Dim cDati As New Collection
Dim cMax As New Collection
Dim cOrdine As New Collection
Dim cella As Range
Dim vDato As Variant
Dim aDati() As Long
Dim vRetVal As Variant
Dim j As Integer

On Error Resume Next
For Each cella In rng
With cella
nCnt = Application.WorksheetFunction.CountIf(rng, .Value)
cDati.Add Array(.Value, nCnt), CStr(.Value)
cMax.Add nCnt, CStr(nCnt)
End With
Next
On Error GoTo 0
j = 0
ReDim aDati(1 To cMax.Count)
For Each vDato In cMax
j = j + 1
aDati(j) = vDato
Next

nPos = Application.WorksheetFunction.Max(nPos, 1)
nPos = Application.WorksheetFunction.Min(nPos, cDati.Count)
'eventualemente togliere il commento alla riga sotto
'nPos = Application.WorksheetFunction.Min(nPos, cMax.Count)
nMax = Application.WorksheetFunction.Large(aDati, nPos)

On Error Resume Next
For Each vDato In cDati
If vDato(1) = nMax Then
vRetVal = vRetVal & "; " & vDato(0)
Else
cOrdine.Add vDato, CStr(vDato(0))
End If
Next
vRetVal = Mid(vRetVal, 3)
cOrdine.Add Array(vRetVal, nMax), CStr(vRetVal)
On Error GoTo 0

For Each vDato In cOrdine
If vDato(1) = nMax Then
vRetVal = vDato(0)
End If
Next

If IsNumeric(vRetVal) Then vRetVal = vRetVal * 1

uModa2 = Array(vRetVal, nMax)

End Function



in cella BB3

=SE.ERRORE(uModa2($AM$3:$AM$17;BA3);"")



e tira in giù


come vedi ti indica tutti i pari merito

non è proprio quello che cerchi
ma o l'autore o Locate sicuramente riusciranno a modificarla

io no [SM=g27829]
locatevaresino
00mercoledì 14 giugno 2017 10:11
ciao BG66

quindi in parole povere a te serve una sub() dinamica, che ad ogni variazione faccia il ricalcolo di questa
basta apportare piccola modifica a quella di prima
da sostituire con la precedente


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("AM3:AX17")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim cont As String
Dim i As Long
Dim cBB As Long
cBB = 54
Dim Nriga As Long

For i = 39 To 50
Range(Cells(3, i), Cells(17, i)).Select
Selection.Copy
Cells(3, cBB).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range(Cells(2, cBB), Cells(17, cBB)).RemoveDuplicates Columns:=1, Header:= _
xlYes

Nriga = Cells(Rows.Count, cBB).End(xlUp).Row + 1
Cells(3, cBB + 1).Select
cont = "=COUNTIF(R3C" & i & ":R17C" & i & ",RC[-1])"
ActiveCell.FormulaR1C1 = cont
Selection.AutoFill Destination:=Range(Cells(3, cBB + 1), Cells(Nriga, cBB + 1)), Type:=xlFillDefault
Range(Cells(3, cBB), Cells(Nriga, cBB + 1)).Select
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Add Key:=Cells(3, cBB + 1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Helper").Sort
.SetRange Range(Cells(2, cBB), Cells(Nriga, cBB + 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Helper").Sort.SortFields.Add Key:=Range(Cells(3, cBB + 1), Cells(Nriga, cBB + 1)) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Helper").Sort
.SetRange Range(Cells(2, cBB), Cells(Nriga, cBB + 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(8, cBB), Cells(Nriga, cBB + 1)).ClearContents
cBB = cBB + 2
Next i
Application.ScreenUpdating = True
Target.Select
End Sub



ciao Federico Questa sera se avro' tempo vedro' di studiarmi la funzione di Scossa, che saluto, e capire dove apportare modifiche per risolvere il problema



Marius44
00mercoledì 14 giugno 2017 11:43
Ciao Gene ed un saluto a tutta la compagnia.

E' noto che con le formule non ci vado a nozze ma il quesito mi ha intrigato e, con un giro nella rete, ho trovato una formula (da me integrata) che potrebbe essere la soluzione.
Il sito è il seguente (senza spazi)
h t t p://www.riolab.org/index.php?option=com_content&view=article&id=203
e la formula è questa:
=GRANDE(SE(FREQUENZA(Elenco;Elenco)>1;Elenco;"");RIF.RIGA(A1))
Poichè potrebbe venir fuori il fastidioso #N/D ho integrato la stessa in questo modo:
=SE.ERRORE(GRANDE(SE(FREQUENZA(Elenco;Elenco)>1;Elenco;"");RIF.RIGA(A1));"")


da mettere in BB3 e confermare con CTRL+SHIFT+ENTER (è una matriciale) e tirare giù fin dove serve. Appariranno i valori la cui frequenza è maggiore di 1

Il nome Elenco si riferisce all'intervallo AM3:AM17

Può andare? Penso di si, se ci si accontenta di non vedere quelli minore di 1

Fai sapere. Ciao,
Mario

federico460
00mercoledì 14 giugno 2017 12:12
ciao

mi dispiace
ma non è quello che chiede BG

il valore che tu trovi è il più GRANDE (alto)

non il più frequente [SM=g27813]

fai una prova mettendo nella tabella 7 volte 2

ti dirà
22 come primo

ma non lo è deve essere il 2 perché il più presente.

26 non lo calcola perché è presente solo una volta

se guardi la mia formula iniziale
utilizza il GRANDE ma abbinato alla FREQUENZA
Marius44
00mercoledì 14 giugno 2017 15:54
Ciao Federico
Hai ragione. Ma mi salvo in corner perchè l'ho detto che con le formule non vado d'accordo. E dire che avevo fatte tante prove.
Mah .... vedo se riesco a farlo con VBA (senza guardare la macro di scossa altrimenti ... vuole i diritti [SM=x423029] [SM=x423030] )

Ciao,
Mario
Marius44
00mercoledì 14 giugno 2017 17:07
Salve a tutti
Mettete da parte la professionalità e ditemi se è quello che serve.
Sub Quanti_Ordina()
Dim num(1 To 15, 1 To 2)
For i = 3 To 16
    num(i - 2, 1) = Range("AM" & i).Value
Next i
For i = 1 To 14
    For j = i + 1 To 15
        If num(i, 1) = num(j, 1) Then
            num(i, 2) = num(i, 2) + 1
            num(j, 1) = 0: num(j, 2) = 0
        End If
    Next j
Next i
'sort
For i = 1 To 14
    For j = i + 1 To 15
    If num(i, 2) < num(j, 2) Then
        temp1 = num(j, 1): temp2 = num(j, 2)
            num(j, 1) = num(i, 1): num(j, 2) = num(i, 2)
            num(i, 1) = temp1: num(i, 2) = temp2
        End If
    Next j
Next i
a = 2
For i = 1 To 15
    If num(i, 1) <> 0 Then
        a = a + 1
        Range("BB" & a) = num(i, 1)
    End If
Next i
End Sub

Ovviamene sono da dichiarare tutte le variabili, rendere la macro dinamica per tutte le colonne interessate, ecc. ecc. ma per queste cose Gene è in grado di provvedere.

Aspetto vostre critiche. Ciao,
Mario
dodo47
00mercoledì 14 giugno 2017 17:32
Re:
BG66, 12/06/2017 10.46:

Ciao a tutti,
vorrei ottenere una graduatoria dei 5 numeri più presenti (NON il numero di presenze) in un elenco definito ma dinamico.
il dato atteso per T9 è:
22 - 15 - 2 - 14 - 26.
Non necessito di particolari sofismi.In presenza di numeri con identica presenza l'uno vale l'altro (nell'esempio ho escluso il n°13.....e non sono scaramantico)
Grazie in anticipo



Ciao
pensavo non volessi utilizzare delle macro.
Ti allego il file che provvede a quanto da te richiesto (e successive precisazioni).
La routine sviluppa tutte le 15 colonne interessate, riportando le prime 5 ricorrenze maggiori.
Se le vuoi stampate tutte, c'è un commento nella macro su come farlo.
Mancano quasi tutte le dim, la pulizia dei risultati precedenti ed una sana gestione di errore...

In sostanza: utilizzo una collection (mCol) per ottenere per ogni colonna i numeri univoci > la trasferisco su un array 2D (mArr) inserendo nella 2^ "colonna" il numero di ricorrenze > ordino tale array decrescente per tale colonna.

cari saluti

Edit: non ho capito se le colonne vuote vicino a T9 - T10 ecc ti servono a qualche cosa oppure ci vuoi riportato il numero delle ricorrenze; se fosse così, inserisci nel seguente ciclo quanto evidenziato:

...
    For r = 3 To 7 
        On Error Resume Next
        Cells(r, DestC) = mArr(k, 0)
        Cells(r, DestC).Offset(, 1) = mArr(k, 1) '<<<<<<<<<<<
        k = k + 1
    Next r
...



BG66
00mercoledì 14 giugno 2017 22:11
[RISOLTO]

Ciao e un immenso grazie a tutti.

Le vs. soluzioni nessuna ESCLUSA sono perfette.
Ora passo a studiarle per capire le diversità "di genere".

Alla prossima.
Questa è la versione 'lo-fi' del Forum Per visualizzare la versione completa clicca qui
Tutti gli orari sono GMT+01:00. Adesso sono le 19:15.
Copyright © 2000-2024 FFZ srl - www.freeforumzone.com