Excel

Массив данных в excel: Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК

Содержание

Массивы Excel

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

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

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

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

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

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

НО! Чтобы не вносить путаницу в определения функций и т.п. данные понятия необходимо разделять, т.к. приложение по-разному обрабатывает диапазоны и массивы.

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

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

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

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

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

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

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

На рисунке ниже представлены оба вида массив

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

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

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

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

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

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

1

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

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

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

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

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

2

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

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

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

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

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

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

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

3

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

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

4

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

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

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

5

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

  1. Выделяем ту ячейку, которая будет содержать результат вычисления по формуле массива.
  2. Вводим туда формулу. В нашем случае мы будем суммировать значения из целевого диапазона, поэтому используем формулу
    СУММ(C3:C8*D3:D8).
  3. С использованием комбинации Ctrl + Shift + Enter осуществляем трансформацию стандартной формулы в ту, которая работает с массивами данных.

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

6

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

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

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

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

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

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

7

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

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

17

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

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

18

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

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

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

19

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

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

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

    20

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

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

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

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

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

8

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

9

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

10

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

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

11

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

12

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

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

13

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

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

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

14

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

После этого надо ввести формулу =ТРАНСП, введя в качестве аргумента функции A1:B8.

15

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

16

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

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

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

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

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

  1. Найти кнопку «Отмена», расположенную слева от строки формул. Еще один вариант – нажатие клавиши Esc, которая выполняет ту же операцию. Во всех случаях блокировка будет снята и все операции можно выполнять заново. 
  2. Снова ввести формулу массива с тем же диапазоном.

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

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

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

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

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

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

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

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

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

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

VBA Excel. Диапазон ячеек и массив (обмен значениями)

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


Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.

Копирование значений из диапазона ячеек в массив

Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):

Dim a As Variant

a = Range(«A1:C3»)

VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).

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

Dim a() As Variant

a = Range(«A1:C3»).Value

Стоит отметить, что для копирования значений из диапазона ячеек в массив можно использовать только обычную переменную или динамический массив универсального типа (Variant). VBA Excel автоматически преобразовывает их в двумерный массив. Если объявить двумерный массив с указанной заранее размерностью, использовать его не получится, будет сгенерирована ошибка с сообщением: Can’t assign to array (Нельзя назначать массив).

Копирование значений из массива в диапазон ячеек

Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):

Range(«A6:F15») = a

‘или

Range(«A6:F15»).Value = a

‘где a — переменная двумерного массива

Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).

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

Простейшие примеры обмена значениями

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

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

Sub Test1()

Dim a(2, 2) As Variant

a(0, 0) = «телепузик»

a(0, 1) = «журналист»

a(0, 2) = «ящерица»

a(1, 0) = «короед»

a(1, 1) = «утенок»

a(1, 2) = «шмель»

a(2, 0) = 200

a(2, 1) = 300

a(2, 2) = 400

Range(«A1:C3»).Value = a

End Sub

В данном случае переменная массива не обязательно должна быть универсального типа (As Variant), например, если бы в нее записывались только текстовые данные, ее можно было бы объявить как строковую (As String), и все бы работало.

Пример 2

Объявление обычной переменной универсального типа, присвоение ей значений из диапазона ячеек «A1:C3», записанных кодом первого примера, и вставка этих значений из полученного двумерного массива в диапазон «D10:F12»:

Sub Test2()

Dim a As Variant

a = Range(«A1:C3»)

Range(«D10:F12») = a

End Sub

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

Пример 3

Допустим, на рабочем листе «Лист1» в ячейках «A1:A5» записано количество какого-то товара, а в ячейках «B1:B5» — его цена. Необходимо к этой информации добавить сумму каждого товара, умножив количество на цену, и перенести данные на «Лист2».

Sub Test3()

Dim a As Variant, i As Long

  a = Лист1.Range(«A1:C5»)

    For i = 1 To 5

      a(i, 3) = a(i, 1) _

      * a(i, 2)

    Next

  Лист2.Range(«A1:C5») = a

End Sub

Массив создан сразу с размерностью 5×3 с элементами под суммы. Даже если на первом листе в ячейках «C1:C5» есть какие-то значения, в массиве они будут перезаписаны результатами вычислений.

Массивы констант в EXCEL. Примеры и описание

Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, для чисел {1:2:3:4:5} или для текстовых значений {«Север»:»ЮГ»:»Восток»:»Запад»}.

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

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

Перед созданием Массива констант посмотрим повнимательнее на диапазон ячеек, содержащий обычные значения (см. Файл примера )

  • в диапазон ячеек A 1: A 5 введите, например, 5 последовательных чисел от 1 до 5.

  • в ячейке B1 введите формулу =A1:A5 ;

  • в Строке формул выделите A1:A5 и нажмите клавишу F9 ;
  • получим некую запись {1:2:3:4:5}, представляющую собой набор значений из диапазона A1:A5

Этот набор значений, как и формулы массива , обрамлен в фигурные скобки, сами значения разделены двоеточиями. Если бы значения были размещены в строке (в диапазоне A1:E1 ), а не в столбце, то значения были бы разделены точкой с запятой {1;2;3;4;5}.

Создадим константу массива в ячейке B2 . Для этого введем в ячейку выражение ={1:2:3:4:5} и нажмем ENTER . Массив значений не заключается в скобки автоматически, как формулы массива после нажатия CTRL+SHIFT+ENTER . Это необходимо делать вручную. В ячейке отразится только первое значение массива, т.е. 1.

Обычно массив значений не вводят в одну ячейку, т.к. в этом случае невозможно вытащить отдельные значения. Чтобы отобразить все значения нашего массива значений нужно выделить 5 ячеек в столбце (например, B1:B5 ), в Строке формул ввести выражение ={1:2:3:4:5} и нажать CTRL+SHIFT+ENTER .

Теперь попробуем удалить один элемент массива, например из ячейки B 3 . Получим предупреждение « Нельзя изменить часть массива » — это определенного вида защита массива.

Чтобы избежать утомительного ввода последовательных чисел для вертикального массива констант можно воспользоваться формулой =СТРОКА(1:5) . Записав ее любой пустой ячейке, выделите ее в строке формул и нажмите F9 , а затем нажмите ENTER . Получите массив констант {1:2:3:4:5} . Скопируйте содержимое ячейки в буфер обмена ( CTRL+C ), затем выделите вертикальный диапазон ячеек, соответствующий размерности массива, вставьте в активную ячейку содержимое Буфера обмена и нажмите CTRL+SHIFT+ENTER.

Чтобы избежать утомительного ввода последовательных чисел для горизонтального массива констант используйте формулу =ТРАНСП(СТРОКА(1:5)) или =СТОЛБЕЦ(A:E) . Получите массив констант ={1;2;3;4;5} . Значения массива будут разделены точкой с запятой.

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


А. Умножение векторов (столбец на столбец, строку на строку) С помощью формулы массива умножим столбец значений ( B 2: B 6 ) на массив констант {1:2:3:4:5} и просуммируем. Массив констант записан в «вертикальном» виде с использованием двоеточия, т.е. также представляет собой столбец. Размерности столбца и массива должны совпадать.

=СУММ(B2:B6*{1:2:3:4:5})

После ввода формулы необходимо нажать CTRL+SHIFT+ ENTER .

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

=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

В последнюю очередь выполняется сложение значений при помощи функции СУММ() .

Б. Проверка значений Проверим, равно ли значение в ячейке А1 одному из определенных значений: 4, 6 или 9.

=ИЛИ(A1={4;6;9})

После ввода формулы нет необходимости нажимать CTRL+SHIFT+ENTER . Такая запись может существенно сократить время создания формулы по сравнению с использованием вложенных функций ЕСЛИ() .

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

Массиву констант можно присвоить Имя . Обычно так поступают с константами, образующими группу однотипных значений, например последовательности с конечным количеством элементов ={1:2:3} .

Чтобы присвоить массиву констант имя необходимо сделать следующее:

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя .
  • В поле Имя введите Массив123 .
  • В поле Диапазон введите массив констант (не забудьте ввести скобки вручную), например {1:2:3} ;
  • Нажмите кнопку ОК.

Пример, найдем сумму 3-х наибольших значений , записав формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)) .

Предполагается, что в диапазоне A1:A10 имеется список числовых значений.

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

Чтобы создать двумерный массив констант необходимо сделать следующее:

  • выделите в книге диапазон ячеек из четырех столбцов и трех строк ( A1:D3 ).
  • в активной ячейке ( А1 ), в Cтроке формул введите выражение ={1;2;3;4: 5;6;7;8: 9;10;11;12}
  • нажмите сочетание клавиш CTRL+SHIFT+ ENTER

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

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

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

Присвоим Имя Месяцы двумерному массиву:

={1;»январь»:2;»февраль»:3;»март»:4;»апрель»:5;»май»:6;»июнь»: 7;»июль»:8;»август»:9;»сентябрь»:10;»октябрь»:11;»ноябрь»:12;»декабрь»}

Чтобы избежать утомительного ввода этого массива создайте два столбца, один с числами от 1 до 12, другой с названиями месяцев (см. статью Текстовые последовательности ). Затем, в Строке формул введите ссылку на этот диапазон, нажмите F9 и скопируйте в Буфер обмена .

Теперь записав формулу =ВПР(A2;Месяцы;2) , где в ячейке A2 — номер месяца, получим желаемый результат.

Для желающих получить дополнительную информацию о константах массива — ]]> ссылка на статью сайта Microsoft на английском языке ]]> .

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

Управление массивами в 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: примеры, использование с ПОИСКПОЗ

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

Описание функции ИНДЕКС

ИНДЕКС входит в категорию операторов “Ссылки и массивы”, причем имеет два разных набора аргументов:

1. Для массивов

Формула функции в данном случае выглядит так:

=ИНДЕКС(Массив;Номер_строки;Номер_столбца).

Может быть заполнен один из двух аргументов: “Номер_строки” или “Номер_столбца”. Все зависит от того, с каким массивом приходиться иметь дело. Если, например, массив занимает только одну строку, то аргумент “Номер_строки” не актуален, и заполняется только “Номер_столбца”. Для массива, занимающего всего лишь один столбец все аналогично. Здесь есть один нюанс – указывать нужно номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри заданного массива.

2. Для ссылок

Формула функции выглядит следующим образом:

=ИНДЕКС(Ссылка;Номер_строки;Номер_столбца;[Номер_области]).

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

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

Функция ИНДЕКС для массивов

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

Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.

  1. Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).
  2. В открывшемся окне вставки функции выбираем категорию “Ссылки и массивы” (или “Полный алфавитный перечень”), отмечаем строку “ИНДЕКС” и жмем OK.
  3. Далее программа предложит на выбор один из двух наборов аргументов, о которых мы писали выше (для массива или для ссылок). В рамках поставленной задачи выбираем первый вариант и жмем OK.
  4. Теперь нам нужно заполнить аргументы функции:
    • в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
    • в аргументе “Номер_строки” пишем цифру 5, так как согласно поставленной задаче требуется отобрать 5-ую позицию из списка.
    • в значении аргумента “Номер_столбца” пишем число 1, потому что наименования позиций находятся в первом столбце рассматриваемого массива.
    • по готовности жмем OK.
  5. В выбранной ячейке отобразится результат согласно заданным условиям отбора в аргументах функции. В нашем случае – это содержимое ячейки, находящейся в 1-ом столбце и 5-ой строке выделенного массива.

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

  1. В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.
  2. Нажав кнопку OK мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формула будут отличаться от первоначального варианта.

Функция ИНДЕКС для ссылок

А сейчас давайте разберемся, как можно работать с несколькими таблицами, используя функцию ИНДЕКС. В этом случае нам понадобится список аргументов для ссылок с полем “Номером_области”.

Допустим, у нас есть 4 таблицы. В каждой из них представлена информация по продажам за определенный период времени (1, 2, 3 и 4 кварталы).

Нам нужно узнать продажи 4-ой позиции (“Системный блок”) за второй квартал в штуках.

  1. Встаем в ячейку, куда планируем выводить итоговый результат и жмем кнопку “Вставить функцию” (fx).
  2. Выбираем функцию ИНДЕКС и жмем OK.
  3. Во вспомогательном окошке останавливаемся на втором варианте (для ссылки) и жмем кнопку OK.
  4. Отобразится окно с аргументами функции для заполнения:
    • поле “Ссылка” заполняется таким же образом, как и аргумент “Массив” в примере выше (вручную или методом выделения в самой таблице). Единственная разница заключается в том, что в данном случае нам нужно указать вместо одного сразу 4 диапазона ячеек, перечислив их через точку с запятой. Т.е. указываем первую область, ставим знак “;”, затем указываем вторую область и т.д. Когда все будет сделано, ставим открывающую и закрывающую скобки в начале и конце ссылки, соответственно.
    • в значении аргумента “Номер_строки” пишем число 4, так как нас интересуют данные по четвертой позиции.
    • в поле “Номер_столбца” пишем число 3, так как нам на нужны продажи в шт., а это третий по счету столбец в выделенных диапазонах.
    • в поле аргумента “Номер области” указываем число 2, так как требуется отобразить данные по второму кварталу, что соответствует второму отмеченному диапазону в аргументе “Ссылка”.
    • когда все готово, жмем OK.
  5. В выбранной ячейке с функцией отобразится требуемый результат согласно заданным условиям в аргументах.

Использование с оператором СУММ

Оператор ИНДЕКС можно использовать вместе с функцией СУММ, формула которой выглядит так:

=СУММ(Адрес_массива).

Если применить функцию суммирования к нашей таблице, например, к столбцу с суммой, формула будет выглядет так: =СУММ(D2:D9).

Мы можем слегка изменить формулу, добавив в нее оператор ИНДЕКС:

  1. В качестве первого аргумента функции СУММ оставляем координаты ячейки, которая является началом диапазона суммирования.
  2. Второй аргумент (крайняя нижняя ячейка диапазона) будем задавать с помощью оператора ИНДЕКС. Перейдя в режим редактирования формулы (двойным щелчком мыши по ячейке с формулой или просто кликнув по строке формул, предварительно выбрав нужную ячейку), приводим ее к следующему виду с учетом нашей таблицы:
    =СУММ(D2:ИНДЕКС(D2:D9;8)).Цифра 8 означает, что мы ограничиваем указанный диапазон между ячейками D2 и D9 позицией под номером 8. Мы можем задать любую цифру (больше или равно 0), не превышающую общее количество всех позиций в выбранном массиве.
  3. Нажав Enter мы получим результат в выбранной ячейке.

