Combinar filas en una matriz Excel 2010
Tengo un problema en Excel 2010 que no se resolver.
Supongamos que tengo 3 filas de números(son observaciones que no siguen ningún criterio), con distintas longitudes:
1 2 15 17
2 14 37
3 17 75
Y necesito que se transforme en "una matriz 3xn" de el siguiente modo:
1 1 1 1 1 1 2 2 2 15 17
2 2 2 14 14 37 14 14 37 37 37
3 17 75 17 75 75 17 75 75 75 75
Es decir, todas las combinaciones distintas de columnas posibles (no deben
aparecer columnas repetidas), sin que aparezca un número superior
arriba de uno inferior.
1 Respuesta
Bueno una forma a mano, pero que no necesita macros y no es costosa en tiempo es hacer lo siguiente:
1. Hacer todas las combinaciones posibles. Para ello arrastras y pegas el 1 seis veces, lo mismo con cada elemento de la primera fila. En la segunda tres veces y en la ultima es repetir los valores. Con esto tenemos todas las combinaciones posibles.
2. Debajo de los tres valores de la primera columna que en mi caso quedaba en A5, A6 y A7 escribes:
=IF(AND(A7>A6;A6>A5);"si"; "no")
Y arrastras esta fórmula hasta el final de las columnas. Con ello tienes una fila nueva que esta llena de "si" y "no"., indicando las columnas que valen y las que no.
3. Si quieres filtrar o borrar puedes seleccionar la matriz completa de las cuatro filas copiar. Te pones en otra celda vacía bajo las filas rellenas y con pegado especial seleccionas la casilla trasponer antes de dar ok. Así tendrás la matriz traspuesta.
4. Seleccionas la columna que contiene "si" y "no" y eliges Filtro/Autofiltro para poder quedarte solo con las filas que contienen si. Luego puedes pegarlas a otro lado eliminar el resto...
Otra opción es hacer esto con una macro, claro está.
Hola. En primer lugar agradecerte tu solución, ya que es sencilla y muy útil para el ejemplo que he expuesto. El problema es que tengo otros casos con series mucho mas largas en las que me sería muy costoso el arrastrar y hacer todas las combinaciones posibles, ya que estaríamos hablando de cientos de columnas o miles. Si conoces otra forma de poder hacerlo de un modo mas "automatizado" te lo agradecería mucho.
Un saludo!
Sería hacer lo mismo pero con una macro. Para ello se necesito saber alguna de las condiciones de contorno. Por ejemplo, ¿el número de filas y columnas puede ser cualquiera (sobre todo el tema de limitar el número de filas ayudaría)? ¿La fila con más columnas es siempre la primera o hay que buscarla? ¿El rango N x M se indica en alguna parte o se debe calcular, (ver cuestiones anteriores)? ¿Todos los valores son números enteros?
Hola. El número de filas pongamos que es 3 y el de columnas no seria problema contarlas, así que pongamos 8, 12 y 10 columnas respectivamente. Supongo que no me supondrá un problema cambiar estas magnitudes posteriormente. La fila con mas columnas puede no ser siempre la primera. Y no, el rango N x M no esta calculado, ya que es parte del resultado que se espera obtener. Los valores son siempre efectivamente numeros enteros.
Saludos.
Aquí tienes el código. La macro a ejecutar es Main() ya que las otras son procedimientos y funciones para facilitar la comprensión y modificación del código. Algunas consideraciones:
- Solo vale para 3 filas a partir de la celda indicada
- No comprueba que existan las 3 filas
- No borra el rango a escribir. Hay que hacerlo a mano antes.
Verás que el código vale para cualesquiera número de columnas, pero si quieres más filas hay que modificarlo o buscar otra estrategia.
Option Explicit
Function CumpleCondicion(n() As Integer) As Boolean
'En el valor de indice = pone 1 si cumple y = si no
If (n(3) > n(2)) And (n(2) > n(1)) Then
n(0) = 1
CumpleCondicion = True
Else
n(0) = 0
CumpleCondicion = False
End If
End Function
Sub CalculaCombinaciones(c As Range)
'Calcula las combinaciones a partir de la matriz que empieza en c (celda)
'Siempre 3 filas
Dim maxc(3) As Integer 'Numero de elementos de cada fila, en = está el máximo de todos
Dim i As Integer 'indices por fila
Dim j As Integer
Dim k As Integer
Dim x As Integer 'indice por columna del resultado
Dim y As Integer
Dim t(3) As Integer 'tupla a comprobar
'Calculo de elementos de cada la fila y máximo
maxc(1) = c.End(xlToRight).Column
maxc(2) = c.Offset(1, 0).End(xlToRight).Column
maxc(3) = c.Offset(2, 0).End(xlToRight).Column
For i = 1 To 3
If maxc(0) < maxc(i) Then maxc(0) = maxc(i)
Next i
'Calculo de combinaciones y comprobación de tuplas
x = Selection.Row
y = Selection.Column
If (x <= c.Row + 2) And (y <= maxc(0)) Then
MsgBox ("Vas a machar los datos." & vbCrLf & "Selecciona otra celda fuera del rango de los datos")
Else 'podemos empezar a ver las combinaciones
For i = c.Column To maxc(1) 'primera fila
For j = c.Column To maxc(2) 'segunda fila
For k = c.Column To maxc(3) ' ultima fila
t(1) = Cells(c.Row, i)
t(2) = Cells(c.Row + 1, j)
t(3) = Cells(c.Row + 2, k)
If CumpleCondicion(t) Then 'este cumple y lo escribo
Cells(x, y) = t(1)
Cells(x + 1, y) = t(2)
Cells(x + 2, y) = t(3)
'actualizo columna
y = y + 1
End If
Next k
Next j
Next i
End If
End Sub
Sub Main()
Call CalculaCombinaciones(Range("A1"))
End Sub
