Convertir en una BD (sin repetición)

Descargar el fichero: matricial_extrae.xlsx

En Excel podemos convertir una tabla de doble entrada en una base de datos. Los datos de la tabla de doble entrada tienen que ser sin repetición. En este caso se trata de países que no se pueden repetir.

En la Hoja1 del fichero matricial_extrae.xlsx estudiamos cómo extraer información de una tabla de doble entrada. Puede verse el siguiente post:

• Extraer valores de una tabla con fórmulas matriciales

Hoja2

En esta ocasión trabajamos en la Hoja2 con la siguiente tabla de doble entrada.

Queremos obtener un listado donde figuren en columna todos los países y junto a cada uno de ellos la fecha y el empresa que le corresponde. El resultado será el siguiente.

La fecha la hemos calculado por un único método pero la empresa se ha calculado por 8 métodos diferentes.

Calcular la fecha es más sencillo al tratarse de un valor numérico. La fórmula matricial empleada en la celda C19 es la siguiente.

{=SUMA((tabla=B19)*Dias)}

Para saber más sobre fórmulas matriciales consulte el siguiente post.

Funciones matriciales en Excel

Los nombres de rango utilizados en la Hoja2 son estos.

Expliquemos la fórmula anterior. La expresión (tabla=B19) es una igualdad que Excel evalúa y nos dice si es verdadera o falsa. Al tratarse de una matriz tendremos una matriz de valores que pueden ser verdaderos o falsos. Como estamos igualando a B19 que es Angola, nos dará la siguiente matriz.

Todos los valores dan FALSO, salvo el primero que da VERDADERO. Esto es así porque Angola ocupa justo el primer lugar de la tabla.

En informática los verdaderos son unos y los faltos son ceros, por lo que en realidad lo que tenemos es la siguiente tabla, con la que podemos operar como si se trataran de números.

Si la tabla anterior se multiplica por el rango 'Dias' que es un vector columna de 10 elementos obtenemos lo siguiente.

El valor 39083 en formato fecha corresponde al día 1-enero-2007.

Ahora sólo falta sumar los valores anteriores, y mostrar el resultado con formato fecha. Esto es lo que se puede observar en la celda C19.

Gracias a que la fecha en realidad es un número hemos podido multiplicar y luego sumar. El problema viene cuando lo que buscamos no es un número sino texto. Es el caso de las Empresas, donde debemos buscar qué empresa corresponde a cada país. Así, por ejemplo, a Croacia le corresponderá la Emp2. Para averiguar esto el método anterior no es válido y hemos tenido que recurrir a otros métodos. Concretamente planteamos 8 método para conseguir nuestro objetivo.

Veamos la fórmulas para Angola, fila 19.

Método 1

{=BUSCARV(SUMA((tabla=B19)*NumEmp);TablaEmp;2)}

Utiliza la tabla auxiliar TablaEmp que está en el rango H12:I15.

Método 2

{="Emp"&SUMA((tabla=B19)*NumEmp)}

Utiliza las celdas auxiliares C4:F4 que se han nombrado con el nombre de rango NumEmp.


Método 3

{=INDICE(empresa;1;SUMA((tabla=B19)*NumEmp))}

Utiliza el rango auxiliar NumEmp.


Método 4

{=BUSCARH(SUMA((tabla=B19)*NumEmp);$C$4:$F$5;2;0)}

Utiliza el rango auxiliar NumEmp.


Método 5

{=INDICE(empresa;1;SUMA((tabla=B19)*{1;2;3;4}))}

La expresión {1;2;3;4} está relacionada con el número de columnas. En este caso son 4 columnas, pero si estuviéramos en un caso con un número mayor o menor de columnas tendríamos que adaptar la fórmula. Por ejemplo, para 5 columnas tendríamos que escribir  {1;2;3;4;5}.


Método 6

{=DESREF($B$5;0;COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(tabla;;COLUMNA(tabla)-CELDA("columna";tabla);;1);B19));0))}


Método 7

{=INDICE(empresa;1;COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(tabla;;COLUMNA(tabla)-CELDA("columna";tabla);;1);B19));0))}


Método 8

{=INDICE(empresa;1;(SUMA((tabla=B19)*(COLUMNA(tabla))))-COLUMNA(tabla)+1)}

Los método 6, 7 y 8 no requieren tablas auxiliares de datos.

Podríamos crear un método más, convirtiendo las celdas que contienen los nombres de las empresas (rango C5:F5) en un rango de valores numéricos. Numerando las empresas del 1 al 4, y para que en lugar de verse el número en esas celdas se vea tal y como se ve ahora: Emp1, Emp2, Emp3, Emp4, podríamos dar a esas celdas un formato personalizado poniendo esto: "Emp"Estandar

Lo que veríamos después de aplicar el formato sería lo mismo que teníamos antes.

Con la ventaja de que esas celdas ahora son verdaderos números y podemos emplear una fórmula similar a la que se empleo en el caso de las Fechas. Lo cual hace el cálculo más sencillo.


¿Y si se repiten?

Y ahora la pregunta que seguro que tienes en mente. Y que pasa cuando los datos de la tabla de doble entrada se repiten. En nuestro caso, ¿qué pasa cuando existen países repetidos?

La respuesta es que algunos de estos método dan error y otros en caso de tener dos Angolas nos dan la Empresa de uno de los valores o de otro de ellos. Dicho de otra forma no son válidos estos métodos.

Pero vayamos más allá, y pensemos que esta forma de presentar la información no es válida, ya que no tendría sentido tener un listado con todos los países sin repetición. Este informe no es válido ya que cuando le toque el turno a uno de los países que figura dos o más veces en la tabla de datos, ¿qué fecha y empresa ponemos?

En estos casos se tendría que ir a otro tipo de informe donde en el listado apareciera tantas veces el país como veces se repita, y por cada fila figuraría la fecha y empresa que corresponde a esa aparición.