Viết giúp em code để trích dữ liệu từ bảng tổng hợp ra các bảng riêng lẻ (1 người xem)

minharsenal · 23/9/13

Chào anh/chị,
Em có một file excel gồm các cột MS, Môn, Họ tên như trong hình dưới đây. Mỗi 1 MS tương ứng với 1 tên và có thể gồm nhiều môn khác nhau.

Đầu tiên, từ bảng tổng hợp này em cần tách riêng ra thành từng bảng, mỗi bảng tương ứng với một môn khác nhau như trong hình.

Tiếp theo, yêu cầu là khi em thêm dữ liệu mới vào bảng tổng hợp ứng với MS, tên và môn thì các bảng tính riêng lẻ tương ứng với môn đó được tự động cập nhật dữ liệu mới thêm vào.
Ví dụ như trong hình 1 là MS 1004 mới sẽ được tự động đẩy vào các bảng môn Toán, Lý, Hoá.
Mong anh chị viết code giúp em. Về nội dung file excel này là em đã đơn giản hoá đi. File gốc thì số cột nhiều hơn, số hàng cũng rất lớn và cập nhật thêm liên tục. Anh chị hướng dẫn giúp em cách sửa code khi áp dụng vào file khác với ạ. Em xin cảm ơn! }}}}}

Quang_Hải · 23/9/13

Bạn copy code này chạy thử nha
Nhớ đặt tên sheet gốc là TONG HOP và sửa chỗ C65536 thành cột nào đó theo đúng file của bạn.

Mã:

Sub tachra()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim data(), dataloc As Range, i As Long, Lop(), sh As Worksheet
With Sheets("TONG HOP")
   data = .Range(.[A2], .[C65536].End(3)).Value
   Set dataloc = .Range(.[A1], .[C65536].End(3))
End With
With CreateObject("scripting.dictionary")
   For i = 1 To UBound(data)
      If Not .exists(data(i, 2)) Then .Add data(i, 2), ""
   Next
   Lop = .keys
End With
For i = 0 To UBound(Lop)
      For Each sh In Worksheets
         If sh.Name = Lop(i) Then sh.Delete
      Next
   With dataloc
      .AutoFilter 2, Lop(i)
      .SpecialCells(12).Copy
      Sheets.Add After:=Sheets(Sheets.Count)
      With ActiveSheet
         .Name = Lop(i)
         .[A1].PasteSpecial 1
         .[A:J].Columns.AutoFit
      End With
      .AutoFilter
   End With
Next
Sheets("TONG HOP").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

minharsenal · 23/9/13

Hic, mình không biết j về vba này, mình vừa chạy thử đoạn code trên thì thấy báo lỗi Run-time error 1004, nhưng ấn end thì vẫn thấy dữ liệu được tách ra thành các sheet khác nhau. Nhưng dữ liệu gốc thì lại bị xoá đi. Bạn có thể hướng dẫn thêm cho mình được không?

Quang_Hải · 23/9/13

minharsenal đã viết:
Hic, mình không biết j về vba này, mình vừa chạy thử đoạn code trên thì thấy báo lỗi Run-time error 1004, nhưng ấn end thì vẫn thấy dữ liệu được tách ra thành các sheet khác nhau. Nhưng dữ liệu gốc thì lại bị xoá đi. Bạn có thể hướng dẫn thêm cho mình được không?

Bạn xem file coi đúng chưa.

SA_DQ · 23/9/13

Mình giới thiệu đến bạn các macro sự kiện 1 khi kích hoạt trang tương ứng

Hãy nhập thêm dữ kiện & sau đó mở lần lượt từng trang, sẽ thấy kết quả được cập nhật

minharsenal · 24/9/13

Cám ơn bạn. Mình vừa thử, chạy rất tốt bạn ạ. Chỉ không hiểu sao nó lại nhảy ra đến 2 sheet mới cùng tên là Toán.
Bạn giải thích giúp mình ý nghĩa các dòng lệnh để mình có thể hiểu và edit được với. Ngoài ra nếu trong các sheet lẻ mình muốn chọn sắp xếp theo thứ tự abc ở cột tên thì làm thế nào?

Quang_Hải · 24/9/13

