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)
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:
ResponderEliminar=SUMA(INDIRECTO("B4"):INDIRECTO("B"&D5+3))
y debe ser:
=SUMA(B4:INDIRECTO("B"&D5+3))
espero tu respuesta
Hola Otoniel.
ResponderEliminarLa fórmula que propones también funciona. Cualquiera de las dos son buenas.
Un cordial saludo.
En primer lugar, mi enhorabuena y gratitud, llevo tiempo siguiendo sus artículos y aplicándolos. Y ahora mi consulta:
ResponderEliminar¿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
Hola Sebastian.
ResponderEliminarHe 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.
Gracias, por la solución y por tan rápida respuesta.
ResponderEliminarHola 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
ResponderEliminarExcelente, 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!
ResponderEliminarEs increible lo de esta función, me acaba de solucionar un problema que tenía.
ResponderEliminarNo 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
Hola Adolfo
ResponderEliminarBuen 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
Hola Adolfo
ResponderEliminarFeliz 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
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
ResponderEliminarBuen 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
ResponderEliminarNecesito 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.
ResponderEliminarHola Adolfo, necesito de tu ayuda.
ResponderEliminarnecesito 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)
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.
ResponderEliminarMuchas gracias.
Buen post, gracias por el apoyo que brindas y por favor necesito ayuda.
ResponderEliminarQuiero 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.
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.
ResponderEliminarAgradezco su ayuda
Saludos cordiales.
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.
EliminarEste 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.
Hola Adolfo, buen día y muchas gracias por tu orientación, la pondré en practica y te comento los resultados.
EliminarSaludos cordiales
Adolfo me ayudas con una fórmula que me cambie automatic un rango cuando encuentre una celda vacía, ejemplo:
ResponderEliminarTengo 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
Estimados,
ResponderEliminarLo 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,
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:
Eliminar+SUMA(B1:INDIRECTO(CONCATENAR("F";FILA(B1);"C")&COLUMNA(A1)+7;0))
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
ResponderEliminarHOLA!!!
ResponderEliminarYO 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...
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!
ResponderEliminarPuede que no funcione con otros archivos como el desref ??
Slds
Gracias me sirvió un mundo, son lo máximo.
ResponderEliminar