Chuyển số liệu đặc biệt

[PhanTuHuong]

Trong công việc chuyên môn, nhiều khi bạn phải thống kê, loại trừ và chuyển dữ liệu sang kiểu đặc biệt. Chức năng Transpose trong copy đặc biệt nhiều khi không thể đáp ứng được hết yêu cầu công việc. Tôi xin giới thiệu 2 phương pháp như sau:

1. Chuyển dữ liệu thành một cột:

Ví dụ có 5 cột giá trị từ B đến F. Ta copy các giá trị đó vào 1 cột H liên tục. Ngoài ra bạn có thể bổ sung các điều kiện loại trừ trong công thức đó:

' Xap xep lai thanh 1 cot
Private Sub CommandButton1_Click()
    Dim i, j, a As Integer
    Range("B2").Select
    a = 0
    For i = 1 To 5 ' Chay tung cot
        For j = 1 To 100 ' Chay tung hang
            If Selection.Cells(j, i) > 0 Then
                Selection.Cells(a + j, 7) = Selection.Cells(j, i)
            Else
                Exit For 'thoat vong lap doi voi j
            End If
        Next j
            a = a + j - 1
    Next i
Range("B1").Select
End Sub

 

[PhanTuHuong]

2. Chuyển số liệu sang hàng và cột giới hạn:

Ví dụ như trên nhưng ta copy sang 12 cột, hết 12 cột thì tự động xuống hàng.

'Xap xep lai thanh n cot va m hang
Private Sub CommandButton1_Click()
    Dim i, j, a As Integer
    Range("B2").Select
    a = 0
    b = 1
    For i = 1 To 5 ' Chay tung cot
        For j = 1 To 100 ' Chay tung hang
            If Selection.Cells(j, i) > 0 Then
                Selection.Cells(b, 7 + a) = Selection.Cells(j, i)
                    If a = 11 Then
                       a = 0
                       b = b + 1
                    Else
                       a = a + 1
                    End If
            Else
                Exit For 'thoat vong lap doi voi j
            End If
        Next j
    Next i
Range("B1").Select
End Sub

Để hiểu hơn thì mời bạn tải file kèm theo.

 

[Hamvui]

Không hiểu máy của bạn bị sao, tôi tải xuống và đã chạy thử thấy tốt mà, có sao đâu. Cứ ấn vào các nút là chạy phăng phăng , nhưng phải để chế độ bảo mật Security Level ở Medium hay low, để high hay very high thì nó sẽ không chạy đâu ***** .

 

[PhanTuHuong]

3. Chuyển dữ liệu thành 1 hàng:

Chỉ cần sửa code 1 chút là bạn có thể chuyển số liệu thành 1 hàng.

' Xắp xếp lại thành 1 hàng
Private Sub CommandButton1_Click()
    Dim i, j, a As Integer
    Range("B2").Select
    a = 0
    For i = 1 To 5 ' Chay tung cot
        For j = 1 To 100 ' Chay tung hang
            If Selection.Cells(j, i) > 0 Then
                Selection.Cells(7, a + j) = Selection.Cells(j, i)
            Else
                Exit For 'thoat vong lap doi voi j
            End If
        Next j
            a = a + j - 1
    Next i
Range("B1").Select
End Sub

 

[PhanTuHuong]

Còn để linh hoạt dữ liệu đầu vào bạn có thể tham khảo mục này:
http://www.giaiphapexcel.com/forum/showthread.php?p=3960#post3960

Mục di chuyển này hết sức có ý nghĩa khi bạn lập biểu đồ nén tĩnh cọc, nén tĩnh nền, tôi sẽ nói cụ thể sau.

 

[PhanTuHuong]

Ví dụ về cách lập biểu đồ thí nghiệm nén tĩnh cọc (nén tĩnh nền)

Khi bạn lập biểu đồ nén tĩnh cọc, trước hết bạn phải vào số đọc của đồng hồ S1, S2 tương ứng các cấp áp lực và thời gian đọc. Biến dạng trung bình được tính ở cột thứ 8 (hình 68). Phần nền được bôi màu vàng chính là số đọc của đồng hồ S1, S2. Để vẽ được biểu đồ lún theo cấp tải trọng (hình 69) và biểu đồ lún theo thời gian của từng cấp tải trọng (hình 70) thì bạn không thể sử dụng số liệu biến dạng trung bình tại hình 68 để vẽ trực tiếp được. Biểu đồ lún theo cấp tải trọng sử dụng vùng dữ liệu T8:U29 (hình 71). Biểu đồ lún theo thời gian của từng cấp tải trọng sử dụng vùng dữ liệu V8:AO29.
Khi đó bạn phải tạo ra một thủ tục để chuyển dữ liệu từ số đọc biến dạng ở hình 68 và đổ sang V9:AO29. Số liệu cần chuyển đổi bao gồm Tải trọng (Tấn) và biến dạng tương ứng theo thời gian. Để tạo được thủ tục này, bạn phải bổ sung thêm một số điều kiện từ những hướng dẫn ở trên.

Hình 68: Số liệu thí nghiệm nén tĩnh cọc thử.

 

[PhanTuHuong]

Hình 69: Biểu đồ quan hệ giữa độ lún và tải trọng

Hình 70: Biểu đồ quan hệ giữa độ lún và thời gian đối với mỗi tải trọng

Hình 71: Số liệu để vẽ các biểu đồ thí nghiệm nén tĩnh cọc.


Như vậy, bạn phải dùng dữ liệu ở hình 68 để chuyển sang như hình 71. Các biểu đồ ở hình 69, 70 sẽ thể hiện kết quả.

 

[PhanTuHuong]

Trong một số trường hợp, bạn có thể xây dựng mối tương quan giữa các đại lượng. Tuy nhiên có trường hợp thể hiện ở dạng phân phối thực nghiệm (hình dưới đây) mà các giá trị thể hiện dưới tần số xuất hiện.

Hình ở trên thể hiện mối quan hệ giữa đại lượng W và a. Các giá trị màu đỏ chính là tần số xuất hiện của W(i) và a(i).
Như vậy, vấn đề đặt ra ở đây là với kiểu trình bày như vậy, bạn không thể xây dựng được phương trình tương quan giữa 2 đại lượng trên. Khi đó bạn phải xây dựng từng cặp giá trị liên tục trong bảng tính thì mới thực hiện được. (hình dưới)

Các giá trị nền vàng ở trên chính là được chuyển từ hình đầu tiên. Như vậy, bạn phải có 1 thủ tục chuyển từ hình 1 sang hình 2. Dưới đây là đoạn code thực hiện (bạn có thể bổ sung, sửa chữa cho hợp lý):

Sub VD_Chuyenbang()
    On Error GoTo Thoat
    Dim Mang, Cell
    Dim Cot, i, j As Integer
        i = 1
        j = 1
        l = 1
    Set Mang = Application.InputBox("Vao mang de chuyen bang" & Chr(13) & "CHU Y: Chi chon vung phan phoi gia tri!", "Chuyen bang tinh", Type:=8)
        Cot = Mang.Columns.Count
        Hang = Mang.Rows.Count
    Set Diemdat = Application.InputBox("Vao diem dau tien chuyen bang den:", "Chuyen bang tinh", Type:=8)
    For i = 1 To Cot
        For j = 1 To Hang
            If Mang(i, j) > 0 Then
                For k = 1 To Mang(i, j).Value
                    Diemdat.Cells(1, k + l) = Mang(0, j) 'Hien cot
                    Diemdat.Cells(2, k + l) = Mang(i, 0) 'Hien hang
                Next k
                    l = k + l - 1
            ElseIf Mang(i, j) = 0 Then
                    l = l
            End If
        Next j
    Next i

Thoat:

End Sub

 

[PhanTuHuong]

1. Chuyển dữ liệu thành một cột:

Ví dụ có 5 cột giá trị từ B đến F. Ta copy các giá trị đó vào 1 cột H liên tục. Ngoài ra bạn có thể bổ sung các điều kiện loại trừ trong công thức đó:

' Xap xep lai thanh 1 cot
Private Sub CommandButton1_Click()
    Dim i, j, a As Integer
    Range("B2").Select
    a = 0
    For i = 1 To 5 ' Chay tung cot
        For j = 1 To 100 ' Chay tung hang
            If Selection.Cells(j, i) > 0 Then
                Selection.Cells(a + j, 7) = Selection.Cells(j, i)
            Else
                Exit For 'thoat vong lap doi voi j
            End If
        Next j
            a = a + j - 1
    Next i
Range("B1").Select
End Sub

Do thói quen, tôi hay sử dụng vòng For .. Next , thủ tục trên được rút gọn như sau:

Sub Chuyen_mot_cot()
   Dim i As Integer, j As Integer, a As Integer
   Range("B2").Select
   a = 0
   For i = 1 To 5          ' Thực hiện từng cột
      j = 1
      Do While Selection.Cells(j, i) > 0
         Selection.Cells(a + j, 7) = Selection.Cells(j, i)
         j = j + 1
     Loop
         a = a + j - 1
   Next i
      Range("B1").Select
End Sub

 

[thu_hanh]

chào bạn
mình muốn viết code cho 1 bảng số liệu gồm 23 hàng ngang và 33 hàng dọc muốn chuyển hết sang 1 hàng dọc ( cho chạy hết hàng ngang thứ 1 thành 1 hàng dọc sau đó chạy tiếp hàng 2 .. tới hàng cuối cùng). nhưng viết macro nó báo lỗi lien tục vậy mình muốn nhờ bạn giúp viết hộ có được ko?
cảm ơn bạn trước nhé

 

[tocroi06]

Chuyển dữ liệu từ cột sang hàng

2. Chuyển số liệu sang hàng và cột giới hạn:

Ví dụ như trên nhưng ta copy sang 12 cột, hết 12 cột thì tự động xuống hàng.

'Xap xep lai thanh n cot va m hang
Private Sub CommandButton1_Click()
    Dim i, j, a As Integer
    Range("B2").Select
    a = 0
    b = 1
    For i = 1 To 5 ' Chay tung cot
        For j = 1 To 100 ' Chay tung hang
            If Selection.Cells(j, i) > 0 Then
                Selection.Cells(b, 7 + a) = Selection.Cells(j, i)
                    If a = 11 Then
                       a = 0
                       b = b + 1
                    Else
                       a = a + 1
                    End If
            Else
                Exit For 'thoat vong lap doi voi j
            End If
        Next j
    Next i
Range("B1").Select
End Sub

Để hiểu hơn thì mời bạn tải file kèm theo.

Anh/Chị ơi em muốn chuyển dữ liệu từ một cột dọc sang thành bảng ngang gồm 4 cột. Em đã down phần của anh chị về và chạy thấy ra kết quả rất hay. Nhưng em không biết phải cài đặt hay làm như thế nào với đoạn mã mà anh đã viết bên trên để có thể chạy ra kết quả như vậy. Em rất mong anh có thể hướng dẫn cho em chi tiết hơn

Em rất cám ơn

 

[tuongnghi0715]

có thể giúp em chuyển từ cột ( file chấm công ) sang hàng ( bảng đi trễ) . em không biết phải bắt đầu như thế nào? mong cả nhà giúp em.

 

[Cúc Hr]

Do thói quen, tôi hay sử dụng vòng For .. Next , thủ tục trên được rút gọn như sau:

Sub Chuyen_mot_cot()
   Dim i As Integer, j As Integer, a As Integer
   Range("B2").Select
   a = 0
   For i = 1 To 5          ' Thực hiện từng cột
      j = 1
      Do While Selection.Cells(j, i) > 0
         Selection.Cells(a + j, 7) = Selection.Cells(j, i)
         j = j + 1
     Loop
         a = a + j - 1
   Next i
      Range("B1").Select
End Sub

chào anh
hiện tại em có 1 file data dữ liệu khá lớn khoảng 74000 dòng, cột B là các tháng trong năm, em muốn chuyển cột B thành hàng ngang, anh có thể cho em xin code được không ah. em cảm ơn anh

 

[Hoàng Tuấn 868]

chào anh
hiện tại em có 1 file data dữ liệu khá lớn khoảng 74000 dòng, cột B là các tháng trong năm, em muốn chuyển cột B thành hàng ngang, anh có thể cho em xin code được không ah. em cảm ơn anh

Mình thấy có bài này gần giống, bạn thử tham khảo xem áp dụng được gì không.

 

[Maika8008]

Thế bạn có biết Excel có bao nhiêu cột không? Tôi không xem file nên không biết bạn muốn chuyển 74.000 dòng thành bao nhiêu cột?

 

[ptm0412]

Thế bạn có biết Excel có bao nhiêu cột không? Tôi không xem file nên không biết bạn muốn chuyển 74.000 dòng thành bao nhiêu cột?

Nguyên văn: "cột B là các tháng trong năm, em muốn chuyển cột B thành hàng ngang"
Dữ liệu 1 năm là 12 cột
Nếu đủ cả 3 con số thì nhân 3 = 36 cột, hoặc vẫn 12 cột
Chỉ dùng Pivot table là nhanh nhất

 

[Cúc Hr]

Thế bạn có biết Excel có bao nhiêu cột không? Tôi không xem file nên không biết bạn muốn chuyển 74.000 dòng thành bao nhiêu cột?

em đã tìm được solution rồi ah

Nguyên văn: "cột B là các tháng trong năm, em muốn chuyển cột B thành hàng ngang"
Dữ liệu 1 năm là 12 cột
Nếu đủ cả 3 con số thì nhân 3 = 36 cột, hoặc vẫn 12 cột
Chỉ dùng Pivot table là nhanh nhất

View attachment 288110

Dạ anh, em đi theo hướng này và đã giải quyết đc yêu cầu công việc. Em cảm ơn anh