Capacitate Excel: Como hacer un Reporte Comparativo en Excel 2010

Como hacer un Reporte Comparativo en Excel 2010 - Parte 4

Ahora que ya tenemos todas nuestras hojas completa solo necesitamos agregar las fórmulas y funciones necesarias a la hoja REPORTE para construir nuestro modelo.

Descargar el Archivo en Excel Aquí

Empezamos agregando en la columna los número del 2 al 5 los cuales son utilizados en la celda C7 que veremos y explicaré a continuación.

Estos números solo se agregan en la hoja Reporte, más adelante se seleccionan y se le da el color blanco para que no se vean.

- VENTAS MENSUALES 2013

Para obtener lo datos del 2013 escribimos la siguiente función en la celda C7 de la hoja REPORTE =BUSCARH($C$2,'2013'!$B$12:$N$17,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2013'!$B$12:$N$17 es el rango en la hoja 2013 donde va a buscar el mes y $A7 es la fila que va a devolver.

- VENTAS ACUMULADAS 2013

Para obtener lo datos acumulados del 2013 escribimos la siguiente función en la celda I7 de la hoja REPORTE =BUSCARH($C$2,'2013'!$B$21:$N$25,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2013'!$B$21:$N$25 es el rango en la hoja 2013 donde va a buscar el mes y $A7 es la fila que va a devolver.

Hoja 2013

La función empieza a buscar en en rango $B$12:$N$17 (ver cuadro rojo) y cuando busca el mes en nuestro caso el mes 2 (ver columna D) devuelve la fila 2 que es a lo que se refiere el $A7 de la función y corresponde a Mérida de los datos "Actualizados" no históricos. Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas 2013.

Recuerda que la función está en la hoja REPORTE.

- VENTAS MENSUALES 2014

Para obtener lo datos del 2014 escribimos la siguiente función en la celda D7 de la hoja REPORTE =BUSCARH($C$2,'2014'!$B$3:$N$8,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2014'!$B$3:$N$8 es el rango en la hoja 2014 donde va a buscar el mes y $A7 es la fila que va a devolver.

- VENTAS ACUMULADAS 2014

Para obtener lo datos acumulados del 2014 escribimos la siguiente función en la celda J7 de la hoja REPORTE =BUSCARH($C$2,'2014'!$B$12:$N$16,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2014'!$B$12:$N$16 es el rango en la hoja 2014 donde va a buscar el mes y $A7 es la fila que va a devolver.

Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas 2014.

Hoja 2014

- VENTAS MENSUALES PRESUPUESTO (PTO)

Para obtener lo datos del PTO escribimos la siguiente función en la celda E7 de la hoja REPORTE =BUSCARH($C$2,PTO!$B$3:$N$8,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, 'PTO'!$B$3:$N$8 es el rango en la hoja PTO donde va a buscar el mes y $A7 es la fila que va a devolver.

Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas PTO.

- VENTAS ACUMULADAS PRESUPUESTO (PTO)

Para obtener lo datos acumulados del PTO escribimos la siguiente función en la celda E7 de la hoja REPORTE =BUSCARH($C$2,PTO!$B$12:$N$16,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, 'PTO'!$B$12:$N$16 es el rango en la hoja PTO donde va a buscar el mes y $A7 es la fila que va a devolver.

Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas PTO.

Hoja PTO

CLIENTES MENSUALES 2013

Escribir en la celda C14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,'2013'!$B$32:$N$36,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen

CLIENTES ACUMULADO 2013

Escribir en la celda I14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,'2013'!$B$32:$N$36,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen

Hoja 2013

CLIENTES MENSUALES 2014

Escribir en la celda D14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,'2014'!$B$23:$N$27,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

CLIENTES ACUMULADO 2014

Escribir en la celda J14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,'2014'!$B$32:$N$36,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

Hoja 2014

CLIENTES MENSUALES PRESUPUESTO (PTO)

Escribir en la celda D14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,PTO!$B$23:$N$27,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

CLIENTES ACUMULADO PRESUPUESTO (PTO)

Escribir en la celda K14 de la hoja REPORTE la siguiente función

=BUSCARH($C$2,PTO!$B$32:$N$36,$A14,FALSO)

De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

Hoja PTO

Ventas x Clientes

Ahora, en la hoja REPORTE dividimos el importe de las ventas entre el número de clientes, tanto en 2013, 2014, PTO mensualmente y acumulado.

Variaciones vs 2013 y vs PTO

Ahora necesitamos obtener las variaciones mensuales y acumuladas de Ventas y Clientes por lo que con lo que solo necesitamos restar el las Ventas 2014 menos Ventas PTO y también Ventas 2014 menos ventas 2013. Esto se hace también por los clientes.

Para terminar cambiamos el color de los números de la columna A y creamos la hoja Títulos esta hoja contiene el título del mes y se usa de la siguiente manera (ver imagen)