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

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

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

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

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

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

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

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

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

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

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

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

Функция ИЛИ

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

Эта перевод книги, посвященной моделированию в Excel. Моделирование – в первую очередь это анализ «что, если». В его основе лежат фиксированные значения в ячейках. Более сложные модели основаны на изменяющихся значениях, распределенных нормально. И это уже моделирование по методу Монте-Карло. Моделирование находит применение в науке, финансах, статистике, логистике, … при решении головоломок. Модели в этой книге основаны на функциях и инструментах Excel (без использования VBA). Каждая глава описывает одну модель по схеме: что делает симуляция, что нужно об этом знать и что нужно сделать. Разделы посвящены отдельным областям знания, таким как статистика или генетика. Даже если это не ваша область интересов, вы всё равно получите пользу от изучения моделей. Они подарят вам массу идей для создания собственных симуляций. Кроме того, моделирование – это весело. Наслаждайтесь!

Dr. Gerard M. Verschuuren. 100 Excel Simulations. Using Excel to Model Risk, Investments, Genetics, Growth, Gambling and Monte Carlo Analysis. – USA: Holy Macro! Books, 2017.

Подробнее »Джерард Вершурен. 100 симуляций в Excel

В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, что книг по теме на русском языке буквально единицы. Поэтому я не оставил без внимания даже издание 1999 г.!

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

Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. – Киев: Юниор, 1999. – 528 с.

Подробнее »Вильям Дж. Орвис. Excel для ученых, инженеров и студентов

В книге представлена техника моделирования данных в Excel и Power BI. Авторы покажут, почему модель данных необходимо строить на основе нескольких таблиц, научат производить расчеты с календарем, расскажут об использовании снимков для подсчета количества товаров в наличии, о том, как работать с несколькими валютами одновременно, и подробно объяснят на примерах многие другие полезные операции. Если вы не знакомы с Power Pivot и формулами DAX, рекомендую начать с Марк Мур. Power Pivot или Роб Колли. Формулы DAX для Power Pivot.

Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel. – М.: ДМК Пресс, 2020. – 288 с.

Подробнее »Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel