Làm thể nào để các file excel con đều cùng liên kết đến một file excel mẹ, tạo thành bảng thống kê

[anhtan0404]

Chào mọi người,
Hiện tại, mình đang làm thủ công trong một (file mẹ) thống kê tất cả các (file con) có trong cùng một thư mục folder và trong (file mẹ) sẽ bao gồm:
1/ Số thứ tự
2/ Tên file con.
2/ Tên các sheet trong (file con).
3/ Link mở đến các sheet của file excel.
4/ (File con) sẽ được link với (file mẹ) để khi coppy không bị mất hàm công thức có liên quan đến (file mẹ)
Nhưng mình chưa làm được (file con) có link với (file mẹ), số thứ tự, tên file con, tên sheet con, và mở lại (file con) từ (file mẹ) vẫn còn làm thủ công.
Nên nhờ mọi người chỉ mình cách làm nhanh hơn và có thể viết code như thế nào?
Mình cám ơn và kèm gửi file

 

[Vũ Hiệp SP]

Dùng power query b nhé

 

[anhtan0404]

Dùng power query b nhé

Dùng power query như thế nào? và dùng cho công tác nào? Mình thật sự chưa hiểu lắm

 

[Vũ Hiệp SP]

Nó dùng để liên kết nhiều file excel lại với nhau với tùy chỉnh cụ thể
B có thể xem bài Tự học Power Query cho người mới bắt đầu (FULL 18 phút) trên youtube và rất nhiều bài hướng dẫn liên quan nhé.

 

[anhtan0404]

Nó dùng để liên kết nhiều file excel lại với nhau với tùy chỉnh cụ thể
B có thể xem bài Tự học Power Query cho người mới bắt đầu (FULL 18 phút) trên youtube và rất nhiều bài hướng dẫn liên quan nhé.

cũng hơi khó hiểu, mình tìm hiểu thì có được code VBA tạo hypelink để từ file mẹ mở các file con, nhưng lại chưa tìm thấy đến các sheet của file con.

 

[Phương Thanh103]

em gửi bác, bác mở file nhấn nút này, chọn yes.
macro này sẽ xóa data từ c3:e10000 và lấy hypelink như yêu cầu cho các file excel đặt trong cùng thư mục nha.

 

[anhtan0404]

em gửi bác, bác mở file nhấn nút này, chọn yes.
macro này sẽ xóa data từ c3:e10000 và lấy hypelink như yêu cầu cho các file excel đặt trong cùng thư mục nha.


View attachment 290874

Mình làm không được, Mình bấm không hiện ra yes. Hay là do mình chưa hiểu ý bạn

 

[Phương Thanh103]

Mình làm không được, Mình bấm không hiện ra yes. Hay là do mình chưa hiểu ý bạn

ko phải đâu do máy ban đang mặc định hạn chế macro rồi.
bạn vào file->option=>trust center=>rồi chọn như hình thử xem nhé.

 

[Quang_Hải]

Mình làm không được, Mình bấm không hiện ra yes. Hay là do mình chưa hiểu ý bạn

Sau khi tải file về máy tính. Chuột phải lên tên file, chọn Properties, chọn Unblock

Mở file và bấm chọn Enable Content đe cho macro hoạt động

 

[anhtan0404]

ko phải đâu do máy ban đang mặc định hạn chế macro rồi.
bạn vào file->option=>trust center=>rồi chọn như hình thử xem nhé.
View attachment 290878

Sau khi tải file về máy tính. Chuột phải lên tên file, chọn Properties, chọn Unblock
View attachment 290880
Mở file và bấm chọn Enable Content đe cho macro hoạt động
View attachment 290881

Kết hợp cả 2 người, mình đã làm và chạy được code.
Nhưng mà mấy file con không có link với file mẹ, vẫn bị mờ. Có cách nào được không ạ?

 

[Phương Thanh103]

Kết hợp cả 2 người, mình đã làm và chạy được code.
Nhưng mà mấy file con không có link với file mẹ, vẫn bị mờ. Có cách nào được không ạ?View attachment 290884

khúc file con có link với file mẹ mình bị quên , giờ mới thấy nhưng lại chưa rõ, ví dụ bạn link file mẹ với file con bằng cột E. vậy bạn phải cho mình vị trí cụ thể (ô nào)ở file con để mình đặt link với file mẹ, chứ mình đặt trúng chỗ sau này bạn dùng bạn xóa thì mất công

 

[anhtan0404]

khúc file con có link với file mẹ mình bị quên , giờ mới thấy nhưng lại chưa rõ, ví dụ bạn link file mẹ với file con bằng cột E. vậy bạn phải cho mình vị trí cụ thể (ô nào)ở file con để mình đặt link với file mẹ, chứ mình đặt trúng chỗ sau này bạn dùng bạn xóa thì mất công

À, bạn làm quá ok rồi. Và ý mình muốn hỏi là chỗ "Edit link" của (file con A) bị mờ. Nếu mình coppy 1 công thức ở (file con B) qua thì sẽ vào làm lại thao tác Edit link / change source lại đường dẫn file mẹ.
Vì vậy có cách nào kết hợp code của bạn, thì tất cả file con A, file con B, file con C... đều được dẫn đến link file mẹ không? để khi coppy bảng công thức qua thì không change source nhiều lần.

 

[Phương Thanh103]

