Tạo danh sách từ bảng dữ liệu (1 người xem)

skullrock · 10/9/15

Chào các anh chị & các bạn,
Mọi người giúp mình xử lý bài toán này với.
Mình có 1 bảng dữ liệu lớn, và mình đã nêu vấn đề trong file đính kèm.
Vấn đề gần như bài toán ngược của hàm index, ví dụ trong bảng dữ liệu thì ô nào có kết quả sẽ trả về giá trị dòng & cột của kết quả đó. Cộng thêm yêu cầu là chỉ lập ra danh sách cho các ô có kết quả, các ô trống thì ko lập ra.
Mong mọi người xem qua và giúp mình
Xin cảm ơn

HyperLink · 10/9/15

bạn thử Macro sau:

Mã:

Sub hotro_SapXep(ws1 As String, ws2 As String)
Dim rLastCol As Range, Arr(), dArr()
Dim R As Long, C As Long, iRow As Long

    With Sheets(ws1)
        Set rLastCol = .Cells(1, .Columns.Count).End(xlToLeft) 'vi. tri' cuoi' cung` cua? Row 1
        Arr = .Range(.Range("A65000").End(xlUp), rLastCol).Value2
    End With
    '----------
    '1 To UBound(Arr, 1): vòng lap duyet tu dong 1 den dong cuoi' cua vung chon
    '1 To UBound(Arr, 2): vòng lap duyet tu cot 1 den cot cuoi' cua vung chon
    ReDim dArr(1 To UBound(Arr, 1) * UBound(Arr, 2), 1 To UBound(Arr, 2)) 'xac dinh kich thuoc mang
    
    For R = 2 To UBound(Arr, 1)
        For C = 2 To UBound(Arr, 2)
            If Trim(Arr(R, C)) <> "" Then
                iRow = iRow + 1
                dArr(iRow, 1) = Arr(R, 1) 'cot 1
                dArr(iRow, 2) = Arr(1, C) 'cot 2
                dArr(iRow, 3) = Arr(R, C) 'cot 3
            End If
        Next C
    Next R
    
    With Sheets(ws2)
        .Select
        .Range("A2:C65000").ClearContents
        .Range("A2").Resize(iRow, 3) = dArr
    End With
    '----------
    Set rLastCol = Nothing
    Erase Arr
    Erase dArr
    '----------
    MsgBox ("xong")
End Sub

Cá ngừ F1 · 10/9/15

Ở cột 2: tôi chưa biêt Quy luật xuất hiện X, Y, Z ... là gì?

ndu96081631 · 10/9/15

skullrock đã viết:
Chào các anh chị & các bạn,
Mọi người giúp mình xử lý bài toán này với.
Mình có 1 bảng dữ liệu lớn, và mình đã nêu vấn đề trong file đính kèm.
Vấn đề gần như bài toán ngược của hàm index, ví dụ trong bảng dữ liệu thì ô nào có kết quả sẽ trả về giá trị dòng & cột của kết quả đó. Cộng thêm yêu cầu là chỉ lập ra danh sách cho các ô có kết quả, các ô trống thì ko lập ra.
Mong mọi người xem qua và giúp mình
Xin cảm ơn

Có 1 cách đơn giản nhưng ít người biết: UnPivotTable
- Đầu tiên bấm tổ hợp phím Alt + D + P (bấm Alt + D trước rồi bấm phím P sau)
- Làm theo hướng dẫn như trong video:

- Sau khi PivotTable được tạo thành, bạn double click vào cell giao điểm của 2 cái Grand Total (Grand Total của Row và Grand Total của Column). Lập tức một bảng tính dạng Unpivot sẽ được tạo trên 1 sheet mới
- Copy thứ bạn cần paste vào nơi nao tùy ý
- Xóa hết mấy cái PivotTable và sheet mới tạo
- Xong!
------------------------

skullrock · 10/9/15

HyperLink đã viết:

bạn thử Macro sau:

Mã:

Sub hotro_SapXep(ws1 As String, ws2 As String)
Dim rLastCol As Range, Arr(), dArr()
Dim R As Long, C As Long, iRow As Long

    With Sheets(ws1)
        Set rLastCol = .Cells(1, .Columns.Count).End(xlToLeft) 'vi. tri' cuoi' cung` cua? Row 1
        Arr = .Range(.Range("A65000").End(xlUp), rLastCol).Value2
    End With
    '----------
    '1 To UBound(Arr, 1): vòng lap duyet tu dong 1 den dong cuoi' cua vung chon
    '1 To UBound(Arr, 2): vòng lap duyet tu cot 1 den cot cuoi' cua vung chon
    ReDim dArr(1 To UBound(Arr, 1) * UBound(Arr, 2), 1 To UBound(Arr, 2)) 'xac dinh kich thuoc mang
    
    For R = 2 To UBound(Arr, 1)
        For C = 2 To UBound(Arr, 2)
            If Trim(Arr(R, C)) <> "" Then
                iRow = iRow + 1
                dArr(iRow, 1) = Arr(R, 1) 'cot 1
                dArr(iRow, 2) = Arr(1, C) 'cot 2
                dArr(iRow, 3) = Arr(R, C) 'cot 3
            End If
        Next C
    Next R
    
    With Sheets(ws2)
        .Select
        .Range("A2:C65000").ClearContents
        .Range("A2").Resize(iRow, 3) = dArr
    End With
    '----------
    Set rLastCol = Nothing
    Erase Arr
    Erase dArr
    '----------
    MsgBox ("xong")
End Sub

Cám ơn anh, code hoạt động rất OK. Nhưng bảng dữ liệu mình hiện có 70000 dòng & 125 cột, chắc bị vượt giới hạn nên code bị lỗi, xóa bớt dòng & cột thì OK.
Anh chỉ giúp chỗ này với. Và cho mình hỏi thêm nếu vùng dò tìm của mình muốn là dòng A2 trở xuống và từ cột E2 trở đi có được không, tại cột B, C, D là các cột tạm mình tính toán cho việc khác rồi.

HyperLink · 10/9/15

skullrock đã viết:
Cám ơn anh, code hoạt động rất OK. Nhưng bảng dữ liệu mình hiện có 70000 dòng & 125 cột, chắc bị vượt giới hạn nên code bị lỗi, xóa bớt dòng & cột thì OK.
Anh chỉ giúp chỗ này với. Và cho mình hỏi thêm nếu vùng dò tìm của mình muốn là dòng A2 trở xuống và từ cột E2 trở đi có được không, tại cột B, C, D là các cột tạm mình tính toán cho việc khác rồi.

bạn gửi lại file có vị trí hàng + cột đúng với thực tế (số liệu ảo cũng được), mình sẽ chỉnh lại code theo yêu cầu ... --=0

skullrock · 10/9/15

HyperLink đã viết:
bạn gửi lại file có vị trí hàng + cột đúng với thực tế (số liệu ảo cũng được), mình sẽ chỉnh lại code theo yêu cầu ...

Đây là bảng dữ liệu mình đang làm, mình mới làm đc vài cột có giá trị thôi, nhưng sẽ hoàn chỉnh hết.
Đây là bảng đơn giá khoán, các dòng là các sản phẩm, các cột là các máy.
Do đặc thù sản xuất nên 1 máy chạy được nhiều sản phẩm và 1 sản phẩm có thể chạy được ở nhiều máy. Mỗi máy lại có công suất khác nhau. Nên mình không làm gọn lại bảng dữ liệu được.
Bạn xem giúp coi được không.
Cám ơn.

skullrock · 10/9/15

ndu96081631 đã viết:
Có 1 cách đơn giản nhưng ít người biết: UnPivotTable
- Đầu tiên bấm tổ hợp phím Alt + D + P (bấm Alt + D trước rồi bấm phím P sau)
- Làm theo hướng dẫn như trong video:

[video=youtube;kE_x5ucMcHc]https://www.youtube.com/watch?v=kE_x5ucMcHc&feature=youtu.be[/video]

- Sau khi PivotTable được tạo thành, bạn double click vào cell giao điểm của 2 cái Grand Total (Grand Total của Row và Grand Total của Column). Lập tức một bảng tính dạng Unpivot sẽ được tạo trên 1 sheet mới
- Copy thứ bạn cần paste vào nơi nao tùy ý
- Xóa hết mấy cái PivotTable và sheet mới tạo
- Xong!
------------------------

Cám ơn thầy rất nhiều.
Cách của thầy rất nhanh gọn và đẹp mắt. Nhưng bảng dữ liệu của em lớn quá, lúc bung giá trị grand total thì chịu không nổi, không đủ dòng để hiển thị.
Nếu không có cách nào thì chắc em phải tách ra làm từ từ 1 lần vài cột.

skullrock · 10/9/15

Cá ngừ F1 đã viết:
Ở cột 2: tôi chưa biêt Quy luật xuất hiện X, Y, Z ... là gì?

Dạ, không có quy luật gì hết anh. Anh có thể xem thêm file em đang làm ở bài #7.

gtri · 10/9/15

skullrock đã viết:
Đây là bảng dữ liệu mình đang làm, mình mới làm đc vài cột có giá trị thôi, nhưng sẽ hoàn chỉnh hết.
Đây là bảng đơn giá khoán, các dòng là các sản phẩm, các cột là các máy.
Do đặc thù sản xuất nên 1 máy chạy được nhiều sản phẩm và 1 sản phẩm có thể chạy được ở nhiều máy. Mỗi máy lại có công suất khác nhau. Nên mình không làm gọn lại bảng dữ liệu được.
Bạn xem giúp coi được không.
Cám ơn.

Mã:

Public Sub QG()
Dim DL As Range, cll As Range, kq(), i

With Sheet1
Set DL = .Range(.Cells(2, 2), .Cells(.Range("A2").End(xlDown).Row, .Range("XFD1").End(xlToLeft).Column))
ReDim kq(1 To 1000000, 1 To 3)

For Each cll In DL.SpecialCells(xlCellTypeConstants)
If Trim(cll.Value) <> "" Then
i = i + 1
kq(i, 1) = .Cells(cll.Row, 1)
kq(i, 2) = .Cells(1, cll.Column)
kq(i, 3) = cll.Value
End If
Next cll
End With

Sheet2.UsedRange.Clear
Sheet2.Range("A1").Resize(i, 3) = kq
End Sub

Tạo mới sheet 2
kết quả dán vào sheet2 mới tạo

Ba Tê · 10/9/15

skullrock đã viết:
Dạ, không có quy luật gì hết anh. Anh có thể xem thêm file em đang làm ở bài #7.

- Tình huống tất cả các ô trong bảng đều có số liệu thì kết quả sẽ có trong 1 cột là 70.000x125= gần 9 triệu dòng, mà excel chỉ có 1.048.576 dòng.
- Nếu chia ra mỗi lần duyệt 5.000 dòng x 125 cột, thì kết quả tối đa là 625.000 dòng
Như vậy, để tránh tình huống này, chỉ có thể tách bảng dữ liệu ra thành từng nhóm, mỗi nhóm 5.000 dòng, kết quả ghi vào từng nhóm 3 cột khác nhau ở sheet khác.
Đây là code viết theo kiểu trên:

PHP:

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, N As Long, Rws As Long, CoL As Long, K As Long, iCoL As Long
Application.ScreenUpdating = False
With Sheets("GIA KHOAN")
    Rws = .Range("A2").End(xlDown).Row
    CoL = .Range("B1").End(xlToRight).Column
    tArr = .Range("A1").Resize(, CoL).Value
    iCoL = 1
    Sheets("GPE").Range("A3").Resize(5000, 200).ClearContents
    For N = 2 To Rws Step 5000
        K = 0
        sArr = .Range("A" & N).Resize(5000, CoL).Value
        ReDim dArr(1 To 1000000, 1 To 3)
        For I = 1 To 5000
            For J = 2 To CoL
                'If sArr(I, J) <> Empty Then    ''<------------Bỏ dấu nháy đầu dòng'
                    K = K + 1
                    dArr(K, 1) = sArr(I, 1)
                    dArr(K, 2) = tArr(1, J)
                    dArr(K, 3) = "ABC"          '------------- Thay "ABC" thành sArr(I, J)'
                'End If                                ''-------------Bỏ dấu nháy đầu dòng'
            Next J
        Next I
        If K Then Sheets("GPE").Cells(3, iCoL).Resize(K, 3).Value = dArr
        iCoL = iCoL + 4
    Next N
End With
End Sub

Với dữ liệu thật, không phải tất cả các ô đều có số liệu thì bạn sửa lại code trên theo các ghi chú trong code.
--- Trong file là chạy theo dữ liệu hiện có của bạn.

ndu96081631 · 10/9/15

skullrock đã viết:
Cám ơn thầy rất nhiều.
Cách của thầy rất nhanh gọn và đẹp mắt. Nhưng bảng dữ liệu của em lớn quá, lúc bung giá trị grand total thì chịu không nổi, không đủ dòng để hiển thị.
Nếu không có cách nào thì chắc em phải tách ra làm từ từ 1 lần vài cột.

File xlsx chứa hơn 1 triệu dòng mà còn không đủ thì thật không hiểu nỗi!

kbw1 · 11/9/15

ndu96081631 đã viết:
Có 1 cách đơn giản nhưng ít người biết: UnPivotTable
- Đầu tiên bấm tổ hợp phím Alt + D + P (bấm Alt + D trước rồi bấm phím P sau)
- Làm theo hướng dẫn như trong video:

[video=youtube;kE_x5ucMcHc]https://www.youtube.com/watch?v=kE_x5ucMcHc&feature=youtu.be[/video]

- Sau khi PivotTable được tạo thành, bạn double click vào cell giao điểm của 2 cái Grand Total (Grand Total của Row và Grand Total của Column). Lập tức một bảng tính dạng Unpivot sẽ được tạo trên 1 sheet mới
- Copy thứ bạn cần paste vào nơi nao tùy ý
- Xóa hết mấy cái PivotTable và sheet mới tạo
- Xong!
------------------------

Có lần e đọc được 1 bài viết vê unpivot này nhưng chưa biết ứng dụng vào thực tế kiểu gì.

ndu96081631 · 12/9/15

kbw1 đã viết:
Có lần e đọc được 1 bài viết vê unpivot này nhưng chưa biết ứng dụng vào thực tế kiểu gì.

Chỉ là việc chuyển cách bố trí dữ liệu kiểu này thành kiểu khác, thế thôi!

kbw1 · 12/9/15

ndu96081631 đã viết:
Chỉ là việc chuyển cách bố trí dữ liệu kiểu này thành kiểu khác, thế thôi!

E đọc tài liệu về Pivot trên diễn đàn mình.
Hướng dẫn cách gộp các vùng cũng mẫu cùng khung khi sử dụng Alt+D+P.
Bài này của anh nào trên diễn đàn viết ấy ạ.

Tạo danh sách từ bảng dữ liệu (1 người xem)

Người dùng đang xem chủ đề này

skullrock

Thành viên chính thức

File đính kèm

HyperLink

Thành viên hoạt động

File đính kèm

Cá ngừ F1

( ͡° ͜ʖ ͡°)

ndu96081631

Huyền thoại GPE

skullrock

Thành viên chính thức

HyperLink

Thành viên hoạt động

skullrock

Thành viên chính thức

File đính kèm

skullrock

Thành viên chính thức

skullrock

Thành viên chính thức

gtri

Thành viên tích cực

Ba Tê

Cạo Rồi Khỏi Gội

File đính kèm

ndu96081631

Huyền thoại GPE

kbw1

Thành viên tích cực

ndu96081631

Huyền thoại GPE

kbw1

Thành viên tích cực

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian