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

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

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

• 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. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММ выполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

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

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

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

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

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

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

Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант: =СУММ(*). Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов: . Созданный массив используется впоследствии в качестве аргумента для функции СУММ, которая возвращает результат вычислений (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. Сумма произведений массива рабочего листа и массива констант

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В двумерных массивах используются точки с запятыми (запятые в английской версии программы) для разделения горизонтальных элементов и двоеточия (точки с запятой в английской версии программы) для разделения вертикальных элементов. Ниже приведен пример массива констант размерностью 3×4: . Для отображения этого массива в диапазоне требуется двенадцать ячеек. Чтобы ввести этот массив в диапазон, выделите диапазон ячеек, состоящий из трех строк и четырех столбцов. Затем введите следующую формулу = после чего нажмите (рис. 6).

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

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

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

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

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

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

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

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

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

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

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

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

Примеры употребления «Фигурные скобки» в русском

Excel автоматически заключает формулу в фигурные скобки <>. Excel will automatically wrap the formula in braces <> for you.

Excel автоматически заключит формулу в фигурные скобки <>. Excel will automatically wrap the formula in braces <>.

Фигурные скобки означают, что это формула массива. The «curly» braces that surround the formula indicate that this is an array formula.

При попытке вручную ввести фигурные скобки Excel отобразит формулу как текст. If you try to enter the braces yourself, Excel will display your formula as text.

Вопросительный знак, восклицательный знак, кавычки скобки, фигурные скобки , квадратные скобки и многоточие. Question mark, exclamation point, quotation marks brackets, parentheses, braces and ellipses.

Примечание: При вводе формулы массива Excel автоматически заключает ее в фигурные скобки < и >. Note: When you enter an array formula, Excel automatically inserts the formula between < >(braces).

У нее есть ряд особенностей. Не вводите фигурные скобки вручную: вместо этого нажмите клавиши CTRL+SHIFT+ВВОД, и Excel заключит формулу =СУММ(ЕСЛИ(A2:A6>20;A2:A6)) в скобки. An array formula requires some special treatment: Instead of typing the braces yourself, you must instead press CTRL+SHIFT+ENTER — Excel then surrounds the formula =SUM(IF(A2:A6>20,A2:A6)) with the braces.

Данные кода поля выводятся между фигурными скобками и выглядят следующим образом: The field code information appears between curly brackets and looks like this:

Раскройте скобки в уравнении. Open the brackets in the equation.

Иначе вы ели бы куриные палочки и фигурные макароны. Normally you’d be having chicken fingers and maccaroni.

Кто бы ни курировал процесс с кремлевской стороны, ему явно была не нужна очередная пиар-катастрофа в стиле Pussy Riot. Поэтому в ответ на попытку Павленского политизировать суд прокуратура попыталась полностью вынести политику за скобки . Whoever was directing the trial for the Kremlin clearly was not interested in another Pussy Riot-style publicity disaster, so, in response to Pavlensky’s attempt to politicize the trial, the prosecution sought to remove politics altogether.

Но если посмотреть поближе, они вроде тех белого медведя и хаски когти убраны, шерсть не торчит, взгляд мягкий, открытая пасть без клыков, балетные движения фигурные движения — все это специфика игры. But if you look closely, they’re kind of like the polar bear and husky: no claws, flat fur, soft eyes, open mouth with no fangs, balletic movements, curvilinear movements — all specific to play.

Если угодно, в вопросе о существовании Бога он агностичен и просто выносит его за скобки . It is agnostic, if you will, on the question of God’s existence — a question that is way above its pay grade.

Для обозначения фоновых звуков используйте квадратные скобки . Use square brackets to designate background sounds.

Недопустимые символы, например, скобки , двоеточия, точки с запятой и т. д. There’s invalid characters like parentheses, colons, semicolons, etc.

Я думаю, что у темноволосого стоят скобки . I think the dark-haired one has braces.

Фундамент Нации, закрыть скобки , Лимитед, была одиннадцатой из предоставленных вам за последние десять дней. Foundation of the Nation, close brackets, Limited, was the eleventh granted you in the past ten days.

Если в качестве аргумента «ссылка» используется несмежный диапазон, его необходимо заключить в скобки If you are entering a nonadjacent range for the reference, enclose reference in parentheses.

У вас могут возникают проблемы при закрытии книги, открытой в режиме защищенного просмотра, если имя файла Excel содержит квадратные скобки (например, «Книга1[1234].xlsx»). You may have problems closing a workbook in protected view when the Excel file name contains square brackets (e.g., Book1[1234].xlsx).

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

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

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

Весенний АПП-Фест: цифры и итоги

Этой весной мы приняли участие в проекте «Весенний АПП-Фест», который задумала и реализовала Ассоциация преподавателей перевода. Проект завершен, и вот его результаты. — На участие в олимпиаде «Язык

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

������� ���������� ��������� MS Office 2007: Microsoft Excel

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

Одна из наиболее интересных (и наиболее мощных) возможностей Excel — допустимость использования массивов в формулах.

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

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

Задача 5.8. Формулы массивов для диапазонов ячеек

На рис.95 показан простой рабочий лист, на котором вычисляются

объемы продаж некоторых товаров. Чтобы вычислить объем продаж каждого конкретного товара (значения в столбце D ), обычно используется формула умножения количества проданного товара (столбец С ) и цены товара (столбец В ). Например, в ячейке D2 будет формула = В2*С2 , которая затем копируется на все оставшиеся ячейки столбца D . В данном случае получим пять отдельных формул в столбце D .

Другой способ вычисления пяти значений в столбце D — использование одной формулы массива. Эта формула займет диапазон D2:D5 и вернет сразу все пять искомых значений.

Для создания формулы массива следует выполнить следующее.

1. Выделить диапазон, в котором должен содержаться результат. В нашем примере это диапазон D2:D5 .
2. Ввести формулу =В2:В5*С2:С5 . Поставьте равно и выделите диапазон В2:В5 , поставьте значок умножить и выделите диа­пазон С2:С5 .
3. Нажмите комбинацию клавиш Ctrl+Shift+Enter , чтобы ввести формулу как формулу массива (обычно для завершения ввода формулы следует нажать клавишу Enter ).

Формула будет введена сразу во все пять выделенных ячеек. Если вы посмотрите в строку формул, то там будет представлена формула , т.е. Excel произвел попарное умножение элементов массивов В2:В5 и С2:С5 и создал новый массив стоимостей D2:D5 .

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

Задача 5.9 . Формулы массивов для отдельных ячеек

Формулы массивов могут возвращать результат и в одну ячейку. На рис.96 в ячейку D7 введена формула массива

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

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

Формула, как и предыдущая, работает с двумя массивами, храня­щимися в диапазонах В2:В5 и С2:С7 . Формула перемножает соответ­ствующие значения из этих массивов виртуальный массив, который существует только в памяти компьютера. Затем функция СУММ обраба­тывает этот новый массив и возвращает сумму его значений.

В данном случае вместо формулы массива можно воспользоваться функцией СУММПРОИЗВ , которая вернет тот же результат: = СУММПРОИЗВ (В2:В6;С2:С6) .

Задача 5.10. Именованные массивы

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

Пусть нам известна сумма прихода и сумма расхода . Необходимо вычислить доход как разность этих величин. Введите исходные данные в таблицу (рис.97).

Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5 . Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен . Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2:В5) и третьим (С2:С5) столбцами таблицы последовательно и присвойте имена во вкладке Формулы-присвоить Имя . Диапазону В2:В5 присвойте имя Приход , а диапазону С2:С5 — имя Расход .

