Công thức chung
=OFFSET($C$5,0,(COLUMN(A8)*n)-1)
Tóm lược
Để sao chép các giá trị hoặc tạo tham chiếu với một mẫu như mọi cột thứ 3, mọi cột thứ 5, v.v., bạn có thể sử dụng công thức dựa trên hàm OFFSET và COLUMN. Trong ví dụ được hiển thị, công thức trong C8 là:
=OFFSET($C$5,0,(COLUMN(A8)*3)-1)
Có thể sao chép giá trị này trên hàng 8 để nhận mọi giá trị thứ 3 từ hàng 5.
Giải trình
Trong Excel, bạn không thể dễ dàng tạo công thức bỏ qua cá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 để duy trì 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ể xây dựng các tham chiếu công thức sẽ tuân theo một mẫu cụ thể bằng cách sử dụng hàm OFFSET.
Hàm OFFSET được thiết kế để tạo các tham chiếu bằng cách sử dụng "hiệu số" từ một ô bắt đầu. Trong ví dụ được hiển thị, ô bắt đầu là C5, được cung cấp cho OFFSET dưới dạng tham chiếu tuyệt đối nên nó sẽ không thay đổi khi công thức được sao chép:
=OFFSET($C$5
Đối với đối số hàng, chúng tôi cung cấp số không, vì chúng tôi muốn ở trong cùng một hàng. Đối với đối số cột, chúng tôi sử dụng công thức con để tính giá trị bù bắt buộc:
(COLUMN(A8)*3)-1
Chúng tôi sử dụng A8 bên trong COLUMN để trả về 1 (vì A là cột đầu tiên), sau đó nhân với n (trong trường hợp này là 3) để được 3.
Khi công thức được sao chép qua hàng bên phải, giá trị mà COLUMN trả về sẽ tăng lên 1, đây là giá trị tạo ra "mẫu thứ n".
Bắt đầu từ 1
Nếu bạn muốn bắt đầu sao chép ở giá trị đầu tiên, bạn có thể điều chỉnh công thức như sau:
=OFFSET($C$5,0,(COLUMN(A11)-1)*3)
Bằng cách trừ đi 1, chúng tôi buộc độ lệch cột bằng 0 trong công thức đầu tiên.
Sao chép vào hàng thay vì cột
Để sao chép từ các cột thành hàng, bạn có thể sửa đổi công thức như sau:
=OFFSET($C$5,0,(ROW(C1)*3)-1)
Ở đây, hàm COLUMN đã được thay thế bằng hàm ROW và tham chiếu đến hàng đầu tiên trong cột, để tăng dần hoạt động chính xác khi công thức được sao chép thành nhiều hàng.
