7. Полезняшки Excel

Разделение данных на таблицы поиска и фактов в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

«Правильная» подготовка данных служит ключом к успеху их анализа. Если данные представлены в единой таблице, лучше разделить их на таблицу транзакций и одну или несколько справочных таблиц. Подробнее о пользе нескольких таблиц в модели данных см. Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel.

Рис. 1. Сохранить в таблице несколько столбцов

Подробнее »Разделение данных на таблицы поиска и фактов в Power Query

Работа с датами в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел

Начнем с преобразования текста в даты. При загрузке таблицы со значениями даты или даты/времени Power Query выполняет преобразование столбцов с учетом «правильного» формата.

Рис. 1. Некоторые значения дат Excel не распознал; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Работа с датами в Power Query

Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных

Ранее я перевел книгу Кен Пульс и Мигель Эскобар. Язык М для Power Query. А спустя некоторое время с удивлением обнаружил, что страничка книги самая посещаемая среди опубликованных за последние 5 лет. Механизм Power Query для Excel относительно новый, но весьма необычный. Это не чистая работа с данными в Excel, а инструмент импорта внешних данных и их предварительной обработки. Я постоянно извлекаю данные из Интернета, поэтому использую Power Query довольно часто. Гил Равив описывает многое из того, что есть у Кена Пульса, поэтому здесь я не повторяюсь. Больше внимания уделяю новым аспектам: языку М, анализу текстов и извлечению знания из текста. Книга содержим массу практически примеров, и будет очень полезна в освоении Power Query.

Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных. – СПб.: БХВ-Петербург, 2021. – 480 с.

Подробнее »Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных

Автоматизация создания панельных диаграмм в Excel

Панельные диаграммы идеально подходят для раздельного отображения сразу нескольких рядов данных. Ниже показан линейный график, явно перегруженный линиями, а справа – его аналог в виде панельной диаграммы, на которой ряды данных располагаются отдельно.

Рис. 1. Линейный график и панельная диаграмма; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Автоматизация создания панельных диаграмм в Excel

Инфографика на основе вафельных диаграмм

В зависимости от того, какую идею вы хотите визуально представить, используются различные типы диаграмм. Для демонстрации вклада частей в целое наиболее часто используется круговая диаграмма. Той же цели служит и вафельная диаграмма. Поскольку стандартом де факто визуализации данных является Excel, вафельные диаграммы встречаются редко. Они не могут быть построены путем выбора типа диаграммы. А жаль…

Вафельная диаграмма состоит из прямоугольных блоков, объединенных в сетку (рис. 1). Точки данных окрашиваются разными цветами. Обычно вафельные диаграммы используют для демонстрации вклада одной точки данных по отношению к целому. Поэтому, чтобы проиллюстрировать двухмерные данные следует использовать набор вафельных диаграмм.

Рис. 1. Вафельная диаграмма

Подробнее »Инфографика на основе вафельных диаграмм

Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel

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

Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel. – М: ДМК ПРЕСС, 2022. – 340 с.

Подробнее »Дик Куслейка. Визуализация данных при помощи дашбордов и отчетов в Excel

Есть ли в диапазоне искомое значение?

Иногда возникает задача определить, есть ли в диапазоне некое значение. Часто задача усложняется выявлением ячейки совпадения, или количеством совпадений. Рассмотрим несколько вариантов для версии Excel 365.

Функция ИЛИ

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

Рис. 1. Функция ИЛИ возвращает ИСТИНА при совпадении хотя бы одного элемента диапазона с тестом

Подробнее »Есть ли в диапазоне искомое значение?

Таблицы подстановки в Excel: ВПР, Power Pivot и Power Query

Для тех, кто не знаком с функцией ВПР, она может показаться сложной. Но попрактиковавшись, вы увидите, насколько она полезна и проста (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). ВПР выполняет поиск по ключу в исходной таблице, и возвращает значение из таблицы подстановки. Например, в качестве исходной можно рассмотреть таблицу продаж велосипедов и аксессуаров (левая таблица на рис. 1). В ней присутствует код товара. В качестве таблицы подстановки возьмем справочник товаров, в котором по коду можно узнать артикул, размер, цвет, … Нас же интересует цена. В версии Excel 365 наряду с ВПР доступна схожая новая функция ПРОСМОТРX – еще более мощная и простая в использовании. Также в версии Excel 365 есть две отличные альтернативы доброй старой функции ВПР – модель данных в Power Pivot и объединение таблиц в Power Query.

Рис. 1. Определение суммы чека с помощью таблицы подстановки и функции ВПР; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Таблицы подстановки в Excel: ВПР, Power Pivot и Power Query

Джеффри Фридл. Регулярные выражения (в Excel)

Когда я работал в издательстве, то очень активно пользовался обработкой текста с помощью шаблонов. Тогда я использовал программу PageMaker (ныне InDesign) и язык скриптов. Позже я перенес этот опыт на обработку текста в Word и Excel, но использовал в макросах возможности, предоставляемые самими программами. Несколько лет назад я открыл для себя язык регулярных выражений. Прочитал книгу Бена Форта Регулярные выражения за 10 минут. К сожалению, доступ к регулярным выражениям открывался только через код VBA или специальные программы, что затрудняло понимание прочитанного и дальнейшее практическое использование регэкспов.

И вот совсем недавно я наткнулся на заметку Николая Павлова, в которой предлагается пользовательская функция RegExpExtract, переносящая всю работу с регулярными выражениями на листы Excel. В заметке также есть ссылка на два ресурса для проверки регулярных выражений в режиме онлайн: https://regex101.com/, https://regexr.com/. Рекомендую! Ваши шаблоны будут разобраны на элементы и показана их работа.

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

Джеффри Фридл. Регулярные выражения, 3-е издание. – СПб.: Символ-Плюс, 2008. – 608 с.

Подробнее »Джеффри Фридл. Регулярные выражения (в Excel)

Упрощение формул Excel путем именования фрагментов с помощью функции LET

Функция LET появилась в версии Office 365 (по подписке) и будет доступна, начиная с версии Office 2021. Функция LET позволяет присваивать имена фрагментам формулы, а затем использовать эти имена в вычислениях. Формулы становятся короче и лучше читаемы. Это похоже на использование имен ячеек, диапазонов и формул в диспетчере имен. Но имена, присвоенные функцией LET, действуют только в пределах указанной формулы, и нигде больше. При использовании в русском Office имя функции сохраняется (не переводится на русский язык).

Синтаксис

Рис. 1. Синтаксис функции LET

Подробнее »Упрощение формул Excel путем именования фрагментов с помощью функции LET