Ввод табличной формулы с использованием имен диапазонов.

Вводим в диапазон D2:D5 формулу массива.

• Выделим блок D2: D5 . В этом блоке активна ячейка D2 .
• Наберем знак равенства =.
• Нажмем функциональную клавишу F3 . Появится окно Вставка имени . Выберем имя Приход и щелкнем Ок . Формула примет вид: = Приход .
• Наберем знак минус (-).
• Вновь нажмем клавишу F3 . В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ок . Формула примет вид: = Приход-Расход.
• Нажмем сочетание клавиш Shift+Ctrl+Enter . Во всех ячейках блока появится формула .

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

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12 и повторите все шаги. Выделите блок D9:D12 . В этом блоке активной ячейкой является D12 . Наберите знак равенства =. Выделите блок В9:В12 , наберите знак минус -, выделите блок С9: С12 , нажмите сочетание клавиш Shift+Ctrl+Enter . Во всех ячейках блока появится формула . Мы получили две идентичные таблицы.

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

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

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

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

1. Выделить весь диапазон, содержащий формулу массива.
2. Нажать клавишу F2 , чтобы перейти в режим редактирования.
3. Нажать комбинацию клавиш Ctrl+Enter . Это действие вводит отдельные формулы (не формулу массива) в каждую ячейку выделенного диапазона.
4. Изменить выделение диапазона так, чтобы он включал новые ячейки (или исключал лишние).
5. Нажать клавишу F2 .
6. Нажать комбинацию клавиш Shift+Ctrl+Enter .

