offset函數的使用方法

That's my dad in the picture, proudly holding the catch of the day. He tried to teach me how to fish, but without much success. (Worms...ewwww.)

那是照片中的我父親，驕傲地拿著當天的收獲。 他試圖教我如何釣魚，但沒有成功。 (蠕蟲... ewwww。)

This week someone asked me to explain the Excel OFFSET function, saying "Please teach me to fish." That's when it struck me that using OFFSET is similar to fishing.

本周有人要求我解釋Excel OFFSET函數，說“請教我釣魚”。 那就是讓我驚訝的是，使用OFFSET類似于釣魚。

• When you're fishing, you can dip into a pond with a bamboo pole and a small hook, or head out to sea, and cast a large net. Or you can fish the way we do in Canada, through a small hole in the ice, but that's another story. (There's a video at the end of this article.)
  釣魚時，您可以浸入帶有竹竿和小鉤子的池塘中，或者出海捕魚，然后撒網。 或者您可以通過在冰上的一個小洞釣魚，就像我們在加拿大所做的那樣，但這是另一回事了。 (本文結尾處有一個視頻。)
• With the Excel OFFSET function, you can pull data from a single cell nearby, or a large range of cells off in the distance.
  使用Excel OFFSET功能，您可以從附近的一個單元格中提取數據，也可以從遠處的較大范圍的單元格中提取數據。

The OFFSET function is useful when you want to make the data selection adjustable.

當您想使數據選擇可調時，OFFSET功能非常有用。

For example, if a February date is entered in cell A2, you can sum the February expense column. If a March date is entered, sum the March expenses instead.

例如，如果在單元格A2中輸入2月日期，則可以對2月費用列進行求和。 如果輸入了3月的日期，則應將3月的費用相加。

您的捕魚設備 (Your Fishing Equipment)

To make the OFFSET function work, you'll tell it 3 things:

為了使OFFSET函數起作用，您將告訴它三件事：

The starting point

起點

Where to go from there

從那里去哪里

How big a range to capture (optional)

捕獲范圍多大(可選)

The OFFSET syntax is: OFFSET(reference,rows,cols,height,width)

的OFFSET語法是：OFFSET( 參考 ， 行 ，COLS，高度，寬度)

The reference is the starting point.

參考是起點。

The rows and cols tell OFFSET where to go from the starting point. It can go up or down a specific number of rows, and left or right a specific number of columns.

該行和cols告訴OFFSET從哪里出發點去。 它可以在特定數量的行上向上或向下移動，并在特定數量的列上向左或向右移動。

The height and width set the size of the range. It can be as small as 1 row and 1 column (a single cell) or much bigger.

高度和寬度設置范圍的大小。 它可以小至1行1列(單個單元格)，也可以大得多。

For example, this OFFSET formula would return the January total, in cell B6: =OFFSET(A1,5,1,1,1)

例如，此OFFSET公式將在單元格B6中返回一月總計： = OFFSET(A1,5,1,1,1)

The starting reference is cell A1.

起始參考是單元格A1。

From there, it goes down 5 rows, and right one column, to cell B6.

從那里開始，它向下下降5行，并向右下一行到達單元格B6。

The selected range size is 1 row tall and 1 column wide.

所選范圍的大小是1行高和1列寬。

誘餌鉤 (Baiting the Hook)

Instead of typing all the values in the formula, you can use one or more cell references, to make the OFFSET formula flexible. In this example, all the totals are in row 5, so that number won't change.

您可以使用一個或多個單元格引用來代替OFFSET公式中的所有值，以使OFFSET公式更靈活。 在此示例中，所有總計都在第5行中，因此該數字不會更改。

However, the month number is typed in cell G1, so you could use that cell to set the number of columns to offset. Change the formula so G1 is the cols argument.

但是，月份號是在單元格G1中鍵入的，因此您可以使用該單元格來設置要偏移的列數。 更改公式，使G1為cols參數。

=OFFSET(A1,5,G1,1,1)

=偏移(A1,5，G1,1,1)

Now, if you change the month number to 3 in cell G1, the March total will be returned.

現在，如果您在單元格G1中將月份號更改為3，則將返回三月份的總數。

鑄造網 (Casting the Net)

Instead of pulling the result from a single cell, you could use OFFSET with the SUM function, to select a range with multiple cells, and calculate the total.

您可以將OFFSET與SUM函數結合使用，而不是從單個單元格中提取結果，而是選擇包含多個單元格的范圍并計算總和。

For example, this formula would calculate the total for the February expenses.

例如，此公式將計算2月費用的總額。

=SUM(OFFSET(A1,1,G1,4,1))

= SUM(OFFSET(A1,1，G1,4,1))

The starting reference is cell A1.

起始參考是單元格A1。

From there, it goes down 1 row, and right 2 columns, to cell C2.

從那里開始，它下降1行，右邊2列，到達單元格C2。

The selected range size is 4 rows tall and 1 column wide – C2:C5.

選定的范圍大小是4行高和1列寬– C2：C5。

其他炸魚 (Other Fish to Fry)

I like the OFFSET function, and use it to create dynamic ranges in some of my workbooks. There are alternatives to using the Excel OFFSET function, such as the Excel INDEX Function.

我喜歡OFFSET函數，并用它在某些工作簿中創建動態范圍 。 除了使用Excel OFFSET函數外，還有其他選擇，例如Excel INDEX函數 。

There's an interesting discussion of the merits of each function on Dick Kusleika's Daily Dose of Excel Blog: New Year's Resolution: No More Offset.

在Dick Kusleika的Excel博客每日劑量： 新年決議：不再有偏移上，有一個有趣的討論，關于每個函數的優點。

冰冷魚 (Ice Cold Fish)

I'd rather stay inside and work on OFFSET formulas, but ice fishing is popular here in Canada. This video makes the sport look almost appealing.

我寧愿呆在里面并研究抵消公式，但在加拿大，冰釣很流行。 該視頻使這項運動看起來很吸引人。

演示地址

翻譯自: https://contexturesblog.com/archives/2010/01/20/something-fishy-using-the-excel-offset-function/

offset函數的使用方法

總結

以上是生活随笔為你收集整理的offset函数的使用方法_有点麻烦：使用Excel OFFSET函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。