Copy dữ liệu dòng thành cột bằng VBA hoặc dùng Pivot (4 người xem)

Liên hệ QC

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

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
T

tranphuson

Thành viên thường trực
Tham gia
14/8/09
Bài viết
268
Được thích
10
Giới tính
Nam
Vui lòng giúp chuyển đổi Cột thành Dòng theo file đính kèm - Do file này dữ liệu gần 500,000 dòng nên file rất nặng

Trong đây có 02 Sheet:

- Sheet 1: dữ liệu gốc "tên DATA"
- Sheet 2: Pivot - mình đang sử dụng Pivot nhưng file excel quá nặng vì còn phải xử lý nhiều file tồn kho khác trong đây. Nên mình cần hỗ trợ

1. Ở Sheet "Pivot" có thể dùng VBA lấy dữ liệu từ Sheet DATA để làm Pivot thành dữ liệu như file đính kèm - trong đây Cột C tất cả các dòng được chuyển thành từng cột riêng biệt
2. Có thể dùng VBA để chuyển đổi Cột C "Ma_Kho" thành từng cột ở Sheet "Pivot" còn lại thì dữ liệu giống như Sheet "Pivot"

Xin cảm ơn
 

File đính kèm

Không hiểu ý của bạn! Bạn nói chuyển "Cột thành Dòng" nhưng trong yêu cầu lại Cột C "thành từng cột riêng biệt"?

Theo dữ liệu trong file thì tôi nghĩ bạn muốn tổng hợp số lượng tại cột S cho từng kho theo mã vật tư?
 
tranphuson đã viết:
Vui lòng giúp chuyển đổi Cột thành Dòng theo file đính kèm - Do file này dữ liệu gần 500,000 dòng nên file rất nặng

Trong đây có 02 Sheet:

- Sheet 1: dữ liệu gốc "tên DATA"
- Sheet 2: Pivot - mình đang sử dụng Pivot nhưng file excel quá nặng vì còn phải xử lý nhiều file tồn kho khác trong đây. Nên mình cần hỗ trợ

1. Ở Sheet "Pivot" có thể dùng VBA lấy dữ liệu từ Sheet DATA để làm Pivot thành dữ liệu như file đính kèm - trong đây Cột C tất cả các dòng được chuyển thành từng cột riêng biệt
2. Có thể dùng VBA để chuyển đổi Cột C "Ma_Kho" thành từng cột ở Sheet "Pivot" còn lại thì dữ liệu giống như Sheet "Pivot"

Xin cảm ơn
Nhấp chuột vào đây để mở rộng...
Tạo sheet lưu kết quả
Mã: 
Sub XYZ()
  Dim sArr(), aKho(), aHang(), Res(), Dic As Object, iKey$
  Dim i&, sRow, k&, iR&, jC&, sR&, sC&
 
  Application.ScreenUpdating = False
  Set Dic = CreateObject("scripting.dictionary")
  Dic.CompareMode = vbTextCompare
  ReDim aKho(1 To 1, 1 To 16000) 'Tam lay 16000 Cot
  ReDim aHang(1 To 10000, 1 To 7) 'Tam lay 10000 dòng (10.000 Ma Vat Tu)
  With Sheets("DATA")
    sArr = .Range("C2", .Range("X" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) = False Then
      sC = sC + 1
      Dic.Item(iKey) = sC
      aKho(1, sC) = iKey
    End If
    iKey = sArr(i, 22) & "|" & sArr(i, 20) & "|" & sArr(i, 21) & "|" & sArr(i, 8) & "|" & sArr(i, 12) & "|" & sArr(i, 7)
    If Dic.exists(iKey) = False Then
      sR = sR + 1
      Dic.Item(iKey) = sR
      aHang(sR, 1) = sArr(i, 22)
      aHang(sR, 2) = sArr(i, 20)
      aHang(sR, 3) = sArr(i, 21)
      aHang(sR, 4) = sArr(i, 8)
      aHang(sR, 5) = sArr(i, 12)
      aHang(sR, 6) = sArr(i, 7)
      aHang(sR, 7) = iKey
    End If
  Next i
  ReDim Res(1 To sR, 1 To sC)
  With Sheets("KQ")
    .Range("A5").Resize(sR, 7) = aHang
    .Range("A5").Resize(sR, 7).Sort .Range("G5"), 1, Orientation:=xlTopToBottom
    aHang = .Range("A5").Resize(sR, 7).Value
    .Range("G4").Resize(1, sC) = aKho
    .Range("G4").Resize(1, sC).Sort .Range("G4"), 1, Orientation:=xlLeftToRight
    aKho = .Range("G4").Resize(1, sC).Value
 
    For i = 1 To sR
      Dic.Item(aHang(i, 7)) = i
    Next i
    For j = 1 To sC
      Dic.Item(aKho(1, j)) = j
    Next j
    For i = 1 To sRow
      iKey = sArr(i, 22) & "|" & sArr(i, 20) & "|" & sArr(i, 21) & "|" & sArr(i, 8) & "|" & sArr(i, 12) & "|" & sArr(i, 7)
      iR = Dic.Item(iKey)
      jC = Dic.Item(sArr(i, 1))
      Res(iR, jC) = Res(iR, jC) + sArr(i, 17)
    Next i
    .Range("G5").Resize(sR, sC) = Res
  End With
  Application.ScreenUpdating = True
End Sub
 

File đính kèm

HieuCD đã viết:
Tạo sheet lưu kết quả
Nhấp chuột vào đây để mở rộng...
Chào anh @HieuCD,
Tôi đọc code thật chậm thật lâu mới hiểu thủ thuật sáng tạo của anh:
- Chỉ dùng 1 Dic cho cả mã hàng và mã kho.
- Thay đổi nội dung lưu trữ vào item của Dic mấy lần để sử dụng cho mục đích khác nhau, lần thay đổi cuối lấy toạ độ i, j mới đáng khâm phục.
- Gán aHang xuống, sort, rồi lại lấy ngược lên làm aHang (!), aKho cũng vậy.

Cho tôi hỏi thêm:
Nếu dùng 2 Dic: 1 cho kho và 1 cho mã hàng thì có vẻ ít nhức đầu hơn, sao anh chọn cách khó vậy?
 
ptm0412 đã viết:
Chào anh @HieuCD,
Tôi đọc code thật chậm thật lâu mới hiểu thủ thuật sáng tạo của anh:
- Chỉ dùng 1 Dic cho cả mã hàng và mã kho.
- Thay đổi nội dung lưu trữ vào item của Dic mấy lần để sử dụng cho mục đích khác nhau, lần thay đổi cuối lấy toạ độ i, j mới đáng khâm phục.
- Gán aHang xuống, sort, rồi lại lấy ngược lên làm aHang (!), aKho cũng vậy.

Cho tôi hỏi thêm:
Nếu dùng 2 Dic: 1 cho kho và 1 cho mã hàng thì có vẻ ít nhức đầu hơn, sao anh chọn cách khó vậy?
Nhấp chuột vào đây để mở rộng...
Mình dùng Dic nhiều lần nên quen, Key của dòng thêm ký tự đặc biệt "|" nên không trùng với Key của cột, item là thứ tự dòng và cột không bị nhầm lẫn khi dùng 1 Dic
 
HieuCD đã viết:
Mình dùng Dic nhiều lần nên quen, Key của dòng thêm ký tự đặc biệt "|" nên không trùng với Key của cột, item là thứ tự dòng và cột không bị nhầm lẫn khi dùng 1 Dic
Nhấp chuột vào đây để mở rộng...
Công việc của tôi ít có phải sử dụng Dic nên không kinh nghiệm mấy. Tôi có thử sử dụng Power query Table.Pivot, chắc chậm hơn code của anh, và chưa biết 500 ngàn dòng của tác giả chạy sẽ chậm đến mức nào

Tạo danh mục kho để làm tham số cho hàm Table.Pivot, kết quả ở sheet Query:
PHP: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source,{"SL_Hang_Dat_Mua", "SL_Dang_Tren_Duong_Ve",  "UnitId", "Ma_Kich_Thuoc", "Ma_Kieu_Dang", "SL_Thuc_Co",
        "SL_Dat_Truoc_Cho_Khach", "Ma_Hang", "Ma_Con", "Ma_Mau", "Ten_Cua_Hang", "Ma_Vi_Tri_Trong_Kho", "Ma_Thung_Hang", "Trang_Thai_Ma_Hang",
        "BarCode", "Ma_Khu_Vuc", "Ten_Khu_Vuc"}),
    ReorderColumns = Table.ReorderColumns(RemovedColumns,{"GROUPBRANDS", "BRAND", "THEME",   "MA_VT", "Ten_Hang","Gia_Le","Ma_Kho", "SL_Hang_Co_The_Dat_Duoc"}),
    RenamedColumns = Table.RenameColumns(ReorderColumns,{{"SL_Hang_Co_The_Dat_Duoc", "SL"}}),
    PivotData = Table.Pivot(RenamedColumns,List.Buffer(DMKho[Kho]),"Ma_Kho","SL",List.Sum)
in
    PivotData
 

File đính kèm

ptm0412 đã viết:
Cho tôi hỏi thêm:
Nếu dùng 2 Dic: 1 cho kho và 1 cho mã hàng thì có vẻ ít nhức đầu hơn, sao anh chọn cách khó vậy?
Nhấp chuột vào đây để mở rộng...
Nhiều khi ta dùng kiểu "bác học" - công thức ngắn nhất, code ngắn nhất, không 2 vòng FOR mà chỉ 1 FOR thôi ... mà không nghĩ được rằng cách "bác học" chưa chắc đã dể̃ hiểu cho người khác. Vì thế trong cuộc sống nhiều khi giáo sư giải thích thì anh nông dân không hiểu, nhưng có thể một anh từ làng quê ra đi có kiến thức mà giải thích thì anh nông dân kia lại hiểu. Tôi thì cho rằng cách "bác học" chỉ dùng khi thi thố, còn khi giải thích cho "em gà lắm" thì dùng cách dễ hiểu thôi. Thường thì cách ngắn, ít câu chữ thì không rõ ràng và dễ hiểu cho lắm.
 
tranphuson đã viết:
Vui lòng giúp chuyển đổi Cột thành Dòng theo file đính kèm - Do file này dữ liệu gần 500,000 dòng nên file rất nặng

Trong đây có 02 Sheet:

- Sheet 1: dữ liệu gốc "tên DATA"
- Sheet 2: Pivot - mình đang sử dụng Pivot nhưng file excel quá nặng vì còn phải xử lý nhiều file tồn kho khác trong đây. Nên mình cần hỗ trợ

1. Ở Sheet "Pivot" có thể dùng VBA lấy dữ liệu từ Sheet DATA để làm Pivot thành dữ liệu như file đính kèm - trong đây Cột C tất cả các dòng được chuyển thành từng cột riêng biệt
2. Có thể dùng VBA để chuyển đổi Cột C "Ma_Kho" thành từng cột ở Sheet "Pivot" còn lại thì dữ liệu giống như Sheet "Pivot"

Xin cảm ơn
Nhấp chuột vào đây để mở rộng...
Góp vui cách dùng truy vấn chéo như sau:

SQL: 
TRANSFORM SUM(SL_HANG_CO_THE_DAT_DUOC)
SELECT GROUPBRANDS,
       BRAND,
       THEME,
       MA_VT,
       TEN_HANG,
       GIA_LE,
       SUM(SL_HANG_CO_THE_DAT_DUOC) AS SL_Hang
FROM   [DATA$]
GROUP  BY GROUPBRANDS,
          BRAND,
          THEME,
          MA_VT,
          TEN_HANG,
          GIA_LE
PIVOT MA_KHO

Kết quả như hình sau:
1608347718522.png
 
