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

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

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

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

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

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

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

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

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

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

Подробнее »Глава 16. Функция массива ЧАСТОТА

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Это глава из книги Билла Джелена Гуру 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

Подробнее »Как узнать путь к папке TEMP

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

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

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

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

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

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

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

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

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

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

Это глава из книги Билла Джелена Гуру 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 возвращает массив, где каждое слово будет элементом массива

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

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

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

Рис. 1. Для доступа к коду объекта ThisWorkbook кликните на ней правой кнопкой мыши и выберите View Code

Подробнее »Создание макроса обработчика событий