Assegnare intervalli di valori a classi in Excel

[b1ack5un]

Ciao a tutti.
Ho allegato un'immagine di esempio per chiarificare quello che mi serve.
E' un problema che riguarda Excel...o anche Vb...

Nella colonna A ho dei valori decimali che rappresentano il valore di inizio di un intervallo; nella colonna B ho il valore finale dell'intervallo.
Il mio obiettivo è suddividerli all'interno di classi di appartenenza del tipo "i valori fra 0 e 1,5 sono in classe 1; i valori fra 1,6 e 3 in classe due etc". Ovviamente, ogni intervallo può appartenere a più classi.
Esempio: un intervallo che va da 1 a 2 appartiene sia alla classe 1 (da 0 a 1,5) che alla classe 2 (da 1,6 a 3).

Alla fine, dovrei ottenere delle classi in cui ho un valore tanto più alto, quanti maggiori sono gli intervalli che ha contenuto.
Esempio: se la classe 1 ha compreso 15 intervalli (o frazioni di essi), alla fine la classe 1 avrà valore 15.

Credo che la soluzione sia con formule tipo CONFRONTA, oppure SE, CERCA...ma sto facendo una gran confusione... :muro:

[b1ack5un]

Volevo solo aggiungere che sto provando varie soluzioni...
Potrebbe essere d'aiuto la funzione RIGHE: potrebbe restituirmi il numero di righe corrispondenti a tutti gli intervalli (quindi slezione di un'area di valori) che cadono nella classe x. Se metto questa funzione vicino alla classe x, ho il valore che desidero sapere per la classe.
Solo...come si imposta un'espressione del genere?

[b1ack5un]

Continuo a provare...Il problema potrebbe essere visto anche in altri termini: si tratta di contare il numero di volte in cui, in un intervallo di valori, si verifica una certa situazione. In particolare, il numero di volte in cui gli intervalli di valori sono compresi in una classe.

Ho pensato di usare la formula COUNTIF, ma non riesco a farla funzionare, forse perchè per il mio obiettivo devo . Ho allegato un'immagine che spiega meglio la cosa. Perchè non va? E' la formula giusta?

C'è nessuno che può aiutarmi?

[Raffaele53]

Sto cercando di ricrealo, poi nel pomeriggio ero impehnato.

Porta pazienza

[b1ack5un]

Grazie!!
Ti mando anche un'immagine di altre formule che ho provato ma che non hanno funzionato...anche se in teoria dovrebbero dare proprio il risultato che spero.
Aspetto fiduciosa! :D

[zuper]

Grazie!!
Ti mando anche un'immagine di altre formule che ho provato ma che non hanno funzionato...anche se in teoria dovrebbero dare proprio il risultato che spero.
Aspetto fiduciosa! :D

un piccolo consiglio :D

se devi farci vedere qualcosa su cui stai lavorando...
è meglio se posti il foglio excel...o comunque i dati "copiabili"

se no così dobbiamo ricreare tutto a mano....;)

[Raffaele53]

Una domanda ...
i valori forniti sono definitivi, oppure un domani diventano qualcuno in più oppure molti di più?

Ps. Onestamente stavo provando con DB.CONTA.NUMERI/VALORI, però non ci sono riuscito

[b1ack5un]

@Raffaele53 - Diventeranno molti di più....tipo 600 classi e circa 800 intervalli di valori... è un problema?

@Zuper - hai ragione....vorrei postare il foglio excel ma anche zippato e con solo un foglio di lavoro arriva a 44kb...come faccio??

[zuper]

@Raffaele53 - Diventeranno molti di più....tipo 600 classi e circa 800 intervalli di valori... è un problema?

@Zuper - hai ragione....vorrei postare il foglio excel ma anche zippato e con solo un foglio di lavoro arriva a 44kb...come faccio??

nn postare tutto :)

basta che metti in un quote qualche riga significativa

tipo quelle che alleghi come immagine...

ma nei tag code...almeno si copia incolla facilmente ;)

[Raffaele53]

>>>>tipo 600 classi e circa 800 intervalli di valori

Io opterei per Access...., più facile da gestire nel futuro.
In Excel vanno bene dati da 1500/3000, ma se ci sono formule pesanti, il foglio rallenta a vista d'occhio. Non conosco Visual-basic

Forse avrei trovato una soluzione..... abbastanza ambigua, ma per pocchi dati dovrebbe andare.

Mi manca solo un passaggio e ho finito.
Se con la funzione CERCA