Коррекция формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год — 1996, приход — 240, расход 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, поступили бы просто: скопировали бы формулу из ячейки D5 в D6 . Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим — образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

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

• Во вкладке Формулы-Диспетчер имен выделить Приход , и внизу окна в Диапазон изменить диапазон значений с В1:С5 на В1:С6 . Для его изменения удалить имеющийся в окне диапазон и, перейдя на лист, выделить новый диапазон В1:С6 . Точно также изменить диапазон для Расхода . После изменений диалоговое окно закрыть.
• Выделить D2: D6 , нажать клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажать клавиши Shift+ Ctrl+Enter .

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

• Выделить D8 :D13 и нажать клавишу F2 для редактирования фор­мулы.
• Изменить в формуле ссылки на диапазоны ячеек с В8:В12 на В8:В13 и с С8: С12 на С8:С13 . Нажать сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока. Теперь необходимо удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменить поименованные блоки (в результате в ячейке D6 результат отобразится как #Н/Д — недоступно). Выделить блок с формулой массива, нажать клавишу F2 . Ввод закончить клавишами Ctrl+Enter.

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

Очистить последнюю строку таблицы. Выделить блок D2: D5 , нажать клавишу F2 , нажать клавиши Shift+Ctrl+Enter .

Аналогично поступить со второй таблицей:

• Выделить диапазон с формулой массива.
• Нажать комбинацию клавиш Ctrl+Enter , чтобы преобразовать формулу массива в обычную.
• Удалить последнюю строчку таблицы.
• Выделить диапазон для формулы массива, D9: D12 .
• Нажать F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удален­ной строки. Для корректировки ссылок в формуле можно умень­шить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменять­ся в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 ( В13 изменить на В12 и С13 на С12 ).
• Закончить изменения клавишами Shift+Ctrl+Enter .

Задача 5.11. Создание массивов на основе значений ячеек диапазона.

На рис.98 показан рабочий лист, содержащий данные в диапазоне A1: С4.

Диапазон С8:F11 содержит массив, созданный на основе этих данных с помощью формулы

Массив в диапазоне С8 :F11 связан со значениями диапазона А1:С4 .

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

Задача 5.12 . Транспонирование массива.

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

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

Для размещения транспонированного массива следует выделить диапазон ячеек (рис. 100). Поскольку исходный массив ячеек состоял из 8 строк на 2 столбца, то необходимо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. Далее в выделенном диапазоне ввести функцию транспонирования =ТРАНСП (, где в качестве аргумента функции является наш массив ячеек А1:В8. Заканчиваем ввод формулы комбинацией клавиш Ctrl+Shift+Enter и получаем «перевернутый массив» в качестве результата (рис. 101).

Задача 5.13. Таблица умножения.

Если вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке была таблица умножения вот такого вида (рис. 102).

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

• ввести два диапазона чисел от 1 до 10 в строке 1 и столбце А;
• выделить диапазон В2:К11 ;
• ввести формулу =А2:A11*В1:К1 ;
• закончить ввод формулы комбинацией клавиш Ctrl+Shift+Enter , чтобы Excel воспринял ее как формулу массива. Результат пред­ставлен ниже (рис. 103).

Задача 5.14. Выборочное суммирование.

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

В данном случае формула массива синхронно пробегает по всем эле­ментам диапазонов СЗ:С21 и ВЗ:В21 , проверяя, совпадают ли они с задан­ными значениями из ячеек G4 и G5 .

Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Можно применить и другой способ:

Нам необходимо просуммировать числа из диапазона D3:D21 (диапазон суммирования) при выполнении нескольких (двух) условий. Для каждой строки в случае одновременного равенства числа из диапазона СЗ:С21 числу G8, и числа из диапазона ВЗ:В21 числу G9 , соответствующее число из диапазона суммирования будет суммироваться.

Для этого применим функцию СУММЕСЯИМН.

=СУММЕСЛИМН (D3:D21; СЗ:С21; G8; ВЗ:В21; G9)

На открывшейся форме СУММЕСЯИМН в окне Диапазон суммирования выбрать диапазон для суммирования ( D3:D21 ). Для прописания условий в окне Диапазон_условия1 необходимо выбрать диапазон первого условия СЗ:С21 , который равен критерию G8 — окно Условие1 , в окне Диапазон_условия2 ввести диапазон второго условия ВЗ:В21 , который должен равняться G9 — окно Условие2 .

Попробуйте решить задачу двумя способами и сравните результат.

Вернемся к задаче обработки данных метеостанции ( Задача 5.5 ). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ . Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:Н14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводится формула = ЕСЛИ(И(ВЗ>20; в3<80) ; 1; 0) и копируется в остальные ячейки блока F3:H14 . В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 — выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 — в противном случае. Для подсчета суммы нормальных месяцев вводим в ячейку F25 формулу =СУММ (F3:F14) и копируем ее в блок G25:Н25 . Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу 0;ЕСЛИ (ВЗ :В14 и скопируем ее в С25:D25 . (Таким образом, компьютер, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае — проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае — 0). Формула 20;ВЗ <80) ;1;0) ) >не приведет к успеху, так как функция И не является дистрибутивной.

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

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу <=СУММ(ЕСЛИ(ВЗ:В14>20;ЕСЛИ(ВЗ:В14 и скопируйте ее в С26:D26 . В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26 , выберите значок Автосумма ). Вы получите блок, показанный на рис. 105.

В блоке A1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение . На новом листе запишите любую последовательность чисел в блок A1:А10 .

Окончательное решение можно записать одной формулой, поместив его в ячейку А12 .

<=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(A1:А10)-1; «возрастающая»; «не является возрастающей»)>

Разбор этой формулы:

• А2:А10-А1:А9 (т.е., из А10 вычитается А9 , из А9 вычитается А8 и т.д.) — образует блок, состоящий из первых разностей элементов исходного блока;
• ЕСЛИ (А2 : А10-А1: А9>0; 1; 0) — составляет блок из индикаторов положительных первых разностей;
• СУММ (ЕСЛИ (А2 :А10-А1 :А9>0; 1; 0) ) — считает количество ненулевых элементов в блоке индикаторов;
• СЧЕТ (A1: А10)-1 — рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;
• и, наконец, если количество ненулевых элементов в блоке инди­каторов равно размеру блока индикаторов, то последователь­ность возрастающая, иначе — нет.

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