วิธีจัดเรียงข้อมูลด้วยเซลล์ที่ผสานใน Excel

Attempting to sort data in Excel that contains merged cells often results in an error prompt, as shown in the screenshot on the left. This behavior is a result of Excel’s current limitations—Excel does not natively support sorting ranges with merged cells, as merged ranges disrupt the underlying data structure and make it difficult for the sort function to operate normally.

When working with worksheets that use merged cells for formatting or grouping data, you may still need to organize or reorder your data. To accomplish this, you must first handle those merged cells appropriately. Below, you will find several practical methods for handling and sorting data with merged cells while preserving your information.

จัดเรียงข้อมูลด้วยเซลล์ที่ผสานโดยการยกเลิกการผสานเซลล์ทั้งหมดก่อน
เรียงลำดับข้อมูลด้วยเซลล์ที่ผสานตาม Kutools for Excel
Sort data with merged cells by VBA automation (unmerge, fill, sort, re-merge)

จัดเรียงข้อมูลด้วยเซลล์ที่ผสานโดยการยกเลิกการผสานเซลล์ทั้งหมดก่อน

Because Excel’s default sort function cannot operate on ranges with merged cells, the necessary approach is to unmerge all merged cells within your data list first. Once unmerged, blanks will appear where merged cells were, so you will need to fill these empty cells to maintain accurate sorting. Here is the step-by-step method:

1. Select the full list or table containing merged cells that you would like to sort. With the data selected, click หน้าแรก > ผสานและศูนย์ to unmerge all merged cells. Please refer to the screenshot below for visual guidance:

2. After unmerging, you’ll notice that the previously merged areas are now blank cells. While the merged cells are still selected, proceed to the หน้าแรก และคลิกที่ ค้นหาและเลือก > ไปที่พิเศษ. This step ensures efficient bulk selection of all blank cells within your range, making the next step much quicker.

3 ใน ไปที่พิเศษ กล่องโต้ตอบเลือกไฟล์ ช่องว่าง ตัวเลือกและคลิก OK. This will immediately highlight all blank cells in your selected range.

4. With blanks still highlighted, go to the Formula Bar and type the = symbol, then select the cell directly above the first blank cell to create a reference formula (for example, if C3 is blank, input = C2). Instead of pressing just Enter, hold Ctrl และกด เข้าสู่ เพื่อเติมเต็ม ทั้งหมด the selected blanks with their respective above-cell values at once.

After this step, every former merged area is populated with its original value, ensuring your data integrity during sorting.

5. You are now ready to sort your data as normal—select any column, go to the ข้อมูล และเลือก เรียงลำดับ A ถึง Z or จัดเรียง Z ถึง A. If you wish to restore the merged formatting after sorting, you may manually re-merge adjacent identical values, but take care to avoid merging across unrelated data.

เคล็ดลับ: Before proceeding, always make a backup of your original data if retaining the merged format is important. Also be aware that after unmerging and filling blanks, formulas will be present. If you’d like to convert formulas to values, select the range, copy, and then paste as values to avoid unwanted formula updates after sorting.

เรียงลำดับข้อมูลด้วยเซลล์ที่ผสานตาม Kutools for Excel

Kutools for Excel provides a more straightforward and efficient solution for sorting data with merged cells, making the process much more reliable when dealing with large datasets or frequent formatting requirements. The ยกเลิกการผสานเซลล์และเติมมูลค่า ยูทิลิตี้ใน Kutools for Excel can automatically unmerge cells and fill the resulting blanks with the original merged value, eliminating manual intervention and significantly improving efficiency.

1. Highlight the range that contains the merged cells you want to sort. Next, navigate to Kutools > ผสานและแยก > ยกเลิกการผสานเซลล์และเติมมูลค่า. Refer to the following screenshot for guidance:

Upon executing this operation, all merged cells within the selected range are promptly unmerged, and the newly created blank cells are filled with the corresponding value from the previously merged cell, ensuring consistency throughout the table.

2. You may now sort your data as usual. After sorting, for a consistent appearance or for data presentation purposes, Kutools also provides a quick way to re-merge cells with the same content. Simply select the sorted range, then go to Kutools > ผสานและแยก > ผสานเซลล์เดียวกัน to efficiently reapply merged formatting only where values are identical.

