Как сделать массив в excel

Обновлено: 07.07.2024

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

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

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

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

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

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

Формула массива примет вид: .

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

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

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

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

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

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

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

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

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

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 просто берутся построчно из ячеек указанных диапазонов.

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

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. Отображение календаря в диапазоне.

Массив - это набор данных одного типа. Массив в MS Excel может храниться в диапазоне смежных ячеек. MS Excel позволяет создавать одномерные, двумерные и трехмерные массивы, которые хранятся, соответственно в одномерных, двумерных и трехмерных диапазонах. Одномерный и двумерный диапазоны создаются на одном рабочем листе. Адресная ссылка на такой диапазон имеет формат: Адрес_первой_ячейки> : . Трехмерные диапазоны создаются в одноименных ячейках нескольких смежных рабочих листов. Адресная ссылка на такой диапазон имеет формат:

Если массив содержит данные арифметического типа, то с таким массивом можно выполнять арифметические операции такие, как:

• операции, в которых в качестве операндов участвуют массив и единственная переменная, например, умножение элементов массива на число;

• операции, в которых в качестве операндов участвует двумерный массив и одномерный массив, например, почленно - построчное умножение;

• операции, в которых участвуют массивы одинаковой размерности.

Для умножения (деления, сложения или вычисления функции) каждого элемента массива на число следует выполнить следующие действия:

1. Ввести значения элементов массива в ячейки рабочего листа.

2. Выделить область для размещения результата операции, ее размерность должна быть такой же, как размерность исходного массива.

4. Указать, что производится операция над массивом, нажав комбинацию клавиш + + .

Пример 1. Требуется умножить элементы двумерного массива 2 х 2 на число 3.

5. Введем в ячейки диапазона значения элементов массива (см. рис.).

6. Выделим диапазон ячеек такой же размерности, в который будет помещаться результат операции.

7. Введем в выделенный диапазон формулу в формате:

8. Нажмем комбинацию клавиш + + . В ячейках выделенного диапазона появится результат.

Для начала следует понимать, что такое массив и какие массивы бывают.

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

Формулы массивов в свою очередь классифицируются на формулы, которые выводят (рассчитывают) единичный результат, и формулы которые рассчитывают и выдают результат в виде массива (матрицы).

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

Смета на проведение работ в Excel.
Вид работ	Количество	Трудозатраты на единицу	Стоимость одного чел часа
Прокладка кабеля, м	25	0,5	123
Укладка тротуарной плитки, м²	45	0,125	244
Покраска металлических поверхностей, м²	4	0,2	233
Монтаж дверного замка, шт.	12	0,8	40
Итого:	3480,4

Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:

Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, для чисел или для текстовых значений .

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

Создание одномерного массива констант

Перед созданием Массива констант посмотрим повнимательнее на диапазон ячеек, содержащий обычные значения (см. Файл примера )

в Строке формул выделите A1:A5 и нажмите клавишу F9 ;
получим некую запись , представляющую собой набор значений из диапазона A1:A5

Этот набор значений, как и формулы массива , обрамлен в фигурные скобки, сами значения разделены двоеточиями. Если бы значения были размещены в строке (в диапазоне A1:E1 ), а не в столбце, то значения были бы разделены точкой с запятой .

Создадим константу массива в ячейке B2 . Для этого введем в ячейку выражение = и нажмем ENTER . Массив значений не заключается в скобки автоматически, как формулы массива после нажатия CTRL+SHIFT+ENTER . Это необходимо делать вручную. В ячейке отразится только первое значение массива, т.е. 1.

Обычно массив значений не вводят в одну ячейку, т.к. в этом случае невозможно вытащить отдельные значения. Чтобы отобразить все значения нашего массива значений нужно выделить 5 ячеек в столбце (например, B1:B5 ), в Строке формул ввести выражение = и нажать CTRL+SHIFT+ENTER .

Чтобы избежать утомительного ввода последовательных чисел для вертикального массива констант можно воспользоваться формулой =СТРОКА(1:5) . Записав ее любой пустой ячейке, выделите ее в строке формул и нажмите F9 , а затем нажмите ENTER . Получите массив констант . Скопируйте содержимое ячейки в буфер обмена ( CTRL+C ), затем выделите вертикальный диапазон ячеек, соответствующий размерности массива, вставьте в активную ячейку содержимое Буфера обмена и нажмите CTRL+SHIFT+ENTER.

Чтобы избежать утомительного ввода последовательных чисел для горизонтального массива констант используйте формулу =ТРАНСП(СТРОКА(1:5)) или =СТОЛБЕЦ(A:E) . Получите массив констант = . Значения массива будут разделены точкой с запятой.

Применение массива констант

После ввода формулы необходимо нажать CTRL+SHIFT+ ENTER .

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

=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ() .

Б. Проверка значений Проверим, равно ли значение в ячейке А1 одному из определенных значений: 4, 6 или 9.

После ввода формулы нет необходимости нажимать CTRL+SHIFT+ENTER . Такая запись может существенно сократить время создания формулы по сравнению с использованием вложенных функций ЕСЛИ() .

Именование массива констант

Массиву констант можно присвоить Имя . Обычно так поступают с константами, образующими группу однотипных значений, например последовательности с конечным количеством элементов = .

Чтобы присвоить массиву констант имя необходимо сделать следующее:

Пример, найдем сумму 3-х наибольших значений , записав формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)) .

Предполагается, что в диапазоне A1:A10 имеется список числовых значений.

Создание двумерного массива констант

Чтобы создать двумерный массив констант необходимо сделать следующее:

выделите в книге диапазон ячеек из четырех столбцов и трех строк ( A1:D3 ).
в активной ячейке ( А1 ), в Cтроке формул введите выражение =
нажмите сочетание клавиш CTRL+SHIFT+ENTER

Мы получили двумерный массив констант, у которого нельзя удалить ни один элемент по отдельности – только все сразу. Как видно из формулы, строки в массиве разделяются двоеточиями, а элементы в строке – точкой с запятой.

Применение двумерного массива констант

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

Присвоим Имя Месяцы двумерному массиву:

Чтобы избежать утомительного ввода этого массива создайте два столбца, один с числами от 1 до 12, другой с названиями месяцев (см. статью Текстовые последовательности ). Затем, в Строке формул введите ссылку на этот диапазон, нажмите F9 и скопируйте в Буфер обмена .

Теперь записав формулу =ВПР(A2;Месяцы;2) , где в ячейке A2 - номер месяца, получим желаемый результат.

Для желающих получить дополнительную информацию о константах массива - ]]> ссылка на статью сайта Microsoft на английском языке ]]> .

Задам несколько целей - нужно в расчётах таблицы использовать формулы массива и комбинировать их с обычными формулами, подробнее рассмотреть плюсы и минусы использования массивов.

Как обычно мы действуем? Вводим формулу - количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

Давайте выполним те же самые действия, только умножать и прибавлять будем не ячейки, а целые диапазоны. Предварительно выделив диапазон от F3 до F11.

Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге.

К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть - если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

Комбинации формул массива и обычных формул

Усложняем задачу - нужно подвести итог (СУММ) по всему столбцу "ИТОГО", высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

Взглянем в строчку формул.

Посмотрим, что в этому случае делает программа Excel - пройдём по шагам вычислений. На вкладке "Формулы" нажмём кнопку "Вычислить формулу".

В окне вычислений видно, что теперь программа извлекает значения из каждой ячейки массива, затем производит математическое действие и выводит результат. Минус - на такое извлечение, если данных много, можно потратить много времени.

Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

Готово. В этом случае даже получилось "экономнее" нежели в обычных формулах, так как для промежуточного итога нет своего столбца!

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

Если остались вопросы - посмотрите новое видео. Ну и по традиции - вам может понравиться статья про функцию ПЛТ. Всем удачи!

Читайте также: