23 Jul Sumar texto como número en Excel
Funciones utilizadas: SUMA, INDICE, N, SI, COINCIDIR
Suponiendo que disponemos de una lista con nombres y distintos valores de texto que representan notas numéricas para un rango de años entre 2016 y 2020 en el rango A2:F10, y que necesitamos obtener en el rango G2:G10 la sumatoria total de notas por nombre que representan los textos en su rango de fila, podemos aplicar una fórmula que combina las funciones SUMA, INDICE, N, SI y COINCIDIR para lograr convertir cada texto simbólico a su valor numérico y sumar los valores por fila. Para ello, debemos disponer de una lista auxiliar que nos indique por cada texto simbólico, cuál nota numérica debe representar. En el rango I1:K6 disponemos de la lista con encabezados que contiene los códigos (textos a representar), notas (a modo ilustrativo) y el puntaje (valor numérico que representa cada código), definido de la siguiente forma:
| CÓDIGO | NOTA | PUNTAJE |
|---|---|---|
| EX | Excelente | 5 |
| MB | Muy Bueno | 4 |
| B | Bueno | 3 |
| R | Regular | 2 |
| M | Malo | 1 |
La sintaxis de la fórmula que debemos colocar en G2 y arrastrar hacia abajo es la siguiente:
|
Donde K2:K6 corresponde al rango total de valores numéricos que pueden corresponder con cada texto simbólico del rango objetivo, B2:F2 corresponde al primer rango de resultados de texto para el primer nombre, e I2:I6 corresponde al rango total de textos simbólicos que pueden corresponder con los mismo valores en el rango objetivo.
Notamos que para obtener la sumatoria que representan dichos valores de texto y poder arrastrar la fórmula, debemos fijar en valores absolutos con F4 los dos rangos utilizados de la lista auxiliar (Códigos y Puntaje), y a su vez debemos aceptar dicha fórmula con CTRL+SHIFT+ENTER por ser una fórmula matricial.
La fórmula parte del uso combinado de las funciones INDICE y COINCIDIR utilizando una tabla de búsqueda para traducir valores de texto en números. Debido a que necesitamos traducir la suma de un rango de valores de texto en vez de un sólo valor de texto y que la función INDICE nos devolvería sólo el primer valor resultado al aplicar la función SUMA, aplicamos la función SUMA y la combinamos con las funciones N y SI para lograr forzar a la función INDICE a que devuelva múltiples resultados. Ésta técnica se conoce como “Desreferenciación” debido a que detiene a la función INDICE de utilizar resultados como referencias de celdas, y por ello permite devolver todos los valores de una matriz de valores.
De esta forma, logramos obtener de forma rápida y práctica la suma de distintos valores numéricos que representan distintos valores de texto para un rango definido de datos.
Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.
Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.