Lần chỉnh sửa cuối:
HieuCD đã viết:
Tạo sheet lưu kết quả
Mã: 
Sub XYZ()
  Dim sArr(), aKho(), aHang(), Res(), Dic As Object, iKey$
  Dim i&, sRow, k&, iR&, jC&, sR&, sC&

  Application.ScreenUpdating = False
  Set Dic = CreateObject("scripting.dictionary")
  Dic.CompareMode = vbTextCompare
  ReDim aKho(1 To 1, 1 To 16000) 'Tam lay 16000 Cot
  ReDim aHang(1 To 10000, 1 To 7) 'Tam lay 10000 dòng (10.000 Ma Vat Tu)
  With Sheets("DATA")
    sArr = .Range("C2", .Range("X" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) = False Then
      sC = sC + 1
      Dic.Item(iKey) = sC
      aKho(1, sC) = iKey
    End If
    iKey = sArr(i, 22) & "|" & sArr(i, 20) & "|" & sArr(i, 21) & "|" & sArr(i, 8) & "|" & sArr(i, 12) & "|" & sArr(i, 7)
    If Dic.exists(iKey) = False Then
      sR = sR + 1
      Dic.Item(iKey) = sR
      aHang(sR, 1) = sArr(i, 22)
      aHang(sR, 2) = sArr(i, 20)
      aHang(sR, 3) = sArr(i, 21)
      aHang(sR, 4) = sArr(i, 8)
      aHang(sR, 5) = sArr(i, 12)
      aHang(sR, 6) = sArr(i, 7)
      aHang(sR, 7) = iKey
    End If
  Next i
  ReDim Res(1 To sR, 1 To sC)
  With Sheets("KQ")
    .Range("A5").Resize(sR, 7) = aHang
    .Range("A5").Resize(sR, 7).Sort .Range("G5"), 1, Orientation:=xlTopToBottom
    aHang = .Range("A5").Resize(sR, 7).Value
    .Range("G4").Resize(1, sC) = aKho
    .Range("G4").Resize(1, sC).Sort .Range("G4"), 1, Orientation:=xlLeftToRight
    aKho = .Range("G4").Resize(1, sC).Value

    For i = 1 To sR
      Dic.Item(aHang(i, 7)) = i
    Next i
    For j = 1 To sC
      Dic.Item(aKho(1, j)) = j
    Next j
    For i = 1 To sRow
      iKey = sArr(i, 22) & "|" & sArr(i, 20) & "|" & sArr(i, 21) & "|" & sArr(i, 8) & "|" & sArr(i, 12) & "|" & sArr(i, 7)
      iR = Dic.Item(iKey)
      jC = Dic.Item(sArr(i, 1))
      Res(iR, jC) = Res(iR, jC) + sArr(i, 17)
    Next i
    .Range("G5").Resize(sR, sC) = Res
  End With
  Application.ScreenUpdating = True
End Sub
Nhấp chuột vào đây để mở rộng...

Cảm ơn bạn đã giúp. Nhân tiện cho hỏi thêm là file này mình có bổ sung thêm các cột từ (Cột Z đến cột AV - Sheet "DATA")

Mình cần hoàn thiện thêm 5 yêu cầu đã được liệt kê trong file theo file đính kèm ở Sheet "KQ"

Ngoài ra, nếu file này dữ liệu lên tới gần 500,000 dòng thì file rất nặng (hơn > 30MB) - cách giảm dung lượng file xuống thấp

Xin cảm ơn
 
tranphuson đã viết:
Cảm ơn bạn đã giúp. Nhân tiện cho hỏi thêm là file này mình có bổ sung thêm các cột từ (Cột Z đến cột AV - Sheet "DATA")

Mình cần hoàn thiện thêm 5 yêu cầu đã được liệt kê trong file theo file đính kèm ở Sheet "KQ"

Ngoài ra, nếu file này dữ liệu lên tới gần 500,000 dòng thì file rất nặng (hơn > 30MB) - cách giảm dung lượng file xuống thấp

Xin cảm ơn
Nhấp chuột vào đây để mở rộng...
Dữ liệu và kết quả ví dụ không có làm sao viết code chuẩn
Giảm dung lượng file: Bỏ các định dạng trang trí, không dùng Table, không dùng Pivot ...
 
HieuCD đã viết:
Dữ liệu và kết quả ví dụ không có làm sao viết code chuẩn
Giảm dung lượng file: Bỏ các định dạng trang trí, không dùng Table, không dùng Pivot ...
Nhấp chuột vào đây để mở rộng...

Mình gửi lại file đính kèm. Vui lòng hỗ trợ lần nữa.

Xin cảm ơn
 

File đính kèm

File đính kèm

HieuCD đã viết:
Kết quả số 1 lấy từ ô nào của sheet Data? tại sao là cột "X" không phải cột "Y" ?
Nhấp chuột vào đây để mở rộng...

Kết quả số 1 lấy từ cột S Sheet "DATA" và thể hiện ở Cột X trong Sheet "KQ" - Cột "Y" là dữ liệu hàng đang đi đường ở cột AR Sheet "DATA"

Cảm ơn

1608612000451.png 1608612065521.png
 
HieuCD đã viết:
Vẫn không rỏ, chờ bạn khác viết code nha
Nhấp chuột vào đây để mở rộng...
Có thể tạm hiểu là pivot table columns 2 cấp. Nhưng tên cột khác nhau: 7 cột đáng lẽ dùng để kéo thả thì tiếng Việt, kết quả sau khi kéo là tiếng Anh. Mặc dù vậy nhưng các cột tiếng Việt kia đang theo cột, muốn kéo như Pivot thì dữ liệu gốc phải là theo dòng.
 
ptm0412 đã viết:
Có thể tạm hiểu là pivot table columns 2 cấp. Nhưng tên cột khác nhau: 7 cột đáng lẽ dùng để kéo thả thì tiếng Việt, kết quả sau khi kéo là tiếng Anh. Mặc dù vậy nhưng các cột tiếng Việt kia đang theo cột, muốn kéo như Pivot thì dữ liệu gốc phải là theo dòng.
Nhấp chuột vào đây để mở rộng...
Bài này tầm vực lớn bỏ bố. Thớt muốn viết coce từ A đến Z, nếu phải hy sinh 1 tháng lương cũng chưa đủ bù công sức.
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

vsitcnh
Hỗ trợ công thức tính điểm dựa theo điều kiện. (3 người xem)
Trả lời
13
Đọc
103
Hoàng Tuấn 868
H
1
Hỏi về đánh dấu hàng ngang hàng dọc bằng màu khi kích chuột vào 1 ô
Trả lời
7
Đọc
175
Gà Công Nghệ
Gà Công Nghệ
PhanTuHuong
Sách Lập trình VBA trong AutoCAD đã có mặt
    • Thích
    • Yêu thích
2
Trả lời
20
Đọc
1K
Mr.hieudoanxd
Mr.hieudoanxd
P
Excell code vba : Tìm Cell để sửa dữ liệu
    • Buồn
2
Trả lời
26
Đọc
522
Phạm Trường 80
P
L
Lỗi font khi convert từ pdf
Trả lời
1
Đọc
56
Hoàng Tuấn 868
H
D
Nhờ anh em giúp đỡ.
    • Buồn
Trả lời
2
Đọc
77
SA_DQ
SA_DQ
00029591
Cần hỗ trợ công thức tổng hợp dữ liệu không cần cột phụ Vlookup
Trả lời
2
Đọc
75
DeTong
D
H
Đổi số thành chữ trong office 365
Trả lời
19
Đọc
1K
HoangTan891026
H
Gà Công Nghệ
Lấy dữ liệu file excel từ sheet này sang sheet khác
Trả lời
4
Đọc
129
Gà Công Nghệ
Gà Công Nghệ
N
Mong mọi người giúp đỡ
Trả lời
1
Đọc
59
minhminh27
minhminh27
minhduc1o2
Excel lỗi font Time New Roman+ (1 người xem)
Trả lời
7
Đọc
116
minhduc1o2
minhduc1o2
F
  • Question Question
Tự động Merge Mail bằng VBA
Trả lời
0
Đọc
75
fujishima
F
H
Lọc dữ liệu trong XL
    • Cười
Trả lời
0
Đọc
46
huong.dinh
H
giaiphap
Add-Ins hổ trợ Word trộn danh sách và lưu ra nhiều tệp tin khác nhau.
    • Thích
    • Yêu thích
    • Cảm ơn
2
Trả lời
39
Đọc
9K
fujishima
F
K
xin file excel bảng đấu bóng đá
    • Yêu thích
2 3
Trả lời
40
Đọc
25K
subin1905
S
B
Xin giải pháp chống sửa, xóa dữ liệu đã nhập vào từ ngày hôm trước.
    • Thích
2
Trả lời
24
Đọc
7K
chihuumvc
C
Trungvt3
Công thức trả về định dạng không như mong muốn và viết phân số trong 1 ô.
Trả lời
1
Đọc
66
bebo021999
B
G
Hướng dẫn Lập danh sách File
    • Không thích
Trả lời
16
Đọc
269
thanhhuy_hvnh
thanhhuy_hvnh
baolong240793
Nhờ Anh/Chị hỗ trợ em hàm Randbetween() với ạ.
Trả lời
4
Đọc
362
bebo021999
B
Y
  • Question Question
Khóa VBA với mật khẩu mở
    • Thích
2 3 4
Trả lời
60
Đọc
5K
PhanTuHuong
PhanTuHuong
Xem thêm…

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

000
Ngày
00
Giờ
00
phút
00
giây
TALK SHOW: CÙNG NHẢY LAM BA ĐA VỚI HÀM LAMBDA (λ) EXCEL 365
Back
Top Bottom