Consolidar datos
Una vez más vamos a ver como, en Excel, podemos realizar una misma tarea de cuatro formas distintas.
Tenemos en un Libro de Excel, nueve hojas; una por producto, con información acerca de las ventas hechas de dicho producto por mes a los distintos clientes.
En todas las hojas se muestran los mismos meses y clientes en las mismas celdas. Nuestro objetivo: en una hoja resumen mostrar el total de ventas por mes y cliente.
Si quieres puedes descargar el libro de trabajo aquí
Preparamos la hoja de resumen.
Vamos a crear una nueva hoja que se llame resumen, a partir de una de las hojas de productos. Para ello, clicamos con el botón derecho en una hoja cualquiera, y hacemos clic en Mover o copiar hoja. Marcamos la opción Crear una copia y aceptamos.
Sólo nos queda suprimir todos los datos de ventas, pues son las mismas que la de la hoja Edulcorantes. Seleccionamos dichos datos y tecla suprimir.
El método más básico.
Nos situamos en la celda C7; y escribimos =
A continuación, hacemos clic en la hoja Edulcorante y marcamos la misma celda (la C7),
Añadimos el signo + y hacemos clic en la hoja Café y seleccionamos de nuevo la celda C7, y
Así con el resto de hojas de productos, hasta conseguir está expresión:
=Edulcorantes!C7+Café!C7+Lácteos!C7+Refrescos!C7+Menaje!C7+Legumbres!C7+Pescados!C7+Carnes!C7+Frutas!C7
La formula para una empresa con 50 productos evidentemente sería mas pasado. Cuantas mas nuevos productos se agencias se agreguen, más habrá que modificar esta fórmula.
Una vez tenemos esta expresión la copiamos para todos los meses y clientes. Para ello:
Estando en la celda C7, situamos el cursor en el extremo inferior derecho de la celda, dónde se observa un pequeño cuadradito (veréis que el cursor cambia de forma), en ese momento doble clic y la fórmula se copia a lo largo de la columna para todos los clientes
Una vez tenemos la columna relativa a las ventas del mes de ENERO, completa y seleccionado, pinchamos de nuevo en ese "cuadradito", y arrastramos hacia la derecha para completar el resto de meses.
Utilizando funciones
Guarda el ejercicio anterior como Consolidado 1, y abre de nuevo el archivo original.
¡Recuerda: En todas las hojas el rango de datos es el mismo.!
- Preparamos de nuevo la hoja Resumen como vimos en el caso anterior.
- Una vez la tenemos lista, nos ponemos de nuevo en la celda C7, y utilizamos la función SUMA
- Escribimos: =SUMA(
- A continuación, marcamos la celda C7 de la primera hoja "Edulcorantes"
=suma(Edulcorantes!C7
- Mantenemos presionada la tecla Shift,
- Nos dirigimos a la ultima de las hojas, en este caso Frutas, y marcamos la celda C7. Verás que todas las hojas desde Edulcorantes hasta Frutas, han quedado seleccionadas.
- Cerramos paréntesis. La expresión nos quedará de este modo:
=SUMA(Edulcorantes:Frutas!C7)
- Copiamos esta fórmula, tal y como vimos anteriormente en el resto de celdas, y ya tenemos consolidado todos los datos.
Observaciones.
Sí añadimos nuevos productos, bastará con insertarlas entre las hojas Edulcorantes y Frutas, la fórmula se realizará automáticamente sin que sea necesario modificar las formulas.
Además, si un producto ya no existe, bastará con suprimir su hoja. el único problema surgirá si debe suprimir una de las dos hojas extremas (en este caso Edulcorantes y Sevilla).
Ante este caso lo aconsejable sería insertar antes del primer producto una hoja que quedara en blanco y la que llamaríamos pro ejemplo PRINCIPIO y una hoja tras la última agencia a la que se llamará FIN y la fórmula quedaría así:
=SUMA(PRINCIPIO:FIN!C7)
Lógicamente si hubiese una hoja que no queremos sumar, esta no tendría que estar comprendida entre la primera hoja seleccionada y la última.
Resúmenes especiales.
Llegado este punto, y dado que estamos utilizando funciones vamos ver dos consolidaciones especiales, tomando como origen estos datos.
Ventas totales a clientes por productos.
Abrimos una nueva hoja a la que llamamos Resumen productos, en la que queremos ver las ventas totales a cada cliente pero de determinados productos. pudiendo seleccionar los que yo quiero, por ejemplo:
=Edulcorantes!O7
Vamos a analizar un poco esta expresión:
Función CONCATENAR() o &
Esta función lo que permite es pegar el contenido de distintas celdas en una sola.
Así, si en la celda C7 escribimos:
=C$6
Obtenemos el nombre del encabezado, fijamos la fila para poder copiar la fórmula hacía abajo sin perder la referencia:
=C$6&"!"
El resultado:
=C$6&"!O"
El resultado:
Para ello previamente veamos la función FILA().
Pero todavía no es suficiente, aún no obtenemos el importe total de las ventas, tan solo tenemos una referencia (una hoja, una columna y una fila). Para ello necesitamos utilizar otra función: INDIRECTO()
Ahora ya podemos copiar esta formula en el resto de celdas. Si queremos ver los totales de cualquier otro producto, tan solo tenemos que cambiar el nombre del encabezado por el de otro producto, sin necesidad de cambiar la fórmula creada.. Eso sí, recuerda escribirlo tal y como está en la etiqueta de dicha hoja 
Guarda el archivo como Consolidar 3, y vuelve a abrir el archivo original, para ver el tercer modo.
En la venta que se nos muestra; estando el cursor parpadeando en el apartado referencia, vamos a la hoja Edulcorantes y seleccionamos desde B6 a O22.
Así ya tenemos los cuatro tipos de datos en cuatro columnas.
Damos formato a los valores y ya tenemos el tercer reporte consolidado.
Mi recomendación, en la medida de lo posible,. trabajar siempre con tablas de datos homogenizadas. este es una columna para cada tipo de dato y después con tablas dinámicas generar los reportes necesarios.
- Devuelve el número de fila de una referencia. Esto es:
- Si marcamos una celda, nos indicará en que fila se encuentra dicha celda.
- Si no indicamos ninguna celda, por defecto nos el numero de fila en la que estamos escribiendo la función.
Así pues, si en la celda D7 escribiésemos =FILA(), el resultado será 7
Si añadimos esta función a lo que ya habíamos escrito del siguiente modo:
=C$6&"!O"&FILA()
Obtenemos:
La función INDIRECTO() devuelve el contenido de una referencia especificada.
Su sintaxis es la siguiente:
=INDIRECTO(Referencia; [a1])
- Referencia. Es un argumento obligatorio. Representa una referencia a una celda que contiene un referencia del tipo A1, una referencia de tipo L1C1, un nombre definido como referencia o una referencia a una celda en forma de cadena de caracteres (este es nuestro caso). Si este argumento no es una referencia a una celda válida, la función INDIRECTO devuelve el valor de error #REF!.
- [a1]. Es un argumento optativo. Se trata de un valor lógico que indica si el estilo de referencia es A1 o L1C1. Si el argumento es VERDADERO o se omite entiende que el estilo es A1, en caso contrario L1C1.
Visto esto, para ye terminar tenemos que modificar lo ya escrito en la celda C7, introduciendo después del igual la función INDIRECTO, quedando del siguiente modo:
=INDIRECTO(C$6&"!O"&FILA())
Y, el resultado:
Ventas totales de productos.
En este caso, queremos consolidar las ventas totales por cada producto.
Previamente en cada una de las hojas de productos obtenemos el total de la columna TOTAL, dato que se recoge en la celda O23
En la misma hoja de resumen, nos preparamos los siguientes datos:
Cómo hemos visto el total de la columna TOTAL , se halla en celda O23, de cada una de las hojas, pues para hacer referencia a esta celda necesitamos conocer la función DIRECCION()
La función DIRECCION() permite obtener la dirección de una celda en una hoja de cálculo en función de un numero de fila y de un número de columna. DIRECCION devuelve la coordenada y no el valor en esta coordenada.
Los argumentos de esta función son:
- Fila. Obligatorio. Valor numérico que especifica el número de fila que se usará en la referencia de la celda. En nuestro ejemplo 23
- Columna. Obligatorio. Valor numérico que especifica el numero de columna que se usará en la referencia de la celda. En nuestro ejemplo la columna es la O, cuyo número es: 15
- [abs]. Optativo. Valor numérico que especifica el tipo de referencia que se devolverá:
- 1 u omitido, devuelve una coordenada absoluta
- 2, fila absoluta, columna relativa
- 3, fila relativa, columna absoluta
- 4, coordenada relativa
- [a1]. Es un argumento optativo. Se trata de un valor lógico que indica si el estilo de referencia es A1 o L1C1. Si el argumento es VERDADERO o se omite entiende que el estilo es A1, en caso contrario L1C1.
- [hoja]. Optativo. Valor de texto que explica el nombre de la hoja de cálculo que se usará como referencia externa, en nuestro caso el valor que se indica en la columna de PRODUCTOS. Si no se detalla, la dirección se devuelve en la hoja activa.
Conocida esta función si en la celda H7, escribimos:
Obtenemos la referencia:
Edulcorantes!$O$23
Así pues, como en el caso anterior tan sólo nos queda combinarla con la función INDIRECTO=INDIRECTO(DIRECCION(23;15;1;1;G7))
Y entonces ya se produce la "magia". Copiamos y pegamos en el resto de celdas y ya tenemos otro resumen consolidado.
Comando consolidar.
Creamos una nueva hoja Resumen y no situamos en la celda B6, por seguir el mismo esquema del resto de hojas, pero no es necesario.
Desde la pestaña Datos, en el bloque Herramientas de datos hacemos clic en el comando Consolidar
Una vez seleccionado el rango clic en Agregar y clicamos en la hoja Café (como todas las hojas de productos tienen la misma estructura, ya nos muestra el rango. De nuevo el botón Agregar y repetimos la operación para el resto de hojas de productos.
Cuando terminemos, marcamos las casillas de usar rótulos y crear vínculos tal y como se muestra en la imagen superior; y aceptamos.
Ya tenemos todos los datos consolidados
Guardamos como Consolidar3, y tomamos de nuevo el archivo original y vamos a por la cuarta forma.
Utilizando Power Query
Lo primero que vamos a hacer es convertir los datos de las distintas hojas en una Tabla de Excel, empezamos por la hoja Edulcorantes.
Nos situamos en la celda C7, y desde la pestaña Inicio, abrimos el desplegable del comando Dar formato como tabla y elegimos un estilo.
Marcamos que nuestros datos tienen encabezados y aceptamos. Ahora estamos en la pestaña Diseño de tabla. En la parte izquierda superior, aparece el apartado Nombre de tabla, cambiamos el nombre poniendo el mismo nombre que tiene la hoja.
Repetimos esta operación para todas las hojas.
Una vez hecho, vamos a la pestaña Datos, en el comando Obtener Datos, seleccionamos la opción Consulta en blanco
Con ello abrimos el editor de Power Query. En la barra de formulas escribimos:
= Excel.CurrentWorkbook()
Y nos muestra dos columnas. Hacemos clic en el icono de la columna Content
Nuestra una ventana con todas las columnas que contienen las tablas que hemos creado. Desmarcamos la opción Usar el nombre de columna original como prefijo, y aceptamos
Tenemos ahora una información que tenemos que tratar, si nos desplazamos con la barra que hay en la parte inferior hasta el final encontramos dos columnas Total y Name.
- Con respecto a la columna TOTAL, hacemos clic sobre su nombre con el botón derecho y clicamos en Quitar.
- Respecto a la columna Name, nos indica los nombres de los productos. Vamos a colocar esta columna al principio. Hacemos clic con el botón derecho en el nombre de la columna y seleccionamos mover al principio.
Tenemos que seguir normalizando los datos. En concreto tenemos cuatro tipos de datos: Productos (que están en la columna Name), Clientes, Meses (que en lugar de estar en una columna están en los encabezados), y las ventas que están distribuidas en varias columnas. Por lo tanto tenemos que conseguir que los cuatro tipos de datos estén en cuatro columnas. Las dos primeras ya las tenemos. Asi pues.
Clicamos en la columna Name y con la tecla Shift presionada hacemos lo mismo en la columna Clientes. Estando las dos columnas seleccionadas, botón derecho del ratón y clicamos en Anulación de dinamización de otras columnas
Ahora cambiamos los nombres de los encabezados. Doble cli sobre Name, y escribimos Productos. Los mismo sobre Atributo donde escribimos Mes y Valor, donde escribimos Ventas.
Hacemos clic en el incono a la izquierda de la columna Ventas y seleccionamos moneda
Por ultimo, clic en cerrar y guardar y no abre en una hoja nueva una tabla con todos los datos. A partir de esta tabla y mediante la utilización de tablas dinámicas podemos obtener los reportes que queremos.
Reporte de totales por clientes y mes
Nos situamos dentro de una celda de la tabla que acabamos de crear, y desde la pestaña Insertar, utilizamos el comando Tabla dinámica. Rellenamos la siguiente tabla del siguiente modo:
Y la venta que se muestra a la derecha para configurar la tabla dinámica así:
Damos formato a los importes y obtenemos el siguiente reporte:
Reporte de ventas totales por cliente y producto
Volvemos a crear una tabla dinámica, en este caso la ventana al cumplimentarla indicamos que la queremos en la celda G30. Y la venta de la derecha la cumplimentamos de este modo
Pero clicamos en el desplegable de Etiquetas de columna y seleccionamos los productos que queremos ver;
Damos formato de moneda a los importes y ya tenemos el segundo reporte
Total por producto
Creamos una nueva tabla dinámica que colocamos en la celda G53. Diseñamos la tabla del siguiente modo:
Damos formato a los valores y ya tenemos el tercer reporte consolidado.
Mi recomendación, en la medida de lo posible,. trabajar siempre con tablas de datos homogenizadas. este es una columna para cada tipo de dato y después con tablas dinámicas generar los reportes necesarios.
Comentarios
Publicar un comentario