Новые возможности Excel 2016

Очень скоро нас с вами ждет очередная версия Excel 2016. На данный момент для всех желающих уже доступна бесплатная Technical Preview версия Office 2016 для ознакомления. Давайте посмотрим, что же нового и вкусного нам готовят в Редмонде.

Общий вид

excel-2016-2.png

Как видно из предыдущего скриншота, общий вид интерфейса не сильно изменился. Подложка ленты стала зеленой, а сама лента - серой и это, на мой взгляд, хорошо - активную вкладку видно более отчетливо и лента не сливается с листом, как это было в прошлом Excel. Названия вкладок попрощались с ПРОПИСНЫМИ - мелочь, а приятно.

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

excel-2016-3.png

... и угольно-черный:

excel-2016-4.png

Небогато для программы, на которую миллиард пользователей по всему миру пялятся по 5-10 часов в сутки иногда. По дизайну еще есть куда расти - это факт. (Прим. автора: я один устал от этого плоского безликого flat-design везде и кругом?)

Помощник

В правом верхнем углу экрана появилось поле Помощника. Это, своего рода, реинкарнация знаменитой Скрепки - быстрая встроенная поисковая система по всем функциям и инструментам Excel. В это поле можно начать вводить название команды или функции и Помощник оперативно выдаст список подсказок, которыми можно воспользоваться:

assistant.gif

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

Надеюсь, что в будущем эта штука будет не просто искать в справке, а поддерживать голосовой ввод и понимать русскоязычную морфологию - тогда можно будет просто сказать Excel'ю что вы хотите сделать: "Сделай квартальный отчет по регионам и отправь его шефу!"

Новые типы диаграмм

Последний раз Microsoft добавляла новые типы диаграмм в Excel аж в 1997 году - почти 20 лет назад! И вот, наконец, лед в этом вопросе тронулся (не без дружеских пенделей разработчикам от участников сообщества MVP, скажу по секрету). В Excel 2016 появилось сразу аж 6 принципиально новых типов диаграмм, большинство из которых в старых версиях можно было построить только с помощью специальных надстроек или танцев с бубном. Теперь же все делается в два движения. Итак, встречайте:

Водопад (Waterfall Chart)

excel-2016-5.png

Другие названия: мост (bridge), "ступеньки", каскадная диаграмма. Очень часто используемый в финансовом анализе (и не только) тип диаграммы, наглядно отображающий динамику изменения параметра во времени (поток наличности, инвестиции) либо влияние различных факторов на результат (факторный анализ цены). Раньше для построения такой диаграммы приходилось либо шаманить, либо покупать специальные надстройки.

Иерархическая (Treemap Chart)

excel-2016-6.png

Специфический тип диаграммы для наглядного отображения распределения какого-либо параметра по категориям в виде эдакого прямоугольного "лоскутного одеяла". Причем можно использовать двойной уровень вложенности категорий (города внутри страны). Удобно использовать для визуализации, например, прибыли по регионам или выручки по категориям товаров. В старых версиях построение такой диаграммы было крайне затруднительно и обычно требовало установки дополнительных надстроек.

Солнечные лучи (Sunburst Chart)

excel-2016-7.png

Аналог предыдущего типа, но с круговым размещением данных в секторах, а не в прямоугольниках. По-сути, что-то вроде круговой или кольцевой диаграммы с накоплением. Для визуализации распределения - самое оно, причем вы уже не ограничены двумя уровнями вложенности, а можете разложить их на три (категория-товар-сорт) или больше.

Парето (Pareto Chart)

excel-2016-8.png

Классическая диаграмма для визуализации "закона 80/20" или "закона Парето", о котором многие, думаю, как минимум слышали. В общем виде он формулируется как "20% усилий дают 80% результата". Применительно к бизнесу, это уточняется до "20% товаров дают 80% выручки", "20% клиентов создают 80% проблем" и т.д. В такой диаграмме наглядно отображается гистограммой суммарная выручка по каждому товару и, одновременно, оранжевый график показывает накопленную долю выручки. Там, где линия пересекает 80% (около Ананаса) и можно мысленно провести вертикальную линию, чтобы отделить ключевые товары (слева от Ананаса) от неважных (справа от Ананаса). Мегаполезная диаграмма для ABC-анализа и подобных ему вещей.

Ящик с Усами (BoxPlot Chart)

excel-2016-9.png

Другое название "диаграмма разброса" или Box-and-Whiskers Chart. Весьма часто применяемый в статистической оценке тип диаграммы, отображающий для набора данных сразу:

  • среднее арифметическое - крестообразная зарубка
  • медиану (50% квантиль) - горизонтальная линия на боксе
  • нижний (25%) и верхний (75%) квантили - нижняя и верхняя границы бокса
  • выбросы - в виде отдельных точек
  • максимальное и минимальное значение - в виде усов

Частотная гистограмма (Histogram Chart)

excel-2016-10.png

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

Power Query

Надстройка для импорта данных Power Query, ранее поставлявшаяся для Excel 2013 отдельно, теперь встроена по-умолчанию. На вкладке Данные (Data) она представлена в виде группы Скачать & Преобразовать:

excel-2016-15.png

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

После загрузки полученные данные можно средствами Power Query еще и обработать, "доводя до ума":

  • исправить числа-как-текст и даты-как-текст
  • добавить вычисляемые столбцы или убрать ненужные
  • консолидировать данные из нескольких таблиц в одну автоматически и т.д.

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

Сводные таблицы

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

excel-2016-14.png

Очень полезная штука, когда в вашей таблице десятки столбцов + вы еще и вычисляемых полей от себя добавили.

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

Инструменты прогнозирования

Excel 2016 получил несколько новых инструментов для прогнозирования. Во-первых, в категории Статистические (Statistical) появились функции для вычисления прогноза по методу экспоненциального сглаживания:

  • ПРЕДСКАЗ.ETS - выдает прогнозируемое значение на заданную дату в будущем по методу эксп.сглаживания с учетом сезонности
  • ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ - вычисляет доверительный интервал для прогноза
  • ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ - выявляет сезонность в данных и вычисляет ее период
  • ПРЕДСКАЗ.ETS.STAT - выдает подробные статистические данные по числовому ряду для вычисляемого прогноза
  • ПРЕДСКАЗ.ЛИНЕЙН - вычисляет линейный тренд

Также появился удобный инструмент для составления прогнозов "на лету" - кнопка Лист прогноза на вкладке Данные (Data):

excel-2016-11.png

Если выделить исходные данные (периоды или даты и значения) и нажать эту кнопку, то мы увидим вот такое окно:

excel-2016-12.png

Как видите, в нем можно легко задать нужные параметры прогнозирования и сразу увидеть результат в графическом представлении - очень удобно. Если нажать кнопку Создать, то появится новый лист, где будет автоматически сформирована модель прогноза с формулами:

excel-2016-13.png

Приятная штука. Раньше мы, например, на тренинге по прогнозированию делали такое вручную "от" и "до" - и занимало это очень прилично времени.

Также в этой версии несколько привычных математических и статистических функций переехало в категорию Совместимость (Compatibility), т.к. вместо них появились их более совершенные "потомки".

Итоговые выводы

Technical Preview - это не релиз и, возможно, в финальной версии мы увидим дополнительно еще какие-то изменения и улучшения. Но, судя по всему, ничего сверхестественного ждать не стоит (кто-то скажет, что это к лучшему, возможно). Microsoft целенаправленно и методично от версии к версии "полирует" уже имеющиеся функции и потихонечку добавляет новые.

Хорошо, что появились, наконец, новые типы диаграмм, которые все давно ждали, но есть еще куда расти - за кадром остались проектные диаграммы (Gantt, Timeline), диаграммы-шкалы ("термометры") и т.д. Про то, что спарклайнов давно можно было сделать не три типа, а существенно больше, как в оригинале - тоже молчу.

Приятно, что в программу по-умолчанию встраивают полезные надстройки (Power Query, Power Pivot), но можно было бы тогда уже и на Fuzzy Lookup с Power Map расщедриться. К сожалению, пока - нет.

И лично мне жаль, что мы так и не увидим, похоже, в новой версии Excel 2016 ни продвинутых инструментов для работы с диапазонами (сравнение диапазонов, например), ни улучшений в среде программирования Visual Basic (которую с 1997 года не меняли), ни новых функций типа ВПР2 или СуммыПрописью.

Надеюсь дожить до момента, когда это все в Excel появится, а пока что придется пользоваться привычными костылями.



24.06.2015 17:03:47
Спасибо, интересно.
Только один момент.

"Другие названия: мост (brige), "
Быть может, Bridge?
26.06.2015 14:45:00
Спасибо, интересный материал! А есть информация о среде разработки? VBA еще не убрали?
02.07.2015 19:12:10
Когда уберут VBA - у меня будет личный траур :)
На месте оно. Правда и улучшений никаких - с 2000 года не трогали.
Действительно. не дай Бог!
jfd
21.06.2016 09:45:48
и пусть лучше не трогают. а то будет как со скайпом
Let
27.06.2015 00:58:40
Николай, в комментариях к теме Новые возможности сводных таблиц в Excel 2013 подсказывают, что еще в Экселе 2016 решили проблему группировки в сводных таблицах, построенных на основе Объектной модели, которая была в 2013 Экселе.

Есть возможность прокомментировать здесь этот момент?
29.06.2015 10:52:31
We're sorry, the page you requested cannot be found.
The page you're looking for is either not available in your market or no longer exists.
29.06.2015 11:11:13
MS сменили ссылку - подправил, спасибо!
29.06.2015 11:03:00
Николай, спасибо за обзор. Давно и с интересом вас читаю и всем рекомендую.

Оффтоп, думаю вы наверняка уже смотрели гугловый эксель
У вас нет в планах сделать хотя-бы короткий обзор его возможностей?
30.06.2015 18:12:53
Статья очень полезная, спасибо огромное,
про Сводные таблицы  построенные на базе OLAP ничего не сказано.
точнее про изменения или их отсутствие
26.09.2015 20:44:21
А будут уроки по 2016 Екселю?
Николай,  спасибо за обзор. очень впечатлилась диаграммой "Ящик с усами" - даже не слышала о таком виде диаграмм.  А вот о чем мечтала, так это о диаграмме "Солнечные лучи". Кстати у этого типа диаграмм интересная история. Впервые именно как прикладная диаграмма она стала использоваться во время Крымской войны англичанами с 1853 по 1856 год. Ее вручную строили дамы(!)-сестры милосердия, руководившие и  оптимизировавшие  госпитальную деятельность и ресурсы.
Легенда тут не указана, но если я правильно помню от центра:
1. *белый) - количество смертей
2. "серый! - инвалиды или с тяжелыми постоперационными осложнениями
3. "голубой" - легкораненые, возвращенные в строй
Тут ссылочка на картинку:
https://onedrive.live.com/redir?resid=4D23415F0BE08C02!262&authkey=!AI_lXQU2TB3uJi8&v=3&ithint=photo%2cJPG
Представляете себе, англичане уже 150 лет назад так методично занимались сбором статистики и визуализацией для оптимизации деятельности. Неудивительно, что они действовали так эффективно в Крыму и России понадобились огромные усилия, чтобы вытурить их оттуда.
15.11.2015 01:33:41
Николай, спасибо за обзор. Давно и с интересом вас читаю и всем рекомендую.
29.11.2015 18:59:26
Почему на солнечной диаграмме не отображается подписи значений? оО При это не активно добавить подписи и соответственно нет редактируемых параметров по подписям оО Что может быть?
10.03.2016 19:51:03
Спасибо за приятный обзор!
16.09.2016 17:35:48
Здравствуйте! У  Вас замечательный сайт! Я Ваш постоянный читатель.
Очень нужно разобраться в совместном использовании
Power BI и Еxcel.
Данные подключит
ь удается а с визуализацией проблемы.
По продукту Power BI нормальной информации  практически нет.
Думаю, что не только я буду благодарна, если возьмётесь за это !

30.09.2016 10:34:01
Мила, Вы имеете в виду Microsoft Power BI или надстройки для Excel Power "BI" - (Query/Pivot/View)?
12.02.2017 17:49:58
Также в 2016 добавили несколько очень интересных функций листа (по информации с офсайта), особенно мне была интересно СЦЕПИТЬ (СЦЕП) для диапазона. Установил (конечно, спец версия), но этой функции нет. В чём дело, не подскажите?
Спасибо!
26.07.2017 09:59:35
Добрый день!
Хочу сделать график, но у меня нет опции Солнечные лучи (Sunburst Chart)
Excel for mac
23.01.2019 14:10:07
Добрый день, Николай. Когда стоит ожидать аналогичной статьи по Excel 2019? :D
Наверх