Перейти к содержимому

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

Написание сложных формул на языке DAX Power Pivot с использованием ChatGPT

Я увлекаюсь игрой Fantasy Premier League. Помимо спортивного азарта соперничества с 10М игроков по всему миру, игра создаёт почву для серьёзных статистических исследований. Особенно после того, как я открыл для себя мир API. Большие данные, получаемые с помощью API, я помещаю в Excel, в модель данных Power Pivot. Дальнейший анализ веду с использованием формул на языке DAX (Data Analysis Expressions). Я только осваиваю этот язык, а практические задачи требуют продвинутых навыков. На помощь приходит ChatGPT.

Я взял итоги выступления игроков в сезоне 2022/23, поместил их в модель данных и вывел на лист Excel в виде сводной таблицы. Ниже показан фрагмент таких данных:

Рис. 1. Фрагмент сводной таблицы

Подробнее »Написание сложных формул на языке DAX Power Pivot с использованием ChatGPT

Визуализация таблиц: удаляй, чтобы улучшить

Недавно опубликовал конспект книги Ольга Базалева. Мастерство визуализации данных. Как доносить идеи с помощью графиков и диаграмм. В одном из комментариев CONSTANTINE заметил, что с его точки зрения пример Ольги с визуализацией таблицы не слишком удачный:

Рис. 1. Пример оформления таблицы из книги Ольги Базалевой

Подробнее »Визуализация таблиц: удаляй, чтобы улучшить

Как запустить второй экземпляр Excel

В последнее время я извлекаю большие объемы данных с использованием Power Query (PQ). Когда запущен PQ, работать в файлах Excel нельзя. Поэтому передо мной встала задача – запустить второй экземпляр Excel.exe, чтобы пока выполняется длительная процедура в PQ я мог бы работать с другими файлами Excel. Подробнее о плюсах и минусах работы с двумя экземплярами Excel см. Что такое экземпляры Excel и почему это важно?

Любопытно, что часто встречаемая рекомендация Microsoft…

… не работает. Может быть, это связано с тем, что рекомендация дана для Excel 2013, а у меня Excel 365…

Подробнее »Как запустить второй экземпляр Excel

Ольга Базалева. Мастерство визуализации данных

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

Ольга Базалева. Мастерство визуализации данных. Как доносить идеи с помощью графиков и диаграмм. – СПб.: ООО «Диалектика», 2020. – 192 с.

Подробнее »Ольга Базалева. Мастерство визуализации данных

Алексей Васильев. Числовые расчеты в Excel

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

Алексей Васильев. Числовые расчеты в Excel. — СПб.: Издательство «Лань», 2022. — 600 с.

Подробнее »Алексей Васильев. Числовые расчеты в Excel

Интерполяционный полином Лагранжа в Excel

В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, когда я обнаружил, что книг по этой теме на русском языке буквально единицы. Ранее я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. В заметке представлены три варианта нахождения интерполяционного полинома Лагранжа: таблица на листе Excel, функция VBA, функция листа Excel на основе REDUCE и LAMBDA. В заметке использованы материалы книги Алексея Васильева Числовые расчеты в Excel. Бумажная и электронная версии книги доступны на сайте издательства.

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

Подробнее »Интерполяционный полином Лагранжа в Excel

Проверка кода на наличие русских букв с помощью функции LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. На сегодня функция доступна только для Office 365. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. Ранее я опубликовал заметку, демонстрирующую мощь LAMBDA. А недавно возникла практическая задача, для решения которой LAMBDA подошла, как нельзя лучше! Я готовил к публикации конспект книги Алексея Васильева Числовые расчеты в Excel. Код VBA, полученный с помощью программы распознавания текста, не работал. Я предполагал, что при обработке pdf файла некоторые символы были неверно распознаны. Например, где-то «с» английское, а где-то – русское. Для обычных формул Excel эта задача – не из простых. A LAMBDA справилась!

Рис. 1. Формула ЧРУС() на основе LAMBDA

Подробнее »Проверка кода на наличие русских букв с помощью функции LAMBDA

Решение алгебраических уравнений методом половинного деления в Excel

В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, когда я обнаружил, что книг по этой теме на русском языке буквально единицы. Ранее я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. Представлено четыре варианта решения алгебраических уравнений методом половинного деления в Excel: итерационная таблица на листе Excel, процедура VBA, функция VBA, пользовательская функция Excel на основе LAMBDA. В настоящей заметке использованы материалы книги Алексея Васильева Числовые расчеты в Excel. Бумажная и электронная версии книги доступны на сайте издательства.

Рис. 1. Метод половинного деления: цифрами и штрихованными линиями со стрелкой показана последовательность перемещения границ интервала поиска решения

Подробнее »Решение алгебраических уравнений методом половинного деления в Excel

Новые функции Excel, основанные на LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять пользовательские функции, написанные на языке формул Excel. Я недавно привел примеры того, как работает LAMBDA. Но Microsoft на этом не остановился, и в июле 2021 г. объявил о создании новых функций, основанных на LAMBDA:

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

REDUCE: реализует цикл обработки элементов массива, применяя функцию LAMBDA к каждому значению и возвращает одно накопленное значение.

SCAN: реализует цикл обработки элементов массива, применяя функцию LAMBDA к каждому значению и возвращает массив того же размера, содержащий все промежуточные значения.

MAKEARRAY: возвращает массив с заданным числом строк и столбцов, применяя функцию LAMBDA для вычисления элементов массива.

BYCOL: возвращает массив в виде строки, один результат для каждого столбца массива.

BYROW: возвращает массив в виде столбца, один результат для каждой строки массива.

ISOMITTED: проверяет, отсутствует ли значение, и возвращает ИСТИНА, в противном случае – ЛОЖЬ.

Подробнее »Новые функции Excel, основанные на LAMBDA

Лебединая песня Microsoft Excel – функция LAMBDA

Формулы Excel используют на порядок больше пользователей, чем все программисты C, C++, C#, Java и Python в мире вместе взятые. Несмотря на свой успех, Excel как язык программирования, имеет фундаментальные недостатки: (1) формулы Excel поддерживают только скалярные значения – числа, строки и логические значения; (2) Excel (без кода VBA) не позволяет определять новые функции. Ситуация изменилась с появлением в 2019 г. динамических массивов. С их помощью обычные формулы обрабатывают диапазон ячеек, а результаты также разливаются в диапазон ячеек. В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. В течение 2021 г. постепенно у всех пользователей Excel после плановых обновлений Office 365 появилась функция LAMBDA.

Рис. 1. Механизм создания пользовательских функций Excel без кода VBA

Подробнее »Лебединая песня Microsoft Excel – функция LAMBDA