Có cách nào tham chiếu đến một workbook mà không cần Open? (1 người xem)

[BNTT]

Tôi thường sử dụng tham chiếu đến một workbook trong công thức.
Tuy nhiên, nếu muốn công thức hiển thị kết quả thì workbook nguồn phải được mở (open).
Cho tôi hỏi, có cách nào tạo một tham chiếu đến một workbook mà không cần mở nó ra? Nghĩa là Excel sẽ âm thầm móc dữ liệu từ workbook nguồn ra làm việc ?

Ví dụ, tôi tạo một bảng danh mục sản phẩm trong một workbook có tên là DMSP.xls
Rồi tôi tạo một workbook mới, đặt tên là Nhập hàng chẳng hạn. Workbook này sẽ truy xuất giá từ DMSP.xls (bằng VLOOKUP chẳng hạn)
Ý tôi là có cách nào vẫn lấy được đơn giá của một mặt hàng để điền vào trong Nhập hàng, mà không cần phải mở cái file DMSP.xls ra?

Hoặc là, để tạo công thức trong Nhập hàng, tôi mở cả hai file ra (để lấy đường dẫn cho tham chiếu), vậy khi tôi đóng file DMSP.xls., thì làm cách nào để những đơn giá trong Nhập hàng không bị "biến mất" ?

Xin cảm ơn trước.

 

[Mr Okebab]

Tôi thường sử dụng tham chiếu đến một workbook trong công thức.
Tuy nhiên, nếu muốn công thức hiển thị kết quả thì workbook nguồn phải được mở (open).
Cho tôi hỏi, có cách nào tạo một tham chiếu đến một workbook mà không cần mở nó ra? Nghĩa là Excel sẽ âm thầm móc dữ liệu từ workbook nguồn ra làm việc ?

Ví dụ, tôi tạo một bảng danh mục sản phẩm trong một workbook có tên là DMSP.xls
Rồi tôi tạo một workbook mới, đặt tên là Nhập hàng chẳng hạn. Workbook này sẽ truy xuất giá từ DMSP.xls (bằng VLOOKUP chẳng hạn)
Ý tôi là có cách nào vẫn lấy được đơn giá của một mặt hàng để điền vào trong Nhập hàng, mà không cần phải mở cái file DMSP.xls ra?

Hoặc là, để tạo công thức trong Nhập hàng, tôi mở cả hai file ra (để lấy đường dẫn cho tham chiếu), vậy khi tôi đóng file DMSP.xls., thì làm cách nào để những đơn giá trong Nhập hàng không bị "biến mất" ?

Xin cảm ơn trước.

Em thường làm thế này : 2 cách:
Cách 1

1. Tạo 1 UDF
2. Đầu UDF : Mở File nguồn
3. Lấy giá trị
4. Cuối : Đóng File nguồn

Tất cả diễn ra âm thầm, nếu File nguồn không có công thức thì rất nhanh.

Đây là cách làm đơn giản nhất.
Cách 2

- Tạo 1 Query truy nhận giá trị File nguồn theo mã hàng hóa (hơi phức tạp hơn chút xíu)

Thân!

 

[BNTT]

Em thường làm thế này : 2 cách:
Cách 1

1. Tạo 1 UDF
2. Đầu UDF : Mở File nguồn
3. Lấy giá trị
4. Cuối : Đóng File nguồn

Tất cả diễn ra âm thầm, nếu File nguồn không có công thức thì rất nhanh.

Đây là cách làm đơn giản nhất.
Cách 2

- Tạo 1 Query truy nhận giá trị File nguồn theo mã hàng hóa (hơi phức tạp hơn chút xíu)

Thân!

Là sao nhỉ? Cái cách 1 đấy?
File nguồn của mình toàn là số liệu thô, không có công thức gì hết. Nó là một Danh mục hàng hóa bình thường thôi, gồm có Mã hàng, Nhóm hàng, Chủng loại hàng, Đơn giá nhập (loại 1, loại 2), Đơn giá bán (loại 1, loại 2), Nhà sản xuất, Nhà cung cấp.
Nhưng do mặt hàng của mình nhiều quá, mới nhập hơn một nửa mà đã hơn 18.000 record rồi. Nên mới tạo riêng nó thành một file.

OKBap có thể nói cụ thể hơn không?
Ở những file cần liên kết với Danh mục hàng hóa, không chỉ là lấy đơn giá không đâu, mà còn giá loại 1, giá loại 2, nếu nhập thì dùng đơn giá nhập, nếu bán thì lôi đơn giá bán ra, và nếu công ty xây dựng nào đó đòi phải có chủng loại thì cũng phải lấy được chủng loại ra, v.v... nghĩa là có nhiều thứ để lấy ra từ file nguồn lắm.

 

[Mr Okebab]

Là sao nhỉ? Cái cách 1 đấy?
File nguồn của mình toàn là số liệu thô, không có công thức gì hết. Nó là một Danh mục hàng hóa bình thường thôi, gồm có Mã hàng, Nhóm hàng, Chủng loại hàng, Đơn giá nhập (loại 1, loại 2), Đơn giá bán (loại 1, loại 2), Nhà sản xuất, Nhà cung cấp.
Nhưng do mặt hàng của mình nhiều quá, mới nhập hơn một nửa mà đã hơn 18.000 record rồi. Nên mới tạo riêng nó thành một file.

OKBap có thể nói cụ thể hơn không?
Ở những file cần liên kết với Danh mục hàng hóa, không chỉ là lấy đơn giá không đâu, mà còn giá loại 1, giá loại 2, nếu nhập thì dùng đơn giá nhập, nếu bán thì lôi đơn giá bán ra, và nếu công ty xây dựng nào đó đòi phải có chủng loại thì cũng phải lấy được chủng loại ra, v.v... nghĩa là có nhiều thứ để lấy ra từ file nguồn lắm.

Bác cho em 1 VD đi, để em làm luôn cho, File gốc của bác nhé.

Kiểu này phải mới bác ptm142 và em đi CF rồi.

Thân!

 

[BNTT]

Đây nè. File Excel 2007 nha.
Nếu Open cả 2 một lần, thì công thức trong "Nhap hang Ha Nam" chạy ngon ơ.
Nhưng nếu đóng cái DMSP.xlsm lại thì... chả còn gì hết.

Cái file DMSP.xlsm còn nhiều lắm, mình đã xóa bớt để nó không lớn hơn 1MB...
Trong cái file đó, có cái form, phím tắt là Ctrl+Shift+Z, OKBap nghía dùm miếng luôn nhá...

Nói thêm tí, mình đặt cái DMSP.xlsm ở trong thư mục D:\Kim-anh\ , và tham chiếu đến nó trong "Nhap hang Ha nam" cũng vậy.

(CF thì vô tư đi... không lo)

 

[hoangdanh282vn]

gấp quá nên em làm VD nho nhỏ để minh họa, anh xem nha

