miércoles, 7 de enero de 2015

Mezclando datos en Excel.

Un compañero me planteó la manera de conseguir mezclar los datos de dos grupos diferentes (frutas y colores):

como puedo con macro o función combinar cada valor de la primera columna con todos los valores de la segunda columna, es decir combinar el primer valor de la primera columna con todos los de la segunda columna, luego pasar al segundo valor de la primera columna y combinarlo con todos los de la segunda y asi sucesivamente hasta tener en una nueva columna todos los valores resultantes de combinar cada uno de los valores de la primera columna con TODOS  y cada uno de los valores de la segunda columna, con un espacio por medio claro...


Estos son nuestros datos de partida:

Mezclando datos en Excel.



El objetivo es sencillo, para cada elemento del primer grupo combinarlo con el del segundo...
Como en el primero tenemos 8 elementos y el segundo 5, deberemos llegar a 40 (8 x 5) nuevos elementos:

Mezclando datos en Excel.



Comenzamos entonces el ejercicio.
En primer lugar en la columna D (en la celda D1) insertamos la siguiente fórmula, que arrastramos 40 filas abajo:
=(MULTIPLO.INFERIOR.MAT(FILA()-1;CONTARA(Tabla1[dato2]))/CONTARA(Tabla1[dato2]))+1

lo que conseguimos con esta fórmula es arrastrar tramos/intervalos tomados de 5 en 5 (determinado por CONTARA(Tabla1[dato2]) ), que es el número de elementos del segundo grupo (colores), llegando hasta el valor 8.
Adicionalmente, en la columna E, incluimos la fórmula:
=INDIRECTO(DIRECCION(D1+1;1))
con la que retornamos el valor de de la celda correspondiente de la columna A, esto es, los elementos del primer grupo (frutas).


En la segunda parte del análisis, en la columna F, trabajamos sobre el segundo grupo de colores.
Insertamos la fórmula:
=CONTAR.SI($D$1:D1;D1)
con el rango corrido desde $D$1, lo que nos asegura un recorrido de 1 a 5 cíclico para cada tramo calculado anteriormente (de 5 en 5) de 1 a 8.
De igual manera en la columna F insertamos:
=INDIRECTO(DIRECCION(F1+1;2))
obteniendo el valor de la celda correspondiente de la columna B, es decir, de los valores de 'colores'.


En la columna H recogemos nuestros frutos (jeje...), insertamos un concatenado de las columnas E y G:
=E1& " " &G1
llegando a nuestro objetivo.


Por supuesto, todo esto se podría conseguir con una sencilla macro...

Sub MezclandoDatos()
'iniciamos contador
x = 1
'recorremos cada elemento del primer grupo (frutas)
For Each celda1 In Range("Tabla1[dato1]")
    'recorremos cada elemento del segundo grupo (colores)
    For Each celda2 In Range("Tabla1[dato2]")
        'controlamos que no haya elementos vación mezclados con otros existentes
        If celda1.Value = "" Or celda2.Value = "" Then
            'nothing
        Else
            'llevamos la mezcla a la hoja..
            Sheets("Hoja1").Cells(x, 1).Value = celda1.Value & " " & celda2.Value
            x = x + 1
        End If
    Next celda2
Next celda1

End Sub

2 comentarios:

  1. Hola! tengo un problema con excel que no se solucionar y puesto que en esta web he encontrado solucion en mas de un apuro me he decidido a escribir mi problema. Tengo una Columna A con descripciones (es texto con varias frases) y tengo una Columna B con un listado de palabras (enfermedades); me gustaría saber si hay algun metodo para saber en que celdas de la Columna A aparece alguna de las palabras e la lista de la Columna B. He probado un Vlookup ''false'' pero me relaciona celdas que no contienen para nada ninguna palabra igual. Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola Keitaro86,
      puedes probar con lo explicado en este post
      http://excelforo.blogspot.com.es/2014/12/formato-condicional-multiple.html

      cambiando la fórmula por esta otra
      =HALLAR($E$1:$F$1;A1)

      suponiendo en E1:F1 algunas de las palabras.

      Un cordial saludo

      Eliminar