Сочетание с функцией ПОИСКПОЗ

Теперь давайте перейдем к более сложным задачам, выполняемым с помощью комбинированного использования оператора ИНДЕКС с другими функциями, например, с ПОИСКПОЗ (довольно часто эти функции используются вместе).

Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.

Формула функции выглядит следующим образом:

=ПОИСКПОЗ(Искомое_значение,Просматриваемый_массив,[Тип_сопоставления]).

Давайте “пробежимся” по аргементам функции:

  • Искомое значение – то значение, которе мы хотим найти в выделенном диапазоне;
  • Просматриваемый массив – область ячеек, в которой мы будем искать искомое значение;
  • Тип сопоставления – с помощью данного аргумента (не является обязательным) можно задать точность поиска.

Использование ПОИСКПОЗ позволяет автоматически заполнить аргументы “Номер строки” и/или “Номер столбца” функции ИНДЕКС.

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

Наша задача – используя ИНДЕКС и ПОИСКПОЗ добавить в ячейку со стоимостью (G2) такую функцию, которая будет выводить конкретное значение в зависимости от того, что будет указано в ячейке с наименованием рядом.

  1. Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
  2. Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
  3. Выбираем функцию ИНДЕКС из списка операторов.
  4. Выбираем список аргументов для массива и жмем OK.
  5. Приступаем к заполнению аргументов:
    • в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
    • в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
      • перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
      • в раскрывшемся перечне выбираем пункт “Другие функции”.
      • в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
      • в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения. В аргумент “Тип_сопоставления” указываем цифру 0.
    • Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.
    • появится снова список аргументов функции ИНДЕКС. Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK.Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
  6. Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
  7. Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
  8. Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.

Заключение

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

Функция ИНДЕКС — служба поддержки Office

Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.

Есть два способа использовать функцию ИНДЕКС:

  • Если вы хотите вернуть значение указанной ячейки или массива ячеек, см. Форма массива.

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

Форма массива

Описание

Возвращает значение элемента в таблице или массиве, выбранном индексами номеров строк и столбцов.

Используйте форму массива, если первый аргумент INDEX является константой массива.

Синтаксис

ИНДЕКС (массив; номер_строки; [номер_столбца])

Форма массива функции ИНДЕКС имеет следующие аргументы:

  • массив Обязательно. Диапазон ячеек или константа массива.

    • Если массив содержит только одну строку или столбец, соответствующий аргумент row_num или column_num является необязательным.

    • Если массив имеет более одной строки и более одного столбца и используется только row_num или column_num, INDEX возвращает массив всей строки или столбца в массиве.

  • row_num Обязательный, если не указан column_num. Выбирает строку в массиве, из которой нужно вернуть значение. Если row_num опущен, column_num является обязательным.

  • column_num Необязательно. Выбирает столбец в массиве, из которого нужно вернуть значение. Если column_num опущен, row_num обязателен.

Примечания

  • Если используются аргументы row_num и column_num, INDEX возвращает значение в ячейке на пересечении row_num и column_num.

  • row_num и column_num должны указывать на ячейку в массиве; в противном случае ИНДЕКС возвращает # ССЫЛКУ! ошибка.

  • Если вы установите row_num или column_num равным 0 (ноль), INDEX вернет массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращаемые в виде массива, введите функцию ИНДЕКС как формулу массива.

    Примечание: Если у вас текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива.В противном случае формулу необходимо ввести как формулу устаревшего массива, сначала выбрав диапазон вывода, введите формулу в верхнюю левую ячейку диапазона вывода, а затем нажмите CTRL + SHIFT + ВВОД для подтверждения. Excel вставляет фигурные скобки в начало и конец формулы за вас. Дополнительные сведения о формулах массива см. В разделе Рекомендации и примеры формул массива.

Примеры

Пример 1

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

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

Данные

Данные

Яблоки

Лимоны

Бананы

Груши

Формула

Описание

Результат

= ИНДЕКС (A2: B3,2,2)

Значение на пересечении второй строки и второго столбца в диапазоне A2: B3.

Груши

= ИНДЕКС (A2: B3,2,1)

Значение на пересечении второй строки и первого столбца в диапазоне A2: B3.

Бананы

Пример 2

В этом примере функция ИНДЕКС в формуле массива используется для поиска значений в двух ячейках, указанных в массиве 2×2.

Примечание: Если у вас текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как формулу устаревшего массива, сначала выбрав две пустые ячейки, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите CTRL + SHIFT + ВВОД для подтверждения. Excel вставляет фигурные скобки в начало и конец формулы за вас.Дополнительные сведения о формулах массива см. В разделе Рекомендации и примеры формул массива.

Формула

Описание

Результат

= ИНДЕКС ({1,2 ; 3,4}, 0,2)

Значение, найденное в первой строке, втором столбце массива.Массив содержит 1 и 2 в первой строке и 3 и 4 во второй строке.

2

Значение, найденное во второй строке, втором столбце массива (тот же массив, что и выше).

4

Верх страницы


Референтная форма

Описание

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

Синтаксис

ИНДЕКС (ссылка; номер_строки; [номер_столбца]; [номер_площади])

Справочная форма функции ИНДЕКС имеет следующие аргументы:

  • ссылка Обязательно. Ссылка на один или несколько диапазонов ячеек.

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

    • Если каждая область в ссылке содержит только одну строку или столбец, аргумент row_num или column_num, соответственно, является необязательным. Например, для ссылки на одну строку используйте ИНДЕКС (ссылка ,, номер_столбца).

  • row_num Обязательно. Номер строки в ссылке, из которой нужно вернуть ссылку.

  • column_num Необязательно. Номер столбца в ссылке, из которого нужно вернуть ссылку.

  • area_num Необязательно. Выбирает диапазон в ссылке, из которого нужно вернуть пересечение row_num и column_num. Первая выбранная или введенная область имеет номер 1, вторая — 2 и так далее. Если area_num опущено, INDEX использует область 1.Все перечисленные здесь области должны быть расположены на одном листе. Если вы укажете области, которые не находятся на одном листе друг с другом, это вызовет ошибку #VALUE! ошибка. Если вам нужно использовать диапазоны, которые расположены на разных листах друг от друга, рекомендуется использовать форму массива функции ИНДЕКС и использовать другую функцию для вычисления диапазона, составляющего массив. Например, вы можете использовать функцию ВЫБОР, чтобы вычислить, какой диапазон будет использоваться.

Например, если Ссылка описывает ячейки (A1: B4, D1: E4, G1: h5), area_num 1 — это диапазон A1: B4, area_num 2 — это диапазон D1: E4, а area_num 3 — это диапазон G1: h5.

Замечания

  • После того, как ссылка и area_num выбрали конкретный диапазон, row_num и column_num выбирают конкретную ячейку: row_num 1 — это первая строка в диапазоне, column_num 1 — это первый столбец и так далее. Ссылка, возвращаемая INDEX, является пересечением row_num и column_num.

  • Если вы установите row_num или column_num равным 0 (ноль), INDEX вернет ссылку для всего столбца или строки соответственно.

  • row_num, column_num и area_num должны указывать на ячейку в пределах ссылки; в противном случае ИНДЕКС возвращает # ССЫЛКУ! ошибка. Если row_num и column_num опущены, INDEX возвращает область, указанную в ссылке area_num.

  • Результат функции ИНДЕКС является ссылкой и интерпретируется как таковой другими формулами. В зависимости от формулы возвращаемое значение ИНДЕКС может использоваться как ссылка или как значение.Например, формула CELL («ширина», ИНДЕКС (A1: B2,1,2)) эквивалентна CELL («ширина», B1). Функция ЯЧЕЙКА использует возвращаемое значение ИНДЕКС в качестве ссылки на ячейку. С другой стороны, такая формула, как 2 * ИНДЕКС (A1: B2,1,2), переводит возвращаемое значение ИНДЕКС в число в ячейке B1.

Примеры

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

Фрукты

Цена

Счетчик

Яблоки

0 руб.69

40

Бананы

$ 0,34

38

Лимоны

0 руб.55

15

Апельсины

$ 0,25

25

Груши

0 руб.59

40

Миндаль

$ 2,80

10

Кешью

$ 3.55

16

Арахис

$ 1,25

20

Грецкие орехи

$ 1.75

12

Формула

Описание

Результат

= ИНДЕКС (A2: C6, 2, 3)

Пересечение второй строки и третьего столбца в диапазоне A2: C6, который является содержимым ячейки C3.

38

= ИНДЕКС ((A1: C6, A8: C11), 2, 2, 2)

Пересечение второй строки и второго столбца во второй области A8: C11, которая является содержимым ячейки B9.

1,25

= СУММ (ИНДЕКС (A1: C11, 0, 3, 1))

Сумма третьего столбца в первой области диапазона A1: C11, которая является суммой C1: C11.

216

= СУММ (B2: ИНДЕКС (A2: C6, 5, 2))

Сумма диапазона, начинающегося с B2 и заканчивающегося на пересечении пятой строки и второго столбца диапазона A2: A6, который представляет собой сумму B2: B6.

2.42

Верх страницы

См. Также

Функция ВПР

Функция ПОИСКПОЗ

КОСВЕННАЯ функция

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

Функции поиска и ссылки (ссылка)

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

  • 4 минуты на чтение
  • Применимо к:
    Excel 2007, Excel 2003, Excel 2002

В этой статье

Примечание

Office 365 профессиональный плюс переименовывается в Microsoft 365 Apps для предприятий .Дополнительные сведения об этом изменении см. В этом сообщении в блоге.

Сводка

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

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

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

Доступная память

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

Правило «всей колонки»

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

Примечание

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

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

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

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

  • 65 472 формул массива, которые ссылаются на Sheet2
  • 65 472 формул массива, относящихся к Sheet3
  • 65 472 формул массива, относящихся к Sheet4

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

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

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

Excel 2007

  • A1: = СУММ (ЕСЛИ (B1: B1048576 = 0,1,0))

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

  • A2: = СУММ (ЕСЛИ (B: B = 0,1,0))

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

  • A3: = СУММ (ЕСЛИ (B1: J1048576 = 0,1,0))

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

    Примечание

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

  • A4: = СУММ (ЕСЛИ (B: J = 0,1,0))

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

    Примечание

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

  • A5: = СУММ (ЕСЛИ (B1: DD1048576 = 0,1,0))

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

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

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

    101758

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

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

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

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

  • A1: = СУММ (ЕСЛИ (B1: B65535 = 0,1,0))

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

  • A2: = СУММ (ЕСЛИ (B: B = 0,1,0))

    Формула в ячейке A2 возвращает # ЧИСЛО! ошибка, поскольку формула массива относится ко всему столбцу ячеек.

  • A3: = СУММ (ЕСЛИ (B1: J65535 = 0,1,0))

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

    Примечание

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

  • A4: = СУММ (ЕСЛИ (B: J = 0,1,0))

    Как и формула в ячейке A2, формула в ячейке A4 возвращает # ЧИСЛО! ошибка, поскольку формула массива относится ко всему столбцу ячеек.

  • A5: = СУММ (ЕСЛИ (B1: DD65535 = 0,1,0))

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

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

    Недостаточно памяти.

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

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

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

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

  • ЛИНЕЙН ()
  • MDETERM ()
  • MINVERSE ()
  • MMULT ()
  • СУММ (ЕСЛИ ())
  • СУММПРОИЗВ ()
  • ТРАНСПОРТ ()
  • ТРЕНД ()

Примечание

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

  • Если какие-либо ячейки в массиве пусты или содержат текст, МИНВЕРС возвращает # ЗНАЧ! Значение ошибки .
  • MINVERSE также возвращает #VALUE! Значение ошибки , если в массиве не одинаковое количество строк и столбцов.
  • MINVERSE возвращает #VALUE! ошибка, если возвращаемый массив превышает 52 столбца на 52 строки.
  • Функция ММУЛЬТ возвращает # ЗНАЧ! , если вывод превышает 5460 ячеек.
  • Функция МДЕТЕРМ возвращает # ЗНАЧ! , если возвращаемый массив больше 73 строк на 73 столбца.

Как создать массив чисел с помощью функции ИНДЕКС в Excel

В этой статье мы узнаем, как создать список или массив чисел в Excel 2016.

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

Как решить проблему?

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

  1. Функция СТРОКА
  2. КОСВЕННАЯ функция

Общая формула:

ПРИМЕЧАНИЕ: Эта формула вернет список чисел от num1 до num2.Используйте Ctrl + Shift + Enter при использовании формулы с другими рабочими формулами.
Пример:

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

Сначала мы узнаем простой выход из этой задачи. Здесь формула генерирует массив чисел от 5 до 10.

Запишите формулу в ячейку D2.

Формула:

5: Start_num

10: end_num

Пояснение:

После того, как формула будет использована в ячейке, единственный выход ячейки будет показан как 5 на гифке ниже.Но массив лежит внутри ячейки. Когда я щелкнул формулу в строке формул и нажал F9 ​​, в ячейке отображается массив = {5; 6; 7; 8; 9; 10) , получившийся массив.

На гифке выше объясняется процесс получения массива {5; 6; 7; 8; 9; 10}

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

Прямой массив

Для первого массива мы будем использовать формулу с использованием общей формулы выше

Используйте формулу:


Пояснение:

После того, как формула будет использована в ячейке, единственный выход ячейки будет показан как 5 на гифке ниже.Но массив лежит внутри ячейки. Когда я щелкнул формулу в строке формул и нажал F9. , в ячейке отображается массив = {5; 6; 7; 8; 9; 10} , результирующий массив.

Microsoft Excel: новый динамический способ СОРТИРОВКИ массивов данных


В формуле = СОРТИРОВАТЬ (A3: B17) используются настройки по умолчанию «сортировка по» и «порядок сортировки»; таким образом, список отсортирован в алфавитном порядке. Синтаксис новой функции SORT: = SORT (array, [sort_index], [sort_order], [by_column]).Первый аргумент идентифицирует массив для сортировки. Все остальные аргументы необязательны. Второй аргумент определяет, по какому столбцу будет отсортирован массив. По умолчанию для [sort_index] установлено значение 1, поэтому формула SORT в приведенном выше примере приводит к сортировке массива по первому столбцу. Третий аргумент определяет, будет ли сортировка по возрастанию (введите 1) или по убыванию (введите -1). По умолчанию — по возрастанию. Поэтому, если вы хотите отсортировать по «Продажам», чтобы составить список продаж по штатам, ранжированный от самого высокого до самого низкого, вам нужно будет отсортировать по второму столбцу в порядке возрастания, в результате получится формула = СОРТИРОВАТЬ (A3: B17, 2, -1).

Четвертый аргумент [by_column] определяет, сортирует ли формула строки данных или столбцы данных. Поскольку гораздо чаще сортировать строки данных, как в примере слева, значение по умолчанию для четвертого аргумента — ЛОЖЬ.

Вот еще несколько комментариев о функциях динамических массивов:

1. Скоро в Office 365: ожидается, что функции динамических массивов будут выпущены для всех участников программы предварительной оценки Office и подписчиков на Office 365 в этом году, возможно, даже до того, как этот выпуск JofA попадет в почтовые ящики.Эта функция не была включена в приобретаемую версию Excel 2019 и не ожидается.

2. Ожидаемые вторичные результаты: Когда вы вводите формулу динамического массива, Excel будет динамически распределять результаты вниз и вправо по мере необходимости. Таким образом, вы должны убедиться, что достаточно пустых ячеек, расположенных вниз и вправо, для размещения результатов. Если текст или данные уже находятся в выходном диапазоне разлитого массива, Excel вернет #SPILL! ошибка, указывающая на то, что инструмент заблокирован.

3. Включение таблиц: При использовании формулы массива СОРТИРОВКА может оказаться полезным сначала преобразовать исходные данные в таблицу Excel (с помощью параметра меню Таблица вкладки Вставить ), пример которого показан на рисунке ниже. Обратите внимание, как формула массива в ячейке D26 ссылается на таблицу 2, которая изображена слева в столбцах A и B. Преимущество этого подхода состоит в том, что он позволяет автоматически обновлять результаты СОРТИРОВКИ при добавлении строк или столбцов в нижнюю часть таблицы 2.

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

формул массива в Excel |

  • Мы объясняем, как работать с формулами массива в Excel.
  • Мастер приветствия тремя пальцами CTRL + SHIFT + ENTER.
  • CTRL + / — удивительно эффективный ярлык.
  • Массив всегда легче расширить, чем сжать.
  • Согласованное форматирование обеспечивает очевидную визуальную подсказку при работе с массивами.

SIMM для Excel

SIMM для Excel — это надстройка, которая выполняет расчеты начальной маржи ISDA SIMM из Excel. Для начала мы предлагаем 14-дневную бесплатную пробную версию, а также примеры учебников. Эти инструменты позволяют согласовывать расчеты ISDA SIMM, а также выполнять предпродажную аналитику по всем портфелям. Это быстро, просто и надежно.

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

Возвращаемые результаты не обязательно являются отдельными значениями — мы возвращаем сетку результатов ( x строк на y столбец). Чтобы реализовать это в Excel, мы должны использовать «формулы массива».

Вот как работать с формулами массива в Excel.

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

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

Однако некоторые формулы не возвращают единственное значение. Они возвращают более одного значения. Таким образом, выходные данные Excel представляют собой прямоугольник значений ( x строк на y столбец). Большинство людей назовут это «матрицей», но Microsoft предпочитает называть их массивами. Понятия не имею, почему….

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

Ввод формулы массива

Ввод формулы массива в Excel немного отличается от привычных формул:

  1. Выберите область в электронной таблице, в которую вы хотите вернуть данные.
  2. Введите формулу, например SIMM_MARGIN (<Диапазон данных>).
  3. Нажмите CTRL + SHIFT + ENTER, чтобы подтвердить эту формулу (вместо простого нажатия ENTER). В результате формула будет заключена в фигурные скобки {}. Эти фигурные скобки показывают, как Excel распознает формулу массива.Их нельзя ввести вручную, они должны быть созданы нажатием CTRL + SHIFT + ENTER.
Выберите формулу AreaEnter, используя CTRL + SHIFT + ENTER

Мы называем CTRL + SHIFT + ENTER «приветствием тремя пальцами».

Работа с формулами массива

Формулы массива действуют немного иначе, чем другие формулы Excel. Вот что нужно запомнить:

Ввод формулы массива
  • Не забудьте вводить формулы, используя три пальца приветствия CTRL + SHIFT + ENTER.
Удаление формулы массива
  • Чтобы удалить формулу массива, необходимо выделить всю область электронной таблицы, в которую введен массив. CTRL + / — самый быстрый способ сделать это.
Монтаж
  • Сама формула массива всегда находится в верхнем левом углу диапазона. Это единственная ячейка в диапазоне, которую можно редактировать. Формула массива будет отображаться во всех остальных ячейках диапазона, но ее нельзя изменить ни в одной ячейке, кроме верхнего левого угла диапазона.
Изменение размера
  • Чтобы расширить диапазон, в который записывается формула массива, начните с формулы в верхнем левом углу. Выберите расширенный диапазон, в который вы хотите записать. Нажмите F2, чтобы изменить формулу. Затем используйте салют тремя пальцами, чтобы подтвердить новый, более крупный массив.
  • Чтобы уменьшить диапазон, в который записывается формула массива, вам придется удалить весь исходный диапазон (для выбора используйте CTRL + /). Затем выберите новый, меньший диапазон и повторно введите формулу.Это будет менее обременительно, если вы сначала скопируете существующую формулу как текстовую строку (выберите в строке формул и используйте CTRL + C):
CTRL + C, чтобы скопировать формулу как текст отсюда
Форматирование
  • Найдите время, чтобы помочь себе. Последовательно форматируйте области массива в электронной таблице, чтобы наглядно показать, где находятся формулы массива. Мы также выделяем верхнюю левую ячейку массива.
Привыкайте к всплывающим окнам
  • Даже самые продвинутые и осторожные мастера Excel иногда забывают, что они находятся в массиве.Это случается с лучшими из нас! К счастью, случаются действительно плохие записи, но вам нужно привыкнуть к разочаровывающему всплывающему окну:

Я всегда думаю: «Нет, это не нормально, Microsoft, это действительно расстраивает», поэтому предпочитаю нажать Escape, чтобы закрыть это сообщение.

Советы и хитрости

Вот четыре ярлыка, которые я считаю чрезвычайно полезными при использовании электронных таблиц с формулами массива:

1. CTRL + /

Выбирает текущий массив. Это ярлык для F5> Special> Current Array, показанный ниже:

2.CTRL + SHIFT + ВПРАВО

CTRL + SHIFT + RIGHT выделяет текущий массив вправо.

CTRL + SHIFT + RIGHT

3. CTRL + SHIFT + DOWN.

CTRL + SHIFT + DOWN подсвечивает текущий массив внизу.

CTRL + SHIFT + DOWN

4. F2 для редактирования, приветствие тремя пальцами для подтверждения

Нет необходимости выделять всю область массива, если вы хотите редактировать формулу. Отредактируйте верхнюю левую ячейку, нажав F2, затем подтвердите, используя приветствие тремя пальцами (CTRL + SHIFT + ENTER). Это автоматически обновит остальную часть массива.

Вкратце

  • Освойте приветствие тремя пальцами (CTRL + SHIFT + ENTER).
  • CTRL + / — ваш друг.
  • Если вы не уверены, насколько велик будет ваш возврат данных, сначала введите массив в небольшой области. Массивы легче расширять, чем сжимать.

Будьте в курсе нашей БЕСПЛАТНОЙ рассылки, подпишитесь Вот.

Связанные

Формула Excel: Создать массив чисел

Мы можем использовать функции СТРОКА и КОСВЕННО, чтобы создать массив чисел, например.г. {1; 2; 3; 4}. Функции в основном используются в формулах массива, требующих для обработки числового массива. В этом посте содержится руководство по созданию массива чисел с помощью функций СТРОКА и ДВССЫЛ.

Рисунок 1: Как создать массив чисел в Excel

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

{= СТРОКА (КОСВЕННАЯ (начало & ”:” и конец))}

Как работает формула

  • Функции СТРОКА и ДВССЫЛ являются фундаментальными, когда мы хотим создать массив чисел
  • Нам нужно указать начало и конец массива, как показано в примере выше
  • Затем в столбце результатов введите формулу; = СТРОКА (КОСВЕННАЯ (начало & ":" & конец)))
  • Затем нажмите «Enter»

Обратите внимание, что если длина столбца недостаточна, мы получаем только начальное значение в массиве.Чтобы получить массив, мы создаем еще один столбец; «Результат массива». Затем перенесите формулу на панель формул и щелкните функцию F9, чтобы получить результаты массива.

Понимание формулы

Строка, представляющая строки, составляет основу этой формулы. Чтобы создать массив из 5 чисел, нам нужно встроить строку в функцию ДВССЫЛ. Это можно сделать, как показано ниже:

= СТРОКА (КОСВЕННАЯ («1: 5»))

Здесь формула работает как:

  • КОСВЕННО интерпретирует это как диапазон 1: 5 (5 строк)
  • Функция ROW возвращает номер строки для каждой строки внутри массива

Пример

Рисунок 2: Создание массива чисел в Excel

В приведенном выше примере мы действуем следующим образом:

  • Укажите начальные и конечные данные на листе.Также укажите, где должен быть результат, а также результат массива.
  • В первой строке, где у нас есть данные, ниже в столбце результатов укажите формулу, например = СТРОКА (ДВССЫЛ (C2 & ”:” & D2))
  • Нажмите Enter, чтобы получить результат для первой строки.
  • Используйте клавишу F9, чтобы получить массив данных в столбце «результат массива».

Мгновенное соединение с экспертом через службу Excelchat

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

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

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