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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

в ячейку B5: =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. Вычисление среднего без учета нулевых значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Большинство пользователей компьютерных систем на основе Windows с установленным пакетом Microsoft Office непременно сталкивались с приложением MS Excel. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Большинство пользователей компьютерных систем на основе Windows с установленным пакетом Microsoft Office непременно сталкивались с приложением MS Excel. У начинающих юзеров программа вызывает некоторые сложности в освоении, тем не менее работа в «Экселе» с формулами и таблицами не так уж и сложна, как может показаться на первый взгляд.

Контрольный тест «Электронные таблицы», ФГОС

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