วิธีการเปลี่ยนเซลล์ในคอลัมน์หนึ่งตามค่าที่ไม่ซ้ำกันในคอลัมน์อื่น

สมมติว่าคุณมีช่วงข้อมูลที่มีสองคอลัมน์ตอนนี้คุณต้องการเปลี่ยนเซลล์ในคอลัมน์หนึ่งเป็นแถวแนวนอนตามค่าที่ไม่ซ้ำกันในคอลัมน์อื่นเพื่อให้ได้ผลลัพธ์ต่อไปนี้ คุณมีแนวคิดดีๆในการแก้ปัญหานี้ใน Excel หรือไม่?

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วยสูตร

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วยรหัส VBA

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วย Kutools for Excel

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วยสูตร

ด้วยสูตรอาร์เรย์ต่อไปนี้คุณสามารถแยกค่าที่ไม่ซ้ำกันและเปลี่ยนข้อมูลที่เกี่ยวข้องเป็นแถวแนวนอนได้โปรดทำดังนี้:

1. ป้อนสูตรอาร์เรย์นี้: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) ลงในเซลล์ว่างตัวอย่างเช่น D2 แล้วกด Shift + Ctrl + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์ที่ถูกต้องดูภาพหน้าจอ:

หมายเหตุ: ในสูตรข้างต้น A2: A16 คือคอลัมน์ที่คุณต้องการแสดงรายการค่าที่ไม่ซ้ำกันและ D1 คือเซลล์ที่อยู่เหนือเซลล์สูตรนี้

2. จากนั้นลากที่จับเติมลงไปที่เซลล์เพื่อแยกค่าที่ไม่ซ้ำกันทั้งหมดดูภาพหน้าจอ:

3. จากนั้นป้อนสูตรนี้ลงในเซลล์ 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)และอย่าลืมกด Shift + Ctrl + Enter คีย์เพื่อรับผลลัพธ์ดูภาพหน้าจอ:

หมายเหตุ: ในสูตรข้างต้น: B2: B16 คือข้อมูลคอลัมน์ที่คุณต้องการเปลี่ยน A2: A16 คือคอลัมน์ที่คุณต้องการเปลี่ยนค่าตามและ D2 มีค่าเฉพาะที่คุณแยกออกมาในขั้นตอนที่ 1

4. จากนั้นลากที่จับเติมไปทางขวาของเซลล์ที่คุณต้องการแสดงรายการข้อมูลที่ย้ายจนกระทั่งแสดง 0 ดูภาพหน้าจอ:

5. จากนั้นลากที่จับเติมลงไปยังช่วงของเซลล์เพื่อรับข้อมูลที่ย้ายตามภาพหน้าจอต่อไปนี้:

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วยรหัส VBA

อาจเป็นสูตรที่ซับซ้อนสำหรับคุณที่จะเข้าใจที่นี่คุณสามารถเรียกใช้รหัส VBA ต่อไปนี้เพื่อให้ได้ผลลัพธ์ที่คุณต้องการ

1. กด ALT + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ โมดูล หน้าต่าง.

รหัส VBA: ย้ายเซลล์ในคอลัมน์หนึ่งตามค่าที่ไม่ซ้ำกันในคอลัมน์อื่น:

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. จากนั้นกด F5 คีย์เพื่อเรียกใช้รหัสนี้และกล่องพร้อมต์จะปรากฏขึ้นเพื่อเตือนให้คุณเลือกช่วงข้อมูลที่คุณต้องการใช้ดูภาพหน้าจอ:

4. จากนั้นคลิก OK ปุ่มกล่องข้อความอีกอันจะปรากฏขึ้นเพื่อเตือนให้คุณเลือกเซลล์ที่จะใส่ผลลัพธ์ดูภาพหน้าจอ:

6. คลิก OK และข้อมูลในคอลัมน์ B ถูกย้ายตามค่าที่ไม่ซ้ำกันในคอลัมน์ A ดูภาพหน้าจอ:

ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วย Kutools for Excel

ถ้าคุณมี Kutools สำหรับ Excelการรวมไฟล์ แถวรวมขั้นสูง และ แยกเซลล์ ยูทิลิตี้คุณสามารถทำงานนี้ให้เสร็จได้อย่างรวดเร็วโดยไม่ต้องใช้สูตรหรือรหัสใด ๆ

หลังจากการติดตั้ง Kutools สำหรับ Excelโปรดดำเนินการดังนี้:

1. เลือกช่วงข้อมูลที่คุณต้องการใช้ (หากคุณต้องการเก็บข้อมูลต้นฉบับโปรดคัดลอกและวางข้อมูลไปยังตำแหน่งอื่นก่อน)

2. จากนั้นคลิก Kutools > ผสานและแยก > แถวรวมขั้นสูงดูภาพหน้าจอ:

3. ใน รวมแถวตามคอลัมน์ โปรดดำเนินการดังต่อไปนี้:

(1. ) คลิกชื่อคอลัมน์ที่คุณต้องการเปลี่ยนข้อมูลตามและเลือก คีย์หลัก;

(2. ) คลิกคอลัมน์อื่นที่คุณต้องการเปลี่ยนแล้วคลิก รวมกัน จากนั้นเลือกตัวคั่นหนึ่งตัวเพื่อแยกข้อมูลที่รวมกันเช่นช่องว่างลูกน้ำเครื่องหมายอัฒภาค

4. จากนั้นคลิก Ok ข้อมูลในคอลัมน์ B ถูกรวมเข้าด้วยกันในเซลล์เดียวตามคอลัมน์ A ดูภาพหน้าจอ:

5. จากนั้นเลือกเซลล์ที่รวมกันแล้วคลิก Kutools > ผสานและแยก > แยกเซลล์ดูภาพหน้าจอ:

6. ใน แยกเซลล์ ใหเลือก แยกเป็นคอลัมน์ ภายใต้ ประเภท จากนั้นเลือกตัวคั่นที่แยกข้อมูลรวมของคุณดูภาพหน้าจอ:

7. จากนั้นคลิก Ok และเลือกเซลล์เพื่อใส่ผลการแยกในกล่องโต้ตอบที่โผล่ออกมาดูภาพหน้าจอ:

8. คลิก OKและคุณจะได้รับผลลัพธ์ตามที่คุณต้องการ ดูภาพหน้าจอ:

ดาวน์โหลดและทดลองใช้ Kutools for Excel ฟรีทันที!

สาธิต: ย้ายเซลล์ในคอลัมน์เดียวตามค่าที่ไม่ซ้ำกันด้วย Kutools for Excel