Ejercicio Resuelto – Nivel medio Excel | Empresa de construcción

Para acceder al enunciado de este ejercicio de excel puedes hacer click en el siguiente enlace (enunciado).

Resolución del ejercicio: Validación de datos

En una hoja diferente a la de los datos del enunciado dibujaremos la siguiente tabla:

Validación de datos variables

En la imagen anterior hemos copiado los indicadores en la columna B y a la derecha, en las columnas C y D hemos puesto el numerador y el denominador. Ambas columnas tienen ligada una lista de validación de datos que proviene de la hoja de datos del nombre de los indicadores:

Validación de datos variables desplegado

El desplegable de la lista nos permite seleccionar las variables que queremos con el mismo literal que encontramos en el listado de datos de empresas de construcción. De esta manera, podremos construir nuevos indicadores e indicar su Numerador y su Denominador de manera muy sencilla, eligiéndolos de un desplegable.

Funciones

Para poder hallar cada uno de los ratios para cada una de las empresas hemos construido la siguiente tabla:

Ratios

El objetivo de esta tabla es que sea fácilmente modificable. Cada fórmula apunta tanto a las celdas de:

  • Numerador
  • Denominador
  • Nombre de empresa

Recordad que partimos de una tabla de datos como esta (que no podremos modificar):

Datos

El objetivo será encontrar a partir de la variable de obra y del nombre de la empresa tanto el numerador como el denominador.

Mediante la función DESREF podemos conocer el valor de una celda dentro de una tabla dadas sus coordenadas de:

  • “Comienzo de tabla”
  • “Fila relativa al comienzo de tabla”
  • “Columna relativa al comienzo de la tabla”

Hagamos un ejemplo con empresa: FCD y variable Directivos España. Esto es el numerador del primer indicador.

  • Comienzo de tabla: será la celda A1 de la hoja de datos (en este ejemplo se llama Ratios).
  • Fila relativa al comienzo de la tabla: la fila de los Directivos de España en la columna de Variables de Obra es la 2 si empezamos a contar desde cero, pero si lo hacemos relativo a la celda A1 será la fila 1, es decir, nuestra lista de variables de obra empieza a contar desde la fila 2. Este valor lo podemos conocer a través de la función COINCIDIR.
  • Columna relativa al comienzo de la tabla: igual que el caso de la fila pero con los nombres de las empresas.

La fórmula usada para el ejemplo es la siguiente:

'Comienzo tabla
=DESREF('Varibles obra'!$A$1;−−fila relativa−−;−−columna relativa−−)
'Fila relativa
=MATCH(Ratios!$C3;'Varibles obra'!$A$2:$A$21;0)

'Columna relativa
=MATCH(Ratios!E$2;'Varibles obra'!$B$1:$Y$1;0))

En Fila relativa tendremos las siguientes variables:

  • Ratios!C3 hace referencia al numerador. Está fijado en su valor columna para al arrastrar fijar siempre en la columna Numerador
  • Variables Obra!A2:A21 hace referencia a la lista de Ratios
  • 0 hace referencia al tipo de búsqueda, en este caso, exacta.

En columna relativa tendremos las siguientes variables:

  • Ratios!E2 hace referencia al nombre de la empresa a la que estamos apuntando. Está fijado en su valor fila para que al arrastrar siempre apunte al nombre de las empresas.
  • Variables Obra!B1:Y1 hace referencia a la lista de empresas cuya información está disponible.
  • 0 hace referencia al tipo de búsqueda, en este caso, exacta.

De la misma manera haríamos con el denominador para finalmente obtener la fórmula que quisiéramos. En el fondo es:

DESREF( numerador ) / DESREF ( denominador )

Podéis encontrar este ejercicio de excel resuelto en el siguiente link: Ejercicio – Variables empresas construcción (Resuelto)

Quique es un fanático del Excel desde que empezó a trabajar con ello hace unos pocos años. Adquirió su conocimiento a través de Google y de infinitas pruebas y más pruebas. Además, Quique es un entusiasta de su familia (mujer e hijos), del deporte y de la cerveza… no dudes en contactar con él para cualquier duda que tengas.

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información ACEPTAR

Aviso de cookies
Shares
Share This