Как выделить массив в экселе

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Формулы массива в Excel — от А до Я

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

Не знаю, новичок вы в Excel или уже опытный пользователь, но коль судьба привела вас на эту страницу, в ходе прочтения вы:

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

Формула массива (array formula) — это такая формула, в процессе вычисления которой создается один или несколько массивов. При этом не обязательно как результат вычисления, но и как промежуточный этап.

Будьте на 100% уверены: если формула

— это формула массива.

Слишком очевидно и все еще непонятно? Не беда — читаем далее.

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

Массивы в Excel

Массив (array) — это фиксированный набор элементов с фиксированным порядком. Иными словами, все элементы массива, включая их положение в нем, являются его неотъемлемой частью, их порядок и количество нельзя изменить.

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) — это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

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

Содержимое диапазона также можно свободно сортировать, если в нем нет объединенных ячеек.

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна — это нарушило бы само его определение.

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

Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

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

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Одномерные и двумерные массивы в Excel

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

По изменяемости элементов —

Методы создания массивов

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

Массив констант, созданный вручную

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

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

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

Именованный массив констант

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

Используем именованный массив

С помощью функций

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

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

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

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

С помощью других массивов

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

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

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

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

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

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

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Взаимодействие функций Excel с массивами

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

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

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

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

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ — эти функции поиска используются наиболее часто.

Формулы массива — примеры формул

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

Источник

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

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

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

Виды массивов функций Excel

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

В зависимости от расположения элементов различают массивы:

Одномерные массивы бывают:

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

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

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

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

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

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» — ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

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

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

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

Рассмотрим ее синтаксис:

Функции работы с массивами Excel

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как работать с массивами функций в Excel

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

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

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

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

В принципе, массивом может считаться любая таблица или диапазон.

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

Виды массивов функций в Excel

В целом, можно перечислить следующие виды массивов функций в Excel:

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

В свою очередь, одномерные массивы можно разделить на горизонтальные и вертикальные. Первые включают ячейки одного ряда, а вторые – колонки.

Формулы массива позволяют обрабатывать информацию из всего разнообразия данных.

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

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

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

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

Затем ставим курсор в строку формул, и там записываем =C3:C8*D3:D8.

Превращаем формулу в формат массива. Нужно нажать Ctrl + Shift + Enter.

После этого мы получаем таблицу с готовыми промежуточными итогами.

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

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

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

5

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

По итогу, получаем следующий результат.

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

Давайте более подробно рассмотрим синтаксис этой формулы.

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

Составные части формул массива:

Чтобы было проще понять, вот небольшой рисунок.

Анализ данных с помощью формулы массива

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

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

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

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

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

В результате получится формула, приведенная на скриншоте.

В ней используется 3 множителя. Первый проверяет менеджеров. Второй – товары. А третий – непосредственно определяет сумму заказа.

Как работает эта формула? Да очень просто. Ее алгоритм следующий:

Сначала Excel проверяет всех менеджеров и товары, соответствуют ли они заданным критериям. Если да, функция возвращает значение 1, если нет – 0.

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

Пример: товарный чек

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

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

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

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

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

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

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

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

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

Важно! Эти скобки не являются текстовыми. Их нельзя вводить вручную. Если потребуется редактирование формулы, то в конце ввода опять нужно нажимать комбинацию Ctrl+Shift+Enter вместо простого нажатия клавиши «Ввод».

Пример: меняем местами строки и столбцы

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

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

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

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

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

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

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

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

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

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

Рассмотрим наиболее часто используемые функции массивов.

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

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

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

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

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

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

Страницы работы

Содержание работы

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

Рассмотрим один важный ас­пект применения функций, работающих с массивами, а именно, подве­дение сложных итогов. Обычно в таблицах требуется подсчитать сум­му или количество элементов с некоторыми общими признаками. Ис­пользование функций СУММЕСЛИ() и СЧЕТЕСЛИ() ограничено воз­можностью анализа в них только одного единственного условия, кото­рое можно внести в качестве аргумента, а также ограниченности ссы­лок на клетки. Ввиду этого, если нужно сделать сложную выборку, они нам не помогут.

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

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

Похожие материалы

Информация о работе

Полный список ВУЗов

Чтобы распечатать файл, скачайте его (в формате Word).

Описание ограничений для работы с массивами в Excel

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Аннотация

В версиях Microsoft Excel, перечисленных в разделе «Применяется к», в разделе Справка «Спецификации вычислений» перечислены ограничения для работы с массивом. В этой статье описываются ограничения массивов в Excel.

Дополнительная информация

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

доступная память;

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

Правило «весь столбец»

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

В столбце в Microsoft Office Excel 2003 и более ранних версиях Excel имеется 65 536 ячеек. В столбце Excel 2007 Microsoft Office 1 048 576 ячеек.

Формулы максимального массива

В Excel 2003 и более ранних версиях Excel одна таблица может содержать максимум 65 472 формул массива, которые относятся к другому листа. Если вы хотите использовать больше формул, разделите данные на несколько таблиц, чтобы было меньше 65 472 ссылок на одну таблицу.

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

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

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

Ниже приводится список примеров формул массива. Чтобы использовать эти примеры, создайте новую книгу и введите каждую формулу в качестве формулы массива. Для этого введите формулу в панели формул и нажмите кнопку CTRL+SHIFT+ENTER, чтобы ввести формулу.

Excel 2007

A1: =SUM (IF(B1:B1048576=0,1,0))

Формула в ячейке A1 возвращает результат 1048576. Этот результат является правильным.

Формула в ячейке A2 возвращает результат 1048576. Этот результат является правильным.

A3: =SUM (IF(B1:J1048576=0,1,0))

Формула в ячейке A3 возвращает результат 9437184. Этот результат является правильным.

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

Формула в ячейке A4 возвращает результат 9437184. Этот результат является правильным.

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

A5: =SUM (IF(B1:DD1048576=0,1,0))

При вводе этой формулы в ячейке A5 вы можете получить одно из следующих сообщений об ошибке:

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

Чтобы определить уникальный номер, связанный с получаемым сообщением, нажмите кнопку CTRL+SHIFT+I. В нижнем правом углу этого сообщения отображается следующее число:

101758

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

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

После пересчета результатов Excel отвечает так, как ожидалось. Формула в ячейке A5 возвращает значение 0 (ноль).

Excel 2003 и более ранние версии Excel

A1: =SUM (IF(B1:B65535=0,1,0))

Формула в ячейке A1 возвращает результат 65535. Этот результат является правильным.

Формула в ячейке A2 возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

Формула в ячейке A3 возвращает результат 589815. Этот результат является правильным.

Для вычисления результата может потребоваться много времени, так как формула проверяет почти 600 000 ячеек.

Как и формула в ячейке A2, формула в ячейке A4 возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

A5: =SUM (IF(B1:DD65535=0,1,0))

При вводе формулы в ячейке A5 вы можете получить одно из следующих сообщений об ошибке:

Не хватает памяти. Продолжить без отмены?

Не хватает памяти.

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

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

После пересчета результатов Excel отвечает так, как ожидалось. Формула в ячейке A5 возвращает значение 0 (ноль).

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

Следует помнить следующие факты о функциях.

Источник