domingo, 18 de septiembre de 2011

Añadir un Suma y sigue al final de cada página impresa.

Recientemente leía un correo de una lectora que me preguntaba sobre la manera de añadir Subtotales al final de cada página impresa:

...Tengo una base de datos que al momento de imprimir tiene alrededor de 120 paginas…Necesito que al momento de imprimirlas aparezcan en ellas los subtotales al pie de cada pagina y al final de la ultima pagina el total general…o quizas como se pueden insertar los subtotales en la parte inferior de cada pagina rapida o automaticamente en la parte inferior de cada pagina en las hojas de trabajo antes de imprimirlo...


La solución que daré hoy no necesita de macros.
Supongamos que en nuestra Hoja de cálculo tenemos un campo con 1000 registros, en nuestro ejemplo valores del 1 al 1000, saldrían immpresas unas 17 páginas:


El siguiente paso es clave, ya que añadiremos una columna auxiliar con la siguiente función:
=ENTERO(FILA()/58)
con esto conseguiremos dividir nuestros 1.000 registros en grupos de 58 (he elegido 58 por que más o menos son los registros que caben en cada página).


Para que al final quede más vistosa nuestra impresión, desde Diseño página > Configurar página > Imprimir títulos > Repetir filas en extremo superior y marcaremos que se repita al imprimir la fila 1:


Será en este paso donde configuraremos lo más importante, ya que con el rango de valores seleccionado A1:B1001, ejecutaremos la herramienta de Subtotales, desde la ficha de Datos > Esquema > Subtotal


Lo más importante es seleccionar Salto de página entre grupos, y añadir como Subtotal la función suma en el campo de importes a subtotalizar.
Hemos obtenido 17 agrupaciones (1.000/58 = 17,2 grupos), dándole la herramienta de Subtotales la forma de Esquema que necesitábamos para tener una línea de Subtotal justo en la última fila de cada página a imprimir.
En la imagen podemos observar esos 17 grupos subtotalizados, y los saltos de página insertados automáticamente.
Para una mejor visualización, y para abordar el siguiente paso, hemos pulsado el botón de Esquema 2, que nos deja nuestra Hoja tal cual se muestra.


Bien, casi acabamos ya.
Fijémosnos en cada una de las funciones que se han añadido a nuestra tabla de valores. Son subtotales parciales de cada agrupación realizada, no son sumas acumulativas desde el primer valor, es decir, el Total 0 sumas sólo aquellos registros cuya valor de campo orden fuera cero; el siguiente Total 1 suma únicamente los registros correspondientes con el campo orden igual a 1, etcétera.
No es exactamente lo que queremos, dichos subtotales no corresponden a un Suma y sigue de registros acumulados desde el primero de ellos. Entonces, ¿cómo lo solucionamos?.
Cambiaremos todas y cada una de esas funciones, en mi caso 17, por la siguiente función:
=SUBTOTALES(9;$B$2:B57)
esto es, fijando el primer valor $B$2,
con esta operación, por fin, hemos llegado al resultado esperado. Tenemos al final de cada página impresa un Subtotal acumulado (un Suma y sigue) desde el primer valor.


Podemos comprobar los resultados.

Para decorar y preparar algo más nuestra impresión, personalizaremos el formato de esas celdas con la función modificada, le daremos un Formato de celda personalizado tipo "Suma y sigue... "#.##0 y a la última celda, que corresponde al Total general, la formatearemos con "Suma final... "#.##0; y ocultamos la columna auxiliar A.
El aspecto final será:


Si en los botones de Esquema pulsamos el 3 y desplegamos todo el detalle de nuestra Tabla de datos, y pulsamos Vista preliminar veremos el resultado obtenido:

18 comentarios:

  1. Saludos cordiales....sinceramente gracias....hace mucho tenia esa pregunta...la explicación es tan clara que pude utilizar el concepto sin ningún problema..

    ResponderEliminar
  2. Hola, tengo una duda a ver si me la puedes solucionar:
    Me han pasado un listado con mas de 1000 empresas en este formato:

    Nombre de la empresa
    dirección
    email

    Nombre de la empresa
    dirección
    email

    Necesito saber como poner que todas las celdas que contegan un email se coloquen en una columna para enviarles un correo, al ser mas de mil no puedo ir una a una seleccionando y poniendolas en una columna. Como lo hago?

    ResponderEliminar
  3. Hola, me imagino todos los valores en la misma columna A, y con la misma cadencia, esto es fila 1 Nombre, fila 2 dirección, fila 3 email, fila 4 Nombre, fila 5 dirección, fila, 6 email, fila 7 Nombre, etc.
    Si es así, una manera muy sencilla, si sólo tienes mil registros, sería en la fila 1 de la columna B, la fórmula =A2, y en la columna C de la fila 1 la fórmula =A3.
    Ahora seleccionas el rango B1:C3 y arrastras hasta el final de tu listado, lo copias y pegas como valores, aplicas un Autofiltro a las tres columnas, y filtras por la columna B los 'Vacíos', eliminándolos. Quitas el Autofiltro y ya tienes tu listado de 1000 empresas por columnas...
    Otra opción sería con macros.
    Espero te sirva, no creo haya nada más fácil de aplicar.
    Un saludo

    ResponderEliminar
  4. Hola. Tengo un cuadro para generar un pedido. Es una hoja que viene de las sumas de las piezas necesarias de otras 4 hojas. En total son unas 100 piezas pero me gustaría que se pudiera imprimir sólo las que tienen valores en el total, para ello he hecho un filtro en el que no se incluyan las de valor 0. A la hora de imprimir, me gustaría que se imprimiesen en las hojas necesarias,con un suma y sigue pero siguiendo los pasos anteriores, al estar ocultas las filas cuyo total es 0, el cuadro no es uniforme (en unas hojas salen pocas filas, en otras muchas...) ¿Es posible? tengo las filas puesta una con trama y otra sin ella para no liarse con las lineas, pero lo mismo, al ocultarse algunas filas, de repente salen 2 sin trama o viceversa ¿se puede poner las tramas en función del cuadro final resultante del filtro?
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Kreativer,
      efecctivamente es un problema importante lo que propones...
      La solución más sencilla sería aplicar un Filtro avanzado con esa condición que no incluya los valores 0, así obtendrás tu listado sin filas ocultas y sin valores 0, sobre este Nuevo listado podrás aplicar el truco propuesto en esta entrada.

      Prueba y comentas si te puede servir.
      Slds

      Eliminar
    2. Hola, lo he intentado pero al final he vuelto al autofiltro, he quitado las tramas y el suma y sigue y como lo máximo que van a salir son tres hojas, las he numerado y he puesto sólo el total al final. Muchas gracias

      Eliminar
    3. Bueno...
      si, eso es más sencillo...
      Siento no te sirviera la propuesta.
      Slds cordiales

      Eliminar
  5. Hola kisiera saber si se puede y como hacerle para sumar los totales de las paginas pero kiero grabar una macro y me varia el numero de celdas k hay k sumar al momento de realizar otro documento, k es lo k puedo hacer???

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que configurar y definir para cada hoja que quieras sumar, una variable que detecte mediante el uso del método .End la última fila con datos, para luego ir recorriendo cada hoja, con un método FOR EACH por ejemplo, e ir acumulando valores de esos rangos por hoja.

      Es demasiado largo para plantearlo en un comentario.
      Espero te oriente

      Slds

      Eliminar
  6. que es una suma no continua y como se lo hace

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?
      Un placer igualmente saludarte
      Un suma no continua es una suma de celdas 'separadas', por ejemplo
      suma continua: =SUMA(A1:B10)
      suma discontinua: =SUMA(A1;C1;E1)

      Saludos cordiales

      Eliminar
  7. hay alguien que pued ayudarme con programacion de visual basic

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?
      Para aprender a programar puedes informarte en los cursos elearning (online) que imparto.
      www.excelforo.com

      Un saludo

      Eliminar
  8. Hola, deseo enumera cada hoja de calculo de un libro de excel. Al utilizar pie de pagina me enumera solo las paginas de la hoja de de calculo. Entonces para todo el libro tengo que ir cambiando hoja por hoja el pie de pagina. Tal vez es posible insertar una formula en le pie de pagina.

    ResponderEliminar
  9. Buenas tardes. Te hago una consulta sobre este post. Hay manera de que te además de subtotalizar al final de cada hoja, te agregue al principio de cada hoja el subtotal del final de la hoja anterior?

    Gracias.

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.