Esempio Cerca 0,234 nelle celle precompilate risponde al valore inferiore = 0 (giusto)

C'è una formula che "dica Cerca 0,639" nelle celle precompilate e risponda col valore Superiore = 1,5

In una tabella spostata, c'è un errore di base, se usi tre cifre dopo la virgola per decodificare un range. Anche nella tabella da 0 a 1,5 deve essere di tre cifre.

Da 0 a 1,500 e da 1,501 a 2

[b1ack5un]

@Zuper - posto pochi dati, e sotto le formule che ho provato e che per qualche motivo non vanno. La colonna "da sommare" contiene i valori da sommare nel caso di formule tipo "SOMMA.SE".

Inizio_interv Fine_interv Classe Parte_da Fino_a Da_sommare

0,0000836 0,0001011 1 0,00000 0,00002 1
0,0000942 0,0001027 2 0,00002 0,00003 1
0,0002269 0,0002424 3 0,00003 0,00005 1
0,0002289 0,0002510 4 0,00005 0,00007 1
0,0002606 0,0002721 5 0,00007 0,00009 1
0,0002766 0,0002935 6 0,00009 0,00010 1
0,0002889 0,0002985 7 0,00010 0,00012 1
0,0003063 0,0003225 8 0,00012 0,00014 1
0,0003276 0,0003464 9 0,00014 0,00016 1
0,0003842 0,0004033 10 0,00016 0,00017 1
0,0003902 0,0004229 11 0,00017 0,00019 1
0,0004851 0,0004964 12 0,00019 0,00021 1
0,0004888 0,0004967 14 0,00021 0,00023 1
0,0004933 0,0005870 15 0,00023 0,00024 1
0,0005139 0,0005377 16 0,00024 0,00026 1
0,0005267 0,0005334 17 0,00026 0,00028 1
0,0005603 0,0005693 18 0,00028 0,00030 1
0,0005615 0,0005669 19 0,00030 0,00031 1
0,0005622 0,0005711 20 0,00031 0,00033 1
0,0005654 0,0005755 21 0,00033 0,00035 1
0,0005853 0,0005973 22 0,00035 0,00036 1
0,0006019 0,0006073 23 0,00036 0,00038 1
0,0006052 0,0006188 24 0,00038 0,00040 1
0,0006195 0,0006419 25 0,00040 0,00042 1
0,0006332 0,0006402 26 0,00042 0,00043 1
0,0006441 0,0006575 27 0,00043 0,00045 1
0,0006584 0,0006673 28 0,00045 0,00047 1
0,0007027 0,0007091 29 0,00047 0,00049 1
0,0007139 0,0007338 30 0,00049 0,00050 1



Ed ecco le formule provate:

Prova formule con la classe 10
(ipotesi di voler calcolare gli intervalli - o frazioni di essi - contenuti nella classe 10)
=CONTA.PIÙ.SE(A3:B730;O(A3:A730>=D12;B3:B730<E12)) da come risultato 0 (sbagliato)


=SOMMA.SE(A2:B730;O(A2:A730>=D12;B2:B730<E12);F3:F602) da come risultato 0 (sbagliato)

=SOMMA(SE((A2:A730>=D12)*(B2:B730<E12);F3:F602)) da come risultato 0 (sbagliato)


@Raffaele53 - Access non l'ho mai usato...ma posso imparare, il procedimento sarà sempre più o meno intuitivo. Non ho capito però che cosa hai detto riguardo alla funzione CERCA. Cioè, non ho capito se è una domanda o una soluzione :mbe:
Grazie a tutti, intanto!

[Raffaele53]

Comincio a non capire più nulla,

Hai scritto che volevi trovare il Numero delle celle che rientravano in alcuni range prefissati. Guardando la prima immagine ho capito:
AB2 0,234 0,639 --->da 0 a 1,5
AB3 0,561 0,902 --->da 0 a 1,5
AB4 0,872 1,673 --->da 0 a 1,5
AB5 1,208 1,244 --->da 0 a 1,5
Tutti questi rientrano nel range da 0 a 1,5, risultato 4. Nonostante AB4 sfori nel range superiore (il quale dovrà essere riproposto nel range superiore e contegiato nuovamente).

Adesso hai scritto >>>>Parte_da Fino_a Da_sommare
Cosa c'è da sommare? Mi sono perso qualcosa?

Puoi spiegarmi cosa sono i valori ---Classe-----0,00000 0,00002 1
1)0,0000836.........0,0001011.............1......... 0,00000 0,00002 1

Perchè se fossero i range che intendevo io, mi sembrano sbagliati.


Forse ho capito, 1 finale e da sommare..... se usi Somma.Se
ma già dal terzo range 0,00003 0,00005 non corrispondono più.
0,0002269 0,0002424
0,00002269 0,00002424 dovrebbero essere così (giusto?)

[b1ack5un]

No, non ti sei perso niente! :) in effetti la colonna "da_sommare" è solo una colonna piena di 1 da utilizzare nel caso di formule tipo SOMMA.SE, per le quali viene sommato un numero corrispondente alla riga in cui si verifica l'ipotesi. In questo caso, mettendo una colonna piena di 1, per ogni volta che si verifica la mia ipotesi (ovvero per ogni volta che un intervallo rientra in una classe di valori), sommo gli 1 di quella colonna.

Le colonne che ho indicato sono:
- Inizio_interv e Fine_interv : indicano l'inizio e la fine degli intervalli che devono essere inseriti nelle classi.
- Classe: il numero della classe...non serve a niente, è solo un riferimento.
- Inizia_da e Fino_A : segnano i valori di inizio e fine di ogni classe.
- Da_sommare: già detto poco sopra.


Grazie ancora tantissimo per il tempo dedicato. Negli altri forum non mi ha risposto nessuno e mi affido completamente a voi! :stordita:

[zuper]

se hai office 2007 che dispone di + di 800 colonne

io metterei i valori delle classi in orizzontale sopra
di modo che ti venga una specie di scacchiera

2
4
1 3 1



così tanto per capirci

a quel punto puoi fare + facilmente i conti

tipo

=+IF($A3>C$1;IF($A3<C$2;1;0);IF($B3>C$1;IF($B3<C$2;1;0);0))

e in basso ad ogni colonna fai la somma degli 1 :D

se hai le canoniche 256 colonne...devi spezzettare in vari fogli le classi :D

sorry al momento nn ho testa di fare una macro che faccia tutto automaticamente :D

[Raffaele53]

>>>se hai office 2007 che dispone di + di 800 colonne
Anche questa non la sapevo....

X b1ack5un

Nella cella C3 metti la formula e trascinala.
.....A......B......C......D
1..................0.....1,6
2.................1,5.....2
3..0,234..0,639...
4..0,561..0,902...

Ritornando sempre alla 1° inmmagine (le altre sono poco visibili)
Ho accennato al fatto che Tu metti sballati i range ( DA - A )
Il 1° range parte da 0 a 1,5.
Il 2° range partirà da 1,501 oppure 1,6 ???
(lasciando così un vuoto da 1,5 a 1,6)
Ps. L'ultima lista poi mi sembra del tutto errata.

X zuper
I miei complimenti più sinceri.
=se($A3>C$1;se($A3<C$2;1;0);se($B3>C$1;se($B3<C$2;1;0);0))

Mi mancava la seconda parte dove bisognava eccedere nel range superiore (pertanto AB4 rientra nel 1° e 2° range).

Purtroppo devo far notare un anomalia riguardante la stringa AB8.
(aspettando una risposta da b1ack5un.... ),desidero chiedere subito scusa se ho interpretato male, perchè non è mia intenzione criticare la stringa e le persone.

Hai aggiunto una parte che sfora nel range superiore, forse bisognerebbe aggiungerne un'altra che sfori nel range inferiore.

Cerco di spiegarmi meglio con l'esempio di AB8 che non rientra nel range che va da da 2,1 a 2,5 oppure 2,001 a 2,5

Ciao e scusami

[zuper]

ehehe effettivamente anche io ho trovato qualche incongruenza tra i dati postati

e ho buttato li un'idea su come risolverla...nn era sicuramente completa ma secondo me è un buon punto di partenza (che modesto :))

x raffa

nn ho capito cosa intendi con questo:
"Hai aggiunto una parte che sfora nel range superiore, forse bisognerebbe aggiungerne un'altra che sfori nel range inferiore."
:)

[Raffaele53]

zuper, non importa...

Perchè se è come penso io, ci potrebbero essere valori che superano di 2/3 range sopra e 2/3 range sotto, pertanto la formula potrebbe essere infinita.Con Access questo quesito sono riuscito a risolverlo.

Per cortesia, mi sapresti dire se:
A1=0
B1=1,5
A2=1,501
B2=2
A3= 0,234
A4= 0,639
B3= =cerca(A3;A1:A2) risultato 0 (giusto)
B4= =cerca(A4;B1:B2) risultato #N/D
(giusto,perchè riporta il valore più piccolo che non esiste)


