Usuario del software
2012-02-28 22:05:49 +0000 2012-02-28 22:05:49 +0000
10

¿Cómo combinar valores de varias filas en una sola fila en Excel?

Tengo un volcado de datos en Excel que consiste en datos anuales de clientes para dos valores diferentes. Los datos fueron proporcionados con una fila separada por valor para cada año y cliente. Es decir, tiene el siguiente aspecto

enter image description here

Me encuentro con una fila para el cliente 1 para el valor A en 2009 y una fila separada para el valor B para el mismo cliente en el mismo año.

En algunos casos no hay Valor A o Valor B. En el ejemplo anterior, se puede ver que el Cliente 1 no tiene Valor B en 2011, por lo que no se generó ninguna fila para eso. Y, aunque no está representado en el ejemplo, algunos clientes no tendrán datos para ninguno de los dos valores en un año (y por lo tanto no habrá fila para ese cliente en ese año). En esa situación, la falta de una fila para ese cliente en ese año está bien.

Quiero llevar esto a una hoja de trabajo donde haya una fila para ambos valores para cada año y cliente. Es decir, quiero que los datos se vean así

enter image description here

¿Cuál es la forma más efectiva de crear ese resultado?

Respuestas [5]

6
2012-02-29 12:18:49 +0000

Esto es VBA, o una macro que puedes ejecutar en tu hoja. Debes pulsar alt+F11 para que aparezca el prompt de Visual Basic para Aplicaciones, ir a tu libro de trabajo y right click - insert - module y pegar este código allí. A continuación, puede ejecutar el módulo desde VBA pulsando F5. Esta macro se llama "test"

Sub test()
'define variables
Dim RowNum as long, LastRow As long
'turn off screen updating
Application.ScreenUpdating = False
'start below titles and make full selection of data
RowNum = 2
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A2", Cells(LastRow, 4)).Select
'For loop for all rows in selection with cells
For Each Row In Selection
    With Cells
    'if customer name matches
    If Cells(RowNum, 1) = Cells(RowNum + 1, 1) Then
        'and if customer year matches
        If Cells(RowNum, 4) = Cells(RowNum + 1, 4) Then
            'move attribute 2 up next to attribute 1 and delete empty line
            Cells(RowNum + 1, 3).Copy Destination:=Cells(RowNum, 3)
            Rows(RowNum + 1).EntireRow.Delete
        End If
     End If
    End With
'increase rownum for next test
RowNum = RowNum + 1
Next Row
'turn on screen updating
Application.ScreenUpdating = True

End Sub

Esto se ejecutará a través de una hoja de cálculo ordenada y combinará filas consecutivas que coincidan tanto con el cliente como con el año y eliminará la fila ahora vacía. La hoja de cálculo debe estar ordenada de la forma en que la has presentado, clientes y años ascendentes, esta macro en particular no mirará más allá de las filas consecutivas.

Editar - es muy posible que mi with statement sea completamente innecesario, pero no hace daño a nadie..

REVISADO 02/28/14

Alguien usó esta respuesta en otra pregunta y cuando volví a pensar que este VBA pobre. Lo he rehecho -

Sub CombineRowsRevisited()

Dim c As Range
Dim i As Integer

For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then
            c.Offset(,3) = c.Offset(1,3)
            c.Offset(1).EntireRow.Delete
End If

Next

End Sub

Revisado 05/04/16

Preguntado de nuevo ¿Cómo combinar valores de varias filas en una sola? Tengo un módulo, pero necesito las variables explicando y de nuevo, es bastante pobre.

Sub CombineRowsRevisitedAgain()
    Dim myCell As Range
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each myCell In Range(Cells("A2"), Cells(lastRow, 1))
        If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 4) = myCell.Offset(1, 4)) Then
            myCell.Offset(0, 3) = myCell.Offset(1, 3)
            myCell.Offset(1).EntireRow.Delete
        End If
    Next
End Sub
  • *

Sin embargo, dependiendo del problema, podría ser mejor step -1 en un número de fila para que no se salte nada.

Sub CombineRowsRevisitedStep()
    Dim currentRow As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For currentRow = lastRow To 2 Step -1
        If Cells(currentRow, 1) = Cells(currentRow - 1, 1) And _
        Cells(currentRow, 4) = Cells(currentRow - 1, 4) Then
            Cells(currentRow - 1, 3) = Cells(currentRow, 3)
            Rows(currentRow).EntireRow.Delete
        End If
    Next