Sub worksheet_change(ByVal target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Dim DL As Range
If target.Column = 1 And target <> "" Then
    Workbooks.Open Filename:="FILE NGUON.xls"
End If
Workbooks("FILE NGUON.xls").Close
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[nvson]

Hiểu thêm về hàm Vlookup

Một anh trong phòng tôi có 1 file dự toán làm bằng Excel, trong đó sử dụng hàm Vlookup để lấy dữ liệu từ 1 file khác. Tôi copy file đó sang máy của tôi, không copy file dữ liệu nguồn.
Tôi thay đổi mã (cột B), thế mà các giá trị cột F cũng thay đổi theo.
(mặc dù máy tôi không có file E:\Thu vien\Dutoan mau_PHU\DG_Hai Phong.xls).
Các anh chị giải thích giúp tôi với!
Xin cảm ơn nhiều.

 

[ndu96081631]

Ai chà! Vụ này hơi bị mới à nha!
Ngay cả khi sửa Col_Index trong Vlookup từ 4 thành 2 nó cũng ra kết quả mới luôn... là cái gì.. "Khoan xoay bơm.... "
Dử liệu này nó lấy ở đâu ra nhỉ?
Tôi đang nghĩ đến việc có thể phục hồi toàn bộ bảng dử liệu gốc dựa vào công thức Vlookup này đấy! Khả thi lắm chứ

 

[TKT]

Một anh trong phòng tôi có 1 file dự toán làm bằng Excel, trong đó sử dụng hàm Vlookup để lấy dữ liệu từ 1 file khác. Tôi copy file đó sang máy của tôi, không copy file dữ liệu nguồn.
Tôi thay đổi mã (cột B), thế mà các giá trị cột F cũng thay đổi theo.
(mặc dù máy tôi không có file E:\Thu vien\Dutoan mau_PHU\DG_Hai Phong.xls).
Các anh chị giải thích giúp tôi với!
Xin cảm ơn nhiều.

Cái này bây giờ em cũng mới biết, em cũng làm thí nghiệm thử cũng y như vậy. Có lẽ Excel copy toàn bộ bảng dữ liệu vào trong những cái "bí ẩn" của excel chăng?!

 

[BNTT]

Hay thật! Ước gì vấn đề của tôi cũng có thể giải quyết như thế này nhỉ!
Nếu như khám phá ra cách làm thì có nghĩa là chúng ta có thể xóa luôn file nguồn, cho nhẹ máy, bởi vì rõ ràng là cái file do nvson cung cấp làm gì có kèm theo file nguồn (E:\... gì gì đó). Mà thậm chí trên máy tôi chẳng có ổ E, nó vẫn chạy! Lạ thật!
Tôi đã thử đi thử lại nhiều cách, thậm chí là thay đổi thử giá trị trong cột B (là phần tử mang đi dò tìm), nó cũng cho ra một kết quả mà không hề báo lỗi, y như cái file nguồn đang được mở ra môt cách bí mật ở đâu đó!!!

Và xin phép nvson, do vấn đề này hình như là liên quan đến việc link dữ liệu chứ không phải là nói về VLOOKUP, mình xin phép ghép chung với bài của mình nhé.

 

[Nguyễn Duy Tuân]

Đây nè. File Excel 2007 nha.
Nếu Open cả 2 một lần, thì công thức trong "Nhap hang Ha Nam" chạy ngon ơ.
Nhưng nếu đóng cái DMSP.xlsm lại thì... chả còn gì hết.

Cái file DMSP.xlsm còn nhiều lắm, mình đã xóa bớt để nó không lớn hơn 1MB...
Trong cái file đó, có cái form, phím tắt là Ctrl+Shift+Z, OKBap nghía dùm miếng luôn nhá...

Nói thêm tí, mình đặt cái DMSP.xlsm ở trong thư mục D:\Kim-anh\ , và tham chiếu đến nó trong "Nhap hang Ha nam" cũng vậy.

(CF thì vô tư đi... không lo)

Theo cách dùng VBA để làm chư Mr Bab đã nói cũng được. Nếu anh còn đang dùng A-Tools thì có thể tận dụng nó.

+ Tạo DBKEY tham chiếu tới file DMSP.xlsm đặt tên là DMSP
+ Trong file Nhap hang Ha Nam.xlsx anh tạo một sheet. Đặt công thức

= BS_SQL("SELECT * FROM DMSP" , "DBKEY = DMSP ; NAME = DMSP")

Sau khi ENTER, công thức trả về một bảng DMSP lấy từ file DMSP.xlsm thông qua DBKEY = DMSP và tạo một tên cho bảng dữ liệu là DMSP (NAME = DMSP).

Bây giờ từ tất cả các nơi anh có thể sử dụng các hàm EXCEL để tham chiếu tới vùng DMSP như bình thường.

 

[ThuNghi]

Không hiểu nó cài cái này ở đâu.
E:\Thu vien\Dutoan mau_PHU\DG_Hai Phong.xls
File chỉ có 1 dòng Vlookup mà tới 1M.
Các bạn tìm hộ với. Tôi thử copy sang 1 wb khac và chỉ có 1 cell Vlookup mà nó cũng OK và cũng 1M

 

[TKT]

Em thử làm một file giống anh nvson không biết được không?

 

[Mr Okebab]

Là sao nhỉ? Cái cách 1 đấy?
File nguồn của mình toàn là số liệu thô, không có công thức gì hết. Nó là một Danh mục hàng hóa bình thường thôi, gồm có Mã hàng, Nhóm hàng, Chủng loại hàng, Đơn giá nhập (loại 1, loại 2), Đơn giá bán (loại 1, loại 2), Nhà sản xuất, Nhà cung cấp.
Nhưng do mặt hàng của mình nhiều quá, mới nhập hơn một nửa mà đã hơn 18.000 record rồi. Nên mới tạo riêng nó thành một file.

OKBap có thể nói cụ thể hơn không?
Ở những file cần liên kết với Danh mục hàng hóa, không chỉ là lấy đơn giá không đâu, mà còn giá loại 1, giá loại 2, nếu nhập thì dùng đơn giá nhập, nếu bán thì lôi đơn giá bán ra, và nếu công ty xây dựng nào đó đòi phải có chủng loại thì cũng phải lấy được chủng loại ra, v.v... nghĩa là có nhiều thứ để lấy ra từ file nguồn lắm.

Em làm 1 UDF đơn giản, bác thử xem nhé.

Chú ý : Sử dụng UDF trong Sheet thì không thể mở File gốc được, nhưng trong VBA thì được. Nếu dùng Sub thì khỏi phải nói.

Thân!

P/S : Bác nhớ xả nén và chạy File Okebab nhé, còn File DanhMuc thì không đả động đến.

 

[Mr Okebab]

Một anh trong phòng tôi có 1 file dự toán làm bằng Excel, trong đó sử dụng hàm Vlookup để lấy dữ liệu từ 1 file khác. Tôi copy file đó sang máy của tôi, không copy file dữ liệu nguồn.
Tôi thay đổi mã (cột B), thế mà các giá trị cột F cũng thay đổi theo.
(mặc dù máy tôi không có file E:\Thu vien\Dutoan mau_PHU\DG_Hai Phong.xls).
Các anh chị giải thích giúp tôi với!
Xin cảm ơn nhiều.

Không hẳn chỉ có File đó, File bất kỳ đều như vậy.

Và các hàm tham chiếu khác đều vậy (Index, match)

Vậy thì đặt ra câu hỏi là : Nó lưu cả mảng thông tin đó vào đâu nhỉ ??


Thân!

 

[ThuNghi]

Không hẳn chỉ có File đó, File bất kỳ đều như vậy.

Và các hàm tham chiếu khác đều vậy (Index, match)

Vậy thì đặt ra câu hỏi là : Nó lưu cả mảng thông tin đó vào đâu nhỉ ??


Thân!

Vây nó nằm ở đâu vậy. Nóng lòng quá!

 

[BNTT]

Tôi đã tìm ra hướng giải quyết cho vấn đề của mình rồi.

Phải nói là cảm ơn nvson rất nhiều, do trường hợp mà bạn đã nêu lên, và cả 090TRI nữa, vì đã cho anh hướng giải quyết.

Vấn đề ở đây là: Với trường hợp của tôi, trong file đích, tôi dùng tham chiếu đến một Name động trong file nguồn, do đó, khi không mở file nguồn, thì file đích không thể xác định được Name động.

Sau khi nghiên cứu trường hợp của nvson và file test của 090TRI, tôi thử thay Name động bẳng địa chỉ tuyệt đối của khối cell mà tôi muốn tham chiếu đến.

Và... đã thành công. Tôi đã thử xóa luôn cái file DMSP.xlsm trên máy, những công thức trong 'Nhap hang Ha Nam' vẫn chạy vèo vèo!

Thích thật!

Với những bạn đã tải 2 file của tôi về, các bạn thử thay công thức trong cột D như sau:

=IFERROR(VLOOKUP([Mã hàng],'D:\[DMSP.xlsm]Danhmuc'!$A$2:$P$3596,3,0),IFERROR(VLOOKUP([Mã hàng],'D:\[DMSP.xlsm]Danhmuc'!$B$2:$P$3596,2,0),""))​

Với D:\ là nơi mà các bạn đang lưu cái file DMSP.xlsm.

Sau đó, các bạn xóa luôn cái file DMSP.xlsm đi, thử xem?
------------------------

Tuy nhiên, tại sao như vậy, tại sao tôi đã xóa luôn cái file nguồn, mà file đích vẫn truy xuất được, giống như câu hỏi của bác ThuNghi: "Nó nằm ở đâu?", thì chịu! Không thể giải thích được!

 

[Mr Okebab]

Tuy nhiên, tại sao như vậy, tại sao tôi đã xóa luôn cái file nguồn, mà file đích vẫn truy xuất được, giống như câu hỏi của bác ThuNghi: "Nó nằm ở đâu?", thì chịu! Không thể giải thích được!

1. Đây mới chính là điều nguy hiểm bởi khi File nguồn thay đổi mà nó lại không cập nhật, lấy luôn thông tin mà nó đã lưu của lần trước thì thật là nguy hiểm.

Mặc dù không xảy ra nhiều, nhưng trong tính toán, báo giá . . chỉ cần sai một ly là đi luôn . . sang Lào.

2. Và có một điều nữa :

Trong công thức ta đã khai báo vùng từ cột A -->F thì ta toàn quyền thay đổi để tìm các giá trị trong các cột đó, nhưng nếu vượt ra ngoài thì lại báo lỗi ngay.

TH này thì Index và match lại ăn theo Vlookup.

Vậy chứng tỏ nó đã lưu cả mảng giá trị vào phần thông tin ẩn sau của File rồi.


3. Và đây chính là cái chúng ta chú ý : Tools/Option/Calculiton/Save External Value Link

Nếu khóa này bị mất sau khi chúng ta xóa File nguồn thì coi như là . . đi tong.

Thân!

 

[yeudoi]

Bởi vậy các cao thủ phải tìm là nó lưu ở đâu để file chỉ có vài dòng công thức nhưng lại trên 1M, đó mới là vấn đề.

 

[ptm0412]

Sau khi nghiên cứu trường hợp của nvson và file test của 090TRI, tôi thử thay Name động bẳng địa chỉ tuyệt đối của khối cell mà tôi muốn tham chiếu đến.

Tôi đã phát hiện vấn đề name động này, nếu name tĩnh hoặc địa chỉ vùng thì OK, tuy nhiên khi mở file vẫn sẽ có 1 thông báo (Excel 2003) yêu cầu xác nhận link, thông báo này có thể tắt. Đối với Excel 2007 thì không thông báo ngay nhưng có thanh Security warning với 1 nút option nhằm enable link. Cái thanh này cũng có thể tắt không cho hiển thị.

Còn vấn đề xoá file nguồn, theo tôi là tuyệt đối không nên vì:
- Nguồn cũng sẽ phải thay đổi
- Mất link bất tử

Mà mất link có nhiều dạng:
- Cái chỗ lưu bí ẩn bị mất
- Cái link của công thức bị mất
- Cái option link bị đổi

Vây nó nằm ở đâu vậy.

Tôi nghĩ nó nằm trong folder C:\Documents and Settings\<User>\Local Settings\Temp đối với file lưu tại máy, và nằm trong folder C:\Documents and Settings\<User>\Local Settings\Temporary Internet File\Content IE5 đối với file tải xuống từ mạng.
Những vị trí này tôi nghĩ nhiều người dùng vẫn thường xuyên quét dọn, thế mới nguy hiểm.