Perchè cerca.vert non funziona?

[aggittoriu]

Ciao a tutti.

Forse sono fuori di testa, ma non capisco.

Link a un esempio di excel https://www.dropbox.com/s/sus9ofgzr9jnpyx/CercaVert.xlsm?dl=0

Domanda semplice semplice. In J1, la formula mi rende un risultato, se in I1 c'è un numero da 1.63 in su. Se c'è da 1.62 in giù, mi da #N/D.

Ma perchè????

[wallace&gromit]

Probabilmente cerca.vert non è lo strumento adatto per cercare nei risultati delle formule di excel, visto che le cifre rappresentate sono un'approssimazione di valori con precisione fino a 15 cifre significative.
La sottrazione progressiva di un valore che non è esattamente 0.01 dopo un po' genera un errore.
Puoi aggirare il problema arrotondando sempre il risultato, mettendo in K6:

Codice: Seleziona tutto

=ARROTONDA(K5-0.01;2)

e copiando verso il basso.
Oppure, visto che sono pur sempre dei numeri, calcoli semplicemente la differenza dal numero di partenza In J1 metti:

Codice: Seleziona tutto

=(K5-I1)*100+1

[aggittoriu]

Ciao wallace&gromit.

Però io non ho capito. Non sono numeri approssimati. Si parte da un numero fisso (non il risultato di chisà quale formula) con due cifre decimali e si scende gradualmente di 0,01. Non ci può essere nessuna approssimazione. Allora perchè mai il cerca.vert non mi dovrebbe rendere il risultato cercato?

Ho provato anche a scrivere in una cella ad esempio

Codice: Seleziona tutto

=I1=K22

e il risultato che mi da è VERO. Quindi... boh... non capisco.

[Anthony47]

Come dice W&G e' un fatto di precisione di calcoli: 1.6 digitato da tastiera non e' lo stesso 1.6 calcolato; mentre Cerca.Vert cerca l'eguaglianza assoluta...
Capisco anche la meraviglia che se la formula =I1=K22 restituisce VERO allora perche' per Cerca.Vert evidentemente risulta Falso?

Se guardiamo "dentro" il file possiamo leggere che "K21" ha il valore 1.63; mentre "K22" ha il valore 1.6199999999999999
Si noti che la precisione interna dei calcoli e' a con 17 cifre significative, mentre la visualizzazione e' a 15 cifre significative. Ora che io ricordi, sulla Luna e ritorno ci siamo andati con calcoli a 8 cifre significative; quindi con 17 probabilmente si va su Plutone e ritorno. Il rovescio della medaglia e' che A=B (come vuole Cerca.Vert con 4° param=Falso) e' un fenomeno sempre piu' raro da riscontrare.

Quando i calcoli sono basati sull'uguaglianza di piu' valori allora la cosa piu' saggia e introdurre un arrotondamento nel risultato delle formule; magari non fermandosi ai centesimi (i due decimali della formula esempio di W&G) ma arrivando a qualche cifra in piu', ad esempio le 8 sufficienti per andare e tornare dalla Luna.

Ciao a tutti

[aggittoriu]

Ciao Anthony47.

Ma mi spieghi che differenza c'è tra 1.62 digitato e il risultato della formula =K21-0.01, se in K21 il valore della cella è di 1.63?

Per me è difficile da capire e mi porta alla conclusione di non potermi più fidare della funzione cerca.vert.

[wallace&gromit]

In realtà è del semplice "-0.01" che non devi fidarti .
Io l'ho imparato quando si parlava di orari: puoi stare quasi sicuro che se leggi 08:00 in realtà per excel non sono per forza le 8 spaccate ma qualcosa che vi si avvicina.
Semplicemente tieni presente che un arrotondamento risolve spesso questo genere di problemi (essendo un tipo "terre à terre" e non pensando alla luna, per me due decimali sono quasi sempre sufficienti).

[aggittoriu]

Ma se io arrotondo sempre, va a finire che le formule mi si incasinano troppo. E poi la mia mente neanche ci arriva a pensare che se sto scrivendo 0.01, devo arrotondarlo, perchè in realtà potrebbe essere un altro numero leggermente diverso. Sto immettendo un dato preciso. Perchè dovrei dubitare che sia preciso?
Insomma... non posso fidarmi neanche di una calcolatrice? Se è così... altro che andare sulla Luna!

[Anthony47]

Ma i calcolatori non fanno le operazioni in colonna su un foglio di quaderno... Prima devono (1) rappresentare in binario i tuoi numeri decimali, poi (2) fanno le operazioni, poi (3) riconvertono per la visualizzazione in decimale.
L'approssimazione si fa principalmente in fase (1), perche' i numeri decimali non hanno quasi mai una corrispondenza esatta in binario.
Questo, insieme all'organizzazione interna dei programmi, porta poi a situazioni assurde, come la tua; o come a questa:
la formula =(0,7-0,6-0,1) a me restituisce un piccolo quid con coefficiente 10 elevato alla -17 (-2,77555756 E-17)
Ma =0,7-0,6-0,1 restituisce un risultato piu' facile da comprendere...

Perche'? Evidentemente nel primo caso la presenza delle parentesi forza il risultato a calcolarsi come variabile transitoria, in una struttura interna di excel, che ha la sua precisione; nel secondo caso i calcoli vengono fatti "in linea" e gli errori si accumulano di meno.

Comunque ricordiamo sempre che excel nelle sue specifiche indica la precisione di calcolo a 15 cifre significative; un errore di 10E-17 e' almeno 10 volte inferiore al dichiarato.

Per informazioni sull'organizzazione dei dati e qualche curiosita' in proposito, prova a leggere:
https://www.microsoft.com/en-us/microso ... g-answers/
https://docs.microsoft.com/en-us/office ... ate-result
https://en.wikipedia.org/wiki/Numeric_p ... soft_Excel

Come detto nel precedente messaggio, se la precisione dei calcoli aumenta, aumenta anche il rischio che le operazioni basate sull'uguaglianza esatta falliscano.

Arrotonda basta sia presente sull'ultima posizione di calcoli dove lavori sull'uguaglianza assoluta.

Con i dati posizionati in ordine decrescente potresti anche considerare di usare Indice+Confronta, invece che il Cerca.Vert; es:

Codice: Seleziona tutto

=INDICE(L5:L59;CONFRONTA(I1;K5:K59;-1))

