realizar - Cómo aplicar iterativamente una función a grupos de datos en Excel
iterar datos (3)
Así que soy bastante nuevo en el uso de Excel y es posible que haya omitido una pregunta como esta que ya fue respondida, pero lo que estoy tratando de hacer es medir las distancias entre los grupos de puntos en una gran hoja de Excel.
Intenté la forma estándar de escribir una función y arrastrarla hacia abajo, pero la forma en que cambia la función cuando la muevo hacia abajo no es lo que necesito y no puedo imaginar cómo podría solucionar esto, así que creo que una macro podría ser la solución.
Los datos son grupos de 8 mediciones con cada grupo con un objeto azul, rojo, amarillo, csome, superposición, verde, cian y completo. La coordenada XYZ de cada objeto se enumera en las primeras tres columnas y, utilizando una fórmula de distancia simple, trato de medir la distancia entre cada objeto y todos los demás objetos en su mismo grupo. P.ej. Azul a rojo, amarillo, Csome ... etc. Luego otra vez para Blue2 a Red2, Yellow2, Csome2 ... y Blue3 a Red3, Yellow3, Csome3 ... y así sucesivamente.
Sin embargo, cuando coloco la siguiente fórmula donde debería estar mi salida deseada e intento arrastrarla hacia abajo, obviamente me encuentro con algunos problemas. Para el primer Azul contra su grupo completo, comienzo con = SQRT ((A $ 2-A2) ^ 2 + (B $ 2-B2) ^ 2 + (B $ 2-B2) ^ 2) y lo arrastro hacia abajo para que pase por fila 2 versus filas 3,4,5,6,7,8 y 9. El problema surge cuando llego a la fila 10 que contiene Blue2. Esto no se debe comparar con Blue1, sino que comienza un nuevo grupo. ¿Cómo puedo hacer que el ciclo se reinicie en la fila 10 para que el nuevo grupo que comienza con Azul 2 se compare dentro de ese grupo, por ejemplo, el XYZ en la fila 10 a XYZ en 11, 12, 13 ...?
Position X Position Y Position Z Unit Category Collection Surpass Object
16.38 41.20 4436.18 um Surface Position Blue Selection
17.35 40.83 4436.17 um Surface Position Red Selection
15.93 40.62 4435.93 um Surface Position Yellow Selection
16.22 40.80 4436.03 um Surface Position Csome1
17.97 42.23 4435.46 um Surface Position Overlap
15.37 40.87 4436.03 um Surface Position Green Selection
15.44 40.04 4436.39 um Surface Position Cyan Selection
17.97 42.23 4435.46 um Surface Position Full 1
15.57 36.20 4435.75 um Surface Position Blue Selection 2
12.83 35.19 4435.33 um Surface Position Red Selection 2
15.76 37.25 4435.74 um Surface Position Yellow Selection 2
14.94 36.17 4435.65 um Surface Position Csome2
15.99 36.78 4436.34 um Surface Position Overlap 2
14.23 36.26 4435.57 um Surface Position Green Selection 2
13.61 36.96 4436.09 um Surface Position Cyan Selection 2
14.64 36.42 4435.80 um Surface Position Full 2
42.83 36.04 4435.47 um Surface Position Blue Selection 3
42.34 36.56 4435.63 um Surface Position Red Selection 3
42.25 36.87 4435.75 um Surface Position Yellow Selection 3
42.48 36.50 4435.62 um Surface Position Csome3
41.77 36.02 4435.67 um Surface Position Overlap 3
42.64 36.89 4435.73 um Surface Position Green 3
42.05 36.90 4436.21 um Surface Position Cyan Selection 3
42.34 36.53 4435.71 um Surface Position Full 3
33.75 33.13 4436.32 um Surface Position Blue Selection 4
34.99 33.12 4436.38 um Surface Position Red Selection 4
35.40 31.97 4436.38 um Surface Position Yellow Selection 4
34.75 32.58 4436.36 um Surface Position Csome4
39.03 35.24 4436.04 um Surface Position Overlap Selection 4
35.49 32.19 4436.49 um Surface Position Green Selection 4
36.14 32.35 4436.42 um Surface Position Cyan Selection 4
36.16 33.20 4436.30 um Surface Position Full 4
Aplicar algún VBA para recorrer las coincidencias hasta Completo con un comodín y restablecer la fila de inicio de la fórmula para cada iteración debería ser la mejor manera de hacerlo.
Sub Group_Formula()
Dim frmla As String, rw As Long, rws As Long, n As Long, cnt As Long
frmla = "=SQRT((A$×-A×)^2+(B$×-B×)^2+(B$×-B×)^2)"
With ActiveSheet ''<-set to worksheet name properly!
For cnt = 1 To Application.CountIf(.Columns("G"), "Full*")
rw = Application.Max(2, rw)
rws = Application.Match("Full*", .Cells(rw, "G").Resize(Rows.Count - rw, 1), 0)
.Cells(rw, "H").Resize(rws, 1).Formula = Replace(frmla, Chr(215), rw)
rw = rw + rws
Next cnt
End With
End Sub
Los resultados deben parecerse a los siguientes.
Debe manejar las referencias A$2 , B$2 con una función que calculará la fila. Puede usar funciones de referencia como INDEX , INDIRECT o OFFSET para lograr lo que necesita. Los dos últimos son volátiles, así que tenga en cuenta que recalculan cada vez que un valor cambia en su hoja. INDEX es más eficiente.
No está claro si sus datos incluyen el número de grupo porque los números al final de la fila son inconsistentes. Si son consistentes en sus datos, puede usarlos. Suponiendo que el número de grupo está en la columna I , esta fórmula INDEX devolverá la referencia A$2 correcta:
INDEX($A$2:$A$33,1+((I2-1)*8))
O con la función OFFSET :
OFFSET($A$2,(I2-1)*8,0)
De lo contrario, (sin usar el número de grupo) puede usar la función ROWS para producir un resultado similar:
INDEX($A$2:$A$33,1+QUOTIENT(ROWS(A$2:A2)-1,8)*8)
Los 8 en todas las fórmulas representan la cantidad de elementos en un grupo.
Y la fórmula completa sería:
=SQRT((INDEX($A$2:$A$33,1+QUOTIENT(ROWS(A$2:A2)-1,8)*8)-A2)^2+(INDEX($B$2:$B$33,1+QUOTIENT(ROWS(B$2:B2)-1,8)*8)-B2)^2+(INDEX($C$2:$C$33,1+QUOTIENT(ROWS(C$2:C2)-1,8)*8)-C2)^2)
Bueno, como nuevo usuario de Excel, has saltado directamente al maravilloso y desconcertante mundo de las fórmulas de matriz.
Primero necesitas preparar tus datos un poco. Todos esos 1s, 2s, 3s y 4s deben estar en una columna propia, todos aquellos en el grupo 1 necesitan un número, y todo debe ser nombrado de manera consistente (''Verde'' frente a ''Selección Verde''). Podríamos hacer que la fórmula sea aún más compleja para dar cuenta de estas idiosincrasias en sus datos, pero su limpieza hará que la comprensión y las fórmulas sean más fáciles.
Ahora colocamos el "Objeto de superación" del que queremos calcular la distancia desde la Celda I1 (en este caso, "Selección azul") y la siguiente fórmula en la celda I2 asegurándonos de mantener ctrl+shift+enter cuando lo ingresemos.
=SQRT((INDEX($A$2:$A$33,MATCH(1,($G$2:$G$33=I$1)*($H$2:$H$33=$H2),0))-$A2)^2+(INDEX($B$2:$B$33,MATCH(1,($G$2:$G$33=I$1)*($H$2:$H$33=$H2),0))-$B2)^2)
Esto nos dará la distancia entre las coordenadas xey para cada uno. Tendrá que ajustar la fórmula para agregar la coordenada z al cálculo si lo necesita.
Entonces, ¿qué es lo que hace la elegante fórmula de igualación de índices? En el primer caso, toma la celda de la columna a, que coincide tanto con el "Objeto de superación" en el encabezado de la columna (celda J$1 ) como con el grupo de la fila actual (celda $H2 )
Ahora puede completar los encabezados restantes y completar la fórmula hacia la derecha y hacia abajo para obtener sus matrices de diferencias