Recuento basado en criterios en una columna filtrada (Microsoft Excel)

Marty tiene una hoja de trabajo grande que enumera a todos los empleados (pasados ​​y presentes) de su empresa. La hoja de trabajo rastrea información diversa sobre cada empleado, como nombre, dirección, departamento, sexo, estado, etc.

Marty a menudo filtra los datos para satisfacer sus necesidades. Le gustaría tener una forma de determinar el porcentaje de empleados que son hombres y el porcentaje que son mujeres, según las filas que se muestran después del filtrado. La función SUBTOTAL puede trabajar en una columna filtrada para proporcionar varios recuentos, pero no le permitirá determinar los recuentos en función del contenido («M» o «F») de la columna filtrada.

Un enfoque consiste en utilizar una tabla dinámica para determinar los porcentajes.

Las tablas dinámicas son relativamente fáciles de usar, especialmente para responder una sola pregunta como esta. Sin embargo, no son muy buenos para permitirle ver información detallada sobre sus empleados; puede ver la respuesta agregada a la pregunta hombre / mujer, pero al mismo tiempo no puede ver el detalle de esos empleados. Entonces, quiero enfocarme en usar fórmulas directas en respuesta al problema de Marty.

Crear una fórmula para obtener los porcentajes deseados es más difícil de lo que parece a primera vista. Es fácil, por ejemplo, determinar los recuentos cuando todos los registros de los empleados están visibles. Por ejemplo, podría simplemente usar algo como esto, asumiendo que el género estaba en la columna C, para determinar qué porcentaje de los registros son para empleados masculinos:

El problema es si filtra los registros por una columna diferente a la columna C. Por ejemplo, puede filtrarlo por lo que esté en la columna de estado. La fórmula anterior aún le dará un porcentaje basado en todos los registros en la columna de género, no solo aquellos que están visibles actualmente debido al filtrado.

En este punto, podría pensar que la función SUBTOTAL podría funcionar, pero Marty descubrió que no lo haría. Nuevamente, si el género está en la columna C, podría poner lo siguiente al final de la columna C:

Sin embargo, esto no funcionará para las necesidades deseadas. El problema principal es que la función SUBTOTAL le da un recuento de qué registros son visibles, pero no hay discernimiento entre si esos registros contienen «M» o «F» en la columna C. El problema secundario es que COUNTA cuenta todos los registros, no solo los que se muestran. Por lo tanto, la fórmula no le da el porcentaje de los registros mostrados que contienen «M» o «F»

pero, en cambio, un porcentaje de cuántos registros se muestran del número total de registros.

Podrías intentar usar una columna de ayuda si quisieras. Simplemente agregue una columna para representar el estado «M» o «F» de cada empleado como 1 o 0.

Esto podría hacerse con una fórmula simple, como esta:

Esta fórmula asume, nuevamente, que la columna de género es C. Suponiendo que esta fórmula se coloca en la columna X (su columna auxiliar), entonces podría usar dos funciones SUBTOTALES, de esta manera:

El primer uso de SUBTOTAL le da la cantidad de filas que contienen «M»

y el segundo uso de SUBTOTAL le da el número total de filas visibles. El resultado de la fórmula es un porcentaje de trabajadores varones cuyos registros son visibles. Puede determinar el porcentaje de trabajadoras restando el resultado masculino de 1.

Si no puede usar una columna de ayuda (o prefiere no usar una columna de ayuda), es un poco más complicado sortear las deficiencias de los enfoques discutidos anteriormente; se necesita una fórmula un poco más complicada. La siguiente es una fórmula que funcionará, pero he dividido la fórmula única en cuatro líneas para que sea un poco más fácil de explicar.

Voy a entrar en detalles sobre cómo funciona esta fórmula, así que tengan paciencia conmigo un poco, esto llevará un tiempo. Primero echemos un vistazo a esta parte de la fórmula, que abarca el final de la primera fila y la totalidad de la segunda fila:

El uso de la función OFFSET aquí se basa en 3 parámetros. El primero corresponde a un punto de partida para calcular la referencia que se devolverá (en este caso el punto de partida es C2: C9999). El segundo parámetro es el número de filas que se desplazarán desde el principio de ese rango definido en el primer parámetro. En este caso, las filas a compensar se definen restando el número de fila más bajo del rango (que siempre devolverá el valor 2) de la fila real que se está analizando. Entonces, por ejemplo, si la fila que se analiza es la fila 10, entonces restar 2 (la fila inicial) nos da un desplazamiento de 8 filas desde el comienzo del rango especificado en el primer parámetro.

El tercer parámetro está en blanco, por lo que el valor predeterminado es 0. Este es el número de columnas que se desplazarán desde la primera columna en el rango especificado en el primer parámetro. Finalmente, el cuarto parámetro es el número 1, que indica que desea que OFFSET devuelva un rango de solo 1 celda de altura.

La conclusión es que toda esta parte de la fórmula se incluye para que devuelva una referencia a una sola celda en la columna que se analiza. En aras de la explicación de esta fórmula, llamemos a lo que se devuelve «SingleCell». Conectando esto a la fórmula original, obtenemos esto:

La primera función SUBTOTAL luego devuelve el resultado CONTAR (indicado por el valor 3 que se usa para el primer parámetro) para la celda individual. El efecto es que SUBTOTAL devuelve un 0 o un 1, dependiendo de si la celda es visible o no. (Si la celda se filtra fuera de los resultados, se devuelve 0. Si no se filtra, es visible, se devuelve 1).

La siguiente parte de la fórmula se basa en las funciones ISNUMBER y SEARCH. Esta parte del código devuelve un 0 o un 1 dependiendo de si la celda contiene la letra «M» o no. Entonces, con lo que terminas es algo que se reduce a esto:

En el caso de esta única fila, SUMPRODUCT devolvería 0, lo que significa que la fila «no cuenta» en el recuento general. Dado que SUMPRODUCT es una función basada en matrices, calcula un producto basándose en la multiplicación de cada fila en el rango original, individualmente. Por lo tanto, determina un recuento de todas las filas que cumplen las dos condiciones: la fila es visible y la fila contiene la letra «M».

Esto finalmente se divide por el resultado de la función SUBTOTAL final, que es el resultado CONTAR del número de filas visibles. El resultado final es el porcentaje de cuántas filas visibles tienen el carácter «M» visible en la columna C, el resultado exacto que quería Marty.

Para obtener el porcentaje de mujeres en las filas visibles, todo lo que necesita hacer es cambiar lo que se está buscando: cambie «M» a «F» en la fórmula, y debería estar bien.

Sin embargo, hay una salvedad que debería mencionarse. La función BUSCAR no diferencia entre mayúsculas y minúsculas. Por lo tanto, si usa «Hombre» en lugar de «M» y «Mujer» en lugar de «F» en la columna de género (columna C), la búsqueda de «Hombre» en la fórmula coincidirá con cada fila porque las celdas que contienen » Female «contendrá los caracteres» male «. Por lo tanto, es mejor seguir con «M» y «F» o, si debe usar «Masculino» y «Femenino», simplemente use «Femenino» en la fórmula y calcule el porcentaje de registros masculinos como igual a 1 menos el porcentaje femenino.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios WordTips), he preparado una página especial que incluye información útil.

link: / wordribbon-WordTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13550) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.