Что такое Excel Смещение функции и как использовать его

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

Что такое функция смещения?

Функция смещения — это функция поиска информации. Если у вас есть огромная таблица, содержащая определенные данные, и вы хотите получить из нее данные, лучший способ сделать это — вставить формулу смещения для таблицы в этот лист или другой лист в той же книге. Таким образом, после того, как вы создали таблицу поиска смещения, вам просто нужно ввести данные, например, «месяц», и вы получите «продажи» или «доход» за этот месяц из этой таблицы.

Другой вариант — просмотр таблицы, но не могли бы вы рассмотреть этот вариант, если у вас есть листы и листы данных, все содержащие таблицу данных из 1000 столбцов? Здесь смещение сочетается с другими функциями.

Концепция сводной таблицы исходит из смещения. Сводная таблица — это общая таблица, и из этой конкретной комбинации данных и графиков получается по мере необходимости. Таким же образом можно создавать динамические таблицы. Для этих таблиц вам нужно будет создать формулу смещения Excel

Синтаксис OFFSET

=OFFSET(reference,rows, columns, [height], [width])

Строки — вы просите Excel переместить количество строк, чтобы получить информацию. В этом случае он должен перемещаться по таблице, поэтому просто оставьте пустое поле, обозначенное запятой (,). В противном случае, чтобы переместить столбец вперед, присвойте значение 1, а для перемещения предыдущего столбца — значение -1.

Столбцы — это для того, чтобы управлять функцией количества столбцов. Система значений такая же, как и для строки.

Высота — высота стола, в данном случае A11.

Ширина — ширина стола, в данном случае D11.

После настройки функции смещения важно запустить пробную версию, чтобы отсеять любые ошибки.

Возьмем таблицу, которая содержит имя, адрес электронной почты, дату рождения и возраст.

Таблица начинается с ячейки A1, содержащей заголовок «Имя». Данные работают, скажем, для A11. И строки идут до D1. Вся таблица содержит A1: D11. Вам нужна система поиска, которая будет получать имя, когда вы вводите идентификатор электронной почты, или идентификатор электронной почты, когда вы вводите имя вместе с другими деталями. Вы создаете формулу, удовлетворяя 5 аргументов.

Контрольная точка — это ячейка, с которой должен начинаться поиск. В данном случае это должен быть A2. Это общий стандарт.

Пример:

Например, вы указываете ссылку на B4 и строку смещения 0 и столбец смещения 1, тогда будет возвращено значение из C4. Сбивает с толку? Приведем еще несколько примеров. Последний пример функции СМЕЩЕНИЕ рассказывает, как СУММИРОВАТЬ динамически.

Функция OFFSET для получения значения справа и слева от ячейки. У нас есть эта таблица.

Теперь, если я хочу получить значение из 2 ячеек прямо до B2 (что означает D2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,0,2)

Функция СМЕЩЕНИЕ переместит 2 ячейки вправо от ячейки B4 и вернет ее значение. См. Изображение ниже. Если я хочу получить значение от 1 ячейки слева до B2 (что означает A2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,0,-1)

Знак минус (-) указывает смещение для обратного перемещения.

Функция OFFSET для получения значения снизу и сверху ячейки Итак, снова в приведенной выше таблице, если я хочу получить значение из 2-х ячеек любимого в B3 (что означает B5). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,2,0)

Если я хочу получить значение из 1 ячейки слева до B2 (что означает A2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,-2,0)

Совет от профессионалов: * Рассматривайте СМЕЩЕНИЕ как указатель графика, где Ссылка — начало координат, а строка и столбец — оси x и y.

Функция OFFSET для динамического суммирования диапазона Рассмотрим этот пример.

В приведенной выше таблице Итоговая строка в конце таблицы. Со временем вы будете добавлять строки в эту таблицу. Затем вам нужно будет изменить диапазон сумм в формуле.

Здесь мы можем воспользоваться функцией OFFSET для динамического суммирования. В настоящее время в ячейке C11 мы имеем = СУММ (C2: C10). Замените это формулой ниже.

=SUM(C2:OFFSET(C11,-1,0))

Эта формула просто берет первую строку данных, а затем суммирует диапазон над общей строкой.

Смещение для получения СМЕЩЕНИЯ массива функция имеет два необязательных аргумента, [высота] и [ширина]. Не принимайте их как должное. Когда функции OFFSET предоставляются аргументы [высота] и [ширина], она возвращает двумерный массив. Если в качестве аргумента передается только один из них, он возвращает размерный массив значений.

Если вы введете эту формулу в любую ячейку:

=SUM(OFFSET(C1,1,0,9,3))

Он суммирует значения в диапазоне от C2 до 9 строк ниже и 3 столбцов справа.

OFFSET (C1,1,0,9,3): это будет преобразовано в \ {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8 , 3,9; 8,9,2; 3,5,4; 6,6,5}.

Итак, в заключение, смещение — очень полезная функция Excel, которая может помочь вам решить множество проблемных потоков. Сообщите мне, как вы используете функцию СМЕЩЕНИЕ в своей формуле и где она вам больше всего помогала.

Функция поиска такая же, как смещение, но использует такие функции, как Match, Counta и IF для работы с динамической таблицей.

Использование смещения в Excel требует навыков и хорошего знания функций Excel и того, как они работают вместе. Сделать это без ошибок — самое большое препятствие.

Вот все заметки по использованию формулы.

Примечания:

Эта формула работает как с текстом, так и с числами.
Есть пять аргументов смещения, которые необходимо удовлетворить, чтобы получить безошибочную информацию. Если вы напишете неправильную формулу, произойдет ошибка Ref.

Надеюсь, вы поняли, что такое функция смещения Excel и как ее использовать в Excel. Дополнительные статьи о поиске в Excel и сопоставлении значений с помощью формул можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].