Thống kê số liệu từ 2 trang tính đến trang tính 3 (Chỉ dành cho các NICK < 500 bài) (1 người xem)

SA_DQ · 28/8/12

Ví dụ ta có 2 trang tính A & B như dưới đây:

Perter|16/08/2012|8
Ivan|16/08/2012|9
Aleks|16/08/2012|10
Mark|16/08/2012|11
Aleks|18/08/2012|28
Perter|18/08/2012|29
Eva|18/08/2012|30
Mark|18/08/2012|31
Ivan|19/08/2012|32
Aleks|19/08/2012|51

Ivan|16/08/2012|61
Aleks|16/08/2012|62
Perter|16/08/2012|63
Mark|16/08/2012|64
Eva|17/08/2012|84
Mark|17/08/2012|85
Ivan|17/08/2012|86
Perter|17/08/2012|87
Aleks|17/08/2012|88
Jovan|17/08/2012|89

Trang tính thứ 3 cần hiện kết quả như sau:

| 16/08 |16/08| 17/08 |17/08| 18/08 |18/08| 19/08 |19/08
Aleks|10|62||88|28||51|
Eva||||84|30||||
Ivan|8|61||86|||32|
Jovan||||89||||
Mark|11|64||85|31||
Perter|8|63||87|29|||

/(/hững ai làm sớm & đúng sẽ có quà!

(< 100 bài ~ < 1 tuần; < 200 ~ < 6 ngày,. . . . .)

Rất mong các bạn hưởng ứng & quà sẽ công bố sau 3 ngày)

ThuNghi · 29/8/12

HYen17 đã viết:
Ví dụ ta có 2 trang tính A & B như dưới đây:

(< 100 bài ~ < 1 tuần; < 200 ~ < 6 ngày,. . . . .)

Rất mong các bạn hưởng ứng & quà sẽ công bố sau 3 ngày)

Bác Sa cứ lấy số bài để xác định tiêu chí viết code.
Đây là bài khá hay, và thú thật là khó, cỡ vài ngàn bài (chủ yếu spam như em) còn chưa nghĩ ra thuật toán viết thế nào.
1/ Dữ liệu có chắc đã sort?
2/ Thống kê theo 2 cột ngày cho từng sh.
E sẽ cố suy nghĩ và tạo new nick để trả lời thử.
Cám ơn Bác Sa.

Hai Lúa Miền Tây · 29/8/12

Theo em thì dùng cách củ chuối là union 2 sheet (Sheet1 và Sheet2) lại, đưa dữ liệu này vào 1 vùng tạm nào đó xong dùng Crosstab Query để tổng hợp. Anh ThuNghi thử nhé

ThuNghi · 29/8/12

Hai Lúa Miền Tây đã viết:
Theo em thì dùng cách củ chuối là union 2 sheet (Sheet1 và Sheet2) lại, đưa dữ liệu này vào 1 vùng tạm nào đó xong dùng Crosstab Query để tổng hợp. Anh ThuNghi thử nhé

Union hay Union All chắc là HL tính nói dùng ADO.
Tôi thử dùng nhưng cáo CrosTab gì đó thì không biết.

PHP:

Sub Thu2()
Dim strPath As String, mySQL As String
Dim Cnn As New ADODB.Connection
Dim Rcs As New ADODB.Recordset
Dim iR&, iC&, nR&, sMa$, endr&
'-------------------------------------------------------------------------
'Tuy chon Loai BC combobox phu hop SQL text sau menh de Where
strPath = ThisWorkbook.FullName
Set Cnn = New ADODB.Connection
'Tao Ket noi voi file du lieu nguon:
'Kg lay tieu de HDR=No
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Persist Security Info=False;" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
'"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
'---------------------------------------------------------------------------
'mySQL = mySQL & "UNION" & Chr(10)
mySQL = "SELECT f1, f2, sum (f3) FROM [Sheet1$]" & Chr(10)
mySQL = mySQL & "group by f1, f2" & Chr(10)
mySQL = mySQL & "UNION ALL" & Chr(10)
mySQL = mySQL & "SELECT f1, f2, sum (f3) FROM [Sheet2$]" & Chr(10)
mySQL = mySQL & "group by f1, f2" & Chr(10)
Rcs.Open mySQL, Cnn, adOpenKeyset, adLockOptimistic
'Gan vao sh

With Sheets("tmp")
  .Cells.ClearContents
  .[A1].CopyFromRecordset Rcs
End With
'Refresh lai hai bien cnEx va Rcs:
Rcs.Close: Set Rcs = Nothing
Cnn.Close: Set Cnn = Nothing
End Sub

Code trên gán tạm vào sh tmp, nhưng chuyển sang Report như kq của bác Sa thì tôi chỉ biết dùng Dic mà chả biết dùng cách khác.

ChanhTQ@ · 29/8/12

(hàng ThuNghi này luôn kè kè dao mổ, kể cả khi đi cắt tiết gà!

Đây là bài khá hay & khó, cỡ như em còn chưa nghĩ ra thuật toán viết thế nào.
1/ Dữ liệu có chắc đã sort?
2/ Thống kê theo 2 cột ngày cho từng sh.
E sẽ cố suy nghĩ và tạo new nick để trả lời thử.

(*) Mình thấy bài này trên mạng & nghỉ rằng dùng vòng lặp cũng giải quyết được vấn đề

(1) Vì ta chưa biết bao nhiêu người trong danh sách, nên trước tiên fải lập danh sách & sau đó cần tạo ra danh sách duy nhứt tại S3;

(2) Dữ liệu trang 'S3' là danh sách duy nhứt cần xếp theo vần ABC;

(3) Ta fải tìm ngày nhỏ nhứt & lớn nhất trong S1 & S2. Từ đó tạo các trường cho báo cáo.

. . . . . . . . .

. . . . . . . . .

Rất cảm ơn ThuNghi & 2 lúa đã dựng bài này dậy!

ThuNghi · 29/8/12

ChanhTQ@ đã viết:
(*) Mình thấy bài này trên mạng & nghỉ rằng dùng vòng lặp cũng giải quyết được vấn đề

(1) Vì ta chưa biết bao nhiêu người trong danh sách, nên trước tiên fải lập danh sách & sau đó cần tạo ra danh sách duy nhứt tại S3;

(2) Dữ liệu trang 'S3' là danh sách duy nhứt cần xếp theo vần ABC;

(3) Ta fải tìm ngày nhỏ nhứt & lớn nhất trong S1 & S2. Từ đó tạo các trường cho báo cáo.

1/ Lâp danh sách duy nhất từ 2 sh, cũng kg đơn giản.
a/ Copy cột 1 sh 1 vào Report
b/ Copy cột 1 sh 2 vào Report
c/ Dùng AdFi hay dùng ... để lấy danh sách duy nhất.
2/ Quay lại sh 1 và 2 tìm ngày max và min.
3/ Vòng lặp từ ngày min - > max và nhân đôi cột ngày.
4/ Dùng sumproduct hay vòng lặp duyệt qua từng dòng, cột ngày của sh Report theo sh 1 sau đó sh 2.
Nhiêu khê quá.
Chả biết có cách nào nhanh hơn Dic. Đó là lý do dùng "dao mỗ".

ChanhTQ@ · 29/8/12

2/ Quay lại sh 1 và 2 tìm ngày max và min.

Sao bước 1 ta không chép luôn 2 cột từ mỗi trang tính & nối tiếp vô nhau ở trang 'S3'
Từ 2 cột dữ liệu này ta :
(a) Lập danh sách duy nhất;
(b) Tìm Ngày Min & Ngày Max

(...) Thậm chí ta chép nối luôn dữ liệu 2 trang sang trang 3 rồi làm gì cũng được.

. . . . . .

. . . . .

Hai Lúa Miền Tây · 29/8/12

Đầu tiên ta union 2 sheet này lại:

"SELECT F1,F2,F3 " & _
"FROM [sheet1$] " & _
"Union ALL " & _
"SELECT F1,F2,F3 " & _
"FROM [sheet2$]"

Được kết quả 2 sheet này rồi thì ta có thể xử nó = crosstab query

Excel Công Cụ Tuyệt Vời Của Bạn · 29/8/12

Làm theo anh HL gợi ý thử như sau:

Mã:

Sub Test1()


Set Cnn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
With Cnn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.FullName & _
                        ";Extended Properties=""Excel 8.0;HDR=No;"";"
    .Open
End With
With adoRS
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .ActiveConnection = Cnn
    .Open "SELECT F1,F2,F3 " & _
          "FROM [sheet1$] " & _
          "Union ALL " & _
          "SELECT F1,F2,F3 " & _
          "FROM [sheet2$]"


End With
Sheet4.Range("A1").CopyFromRecordset adoRS
adoRS.Close
adoRS.Open "TRANSFORM Sum(F3) AS SumOfC1 " & _
            "SELECT F1 as T, Sum(F3) AS [Tong] " & _
            "FROM [tmp$] " & _
            "GROUP BY F1 " & _
            "PIVOT Format([F2],'dd/mm') "
            
Sheet3.Range("a2").CopyFromRecordset adoRS
For i = 0 To (adoRS.Fields.Count - 1)
   Sheet3.Cells(1, i + 1) = adoRS.Fields(i).Name
   
Next
Sheet4.Range("a1:c6000").ClearContents
adoRS.Close
Set adoRS = Nothing
Cnn.Close




End Sub

ThuNghi · 29/8/12

Mới học Excel đã viết:

Làm theo anh HL gợi ý thử như sau:

Mã:

Sub Test1()


Set Cnn = New ADODB.Connection
 ...
Sheet4.Range("A1").CopyFromRecordset adoRS
adoRS.Close
adoRS.Open "TRANSFORM Sum(F3) AS SumOfC1 " & _
            "SELECT F1 as T, Sum(F3) AS [Tong] " & _
            "FROM [tmp$] " & _
            "GROUP BY F1 " & _
            "PIVOT Format([F2],'dd/mm') "
            
Sheet3.Range("a2").CopyFromRecordset adoRS
For i = 0 To (adoRS.Fields.Count - 1)
   Sheet3.Cells(1, i + 1) = adoRS.Fields(i).Name
   
Next
Sheet4.Range("a1:c6000").ClearContents
adoRS.Close
Set adoRS = Nothing
Cnn.Close

End Sub

"Mới học Excel" - Mới học mà siêu quá nhỉ.
Học thêm 1 chiêu CrossTab.
Nhưng hình như kq kg như đáp án. Mỗi ngày phải có 2 cột mới OK.
Và tại sao khi gán sang tmp thì dùng Union thôi, bỏ All đi => Nó sẽ kg phân thành 2 phần, nếu điều này dùng để làm 2 cột thì OK.
Có cách gì dùng rec from rec không? Đỡ mất công gán sang tmp.

Excel Công Cụ Tuyệt Vời Của Bạn · 30/8/12

ThuNghi đã viết:
"Mới học Excel" - Mới học mà siêu quá nhỉ.
Học thêm 1 chiêu CrossTab.
Nhưng hình như kq kg như đáp án. Mỗi ngày phải có 2 cột mới OK.
Và tại sao khi gán sang tmp thì dùng Union thôi, bỏ All đi => Nó sẽ kg phân thành 2 phần, nếu điều này dùng để làm 2 cột thì OK.
Có cách gì dùng rec from rec không? Đỡ mất công gán sang tmp.

Em thấy kết quả cột nên gom lại theo ngày duy nhất cho dể kiểm soát, nếu dùng crosstab mà kết quả thu được ngày như thế thì không thể được. Ngoài ngày khác ra còn khác là dư cột tổng, nếu thế ta bỏ cột tổng như sau:

adoRS.Open "TRANSFORM Sum(F3) " & _
"SELECT F1 as [GPE_COM] " & _
"FROM [tmp$] " & _
"GROUP BY F1 " & _
"PIVOT Format([F2],'dd/mm') "

Hai Lúa Miền Tây · 30/8/12

ThuNghi đã viết:
Có cách gì dùng rec from rec không? Đỡ mất công gán sang tmp.

Giải quyết bài toán này mà không dùng 1 sheet phụ là hoàn toàn có thể làm được bằng cách lồng 2 query đó vào 1 là được. Mới học Excel và anh ThuNghi thử nhé.

Excel Công Cụ Tuyệt Vời Của Bạn · 30/8/12

Hai Lúa Miền Tây đã viết:
Giải quyết bài toán này mà không dùng 1 sheet phụ là hoàn toàn có thể làm được bằng cách lồng 2 query đó vào 1 là được. Mới học Excel và anh ThuNghi thử nhé.

Theo gợi ý của anh, em làm thử như sau:

Mã:

Sub TrichLoc()


Set Cnn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
With Cnn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.FullName & _
                        ";Extended Properties=""Excel 8.0;HDR=No;"";"
    .Open
End With
With adoRS
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .ActiveConnection = Cnn
    .Open "TRANSFORM Sum(F3) " & _
           "SELECT F1 as [GPE_COM] " & _
           "FROM (SELECT F1,F2,F3 " & _
           "FROM [sheet1$] " & _
           "Union ALL " & _
           "SELECT F1,F2,F3 " & _
           "FROM [sheet2$]) " & _
           "GROUP BY F1 " & _
           "PIVOT Format(F2,'dd/mm') "
End With
With Sheet3
    .Cells.ClearContents
    For i = 0 To (adoRS.Fields.Count - 1)
        .Cells(1, i + 1) = Replace(adoRS.Fields(i).Name, "_", ".")
    Next
    .Range("A2").CopyFromRecordset adoRS
    
End With
adoRS.Close: Set adoRS = Nothing: Cnn.Close

End Sub

ThuNghi · 30/8/12

ExcelCongCuTuyetVoiCuaBan đã viết:
Theo gợi ý của anh, em làm thử như sau:

Mã:

Sub TrichLoc() ... .Open "TRANSFORM Sum(F3) " & _ "SELECT F1 as [GPE_COM] " & _ "FROM (SELECT F1,F2,F3 " & _ "FROM [sheet1$] " & _ "Union ALL " & _ "SELECT F1,F2,F3 " & _ "FROM [sheet2$]) " & _ "GROUP BY F1 " & _ "PIVOT Format(F2,'dd/mm') " ...

Công nhận code CrossTab này hay quá và lồng rất hay.
Nhưng BC kg như kq của bác Sa.
Vậy đề xuất là thế này nhé, dùng ADO và kết hợp arr hay for.
1/ Dùng ADO để lấy dm duy nhất (thay vì dùng AdFi) và ngày max, min.
2/ Gán DM vào report, gán ngày từ min-> max và nhân đôi dòng ngày (for i)
3/ Dùng ADO để lấy sh1, sh 2 và truy xuất có thể dùng Dic hay for i.
Nói chung với yêu cầu kết quả như đề bài thì để học cách viết code còn vận dụng thì chưa biết có khá thi.

ChanhTQ@ · 30/8/12

Thật ra bài này mình dùng vòng lặp, AdvancedFilter vẫn ra kết qủa theo iêu cầu . . .

Các bạn trình độ sơ cấp VBA đừng thấy người ta xài dao fẩu thuật mà nản lòng với í định vòng lặp của mình!

(húc thành công!

hoangvuluan · 30/8/12

Hix, bài này nhìn thế mà cũng vui nhỉ!
Tôi thì cứ cách cổ điển mà suy nghĩ!
bước 1: làm sao tạo được cấu trúc bảng như kết quả
=> bước 1.1. cần lấy dữ liệu về ngày ở 2 sheet, nhập lại, xóa trùng, sắp xếp, xoay ngang và nhân đôi
=> bước 1.2. cần lấy tên ở 2 sheet, nhập lại, xóa trùng, sắp xếp
bước 2: làm sao để có kết quả => giải pháp dùng sumproduct (cột ngày 1 lấy ở sheet1, ngày 2 lấy ở sheet 2)
bước 3: đưa công thức vào vùng bảng đã tạo
Xong!

Excel Công Cụ Tuyệt Vời Của Bạn · 19/9/12

(< 100 bài ~ < 1 tuần; < 200 ~ < 6 ngày,. . . . .)

Vẫn theo dõi bài này lâu lắm rồi nhưng chưa thấy chủ topic ra đáp án.

Thống kê số liệu từ 2 trang tính đến trang tính 3 (Chỉ dành cho các NICK < 500 bài) (1 người xem)

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

/(hông là gì!

File đính kèm

Hãy cho rồi sẽ nhận!

❆❆❆❆❆❆❆❆

Hãy cho rồi sẽ nhận!

0901452không62

Hãy cho rồi sẽ nhận!

0901452không62

❆❆❆❆❆❆❆❆

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

File đính kèm

Hãy cho rồi sẽ nhận!

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

❆❆❆❆❆❆❆❆

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

File đính kèm

Hãy cho rồi sẽ nhận!

0901452không62

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

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

Đếm ngược thời gian