Copy dữ liệu dòng thành cột bằng VBA hoặc dùng Pivot

Liên hệ QC
T

tranphuson

Thành viên thường trực
Tham gia
14/8/09
Bài viết
260
Đượ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

  • Tồn kho hang ngay.xlsb
    157.7 KB · Đọc: 21
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

  • Tồn kho hang ngay.xlsm
    447.4 KB · Đọc: 27
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

  • Tồn kho-Query-Pivot.xlsm
    477.1 KB · Đọc: 19
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

  • Tồn kho-Query-Pivot (bo sung).xlsm
    704.7 KB · Đọc: 4

File đính kèm

  • Tồn kho-Query-Pivot (bo sung).xlsm
    692.3 KB · Đọc: 7
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

T
Cần giúp đỡ viết measure bằng dax cho power pivot
    • Thích
Trả lời
5
Đọc
125
tranhungdao12a3
T
hoamattroicoi
Trích xuất file *.xml sang Excel cho hóa đơn điện tử áp dụng nghị định 123/2020
    • Thích
    • Yêu thích
    • Cảm ơn
4 5 6
Trả lời
109
Đọc
27K
Hoàng Tuấn 868
H
N
tự động thêm dữ liệu giữa sheet con vào sheet tổng không dùng VBA
    • Cười
Trả lời
5
Đọc
108
DeTong
D
nvhieutk
  • Question
WorksheetFunction.SumIfs() trong VBA tính sai
2
Trả lời
21
Đọc
357
VetMini
V
L
Nhờ các bạn sửa hộ em công thức if ạ
Trả lời
14
Đọc
218
ptm0412
ptm0412
thlpro
Tổng Hợp Dữ Liệu Từ Nhiều Sheet
Trả lời
8
Đọc
185
DeTong
D
PhanTuHuong
Microsoft Excel sắp hỗ trợ ngôn ngữ Python (chính thức)
    • Thích
Trả lời
2
Đọc
109
PhanTuHuong
PhanTuHuong
A
  • Question
Share file copy dữ liệu từ tờ khai hải quan.
    • Thích
Trả lời
9
Đọc
494
HUONGHCKT
H
HongĐp
Mọi người giúp e chia nhóm với ạ
Trả lời
4
Đọc
97
CHAOQUAY
C
A
  • Question
Code di dời cùng hàng nhưng cố định cột
Trả lời
9
Đọc
152
huhumalu
H
L
[Xin giúp đỡ] Công thức tính thời gian làm được tính tiền mà không tính thời gian nghỉ
2
Trả lời
20
Đọc
880
thang_nguyen1
T
S
TÍNH GIỜ ĐI TRỄ THEO CA LÀM VIỆC
Trả lời
12
Đọc
290
Phan Thế Hiệp
Phan Thế Hiệp
L
xin chào mọi người
Trả lời
0
Đọc
30
linhdan2306
L
GPE-Trợ Lý
  • Dán lên cao
[KHẨN] HƯỚNG VỀ ĐỒNG BÀO CHỊU ẢNH HƯỞNG CỦA BÃO YAGI - BẢO YÊN, LÀO CAI
    • Thích
    • Cảm ơn
    • Yêu thích
2 3 4
Trả lời
74
Đọc
3K
PhanTuHuong
PhanTuHuong
befaint
[ Python và ứng dụng ] Siêu siêu phẩm đồng bộ giữa Excel và Google Sheets theo thời gian thực
    • Thích
    • Yêu thích
Trả lời
4
Đọc
195
anhdepjai
A
B
Hỏi về căn chỉnh droplist của combobox
Trả lời
0
Đọc
56
blueskyhd199x
B
PhanTuHuong
Các địa phương thiệt hại nặng nề do bão Yagi (số 3)
    • Thích
Trả lời
11
Đọc
336
PhanTuHuong
PhanTuHuong
ongke0711
  • Question
Trộn dữ liệu từ Excel sang Word mở rộng (phiên bản ongke0711)
    • Thích
    • Yêu thích
    • Cảm ơn
11 12 13
Trả lời
246
Đọc
23K
hoangtuan.net
hoangtuan.net
H
Hàm excel để làm báo cáo sản xuất
2
Trả lời
25
Đọc
2K
hoangtuaotrang_hp_vn
H
N
  • Question
Bảo mật code VBA
    • Cười
2
Trả lời
36
Đọc
846
VetMini
V
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

Back
Top Bottom