Формула массива в Excel

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

...более гибко и квалифицированно реагировать на нюансы ситуаций и выбирать из имеющегося арсенала знаний наиболее эффективное в каждом конкретном случае.

Для обработки данных, хранящихся в таблицах баз данных в Excel, наряду с фильтрами и сводными таблицами есть малоизвестный широкому кругу пользователей инструментарий – формулы массивов.

Массив в Excel – это однородные данные, размещенные в нескольких ячейках столбца или строки или области. Данные, размещенные в ячейках одной строки или одного столбца – это одномерный массив. Данные, размещенные в нескольких строках и столбцах, то есть в таблице – это двумерный массив.

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

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

Формула массива вводится в ячейку после знака =, как и обычная формула, но если окончание ввода простой формулы подтверждается нажатием на клавишу Enter, то для окончания ввода формулы массива необходимо последовательно нажать на три клавиши – Ctrl+Shift+Enter. (Клавишу + нажимать не нужно, следует удерживать нажатыми предыдущие клавиши до момента нажатия на последнюю кнопку!)

Формула массива примет вид: {=ФОРМУЛА}.

Не стоит пробовать ввести фигурные скобки вручную напрямую с помощью клавиатуры!!! Для того чтобы программа Excel «поняла», что вы хотите ввести формулу массива нужно нажать на три вышеперечисленные клавиши!

Аргументами функций ФОРМУЛЫ могут быть как одиночные ячейки, так и диапазоны ячеек – все, как и в обычных формулах.

Формула массива может возвращать результат не только в одну ячейку, но и в группу ячеек. Для этого перед вводом формулы нужно выделить массив ячеек предназначенных для отображения результатов и затем ввести формулу.

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

Ряд важных функций в Excel работают только с массивами и выводят результаты в массив. Это функции ЛИНЕЙН() ЛГРФПРИБЛ(), ТЕНДЕНЦИЯ() и другие.

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

Пример использования формул массивов для выборочного суммирования.

Требуется выборочно быстро найти и просуммировать некоторые значения из большой таблицы  MS Excel.

Например, из обширной базы поставок изделий необходимо извлечь информацию о количестве и стоимости поставленных компании ООО «Оберон» изделий «Блок-СМ15».

Для решения задачи следует выполнить ниже перечисленную последовательность действий.

1. Скопировать из таблицы «База поставок изделий» название фирмы-заказчика и вставить, например,

в ячейку B2: ООО «Оберон»

2. Скопировать название изделия и вставить

в ячейку B3: Блок-СМ15

3. Ввести формулу массива для подсчета количества изделий

в ячейку B4: {=СУММ((A9:A27=B2)*(B9:B27=B3)*C9:C27)}=12

4. Ввести формулу массива для подсчета общей стоимости поставленных изделий

в ячейку B5: {=СУММ((A9:A27=B2)*(B9:B27=B3)*E9:E27)}=420 000,00

Как работают формулы?

Формула в ячейке B4 суммирует построчно произведения: Σ(a_i*b_i*c_i).

Формула в ячейке B5 суммирует построчно произведения: Σ(a_i*b_i*e_i).

Если в строке диапазона A9:A27 Excel находит значение равное значению в ячейке B2, то a_i=1. Если находит значение не равное значению в ячейке B2, то a_i=0. Аналогично присваиваются значения b_i. Значения c_i и e_i просто берутся построчно из ячеек указанных диапазонов.

Поставленную задачу можно решить и другим способом: при помощи объявления таблицы «База поставок изделий» списком, последовательного применения автофильтров к столбцам A и B и добавлением строки итогов.

Спектр задач, решаемых при помощи формул массивов.

Ниже приведен внушительный перечень задач, подробно рассмотренных в главе Применение формул массивов книги «Формулы в Excel 2013» Джона Уокенбаха. Изучив эти примеры и начав их использование на практике, можно серьезно повысить эффективность своей работы в Excel.

1. Суммирование в диапазоне, содержащем ошибки.

2. Подсчет количества ошибок в диапазоне.

3. Суммирование n наибольших значений в диапазоне.

4. Вычисление среднего без учета нулевых значений.

5. Поиск значения в диапазоне.

6. Подсчет отличающихся значений в двух диапазонах.

7. Местоположение максимального значения диапазона.

8. Поиск номера строки, в которой находится n-е значение, совпадающее с заданным.

9. Получение самого длинного текста в диапазоне.

10. Определение допустимых значений диапазона.

11. Вычисление суммы цифр числа.

12. Суммирование округленных значений.

13. Суммирование каждого n-го значения в массиве.

14. Удаление нечисловых символов из текстовой строки.

15. Поиск ближайшего значения в диапазоне.

16. Получение последнего значения в столбце.

17. Получение последнего значения в строке.

18. Извлечение положительных значений из диапазона.

19. Извлечение непустых ячеек из диапазона.

20. Изменение порядка следования элементов в диапазоне на противоположный.

21. Динамическая сортировка значений диапазона.

22. Возвращение списка уникальных значений диапазона.

23. Отображение календаря в диапазоне.

Другие статьи автора блога

На главную