Các bài toán đau đầu khi xử lý khối dữ liệu lớn

[hiphopboy8x]

Em vừa hoàn thành công việc xử lý và sắp xếp số liệu đếm xe với 1 khối dữ liệu lên đến gần 30.000 row, mỗi row là 1 bản ghi thông tin về : biển số xe, màu xe, loại xe, hướng đi, hãng sx, thời gian xuất hiện ở ngã 4...
Mặc dù bây giờ công việc đã hoàn thành và đã giở hết các loại võ ra nhưng thấy mình vẫn làm thủ công quá (vì em không biết VBA). Em đưa ra 1 số bài toán làm em nhức đầu để các bác cao thủ tham khảo, xem có cách nào làm nhanh và đơn giản không nhé (em mất cả 1 tuần liên tục để xử lý đống dữ liệu này).

Đây là bảng sau khi xử lý sơ bộ thông tin về thời gian các xe xuất hiện ở ngã tư (mỗi tape là 1 ngã 4)
http://img211.imageshack.us/my.php?image=60559025.jpg

Bài toán 1: Khi có 1 khối dữ liệu đến hàng chục nghìn dòng, làm sao để select các ô đáp ứng theo điều kiện của mình (ví dụ theo hình trên, điều kiện để select là các ô có thời gian sau 9:00 AM hoặc chỉ select các ô có dữ liệu kiểu thời gian) mà không phải dùng chuột + ctrl để chọn.

Bài toán 2: Lấy ví dụ ở các hàng có car ID (biển số xe) bằng 3 ở hình trên, yêu cầu là kéo các ô có dữ liệu thời gian lên hết các dòng trên cùng của Car ID = 3 và xóa hết các hàng trống sau khi kéo mà không phải copy, cut rồi paste 1 cách thủ công. Làm như thế cho tất cả các xe có Car ID khác.

Bài toán 3: Để xem xe từ ngã tư (tape) n đến ngã tư n+1 mất bao lâu, em cần phải lấy thời gian ở tape n+1 - tape n. Ví dụ ở hình 2, muốn biết thời gian xe có biển số 3 đi từ 5 đến 6 là bao nhiêu, lấy lần lượt thời gian ở tape 6 - thời gian tape 5. (sở dĩ có 2 giá trị thời gian ở ngã tư 6 vì 2 lần em thấy xe có biển số 3 xuất hiện ở ngã tư này, tức là nó quay đi quay lại ngã tư 2 lần trong 3 tiếng quan sát). Tuy nhiên, ở ngã tư 5 chỉ thấy có 1 lần xuất hiện do có thể có 1 lần nó đi qua không ghi lại được hoặc nó đi đường khác. Ngã tư 5 cách ngã tư 6 là 2700m, thời gian đi trong điều kiện giao thông thường chỉ khoảng 15 phút. Yêu cầu là chọn giá trị (6) - (5) gần 15 phút nhất, tức là ở đây phải xóa giá trị 8:59:56 AM đi và giữ giá trị 7:02:16 AM lại. Làm tương tự với các cột và hàng khác.

EM hi vọng các cao thủ EXCEL có thể giải quyết được mấy vấn đề này nhanh hơn cả tuần của em

 

[sealand]

Mình thấy bạn đã sử dụng Auto Fil. Nếu nó chạy bình thường thì việc khác cũng cũng sử lý được. Đúng là dòng nhiều như vậy thì không thể làm thủ công được mà phải dùng các biện pháp sử lý tự động như lệnh, công thức hay VBA thôi.
Hơn nữa, việc phỏng đoán e rằng sai với thực tế ví dụ.

 

[Lệnh Hồ Đại Hiệp]

Bạn nên cho dữ liệu gọn gàng lại :

Order|Call ID|Time|Tape
1|1|9:14:23 AM|3
2|1|9:17:16 AM|3
3|1|8:57:37 AM|4
4|1|9:01:32 AM|4
5|1|7:01:30 AM|5
6|1|9:06:23 AM|5
7|1|9:02:27 AM|6
8|1|9:06:38 AM|6

Khi đó việc xử lý sẽ dễ dàng hơn.

--CV--

 

[yeudoi]

Cái này thì là lọc dữ liệu đấy bạn cứ dùng filter/Advanced filter là được hết vừa đơn giản và nhanh. Còn nếu dùng thêm thì dùng Pivottable

 

[SA_DQ]

Một cách để giải bài thứ hai của bạn

Xin xem thêm trong file đính kèm

Chú í: Trên trang tính mình đã chạy macro & kết quả ở vùng đầu của trang đó.
Muốn chạy lại macro từ đầu, ta phải chép hết vùng hai sang vùng 1
Xin tác giả cho biết để còn chỉnh sửa lại.

Option Explicit
Sub MoveValue()
 Dim eRw As Long, Ff As Long
 Dim Rng As Range:                          Dim GTr, SoHg As Integer
 
 eRw = [b65500].End(xlUp).Row + 1:          Range("J1:O1").EntireColumn.Insert
 Range("J1:O" & eRw).NumberFormat = "hh:mm:ss"
 For Ff = 2 To eRw
    With Cells(Ff, "B")
        If GTr <> .Value Then
            If Rng Is Nothing Then
                If Ff = 2 Then
                    Set Rng = .Offset(, 1)
                Else
                    Set Rng = .Offset(-1, 1)
                End If
                GTr = .Value
            Else
                SoHg = .Cells().Row - Rng.Row
                [j1].Resize(eRw, 6).Clear
                [j1].Resize(SoHg, 6).Value = Rng.Resize(SoHg, 6).Value
                
                [j1].Resize(eRw, 6).SpecialCells(xlCellTypeBlanks).Select
                Selection.Delete Shift:=xlUp
                Rng.Resize(SoHg, 6).Value = [j1].Resize(SoHg, 6).Value
                Set Rng = Nothing
            End If
        End If
    End With
 Next Ff
 Range("C1:I" & eRw).NumberFormat = "hh:mm:ss"
 Range("J1:O1").EntireColumn.Delete
End Sub

 

[ChanhTQ@]

Dễ đến khó!

Giải phần đơn giản nhất của bài I:

Option Explicit
Sub FindAllTimeValue()
 Dim Rng As Range, sRng As Range, tRng As Range
 Dim StrC As String
 
 Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 1)
' Rng.NumberFormat = "hh:mm:ss"'
 Set sRng = Rng.Find(":", , xlFormulas, xlPart)
  If Not sRng Is Nothing Then
    StrC = sRng.Address
    Do
      If tRng Is Nothing Then
        Set tRng = sRng
      Else
        Set tRng = Union(tRng, sRng)
      End If
      Set sRng = Rng.FindNext(sRng)
    Loop While Not sRng Is Nothing And sRng.Address <> StrC
  End If
  tRng.Interior.ColorIndex = 35
End Sub

 

[buoc_tiep]

Em mới học về excel, giờ em muốn trong một số ô trong danh sách có mầu phụ thuộc vào giá trị của ô khác. Ví dụ trong danh sách tổng kết điểm học sinh, em muốn ô học lực là mầu "đỏ" và text là "yếu" nếu ô điểm tổng kết <5?

 

[ChanhTQ@]

Bạn hãy tích cực tìm kiếm hơn, nha!

Giờ em muốn trong một số ô trong danh sách có mầu phụ thuộc vào giá trị của ô khác. Ví dụ trong danh sách tổng kết điểm học sinh, em muốn ô học lực là mầu "đỏ" và text là "yếu" nếu ô điểm tổng kết <5?

VD: http://www.giaiphapexcel.com/forum/showthread.php?t=344

 

[hiphopboy8x]

Em đã thử macro của bác SA DQ và bác CHANHTQ nhưng hổng ăn thua các bác ạ. Của bác SA DQ thì bị báo lỗi tham chiếu còn của bác CHANHTQ thì bấm vẫn thấy nguyên xi, k thấy select gì cả. Em cũng k hiểu sao nữa nhưng em nghĩ nếu đụng việc như em chắc các bác làm tốt. Còn như bác Lệnh hồ đại hiệp nói thực ra bản đầu tiên em làm y hệt như bác chỉ rồi, nhưng không tiện so sánh giá trị và thao tác hàng loạt nên em mới phải tách ra từng cột rời rạc.
Cách giải quyết của em là dùng chức năng fill và vlookup để lọc và bỏ các ô trống. Còn đối với bài toán 3, phải đánh dấu màu từng ô có giá trị nghi ngờ (thủ công nhất là ở chỗ này vì phải dò đến 30000 bản ghi) sau đó fill theo màu và kết hợp 2 hàm if, vlookup tiếp để tính kết quả.
Dù sao em cũng vote cho các bác mỗi người 1 sao, he he

 

[ChanhTQ@]

Của bác SA DQ thì bị báo lỗi tham chiếu còn của bác CHANHTQ thì bấm vẫn thấy nguyên xi, k thấy select gì cả.
Dù sao em cũng vote cho các bác mỗi người 1 sao, he he

Bạn vội quá, khi VOTE cho bọn này đó!

