Загрузите образец книги
В этом руководстве показано, как использовать Функция смещения Excel в Excel, чтобы создать опорное смещение от начальной ячейки.
Обзор функции смещения
Функция OFFSET Начинается с определенной ссылки на ячейку и возвращает ссылку на ячейку с заданным числом строк и столбцов, смещением от исходной ссылки. Ссылки могут быть одной ячейкой или диапазоном ячеек. Смещение также позволяет изменять размер ссылки на заданное количество строк / столбцов.
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и входные данные функции ЕСЛИОШИБКА:
| 1 | = СМЕЩЕНИЕ (ссылка, строки, столбцы, высота, ширина) |
ссылка - Исходная ссылка на ячейку, от которой вы хотите сместиться.
ряды - Количество строк для смещения.
cols - Количество столбцов для смещения.
рост - ДОПОЛНИТЕЛЬНО: отрегулируйте количество строк в ссылке.
ширина - ДОПОЛНИТЕЛЬНО: отрегулируйте количество столбцов в справке.
Что такое функция СМЕЩЕНИЕ?
Функция СМЕЩЕНИЕ - одна из наиболее мощных функций электронных таблиц, поскольку она может быть весьма универсальной в том, что она создает. Это дает пользователю возможность определять ячейку или диапазон в различных положениях и размерах.
ВНИМАНИЕ: Функция СМЕЩЕНИЕ - одна из непостоянных функций. В большинстве случаев, когда вы работаете с таблицей, компьютер будет пересчитывать формулу только в том случае, если входные данные изменили свои значения. Однако изменчивая функция пересчитывает каждый раз вы вносите изменения в любую ячейку. Следует проявлять осторожность, чтобы не допустить увеличения времени пересчета из-за чрезмерного использования энергозависимой функции или наличия большого количества ячеек, зависящих от результата энергозависимой функции.
Основные примеры строк
При каждом использовании функции СМЕЩЕНИЕ необходимо указывать начальную точку или якорь. Давайте посмотрим на эту таблицу, чтобы понять это:
Мы будем использовать «Боб» в ячейке B3 в качестве точки привязки. Если бы мы хотели получить значение чуть ниже (Чарли), мы бы сказали, что хотим сдвинуть строку на 1. Наша формула будет выглядеть так:
| 1 | = СМЕЩЕНИЕ (B3; 1) |
Если бы мы хотели перейти на более высокий уровень, это был бы отрицательный сдвиг. Вы можете думать об этом как об уменьшении номера строки, поэтому нам нужно вычесть. Таким образом, чтобы получить значение выше (Адам), мы должны написать
| 1 | = СМЕЩЕНИЕ (B2; -1) |
Основные примеры столбцов
Продолжая идею из предыдущего примера, мы добавим в нашу таблицу еще один столбец.
Если бы мы хотели взять учителя для Боба, мы могли бы использовать формулу
| 1 | = СМЕЩЕНИЕ (B2; 0; 1) |
В этом случае мы сказали, что хотим сместить нулевые строки (иначе говоря, оставаться в той же строке), но мы хотим сместить 1 столбец. Для столбцов положительное число означает смещение вправо, а отрицательные числа - смещение влево.
СМЕЩЕНИЕ и МАТЧ
Предположим, у вас есть несколько столбцов данных, и вы хотите дать пользователю возможность выбирать, из какого столбца получать результаты. Вы можете использовать функцию ИНДЕКС или СМЕЩЕНИЕ. Поскольку ПОИСКПОЗ вернет относительное положение значения, нам нужно убедиться, что точка привязки находится слева от нашего первого возможного значения. Рассмотрим следующий макет:
В B2 мы напишем эту формулу:
| 1 | = СМЕЩЕНИЕ (B2; 0; ПОИСКПОЗ (A2; $ C $ 1: $ F $ 1; 0)) |
МАТЧ будет искать «февраль» в диапазоне C1: F1 и находить его в диапазоне 2nd клетка. СМЕЩЕНИЕ затем сместит 1 столбец вправо от B2 и получит желаемое значение 9. Обратите внимание, что СМЕЩЕНИЕ не имеет проблем с использованием той же ячейки, которая содержит формулу, в качестве точки привязки.
ПРИМЕЧАНИЕ. Этот метод можно использовать как замену ВПР или ГПР, если вы хотите вернуть значение слева / над диапазоном поиска. Это потому, что OFFSET может делать отрицательные смещения.
OFFSET, чтобы получить диапазон
Вы можете использовать 4th и 5th аргументы в функции OFFSET, чтобы вернуть диапазон, а не только одну ячейку. Предположим, вы хотите суммировать 3 столбца в этой таблице.
| 1 | = СРЕДНИЙ (СМЕЩЕНИЕ (A1; ПОИСКПОЗ (F2; A2: A5,0); 1,1,3)) |
В F2 мы выбрали имя учащегося, для которого хотим получить средние результаты тестов. Для этого воспользуемся формулой
| 1 | = СРЕДНИЙ (СМЕЩЕНИЕ (A1; ПОИСКПОЗ (F2; A2: A5,0); 1,1,3)) |
MATCH будет искать в столбце A наше имя и возвращать относительную позицию, которая в нашем примере равна 3. Посмотрим, как это будет оцениваться. Во-первых, OFFSET собирается пойти вниз 3 строки от A1 и 1 столбец до Правильно из A1. Это помещает нас в ячейку B3.
| 1 | = СРЕДНИЙ (СМЕЩЕНИЕ (A1; 3; 1; 1; 3)) |
Затем мы собираемся изменить размер диапазона. В левом верхнем углу нового диапазона будет B3. Это будет 1 строка в высоту и 3 столбца в высоту, что даст нам диапазон B4: D4.
| 1 | = СРЕДНИЙ (СМЕЩЕНИЕ (A1,3; 1; 1; 3)) |
Обратите внимание, что, хотя вы можете законно помещать отрицательные значения в аргументы смещения, вы можете использовать только неотрицательные значения в аргументах изменения размера.
В конце наша функция AVERAGE видит:
| 1 | = СРЕДНИЙ (B4: D4) |
Таким образом, мы получаем решение 86,67
СМЕЩЕНИЕ с динамической суммой
Поскольку СМЕЩЕНИЕ используется для поиска ссылки, а не указывает непосредственно на ячейку, это наиболее полезно, когда вы имеете дело с данными, в которые добавлены или удалены строки. Рассмотрим следующую таблицу с итоговым значением внизу
| 1 | = СУММ (B2: B4) |
Если бы мы использовали здесь базовую формулу СУММ «= СУММ (B2: B4)», а затем вставили бы новую строку, чтобы добавить запись для Билла, у нас был бы неправильный ответ.
Вместо этого давайте подумаем, как решить эту проблему с точки зрения Всего. Мы действительно хотим захватить все, от ячейки B2 до ячейки чуть выше нашего общего. Мы можем записать это в формуле, сделав смещение строки равным -1. Таким образом, мы используем это как формулу для нашей суммы в ячейке B5:
| 1 | = СУММ (B2: СМЕЩЕНИЕ (B5; -1,0)) |
Эта формула выполняет то, что мы только что описали: начните с B2 и перейдите на 1 ячейку над нашей общей ячейкой. Вы можете увидеть, как после добавления данных Билла наша общая сумма обновляется правильно.
СМЕЩЕНИЕ, чтобы получить последние N предметов
Предположим, вы регистрируете ежемесячные продажи, но хотите видеть последние 3 месяца. Вместо того, чтобы вручную обновлять формулы для продолжения корректировки по мере добавления новых данных, вы можете использовать функцию СМЕЩЕНИЕ с COUNT.
Мы уже показали, как можно использовать OFFSET для захвата диапазона ячеек. Чтобы определить, сколько ячеек нам нужно сдвинуть, мы воспользуемся COUNT, чтобы определить, сколько числа находятся в столбце B. Давайте посмотрим на наш образец таблицы.
| 1 | = СУММ (СМЕЩЕНИЕ ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Если бы мы начали с B1 и сместили 4 строки (количество чисел в столбце B), мы оказались бы в нижней части нашего диапазона, B5. Однако, поскольку OFFSET не может изменить размер с отрицательным значением, нам нужно сделать некоторые настройки, чтобы мы оказались в B3. Общее уравнение для этого будет следующим:
| 1 | COUNT (…) - N + 1 |
Мы берем количество всего столбца, вычитаем сколько угодно возвращаемых значений (так как мы изменим размер, чтобы захватить их), а затем добавляем 1 (так как по сути мы начинаем смещение с нулевой позиции).
Здесь вы можете увидеть, что мы настроили диапазон для получения суммы, среднего и максимального значения за последние N месяцев. В E1 мы ввели значение 3. В E2 наша формула
| 1 | = СУММ (СМЕЩЕНИЕ ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Выделенный раздел - это наше общее уравнение, которое мы только что обсудили. Нам не нужно смещать какие-либо столбцы. Затем мы собираемся изменить размер диапазона, чтобы он составлял 3 ячейки в высоту (определяется значением в E1) и 1 столбец в ширину. Затем наша СУММ берет этот диапазон и дает нам результат 1850 долларов. Мы также показали, что вы можете вычислить среднее значение max этого же диапазона, просто переключив внешнюю функцию с SUM на то, что требует ситуация.
OFFSET списки динамической проверки
Используя технику, показанную в последнем примере, мы также можем создавать именованные диапазоны, которые можно использовать при проверке данных или диаграммах. Это может быть полезно, если вы хотите настроить электронную таблицу, но ожидаете, что наши списки / данные изменят размер. Допустим, наш магазин начинает продавать фрукты, и в настоящее время у нас есть 3 варианта.
Чтобы создать раскрывающийся список проверки данных, который можно было бы использовать в другом месте, мы определим именованный диапазон MyFruit как
| 1 | = $ A $ 2: СМЕЩЕНИЕ ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Вместо COUNT мы используем COUNTA, поскольку имеем дело с текстовыми значениями. Однако из-за этого наш COUNTA будет на единицу выше, поскольку он будет подсчитывать ячейку заголовка в A1 и давать значение 4. Однако, если мы смещаем на 4 строки, мы окажемся в ячейке A5, которая будет пустой. Чтобы скорректировать это, мы вычитаем 1.
Теперь, когда у нас есть настройка именованного диапазона, мы можем настроить проверку данных в ячейке C4, используя тип списка с источником:
| 1 | = MyFruit |
Обратите внимание, что в раскрывающемся списке отображаются только три наших текущих элемента. Если затем мы добавим дополнительные элементы в наш список и вернемся к раскрывающемуся списку, в списке будут показаны все новые элементы без необходимости изменять какие-либо формулы.
Меры предосторожности при использовании смещения
Как упоминалось в начале этой статьи, СМЕЩЕНИЕ - непостоянная функция. Вы не заметите этого, если используете его всего в нескольких ячейках, но если вы начнете задействовать его в сотнях вычислений, вы быстро заметите, что ваш компьютер тратит заметное количество времени на пересчет каждый раз, когда вы вносите какие-либо изменения. .
Кроме того, поскольку OFFSET не дает прямого имени ячейкам, на которые он смотрит, другим пользователям труднее зайти позже и при необходимости изменить ваши формулы.
Вместо этого было бы целесообразно использовать таблицы (представленные в Office 2007), которые допускают структурные ссылки. Это помогло пользователям предоставить единую ссылку, размер которой автоматически изменялся по мере добавления или удаления новых данных.
Другой вариант использования вместо СМЕЩЕНИЯ - мощная функция ИНДЕКС. INDEX позволяет вам создавать все динамические диапазоны, которые мы видели в этой статье, без учета нестабильной функции.
Дополнительные замечания
Используйте функцию OFFSET, чтобы вернуть значение ячейки (или диапазон ячеек) путем смещения заданного количества строк и столбцов от начальной ссылки. При поиске только одной ячейки формулы СМЕЩЕНИЯ достигают той же цели, что и формулы ИНДЕКС, с использованием немного другой техники. Настоящая сила функции СМЕЩЕНИЕ заключается в ее способности выбирать диапазон ячеек для использования в другой формуле.
При использовании функции СМЕЩЕНИЕ вы определяете начальную начальную ячейку или диапазон ячеек. Затем вы указываете количество строк и столбцов для смещения от этой начальной ячейки. Вы также можете изменить размер диапазона; добавить или вычесть строки или столбцы.
Вернуться к списку всех функций в Excel
СМЕЩЕНИЕ в Google Таблицах
Функция СМЕЩЕНИЕ в Google Таблицах работает точно так же, как и в Excel:
