Kết hợp VLOOKUP và INDIRECT trong dò tìm nhiều sheet

Quảng cáo
Kết hợp VLOOKUP và INDIRECT trong dò tìm nhiều sheet


Đã bao giờ bạn gặp trường hợp giá trị bạn cần có mặt ở nhiều sheet và bạn có nhiệm vụ lấy các giá trị đó để thể hiện trên một sheet Tổng cộng?

Để dễ hình dung, giả sử tôi có dữ liệu chấm công được xuất ra từ hệ thống với cấu trúc ngày tháng năm thể hiện theo từng sheet và cấu trúc dữ liệu của các sheet thì hoàn toàn giống nhau như sau:

36696674402_197a269506_b.jpg


Và tôi có một sheet Tổng cộng có cấu trúc sau:

36867510535_72c4869241_b.jpg


Bạn có thể thấy yêu cầu của bảng trên hình, đó là tôi muốn thấy được thời gian đi làm của từng nhân viên theo từng ngày. Như vậy chúng ta sẽ làm như thế nào?

Một cách phổ biến, đa phần mọi người đều "cam chịu" làm tay theo từng cột. Điều này có nghĩa là, tôi sẽ viết hàm VLOOKUP cho cột D trước như sau:

36696674062_39c0503ea3_b.jpg


Sau đó, tôi lại qua cột E để viết cho sheet 0201:

36867510265_f926c2c3d0_b.jpg


Và quá trình này cứ kéo dài cho đến hết cột M. Tuy nhiên, bạn hãy thử tưởng tượng bạn cần dữ liệu của 1 tháng, và chắc chắn bạn không thể làm tay như vậy 30 lần liên tiếp được. Và dĩ nhiên rồi, việc làm thế này vừa tốn thời gian lại không chuyên nghiệp, và khi có lỗi xảy ra, bạn sẽ phải mất công đi sửa 30 lần.

Do vậy, để tránh sự đau khổ này, bạn có thể tìm đến hàm INDIRECT của Excel. Từ đó, bạn hãy làm thế này:

1/ Đầu tiên, bạn hãy viết VLOOKUP như bình thường, nghĩa là bạn sẽ có hàm như sau: =VLOOKUP($B2,'0101'!$B:$J,9,FALSE)

2/ Bạn để ý thấy sheet 0101 trùng với tên ô D1 không? Và sheet 0201 thì trùng với E1, và cứ thế. Do vậy, hãy chèn hàm INDIRECT vào. Từ đó, bạn sẽ có như sau:
=VLOOKUP($B2,INDIRECT("'" & D$1 &"'!$B:$J"),9,FALSE)

36696673852_90614df108_b.jpg


Rất dễ dàng phải không? Từ đây, bạn để ý thấy hàm INDIRECT sẽ biến chuỗi '0101'!$B:$J thành địa chỉ và từ đó, Excel sẽ hiểu cú pháp này là cú pháp tham chiếu đến 1 sheet khác mang tên 0101 và quét từ cột B đến cột J. Và ứng với tham chiếu tương đối được thiết lập qua ô D1, D2, D3,… công thức sẽ tự lấy và điền vào để tạo thành các chuỗi tương ứng. Tuy nhiên, nếu không có hàm INDIRECT, Excel sẽ chỉ hiểu đó là một chuỗi mà thôi, do vậy, chúng ta phải sử dụng hàm này ở phía trước.

Đây là một ứng dụng rất tiêu biểu và thường thấy đối với những người làm việc với nhiều sheet có chung một cấu trúc giống nhau.

Chúc bạn thành công!

Một số bài viết có liên quan:
1/ Bạn đang ở quý mấy trong năm?
2/ Chuyển đổi dữ liệu dạng ma trận (ngang dọc) thành dạng phẳng
3/ Tùy chỉnh các điểm (marker) của biểu đồ theo ý thích
4/ Biểu đồ bước nhảy
5/ [Vui vui] Tạo báo cáo 3D
6/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 3)
7/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 2)
8/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 1)
9/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
10/ Làm việc với công thức mảng trong Excel
 
