Chuyển đổi một cột dữ liệu vào một bảng hai chiều

Liên hệ QC

boyxin

Members actively
Tham gia
10/3/08
Bài viết
1,664
Được thích
2,335
Introduction
Giới thiệu
Users are often faced with the task of converting a single column of data into a two-dimensional table of rows and columns. This transformation can be done with a simple formula. For example, suppose you have in column A a list of Name, Address, City, State, and Zip Codes for many employees. This list has 5 elements per employee, as shown in the image on the left below, and you want to create a two dimensional table as shown in the image on the right below:
Người dùng Excel thường phải đối mặt với nhiệm vụ chuyển đổi một cột dữ liệu vào một bảng hai chiều gồm các hàng và cột. Việc này có thể được thực hiện với một công thức rất đơn giản. Ví dụ, giả sử bạn có trong cột A một danh sách các Tên, Địa chỉ, thành phố, Nhà nước, và Zip Codes cho nhiều nhân viên. Danh sách này có 5 yếu tố cho mỗi nhân viên, như hiển thị trong hình bên trái dưới đây, và bạn muốn tạo một bảng hai chiều như hiển thị trong hình bên phải dưới đây:
ColToTableOrigCol.png
ColToTableTable2.png

This transformation can be done with a single formula entered into the cells in which you want the two dimensional table to appear. This page will present two formulas, one simple formula for use if your column of data has a fixed number of elements, and a second formula, an enhancement of the first, for use if your column of data is variable in size.
Điều này có thể thực hiện với một công thức nhập vào trong ô mà bạn muốn dữ liệu chuyển đếndưới dạng bảng 2 chiều. Bài này sẽ trình bày hai công thức, một công thức rất đơn giản nếu sử dụng cho các cột dữ liệu của bạn có một số yếu tố cố định, và thứ hai là một công thức, mở rộng cho công thức thứ nhất, nếu sử dụng cho cột dữ liệu của bạn có kích thước không xác định cụ thể.