minharsenal đã viết:
Cám ơn bạn. Mình vừa thử, chạy rất tốt bạn ạ. Chỉ không hiểu sao nó lại nhảy ra đến 2 sheet mới cùng tên là Toán.
Bạn giải thích giúp mình ý nghĩa các dòng lệnh để mình có thể hiểu và edit được với. Ngoài ra nếu trong các sheet lẻ mình muốn chọn sắp xếp theo thứ tự abc ở cột tên thì làm thế nào?

Do dữ liệu 2 chữ toán không giống nhau, thừa 1 khoảng trắng
Sửa lại dòng lệnh này
If Not .exists(Trim(data(i, 2))) Then .Add Trim(data(i, 2)), ""

...................
Cột tên dính chùm thế sao sắp xếp được. Cái này mình thua nhé

minharsenal · 24/9/13

Mình sửa đuợc rồi :X
Ý mình là nếu muốn sắp xếp theo thứ tự abc ở 1 cột thì phải thêm câu lệnh như thế nào? Như cột tên đó lúc đầu mình không để ý, dữ liệu sẽ nhập họ với đệm riêng 1 cột, tên riêng 1 cột, rồi sẽ xếp thứ tự abc theo cột tên đó.

dvh.hy.9902 · 24/9/13

quanghai1969 đã viết:
Do dữ liệu 2 chữ toán không giống nhau, thừa 1 khoảng trắng
Sửa lại dòng lệnh này
If Not .exists(Trim(data(i, 2))) Then .Add Trim(data(i, 2)), ""

...................
Cột tên dính chùm thế sao sắp xếp được. Cái này mình thua nhé

- Mới xem về VBA Thấy chỗ này hay quá.
- Cảm ơn Anh Quanghai1969
- Nhân đây Anh cho em hỏi một chút. Cách làm thế nào để bấm vào nút button1 thì đoàn mã code đó được thực hiện. Và nếu tạo thêm ra một button2 để thực hiện một lệnh tiếp theo nào đó cùng nằm trong sheet tổng hợp thì làm thé nào?. Em mới xem nên chưa biết về VBA mong được anh chỉ bảo?. Em cảm ơn!.

dvh.hy.9902 · 24/9/13

SA_DQ đã viết:
Hãy nhập thêm dữ kiện & sau đó mở lần lượt từng trang, sẽ thấy kết quả được cập nhật

Anh SA_DQ cách chạy code của anh thế nào?. híc híc vấn đề này mới tiếp xúc hỏng biết mong anh giải thích giúp.

VetMini · 24/9/13

minharsenal đã viết:
Cám ơn bạn. Mình vừa thử, chạy rất tốt bạn ạ. Chỉ không hiểu sao nó lại nhảy ra đến 2 sheet mới cùng tên là Toán.
Bạn giải thích giúp mình ý nghĩa các dòng lệnh để mình có thể hiểu và edit được với. Ngoài ra nếu trong các sheet lẻ mình muốn chọn sắp xếp theo thứ tự abc ở cột tên thì làm thế nào?

quanghai1969 đã viết:
Do dữ liệu 2 chữ toán không giống nhau, thừa 1 khoảng trắng
Sửa lại dòng lệnh này
If Not .exists(Trim(data(i, 2))) Then .Add Trim(data(i, 2)), ""

...................
Cột tên dính chùm thế sao sắp xếp được. Cái này mình thua nhé

Bài học căn bản cho bất kỳ ai bước vào làm việc với dữ liệu:

Trước khi làm việc "tổng hợp" hay "phân tách" dữ liệu, bạn phải xem trước tích chất chuẩn của dữ liệu.

Việc đầu tiên là filter chúng xem có bị tình trạng một dữ liệu 2-3 cách trình bày hay không.

tinhnv1 · 24/9/13

Bài viết rất bổ ích. Anh SA_DQ viết code gộp nội dung các sheets vào 1 sheets giúp nhé.
!

SA_DQ · 24/9/13

Thích thì chìu!

[Thongbao]Bài viết rất bổ ích;
Anh SA_DQ viết code gộp nội dung các sheets vào 1 sheets giúp nhé!
[/ThongBao]

dvh.hy.9902 · 24/9/13

SA_DQ đã viết:
[Thongbao]Bài viết rất bổ ích;
Anh SA_DQ viết code gộp nội dung các sheets vào 1 sheets giúp nhé!
[/Thongbao]

- Hay quá. Chọn môn là có ngay.
- Anh ơi cho em hỏi một chút. hì hì (Cách làm thế nào để chọn tên môn như trong sheet Report ở cột [D2] mà lại chạy được vậy). Vấn đề này em lơ mơ quá, mong anh giải thích giúp em với). Cũng như trong bài của Anh Quanghai1969 làm thế nào để bấm vao button1 là được (Cách làm như nào vậy anh. Hoặc có bài nào hướng dẫn cách làm về vấn đề này chỉ cho em với. Em)

SA_DQ · 24/9/13

[ThongBao]
- Anh ơi cho em hỏi một chút. hì hì (Cách làm thế nào để chọn tên môn như trong sheet Report ở cột [D2] mà lại chạy được vậy). Vấn đề này em lơ mơ quá, mong anh giải thích giúp em với). Cũng như trong bài của Anh Quanghai1969 làm thế nào để bấm vao button1 là được (Cách làm như nào vậy anh. Hoặc có bài nào hướng dẫn cách làm về vấn đề này chỉ cho em với. Em)[/ThongBao]

Mã:

Option Explicit
[B]Private Sub Worksheet_Change(ByVal Target As Range)
[/B]1 If Not Intersect(Target, [D2]) Is Nothing Then
    Dim Sh As Worksheet    
3    Set Sh = ThisWorkbook.Worksheets("TongHop")
    [b5].CurrentRegion.Offset(1, 1).ClearContents
5    Sh.[AB2].Value = Target.Value
    Sh.[B2].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sh.[AB1:AB2], _
        CopyToRange:=Sh.[Ad1].Resize(, 3), Unique:=False
7    Sh.[ad2].CurrentRegion.Offset(1).Copy Destination:=[b5]
    Randomize
9    [b4].Resize(, 3).Interior.ColorIndex = 34 + 9 * Rnd() \ 1
 End If
[B]End Sub[/B]

Mình sẽ dịch mã lệnh này, bạn xem có hiểu thêm tí nào không?

D1: Đây là 1 macro sự kiện, 1 khi ta tác động lên ô nào đó qui ước thì macro sẽ làm việc của nó; (Cụ thể là ta thay gì đó ở ô [D2]

D2: Khai báo 1 biến đối tượng kiểu Worksheet để làm việc với trang tính;

D3: Gán trang 'TongHop' vô biến vừa khai báo;

D4: Xóa dữ liệu của lần chạy macro gần nhất;

D5: Gán trị thích hợp cho vùng điều kiện để lọc ( D6)

D6: Lọc mở rọng (Advanced); Đưa ra kết quả tại vùng cột [AD:Af]

D7: Copy vùng kết quả lọc dán lên trang Report

D8 & D9: Tơ màu cho vui mắt!

dvh.hy.9902 · 24/9/13

Cảm ơn bạn đã giải thích giúp mình. Có phải chỗ này If Not Intersect(Target, [D2]) Is Nothing. là để chọn giá trị
vùng giá trị được trọn nằm trên cột AI ở sheet tonghop không?. mình chưa hiểu cách gán vùng giá trị được chọn đó (vì các cột và dòng được gán mình nhìn chưa ra), đừng cười mình nhé, VBA của mình = 0 mà, mới đọc chỉ biết bấm mỗi Enable macro để chạy thôi. Thấy công dụng của VBA quá hay vậy mới tìm hiểu chưa được gì mặt mũi đã tối sầm. -+*/

Quang_Hải · 25/9/13

dvh.hy.9902 đã viết:
Cảm ơn bạn đã giải thích giúp mình. Có phải chỗ này If Not Intersect(Target, [D2]) Is Nothing. là để chọn giá trị
vùng giá trị được trọn nằm trên cột AI ở sheet tonghop không?. mình chưa hiểu cách gán vùng giá trị được chọn đó (vì các cột và dòng được gán mình nhìn chưa ra), đừng cười mình nhé, VBA của mình = 0 mà, mới đọc chỉ biết bấm mỗi Enable macro để chạy thôi. Thấy công dụng của VBA quá hay vậy mới tìm hiểu chưa được gì mặt mũi đã tối sầm.

Nếu bạn đọc hiểu được tiếng Anh thì sẽ hiểu câu lệnh đó và những câu lệnh khác dễ dàng hơn.

minharsenal · 25/9/13

quanghai1969 đã viết:
Nếu bạn đọc hiểu được tiếng Anh thì sẽ hiểu câu lệnh đó và những câu lệnh khác dễ dàng hơn.

Bạn quanghai1969 viết nốt giúp mình phần tự động sắp xếp dữ liệu abc theo 1 cột trong sheet riêng lẻ với. Cám ơn bạn nhiều!

Quang_Hải · 25/9/13

minharsenal đã viết:
Bạn quanghai1969 viết nốt giúp mình phần tự động sắp xếp dữ liệu abc theo 1 cột trong sheet riêng lẻ với. Cám ơn bạn nhiều!

Mình đã nói là cột tên dính chùm như thế thì làm sao sắp xếp theo ABC. Pó tay rồi mà.

minharsenal · 25/9/13

quanghai1969 đã viết:
Mình đã nói là cột tên dính chùm như thế thì làm sao sắp xếp theo ABC. Pó tay rồi mà.

Ý mình là mình sẽ nhập dữ liệu từ đầu là họ đệm 1 cột, tên 1 cột. Và sau đó muốn tự động sắp xếp thứ tự theo ưu tiên là cột tên rồi đến cột họ đệm. File bạn viết, mình đã tách riêng họ và tên. Bạn code tiếp giúp mình nhé!

ChanhTQ@ · 25/9/13

[ThongBao] Có phải chỗ này:
If Not Intersect(Target, [D2]) Is Nothing
là để chọn giá trị
vùng giá trị được chọn nằm trên cột AI ở sheet tonghop không?. mình chưa hiểu cách gán vùng giá trị được chọn đó (vì các cột và dòng được gán mình nhìn chưa ra), đừng cười mình nhé, VBA của mình = 0 mà, mới đọc chỉ biết bấm mỗi Enable macro để chạy thôi.[/thongBao]

Trước tiên bạn cùng chúng tôi cần nghiền ngẫm về fương thức Intersect( Taget, Ranges)

Nôm na fương thức Intersect() này là tìm sự giao nhau của 2 đối tượng, đối tượng thứ nhất là Taget & đối tượng thứ hai sẽ được viết sau dấu fảy của fương thức;
Đối tượng Ranges ta sẽ có thể thấy sẽ là:
1 Column, như Columns("B:B")
1 Vùng địa chỉ, như Range("B35: D70")
1 Vùng gồm nhiều hàng như Rows("4:9")
Hay ô hợp chúng, như Union(Cells(2,3).Reize(3,5), Range("B35: D70"))
Cells(2,3).Reize(3,5): Là ô [C2] được mở rọng xuống 3 hàng bên dưới & mở sang fải 5 cột

Tại [D2] của trang Report ta cài sẵn danh sách (DS) Validation để chọn môn học;
Khi ta chọn 1 môn nào đó trong DS
Thì dòng lệnh số 5 của macro sẽ ngay lập tức gán trị ta chọn lên ô [AB2] của trang 'TongHop' (để làm điều kiện lọc cho dòng lệnh bên dưới liền kề)

Những mong giúp được bạn ít nhiều.

Chúc vui!

dvh.hy.9902 · 25/9/13

ChanhTQ@ đã viết:
[ThongBao] Có phải chỗ này:
If Not Intersect(Target, [D2]) Is Nothing
là để chọn giá trị
vùng giá trị được chọn nằm trên cột AI ở sheet tonghop không?. mình chưa hiểu cách gán vùng giá trị được chọn đó (vì các cột và dòng được gán mình nhìn chưa ra), đừng cười mình nhé, VBA của mình = 0 mà, mới đọc chỉ biết bấm mỗi Enable macro để chạy thôi.[/ThongBao]

Trước tiên bạn cùng chúng tôi cần nghiền ngẫm về fương thức Intersect( Taget, Ranges)

Nôm na fương thức Intersect() này là tìm sự giao nhau của 2 đối tượng, đối tượng thứ nhất là Taget & đối tượng thứ hai sẽ được viết sau dấu fảy của fương thức;
Đối tượng Ranges ta sẽ có thể thấy sẽ là:
1 Column, như Columns("B:B")
1 Vùng địa chỉ, như Range("B35: D70")
1 Vùng gồm nhiều hàng như Rows("4:9")
Hay ô hợp chúng, như Union(Cells(2,3).Reize(3,5), Range("B35: D70"))
Cells(2,3).Reize(3,5): Là ô [C2] được mở rọng xuống 3 hàng bên dưới & mở sang fải 5 cột

Tại [D2] của trang Report ta cài sẵn danh sách (DS) Validation để chọn môn học;
Khi ta chọn 1 môn nào đó trong DS
Thì dòng lệnh số 5 của macro sẽ ngay lập tức gán trị ta chọn lên ô [AB2] của trang 'TongHop' (để làm điều kiện lọc cho dòng lệnh bên dưới liền kề)

Những mong giúp được bạn ít nhiều.

Chúc vui!

- Cảm ơn bạn đã nhiệt tình giải thích giúp mình!.
- Cho mình hỏi nhé. Như vậy vùng giá trị trên mầ ô hợp chúng lại đó có thể coi là các giá trị trong mảng được không để sử dụng khai báo Dim sArr (), dArr (1 to... , 1 to ... ) và gán giá trị mon = Ucase(sheets(''Report'').[D3]) thì có được không bạn? mình thấy có bài viết trên diễn đàn như vậy và mình cũng chưa hiểu lắm về UCase. Nếu sử dụng Ucase(sheets(''Report'').[D3]) thì tại [D2] của trang Report ta cài sẵn danh sách (DS) Validation để chọn môn học không?. Rất mong được bạn và các bạn trên diện đàn hướng dẫn giúp mình. Nếu chủ đề này không đúng ở topic này thì cũng thông cảm và cho mình linhk những chủ đề đã bàn về vấn đề này để mình được hỏi. Cảm ơn các bạn; Cảm ơn diễn đàn.

SA_DQ · 25/9/13

Như vậy vùng giá trị trên mầ ô hợp chúng lại đó có thể coi là các giá trị trong mảng được không để sử dụng khai báo Dim sArr (), dArr (1 to... , 1 to ... ) và gán giá trị mon = Ucase(sheets(''Report'').[D3]) thì có được không bạn? mình thấy có bài viết trên diễn đàn như vậy và mình cũng chưa hiểu lắm về UCase. Nếu sử dụng Ucase(sheets(''Report'').[D3]) thì tại [D2] của trang Report ta cài sẵn danh sách (DS) Validation để chọn môn học không?.

Rất mong được bạn và các bạn trên diện đàn hướng dẫn giúp mình. Nếu chủ đề này không đúng ở topic này thì cũng thông cảm và cho mình linhk những chủ đề đã bàn về vấn đề này để mình được hỏi. Cảm ơn các bạn; Cảm ơn diễn đàn.

(1) Macro mà chúng ta đang nói đến chưa có xài tẹo nào về mảng.

(2) Nếu trong của sổ Immediate của VBE, ta nhập dòng lệnh
?Ucase([D2].value)

Thì ta sẽ nhận được sự trả về là 'HÓA"
Nếu lấy từ này làm chuẩn lọc thì ta sẽ không thu được gì đâu bạn;

Hình như trên bàn tiệc nhiều món ngon quá, làm bạn mất fương hướng thì fải?

dvh.hy.9902 · 25/9/13

HYen17 đã viết:
(1) Macro mà chúng ta đang nói đến chưa có xài tẹo nào về mảng.

(2) Nếu trong của sổ Immediate của VBE, ta nhập dòng lệnh
?Ucase([D2].value)

Thì ta sẽ nhận được sự trả về là 'HÓA"
Nếu lấy từ này làm chuẩn lọc thì ta sẽ không thu được gì đâu bạn;

Hình như trên bàn tiệc nhiều món ngon quá, làm bạn mất fương hướng thì fải?

Hì hì ... Cảm ơn bạn đã nhắc, không thể tham ngay được. Phải từ từ nghiền ngẫm mới được mới tiêu hóa nổi.

Viết giúp em code để trích dữ liệu từ bảng tổng hợp ra các bảng riêng lẻ (1 người xem)

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

Thành viên mới

File đính kèm

Thành viên gạo cội

Thành viên mới

Thành viên gạo cội

File đính kèm

/(hông là gì!

File đính kèm

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên hoạt động

Thành viên hoạt động

Đang đi tìm hòn đá

Thành viên mới

/(hông là gì!

File đính kèm

Thành viên hoạt động

/(hông là gì!

Thành viên hoạt động

Thành viên gạo cội

Thành viên mới

Thành viên gạo cội

Thành viên mới

0901452không62

Thành viên hoạt động

/(hông là gì!

Thành viên hoạt động

Thời gian đếm ngược.