将每个第n行从一个工作表复制到另一个工作表

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

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

但是我不想输入这些公式中的每一个......"100次.我想如果我选择了三个并拖动了盒子,它会理解我想要做什么,但没有运气.

关于如何优雅/高效地做到这一点的任何想法？

在新表的A1中,放下:

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

......并复制下来.如果从第1行以外的其他位置开始,将ROW()更改为ROW(A1)或第1行的其他单元格,然后再次向下复制.

如果要复制第n行但是多列,请使用以下公式:

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

这也可以正确复制.

如果我遇到每提取第7行,我会在"A"列之前"插入"一列.然后我会(假设第1行有一个标题行)输入2,3,4,5,6,7,8行中的数字1,2,3,4,5,6,7突出显示1,2,3,4,5,6,7并将其粘贴到工作表的末尾(700行值).结果将是1,23,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7 .......现在,在"A"列上进行数据升序排序.在排序之后,所有的1将是系列中的第一个,所有7个将是第七个项目.

在我看来,这个问题的答案太具体了.以下是两种不同方法和完整示例的更一般性答案.

该OFFSET方法

OFFSET需要3个强制参数.第一个是我们想要偏移的给定单元格.接下来的两个是我们想要偏移的行数和列数(向下和向右).OFFNET返回由此产生的单元格的内容.例如,OFFSET(A1, 1, 2)返回单元格的内容,C2因为A1是单元格(1,1),如果我们添加(1,2)到那里,我们得到的(2,3)单元格对应于单元格C2.

为了让它返回另一列的每第n行,我们可以使用该ROW函数.如果此函数没有参数,则返回当前单元格的行号.因此OFFSET,我们可以组合并ROW创建一个函数,通过向返回的值添加乘数来返回每个第n个单元格ROW.例如OFFSET(A$1,ROW()*3,0).注意$1在目标单元格中​​的使用.如果不使用,则偏移将从不同的单元格偏移,因此实际上增加1了乘数.

在ADDRESS+ INDIRECT方法

ADDRESS获取两个整数输入并以字符串形式返回单元格的地址/名称.例如,ADDRESS(1,1)返回"$A$1".INDIRECT获取单元格的地址并返回内容.例如,INDIRECT("A1")返回单元格的内容A1(它也接受带有$'s'的输入).如果我们ROW在里面ADDRESS使用乘数,我们可以得到每个第n个单元格的地址.例如,ADDRESS(ROW(), 1)在第1行将返回"$A$1",第2行将返回"$A$2",依此类推.所以,如果我们把它放在里面INDIRECT,我们可以得到每个第n个细胞的内容.例如,INDIRECT(ADDRESS(1*ROW()*3,1))向下拖动时返回第一列中每个第3个单元格的内容.

例

请考虑以下电子表格的屏幕截图.标题(第一行)包含下面行中使用的调用. 列A包含我们的示例数据.在这种情况下,它只是正整数(计数继续显示在显示的区域之外).这些是我们想要获得的每三分之一的值,也就是说,我们希望获得1,4,7,10等等.

列B包含不正确的尝试使用该OFFSET方法但我们忘记使用的地方$.可以看出,当我们乘以时3,我们实际上得到了第4行.

列C包含使用OFFSET我们记得使用的方法的错误尝试$,但忘记减去.因此,虽然我们确实获得了每个第3个值,但我们跳过了一些值(1和4).

D使用该OFFSET方法,列包含正确的函数.

列E包含使用ADDRESS+ INDRECT方法的错误尝试,但我们忘记减去.因此我们最初跳过了一些行.与列相同的问题C.

列F使用ADDRESS+ INDRECT方法包含正确的函数.