Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Offset vba excel описание

30 функций Excel за 30 дней: СМЕЩ (OFFSET)

Вчера в марафоне 30 функций Excel за 30 дней мы заменяли текстовые строки с помощью функции REPLACE (ЗАМЕНИТЬ) и выяснили, что вставлять символы она тоже умеет.

В 26-й день марафона мы будем изучать функцию OFFSET (СМЕЩ). Она возвращает ссылку заданного размера, отстоящую от стартовой ссылки на указанное количество строк и столбцов.

Итак, давайте изучим информацию и примеры применения функции OFFSET (СМЕЩ), а если у Вас есть дополнительные сведения и свои примеры, пожалуйста, делитесь ими в комментариях.

Функция 26: OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) возвращает ссылку, смещённую от заданной ссылки на определенное количество строк и столбцов.

Как можно использовать функцию OFFSET (СМЕЩ)?

Функция OFFSET (СМЕЩ) может возвратить ссылку на диапазон, а также работать в сочетании с другими функциями. Используйте её для того, чтобы:

  • Находить суммы продаж для выбранного месяца.
  • Суммировать данные о продажах за выбранные месяцы.
  • Создавать динамические диапазоны на основе подсчета.
  • Суммировать продажи за последние n месяцев.

Синтаксис OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) имеет вот такой синтаксис:

OFFSET(reference,rows,cols,[height],[width])
СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

  • reference (ссылка) – ячейка или диапазон смежных ячеек.
  • rows (смещ_по_строкам) – может быть положительным (вниз от начальной ссылки) или отрицательным (вверх от начальной ссылки).
  • cols (смещ_по_столбцам) – может быть положительным (вправо от начальной ссылки) или отрицательным (влево от начальной ссылки).
  • height (высота) – количество строк в возвращаемой ссылке, должен быть положительным.
  • width (ширина) – количество столбцов в возвращаемой ссылке, должен быть положительным.
  • Если аргументы height (высота) или width (ширина) не указаны, используются размеры исходной ссылки.

Ловушки OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) пересчитывается каждый раз при любом изменении значений на листе Excel. Используя эту функцию во многих формулах, можно сильно замедлить работу. Вместо OFFSET (СМЕЩ) Вы можете использовать функцию INDEX (ИНДЕКС), чтобы возвратить ссылку. INDEX (ИНДЕКС) пересчитывает результат только при изменении своих аргументов.

Пример 1: Находим сумму продаж для выбранного месяца

При помощи функции OFFSET (СМЕЩ) Вы можете возвращать ссылку на диапазон, отталкиваясь от стартовой ссылки. В этом примере мы хотим получить сумму продаж в ячейке G2.

  • Стартовая ссылка – это ячейка C1.
  • Количество строк для сдвига вводится в ячейке F2.
  • Суммы продаж находятся в столбце C, поэтому сдвиг по столбцам равен нулю.
  • Аргумент height (высота) равен 1 строке.
  • Аргумент width (ширина) равен 1 столбцу.

В ячейке H2 находится формула, очень похожая на предыдущую, которая возвращает имя месяца. Единственное отличие – это сдвиг по столбцам: 1 вместо 0.

=OFFSET(C1,F2, 1 ,1,1)
=СМЕЩ(C1;F2;1; 1 ;1)

Замечание: В данном примере аргументы height (высота) и width (ширина) можно не указывать, поскольку нам нужна ссылка того же размера, что и стартовая.

Пример 2: Суммируем продажи за выбранные месяцы

В этом примере функция OFFSET (СМЕЩ) возвращает ссылку на данные о продажах за выбранный месяц, а функция SUM (СУММ) подсчитывает сумму для этого диапазона. В ячейке B10 указан номер выбранного месяца 3, следовательно, результатом будет сумма продаж в марте (Mar).

  • Начальная ссылка A3:A6.
  • Сдвиг по строкам равен нулю (если ноль вообще не указать, результат будет тот же).
  • Сдвиг по столбцам соответствует значению ячейки B10.
  • Высота и ширина не указаны, поскольку итоговый диапазон должен быть того же размера, что и начальный.

Пример 3: Создаем динамический диапазон, основанный на подсчете

Вы можете использовать функцию OFFSET (СМЕЩ), чтобы создать динамический диапазон. В этом примере мы создали именованный диапазон MonthList с такой формулой:

Если к списку в столбце C добавить ещё один месяц, он автоматически появится в выпадающем списке в ячейке F2, который использует имя MonthList, как источник данных.

Пример 4: Суммируем продажи за последние n месяцев

В этом заключительном примере OFFSET (СМЕЩ) работает вместе с SUM (СУММ) и COUNT (СЧЁТ), чтобы показать сумму за последние n месяцев. Как только добавляются новые значения, результат формулы будет автоматически скорректирован, чтобы включить величину продаж за последний месяц. В ячейке E2 количество месяцев равно 2, поэтому складываться будут суммы за август (Aug-10) и сентябрь (Sep-10).

  • Начальная ссылка – ячейка C2.
  • Количество строк для сдвига рассчитывается так:
    • подсчитываем количество чисел в столбце C,
    • вычитаем из результата количество месяцев, указанное в ячейке E3,
    • и прибавляем 1.
  • Продажи находятся в столбце C, поэтому сдвиг по столбцам равен нулю.
  • Высота введена в ячейке E3.
  • Ширина равна 1 столбцу.

VBA OFFSET

Excel VBA OFFSET Function

VBA Offset function is used to move or refer to a reference skipping a particular number of rows and columns, the arguments for this function in VBA is same as to the arguments in worksheet.

For example assume you have a set of data like the below one.

Now from cell A1, you want to move down 4 cells and you want to select that 5 th cell i.e. A5 cell.

Similarly, if you want to move down from A1 cell 2 rows and go 2 columns to the right and select that cell i.e. C2 cell.

In these cases, the OFFSET function is very helpful. Especially in VBA OFFSET Function is just phenomenal.

OFFSET is Used with Range Object in Excel VBA

In VBA we cannot directly enter the word OFFSET. We need to use the VBA RANGE object first and from that range object, we can use OFFSET property.

In excel range is nothing but a cell or range of the cell. Since OFFSET refers cells we need to use the object RANGE first and then we can use the OFFSET method.

Syntax of OFFSET in VBA Excel

  • Row Offset: How many rows you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).
  • Column Offset: How many columns you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).

Examples

Example #1

Consider the below data for example demonstration.

Now I want to select the cell A6 from the cell A1. Start the macro and reference cell using the Range object.

Code:

Now I want to select the cell A6 i.e. I want to do down 5 cells. So enter 5 as the parameter for Row Offset.

Читать еще:  Команда правка в excel

Code:

Since I am selecting in the same column I leave out the column part. Close the bracket and put a dot (.) and type the method “Select”.

Code:

Now run this code using the F5 key or you can run manually to select the cell A6 as shown below.

Output:

Example #2

Now take the same data but here will see how to use column offset argument as well. Now I want to select the cell C5.

Since I want to select the cell C5 firstly I want to move down 4 cells and take the right 2 columns to reach the cell C5. The Below code would do the job for me.

Code:

I run this code manually or using the F5 key then, it will select the cell C5 as shown in the below screenshot.

Output:

Example #3

We have seen how to offset rows and columns. We can also select the above cells from the specified cells as well. For example, if you are in the cell A10 and you want to select A1 cell, how do you select?

In case of moving down the cell, we can enter a positive number, so here in case of moving up, we need to enter negative numbers.

From A9 cell we need to move up by 8 rows i.e. -8.

Code:

If you run this code using the F5 key or you can manually run this code then, it will select the cell A1 from A9 cell.

Output:

Example #4

Assume you are in the cell C8. From this cell, you want to select the cell A10.

From the active cell i.e. C8 cell we need to first move down 2 rows and we need to move to left by 2 columns to select the cell A10.

In case of moving left to select the column, we need to specify the number is negative. So here we need to come back by -2 columns.

Code:

Now run this code using F5 key or run manually, it will select A10 cell as shown below:

Output:

Things to Remember

  • In case of moving up of rows, we need to specify the number in negatives.
  • In case of moving left to select the column, the number should be negative.
  • A1 cell is the first row and first column.
  • Active Cell means presently selected cells.
  • If you want to select the cell using OFFSET you need mention “.Select”.
  • If you want to copy the cell using OFFSET you need mention “.Copy”.

Recommended Articles

This has been a guide to VBA OFFSET. Here we learn how to use VBA OFFSET Property to navigate in Excel along with practical examples and a downloadable template. Below are some useful excel articles related to VBA –

Offset VBA Property to Navigate Excel

The VBA Tutorials Blog

Introduction — Offset VBA

This tutorial shows you how the Offset VBA property is used to navigate Excel. In VBA, offset allows you to access cells relative to your target cell. For example, you can select a cell and change or grab the value of a cell that’s a couple rows or columns away from the cell you have selected.

The Offset property in VBA is similar to the =OFFSET() function in Excel, except the VBA property is more powerful! I say that because, unlike the OFFSET() function, the offset VBA property doesn’t limit you to just grabbing the value of a nearby cell. You can also change the contents of that nearby cell! I’ll demonstrate why I think this is so important later in this tutorial.

For more information on changing ranges and cells in Excel, check out the Select and Selection chapter of my VBA Basics tutorial.

Example — Offset VBA

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now.

Tutorial — Offset VBA

This is a basic example. All it does is select cell A1 , types some data in the cell, moves down one row using the offset command, and types some data in the next cell. Here’s what the output looks like:

Kind of boring, eh? Let’s dive deeper into the Offset property to add a little excitement.

Arguments

The Offset VBA property is often referred to as the Range.Offset Property. It accepts 2 arguments:

RowOffset is the number of rows you want to move up or down. It can be a positive integer (down), negative integer (up), or 0.

ColumnOffset is the number of columns you want to move left or right. It can also be a positive integer (right), negative integer (left), or 0.

Good Practices

Before we go any further, I want to point out a good practice to make your macros run quicker.

If you’re using the offset property to change the cell/range you have selected in Excel, like we did in our example, get in the habit of placing Application.ScreenUpdating = False at the top of your macro. If you don’t use this command, each time the selected cell changes, your screen will update. Eventually, your screen may freeze on you, but even if it doesn’t freeze, your macro run-time will be much longer than if you added the command to the top of your macro.

Bottom line, add the command to the top of your macros to increase speed.

Offset in a Loop

My favorite thing to do with the Offset VBA property is to stick it in a For Loop.

Each time the macro iterates through the loop, the selected cell is moved two cells down and one cell to the right.

Читать еще:  Excel workbooks open имяфайла

Offset Active Cell

You don’t have to use offset to select the cell. The same macro works if you just want to refer to the active cell. This may make your macros run faster.

Offset a Range

So far we’ve only used the Offset in VBA to move a single cell. This example shows you how to use Offset for an entire range:

In this example, the values in range A1:B2 are selected, and offset is used to select the range 2 cells over and 2 cells down. The range dimensions remain the same!

Read Contents of Offset Cell

In all the examples above, we used Offset to navigate the Excel spreadsheet. This example will by slightly different because we’ll never actually change the selected cell.

See? Cell A1 is still selected, but the value stored one column to the right — cell B1 — is copied to the variable str1 and eventually displayed in a message box.

Change Contents of Offset Cell

This time, we’ll change the value of an offset cell without selecting that cell.

Like magic, cell A1 is selected, but we changed the value of the cell 2 rows down and 1 row over:

VBA Offset Map

If you’re wondering how I made the VBA offset map in my header image, here’s a bonus macro for you:

The resulting map tells you what arguments to enter into the offset command to access cells relative to cell E5


Offset VBA Map

Application Ideas

The power of the Offset VBA command doesn’t come when you select a cell in your macro, like I did in these examples. The power comes when you search through your data for a number or string, then offset relative to that cell once you find it.

I primarily use the Offset VBA property to write scripts in other programming languages. For example, I’m not a Bash or Awk expert. It would take me a while to write a functional Bash or Awk script that loops through a bunch of files.

Instead of wasting time trying to write a short, clean script in these languages, I’ll use VBA to write hundreds of simple Bash or Awk command lines and copy and paste the commands from Excel into Unix or Linux. It’s certainly not practical, but it gets the job done!

Most people try to deter VBA programmers from using the offset command, because it’s slow, clunky, and often unnecessary. As I’ve been saying since my first post over one year ago, I’m not “most people.” Form follows function. As long as your code is functional and meets your intended purpose, who am I to deter you? Most VBA projects just aren’t resource-intensive enough to warrant optimizing. Do what you’ve gotta do!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

Объекты MS Excel

Аргументы задают координаты интервала:

  • Cell1 — единственная ячейка (строка или столбец), задающая левый верхний угол интервала;
  • Cell2 — единственная ячейка (строка или столбец), задающая правый нижний угол интервала. Необязательный аргумент.

Допустимо задание аргументов переменными, выражениями, свойствами или методами, представляющими объект Range — одну ячейку, одну строку или один столбец рабочего листа .

  • Если свойство Range применяется к объекту Range , то ссылка на интервал ячеек считается относительной и возвращается смещенный объект Range .

Например, если выделен интервал C1:D5, то запись Selection.Range(«B2») возвратит ячейку D2.

Свойство Cells

Свойство Cells возвращает единственную ячейку рабочего листа , которая находится на пересечении строки и столбца, задаваемых целыми числами.

Синтаксис object. Cells (RowIndex,ColumnIndex)

  • object — ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
  • RowIndex — индекс строки;
  • ColumnIndex — индекс столбца.
  • В свойстве Cells индекс строки является первым аргументом, а индекс столбца — вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка.
  • Понятие «индекс» ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число.
Свойство Offset

Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range(«A5»).Offset(-2,1) возвращает ячейку B3.

  • object — ссылка на объект Range . Ссылка обязательна и определяет объект, относительно которого задается смещение;
  • RowOffset — смещение строки искомой ячейки относительно исходной ячейки;
  • ColumnOffset — смещение столбца искомой ячейки относительно исходной ячейки.

Необязательные аргументы RowOffset и ColumnOffset — числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю.

Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7.

Метод Union и свойство Areas

Метод Union используется для объединения двух и более объектов Range , заданных ссылками на непересекающиеся интервалы, в один объект Range .

Синтаксис Object. Union (arg1,arg2. )

  • object — всегда объект Application . Ссылка необязательна;
  • arg1,arg2 — интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов.

Например, оператор Union (Range(«A1:C5»),Range(«B10:D12»)).Select выделяет несмежные интервалы A1:C5 и B10:D12.

Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range .

  • object — ссылка на объект Range , состоящий из нескольких интервалов;
  • index — номер интервала в объекте. Аргумент необязателен.
Читать еще:  Вырезать музыку из видео онлайн ютуб
Свойства Column и Row (R/O Integer)

Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств

  • object — обязательная ссылка на объект Range .

Например, запись Range(«C5»).Column возвращает число 3, а запись Range(«C5»).Row возвращает число 5.

Свойства Columns и Rows

Свойство Columns (не путайте со свойством Column !) возвращает объект Range , представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено.

  • object — ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
  • index — индекс колонки в объекте.

Например, запись Columns(1) возвращает колонку A активного рабочего листа , а запись Range(«C1:D5»).Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5.

  • Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range .
  • Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns(«B») указывают на одну и ту же колонку B.

