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)

26 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
  15. Excelente blog, tengo bastante conocimeinto en Excel pero me gusta buscar información constantemente para se cada vez mejor. me parece maravilloso que compartas tu conocimiento.
    Muchas gracias.

    ResponderEliminar
  16. Buen post, gracias por el apoyo que brindas y por favor necesito ayuda.
    Quiero hacer una suma de rangos variables (que sumen la cantidad de valores dentro de un rango que puedo modificar, llamese intervalo), ejem: 1-12 meses del año, que sumen los mes incluidos en 4-9 y que estos se puedan modificar.

    ResponderEliminar
  17. Buenas tardes, tengo una matriz con una columna en la que tengo varias celdas repetidas, en celdas no adyacentes, para diferenciarlas a fin de poder utilizar la formula BUSCARV las numero consecutivamente. Ej. Costos1, Costos2, etc. El problema que tengo es que al ingresar al formula esta me toma el Valor_buscado como “Costos1” y así al arrastrarla la celda se pierde el consecutivo que necesito para poder tomar los datos de las celdas respectivas. Esto hace el trabajo muy dispendioso pues debo modificar las formulas manualmente Costos1 por Costos2 y así sucesivamente. Si tenemos en cuenta que debo alimentar con esta matriz varias hojas y muchas celdas es muy difícil y además corro el riesgo de editar alguna de forma incorrecta. ¿Mi pregunta es como debo ingresar mi variable Costos1 dentro de la fórmula para que sea consecutiva? O por el contrario debo utilizar otra función para este trabajo.

    Agradezco su ayuda

    Saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos. Si usas la función BUSCARV y tienes una tabla con varias columnas puedes indicar dentro de la función BUSCARV qué columna quieres utilizar. Para automatizar el proceso al copiar un truco sencillo es numerar las columnas. Sobre Costos1 pon un 1, sobre Costos2 pon un 2, y así sucesivamente. Luego en la función BUSCARV cuando tengas que indicar la columna indicas ese número y sumas 1, ya que la columna Costos1 es la columna 2, y la Costos2 es la columna 3, debido a que la columna 1 estará formada por los valores que deseas buscar. Este sistema te permitirá copiar la fórmula BUSCARV sin necesidad de editarla y cambiar sus parámetros.
      Este método admite otras variaentes más refinadas pero tal como te lo he descrito funciona bien.
      También puedes usar la función DESREF o la función INDICE.
      Espero que esto te solucione el problema planteado.
      Un saludo.

      Eliminar
    2. Hola Adolfo, buen día y muchas gracias por tu orientación, la pondré en practica y te comento los resultados.

      Saludos cordiales

      Eliminar
  18. Adolfo me ayudas con una fórmula que me cambie automatic un rango cuando encuentre una celda vacía, ejemplo:

    Tengo una table dinámica con subtotales y necesito que cada que haya un Nuevo inicio de datos, el modifique el rango hasta llegar nuevamente al subtotal

    ResponderEliminar
  19. Estimados,
    Lo expuesto en este artículo me sirvió mucho para crear un archivo con datos anuales, capaz de visualizar múltiples datos acumulados de acuerdo a la orden de una sola celda. A la vez también me dio mucho trabajo ya que cuando las bases de datos son grandes es necesario escribir celda por celda la referencia "F1C". Después de un año tipeando muchos números se me ocurrió probar con la función concatenar y la función fila, efectivamente funcionó por lo que me siento en la obligación de devolver la mano a este blog.

    La formula queda de la siguiente forma, tomaré como ejemplo la suma hipotética desde la celda B1 hasta el 7mo dato:

    +SUMA(B1:INDIRECTO(CONCATENAR("F";FILA(B1);"C")&1+7;0))

    Donde 1 indica el número de columna anterior al inicio de la suma.

    Saludos,

    ResponderEliminar
    Respuestas
    1. De hecho la referencia a la columna también se podría asociar a una función, con lo cual no es necesario reescribir ésta si es que se copia en otra celda. La formula quedaría de la siguiente forma:

      +SUMA(B1:INDIRECTO(CONCATENAR("F";FILA(B1);"C")&COLUMNA(A1)+7;0))

      Eliminar
  20. Si me pueden ayudar tengo un rango de celdas que obtienen datos en forma variable de acuerdo al dia, por ejemplo Dia 1 me puede dar 3, Dia 2 0, Dia 3 5, lo que quiero es alguna formula que vaya acumulando los resultados que voy pasado dia por dia en otra celda. Espero su ayuda por favor

    ResponderEliminar
  21. HOLA!!!
    YO UTILIZO INDIRECTO MAS COINCIDIR, PARA EXTRAER DATOS ESPECIFICOS DE UN BASE DE DATOS A OTRA, PERO AHORA QUIERO SABER COMO PUEDO SUMAR LOS DATOS CINCIDENTES (CONCEPTOS) DEL DATO ESPECIFICO,

    DE ANTEMANO GRACIAS...

    ResponderEliminar
  22. Buenas tardes , crei que me solucionaba un problemon la formula indirecto hasta que probé usarla extrayendo la información de otro excel y me dió valor!
    Puede que no funcione con otros archivos como el desref ??
    Slds

    ResponderEliminar
  23. Gracias me sirvió un mundo, son lo máximo.

    ResponderEliminar