aiuto somma 70

Hai un problema con Excel?

Nuova discussione

aiuto somma 70%
di cilosrip (utente non iscritto) data: 23/01/2014 15:06:03
salve a tutti. avrei bisogno di una funzione vba che mi permetta di fare la seguente operazione. dati i segueni numeri così disposti per esempio (non spostabili in elenco)

30
43
31
54
20
38
38
48
27
12
36
71
126
169
99
106
136
149
138
93
94
66
71
1

Il cui valore più alto è restituito dal massimo valore in elenco (ovvero in questo caso 169);
data la somma dei valori totali uguale a 1707 e di questi preso come dato di paragone il 70% della somma ovvero:

1707*70/100=1194,9

si voglia calcolare (sommando i valori e confrontandoli nel seguente modo partendo dal valore max = 169) un risultato prossimo e inferiore a 1194,9

es.
si confronta partendo da 169 il valore subito prima e subito dopo e si sceglie il più grande:

126 <------
169
99

la loro somma darà: 169+126=295

poi si prende il valore sopra a quello appena scelto e si paragona a quello scartato (ovvero 99). tra i due deve essere preso il più alto:

71
126
169
99 <------

confrontandolo col 71 si prende il 99 perchè più grande e si somma ai due numeri precedenti:
169+126=295
295+99=394

questa operazione si dovrebbe ripetere con lo stesso meccanismo fino ad ottenere il seguente risultato:

71
126
169 ------- (valore max dell'elenco)
99
106
136
149
138
93
94

la cui somma totale è pari a 1181 che risulta essere l'unico dato più vicino a 1194,9 (il 70% della somma totale) e comunque inferiore a 1194,9.

Avrei bisogno di una macro che faccia questo e che ogni volta mi restituisca i valori che prende per cercare di arrivare alla somma vicina al 70% della somma totale e che li disponga nel seguente modo:

Dati ---- Dati
Scartati ---- Scelti

8---- 0
3 ----0
30----0
43----0
31----0
54----0
20----0
38----0
38----0
48----0
27----0
12----0
36----0
0----71
0----126
0----169
0----99
0----106
0----136
0----149
0----138
0----93
0----94
66----0
71----0
1----0

ovvero mi dovrebbe scrivere i numeri scelti e quelli scartati in due colonne diverse senza alterare il numero di righe.
mi potete aiutare per favore? ve ne sarei infinitamente grato.
di scossa data: 23/01/2014 17:05:32
Dovresti aggiungere anche i successivi passaggi in chiaro, perché non capisco perché scarti il 66 e prendi il 71.
di cilosrip (utente non iscritto) data: 23/01/2014 18:40:16
lo scarto semplicemente perchè è più grande. faccio esempio:

parto da 169
prendo i numeri immediatamente prima e dopo il 169 che sono 126 e 99. li confronto e vedo che il 126 è il più grande tra i due

126
169----
99

adesso ricomincio. (avendo sommato 169+126) il risultato lo tengo da parte. vado avanti

prendo il numero prima del 126, che è il 71 e lo confronto con il 99. ma il 99 è più grande per cui prendo il 99 e lo vado a sommare ai due numeri di prima:

169+126+99

adesso vado a prendere il numero dopo il 99 che è il 106:

71
126
169
99
106 lo confronto con il 71. anche il 106 è più grande e allora lo vado a sommare a quelli di prima:

169+126+99+106. continuo:

il numero dopo il 106 è il 136 che anche questo è più grande del 71. per cui lo sommo al resto:

169+126+99+106+136.

se io avessi avuto dopo il 136, anzichè il 149, il numero 15 (per esempio) lo confrontavo col 71 e vedevo che il numero era più piccolo e allora sceglievo il 71 e lo sommavo agli altri. sfortunatamente per questa sequenza non ci sono numeri più piccoli del 71 se non il 66 alla fine. quindi devo arrivare al totale massimo che posso ottenere sommando la sequenza secondo questa logica che mi dia un risultato minore o uguale a 1194,9 (il 70% della somma totale di tutti i numeri in elenco). la macro dovrebbe restituirmi due colonne quindi così fatte:

Dati ---- Dati
Scartati ---- Scelti

8---- 0
3 ----0
30----0
43----0
31----0
54----0
20----0
38----0
38----0
48----0
27----0
12----0
36----0
0----71
0----126
0----169
0----99
0----106
0----136
0----149
0----138
0----93
0----94
66----0
71----0
1----0

mi puoi aiutare?
di cilosrip (utente non iscritto) data: 23/01/2014 18:41:13
scusa all'inizio dovevo scrivere:

"lo scarto semplicemente perchè è più PICCOLO. faccio esempio: "

non più grande
di scossa data: 23/01/2014 19:22:43
cit.: "mi puoi aiutare?"

Io posso anche aiutarti, ma prima devi far qualcosa tu: come già richiesto devi mettere TUTTI i passaggi in chiaro, perché non sono ancora convinto della sequenza risultante.
di ninai (utente non iscritto) data: 24/01/2014 07:37:54
Ciao
di capire, in buona parte ho capito cosa vuole ottenere, qualche dummio l'ho su cosa succede se la scelta va a finire sul primo o ultimo della serie, prima di arrivare alla soglia del 70%.
ho allestito un file con colonne di appoggio, è tutto da verificare se sono rispettate le aspettatve.
in breve:

tuoi dati in : C6:C26

in D3:
=(C3=MAX($C$3:$C$26))*C3 e trascini fino a D26

in E3:
=((D$31+MAX(INDIRETTO("C"&2+MAX($E$1:E$1)+1);INDIRETTO("C"&2+MIN($E$1:E$1)-1))<=$C$31)*(MAX(INDIRETTO("C"&2+MAX($E$1:E$1)+1);INDIRETTO("C"&2+MIN($E$1:E$1)-1))=$C3)*$C3)
e trascini fino a U26

in E1:
=CONFRONTA(SOMMA(D$3:D$26);D$3:D$26;0)
trascini fino a U1

in C30:
=SOMMA(C3:C26)

in C31:
=C30*0,7

in D30:
=MAX(D3:D26)
trascini fino a U30

in D31:
=SOMMA($D$30:D30)
trascini fino a U31

RISULTATI
in A3:
=(SOMMA($D3:$U3)=0)*$C3
trascini fino a A26

in B3:
=(SOMMA($D3:$U3)>0)*$C3
trascini fino a B26

ALLEGO FILE
di ninai (utente non iscritto) data: 24/01/2014 07:51:18
se la logica combacia, c'è da risolvere alche i casi di numeri duplicati
di cilosrip (utente non iscritto) data: 24/01/2014 08:28:08
ciao ninai e grazie.
allora per il fatto dei numeri finali riporto un esempio qui di seguito. per quello dove i numeri combaciano si guardano quelli immediatamente sopra e sotto. comunque dove posso trovare il file allegato ninai che mi hai lasciato?

faccio un altro esempio. un'altra lista:
0
0
0
0
0
0
0
0
0
0
0
0
25
41
31
49
29
41
26
26
4
27
17
51
25
16
36
85
127
210
112
62
52
23
25
31
46
100
65
4
80
85
124
213
108
202
79
82
74
18
52
57
94
99
121
115
38
75
18
0
0
0
0
0
0
0
0
0
0
0

Vedo quanto fa la somma di tutti i numeri:

Somma totale = 3120

faccio il 70% della somma = 2184

vedo qual'è il valore massimo tra i numeri: 213

Adesso devo sommari i numeri secondo la seguente logica (cioè confronto tra due numeri immediatamente prima e dopo quello max)
fino ad arrivare all'unico totale che sia il più vicino possibile a 2184 (che è il 70%) ma che cmq sia inferiore o uguale a 2184.

parto col primo dato e confronto i due numeri selezionati scegliendo quello più alto:

124 <---
213
108 <---

in questo caso il 124 è il più grande tra i due e lo sommo a 213:

213+124=337

Proseguoconfrontando altri due numeri (ovviamente avendo scartato prima il 108 adesso lo riprendo e prendo il numero sopra il 124)

85 <---
124
213
108 <---

Adesso il 108 è un valore più grande del nuovo numero preso , per cui scarto l'85 e vado a sommare invece il 108 agli altri:

213+124+108=445

vado avanti con la stessa logica, ovvero prendo il numero sotto il 108 e riprendo l'85:

85 <---
124
213
108
202 <---

ho il 202 che è più grande dell'85 per cui adesso sommo il 202 al resto e riscarto l'85:

213+124+108+202=647

vado avanti prendendo il numero dopo a quello che ho scelto:

85 <---
124
213
108
202
79 <---

adesso il 79 è più piccolo dell'85 per cui scelgo l'85 e lo vado a sommare scartando il 79:

213+124+108+202+85=732

adesso scelgo il numero prima dell'85 perchè è quello che ho scelto mentre mantengo il 79:

80 <---
85
124
213
108
202
79 <---

confronto l'80 al 79. l'80 è + grande per cui sommerò questo:

213+124+108+202+85+80=812

vado avanti:

4 <---
80
85
124
213
108
202
79 <---

scelgo il 79 perchè più grande del 4:

213+124+108+202+85+80+79=891

avanti:

4 <---
80
85
124
213
108
202
79
82 <---

82 perchè più grande del 4:

213+124+108+202+85+80+79+82=973

arriverò facendo i confronti fino al seguente passaggio:

4 <---
80
85
124
213
108
202
79
82
74
18
52
57
94
99
121
115
38
75
18 <---

arrivo fino qui scartando sempre il 4 perchè non ho trovato durante il percorso numeri più piccoli:

e sommo il 18 che a confronto col 4 è + grande:

80+85+124+213+108+202+79+82+74+18+52+57+94+99+121+115+38+75+18=1734

Adesso se vado avanti sotto al 18 avrò lo 0:

4 <---
80
85
124
213
108
202
79
82
74
18
52
57
94
99
121
115
38
75
18
0 <---

per cui sommo il 4 e procedo verso l'alto.
80+85+124+213+108+202+79+82+74+18+52+57+94+99+121+115+38+75+18+4=1738

arriverò a scegliere alla fine i seguenti numeri:

62
52
23
25
31
46
100
65
4
80
85
124
213
108
202
79
82
74
18
52
57
94
99
121
115
38
75
18
che sommati insieme danno un totale 2142 minore di 2184. questo perchè mi sono fermato qui:

112 <---
62
52
23
25
31
46
100
65
4
80
85
124
213
108
202
79
82
74
18
52
57
94
99
121
115
38
75
18
0 <---
se aggiungevo il 112 perchè più grande dello zero avrei avuto una somma pari a 2254 e questo sarebbe stato più grande di 2184 e il risultato era sbagliato.

. spero tu mi possa aiutare.
di Mister_x (utente non iscritto) data: 24/01/2014 11:03:45
ciao

ho allegato un file con una possibile soluzione con due colonne di appoggio
prova a vedere se la cosa e' valida come riscontro ai dati, altrimenti penso che bisogna adottare una sub() per fare questo lavoro,e qui la cosa e' come stiamo a VBA ?????

ciao Mister_x
di cilosrip (utente non iscritto) data: 24/01/2014 13:40:00
si credo ci voglia una sub (). io diciamo che me la cavicchio ma ho bisogno di una mano, da solo di certo non ci riesco.
di ninai (utente non iscritto) data: 24/01/2014 13:46:49
Ciao
ti chiedo di provare il mio file (l'ho riuploato). Mi interessa sapere se, nel caso non metti numeri doppiati, funziona.
Se senza doppioni funziona, vedo se riesco ad ultimarlo con le formule, altrimenti butto la spugna.
Mi sto spremendo le meningi, dammi almeno questo riscontro.

di Mister_x (utente non iscritto) data: 24/01/2014 17:10:08
ciao

la sub() che e' uscita in base alle tue spiegazioni e questa riportata sotto e quella nel file allegato in base al tuo post con le spiegazioni
troverai anche le formule dato che ho utilizzato lo stezzo file
la sequenza e' quella che trovi in Colonna C:C

ciao Mister_x

Sub Elenca_numeri()
Dim totSC As Variant, Ngrande As Variant, totRange As Variant
Dim NrigaMax As Long, nrimin, nrimag
Dim i As Long
Dim Nriga As Long
totSC = Application.Sum(Range("A1:A70")) * 70 / 100
Ngrande = Application.Max(Range("A1:A70"))
For i = 1 To 70
 If Cells(i, "A") = Ngrande Then
   NrigaMax = i
   Exit For
 End If
Next i
nrigmin = NrigaMax - 1
nrigmag = NrigaMax + 1
totRange = Ngrande
For i = 1 To 70 / 2
  If Cells(nrigmin, "A") >= Cells(nrigmag, "A") Then
   totRange = totRange + Cells(nrigmin, "A")
    If totRange > totSC Then
     nrigmin = nrigmin + 1
       Exit For
    End If
   nrigmin = nrigmin - 1
   Else
   totRange = totRange + Cells(nrigmag, "A")
     If totRange > totSC Then
     nrigmag = nrigmag - 1
       Exit For
     End If
   nrigmag = nrigmag + 1
  End If
Next i
''''''''''''''''''''
Nriga = 1
Range("C1:C70").ClearContents
For i = nrigmin To nrigmag
  Cells(Nriga, "C") = Cells(i, "A")
  Nriga = Nriga + 1
Next i
'''''''''''''''''''
End Sub

di cilosrip (utente non iscritto) data: 24/01/2014 18:22:35
ciao è esattamente questo. GRAZIEEEEEEEEEEEEEEEEEEEEEE!!!!!!!!!!! mi togli una curiosità? hai studiato tutto vba per fare questa cosa?
di scossa (utente non iscritto) data: 24/01/2014 19:02:38
Questo non è il modo di comportarsi.

Ninai ti ha chiesto un riscontro su quanto ha fatto per cercare di aiutarti, impiegando il suo tempo per te.
Una tua risposta è d'obbligo, quanto meno per educazione.

di Mister_x (utente non iscritto) data: 24/01/2014 19:07:57
ciao

gurda il file che ho cambiato perche' nella sub() avevo lasciato inserito degli sporchi fatti per prova
la sub giusta e' questa che adesso trovi

riciao

Option Explicit

Sub Elenca_numeri()
Dim totSC As Variant, Ngrande As Variant, totRange As Variant
Dim NrigaMax As Long, nrigmin, nrigmag
Dim i As Long
Dim Nriga As Long
totSC = Application.Sum(Range("A1:A70")) * 70 / 100
Ngrande = Application.Max(Range("A1:A70"))
For i = 1 To 70
 If Cells(i, "A") = Ngrande Then
   NrigaMax = i
   Exit For
 End If
Next i
nrigmin = NrigaMax - 1
nrigmag = NrigaMax + 1
totRange = Ngrande
For i = 1 To 70
  If Cells(nrigmin, "A") >= Cells(nrigmag, "A") Then
   totRange = totRange + Cells(nrigmin, "A")
    If totRange > totSC Then
     nrigmin = nrigmin + 1
       Exit For
    End If
   nrigmin = nrigmin - 1
   Else
   totRange = totRange + Cells(nrigmag, "A")
     If totRange > totSC Then
     nrigmag = nrigmag - 1
       Exit For
     End If
   nrigmag = nrigmag + 1
  End If
Next i
''''''''''''''''''''
Nriga = 1
Range("C1:C70").ClearContents
For i = nrigmin To nrigmag
  Cells(Nriga, "C") = Cells(i, "A")
  Nriga = Nriga + 1
Next i
'''''''''''''''''''
End Sub

di ninai (utente non iscritto) data: 24/01/2014 22:00:26
Grazie scossa per il sostegno, in effetti il sentore l'avevo già avuto.......
Comunque, l'ottima soluzione di Misterx (ciao), mi lascia perplesso.
La formula, estrae una volta sopra il massimo ed una volta sotto, ma non sempre è così, se si incontra un numero basso, si estrae dall'altro lato, fino a quando non si trova un numero ancora più basso.
Almeno, così ho capito.

qualora , ad esempio, il 4 si trovasse, nella lista, più vicino al numero massimo (213) , cosa succede??

Io credo di aver risolto.
di Cilosrip (utente non iscritto) data: 25/01/2014 01:20:58
Ciao ninai xdonami ma nn avevo letto. Andavo parecchio di fretta oggi. Ho finito di lavorare poco fa. Allora domani provo il tutto e ti dico. Avevo notato che andava bene. Ma nn l'ho provato con i doppioni. Comunque mi dite cosa avete studiato x conoscere così bene formule e vba? Io mi sn ammazzato di libri su vba ma alla fine mi manca qualcosa. Forse l'esercizio. Sapreste consigliarmi un buon libro? O un videocorso? O altro?
Grazie x il sostegno a tutti.

Vuoi Approfondire?