End Sub
6
2
2013-07-17 18:08:36 +0000

Otra opción:

  1. Seleccione la columna B, pulse CTRL+G -> Special-> Blanks -> OK
  2. Escriba = pulse ↑, luego CTRL+ENTER
  3. Seleccione la Columna C, pulse CTRL+G -> Special-> Blanks -> OK
  4. Escriba =IF( pulse ← escriba = pulse ↓ escriba , pulse ↓ escriba ,0) luego CTRL+ENTER
  5. Seleccionar todos los datos y Copy y Paste Special as Values
  6. Elimine los duplicados.

EDIT:

Explicación: Estoy tratando de hacer uso de la opción GoTo Blanks . Una vez seleccionados los espacios en blanco, se puede introducir la misma fórmula para todas las celdas en blanco seleccionadas. En cuanto a la pregunta del OP, los datos parecen tener espacios en blanco consistentes, es decir: los espacios en blanco de la columna Value A tienen el mismo CustomerID que la fila anterior que no está en blanco. Del mismo modo, los espacios en blanco de la columna Value B tienen el mismo CustomerID que la fila inferior que no está en blanco.

2
0
2012-02-29 18:12:36 +0000

Aquí están los pasos para crear una tabla separada con los datos condensados.

  1. Copie su tabla completa y péguela en una nueva hoja.
  2. Seleccione su nueva tabla y Elimine los duplicados (Cinta de opciones de datos -> Eliminar duplicados) en las columnas Customer y Year. Esto proporcionará el marco para la tabla condensada.
  3. En B2 (la primera entrada de Value A) introduzca lo siguiente:

  4. Rellene la fórmula hacia abajo en la columna. A continuación, rellene también la columna Value B. Y ya está.

Algunas notas:

  • Por supuesto, tendrás que ajustar las direcciones para que se ajusten a tus datos. Como referencia, la Hoja1 son los datos originales en las columnas de la A a la D.
  • Estoy asumiendo que sus datos (o encabezados) comienzan en la Fila 1. Esto es probablemente cierto si se trata de un volcado de datos. La fórmula tendrá que ser ajustada si este no es el caso.
  • Estoy asumiendo que su tabla tiene menos de un millón de filas. Si de alguna manera tiene más que eso, cambie los 1000000 en la fórmula por algo mayor que su número de filas.
  • Si su tabla tiene muchos miles de filas (más de 100.000), es posible que desee considerar el uso de una solución VBA en su lugar, ya que las fórmulas de matrices pueden atascarse con matrices grandes.
0
0
2016-07-15 16:44:30 +0000

Todo el mundo está utilizando un montón de código VBA o funciones complicadas para esto. Yo tengo un método que tarda un segundo en implementarse pero es mucho más comprensible y muy fácil de ajustar en función de otras posibilidades.

En el ejemplo que has dado arriba, pega estas (4) funciones en las celdas, E2, F2, G2 y H2, respectivamente (las funciones F&G hacen referencia a las celdas de arriba):

=IF(D2=D3, A2, IF(D2<>D1, A2, ""))    
=IF(D2=D3, MAX(B2:B3), IF(D2<>D1, B2, ""))    
=IF(D2=D3, MAX(B2:B3), IF(D2<>D1, IF(C2=0,"",C2),""))    
=IF(D2=D3, D2, IF(D2<>D1, D2, ""))

Arrastra estas fórmulas tan abajo como sea necesario. Genera una sola fila de datos cada vez que haya 2 filas, dejando las filas individuales sin afectar. Pega los valores especiales (para eliminar las fórmulas) de las columnas E-F-G-H en otro lugar y ordénalos por cliente para eliminar todas las filas extra.

0
0
2012-02-28 22:15:04 +0000

La forma más eficaz de hacerlo es volcar todos los datos en una tabla dinámica y soltar 'Cliente' en las etiquetas de las filas y luego seguir con las otras columnas. Puede colocar el 'Año' en la cabecera de la columna si quiere ver el desglose por año

Las tablas dinámicas se encuentran en Insertar en Excel 2010

0