Formula generica
=SUMPRODUCT(--(A1=things))>0
Sommario
Se vuoi testare una cella per vedere se è uguale a una delle diverse cose, puoi farlo con una formula che utilizza la funzione SUMPRODUCT.
Contesto
Supponiamo che tu abbia un elenco di stringhe di testo nell'intervallo B5: B11 e desideri testare ogni cella rispetto a un altro elenco di cose nell'intervallo E5: E9. In altre parole, per ogni cella in B5: B11, vuoi sapere: questa cella è uguale alle cose in E5: E9?
Potresti iniziare a creare una grande formula basata su istruzioni IF annidate, ma una formula di matrice basata su SUMPRODUCT è un approccio più semplice e pulito.
Soluzione
La soluzione è creare una formula che testerà più valori e restituirà un elenco di valori VERO / FALSO. Una volta ottenuto ciò, possiamo elaborare quell'elenco (un array, in realtà) con SUMPRODUCT.
La formula che stiamo usando è simile a questa:
=SUMPRODUCT(--(B5=things))>0
Spiegazione
La chiave è questo snippet:
--(B5=things)
che confronta semplicemente il valore in B5 con ogni valore nell'intervallo denominato "cose". Poiché stiamo confrontando B5 con un array (cioè l'intervallo denominato "cose", E5: E11) il risultato sarà un array di valori VERO / FALSO come questo:
(VERO; FALSO; FALSO; FALSO; FALSO)
Se abbiamo anche un VERO nell'array, sappiamo che B5 è uguale ad almeno una cosa nell'elenco, quindi, per forzare i valori VERO / FALSO a 1 e 0, usiamo un doppio negativo (-, chiamato anche doppio unario). Dopo questa coercizione, abbiamo questo:
(1; 0; 0; 0; 0)
Ora elaboriamo il risultato con SUMPRODUCT, che sommerà gli elementi nell'array. Se otteniamo un risultato diverso da zero, abbiamo almeno una corrispondenza, quindi usiamo> 1 per forzare un risultato finale TRUE o FALSE.
Con un elenco hardcoded
Non è necessario utilizzare un intervallo per l'elenco delle cose. Se stai cercando solo un numero limitato di cose, puoi utilizzare un elenco in formato array, chiamato costante di array. Ad esempio, se stai solo cercando i colori rosso, blu e verde, puoi utilizzare ("rosso", "blu", "verde") in questo modo:
--(B5=("red","blue","green"))
Trattare con spazi extra
Se le celle che stai testando contengono spazio extra, non corrisponderanno correttamente. Per rimuovere tutto lo spazio extra, puoi modificare la formula per utilizzare la funzione TRIM in questo modo:
=SUMPRODUCT(--(TRIM(A1)=things))>0
