Seleccione dinámicamente cualquier columna en una tabla de Excel
Los datos vienen en todas las formas y tamaños, y como Excel no es una base de datos, nos enfrentamos a esas formas y tamaños a diario. Una ventaja de las Tablas en Excel es que nos obligan a aplicar algunos estándares a nuestra estructura de datos, como por ejemplo:
- Sólo puede haber una fila de encabezado
- Los encabezados deben ser únicos.
Pero incluso con estos estándares, encontramos que los datos a menudo están en un formato parcialmente de presentación. Mira el ejemplo de abajo; aquí las columnas se muestran en orden temporal. Esto es lo que quiero decir con “un formato parcialmente de presentación”.
Este no es el formato ideal para que las computadoras procesen datos, pero así es como piensa nuestro cerebro, por lo que a menudo las hojas de cálculo se presentan de esta manera. Sin embargo, esto crea un problema cuando queremos brindarle al usuario la posibilidad de elegir qué columna usar en la tabla. Entonces, ¿qué podemos hacer?
Analicemos algunos ejemplos de fórmulas para seleccionar dinámicamente una columna para usar dentro de una función SUMIFS. Los tres métodos que utilizaremos son:
- INDIRECTO
- ÍNDICE / PARTIDO
- SUMAPRODUCTO
La tabla de nuestro ejemplo se llama tblSales, a la que se hará referencia en el resto de la publicación.
Selección dinámica de columnas con INDIRECTA
La función INDIRECTA se utiliza para convertir una cadena de texto en un rango, para usarla dentro de otra fórmula. Como ejemplo simple, la siguiente fórmula devolverá el valor en la celda B4.
=INDIRECTO("B4")
El "B4" en la fórmula anterior no es una referencia de celda; está entre comillas dobles, por lo que es una cadena de texto. Las Referencias Estructuradas utilizadas con las Tablas también se pueden utilizar como una cadena de texto dentro de la función INDIRECTA.
La referencia estructurada para la columna 2019-T3 de la tabla tblSales sería:
tblVentas[2019-Q3]
Usar esto dentro de la función INDIRECTA sería la siguiente:
INDIRECTO("tblVentas[2019-Q3]")
Ahora toca hacer esta dinámica. Si el texto 2019-Q3 estuviera contenido dentro de otra celda, podría insertarse en la función INDIRECTA de la siguiente manera (supongo que la celda I4 contiene el texto 2019-Q3).
INDIRECTO("tblVentas["I4"]")
Se utiliza para unir el texto con el valor en la celda I4 para crear una única cadena de texto (el término técnico para esto es concatenar).
Ahora podemos insertar la función INDIRECTA en la función SUMIFS:
=SUMIFS(INDIRECTO("tblVentas["I4"]"),tblVentas[Producto],I2)
Esto se muestra a continuación en la celda I9.
El valor en la celda I4 se puede cambiar para seleccionar cualquier columna, por lo tanto, la función SUMIFS ahora se puede cambiar a cualquier columna dinámicamente.
Antes de que te emociones demasiado; la función INDIRECTA tiene un gran problema: es una función volátil. Normalmente, Excel solo vuelve a calcular una fórmula cuando cambia alguna celda anterior. Esto no es cierto para las funciones volátiles, ellas y cualquier celda dependiente se recalculan con cada cambio. Por lo tanto, si obtiene muchas fórmulas volátiles, o si se utiliza una al principio de la cadena de cálculo, pueden ralentizar drásticamente los tiempos de cálculo.
Selección dinámica de columnas con INDEX / MATCH
INDEX/MATCH también se puede utilizar para seleccionar dinámicamente una columna de una tabla y tiene la ventaja de no ser volátil. INDEX / MATCH solo se volverá a calcular cuando cambien las celdas anteriores. Sin embargo, es más complicado de aplicar y más difícil de entender para un usuario medio.
INDEX / MATCH tiene un superpoder que la mayoría de los usuarios desconocen... puede devolver una columna o fila completa de resultados. Por lo tanto, con esta combinación de fórmulas podemos insertar la columna seleccionada dinámicamente en SUMIFS.
Función COINCIDIR
COINCIDIR devuelve la posición de un valor de búsqueda de un rango. Entonces, si buscamos 2019-T3 en la sección del encabezado de la tabla, debería devolver 5, ya que es la quinta celda dentro del encabezado.
=COINCIDIR("2019-T3",tblVentas[#Encabezados],0)
tblSales[#Headers] se refiere a la fila del encabezado de la tabla.
Función ÍNDICE
ÍNDICE se utiliza para devolver un valor (o valores) de un rango de una o dos dimensiones. Como ejemplo simple, lo siguiente devolvería la segunda fila y la quinta columna de la tabla.
=ÍNDICE(tblVentas,2,5)
Al utilizar tblSales, hacemos referencia al cuerpo de la tabla. No incluye los Encabezados ni los Totales.
Cuando se utiliza la función ÍNDICE, si al número de fila o columna se le asigna un valor de 0 o se excluye, devolverá la fila o columna completa. Por lo tanto, lo siguiente devolverá la quinta columna completa de la tabla.
ÍNDICE(tblVentas,0,5)
O la alternativa es excluir el 0; tenga en cuenta que la coma debe permanecer,
ÍNDICE(tblVentas,,5)
INDEX MATCH se unió
Unamos ahora estas dos funciones:
=ÍNDICE(tblSales,0,MATCH("2019-Q3",tblSales[#Headers],0))
La función COINCIDIR en nuestro ejemplo devolverá 5, por lo tanto, la función ÍNDICE devolverá todos los valores de la quinta columna. Podemos incluir esto dentro de la función SUMIFS para que solo los valores de la columna 2019-T3 se incluyan en el cálculo.
=SUMIFS(INDEX(tblSales,,MATCH("2019-Q3",tblSales[#Headers],0)),tblSales[Producto],I2)
Tal como hicimos con INDIRECT, podemos hacer referencia a una celda para hacerla más dinámica para un usuario.
=SUMIFS(ÍNDICE(tblVentas,,COINCIDIR(I4,tblVentas[#Encabezados],0)),tblVentas[Producto],I2)
El valor en la celda I4 determina qué columna se utiliza dentro de la función SUMIFS.
Esta es claramente una solución más complicada que INDIRECTA; sin embargo, al no ser volátil, es una mejor solución cuando se trata de grandes conjuntos de datos.
SUMPRODUCT el arma definitiva
Pero ¿qué pasa si queremos sumar varias columnas? Buena pregunta. Para ello debemos dejar atrás las comodidades de SUMIFS y dirigirnos hacia SUMPRODUCT.
SUMPRODUCT multiplica varios rangos y devuelve el resultado. Puede que la multiplicación no parezca la solución obvia a nuestro problema, pero créanme, lo es.
El siguiente ejemplo muestra la suma de los Lattes del segundo trimestre de 2019 o del tercer trimestre de 2019.
La fórmula en la celda I9 es:
=SUMAPRODUCTO((tblVentas[[2019-Q1]:[2019-Q4]])*(tblVentas[Producto]=I2)*(tblVentas[[#Headers],[2019-Q1]:[2019-Q4]]= I4)*(tblVentas[[#Encabezados],[2019-Q1]:[2019-Q4]]=I6))
Vaya, mira eso, es tan grande que necesita varias líneas. Como esta no es una publicación sobre SUMPRODUCT, solo resaltaré algunos puntos clave para ilustrar el punto.
A continuación se identifican las celdas que son mayores o iguales que el segundo trimestre de 2019 (el valor en la celda I4) y menores o iguales que el tercer trimestre de 2019 (el valor en la celda I6).
(tblSales[[#Headers],[2019-Q1]:[2019-Q4]]=I4)*(tblSales[[#Headers],[2019-Q1]:[2019-Q4]]=I6))
A continuación multiplicamos para encontrar qué elementos son Latte's.
(tblSales[Producto]=I2)*(tblSales[[#Headers],[2019-Q1]:[2019-Q4]]=I4)*(tblSales[[#Headers],[2019-Q1]:[2019- P4]]=I6)
Cada celda que cumple con todos los criterios tiene un valor de 1, mientras que las celdas restantes tienen un valor de 0. Si multiplicamos eso por los valores de la tabla, solo los 1 devolverán un valor positivo.
=SUMAPRODUCTO((tblVentas[[2019-Q1]:[2019-Q4]])*(tblVentas[Producto]=I2)*(tblVentas[[#Headers],[2019-Q1]:[2019-Q4]]= I4)*(tblVentas[[#Encabezados],[2019-Q1]:[2019-Q4]]=I6))
La suma de todos estos proporciona el resultado de 637 en la celda I9.
SUMPRODUCT es claramente una función muy flexible que se puede utilizar para sumar valores en dos dimensiones.
Conclusión
Si SUMPRODUCT es tan bueno, ¿por qué deberíamos preocuparnos por INDIRECT o INDEX? Todo se reduce a la velocidad del cálculo.
SUMPRODUCT es potente pero excepcionalmente lento, por lo tanto, si el resultado de una sola columna es suficiente, entonces INDEX/MATCH o INDIRECT será mucho más rápido. Y dado que INDIRECT es una función volátil, INDEX MATCH es la mejor opción a utilizar.
Deja una respuesta