Работа с массивами excel – Работа с массивами функций в Excel

Содержание

Работа с массивами в Excel

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

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

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

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

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

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

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

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

=адрес_массива1*адрес_массива2

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

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

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

A2:D7.

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

Изменение содержимого массива

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

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

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

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

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

  1. Для удаления формулы массива нужно точно так же, как и в предыдущем случае, выделить курсором весь диапазон ячеек, в котором она находится. Затем нажать на кнопку Delete на клавиатуре.
  2. После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.

Функции массивов

Наиболее удобно в качестве формул использовать уже готовые встроенные функции Excel. Доступ к ним можно получить через

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

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

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

Урок: Мастер функций в Excel

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

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

=СУММ(массив1;массив2;…)

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

Урок: Как посчитать сумму в Экселе

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

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

=ТРАНСП(массив)

Урок: Транспонирование матриц в Excel

Урок: Как перевернуть таблицу в Экселе

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

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

=МОБР(массив)

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

=МОПРЕД(массив)

Урок: Обратная матрица в Excel

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

lumpics.ru

5 основных функции для работы с массивами

     Доброго времени суток друзья!

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

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

     Ну, что же, изучим необходимые функции для работы с массивами:

     Позволит вам выбрать значение из общего списка по указанному номеру позиции:

           =ВЫБОР(2;"Стул";"Стол";"Шкаф";"Диван")

      Эта функция возвращает указанное значение из одно- или двумерного диапазона:

       =ИНДЕКС(A1:C6;4;3)

     Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

       =ИНДЕКС(С1:С6;2)

     Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:  

         =ПОИСКПОЗ(B3;B2:B5;0)

    С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

     Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца:  =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

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

         =ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

      Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

     А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора! 

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

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

Работа с массивами данных эксель

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

​Смотрите также​ или удалить одну​ Enter, как обычно,​Скачать примеры массива функций​ простые формулы. Это​Примечание. Двумерные массивы Excel​ статье «Сортировка в​ когда видит фигурные​.​ обмена (​ качестве результата массив:​ массивах разделяются точкой​Обратная матрица в Excel​ вручную.​ области. Теперь в​ переместить курсор с​

​ действия.​Во время работы с​

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

​ отдельно взятую формулу​ а​Распространенная ошибка при работе​ сокращенный вариант, вместивший​ могут занимать сразу​ Excel формулой» здесь.​ скобки. А именно​Массив в Excel​CTRL+C​Во втором случае формула​ с запятой (;).​Как видим, операции с​Урок:​ неё можно будет​ помощью мышки, или​Координаты массива имеют вид​ таблицами Excel довольно​ (например в ячейке​Ctrl + Shift +​

​ с массивами функций​ всю необходимую информацию​

​ несколько листов (это​Шестой пример.​ - к числу​ – это любое​) и вставить его​ вводится в одну​ Например, на рисунке​

​ диапазонами помогают сэкономить​Как посчитать сумму в​ вводить любые данные.​ просто нажмете кнопку​ адресов первой её​ часто приходится оперировать​ D10) и выдаст​ Enter​ – НЕ нажатие​ для решения сложной​

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

​ сотни и тысячи​С помощью формулы​ 1+2, к 2+2,​ количество ячеек с​ в нужную формулу.​ ячейку и возвращает​ ниже представлен одномерный​ время при вычислениях,​ Экселе​Наиболее удобно в качестве​«Enter»​ ячейки и последней,​

​ с целыми диапазонами​

​ предупреждающее сообщение​Вуаля!​ кодового сочетания «Ctrl​ задачи.​ данных).​

​ массива можно удалить​ к 3+2, к​ данными (кроме одной),​Другим подходом для формирования​ только одно значение:​ горизонтальный массив, который​ а также свободное​Функция​ формул использовать уже​, то информационное сообщение​ разделенные двоеточием. Если​​ данных. При этом​​Невозможно изменить часть массива​

​Т.е. Excel произвел попарное​ + Shift +​Аргументы для функции –​​Формула массива – позволяет​​ из списка, столбца​

  1. ​ 4+2, к 5+2,​ расположенных в строках,​ числовой последовательности является​Итак, в данном уроке​ состоит из 5​ пространство листа, ведь​ТРАНСП​

  2. ​ готовые встроенные функции​ появится опять. Не​​ диапазон двумерный, то​​ некоторые задачи подразумевают,​.​​ умножение элементов массивов​​ Enter» (никогда не​ одномерные массивы. Формула​ обработать данные из​ пустые ячейки. Смотри​ к 6+2. Затем,​ столбцах, диапазонах. Можно​ использование формулы СТРОКА(A1:A3)​ Вы познакомились с​ элементов:​ не нужно дополнительно​

Изменение содержимого массива

​является типичным оператором​ Excel. Доступ к​ получится также закрыть​ первая и последняя​ что вся группа​Для редактирования формулы массива​ B2:B5 и C2:C5​ забывайте эту комбинацию​ просматривает каждый из​ этого массива. Она​ этот способ в​ получившиеся суммы сложит.​ сказать, массив в​В итоге получим =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;СТРОКА(A1:A3)))​ основными терминами и​Если ввести его на​ суммировать данные, которые​ массивов. Она позволяет​ ним можно получить​ окно программы или​ ячейки расположены по​ ячеек должна быть​ необходимо выделить весь​ и образовал новый​ клавиш). Это самое​ них по отдельности,​

​ может возвращать одно​ статье «Как удалить​ Получится результат =​​ Excel – это​​Но у этого подхода​ определениями, касаемо формул​ рабочий лист Excel,​ объединены в диапазон,​​ переворачивать таблицы или​​ через​ сохранить документ. Все​ диагонали друг от​ преобразована буквально в​ диапазон (A10:h21 в​ массив стоимостей (в​ главное, что нужно​ совершает заданные пользователем​ значение либо давать​ пустые ячейки в​ 33.​ диапазон ячеек. Что​

  1. ​ есть два недостатка:​ массива в Excel.​​ получим следующий результат:​​ для последующей работы​

  2. ​ матрицы, то есть,​​Мастер функций​​ время будет появляться​ друга. Например, адрес​ один клик. В​ нашем случае) и​ памяти компьютера), а​ запомнить при обработке​ операции и генерирует​​ в результате массив​​ Excel».​Корректировка формулы массива в​ такое диапазон, как​при вставке новых строк​ Если желаете получить​Чтобы вставить такой массив​ с ними. Все​

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

  1. ​ затем сложил все​ больших объемов информации.​ единый результат.​ (набор) значений.​Седьмой пример.​Excel​ его выделять, читайте​ на лист перед​ еще больше информации​ в Excel, необходимо​ это выполняется «на​ столбцы местами. При​«Вставить функцию»​

my-excel.ru

Массивы Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • {=A3:A7} – это обычный массив;
  • {1: 2: 3: 4: 5} – это массив констант.

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

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

После заполнения формы, нажмите «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))}.

Резюме

  1. Горизонтальный массив – это массив, ссылающийся на ячейки одной сроки либо содержащий константы, разделенные символом «;».
  2. Вертикальный массив – массив, ссылающий на ячейки одного столбца либо содержащий константы, разделенные символом «:».
  3. Размерность массива – количество элементов массива в одном измерении.
  4. Одномерный массив – либо горизонтальный либо вертикальный массив;
  5. Двумерный массив – вертикальный массив, содержащий в себе горизонтальные массивы одной размерности.
  6. Ввод массива ВСЕГДА необходимо подтверждать нажатием клавиш Ctrl + Shift + Enter.
  7. Нельзя изменить отдельный элемент массива. Изменению подлежать сразу все его элементы.
  8. В случае применения к массиву переменной, переменная будет сопоставлена каждому элементу массива.
  9. В случае применения к горизонтальному массиву вертикального массива, все их элементы будут сопоставлены друг другу.
  10. В случае применения массивов друг к другу, когда хоть один из них является двумерным, элементы массивов сопоставляются между собой по порядковым номера в равных измерениях (одинаковому расположению в массиве).
  11. При применении массивов друг к другу, в случае, когда хоть один из них является двумерным, размерности массивов должны совпадать.
  • < Назад
  • Вперёд >
Похожие статьи:Новые статьи:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

office-menu.ru

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

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра - товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

  1. выделяем ячейку С7
  2. вводим с клавиатуры =СУММ(
  3. выделяем диапазон B2:B5
  4. вводим знак умножения (звездочка)
  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ - в итоге должно получиться так:

  6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас... транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

  • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
  • вводим функцию транспонирования =ТРАНСП(   
  • в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:h21 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:h21 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике... На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

  1. выделяем диапазон B2:K11
  2. вводим формулу =A2:A11*B1:K1
  3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

 

www.planetaexcel.ru

Формулы массива в Excel - Microsoft Excel для начинающих

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

Без формулы массива

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

  1. Первым делом мы рассчитаем развитие для каждого студента.
  2. Затем при помощи функции МАКС (MAX) мы найдём наибольшее значение в столбце Progress.

С формулой массива

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

  1. Мы уже знаем, что определить прогресс первого студента нам поможет формула, показанная на рисунке ниже.
  2. Чтобы из значений прогресса всех студентов найти максимальное, мы добавляем сюда функцию МАКС (MAX) и заменяем C2 на C2:C6, а B2 на B2:B6.
  3. Завершаем редактирование формулы нажатием Ctrl+Shift+Enter.

Замечание: Формула массива в строке формул заключена в фигурные скобки {}. Не пытайтесь ввести их вручную, скобки исчезнут при попытке отредактировать формулу.

Пояснение:

  • Диапазон данных (массив констант) хранится в памяти Excel, не в диапазоне ячеек. Массив констант можно представить в таком виде: {19;33;63;48;13}.
  • Функция МАКС (MAX) использует этот массив как аргумент и возвращает результат 63.

Клавиша F9

При работе с формулами массива можно использовать режим просмотра содержимого массива констант.

  1. В формуле выделите C2:C6-B2:B6, как показано на рисунке ниже.
  2. Нажмите F9.

Отлично! Элементы вертикального массива констант разделяются точкой с запятой (в англоязычной версии Excel) или двоеточием (в русифицированной версии Excel). Элементы горизонтального массива констант разделяются запятой (в англоязычной версии Excel) или точкой с запятой (в русифицированной версии Excel).

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Excel массив

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

​Смотрите также​ массива. Отсортируем суммы​ На экране появляется​ обработать данные из​ этот диапазон, нажмите​Нажмите кнопку ОК.​=СУММ(B2:B6*{1:2:3:4:5})​

Без формулы массива

​ изменить часть массива»​Этот набор значений, как​Перевел: Антон Андронов​ нашем случае мы​ нажав комбинацию клавиш​ поскольку сложность сразу​​ следующим образом:​​ резонный вопрос: Зачем​

  1. ​ (в англоязычной версии​на​Эта статья поможет разобраться,​
  2. ​ к оплате в​​ соответствующая запись:​​ этого массива. Она​F9​Пример, найдем сумму 3-х​​После ввода формулы необходимо​​ - это определенного​

    С формулой массива

    ​ и формулы массива,​Автор: Антон Андронов​ перезададим обе области:​​Ctrl+Shift+Enter​​ отсеивает дилетантов. В​Мы можем пойти еще​ нужен такой массив?​ Excel) или точкой​B2:B6​ как работают формулы​

    1. ​ порядке возрастания. Для​Рассмотрим другие примеры использования​ может возвращать одно​и скопируйте в​ наибольших значений, записав​
    2. ​ нажать​ вида защита массива.​ обрамлен в фигурные​Массив значений (или константа​​Нажмите комбинацию клавиш​​. Формула массива будет​​ этот уроке мы​​ дальше и присвоить​​ Отвечу на него​​ с запятой (в​​.​​ массива в Excel.​​ списка отсортированных данных​​ функций массива Excel​
    3. ​ значение либо давать​​ Буфер обмена.​​ формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)).​

      ​CTRL+SHIFT+ENTER​​Чтобы избежать утомительного ввода​ скобки, сами значения​ массива или массив​​Ctrl+Shift+Enter​​ обновлена.​ разберем два подхода​ массиву констант имя.​ в виде примера.​

      ​ русифицированной версии Excel).​

      • ​Завершаем редактирование формулы нажатием​ Помещённая в одну​ создадим диапазон. Выделим​ – рассчитаем итоговую​ в результате массив​Теперь записав формулу =ВПР(A2;Месяцы;2),​​Предполагается, что в диапазоне​​.​
      • ​ последовательных чисел для​​ разделены двоеточиями. Если​​ констант) – это​​, чтобы сохранить изменения.​Второй подход можно применить​ к редактированию формул​​ Имя назначается точно​​На рисунке ниже приведен​

        Клавиша F9

        ​Урок подготовлен для Вас​Ctrl+Shift+Enter​ ячейку формула массива​ его. В строке​

        1. ​ сумму коммунальных платежей​​ (набор) значений.​​ где в ячейке​A1:A10​
        2. ​Формула массива сначала выполнит​​вертикального​​ бы значения были​

          ​ совокупность чисел или​ Размер массива будет​ как для уменьшения,​ массива в Excel.​ так же, как​ список студентов, которые​ командой сайта office-guru.ru​.​ выполняет сразу несколько​ формул вводим .​ с помощью одной​С помощью формул массива​

          ​A2​имеется список числовых​​ поэлементное умножение значений​​массива констант можно​

          ​ размещены в строке​

          office-guru.ru>

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

          ​ текстовых значений, которую​ уменьшен.​ так и для​ Они не идеальны,​ и обычной константе,​​ получили определенные оценки:​​Источник: http:http://my-excel.ru//http://my-excel.ru//www.excel-easy.comhttp://my-excel.ru//functionshttp://my-excel.ru//array-formulas.html​Замечание:​ вычислений.​ Жмем сочетание Ctrl​ формулы.​ реально:​

          Коротко о массивах констант

          ​- номер месяца,​ значений.​ из столбца и​ воспользоваться формулой =СТРОКА(1:5).​ (в диапазоне​ можно использовать в​Удалите лишние формулы из​ увеличения размера массива​

          ​ но, возможно, Вам​

          ​ через диалоговое окно​Наша задача перевести оценку​Перевел: Антон Андронов​Формула массива в​Не прибегая к помощи​

          ​ + Shift +​

          ​Выделяем ячейку Е9 (напротив​подсчитать количество знаков в​ получим желаемый результат.​Чтобы создать двумерный массив​ констант из массива.​ Записав ее любой​A1:E1​

          ​ формулах массива. Константы​

          ​ диапазона C6:C10.​ в Excel. Здесь​ пригодятся.​Создание имени​​ из числового вида​​Автор: Антон Андронов​

          ​ строке формул заключена​

          ​ формулы массива, нам​ Enter.​ «Итого»).​ определенном диапазоне;​Для желающих получить дополнительную​

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

          ​ констант необходимо сделать​ Эквивалентом данной формулы​ пустой ячейке, выделите​

          ​), а не в​ массива необходимо вводить​Как видите, оба подхода​ главное, чтобы у​На рисунке ниже представлена​:​ в ее словесное​В Microsoft Excel можно​ в фигурные скобки​ потребуется выполнить следующую​Транспонированная матрица. Специальная функция​Вводим формулу вида: =СУММ(C3:C8*D3:D8).​суммировать только те числа,​

          ​ информацию о константах​

          ​ следующее:​ является следующее обычное​ ее в строке​ столбце, то значения​ в определенном формате,​ достаточно сложные и​

          ​ старого и нового​ формула массива, размер​Не забывайте указывать знак​

          ​ описание и вывести​​ создавать массивы, которые​​{}​ последовательность действий, чтобы​ Excel для работы​Нажимаем сочетание клавиш: Ctrl​

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

          ​ массивов была как​ которой необходимо расширить​ равенства в поле​ соответствующие значения в​ не хранятся в​

          ​. Не пытайтесь ввести​ определить максимальное развитие​ с двумерными массивами.​ + Shift +​

          ​ условию;​Массив функций Excel позволяет​ ячеек из четырех​=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)​F9​ точкой с запятой​ {1:2:3:4:5} или для​​ реальная применимость в​​ минимум одна общая​

          my-excel.ru

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *