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:
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:
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:
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):
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−−DVFMTSC−−> tabla
=DESREF('Varibles−−DVFMTSC−−> obra'!$A$1;−−DVFMTSC−−>−−−DVFMTSC−−>−fila−−DVFMTSC−−> relativa−−DVFMTSC−−>−−−DVFMTSC−−>−;−−DVFMTSC−−>−−−DVFMTSC−−>−columna−−DVFMTSC−−> relativa−−DVFMTSC−−>−−−DVFMTSC−−>−)
'Fila−−DVFMTSC−−> relativa
=MATCH(Ratios!$C3;'Varibles−−DVFMTSC−−> obra'!$A$2:$A$21;0)
'Columna−−DVFMTSC−−> relativa
=MATCH(Ratios!E$2;'Varibles−−DVFMTSC−−> 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)