PDA

Visualizza versione completa : Applicativi Ordinamento celle MS Excel



dactylium
15-03-10, 10: 19
Ciao a tutti.

Vorrei chiedere (se possibile in questa sezione) un'informazione relativa all'ordinamento di valori contenuti nelle celle di Excel.

Faccio un esempio concreto, nella speranza di essere il più chiaro possibile.

Poniamo che in un foglio di Excel abbia una serie di valori numerici disposti in colonna.
Attraverso il filtro automatico posso ordinare questi valori ad esempio in ordine crescente o decrescente.

In un altro foglio di Excel ho invece una cella il cui valore fa riferimento a una cella del primo foglio (ad esempio nella cella B2 del secondo foglio, nella barra della formula, trovo scritto =Foglio!1A1). Questo significa cioè che il valore della cella B2 del secondo foglio dipende dal valore della cella A1 del primo foglio.

E qui scatta il problema.
Nel momento in cui vado a ordinare in modo diverso i valori della colonna del primo foglio (ad esempio da crescente a decrescente), il valore di cella del secondo foglio cambierà di conseguenza.

Ad esempio se, con ordinamento crescente, nella cella A1 del primo foglio ho il valore 3, in B2 del secondo foglio trovo il valore 3.
Se ordino in modo diverso (ad esempio decrescente), il valore della cella A1 del primo foglio cambia (poniamo che per ipotesi diventi 5), e, come conseguenza, cambia anche il valore della cella B2 del secondo foglio.

Tutto ciò accade a prescindere che si usino nella barra della formula riferimenti relativi o assoluti.

La mia domanda è: è possibile legare la cella del secondo foglio al valore della cella del primo in modo che non cambi applicando un ordinamento diverso alla colonna di valori del primo foglio?

Spero tanto possiate aiutarmi.
Grazie, dacty

Clairvoyant
15-03-10, 22: 33
Ciao dactylium.

Ho chiaro il tuo problema, ma non lo scopo del foglio.

Quei numeri sono una semplice serie numerica senza alcun riferimento, o nella colonna a fianco ci sono altre voci che li identificano e quindi sarebbero quantità?

:bai

dactylium
16-03-10, 12: 09
Ciao Clair, grazie della risposta.

I valori contenuti nel primo foglio sono delle quantità.
Il primo foglio contiene una serie di record (diciamo pure righe) con varie etichette che si riferiscono a titoli di investimento.
Ciascuna riga è riferita a un titolo diverso, del quale sono espressi differenti valori nelle varie celle attigue, ad es. quantità investita, scadenze, interessi, tasse da pagare, ecc.
A ciascuna colonna è invece assegnata un'etichetta di cui sopra (ad es. quantità investita, scadenze, interessi, tasse da pagare, ecc.) che ovviamente si riferisce a record diversi, quindi a titoli diversi.
Il primo foglio ha quindi funzione riepilogativa e di database.

Il secondo foglio è invece quello che contiene le formule per calcolare i rendimenti.
Ciascuna formula ovviamente fa riferimento a celle del primo foglio con il relativo valore.

Come già hai capito, quando vado ad eseguire un ordinamento diverso dei miei dati del primo foglio attraverso la linguetta del filtro automatico (ad esempio desidero ordinare i titoli per scadenza piuttosto che per quantità investita o altro), nel secondo foglio Excel non aggiorna i riferimenti di cella nelle formule.
Per lui, ad es, la cella B3 del primo foglio resta, nella formula che la coinvolge, sempre la cella B3.
Solo che, con l'esecuzione di un ordinamento differente dei dati, il valore contenuto nella cella B3 è cambiato, perché si riferisce ad un altro record.

Questo non accade eseguendo invece un "ordinamento manuale", cioè spostando i singoli record nella posizione desiderata (o anche ad es. inserendo nuove righe, eliminandole altre), perché in tal caso Excel aggiorna i riferimenti di cella nel secondo foglio.
Se ad esempio inverto a mano la posizione di due righe, Excel capisce che quella che in precedenza era la cella B3 ora deve essere la cella B2 e aggiorna il riferimento nella formula.

Non saprei però come ovviare al problema eseguendo un ordinamento diverso con filtro automatico.

Grazie, ciao.

Clairvoyant
16-03-10, 22: 43
Ciao dactylium.

Ti ho fatto la domanda perchè mi sembrava mancasse qualcosa. :P

Credo che il cerca.vert faccia al caso tuo.

Ti allego un file di esempio, poi prova a vedere nella guida specifica della funzione e dovresti trovare quanto ti serve.:eye


:bai

dactylium
17-03-10, 17: 48
Ciao Clair.

Innanzi tutto ti ringrazio sentitamente, perché il tuo suggerimento è stato perfetto!
In effetti continuavo a girare attorno alla funzione cerca e relative varianti verticale e orizzontale senza capire se facevano al mio caso, e soprattutto senza approdare a nulla.
Va be', chi è vittima della propria ignoranza, pianga se stesso... :ehmm

Mi permetto di chiederti un'ulteriore informazione, se hai tempo e voglia.
In caso contrario, sono già più che soddisfatto così.

La funzione cerca verticale è perfetta fino al momento in cui non si aggiorna il primo foglio (db) con altri record di dati.
In questo caso Excel si comporta in modo diverso a seconda di come vengono inseriti i nuovi dati:

1) se inserisco una riga vuota in mezzo a quelle già compilate del primo foglio, la compilo con nuovi valori, ordino i dati secondo le mie preferenze (incluso quindi il nuovo record) e poi verifico i risultati delle formule nel secondo foglio, tutto è ok.
Excel ha cioè aggiornato la matrice_tabella della formula includendo il nuovo record (con riferimento al file allegato, se prima la matrice_tabella era A2:E6 ora Excel l'ha aggiornata a A2:E7)

2) se invece aggiungo una nuova riga di dati secondo quella che, dal mio punto di vista, sarebbe la linea più logica, cioè inizio a compilare il mio nuovo record partendo dalla prima cella vuota della prima colonna, Excel non aggiorna la matrice_tabella delle formule.

Quindi, operando nel seguente modo (con riferimento al file allegato):
- inserisco un nuovo record nel primo foglio partendo dalla prima cella disponibile a sinistra (A7)
- riordino i dati secondo le mie preferenze (incluso il nuovo record)
- verifico i risultati della formule nel secondo foglio e ottengo degli errori perché per Excel la matrice_tabella è sempre A2:E6, mentre, per restituire dei risultati corretti, dovrebbe diventare A2:E7 (questo accade a prescindere che nelle formule si usino riferimenti assoluti o relativi)

Lo stesso risultato del punto 2 si ottiene anche quando si inserisce un nuovo record di valori (come nel punto 1) esattamente sotto la riga delle etichette. In tal caso per Excel la matrice_tabella (che per essere corretta dovrebbe essere A2:E7) diventa A3:E7.

Che tu sappia, esiste un modo per ovviare all'inconveniente?
O magari uno che permetta a Excel di aggiornare le formule in modo corretto e automaticamente?

Le alternative sarebbero l'inserimento di nuovo record a partire dall'inserimento di righe all'interno della tabella del foglio db (come al punto 1) o l'aggiornamento manuale delle formule.

Grazie ancora per l'aiuto.
Ciao, dacty

- EDIT -

OK Clair, puoi dimenticare tutto quanto scritto sopra, a parte i ringraziamenti, ovviamente; quelli sono doverosi e meritati.

Si può risolvere con una combinazione di Scarto e Confronta.
Per chi fosse interessato qui (http://office.microsoft.com/it-it/excel/HP052289521040.aspx) si trova la relativa spiegazione (paragrafo "Cercare valori in un intervallo di dimensione non definita e non ordinato").

In realtà non so se ricorrerò a questo sistema perché le formule si complicano parecchio e temo che tra qualche tempo potrei non ricordarmi nemmeno come sono giunto a un simile risultato.
Forse per me ci vuole ancora la carta e la matita...

Grazie di nuovo per il tempo che mi hai dedicato.
Ciao!

Clairvoyant
18-03-10, 00: 42
Ciao dacty.

Non ti trovava il valore perchè lavorando con riferimenti assoluti alla matrice, dati dal prefisso $ per i riferimenti RC, la formula funzionava solo sulla matrice impostata.

Con l' inserimeno di righe nel foglio db tra le altre già comprese nella tabella si aggiorna automaticamente la matrice, in quanto operi sulla stessa, mentre se aggiungi i dati sotto come hai fatto la formula non li considera in quanto sei fuori matrice.

Nell' allegato trovi la versione a matrice ampliata fino a 100, se confronti i due file vedi la differenza, e sei a posto fino a 99 inserimenti; per il foglio report basta trascinare la formula o copiaincollarla nelle righe sotto a quelle presenti ed inserire il relativo titolo sul quale vuoi che sia effettuato il calcolo.
Se poi non bastano o son troppi basta modificare i riferimenti.:eye

Si può risolvere con una combinazione di Scarto e Confronta.
Per chi fosse interessato qui (http://office.microsoft.com/it-it/excel/HP052289521040.aspx) si trova la relativa spiegazione (paragrafo "Cercare valori in un intervallo di dimensione non definita e non ordinato").

In realtà non so se ricorrerò a questo sistema perché le formule si complicano parecchio e temo che tra qualche tempo potrei non ricordarmi nemmeno come sono giunto a un simile risultato.
Ad ogni problema c'è sempre più di una soluzione, ma perchè andarsi a cercare quella più difficile? :P
Quando crei un foglio excel ( con i db di Access è anche peggio :ghgh ) è sempre bene ricercare la soluzione più scalabile e manutentabile possibile, magari perdendo più tempo all' inizio per capire come impostarlo, non si sa mai come possono svilupparsi.:eye
Forse per me ci vuole ancora la carta e la matita...
Ma va là, io excel l'ho imparato da autodidatta anni fa seguendo la guida in linea e sperimentando.:bgg2
Poi è vero che usandolo in ambito lavorativo e trovandomi di fronte a problematiche non proprio comuni diciamo che ho avuto occasione ( e necessità :ghgh ) di sperimentare assai ed ormai ci faccio quasi di tutto, ma per uso domestico o similare vedrai che non è complicato.

Già con il Cerca.Vert ed i suoi cugini riesci a risolvere una moltitudine di problemi, poi se ti metti a cercare nelle varie formule vedrai che troverai cose interessanti.:eye

:bai

dactylium
18-03-10, 11: 02
Grazie di nuovo Clair.

Senza il tuo aiuto avrei proceduto per tentativi senza probabilmente arrivare a una soluzione.

In effetti ampliare la matrice è l'espediente più logico, anche perché consente l'inserimento di nuovi valori senza complicarsi la vita.
Peccato che non mi fosse proprio venuto in mente... :ehmm

I tuoi suggerimenti sono stati preziosi .
Grazie ancora.

Ciao!
dacty

Clairvoyant
18-03-10, 22: 34
De nada. :eye

:bai