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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читайте также:  Зависимость мощности двигателя от объема

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Название авто Литраж бака, л Расход, л на 100 км
    Авто1 50 6
    Авто2 60 7
    Авто3 70 10
    Авто4 80 12

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

    При стандартных вычислениях формула составлялась бы так: =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))>.