Как сделать фигурные скобки в excel?

Как поставить фигурные скобки в excel

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

​Смотрите также​ вида «5 час.,​ B3:B21, проверяя, совпадают​ изменить формулу в​ и функции​выделяем диапазон​ группу. Массивы бывают​6​ в Excel в​ массива и внести​ раз.​ (=) и могут​ клавиш Ctrl +​

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

​ следующие статьи:​Внесите необходимые корректировки в​Выделить группу ячеек​ в том числе​В прошлых уроках мы​ 30 мин.» в​ ли они с​ строке формул (или​​ТРАНСП (TRANSPOSE)​​C2:C5​

​одномерные​2250​ ячейке A1. Обязательно​ изменения в формуле​Для вычисления нескольких значений​ содержать встроенные функции​ Shift + Ввод,​Знакомство с формулами массива​ формулу:​

  1. ​. Установите переключатель на​ строки и столбцы,​ разобрали основные понятия​ число минут. Вопрос:​ заданными значениями из​
  2. ​ нажав​это делается на​и закрываем скобку​(элементы массива образуют​
  3. ​Купе​ выберите диапазон ячеек​ массива и добавлять​ с помощью формулы​
  4. ​ Excel.​ чтобы ввести их. ​ в Excel​А затем нажмите комбинацию​
  5. ​ пункт текущий массив​ в диапазон массива.​ и сведения касаемо​ что значат фигурные​​ ячеек G4 и​​F2​

​ раз.​ функции СУММ -​ строку или столбец)​8​ E2: E11, введите​ фигурные скобки необходимо​ массива необходимо ввести​Например эта формула вычисляет​ Формулы массива можно​Многоячеечные формулы массива в​

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

​ клавиш​ и нажмите​Нельзя использовать многоячеечные формулы​ массивов в Excel.​ скобки в середине​ G5. Если совпадения​). Затем необходимо повторить​Допустим, имеем двумерный массив​ в итоге должно​

  1. ​ или​2000​ формулу​ нажать сочетание клавиш​ массив в диапазон​ суммарное значение массив​
  2. ​ использовать для выполнения​​ Excel​​Ctrl+Shift+Enter​ОК​ массива в таблицах,​ ​ В этом уроке​ формулы и как​ нет, то результат​​ ввод измененной формулы​​ ячеек, который хотим​​ получиться так:​​двумерные​

​Создание формулы массива с​​= C2: C11 *​​ Ctrl + Shift​ ячеек, состоящий из​ котировок акций и​​ пятизначные невозможно, такие​​Одноячеечные формулы массива в​

​, чтобы сохранить изменения.​

  • ​.​​ созданных с помощью​​ мы продолжим изучение​ ими правильно пользоваться?​ равенства ноль, если​
  • Как удалить формулу массива

    ​ массива, нажав сочетание​ транспонировать.​чтобы Excel воспринял нашу​(матрица). Легко сообразить,​ несколькими ячейками​ D2: D11​ + Ввод еще​ того же числа​​ поместит результат в​​ как​

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

    ​ Excel​ Формула будет отредактирована.​Текущий массив будет выделен:​ команды​ формул массива, но​ Ссылки приветствуются​ совпадение есть, то​ клавиш​Выделяем диапазон ячеек для​

    ​ формулу как формулу​ что почти в​

    1. ​В образце книги выберите​, и нажмите клавиши​ раз.​ строк или столбцов,​ ячейке рядом с​
    2. ​вычислять число символов в​Массивы констант в Excel​Очень часто возникает необходимость​При помощи комбинации клавиш​Таблица​​ с большим уклоном​​Полосатый жираф алик​ единица. Таким образом​Ctrl + Shift +​
    3. ​ размещения транспонированной таблицы.​ массива жмем не​
    4. ​ любой таблице Excel​ ячейки от E2​​ Ctrl + Shift​​Если необходимо включить новые​ что и аргументы​

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

    ​ кнопкой «Общее значение».​ диапазоне ячеек;​Применение формул массива в​ уменьшить или увеличить​Ctrl+/​.​ на их практическое​: Это массивные константы.​ суммы всех сделок,​ Enter.​

    ​ Поскольку исходный массив​ Enter, как обычно,​ при желании можно​ до E11. В​ + Ввод, чтобы​ данные в формуле​ массива.​Эта формула сначала умножает​суммировать только те числа,​

    ​ Excel​ количество ячеек в​. Для этого выберите​Как видите, все перечисленные​ применение. Итак, как​ То есть, в​

    ​ где заказчик не​Excel также не позволит​ ячеек был 8​ а​ найти один или​

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

    • ​ формулу подставляется первая​ ANTON и товар​
    • ​ свободно перемещать ячейки,​ строк на 2​
    • ​Ctrl + Shift +​ несколько таких массивов:​
    • ​ результаты.​
    • ​Продавец​ статьей развернуть формулу​
    • ​ который нужно ввести​ B2–I2) на их​

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

    ​ и нажмите комбинацию.​

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

    ​ что массив –​​ существующую формулу массива​ константа, а потом​ не Boston Crab​ входящие в формулу​ столбца, то надо​ Enter​Формулы массива​Перед вводом формулы всегда​Тип автомобиля​ массива. Вы также​ формулу массива.​ курс (ячейки B3–I3),​ значения в диапазоне​Урок подготовлен для Вас​ это не простое​Самое простое, что Вы​ это одно целое.​ в Excel?​ вторая.​ Meat умножаются на​ массива или добавлять​

    ​ выделить диапазон пустых​Вуаля!​в Excel -​ следует выбирать ячейки​Число проданных единиц​ можете попробовать:​Введите необходимую формулу.​ а затем складывает​ чисел, определенном верхней​ командой сайта office-guru.ru​ и в большинстве​ можете сделать с​ Если не выполнить,​Когда формула массива помещена​Например, из «​ ноль и суммируются​

    ​ новые строки-столбцы-ячейки в​ ячеек размером 2​

    ​Т.е. Excel произвел попарное​ это специальные формулы​ для результатов.​Цена за единицу​Правила изменения формул массива​В формулах массива используется​

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

    ​ массивом в Excel​ хотя бы одно​ в одну ячейку,​ «&A1&<"00 ч";"00 м">​ только нужные заказы.​ диапазон формулы массива​ строки на 8​ умножение элементов массивов​ для обработки данных​»Всегда» значит в 100​Итоги продаж​ (это может быть​ синтаксис обычных формул.​ сумму 53 184.​суммировать все​

    Создание формулы массива для вычисления одного результата

    ​Автор: Антон Андронов​ удалить старый массив​ – это удалить​ из вышеперечисленных правил,​ то ее редактирование​

    ​ B2:B5 и C2:C5​ из таких массивов.​ процентах случаев.​Ильина​ кропотливой работой)​ Они все начинаются​ В этом примере​

    ​n​Примечание:​ и создать новый.​ его. Для этого​ Excel не даст​ в Excel обычно​

    ​ час., 30 мин.​ объединяющую скобку?​ A10:H11 в нашем​вводим функцию транспонирования =ТРАНСП(​ и образовал новый​ Формулы массива делятся​Введите следующую формулу. Для​Седан​Удаление формулы массива (можно​ со знака равенства​ используется формула массива,​

    ​-е значения в диапазоне​

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

    ​ 00 ч»;» 5​​Bagira​ случае)​ ​ массив стоимостей (в​ на две категории​ этого достаточно просто​5​ также нужно нажимать​ (=) и могут​ расположенная только в​ значений.​​ можно оперативнее обеспечивать​​ массив, скопируйте его​ массив и нажать​ выдаст следующее предупреждение:​ сложности. Здесь главное​ час., 30 мин.​: я так поняла,​Вспомните детство, школу, свою​в качестве аргумента функции​ памяти компьютера), а​

    Создание формулы массива для вычисления нескольких результатов

    ​ — те, что​ начать ввод (со​2200​ клавиши Ctrl +​ содержать встроенные функции​ одной ячейке.​В программе Excel существует​ вас актуальными справочными​ формулу как текст,​

    ​ клавишу​Если необходимо изменить формулу​ не забыть закончить​

    ​ что тебе надо​ тетрадку по математике. ​ выделяем наш массив​ затем сложил все​ возвращают одно значение​ знака равенства), и​=C2:C11*D2:D11​

    ​ Shift + Ввод)​ Excel.​Нажмите клавиши CTRL+SHIFT+ВВОД.​ два типа формул​ материалами на вашем​ а затем используйте​

    ​ массива, то первое,​ редактирование комбинацией клавиш​* <60;1>означает, что​​ решить систему уравнений.​​ На обороте тетради​

    ​ ячеек A1:B8​​ элементы этого нового​ и те, что​ формула появится в​Купе​Использование констант массива в​В приведенном ниже примере​Формула автоматически вставляется между​ массива: массива формулы​ языке. Эта страница​ ее в новом​.​​ что нужно сделать​​Ctrl+Shift+Enter​ первый из результатов​ этот символ так​ на обложке было​жмем​ массива.​ дают на выходе​ последней выбранной ячейке.​4​

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

    ​На рисунке ниже представлена​ – это выделить​.​

    ​ умножается на 60,​ механически не используются​ что? Таблица умножения​Ctrl + Shift +​

    ​Обратите внимание на фигурные​ целый набор (массив)​ Вы также можете​1800​

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

    Попробуйте попрактиковаться

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

    ​ а второй -​ в решении, тут​ вот такого вида:​ Enter​ скобки, появившиеся в​ значений. Рассмотрим их​ ввести ее строке​Егоров​ полезными)​ каждого столбца, формула​< >​

    ​ значения и формулы​ содержать неточности и​ позволит сэкономить уйму​ складывает значения двух​ содержится массив. В​ т.е. возвращает массив,​ на 1. СУММ​​ иной подход.​При помощи формул массива​​и получаем «перевернутый​ формуле — отличительный​ на простых примерах. ​ формул:​

    Excel. Введение в формулы массива

    Ранее я уже посвятил несколько заметок использованию формул массива:

    Читать еще:  Как сделать функцию суммы в excel?

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

    Предлагаю вашему вниманию посвященный массивам фрагмент книги Джона Уокенбаха. Подробное руководство по созданию формул в Excel 2002. – М.: Издательский дом «Вильямс», 2002. – 624 с.

    Скачать заметку в формате Word, примеры в формате Excel

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

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

    Формула массива, возвращающая несколько значений

    Рис. 1 демонстрирует рабочий лист, на котором производится расчет объема продаж товаров. Для этого вычисляют значение в столбце D (объем продаж каждого товара), используя формулу =В2*С2, а затем копируют ее в расположенные ниже ячейки.

    Рис. 1. Диапазон D2:D6 содержит традиционные формулы произведения (в ячейках D2 и D3 различные формулы, хотя и похожие)

    После копирования этой формулы рабочий лист содержит пять формул в столбце D. Альтернативный путь вычислений – использование единой формулы (формулы массива) для вычисления всех пяти значений в диапазоне D2:D6. Эта единственная формула занимает пять ячеек и возвращает массив из пяти значений. Для создания единой формулы массива нужно произвести следующие операции:

    1. Выделить диапазон, в котором будет сохраняться результат вычислений. В данном случае это диапазон D2:D6.
    2. Ввести формулу: =В2:В6*С2:С6
    3. Обычно после введения формулы нажимается клавиша , но только не в случае формулы массива. Так как введенная формула является формулой массива, нажимается комбинация клавиш .

    Формула добавляется во все пять выделенных ячеек (рис. 2), при этом в строке формул появится следующее выражение:

    Рис. 2. Диапазон D2:D6 содержит формулу массива (в ячейках D2 и D3 одна и та же формула)

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

    Эта формула производит вычисления и возвращает массив из пяти элементов. В данном случае формула массива производит операции над двумя другими массивами, оба из которых хранятся в диапазонах ячеек. Значения первого массива хранятся в диапазоне В2:В6, а второго — в С2:С6. Для отображения итогового массива требуется пять ячеек, потому что в одной ячейке нельзя отобразить более чем одно значение. Этим объясняется, зачем нужно выделять пять ячеек перед введением формулы.

    Конечно, эта формула массива возвращает точно такие же значения, что и пять формул, введенных в соответствующие ячейки диапазона D2:D6

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

    • Легко убедиться, что все формулы в диапазоне идентичны.
    • Использование формул массива, возвращающих сразу несколько значений, избавляет от случайных изменений в формуле. Нельзя просто изменить одну ячейку в формуле массива.
    • Использование формул массива почти всегда предотвращает вмешательство начинающих пользователей, которые хотят внести свои изменения в формулы.

    Формула массива, возвращающая одно значение

    Обратимся к рис. 3. Следующая формула массива размещается в одной ячейке – D8:

    Рис. 3. Сумма произведений в виде формулы массива

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

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

    Эта формула оперирует значениями, находящимися в двух массивах. Оба массива хранятся в ячейках. Первый – в диапазоне ячеек В2:В6, второй – C2:С6. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММ выполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

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

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

    Для создания массива констант нужно список его элементов заключить в фигурные скобки. Вот пример горизонтального массива констант, состоящего из пяти элементов: <1;0;1;0;1>. Для создания вертикального массива констант используйте в качестве разделителя двоеточие.

    Формула =СУММ(<1;0;1;0;1>) использует функцию СУММ, аргументом которой является массив констант. Формула возвращает значение суммы элементов массива, равное 3. Отметим, что эта формула использует массив в качестве аргумента, но при этом не является формулой массива. Поэтому не нужно использовать клавиши для ввода формулы.

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

    Пока, возможно, трудно заметить, какие-либо преимущества использования массивов констант. Действительно, формула =СУММ(1;0;1;0;1) возвращает точно такое же значение, как и =СУММ(<1;0;1;0;1>).

    Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант: =СУММ(<1;2;3;4>*<5;6;7;8>). Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов: <5;12;21;32>. Созданный массив используется впоследствии в качестве аргумента для функции СУММ, которая возвращает результат вычислений (70). Формула эквивалентна следующей формуле, которая не использует массивы: =СУММ(1*5;2*6;3*7;4*8)

    Можно составлять формулы массивов для разных типов массивов. Например, можно составить формулу, в которой будет одновременно использоваться массив констант и массив, элементы которого хранятся в диапазоне ячеек (рис. 4). Для подсчета суммы значений, которые являются произведением элементов массива в диапазоне Al:D1 и соответствующих элементов массива констант, применяется следующая формула: =<СУММ((A1:D1*<1;2;3;4>)>. Эта формула эквивалентна следующей: =СУММ(А1*1;В1*2;С1*3;D1*4)

    Рис. 4. Сумма произведений массива рабочего листа и массива констант

    Элементы массива констант

    Массивы констант могут состоять из чисел, текста, логических значений (например, ИСТИНА, ЛОЖЬ) и даже значений ошибок, таких как #Н/Д. Числа в массиве могут быть целыми, с десятичной точкой или экспоненциальными. Текст в массиве констант заключается в двойные кавычки (например, » Вторник » ). Массив констант может состоять из элементов разного типа, например:

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

    Понятие размерности массива

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

    Одномерные горизонтальные массивы

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

    Для отображения этого массива в диапазоне необходимо пять последовательных ячеек в строке. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из одной строки и пяти столбцов. Затем введите: = <1;2;3;4;5>и нажмите (рис. 5).

    Рис. 5. Одномерный горизонтальный массив.

    Обратите внимание, внутренние фигурные скобки обозначают массив констант; внешние фигурные скобки – формулу массива. Если ввести этот массив в горизонтальный диапазон, состоящий более чем из пяти ячеек, то лишние ячейки будут содержать #Н/Д (что указывает на недопустимые значения). Если ввести этот массив в вертикальный диапазон ячеек, то в каждой ячейке диапазона появится первый элемент (1) массива.

    Ниже приведен пример другого горизонтального массива. Он состоит из семи элементов и создает текстовую последовательность: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Для ввода этого массива выделите семь элементов в строке и введите следующий текст: =< " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " , " Пятница " ; " Суббота " ; " Воскресенье " >. Нажмите .

    Одномерные вертикальные массивы

    Элементы в одномерном вертикальном массиве разделяются двоеточием (точкой с запятой в английской версии программы). Ниже приведен пример вертикального массива констант, состоящего из шести элементов: <10:20:30:40:50:60>. Для отображения этого массива в диапазоне необходимо шесть ячеек в столбце. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из шести строк и одного столбца. Затем введите: = <10:20:30:40:50:60>и нажмите .

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

    Двумерные массивы

    В двумерных массивах используются точки с запятыми (запятые в английской версии программы) для разделения горизонтальных элементов и двоеточия (точки с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4: <1;2;3;4:5;6;7;8:9;10;11;12>. Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу = <1;2;3;4:5;6;7;8:9;10;11;12>после чего нажмите (рис. 6).

    Читать еще:  Как сделать прогрессию в excel 2007?

    Рис. 6. Двумерный массив

    Если ввести массив в диапазон, состоящий из большего числа ячеек, Ехсеl отобразит #Н/Д в лишних ячейках (рис. 7).

    Рис. 7. Показано, что получится, если ввести массив 3×4 в диапазон ячеек 10×5.

    Каждая строчка двумерного массива должна содержать одинаковое количество элементов. Например, представленный ниже массив недопустимый, потому что его третья строка состоит только из трех элементов; <1;2;3;4:5;6;7;8:9;10;11>. Excel не даст возможности ввести формулу, в которой присутствует недопустимый формат массива.

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

    Excel позволяет создать массив констант, присвоить ему имя и затем использовать это имя в формулах. Фактически присвоение имени массиву заключается в присвоении имени формуле (рис. 8). Пройдите по меню: Формулы – Диспетчер имен.

    Рис. 8. Показано, как присвоить имя массиву, используя диалоговое окно Создание имени.

    Массиву присвоено имя ДеньНедели, которое соответствует следующему массиву констант: < " Понедельник " ; " Вторник " ; " Среда " ; " Четверг " ; " Пятница " ; " Суббота " ; " Воскресенье " >. Отметим, что в диалоговом окне Создание имени для задания массива необходимо использовать знак равенства (=) перед массивом констант. В противном случае массив будет воспринят как текстовая строка. Также необходимо самостоятельно взять элементы массива в фигурные скобки. Excel в этом случае не вводит скобки автоматически. После присвоения имени массиву, его имя можно использовать в формулах (рис. 9).

    Рис. 9. Ввод поименованного массива в диапазон Al:G1

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

    Рис. 10. Ввод горизонтального массива в вертикальный диапазон

    При использовании функции Excel ИНДЕКС можно получить доступ к отдельному элементу массива. Например, ниже приведенная формула возвращает значение «Четверг», четвертого элемента массива ДеньНедели: =ИНДЕКС(ДеньНедели;4)

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

    Если Вы довольно много работаете в Excel то наверняка уже слышали выражение «формула массива«. Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
    Большинство уже наверняка знакомо с функцией СУММ (SUMM) . Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5) . Получим сумму этих чисел — 15. Все просто и понятно. И вроде бы — функция уже работает с массивами — ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1) . Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и. Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами. Ему надо явно указать, что мы хотим это сделать и результат вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш — Ctrl+Shift+Enter. Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки — < =СУММ(A1:A5+1) > . Если Вы после ввода их увидели — значит все сделано верно и формула массива введена правильно. И результат будет 20.

    Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке <=СУММ(A1:A5+1)>и ни о какой формуле и суммировании речи быть уже не может.

    Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5) . Теперь мы изменили функцию и ввели её как формулу массива: <=СУММ(A1:A5+1)>. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1) ⇒ =СУММ(2;3;4;5;6)

    Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
    Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
    =МИН(ЕСЛИ(A1:A10<>0;A1:A10)) Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
    <=МИН(ЕСЛИ(A1:A10<>0;A1:A10))> то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет — 1.

    Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП (TRANSPOSE) . Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10 ):

    • выделяете диапазон ячеек( D1:M3 ), равный по количеству ячеек исходному диапазону значений( A1:C10 ), которые необходимо транспонировать;
    • вписываете функцию ТРАНСП;
    • в качестве аргумента указываете ссылку на исходный диапазон значений: =ТРАНСП( A1:C10 ) ;
    • завершаете ввод функции сочетанием клавиш Ctrl+Shift+Enter.

    В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции( D1:M3 ) должно быть равно числу столбцов в исходном диапазоне( A1:C10 ), а число столбцов — числу строк. Если указать меньше — не все значения будут транспонированы. Если больше — то все лишние ячейки будут заполнены значениями #Н/Д

    Какие особенности подобного применения функций массива:

    • во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать — так надо;
    • ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности — только все вместе. В противном случае просто получите сообщение «Нельзя изменять часть массива!». Бывает очень удобно иногда в целях защиты формул от изменений.

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

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

    Статья помогла? Поделись ссылкой с друзьями!

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

    Как узнать, является ли данная формула формулой массива? Что вообще она означает?

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

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

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

    К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к «функциям массива» (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.

    Читать еще:  Как сделать таблицу умножения в excel быстро?

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

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

    <23;-32;15;7>– это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.

    Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ(<23;-32;15;7>) – они идентичны:

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

    Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:

    Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):

    В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:

    А так выглядят эти значения в массиве:

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

    Тоже самое что и:

    Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив <"больше", "меньше", "больше", "больше">. Схематически вторую таблицу можно отобразить так:

    Так же ее можно прочитать функцией:

    В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.

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

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

    Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».

    Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.

    Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения «-32». Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:

    0;$A$1:$A$4;0))’ class=’formula’>

    Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив <ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА>. Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив <23; 0; 15; 7>. Затем эта таблица передается в качестве аргумента функции =СУММ(<23; 0; 15; 7>), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.

    Ни одна из описанных выше таблиц, в этом случае, не будет создана. Только одна ячейка диапазона будет проверена (ячейка в той же строке, в которой находится функция). В нашем случае 15>0 значит, как первый аргумент функция ЕСЛИ получит логическое значение ИСТИНА. Затем ВЕСЬ диапазон A1:A4 будет передан функции СУММ, и в результате функция возвращает значение равное 13 (23-32+15+7). Если бы в ячейке вместо значения 15 было число меньше нуля, функция ЕСЛИ в качестве аргумента получила бы значение ЛОЖЬ, и, следовательно, только значение нуля было бы передано функции СУММ. Наша функция СУММ в результате также вернет значение равное нулю.

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

    При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?

    Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.

    Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить «Ctr+Shift+Enter». Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.

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

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

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

    Нажмите клавишу F9 (или «Пересчет» в правом верхнем углу меню «Формулы»), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:

    — запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом — «;» (точкой с запятой).

    Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).

    Нажмите кнопку F9, и вы получите массив результатов вычислений, как показано ниже:

    То есть, созданный в памяти компьютера массив:

    Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):

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

    То есть, созданный в памяти компьютера массив:

    Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»

    После чего нажмите на кнопку «Вычислить»:

    В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:

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

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

    Ссылка на основную публикацию
    Adblock
    detector