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

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

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

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

Во 2-м — в нескольких сразу.

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

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

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

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

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

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

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

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

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

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

«OK»

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

Delete

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

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

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

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

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

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

Интересно почитать: Excel формулы обучение

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

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

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

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

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

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

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

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

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

Массивы Excel

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

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

Что такое массив?
Измерения массива;
Массив констант;
Операции с массивами;
Резюме.

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

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

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

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

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

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

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

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

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

Интересно почитать: Как скопировать формулу в excel в другую ячейку без изменения

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

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

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

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

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

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

<=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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тут все просто, программка помножила любой элемент массива на 5, потом их просуммировала.

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

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

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

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

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

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

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

Время от времени при работе в Excel протягивать формулу не комфортно. Но можно применять массив.

Для этого введем выделим сходу все ячейки, к которые нужно ввести формулу, а опосля ее ввода нажмем Ctrl+Shift+Enter

В итоге получим вычисления сходу в 3-х ячейках:

Динамические массивы

Юзеры Office 365 уже могут опробовать динамические массивы.

Ранее для того, чтоб вывести некий массив, нам было надо выделять подходящее количество ячеек, а потом не запамятовать надавить Ctrl+Shift+Enter.

Сейчас все значительно проще: ставим курсор в ячейку, жмем =, ссылаемся на массив, жмем Enter. Готово, нам вывелся сходу весь массив данных.

Если на собственном пути массив повстречает преграду в виде уже заполненной ячейки, мы увидим новейший тип ошибки: #ПЕРЕНОС! Для того, чтоб от нее избавиться, нужно очистить ячейку.

Еще одна увлекательная возможность динамических массивов: если вы ссылаетесь на иной динамический массив, то его даже не надо выделять. Указываем первую ячейку массива и ставим #. К примеру: =B5# Жмем Enter и лицезреем весь наш начальный динамический массив.

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