Để thấy macro của mình kết quả ra sao; bạn tải file của Sa_DQ vế máy; Chọn toàn bộ các ô của trang tính; Khi đó ấn định màu nền toàn bộ trang tính đang có dữ liệu này là màu trắng;
Sau đó chạy macro & phát biểu lại, nếu thấy cần!
Còn nếu muốn chọn được toàn bộ các ô chứa dữ liệu thời gian đang được tô màu lam kia; ta phải thay dòng lệnh cuối; Thay vì

 tRng.Interior.ColorIndex = 35

ta sẽ phải

 [B]tRng.Select[/B]

Với macro của Sa_DQ, bạn làm các bước sau:
+ Tô chọn vùng 'J127';
+ Copy & dán vô ô 'A1' (Bạn suy xem, tại sao 27 mà không là số lớn hơn?)
+ Chạy macro lần cuối
Sau đó bạn thấy có nên thật sự cảm ơn ba người bọn này hay không?!)

 

[Lệnh Hồ Đại Hiệp]

Còn như bác Lệnh hồ đại hiệp nói thực ra bản đầu tiên em làm y hệt như bác chỉ rồi, nhưng không tiện so sánh giá trị và thao tác hàng loạt nên em mới phải tách ra từng cột rời rạc.

Bạn gửi File VD lên diễn đàn, mình sẽ bố trí lại theo cách mình trình bày bên trên và đảm bảo cả 3 bài toán của bạn đều được giải quyết rất dễ dàng.

--CV--

 

[hiphopboy8x]

Um, mấy hôm nay em bận không theo dõi được, các bác nhiệt tình quá. Vấn đề mô tả yêu cầu chi tiết cũng hơi khó vì hơi dài dòng, mà dài dòng quá thì loạn lên mất, nên em nêu ý tưởng chính của công việc em đang làm cho các bác hiểu nhé:
Yêu cầu của em là phải tính thời gian chuyến đi bình quân giữa 2 ngã tư kề nhau của các loại xe khác nhau trên 1 trục đường chính của thành phố Nagaoka, Nhật Bản. Nếu 1 xe đi trên trục đường đó và ta ghi lại được biển số xe và các thông số nhận dạng (loại xe, màu xe..) của nó ở 2 ngã tư thì ta có thể tính được thời gian chuyến đi bình quân dựa vào thời gian nó xuất hiện ở 2 nơi đó. Để giải quyết vấn đề, bọn em đặt camera đồng loạt tại 7 ngã tư liên tiếp trên trục đường, liên tục quay trong 3 tiếng (mỗi ngã tư quay hết 2 cuộn băng, ví dụ ngã tư 1:tape 1-1, 1-2, viết mã là 11, 12) - chú ý là ở bảng Transpose Table trong sheet Kill blank and unique em đã gộp cả các xe ở 2 tape 11, 12 quay ở ngã tư 1 lại thành một cột gọi là Tape 1, tương tự cho các ngã tư khác.
Sau khi em đếm xe và thống kê hết toàn bộ vào sheet (original) và các xe có giá trị tính toán ra sheet Kill blank and unique. Yêu cầu là phải thao tác trên sheet này với 3 bài toán như trên.

Nhiệm vụ được giao của em là:
- Tính thời gian chuyến đi bình quân của mỗi loại xe trên các đoạn từ ngã tư 1-2, 2-3, 3-4, 4-5, 5-6, 6-7. (kết quả của em đã làm là các bảng bus, van, minivan, pickup, truck (gộp cả truck, container và các loại xe lớn), car (gộp sedan, wagon, SUV, hatchback)
Kết cấu File của em:
- Bảng gốc -Original (không cần quan tâm đến nó lắm)
- Bảng thao tác chính -Kill blank and unique (tức là bỏ các row không cần thiết đi và transpose bảng rồi, bác nào không thích có thể sử dụng bảng thứ nhất - counting table)
- Bảng code - Các mã hóa ứng với từng loại xe, có thể sử dụng để xem các mã em mã hóa trong các bảng, ví dụ loại xe số 4 là van, màu số 6 là màu lá cây đậm, 66 là lá cây nhạt...
- Các bảng khác (car, bus, truck, small van, van,...)- tương ứng với mỗi loại xe có 1 bảng kết quả (là kết quả em làm thủ công bằng các công cụ có sẵn của excel với số liệu rút từ bảng Kill blank and unique mà ra)


Vì số liệu được phân loại hơi phức tạp nên các bác có gì cần hỏi cứ hỏi em nhé, từ mai em sẽ vào topic này thường xuyên hơn. Mong được sự chỉ giáo của các bác

File đính kèm: http://download38.mediafire.com/fvmmwta5dypg/k4ycmvznyct/traffic+count.rar