Как вводить массив в excel - IT Справочник
Llscompany.ru

IT Справочник
9 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вводить массив в excel

Массивы в Excel пример использования

Массивы в Excel пример использования

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Формулы массивов в Excel — синтаксис формул массивов и массивов констант

Формула массивов в Excel – это формула, которая используют в качестве входящего параметра целый массив, а не отдельную ячейку. Формулу массива можно рассматривать как множество обычных, упакованных в одну супер формулу. В сегодняшней статье мы познакомимся с синтаксисом формул массивов: от написания простейших до более мощных версий. Прежде, чем начать, давайте разберемся, что такое массив.

Что такое массив?

В Excel массив – это диапазон ячеек. Ниже приведены несколько примеров. Массивы в Excel бывают двухмерные и одномерные. Одномерные в свою очередь делятся на горизонтальные и вертикальные.

Когда мы говорим о формулах массива, мы подразумеваем, что это нормальная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ…), но немного измененная, чтобы принять в качестве входных данных массив или набор массивов. Это то, что лежит в основе формул массива и делает его столь мощным.

Формула массива вводится определенным образом – простой ввод работать не будет. Давайте рассмотрим пример. Откройте пустой рабочий лист и введите несколько значений, как показано на рисунке. Теперь предположим, что вам необходимо определить адрес ячейки с наименьшим значением, для этого введите формулу, указанную ниже и нажмите сочетание клавиш Ctrl + Shift + Enter.

Результатом в этом случае будет адрес ячейки с наименьшим значением в данном диапазоне. Как вы можете увидеть, при обновлении данных в диапазоне, результат тоже меняется. Подобного эффекта можно также добиться с помощью обычной формулы =АДРЕС(ПОИСКПОЗ(МИН(A1:A9);A1:A9;0);1). Однако ж мы только начали изучение, в дальнейшем вы обнаружите, что некоторые вещи можно делать только с помощью формулы массива, либо с большим количеством обычных формул.

Части формул массивов в Excel

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

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

Массив констант в формулах массивов

Массив констант – это набор статических значений. Эти значения не ссылаются на другие ячейки или диапазоны. Поэтому они будут всегда одинаковыми независимо от изменений происходящих на листе.

Горизонтальный массив констант

Горизонтальный массив констант вводиться как последовательность чисел, разделенных точкой с запятой (;), заключенных в фигурные скобки. Например: <1;2;3;4;5>. Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: <1:2:3:4:5>.

Операторы массива в формулах массивов

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Оператор массива И

Оператор И возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример ниже показывает использование оператора массива И (*) между массивами:

Оператор массива ИЛИ

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из условий выражения возвращает значение ИСТИНА. Пример ниже показывает использование оператора массива ИЛИ (+) между массивами:

Что такое диапазон массива?

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

Синтаксис формул массивов

Возможно, вы уже получили кое-какое представление об этой части статьи. Все что вам нужно, чтобы написать формулу массива – это использовать в качестве аргументов массив и нажать сочетание клавиш Ctrl + Shift + Enter. Давайте рассмотрим некоторые основные моменты синтаксиса формул массивов.

Сортировка с помощью формулы массива

Скажем, у вас есть набор данных в ячейках D2:D10 и вы хотите отсортировать их в порядке возрастания. Вы уже наверное догадались, что нам понадобиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое наименьшее значение и заданного диапазона. Нам также понадобиться диапазон, где мы будем производить вычисления.

Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит следующим образом =НАИМЕНЬШИЙ(D2:D10;1). Такая формула вернет нам наименьшее значение диапазона D2:D10. Но нам необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список. Для начала выделим диапазон, в котором мы хотим увидеть список, затем вводим формулу в первую ячейку и жмем Ctrl + Shift + Enter. Формула будет скопирована на весь диапазон, результатом станет отсортированный список.

Поиск уникального значения, отвечающего определенным условиям

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Вот где обнаруживается истинная мощь формул массивов. Ели бы мы использовали обычные формулы, нам понадобилось бы столько же строк, сколько менеджеров, если не больше. Однако мы можем сделать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем здесь – это сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращает номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью функции СМЕЩ возвращаем имя из этой строки.

Консолидация данных по более чем одному условию

Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами. Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0. Затем мы используем функцию СУММ для суммирования всех этих значений массива.

Еще один пример консолидации данных по условию

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

Мы можем достичь этого с помощью формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0)). Так что же мы здесь сделали? Мы проверили три условия – первые два были скомбинированы с помощью оператора И (*) и третье было добавлено с помощью оператора ИЛИ (+). В результате получилась структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из четвертого столбца, когда оно принимает значение ИСТИНА и 0, если ЛОЖЬ. Результат, конечно, представляет собой сумму продаж отвечающим всем трем, указанным выше, условиям.

Читать еще:  Произведение ячеек столбца excel vba

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

Excel. Введение в формулы массива

Ранее я уже посвятил несколько заметок использованию формул массива:

Полученные комментарии подсказали мне, что пользователи не часто используют формулы массива. Более того мало кто знаком с ними.

Предлагаю вашему вниманию посвященный массивам фрагмент книги Джона Уокенбаха. Подробное руководство по созданию формул в Excel 2002. – М.: Издательский дом «Вильямс», 2002. – 624 с.

Скачать заметку в формате Word, примеры в формате Excel

В Excel массивы могут быть одномерными (их еще называют векторами) или двумерными (матрицы). Размерность массива зависит от количества строк и столбцов в нем. Например, одномерный массив может храниться в диапазоне, состоящем из одной строки (горизонтальный массив) или столбце (вертикальный массив). Двумерный массив хранится в прямоугольном диапазоне ячеек. Excel (в отличие от языка программирования VBA) не поддерживает трехмерные массивы.

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

Формула массива, возвращающая несколько значений

Рис. 1 демонстрирует рабочий лист, на котором производится расчет объема продаж товаров. Для этого вычисляют значение в столбце D (объем продаж каждого товара), используя формулу =В2*С2, а затем копируют ее в расположенные ниже ячейки.

Рис. 1. Диапазон D2:D6 содержит традиционные формулы произведения (в ячейках D2 и D3 различные формулы, хотя и похожие)

После копирования этой формулы рабочий лист содержит пять формул в столбце D. Альтернативный путь вычислений – использование единой формулы (формулы массива) для вычисления всех пяти значений в диапазоне D2:D6. Эта единственная формула занимает пять ячеек и возвращает массив из пяти значений. Для создания единой формулы массива нужно произвести следующие операции:

  1. Выделить диапазон, в котором будет сохраняться результат вычислений. В данном случае это диапазон D2:D6.
  2. Ввести формулу: =В2:В6*С2:С6
  3. Обычно после введения формулы нажимается клавиша , но только не в случае формулы массива. Так как введенная формула является формулой массива, нажимается комбинация клавиш .

Формула добавляется во все пять выделенных ячеек (рис. 2), при этом в строке формул появится следующее выражение:

Рис. 2. Диапазон D2:D6 содержит формулу массива (в ячейках D2 и D3 одна и та же формула)

Excel заключил формулу в фигурные скобки. Это говорит о том, что данная формула является формулой массива.

Эта формула производит вычисления и возвращает массив из пяти элементов. В данном случае формула массива производит операции над двумя другими массивами, оба из которых хранятся в диапазонах ячеек. Значения первого массива хранятся в диапазоне В2:В6, а второго — в С2:С6. Для отображения итогового массива требуется пять ячеек, потому что в одной ячейке нельзя отобразить более чем одно значение. Этим объясняется, зачем нужно выделять пять ячеек перед введением формулы.

Конечно, эта формула массива возвращает точно такие же значения, что и пять формул, введенных в соответствующие ячейки диапазона D2:D6

Использование единой формулы массива предпочтительнее, чем использование нескольких формул, так как предоставляет определенные преимущества:

  • Легко убедиться, что все формулы в диапазоне идентичны.
  • Использование формул массива, возвращающих сразу несколько значений, избавляет от случайных изменений в формуле. Нельзя просто изменить одну ячейку в формуле массива.
  • Использование формул массива почти всегда предотвращает вмешательство начинающих пользователей, которые хотят внести свои изменения в формулы.

Формула массива, возвращающая одно значение

Обратимся к рис. 3. Следующая формула массива размещается в одной ячейке – D8:

Рис. 3. Сумма произведений в виде формулы массива

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

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

Эта формула оперирует значениями, находящимися в двух массивах. Оба массива хранятся в ячейках. Первый – в диапазоне ячеек В2:В6, второй – C2:С6. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММ выполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

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

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

Для создания массива констант нужно список его элементов заключить в фигурные скобки. Вот пример горизонтального массива констант, состоящего из пяти элементов: <1;0;1;0;1>. Для создания вертикального массива констант используйте в качестве разделителя двоеточие.

Формула =СУММ(<1;0;1;0;1>) использует функцию СУММ, аргументом которой является массив констант. Формула возвращает значение суммы элементов массива, равное 3. Отметим, что эта формула использует массив в качестве аргумента, но при этом не является формулой массива. Поэтому не нужно использовать клавиши для ввода формулы.

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

Пока, возможно, трудно заметить, какие-либо преимущества использования массивов констант. Действительно, формула =СУММ(1;0;1;0;1) возвращает точно такое же значение, как и =СУММ(<1;0;1;0;1>).

Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант: =СУММ(<1;2;3;4>*<5;6;7;8>). Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов: <5;12;21;32>. Созданный массив используется впоследствии в качестве аргумента для функции СУММ, которая возвращает результат вычислений (70). Формула эквивалентна следующей формуле, которая не использует массивы: =СУММ(1*5;2*6;3*7;4*8)

Можно составлять формулы массивов для разных типов массивов. Например, можно составить формулу, в которой будет одновременно использоваться массив констант и массив, элементы которого хранятся в диапазоне ячеек (рис. 4). Для подсчета суммы значений, которые являются произведением элементов массива в диапазоне Al:D1 и соответствующих элементов массива констант, применяется следующая формула: =<СУММ((A1:D1*<1;2;3;4>)>. Эта формула эквивалентна следующей: =СУММ(А1*1;В1*2;С1*3;D1*4)

Рис. 4. Сумма произведений массива рабочего листа и массива констант

Элементы массива констант

Массивы констант могут состоять из чисел, текста, логических значений (например, ИСТИНА, ЛОЖЬ) и даже значений ошибок, таких как #Н/Д. Числа в массиве могут быть целыми, с десятичной точкой или экспоненциальными. Текст в массиве констант заключается в двойные кавычки (например, » Вторник » ). Массив констант может состоять из элементов разного типа, например:

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

Понятие размерности массива

Массивы могут быть одномерными или двумерными. Ориентация одномерного массива может быть горизонтальной или вертикальной.

Одномерные горизонтальные массивы

Элементы в одномерном горизонтальном массиве разделяются точкой с запятой (запятыми в английской версии программы). Ниже приведен пример одномерного горизонтального массива констант:

Для отображения этого массива в диапазоне необходимо пять последовательных ячеек в строке. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из одной строки и пяти столбцов. Затем введите: = <1;2;3;4;5>и нажмите (рис. 5).

Рис. 5. Одномерный горизонтальный массив.

Обратите внимание, внутренние фигурные скобки обозначают массив констант; внешние фигурные скобки – формулу массива. Если ввести этот массив в горизонтальный диапазон, состоящий более чем из пяти ячеек, то лишние ячейки будут содержать #Н/Д (что указывает на недопустимые значения). Если ввести этот массив в вертикальный диапазон ячеек, то в каждой ячейке диапазона появится первый элемент (1) массива.

Ниже приведен пример другого горизонтального массива. Он состоит из семи элементов и создает текстовую последовательность: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Для ввода этого массива выделите семь элементов в строке и введите следующий текст: =< " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " , " Пятница " ; " Суббота " ; " Воскресенье " >. Нажмите .

Одномерные вертикальные массивы

Элементы в одномерном вертикальном массиве разделяются двоеточием (точкой с запятой в английской версии программы). Ниже приведен пример вертикального массива констант, состоящего из шести элементов: <10:20:30:40:50:60>. Для отображения этого массива в диапазоне необходимо шесть ячеек в столбце. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из шести строк и одного столбца. Затем введите: = <10:20:30:40:50:60>и нажмите .

Читать еще:  Аргументы функции в excel

Ниже приведен пример другого вертикального массива, состоящего из четырех элементов:

Двумерные массивы

В двумерных массивах используются точки с запятыми (запятые в английской версии программы) для разделения горизонтальных элементов и двоеточия (точки с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4: <1;2;3;4:5;6;7;8:9;10;11;12>. Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу = <1;2;3;4:5;6;7;8:9;10;11;12>после чего нажмите (рис. 6).

Рис. 6. Двумерный массив

Если ввести массив в диапазон, состоящий из большего числа ячеек, Ехсеl отобразит #Н/Д в лишних ячейках (рис. 7).

Рис. 7. Показано, что получится, если ввести массив 3×4 в диапазон ячеек 10×5.

Каждая строчка двумерного массива должна содержать одинаковое количество элементов. Например, представленный ниже массив недопустимый, потому что его третья строка состоит только из трех элементов; <1;2;3;4:5;6;7;8:9;10;11>. Excel не даст возможности ввести формулу, в которой присутствует недопустимый формат массива.

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

Excel позволяет создать массив констант, присвоить ему имя и затем использовать это имя в формулах. Фактически присвоение имени массиву заключается в присвоении имени формуле (рис. 8). Пройдите по меню: Формулы – Диспетчер имен.

Рис. 8. Показано, как присвоить имя массиву, используя диалоговое окно Создание имени.

Массиву присвоено имя ДеньНедели, которое соответствует следующему массиву констант: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Отметим, что в диалоговом окне Создание имени для задания массива необходимо использовать знак равенства (=) перед массивом констант. В противном случае массив будет воспринят как текстовая строка. Также необходимо самостоятельно взять элементы массива в фигурные скобки. Excel в этом случае не вводит скобки автоматически. После присвоения имени массиву, его имя можно использовать в формулах (рис. 9).

Рис. 9. Ввод поименованного массива в диапазон Al:G1

Так как элементы массива разделяют точки с запятой, ориентация массива будет горизонтальной. Для создания вертикальных массивов используются двоеточия. Также можно воспользоваться функцией транспонирования для того, что бы вставить горизонтальный массив в вертикальный диапазон (рис. 10). Для этого введите в диапазон семи вертикальных ячеек A1:A7 формулу массива:

Рис. 10. Ввод горизонтального массива в вертикальный диапазон

При использовании функции Excel ИНДЕКС можно получить доступ к отдельному элементу массива. Например, ниже приведенная формула возвращает значение «Четверг», четвертого элемента массива ДеньНедели: =ИНДЕКС(ДеньНедели;4)

Массивы Excel

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

Содержание статьи:

Определение массива

Чтобы понять, что такое массив, необходимо вспомнить, что такое переменная.

Переменная — область памяти, за которой закреплено определенной имя, например «x = 5» или «Имя = “Андрей”». Переменная всегда содержит только одно значение, т.е. не может быть разделена на более мелкие части. Данное определение больше подходит для переменных в языках программирования. Чтобы не отходить от темы Excel, переопределим его.

Каждый лист Excel является таблицей, за которой закреплена область в памяти компьютера. Таблица состоит из ячеек, которые имеют свой уникальный адрес. Сама ячейка не может быть разделена на более мелкие части, поэтому ее можно назвать переменной. Массив содержит набор переменных и имеет имя. То, что называют в Excel диапазоном, по своей сути является массивом: строка листа, столбец листа, количество ячеек >1, все это массивы данных. НО! Чтобы не вносить путаницу в определения функций и т.п. данные понятия необходимо разделять, т.к. приложение по-разному обрабатывает диапазоны и массивы.

Чтобы дать программе понять, что формула содержит массив, нужно после ввода данных в строку формул одновременно нажать клавиши клавиатуры Ctrl + Shift + Enter. Все ее содержимое заключится в фигурные скобки <>.

Далее будет подробнее описана работа с массивами.

Измерения массива

Массивы могут содержать несколько измерений вплоть до измерений в несколько десятков и даже больше, но хорошо это или плохо, в формулах Excel используются максимум 2 измерения, поэтому массив в Excel может быть:

  • Одномерным – 1 измерение;
  • Двумерным – 2 измерения.

Одномерный массив состоит из одного ряда значений. Это может быть строка или столбец.

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

На рисунке ниже представлены оба вида массивов. Обратите внимание, что разные измерения имеют разные разделители («;» — для столбцов и «:» — строк).

Массив констант

Константа это та же переменная, только не меняющая значение. Если значение переменной можно поменять в любое время, то константа задается один раз и больше не меняется. Наверное, самая известная константа – число Пи.

Массив констант отличается от обычного массива тем, что обычный массив ссылается на диапазон ячеек, а массив констант задается пользователем вручную:

Представьте, что Вам необходимо использовать в расчетах большой массив, состоящий из сотни констант, и использовать его нужно многократно. Набивать константы каждый раз для каждой формулы ручками – дело «неблагодарное». Поэтому, создайте синоним массива с помощью функции присвоения имен, расположенной на вкладке «Формулы» -> раздел «Определенные имена» -> кнопка «Диспетчер имен». В появившемся окне нажмите на кнопку «Создать», после чего появиться следующая форма:

  • Имя – имя диапазона;
  • Область – место, где данное имя будет доступно;
  • Примечание – комментарий. Текст, введенный здесь, будет высвечиваться при выборе имени массива из определенной для него области;
  • Диапазон – сам массив в виде ссылки на диапазон либо массив констант «=<…>».

После заполнения формы, нажмите «OK».

Теперь данный массив доступен по заданному имени в ячейках из определенной для него области.

О том, как их применять рассказывается дальше.

Операции с массивами

Перейдем, наконец, к примерам использования массивов.

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

При стандартных вычислениях формула составлялась бы так: =B2/C2*100. Затем ее необходимо протянуть. Мы получили готовый результат.

Повторим расчет, только с использованием массивов.

Сначала выделяем ячейки, в которых необходимо произвести расчет. Далее записываем в строку формул: =B2:B5/C2:C5*100, где “B2:B5” диапазон всех ячеек для литража, “C2:C5” диапазон всех ячеек для расхода. На данный момент это именно диапазоны, чтобы они превратились в массивы, нажмите одновременно клавиши Ctrl + Shift + Enter. Формула автоматически будет заключена в фигурные скобки, а расчет появиться во всех предварительно выделенных ячейках. Результат тот же, что и в первом варианте.

Следовательно, уже навязывается вопрос о смысле использования массивов.

В данном конкретном примере, что использовать – разницы нет, если только Вы не хотите защитить ячейки от случайного или намеренно изменения. Поясним. Когда к какому-то диапазону применяется массив, то затем никакой элемент этого массива не может быть отдельно изменен либо удален. Если попробовать произвести эти действия, приложение Excel выдаст ошибку. Чтобы избежать ее, выделите весь диапазон, к которому применен массив, а затем измените строку формул либо удалите ее полностью, после подтвердите изменения для всех элементов нажатием Ctrl + Shift + Enter.

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

Используя стандартные формулы, подсчет производиться двумя действиями:

  1. Рассчитать километраж для каждого авто;
  2. Просуммировать все имеющиеся результаты.

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

Выберите ячейку, в которую хотели бы записать результат. В нее впишите уже применяемую формулу, но в качестве аргумента функции СУММ. Подтвердите использование массива нажатие Ctrl + Shift + Enter . Должно получиться следующее: <=СУММ(B2:B5/C2:C5*100)>.

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

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

Применим массив, который мы создавали в начале урока: Имя_диапазона.

Данный массив является одномерным и имеет размерность 5, т.к. содержит пять элементов. Перенесем значения массива в ячейки книги. Для этого выделим диапазон A1:E1, в строку формул введем имя массива и нажмем Ctrl + Shift + Enter. Получим результат:

В данном случае программа поочередно будет вытаскивать значения из массива для каждой ячейки. Если ячеек окажется больше, чем элементов есть в массиве, то для последних ячеек будет выдана ошибка #Н/Д, т.е. элемент не найден. Если ячеек, наоборот окажется меньше, то массив будет задействован частично, а именно в количестве заданных ячеек.

Теперь поступим по-другому. Выделите вертикальный диапазон A1:A5 и введите в него массив. Во все ячейки диапазона загрузиться только первый элемент массива. Это произошло от того, что созданный нами массив является горизонтальным, а не вертикальным.

Для создания горизонтального массива его элементы разделяются «;», для создания вертикального используется «:». Для создания двумерных массивов используются оба символа. Имейте в виду, иногда, где и какой символ использовать, задается настройками, чтобы проверить, какие разделители использовать именно Вам, поступите следующим образом:

  1. Заполните числами несколько ячеек подряд по горизонтали и вертикали;
  2. Создайте функцию СУММ;
  3. Для первого аргумента функции укажите горизонтальный диапазон, для второго вертикальный;
  4. Нажмите на кнопку «вставить функцию» рядом со строкой формул;
  5. В окне аргументов функции посмотрите предварительные результаты для каждого аргумента. Те разделите, которые использует программа, необходимо использовать и Вам.

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

Для начала создадим одномерный вертикальный массив = <1: 1: 1>и умножим его на 5, после чего узнаем сумму произведений его элементов.

Здесь все просто, программа умножила каждый элемент массива на 5, затем их просуммировала.

Теперь умножим тот же диапазон на горизонтальный массив =<5; 5>.

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

Снова изменим условия и из вертикального массива сделаем двумерный массив =<1; 1: 1; 1: 1; 1>. Перемножим на =<5; 5>. И вот оно! Результат не поменялся. Почему? Потому что в случае использования двумерных массивов их элементы сопоставляются другим элементам массива по их порядковым номерам в равных измерениях.

А если размерность одного массива в определенном измерении будет превышать размерность другого массива в том же измерении, то элементам первого будет сопоставлена ошибка #Н/Д, т.к. для них отсутствуют сопоставляемые элементы второго, в результате чего, вся формула может вернуть данную ошибку.

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

Имейте в виду, что к массивам можно применять все формулы, которые применяются в стандартных ситуациях, включая логические. Например, в случае, описанном выше, когда перемножаются массивы с разными размерностями в одинаковых измерениях, чтобы избежать возврата ошибки, необходимо изменить формулу – <=СУММ(ЕСЛИОШИБКА(A1:C3*E1:F1;0))>.

Управление массивами в Microsoft Excel

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

Операции с массивами

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

Во втором — в нескольких одновременно.

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

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

Создание формулы

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

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

Координаты массива имеют вид адресов первой её ячейки и последней, разделенные двоеточием. Если диапазон двумерный, то первая и последняя ячейки расположены по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.

А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.

    Чтобы рассчитать подобную формулу, нужно выделить на листе область, в которую будет выводиться результат, и ввести в строку формул выражение для вычисления.

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

    Если вы в дальнейшем попытаетесь удалить содержимое или изменить любую из ячеек, которая расположена в диапазоне, куда выводится результат, то ваше действие окончится неудачей. Также ничего не выйдет, если вы сделаете попытку отредактировать данные в строке функций. При этом появится информационное сообщение, в котором будет говориться, что нельзя изменять часть массива. Данное сообщение появится даже в том случае, если у вас не было цели производить какие-либо изменения, а вы просто случайно дважды щелкнули мышью по ячейке диапазона.

    Если вы закроете, это сообщение, нажав на кнопку «OK», а потом попытаетесь переместить курсор с помощью мышки, или просто нажмете кнопку «Enter», то информационное сообщение появится опять. Не получится также закрыть окно программы или сохранить документ. Все время будет появляться это назойливое сообщение, которое блокирует любые действия. А выход из ситуации есть и он довольно прост

      Закройте информационное окно, нажав на кнопку «OK».

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

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

  • После того, как изменения внесены, набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.
    1. Для удаления формулы массива нужно точно так же, как и в предыдущем случае, выделить курсором весь диапазон ячеек, в котором она находится. Затем нажать на кнопку Delete на клавиатуре.

  • После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.
  • Функции массивов

    Наиболее удобно в качестве формул использовать уже готовые встроенные функции Excel. Доступ к ним можно получить через Мастер функций, нажав кнопку «Вставить функцию» слева от строки формул. Или же во вкладке «Формулы» на ленте можно выбрать одну из категорий, в которой находится интересующий вас оператор.

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

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

    Оператор СУММ

    Одной из наиболее востребованных функций в Экселе является СУММ. Её можно применять, как для суммирования содержимого отдельных ячеек, так и для нахождения суммы целых массивов. Синтаксис этого оператора для массивов выглядит следующим образом:

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

    Оператор ТРАНСП

    Функция ТРАНСП является типичным оператором массивов. Она позволяет переворачивать таблицы или матрицы, то есть, менять строки и столбцы местами. При этом она использует исключительно вывод результата в диапазон ячеек, поэтому после введения данного оператора обязательно нужно применять сочетание Ctrl+Shift+Enter. Также нужно отметить, что перед введением самого выражения нужно выделить на листе область, у которой количество ячеек в столбце будет равно числу ячеек в строке исходной таблицы (матрицы) и, наоборот, количество ячеек в строке должно равняться их числу в столбце исходника. Синтаксис оператора следующий:

    Оператор МОБР

    Функция МОБР позволяет производить вычисление обратной матрицы. Все правила ввода значений у этого оператора точно такие же, как и у предыдущего. Но важно знать, что вычисление обратной матрицы возможно исключительно в том случае, если она содержит равное количество строк и столбцов, и если её определитель не равен нулю. Если применять данную функцию к области с разным количеством строк и столбцов, то вместо корректного результата на выходе отобразится значение «#ЗНАЧ!». Синтаксис у этой формулы такой:

    Для того чтобы рассчитать определитель, применяется функция со следующим синтаксисом:

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector