Cálculo de promedios en VBA

Ivette buen día;

Sólo una duda, ¿no sería más sencillo el usar la fórmula de promedio? ¿O me indicas qué otra implicación tiene este requerimiento?

Hola Kike Gutierez, gracias por tu correo. Es eso lo que deseo hacer. Te doy más elementos de lo que estoy intentando preguntar. Tengo por ejemplo una tabla formada por 30 filas y 5 columnas. La primera de las columnas es un ordinal del 1 al 30. Deseo calcular el promedio de los 4 datos que aparecen en las filas y que el resultado se escriba en la que seria la sexta columna, que se llamaría Promedios.

Este seria una paso más dentro de lo que deseo en total.

Como estoy aprendiendo ahora VBA, estoy trabada a la hora de escribir correctamente la sintaxis. Desearia hacer un For to...

Tengo un ejemplo de lo que he echo, que seguro esta muy mal, porque no me funciona, pero a lo mejor ayuda a transmitir lo que yo quisiera. ¿Te lo puedo pegar aqqui mismo?

Gracias

Me ayudaría muchísimo que me compartieras lo que estás haciendo.

Fejoal, he visto en mi bandeja de entrada que usted me ha enviado una respuesta y por algún motivo no la puedo ver. ¿Podría re enviarla por favor? Es muy importante para mi. Gracias,

Ivette

30.08.16

Efectivamente, Ivette, hay muchas formas.

La siguiente deja en la celda activa el promedio del rango indicado:

    ActiveCell.Value = Application.WorksheetFunction.Average(Range("A2:D2"))

O si quieres que quede en otra celda, por caso H8, sería como esto

Range("H8").Value = Application. WorksheetFunction. Average(Range("A2:D2"))

Si, eventualmente, fuera un rango discontinuo - no es este el caso- deberías especificarlo como sigue:

ActiveCell.Value = Application.WorksheetFunction.Average(Range("A1"), Range("B1"), Range("C1"), Range("D1"))

Bien, espero que te sea de ayuda.

Saludos

Fernando

.

Gracias!

Para dejar de molestar por hoy, como puedo hacer para que los promedios que se calcularn así, y que completaron una columna, se escriban además en otra adyacente, ¿pero organizados de menor a mayor?

Infinitas gracias por su paciencia y su cooperación. Me resulta muy útil y me permite aprender.

.

Un placer, Ivette

Imagino que lo que quieres son dos operaciones:

1.- Colocar los promedios a la derecha de cada fila de números.

2.- Ordenar la tabla de menor a mayor promedio.

Supuse que tu hoja luciría -previo a la ejecución de la rutina- como muestra esta figura:

Si así fuera, accede al Editor de VBA (Atajo: Alt + F11), inserta un módulo - si no tuvieras uno ya- y pega el siguiente código:

Sub PonePromOrd()
'IVETTE, Indica la celda donde empiezan los títulos/encabezados
CeldaIniTit = "A1"
ColProm = Range(CeldaIniTit).CurrentRegion.Columns.Count - 1
UltFila = Range(CeldaIniTit).CurrentRegion.Columns.Rows.Count - 1
NumForm = "#" & Application.ThousandsSeparator & "###" & Application.DecimalSeparator & "00"
For FilaAct = Range(CeldaIniTit).Offset(0).Row To UltFila
    ElRango = Range(Range(CeldaIniTit). Offset(FilaAct, 0), Range(CeldaIniTit). Offset(FilaAct, ColProm - 1)). Address
    Range(CeldaIniTit). Offset(FilaAct, ColProm).Value = Application. WorksheetFunction. Average(Range(ElRango))
    Range(CeldaIniTit).Offset(FilaAct, ColProm).NumberFormat = NumForm
Next
' ordenamiento de la base:
Set RangTit = Range(CeldaIniTit, Range(CeldaIniTit).Offset(0, Range(CeldaIniTit).CurrentRegion.Columns.Count - 1))
UltFila = RangTit.CurrentRegion.Rows.Count
Range(CeldaIniTit).Select
RangTit.CurrentRegion.Sort Key1:=Range(CeldaIniTit).Offset(0, ColProm), Order1:=xlAscending, Header:=xlYes
Set RangTit = Nothing
End Sub

