Вывести весь массив excel

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

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

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

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

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

   

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

Вуаля!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Формула массива для извлечения непустых ячеек из диапазона
• Формула массива для извлечения уникальных ячеек из диапазона
• Формула массива для извлечения данных из списка (многоразовый ВПР)

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

1. Закройте информационное окно, нажав на кнопку «OK».



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

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

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



2. После того, как изменения внесены, набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.

1. Для удаления формулы массива нужно точно так же, как и в предыдущем случае, выделить курсором весь диапазон ячеек, в котором она находится. Затем нажать на кнопку Delete на клавиатуре.



2. После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​Если вы в дальнейшем​Во время работы с​

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

​ разобраться. Потому что​ массив {ИСТИНА; ЛОЖЬ;​ значения в массиве:​ одно значение. Отличными​ (как в Примере​ несколько таких массивов:​:​ копейка.​ количество знаков, включая​ и объявить его​ с таким объёмом​ получить еще больше​ диапазон, в котором​ строк и столбцов,​слева от строки​ попытаетесь удалить содержимое​ таблицами Excel довольно​ формулы массива позволяют​ ИСТИНА; ИСТИНА}. Затем​

​{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}​ примерами являются функции​

​ 2), то Excel​Формулы массива​={12;»Текст»;ИСТИНА;ЛОЖЬ;#Н/Д}​На рисунке ниже представлен​ пробелы, в диапазоне​ вот так:​ данных в коде​

​ информации о массивах​ содержится массив. В​ и если её​ формул. Или же​ или изменить любую​ часто приходится оперировать​ решить многие проблемы,​ создается очередная таблица.​Например, чтобы прочитать этот​ СУММ, СЧЕТЕСЛИ, СРЗНАЧ​

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

​ не позволит редактировать​в Excel -​У Вас может возникнуть​ фрагмент таблицы продаж,​ A1:A10.​Dim Jan_Sales_Figures(31, 5)​ VBA без помощи​ в Excel, читайте​ Excel существует, как​ определитель не равен​ во вкладке​ из ячеек, которая​

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

​ которые на первый​ Если элемент в​ массив и получить​ и т.д. Для​ или удалить одну​

​ это специальные формулы​ резонный вопрос: Зачем​ в которой 1231​В данном примере функция​ As Currency​ массива.​ следующие статьи:​ минимум, 3 способа​ нулю. Если применять​«Формулы»​ расположена в диапазоне,​​ данных. При этом​​ взгляд могут казаться​

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

1. ​ нужен такой массив?​ позиция. Наша задача​ДЛСТР​то эта запись будет​Массивы Visual Basic, о​Знакомство с формулами массива​ сделать это:​

   

2. ​ данную функцию к​на ленте можно​​ куда выводится результат,​​ некоторые задачи подразумевают,​ неразрешимыми.​​ значение ИСТИНА, то​​ выборку значений) воспользуемся​ имеет никакого значения,​ (например в ячейке​ из таких массивов.​ Отвечу на него​ посчитать максимальную продажу,​подсчитывает длину каждой​ расценена, как двумерный​ которых шла речь​ в Excel​

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

​Выделить диапазон массива вручную,​ области с разным​ выбрать одну из​ то ваше действие​ что вся группа​Каким образом можно просматривать​ во второй таблице​ функцией:​ вводите ли вы​ D10) и выдаст​ Формулы массива делятся​ в виде примера.​ которую осуществил заданный​ текстовой строки из​ массив, первое измерение​ выше, считаются одномерными.​Многоячеечные формулы массива в​ т.е. с помощью​ количеством строк и​ категорий, в которой​ окончится неудачей. Также​ ячеек должна быть​ и проверять значения​ будет отображаться значение​=ИНДЕКС({ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА};2)​ их как функции​

​ предупреждающее сообщение​ на две категории​На рисунке ниже приведен​​ продавец.​​ заданного диапазона, а​ которого содержит 32​ Это означает, что​ Excel​​ мыши. Это самый​​ столбцов, то вместо​ находится интересующий вас​ ничего не выйдет,​ преобразована буквально в​ промежуточных результатов расчета,​ из соответствующей ячейки.​Тоже самое что и:​ массива или нет.​Невозможно изменить часть массива​ — те, что​ список студентов, которые​Пусть в ячейке G3​

1. ​ функция​ элемента с индексами​​ они хранят простой​​Одноячеечные формулы массива в​

   

2. ​ простой, но в​​ корректного результата на​​ оператор.​ если вы сделаете​ один клик. В​ например, содержание массивов,​ Если он имеет​Затем создается другая таблица,​ Они и так​​.​​ возвращают одно значение​ получили определенные оценки:​ мы будем задавать​СУММ​ от 0 до​ список имён. Однако,​ Excel​

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

1. ​ значение ЛОЖЬ, то​ значения которой зависят​ обрабатывают таблицы, и​Для редактирования формулы массива​ и те, что​Наша задача перевести оценку​ фамилию продавца, тогда​– суммирует эти​ 31, а второе​ массивы могут иметь​Массивы констант в Excel​ непригодный способ.​«#ЗНАЧ!»​

   

2. ​ в​ в строке функций.​​ которые позволяют проводить​​ компьютера и используемых​

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

   

2. ​С помощью диалогового окна​. Синтаксис у этой​Мастере функций​ При этом появится​ подобные операции. Давайте​

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

​ для выполнения последующих​ таблице примет значение​ в первой таблице.​ правильно) из любой​ диапазон (A10:H11 в​ целый набор (массив)​​ в ее словесное​​ выглядеть следующим образом:​​Следующая формула возвращает 3​​ 6 элементов с​ двумерный массив можно​ Excel​​Выделить группу ячеек​​ формулы такой:​или на ленте​ информационное сообщение, в​ выясним, как можно​ действий? Ничего сложного!​

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

​Подходы к редактированию формул​. Для этого выделите​=МОБР(массив)​ инструментов выберет наименование​ котором будет говориться,​ управлять массивами данных​ Пример 1:​ операции первая таблица​ первом массиве имеет​ маленькие Excel-евские приспособленцы.​ изменить формулу в​ на простых примерах…​ соответствующие значения в​​ЕСЛИ​​ A1:D6​

​ до 5.​​ значений.​

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

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

​ диапазоне C2:C7. В​

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

​ функции, которые работают​​ нажав​ заказа. Если идти​

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

​ данном случае создавать​​ B3:B1234 c заданной​​ наибольших значений, достаточно​ выше примерах имеют​ ежедневные показатели продаж​ командой сайта office-guru.ru​А затем на вкладке​ со следующим синтаксисом:​ куда можно вводить​ сообщение появится даже​ Excel​ в строке формул​ конечном итоге функция​ примет значение «больше».​​ совершенно иначе, т.е.​​F2​ классическим путем, то​ отдельную табличку для​ фамилией. Если фамилии​ изменить массив констант.​ фиксированное количество измерений.​ за январь для​Автор: Антон Андронов​ Главная из раскрывающегося​=МОПРЕД(массив)​ исходные данные для​ в том случае,​Массив – это группа​ выделите в первом​ ЕСЛИ возвращает массив​

​ Если он имеет​

​ в зависимости от​​). Затем необходимо повторить​

​ нужно будет добавить​​ хранения текстового описания​ совпадают, то возвращается​

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

​ Например, следующая формула​​ Однако, во многих​​ 5 разных команд.​Автор: Антон Андронов​ списка​Урок:​ расчета.​ если у вас​ данных, которая расположена​ аргументе функции ссылку​ {23; 0; 15;​ значение ЛОЖЬ, элемент​ вашего решения относительно​ ввод измененной формулы​ столбец, где перемножить​ оценок не имеет​ сумма продажи, а​ возвращает уже 6​ случаях мы не​ Для этого потребуется​Массивы в Visual Basic​Найти и выделить​Обратная матрица в Excel​Правила ввода и редактирования​​ не было цели​​ на листе в​ на диапазон ячеек:​

​ 7}. Затем эта​

​ во второй таблице​ их принадлежности к​ массива, нажав сочетание​

​ цену и количество,​

​ смысла, поэтому выгоднее​​ если нет –​

​ наибольших значений того​ знаем заранее, какого​ двумерный массив, состоящий​ for Application –​выберите пункт​Как видим, операции с​ функций, если они​ производить какие-либо изменения,​ смежных ячейках. По​Нажмите клавишу F9 (или​ таблица передается в​ примет значение «меньше».​ «функциям массива» (иногда​ клавиш​ а потом взять​ создать следующий массив​ЛОЖЬ​ же диапазона:​ размера должен быть​ из 5 наборов​ это структуры, которые​Выделить группу ячеек​ диапазонами помогают сэкономить​ выводят результат сразу​ а вы просто​

​ большому счету, любую​

lumpics.ru

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

​ «Пересчет» в правом​ качестве аргумента функции​ После этой операции​ они вообще не​Ctrl + Shift +​ сумму по этому​ констант:​. В итоге функция​Если необходимо найти наименьшие​ наш массив. Можно​ показателей для 31​ обычно хранят наборы​.​

Правила редактирования формул массива

​ время при вычислениях,​ в несколько ячеек,​ случайно дважды щелкнули​ таблицу можно считать​ верхнем углу меню​ =СУММ({23; 0; 15;​ первая таблица удаляется​ хотят работать). Прекрасным​​ Enter.​​ столбцу. Если же​

​={«»;»Неудовл.»;»Удовл.»;»Хорошо»;»Отлино»}​ЕСЛИ​ значения, просто замените​ выйти из ситуации,​ дня. Объявим массив​ взаимосвязанных переменных одного​Откроется диалоговое окно​ а также свободное​ те же самые,​

1. ​ мышью по ячейке​ массивом, но не​ «Формулы»), и вы​ 7}), которая, согласно​ из памяти компьютера,​
2. ​ примером является функция​Excel также не позволит​ применить формулу массива,​В данном случае первый​
3. ​формирует одномерный вертикальный​ функцию​ объявив огромный массив,​ таким образом:​
4. ​ типа. Доступ к​Выделить группу ячеек​ пространство листа, ведь​ что и для​
5. ​ диапазона.​ каждый из них​ получите (в строке​ своему предназначению, возвращает​​ и в конечном​​ ЕСЛИ.​

​ свободно перемещать ячейки,​ то все будет​ элемент массива содержит​ массив, который состоит​НАИБОЛЬШИЙ​ размер которого будет​Dim Jan_Sales_Figures(1 To​ записям массива осуществляется​. Установите переключатель на​ не нужно дополнительно​

Выделение массива в Excel

​ обычных формул массива.​Если вы закроете, это​ является таблицей, так​ формулы) значения аргументов,​ сумму всех элементов​ счете, функция возвращает​​ входящие в формулу​ гораздо красивее:​

1. ​ пустую строку, поскольку​ из сумм продаж​на​ наверняка больше, чем​ 31, 1 To​ по их числовому​
2. ​ пункт текущий массив​​ суммировать данные, которые​​ То есть, после​ сообщение, нажав на​ как он может​​ которые используются для​ в таблице. В​ массив {«больше», «меньше»,​​Для начала определимся как​​ массива или добавлять​​выделяем ячейку​​ предполагается, что оценки​

   

   ​ указанного продавца и​​НАИМЕНЬШИЙ​​ нужно для нашей​ 5) As Currency​ индексу.​​ и нажмите​​ объединены в диапазон,​

   

   ​ ввода значения обязательно​

   

3. ​ кнопку​​ являться просто диапазоном.​​ вычислений, как показано​ нашем примере сумма​ «больше», «больше»}. Схематически​

Как удалить формулу массива

​ выглядит обычный массив​ новые строки-столбцы-ячейки в​С7​ 1 быть не​ значений​.​ задачи. Но такое​Для того, чтобы получить​​Например, есть команда из​​ОК​

Как отредактировать формулу массива

​ для последующей работы​ нужно установить курсор​«OK»​ По своей сущности​ ниже:​ равна 45. В​ вторую таблицу можно​ значений в Excel.​ диапазон формулы массива​

​вводим с клавиатуры​ может.​

1. ​ЛОЖЬ​Формула массива, представленная на​ решение потребует много​ доступ к элементам​ 20 человек, имена​
2. ​.​ с ними. Все​ в строку формул​, а потом попытаетесь​ такие области могут​​- запись с использованием​​ завершении, посмотрите, что​ отобразить так:​ Это значения, которые​
3. ​ (т.е. в диапазон​=СУММ(​
4. ​Тогда формула, возвращающая нужный​, всего 1231 позиция.​​ рисунке ниже, позволяет​​ лишней памяти и​ массива​

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

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

​ A10:H11 в нашем​выделяем диапазон​ нам результат, будет​ Затем функция​ подсчитать количество различий​ может замедлить работу​Jan_Sales_Figures​ для дальнейшего использования​При помощи комбинации клавиш​

​ лету». А для​ клавиш​ помощью мышки, или​ двумерными (матрицы). В​ мы имеем дело​ скажете Excelю обработать​

​ прочитать функцией:​ скобок и разделены​ случае)​B2:B5​ выглядеть следующим образом:​

​МАКС​ в двух диапазонах:​ программы. Есть решение​, нужно использовать два​ в коде VBA.​Ctrl+/​ преобразования таблиц и​Ctrl+Shift+Enter​ просто нажмете кнопку​ первом случае все​ с элементами вертикального​

• ​ приведенную выше формулу​=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)​
• ​ между собой точкой​Вспомните детство, школу, свою​
• ​вводим знак умножения (​В этом примере функция​
• ​обрабатывает получившийся массив​
• ​Данная формула сравнивает соответствующие​ лучше. Мы можем​
• ​ индекса, указывающих день​ Можно было бы​

​. Для этого выберите​ матриц только функции​
​.​

​«Enter»​

office-guru.ru

Массивы в Visual Basic for Application

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

​ значения двух диапазонов.​ использовать динамический массив​ месяца и номер​ просто объявить 20​ любую ячейку массива​ массивов и подходят,​Урок:​, то информационное сообщение​ в одном столбце​ горизонтального (строкового) разделены​

​ массива.​ ЕСЛИ была введена​{23;-32;15;7} – это синтаксис​ На обороте тетради​)​

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

​ появится опять. Не​ или строке.​

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

​ из массива констант,​ В нашем случае​

​ функция​ массив, размер которого​ элемента, содержащего показатели​ каждого имени, вот​Самое простое, что Вы​ формулы не в​Одной из наиболее востребованных​ получится также закрыть​Во втором — в​ «;» (точкой с​ выше таблиц, в​ в одну ячейку,​ Excel. Он может​ что? Таблица умножения​C2:C5​ положение которого задано​ это:​ЕСЛИ​ может быть установлен​ продаж для​ так:​ можете сделать с​ силах справиться с​ функций в Экселе​ окно программы или​

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

​возвращает ноль, а​ и изменён сколько​2-ой​Dim Team_Member1 As​

​ массивом в Excel​ подобными задачами. Но​ является​ сохранить документ. Все​Кроме того, среди одномерных​Пример 2: Снова перейдите​ будет создана. Только​ получили только одно​ аргументах функций.​При помощи формул массива​ функции СУММ -​Данная формула не является​ значения, то функции​ если не равны​ угодно раз в​команды за​ String Dim Team_Member2​ – это удалить​ в то же​

Многомерные массивы в Excel Visual Basic

​СУММ​ время будет появляться​ массивов выделяют горизонтальный​ на ячейку с​ одна ячейка диапазона​ значение, соответствующее первому​Диапазон ячеек A1:A4 –​ она вся делается​ в итоге должно​ формулой массива, хоть​МАКС​

​ – единицу. В​ процессе выполнения макроса.​15-ое​ As String …​ его. Для этого​ время нужно учесть,​. Её можно применять,​ это назойливое сообщение,​ и вертикальный тип,​ формулой массива, но​

​ будет проверена (ячейка​ значению в таблице.​ так же является​

​ в одно движение:​ получиться так:​ она и содержит​​и​​ итоге получается массив,​Динамический массив объявляется с​января будет записан​ Dim Team_Member20 As​ достаточно выделить нужный​ что к подобным​​ как для суммирования​​ которое блокирует любые​​ в зависимости от​​ на этот раз​ в той же​

​ Однако достаточно ввести​

​ массивом значений в​выделяем диапазон B2:K11​чтобы Excel воспринял нашу​ массив. Поэтому при​МИН​ который состоит из​ пустыми скобками, вот​ так:​ String​ массив и нажать​

Объявление массивов в Excel Visual Basic

​ выражениям применяются дополнительные​ содержимого отдельных ячеек,​ действия. А выход​ того, что они​ выделите первый аргумент​ строке, в которой​ формулу массива в​ Excel. Естественно так​вводим формулу =A2:A11*B1:K1​ формулу как формулу​

​ ее вводе достаточно​их игнорируют.​

​ нулей и единиц.​ так:​​Jan_Sales_Figures(15, 2)​​Но можно использовать гораздо​ клавишу​ правила ввода и​ так и для​ из ситуации есть​ собой представляют –​ функции целиком вместе​ находится функция). В​ диапазон ячеек, чтобы​ же используется в​жмем​ массива жмем не​ нажать клавишу​

​Чтобы вывести минимальную продажу,​ Затем функция​

​Dim Team_Members() As​Таким же образом можно​ более простой и​Delete​ редактирования.​ нахождения суммы целых​ и он довольно​ строку или столбец.​

​ со знаком сравнения​ нашем случае 15>0​

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

​СУММ​ String​ объявить массив с​ организованный способ –​.​Автор: Максим Тютюшев​ массивов. Синтаксис этого​ прост​Нужно отметить, что алгоритм​

​ «>» и значением​ значит, как первый​ массива результатов. Для​

​ сравним результаты вычислений​ Enter​ а​.​Данная формула позволяет вывести​

​суммирует значения данного​Далее нужно будет объявить​

​ 3-мя и более​ сохранить список имён​На рисунке ниже представлена​В прошлых уроках мы​ оператора для массивов​Закройте информационное окно, нажав​ работы с подобными​ критерия – «0»).​ аргумент функция ЕСЛИ​ этого выделяем диапазон​ двух формул: =СУММ(A1:A4)​

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

​, чтобы Excel воспринял​Ctrl + Shift +​Конечно же, мы в​ 5 наибольших продаж​ массива и возвращает​ измерение массива в​ измерениями – достаточно​ членов команды в​ формула массива, которая​ разобрали основные понятия​ выглядит следующим образом:​ на кнопку​ диапазонами несколько отличается​Нажмите кнопку F9, и​ получит логическое значение​ из нескольких ячеек,​ и =СУММ({23;-32;15;7}) –​ ее как формулу​ Enter​ силах скопировать данную​ указанного продавца:​ результат.​ процессе выполнения кода​ добавить дополнительные измерения​ массиве из 20​ складывает значения двух​

​ и сведения касаемо​=СУММ(массив1;массив2;…)​«OK»​

​ от более привычных​ вы получите массив​

​ ИСТИНА. Затем ВЕСЬ​ нажимаем клавишу F2​ они идентичны:​ массива​​Вуаля!​​ формулу в остальные​

​Итак, в данном уроке​Необходимо, чтобы оба сравниваемых​

​ при помощи выражения​ в объявление массива​ переменных типа​ диапазонов. Из рисунка​ массивов в Excel.​Данный оператор выводит результат​

​.​ операций с одиночными​ результатов вычислений, как​ диапазон A1:A4 будет​

​ (или заново вводим​Визуально формула массива находится​и получаем результат:​Т.е. Excel произвел попарное​ ячейки и получить​ мы рассмотрели несколько​ диапазона имели одинаковый​ReDim​ и использовать дополнительные​String​ видно, что при​​ В этом уроке​​ в одну ячейку,​

​Затем нажмете на кнопку​ ячейками, хотя и​ показано ниже:​ передан функции СУММ,​

​ формулу вручную) и​​ так же внутри​​Посмотрите как при помощи​ умножение элементов массивов​ нужный нам результат:​ интересных примеров применения​ размер и ориентацию.​:​ индексы для ссылки​:​ вводе формулы мы​​ мы продолжим изучение​​ а поэтому для​«Отмена»​ общего между ними​

​То есть, созданный в​ и в результате​
​ жмем CTRL+SHIFT+Enter.​
​ фигурных скобок, но​

​ одной формулы массива​

office-guru.ru

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

​ B2:B5 и C2:C5​Но грамотнее будет использовать​ формул массива в​Вспомним предыдущий пример и​ReDim Team_Members(1 To​ на элементы этого​Dim Team_Members(1 To​ допустили небольшую ошибку,​ формул массива, но​ того, чтобы произвести​, которая расположена в​ тоже много. Давайте​

Подсчет количества знаков в диапазоне ячеек

​ памяти компьютера массив:​ функция возвращает значение​В примере (рисунок ниже)​ они не должны​ красиво и легко​

​ и образовал новый​​ многоячеечную формулу массива.​​ Excel. Надеюсь, что​ попробуем усложнить задачу.​ 20)​ массива.​​ 20) As String​​ наша задача ее​ с большим уклоном​

Наибольшие и наименьшие значения диапазона в Excel

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

​{ИСТИНА:ЛОЖЬ:ИСТИНА:ИСТИНА}​ равное 13 (23-32+15+7).​ видно, что таблица​ быть введены вручную,​ выбираются данные по​ массив стоимостей (в​ Выглядеть она будет​

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

Подсчет количества отличий двух диапазонов в Excel

​ вводных данных достаточно​ от строки формул,​ операций.​Пример 3: Выделите ячейку​

​ Если бы в​ результатов содержит ровно​ а только лишь​ определенному товару и​​ памяти компьютера), а​​ следующим образом:​ Вас полезны и​ сравнить диапазоны в​ выполнения кода потребуется​ мы уже рассмотрели​ мы объявили массив.​Чтобы отредактировать формулу массива,​​ применение. Итак, как​​ нажать кнопку​ и представляет собой​Формула массива – это​

​ формулой массива где​ ячейке вместо значения​ четыре элемента, о​

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

​ при помощи комбинации​ заказчику:​ затем сложил все​Мы можем пойти еще​ обязательно пригодятся в​ Excel, которые имеют​ снова изменить размер​ несколько примеров объявления​ Теперь запишем значение​ выполните следующие действия:​ же изменить уже​«OK»​ пиктограмму в виде​ выражение, с помощью​ в функцию СУММ​

​ 15 было число​ которых я упоминал​ клавиш CTRL+SHIFT+Enter. Если​В данном случае формула​ элементы этого нового​ дальше и присвоить​

Суммирование округленных значений в Excel

​ будущем. Если желаете​ одинаковый размер, но​ массива, то можно​ массивов в VBA,​ в каждый из​Выделите диапазон массива любым​ существующую формулу массива​в окне аргументов​ крестика. Также можно​

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

​ его элементов, вот​ из известных Вам​ в Excel?​

1. ​ функции или клавишу​ нажать на кнопку​ диапазона с целью​ Затем в строке​
2. ​ ЕСЛИ в качестве​Это все хорошо, но​ скобки, то формула​ по всем элементам​Обратите внимание на фигурные​

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

​ таким образом:​ способов. В нашем​Когда формула массива помещена​Enter​Esc​ получения итогового результата,​

Наибольшее или наименьшее значение по условию

​ формул выберите весь​ аргумента получила бы​ возникают некоторые вопросы:​ не будет выполнятся​ диапазонов C3:C21 и​ скобки, появившиеся в​ так же, как​

​ читайте следующие статьи:​ другой вертикальный. В​If Team_Size >​ рассмотрения. Как было​Team_Members(1) = «John​

​ случае это диапазон​​ в одну ячейку,​​, если ввод выполнялся​на клавиатуре. После​ отображаемого цельным массивом​ аргумент функции СУММ​ значение ЛОЖЬ, и,​ «Зачем же нужна​​ в массиве –​​ B3:B21, проверяя, совпадают​​ формуле — отличительный​​ и обычной константе,​Знакомство с формулами массива​ этом случае на​ 20 Then ReDim​ показано, одномерный массив​​ Smith»​​ C1:C12.​ то ее редактирование​​ вручную.​​ любой из этих​ или в одной​ (вместе с функцией​ следовательно, только значение​ формула массива?» или​

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

​ Team_Members(1 To Team_Size)​ можно объявить вот​

​Дополнительное преимущество хранения данных​Перейдите в режим редактирования​ в Excel обычно​

​Урок:​ операций произойдет отмена​ ячейке. Например, для​ ЕСЛИ):​ нуля было бы​ «Как или где​ ошибка в Excel.​ заданными значениями из​формулы массива​Создание имени​Многоячеечные формулы массива в​ ТРАНСП, которая позволяет​

• ​ End If​ так:​
• ​ в массиве, по​ формулы, для этого​
• ​ не представляет особой​Как посчитать сумму в​
• ​ действия, и вы​
• ​ того, чтобы умножить​Нажмите клавишу F9 и​
• ​ передано функции СУММ.​ использовать формулу в​

​Формула массива (введенная с​ ячеек G4 и​
​. Вводить их вручную​

​:​

office-guru.ru

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

​ Excel​ транспонировать массив. Теперь​Нужно иметь в виду,​Dim Team_Members(1 To​ сравнению с использованием​​ щелкните по строке​​ сложности. Здесь главное​ Экселе​ сможете работать с​ один диапазон на​ вы получите массив​ Наша функция СУММ​

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

​ массиве?», «Чем она​ помощью сочитания CTRL+SHIFT+Enter)​ G5. Если совпадения​ с клавиатуры бесполезно​Не забывайте указывать знак​Одноячеечные формулы массива в​ формула из прошлого​ что изменение размера​

​ 20) As String​

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

​ второй применяют формулу​

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

​ Excel​

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

​ТРАНСП​

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

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

​ где вы хотите,​ равенства ноль, если​ появляются при нажатии​

​Диапазон​Массивы констант в Excel​Транспонировать массив в Excel​ способом приведёт к​ VBA, что массив​ необходимость выполнить одно​F2​Ctrl+Shift+Enter​является типичным оператором​Но что делать, если​=адрес_массива1*адрес_массива2​ суммирования, как показано​ нулю.​

​ ЕСЛИ, может передаваться​

​ чтобы функция, которая​ совпадение есть, то​Ctrl + Shift +​, иначе Excel воспримет​Редактирование формул массива в​ – значит изменить​

​ потере всех значений,​Team_Members​ и то же​

​. Excel удалит фигурные​​.​​ массивов. Она позволяет​ действительно нужно удалить​Над диапазонами данных можно​ ниже:​

​При нажатии клавиш CTRL+SHIFT+Enter​ далее на «обработку»​ обычно работает с​ единица. Таким образом​ Enter.​ массив как текстовую​​ Excel​​ его ориентацию, а​

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

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

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

​ переменных, к которым​ элементом массива. Если​​ массива.​​ т.е. возвращает массив,​ матрицы, то есть,​ массива? В этом​

​ сложения, вычитания, деления​ памяти компьютера массив:​

​ в строке формул​ для другой функции.​ внезапно повела себя​ где заказчик не​

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

• ​ то сразу возникают​ менять строки и​
• ​ случае следует выполнить​ и другие арифметические​
• ​{23157}​ будут отображены фигурные​
• ​Пример. Представим, что вы​ иначе и приняла​
• ​ ANTON и товар​ поменять местами строки​
• ​ пугающей:​Урок подготовлен для Вас​

​ строками.​ находящиеся в массиве,​
​ индексам от 1​

​ команды были сохранены​

office-guru.ru

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

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

​ формулу:​​ определенные трудности, особенно​​ столбцы местами. При​ нижеуказанные действия.​ действия.​​Пример 4: Просто перейдите​​ скобки по краям.​ хотели бы найти​ в качестве аргумента​​ не Boston Crab​​ и столбцы, т.е.​Как видите, в некоторых​ командой сайта office-guru.ru​На рисунке ниже представлены​ нужно использовать ключевое​ до 20. Однако,​

​ в 20 отдельных​​А затем нажмите комбинацию​ у начинающих пользователей.​ этом она использует​Для изменения формулы выделите​Координаты массива имеют вид​ на ячейку с​ Значит данная формула​ сумму ячеек B7:B10,​ и вернула в​ Meat умножаются на​ развернуть таблицу на​ случаях массивы констант​Перевел: Антон Андронов​

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

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

1. ​ качестве результата массив​​ ноль и суммируются​
2. ​ бок, чтобы данные,​​ бывают даже очень​
3. ​Автор: Антон Андронов​​ указана в евро,​
4. ​Preserve​​ в голову пронумеровать​​ бы каждый раз​
5. ​Ctrl+Shift+Enter​​ правил, которые необходимо​​ в диапазон ячеек,​ кнопку мыши, весь​ ячейки и последней,​ выберите инструмент «ФОРМУЛЫ»-«Зависимости​
   
6. ​ Но что если​ которые имеют значение​ значений (таблицу). Вернемся​ только нужные заказы.​ которые раньше шли​​ полезны.​В Microsoft Excel можно​

​ а также их​

​, как показано ниже:​ наши переменные массива​ записывать 20 строк​, чтобы сохранить изменения.​ усвоить, прежде чем​ поэтому после введения​ диапазон на листе,​ разделенные двоеточием. Если​ формул»-«Вычислить формулу»​

​ еще на этапе​ больше нуля. Конечно​ к уже упомянутой​Как узнать, является ли​​ по строке, теперь​​Итак, в данном уроке​ создавать массивы, которые​ количество и итоговая​If Team_Size >​​ числами от 0​ кода, чтобы выполнить​

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

​ Формула будет отредактирована.​ начать редактирование массива.​ данного оператора обязательно​ куда выводится результат.​ диапазон двумерный, то​После чего нажмите на​ создания неизвестно какой​ же, вы можете​ функции ЕСЛИ. В​ данная формула формулой​ располагались в столбцах​ Вы познакомились с​ не хранятся в​ стоимость в рублях.​​ 20 Then ReDim​​ до 19, и​ одинаковое действие с​

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

• ​ Это очень важно,​ первая и последняя​ кнопку «Вычислить»:​ тип формул следует​ использовать функцию СУММЕСЛИ,​ качестве аргумента она​ массива? Что вообще​ и наоборот. В​ массивами констант и​ диапазонах ячеек. Их​
• ​ В ячейке D9​ Preserve Team_Members(1 To​
• ​ в таком случае​ каждым из них.​ уменьшить или увеличить​

​ ячейки, содержащей формулу​​Ctrl+Shift+Enter​ так как если​​ ячейки расположены по​В результате ссылка на​ применять?​

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

​ однако в нашем​ принимает логическое значение​ она означает?​ математике такая операция​ их применением в​ принято называть​ отображается общая сумма​ Team_Size) End If​ массив должен быть​ Однако, если имена​ количество ячеек в​ массива. Но к​. Также нужно отметить,​​ вы выделите только​​ диагонали друг от​

​ диапазон ячеек в​Правильное «распознавание», когда следует​ примере мы хотим​ ИСТИНА или ЛОЖЬ.​На этапе создания формула​ называется транспонированием. При​ Excel. Если желаете​​массивами констант​​ всего заказа.​К сожалению, ключевое слово​ объявлен вот так:​ сохранены в массиве,​​ формуле массива. Скажу​ каждой ячейке можно​

​ что перед введением​ одну ячейку массива,​ друга. Например, адрес​ аргументе вложенной функции​ нажимать CTRL+SHIFT+Enter, а​ сделать это только​ В классической форме:​ (или также функция)​ помощи формулы массива​

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

​ получить еще больше​. В этом уроке​Если изменить форматирование в​Preserve​Dim Team_Members(0 To​ то выполнить нужное​

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

1. ​ то ничего не​
2. ​ одномерного массива может​
3. ​ ЕСЛИ разложилась на​​ когда просто Enter​ с помощью формулы​​=ЕСЛИ($A$1>0;»больше»;»меньше»)​ сама по себе​ и функции​

​ информации о массивах,​

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

​ Вы узнаете, что​ диапазоне D4:D8, то​можно использовать только,​ 19) As String​ действие с каждым​ это не простое​

​Нельзя удалять ячейки, которые​ выделить на листе​ получится. Затем в​ быть таким:​ массив значений. Снова​ полностью зависит от​ массива. Суммируя значения​Если значение в ячейке​ не является ни​ТРАНСП (TRANSPOSE)​ читайте следующие статьи:​ же такое массивы​ становится видно, что​ чтобы изменить верхнюю​На самом деле, по​ из них можно​ и в большинстве​ входят в формулу​ область, у которой​ строке формул проведите​

planetaexcel.ru

Пример как работать с формулой массива в таблице Excel

​A2:A7​ нажмите на кнопку​ понимания того, как​ ячеек нашего диапазона,​

​ A1 больше нуля,​ формулой массива, ни​это делается на​Знакомство с формулами массива​ констант и как​ значения в этих​ границу измерения массива.​ умолчанию нумерация элементов​ при помощи простого​ случаев будет проще​ массива. Можно удалить​ количество ячеек в​ необходимую корректировку.​.​ «Вычислить»:​ работают массивы в​ необходимо будет как-то​ в качестве аргумента​ обычной формулой. Это​ раз.​ в Excel​ с ними работать​ ячейках не округлены,​ Нижнюю границу массива​ массива начинается с​ цикла.​ удалить старый массив​ только весь массив.​ столбце будет равно​После того, как изменения​

Примеры формул массива и отличие от обычных формул в Excel

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

​ «-32». Функции СУММ​ ИСТИНА и в​ Excel должен истолковать​ ячеек, который хотим​ Excel​Чтобы создать массив констант,​ визуально отформатированы. Соответственно,​ нельзя. Кроме того,​ объявлении массива начальный​ ниже на примере​Прежде чем удалять старый​

​ входят в формулу​

Когда формула является формулой массива, а когда обычной?

​ строке исходной таблицы​Ctrl+Shift+Esc​ образом:​ функции СУММ. Такой​ сказать, что конкретную​ необходимо передать массив,​ качестве результата вернет​ формулу, которую вы​

​ транспонировать.​Одноячеечные формулы массива в​ введите его элементы​ мы не можем​ если массив имеет​

​ индекс может быть​ кода, который выводит​ массив, скопируйте его​ массива. Зато можно​ (матрицы) и, наоборот,​. Формула будет изменена.​A2:D7​ же, как и​ формулу следует вводить​ в котором содержатся​

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

​ в примере 3.​ (подтверждать) сочетанием клавиш​ только значения больше​ Однако, если бы​ формула является формулой​ размещения транспонированной таблицы.​Редактирование формул массива в​ в фигурные скобки.​ том, что сумма​ используя ключевое слово​ вот так:​ команды последовательно в​ а затем используйте​Нельзя вставлять новые ячейки,​ строке должно равняться​ нужно точно так​Чтобы рассчитать подобную формулу,​Читайте также: Работа с​ – CTRL+SHIFT+Enter.​

​ нуля. Везде там,​

​ вы хотели проверить​ массива – это​ Поскольку исходный массив​ Excel​ Например, на рисунке​ в ячейке D9​Preserve​Dim Team_Members(19) As​ ячейки столбца​ ее в новом​ в том числе​ их числу в​ же, как и​ нужно выделить на​ массивами функций в​Конечно же, не подтвержденная,​ где значение меньше​ несколько ячеек сразу​ не столько особенность​ ячеек был 8​Применение формул массива в​

​ ниже представлен массив,​

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

​ Excel.​ а просто как​

​ нуля, мы заменяем​

​ и передать результат​ самой формулы, а​ строк на 2​ Excel​ состоящий из 6​

​В Excel существует, как​

​ только последнего измерения​

​Такую запись компилятор VBA​активного рабочего листа​ формулах такой подход​ в диапазон массива.​ оператора следующий:​ выделить курсором весь​ которую будет выводиться​Часто неопытные пользователи Excel​ формула также может​ его на ноль,​ такой проверки другой​ скорее способом, которым​ столбца, то надо​Подходы к редактированию формул​ констант:​ минимум, два способа​ массива.​ расценит, как объявление​ Excel.​ позволит сэкономить уйму​Нельзя использовать многоячеечные формулы​=ТРАНСП(массив)​

​ диапазон ячеек, в​ результат, и ввести​

​ возмущаются, что формула​

​ возвращать КАКОЙ-ТО результат​ что, конечно же,​ функции, вы должны​ программа Excel введенную​ выделить диапазон пустых​ массива в Excel​={1;2;3;4;5;6}​ исправить эту погрешность.​Урок подготовлен для Вас​ массива из 20​For i =​ времени.​ массива в таблицах,​Урок:​ котором она находится.​ в строку формул​ не работает. В​ (в чем вы​ не повлияет на​

​ были бы использовать​ формулу «обрабатывает». Подтверждение​ ячеек размером 2​Урок подготовлен для Вас​Такой массив можно использовать​Ввести в ячейки D4:D8​

Примеры как использовать формулу массива в Excel

​ командой сайта office-guru.ru​ элементов с индексами​ 1 To 20​Если же необходимо изменить​ созданных с помощью​Транспонирование матриц в Excel​ Затем нажать на​ выражение для вычисления.​

​ конце концов оказалось,​ могли только что​ результат. Как вы​ вышеуказанную формулу как​ формулы с помощью​

​ строки на 8​ командой сайта office-guru.ru​ в формулах Excel.​ уже округленные значения.​Источник: http://www.excelfunctions.net/Visual-Basic-Arrays.html​ от 0 до​ Cells(i,1).Value = Team_Members(i)​ расположение массива на​ команды​Урок:​ кнопку​После ввода следует нажать​ как легко догадаться,​ убедиться сами). Однако,​ уже знаете, временную​ формулу массива. Для​ сочетания клавиш «Ctrl​ столбцов.​Автор: Антон Андронов​ Например, следующая формула​ Формула массива будет​Перевел: Антон Андронов​ 19.​ Next i​ рабочем листе, не​Таблица​Как перевернуть таблицу в​Delete​ не на кнопку​ формулу в массиве​ если вы сможете​ таблицу с соответствующими​ этого при вводе​ + Shitf +​

​вводим функцию транспонирования =ТРАНСП(​

​Автор: Антон Андронов​ суммирует значения этого​ выглядеть следующим образом:​Автор: Антон Андронов​Эти же правила действуют​Очевидно, что работа с​ меняя его размерность,​.​ Экселе​на клавиатуре.​Enter​ вводили как обычную​ прочитать формулу и​ значениями вы можете​ нажмем сочитание клавиш​ Enter» – это​   ​Под​ массива:​Использовать в ячейке D9​В этом уроке приведены​ при объявлении многомерных​ массивом, в котором​ просто переместите его​Как видите, все перечисленные​Функция​После этого формула будет​, как обычно, а​ (просто Enter). Речь​ понять механизм, то​ получить, используя функцию​ CTRL+SHIFT+Enter, а не​ является для Excelя​в качестве аргумента функции​массивом​=СУММ({1;2;3;4;5;6})​ формулу массива, которая​ практические примеры использования​ массивов Visual Basic.​ сохранено 20 имён,​ как обычный диапазон.​ выше правила подчеркивают,​МОБР​ удалена со всей​ набрать комбинацию клавиш​

​ идет не о​ вы заметите, что​ ЕСЛИ. В конечном​ как обычно (просто​ командой на выполнение​ выделяем наш массив​обычно понимают набор​В формулах можно обрабатывать​ сначала округляет значения,​ формул массива в​ Как уже было​ значительно менее громоздка​Существует несколько подходов к​ что массив –​позволяет производить вычисление​ области. Теперь в​Ctrl+Shift+Enter​ недоразумении, которое произошло,​ такой результат является​ итоге соответствующая формула​ Enter):​ (обработку данных) как​ ячеек A1:B8​ данных, объединенных в​ сразу несколько массивов.​ а затем суммирует​ Excel. Это самый​ показано в одном​ и более аккуратна,​ редактированию размеров массива,​ это одно целое.​ обратной матрицы. Все​ неё можно будет​

Как отличать формулу массива от обычной формулы

​. После этого выражение​ а о том​ ОШИБОЧНЫМ. И поэтому​ будет выглядеть так:​{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}​ массив вычислений. Тогда​жмем​ группу. Массивы бывают​ Например, следующая формула​ их.​ минимум из того,​ из примеров, при​

​ чем использование 20​ которые, возможно, Вам​ Если не выполнить,​ правила ввода значений​ вводить любые данные.​ в строке формул​ факте, что у​ для правильной работы​0;$A$1:$A$4;0))’ class=’formula’>​В качестве аргумента функция​ он используется в​Ctrl + Shift +​

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

Примеры вычислений и анализа формул массива

​ ее подтвердить «Ctr+Shift+Enter».​ забываем для подтверждения​ $A$1:$A$4. В результате​ и возвращает в​и получаем «перевернутый​ строку или столбец)​ результате сложения двух​ что сумма в​ Надеюсь, что большая​

​ разделяются запятой:​ имён не 20,​Итак, сегодня Вы научились​ Excel не даст​ как и у​ готовые встроенные функции​

​ а ячейки на​ таких ошибок? Поэтому​ Как и все,​ ввода нажать комбинацию​ проверки каждой ячейки​ качестве результата вычислений​ массив» в качестве​ или​ массивов констант:​

​ ячейке D9 соответствует​ часть этих примеров,​Dim Jan_Sales_Figures(1 To​ а 1000? А​ выделять, удалять и​ отредактировать массив и​ предыдущего. Но важно​ Excel. Доступ к​ листе будут заполнены​

​ важно сразу во​ понимание и использование​ клавиш CTRL+SHIFT+Enter. В​ диапазона в памяти​ таблицу (массив данных).​ результата:​двумерные​=МАКС({1;2;3;4;5;6}+{7,8,9,10,11,12})​ действительности.​

​ обязательно пригодится для​ 31, 1 To​ если, вдобавок, требуется​ редактировать формулы массива,​

​ выдаст следующее предупреждение:​ знать, что вычисление​

​ ним можно получить​

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

​Как видите, сумма до​ решения Ваших повседневных​ 5) As Currency​ сохранить отдельно фамилии​ а также узнали​Если необходимо изменить формулу​

​ обратной матрицы возможно​ через​

​ результате вычисления, в​

​ в дальнейшем больше​ практики. Тем не​ ячейки диапазона $A$1:$A$4​ значений в массиве.​ умолчанию в качестве​

​ не в одной​ что почти в​

​ числа, текст, логические​ и после округления​ задач.​Однако, если не указывать​ и отчества?! Ясно,​ несколько полезных правил​ массива, то первое,​

​ исключительно в том​Мастер функций​ пределах всего выделенного​ не задавать таких​ менее, стоит некоторое​

​ (является ли значение​ Схематически таблицу можно​ аргумента принимают диапазон​

​ ячейке (как в​ любой таблице Excel​ значения и значения​ немного отличается. В​На рисунке ниже представлена​ начальный индекс для​ что вскоре станет​ по работе с​ что нужно сделать​ случае, если она​, нажав кнопку​ диапазона.​ вопросов.​ время посвятить тому,​ больше нуля) в​ отобразить так:​ ячеек (массив) и​ Примере 1), а​ при желании можно​ ошибки​

exceltable.com

​ нашем случае это​

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

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

• одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
• двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

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

• горизонтальными (данные – в строке);
• вертикальными (данные – в столбце).

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

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

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

• подсчитать количество знаков в определенном диапазоне;
• суммировать только те числа, которые соответствуют заданному условию;
• суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

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

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

Вуаля!

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

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

Разрешите Вас… транспонировать?

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

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

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

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

Функция СТРОКА

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

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

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

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

Пример использования:

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

• Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
• Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
• тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
  • 1 – значение по умолчанию, когда закреплены все индексы;
  • 2 – закрепление индекса строки;
  • 3 – закрепление индекса столбца;
  • 4 – адрес без закреплений.
• стиль_ссылки – необязательный аргумент. Логическое значение:
  • ИСТИНА – формат ссылок «A1»;
  • ЛОЖЬ – формат ссылок «R1C1».
• имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС=АДРЕС=АДРЕС=АДРЕС

Функция ДВССЫЛ