Динамические диапазоны – это инструмент, который позволяет вам работать с меняющимися данными более гибко и удобно. Вам больше не придется вручную обновлять диапазоны ячеек при добавлении или удалении данных.
Сегодня я покажу, как с помощью функции СМЕЩ можно автоматизировать создание таких диапазонов. Если вы только начинаете знакомиться с более сложными функциями Excel, не переживайте – я объясню все максимально просто и наглядно.
Пример таблицы для работы
Для начала давайте создадим небольшую таблицу, с которой мы будем работать, чтобы на практике разобрать функцию СМЕЩ. Представьте, что у нас есть таблица продаж за определенный период, которую вы видите на следующем изображении.
В этой таблице указаны даты продаж, названия товаров и количество проданных единиц. Мы будем использовать функцию СМЕЩ для того, чтобы динамически определять диапазоны, например, для подведения итогов продаж за последние несколько дней или для автоматического обновления данных в графиках по мере их добавления.
Что такое функция СМЕЩ
Функция СМЕЩ в Excel возвращает диапазон ячеек, смещенный относительно заданной ячейки на определенное количество строк и столбцов. Это позволяет динамически изменять диапазон данных, который вы хотите анализировать. Ее синтаксис выглядит следующим образом:
СМЕЩ(начальная_ячейка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина])
Стоит более детально разобрать каждый аргумент, чтобы вы понимали, в каком порядке вводятся данные и что вообще представляет собой функция в полном своем представлении.
-
начальная_ячейка – ячейка, от которой начинается смещение.
-
смещение_по_строкам – количество строк, на которое нужно сместиться.
-
смещение_по_столбцам – количество столбцов для смещения.
-
высота (опционально) – количество строк в возвращаемом диапазоне.
-
ширина (опционально) – количество столбцов в возвращаемом диапазоне.
Теперь, когда вы знаете базовый синтаксис, давайте разберем несколько примеров. Это поможет не только усвоить полученные знания, но и на практике разобраться, как работает функция СМЕЩ и для каких целей может быть применена.
Пример 1: Простой диапазон на основе СМЕЩ
Этот и следующие примеры будут базироваться на описанном выше примере. Вы можете запомнить или скопировать его, чтобы повторять действия и смотреть на результат. Предположим, вы хотите получить значение продаж для товара, который был продан 03.01.2024, но не хотите вручную выбирать ячейку. В этом случае можно использовать функцию СМЕЩ. Мы будем смещаться от ячейки B2 (это первое значение столбца «Товар» после его заголовка) на две строки вниз и одну строку вправо, чтобы получить значение продаж. В этом случае вся формула будет выглядеть очень просто.
=СМЕЩ(B2; 2; 1)
Эта формула берет начальную точку в виде первого значения столбца (можете заменить ячейку на заголовок B1 и добавить еще одно смещение вниз, если так будет удобнее) и сдвигает диапазон на необходимое количество строк и столбцов, что позволяет без лишних манипуляций получить нужные данные из таблицы. Как итог, вы получаете конкретное значение продаж за выбранную дату.
Пример 2: Создание динамического диапазона для суммирования
Теперь давайте представим, что вы хотите суммировать продажи за последние три дня, но данные будут постоянно добавляться. Для этого вам понадобится динамический диапазон, который автоматически обновляется по мере ввода новых данных. Введите следующую формулу для суммирования последних трех значений в столбце «Продажи».
=СУММ(СМЕЩ(C2; СЧЁТ(C2:C6)-3; 0; 3; 1))
Здесь:
-
C2 – это начальная точка (первое значение столбца «Продажи»).
-
СЧЁТ(C2:С6)-3 – указывает Excel сместиться на столько строк вниз, чтобы выбрать последние три значения.
-
3 – это высота диапазона (3 строки).
-
1 – ширина диапазона (1 столбец).
Формула автоматически подстраивает диапазон, включая в расчет только последние три дня. Это удобно, так как при добавлении новых строк диапазон будет изменяться без необходимости ручного редактирования, а итоговое значение суммы всегда будет актуальным.
Пример 3: Автоматическое обновление данных для графика
Теперь давайте рассмотрим более сложный пример. Допустим, вы строите график на основе данных продаж, и вам нужно, чтобы этот график автоматически обновлялся по мере добавления новых строк в таблицу. Для решения задачи создадим динамически обновляемый диапазон при помощи уже знакомой функции.
=СМЕЩ(C2; 0; 0; СЧЁТ(C2:C100); 1)
Здесь СЧЁТ(C2:С100) автоматически определяет количество строк с данными в столбце, начиная с ячейки C2, и диапазон будет изменяться по мере добавления новых строк. Привяжите полученный в новых ячейках динамический диапазон к вашему графику, и он будет обновляться автоматически без необходимости вручную редактировать диапазон данных.
Формула создает автоматически изменяющийся диапазон, который постоянно расширяется или сокращается в зависимости от количества данных. Это избавляет вас от необходимости редактировать график при каждом изменении в таблице, что делает работу с графиками более эффективной и удобной.
Пример 4: Комбинация с функцией СУММЕСЛИ
Функцию СМЕЩ можно комбинировать с еще со множеством других функций, например, с СУММЕСЛИ. Допустим, вам нужно посчитать суммарные продажи для товаров, проданных за последние 3 дня, если количество продаж превышает 100 единиц. В таком случае формула немного усложняется, добавляются математические условия и ее итоговый вид будет таким, как видно на следующем изображении.
=СУММЕСЛИ(СМЕЩ(C2; СЧЁТ(C2:C6)-3; 0; 3; 1); ">100")
Здесь СМЕЩ динамически выбирает последние три дня, а СУММЕСЛИ считает только те продажи, которые превышают 100 единиц. В результате использования этой формулы вы получите сумму продаж только для тех дней, когда количество проданных товаров превышает 100. Это решение гибко адаптируется к изменению данных в таблице, а значит, результат всегда будет актуален без необходимости пересчета вручную.
В завершение можно подвести итог, что СМЕЩ – полезная функция, которая значительно облегчает работу с динамическими диапазонами в Excel. Она позволяет автоматически изменять диапазоны данных в зависимости от ввода новых данных, что особенно полезно при работе с большими таблицами или при необходимости обновления графиков. Я предлагаю начать с простых примеров, чтобы закрепить понимание функции, а затем двигаться к более сложным комбинациям с другими функциями, отталкиваясь от того, в каких условиях вы собираетесь использовать формулы.
Комментарии