Trích xuất dữ liệu để lập Report

[usnex]

Xin chào, nhờ các anh chị GPE giúp đỡ lập công thức trích xuất dữ liệu lập Báo cáo tháng như sau (vui lòng xem file đính kèm):

1) Làm thế nào để xem được dữ liệu của từng tháng trong Report khi nhập Tháng vào cell tương ứng?
2) Định dạng Sheet dữ liệu như thế nào để có kết quả tối ưu nhất?

Mình đã tham khảo GPE rồi nhưng vẫn chưa nắm rõ. Mong các anh chị GPE giúp đỡ. Cám ơn rất nhiều.

 

[SA_DQ]

Câu I

Xin các anh chị GPE lập công thức trích xuất dữ liệu lập Báo cáo tháng như sau

1) Làm thế nào để xem được dữ liệu của từng tháng trong Report khi nhập Tháng vào cell tương ứng?

(*) Tại A1:A12 của sheet4 ta nhập tên các tháng (bằng tiếng Anh- tiếng Tàu khó chơi hơn, bạn phải tự lực vậy!)
Gán tên vùng này là 'Thang'
(**) Trong cửa sổ VBE ta chép hàm tự tạo này vô:

Option Explicit
Function ForMonth(Thg As Range) As String
ForMonth = Switch(Thg = "January", 4, Thg = "February", 10, Thg = "March", 16, Thg = "April", 22, _
   Thg = "May", 28, Thg = "June", 34, Thg = "July", 40, Thg = "August", 46, _
   Thg = "September", 52, Thg = "October", 58, Thg = "November", 64, Thg = "December", 70)
End Function

(***) Tại 'Englishl' ta thêm các trường như trong file đính kèm

(****) Tại 'April' ta thực hiện các việc như sau:
+ Ta chọn [A4] & vô menu Data chọn dòng Validation;
Tại ngăn Allow ta chọn 'List'
Tại ngăn Source ta nhập =Thang & bấm chọn 'OK'
+ Tại [B4] ta nhập =ForMonth(A4)

+ Nhập công thức còn lại mà bạn chưa hoàn chỉnh theo tham khảo từ vùng
B9:G18

Muốn thử nghiệm lần cuối, chí ít bạn phải thêm số liệu của 1 thàng nữa ngoải tháng 4 mà bạn đang có. Sau đó chọn từ hộp Combo trong 'A4' để xem kết quả.

 

[Ba Tê]

Trích xuất dữ liệu ...

Tôi không rành lắm về VBE, nhưng việc này có thể thay đổi điều kiện lọc bằng cách tạo thêm 2 ô điều kiện - ví dụ: ô B4, B6 trong sheet april - ô B4 thay đổi thì ô B6 cũng thay đổi theo. Ô B6 là điều kiện chủ yếu để thay đổi điều kiện lọc trong công thức các ô B9:G18. Bạn hãy nhập thêm dữ liệu của các tháng tiếp theo - nhớ là số lượng cột trong các tháng phải giống nhau - rồi thay đổi số 1 trong ô B4 thử xem.

 

[usnex]

Cảm ơn HYen17 đã trả lời rất nhanh và chi tiết. Theo hướng dẫn của HYen17, mình đã tạo được Report như yêu cầu. Cảm ơn câu trả lời của Ba Tê, cách làm đơn giản và hiệu quả.

Theo hướng dẫn của HYen17, mặc dù hơi vất vả (do không rành VBE) nhưng học thêm được rất nhiều. Cảm ơn HYen17.

 

[ChanhTQ@]

Nên xoay cơ sở dữ liệu lại

Nếu dữ liệu 1 năm đầy đủ, bạn sẽ có gần 80 cột, nhưng chỉ khoảng 20 dòng;

Theo mình bạn nên xoay dữ liệu lại, chỉ khoảng mươi, mươi hai cột & số hàng sẽ khoảng 80- 90

Có vậy mới giống với kết cấu của trang tính trắng mặc định, phải không bạn?

 

[usnex]

Cảm ơn ChanhTQ@ đã có bài reply.

Thực ra, bảng dữ liệu đầy đủ số hàng lên đến vài trăm (nghìn), mình chỉ lọc một phần thông tin tùy theo yêu cầu report (và đây cũng là yêu cầu đơn giản nhất, mình vẫn luyện GPE dài dài để tập tành cho các báo cáo và phân tích phức tạp hơn).

Vừa học vừa hỏi!! Mong các GPEs tiếp tục giúp đỡ.

 

[usnex]

Nhờ các anh chị GPE tiếp tục hướng dẫn mình thực hiện tiếp bài Report trên như sau:
1) Viết VBA như thế nào để lọc dữ liệu từ 2 sheet Plan và Actual của Data sheet sang các cột tương ứng ở sheet Data?
2) Tạo bảng dữ liệu như thế nào để có được Pivot Table giống như ở sheet Report?
Cám ơn GPE rất nhiều.

 

[SA_DQ]

Câu 1 của bạn đây, xin mời!

Option Explicit
Sub CopyValueFromSheets()
 Dim Zz As Byte, Ww As Byte, Col As Byte
2  Dim Sh As Worksheet:               Dim aRw As Long
 
 Sheets("Data").Select:             aRw = Range([c9], [c9].End(xlDown)).Rows.Count
4 For Zz = 1 To 12
   For Ww = 1 To 2
6      Set Sh = Sheets(Choose(Ww, "Plan", "Actual"))
      Col = 3 + (Zz - 1) * 6
8      Cells(9, Col).Offset(, Ww).Resize(aRw).Value = Sh.Cells(2, 3 + Zz).Resize(aRw).Value
   Next Ww
10  Next Zz
End Sub

 

[usnex]

Cảm ơn HYen17 nhiều !!
Nhờ các chuyên gia Pivot giúp mình câu 2 với. Đang cố gắng tự làm nhưng kết quả không được như ý. Pls xem file đính kèm.

 

[usnex]

Nhờ HYen17 giải thích giúp mình các hàm và tham số sử dụng trong đoạn VBA trên được không? Khi áp dụng vào file mẫu thì chạy rất tốt nhưng khi dữ liệu thay đổi (tăng số sheet, số cột trong sheet) thì VBA không chạy mặc dù mình đã thay đổi tên sheet và cột tham chiếu trong VBA.
Rất mong được chỉ giáo. Cám ơn rất nhiều.

 

[SA_DQ]

Mình vừa đánh số các dòng lệnh, bạn theo dõi nha

(*) Hai dòng đầu: Khai báo các biến cần thiết sẽ dùng trong macro
(*) Phần đầu dòng lệnh 3: Kích hoạt trang tính tương ứng;
Phần sau: Đếm số dòng dữ liệu có trong cột 'C' bắt đầu từ C9
(*) Từ dòng 4-10 Thiết lập vòng lặp cho 12 lần, tương đương 12 tháng;
(*) Từ dòng 5-9 Thiết lập vòng lặp 2 lần, tương đương với việc xử lý 2 trang tính tương ứng;
(*) Dòng 7 Xác định cột bắt đầu để chép (Dòng quan trọng nhứt, theo mình, của macro)
(*) Dòng 8 thực thi việc chép dữ liệu theo kích thước qui định

Cells(9, Col).Offset(, Ww).Resize(aRw).Value = _
Sh.Cells(2, 3 + Zz).Resize(aRw).Value

+ Chép từ trang tính đang có trong biến Sh
+ Chép từ dòng 2, cột có số thứ tự 3 + Zz, & vùng Copy này có aRw dòng
+ Chép đến ô thuộc dòng 9 & tại cột có trong biến Col nhưng dịch chuyển qua phải Ww cột tương ứng

Chúc thành công!

 

[usnex]

Nhờ HYen 17 xem giúp mình file này lần nữa.
Đã cố gắng làm theo hướng dẫn nhưng do 2 cột dữ liệu không liền nhau nên không ra được kết quả.
Pls xem file đính kèm nhé. Cảm ơn.

 

[SA_DQ]