À, bạn làm quá ok rồi. Và ý mình muốn hỏi là chỗ "Edit link" của (file con A) bị mờ. Nếu mình coppy 1 công thức ở (file con B) qua thì sẽ vào làm lại thao tác Edit link / change source lại đường dẫn file mẹ.
Vì vậy có cách nào kết hợp code của bạn, thì tất cả file con A, file con B, file con C... đều được dẫn đến link file mẹ không? để khi coppy bảng công thức qua thì không change source nhiều lần.

xin lỗi bạn mình đã hiểu lầm ý bạn . mà đọc lại vẫn không hiểu khúc " để khi coppy bảng công thức qua thì không change source nhiều lần." vì hiện tại file ví dụ của bạn cũng chưa có công thức nào link file mẹ. và tính huống gì khiến bạn phải change source?

nếu bạn chỉ cần dẫn link đến file mẹ thì mình sẽ link với ô A4 của sheet đâu tiên của các file con nhưng điều đó ko đảm bảo là khi copy file hoặc đổi tên, bạn không phải change source. vì source link nằm trong công thức. 1 file con , trong quá trình thao tác lỗi có thể dẫn tới kết nối với nhiều source file mẹ ở nhiều folder khác nhau ý.

 

[anhtan0404]

xin lỗi bạn mình đã hiểu lầm ý bạn . mà đọc lại vẫn không hiểu khúc " để khi coppy bảng công thức qua thì không change source nhiều lần." vì hiện tại file ví dụ của bạn cũng chưa có công thức nào link file mẹ. và tính huống gì khiến bạn phải change source?

nếu bạn chỉ cần dẫn link đến file mẹ thì mình sẽ link với ô A4 của sheet đâu tiên của các file con nhưng điều đó ko đảm bảo là khi copy file hoặc đổi tên, bạn không phải change source. vì source link nằm trong công thức. 1 file con , trong quá trình thao tác lỗi có thể dẫn tới kết nối với nhiều source file mẹ ở nhiều folder khác nhau ý.

Dạ là, (file con C) có công thức có link với (file mẹ), nhưng (file con B) và (file con A) thì chưa, nên khi mình coppy công thức từ (file con C) qua (file con B) và (file con A), thì mình phải vào edit link để change source lại ở (file con A và B). Dù tất cả file đều cùng trong 1 thư mục folder

 

[Phương Thanh103]

chắc hóng các bác khác chứ nếu công thức trong file C bạn llink với file Mẹ thì copy qua file khác nó vẫn link với file mẹ, nhưng nếu công thức file C chứa địa chỉ sheet của file C thì đem đi đâu cũng sẽ dính, mình không có cách khắc phục:ví dụ sum(e1:h1) thì sẽ ok, nhưng sum(sheet1!e1:h1) thì sẽ bị dính source.
về phía kinh nghiệm mình thì khi tạo file kiểu giống file BOQ-1 của bạn ý, là tất cả công thức dc điền đầy đủ trong file. khi mình cần tạo file BOQ-2 thì mình sẽ copy file BOQ 1 thành file khác, đổi tên thành BOQ-2 và xóa data cũ trong đó(chỉ giữ các cột, ô công thức).

trong trường hợp đặc biệt dẫn tới không thể giữ cột công thức cố định(đã từng bị):ví dụ filter công tác xây tường thì dùng công thức 1, filter công tác xây sàn thì dùng công thức 2:thì mình sẽ bỏ các công thức vào 1 sheet khác-gọi là sheet fomular (nằm trong chính file đó), lúc cần,vào sheet fomular copy. không copy từ file khác.

nếu bạn cho mình file C và file B( sau khi đã copy sang, chưa sửa source) thì sẽ dễ hình dung hơn

 

[anhtan0404]

chắc hóng các bác khác chứ nếu công thức trong file C bạn llink với file Mẹ thì copy qua file khác nó vẫn link với file mẹ, nhưng nếu công thức file C chứa địa chỉ sheet của file C thì đem đi đâu cũng sẽ dính, mình không có cách khắc phục:ví dụ sum(e1:h1) thì sẽ ok, nhưng sum(sheet1!e1:h1) thì sẽ bị dính source.
về phía kinh nghiệm mình thì khi tạo file kiểu giống file BOQ-1 của bạn ý, là tất cả công thức dc điền đầy đủ trong file. khi mình cần tạo file BOQ-2 thì mình sẽ copy file BOQ 1 thành file khác, đổi tên thành BOQ-2 và xóa data cũ trong đó(chỉ giữ các cột, ô công thức).

trong trường hợp đặc biệt dẫn tới không thể giữ cột công thức cố định(đã từng bị):ví dụ filter công tác xây tường thì dùng công thức 1, filter công tác xây sàn thì dùng công thức 2:thì mình sẽ bỏ các công thức vào 1 sheet khác-gọi là sheet fomular (nằm trong chính file đó), lúc cần,vào sheet fomular copy. không copy từ file khác.

nếu bạn cho mình file C và file B( sau khi đã copy sang, chưa sửa source) thì sẽ dễ hình dung hơn

Mình thật sự xin lỗi, hôm qua về nhà không có vào diễn đàn, nay mình kiểm tra lại là do lỗi của mình. do mình lấy (file con C) từ thư mục khác bỏ vào thư mục có chứa (file mẹ) và (file con A và B) mà lại chưa chạy lại code VBA của bạn để thống kê lại, nên khi coppy công thức nó từ (file con C) qua (file con A và B) thì nó không hiểu.
Mình đã làm được và cám ơn bạn rất rất nhiều ạ.