Arrastrar referencias relativas de 3 a 3

8 envíos / 0 nuevos
Último envío
chronot
Imagen de chronot
Offline
última acción: Hace 4 años 7 meses
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 30/01/2011 - 22:33
Puntos: 2727
Arrastrar referencias relativas de 3 a 3

¡Buenos días!

Me surgió la siguiente duda y quisiera consultarles la mejor forma de realizar en Excel:

 

Estoy calculando el promedio trimestral de una serie de datos mensuales, que abarca varios años, en una hoja separada llamada "Trimestral", es decir, los datos mensuales se encuentran en una hoja y el promedio trimestral estoy calculando en otra hoja.

El primer promedio trimestral del año se calcula así en la celda B4: =PROMEDIO(Mensual!B4:D4) b4: enero, c4: febrero y d4: marzo de la hoja "Mensual"

 

Al arrastrar esta formula a la celda C4 (para calcular el promedio del 2º trimestre), la fórmula queda así: =PROMEDIO(Mensual!C4:E4)

 

Quisiera que la fórmula de la celda C4 quedé así: =PROMEDIO(Mensual!E4:G4) e4: abril, f4: mayo y g4: junio de la hoja "Mensual", es decir, que aumente 3 columnas en relación a cada referencia de la celda B4 (promedio del primer trimestre) y así sucesivamente para calcular el promedio trimestral durante varios años.

 

Les agradezco inmensamente su colaboración de siempre

 

Etiquetas: 

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Re: Arrastrar referencias relativas de 3 a 3

Se me ocurre que puedes hacer el promedio en una de cada tres filas. Así puedes arrastrar hasta el final. Puedes utilizar una columna de apoyo en la que pongas 1 al lado de la primera fórmula, nada donde haya espacios, 2 en la siguiente fórmula, etc. Esto servirá para mantener el orden posteriormente.

 

Adjunto una imagen para ilustrar estos pasos.

 

Después deberías convertir las referencias de relativas (que te hacía falta para poder arrastrar) a absolutas. Esto se puede hacer mediante una macro sencilla:

 

For Each cell In Application.Selection
  addr = Worksheet.Name & "!" & cell.Address
  If Range(addr).HasFormula = True Then Range(addr).Formula = _
  Application.ConvertFormula(Formula:=Range(addr).Formula, _
  fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)

Next cell


Esta macro la puedes vincular a un botón que pongas en la hoja. Funciona si seleccionas la columna donde tienes la fórmula. Si solo quieres aplicarla a un rango, basta con seleccionar el rango a mano antes de ejecutar la macro.

 

Una vez hecho esto, las referencias ya son absolutas. Ahora selecciona las columnas que hemos creado (la de la fórmula y la de los números correlativos) y en la pestaña de Datos la ordenas respecto a la columna de los números.

Terminado esto, ya puedes eliminar la columna de apoyo (la de los números) y tendrás las fórmulas del promedio trimestral una detrás de otra.

 

 

Es un poco lioso, pero resuelve el problema.

 

 

Saludos

AdjuntoTamaño
Image icon excel.png33.19 KB
chronot
Imagen de chronot
Offline
última acción: Hace 4 años 7 meses
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 30/01/2011 - 22:33
Puntos: 2727
Re: Arrastrar referencias relativas de 3 a 3

¡Muchísimas gracias Cron por tu respuesta! Adjunto un ejemplo con los datos de muestra, he calculado los promedios cada 3 celdas y así puedo arrastrar sin ningún problema.

¿Es posible ordenar los datos si están dispuestos en forma horizontal?

La verdad que no tengo ninguna experiencia en el tema de macros como para insertar el botón... no sé cómo hacerlo.

 

AdjuntoTamaño
File libro1.xlsx8.5 KB
Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Re: Arrastrar referencias relativas de 3 a 3

Te adjunto la cosa con un botón y la macro.

 

Funcionaría solo si las fórmulas están en la fila 4.

 

En cuanto a lo de ordenar, en la ventana que se te abre hay un botón de Opciones que te permite elegir entre ordenar columnas o filas. Eso sí, para las filas no te permite marcar la casilla de "los datos contienen encabezados", por lo que no podrás seleccionar la fila entera, sino solo los datos para que no te "desordene" el título de la primera columna.

 

 

Un saludo

AdjuntoTamaño
File libro1.xlsm16.67 KB
chronot
Imagen de chronot
Offline
última acción: Hace 4 años 7 meses
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 30/01/2011 - 22:33
Puntos: 2727
Re: Arrastrar referencias relativas de 3 a 3

Te agradezco mucho Cron por tu tiempo en responderme.

¿Cómo hiciste para calcular el promedio en celdas adyancentes y que mueva la referencia de tres en tres columnas? ¿O es un procedimiento manual para cambiar las referencias, fórmula por fórmula?

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Re: Arrastrar referencias relativas de 3 a 3

Lo he hecho de manera manual.

 

Lo primero es hacer lo que has hecho tú. A la vez que ponía las fórmulas, en la fila siguiente ponía un contador (1, 2, 3...). Esto se puede arrastrar como hiciste con las fórmulas, dejando dos celdas vacías cada tres columnas.

Luego hago referencias absolutas para que no varíen los cálculos.

 

Por ejemplo, si en la celda la fórmula es

=PROMEDIO(A1:C1)

Lo que hago es sustituirlo por

=PROMEDIO($A$1:$C$1)

De esta manera, al mover de lugar las celdas, no variará el cálculo, pero en caso de variar los datos de entrada, rehará el cálculo correctamente.

Esta sustitución la he hecho mediante la macro que te he mandado. Si fueran pocas celdas, se podría hacer a mano. A medida que aumenta el número de celdas, va siendo hora de hacer trabajar a Excel por nosotros.

 

Una vez hecho esto, selecciono desde la celda B4 hasta la última con datos en la fila 5, y utilizo la opción Datos|Ordenar

En la ventanita que se te abre, en Opciones marco "ordenar de izquierda a derecha", y en "ordenar por" selecciono "fila 5" para ordenar según los números que puse bajo las fórmulas. De esta manera, las fórmulas no se desordenan.

 

Es un método un poco pedestre, pero puede funcionar.

 

 

Saludos

 

chronot
Imagen de chronot
Offline
última acción: Hace 4 años 7 meses
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 30/01/2011 - 22:33
Puntos: 2727
Re: Arrastrar referencias relativas de 3 a 3

¡Excelente, Cron, muchísimas gracias! El comando Ordenar de izquierda a derecha hace exactamente lo que necesitaba, que es juntar los promedios en celdas adyacentes.

Me gustaria recibir tus recomendaciones y consejos para aprender sobre macros, me sería tan útil como en este caso, quien sabe cuántas situaciones podría optimizar.

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 2 días
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Re: Arrastrar referencias relativas de 3 a 3

De nada.

 

Si necesitas saber sobre macros no tienes más que preguntar en el subforo correspondiente. Responderé en la medida de mis posibilidades, pero hay aquí gente que sabe muchísimo más que yo de la que se puede aprender mucho.

Si te interesa, puedes echar un vistazo al foro, porque ya hay temas muy interesantes que te pueden dar muchas ideas.

 

Un saludo