Petua: Bahasa lain diterjemahkan Google. Anda boleh melawat English versi pautan ini.
Log masuk
x
or
x
x
Daftar Sekarang
x

or

Bagaimana untuk menukar sel dalam satu lajur berdasarkan nilai unik dalam lajur yang lain?

Supaya, anda mempunyai pelbagai data yang mengandungi dua lajur, sekarang, anda ingin menukar sel dalam satu lajur ke baris mendatar berdasarkan nilai unik dalam lajur yang lain untuk mendapatkan hasil yang berikut. Adakah anda mempunyai idea yang baik untuk menyelesaikan masalah ini dalam Excel?

doc transpose nilai unik 1

Transpose sel dalam satu lajur berdasarkan nilai unik dengan formula

Transpose sel dalam satu lajur berdasarkan nilai unik dengan kod VBA

Transpose sel dalam satu lajur berdasarkan nilai unik dengan Kutools untuk Excel



Dengan formula tatasusunan berikut, anda boleh mengekstrak nilai-nilai unik dan menukar data yang sama ke dalam baris mendatar, sila lakukan seperti berikut:

1. Masukkan formula array ini: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) ke dalam sel kosong, D2, misalnya, dan tekan Shift + Ctrl + Enter kunci bersama untuk mendapatkan hasil yang betul, lihat tangkapan skrin:

doc transpose nilai unik 2

Nota: Dalam formula di atas, A2: A16 adalah lajur yang anda mahu menyenaraikan nilai unik dari, dan D1 adalah sel di atas sel formula ini.

2. Kemudian seret pemegang pegangan ke sel untuk mengekstrak semua nilai unik, lihat tangkapan skrin:

doc transpose nilai unik 3

3. Kemudian masukkan formula ini ke dalam sel 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), dan ingat untuk menekan Shift + Ctrl + Enter kunci untuk mendapatkan hasilnya, lihat tangkapan skrin:

doc transpose nilai unik 4

Nota: Dalam formula di atas: B2: B16 adalah data lajur yang anda ingin tukar, A2: A16 adalah lajur yang anda ingin menukar nilai berdasarkan, dan D2 mengandungi nilai unik yang anda telah diekstrak dalam Langkah 1.

4. Kemudian seret pemegang pengisian ke kanan sel-sel yang anda mahu senarai data yang dipadamkan sehingga memaparkan 0, lihat tangkapan skrin:

doc transpose nilai unik 5

5. Dan kemudian terus menyeret pemegang pengisian ke julat sel untuk mendapatkan data yang dipindahkan seperti tangkapan skrin berikut:

doc transpose nilai unik 6


Mungkin formula rumit untuk anda fahami, di sini, anda boleh menjalankan kod VBA berikut untuk mendapatkan hasil yang anda perlukan.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi tingkap.

2. Klik Memasukkan > Modul, dan tampal kod berikut dalam Modul Tetingkap.

Kod VBA: Transpose sel dalam satu lajur berdasarkan nilai unik dalam lajur yang lain:

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. Kemudian tekan F5 kunci untuk menjalankan kod ini, dan kotak segera akan keluar untuk mengingatkan anda memilih julat data yang anda mahu gunakan, lihat tangkapan skrin:

doc transpose nilai unik 7

4. Kemudian klik OK butang, kotak petak lain akan muncul untuk mengingatkan anda untuk memilih sel untuk meletakkan hasilnya, lihat tangkapan skrin:

doc transpose nilai unik 8

6. Klik OK butang, dan data dalam lajur B telah dipindahkan berdasarkan nilai unik dalam lajur A, lihat tangkapan skrin:

doc transpose nilai unik 9


Jika anda mempunyai Kutools untuk Excel, menggabungkan Advanced Combine Rows dan Sel Split utiliti, anda boleh menyelesaikan tugas ini dengan cepat tanpa sebarang formula atau kod.

Kutools untuk Excel : dengan lebih daripada 300 berguna tambahan Excel, bebas untuk mencuba tanpa had pada hari 60.

Selepas memasang Kutools untuk Excel, sila lakukan seperti berikut:

1. Pilih julat data yang anda mahu gunakan. (Sekiranya anda ingin menyimpan data asal, sila salin dan tampal data ke lokasi lain terlebih dahulu.)

2. Kemudian klik Kutools > Kandungan > Advanced Combine Rows, lihat tangkapan skrin:

doc transpose nilai unik 10

3. Dalam Combine Rows Based on Column kotak dialog, sila lakukan operasi berikut:

(1.) Klik nama lajur yang anda hendak tukar data berdasarkan, dan pilih Kunci utama;

(2.) Klik lajur lain yang anda mahu alihkan, dan klik Gabungkan kemudian memilih satu pemisah untuk memisahkan data gabungan, seperti ruang, koma, titik koma.

doc transpose nilai unik 11

4. Kemudian klik Ok butang, data dalam lajur B telah digabungkan bersama dalam satu sel berdasarkan lajur A, lihat tangkapan skrin:

doc transpose nilai unik 12

5. Kemudian pilih sel gabungan dan klik Kutools > teks > Sel Split, lihat tangkapan skrin:

doc transpose nilai unik 13

6. Di dalam Sel Split kotak dialog, pilih Berpisah ke Lajur di bawah Jenis pilihan, dan kemudian pilih pemisah yang memisahkan data gabungan anda, lihat tangkapan skrin:

doc transpose nilai unik 14 14

7. Kemudian klik Ok butang, dan pilih sel untuk meletakkan hasil berpecah dalam kotak dialog yang muncul, lihat tangkapan skrin:

doc transpose nilai unik 15

8. Klik OK, dan anda akan mendapat hasil yang anda perlukan. Lihat tangkapan skrin:

doc transpose nilai unik 16

Muat turun dan percubaan percuma Kutools untuk Excel Sekarang!


Kutools untuk Excel: dengan lebih daripada 200 berguna Excel tambahan, bebas untuk mencuba tanpa had pada hari 60. Muat turun dan percubaan percuma Sekarang!


Cadangan Alat Produktiviti untuk Excel

kte tab 201905

Kutools untuk Excel Membantu Anda Selalu Menyelesaikan Kerja di Hadapan Masa, dan Berdiri Daripada Orang

  • Lebih daripada ciri-ciri canggih 300 yang canggih, yang direka untuk senario kerja 1500, meningkatkan produktiviti oleh 70%, memberi anda lebih banyak masa untuk menjaga keluarga dan menikmati kehidupan.
  • Tidak perlu lagi menghafal formula dan kod VBA, berikan rehat dari otak anda sekarang.
  • Menjadi pakar Excel dalam minit 3, Operasi yang rumit dan berulang boleh dilakukan dalam beberapa saat,
  • Mengurangkan beribu-ribu operasi papan kekunci & tetikus setiap hari, mengucapkan selamat tinggal kepada penyakit pekerjaan sekarang.
  • 110,000 orang yang sangat berkesan dan pilihan syarikat 300 + yang terkenal di dunia.
  • Ciri-ciri penuh 60-hari adalah percubaan percuma. Jaminan wang balik 60 hari. Tahun 2 peningkatan dan sokongan percuma.

Membawa Browsing Tab dan Editing ke Microsoft Office, Jauh Lebih Berkuasa Daripada Tab Penyemak Imbas

  • Tab Pejabat direka untuk Aplikasi Pejabat Word, Excel, PowerPoint dan Lain-lain: Penerbit, Akses, Visio dan Projek.
  • Buka dan buat beberapa dokumen dalam tab baharu pada tetingkap yang sama, dan bukannya dalam tetingkap baru.
  • Meningkatkan produktiviti anda oleh 50%, dan mengurangkan beratus-ratus klik tetikus untuk anda setiap hari!
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.
    martha Bright · 2 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 · 11 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.
  • To post as a guest, your comment is unpublished.
    Raj · 4 months ago
    Need to get the same out put but for predefined columns to be selected would be ($A,$B) and need the output column Position on $D$1.
    If any one have idea's that would be a great help!!!!