Verás una opción para cambiar la celda donde empiezan los títulos de tu cuadro. Reemplazalo por otro si no fuera "A1"

Le hubiera hecho algunas mejoras a la rutina, pero a los efectos didácticos -como sé que quieres aprender- la dejé más básica.

Ejecutada la macro me quedó el siguiente resultado:

Como verás, colocó el promedio de cada línea, lo redondeó a dos decimales y ordenó la tabla.

Espero que sea lo que buscabas.

Saludos

Fernando

.

SI es lo que queria.

Quiero compartirle una imagen para mostrarle lo que me pasa cuando intento calcular al menos uno de los promedios, el primero.

Lo deseado es que se genere una estructura como la que le describi, como la de su ultima respuesta, n_lab es el numero de filas (declaradas en option explicit al inicio) y las columnas son siempre las mismas (como las que se crean al correr la primera parte de mi macro). Deseo que la 5ta columna contenga los promedios y una sexta, los contenga ordenados.

Por favor, tengame paciencia, intento adaptar lo que me explica a mi problema. Deseo comprender realmente lo que escribo en VB.

Quisiera que me corrija mi "intento de macro".

Muy agradecida,

Ivette

Por cierto, como es realmente importante que la primera columna siga siempre fiel a la de los promedios, aunque estos estén ordenados en la séptima, quizá es más razonable que yo lograra re escribir estas dos columnas, o sea, la que contiene los ordinales y la de los promedios, para que cuando estos se ordenen mantengan sus relaciones iniciales (no se si lo dije bien, la cosa es que cada promedio ya en orden manenga a su lado el código que le correposndia antes de ser ordenado). Es esa pareja de datos la que me permitiría continuar mis cálculos a la macro siguiente...

Gracias otra vez, un beso por tanta paciencia de leer mis trabalenguas,

Ivette

.

Hola, Ivette

Hubiera ayudado que pegaras el código como texto o usando el símbolo <> de la barra de herramientas. Además, no reconozco el código que te mandé en lo que me estás mostrando.

De todos modos, a simple vista no alcanzo a ver de dónde toman el valor las variables n_lab y n_fr, límites del For... Next. Imagino que se declaron públicas en alguna otra rutina.

Ahora bien, si llegó hasta la linea:

Range("F3").Value = Application.WorksheetFunction.Average(Range("B3:E3"))

Quiere decir que funciona.

El error se da, no por la instrucción en sí, sino porque la función promedio de ese rango en particular, sea porque está vacío o porque tiene caracteres numéricos.

Para resolverlo podrías ponerle una protección para que continúe la ejecución a pesar del error.

Una estructura como esta:

On Error Resume Next
    Range("F3").Value = Application.WorksheetFunction.Average(Range("B8:E8"))
On Error GoTo 0

Nota en la imagen que te envié que las lineas contienen número en todas las filas.

Espero que con esto lo resuelvas.

Respecto a tu segunda consulta no sé si entendí bien lo de la primera columna siga fiel, porque los promedios -interpreté- eran los que definían el orden de la planilla.

Tal vez quieras que primero ordene por la primera columna y luego por la de los promedios.

En tal caso, sólo reemplaza la línea:

RangTit.CurrentRegion.Sort Key1:=Range(CeldaIniTit).Offset(0, ColProm), Order1:=xlAscending, Header:=xlYes

por la siguiente:

RangTit.CurrentRegion.Sort Key1:=Range(CeldaIniTit).Offset(0, 0), Order1:=xlAscending, Key2:=Range(CeldaIniTit).Offset(0, ColProm), Order1:=xlAscending, Header:=xlYes

Quedando, entonces, la planilla de esta manera:

Si no te hubiera interpretado correctamente, escríbeme de nuevo.

Tal vez una imagen de tu planilla me ayude a interpretar lo que necesitas.

Saludos

Fer

Pd: lamento mucho lo que tarda este sitio en actualizar las preguntas/respuestas.

Hace parecer que uno no quiere responder. En fin, es lo que hay.

Hola Fermando, gracias otra vez por su respuesta. No había podido volver al sitio. Lo siento por no haber pegado el código que envíe ayer como texto. También tengo escasa experiencia en el uso del sitio. Discúlpeme por favor.

Usted no reconoce el código que me envío porque aunque por supuesto que funciona perfectamente, me resulta elevado y complejo. Es muy gracioso de hecho, porque como usted sabe tanto, me ha enviado sus códigos "de manera didáctica", y para mi están en "modo avanzadisimo". Lo que pasa es que yo estoy muy al inicio, y no quiero renunciar a comprender lo que hago, así que aunque voy muy lentamente, intento caminos alternativos, para de alguna manera familiarizarme con el lenguaje a la vez que resuelvo el problema en cuestión. Por favor, tengame paciencia. Si el sitio se demora seguramente no es su culpa, sino el tipo de conexión que yo tengo. Pero yo espero sus repuestas, no importa cuanto se demoren, porque me son bien 'útiles. Le estoy enviando a continuación, mi intento de rutina, completa, y un ejemplo de que como quiero que me quede.

Gracias otra vez,

Ivette

?Como puedo enviarle un pequeño archivo de 26k conteniendo mi ejemplo?

Option Explicit

Dim n_lab As Integer
Dim n_fr As Integer
Dim n_repl As Integer
Dim redondeo As Integer

Sub Carga_de_datos()

' Macro que carga los parametros que el usuario declara en la hoja Inicio

n_lab = Worksheets("Inicial").Range("B4").Value
n_fr = Worksheets("Inicial").Range("B5").Value
n_repl = Worksheets("Inicial").Range("B6").Value

End Sub

Sub Prepara_hoja_de_datos()
' Macro que prepara la hoja de datos, borrando primeramente lo que tuviera y colocando los titulos a las lineas y columnas
' El subprograma anterior (carga de datos) debe haberse ejecutado antes.

' Declaracion de variables especificas en este subprograma
Dim I As Integer
Dim J As Integer
Dim contador As Integer
' Preparacion de la Hoja de datos crudos
Call Carga_de_datos
Worksheets("Datos Crudos").Cells.Clear
For I = 1 To n_lab
Worksheets("Datos Crudos").Cells(I + 2, 1).Value = I
Next I
contador = 2
For I = 1 To n_fr
For J = 1 To n_repl
Worksheets("Datos Crudos").Cells(1, contador).Value = "Frasco " & I
Worksheets("Datos Crudos").Cells(2, contador).Value = "Répl " & J
contador = contador + 1
Next J
Next I
Worksheets("Datos Crudos").Cells(2, 6).Value = "Promedio"
' en este momento debo copiar manualmente la matriz de datos, en el futuro la insertare con una instruccion VBA
' Eso para que la siguiente macro se pueda ejecutar

End Sub

Sub Prom_repl()
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim I As Integer
Dim J As Integer
Dim PromLab As Double
Call Carga_de_datos
' Para calcular los promedios de las filas

J = 2
For I = 1 To n_lab
A = Worksheets("Datos Crudos").Cells(I + 2, J).Value
B = Worksheets("Datos Crudos").Cells(I + 2, J + 1).Value
C = Worksheets("Datos Crudos").Cells(I + 2, J + 2).Value
D = Worksheets("Datos Crudos").Cells(I + 2, J + 3).Value

PromLab = (A + B + C + D) / 4
Worksheets("Datos Crudos").Cells(I + 2, 6).Value = PromLab
Next I
End Sub
Sub prepara_AlgoA()
Dim I As Integer
' Adicionar una primera columna ordinal
For I = 1 To n_lab
Worksheets("AlgoA").Cells(I + 1, 1).Value = I
Next I
' titular la segunda columna, como xi

Worksheets("AlgoA").Cells(1, 2).Value = "xi"
' Para copiar cada valor de promedio obtenido en la hoja Datos Crudos hacia la celda 2,2 de la hoja AlgoA
For I = 1 To n_lab
Sheets("Datos Crudos").Activate
Range(Cells(I + 2, 6), Cells(n_lab + 2, 6)).Select
Selection.Copy
Sheets("AlgoA").Activate
Range(Cells(I + 1, 2), Cells(I + 1 + n_lab, 2)).Select
ActiveSheet.Paste
Next I
' Siguiente: Ordenar la columna de datos en la hoja AlgoA de menor a mayor, titularla.

