Еще в начале этого года на глаза попался интересный пример дашборда, подготовленного в Tableau. Ради интереса решил некоторые приёмы и оформление воспроизвести в Excel и сделал файл без «стероидов» – надстроек Power Query & Pivot. Ну и заодно чтобы проверить, получится ли воспроизвести функционал из оригинальной версии.
Оригинал в Tableau, советую обязательно посмотреть:
В итоге получился файл Excel, в котором используются сводные таблицы, срезы и многое другое. А также самые новые подходы и формулы, которые добавлены, чтобы вам было интереснее.
Отдельно хочу обратить внимание на формулы динамических массивов. Этот функционал разработчики относительно недавно добавили в Excel 365 и 2021 (у новизны есть побочный эффект: если файл не будет работать и выдаст ошибку, например, ИМЯ, вам придется обновить версию Excel). Но, как показывает опыт, у многих аналитиков версия Excel всегда самая свежая. Поэтому скачивайте, смотрите и разбирайте формулы 🙂
Если тема про дашборды в Excel вам интересна, смотрите статьи:
Динамические массивы
Относительно недавно разработчики добавили в Excel новый функционал – формулы и ссылки для динамических массивов. И заодно создали новую концепцию, которая, как нам кажется, повлияет на многие будущие подходы к построению таблиц и работе с ними. Чтобы объяснить, как работают динамические массивы, начнём с формул, которые их создают. Например, с формулы УНИК.
Формула УНИК извлекает уникальные значения из диапазона.
Формула вводится в одну ячейку и создает столбец, ячейки которого рассчитываются автоматически.
У созданного динамического spill-массива* нет фиксированного размера – столбец автоматически меняется при добавлении новых значений в таблицу с исходными данными.
Динамические массивы подсвечиваются синей рамкой при их выделении мышкой.
* от spill — проливаться
Ссылки для динамических массивов
Кто говорил, что типы ссылок в Excel – абсолютные и относительные – тема для новичков и все её знают? Разбираем новый функционал ссылок для динамических массивов. Заодно можно поностальгировать и вспомнить, когда вы впервые познакомились с темой про типы ссылок в Excel =)
Если в файле есть динамический массив или диапазон в фигурных скобках { }, то на него можно сделать ссылку.
В конце такой ссылки должен быть значок # – решетка.
В целом, у ссылок на динамические массивы логика очень похожа на ссылки диапазонов, которые записываются в фигурных скобках { } с нажатием Ctrl+Shift+Enter. Но, в отличие от диапазонов, ссылку на массив записывают только в одну ячейку, а ещё массивы умеют сами автоматически «протягиваться».
Диапазон
{ = A1:A3 }
Динамический массив
= A1#
Динамические массивы и обычные формулы
Ссылки на динамические массивы можно применять так же, как обычные, добавлять значки $. И применять в вычислениях с «обычными» формулами. Ниже несколько примеров применения таких ссылок с формулами ранжирования, транспонирования, подсчета значений. Проверка вводимых значений тоже становится проще и быстрее.
Ранжирование
= РАНГ.РВ( A2#; A2# )
Подсчёт количества непустых ячеек
= СЧЁТЗ( A2# )
Транспонирование
=ТРАНСП( A1# )
Выпадающие списки
в меню Данные → Проверка данных
Формулы для динамических массивов
На сегодняшний день в Excel существует восемь формул для динамических массивов. Первоначально в этот список не входили ПРОСМОТРX и ПОИСКПОЗX. Формула Let также «заточена» для работы с массивами. И ещё там можно использовать оператор неявного пересечения @ (обязательно почитайте, что это такое). При обращении к массивам может появиться новый вид ошибки — #ПЕРЕНОС! или #SPILL!
| Формула | Для чего нужна |
| УНИК | Извлекает список уникальных значений |
| ФИЛЬТР | Фильтрация по заданным условиям |
| СОРТ | Сортировка по столбцу (строке) |
| СОРТПО | Сортирует по данным в другом массиве |
| СЛМАССИВ | Генерация массива случайных чисел |
| ПОСЛЕД | Генерация массива последовательных чисел |
| ПРОСМОТРX | Современный аналог ВПР |
| ПОИСКПОЗX | Современный аналог ПОИСКПОЗ |
Похоже, что в будущем динамические массивы будут всё чаще применяться в Excel. Нам с коллегами они показались классными – формула вводится только в одну ячейку, ничего не надо «протягивать», потому что массив обновляется сам и даже кнопку «Обновить» не надо нажимать.
