Tham chiếu không hoạt động với file đóng (1 người xem)

[phuonghhn]

Tình hình là mình có 2 file: File-1.xlsx và File-2.xlsx. Ở File-2, dữ liệu ở cột Ma-2 được lấy từ cột Ma-1 của File-1 nhưng sắp thứ tự từ nhỏ đến lớn và cột So-2 (File-2) lấy từ cột So-1 (File-1) tương ứng với dữ liệu của Ma-1. Mình dùng hàm OFFSET, MATCH, SMALL, ROW lồng nhau để giải quyết.
Nếu cả 2 file cùng mở thì công thức sử dụng tốt. Nhưng nếu File-1 đóng và chỉ mở mình File-2 lên thì công thức không hoạt động (báo lỗi #VALUE!).
Mình vẫn muốn để 2 file và chỉ mở file 2 thôi và công thức vẫn hoạt động được. Xin mọi người chỉ giùm cách khắc phục với.
Gửi kèm 2 file để mọi người tham khảo.
Chấn thành cám ơn.

 

[ndu96081631]

Tình hình là mình có 2 file: File-1.xlsx và File-2.xlsx. Ở File-2, dữ liệu ở cột Ma-2 được lấy từ cột Ma-1 của File-1 nhưng sắp thứ tự từ nhỏ đến lớn và cột So-2 (File-2) lấy từ cột So-1 (File-1) tương ứng với dữ liệu của Ma-1. Mình dùng hàm OFFSET, MATCH, SMALL, ROW lồng nhau để giải quyết.
Nếu cả 2 file cùng mở thì công thức sử dụng tốt. Nhưng nếu File-1 đóng và chỉ mở mình File-2 lên thì công thức không hoạt động (báo lỗi #VALUE!).
Mình vẫn muốn để 2 file và chỉ mở file 2 thôi và công thức vẫn hoạt động được. Xin mọi người chỉ giùm cách khắc phục với.
Gửi kèm 2 file để mọi người tham khảo.
Chấn thành cám ơn.

Muốn làm việc được với file đang đóng, bắt buộc bạn phải chọn các hàm có khả năng hoạt động với Array (như IF, COUNT, MATCH, VLOOKUP, INDEX, SUMPRODUCT, SUM....)
Trong các hàm đã sử dụng thì OFFSET chỉ làm việc được với Range chứ không làm việc với Array ---> Dẫn đến nó cũng sẽ không hoạt động với file đang đóng
Giải pháp: Thay OFFSET bằng INDEX
Ví dụ:
Thay đoạn =OFFSET('[File 1.xlsx]Sheet1'!B$2,.....
Thành đoạn =INDEX('[File 1.xlsx]Sheet1'!B$2:B$1000,...
Đồng thời kết quả của hàm MATCH phải được cộng thêm 1 đơn vị
Cụ thể công thức tại B2 hiện tại là:

=[COLOR=#ff0000]OFFSET[/COLOR]('[File 1.xlsx]Sheet1'![COLOR=#ff0000]B$2[/COLOR],MATCH(SMALL('[File 1.xlsx]Sheet1'!$C$2:$C$30,ROW()-ROW(Sheet1!$C$2)+1),'[File 1.xlsx]Sheet1'!$C$2:$C$30,0)[COLOR=#ff0000]-1[/COLOR],0)

Thay thành:

=[COLOR=#ff0000]INDEX[/COLOR]('[File 1.xlsx]Sheet1'![COLOR=#ff0000]B$2:B$1000[/COLOR],MATCH(SMALL('[File 1.xlsx]Sheet1'!$C$2:$C$30,ROW()-ROW(Sheet1!$C$2)+1),'[File 1.xlsx]Sheet1'!$C$2:$C$30,0),0)

 

[phuonghhn]

Thực sự rất cám ơn ndu96081631, tôi sẽ thử và báo lại kết quả sau.