domingo, 30 de octubre de 2011

Rangos variables en fórmulas

Descargar el fichero: FormulasVariables.xlsx

Algo con lo que siempre hemos soñado los usuarios avanzados de Excel es con la posibilidad de poder modificar la sintaxis de una fórmula para que se adapte de forma automática a nuestras necesidades.

Hoja1

En este caso, no es que se logre completamente este sueño, pero si seremos capaces de modificar la función SUMA para que el rango que sume sea el que nosotros elijamos sin tener que editar la fórmula, y sin tener que cambiar el rango, de forma manual.



En la columna B disponemos de 100 números aleatorios. En la celda amarilla indicamos cuantos de estos números deseamos sumar.

En la celda verde conseguimos efectuar la suma del número de sumandos indicado en la celda amarilla. Para ello, utilizamos la función:

=SUMA(INDIRECTO("B4"):INDIRECTO("B"&D5+3))

La función INDIRECTO es ya una vieja amiga de este blog y pertenece a las funciones avanzadas con las que conseguimos grandes resultados.

Además usamos el concatenador: &, que nos permite unir (concatenar) cadenas. Esto es, unimos texto o cadenas alfanuméricas.

En la celda D9 hemos efectuado las suma de los 20 primeros datos de la columna B, y para ello hemos utilizado el método tradicional, usando la función SUMA y como rango tomamos los 20 primeros valores:

=SUMA(B4:B23)

Esta fórmula tiene el inconveniente de que si en la celda amarilla cambiamos el valor, que actualmente es de 20, por otro valor, tendríamos que editar la fórmula y cambiar el rango al que se extiende la función SUMA, si realmente queremos obtener la suma del número de valores indicados en la celda amarilla.

Para ver otras maravillas que se pueden conseguir con la función INDIRECTO puede consultar estos Posts,

Hoja2

Podemos practicar sumando en horizontal.


Hoja3

Calcular el determinante de una submatriz.

En las celdas de color rosa indicamos la posición inicial de fila y columna de la submatriz e indicamos la dimensión. Necesariamente la submatriz ha de ser una matriz cuadrada donde el número de filas y columnas coincide, por lo que únicamente marcamos de color rosa el número de filas (celda E23) y el número de columnas queda igual que el de filas.


Los valores de la matriz se generan de forma aleatoria. Al pulsar la tecla F9 cambian.

Hemos marcado de color verde la submatriz dentro de la matriz principal. Para obtener este color hemos utilizado Formato condicional. Esta es la fórmula empleada en el formato condicional para la celda B5 que luego se extiende al resto de la matriz.
=Y(FILA(B5)>=$C$13+$E$19-1;FILA(B5)<=$C$13+$E$19+$E$23-2;COLUMNA(B5)>=$C$14+$E$20-1;COLUMNA(B5)<=$C$14+$E$20+$E$24-2)

14 comentarios:

  1. Hola me encanta tu blog y siempre leo todos tu articulos pero en este creo que hay un pequeño error y considero que la formula no es:
    =SUMA(INDIRECTO("B4"):INDIRECTO("B"&D5+3))

    y debe ser:
    =SUMA(B4:INDIRECTO("B"&D5+3))

    espero tu respuesta

    ResponderEliminar
  2. Hola Otoniel.

    La fórmula que propones también funciona. Cualquiera de las dos son buenas.

    Un cordial saludo.

    ResponderEliminar
  3. En primer lugar, mi enhorabuena y gratitud, llevo tiempo siguiendo sus artículos y aplicándolos. Y ahora mi consulta:

    ¿Cómo aplicar esta solución de INdirecto para rangos variables cuando el Rango de datos está en filas y no en columnas?

    Gracias por anticipado

    Tano

    ResponderEliminar
  4. Hola Sebastian.

    He añadido una segunda hoja (Hoja2) al libro de Excel que puedes descargar al inicio del post: FormulasVariables.xlsx

    En esta segunda hoja puedes ver un ejemplo para trabajar con filas.
    Ha sido necesario usar la función INDIRECTO en el modo F1C1, que es un sistema donde para referirte a la celda A1 lo dices indicando la fila de que se trata y la columna de que se trata. La F significa Fila y la C significa columna.

    Así por ejemplo la celda D3 equivale a F3C4 según este sistema.

    Si a alguien de Sudamérica no le funciona es debido a que con algunas configuraciones regionales del idioma idioma español, no se dice Fila (F) sino Línea (L), y por la tanto la celda D3 sería la celda L3C4 según este sistema.

    Un saludo.

    ResponderEliminar
  5. Gracias, por la solución y por tan rápida respuesta.

    ResponderEliminar
  6. Hola debo resolver una operación con la función sumar si y no me da el resultado dice así total de compras entre 5000 y 10000

    ResponderEliminar
  7. Excelente, la función INDIRECTO me sirvió para usar el contenido de una celda como referencia a un NOMBRE que tengo definido en mi libro, esto lo utilicé para que un cuadro de lista me desplegue determinados municipios en dependencia del departamento que contiene la celda contigua!

    ResponderEliminar
  8. Es increible lo de esta función, me acaba de solucionar un problema que tenía.
    No obstante te lo voy a plantear de forma sencilla:
    Tengo unas celdas combinadas con esta función =+SUMPRODUCT(O97:O103;T97:T103), pero además tengo una Macro que puede añadir a esos bloques más líneas y por lo tanto el Sumproduct debería de arrastrarse según las celdas que se añadan. Mi solución era escribir en la misma Macro que añade las líneas una función de escribir fórmula con una variable con la que trabajo para que amplie los rangos con los que tiene que trabajar el Sumproduct, pero en el lenguaje de VisualBsic parace que no permite introducir una variable en ese tipo de comandos porque no lo entiende:
    Yo grabé esto
    ActiveCell.FormulaR1C1 = "=+SUMPRODUCT(RC[-7]:R[23]C[-7],RC[-2]:R[23]C[-2])"

    E intnté estas dos cosas pero me da error ambas. Fila y Filas son variables Integer que me definirían los rangos.
    ActiveCell.FormulaR1C1 = "=+SUMPRODUCT(RC[-7]:R[Filas]C[-7],RC[-2]:R[Filas]C[-2])"

    ActiveCell.Formula = "=+SUMPRODUCT(Range("V" & Fila, Range("V" & Fila + Filas - 1 + 5)),Range("T" & Fila, Range("T" & Fila + Filas - 1 + 5)). )"

    Por eso encontré tu blog porque estoy buscando la forma de escribir formulas con rangos variables en VBA

    ResponderEliminar
  9. Hola Adolfo
    Buen contenido en tu blog
    queria preguntar si conoces una formula que saque el promedio de palabras por ejemplo:

    (hola=20 adiós=40 bienvenido=60 prueba=80 cita=100)/5

    asignamos valores a las letras en caso de la palabra que este tome ese valor para
    despues hacer el calculo del promedio

    saludos

    ResponderEliminar
  10. Hola Adolfo
    Feliz Año.
    Quisiera saber si las funciones PENDIENTES e INTERSECCION.EJE, permiten calcular valores de sub-rangos, es decir si el par ordenado de la base de datos es (A2:A100;B2:B100) y, yo quiero obtener la PENDIENTE e INTERSECCION.EJE del sub-rango (A20:A89;B20:B89)
    Agradecido
    José Luis

    ResponderEliminar
  11. Buen dia sr. Adolfo. Mi pregunta es: se puede trabajar en excel este tipo de formula R(n) = A(n) + B(n) donde "n" es mi variable libre dentro de esta matriz de 3xn

    ResponderEliminar
  12. Buen dia sr. Adolfo. Mi pregunta es: se puede trabajar en excel este tipo de formula R(n) = A(n) + B(n) donde "n" es mi variable libre dentro de esta matriz de 3xn

    ResponderEliminar
  13. Necesito su ayuda Sr.Adolfo, si añadimos en excel un valor de celda 2 y 4, y arrastramos creara una lista que aumente con el mismo intervalo (+2). Es posible hacer esto para una formula que escribe datos de otra hoja de excel. Por ejemplo, =pr!F2 y =pr!F12, al arrastrar no aumenta el valor (+10). Es posible hacerlo de alguna manera? Muchas gracias por su ayuda y su labor.

    ResponderEliminar
  14. Hola Adolfo, necesito de tu ayuda.
    necesito una formula con sumar.si que al copiar la formula hacia abajo, me cambie las columnas a sumar sin necesidad de intervenir la formula.
    las columnas se cambian cuando copio la formula hacia el lado(horizontalemte), pero no lo hace cuando copio la firmula hacia abajo(verticalmente)

    ResponderEliminar