Chuyển đổi dữ liệu từ cột sang dòng và ngược lại.

[exworld]

Thực hiện như sau:
Copy vùng dữ liệu cần chuyển đổi, từ me nu chọn Edit, chọn Paste Special….Làm xuất hiện cửa sổ Paste Special, trong cửa sổ Paste Special bạn chọn Values (bạn phải chọn ở đây để xóa bỏ dữ liệu có sử dụng hàm), tiếp theo chọn Transpose và nhất nút OK, vậy là bạn đã thực hiện hoàn thành việc chuyển đổi dữ liệu từ cột sang dòng hoặc chuyển đổi dữ liệu từ dòng sang cột.
Chúc các bạn thực hiện thành công.

View more random threads:

• Công cụ trợ giúp tính giá thành
• Cách chèn hình chìm dưới chữ trong bảng excel 2007!
• Bảng tính lãi vay và cho Vay
• công thức tính phép năm
• xin phần mềm excel theo QD 48
• Mình gởi mẫu sổ sách excel - hình thức NKC nè các bạn
• Nước sôi lửa bỏng đâyyyyyyyyyyyyyyyyyyyyy!
• Phần mền KT excel theo QD 48 cho DN sản xuất
• Nhờ chỉnh lỗi hộ file in thu chi
• Phần mềm kế toán excel theo QD15

[hoanghaodl]

Ðề: Tái cấu trúc lại bảng biểu

Mình có 1 bảng số liệu, như bên trái của hình;

Giờ muốn có 1 bảng số liệu tổng hợp theo 1 tiêu chí khác (như bên fải hình)



Muốn vậy ta chạy macro sau

[COLOR=#000000]
Option ExplicitSub GPE[/COLOR][COLOR=#007700]() [/COLOR][COLOR=#0000BB]Dim Rng [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Cls [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]nRg [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Cll [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Rg0 [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range Dim Rw [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]R0 [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Ten [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Ma [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]()  [/COLOR][COLOR=#0000BB]Rw [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]9 [/COLOR][COLOR=#007700]* [[/COLOR][COLOR=#0000BB]b2[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]CurrentRegion[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Count Arr [/COLOR][COLOR=#007700]= Array([/COLOR][COLOR=#DD0000]"MHg"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"Ten Hg"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"MKH"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"TenKH"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"SoLg"[/COLOR][COLOR=#007700]) [[/COLOR][COLOR=#0000BB]aa1[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]5[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Arr [/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]ab1[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]CurrentRegion[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Clear [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]Each Cls In Range[/COLOR][COLOR=#007700]([[/COLOR][COLOR=#0000BB]c1[/COLOR][COLOR=#007700]], [[/COLOR][COLOR=#0000BB]c1[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlToRight[/COLOR][COLOR=#007700]))    [/COLOR][COLOR=#0000BB]On Error Resume Next    Set nRg [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]SpecialCells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlCellTypeConstants[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])    [/COLOR][COLOR=#0000BB]Err [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]0    [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Not nRg Is Nothing Then        [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]Each Cll In nRg            [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Cll[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]> [/COLOR][COLOR=#0000BB]0 Then                R0 [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"AC"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Row                Ten [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value[/COLOR][COLOR=#007700]:                [/COLOR][COLOR=#0000BB]Ma [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value                With Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"AC"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])                    .[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Cll[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Row[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"A"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value                    Set Rg0 [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"AA"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700])                    If [/COLOR][COLOR=#0000BB]Rg0[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]<> [/COLOR][COLOR=#0000BB]Ten Then                        [/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, -[/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Ten                        [/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, -[/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Ma                        [/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, -[/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Interior[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]ColorIndex [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]34 [/COLOR][COLOR=#007700]+ ([/COLOR][COLOR=#0000BB]R0 Mod 9[/COLOR][COLOR=#007700])                    [/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]If                    .[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cll[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value                End With            End [/COLOR][COLOR=#007700]If        [/COLOR][COLOR=#0000BB]Next Cll        Set nRg [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Nothing    End [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Next ClsEnd Sub[/COLOR] 

[Alpenliebe]

Ðề: T ái cấu trúc bảng dữ liệu

Cũng vấn đề như vậy, khi tôi đưa lên 1 diễn đàn khác ở địa chỉ Excel Programming & người ta đã đưa ra 1 giải thuật có fần khác hơn. Sau khi chỉnh sửa để fù hợp hơn với việc tính thời gian tiêu hao trong mỗi macro, tôi thu được nội dung sau:

[COLOR=#000000]
Sub hsv[/COLOR][COLOR=#007700]() [/COLOR][COLOR=#DD0000]'Copyright by HSV' [/COLOR][COLOR=#0000BB]Dim j [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]k [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long Timer_ [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Timer[/COLOR][COLOR=#007700]() [[/COLOR][COLOR=#0000BB]AG2[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]CurrentRegion[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]ClearContents [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]j [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]3 To 8    [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]i [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]3 To 17        [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700]) > [/COLOR][COLOR=#0000BB]0 Then            Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Count[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]33[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Application[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Transpose[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value[/COLOR][COLOR=#007700])            [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Count[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]35[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"A" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value            Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Count[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]37[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700])        [/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]If    [/COLOR][COLOR=#0000BB]Next i Next j [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]k [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]ag65500[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Row To 3 Step [/COLOR][COLOR=#007700]-[/COLOR][COLOR=#0000BB]1    [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"AG" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]k[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"AG" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]k [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) [/COLOR][COLOR=#0000BB]Then      Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"AG" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]k[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]ClearContents    End [/COLOR][COLOR=#007700]If [/COLOR][COLOR=#0000BB]Next k [/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]Am65500[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Timer[/COLOR][COLOR=#007700]() - [/COLOR][COLOR=#0000BB]Timer_End Sub[/COLOR]