Excel: filtre filas según la cadena inicial, lea números de filas filtradas y realice operaciones aritméticas para generar un resumen de columnas

Question

Tengo un informe de asignación de recursos y proyectos. Necesito ayuda para generar la fórmula para mostrar el resumen en la sección inferior de mi informe.

Aquí están las especificaciones de mi informe:

• Column Ade Row 2a Row 15representa el recurso (personas) asignado para cada proyecto

• Column B, Column C.... representa el porcentaje de recurso asignado en cada proyecto por mes

• Cada recurso column Ase fusiona en 2 celdas porque cada recurso puede funcionar en 1 o 2 proyectos por mes.

• El recurso que trabaja en un solo proyecto en un mes tendrá ambas celdas fusionadas. El recurso que trabaja en 2 proyectos tendrá 2 filas que representan el proyecto.

• Frente a cada proyecto hay un número que representa el porcentaje de ese recurso asignado al proyecto.

Aquí está la captura de pantalla de mi informe:

En la parte inferior del informe tengo una sección de resumen que representa el recuento de recursos asignados por proyecto. Actualmente, estos recuentos los agrego manualmente. Necesito ayuda para generar la fórmula para este resumen. Esto es lo que estoy tratando de lograr:

1. Leer cadena de celdas (A20 - A24)
2. Haga una búsqueda en la columna del mes (B2 - B15) para que las celdas comiencen con la cadena del proyecto
3. Filtra los números de las celdas anteriores (del paso 2)
4. Sume los números y divídalos 100para obtener el recuento de recursos.

Hasta ahora puedo generar esta fórmula que lee el número de la fila que se le pasa:

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

lo que me da el número 50de la celda B2que contiene "Project A 50%"texto.

Answer 1

Si tienes O365, puedes usar la FILTERfunción:

• Filtrar la lista por el contenido de A20:Ann

• Devuelve solo el último valor separado por espacios en cada cadena (el porcentaje)

• Sumar los resultados

• Usar IFERRORen caso de que el proyecto no exista

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")
  

o(dependiendo de si desea devolver cero o ""una entrada inexistente)

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

Editar:en los comentarios, el OP menciona que los proyectos pueden tener nombres similares pero ir precedidos de un carácter, y estos deben tratarse por separado. Esto requiere un criterio de filtro diferente:

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

Answer 2

Su representación de datos en las filas 2 a 15 es un desastre porque combina dos piezas de información en una celda. Básicamente, eso desactivará la usabilidad de todas las funciones numéricas de las celdas. Divida el identificador del proyecto y la cantidad de recursos en dos columnas.

Dado el identificador del proyecto en la columna B y el recurso en la columna C, simplemente establecería la celda B20 en "=sumif(b$2:b$15;"A";c$2:c$15)". Modificaría esa fórmula a "B" para el Proyecto B.

Para una entrada más rápida sin modificar la fórmula, cree una columna oculta que contenga ABCD... y deje que la cadena fija de la fórmula anterior apunte a los valores ocultos ABCD...

Answer 3

Probablemente no te guste mucho esta respuesta, pero por favor.Manten una mente abierta

Es de suponer que esta no será la última vez que sea necesario analizar esta tabla. Si reestructuras ahora, tu vida será más fácil en el futuro.

1. Las celdas fusionadas parecen una buena idea en ese momento, pero solo te causarán dolor más adelante
2. Combinar variables en una sola celda "Proyecto A 50%" siempre hará que el análisis sea más difícil, ya que ahora hay que separar esas variables para darles sentido.
3. Introducir datos tabulados de forma cruzada puede ser un poco más sencillo, pero tiene un coste. ¿Qué sucede si ahora necesita producir un informe preguntándole el recurso trimestral año tras año asignado al Proyecto A? Además, ¿qué pasará cuando llegue el próximo año? ¿Crear una nueva tabla? Entonces, ¿cómo los comparas?

No intento ser obtuso aquí, pero después de 25 años trabajando con datos en Excel y otros sistemas, quiero animar a la gente a pensar en lo que podrían necesitar hacer más adelante.

Algo como esto te servirá mejor:

Tenga en cuenta que esto tiene formato de tabla usando Ctrl+T o "Formatear como tabla" en la pestaña Inicio de la cinta.

Esta estructura tiene una serie de beneficios:

1. Ingresar datos ahora es trivialmente fácil con solo escribir en una nueva fila
2. El análisis se puede realizar en cualquier dimensión (persona, mes, proyecto) con una tabla dinámica, sin fórmulas complejas para lo que deberían ser preguntas simples.
3. Hacer gráficos ahora es trivialmente fácil
4. Puede agregar nueva información a cada fila como columnas. Quizás desee agregar el superior directo de la persona o el nombre del equipo a la fila como una BUSCAR XL de otra tabla, o quizás desee agregar notas sobre el desempeño o las vacaciones durante ese mes para esa persona.
5. Si alguna vez necesita copiar esto en otra hoja de cálculo o enviarlo a alguien como un csv o cargarlo en una tabla en una base de datos o en un marco de datos para su análisis, no tendrá que perder tiempo desintegrando y llenando las celdas vacías. antes de que puedas hacer eso

EDITAR:

Para responder a su pregunta con esta estructura de datos revisada, simplemente puede crear una tabla dinámica: