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

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

Алексей Васильев. Числовые расчеты в 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 не позволяет определять новые функции. Ситуация изменилась с появлением в 2019 г. динамических массивов. С их помощью обычные формулы обрабатывают диапазон ячеек, а результаты также разливаются в диапазон ячеек. В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. В течение 2021 г. постепенно у всех пользователей Excel после плановых обновлений Office 365 появилась функция LAMBDA.

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

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

Заключительный проект в Power Query: объединяем все вместе

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

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

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

Отчет о доходах компании Wide World Importers

Вы – специалист по данным в компании Wide World Importers. После атаки хакеров вас попросили восстановить отчет о доходах компании на основе старых данных. Ваш предшественник вел отчеты в трех файлах Excel за 2015-2017 годы. В каждом из них содержалось 12 листов – по одному на каждый месяц. А выручка 2018 года была экспортирована в формате csv. Нужно импортировать данные с 36 листов, объединить их с данными 2018 года и создать единый отчет.

Загрузите папку C14E01 и целевую таблицу в файле C14E01 — Goal.xlsx. Изучите файлы 2015.xlsx, 2016.xlsx и 2017.xlsx. Каждый лист содержит сводную таблицу формата 2*3:

Рис. 1. Исходные данные за 2015–2017 гг. в файлах Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Заключительный проект в Power Query: объединяем все вместе

Основы текстовой аналитики в Power Query

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

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

Текстовые столбцы позволяют извлекать дополнительный контекст. Описанные ниже методы полезны при анализе отзывов клиентов, комментариев или социальных сетей.

Поиск ключевых слов в текстовых столбцах

В следующем упражнении мы будем искать ключевые слова в сообщениях, которые Microsoft Press использует в постах на своей официальной странице в Facebook. Допустим, вам нужно оценить, какие из тем наиболее популярны в обсуждениях на Facebook: Microsoft Excel, Visual Studio, Azure, Windows.

Загрузите файл C11E01.xlsx и сохранить его в папке C:\Data\C11\. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C11E01.xslx и щелкните Импорт. В окне Навигатор выберите Sheet1 и щелкните Преобразовать данные. Обратите внимание, что посты Microsoft Press Facebook сохраняются в столбце Message. Ваша задача – выполнить поиск ключевых слов в этом столбце.

Рис. 1. Сообщения Microsoft Press на Facebook в редакторе Power Query; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Основы текстовой аналитики в Power Query

От ловушек к робастным запросам в Power Query

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

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

10 ловушек, рассмотренные в этой главе, подробно обсуждаются в блоге автора книги.

Причины и следствия ловушек

В основе редактора Power Query лежат два основополагающих принципа:

  • Пользовательский интерфейс переводит шаги в код.
  • Предварительный просмотр данных помогает формировать логику преобразования.

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

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

Подробнее »От ловушек к робастным запросам в Power Query

Введение в язык формул M Power Query

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

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

Возможности языка M реализуются с помощью трех пользовательских интерфейсов:

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

Даже если вы не до конца разобрались с синтаксисом или с кодом, тот факт, что код генерится с помощью пользовательского интерфейса, помогает понять код. Лучший способ изучить возможности языка M — применить шаги преобразования в пользовательском интерфейсе, а затем постепенно пытаться освоить базовый код.

Можно выделить шесть этапов в изучении языка M. Каждый этап позволяет решать новые типы проблем с данными, но при этом он будет удерживать вас в определенной «зоне комфорта». Задайте себе вопрос: «Готовы ли вы приложить значительные усилия, чтобы перейти к следующему этапу?»

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

Подробнее »Введение в язык формул M Power Query