Свойство Rows (не путайте со свойством Row !) возвращает объект Range , представляющий строку или коллекцию строк в объекте, к которому это свойство было применено.

  • object — ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
  • index — индекс строки в объекте.
  • Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range .

Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек.

Свойство CurrentRegion

Свойство CurrentRegion определяет объект Range , который соответствует интервалу ячеек, включающему заданную ячейку.

В процедуре сравниваются значения первой ячейки первой строки и первой ячейки каждой следующей строки заполненного данными интервала, включающего первую ячейку. Если значения совпадают, то очередная строка удаляется.

Предполагается, что данные начинаются с ячейки A1 и занимают несколько строк и столбцов, при этом расположены не плотно, т.е. внутри интервала с данными могут находиться пустые строки или пустые столбцы. Анализируются только строки заполненного данными интервала ячеек вокруг ячейки A1, не содержащего пустых строк и столбцов.

The Excel VBA Offset Property

Another useful way to refer to a cell or group of cells is with the Offset property. This is used with the Range property so that you can specify a new location. But the new location is based on cells that you specify. As an example, examine this code:

The above code will select cell B2. The location we’ve used is cell A1. We’ve then typed a dot followed by the word Offset. In between round brackets, you tell Excel the new location. This is done with the parameters RowOffSet and ColumnOffSet. (The two are separated by a comma.) In other words, move one row from cell A1 and one column from cell A1.

You can use a shorthand instead of RowOffSet and ColumnOffSet. The shorthand just uses the numbers:

You can also just specify the Rows and not the columns:

Here, we’ve missed off the column parameter and its comma. Excel takes this to mean you want to move one row down from the cell you specified with Range. The cell selected will now be A2. (We’ve gone down a row but stayed in the same column.)

Similarly, you can specify the columns but not the rows:

Range(«A1»).Offset(, 1 ).Select

Now, the row position is blank and the column position is filled in. Notice that we have left in the comma, though. Excel takes this to mean that you don’t want a new position for the rows, just the columns. The cell selected will now be B1. (We’ve gone across one column but stayed in the same row.)

You can also specify negative numbers:

Range(«B2»).Offset(-1, -1 ).Select

This time, we’re starting in cell B2. In between the round brackets of Offset we have -1, -1. A negative number means go up one row or column from the starting position. Going up one row and one column from cell B2 takes you to cell A1.

You can also specify more than one cell for the Range:

Here, we’ve started with the range of cells A1 to C3. We want to offset this entire range by one row and one column. The new range of cells in the line of code above will be B2 to D4. When specifying a range of cells with offset, not only does the first cell (A1 above) get moved across and down 1 but the last cell (C3) gets moved across 1 and down 1.

If all this is confusing, let’s clear it up with a few practical examples.

Return to your coding window from the previous lesson. Create another Sub and give it the name Range_Offset. Add the following code:

Your coding window will then look like this (we’ve added some comments):

Return to Excel and draw out another button on your spreadsheet. When the Assign Macro dialogue box appears, select your new Sub, Range_Offset. Change the text on the button, as well. To test it out, select the cell A1 on your spreadsheet. Then click your button. You should find that Excel now selects cell B2:

Return to your coding window. Now change the code for your new Sub to this:

Test out the new code by returning to Excel and clicking your button. Which cell does Excel select now?

Try out the other Offset positions we’ve mentioned in this lesson. Amend your code and try each of these in turn:

Range(«A1»).Offset(3).Select
Range(«A1»).Offset(, 2 ).Select
Range(«B2»).Offset(-1, -1 ).Select
Range(«A1:C3»).Offset(1, 1).Select

Now try these two exercises.

Exercise
Suppose you’ve used Range to reference the cell A2. How would you get to cell C5 using Offset?

Exercise
If you’ve used Range to reference the cell E8, how would to get to cell B3 using Range and Offest?

OK, we’ll move on. Before doing so, make sure you understand how Offset works. We’ll take a look at the Resize property in the next lesson below.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×