Chú ý tên các trang tính!

Option Explicit
Sub CopyValueFromSheets()
 Dim Zz As Byte, Ww As Byte, Col As Byte
2  Dim Sh As Worksheet:               Dim aRw As Long
 
 Sheets("Data").Select:             aRw = Range([c9], [c9].End(xlDown)).Rows.Count
4 For Zz = 1 To 12
   For Ww = 1 To 4 Step 2 '<=|'
6      Set Sh = Sheets(Choose(Ww, "Plan", "GPE.COM",  "Actual")) '<=|'
      Col = 3 + (Zz - 1) * 6
8      Cells(9, Col).Offset(, Ww).Resize(aRw).Value = Sh.Cells(2, 3 + Zz).Resize(aRw).Value
   Next Ww
10  Next Zz
End Sub

 

[usnex]

Xin lỗi bác SA_DQ, do có trục trặc nên lâu nay không vào thank và thắc mắc được. Bác có thể gaỉi thích tên sheet GPE.COM giúp được không ạ?
@HYen17: mình làm theo hướng dẫn đã có kết quả như ý muốn rồi nhưng sao dữ liệu không chép đến row cuối cùng (chỉ đến khoảng row 34 thôi). Nhờ các GPE vui lòng chỉ dẫn thêm. Cảm ơn nhiều lắm lắm.

 

[SA_DQ]

(1) Bác có thể gaỉi thích tên sheet GPE.COM giúp được không ạ?
(2) Mình làm theo hướng dẫn đã có kết quả như ý muốn rồi nhưng sao dữ liệu không chép đến row cuối cùng (chỉ đến khoảng row 34 thôi). Nhờ các GPE vui lòng chỉ dẫn thêm. Cảm ơn nhiều lắm lắm.

(1) Thực ra thêm vô để quảng bá cho "GPE.COM" của chúng ta mà thôi; Nhưng không thể bỏ đi, bằng không sẽ báo lỗi;
Lỗi khi bỏ chuỗi đó đi, tại vì:
Bạn chú í vòng lặp nhỏ bên trong vòng lặp lớn có bước nhãy là 2; có nghĩa là 1=>3;
Nhưng để gọn chương trình, ở đây tôi không dùng If . . . Else . . . End If mà dùng sự trợ giúp của hàm CHOOSE(), nên thêm vô để khỏi báo lỗi hàm này khi Ww bỏ qua giá trị 2
(Thực ra ta có thể thay chuỗi này bằng 1 chuỗi khác tùy hứng hoặc đơn giản chỉ là bỏ "" vô đó.)

(2) Nó không chép tới hàng cuối theo mình có lẻ trong cột dữ liệu của bạn có ô trống.
Nếu vậy, thay vì câu lệnh:

aRw = Range([c9], [c9].End(xlDown)).Rows.Count

Bạn xài câu lệnh này xem sao:

aRw = Range([c9], [c65500].End(xlUp)).Rows.Count

 

[usnex]

Đúng là data có ô trống. Đã sửa lại câu lệnh aRw như trên và macro chạy tốt, nhưng mình xài code của HYen17 vẫn được (không cần chuỗi quảng bá GPE !!).
Nhờ bác SA_DQ giải thích dòng lệnh này giúp em luôn được không. Thanks bác.
Col = 3 + (Zz - 1) * 6

 

[SA_DQ]

Câu lệnh đó là gán trị cho biến Col theo sự biến thiên của Zz; Đến lượt mình Zz lại biến thiên theo chỉ số của vòng lặp 'Lớn'
Ta có thể lập bảng biến thiên này để hiểu thêm chi tiết:

( Col = 3 + (Zz - 1) * 6)

Zz | Col
1| 3
2|9
3|15
. .|. .
12|69
|

Đến lượt mình, Col là trị số cột trái nhứt của vùng cần được gán các tri
trong dòng lệnh mang số 8 bên dưới

Chúc vui

 

[usnex]

Các anh chị GPE giúp em phần PivotTable với, pls!
Cảm ơn rất nhiều.