Возможно, вы не знаете, как использовать функцию СМЕЩ в Excel.

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

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

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

Создайте набор данных нестандартного размера

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

Например, чтобы предоставить автоматический отчет об общих объемах продаж сотрудников, вам необходимо получить общую сумму за определенную неделю или недели.

Написать отдельную функцию СУММ для каждой суммы может быть довольно легко. Однако использование функции СМЕЩ будет проще и быстрее в повторном использовании.

Сначала введите дату, с которой вы хотите начать, в ячейку B6. Затем введите количество дней, в течение которых вы хотите запустить отчет, в B7. Затем используйте простые функции СУММА и СРЗНАЧ, чтобы получить больше данных по каждому сотруднику за выбранный период:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Настроить дату или время начала так же просто, как отредактировать ячейку, но перетаскивать группы данных и изменять их размер гораздо проще.

Позволяет создавать собственные отчеты

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

Вышеупомянутое действие также позволяет перетаскивать выбранные фрагменты данных с одного листа на другой. Кроме того, он позволяет отображать только информацию, имеющую отношение к текущему отчету. Настроить отчет, который показывает ежедневные итоги с использованием тех же данных, что и раньше, довольно просто. Вы можете сделать это, не получая данные для несвязанных дат.

Следующая функция смещения, помещенная в ячейку B5, получит количество дней, указанное в B2. Он начинается на неделе, выбранной в B1, и отображает данные только за соответствующий период:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Можно ограничить записи в B1 и B2 и создать раскрывающийся список в Excel с доступными неделями, используя проверку данных.

Пагинация таблицы

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

При работе с электронными таблицами, содержащими до 30 000 строк данных, может быть сложно просмотреть все данные одновременно. Создайте раскрывающееся меню, которое принимает диапазон чисел от 1 до 300, что позволит вам быстро разделять данные по страницам со 100 результатами на страницу.

Точка привязки функции СМЕЩ всегда находится в верхней ячейке данных. Однако второй аргумент — это вертикальное смещение. Это значение равно номеру выбранной страницы минус 100. Смещение — горизонтальное, третий аргумент можно установить равным 0.

Четвертому аргументу присвоено значение 100, пятый аргумент — это количество столбцов в наборе данных. Этот метод чрезвычайно упрощает разделение и управление таблицами данных.

Ниже приведен пример данных с разбивкой на страницы. Он включает в себя 500 фрагментов информации, записанных с датчика, на одном листе и отчет с разбивкой на страницы на другом листе.

В отчете используется проверка данных в ячейке B1 для создания раскрывающегося меню со страницами внутри. Следующее выражение в ячейке A3 создает тело отчета:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Выше приведены некоторые способы использования функции смещения в Excel , о которых мало кто знает, но они действительно полезны, особенно когда вам нужно обработать большие объемы данных.

Надеюсь, статья окажется для вас полезной.