Il parametro "-1" fornisce dati "approssimati" ma accetta solo dati decrescenti; il parametro 1 accetta solo dati crescenti (come fa gia' il parametro 1, o VERO, del Cerca.Vert)

Ciao

[aggittoriu]

Ok, dai. La chiudo qui, perchè tanto non lo capisco. Ma se ci sono queste magagne in excel, discuterne all'infinito non porterebbe a niente.

Ma così mi è crollato un mito.

Grazie comunque a tutti e due!

[wallace&gromit]

c'è comunque ancora un'altra soluzione alternativa: se invece di inserire il calcolo dei valori scrivi in K5 1.79, in K6 1.78, selezioni entrambe le celle e fai clic sul quadratino in basso a destra della selezione, nelle celle inferiori verrà inserito un valore preciso che ti farà funzionare il cerca.vert

[Anthony47]

Ma così mi è crollato un mito.

Prima dovresti trovare un foglio elettronico con precisione superiore a 15 cifre...

Il processo indicato da W&G e' quello di creare una "serie", che excel memorizzera' come se fosse stata digitata da tastiera:
-scrivi i primi 2 valori (da cui si puo' dedurre la regola della serie da creare); selezione ambedue le celle; col mouse punti l'angolo in basso a destra della selezione, e il mouse dovrebbe a questo punto visualizzarsi come una croce (*); trascini verso il basso per quanto vuoi fare lunga la tua serie.
(*)Se il mouse non cambia forma, controlla in Menu /File /Opzioni /Impostazioni avanzate; gruppo Opzioni di modifica, controlla che sia spuntato il box "Attiva quadratino di riempimento e trascinamento celle"

Anche se credo che quanto presentato dall'Utente sia solo un ambiente dimostrativo

Ciao a tutti

[aggittoriu]

Ho visto solo ora le vostre risposte.

Vorrei che fosse chiaro, che il mio problema non è risolvere questo singolo caso. Perchè questo lo risolvo facilmente.

Il mio vero problema è che ora, tutte le volte che userò la funzione cerca.vert, non saprò se fidarmi del risultato trovato o no.

Questo è.

[Anthony47]

Non disperare, la situazione e' meno incasinata di come dici..
Se Cerca.Vert ti restituisce un risultato, il risultato e' giusto
Se invece ti restituisce #N/D e la colonna di ricerca contiene valori numerici calcolati, e la ricerca e' su valori esatti, allora potrebbe trattarsi di un problema di approssimazione che fa fallire la ricerca. A spanne direi che se su dati calcolati sai che poi farai delle ricerche di valore esatto allora sarebbe meglio se i calcoli includano Arrotonda.
Abbiamo parlato di calcoli numerici, ma gli stessi problemi affliggono il calcolo Orario.

Ciao

[aggittoriu]

Eh... si. Se io so già in partenza che quel dato ci deve per forza essere, posso aggirare il problema, ricordandomi di arrotondare.

Ma se io non so in partenza che quel dato ci deve essere... vedo come risultato #N/D e penso che quel dato non ci sia (quando invece c'è).

Anzi... peggio ancora.

Io non visualizzo mai come risultato #N/D. Perchè è brutto e mi incasina le formule. Nelle mie formule prevedo sempre un se il risultato mi da val.errore, allora restituiscimi un risultato prestabilito (di solito ""). Quindi neanche me ne accorgo che lì excel mi sbaglia i calcoli.

[Anthony47]

Ma Excel non sbaglia i calcoli; li fa con una approssimazione di 15 cifre, che e' notevole (anzi, guardando come i dati vengono memorizzati, la precisione e' a 19 cifre).
Sapendo che c'e' questa approssimazione, e intuendo che la precisione piu' e' elevata e piu' riduce le chances di risultati "esattamente uguali", se su quei dati numerici che stai elaborando sai che ci fai elaborazioni basate sul concetto di "esattamente uguali" allora in modo preventivo userai la funzione Arrotonda.
Ricorda che se i dati sono in ordine crescente allora puoi usare proficuamente Cerca.Vert col terzo parametro pari a "Vero"; o se sono in ordine crescente o anche decrescente allora puoi usare CONFRONTA col terzo parametro pari a -1 /0 /1. Tra l'altro, su lunghi elenchi la situazione di dati crescenti /decrescenti riduce drasticamente i tempi di calcolo.
Quanto a nascondere gli errori, e' una pratica che andrebbe evitata; o meglio: bisognerebbe separare la fase dei calcoli, dove gli errori dovrebbero essere belli in evidenza, dalla fase di presentazione dei risultati, dove colori e formattazioni accattivanti possono farla da padroni.

Ciao

[aggittoriu]

Beh... se io dico ad excel di trovarmi il numero 1.62 e restituirmi il valore accando e quello mi dice che 1.62 non c'è, mi sbaglia i calcoli.

E 1.62 per me sono due cifre decimali, non 15. Non mi deve approssimare niente. Se poi lui capisce 1.619999999999999 anzichè 1.62, per me ha qualche problema ( e di conseguenza lo crea a me ).

Per quanto riguarda il nascondere gli errori, quasi sempre non ne posso fare a meno. Se non lo facessi, poi non potrei fare neanche una seplice somma. Mi sballerebbe tutto. Non è solo un fatto estetico. Ma anche funzionale.

[Anthony47]

Premetto che non sono pagato da Microsoft e non ho percentuali sulle vendite di licenze Excel...
Sto cercando di spiegarti che, indipendentemente dalle opinioni che ognuno si fa, conoscendo gli eventi sia possibile di conviverci felicemente.
I fatti sono che i calcoli di un "calcolatore" sono eseguiti in logica binaria, che rappresenta i decimali come 1/2, 1/4, 1/8, 1/16, 1/32, .... con la conseguenza che pochi numeri decimali hanno una rappresentazione perfetta (gli altri sono arrotondati alla 15ma posizione decimale)
Cosi' come se affacci di taglio due coltelli affilati hai poche chances di farli incontrare, allo stesso modo se confronti due numeri approssimati alla 15ma cifra hai poche chances che risulti A=B
Il trucco per convivere con questa situazione e' arrotondare, cioe' creare un bordo del coltello meno affilato.
Sono sicuro che hai compreso, ma sei ancora sotto choc per la scoperta. Con un po' di pazienza riuscirai a organizzare le tue tabelle perche' siano perfette per i tuoi scopi.

Ciao!