End Sub

Option Explicit
    Dim n_lab As Integer
    Dim n_fr As Integer
    Dim n_repl As Integer
    Dim redondeo As Integer
Sub Carga_de_datos()
' Macro que carga los parametros que el usuario declara en la hoja Inicio
    n_lab = Worksheets("Inicial").Range("B4").Value
    n_fr = Worksheets("Inicial").Range("B5").Value
    n_repl = Worksheets("Inicial").Range("B6").Value
End Sub
Sub Prepara_hoja_de_datos()
' Macro que prepara la hoja de datos, borrando primeramente lo que tuviera y colocando los titulos a las lineas y columnas
' El subprograma anterior (carga de datos) debe haberse ejecutado antes.
' Declaracion de variables especificas en este subprograma
    Dim I As Integer
    Dim J As Integer
    Dim contador As Integer
    ' Preparacion de la Hoja de datos crudos
        Call Carga_de_datos
        Worksheets("Datos Crudos").Cells.Clear
    For I = 1 To n_lab
        Worksheets("Datos Crudos").Cells(I + 2, 1).Value = I
    Next I
        contador = 2
    For I = 1 To n_fr
    For J = 1 To n_repl
        Worksheets("Datos Crudos").Cells(1, contador).Value = "Frasco " & I
        Worksheets("Datos Crudos").Cells(2, contador).Value = "Répl " & J
        contador = contador + 1
    Next J
    Next I
        Worksheets("Datos Crudos").Cells(2, 6).Value = "Promedio"
' en este momento debo copiar manualmente la matriz de datos, en el futuro la insertare con una instruccion VBA
' Eso para que la siguiente macro se pueda ejecutar
End Sub
Sub Prom_repl()
    Dim A As Double
    Dim B As Double
    Dim C As Double
    Dim D As Double
    Dim I As Integer
    Dim J As Integer
    Dim PromLab As Double
    Call Carga_de_datos
' Para calcular los promedios de las filas
    J = 2
    For I = 1 To n_lab
        A = Worksheets("Datos Crudos").Cells(I + 2, J).Value
        B = Worksheets("Datos Crudos").Cells(I + 2, J + 1).Value
        C = Worksheets("Datos Crudos").Cells(I + 2, J + 2).Value
        D = Worksheets("Datos Crudos").Cells(I + 2, J + 3).Value
        PromLab = (A + B + C + D) / 4
        Worksheets("Datos Crudos").Cells(I + 2, 6).Value = PromLab
    Next I
  End Sub
  Sub prepara_AlgoA()
  Dim I As Integer
' Adicionar una primera columna ordinal
    For I = 1 To n_lab
        Worksheets("AlgoA").Cells(I + 1, 1).Value = I
    Next I
' titular la segunda columna, como xi
    Worksheets("AlgoA").Cells(1, 2).Value = "xi"
' Para copiar cada valor de promedio obtenido en la hoja Datos Crudos hacia la celda 2,2 de la hoja AlgoA
    For I = 1 To n_lab
    Sheets("Datos Crudos").Activate
    Range(Cells(I + 2, 6), Cells(n_lab + 2, 6)).Select
    Selection.Copy
    Sheets("AlgoA").Activate
    Range(Cells(I + 1, 2), Cells(I + 1 + n_lab, 2)).Select
    ActiveSheet.Paste
    Next I
' Siguiente: Ordenar la columna de datos en la hoja AlgoA de menor a mayor, titularla.
End Sub

Es así, ¿cierto? Es esta menera la mejor, ya no se me olvida más.

Bueno, hoy si que esta super lento el sitio. Al menos hoy si aprendí a colocar mis códigos en el símbolo <> como se debe... algo es algo.

Si llegara a tener tiempo para decirme porque la ultima parte no me funciona, le agradezco.

Saludos,

Ivette

No se como enviarle el archivo de excel. Seguro es algo evidente y no me doy cuenta. ¿Indíqueme como puedo hacer?