Sommario
Per tradurre i valori di testo in numeri e sommare il risultato, è possibile utilizzare una formula INDICE e CONFRONTA e la funzione SOMMA. Nell'esempio mostrato, la formula in H5 è:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
dove "codice" è l'intervallo denominato K5: K9 e "valore" è l'intervallo denominato L5: L9.
Nota: questa è una formula di matrice e deve essere immessa con control + shift + invio.
Spiegazione
Il cuore di questa formula è una formula INDICE e CONFRONTA di base, utilizzata per tradurre i valori di testo in numeri come definito in una tabella di ricerca. Ad esempio, per tradurre "EX" nel numero corrispondente, useremmo:
=INDEX(value,MATCH("EX",code,0))
che restituirebbe 4.
La svolta in questo problema, tuttavia, è che vogliamo tradurre e sommare un intervallo di valori di testo nelle colonne da C a G in numeri. Ciò significa che dobbiamo fornire più di un valore di ricerca e che INDICE restituisca più di un risultato. L'approccio standard è una formula come questa:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Dopo l'esecuzione di MATCH, abbiamo un array con 5 elementi:
=SUM(INDEX(value,(2,2,3,2,5)))
Quindi sembra che INDICE dovrebbe restituire 5 risultati a SOMMA. Tuttavia, se lo provi, la funzione INDICE restituirà un solo risultato SOMMA. Per ottenere INDICE per restituire più risultati, dobbiamo usare un trucco piuttosto oscuro e avvolgere MATCH in N e IF in questo modo:
N(IF(1,MATCH(C5:G5,code,0)))
Ciò impone effettivamente a INDICE di fornire più di un valore alla funzione SOMMA. Dopo l'esecuzione di INDEX, abbiamo:
=SUM((3,3,2,3,-1))
E la funzione SUM restituisce la somma degli elementi nell'array, 10. Per una buona descrizione di questo comportamento, vedere questo interessante articolo sul sito Web EXCELXOR.
