Что такое массивы в эксель

Работа с массивами функций в 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 произвел попарное умножение элементов массивов 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

Что такое динамические массивы

В сентябре 2018 года Microsoft выпустила обновление, которое добавляет в Microsoft Excel совершенно новый инструменты: динамические массивы (Dynamic Arrays) и 7 новых функций для работы с ними. Эти вещи, без преувеличения, совершенно кардинальным образом меняет всю привычную технику работы с формулами и функциями и касаются, буквально, каждого пользователя.

Рассмотрим простой пример, чтобы объяснить суть.

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

Во всех прошлых версиях Excel после нажатия на Enter мы бы получили содержимое только одной первой ячейки B2. А как иначе?

Ну, или можно было бы завернуть этот диапазон в какую-нибудь аггрегирующую функцию типа =СУММ(B2:C4) и получить по нему общий итог.

Теперь всё по-другому.

Это не магия, а новые динамические массивы, которые теперь есть в Microsoft Excel. Добро пожаловать в новый мир 🙂

Особенности работы с динамическими массивами

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

При этом ссылаться на эти «дочерние» ячейки в других формулах мы можем совершенно спокойно:

Если копировать первую ячейку массива (например из G4 в F8), то и и весь массив (его ссылки) сдвинется в том же направлении, как и в обычных формулах:

Если вам нужно сослаться где-нибудь еще на листе на созданный динамический массив, то можно использовать спецсимвол # («решётка») после адреса его ведущей ячейки:

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

Ошибки динамических массивов

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

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

Динамические массивы и «умные» таблицы

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

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

Динамические массивы и другие функции Excel

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

Транспонирование

Таблица умножения

Этот пример я обычно приводил, когда меня просили наглядно показать преимущества формул массива в Excel. Теперь чтобы посчитать всю таблицу Пифагора достаточно встать в первую ячейку B2, ввести туда формулу перемножающую два массива (вертикальный и горизонтальный набор чисел 1..10) и просто нажать на Enter :

Склейка и преобразование регистра

Вывод Топ-3

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

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

ВПР извлекающая сразу несколько столбцов

Функция СМЕЩ (OFFSET) возвращающая динамический массив

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

Давайте разберём её аргументы:

Новые функции для динамических массивов

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

Выводы

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

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

Нашлось и некоторое количество минусов :

Само-собой, это еще не конец и, я уверен, Microsoft продолжит совершенствовать этот механизм в будущем.

Где скачать?

И, наконец, главный вопрос 🙂

Microsoft впервые анонсировало и показало превью динамических массивов в Excel еще в сентябре 2018 года на конференции Ignite. В последующие несколько месяцев происходило тщательное тестирование и обкатка новых возможностей сначала на кошках сотрудниках самой Microsoft, а потом на добровольцах-тестировщиках из круга Office Insiders. В этом году обновление, добавляющее динамические массивы стали постепенно раскатывать уже по обычным подписчикам Office 365. Я, например, получил его только в августе с моей подпиской Office 365 Pro Plus (Monthly Targeted).

Если в вашем Excel ещё нет динамических массивов, а поработать с ними очень хочется, то есть следующие варианты:

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

Николай Спасибо.
Достучался через IT до Microsoft, сказали что для нас добавление новых функций запланировано на конец Q1’2020.

Теперь в томительном ожидании.

Я работаю в крупной международной компании и от Microsoft для нас делается отдельная локализация office.
Которая сильно запаздывает и пропускает некоторые версии.

Например мы с версии 19.02 сразу идем в 19.08, а потом 19.09 которую нам должны накатить только в конце июня 2020.

Николай,
Октябрь 2020 замечательный месяц, нам наконец то накатили обновление в котором есть динамические массивы.
Была 1908 и сейчас нам поставили 2002.
В итоге вместо Q1’20 все сместилось на Q4’20

PS: Немного поправлю функция «НАИБОЛЬШИЙ» в английской версии «LARGE»
PPS: Да версия конечно же у нас не отдельная

Обновления приходят (чем левее, тем раньше) : Monthly (Targeted) > Monthly > Semi-Annual (Targeted) > Semi-Annual. Судя по ответу Николая выше, ваша версия и не должна иметь ничего нового.

Здравствуйте!
Можно собрать одну таблицу с нескольких листов при помощи динамических массивов?

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

Источник

Массивы Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При стандартных вычислениях формула составлялась бы так: =B2/C2*100. Затем ее необходимо протянуть. Мы получили готовый результат.

Повторим расчет, только с использованием массивов.

Сначала выделяем ячейки, в которых необходимо произвести расчет. Далее записываем в строку формул: =B2:B5/C2:C5*100, где “B2:B5” диапазон всех ячеек для литража, “C2:C5” диапазон всех ячеек для расхода. На данный момент это именно диапазоны, чтобы они превратились в массивы, нажмите одновременно клавиши Ctrl + Shift + Enter. Формула автоматически будет заключена в фигурные скобки, а расчет появиться во всех предварительно выделенных ячейках. Результат тот же, что и в первом варианте.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Здесь все просто, программа умножила каждый элемент массива на 5, затем их просуммировала.

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

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

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

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

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

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

Источник