Глава 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. Условное форматирование на основе формул

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

Использование функции VBA Evaluate вместо цикла

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: необходимо изменить все ячейки диапазона на основе расчета. Вы планируете в коде VBA запустить цикл и перебрать все ячейки:

For Each cell In Selection
    cell.Value = -1 * cell.Value
Next cell

Решение: функция Evaluate позволяет выполнить эту работу быстрее цикла. Замените приведенный выше фрагмент кода одной строкой:

Selection.Value = Evaluate(Selection.Address & "*-1")

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

Как узнать путь к папке TEMP

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: я хочу найти путь к папке TEMP в VBA. Решение: чтобы на вашем компьютере найти путь к папке TEMP, используйте код VBA:

TempPath = Environ("Temp")

Функция Environ помимо обнаружения пути к папке TEMP, дает большое количество информации о системе. Однако, вы должны быть аккуратны, потому что переменные функции, присутствующие на одном компьютере, могут отсутствовать на другом. Чтобы узнать, какие переменные доступны в вашей системе, используйте следующий код:

Sub ListEnvironVariables()
    For i = 1 To 99
        Cells(i, 1).Value = Environ(i)
    Next i
End Sub

Когда вы запустите этот код, вы получите список доступных переменных (рис. 1). Каждая строка здесь содержит имя переменной, знак равенства и значение переменной для данного компьютера.

Рис. 1. Значение переменных функции Environ

Рис. 1. Значение переменных функции Environ

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

Использование таймера для сравнения производительности формул

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: два гуру Excel заходят в бар. Один из них говорит, что быстрее работает формула =МАКС(0;МИН(A1;B1)), а другой – что =МЕДИАНА(0;A1;B1). Кто из них прав?

Решение: вы можете найти ответ путем использования макроса VBA для вычисления каждой формулы 50 000 раз. Прежде чем стартует макрос, вы сохраняете значение таймера в переменную. Когда 50 000 расчетов завершится, можно сравнить исходное и конечное значения таймера.

На ПК под управлением Windows, таймер показывает количество секунд и долей секунды, прошедших с полуночи. На Mac, функция возвращает только целые секунды, так что процесс вычислений нужно повторить достаточное количество раз, чтобы увидеть разницу в секундах. Также, убедитесь, что во время работы макроса не начался новый день (вы не перешли через полночь).

Рис. 1. МЕДИАНА работает немного медленнее, чем связка МАКС(МИН())

Рис. 1. МАКС(МИН()) работает немного медленнее, чем МЕДИАНА()

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

Нахождение наиболее близкого значения

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: люди вводят данные различными способами. Если вы попросите 50 торговых представителей записать прогноз для Дженерал Моторс, вы обнаружите дюжину различных способов написания названия компании или ее аббревиатуры. Объединить прогнозы от всех торговых представителей будет непросто. В столбце А (рис. 1) показаны некоторые способы написания имен клиентов, чьи официальные названия приведены в столбце D.

Рис. 1. Когда предложено ввести имена клиентов, сотрудники воспользуются разными написаниями или сокращениями

Рис. 1. Когда предложено ввести имена клиентов, сотрудники воспользуются разными написаниями или сокращениями

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

Извлечение адреса электронной почты из ячейки, содержащей другой текст

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: у вас есть ячейки, содержащие адреса электронной почты, а также другой текст. Вам нужно извлечь адреса электронной почты.

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

Макрос

В основе макроса лежит функция VBA Split. Допустим, что ячейка содержит текст Write to lora@mrexcel.com to book a seminar. Если передать этот текст в функцию Split и указать, что текст следует разбить на элементы, разделенные пробелом – x = Split(cell.Value, " ") – VBA вернет массив, где каждое слово будет элементом массива. На рис. 1 показан массив x после использования функция Split.

Рис. 1. Функция Split возвращает массив, где каждое слово будет элементом массива

Рис. 1. Функция Split возвращает массив, где каждое слово будет элементом массива

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