Что означают фигурные скобки в excel

Что означают фигурные скобки в excel

На этом шаге мы рассмотрим ввод и редактирование формул массива, выделение массива.

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

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

Редактирование формулы массива
Если формула массива помещена в несколько ячеек, то необходимо отредактировать все ячейки диапазона как одну ячейку. Нельзя изменять только один элемент, содержщий формулу массива! Если Вы попытаетесь сделать это, Excel выдаст сообщение, показанное на рисунке 1.

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

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

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

Чтобы отредактировать формулу массива, выделите все ячейки массива и активизируйте строку формул (щелкните на ней или нажмите F2 ). При редактировании формулы Excel удаляет фигурные скобки. Закончив редактирование формулы, нажмите Ctrl + Shift + Enter , чтобы ввести изменения. Теперь содержимое всех ячеек массива изменится в соответствии с внесенными изменениями.

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

  • Перейдите к одной из ячеек диапазона массива. Выберете команду Правка | Перейти или нажмите клавишу F5 , в открывшемся диалоговом окне Переход щелкните на кнопке Выделить и в следующем диалоговом окне Выделение группы ячеек выберете опцию Текущий массив . Щелкните на кнопке OK , чтобы закрыть диалоговые окна.
  • Перейдите к одной из ячеек диапазона массива и нажмите комбинацию клавиш Ctrl + / , чтобы выделить весь массив.

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

На следующем шаге рассмотрим использование массивов констант.

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

На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу "обрабатывает". Подтверждение формулы с помощью сочетания клавиш "Ctrl + Shitf + Enter" – это является для 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))’ >

Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш 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). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.

Если Вы довольно много работаете в 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;
  • Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат

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

Ссылка на основную публикацию
Что означает ошибка 110
Ошибка 110 в Android происходит главным образом при обновлении или установке приложений из Google Play. Случается это из-за несовместимости ОС:...
Что выбрать windows 7 или windows 10
Сегодня в нашем блоге «Чо?! Чо?!» я раскрою все преимущества и недостатки новой операционной системы для ноутбуков, сравнив ее с...
Что в китае дешевле чем в россии
Я экономлю тысячи рублей, покупая товары из Китая через интернет Сегодня я расскажу Вам о том, что выгодно покупать в...
Что означает ошибка 963
Ошибки в Google Play дело достаточно частое, это не удивительно, ведь Плей маркет – это один из крупнейших магазинов приложений....