C'è un modo da fargli riportare il valore superiore, cioè la cella superiore B1 = 1,5

[Raffaele53]

Dato che era diventata una sfida personale, concludo la questione anche se non ci sono più risposte da parte dell'utente.

A)Usando le 800 colonne di office 2007 e creando una tabella come suggerito da zuper.

B)Inserendo due colonne con:
=CERCA(B3;A$18:A$22)
=CERCA.VERT(C3;A$18:B$22;2)
Aggiungendo in basso, oppure a fianco (importante sulle colonne A:B i range)

C) =SE($I3>=D$1;SE($I3<=D$2;$A3;"");SE($J3>=D$1;SE($J3<=D$2;$A3;SE($B3>D$1;$A3;SE($C3<D$2;"";$A3)));""))

Ps. Per questa problematica (meglio Access), provo a inserire il foglio (mai fatto).

PPs. Naturalmente se si modificano i range, bisogna fare un copia/incolla speciale\trasponi

[b1ack5un]

Ciao, non sono sparita...ero fuori per il weekend e non avevo internet :D
Ho letto tutti i vostri suggerimenti...wow, grazie mille! Adesso li provo subito, e ho Office 2007, quindi che bello...non mi farò problemi sul numero di colonne! Provo e torno.

[b1ack5un]

Finito!
Ecco la formula che ho usato:
=IF($A$2>=D2;IF($A$2<=E2;1;0);IF($B$2<=E2;IF($B$2>=D2;1;0);IF($B$2>=E2;1;0)))

In cui:
- colonna A: inizio intervallo
- colonna B: fine intervallo
- colonna D: inizio classe
- colonna E: fine classe

Effettivamente avevo scritto valori che potevano confondere circa l'inizio e la fine delle classi. Ho usato la formula di zuper e la formula (C) di Raffaele53. Grazie ad entrambi, senza di voi non ne sarei mai venuta a capo :D

[Raffaele53]

Solo per curiosità, puoi spostare un foglio come ho fatto io?

[b1ack5un]

Certo, eccolo qui.
Grazie ancora, ciao

[Raffaele53]

>>>Esempio: se la classe 1 ha compreso 15 intervalli (o frazioni di essi), alla fine la classe 1 avrà valore 15.

La difficoltà nel trovare una formula che dovesse ricercare un %valore% tra due parametri, mi ha fatto dimenticare la "Classe"

Se a te va bene così, OK come non detto.

Sempre Ti interessi, riempi le righe sotto (come l'esempio citato)

...A1......B1......C1......D1......E1.......Valore classe
...xx......xx......xx......0,00....3,00.....3
...xx......xx......xx......xx......xx.......
...xx......xx......xx......xx......xx.......

Ps. Adesso dimmi quali sono i valori fissi 1 oppure 2?
1) Da A
2) Inizio_intervallo Fine_intervallo

[b1ack5un]

Io in effetti ho bisogno di avere classi che non abbiamo solo valore 1, ma che abbiano un valore tanto più alto, quanti più intervalli comprendono. Nell'esempio che ho messo, la formula in effetti non contiene i simboli $.:doh:
Tieni presente come ho posizionato i dati nell'allegato precedente. Metterei come valori fissi quelli di (2), ovvero fisso l'intervallo, e si scorrono le classi (1). Poi si passa all'intervallo dopo, mettendo la formula nella colonna adiacente alla precedente, si fissa con $ il nuovo intervallo e si scorrono le classi verso il basso. Alla fine, si sommano tutti i risultati ottenuti per una tale classe (i rusltati saranno tutti in orizzontale e saranno 0/1).

L'unica cosa a cui non sono riuscita a venire a capo, è come posizionare i dati per evitare di dover rifare a mano $ ogni volta. Quando scorro verso il basso la prima volta, infatti, tutto ok. Ma per passare alla cella adiacente devo modificare i valori di $ e, sempre nell'ottica di avere circa 600 classi, è allucinante. :eek:
Si potrebbe, dopo ave rfatto il primo passaggio verso il basso con l'intervallo bloccato, modificare la funzione e bloccare la classe, scorrere tutto in orizzontale fino a coprire il numero di classi...poi però mi ritrovo da capo con lo stesso pproblema, perchè se prendo ognuna di queste celle e la scorroverso il basso,m devo prima modificare i $ di ciascuna!! :muro:

[Raffaele53]

Scusa la franchezza, NON riesco più a seguirti (oppure sono tonto io).

Con il foglio spostato da me, (cambiando solo in D3 (le parti relative a $A3 in 1 e Trascinandola/copiando la formula era già OK), aggiungevi una riga che sommava quante volte rientravano nei range ed era tutto fatto.
Il fatto che fosse fatto a Tabella/scacchiera come anche suggerito da zuper, pensavo ti andasse bene.

Nel'ultimo post, ho chiesto di compilare solo tre righe per fare un'esempio finale. Non l'hai fatto e perciò (ancora adesso) mi devo basare solo su quello che vedo (che fra l'altro ci sono sempre valori errati, oppure non li capisco io?).

Nel tuo foglio,
A1 B1 C1 D1 E1 F1
1 1,00 2,00 0,00 3,00 1 - OK il valore B e C rientra in D e E
2 2,01 3,00 1,00 2,00 1 - Mi spieghi come fa a rientrare
3 3,01 4,00 3,00 5,00 0 - OK il valore B e C rientra in D e E
4 4,01 5,00 3,00 7,00 0 - OK il valore B e C rientra in D e E
5 5,01 6,00 4,00 5,00 0 - Mi spieghi come fa a rientrare
6 6,01 7,00 2,00 6,00 0 - Mi spieghi come fa a rientrare
7 7,01 8,00 1,00 5,00 0 - Mi spieghi come fa a rientrare

In base alle tue esigenze, la formula e ormai fatta.

Ps, poi si dovrebbe essere aggiungere una riga
A1 B1 C1 D1 E1 F1
x 0.00 1,00 x x
1 1,01 2,00 0,00 3,00 1

[b1ack5un]

Ciao,
il foglio che ho postato non è sbagliato... Se guardi le formule, viene mantenuto fermo l'intervallo e la classe scorre, quindi i valori finali delle classi, almeno relativamente all'intervallo selezionato, tornano. Cioè, la formula risponde in particolare alla domanda "questo intervallo, fa parte o no di questa classe selezionata?".

Il discorso della scacchiera mi tornerebbe molto comodo, ma non ho assolutamente capito come vanno messi i dati...e qui probabilmente è perchè non capisco bene io :) Voi spesso parlate di "colonna A" oppure "colonna D", senza dire che cosa c'è nella tale colonna, quindi non so a cosa vi riferite...se al primo foglio postato, al mio, al vostro...però non ho voluto insistere perchè eravate già stati più che disponibili. :D
Magari se mi rispondi a qualcosa in cui ci sono riferimenti precisi a righe o colonne, potresti postarmi il foglio?

Non ho capito perchè dici che ancora adesso non ho postato tre righe di esempio. Il foglio che ho messo in allegato nell'ultimo post non va bene? :mbe:

grazie!

[Raffaele53]

Mi dispiace, continuo a NON capire i tuoi dati, pertanto non posso essere d'aiuto

[zuper]

Ciao,
il foglio che ho postato non è sbagliato... Se guardi le formule, viene mantenuto fermo l'intervallo e la classe scorre, quindi i valori finali delle classi, almeno relativamente all'intervallo selezionato, tornano. Cioè, la formula risponde in particolare alla domanda "questo intervallo, fa parte o no di questa classe selezionata?".

Il discorso della scacchiera mi tornerebbe molto comodo, ma non ho assolutamente capito come vanno messi i dati...e qui probabilmente è perchè non capisco bene io :) Voi spesso parlate di "colonna A" oppure "colonna D", senza dire che cosa c'è nella tale colonna, quindi non so a cosa vi riferite...se al primo foglio postato, al mio, al vostro...però non ho voluto insistere perchè eravate già stati più che disponibili. :D
Magari se mi rispondi a qualcosa in cui ci sono riferimenti precisi a righe o colonne, potresti postarmi il foglio?

Non ho capito perchè dici che ancora adesso non ho postato tre righe di esempio. Il foglio che ho messo in allegato nell'ultimo post non va bene? :mbe:

grazie!

la scacchiera è facile :)
nelle colonne a e b metti i dati da cercare, partendo da A2

nelle righe 1 e 2 metti in orizzontale le classi (copia, incolla speciale trasponi)
partendo da c2 ;)

[b1ack5un]

la scacchiera è facile :)
nelle colonne a e b metti i dati da cercare, partendo da A2

nelle righe 1 e 2 metti in orizzontale le classi (copia, incolla speciale trasponi)
partendo da c2 ;)

Perfetto Zuper! Risolto tutto e anche il trascinamento!
Grazie a entrambi! :D

[Raffaele53]

Sempre riferito alla 1° immagine.