在Excel中跳过单元格 [英] Skipping Cells in Excel

问题描述

Hello Experts，



为了假设这个相对简单的问题，我将使用一个例子：



让我们假设在表2中所需的数据是以下单元格：C4，H4，M4，R4 ......等等



我会喜欢在Sheet 1 A1，B1，C1，D1 .......中显示这些数据



我试图手动输入前3个单元格，并拖动细胞，希望它会重复模式，但这没用。



有什么提示吗？

Hello Experts,

To postulate this relatively simple issue i am going to use an example:

Let us assume that in Sheet 2 the required data is the the following cells: C4,H4,M4,R4......etc

I would like to show this data in Sheet 1 A1,B1,C1,D1.......etc

I have tried to input the first 3 cells manually, and dragging the cells, hoping that it would repeat the pattern, but this was useless.

Any tips please?

推荐答案

在Sheet 1单元格中尝试此公式...

Try this formula in the Sheet 1 cells...

=OFFSET(Sheet2!

C1,0， 5 *（COLUMN（） - 1））

C1, 0, 5 * (COLUMN()-1))

作为解释 COLUMN（）将返回列号对于包含公式的单元格如此为

By way of explanation COLUMN() will return the column number for the cell containing the formula so for

A1   B1   C1   D1
1    2    3    4     etc...

您想要的数据是每隔5列（一旦模式开始）因此 5 *



OFFSET 返回来自单元格的值（在这种情况下） 0 远离初始起点（C1）的行和计算出的相同起始单元格的列数。



-1 是因为在A1中我想要起始单元格的值和

The data you want is every 5th column (once the pattern starts) hence the 5 *

OFFSET returns the value from the cell (in this case) 0 rows away from the initial starting point (C1) and the calculated number of columns away from the same starting cell.

The -1 is because in A1 I want the value of the starting cell and the

C1 是因为我总是想从同一点计算（否则Excel会在你拖动时将其更改为D，E等公式对面）



编辑 - 将表格编号更改为正确的编号！对此感到抱歉。

C1 is because I always want to calculate from the same point (otherwise Excel will change this to D, E etc as you drag the formula across)

Edit - changed the sheet number to the correct one!! Sorry about that.

这篇关于在Excel中跳过单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！