jueves, 22 de septiembre de 2011

Añadir un Suma anterior al inicio de cada página impresa.

Algunos días atrás escribí una explicación de cómo añadir Subtotales al final de cada página impresa, la cuestión que explicaré hoy no podía hacerse esperar.
¿Cómo añado una Suma anterior al principio de cada página impresa:

...¿se podría con un criterio similar obtener una fila al final (tendría que ser la penúltima fila) de cada página con el subtotal de los valores de la página y otra fila bajo la misma que indique el total acumulado de cada una de las páginas anteriores? (la fila del acumulado podría ser la primera fila de la siguiente pagina tal como un transporte en un libro diario contable)...


La respuesta la encontraremos igualmente con la herramienta Subtotal.
Veamos los pasos a seguir, si partimos de esta tabla de valores:


En primer lugar, dividimos nuestro listado en cuatro partes, para que cada una de ellas se imprima en una página diferente. Para ello, añadiremos una columna auxiliar con la función:
=ENTERO((FILA()+2)/4)
inmediatamente después, los copiaremos y pegaremos como valores. Si no realizamos este punto, en pasos siguientes tendremos ciertas incongruencias.
Seleccionamos nuestro nuevo rango, y ejecutamos la herramienta Subtotal:


Como vemos en la imagen, hemos decidido añadir Subtotales a los campos 'Rosas', 'Margaritas' y 'Violetas'. También es muy importante que para este primer Subtotal agregado no marquemos la opción de Salto de página entre grupos.
Sin desmarcar el rango activo, volvemos a ejecutar la herramienta Subtotal, pero teniendo cuidado de seleccionar la opción Salto de página entre grupos, ahora sí:


Veamos el resultado, y donde quedan seleccionados los Saltos de página:


Por último, ya que la usuaria lo pide así, añadiremos un Subtotal más, donde aparecerá el parcial de cada página impresa.
Repetimos la operación, nuevamente sin marcar la opción Salto de página entre grupos, con nuestro rango seleccionado:


Y llegamos a:


Por una cuestión estética, desde Diseño página > configurar página > Imprimir títulos repetiremos la fila $1:$1 en el extremo superior.

Nos paramos unos segundos, y observamos a qué hemos llegado. Tenemos tres Subtotales para cada división, con un salto de página entre la segunda y tercera.
Cómo vamos a modificar las fórmulas de dichos subtotales?; la primera se quedará tal cual y reflejará el parcial por página que quería nuestra lectora; la segunda (que sería la última fila de la página impresa, reflejará el Suma y sigue... acumulado; por último, el tercer subtotal, que quedará en la primera fila de cada hoja, representará la Suma anterior, esto es, el acumulado final de la hoja anterior.
Para este segundo subtotal, nuestra Suma y sigue..., cambiaremos su función por:
=SUBTOTALES(9;B$2:B5)


Para el tercer subtotal, nuestra Suma anterior..., cambiaremos su función por la misma función!!:
=SUBTOTALES(9;B$2:B6)


Sólo queda dar los formatos personalizados concretos a cada celda; en concreto, a los primeros Subtotales, le asignamos el Formato de celda personalizado: "Parcial hoja... "#.##0
al segundo de los Subtotales, el Formato personalizado: "Suma y sigue... "#.##0
al tercero, el Formato de celda personalizado: "Suma anterior... "#.##0
Y llegamos a:


Eliminamos la fila 28, ya que es un Subtotal que no nos sirve para nada... equivaldría a la 'Suma anterior' de una página siguiente que no existe. Y ocultamos la columna A.
Mostramos todo el detalle de nuestro listado y presionamos Vista preliminar, donde vemos, por ejemplo, la página 2:


donde la primera fila devuelve la Suma anterior acumulada, la penúltima fila es el Parcial de la hoja, y la última fila corresponde al Suma y sigue, i.e., a la suma acumulada desde el primer registro.

14 comentarios:

  1. Saludos Cordiales!!
    muchas gracias por el tiempo dedicado..te cuento que probé el ejercicio con el ejemplo y me salio perfecto...pero al querer aplicarlo a mi base me encontré que no pude aplicar la formula de la columna Orden....no la entiendo....mi base tiene entre 50 y 60 hojas y quiero que cada hoja tenga 11 lineas + las lineas subtotales...Al aplicar la formula con 11 la primera hoja no alcanza el parámetro, las siguientes no tienen problema...podrias ayudarme y aclararlo? te comento que esto me pasa con la aplicacion de las dos fórmulas de =ENTERO(fila()/58) y =ENTERO((FILA()+2)/4)...el cuatro lo cambie por 11..y el 2 no se su finalidad..gracias desde ya..

    ResponderEliminar
  2. Hola Graciela,
    la fórmula =ENTERO(FILA()+X/Y) es una forma de llegar a la división de las páginas, esto es, cuantos registros necesitas por página impresa. Esto lo conseguimos ajustando el primer grupo sumando o restando X y dividiendo por Y, siendo Y el número elegido de registros por páginas.
    Si quiers que tus páginas tengan 11 líneas + los subtotales, y suponiendo los datos se encuentren a partir de la fila 2 (la fila 1 sería el rótulo) escribirías:
    =ENTERO((FILA()-2)/11)
    pruébalo y comentas.
    Slds

    ResponderEliminar
    Respuestas
    1. Hola!!! Saludos nuevamente....estoy queriendo usar la fórmula y no consigo que me arroje otro resultado mas que una columna interminable de ceros o unos..... pensé que era la maquina pero sea donde sea no arroja el resultado esperado y no entiendo porque.. habría alguna otra forma de hacer este calculo para hacer la división de la hoja y poder aplicar los subtotales o no estoy teniendo algo en cuenta....mis titulos + las lineas en blanco e inclusive la 1ra linea tienen 18 filas y requiero que el informe tenga siete filas + 2 subtotales (total de la hoja y total acumulado) la formula que aplico es entero((fila()-18)/7) y el resultado me da una interminable secuencia de ceros...y si les doy otro numero tambien.....probe con +2)/4 e inclusive -2)/4 sin exito...que puede estar pasando? gracias desde ya!!!!

      Eliminar
    2. Hola Graciela,
      lo único que se me ocurre es que lo hagas a mano, quiero decir, comiences escribiendo 7 unos consecutivos (desde A1:A7), y a continuación 7 celdas con la fórmula =A1+1 hasta A7+1, desde A8:A14, y luego arrastrar hasta donde necesites.

      De todas formas es muy raro que te salgan todo ceros aplicando la fórmula que te dí
      =ENTERO((FILA()-2)/11)
      o incluso la tuya
      entero((fila()-18)/7)

      por que están probadas y funcionan en diferentes versiones de Excel y en distintos equipos.
      Slds

      Eliminar
  3. Saludos Cordiales: Desconocía la fórmula hasta que la presentaste...con tu explicación la entendí y en consecuencia la apliqué con resultados muy satisfactorios...por fin estoy redondeando la presentación de mi base de acuerdo a los requerimientos de formato (mil gracias por ello!!)...Dime...con un filtro en el subtotal por hoja, en el suma y sigue y en el total anterior, pude darle formato a la celda en cuanto a dimensiones y de lineas ...solo en dichas lineas, pero cuando intento colocarles color de la misma forma...toda la planilla asume dicho color...que dolor de cabeza...(te cuento que en el primer subtotal elegido si asumieron únicamente las lineas elegidas como quería, pero para la segunda selección ya no hubo caso)quiero que las filas de los distintos subtotales queden bien diferenciadas ..hay alguna forma que solo las lineas elegidas asuman los parámetros que les doy..o solo podré darles color linea x linea? nuevamente espero tu respuesta y te elevo mi agradecimiento por compartir tus conocimientos.

    ResponderEliminar
  4. Hola Graciela,
    en breve subiré una explicación de cómo conseguir lo que necesitas.
    Te adelanto que la soluciónb pasa por seleccionar sólo las celdas visibles.
    Un saludo

    ResponderEliminar
  5. sencillamente espectacular, Gracias por donar sus tiempos y conocimientos. Desde Asncion, Paraguay - Emilio Benitez

    ResponderEliminar
    Respuestas
    1. A mi lo único que no me hace es que no me suma el acumulado de una hoja con la siguiente, por ahi si pueden ayudarme. Gracias

      Eliminar
    2. Fíjate bien en los pasos, y en las imágenes... verás como según se insertan saltos, se añade la información por la que preguntas...
      ;-)
      Saludos

      Eliminar
  6. Estimado Ismael,
    Este post no corresponde con el tem en cuestion, pero no consegui ninguno con la funcion SUMIFS. A continuacion te explico el problema:
    Tengo una tabla de excel como la que sigue:
    P05C P08C P10C P11C P15C P20C P24C P25C P30UC P30LC P32C P40C P41C P42C P50C P51C P55C
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 1052 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 1052 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 1052 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 1052 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 1052 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 81 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4824 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 456 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

    cada fila es un dia del mes, ahora, lo que quiero hacer es sumar cada columna por separado. estoy utilizando esta formula:
    =SUMIFS(MIND_CoalInSitu,MIND_CoalName,A3,MIND_date,">="&B2,MIND_date,"<"&C2)
    Donde:
    CoalInSitu, es la cantidad de carbon en cada una de las celdas:
    CoalName, es el nombre del manto de carbon, (P05C, P08C, etc, etc, etc)
    Mind_Date son las fechas de una fecha hasta otra (porque quiero sumar el carbon entre dos fechas distintas)
    Y el resultado recibido es:#VALUE!
    No se si la seleccion que tome para darle el nombre de las variables esta malo, o que hice mal. Se que es una estupidez, pero no le he podido encontrar solucion.

    Espero que haya sido claro y me puedas ayudar.

    Saludos Cordiales,
    Carlos Lopez

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      en castellano SUMIFS es SUMAR.SI.CONJUNTO.. quizá así te sea más sencillo encontrar multitud de ejemplos (esta función es de la más usada en Excel).

      Es difícil darte una respuesta, viendo sólo nombres definidos, sin ver las referencias reales... aparentemente la función está bien construida.
      El error podría ser (es una suposición) en que haya una discordancia entre el rango_suma (CoalinSitu) con los CoalName, quiero decir, si CoalinSitu es A:H (en lugar de A:A, por ejemplo), te fallará..

      Deberías probar a emplear vectores (en lugar de matrices) de celdas...

      Espero haberme explicado.
      Cordiales saludos

      Eliminar
  7. hola, la explicación esta barbara, el problema que me surge es que mi base de datos, al imprimirla son alrededor de 145 hojas, y modificar el rango de cada una de las formulas "suma y sigue" de manera manual se vuelve algo tedioso, alguna alternativa que me puedan brindar?

    ResponderEliminar
    Respuestas
    1. Hola Sebastian,
      echa un vistazo también a esta otra entrada
      http://excelforo.blogspot.com.es/2011/09/anadir-un-suma-y-sigue-al-final-de-cada.html

      En ningún caso hay que modificarlas una a una.. esto lo hace la herramienta explicada por nosotros.
      Saludos

      Eliminar

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