Как сделать массив в excel
Для решения любой задачи почти всегда есть несколько способов или подходов. Большинство из нас будет использовать чаще всего на практике самый простой, быстрый и понятный метод. Однако знание различных вариантов решения одной и той же задачи позволяет специалисту.
. более гибко и квалифицированно реагировать на нюансы ситуаций и выбирать из имеющегося арсенала знаний наиболее эффективное в каждом конкретном случае.
Для обработки данных, хранящихся в таблицах баз данных в Excel, наряду с фильтрами и сводными таблицами есть малоизвестный широкому кругу пользователей инструментарий – формулы массивов.
Массив в Excel – это однородные данные, размещенные в нескольких ячейках столбца или строки или области. Данные, размещенные в ячейках одной строки или одного столбца – это одномерный массив. Данные, размещенные в нескольких строках и столбцах, то есть в таблице – это двумерный массив.
Формулы массивов имеют и иное, менее распространенное в последнее время, но более понятное русскоговорящему человеку название – табличные формулы. Эти формулы работают с блоками ячеек (массивами) так, как обычные формулы с одиночными ячейками.
Формула массива вводится в ячейку после знака =, как и обычная формула, но если окончание ввода простой формулы подтверждается нажатием на клавишу Enter, то для окончания ввода формулы массива необходимо последовательно нажать на три клавиши – Ctrl+Shift+Enter. (Клавишу + нажимать не нужно, следует удерживать нажатыми предыдущие клавиши до момента нажатия на последнюю кнопку!)
Формула массива примет вид: .
Аргументами функций ФОРМУЛЫ могут быть как одиночные ячейки, так и диапазоны ячеек – все, как и в обычных формулах.
Формула массива может возвращать результат не только в одну ячейку, но и в группу ячеек. Для этого перед вводом формулы нужно выделить массив ячеек предназначенных для отображения результатов и затем ввести формулу.
При работе с большими таблицами применение формул массива позволяет избежать создания блоков промежуточных результатов и ускоряет получение конечного результата
Ряд важных функций в Excel работают только с массивами и выводят результаты в массив. Это функции ЛИНЕЙН() ЛГРФПРИБЛ(), ТЕНДЕНЦИЯ() и другие.
Полностью разделяя мнение о том, что примеры полезнее правил (Ньютон), предлагаю рассмотреть задачу, при решении которой применение формул массивов целесообразно, а так же понять смысл и синтаксис этих формул.
Пример использования формул массивов для выборочного суммирования.
Требуется выборочно быстро найти и просуммировать некоторые значения из большой таблицы MS Excel.
Для решения задачи следует выполнить ниже перечисленную последовательность действий.
в ячейку B3: Блок-СМ15
3. Ввести формулу массива для подсчета количества изделий
4. Ввести формулу массива для подсчета общей стоимости поставленных изделий
в ячейку B5: =420 000,00
Как работают формулы?
Формула в ячейке B4 суммирует построчно произведения: Σ( ai * bi * ci ).
Формула в ячейке B5 суммирует построчно произведения: Σ( ai * bi * ei ).
Если в строке диапазона A9:A27 Excel находит значение равное значению в ячейке B2, то ai =1. Если находит значение не равное значению в ячейке B2, то ai =0. Аналогично присваиваются значения bi . Значения ci и ei просто берутся построчно из ячеек указанных диапазонов.
Спектр задач, решаемых при помощи формул массивов.
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 - пройдём по шагам вычислений. На вкладке "Формулы" нажмём кнопку "Вычислить формулу".
В окне вычислений видно, что теперь программа извлекает значения из каждой ячейки массива, затем производит математическое действие и выводит результат. Минус - на такое извлечение, если данных много, можно потратить много времени.
Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.
Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.
Готово. В этом случае даже получилось "экономнее" нежели в обычных формулах, так как для промежуточного итога нет своего столбца!
Как видно их примеров - у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс - улучшаем безопасность, минус - приходится переделывать весь массив, если что-то пошло не так.
Если остались вопросы - посмотрите новое видео. Ну и по традиции - вам может понравиться статья про функцию ПЛТ. Всем удачи!
Читайте также: