Come trasporre le celle in una colonna in base a valori univoci in un'altra colonna?
Supponendo che tu abbia un intervallo di dati che contiene due colonne, ora, vuoi trasporre le celle in una colonna in righe orizzontali in base a valori univoci in un'altra colonna per ottenere il seguente risultato. Hai qualche buona idea per risolvere questo problema in Excel?
Trasponi le celle in una colonna in base a valori univoci con formule
Trasponi le celle in una colonna in base a valori univoci con codice VBA
Trasponi le celle in una colonna in base a valori univoci con Kutools per Excel
Trasponi le celle in una colonna in base a valori univoci con formule
Con le seguenti formule di matrice, puoi estrarre i valori univoci e trasporre i loro dati corrispondenti in righe orizzontali, per favore fai come segue:
1. Inserisci questa formula di matrice: = INDICE ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) in una cella vuota, ad esempio D2, e premere Maiusc + Ctrl + Invio tasti insieme per ottenere il risultato corretto, vedi screenshot:
Note:: 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 trascina il quadratino di riempimento sulle celle per estrarre tutti i valori univoci, vedi screenshot:
3. E poi continua a inserire questa formula nella cella E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)e ricordati di premere Maiusc + Ctrl + Invio tasti per ottenere il risultato, vedi screenshot:
Note:: Nella formula sopra: B2: B16 sono i dati della colonna che vuoi trasporre, A2: A16 è la colonna su cui si desidera trasporre i valori in base a, e D2 contiene il valore univoco che hai estratto nel passaggio 1.
4. Quindi trascina il quadratino di riempimento a destra delle celle in cui desideri elencare i dati trasposti fino a visualizzare 0, vedi screenshot:
5. E poi continua a trascinare il quadratino di riempimento verso il basso nell'intervallo di celle per ottenere i dati trasposti come mostrato nella seguente schermata:
Trasponi le celle in una colonna in base a valori univoci con codice VBA
Potrebbe essere che le formule siano complesse da capire, qui puoi eseguire il seguente codice VBA per ottenere il risultato desiderato di cui hai bisogno.
1. Tieni premuto il ALT + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
2. Clic inserire > Modulie incolla il codice seguente nel file Moduli Finestra.
Codice VBA: trasponi le celle in una colonna in base a 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. Quindi premere F5 chiave per eseguire questo codice e verrà visualizzata una finestra di messaggio per ricordarti di selezionare l'intervallo di dati che desideri utilizzare, vedi screenshot:
4. E poi clicca OK pulsante, verrà visualizzata un'altra finestra di messaggio per ricordarti di selezionare una cella per inserire il risultato, vedi screenshot:
6. Clic OK pulsante e i dati nella colonna B sono stati trasposti in base a valori univoci nella colonna A, vedi screenshot:
Trasponi le celle in una colonna in base a valori univoci con Kutools per Excel
Se si dispone di Kutools for Excel, combinando il Combina righe avanzate ed Celle divise utilità, è possibile completare rapidamente questa attività senza formule o codice.
| Kutools for Excel : con oltre 300 utili componenti aggiuntivi di Excel, da provare gratuitamente senza limitazioni in 30 giorni. |
Dopo l'installazione Kutools for Excel, si prega di fare come segue:
1. Seleziona l'intervallo di dati che desideri utilizzare. (Se desideri conservare i dati originali, copia e incolla prima i dati in un'altra posizione.)
2. Quindi fare clic Kutools > Unisci e dividi > Combina righe avanzate, vedi screenshot:
3. Nel Combina righe in base alla colonna finestra di dialogo, eseguire le seguenti operazioni:
(1.) Fare clic sul nome della colonna su cui si desidera trasporre i dati e selezionare Chiave primaria;
(2.) Fare clic su un'altra colonna che si desidera trasporre e fare clic Combinare quindi scegli un separatore per separare i dati combinati, come spazio, virgola, punto e virgola.
4. Quindi fare clic Ok pulsante, i dati nella colonna B sono stati combinati insieme in una cella basata sulla colonna A, vedi screenshot:
5. Quindi seleziona le celle combinate e fai clic Kutools > Unisci e dividi > Celle divise, vedi screenshot:
6. Nel Celle divise finestra di dialogo, selezionare Dividi in colonne sotto il Tipologia opzione, quindi scegli il separatore che separa i dati combinati, vedi screenshot:
7. Quindi fare clic Ok pulsante e seleziona una cella per inserire il risultato della divisione nella finestra di dialogo visualizzata, vedi screenshot:
8. Clic OKe otterrai il risultato di cui hai bisogno. Vedi screenshot:
Scarica e prova gratuitamente Kutools per Excel ora!
Demo: trasponi le celle in una colonna in base a valori univoci con Kutools per Excel
I migliori strumenti per la produttività in ufficio
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
