Kiểm tra hình này:
Giả sử bạn muốn tạo báo cáo từ điều này như thể bạn lọc trên Vùng. Tức là, nếu bạn lọc theo hướng Bắc, bạn sẽ thấy:
Nhưng nếu bạn muốn một phiên bản dựa trên công thức của cùng một thứ thì sao?
Đây là kết quả bạn đang tìm kiếm trong cột I: K:
Rõ ràng, đó là cùng một báo cáo, nhưng không có mục nào được lọc ở đây. Nếu bạn muốn có một báo cáo mới về East, sẽ rất tuyệt nếu bạn chỉ cần thay đổi giá trị trong G1 thành East:
Đây là cách nó được thực hiện. Trước hết, nó không được thực hiện bằng cách sử dụng VLOOKUP. Vì vậy, tôi đã nói dối về tiêu đề của kỹ thuật này!
Cột F không được hiển thị trước đây và nó có thể bị ẩn (hoặc chuyển đi nơi khác để không ảnh hưởng đến báo cáo).
Những gì được hiển thị trong cột F là số hàng nơi G1 được tìm thấy trong cột A; nghĩa là những hàng nào chứa giá trị "North"? Kỹ thuật này liên quan đến việc sử dụng các tế bào trên, vì vậy nó phải bắt đầu trong ít nhất hàng 2. Nó phù hợp với giá trị “Bắc” chống lại cột A, nhưng thay vì toàn bộ cột, sử dụng một chức năng OFFSET: OFFSET($A$1,F1,0,1000,1).
Vì F1 là 0 nên đây OFFSET(A1,0,0,1000,1)là A1: A1000. (1000 là tùy ý, nhưng đủ lớn để thực hiện công việc - bạn có thể biến nó thành bất kỳ số nào khác).
Giá trị 2 trong F2 là nơi đầu tiên "Bắc". Bạn cũng muốn thêm lại giá trị của F1 vào cuối, nhưng giá trị này là 0, cho đến nay.
"Điều kỳ diệu" trở nên sống động trong ô F3. Bạn đã biết rằng miền Bắc đầu tiên được tìm thấy ở Hàng 2. Vì vậy, bạn muốn bắt đầu tìm kiếm hai hàng bên dưới A1. Bạn có thể làm điều đó bằng cách chỉ định 2 làm đối số thứ hai của hàm OFFSET.
Công thức trong F3 sẽ tự động trỏ đến 2 mà đã được tính toán trong F2 di động: Khi bạn sao chép xuống công thức, bạn sẽ thấy =OFFSET($A$1,F2,0,1000,1)đó là OFFSET($A$1,2,0,1000,1)đó là A3: A1000. Vì vậy, bạn đang so khớp Bắc với phạm vi mới này và nó tìm thấy Bắc trong ô thứ ba của phạm vi mới này, vì vậy MATCH cho kết quả là 3.
Bằng cách thêm lại giá trị từ ô ở trên, F2, bạn sẽ thấy 3 cộng với 2 hoặc 5, là hàng chứa phương Bắc thứ hai.
Công thức này được điền đủ xa để nhận được tất cả các giá trị.
Điều đó sẽ giúp bạn có được số hàng nơi tất cả các bản ghi miền Bắc được tìm thấy.
Làm thế nào để bạn dịch các số hàng đó thành kết quả trong các cột từ I đến K? Tất cả được thực hiện với một công thức duy nhất. Nhập công thức này trong I2: =IFERROR(INDEX(A:A,$F2),””). Sao chép bên phải và sau đó sao chép xuống.
Tại sao sử dụng IFERROR? Lỗi ở đâu? Lưu ý ô F6 - nó chứa # N / A (đó là lý do tại sao bạn muốn ẩn cột F) vì không còn chữ Bắc nào nữa sau hàng 15. Vì vậy, nếu cột F là lỗi, hãy trả về một ô trống. Nếu không, hãy lấy giá trị từ cột A (và khi được điền bên phải, B & C).
$ F2 là tham chiếu tuyệt đối đến cột F vì vậy bên phải điền vẫn tham chiếu đến cột F.
Bài viết của khách này là từ Excel MVP Bob Umlas. Đó là một trong những kỹ thuật yêu thích của anh ấy trong cuốn sách của anh ấy, Excel Outside the Box.
Excel Bên ngoài Hộp »
