Формула массива в Excel
Опубликовано 24 мая 2015
Рубрика: Справочник Excel | 8 комментариев
Для решения любой задачи почти всегда есть несколько способов или подходов. Большинство из нас будет использовать чаще всего на практике самый простой, быстрый и понятный метод. Однако знание различных вариантов решения одной и той же задачи позволяет специалисту...
...более гибко и квалифицированно реагировать на нюансы ситуаций и выбирать из имеющегося арсенала знаний наиболее эффективное в каждом конкретном случае.
Для обработки данных, хранящихся в таблицах баз данных в 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 суммирует построчно произведения: Σ(ai*bi*ci).
Формула в ячейке B5 суммирует построчно произведения: Σ(ai*bi*ei).
Если в строке диапазона A9:A27 Excel находит значение равное значению в ячейке B2, то ai=1. Если находит значение не равное значению в ячейке B2, то ai=0. Аналогично присваиваются значения bi. Значения ci и ei просто берутся построчно из ячеек указанных диапазонов.
Поставленную задачу можно решить и другим способом: при помощи объявления таблицы «База поставок изделий» списком, последовательного применения автофильтров к столбцам 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. Отображение календаря в диапазоне.
Статьи с близкой тематикой
Отзывы
8 комментариев на «Формула массива в Excel»
Ваш отзыв
Посетители: 2,1 млн
Спасибо, Александр! Очень полезные и познавательные статьи.
Здравствуйте, Александр! Статья интересная, но у меня просьба можете предоставить таблицы в формате Excel, так как не совсем рационально перепечатывать таблицу с сайта для одного примера.
С уважением, Роман.
Добрый день, Роман. Не понял какие таблицы Вы просите? С примером приведенном в статье? Но в нем ничего нет, кроме двух формул в ячейках B4 и B5, которые есть в тексте статьи и видны на скриншоте...
Спасибо за ответ. Я просто привык, что в Ваших статьях в конце указан файл с примером и не прочитал полностью статью до того, как написал Вам.
Здравствуйте, уважаемый Александр!В поисках ответа попал на Ваш блог. Не жалею! Но я пишу с рабочей почты и, если Вы не против, хотел бы пообщаться с Вами по домашней почте, указанной в строке «e-mail».
В случае Вашего согласия жду ответа на своём домашнем компьютере. Спасибо! До встречи! С уважением, Ямиль.
Спасибо за статью! Осваиваю exel)))Наверное этот процесс бесконечный))))))))
Пожалуйста. Наверное, да...
Только я не понял — зачем Вы вставили ссылку на бесплатный аудио курс Саши Борисова?
Спасибо за материал! Очень внятное объяснение на конкретном примере. Занятная штука, этот массив, как оказалось.