#Excel – Corrispondenza esatta con MATRICE.SOMMA.PRODOTTO

Ricerche con corrispondenza esatta in Excel

Per impostazione predefinita, le ricerche standard in Excel non sono case-sensitive. Sia CERCA.VERT che INDICE/CONFRONTA semplicemente restituiscono la prima corrispondenza, ignorando le maiuscole.

Un modo diretto per aggirare questa limitazione, è quello di realizzare una formula matriciale che abbini INDICE/CONFRONTA a IDENTICO. Tuttavia, se dovete cercare solo valori numerici, anche MATR.SOMMA.PRODOTTO + IDENTICO forniscono un sistema interessante e flessibile per effettuare una ricerca tenendo conto delle differenze tra maiuscole e minuscole.

Corrispondenza esatta con MATR.SOMMA.PRODOTTO

Nell’esempio, stiamo usando la seguente formula:

=MATR.SOMMA.PRODOTTO(--(IDENTICO(E3;B3:B8));C3:C8)

Sebbene questa formula sia una formula matriciale, non necessita di essere inserita con Ctrl + Maiusc + Invio, in quanto MATR.SOMMA.PRODOTTO gestisce le matrici in modo nativo.

Come funziona questa formula

MATR.SOMMA.PRODOTTO è progettata per funzionare con le matrici, che prima moltiplica e poi addiziona.

In questo caso, ci sono due matrici con MATR.SOMMA.PRODOTTO (B3:B8 e C3:C8). Il trucco è quello di eseguire un test sui valori di colonna B, per poi convertire i risultanti valori VERO o FALSO in 1 e 0. Eseguiamo tale test con IDENTICO in questo modo:

IDENTICO(E3;B3:B8)

Che produce questa matrice:

{FALSO.FALSO.VERO.FALSO.FALSO.FALSO}

Si noti il valore VERO in 3^ posizione: è la nostra corrispondenza esatta. Poi usiamo il doppio trattino (chiamato tecnicamente “doppio unario”) per trasformare questi valori VERO / FALSO in 1 e 0. Il risultato è questa matrice:

{0.0.1.0.0.0}

A questo punto del calcolo, la formula MATR.SOMMA.PRODOTTO si presenta così:

=MATR.SOMMA.PRODOTTO({0.0.1.0.0.0};{950.939.510.837.606.718})

MATR.SOMMA.PRODOTTO quindi semplicemente moltiplica insieme gli elementi di ogni matrice per produrre una matrice finale:

{0.0.510.0.0.0}

Che MATR.SOMMA.PRODOTTO poi somma, restituendo 510.

Quindi, il senso di questa formula è che i valori FALSO vengono utilizzati per “cancellare” i valori della seconda matrice. I soli valori che “sopravvivono” sono quelli che erano VERO.

LEGGI:  #Excel - Come usare CERCA.VERT con le funzioni SOMMA o SOMMA.SE

Si noti che, poiché stiamo usando MATR.SOMMA.PRODOTTO, questa formula è dotata di un tocco unico: se ci fossero corrispondenze multiple, MATR.SOMMA.PRODOTTO restituirebbe la somma di tutte quelle corrispondenze. Questo potrebbe anche non essere quello che volete, pertanto se nell’intervallo di ricerca fossero presenti valori duplicati, considerate questo comportamento.

Ricordate, questa formula funziona solo per i valori numerici, in quanto MATR.SOMMA.PRODOTTO non gestisce valori testuali. Se volete recuperare anche del testo, utilizzate INDICE / CONFRONTA + IDENTICO.

 

LE SCHEDE TECNICHE DELLE FUNZIONI UTILIZZATE

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti