Anónimo
Fórmula excel
Buenas tardes,
Tengo un libro excel con tres hojas de cálculo:
- Input (entrada de datos)
- Preparación (plantilla que trata los datos de la hoja Input)
- Salida (toma los datos de "Preparación" para ponerlos en un fichero de texto plano).
En la hoja Preparación, tengo una columna que hace referencia a una fila de la hoja Input. Las fórmulas que contienen son las siguientes:
=SI(Input!A$5=0;"";Input!A$5)
=SI(Input!D$5=0;"";Input!D$5)
=SI(Input!E$5=0;"";Input!E$5)
=SI(Input!$B$5=1;1;"")
=SI(Input!$C$5=1;1;"")
Mi pregunta es: ¿Hay alguna forma de copiar y pegar esas fórmulas en otras celdas de forma que hagan referencia a la fila 6,7, etc de la hoja Input? Es decir, copiar esa función, pero que tome los valores consecutivos de las filas.
Muchas gracias. Saludos!
Tengo un libro excel con tres hojas de cálculo:
- Input (entrada de datos)
- Preparación (plantilla que trata los datos de la hoja Input)
- Salida (toma los datos de "Preparación" para ponerlos en un fichero de texto plano).
En la hoja Preparación, tengo una columna que hace referencia a una fila de la hoja Input. Las fórmulas que contienen son las siguientes:
=SI(Input!A$5=0;"";Input!A$5)
=SI(Input!D$5=0;"";Input!D$5)
=SI(Input!E$5=0;"";Input!E$5)
=SI(Input!$B$5=1;1;"")
=SI(Input!$C$5=1;1;"")
Mi pregunta es: ¿Hay alguna forma de copiar y pegar esas fórmulas en otras celdas de forma que hagan referencia a la fila 6,7, etc de la hoja Input? Es decir, copiar esa función, pero que tome los valores consecutivos de las filas.
Muchas gracias. Saludos!
1 respuesta
Respuesta de eldata
1
El problema que tienes es que tienes fijo la parte de la dirección correspondiente a la fila Input! A$5 ($5 te fija la fila 5). Si tus fórmulas estarían todas en una misma fila (y no en una misma columna) solo deberías de quitar el $ y al copiar se mueven solas. Esta es la solución más fácil, pero entiendo que tienes las fórmulas en una columna.
Otra opción sería, por ejemplo si todas estas fórmulas estarían en la columna A, te reservas la fila 2 para poner el número de fila que quieres apuntar en cada columna (en A2 pondrías 5, para B2 6, o en la columna que deba de ir la siguiente fila).
Luego cambias todas tus fórmulas de de manera de dejar el 5 variable, la dirección la vamos a armar con la función INDIRECTO a la que le vamos a concatenar la parte fija (nombre de hoja, columna y el peso) ej: Input!A$ Al número de celda.
=SI(INDIRECTO("Input!A$"&A$2)=0;"";INDIRECTO("Input!A$"&A$2))
Al copiar esta celda hacia la derecha, por ejemplo columna B, te cambiará la columna donde buscará el numero de fila a B (lo mismo para C o cualquier otra letra). Dejamos fija la fila 2 ya que siempre tendremos el dato de la fila allí. Luego puedes ocultar esta fila.
Como verás con la segunda propuesta nos complicamos un poco, por lo que es mejor modificar tu formato para que se adapte a la primer solución que es mucho más natural (si es que se puede).
Otra opción sería, por ejemplo si todas estas fórmulas estarían en la columna A, te reservas la fila 2 para poner el número de fila que quieres apuntar en cada columna (en A2 pondrías 5, para B2 6, o en la columna que deba de ir la siguiente fila).
Luego cambias todas tus fórmulas de de manera de dejar el 5 variable, la dirección la vamos a armar con la función INDIRECTO a la que le vamos a concatenar la parte fija (nombre de hoja, columna y el peso) ej: Input!A$ Al número de celda.
=SI(INDIRECTO("Input!A$"&A$2)=0;"";INDIRECTO("Input!A$"&A$2))
Al copiar esta celda hacia la derecha, por ejemplo columna B, te cambiará la columna donde buscará el numero de fila a B (lo mismo para C o cualquier otra letra). Dejamos fija la fila 2 ya que siempre tendremos el dato de la fila allí. Luego puedes ocultar esta fila.
Como verás con la segunda propuesta nos complicamos un poco, por lo que es mejor modificar tu formato para que se adapte a la primer solución que es mucho más natural (si es que se puede).
Buenas tardes,
En primer lugar, muchas gracias por contestar tan rápido.
Lo siento, pero no veo la forma de poder aplicar esto a mi plantilla. He hecho la prueba con tu segunda solución pero en columnas. El problema que me encuentro es el siguiente: La plantilla tiene las fórmulas en una columna, por ejemplo A. Cada una de esas fórmulas está en una fila de esa columna, del 1 al 5. A partir de la fila 6 de esas fórmulas se repetirían otra vez hasta la fila 10 y así continuamente. Lo único que cambia es el número de fila de la hoja Input:
Fila Columna A
1 =SI(Input!A$4=0;"";Input!A$4)
2 =SI(Input!D$4=0;"";Input!D$4)
3 =SI(Input!E$4=0;"";Input!E$4)
4 =SI(Input!$B$4=1;1;"")
5 =SI(Input!$C$4=1;1;"")
6 =SI(Input!A$5=0;"";Input!A$5)
7 =SI(Input!D$5=0;"";Input!D$5)
8 =SI(Input!E$5=0;"";Input!E$5)
9 =SI(Input!$B$5=1;1;"")
10 =SI(Input!$C$5=1;1;"")
Si yo copio y pego las fórmulas de las columnas a partir de la fila 11, me deja fija la fila 4 (por el $). Si aplico tu método el problema es que me coge la referencia a partir de la celda en la que pego, me pasa esto:
Fila Columna A Columna B
1 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 4
2 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 5
3 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 6
4 =SI(INDIRECTO("Input!B$"&$B1)=1;1;"") 7
5 =SI(INDIRECTO("Input!B$"&$B1)=1;1;"") 8
6 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 9
7 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 10
8 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 11
9 =SI(INDIRECTO("Input!B$"&$B6)=1;1;"") 12
10 =SI(INDIRECTO("Input!B$"&$B6)=1;1;"") 13
En vez de coger la fila B2, coge B6 . No sé si esto se puede hacer de alguna forma.
Muchas gracias. Saludos!
En primer lugar, muchas gracias por contestar tan rápido.
Lo siento, pero no veo la forma de poder aplicar esto a mi plantilla. He hecho la prueba con tu segunda solución pero en columnas. El problema que me encuentro es el siguiente: La plantilla tiene las fórmulas en una columna, por ejemplo A. Cada una de esas fórmulas está en una fila de esa columna, del 1 al 5. A partir de la fila 6 de esas fórmulas se repetirían otra vez hasta la fila 10 y así continuamente. Lo único que cambia es el número de fila de la hoja Input:
Fila Columna A
1 =SI(Input!A$4=0;"";Input!A$4)
2 =SI(Input!D$4=0;"";Input!D$4)
3 =SI(Input!E$4=0;"";Input!E$4)
4 =SI(Input!$B$4=1;1;"")
5 =SI(Input!$C$4=1;1;"")
6 =SI(Input!A$5=0;"";Input!A$5)
7 =SI(Input!D$5=0;"";Input!D$5)
8 =SI(Input!E$5=0;"";Input!E$5)
9 =SI(Input!$B$5=1;1;"")
10 =SI(Input!$C$5=1;1;"")
Si yo copio y pego las fórmulas de las columnas a partir de la fila 11, me deja fija la fila 4 (por el $). Si aplico tu método el problema es que me coge la referencia a partir de la celda en la que pego, me pasa esto:
Fila Columna A Columna B
1 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 4
2 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 5
3 =SI(INDIRECTO("Input!A$"&$B1)=0;"";INDIRECTO("Input!A$"&$B1)) 6
4 =SI(INDIRECTO("Input!B$"&$B1)=1;1;"") 7
5 =SI(INDIRECTO("Input!B$"&$B1)=1;1;"") 8
6 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 9
7 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 10
8 =SI(INDIRECTO("Input!A$"&$B6)=0;"";INDIRECTO("Input!A$"&$B6)) 11
9 =SI(INDIRECTO("Input!B$"&$B6)=1;1;"") 12
10 =SI(INDIRECTO("Input!B$"&$B6)=1;1;"") 13
En vez de coger la fila B2, coge B6 . No sé si esto se puede hacer de alguna forma.
Muchas gracias. Saludos!
Vamos a hacer una cosa partiendo que tus "grupos" de fórmulas (las que aplican para el mismo número) siempre son de a 5 filas, y comienzan en la fila numero 1.
El número que al inicio es 4, luego 5, y ... lo calcularemos de la siguiente manera:
=(TRUNCAR((FILA()-1)/5)+4)
Pon esta fórmula en una hoja cualquiera en la fila uno, y cópiala hasta la fila POR y se supone que se va a ir incrementando cada 5 filas, partiendo desde el 4.
La primer fórmula (la que va en la fila 1, 6, ... ) tendrá lo siguiente:
=SI(INDIRECTO("Input!A$"&(TRUNCAR((FILA()-1)/5)+4))=0;"";INDIRECTO("Input!A$"&(TRUNCAR((FILA()-1)/5)+4)))
La segunda (que va en la fila 2, 7, ...) tendrá lo siguiente:
=SI(INDIRECTO("Input!D$"&(TRUNCAR((FILA()-1)/5)+4))=0;"";INDIRECTO("Input!D$"&(TRUNCAR((FILA()-1)/5)+4)))
Arma el resto de las fomrulas con la función indirecto y el número de fila variable.
El número que al inicio es 4, luego 5, y ... lo calcularemos de la siguiente manera:
=(TRUNCAR((FILA()-1)/5)+4)
Pon esta fórmula en una hoja cualquiera en la fila uno, y cópiala hasta la fila POR y se supone que se va a ir incrementando cada 5 filas, partiendo desde el 4.
La primer fórmula (la que va en la fila 1, 6, ... ) tendrá lo siguiente:
=SI(INDIRECTO("Input!A$"&(TRUNCAR((FILA()-1)/5)+4))=0;"";INDIRECTO("Input!A$"&(TRUNCAR((FILA()-1)/5)+4)))
La segunda (que va en la fila 2, 7, ...) tendrá lo siguiente:
=SI(INDIRECTO("Input!D$"&(TRUNCAR((FILA()-1)/5)+4))=0;"";INDIRECTO("Input!D$"&(TRUNCAR((FILA()-1)/5)+4)))
Arma el resto de las fomrulas con la función indirecto y el número de fila variable.