uniformare numeri in colonna basandosi su rapporto con altre

[frank10]

Su una colonna ho dei numeri che sono il risultato di altre due colonne.
Ci sono numeri molto bassi, medi (alcuni anche che si ripetono) e molto alti.
L'obiettivo è uniformare il più possibile i numeri bassi per raggiungere quelli medi. I troppo alti, tipo oltre i 2500 li ignoro.

Ho provato a usare la media di tutti i valori, ma non è preciso.


Nel foglio allegato ho messo due blocchi come due esempi.
Devo uniformare la colonna D.

I valori medi sono 1190 molto ripetuto e 1488, per cui sceglierei 1190. Ignoro il 5952.
Quelli da modificare sono i 2 bassi, 297 e 446.
Quindi verrebbe
1190/297 = 4x
1190/446 = 2,7x
Con questi rapporti il risultato finale sarà:
4x corrispondente riga della colonna B-> 4x25= 100
2,7x50 = 135
Ho provato a fare una media escludendo i troppo alti e arrotondando per eccesso: viene abbastanza bene.

Nel secondo esempio, tralascerei i 5 numeri oltre i 2500 e quindi rimarrebbero 1020 e 1176 i bassi da alzare, e i medi: 1533, 1538 e 1908.
Sceglierei un valore tipo 1538. Con la media viene 1570, poi arrotondando viene bene.

Domande:
1) C'è qualche metodo più preciso/veloce del mio che occupa parecchie celle?
2) Nella colonna E dove ho eliminato gli alti, la media verrebbe ancora meglio eliminando anche i bassi: come calcolare questa media eliminando i valori più bassi (che non sono solo il min, potrebbero essere di più di uno (qui sono 2) )?

[unlucky83]

Alla domanda 1 non riesco a rispondere perchè non capisco il senso di questi calcoli
Alla domanda due puoi sostituire la formula

Codice: Seleziona tutto

=SE(D1>2500;"";SE(D1<1000;"";D1))

[frank10]

Ciao unlucky, mi spiego:
Nella colonna B ho i grammi di una confezione di una determinata polvere.
nella colonna D quante compresse riusirei a realizzare con quella confezione con la giusta quantità di quella sostanza.
Voglio comprare la minima quantità di sostanza (grammatura B) che però mi permetta di livellare il più possibile il numero delle compresse (in D).
Tralasciando valori troppo alti che mi costringerebbero a comprare troppo.

Non va bene una soglia fissa del minimo perché ad es. nel primo caso ho i bassi sotto i 1000, ma nel secondo i bassi sono 1020 e 1176...

In effetti non so bene come identificare i bassi, perché a occhio li vedo subito, ma in formula...
es:
qui da alzare 297 e 446 fino a 1200 circa:

Codice: Seleziona tutto

1190,48
1488,1
5952,38
1190,48
446,43
1190,48
1190,48
297,62
1190,48

qui da alzare 1020 e 1176 fino a 1500 circa:

Codice: Seleziona tutto

1533,74
1908,4
1908,4
1908,4
1020,41
1176,47
3846,15
1538,46
7575,76
7575,76
3030,3
3030,3

qui da alzare 318, 490, 641 e 781 fino ai 1000-1200 circa:

1908,4
318,47
781,25
490,2
1086,96
641,03
1282,05
1262,63

qui da alzare i 2x 862 fino ai 1000-1100 circa:

Codice: Seleziona tutto

1326,26
1379,31
1034,48
862,07
862,07
1162,79

Tenendo presente che alla fine dei conteggi, quello che detta legge sarà il minimo di tutti i valori: sarà il numero massimo di compresse che riuscirò a realizzare con tutte quelle sostanze insieme, le altre avanzeranno per lavori successivi. Chiaro che più sono livellati i valori meglio è.
Il problema è anche che certe confezioni sono grosse, senza tagli piccoli, per quello mi danno valori molto grandi che quindi scarto dai conteggi.
Tra l'altro, questi sono valori "ideali", dopo mi dovrò scontrare con i valori reali delle confezioni a disposizione... e quindi magari dovrà prendere in eccesso o in difetto comunque, ma almeno mi avvicino al meglio...

Un'altra possibile soluzione, potrebbe essere all'opposto, partire dalle confezioni disponibili e vedere quale è migliore per livellare il tutto.
In ogni caso devo vedere come identificare i bassi.

[unlucky83]

E' un problema di ottimizzazione,capisco.
Io partirei dalle confezioni in commercio per poi ottenere la giusta combinazione di acquisti.
Hai aperto molte discussioni sul forum per spacchettare il problema in tanti piccoli problemini e hai fatto bene, però credo che sia giunto il momento di aggredire il problema nella sua complessità, altrimenti rischiamo di ottenere tante piccole risoluzioni che tra loro non si legano...un pò come un cane che si morde la coda.

Se ho capito bene, leggendo tra le varie discussioni, l'ottimo da raggiungere deve essere ricercato cercando la minor spesa, massima produzione, minor scarto.
Le compresse che devi preparare, sono tutte uguali oppure hai più ricette da seguire? Se hai più ricette dovresti catalogare i vari ingredienti in base alla loro quantità di utilizzo...parlando di cucina è accettabile fare scorta di acqua ma non mi lascio in giacenza un prodotto come i canditi che vengono impiegati di rado.
Inoltre, se è difficoltoso ricercare i bassi in maniera automatica (sicuramente c'è un modo ma non lo si riesce a implementare a colpo d'occhio), si potrebbe riportare in una cella, manualmente, il valore che te sai riconoscere come valore soglia e adottarlo nelle formule che filtrano i dati.
Probabilmente, se pensi di effettuare gli acquisti da più fornitori, per lo stesso taglio avrei più prezzi, quindi ottimizzare gli acquisti sulla base della spesa può essere parecchio complicato.

Io aprirei una nuova discussione con titolo "ottimizzazione acquisti".
Preparerei un allegato di esempio con i dati da manipolare, provvisto di intestazione sulle colonne per comprendere quei numeri cosa vogliono dire.

Intanto ragiono un pò sulla cosa leggendomi più nel dettaglio il tuo ultimo messaggio

[unlucky83]

Nella colonna B ho i grammi di una confezione di una determinata polvere.
nella colonna D quante compresse riusirei a realizzare con quella confezione con la giusta quantità di quella sostanza.

Ma come è possibile che con 100 grammi della stessa sostanza ottieni un numero differente di compresse? Ma che nella colonna C ci va il quantitativo che va per compresse differenti?

Schermata del 2017-11-24 11-29-05.png (6.73 KiB) Visto 2173 volte

[frank10]

No, quelle liste sono liste di sostanze diverse da mescolare per una sola compressa finale. Ogni riga di ogni blocco è una sostanza diversa. Per fare una compressa devo unire le sostanze di ogni blocco.
Ogni sostanza ha un dosaggio diverso dalle altre. Perciò con tot g di una sostanza farei tot compresse teoriche, mentre con un'altra sostanza ne faccio di più o di meno.

EDIT:
es:
sostA con 100g farei 300cps
sostB con 50g farei 1100cps
Mescolando le sostanze A + B potrei fare al massimo 300cps, perché mi manca la sostA da mescolare con il resto della B che invece avanzo.
Per cui se ordino 3x di sostA = 300g farei 900cps, così avanzo meno di sostB.
Se invece ordino 4xsostA = 400g farei 1200cps teoriche, ma avendone solo 1100 di sostB ne potrò fare solo 1100, quindi questa volta esaurirei la sostB e avanzerei un po' di A.
Alla fine è sempre il min che mi determinerà quante compresse reali faccio, però almeno avanzerò meno materiale. Che comunque non butto via... lo riuserò poi con altri ordini.

[frank10]

si potrebbe riportare in una cella, manualmente, il valore che te sai riconoscere come valore soglia e adottarlo nelle formule che filtrano i dati.

In effetti penso che farò così perché mi pare il modo più semplice.