Come trasporre le celle in una colonna in base ai valori univoci di un'altra colonna?

Supponendo di avere un intervallo di dati che contiene due colonne, ora si desidera trasporre le celle di una colonna in righe orizzontali basandosi sui valori univoci di un'altra colonna per ottenere il seguente risultato. Avete qualche buona idea per risolvere questo problema in Excel?

Trasporre le celle in una colonna in base ai valori univoci con formule

Trasporre le celle in una colonna in base ai valori univoci con il codice VBA

Trasporre le celle in una colonna in base ai valori univoci con Kutools per Excel

Trasporre le celle in una colonna in base ai valori univoci con formule

Con le seguenti formule matrice, è possibile estrarre i valori univoci e trasporre i relativi dati in righe orizzontali; procedere come segue:

1. Immettere questa formula matrice: =INDICE($A$2:$A$16; CONFRONTA(0; CONTA.SE($D$1:$D1; $A$2:$A$16); 0)) in una cella vuota, ad esempio D2, e premere contemporaneamente i tasti Maiusc + Ctrl + Invio per ottenere il risultato corretto, vedere la schermata:

Nota: Nella formula sopra, A2:A16 è la colonna da cui si desidera elencare i valori univoci, e D1 è la cella sopra questa cella della formula.

2. Quindi trascinare il quadratino di riempimento verso il basso fino alle celle per estrarre tutti i valori univoci, vedere la schermata:

3. Successivamente, inserire questa formula nella cella E2: =SE.ERRORE(INDICE($B$2:$B$16; CONFRONTA(0; CONTA.SE($D2:D2;$B$2:$B$16)+SE($A$2:$A$16<>$D2; 1; 0); 0)); 0), e ricordarsi di premere i tasti Maiusc + Ctrl + Invio per ottenere il risultato, vedere la schermata:

Nota: Nella formula precedente: B2:B16 è la colonna di dati che si desidera trasporre, A2:A16 è la colonna in base alla quale si desidera trasporre i valori, e D2 contiene il valore univoco estratto nel Passo 1.

4. Quindi trascinare il quadratino di riempimento verso destra delle celle in cui si desidera elencare i dati trasposti fino a quando non viene visualizzato 0, vedere la schermata:

5. Continuare quindi a trascinare il quadratino di riempimento verso il basso nell'intervallo di celle per ottenere i dati trasposti come mostrato nello screenshot seguente:

Trasporre le celle in una colonna in base ai valori univoci con il codice VBA

Forse le formule sono complesse da comprendere, qui è possibile eseguire il seguente codice VBA per ottenere il risultato desiderato.

1. Tenere premuti i tasti ALT + F11 per aprire la finestra Microsoft Visual Basic per Applicazioni.

2. Fare clic su Inserisci > Modulo e incollare il seguente codice nella Finestra del Modulo.

Codice VBA: Trasporre le celle in una colonna in base ai valori univoci in un'altra colonna:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Premere quindi il tasto F5 per eseguire questo codice, e apparirà una finestra di dialogo per ricordarvi di selezionare l'intervallo di dati che si desidera utilizzare, vedere la schermata:

4. Quindi fare clic sul pulsante OK, apparirà un'altra finestra di dialogo per ricordarvi di selezionare una cella in cui inserire il risultato, vedere la schermata:

6. Fare clic sul pulsante OK, e i dati nella colonna B saranno stati trasposti in base ai valori univoci nella colonna A, vedere la schermata:

Trasporre le celle in una colonna in base ai valori univoci con Kutools per Excel

Se avete Kutools per Excel, combinando le funzionalità Unione Avanzata Righe e Dividi Celle, potete completare rapidamente questa operazione senza alcuna formula o codice.

Dopo aver installato Kutools per Excel, procedere come segue:

1. Selezionare l'intervallo di dati che si desidera utilizzare. (Se si desidera mantenere i dati originali, copiare e incollare prima i dati in un'altra posizione.)

2. Quindi fare clic su Kutools > Unisci e Dividi > Unione Avanzata Righe, vedere la schermata:

3. Nella finestra di dialogo Combina Righe Basate su Colonna, effettuare le seguenti operazioni:

(1.) Fare clic sul nome della colonna in base alla quale si desidera trasporre i dati, e selezionare Chiave Principale;

(2.) Fare clic su un'altra colonna che si desidera trasporre, e fare clic su Unisci, quindi scegliere un separatore per separare i dati combinati, come spazio, virgola, punto e virgola.

4. Quindi fare clic sul pulsante Ok, i dati nella colonna B saranno stati combinati insieme in una cella in base alla colonna A, vedere la schermata:

5. Quindi selezionare le celle combinate, e fare clic su Kutools > Unisci e Dividi > Dividi Celle, vedere la schermata:

6. Nella finestra di dialogo Dividi Celle, selezionare Dividi in Colonne sotto l'opzione Tipo, e quindi scegliere il separatore che separa i dati combinati, vedere la schermata:

7. Quindi fare clic sul pulsante Ok, e selezionare una cella in cui inserire il risultato diviso nella finestra di dialogo apparsa, vedere la schermata:

8. Fare clic su OK, e otterrete il risultato di cui avete bisogno. Vedere la schermata:

Scarica e prova gratuitamente Kutools per Excel Ora!

Demo: Trasporre le celle in una colonna in base ai valori univoci con Kutools per Excel