excel函数里偏移单元格_30天中的30个Excel函数：26 –偏移

excel函数里偏移单元格

Yesterday, in the 30XL30D challenge, we changed text strings with the REPLACE function, and learned that it can insert characters too.

昨天，在30XL30D挑战赛中，我们使用REPLACE函数更改了文本字符串，并了解到它也可以插入字符。

For day 26 in the challenge, we'll examine the OFFSET function. From a starting reference, it returns another reference, of a specified size, and offset by a set number of rows and columns.

在挑战的第26天，我们将检查OFFSET函数。 从起始引用，它返回指定大小的另一个引用，并且偏移一定数量的行和列。

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the OFFSET information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下OFFSET信息和示例，如果还有其他提示或示例，请在评论中分享。

功能26：偏移 (Function 26: OFFSET)

The OFFSET function returns a reference offset from a given reference.

OFFSET函数返回给定参考的参考偏移。

您如何使用抵消？ (How Could You Use OFFSET?)

The OFFSET function can return a reference to a range, and be combined with other functions. Use it to:

OFFSET函数可以返回对范围的引用，并可以与其他函数结合使用。 使用它来：

• Find sales amount for selected month
  查找选定月份的销售额
• Sum the selected month's sales
  汇总所选月份的销售额
• Create a dynamic range based on count
  根据计数创建动态范围
• Sum the last n month's sales
  总结最近n个月的销售额

偏移语法 (OFFSET Syntax)

The OFFSET function has the following syntax:

OFFSET函数具有以下语法：

• • reference is a cell or range of adjacent cells.
    参考是一个单元格或相邻单元格的范围。
  • rows can be positive (below starting reference) or negative (above starting reference)
    行可以是正数(在起始参考之下)或负数(在起始参考之上)
  • cols can be positive (right of starting reference) or negative (left of starting reference)
    cols可以为正(起始参考的右侧)或负(起始参考的左侧)
  • height must be positive, and is number of rows in returned reference
    height必须为正，并且是返回的引用中的行数
  • width must be positive, and is number of columns in returned reference
    width必须为正，并且是返回引用中的列数
  • if height or width are omitted, starting reference size is used
    如果省略了高度或宽度，则使用起始参考尺寸

  OFFSET(reference,rows,cols,height,width)

  偏移量(参考，行，列，高度，宽度)

补偿陷阱 (OFFSET Traps)

The OFFSET function is volatile, so it could slow down a workbook if used in too many cells. Instead, you could use another function, like INDEX, to return a reference.

OFFSET函数是易失性的，因此如果在太多单元格中使用它可能会减慢工作簿的速度。 相反，您可以使用另一个函数(如INDEX)返回引用。

示例1：查找选定月份的销售额 (Example 1: Find sales amount for selected month)

With the OFFSET function, you can return a reference to a range, based on a starting reference. In this example, we want the sales amount in cell G2:

使用偏移功能，您可以基于起始参考将参考返回到范围。 在此示例中，我们想要单元格G2中的销售额：

• starting reference is cell C1

  起始参考是单元格C1

• number of rows to offset is entered in cell F2

  在单元格F2中输入要偏移的行数

• sales amount is in column C, so the number of columns to offset is zero
  销售金额在C列中，因此要抵消的列数为零
• height is 1 row
  高度是1行
• width is 1 column
  宽度为1栏

=OFFSET(C1,F2,0,1,1)

=偏移(C1，F2,0,1,1)

In cell H2, there is a similar OFFSET formula, to return the month name. The only difference is the column offset -- 1 instead of zero.

在单元格H2中，有一个相似的OFFSET公式，用于返回月份名称。 唯一的区别是列偏移量-1而不是零。

=OFFSET(C1,F2,1,1,1)

= OFFSET(C1，F2，1，1,1)

Note: The height and width arguments could be omitted, because we want a reference that is the same size as the starting reference. I used them in this example, to show how all the arguments work.

注意：可以省略height和width参数，因为我们想要一个与开始引用大小相同的引用。 在本示例中，我使用它们来显示所有参数的工作方式。

示例2：汇总所选月份的销售额 (Example 2: Sum the selected month's sales)

In this example, the OFFSET function returns a reference to the sales amounts for the selected month, and the SUM function returns the total for that range.

在此示例中，OFFSET函数返回对所选月份的销售额的引用，而SUM函数返回该范围内的总计。

In cell B10, the selected month number is 3, so the result is the total of the March sales.

在单元格B10中，所选的月数是3，因此结果是三月销售总额。

• starting reference is A3:A6
  起始参考为A3：A6
• rows to offset is zero (you could omit the zero, for the same result)
  要偏移的行为零(对于相同的结果，您可以省略零)
• columns to offset is entered in cell B10
  在单元格B10中输入要偏移的列
• height and width are omitted, because the reference should be the same size as the starting reference
  高度和宽度被省略，因为参考的大小应与起始参考的大小相同

=SUM(OFFSET(A3:A6,0,B10))

= SUM(OFFSET(A3：A6,0，B10))

示例3：基于计数创建动态范围 (Example 3: Create a dynamic range based on count)

You can also use the OFFSET function to create a dynamic range. In this example, I've created a name, MonthsList, with this formula:

您还可以使用OFFSET函数创建动态范围。 在此示例中，我使用以下公式创建了名称MonthsList：

=OFFSET('Ex03'!$C$1,0,0,COUNTA('Ex03'!$C:$C),1)

= OFFSET('Ex03'！$ C $ 1,0,0，COUNTA('Ex03'！$ C：$ C)，1)

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthsList as its source.

如果将另一个月份添加到C列中的列表，它将自动出现在单元格F2中的数据验证下拉列表中，该单元格使用MonthsList作为其来源。

示例4：总结最近n个月的销售额 (Example 4: Sum the last n month's sales)

In this final example, OFFSET is combined with SUM and COUNT, to show the total for the last n months. As new quantities are added, the formula result will automatically adjust to include the latest months.

在最后一个示例中，OFFSET与SUM和COUNT相结合，以显示最近n个月的总数。 添加新数量后，公式结果将自动调整为包括最近的月份。

In cell E2, the number of months is 2, so the August and September amounts are summed.

在单元格E2中，月份数为2，因此将8月和9月的金额相加。

• starting reference is cell C2

  起始参考是单元格C2

• number of rows to offset is calculated by counting numbers in column C, subtracting number in cell E3, adding 1

  要计算偏移量的行数，是通过计算C列中的数字，减去单元格E3中的数字 ，再加上1

• quantity is in column C, so the number of columns to offset is zero
  数量在C列中，因此要抵消的列数为零

• height is entered in cell E3

  在单元格E3中输入高度

• width is 1 column
  宽度为1栏

=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))

= SUM(OFFSET(C2，COUNT(C：C)-E3 + 1,0，E3,1))

下载偏移功能文件 (Download the OFFSET Function File)

To see the formulas used in today's examples, you can download the OFFSET function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载OFFSET函数示例工作簿 。 该文件已压缩，并且为Excel 2007文件格式。

观看偏移视频 (Watch the OFFSET Video)

To see a demonstration of the examples in the OFFSET function sample workbook, you can watch this short Excel video tutorial.

要观看OFFSET函数示例工作簿中示例的演示，可以观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/01/27/30-excel-functions-in-30-days-26-offset/

excel函数里偏移单元格