Excel evidenziare caselle (per veri esperti)

[Zenit]

Ho un foglio di Ecel con circa 100 celle contenenti dei valori orari.

Di fianco ho una tabella per calcolare il Tempo di Viaggio fra Ora attuale ed ora di arrivo

A1 = Ora attuale
A2 = Ora di arrivo

A3 = Tempo di viaggio

La formula in A3 è : =SE(A2>=A1;A2-A1;24-(A1-A2))
Così ottengo in A3 il valore del tempo trascorso in ore (hh:mm)

Ora sarebbe possibile evidenziare, ad esmpio in rosso, le circa 100 celle conteneti valori orari, con i 3 o 4 valori che più si avvicinano al risultato di A3 ?

O se impossibile, in alternativa avere una serie di celle in ordine incui si inseriscono automaticamente i 4/5 valori più vicini ad A3 ?

Grazie



http://www.wintricks.it/foto/firma.gif

[LoryOne]

Mi pare non esista alcuna funzione in grado di assolvere al compito richiesto.
Ne i filtri avanzati ne la formattazione condizionale sono in grado di fare cio che vuoi, quindi è necessario ricorrere a qualche riga di codice. (Chissà se Cricchia o qualcuno veramente esperto mi vorrà smentire in futuro...)

Per prima cosa aggiungi un modulo ed inserisci questo codice:


Private Type rRange
ColDa As String * 1
RigDa As Long
ColA As String * 1
RigA As Long
Column As Long
Value As Single
End Type

Public Valori() As rRange, RangeCelle As rRange, RangeCella As rRange
Public bBefore As Long, aAfter As Long, ColIndex As Long
Public Idx As Long

Private Sub Swap(ByRef x As rRange, ByRef y As rRange)
Dim temp As rRange

temp = x
x = y
y = temp
End Sub

Private Sub BubbleSort(ByRef t() As rRange, ByVal n As Long)
Dim sorted As Boolean
Dim i As Long

Do
sorted = True
For i = 0 To n - 1
If t(i).Value > t(i + 1).Value Then
Swap t(i), t(i + 1)
sorted = False
End If
Next
Loop Until sorted
End Sub

Public Sub Evid()
Dim Ty As Long, Col As Long

If Idx >= 0 Then
With RangeCelle
For Ty = 0 To .RigA - .RigDa
If Ty >= Idx - bBefore + 1 And Ty <= Idx + aAfter Then Col = ColIndex Else Col = 0
Cells(Valori(Ty).RigDa, Valori(Ty).Column).Font.ColorIndex = Col
Next
End With
End If
End Sub

Private Function IsRange(ByRef S As rRange) As Boolean
IsRange = IIf(S.ColA = S.ColDa And S.RigA - S.RigDa, True, False)
End Function

Private Function IsCell(ByRef S As rRange) As Boolean
IsCell = IIf(S.ColA = S.ColDa And S.RigA - S.RigDa = 0, True, False)
End Function

Public Function EVIDENZIA(ByVal Target As Range, _
ByVal Cell As Range, _
ByVal Before As Long, _
ByVal After As Long, _
ByVal Color As Long) As String
Dim Ty As Long

With RangeCelle
.RigDa = Target.Row
.RigA = Target.Row + Target.Rows.Count - 1
.Column = Target.Column
.ColDa = ChrW(.Column + 64)
.ColA = ChrW((.Column + Target.Columns.Count - 1) + 64)
End With
If IsRange(RangeCelle) = False Then
MsgBox "E' necessario un range di celle a colonna unica " & _
"come primo parametro !", vbExclamation + vbOKOnly
Else
With RangeCella
.RigDa = Cell.Row
.RigA = Cell.Row + Cell.Rows.Count - 1
.Column = Cell.Column
.ColDa = ChrW(.Column + 64)
.ColA = ChrW((.Column + Cell.Columns.Count - 1) + 64)
.Value = Cells(.RigDa, .Column)
End With
If IsCell(RangeCella) = False Then
MsgBox "E' necessario specificare una cella singola " & _
"come secondo parametro !", vbExclamation + vbOKOnly
Else
With RangeCelle
ReDim Valori(0 To .RigA - .RigDa) As rRange
While Ty <= .RigA - .RigDa
Valori(Ty).Value = Cells(.RigDa + Ty, .Column)
Valori(Ty).RigDa = .RigDa + Ty
Valori(Ty).Column = .Column
Ty = Ty + 1
Wend
BubbleSort Valori, .RigA - .RigDa: Ty = 0
While Ty <= .RigA - .RigDa
If Valori(Ty).Value <= RangeCella.Value Then Idx = Ty
Ty = Ty + 1
Wend
ColIndex = Color
bBefore = Before
aAfter = After
End With
End If
End If
End Function


Poi cerca il foglio che contiene la formula del calcolo ed aggiungi Evid in Worksheet_Calculate()
A questo punto avrai aggiunto alla lista delle funzioni disponibili la funzione EVIDENZIA.
I primi due parametri sono di tipo range mentre gli altri sono numerici
Parametro 1:
Target: Indica il range di celle che contengono i vari valori temporali
Parametro 2:
Cell: Indica la cella che contiene il valore di riferimento
Parametro 3:
Before: Indica quanti valori inferiori a quello di riferimento devono essere evidenziati
Parametro 4:
After: Indica quanti valori superiori a quello di riferimento devono essere evidenziati
Parametro 5:
Color: Indica l'indice del colore dell'evidenziazione

Adesso se per esempio in una qualsiasi cella tu immetti la seguente formula =evidenzia(B6:B37;C3;2;1;3), ottieni il seguete risultato:
Evidenzia in rosso i due valori inferiori più vicini a quello di riferimento ed il primo valore superiore più vicino a quello di riferimento

Nb: Non è necessario che i valori nelle celle appartenenti a Target siano in ordine crescente/decrescente
E' invece NECESSARIO CHE Target NON SIA COMPOSTO DA RANGE MULTIPLI es: A3:A7;A9:A25

Ps: il codice fa schifo :( ma non mi viene in ente niente di meglio.

[Zenit]

Originariamente inviato da LoryOne

Per prima cosa aggiungi un modulo ed inserisci questo codice:


Prima cosa , come si aggiunge un modulo ? :rolleyes:





http://www.wintricks.it/foto/firma.gif

[Cricchia]

Originariamente inviato da Zenit


Prima cosa , come si aggiunge un modulo ? :rolleyes:





http://www.wintricks.it/foto/firma.gif

questo lo so! questo lo so! :p

menù Strumenti => Macro => Visual Basic Editor

direttamente dall'editor, poi

menù Inserisci => Modulo

:)

@LoryOne: io non sono in grado di fare una cosa così complicata in vb! (Y)
per la formattazione condizionale mi sa che hai ragione.. :(

[Cricchia]

anzi un modo ci sarebbe... un pò contorto forse... e sicuramente meno preciso :mm:

comunque... si potrebbe ipotizzare uno scarto massimo ad esempio di 20 minuti e poi con la formattazione condizionale evidenziare tutte le celle che rientrano in questi chiamiamoli tempi con più o meno lo scarto scelto.. (oddio che casino!!! :p)

Certo, così facendo non si evidenziano gli orari più vicini, ma solo quelli che rientrano negli orario "consentiti" e non si può decidere che solo le 100 celle più vicine siano evidenziate, ma è cmq un'alternativa e in più non so va bene... :confused:

un esempio:

http://img302.imageshack.us/img302/5683/provacontorta1jh.jpg

ps. la colonna B mostra quello che è scritto in A ;)

poi, se invece non ho capito niente.. scusate... sono i postumi del capodanno! :timid:

[LoryOne]

E' giusto come hai postato , solo che se i valori fossero 10 li evidenzierebbe tutti mentre Zenit vorrebbe evidenziarne solo un certo numero.
Il codicillo che ho postato da questa possibilità ma ha un grande difetto e cioè non considera ne range multipli, ne range su più colonne.
La cosa è comunque fattibile purchè non si sia in preda ai fumi dell' alcool :D

[DanPis]

Non so se ho capito bene il problema, tu in pratica hai una tabella in cui sono riportate le durate dei viaggi e non gli orari di partenza e arrivo, giusto? E vuoi che in questa tabella vengano evidenziate le durate che più si avvicinano al valore da te trovato in A3. Se il problema è questo in pratica si riconduce a trovare all'interno della tabella i 4-5 valori più vicini al valore di A3, perchè una volta trovati li puoi usare direttamente nel test nella formattazione automatica.
Per trovare i valori il metodo più semplice credo sia farlo in Visual Basic, basta fare una tabella bidimensionale con la copia dei valori della tabella originale, da questa creare una nuova tabella delle stesse dimensioni della prima in cui ogni valore sia il modulo della differenza rispetto al valore di tempo cercato, da questa prendere ricorsivamente (tante volte quanti valori si vogliono)il valore minimo (ovviamente eliminandolo di modo che non sia presente nella ricorsione seguente), per l'implementazione in formula Visual Basic però devi chiedere aiuto a LoryOne perchè non sono molto ferrato.
Tutto questo lo puoi mettere in pratica anche senza conoscere un acca di Visual Basic o di programmazione, anche se il risultato devo dire che è molto poco elegante (per usare un eufemismo).
Cercherò di spiegarmi nella maniera più chiara possibile, ti assicuro che da fare è molto semplice e si fa prima sicuramente a farlo che a scriverlo, in massimo 10 minuti hai finito.
Prima di iniziare clicca sul tasto A3 (quello dove c’è il valore di tempo desiderato) vai sul menù Inserisci->Nome->Definisci e scrivi nello spazio più in alto TempoVoluto, e dai ok (serve solo per comodità mia nelle formule per non farti scrivere cose troppo strane)
Innanzitutto devi creare una nuova tabella, che chiameremo dei moduli, esattamente grande come quella che contiene i dati , disegna i bordi così ti viene più semplice da fare il resto ( in seguito ne dovrai aggiungere anche altre, quindi ti consiglio di aggiungerle sempre a destra lasciando una colonna vuota in mezzo). Dovrei sapere per continuare dove si trova la prima cella in alto a sinistra della tabella dei tempi, io non lo so e immagino per comodità che sia in D1, quindi nella formula seguente devi sostituire D1 con la posizione vera della tabella dei tempi. Adesso vai nella prima casella in alto a sinistra della tabella dei moduli e inserisci la formula =ASS(D1 - TempoVoluto), dai invio (ricordati quello che ho appena detto per D1). Ora riclicca sulla cella, dai il comando Copia, poi seleziona tutta la tabella dei moduli e dai il comando incolla. Dovrebbero apparirti nella tabella i valori dello scarto tra il tempo di A3 e quello delle tabella dei tempi.
Da questa tabella adesso dobbiamo prendere i 4-5 valori più piccoli e abbiamo finito.
Trovare il primo è semplicissimo, ma prima devi scegliere dove salvarlo, quindi clicca su una casella vuota (ad esempio A4) dai anche a questa cella il formato hh.min, vai sul menù Inserisci->Nome->Definisci e scrivi nello spazio più in alto Minimo1, e dai ok (serve solo per comodità mia nelle formule).
Adesso nella stessa cella inserisci la formula =min(Tabella moduli), dove ovviamente non dovrai scrivere Tabella moduli ma i riferimenti della tabella, la prima cella in alto a sinistra e la più in basso a destra Esempio =min(H10:W20).
Per trovare gli altri minimi devi svolgere più volte le stesse operazioni che adesso ti elenco tante volte quanti valori vicini al tempo desiderato desideri, ovviamente in ordine 1-2-3….1-2-3….1-2-3.
Se fai le cose in ordine non ci dovrebbero essere problemi, cercherò di essere più chiaro possibile, anche qua è molto più facile da fare.
1) Crea una nuova tabella grande come le precedenti, per capirci chiameremo quella che stai appena creando tabella dei moduli2, quella che creerai la prossima volta tabella dei moduli3 ecc, Quando parlerò di tabella dei moduli precedente intendo che la tabella dei moduli è la precedente di quella dei moduli2, moduli2 è la precedente di moduli3 e così via, se te le imposti bene nel foglio una a fianco all’altra lasciando una colonna come spazio e inserendo i bordi per vederle meglio ti sarà ovvio.
2) Nella cella più in alto a sinistra della tabella appena creata inserisci la formula =se(cellaPrec<>minimo1; cellaPrec;tempoVoluto), in questa formula devi sostituire cellaPrec con il riferimento della cella più in alto a sinistra della tabella precedente (quindi la formula sarà ad esempio =se(Y1<>minimo1;Y1;tempoVoluto). Anche qui la prima volta ci sarà minimo1, la seconda minimo2….tempoVoluto rimane sempre uguale
3) Dai invio riclicca sulla cella, dai il comando copia poi riseleziona tutta la tabella e dai incolla
4) Scegli dove salvare il minimo (ad esempio il minimo 2 in A5, il minimo3 in A6…)
5) Dai alla cella scelta il formato hh.min
6) Clicca sulla cella scelta vai sul menù Inserisci->Nome->Definisci e scrivi nello spazio più in alto Minimo2 la prima volta, Minimo3 la terza (e così via), e dai ok
7) Adesso clicca nuovamente sulla cella del minimo e inserisci la formula =min(Tabella moduli) dove Tabella moduli indica la Tabella dei moduli che hai appena creato.

Abbiamo finito, rimane solo la formattazione automatica, hai due possibilità la prima è dare un colore diverso a ciascuno dei valori trovati, magari rosso al valore più vicino al colore della tabella, marrone al secondo, giallo al terzo e così via ovviamente con formati a tuo piacimento.
Per fare così devi cliccare su Formato->Formattazione condizionale e devi inserire la formula = O(D1=tempovoluto+ minimo1; D1= tempovoluto -minimo1), dai il formato che vuoi al tempo più vicino, poi clicca su Aggiungi e inserisci nella nuova mascherina
= O(D1=tempovoluto+ minimo2; D1= tempovoluto -minimo2) x il secondo,
= O(D1=tempovoluto+ minimo3; D1= tempovoluto -minimo3) x il terzo e così via per gli altri. NOTA BENE: anche qui in tutte queste formule devi sostituire D1 con il riferimento della cella più in alto a sinistra della tabella dei tempi. Per tutte le altre celle ti basta usare il copia e incolla.

Se invece vuoi dare un colore unico la formula è
O(D1=tempovoluto+ minimo1; D1= tempovoluto -minimo1; D1=tempovoluto+ minimo2; D1= tempovoluto –minimo2; D1=tempovoluto+ minimo3; D1= tempovoluto –minimo3; eccetera eccetera)

Una volta fatto ti consiglio di selezionare tutte le colonne delle tabelle dei moduli (selezionandole partendo dall’alto, dove sono scritte le lettere dei nomi delle colonne) poi clicca col destro e scegli “Nascondi colonne”

Ciao, spero di esserti stato utile, anche se sono curioso di sapere a che serve.
P.S. Per motivi di fretta non posso stare a ricontrollare per filo e per segno tutto ciò che ho scritto, quindi scusa eventuali errori di battitura, se c’è qualche problema fammelo sapere.

[Zenit]

Vi ringrazio tutti, ho già risolto usando in parte la soluzione di Cricchia, solo che invece di impostare un intervallo di tempo fisso, ho agginto una casella dove posso mettere un valore % , a mio piacimento, che mi modifica l'intervallo per lo scarto dei tempi.

Grazie ancora a tutti.


http://www.wintricks.it/foto/firma.gif