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

Опубликовано 24 мая 2015
Рубрика: Справочник Excel | 7 комментариев



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

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

Для обработки данных, хранящихся в таблицах баз данных в 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

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

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

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

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

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

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

Работа автофильтров в Excel

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

Ниже приведен внушительный перечень задач, подробно рассмотренных в главе Применение формул массивов книги «Формулы в 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. Отображение календаря в диапазоне.

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

Комментарии пишите пожалуйста в блоке  внизу страницы.

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

На главную


Введите Ваш e-mail:

Статьи с близкой тематикой

Отзывы

7 комментариев на «Формула массива в Excel»

  1. Салават 10 Июн 2015 19:54

    Спасибо, Александр! Очень полезные и познавательные статьи.

  2. Роман 30 Июл 2015 13:16

    Здравствуйте, Александр! Статья интересная, но у меня просьба можете предоставить таблицы в формате Excel, так как не совсем рационально перепечатывать таблицу с сайта для одного примера.

    С уважением, Роман.

  3. Александр Воробьев 30 Июл 2015 21:30

    Добрый день, Роман. Не понял какие таблицы Вы просите? С примером приведенном в статье? Но в нем ничего нет, кроме двух формул в ячейках B4 и B5, которые есть в тексте статьи и видны на скриншоте...

  4. Роман 31 Июл 2015 15:19

    Спасибо за ответ. Я просто привык, что в Ваших статьях в конце указан файл с примером и не прочитал полностью статью до того, как написал Вам.

  5. Ямиль 15 Окт 2015 13:33

    Здравствуйте, уважаемый Александр!В поисках ответа попал на Ваш блог. Не жалею! Но я пишу с рабочей почты и, если Вы не против, хотел бы пообщаться с Вами по домашней почте, указанной в строке «e-mail».

    В случае Вашего согласия жду ответа на своём домашнем компьютере. Спасибо! До встречи! С уважением, Ямиль.

  6. Елена 11 Фев 2016 20:31

    Спасибо за статью! Осваиваю exel)))Наверное этот процесс бесконечный))))))))

  7. Александр Воробьев 11 Фев 2016 21:01

    Пожалуйста. Наверное, да...

    Только я не понял — зачем Вы вставили ссылку на бесплатный аудио курс Саши Борисова?

Ваш отзыв







  • Посетители: 656 202

  • Подписчики: 3 414