จุดเด่น: This approach minimizes manual steps, reduces the likelihood of errors, and is especially suitable when working with complex tables or regular reporting needs. Always ensure you review the merged output after sorting to verify that merging does not exclude any important data relationships.

เรียงลำดับข้อมูลด้วยเซลล์ที่ผสานตาม Kutools for Excel

Sort data with merged cells by VBA automation (unmerge, fill, sort, and re-merge)

For users familiar with macros, automating the process via VBA can streamline the handling of merged cells—especially with repetitive or large-scale sorting tasks. This approach can efficiently:

• Unmerge all merged cells in a specified range
• Fill resulting blank cells with the value from above for consistency
• Sort the data by any specified column
• Optionally, re-merge consecutive identical values in the sorted data

This method is flexible for customized workflows but requires enabling macros and careful operation to avoid data loss. If you’re new to VBA, consider testing on a sample sheet first.

ขั้นตอนการดำเนินงาน:

1. บน Ribbon ให้คลิก ผู้พัฒนา > ของ Visual Basic to open the Visual Basic for Applications window, then click สิ่งที่ใส่เข้าไป > โมดูล. Copy and paste the following code into the Module window:

Sub SortDataWithMergedCells()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastRow As Long, lastCol As Long
    Dim sortCol As Variant
    Dim reMerge As VbMsgBoxResult
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng = Application.InputBox("Select the data range to sort", xTitleId, Selection.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    ' Store dimensions
    lastRow = rng.Rows.Count + rng.Row - 1
    lastCol = rng.Columns.Count + rng.Column - 1
    
    ' Unmerge and fill down values
    rng.UnMerge
    For Each cell In rng
        If IsEmpty(cell.Value) Then
            cell.Value = cell.Offset(-1, 0).Value
        End If
    Next cell
    
    ' Ask for sort column
    sortCol = Application.InputBox("Enter column number in your selection to sort by (e.g. 1 for first column)", xTitleId, 1, Type:=1)
    
    If sortCol = False Then Exit Sub
    
    ' Sort the range
    rng.Sort Key1:=rng.Cells(1, sortCol), Order1:=xlAscending, Header:=xlNo
    
    ' Ask if user wants to re-merge identical consecutive values
    reMerge = MsgBox("Do you want to re-merge identical consecutive values in the sorted range (column " & sortCol & ")?", vbYesNo + vbQuestion, xTitleId)
    
    If reMerge = vbYes Then
        Dim startCell As Range, endCell As Range
        Dim currVal As Variant
        Dim i As Long
        
        Set startCell = rng.Cells(1, sortCol)
        currVal = startCell.Value
        Set endCell = startCell
        
        For i = 2 To rng.Rows.Count
            If rng.Cells(i, sortCol).Value = currVal Then
                Set endCell = rng.Cells(i, sortCol)
            Else
                If startCell.Address <> endCell.Address Then
                    ws.Range(startCell, endCell).Merge
                End If
                Set startCell = rng.Cells(i, sortCol)
                currVal = startCell.Value
                Set endCell = startCell
            End If
        Next i
        
        ' Final group
        If startCell.Address <> endCell.Address Then
            ws.Range(startCell, endCell).Merge
        End If
    End If
    
    On Error GoTo 0
End Sub

2. To run the macro, click the วิ่ง button in the VBA editor. You will be prompted to select the data range and the sorting column number. Confirm each prompt and let the macro complete all steps. If you choose to re-merge, the code will automatically merge adjacent cells with the same value in the designated column.

ทิปส์:

• Always make a backup of your worksheet before running VBA macros, as actions like unmerging/merging are irreversible.
• Sorting by header or non-numeric columns may require extra adjustments—be sure to specify the correct column number as indicated in your selection.
• If you encounter errors after sorting, verify entire row or column references, and adjust the macro's parameters as needed for your specific data layout.

ข้อดี: Automates an otherwise repetitive, multi-step task—especially useful for recurring work.
ข้อ จำกัด : VBA macros require enabling macros (potential security warning), and complex table layouts may need manual review after merging is restored.

For best results, test this macro on a duplicate of your data first. This VBA solution is particularly useful for users who frequently need to repeat the sort-and-merge process or work with dynamic data exports.