Новые функции Excel 2016 в апреле

Наконец-то и до меня дошла волна обновлений и в моем Excel 2016 по подписке Office 365 Pro Plus появились новые функции для работы с текстом и логикой. Давайте я пробегусь по ним на примерах:

Функция СЦЕП (CONCAT)

Многим, думаю, известна функция СЦЕПИТЬ (CONCATENATE), которую можно использовать для склеивания фрагментов текста из нескольких ячеек в одно целое. На практике, часто также используется спецсимвол & для аналогичного действия. Но оба этих способа предполагают указание каждой ячейки с текстовым фрагментом отдельно, что, при большом количестве ячеек, начинает напрягать:



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



Функция ОБЪЕДИНИТЬ (TEXTJOIN)

В предыдущем примере видно, что для красивого объединения мне пришлось предусмотреть отдельные ячейки с символами-разделителями. Новая функция ОБЪЕДИНИТЬ работает аналогично СЦЕП, но автоматически добавляет еще заданный символ между разными фрагментами:



Также обратите внимание на второй аргумент этой функции - он определяет, нужно ли игнорировать пустые ячейки (ИСТИНА) или нет (ЛОЖЬ).

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



Функция УСЛОВИЯ (IFS)

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



Теперь же можно ощутимо упростить формулу, если использовать новую функцию УСЛОВИЯ (IFS). В её аргументах попарно перечисляются условия и то, что нужно вывести при их выполнении:



Получается не сильно компактнее, но заметно понятнее, не правда ли?

Если будете ее использовать, то обратите особое внимание на два важных момента:
  • Функция проверяет условия в порядке их указания и при выполнении какого-либо условия уже не проверяет оставшиеся. Поэтому порядок условий играет роль: в приведенном выше примере проверка нарушений скорости идет от большего к меньшему, а не наоборот.
  • Если нужно задать результат, который должен выводиться, если ни одно из условий не выполнилось (как Else в VBA), то в конце списка условий можно задать условие ИСТИНА. В нашем примере - это сумма штрафа (0), если ни одного из нарушений скорости не было.
Функция ПЕРЕКЛЮЧ (SWITCH)

Некий аналог предыдущей функции или конструкции Select Case в языке программирования Visual Basic, если вам знакомы макросы. Сравнивает ячейку с набором заданных значений и выдает один из заданных в наборе результатов. Например:



Очень похоже на предыдущую функцию УСЛОВИЯ, но в ней условия можно задавать гибко (использовать знаки <>= и т.д.), а здесь проверяется только точное совпадение. Последним аргументом можно задать то значение, которое должно выводиться, если ни одно из предыдущих условий не выполнилось. Раньше, чтобы реализовать что-то подобное приходилось шаманить с функцией ИНДЕКС (INDEX) и массивом констант в фигурных скобках внутри формулы:



Функции МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

Совсем недавно писал статью и делал видео в Приемах на тему поиска максимального и минимального значений в таблице при выполнении одного или нескольких условий. Не суперсложная техника, но без формул массива или функций баз данных не обойтись. Теперь в Excel 2016 появились новые функции, которые все делают красиво:



Как видно, все по аналогии с СУММЕСЛИМН, СЧЁТЕСЛИМН и т.д. - переучиваться не нужно.

P.S.

Приятно, что Microsoft начала прислушиваться к мнению пользователей и добавлять новые удобные функции с каждым обновлением Excel. Происходит это не так быстро, как хотелось бы, но появилась надежда увидеть сумму прописью в Excel уже при моей жизни :)

И помните о том, что все эти приятные фишки будут работать только у тех, кто установил себе последнюю версию Excel 2016 и регулярно ее обновляет (сейчас это происходит, в основном, автоматически). В противном случае в списке доступных функции вы их не найдете, а другие пользователи увидят ошибку #ИМЯ на ячейках, где вы их использовали.

Файл со всеми вышеприведенными примерами новых функций можно скачать ниже:
1 мая 2016 15:18
Ошибка в формуле?
На втором скриншоте из параграфа "Функция ПЕРЕКЛЮЧ (SWITCH)" в формуле в массиве констант отсутствует единица перед "пн" в начале списка. И результат в C1 - ср, хотя номер дня - 5.  И даже с  исправлением массива констант, и заменой разделителей строк в этом массиве на двоеточие, ИНДЕКС ожидаемо вернет 5 вместо "пт",  для верного результата после номера строки (B1) нужно будет указать номер столбца - 2.
6 мая 2016 8:27
Виктор, спасибо за наводку - поправил.
2 мая 2016 17:00
Новые функции работают, к сожалению, только в офисе, распространяемом по подписке.
9 мая 2016 11:20
Да, это грусть.
Но подобный вариант распространения ПО нас всех, похоже, ждет в очень скором будущем.
Большинство крупных компаний-производителей (Microsoft, Adobe, Касперский...) уже давно перешли на такую модель.
13 мая 2016 9:46
Учитывая, что корпоративные операционные системы и офисы отстают на пару поколений, то может есть смысл ввести что-то подобное в PLEX, через UDF? Почитал статью и понял, что мне этого не хватает :)

Особенно УСЛОВИЯ (IFS) и СЦЕП (CONCAT)  
13 мая 2016 16:27
так в тот-то и дело, что корпорация Майкрософт изменила политику обновлений офиса
и сейчас это происходит очень оперативно по сравнению с тем, что было ранее (до 2016 офиса)
правда, нюанс в том, что в первую очередь обновляется только офис по подписке
таким нехитрым образом пользователей мотивируют переходить именно на офис, приобретаемый по подписке
13 мая 2016 16:50
Я как раз в курсе, но есть одно большое НО.

У нас дома два компа. Мой рабочий с MS Office 2010 и жены с офисом 365 - по годовой подписке и с регулярными обновлениями. Я активный пользователь Excel, но на корпоративный комп поставить офис 365, как Вы понимаете, не могу.

Поэтому и прошу Николая ввести новые User Defined Functions в PLEX, которые будут аналогичны описанным выше built-in функциям MS Excel 2016.
14 мая 2016 19:10
Олег, изобразить что-то похожее можно, но работать они буду в разы медленнее, чем "родные".
Т.е. на больших таблицах тормозить может ощутимо.
14 мая 2016 20:23
Николай, я это хорошо понимаю, но лучше уж так чем никак. Всегда можно конвертировать формулы в значения, чтобы таблица не тормозила. Если таблица нужна часто, а данные не уникальны (т.е. получаются с внешних источников) то пишу макрос и формулы также превращаю в значения после каждого шага макроса с целью ускорения быстродействия таблицы.

Также хорошо понимаю, что мое видение Вашей надстройки достаточно однобоко...так как осведомлен только о собственных нуждах. Однако, я предполагаю, что Вы получаете достаточно много комментариев от пользователей надстройки и можете определить, что стоит вводить в PLEX, а что нет. Поэтому решение какие функции надо вводить в PLEX, а какие нет - только за Вами :) а мы порадуемся очередному релизу.
12 августа 2016 9:51
Олег, я для этого в офисе использую приложение Excel online (от  создателей, так сказать ;-)
он  устанавливается в chrome как приложение. при запуске просит ввести учетную запись майкрософта и открывает в отдельной вкладке знакомый все интерфейс офиса 365. все функции естественно там есть.
хром устанавливается и без учетки администратора на корпоративный комп ;-)
12 августа 2016 10:02
Алексей, спасибо большое за участие и советы! Chrome на корпоративном компе у меня стоит с самого начала работы (нравится за его расширения и скорость работы на некоторых сайтах).

В онлайне Exсel работает не очень быстро :( плюс фишек PLEX нет, а они мне зачастую очень нужны. Это и является причиной того, что я его не использую. Плюс у нас корпоративный Office 365 с корпоративным Excel online поэтому плавализнаем :D:D:D

Николай, это я к тому, что продолжаю выпрашивать у Вас UDF ;) Подозреваю, что релиз PLEX уже на подходе так как прошло 4 месяца с момента выпуска версии 2.6
18 августа 2016 7:52
Олег, намек понял :)
30 августа 2016 8:12
Здравствуйте, я правильно понял, что в Excel 365 у меня была функция Условия, а установив Excel 2016 для дома и учебы, я не увижу ее никогда?

Будет ли реализация новых функций в PLEX? Если да, то как скоро?

Есть ли какая-нибудь форма приобретения Академической лицензии, для вузов (поставить в класс).
Или только 795р. на 5 машин?
1 февраля 2017 11:36
ЕСЛИМН
окей, к примеру файл создан в пакете 365 и открыть в офис 2016, что происходит с формулой "еслимн" ???
14 февраля 2017 9:27
У развития Excel есть обратная сторона
Скоро мы так обленимся, что не будем вникать в суть проблемы, а будем только говорить "Почему не работает" ))
Наверх