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

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

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

Синтаксис

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

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

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

Эта перевод книги, посвященной моделированию в 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 для ученых, инженеров и студентов

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

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

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

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

Альберто Феррари, Марко Руссо. Анализ данных при помощи Microsoft Power BI и Power Pivot для 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

Алан Мюррей. Эффективная работа в Microsoft Excel

Книга предназначена для пользователей Excel, которые хотят расширить свой арсенал. Алан Мюррей, преподаватель и консультант по Excel, открывает ряд секретов, которые выведут работу с программой на новый уровень. Вы изучите техники форматирования, улучшающие представление данных, создание динамических отчетов. Отдельная глава посвящена приемам построения профессионально выглядящих, динамически обновляемых диаграмм. Представлены инструменты Power Query и Power Pivot, расширяющие возможности Excel.

Алан Мюррей. Эффективная работа в Microsoft Excel. – М.: ДМК Пресс, 2021. – 276 с.

Подробнее »Алан Мюррей. Эффективная работа в Microsoft Excel

Функции предсказания в Excel

Excel, как универсальный табличный редактор, давно и неплохо справляется с большинством задач прогнозирования (см. список литературы в конце заметки). Однако, не всегда вычисления в Excel являются простыми и понятными. И вот в версии 2016 года разработчики Microsoft добавили семейство функций ПРЕДСКАЗ (FORECAST), которые позволяют в несколько кликов решать большой круг задач прогнозирования на основе экспоненциального сглаживания.

Рис. 1. Прогнозирование продаж в Excel с помощью семейства функций ПРЕДСКАЗ

Подробнее »Функции предсказания в Excel

Динамические массивы и функции кубов

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Недавно я обновлял 6-е издание книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel.[1] Я хотел расширить главу об инструментах OLAP, чтобы показать, как преобразовать сводную таблицу в формулы кубов. Оказывается, это довольно сложно. Первые шаги даются легко:

  1. Выделите ячейку на листе данных.
  2. Выберите Вставить –> Сводная Таблица.
  3. В диалоговом окне Создание сводной таблицы выберите Добавить эти данные в модель данных (подробнее о модели данных см. Роб Колли. Формулы DAX для Power Pivot):

Рис. 96. Использование модели данных

Подробнее »Динамические массивы и функции кубов

Обычные функции, использующие динамические массивы в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

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

Использование СЕГОДНЯ() и ПОСЛЕД() для календаря

Допустим, вы используете СЕГОДНЯ() в качестве третьего аргумента функции ПОСЛЕД(). Тогда формула =ПОСЛЕД(6;7;СЕГОДНЯ()) в ячейке А4 вернет массив из 6 строк и 7 столбцов календаря, начинающегося сегодня:

Рис. 74. Использование СЕГОДНЯ() и ПОСЛЕД() для создания динамического календаря на ближайшие шесть недель

Подробнее »Обычные функции, использующие динамические массивы в Excel

Функция ПРОСМОТРX и динамические массивы в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

ПРОСМОТРX – первая новая функция после появления динамических массивов

Функции ПРОСМОТРX и ПОИСКПОЗX дебютировали для Office 365 в августе 2019 года. Они были разработаны для объединения возможностей ВПР, ИНДЕКС, ПОИСКПОЗ и динамических массивов. Ранее я писал о двенадцати удивительных преимуществах ПРОСМОТРX.[1] Здесь расскажу лишь о двух из них. Но сначала введение для тех, кто еще не знаком с ПРОСМОТРX.

В ячейке D4 вы хотите найти категорию (Category) для элемента W25-6. До появления ПРОСМОТРX, вы могли это сделать с помощью =ВПР(A4;F6:I17;4;ЛОЖЬ) или комбинации функций =ИНДЕКС(I7:I17;ПОИСКПОЗ(A4;F7:F17;0)). С ПРОСМОТРX сделать это еще проще. Вы ищете значение ячейки A4 в диапазоне F$7:F$17, и хотите вернуть соответствующее значение из I$7:I$17. Нет необходимости в аргументе ЛОЖЬ функции ВПР или 0 функции ИНДЕКС, потому что ПРОСМОТРX по умолчанию ищет точное совпадение:

Рис. 68. Простой пример ПРОСМОТРX

Подробнее »Функция ПРОСМОТРX и динамические массивы в Excel

Почему формулы массива (Ctrl+Shift+Enter) такие сложные: неявное пересечение

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Почему формулы массива (Ctrl+Shift+Enter) были так сложны для понимания?

Если вы когда-либо вводили Ctrl+Shift+Enter, эта глава прольет свет, почему формулы массива были столь трудными в использовании в предыдущих версиях Excel. Для начала несколько вопросов о работе прежних версий Excel. Я не ожидаю, что все ваши ответы будут правильными. Попробуйте угадать. Далее по ходу главы я внесу ясность в эти вопросы.

Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?

  • 2047, сумма значений B4:B14.
  • 1, значение первой ячейки в диапазоне.
  • 16, значение в той же строке диапазона.
  • Ошибка #ЗНАЧ! синтаксис формулы неверный

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

Подробнее »Почему формулы массива (Ctrl+Shift+Enter) такие сложные: неявное пересечение