30 функций Excel за 30 дней: СМЕЩ (OFFSET) – ОфисГуру

Как сделать многоуровневый зависимый выпадающий список, чтобы значения каждого уровня зависели от выбранного на предыдущем уровне?

Описание

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

1) ВПР

Функция ВПР позволяет найти данные в текстовой строке таблицы или диапазоне ячеек и добавить их в другую таблицу. Аббревиатура ВПР расшифровывается как «вертикальный просмотр».

Синтаксис

Данная функция состоит из 4 аргументов и представлена следующей формулой:

=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Рассмотрим каждый из аргументов:

  • «Искомое значение» указывают в первом столбце рассматриваемого диапазона ячеек. Данный аргумент может являться значением или ссылкой на ячейку.
  • «Таблица». Группа ячеек, в которой выполняется поиск искомого значения и возвращаемого. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение – в любом месте.
  • «Номер столбца». Номер столбца, содержащий возвращаемое значение.
  • «Интервальный просмотр» – необязательный аргумент. Это логическое выражение, определяющее – насколько точное совпадение должна обнаружить функция. В связи с этим условием выделяют 2 функции:
  1. ИСТИНА. Эта функция, вводимая по умолчанию, ищет ближайшее к искомому значение. Данные первого столбца должны быть упорядочены по возрастанию или в алфавитном порядке.
  2. ЛОЖЬ. Данная функция ищет точное значение в первом столбце.

Примеры

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

Результат – значение ячейки, содержащей искомое слово «планшет»: «31325»

Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.

В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.

В данном случае применен интервальный просмотр с функцией ИСТИНА, которая выдает приблизительное значение – «886146» («купить машину») при искомом значении «900000»
Применение интервального просмотра с функцией ЛОЖЬ позволяет найти значение, в точности равное искомому

Функция ВПР является одной из самых популярных функций Excel, достаточно сложной для понимания, но чрезвычайно полезной.

3) СУММЕСЛИ и СУММЕСЛИМН

Функция СУММЕСЛИ позволяет суммировать данные, соответствующие определенному условию, находящиеся в указанном диапазоне.

Синтаксис

Функция состоит из 3 аргументов и имеет формулу:

=СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])

«Условие» – аргумент, определяющий какие именно ячейки нужно суммировать. Это может быть текст, число, ссылка на ячейку или функция. Обратите внимание на то, что условия с текстом и математическими знаками необходимо заключать в кавычки.

«Диапазон суммирования» – необязательный аргумент, который позволяет указать на ячейки, данные которых нужно суммировать, если они отличаются от ячеек, входящих в диапазон.

Пример

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

В данном примере функция вычислила сумму (519414), которая является сложением ячеек (B2:B4), удовлетворяющих условию (>100000). Формула: =СУММЕСЛИ(B2:B6;«>100000»)

Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.

Синтаксис

Формула данной функции имеет следующий вид:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.

Синтаксис

СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Аргументы функции СМЕЩ описаны ниже.

  • Ссылка    — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент “ссылка” должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

  • Смещ_по_строкам    Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента “смещ_по_строкам” задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе “ссылка”. Значение аргумента “смещ_по_строкам” может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).

  • Смещ_по_столбцам    Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента “смещ_по_столбцам” задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе “ссылка”. Значение “смещ_по_столбцам” может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).

  • Высота    Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента “высота” должно быть положительным числом.

  • Ширина    Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента “ширина” должно быть положительным числом.

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ() , использовано Условное форматирование . Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик .

Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования – $B$2:$B$9 (левый верхний угол – $B$2 , высота 8 , смещения верхнего угла нет). Результат 34 .

Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0; 1 ;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9 . Результат 68 .

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8; 2 )) указав ширину в 2 ячейки, результат составит 102 , диапазон будет модифицирован в $В$2:$С$9 .

Добавив смещение по строкам (+1), получим результат 99 : =СУММ(СМЕЩ($B$2; 1 ;0;8;2)) диапазон будет модифицирован в $В$3:$С$9 .

6) ЛЕВСИМВ

Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.

Синтаксис

Функция состоит из 2 аргументов и представлена формулой: =ЛЕВСИМВ(текст;[число_знаков]), где:

  • «текст» – текстовая строка, содержащая знаки, которые необходимо извлечь;
  • «число знаков» необязательный аргумент, указывает на количество извлекаемых знаков.

Пример

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

Формула в данном случае следующая: =ЛЕВСИМВ(А5;60)

К примеру, если вы хотите, чтобы тайтлы были максимально лаконичными и состояли из 60 знаков, функция отсчитает первые 60 символов и покажет, как будет выглядеть тот или иной тайтл. Для этого необходимо составить формулу: =ЛЕВСИМВ(А5;60), где А5 – адрес рассматриваемой ячейки, «60» – число извлекаемых символов.

Примечания

  • Если аргументы “смещ_по_строкам” и “смещ_по_столбцам” выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.

  • Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе “ссылка”.

  • Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа “ссылка”. Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

=СУММ(СМЕЩ(A2;;;4))

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5 .

Особенности применения

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Альтернативы функции OFFSET (СМЕЩ) в Excel

Ввиду некоторых ограничений функции, многие из вас рассматривают альтернативные методы:

  • Функция INDEX (ИНДЕКС) также может использоваться для возврата ссылки на ячейку.
  • Excel таблицы: если вы используете структурированные ссылки в таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.

15) СЖПРОБЕЛЫ

Данная функция удаляет все лишние пробелы между словами.

Синтаксис

Формула функции проста: =СЖПРОБЕЛЫ(номер_ячейки)

Пример

Функция позволяет убрать лишние пробелы в тексте

Функция простая и полезная. Единственный минус состоит в том, что она не различает границ слов, и если внутри него стоят пробелы, функция этого не поймет и не удалит их.

Функция не распознает пробелы внутри слов

17) ИНДЕКС

Функция ИНДЕКС позволяет возвращать искомое значение.

Синтаксис

Формула функции ИНДЕКС имеет следующий вид:

=ИНДЕКС(массив; номер_строки; [номер_столбца])

«Номер столбца» – необязательный аргумент.

Пример

В этом примере введена формула: =ИНДЕКС(A2:B3;2;2), данным которой соответствует значение («сливы»)

Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...