Глава 23. Можете ли вы понять, как работает огромная формулу массива?

Рубрика: 7. Полезняшки Excel

Это заключительная глава книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление

Что, если перед вами огромная формула массива, и вы не можете понять, как она работает? Вспомним подходы, изложенные в предыдущих разделах.

Разбейте формулу на минимально возможные фрагменты и каждый фрагмент поместите в отдельную ячейку. Это особенно актуально, когда фрагмент формулы изменяется, при копировании формулы вдоль столбца (строки).

Рис. 23.0. Выделение фрагмента формулы, соответствующего аргументу

Рис. 23.0. Выделение фрагмента формулы, соответствующего аргументу номер_строки

Читать полностью

Глава 22. Функция массива ЛИНЕЙН

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику (подробнее см. справку MS Excel).

Рис. 22.1. Четыре аргумента функции ЛИНЕЙН

Рис. 22.1. Четыре аргумента функции ЛИНЕЙН

Читать полностью

Глава 21. Таблицы данных

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

В этой главе рассматривается таблица данных, которая создает массив, как результат работы функции Таблица (эту функцию нельзя ввести в ячейки вручную). Таблица данных – это быстрый и простой способ выполнить анализ «что если» для сложных взаимосвязанных вычислений на основе формул. Эта функция позволяет изменять одну или две формулы входов и отображение того, что результаты. В главах 4, 5 и 10 уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.

Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2

Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2

Читать полностью

Глава 20. Условное форматирование и формулы массива

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Несколько важных фактов об условном форматировании на основе формул:

  • Проверка условия возвращает значения ИСТИНА/ЛОЖЬ и применяется к диапазону ячеек.
  • Условие проверяется для каждой ячейки в диапазоне. Если условие выполнено, ячейка получает форматирование. Если условие не выполнено, ячейка не форматируется.
  • Для создания условия подходит любая формула, возвращающая значения ИСТИНА/ЛОЖЬ (если формула возвращает число, то любое число, кроме нуля приравнивается к значению ИСТИНА, а значение ноль – ЛОЖЬ).
  • Условное форматирование можно использовать, как с обычными формулами, так и с формулами массива.
  • Условное форматирование является волатильным (летучим): оно пересчитывается довольно часто, даже, когда вы напрямую не задействуете диапазон, в отношении которого применено условное форматирование (см. главу 13). Это замедляет работу.

Читать полностью

Глава 19. Формулы сортировки и извлечения уникальных списков

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Эта заметка для тех, кого по-настоящему интересуют сложных формулы массива. Если вам просто нужно один раз извлечь список уникальных значений, гораздо проще использовать Расширенный фильтр или сводную таблицу. Основные преимущества использования формул – автоматическое обновление при изменении/добавлении исходных данных или критериев отбора. Перед прочтением желательно освежить в памяти идеи, содержащиеся в предыдущих материалах:

Рис. 19.1. Извлечение уникальных записей с помощью опции Расширенный фильтр

Рис. 19.1. Извлечение уникальных записей с помощью опции Расширенный фильтр

Читать полностью

Глава 18. Функция массива МУМНОЖ

Рубрика: 7. Полезняшки Excel

Функция МУМНОЖ используется в Excel для перемножения матриц. Матричная алгебра?! Я напрочь забыл университетский курс матричной алгебры! Хотя это верно для большинства из вас, рекомендую вспомнить, как умножать матрицы. Зачем? Умножение матриц имеет некоторые весьма практичные применения. В этой главе вы рассмотрите проблему вычисления суммарной стоимости, сначала решите ее методом «длинной руки», а затем используете функцию МУМНОЖ для выполнения той же задачи легче и быстрее.

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Рис. 18.1. Стоимость оборудования для мужской команды

Рис. 18.1. Стоимость оборудования для мужской команды

Читать полностью

Глава 17. Формулы счета уникальных значений и еще раз о функции ЧАСТОТА

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

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

Рис. 17.1. Подсчет уникальных счетов-фактур

Рис. 17.1. Подсчет уникальных счетов-фактур

Читать полностью

Глава 16. Функция массива ЧАСТОТА

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Знакомство с функциями массива началось в главе 9. Мы узнали о функциях: ТРАНСП, МОДА.НСК и ТЕНДЕНЦИЯ. Настоящая заметка знакомит с четвертой функцией массива – ЧАСТОТА. Эта функция очень простая, но весьма мощная и универсальная. Она находит массу применений. Основная задача функции ЧАСТОТА – подсчитать, сколько чисел попадают в диапазон (рис. 16.1).

Рис. 16.1. Функция ЧАСТОТА подсчитывает, сколько результатов попали в тот или иной диапазон

Рис. 16.1. Функция ЧАСТОТА подсчитывает, сколько результатов попали в тот или иной диапазон; диапазоны в D5:D10 не являются частью формулы; они показаны для иллюстрации

Читать полностью

Глава 15. Извлечение данных на основе критериев

Рубрика: 7. Полезняшки Excel

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel. [1]

Предыдущая глава                          Оглавление                               Следующая глава

Извлечение данных (записей) из таблицы, списка или базы данных

Извлечение данных из набора данных на основе критериев И, ИЛИ – типичная задача для Excel. Вы можете решить ее, используя один из следующих методов:

  • Фильтр
  • Расширенный фильтр
  • Обычная формула в специально добавленном столбце
  • Формула массива на основе функций НАИМЕНЬШИЙ и ИНДЕКС
  • Формулу массива на основе функций АГРЕГАТ и ИНДЕКС

Рис. 15.1. Аккуратный набор данных

Рис. 15.1. Аккуратный набор данных

Читать полностью

Формат на основе содержимого ячейки

Рубрика: 7. Полезняшки Excel

В апреле 2014 г. я написал заметку Пользовательский формат числа в Excel. (Кстати, она довольно популярна – более 50 посетителей в день.) Несколько неожиданным для меня было большое число вопросов о возможности изменения формата в зависимости от содержимого ячейки. И я решил описать эту ситуацию подробнее.

Напомню, что числовые форматы могут иметь до четырех разделов кода, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных, нулевых значений и текста. При этом использование формул внутри формата не предусмотрено. Нельзя, например, вставить функцию ЕСЛИ в код формата, и попытаться применить разные форматы в случае различного содержимого ячеек. Однако…

…для этих целей можно применить условное форматирование на основе формул.

Рис. 1. Условное форматирование на основе формул

Рис. 1. Условное форматирование на основе формул

Читать полностью