Функция СМЕЩ() в EXCEL

Функция СМЕЩ() нередко употребляется при разработке динамических диапазонов . Разглядим ее подробнее.

Синтаксис функции СМЕЩ()

ссылка — ссылка, от которой рассчитывается смещение;
смещ_по_строкам — количество строк, которые требуется отсчитать ввысь либо вниз, чтоб левая верхняя ячейка результата ссылалась на подходящую ячейку (по дефлоту =0);
смещ_по_столбцам – аналогично смещ_по_строкам, лишь смещение отсчитывается по столбцам (по дефлоту =0);
высота — число строк возвращаемой ссылки. Значение аргумента «высота» обязано быть положительным числом;
ширина — число столбцов возвращаемой ссылки (по дефлоту =1).

Чтоб было понятнее, потренируемся с функцией СМЕЩ() , используя файл примера .

Примеры

Дана начальная таблица с 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 .

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

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

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

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

Функция СЦЕПИТЬ в Excel: синтаксис, внедрение, примеры

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

Функция «Сцепить»

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

Интересно почитать: Функция впр в эксель с несколькими условиями примеры

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

Если вы используете не русскую версию программки, то на британском функция «Сцепить» в Excel именуется «Concatenate». В остальном все, описанное ниже, соответствует и английской версии – синтаксис, применение и примеры.

Применение

Операция имеет последующий синтаксис: СЦЕПИТЬ([текст2];[текст2];…). Количество строковых атрибутов быть может каким угодно огромным, основное, чтоб приобретенный текст соответствовал требованию к длине результирующего поля. В качестве этих атрибутов могут быть как заблаговременно определенные значения, так и данные на листе Excel. При всем этом типы данных элементы для объединения не непременно должны быть текстовыми.

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

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

Характеристики функции можно заносить и прямо в окно содержимого ячейки. В приведенном нами примере строчка операции будет смотреться так:

В итоге получим в избранной ячейке текст 21.09, состоящий из данных ячейки D2 – числа 21, точки и данных ячейки Е2 – строчки «09». Информация в поле Е2 представляет собой число, но отформатирована как текст, потому что принципиально, чтоб на первом месте стояла цифра 0. Если такое число привести к числовому формату, этот ноль пропадет, и остается лишь цифра 9.

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

Примеры

Приведем еще несколько примеров функции «Сцепить» в Excel. До этого всего, закончим начатое выше и превратим информацию в ячейке G2 в полноформатную дату рождения клиента. Для этого нам будет нужно соединить в одной строке денек рождения, месяц и год, содержащиеся, соответственно, в ячейках D2, E2 и F2. Меж этими значениями нужно поставить точки.

В окне аргументов добавится еще 2 поля, в итоге оно будет смотреться таковым образом:

Интересно почитать: Функции эксель

Строчка функции будет иметь вид:

Видите ли, специально введенные значения – в нашем примере это точки – выделяются кавычками в строке.

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

По аналогии, в данной нам же таблице соберем при помощи функции «Сцепить» в Excel данные о именах клиентов и получим их полные ФИО в одном столбце из разбитых частей. Используем выражение =СЦЕПИТЬ(A2;» «;B2;» «;C2). Направьте внимание, что в качестве разделителей отдельных частей имени стоят пробелы. О их необходимо постоянно держать в голове при таковых операциях.

Результирующая таблица показана на снимке:

Оборотная функция

Время от времени юзеру нужно выполнить обратную задачку: поделить значение в ячейке на составляющие по какому-либо признаку. Функции в Excel, оборотной СЦЕПИТЬ, нет, но есть несколько легких методов достигнуть хотимого результата.

1-ый – внедрение функции «Данные по столбцам» панели «Работа с данными» вкладки «Данные». При вызове данной нам способности возникает мастер разделения, в котором вы задаете характеристики:

Каким образом проводить разделение: исходя из разделяющих символов либо по количеству знаков.
Если с разделяющими знаками, то какие конкретно считать такими.
Определяете характеристики всякого из приобретенных столбцов, и в какие конкретно колонки записывать значения.

2-ой метод – внедрение команды VBA Split. Ее синтаксис:

Split (Выражение,[Разделитель],[Количество строк],[Сравнение]).

Атрибут «Выражение» является неотклонимым – это строчка для разделения. «Разделитель» указывает символ, по которому начальный текст делится на части, по дефлоту это пробел. «Количество строк» описывает наибольшее число результирующих значений. «Сопоставление» — способ, определяющий чувствительность к реестру знаков.

Остальные способности объединения строк

Аналогом функции СЦЕПИТЬ в Excel является символ амперсанда — &. В нашем примере он применяется таковым образом:

В итоге получим те же значения, что и ранее:

В Excel версии 2016 возникла новенькая функция СЦЕП(). Ее отличие в том, что у нее всего один атрибут – спектр ячеек. Можно соединить значения полей, выделив спектр, а не вводя раздельно каждую ячейку.

Функция «Сцепить» в Excel очень полезна в работе с программкой. На базе имеющихся данных создаются новейшие поля при помощи композиции их в хотимом порядке и сочетании.

Функция И(AND) excel с примерами

Функция И в Excel — это логическая функция, которая просит одновременного выполнения нескольких критерий. И возвращает ИСТИНА если все аргументы имеют значение ИСТИНА либо ЛОЖЬ если хотябы один из аргументов имеет значение ЛОЖЬ. Чтоб проверить, является ли число в A1 больше нуля и меньше 10, используйте
=И(A1> 0, A1 <10). Функцию И можно применять в качестве логической проверки снутри функции ЕСЛИ, чтоб избежать доп вложенных ЕСЛИ, и ее можно сочетать с функцией ИЛИ.

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

Функция AND(И) имеет 1 неотклонимый аргумент и может употребляться для одновременной проверки до 255 критерий, передаваемых в качестве аргументов, в Excel 2003 функция может обрабатывать не наиболее 30 аргументов. Любой аргумент ( логическое_значение1 , логическое_значение2 и т. Д.) Должен быть выражением, возвращающим ИСТИНА либо ЛОЖЬ, либо значением, которое быть может оценено как ИСТИНА либо ЛОЖЬ. Аргументы, предоставляемые функции И, могут быть константами, ссылками на ячейки, массивами либо логическими выражениями.

Предназначение функции И — оценить наиболее 1-го логического теста сразу и возвратить ИСТИНА, лишь если все результаты ИСТИНА. К примеру, если B3 содержит число 40, то:

Функция И будет оценивать все предоставленные значения и возвращать ИСТИНА, лишь если все значения оцениваются как ИСТИНА. Если какое-либо значение оценивается как ЛОЖЬ, функция И возвратит ЛОЖЬ. Примечание. Excel оценит хоть какое число, не считая нуля (0), как ИСТИННОЕ.

Как и функция И функция ИЛИ будет соединять воединыжды результаты в одном значении. Это значит, что их недозволено применять в операциях с массивами, которые должны выдавать массив результатов. Чтоб обойти это ограничение, вы сможете применять логическую логику. Для получения доборной инфы см .: Формулы массивов с логикой И и ИЛИ.

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

Функция И возвращает #ЗНАЧ! если во время оценки не найдено либо не сотворено логическое значение

Примеры использования функции И

Пример 1

В последующей таблице показаны три примера функции И в Excel:

Направьте внимание, что в приведенном выше примере электрической таблицы:

функция в ячейке Е2 оценивается как ИСТИНА, так как ОБА из поставленных критерий ИСТИНА;
функция в ячейке Е3 оценивается как ЛОЖЬ, так как третье условие, С3> 22 , ЛОЖЬ;
функция в ячейке Е4 оценивается как ЛОЖЬ, так как ВСЕ предоставленные условия — ЛОЖЬ.

Пример 2

Чтоб проверить, является ли значение в B3 больше 1 и меньше 6, вы сможете применять AND последующим образом:

Пример 3

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

Эта формула возвратит «Правильно», лишь если значение в B3 больше 0 и меньше 5.