Коллеги, добрый день! В этом видео мы обсудим функцию СМЕЩ, или OFFSET на английском, которая формирует динамически изменяемую ссылку на любой диапазон и позволяет решать много задач как в Excel, так и в Google-таблицах. Например, получать из диапазона только определенную часть данных в зависимости от выполнения определенного условия. Допустим, можно получать сумму продаж за любой период, меняя только количество месяцев в ячейке и никак не трогая сами формулы. Можно автоматически обрабатывать данные за то количество месяцев, которое прошло на текущую дату. Например, чтобы сравнивать показатели за несколько лет нарастающим итогом. Этот пример мы рассмотрим в данном видео. Давайте посмотрим на синтаксис функции СМЕЩ. У нее целых пять аргументов — это адрес ячейки, число строк, число столбцов, высота и ширина. Адрес ячейки — это левая верхняя ячейка того диапазона, который вы хотите вернуть с помощью функции СМЕЩ. Число строк — это отступ от ячейки вниз или вверх при отрицательном аргументе, заданный в предыдущем аргументе. Число столбцов — это отступ от ячейки вправо или влево при отрицательном аргументе, заданный в первом аргументе. Высота — это высота итогового диапазона. Ну и ширина, соответственно, это его ширина. Функция кажется очень сложной, но несколько примеров помогут нам ее понять. Например, следующая функция задает диапазон A1...A9. Это диапазон, начинающийся в ячейке A1, без отступов от нее по строкам и столбцам, два нуля, с высотой 9 и шириной 1. А если мы добавим отступ по столбцам и строкам, на одну строку и на один столбец, и увеличим ширину до 3, получим другой диапазон, который вы можете видеть. Следующая функция возвращает диапазон, начинающийся в ячейке B2, на строку ниже A1 и на столбец правее, с высотой 9 и шириной 1. Данная функция возвращает диапазон, который начинается в ячейке B6, на строку выше C7 и на столбец левее, с высотой 5 и шириной 2 ячейки. В общем виде диапазон, задаваемый функцией СМЕЩ, выглядит так. Синяя ячейка — это точка отсчета, с которой начинается диапазон, далее мы задаем отступ от этой ячейки по строкам и по столбцам в любую сторону и задаем высоту, ширину диапазона. Если же их не задать, это будет ссылка на одну-единственную ячейку. На самом деле, меняя параметры функции СМЕЩ, можно задать ссылку на абсолютно любой диапазон листа, начиная с любой ячейки, она универсальна. Конечно, на практике никто не будет ссылаться на какой-то фиксированный диапазон таким замысловатым образом с помощью функции СМЕЩ. Если вам нужна ссылка именно на A1...A9, вы так и укажете в формулах, A1:A9, напрямую. А преимущество функции СМЕЩ в том, что некоторые из ее параметров могут изменяться. Например, высота или ширина диапазона могут меняться в зависимости от текущей даты или пожеланий пользователя файла, допустим, чтобы суммировать данные за n месяцев, меняя это самое число n в какой-то ячейке. Этот пример мы с вами и рассмотрим в данном документе, где у нас есть статистика по выручке по месяцам за несколько лет. Давайте посмотрим на функции СМЕЩ на практике. Напомню, первый ее аргумент — это стартовая ячейка, от которой можно сделать отступ или его не делать. Допустим, если мы не делаем никакого отступа и потом укажем параметры «высота» и «ширина» равными 5 и 1, мы получим пять ячеек в один столбец, пять строк, один столбец от ячейки B2. Если бы мы указали отступ по строкам, это были бы данные, соответственно, с февраля, а не с января, не с первого месяца. Сама по себе функция СМЕЩ, как вы видите, возвращает диапазон, то есть выводит целый массив данных, мы могли бы вывести данные за целый год, например. При этом ее можно обрабатывать. То есть на самом деле она может быть аргументом, например, функции СУММ. И тогда мы будем автоматически получать сумму всех этих чисел, а не выводить их массивом. Как я уже говорил, просто ссылаться на фиксированный диапазон из 12 строк и одного столбца никакого смысла нет, весь интерес в том, что мы можем в отдельной ячейке, давайте ее выделим, указывать число месяцев, которое хотим вывести. Например, вот таким образом сделаем ячейку, подпишем ее «Месяцы» и будем ссылаться на нее, то есть в функции СМЕЩ не фиксированно писать 12 или 6 месяцев, а ссылаться на ячейку. Теперь мы можем поменять число в ячейке, и функция будет тут же выводить то количество месяцев, которое нам нужно. Таким образом, если, например, мы теперь добавим к ней функцию СУММ, то мы будем сразу получать функцию за то количество месяцев, которое укажем в ячейке: за 12, 24 или любое другое число будет пересчитываться автоматически. При этом пользователю необязательно вообще залезать в формулу и разбираться в ней, если ее настроили один раз. Второй параметр, ширина, тоже может браться из ячейки. В данном случае это неактуально, у нас один столбец с выручкой. Но мы можем брать отступ, вместо единички брать его из другой ячейки. Давайте сделаем ячейку с отступом, назовем ее «Отступ» или, например, «Начиная с», двоеточие. И будем на нее тоже ссылаться. И можем поменять для интереса функцию на другую. То есть сейчас мы суммируем, а давайте считать среднее значение за определенный период. Функция СРЗНАЧ. Таким образом что происходит? Мы считаем среднее значение за столько месяцев, сколько указано в ячейке E1, отступаем при этом от начала периода, от января 17-го года, столько месяцев, сколько указано в G1, и считаем среднее. То есть мы можем посчитать среднее, например, за первый год вот таким образом, а можем посчитать за второй год или за два первых года, меняя только эти параметры. Кстати говоря, функцию СМЕЩ можно использовать и для создания динамических графиков, хотя мы еще не рассматривали такую функцию, как SPARKLINE, и будем рассматривать ее позднее. Я сейчас просто покажу, что она может использоваться в сочетании с функцией СМЕЩ для построения динамических графиков. Функция SPARKLINE строит маленький график в ячейке, исходя из какого-то диапазона. И диапазон может задаваться динамически с помощью СМЕЩ. То есть мы, например, можем строить график по выручке, начиная с ячейки B2, никуда не отступать и брать то количество месяцев, которое у нас указано в специальной ячейке, а ширину сделать единичкой. И таким образом, видите, у нас будет строиться график за то количество месяцев, которое мы укажем в ячейке, то есть абсолютно динамически формируемый автоматически в зависимости от параметров. Коллеги, давайте рассмотрим еще один пример применения функции СМЕЩ. Речь будет идти про нарастающий итог. У нас есть некий автосервис с разными категориями, с разными направлениями, с выручкой по месяцам. Есть данные за предыдущий год, допустим, это 18-й. Есть данные за 19-й год, которые заполняются по мере формирования отчетности, по мере завершения периодов. И мы хотим сравнивать именно нарастающий итог. То есть столько месяцев прошлого года, сколько прошло в текущем, чтобы не сравнивать 12 месяцев прошлого года с четырьмя текущего. Если с текущим годом все понятно, и можно ограничиться простой функцией СУММ, просто потому что у нас не прошедшие месяцы еще не заполнены, там стоят нули, искажения все равно не будет, в текущем году простая сумма и будет выдавать нарастающий итог, то в случае с 18-м годом у нас не получится это сделать, нам придется каждый месяц руками менять формулы. То есть сейчас мы считаем первые четыре месяца, чтобы иметь возможность сравнивать, уже в июне нам придется руками менять формулы, добавлять май и так далее. Но с помощью функции СМЕЩ этот процесс можно автоматизировать. Ведь по сути что происходит? Нам каждый месяц нужно брать диапазон, который начинается в столбце B, высота у него — 1, а ширина — это столько месяцев, сколько прошло на данный момент. То есть если сейчас у нас май, пятый месяц, то нам надо взять диапазон вот такой, шириной 4. Если сейчас июнь, то шириной 5, и так далее. Соответственно, как это можно сделать? Есть функция МЕСЯЦ. Она по текущей, вернее, по любой дате может вернуть номер месяца, например, сегодняшний по сегодняшней дате. То есть вот такая конструкция будет выдавать каждый день текущий месяц, номер текущего месяца. Если вычесть из него единичку, то мы получим то количество месяцев, которое прошло в любой заданный день. И осталось это использовать как ширину диапазона функции СМЕЩ. То есть мы задаем СМЕЩ, начиная с ячейки B2, никуда не отступаем, второй и третий аргумент — нули, то есть всегда стартуем с B2, B3 и так далее. Высота у нас единичка, а, соответственно, ширина у нас задается количеством прошедших в году месяцев. Ошибка у нас из-за двух знаков «равно», давайте это исправим и проверим: 270 857 — это всего лишь первая ячейка. Давайте убедимся, в чем у нас ошибка. Смотрим. Напомню, можно выделять фрагменты формулы. Ширина у нас действительно 4, все в порядке. В чем же проблема? В том, что мы вывели целый массив с помощью функции СМЕЩ, а нам нужна сумма. Мы уже в предыдущем примере рассмотрели, что можно просто взять функцию СМЕЩ, не выводить четыре ячейки отдельно, а вывести всю сумму. А в остальном у нас все работало корректно, просто мы не сформировали сумму сразу, а вывели четыре ячейки, которые нам были нужны. Итак, вот эта формула суммирует все ячейки, начиная с B2 и вправо вплоть до того момента, пока не закончится количество прошедших на данный момент месяцев. То есть это будет формула автоматического формирования нарастающего итога всегда. Соответственно, как только наступит июнь, функция МЕСЯЦ вернет шестерку, потому что это номер текущего месяца, и функция СМЕЩ будет возвращать диапазон из пяти столбцов, то есть автоматически подсчитывать корректный нарастающий итог. Итак, коллеги, функция СМЕЩ, которую мы рассмотрели с вами в этом видео, не так проста для освоения, но она открывает большие возможности для работы с данными, так как позволяет ссылаться динамически на любой диапазон, создавать интерактивные отчеты, избавляя при этом пользователя от необходимости копаться в формулах и позволяя ему менять нужные параметры прямо в ячейках.
