Necesito fórmula para coincidir rangos variables
Tengo una duda con excel a ver si alguien me pudiera echar una mano.. Es de una investigación que estamos realizando y tengo 150 sujetos con datos diferentes y cada sujeto es un excel.
Utilizo este archivo como planilla, con todas las fórmulas que necesito, y en este archivo desde la columna E hasta la L introduzco los datos nuevos de cada sujeto, desde la hoja DATE 1 hasta la hoja DATE 7. Ahora bien, los datos de cada sujeto tienen una extensión diferente y necesito cambiar todo el resto de columnas para hacerlas coincidir con este largo. Tengo que hacer varias cosas:
1) En el caso de las columnas M-AM y BC-BN, lo que tengo que hacer es arrastrar o borrar hasta el último valor colocado en K que corresponda.
2) El mayor problema lo tengo en las columnas AO-BB, ya que tengo que cambiar la fórmula a mano para que la celda hasta la que llegue, sea la última en la que existe un valor colocado en la columna K por ejemplo, en está =(SUMA(N(FRECUENCIA(SI($K$134:$K$1409<=9;FILA($K$134:$K$1409));SI($K$134:$K$1409>9;FILA($K$134:$K$1409)))=Hoja1!A1)))*Hoja1!A1 ---> Lo tengo así porque el último valor que tengo es en la fila 1409 (el valor que está en negrita sería el que tengo que cambiar); pero tengo que cambiarlo uno a uno si por ejemplo en otro sujeto es en K1100 donde tiene el último valor... Una vez que lo tengo cambiado en todas, arrastro desde la 134 hasta la 193.
3) Cambiar las fórmulas de la columna C hasta la última celda con valor colocada en K; en concreto en C1, C2, C3, C9, C10, C16, C17, C23, C24, C30, C31, C37, C38, C44, C45, C51, 52, C58, C59, C65, C66, C72, C73, C79, C80, C86, C87, C93, C94, y desde C119 hasta C130... Al igual que antes, la última celda donde tengo un valor es la que quiero coger.
Tengo que hacerlo con 150 archivos excel y me supone muchísimo tiempo ir haciéndolo a mano como les digo, quería saber si existe alguna fórmula que me ajuste el largo del rango, hasta donde está el último valor puesto, o forma de hacer una macro con VBA, para hacer esto de forma más automática..
Sin entender mucho lo que explicas, que es meridianamente complejo sin verlo, lo que necesitas son formulas que al contrario de lo normal o esperado devuelve un rango, y si agregamos contar o contara lo haces ajustado a los datos de cada rango. Paso a explicar...
DESREF https://exceltotal.com/la-funcion-desref-en-excel/ aqui te explican como funciona.... brevemente, es una formula que te devuelve un rango, o sea que si quisieras sumar un rango podrías escribirlo asi =suma(desref(a1;0;0;contar(a1:a35);1) significa que si hubiere datos en la columna A, parte de la celda A1, no se mueve ni en vertical ni en horizontal, por eso 0;0; y define cuantas celdas tiene el rango en vertical, contando las celdas con datos numericos (si hubieren textos usarías "contara"). Entonces en el ejemplo que te propongo si hubieren 15 datos en el rango A1:A35, desref devolvería un rango a sumar desde A1 a A15. Si lo escribes solito no es posible que excel te muestre un dato pues no es posible... si pudiere te devolvería A1:A15, me explico? no es muy intuitivo pero resulta útil especialmente con tu problema
Quizá con una macro podrías resolverlo usando el grabador de macros, con referencias relativas y usando "crtl + shift + flecha abajo", esto es selección continua entre la celda que esta activa y la ultima con datos. Te explico la herramienta pues la escritura del código seria muy compleja y especifica a tu caso
Muchas gracias por tu explicación! Pero no es exactamente eso lo que quiero..
En el ejemplo de esta fórmula:
(SUMA(N(FRECUENCIA(SI($K$134:$K$1409<=9;FILA($K$134:$K$1409));SI($K$134:$K$1409>9;FILA($K$134:$K$1409)))=Hoja1!A1)))*Hoja1!A1
Lo que quiero es que lo que tengo en negrita, se me vaya cambiando en función del número de celdas con valores que tenga, es decir en ese caso la última celda con valor sería K1409, pero en otro caso sería otro.. entonces si existe algo que me lo cambie automáticamente? Cómo quedaría la fórmula?
Gracias!!
Aun creo que desref es lo que necesitas... veo que usas referencias absolutas con $ delante de la columna... si deseas que el valor rango sea variable podrías dar nombre al rango y que este se ajuste a tu necesidad/circunstancia de cada tabla. ¿Suma si la frecuencia es menor que 9 y ahí determinas la fila en otra tabla? Estimo muy complejo y si entiendo bien lo podrías hacer de otra manera...
Gracias por responder!!
He probado ya con índice y con coincidir y me da errores... con desref no consigo aplicarlo bien, pero parece que tampoco me sirve.
Gracias igualmente!
Si te parece enviame el archivo, o la hoja en cuestión y vemos como puedo ayudarte.
1 respuesta más de otro experto
Es más rápido que una macro lo haga y más para 150 archivos además llenar de fórmulas las hojas de excel va a terminar haciendo el archivo muy pesado y lento, yo he hacho macros para que coloquen las fórmulas y luego los conviertan a valores planos sigustas pon un un correo te solicito tu archivo y veo que es exactamente lo que quieres así como tienes la explicación sin un ejemplo me cuesta trabajo adaptar una de mis macros a lo que ue necesitas.