mình có bảng tính nhôm như file đính kèm. VÌ công việc lặp đi lặp lại như do kiến thức về hàm và exel có hạn lên up bài này mong được mọi người giúp đỡ.
Mình có 2 vấn đề muốn hỏi:
1.Việc tham chiếu cell từ các sheet về sheet tổng hợp có thể dùng hàm nào dựa vào cột B(tên sheet) của sheet " TH_Giá" không? ( để bình thường mình phải gõ thủ công như ở cột l : = D2!B4 .....). Để khi mình có thay đổi tên sheet ở cột B của sheet "TH_Gia" thì cột L "đơn giá" cũng được thay đổi tới cell của sheet mình vừa thay đổi
2. Tổng hợp dữ liệu các sheet về sheet tổng hơp nhưng mình không lấy dữ liệu của toàn bộ sheet con mà chỉ lấy 1 đoan trong sheet thôi ( như trong file mình đính kèm ở sheet "TH_Nhom_kinh"
mình có bảng tính nhôm như file đính kèm. VÌ công việc lặp đi lặp lại như do kiến thức về hàm và exel có hạn lên up bài này mong được mọi người giúp đỡ.
Mình có 2 vấn đề muốn hỏi:
1.Việc tham chiếu cell từ các sheet về sheet tổng hợp có thể dùng hàm nào dựa vào cột B(tên sheet) của sheet " TH_Giá" không? ( để bình thường mình phải gõ thủ công như ở cột l : = D2!B4 .....). Để khi mình có thay đổi tên sheet ở cột B của sheet "TH_Gia" thì cột L "đơn giá" cũng được thay đổi tới cell của sheet mình vừa thay đổi
2. Tổng hợp dữ liệu các sheet về sheet tổng hơp nhưng mình không lấy dữ liệu của toàn bộ sheet con mà chỉ lấy 1 đoan trong sheet thôi ( như trong file mình đính kèm ở sheet "TH_Nhom_kinh"
1/Bạn nghiên cứu sử dụng hàm indirect (xem file gửi kèm)
2/Thêm cột phụ ở sheet TH GIA và sử dụng Macro. (Click chuột vào nút cập nhập Profile kết quả sẽ phun ra ầm ầm!!)
Tuy nhiên lưu ý bạn phải kiểm soát dữ liệu ở các sheet thành phần thật chuẩn đảm bảo các giá trị nằm đúng vị trí của nó thì các hàm mới cho ra kết quả đúng ở các sheet tổng hợp
Bạn xem file gửi kèm nhé.
1/Bạn nghiên cứu sử dụng hàm indirect (xem file gửi kèm)
2/Thêm cột phụ ở sheet TH GIA và sử dụng Macro. (Click chuột vào nút cập nhập Profile kết quả sẽ phun ra ầm ầm!!)
Tuy nhiên lưu ý bạn phải kiểm soát dữ liệu ở các sheet thành phần thật chuẩn đảm bảo các giá trị nằm đúng vị trí của nó thì các hàm mới cho ra kết quả đúng ở các sheet tổng hợp
Bạn xem file gửi kèm nhé.
Cảm ơn bạn rất nhiều có được hàm này mọi công việc của mình trở nên dễ dàng hơn rất nhiều.
Do trình độ VBA của mình còn quá kém lên chưa hiểu hết được các thuật toán của bạn. Mong bạn chỉ giáo thêm cho mình 1 chút về VBA. Như :
- vai trò của 4 modun mà bạn viết
- Ý nghĩa của cốt phụ bạn tạo ra
- và các bạn đặt điều kiện để kết thúc lấy số liệu Profile ở 1 sheet.
( theo mình hiểu :
modun 1 "Button2_Click" : modun tính
modun2 "abc()" : không hiểu ý nghĩa của modun này để làm gì ?
modun3 "Button3_Click()":
modun 4 : ẩn cột F
- cột phụ chính là số hàng sẽ nhặt ra tại sheet đó thì phải?)
Trong 4 module mình tạo thì bạn chỉ cần quan tâm đến module 1. Các module 2,3,4 chỉ là bản nháp để kiểm tra kết quả các thành phần của code trước khi lắp ráp vào code chính (hay còn gọi là test code)
Trong module 1 là chứa code để thực hiện cho hành động tạo Profile cho sheet TH Nhom Kinh.
Mình tạm diễn nôm na các bước thực thi của code gồm
Mã:
With ActiveSheet
Range("A7:F999").Select
Selection.ClearContents
End With
Bước 1: Xóa dữ liệu từ ô A7: F999
Tiếp đến đoạn code
Bước 2: Copy giá trị 2 cột B và L bên sheet TH Gia sang cột E và F của sheet TH Nhom Kinh
Tiếp đến đoạn
Mã:
With ActiveSheet
lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row
..........................................
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Bước 3: Copy công thức từ A6:C6 xuống đến dòng cuối của cột A. Dòng cuối này được xác định dựa vào Cột E. Công thức ở A6:C6 là lấy dữ liệu từ các sheet thành phần về sheet Tổng hợp. Offset trong công thức này để lấy đủ số lượng dòng Profile dựa vào số lượng dòng được tạo ra ở Bước 4
Mã:
lr = [a6].End(xlDown).Row
For i = lr To 6 Step -1
...............................................................
End If
lr = [a6].End(xlDown).Row
Next
Bước 4: Tách các dòng để số lượng dòng Profile của sheet Tổng hợp khớp với số lượng dòng Profile của các sheet thành phần. Bước 4 và Bước 2 có liên quan đến nhau, vì Bước 4 phải sử dụng toàn bộ dữ liệu được copy từ cột phụ của sheet Gia. Ví dụ L2 của cột phụ (sheet GIA) có kết quả là 12 , code sẽ tách thành 12 dòng ở bên sheet TH Nhom kinh v..v
Bước 5 (cuối code) là để kết thúc code, gồm các thao tác nhỏ : Ẩn cột F, đưa trỏ chuột về ô A6, hiện Msgbox thông báo
Nói thêm về ý Nghĩa của cột phụ bên sheet Th GIA: mục đích là để xác định được tổng số dòng profile của từng sheet thành phần. Rồi dựa vào các kết quả đó sử dụng code tách ra số lượng dòng bên sheet TH Nhom kinh khớp với số lượng dòng profile ở các sheet thành phần.Ví dụ sheet D1 vùng Profile là từ A18: D29 => cột phụ có kết quả ô L2=12 dòng.
Từ tất cả các diễn giải trên, mình nói ngắn gọn lại: code và cột phụ nhằm tạo ra 1 bảng dữ liệu mà số lượng dòng trong bảng bằng tổng số lượng dòng gộp của tất cả các bảng con ở các sheet thành phần .Phần công thức Offset+ Indirect thực hiện việc đưa dữ liệu từ các sheet thành phần về sheet Tổng hợp
Mình giải thích vậy hy vọng bạn sẽ hiểu, chỗ nào chưa hiểu cứ hỏi mình nhé Rất vui đã giúp được bạn
Riêng hàm Indirect bạn đọc link này để hiểu rõ hơn cách sử dụng: https://www.giaiphapexcel.com/diendan/threads/bài-viết-kết-hợp-vlookup-và-indirect-trong-dò-tìm-nhiều-sheet.120696/#post-755500
Trong 4 module mình tạo thì bạn chỉ cần quan tâm đến module 1. Các module 2,3,4 chỉ là bản nháp để kiểm tra kết quả các thành phần của code trước khi lắp ráp vào code chính (hay còn gọi là test code)
Trong module 1 là chứa code để thực hiện cho hành động tạo Profile cho sheet TH Nhom Kinh.
Mình tạm diễn nôm na các bước thực thi của code gồm
Mã:
With ActiveSheet
Range("A7:F999").Select
Selection.ClearContents
End With
Bước 1: Xóa dữ liệu từ ô A7: F999
Tiếp đến đoạn code
Bước 2: Copy giá trị 2 cột B và L bên sheet TH Gia sang cột E và F của sheet TH Nhom Kinh
Tiếp đến đoạn
Mã:
With ActiveSheet
lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row
..........................................
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Bước 3: Copy công thức từ A6:C6 xuống đến dòng cuối của cột A. Dòng cuối này được xác định dựa vào Cột E. Công thức ở A6:C6 là lấy dữ liệu từ các sheet thành phần về sheet Tổng hợp. Offset trong công thức này để lấy đủ số lượng dòng Profile dựa vào số lượng dòng được tạo ra ở Bước 4
Mã:
lr = [a6].End(xlDown).Row
For i = lr To 6 Step -1
...............................................................
End If
lr = [a6].End(xlDown).Row
Next
Bước 4: Tách các dòng để số lượng dòng Profile của sheet Tổng hợp khớp với số lượng dòng Profile của các sheet thành phần. Bước 4 và Bước 2 có liên quan đến nhau, vì Bước 4 phải sử dụng toàn bộ dữ liệu được copy từ cột phụ của sheet Gia. Ví dụ L2 của cột phụ (sheet GIA) có kết quả là 12 , code sẽ tách thành 12 dòng ở bên sheet TH Nhom kinh v..v
Bước 5 (cuối code) là để kết thúc code, gồm các thao tác nhỏ : Ẩn cột F, đưa trỏ chuột về ô A6, hiện Msgbox thông báo
Nói thêm về ý Nghĩa của cột phụ bên sheet Th GIA: mục đích là để xác định được tổng số dòng profile của từng sheet thành phần. Rồi dựa vào các kết quả đó sử dụng code tách ra số lượng dòng bên sheet TH Nhom kinh khớp với số lượng dòng profile ở các sheet thành phần.Ví dụ sheet D1 vùng Profile là từ A18: D29 => cột phụ có kết quả ô L2=12 dòng.
Từ tất cả các diễn giải trên, mình nói ngắn gọn lại: code và cột phụ nhằm tạo ra 1 bảng dữ liệu mà số lượng dòng trong bảng bằng tổng số lượng dòng gộp của tất cả các bảng con ở các sheet thành phần .Phần công thức Offset+ Indirect thực hiện việc đưa dữ liệu từ các sheet thành phần về sheet Tổng hợp
Mình giải thích vậy hy vọng bạn sẽ hiểu, chỗ nào chưa hiểu cứ hỏi mình nhé Rất vui đã giúp được bạn
Riêng hàm Indirect bạn đọc link này để hiểu rõ hơn cách sử dụng: https://www.giaiphapexcel.com/diendan/threads/bài-viết-kết-hợp-vlookup-và-indirect-trong-dò-tìm-nhiều-sheet.120696/#post-755500
