将第n行从一张纸复制到另一张 [英] Copy every nth line from one sheet to another

问题描述

我有一个包含1列，700行的Excel电子表格。我关心每七行。我不想进入并删除我关心的每一行之间的6行。所以我的解决方案是创建另一个工作表，并指定我想要的每个单元格的引用。

  = sheet1！a1 
 = sheet1！a8 
 = sheet1！a15 
  

但是我不想输入每个这些公式...100次。我想如果我选择了三个并拖动框，它会明白我在做什么，但没有运气。

解决方案

在新的表格的A1中，放这个： / p>

  = OFFSET（Sheet1！$ A $ 1，（ROW（） -  1）* 7,0）
  

...并复制。如果您从第1行以外的地方开始，请将ROW（）更改为ROW（A1）或第1行上的其他单元格，然后再次复制。

如果您要复制第n行但多列，使用公式：

  = OFFSET（Sheet1！A $ 1，（ROW（） ）* 7,0）
  

这也可以被正确复制。

I have an Excel spreadsheet with 1 column, 700 rows. I care about every seventh line. I don't want to have to go in and delete the 6 rows between each row I care about. So my solution was to create another sheet and specify a reference to each cell I want.

=sheet1!a1
=sheet1!a8
=sheet1!a15

But I don't want to type in each of these formulas ... `100 times.I thought if I selected the three and dragged the box around, it would understand what I was trying to do, but no luck.

Any ideas on how to do this elegantly/efficiently?

解决方案

In A1 of your new sheet, put this:

=OFFSET(Sheet1!$A$1,(ROW()-1)*7,0)

... and copy down. If you start somewhere other than row 1, change ROW() to ROW(A1) or some other cell on row 1, then copy down again.

If you want to copy the nth line but multiple columns, use the formula:

=OFFSET(Sheet1!A$1,(ROW()-1)*7,0)

This can be copied right too.

这篇关于将第n行从一张纸复制到另一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！