In questo video vedremo come evidenziare le ricerche di corrispondenze approssimative con la formattazione condizionale.
Qui abbiamo una semplice tabella di ricerca che mostra i costi dei materiali per varie altezze e larghezze. La formula in K8 utilizza le funzioni INDICE e CONFRONTA per recuperare il costo corretto in base ai valori di larghezza e altezza immessi in K6 e K7.
Notare che la ricerca si basa su una corrispondenza approssimativa. Poiché i valori sono in ordine crescente, CONFRONTA controlla i valori finché non viene raggiunto un valore maggiore, quindi torna indietro e restituisce la posizione precedente.
Creiamo una regola di formattazione condizionale per evidenziare la riga e la colonna corrispondenti.
Come sempre con una formattazione condizionale più complicata, ti consiglio di lavorare prima con formule fittizie, quindi trasferire una formula funzionante direttamente alla regola di formattazione condizionale. In questo modo, puoi utilizzare tutti gli strumenti di Excel quando esegui il debug della formula, il che ti farà risparmiare molto tempo.
Imposterò prima la formula per la larghezza. Dobbiamo restituire VERO per ogni cella nella riga 7, dove la larghezza corrispondente è 200.
Ciò significa che iniziamo la nostra formula con $ B5 = e dobbiamo bloccare la colonna.
= $ B5 =
Ora, non possiamo cercare 275 nella colonna delle larghezze, perché non è presente. Invece, abbiamo bisogno di una corrispondenza approssimativa che trovi 200, proprio come la nostra formula di ricerca.
Il modo più semplice per farlo è utilizzare la funzione CERCA. CERCA esegue automaticamente una corrispondenza approssimativa e, invece di restituire una posizione come CORRISPONDENZA, RICERCA restituisce il valore di corrispondenza effettivo. Quindi, possiamo scrivere:
$ B5 = CERCA ($ K $ 6, $ B $ 6: $ B $ 12)
Con la nostra larghezza di input per il valore di ricerca e tutte le larghezze nella tabella per il vettore dei risultati.
Se uso F9, puoi vedere il valore di RICERCA restituito.
Ora, quando inserisco la formula nella tabella, otteniamo TRUE per ogni cella nella riga di larghezza 200.
Ora dobbiamo estendere la formula in modo che corrisponda alla colonna dell'altezza. Per fare ciò, aggiungerò la funzione OR e quindi una seconda formula per abbinare l'altezza.
Inizieremo la formula allo stesso modo, ma questa volta dobbiamo bloccare la riga:
= B $ 5
Quindi usiamo di nuovo la funzione CERCA con l'altezza per il valore di ricerca e tutte le altezze nella tabella come vettore del risultato.
= OR ($ B5 = RICERCA ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = RICERCA ($ K $ 7, $ C $ 5: $ H $ 5))
Quando copio la formula nella tabella, otteniamo TRUE per ogni cella nella colonna corrispondente e ogni cella nella riga corrispondente, proprio ciò di cui abbiamo bisogno per la formattazione condizionale.
Posso semplicemente copiare esattamente la formula nella cella in alto a sinistra e creare una nuova regola.
Ora se cambio la larghezza o l'altezza, l'evidenziazione funziona come previsto.
Infine, se vuoi evidenziare solo il valore di ricerca stesso, è una semplice modifica. Basta modificare la formula e sostituire la funzione OR con la funzione AND.
= AND ($ B5 = CERCA ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = RICERCA ($ K $ 7, $ C $ 5: $ H $ 5))
Corso
Formattazione condizionaleScorciatoie correlate
Immettere gli stessi dati in più celle Ctrl + Enter ⌃ + Return Visualizza la finestra di dialogo Incolla speciale Ctrl + Alt + V ⌘ + ⌃ + V Alterna i riferimenti assoluti e relativi F4 ⌘ + T