Lần chỉnh sửa cuối:
cho Em hỏi nếu như nằm ở 2 file có thể làm được giống như trên không ạ. =vlookup($a2,indirect("'"&c$1&"'!$a:$c"),3,false)
- file tổng thì nằm riêng một file còn các file con thì nằm chung 1 file. lúc đó công thức chổ này mình phải thế như thề nào ạ
("'"&c$1&"')
E trân thành cám ớn về sự chỉ bảo.
 
mình có 1 file như thế này , xin giúp đỡ

mình cũng dùng hàm Vlookup để giải quyết được 1 phần , những thắc mắc mình ghi cụ thể trong file , xin được giúp đỡ .
 

File đính kèm

  • file up lên diễn đàn.xlsx
    16.8 KB · Đọc: 49
Bác có thể giúp e cái này với được không ạ. https://goo.gl/quE1HZ e muốn khi nhập thông tin vào 1 ô bất kỳ trong sheet đầu thì sẽ tự động tìm ra thôn tin trong tất cả các sheet ý ạ. mong các bác chỉ giáo cho e với ạ!!!
 
Bác có thể giúp e cái này với được không ạ. https://goo.gl/quE1HZ e muốn khi nhập thông tin vào 1 ô bất kỳ trong sheet đầu thì sẽ tự động tìm ra thôn tin trong tất cả các sheet ý ạ. mong các bác chỉ giáo cho e với ạ!!!
Bài của bạn nên:
1/ Sửa STT thành Mã nhân viên ( không trùng)
2/ Chỉ nhập thông tin là Mã nhân viên thì mới ra các thông tin khác (không nhập tên hay ngày sinh ..)
Chỉ có vậy thì mới có thể dùng công thức
 
Có thể giúp em cái này với: https://goo.gl/quE1HZ e muốn khi nhập thông tin vào 1 ô bất kỳ trong sheet đầu thì sẽ tự động tìm ra thôn tin trong tất cả các sheet ý ạ. mong các bác chỉ giáo cho e với ạ!!!
Để vậy ta cần xài VBA, bạn chịu không?
Đường hướng là macro sự kiện tại 4 ô bạn định ra; Hễ nhập vô ô nào thì đi tìm dữ liệu ở các trang tính còn lại cái bạn vừa nhập mà thôi.
 
cho em hỏi ,
Gạch men Prime Loại 1_25*40(2442)
Gạch men Prime Loại 1_25*40(2442V)
HG-L1-30*45-2D345001P1
Gạch men loại 1_30*45(34505)
Gạch men khác loại 1_30*45(34505V)
Gạch men loại 1_30*45(34506)
Gạch men khác loại 1_30*45(34506V)
Gạch men khác loại 2_30*45(34507)

NHƯ EM MUỐN TÁCH CHỮ (25*40) TRONG NHIỀU MÃ NHƯ VẬY THÌ MÌNH DÙNG CÔNG THỨC GÌ Ạ
 
cho em hỏi ,
Gạch men Prime Loại 1_25*40(2442)
Gạch men Prime Loại 1_25*40(2442V)
HG-L1-30*45-2D345001P1
Gạch men loại 1_30*45(34505)
Gạch men khác loại 1_30*45(34505V)
Gạch men loại 1_30*45(34506)
Gạch men khác loại 1_30*45(34506V)
Gạch men khác loại 2_30*45(34507)

NHƯ EM MUỐN TÁCH CHỮ (25*40) TRONG NHIỀU MÃ NHƯ VẬY THÌ MÌNH DÙNG CÔNG THỨC GÌ Ạ
hàm thì dùng mid+ find còn không có thể sử dụng Text to Column cũng được. b nghiên cứu thử xem
P/s vừa thấy có dòng HG-L1-30*45-2D345001P1 quy luật không giống nên bạn k dùng Text to Column được :)
Mã:
=MID(A1;FIND("*";A1)-2;2)&MID(A1;FIND("*";A1);3)
 
Lần chỉnh sửa cuối:
hàm thì dùng mid+ find còn không có thể sử dụng Text to Column cũng được. b nghiên cứu thử xem
P/s vừa thấy có dòng HG-L1-30*45-2D345001P1 quy luật không giống nên bạn k dùng Text to Column được :)
Mã:
=MID(A1;FIND("*";A1)-2;2)&MID(A1;FIND("*";A1);3)
Sao chị không dùng luôn:
=MID(A1;FIND("*";A1)-2;5)
 
Quảng cáo
Top Bottom