Formula generica
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Sommario
Per contare le celle che non contengono molte stringhe diverse, puoi usare una formula piuttosto complessa basata sulla funzione MMULT. Nell'esempio mostrato, la formula in F5 è:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
dove "dati" è l'intervallo denominato B5: B14 e "escludi" è l'intervallo denominato D5: D7.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO
Prefazione
Questa formula è complicata dal requisito "contiene". Se hai solo bisogno di una formula per contare le celle che non * sono uguali * a molte cose, puoi usare una formula più semplice basata sulla funzione CONFRONTA. Inoltre, se hai un numero limitato di stringhe da escludere, puoi utilizzare la funzione CONTA.SE in questo modo:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Tuttavia, con questo approccio, è necessario immettere una nuova coppia di argomenti intervallo / criteri per ciascuna stringa da escludere. Al contrario, la formula spiegata di seguito può gestire un numero elevato di stringhe da escludere immesse direttamente nel foglio di lavoro.
Infine, questa formula è complessa. Fammi sapere se hai una formula più semplice da proporre :)
Spiegazione
Il nucleo di questa formula è ISNUMBER e SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Qui, trasponiamo gli elementi nell'intervallo denominato "escludi", quindi forniamo il risultato a RICERCA come "trova testo", con "dati" come "all'interno del testo". La funzione RICERCA restituisce una matrice 2d di valori VERO e FALSO, 10 righe per 3 colonne, in questo modo:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Per ogni valore in "dati", abbiamo 3 risultati (uno per stringa di ricerca) che sono errori #VALORE o numeri. I numeri rappresentano la posizione di una stringa di testo trovata e gli errori rappresentano le stringhe di testo non trovate. A proposito, la funzione TRANSPOSE è necessaria per generare la matrice 10 x 3 di risultati completi.
Questo array viene inserito in ISNUMBER per ottenere valori TRUE FALSE, che convertiamo in 1 e 0 con un doppio operatore negativo (-). Il risultato è un array come questo:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
che va nella funzione MMULT come array1. Seguendo le regole della moltiplicazione di matrici, il numero di colonne in array1 deve essere uguale al numero di righe in array2. Per generare array2 , usiamo la funzione ROW in questo modo:
ROW(exclude)^0
Questo produce un array di 1s, 3 righe per 1 colonna:
(1;1;1)
che va in MMULT come array2 . Dopo la moltiplicazione dell'array, abbiamo un array dimensionato in modo da corrispondere ai dati originali:
(2;1;0;0;1;1;0;0;0;2)
In questo array, qualsiasi numero diverso da zero rappresenta un valore in cui è stata trovata almeno una delle stringhe escluse. Gli zeri indicano che non sono state trovate stringhe escluse. Per forzare tutti i valori diversi da zero a 1, usiamo maggiore di zero:
(2;1;0;0;1;1;0;0;0;2)>0
che crea ancora un altro array o valori TRUE e FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Il nostro obiettivo finale è contare solo i valori di testo in cui non sono state trovate stringhe escluse, quindi dobbiamo invertire questi valori. Lo facciamo sottraendo l'array da 1. Questo è un esempio di logica booleana. L'operazione matematica costringe automaticamente i valori TRUE e FALSE a 1 e 0, e finalmente abbiamo un array per tornare alla funzione SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
La funzione SOMMA restituisce un risultato finale di 5.
