domingo, 19 de abril de 2009

Ajuste Automático de Rangos para Inserción de Filas

El siguiente ejercicio es bastante sencillo pero se trata de una pregunta que de manera reiterativa me realizan mis alumnos. Cuando tenemos columnas con cifras y justo al final añadimos, como suele ser habitual, el sumatorio total de dichas cifras, si queremos insertar una nueva fila tenemos que hacerlo en el medio de dicha lista para que el sumatorio recoja el nuevo concepto ¿Hay alguna manera de poder añadir nuevas filas al final de una lista y que el sumatorio sea correcto?
Como veremos a continuación la solución es razonablemente sencilla anidando la función DESREF dentro de la función SUMA.
Supongamos que tenemos la siguiente lista de gastos de oficina:
Como se puede ver en la celda C12 hemos calculado el sumatorio de los gastos. Si necesitamos añadir, por ejemplo, dos nuevos conceptos a continuación de la última referencia entonces tendremos que replantear nuestra fórmula de C12 para que recoja los nuevos importes. Necesitamos generar un rango que se ajuste automáticamente a las nuevas entradas. La fórmula para conseguirlo es la siguiente:

=SUMA(C4:DESREF(C12;-1;0;1;1))

Fíjese que hemos mantenido el comienzo del rango de suma igual =SUMA(C4:   pero hemos hecho "dinámico" el final del rango con la función DESREF. Lo que le estamos indicando con esta función es que se "desvíe" desde C12  -1 filas, es decir, una fila más arriba y 0 columnas, ya que ya nos encontramos en la columna que queremos sumar. EL resto de argumentos alto 1;ancho 1, podríamos omitirlos ya que no son obligatorios y por defecto ya establecen tales valores (para ver cómo funcionan estos argumentos puede consultar "Cálculos con Rangos Dinámicos").

Pruebe a insertar nuevas filas a partir de la fila 12 y comprobará como el sumatorio contemplará los nuevos importes que introduzca:
Apunte sobre Inserción de Filas: Existen muchas formas para insertar nuevas filas. A continuación detallamos varios (el tercero es quizás menos conocido pero muy práctico y rápido):
1. Nos colocamos en la celda donde queremos que inserte la fila y vamos al menú Insertar/Fila.
2. Seleccionamos toda la fila donde queremos insertar una nueva fila y pulsamos Ctrl y la tecla +.
Estos dos sistemas insertan una fila justo encima de la fila donde nos encontremos.
3. Para que se entienda mejor este método lo aplicaremos a nuestro ejemplo. Seleccionamos el rango A11:D11 y nos situamos en la parte inferior derecha de este rango como si fueramos a copiar hacia abajo (encima del pequeño cuadrado negro). Pulsamos las teclas Ctrl+Shift y con estas teclas pulsadas arrastramos hacia abajo tantas filas como queramos añadir. Excel insertará las nuevas filas debajo del rango seleccionado.

3 comentarios:

  1. Una vez solucionado el problema de la fila de totales yo lo que quiero es que se inserten filas según voy rellenando, manteniendo formato y fórmulas, supongo que hay que crear una macro; pero ¿cómo pongo en marcha la macro cada vez que se rellena una fila sin crear un botón?.
    Gracias

    ResponderEliminar
    Respuestas
    1. No es necesario montar una macro. Lo único que debes hacer es seleccionar todo el rango (incluyendo los rótulos) y en la ficha Insertar hacer clic en la opción Tabla. De esta manera cuando introduzcas un nuevo dato insertará una nueva fila con el formato designado y copiando también las fórmulas que tengas.

      Eliminar
    2. He probado, pero no se adapta a lo que necesito, primero porque no discrimina la fila totales, tampoco copia el formato de las celdas y obliga a usar la tecla Entrar en todas las celdas, en realidad no inserta filas, añade filas a la tabla sin respetar la hoja (bueno, si le ocultas las filas vuelve a la primera).
      La macro tampoco la he conseguido ya que se tiene que actualizar la fila en la que debe insertar las siguientes y por supuesto asignada a un botón.
      Seguiré probando, gracias

      Eliminar