As an example, assume that your column of original data begins in cell B4. (In the image above, cell B3 contains the (optional) header Column Data and the actual data to be transformed, shown in colored fonts, begins in cell B4. The data in both the original columns and transformed tables are shown in colored fonts for illustration purposes. In practice, the color is entirely irrelevant.) Next, assume that the first cell of the transformed table (not including the Tablular Data header) begins in cell F4.
Ví dụ, giả định cột dữ liệu gốc của bạn bắt đầu từ ô B4. (Trong hình trên, B3 là ô chứa tiêu đề cột dữ liệu và dữ liệu sẽ được chuyển đổi, hiển thị bằng chữ các màu, bắt đầu tử ô B4. Các dữ liệu trong cả hai bản gốc và bảng đích được hiển thị cùng màu nhằm mục đích minh hoạ. (Trong thực tế, màu sắc là hoàn toàn không thích hợp.) Tiếp theo, giả định rằng ô đầu tiên của bảng dữ liệu đích (không bao gồm dòng tiêu đề) bắt đầu từ ô F4.

The first formula can be used if the number of rows in the column of data is fixed. The formula is shown below:

Công thức thứ nhất có thể được sử dụng nếu số hàng trong cột của dữ liệu nguồn là cố định. Công thức được hiển thị dưới đây:


PHP:
=OFFSET($B$4,(BlockSize*(ROW()-ROW(F$4)))+(COLUMN()-COLUMN($F4)),0,1,1)
Since the data in the original column of data is grouped into sections of 5 rows, we use a defined name called BlockSize with a value of 5 in the formula. You can use a defined name as in this example formula, or you can hard code the number within the formula itself, or you can reference another cell. All that matters is that the block size somehow gets into the formula. Enter the formula above into cell F4 and fill across row 4 for 5 columns (or the number of columns equal to the block size of your columnar data). With this formula now in cells E4:J4, fill that range down so it fills the range F4:J9. This range has 6 rows, but the column of data has only 3 blocks of data. Thus, the remaining cells in F4:J9 are filled with 0s, as shown below.
Vì dữ liệu trong cột ban đầu được nhóm vào các nhóm gồm 5 phần tử trên 5 hàng, chúng tôi sử dụng một Name gọi là BlockSize với một giá trị = 5 trong công thức. Bạn có thể xác định một Name như công thức trong ví dụ này, hoặc bạn có thể gài trực tiếp con số 5 trong công thức riêng của mình, hoặc bạn có thể tham chiếu từ 1 ô khác. Tất cả những cách đó nhằm mục đích là đưa số nào đó vào công thức. Nhập công thức ở trên vào ô F4 và fill ngang theo dòng cho 4 cột kế cho đủ 5 (hoặc số cột bằng với số phần tử trong 1 nhóm). Với công thức này trong E4: J4, copy xuống hết F4: J9. Vùng F4: J9 hiện có 6 hàng, nhưng chỉ 3 hàng là có dữ liệu, còn lại là những số zero như được hiển thị dưới đây.
ColToTableTable1.png

This is most likely undesirable, which leads us to the second formula presented on this page.
If your original data column has a variable number of rows, but does have a maximum number of rows that will not be exceeded, you can use an enhancement of the formula above to fill the two dimensional table with blanks if the table contains more cells that there are rows in the column of data. This formula is shown below:
Điều này rất có thể không ai ưa, dẫn chúng ta đến việc thực hiện công thức thứ hai trình bày như sau:
Nếu cột dữ liệu ban đầu có một số lượng dòng nào đó cho 1 nhóm, nhưng không có gì cho biết sẽ cần một số lượng tối đa các dòng trong bảng đích là bao nhiêu, bạn có thể sử dụng công thức ở trên với phần mở rộng để không hiển thị số 0 vô nghĩa trên bảng đích. Công thức này được hiển thị dưới đây:

PHP:
=IF((BlockSize*(ROW()-ROW(F$14)))+(COLUMN()-COLUMN($F14))>=ROWS($B$4:$B$18),"",
OFFSET($B$4,(BlockSize*(ROW()-ROW(F$14)))+(COLUMN()-COLUMN($F14)),0,1,1))
This formula is split in to two lines for clarity, but should be all on a single line on your worksheet.
Enter this formula in cell F14, fill across to cell J14, and then fill this range down to fill the range F14:J19. The actual number of rows in this table should be equal to the maximum number of blocks of data in the original columnar data. For example, if the block size is 5 and your column of data will have no more than 100 rows (= 20 blocks), the table should have 20 rows and 5 columns. If there is not enough data in the original column of data to fill the entire table, unused entries in the table are filled with empty strings, as shown below:

Đây là công thức được viết trên hai dòng để đọc cho rõ ràng, nhưng khi gõ vào thanh công thức phải được gõ chỉ trên một dòng.
Nhập công thức này trong F14, fill ngang qua J14, và sau đó fill vùng này xuống cho hết vùng F14: J19. Trên thực tế số dòng trong bảng đích này nên bằng với số lượng tối đa của các nhóm dữ liệu trong cột dữ liệu ban đầu. Ví dụ, nếu kích thước nhóm là 5 và cột của dữ liệu của bạn không có hơn 100 các hàng (số nhóm = 20), bảng nên có 20 hàng và 5 cột. Nếu không có đủ dữ liệu trong cột dữ liệu ban đầu để điền đầy vào bảng, các ô không có dữ liệu sẽ được thay thế bằng 1 chuỗi rỗng như được hiển thị dưới đây:

ColToTableTable2.png

FileDownload.png
download the file
SectionBreak.png

Using VBA To Transform A Column
In addition to the formula approach described above, you can use VBA code to automate the transformation of a data column to a two dimensional table. The code below will transform a column of data that has a defined name of ColumnData to a two dimensional table, the upper left cell of which has a defined name of StartTable. The block size of the data in the column is specified by the C_BLOCK_SIZE constant.
Bằng cách sử dụng VBA để chuyển đổi một cột
Ngoài những cách tiếp cận công thức được mô tả ở trên, bạn có thể sử dụng mã VBA để tự động hoá trong quá trình chuyển đổi của một cột dữ liệu vào một bảng hai chiều. Mã dưới đây sẽ chuyển đổi một cột dữ liệu đã được xác định là một tên của ColumnData vào một bảng hai chiều, ô trên cùng bên trái của bảng đích sẽ được đặt name là startTable. Các khối kích thước của dữ liệu trong cột được xác định bởi các C_BLOCK_SIZE cố định.

PHP:
Sub ColumnToTable()

    Dim ColumnData As Range     ' The original column of data'
    Dim RNdx As Long            ' Row index’
    Dim CNdx As Long            ' Column index’
    
    Dim StartRow As Long        ' Row number of Range("StartTable")'
    Dim StartColumn As Long     ' Column number of Range("StartTable")'
    Dim N As Long               ' Index into ColumnData'
    Dim WS As Worksheet         ' Worksheet reference'
    
    Const C_BLOCK_SIZE = 5      ' Block size of data in ColumnData'
    
    ' Initialize the variables.'
    
    Set ColumnData = Range("ColumnData")
    StartRow = Range("StartTable").Row
    StartColumn = Range("StartTable").Column
    RNdx = StartRow
    CNdx = StartColumn
    Set WS = Worksheets("UsingVBA")
    N = 0
    
    ' Loop across then down filling'
    ' cells with element N of ColumnData.'
    
    For RNdx = StartRow To (StartRow + (ColumnData.Rows.Count / C_BLOCK_SIZE))
        For CNdx = StartColumn To StartColumn + C_BLOCK_SIZE - 1
            N = N + 1
            WS.Cells(RNdx, CNdx).Value = ColumnData.Cells(N, 1)
        Next CNdx
    Next RNdx
    
End Sub
FileDownload.png
download the file

--------------
Sưu tầm = Nguồn + Tự động dịch
 
Chỉnh sửa lần cuối bởi điều hành viên:
Mình thấy phần công thức thì hay, nhưng phần VBA code phức tạp và dài quá. Mình thấy code chỉ cần viết như sau là đã OK rồi

Mã:
Sub Trans()
Dim i, size
Dim It As Range, It1 As Range
size = 5
Set It = Sheet3.[B7]: Set It1 = Sheet3.[F11]
Do
If It = "" Then Exit Do
It1.Resize(, size) = WorksheetFunction.Transpose(It.Resize(size))
Set It = It.Offset(size): Set It1 = It1.Offset(1)
Loop
Set IT= Nothing: Set It1=Nothing
End Sub
 
Chuyển đổi một cột dữ liệu vào một bảng hai chiều.
Một cách đơn giản dành cho các bạn chưa biết rành về VBA và hàm. 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à xong.
Chúc các bạn thực hiện thành công.
 
Chào Be09,
Hàm Transpose chỉ có thể xoay ngang bảng chứ không thể biết 1 cột thành nhiều cột được.
 
Chào Be09,
Hàm Transpose chỉ có thể xoay ngang bảng chứ không thể biết 1 cột thành nhiều cột được.
Tức là vầy sư phụ à:
- Quét chọn Block 1, copy rồi Paste Special\Transpose
- Quét chọn Block 2, copy rồi Paste Special\Transpose
vân vân... Nếu có 100 Block thì làm 100 lần
Ẹc... Ẹc... công thức và code "ế" luôn!
 
Web KT
Back
Top Bottom