Ha llegado a mis manos un libro excel para determinar el conocimiento que tiene de excel un empleado de una multinacional. Llevo mucho tiempo sin publicar en este blog, por falta de ideas, pero con el ese libro excel me ha llegado una idea, ¿cuando tengo que hacer un informe en excel, puedo utilizar otros medios que me faciliten el trabajo? Por supuesto. Tenemos una infinidad de utilidades, gratuitas o de pago que podemos utilizar sin ningún escrúpulo, siempre y cuando nos facilite de verdad el trabajo.
Esta vez voy a obtener un informe, a partir de unos datos incluidos en una hoja excel, con consultas access.
El libro excel recibido, en principio, parece un poco desordenado, por una cuestión de criterios yo considero que datos e informes no deben estar en la misma hoja. Intentemos "despejar" el tema, no creemos confusión.
- Separo los datos en una hoja independiente
- Creo una B.D. access en blanco.
- Vinculo, no importo, la hoja con los datos a una tabla de esa B.D.
- Los datos son datos trimestrales, son un informe de productos vendidos por trimestre.
- A su vez los productos vendidos están agrupados por producto propiamente dicho y categoría de producto.
- El informe a realizar es un informe sobre la venta por categorías y por trimestre.
- Por lo tanto, hay que agrupar todos los productos de la misma categoría en una sola línea y sumar los totales por trimestre y año en diferentes columnas.
CATEGORIA | T1 | T2 | T3 | T4 |
| Bebidas | 42.751,70 | 24.054,80 | 26.312,90 | 38.129,40 |
| Condimentos | 17.002,80 | 9.663,80 | 9.075,50 | 10.920,90 |
| Repostería | 22.694,40 | 19.548,90 | 26.183,80 | 25.004,40 |
| Lácteos | 78.494,10 | 44.976,90 | 39.050,40 | 56.020,90 |
| Pastas/Cereales | 15.759,50 | 19.131,00 | 11.888,20 | 16.023,40 |
| Carnes | 33.907,60 | 24.275,40 | 16.577,20 | 22.038,00 |
| Elaborados | 11.905,90 | 8.440,00 | 11.866,80 | 11.798,70 |
| Pescados/Mariscos | 45.879,00 | 20.311,70 | 22.188,40 | 33.123,60 |
Como ya he dicho, primero creo una B.D. en blanco y vinculo los datos excel con esa B.D. Una vez vinculados los datos con la B.D. solamente voy a trabajar con consultas acces.
- Las consultas que voy a utilizar, salvo una, son consultas elementales, escritas directamente con sentencias SQL.
- Segundo paso: Cada registro de la tabla "Datos" está formado por el campo "producto", el campo "categoria", el campo trimestre "Trim" y el campo "Importe". Como paso intermedio a la obtención del informe, aunque se puede hacer directamente sin pasar por este paso intermedio, creo una consulta que coloca los distintos importes en la columna que se corresponde con su trimestre, según el informe a obtener. Paso el campo trimestral único a un campo (una columna) para cada trimestre y una columna mas para el total año. Esta es una consulta intermedia, aunque, como ya he comentado se puede hacer directamente. Para ello utilizo la función condicional IIF().
- Consulta 01ImportesEnSuColumna:
- SELECT Datos.PRODUCTO, Datos.CATEGORIA, IIf(trim="T1",importe,0) AS PT, IIf(trim="T2",importe,0) AS ST, IIf(trim="T3",importe,0) AS TT, IIf(trim="T4",importe,0) AS CT, importe AS TA FROM Datos;
- Acumulo o sumo esas columnas con la consulta 02CategoriaTrimestre agrupadas por "Categoria"
- SELECT CATEGORIA, sum(PT) AS T1, sum(ST) AS T2, sum(TT) AS T3, sum(CT) AS T4, sum(TA) AS Total FROM 01ImportesEnSuColumna GROUP BY Categoria;
- Como necesito un total de todas las categorías preparo una consulta que me de ese total, 03TotalTrimestres. En esta consulta doy un valor constante a uno de los campos (con "Total" AS CATEGORIA) y sumo los distintos importes de los distintos trimestres.
- SELECT "Total" AS CATEGORIA, sum( [01ImportesEnSuColumna].PT) AS T1, sum([01ImportesEnSuColumna].ST) AS T2, sum( [01ImportesEnSuColumna].TT) AS T3, sum([01ImportesEnSuColumna].CT) AS T4, sum( [01ImportesEnSuColumna].TA) AS Total FROM 01ImportesEnSuColumna;
- Uno ambos informes con la consulta de unión "04 InformeFinalPorTrimestre"
- SELECT * FROM 02CategoriaTrimestre UNION select * from 03TotalTrimestres;
Consulta que nos da directamente el informe:
SELECT Datos.CATEGORIA, sum(iif(trim="T1", importe,0) ) AS PT, sum(iif(trim="T2", importe,0) )AS ST,sum( iif(trim="T3", importe,0) )AS TT, sum(iif(trim="T4", importe,0) ) AS CT,sum( importe ) AS TA
FROM Datos group by Categoria
UNION SELECT "Total" as CATEGORIA, sum(iif(trim="T1", importe,0) ) AS PT, sum(iif(trim="T2", importe,0) )AS ST,sum( iif(trim="T3", importe,0) )AS TT, sum(iif(trim="T4", importe,0) ) AS CT,sum( importe ) AS TA
FROM Datos;
Segundo informe, Tanto por ciento de las ventas por trimestre y año.
- Este informe presenta el importe acumulado por trimestre y el tanto por ciento que supone ese acumulado sobre las ventas anuales. Para obtenerlo hay que obtener el acumulado por trimestre, consulta 11TotalPorTrimestre (SELECT Datos.TRIM, Sum(Datos.IMPORTE) AS TTrim FROM Datos GROUP BY Datos.TRIM;). Como necesito conocer el acumulado de los importes total preparo una consulta que me da ese acumulado (12TotalAñoInnecesario).
- Como ya dispongo de una consulta que me da ese total, esa consulta no es necesaria, con utilizar la consulta (03TotalTrimestres) que ya me da el total vale, no necesito otra consulta adicional.
- Aquí tenemos un valor, el total, que no se añade como un registro mas después de los anteriores, es un valor que aparece como un campo mas en cada registro. No es una operación tipo JOIN, es una multiplicación matricial.
- Esta multiplicación se hace abriendo dos tablas o consultas simultáneamente, como en la consulta "13TotalTrimestreyAño"
- SELECT Trim, TTrim, format(TTrim/Total,"0.00%") as PorCent FROM 11TotalPorTrimestre, 03TotalTrimestres;
- UNION select "Total" as Trim, Total,"" as PorCent from 03TotalTrimestres;
