Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako prenesti celice v en stolpec na podlagi unikatnih vrednosti v drugem stolpcu?

Če mislite, da imate vrsto podatkov, ki vsebujeta dva stolpca, zdaj želite prenesti celice v en stolpec v vodoravne vrstice, ki temeljijo na edinstvenih vrednostih v drugem stolpcu, da dobite naslednji rezultat. Ali imate kakšne dobre ideje za rešitev te težave v Excelu?

doc prenese edinstvene vrednosti 1

Transpose celice v enem stolpcu na podlagi enkratnih vrednosti s formulami

Pretvorite celice v en stolpec na podlagi edinstvenih vrednosti s kodo VBA

Transpose celice v enem stolpcu na podlagi edinstvenih vrednosti s Kutools za Excel



Z naslednjimi formulami matrike lahko izvlečete edinstvene vrednosti in prenesete ustrezne podatke v vodoravne vrstice, storite naslednje:

1. Vnesite to formulo: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) v prazno celico, na primer D2, in pritisnite Shift + Ctrl + Enter skupaj, da dobite pravilen rezultat, si oglejte posnetek zaslona:

doc prenese edinstvene vrednosti 2

Opombe: V zgornji formuli, A2: A16 je stolpec, v katerega želite navesti edinstvene vrednosti, in D1 je celica nad to celico formule.

2. Potem povlecite ročico za polnjenje v celice, da izvlečete vse edinstvene vrednosti, glejte sliko zaslona:

doc prenese edinstvene vrednosti 3

3. In potem pojdite na to formulo v celico 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), in ne pozabite pritisniti Shift + Ctrl + Enter ključne besede, da dobite rezultat, si oglejte posnetek zaslona:

doc prenese edinstvene vrednosti 4

Opombe: V zgornji formuli: B2: B16 so podatki o stolpcu, ki jih želite prenesti, A2: A16 je stolpec, v katerega želite prenesti vrednosti na podlagi, in D2 vsebuje edinstveno vrednost, ki ste jo pridobili v koraku 1.

4. Potem povlecite polnilno ročico desno od celic, v katere želite prenesti prenesene podatke, dokler ni prikazano 0, si oglejte posnetek zaslona:

doc prenese edinstvene vrednosti 5

5. In nato še naprej povlecite ročico za polnjenje navzdol na obseg celic, da prenesete prenesene podatke na naslednji prikaz zaslona:

doc prenese edinstvene vrednosti 6


Morda so formule zapletene za razumevanje, tukaj lahko zaženete naslednjo kodo VBA, da dobite želeni želeni rezultat.

1. Drži dol ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduli, in prilepite naslednjo kodo v Moduli Okno.

VBA koda: prenesite celice v en stolpec na podlagi unikatnih vrednosti v drugem stolpcu:

Sub transposeunique()
'updateby Extendoffice 20151207
    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. Nato pritisnite F5 ključ za zagon te kode in se bo pojavilo odzivno okno, ki vas opomni, da izberete obseg podatkov, ki ga želite uporabiti, glejte sliko zaslona:

doc prenese edinstvene vrednosti 7

4. In nato kliknite OK gumb, se bo izpisalo drugo okno, ki vas bo opomnilo, da izberete celico, ki bo dala rezultat, si oglejte posnetek zaslona:

doc prenese edinstvene vrednosti 8

6. Kliknite OK gumb in podatki v stolpcu B so bili preneseni na podlagi edinstvenih vrednosti v stolpcu A, glejte sliko zaslona:

doc prenese edinstvene vrednosti 9


Če imate Kutools za Excel, ki združuje Napredne kombinacije vrst in Razdeljene celice lahko hitro opravite to opravilo brez kakršnihkoli formul ali kod.

Kutools za Excel : z več kot 300 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60.

Ko namestite Kutools za Excel, storite naslednje:

1. Izberite obseg podatkov, ki ga želite uporabiti. (Če želite ohraniti izvirne podatke, najprej kopirajte in prilepite podatke na drugo lokacijo.)

2. Nato kliknite Kutools > vsebina > Napredne kombinacije vrst, si oglejte sliko zaslona:

doc prenese edinstvene vrednosti 10

3. V COmbine vrstice na podlagi stolpca pogovorno okno, naredite naslednje operacije:

(1.) Kliknite ime stolpca, na katerega želite prenesti podatke, in izberite Primarni ključ;

(2.) Kliknite drug stolpec, ki ga želite prenesti, in kliknite Združite nato izberite enega separatorja, da ločite kombinirane podatke, kot so prostor, vejica, podpičje.

doc prenese edinstvene vrednosti 11

4. Nato kliknite Ok gumb, so podatki v stolpcu B združeni v eni celici, ki temelji na stolpcu A, glejte sliko zaslona:

doc prenese edinstvene vrednosti 12

5. In nato izberite kombinirane celice in kliknite Kutools > Besedilo > Razdeljene celice, si oglejte sliko zaslona:

doc prenese edinstvene vrednosti 13

6. v Razdeljene celice pogovorno okno, izberite Razdeli na stolpce pod tip in izberite ločilo, ki ločuje vaše kombinirane podatke, si oglejte sliko zaslona:

doc prenese edinstvene vrednosti 14 14

7. Nato kliknite Ok gumb in izberite celico, da bi v razdelku pogovornega okna izpisali deljen rezultat, si oglejte sliko zaslona:

doc prenese edinstvene vrednosti 15

8. Kliknite OK, in dobili boste rezultat, kot boste potrebovali. Prikaz slike:

doc prenese edinstvene vrednosti 16

Prenesite in brezplačno preizkusite Kutools za Excel zdaj!


Kutools za Excel: z več kot 200 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60. Prenesite in brezplačno preizkusite zdaj!



Priporočena orodja za produktivnost za Excel

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    arielle · 1 days ago
    I have a data set in Columns A (Unique ID) - E. Each row has data based on the ID#, there are multiple rows for each ID# but I want one row per ID# with all of the other data in columns (it would be 5 columns long minimum and 25 maximum depending on how many each unique ID has). I found a code but it only works for two columns. I had to concatenate the four columns (not including ID) then delimit after running the macro (lot of work). For 15,000 rows of data this is extra time consuming. Is there an endless column macro that would work? Thanks in advance everyone for your help!
    ID CODE ST CODE# DATE
  • To post as a guest, your comment is unpublished.
    martha Bright · 7 days ago
    The macro did not work. It just copied the contents in cell A1.
  • To post as a guest, your comment is unpublished.
    Vinod · 15 days ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of A column into a new column BUT...is there a way to get the all the values in B column to be transposed as given below:

    Product Order Date Product Order Order Order Order Order Order Order
    KTE 100 3/3/2019 KTE 100 100 100 200 100 150 100
    KTO 150 3/3/2019 KTO 150 100 200 100 150 200
    KTE 100 3/4/2019 BOT 150 100 200 150 100 200
    KTO 100 3/4/2019 COD 200 150 100 150
    KTO 200 3/5/2019
    KTE 100 3/5/2019
    BOT 150 3/5/2019
    BOT 100 3/6/2019
    KTO 100 3/6/2019
    KTE 200 3/6/2019
    BOT 200 3/7/2019
    COD 200 3/7/2019
    KTE 100 3/7/2019
    KTO 150 3/7/2019
    BOT 150 3/8/2019
    KTE 150 3/8/2019
    COD 150 3/8/2019
    BOT 100 3/9/2019
    BOT 200 3/10/2019
    COD 100 3/10/2019
    KTO 200 3/10/2019
    COD 150 3/11/2019
    KTE 100 3/11/2019
  • To post as a guest, your comment is unpublished.
    Sean Vizcarrondo · 3 months ago
    So I am working for a company. We have columns for info such as Last name, first name, rank, section, phone number, address. Is there a way I can use a similar formula to transpose the entire row of info to a column by names?
  • To post as a guest, your comment is unpublished.
    kumar · 4 months ago
    Hi can we add each row and give the output in one column, with the above functionality.