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

Вчера в марафоне 30 функций Excel за 30 дней мы подменяли текстовые строчки при помощи функции REPLACE (ЗАМЕНИТЬ) и узнали, что вставлять знаки она тоже умеет.

В 26-й денек марафона мы будем учить функцию OFFSET (СМЕЩ). Она возвращает ссылку данного размера, отстоящую от стартовой ссылки на обозначенное количество строк и столбцов.

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

Функция 26: OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) возвращает ссылку, смещённую от данной ссылки на определенное количество строк и столбцов.

Как можно применять функцию OFFSET (СМЕЩ)?

Функция OFFSET (СМЕЩ) может вернуть ссылку на спектр, также работать в сочетании с иными функциями. Используйте её для того, чтоб:

Отыскивать суммы продаж для избранного месяца.
Суммировать данные о продажах за избранные месяцы.
Создавать динамические спектры на базе подсчета.
Суммировать реализации за крайние n месяцев.

Синтаксис OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) имеет вот таковой синтаксис:

reference (ссылка) – ячейка либо спектр смежных ячеек.
rows (смещ_по_строкам) – быть может положительным (вниз от исходной ссылки) либо отрицательным (ввысь от исходной ссылки).
cols (смещ_по_столбцам) – быть может положительным (на право от исходной ссылки) либо отрицательным (на лево от исходной ссылки).
height (высота) – количество строк в возвращаемой ссылке, должен быть положительным.
width (ширина) – количество столбцов в возвращаемой ссылке, должен быть положительным.
Если аргументы height (высота) либо width (ширина) не указаны, употребляются размеры начальной ссылки.

Ловушки OFFSET (СМЕЩ)

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

Пример 1: Находим сумму продаж для избранного месяца

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

Стартовая ссылка – это ячейка C1.
Количество строк для сдвига вводится в ячейке F2.
Суммы продаж находятся в столбце C, потому сдвиг по столбцам равен нулю.
Аргумент height (высота) равен 1 строке.
Аргумент width (ширина) равен 1 столбцу.

В ячейке H2 находится формула, весьма схожая на предшествующую, которая возвращает имя месяца. Единственное отличие – это сдвиг по столбцам: 1 заместо 0.

=OFFSET(C1,F2, 1 ,1,1)
=СМЕЩ(C1;F2;1; 1 ;1)

Замечание: В данном примере аргументы height (высота) и width (ширина) можно не указывать, так как нам нужна ссылка такого же размера, что и стартовая.

Пример 2: Суммируем реализации за избранные месяцы

В этом примере функция OFFSET (СМЕЩ) возвращает ссылку на данные о продажах за избранный месяц, а функция SUM (СУММ) подсчитывает сумму для этого спектра. В ячейке B10 указан номер избранного месяца 3, как следует, результатом будет сумма продаж в марте (Mar).

Исходная ссылка A3:A6.
Сдвиг по строчкам равен нулю (если ноль совершенно не указать, итог будет этот же).
Сдвиг по столбцам соответствует значению ячейки B10.
Высота и ширина не указаны, так как итоговый спектр должен быть такого же размера, что и исходный.

Пример 3: Создаем динамический спектр, основанный на подсчете

Вы сможете применять функцию OFFSET (СМЕЩ), чтоб сделать динамический спектр. В этом примере мы сделали именованный спектр MonthList с таковой формулой:

Если к списку в столбце C добавить ещё один месяц, он автоматом покажется в выпадающем перечне в ячейке F2, который употребляет имя MonthList, как источник данных.

Пример 4: Суммируем реализации за крайние n месяцев

В этом заключительном примере OFFSET (СМЕЩ) работает вкупе с SUM (СУММ) и COUNT (СЧЁТ), чтоб показать сумму за крайние n месяцев. Как добавляются новейшие значения, итог формулы будет автоматом скорректирован, чтоб включить величину продаж за крайний месяц. В ячейке E2 количество месяцев равно 2, потому складываться будут суммы за август (Aug-10) и сентябрь (Sep-10).

Функция СМЕЩ() в EXCEL. Примеры и описание

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

Функция СМЕЩ в Excel

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

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

Синтаксис

=OFFSET(reference, rows, cols, [height], [width]) – британская версия

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

Индивидуальности внедрения

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

Интересно почитать: Функция в excel ячейка

Синтаксис

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

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

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

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

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

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

Примеры

Дана начальная таблица с 3-мя столбцами.

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

Для подсчета суммы значений в столбце Продажа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 .

Пример функции СМЕЩ

Тем, кто знакомится с функцией СМЕЩ, сначала кажется, что ее полностью можно поменять обычной нам функцией СУММ. Но в неких вариантах СУММ просто не работает. Разберем это на определенном примере.

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

Но как склад дает новейшую марку зубной пасты (назовем ее M), предприятию приходится добавлять доп строчки. Если создать это и ввести новейшую цифру, то СУММ никак не отреагирует на новейшую строчку (о этом гласит возникновение зеленоватого треугольника в верхнем левом углу ячейки и восклицательного знака рядом), поэтому что команда рассчитана для определенного спектра. И мы вправду лицезреем, что сумма осталась прежней = 554.

Принципиально хорошо записать формулу для команды СМЕЩ, чтоб она работала верно. Действуем по порядку. Наша цель – сосчитать сумму, потому функция СУММ будет наружной.

Первым числом в СУММ будет ссылка на первоначальную ячейку, с которой пойдет подсчет. Это D5. Опосля нее записываем команду СМЕЩ, которая будет находиться снутри СУММ.

Начинаем заполнять синтаксис функции СМЕЩ. 1-ое – это ссылка. Ссылаться будем на саму ячейку, в которой у нас стоит формула, т.е. на D18.

Смещение по строчкам указываем в отрицательном значении -1, чтоб 2-ой элемент спектра суммирования постоянно находился в предшествующей ячейке.

Смещение по столбцам нам не надо, потому ставим или 0, или просто оставляем пустое пространство. Не забываем делить элементы формулы точкой с запятой.

Высоту и ширину не указываем. По дефлоту она будет равна одной ячейке.

Опосля всех действий жмем Enter и получаем итог. Функция СМЕЩ просуммировала не только лишь данный спектр, да и добавленную строчку. Попробуем добавить еще одну, чтоб проверить работу команды. Пусть это будет марка зубной пасты под заглавием N по стоимости 51 рубль.

И лицезреем, что сумма возросла на 51 (вышло 650), в то время как команда СУММ осталась прежней (554).

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

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

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

Интересно почитать: Как расшифровывается функция впр в excel

Примеры использования функции СМЕЩ в Excel

Пример 1. Отыскиваем последнюю заполненную ячейку в колонке

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

=OFFSET(A1,COUNT(A:A)-1,0) – британская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) – российская версия

Эта формула подразумевает, что не считая обозначенных значений нет никаких остальных, и в данной колонке нет пустых ячеек. Функция работает, подсчитывая полное количество заполненных ячеек и подходящим образом сдвигает ячейку “A1”.

К примеру, в обозначенном примере есть 8 значений, потому функция COUNT(A:A) либо СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку “A1” на 7, чтоб получить крайнее значение.

Пример 2. Создаем динамический выпадающий перечень с автоматическим дополнением новейших данных

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

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

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

Как создать таковой перечень:

Изберите ячейку, в которой вы желаете сделать выпадающий перечень;
Нажмите на вкладку Data => Data Tools => Data Validation;
В диалоговом окне Data Validation, в разделе Опции изберите List из выпадающего перечня;
В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) либо =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
Нажмите ОК

Как эта формула работает:

1-ые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это значит что изначальное значение в ячейке “A1”, которое не сдвигается ни по строчкам и по колонкам (0, 0);
4-ый аргумент функции показывает на высоту, и тут функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в спектре данных для выпадающего перечня. Основное условие – отсутствие пустых ячеек в спектре.
5-ый аргумент функции “1”, обозначает ширину спектра данных, которая в нашем случае равна одной колонке.

Пример

Скопируйте эталон данных из последующей таблицы и вставьте их в ячейку A1 новейшего листа Excel. Чтоб показать результаты формул, выделите их и нажмите кнопку F2, а потом — кнопку ВВОД. По мере необходимости измените ширину столбцов, чтоб созидать все данные.

Практическая работа «excel. обработка табличной инфы. логические функции»

Существует несколько операторов логических функций. Посреди главных следует выделить такие:

Есть и наименее всераспространенные логические функции.

У всякого из вышеуказанных операторов, не считая первых 2-ух, имеются аргументы. Аргументами могут выступать, как определенные числа либо текст, так и ссылки, указывающие адресок ячеек с данными.

Функции ИСТИНА и ЛОЖЬ

Оператор ИСТИНА воспринимает лишь определенное данное значение. У данной функции отсутствуют аргументы, и, обычно, она фактически постоянно является составной частью наиболее сложных выражений.

Оператор ЛОЖЬ, напротив, воспринимает хоть какое значение, которое не является правдой. Буквально так же эта функция не имеет аргументов и заходит в наиболее сложные выражения.

Функции И и ИЛИ

Функция И является связывающим звеном меж несколькими критериями. Лишь при выполнении всех критерий, которые связывает данная функция, она возвращает значение ИСТИНА. Если хотя бы один аргумент докладывает значение ЛОЖЬ, то и оператор И в целом возвращает это значение. Вид данной функции: =И(лог_значение1;лог_значение2;…) . Функция может включать в себя от 1 до 255 аргументов.

Функция ИЛИ, напротив, возвращает значение ИСТИНА даже в том случае, если лишь один из аргументов отвечает условиям, а все другие неверные. Её шаблон имеет последующий вид: =И(лог_значение1;лог_значение2;…) . Как и предшествующая функция, оператор ИЛИ может включать в себя от 1 до 255 критерий.

Функция НЕ

В отличие от 2-ух прошлых операторов, функция НЕ имеет всего только один аргумент. Она меняет значение выражения с ИСТИНА на ЛОЖЬ в пространстве обозначенного аргумента. Общий синтаксис формулы смотрится последующим образом: =НЕ(лог_значение) .

Функции ЕСЛИ и ЕСЛИОШИБКА

Для наиболее сложных конструкций употребляется функция ЕСЛИ. Данный оператор показывает, какое конкретно значение является ИСТИНА, а какое ЛОЖЬ. Его общий шаблон смотрится последующим образом: =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если-ложь) . Таковым образом, если условие соблюдается, то в ячейку, содержащую данную функцию, заполняют заблаговременно обозначенные данные. Если условие не соблюдается, то ячейка заполняется иными данными, обозначенными в 3-ем по счету аргументе функции.

Оператор ЕСЛИОШИБКА, в случае если аргумент является правдой, возвращает в ячейку его собственное значение. Но, если аргумент неверный, тогда в ячейку ворачивается то значение, которое показывает юзер. Синтаксис данной функции, содержащей всего два аргумента, смотрится последующем образом: =ЕСЛИОШИБКА(значение;значение_если_ошибка) .

Функции ЕОШИБКА и ЕПУСТО

Функция ЕОШИБКА инспектирует, не содержит ли определенная ячейка либо спектр ячеек неверные значения. Под неверными значениями понимаются последующие:

Зависимо от того неверный аргумент либо нет, оператор докладывает значение ИСТИНА либо ЛОЖЬ. Синтаксис данной функции последующий: = ЕОШИБКА(значение) . В роли аргумента выступает только ссылка на ячейку либо на массив ячеек.

Оператор ЕПУСТО делает проверку ячейки на то, пустая ли она либо содержит значения. Если ячейка пустая, функция докладывает значение ИСТИНА, если ячейка содержит данные – ЛОЖЬ. Синтаксис этого оператора имеет таковой вид: =ЕПУСТО(значение) . Так же, как и в прошлом случае, аргументом выступает ссылка на ячейку либо массив.

Интересно почитать: В эксель функция индекс

Как вставить функцию

Для этого необходимо создать последующие шаги.

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

Потом изберите желаемую функцию. В качестве примера разглядим «СЧЁТЕСЛИ». Сходу опосля этого вы увидите маленькую информацию о избранном пт. Для подробной справки необходимо будет кликнуть на обозначенную функцию. Для продолжения нужно надавить на «OK».

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

Перейдите к первому полю. Выделите необходимое количество клеток.

Во 2-м поле, в качестве примера, можно указать две пустые кавычки. Данная запись значит пустой текст. Другими словами в клеточке ничего не обязано содержаться.

Опосля этого нажмите на «OK».

Благодаря этому вы увидите какое-нибудь число. Этому значению будет соответствовать количество тех ячеек, которые удовлетворяют вашему аспекту. В этом случае мы выделили 14 пустых ячеек.

Если внести какие-нибудь конфигурации, то итог функции поменяется одномоментно.

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

Если данная строчка для вас кажется малеханькой и неловкой, необходимо надавить на жаркие клавиши Ctrl+Shift+U. Благодаря этому её высота возрастет в пару раз.

Для возврата к прежнему режиму необходимо повторить комбинацию кнопок Ctrl+Shift+U.

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

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

Функция ЕСЛИ

Возвращает одно значение, если данное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

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

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

Лог_выражение — это хоть какое значение либо выражение, принимающее значения ИСТИНА либо ЛОЖЬ. К примеру, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение воспринимает значение ИСТИНА. В неприятном случае — ЛОЖЬ. Этот аргумент быть может применен в любом операторе сопоставления.

Значение_если_истина — это значение, которое ворачивается, если лог_выражение равно ИСТИНА. К примеру, если этот аргумент — строчка «В границах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ покажет текст «В границах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то ворачивается значение 0. Чтоб показать слово ИСТИНА, нужно применять логическое значение ИСТИНА для этого аргумента. Значение_если_истина быть может формулой.

Значение_если_ложь — это значение, которое ворачивается, если лог_выражение равно ЛОЖЬ. К примеру, если этот аргумент — строчка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ покажет текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (другими словами опосля значение_если_истина нет точки с запятой), то ворачивается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (другими словами опосля значение_если_истина стоит точка с запятой с следующей закрывающей скобкой), то ворачивается значение 0. Значение_если_ложь быть может формулой.

До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования наиболее сложных проверок. См. крайний из приведенных ниже примеров.

Когда значения аргументов значение_если_истина и значение_если_ложь вычислены, функция ЕСЛИ возвращает приобретенное значение.

Если хоть какой из аргументов функции ЕСЛИ является массивом, все элементы массива рассчитываются при выполнении функции ЕСЛИ.

Microsoft Excel дает доп функции, которые можно использовать для анализа данных с внедрением критерий. К примеру, для вычисления числа возникновений текстовой строчки либо числа в спектре ячеек используйте функцию СЧЁТЕСЛИ. Для вычисления суммы значений, попадающих в интервал, данный текстовой строчкой либо числами, используйте функцию СУММЕСЛИ. Вычисление значений с внедрением условия.

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

В последующем примере, если значение ячейки A10 — 100, то лог_выражение имеет значение ИСТИНА и рассчитывается сумма для ячеек B5:B15. В неприятном случае лог_выражение имеет значение ЛОЖЬ и ворачивается пустой текст («»), очистительный ячейку, которая содержит функцию ЕСЛИ.

ЕСЛИ (A10=100; СУММ (B5:B15); «»)

Представим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925.

Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений при помощи последующих формул:

ЕСЛИ (B2>C2; «Превышение бюджета»; «OK») приравнивается «Превышение бюджета»

ЕСЛИ (B3>C3; «Превышение бюджета»; «OK») приравнивается «OK»

Представим, что необходимо назначить буквенную категорию числам, на которые ссылаются по имени «СреднийБалл». Группы приведены в последующей таблице.