Công thức chung
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Tóm lược
Để sao chép giá trị hoặc tạo tham chiếu với một mẫu như mọi hàng thứ 3, mọi dòng thứ 7, v.v., bạn có thể sử dụng công thức dựa trên hàm OFFSET và ROW. Trong ví dụ được hiển thị, công thức trong D5 là:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Có thể sao chép giá trị này xuống cột D để lấy giá trị thứ n từ cột B.
Giải trình
Trong Excel, bạn không thể dễ dàng sao chép các công thức cần bỏ qua các hàng hoặc cột theo một mẫu nhất định, vì các tham chiếu trong công thức sẽ tự động thay đổi theo mối quan hệ giữa ô nguồn ban đầu và ô đích mới. Tuy nhiên, với một chút công việc, bạn có thể tạo các tham chiếu công thức tuân theo các mẫu cụ thể.
Trong trường hợp này, chúng tôi đang sử dụng hàm OFFSET, được thiết kế để xây dựng các tham chiếu đến các ô hoặc phạm vi ô khác, dựa trên một điểm xuất phát hoặc điểm gốc nhất định.
Trong ví dụ được hiển thị, ô bắt đầu là B5, được cung cấp cho OFFSET làm đối số tham chiếu và được cố định làm tham chiếu tuyệt đối để nó sẽ không thay đổi khi công thức được sao chép:
=OFFSET($B$5
Để tính toán giá trị chính xác cho đối số hàng, chúng tôi sử dụng công thức phụ dựa trên hàm ROW:
(ROW(D1)*3)-1
Hàng được đưa ra ô đầu tiên trong cùng một cột và trả về 1. Kết quả này được nhân với n, trong ví dụ này là 3 để được 3.
Khi công thức được sao chép xuống cột, giá trị ROW trả về sẽ tăng lên 1, đây là giá trị tạo ra "mẫu thứ n".
Lý do chúng tôi trừ 1 trong mỗi trường hợp là vì hàm OFFSET không bao gồm ô tham chiếu khi đối số hàng được áp dụng. Nói cách khác, việc bù trừ một hàng từ A1 trả về A2:
=OFFSET(A1,1,0) // returns A2
Trừ đi 1 sẽ tính đến hành vi này.
Bắt đầu từ 1
Nếu bạn muốn bắt đầu sao chép ở hàng đầu tiên, sau đó làm theo mẫu thứ n, bạn có thể điều chỉnh công thức như sau:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
