Diễn đàn giaiphapexcel.com sẽ chuyển sang hoạt động ở link sau

http://www.giaiphapexcel.com/diendan/forums


[CLICK] học VBA ở Đà Nẵng tháng 5/2017


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

      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:




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



      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:




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



      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)




      Rất dễ dàng phải không? Ở đâ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
      Góp ý 24 Góp ý
      1. Nguyen Hong Quan's Avatar
        Nguyen Hong Quan -
        Cảm ơn bác kyo nhiều ạ
      1. nguyendaithang93's Avatar
        nguyendaithang93 -
        Nhưng sao lại cần dấu "" trong công thứcIndirect(""&D$1&""!$B$J") vậy ạ?
      1. kyo's Avatar
        kyo -
        Một cách tổng quát thì tên sheet nên có dấu ' nha bạn, ví dụ 'Sheet1'!A:B

        Tuy nhiên, đối với tên sheet không có dấu cách quãng, ví dụ Sheet1, 0101,... thì có thể lược bỏ dấu đó đi. Tuy nhiên bài viết này kyo muốn viết tổng quát, nghĩa là sẽ có nhiều người đặt tên theo kiểu 'Tháng 1', 'Tháng 2',... thì khi đó chắc chắn là phải có cặp dấu ' trong INDIRECT.
      1. khoa289's Avatar
        khoa289 -
        Hỏi:Tôi đã làm tương tự nhưng file bị lỗi ???
        Mọi người giúp xem công thức chưa đúng chỗ nào?
        Cám ơn
      1. khoa289's Avatar
        khoa289 -
        [QUOTE=kyo;755193]Một cách tổng quát thì tên sheet nên có dấu ' nha bạn, ví dụ 'Sheet1'!A:B

        Tuy nhiên, đối với tên sheet không có dấu cách quãng, ví dụ Sheet1, 0101,... thì có thể lược bỏ dấu đó đi. Tuy nhiên bài viết này kyo muốn viết tổng quát, nghĩa là sẽ có nhiều người đặt tên theo kiểu 'Tháng 1', 'Tháng 2',... thì khi đó chắc chắn là phải có cặp dấu ' trong INDIRECT.[/QUOT

        Tôi đã làm như vậy nhưng không được. có thể sửa công thức lại như thế nào để đúng.
        Cám ơn
      1. kyo's Avatar
        kyo -
        Đây là công thức của bạn: =VLOOKUP($B2,INDIRECT('""&D$1&"'!$B:$D),3,FALSE)

        Đây là công thức kyo sửa: =VLOOKUP($B2,INDIRECT("'"&D$1&"'!$B:$D"),3,FALSE)

        Bạn xem sẽ thấy sự khác biệt.
      1. thuanduc08's Avatar
        thuanduc08 -
        Cảm ơn bác nhiều, bài hay quá. Bác cho em hỏi tí xíu. Nếu tổng hợp các file thì dùng hàm gì bác ơi?
      1. Jiminy's Avatar
        Jiminy -
        k có file để làm thử sao Kyo?
      1. cuongvannhi's Avatar
        cuongvannhi -
        =VLOOKUP($A2,INDIRECT(""&C$1&"'!$A:$C"),3,FALSE)

        mình làm báo lỗi không biết sao nữa, nhờ bạn chỉ rõ giúp mình
      1. phihndhsp's Avatar
        phihndhsp -
        Trích Nguyên văn bởi cuongvannhi View Post
        =vlookup($a2,indirect(""&c$1&"'!$a:$c"),3,false)

        mình làm báo lỗi không biết sao nữa, nhờ bạn chỉ rõ giúp mình
        =vlookup($a2,indirect("'"&c$1&"'!$a:$c"),3,false)
      1. AMV's Avatar
        AMV -
        Cho mình xin file này nhé
      1. congtao's Avatar
        congtao -
        Các cao thủ xem giúp tôi file này. Cảm ơn nhiều!
      1. congtao's Avatar
        congtao -
        Các cao thủ xem giúp tôi file này. Cảm ơn nhiều!
      1. congtao's Avatar
        congtao -
        Các cao thủ xem giúp tôi file này. Cảm ơn nhiều!
      1. Nguyễn Hồng Quang's Avatar
        Nguyễn Hồng Quang -
        =IF(Cách trình bày của Kyo = chuyên nghiệp; LIKE; THANKS)
      1. phitien's Avatar
        phitien -
        add cho xin file về luyện nhé.thank add nhiều !
      1. lantktl's Avatar
        lantktl -
        Rất hay, tôi đã áp dụng tổng hợp khối lượng thành công, cảm ơn nhiêu!.
      1. toidjtjmtoi's Avatar
        toidjtjmtoi -
        rất hay , cảm ơn anh
      1. huỳnh thị tĩnh's Avatar
        huỳnh thị tĩnh -
        Tôi vừa tham khảo bài viết cũng như kết quả giải quyết vấn đề. Điều này giúp ích cho công việc của tôi. Xin cảm ơn Kyo.
      1. Mr.Hoang57's Avatar
        Mr.Hoang57 -
        Xin chào, ai có file bài này không cho mình xin với, mình thây hay , nen muốn xin file về làm thử