Добрый день!
Темой этой статьи станет функция СМЕЩ в Excel. Как вы поняли с названия функции, ее основное задание работать с данными которые будут или уже смещены, то есть со своеобразными динамическими диапазонами.
Вы сами понимаете, как это хорошо когда Excel при работе с вашими данными при добавлении новых строк или столбиков изменяет диапазон данных. Особенно это актуально при построениях графиков и диаграмм. Представьте, к примеру, что у вас есть диапазон данных, которые вы используете для построения графиков и диаграммы, но их очень много и диаграмма получается в нечитабельном виде. Как выход из ситуации, вы можете использовать только часть данных, неделя, месяц, квартал и т.п., а после, функция СМЕЩ, нужный вам диапазон данных отправит для построения нового, более понятной диаграммы.
Так вот давайте же познакомимся более близко с этой замечательной возможностью, а также с функцией, которая нам это позволит сделать. Итак, функция СМЕЩ в Excel имеет следующий синтаксис:
=СМЕЩ( ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина]), где:
- Ссылка – этот аргумент является обязательным и от него, собственно и начинается вычисление смещения. Данный аргумент должен являться ссылкой на конкретную ячейку или же на определённый диапазон смежных ячеек, иначе функция СМЕЩ вернет вам ошибку #ЗНАЧ!;
- Смещение по строкам – является обязательным аргументом, он означает то количество строчек, которое нужно отсчитать вниз или вверх и необходимо, чтобы верхняя левая ячейка полученного результата ссылалась на нужную вам ячейку. К примеру, для этого аргумента ставим число 5, это означает что верхняя левая ячейка ссылки, которая возвращается, обязана быть ниже на 5 строк, нежели тот, который вы указали в предыдущем аргументе. Данное значение может быть положительным (это когда смещение происходит ниже начальной ссылки) и отрицательным (это когда выше начальной ссылки);
- Смещение по столбцам – также обязательный аргумент и означает то количество столбиков которое необходимо отсчитать вправо или же влево, для того чтобы верхняя левая ячейка результата ссылалась на нужную вам ячейку. К примеру, если этот аргумент указан как число 5, то это означает что верхняя левая ячейка ссылки, которая возвращается, смещается на 5 столбиков вправо от значения аргумента «ссылка». Этот аргумент бывает положительным (для размещения справа от первоначальной ссылки) и отрицательным (при размещении слева);
- Высота – является необязательным аргументом, в котором указывается высота (число строчек) ссылки, которая возвращается. В обязательном порядке этот аргумент обязан быть положительным значением;
- Ширина— является необязательным аргументом, в котором указывается ширина (число столбиков) ссылки, которая возвращается. В обязательном порядке этот аргумент обязан быть положительным.
А теперь рассмотрим несколько стандартных примеров, как используется функция СМЕЩ в Excel: При работе функция СМЕЩ в Excel может иметь некоторые ограничения или нюансы:
- В случае, когда аргументы «смещение_по_строкам» и «смещение_по_столбцам» уводят вашу ссылку за границу вашего рабочего листа, функцией будет возвращена ошибка #ССЫЛ!;
- Когда аргументы «высота» и «ширина» в формуле не указан, то по умолчанию будет использоваться те же параметры высоты и ширины, как и в аргументе «ссылка»;
- Функция СМЕЩ в Excel не меняет выделения и не двигает никакие ячейки, она всего лишь возвращает ссылку и ее можно использовать в совмещении с любой функцией, где используется аргумент типа «ссылка».
При работе с функцией СМЕЩ возникает закономерный вопрос, почему просто указать диапазон, типа A1:D5, нельзя прямо? А дело в том что причин может быть несколько:
- Во-первых, в случае, когда точный адрес нам неизвестен. В большинстве случаев нам известна, только лишь стартовая ячейка и иногда отсутствуют знания фактического адреса. В таких ситуациям использовать функцию СМЕЩ в Excel обязательно;
- Во-вторых, необходимость использования динамических диапазонов. Как вы понимаете указанный диапазон в ссылке A1:D5 и будет возвращать аналогичное значение, то есть он имеет статичную основу, а в некоторых случаях нам нужно динамичный диапазон. Эта необходимость возникает в случаях, когда данные изменяются (добавляются новые строчки и колонки).
Чтобы функция СМЕЩ в Excel стала вам ближе, рассмотрим ее на основе примера, вы ведете учет топлива в АТП, или транспортного отдела другой компании. Вот вам для заказа или учёта ГСМ нужно отслеживать постоянно расход топлива и сопутствующих материалов. Для этих целей мы создаем простую таблицу следующего вида (я использую небольшие числа для наглядности): Итак, какое же будет среднее значение расхода ГСМ, исходя из того что наши данные расхода находятся в диапазоне B2:B23, то нам можно написать формулу =СРЗНАЧ(B2:B23) и всё у нас получилось. Но, увы, нам очень важно, чтобы изменение в формуле происходили ежедневно и автоматически. Для этого нам нужно использовать функцию СМЕЩ всего один раз для вычисления среднего значения расхода топлива за последнюю неделю и вопрос будет закрыт, для этого нам нужна формула следующего вида:
=СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(B2:B300) -7;0;7;1))
Эта формула позволит нам найти искомое, и давайте познакомимся с ней более близко. Для начала формула СЧЁТЗ(B2:B300) -7 позволит нам посчитать количество значений и от полученного результата отнимает 7 дней (неделю). Это даст нам возможность определится с диапазоном для вычисления с помощью функции СРЗНАЧ, среднего недельного расхода топлива. А формула СМЕЩ смещает с ячейки В2 на начальную ячейку нашей последней недели. Ну вот, я надеюсь, что функция СМЕЩ в Excel стала вам понятнее и ближе. Если статья вам помогла, жду ваш лайк или комментарий, заранее за это благодарен. С другими не менее интересными функциями вы можете ознакомиться в «Справочнике функций».
До новых встреч на страницах сайта!!!
«Мало кто из нас может вынести бремя богатства. Конечно, чужого
«
Марк Твен
