Chúng tôi có một danh sách các đơn đặt hàng với số lượng và tên hàng hóa. Ví dụ, tôi muốn lấy ra khỏi bảng theo số thứ tự của tất cả các hàng hóa có trong đó. Nhiều hơn hoặc ít hơn như thế này:
Tính năng tuyệt vời VLOOKUP (VLOOKUP) trong tình huống như vậy, nó sẽ chỉ giúp ích một phần, vì chỉ có thể trích xuất dữ liệu bằng kết quả phù hợp được tìm thấy đầu tiên, tức là sẽ chỉ cung cấp cho chúng tôi Táo. Để tìm và trích xuất tất cả các mục từ bảng, tốt hơn là sử dụng công thức mảng. Giống như cái này:
=INDEX($ B $ 2: $ B $ 16;ÍT NHẤT(IF($ E $ 2=A2: A16;ĐƯỜNG DÂY(B2:B16) -1; »»);ĐƯỜNG DÂY() -5))
Nó phải được nhập như sau:
- chọn các ô nơi kết quả sẽ được hiển thị (trong ví dụ của chúng tôi, đây là phạm vi D6: D20)
- enter (sao chép công thức vào ô đầu tiên) của dải ô
- nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi
Phép trừ đơn vị trong phân đoạn STRING (B2: B16) -1 được thực hiện vì tiêu đề bảng. Vì lý do tương tự, để bù cho sự thay đổi trong phạm vi kết quả so với phạm vi ban đầu, số năm trong phân đoạn bị trừ đi STRING () - 5
Để ẩn #NUM! lỗi sẽ xuất hiện trong các ô trống trong phạm vi kết quả D6: D20, bạn có thể sử dụng các hàm kiểm tra lỗi IF và EOSH, thay thế công thức của chúng tôi bằng một công thức phức tạp hơn một chút:
= IF (EOSH (ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5))); »»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
Trong Excel 2007, một hàm IFERROR tiện lợi hơn đã xuất hiện - nó cho phép bạn giải quyết vấn đề một cách gọn nhẹ hơn:
= IFERROR (ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)); »»)
PS
Trong phiên bản tiếng Anh của Excel, các hàm này sẽ giống như sau:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Sử dụng hàm VLOOKUP để tìm dữ liệu trong bảng
- Một phiên bản cải tiến của hàm VLOOKUP2 có thể tìm kiếm trong bất kỳ cột nào và không chỉ giá trị đầu tiên
- Các hàm VLOOKUP2 và VLOOKUP3 từ tiện ích bổ sung PLEX
- Công thức mảng là gì và chúng được sử dụng để làm gì?