Формулы массив, работа с массивами таблицы Excel

В этом разделе рассматриваются фор- мулы массива и поясняется, как вво- дить и изменять их, а также устранять связанные с ними проблемы.

Назначение формул массива

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

• Подсчет числа знаков в диапазоне ячеек.
• Суммирование только тех чисел, которые отвечают определенным условиям, например наименьших значений в диапазоне чисел, определенном верхней и нижней границами.
• Суммирование всех n-ных значений в диапазоне значений.

 Примечание. Формулы массива иногда называют «формулами CSE», поскольку для их ввода в рабочих книгах используется сочетание клавиш CTRL+SHIFT+ВВОД (ENTER).

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

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

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

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

Проверьте, как это работает!

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

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

1. Откройте новую пустую книгу.
2. Скопируйте данные листа примера и вставьте их в новую книгу, начиная с ячейки A1.

1. Чтобы применить конечное форматирование, нажмите расположенную рядом кнопку Параметры вставки .
2. Чтобы умножить значения в массиве (диапазон ячеек C2 \endash D11), выделите ячейки от E2 до E11, а затем введите в строке формул следующую формулу: =C2:C11*D2:D11
3. Нажмите клавиши CTRL+SHIFT+ВВОД.

Формула будет заключена в фигурные скобки ({ }), экземпляр формулы будет подставлен в каждую ячейку выделенного диапазона. Эта операция выполняется очень быстро, поэтому вы сразу увидите в столбце E итоговую сумму продаж каждого из типов машин для каждого из продавцов.

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

1. В ячейке A13 книги введите текст Итоги продаж.
2. В ячейке B13 введите следующую формулу (и нажмите клавиши CTRL+SHIFT+ВВОД):

=СУММ(C2:C11*D2:D11)

В этом случае будет выполнено перемножение значений массива (диапазон ячеек C2 \endash D11), а затем \endash при помощи функции СУММА \endash сложение полученных результатов. Общий итог продаж составит 111 800 рублей. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул. Например, предположим, что у вас есть 15 000 строк с данными. Вы можете частично или полностью сложить их значения, создав формулу массива в отдельной ячейке.

Кроме того, обратите внимание на то, что формула с одной ячейкой (в ячейке B13) полностью независима от формулы с несколькими ячейками (формула в ячейках E2—E11). Это указывает на еще одно преимущество использования формул массива — их гибкость. Можно выполнять с данными любые действия, например изменить формулы в столбце E или вообще удалить этот столбец, и это не повлияет на формулу с одной ячейкой.

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

• Согласованность Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
• Безопасность Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E3 и нажмите клавишу DELETE. Вам придется выделить весь диапазон ячеек (E2 \endash E11) и изменить формулу для всех ячеек либо оставить массив без изменений. Помимо этого вы должны будете нажать сочетание клавиш CTRL+SHIFT+ВВОД, чтобы подтвердить изменение формулы, что еще больше повышает уровень безопасности.
• Меньший размер файлов Зачастую вместо нескольких промежуточ-ных формул можно использовать одну формулу массива. Например, в созданной для этого упражнения рабочей книге для вычисления результатов в столбце E применяется всего лишь одна формула. Если бы вы прибегли к обычным формулам (таким как =C2*D2), вам понадобилось бы 11 разных формул для получения таких же результатов.

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

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

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

=C2*D2
=C3*D3

и так далее. Формула с одной ячейкой, находящаяся в ячейке B13, содержит в кратком виде все эти операции умножения, а также арифметические действия, требуемые для сложения промежуточных итогов: =E2+E3+E4 и так далее.

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

Главное правило при создании формулы массива стоит повторить еще раз: нажимайте сочетание клавиш CTRL+SHIFT+ВВОД, когда хотите ввести или изменить формулу массива. Это правило действует как в отношении формул с одной ячейкой, так и в отношении формул с несколькими ячейками.

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

• Необходимо выделить диапазон ячеек, который будет содержать результаты, прежде чем вводить формулу. Вы сделали это в шаге 3 упражнения по созданию формулы массива с несколькими ячейками, когда выбрали ячейки E2 \endash E11.
• Содержимое отдельной ячейки в формуле массива изменить нельзя. Чтобы проверить это правило, выделите ячейку E3 в образце книги и нажмите клавишу DELETE.
• Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала.

 Чтобы удалить формулу массива, выделите ее целиком (например, =C2:C11*D2:D11), нажмите клавишу DELETE, а затем — клавиши CTRL+SHIFT+ВВОД.

• В формулу массива с несколькими ячейками нельзя вставить пустые строки (или удалить строки из нее).

Расширение диапазона формулы массива

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

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

1. Выделите диапазон ячеек, содержащий текущую формулу массива (E2:E11), а также пустые ячейки (E12:E17), расположенные рядом с новыми данными. Другими словами, выделите ячейки E2:E17.
2. Нажмите клавишу F2, чтобы переключиться в режим правки.
3. В строке формулы измените значение C11 на C17, измените значение D11 на значение D17, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД. Формула в ячейках E2 \endash E11 будет обновлена, при этом экземпляры новой формулы будут вставлены в новые ячейки, E12 \endash E17.

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

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

• Вы можете случайно забыть нажать сочетание клавиш CTRL+SHIFT+ВВОД. Не забывайте нажимать это сочетание клавиш каждый раз при вводе или изменении формулы массива.
• Другие пользователи могут не понять ваши формулы. На практике формулы массива редко документируются на листе, поэтому если необходимо обеспечить возможность изменения книг другими пользователями, следует избегать использования формул массива или описать, как можно изменять их.
• Большие формулы массива могут замедлять вычисления на компьютерах с невысокой скоростью процессора или небольшим объемом памяти.

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

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

Сведения о константах

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

Краткое введение в константы массива

Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:

={1,2,3,4,5}

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

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

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

Создание одномерных и двумерных констант

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

Создание горизонтальной константы

1. Воспользуйтесь книгой из предыдущих примеров или создайте новую книгу.
2. Выделите ячейки от A1 до E1 включительно.
3. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

={1,2,3,4,5}

 Примечание. В этом случае следует ввести открывающую и закрывающую фигурные скобки ({ }).

Будет отображен следующий результат:

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

Создание вертикальной константы

1. Выделите в книге столбец из пяти ячеек.
2. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

={1;2;3;4;5}

Будет отображен следующий результат:

Создание двумерной константы

1. Выделите в книге блок ячеек из четырех столбцов и трех строк.
2. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

={1,2,3,4;5,6,7,8;9,10,11,12}

Вы получите следующий результат:

Использование констант в формулах

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

1. Откройте пустую книгу.
2. Скопируйте представленную ниже таблицу, начиная с ячейки A1. Для применения конечного форматирования воспользуйтесь расположенной рядом кнопкой Параметры вставки.

1. В ячейке A3 введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=СУММА(A1:E1*{1,2,3,4,5})

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

В ячейке A3 появится значение 85. В следующем разделе поясняется работа этой формулы.

Краткий обзор синтаксиса констант массивов

Использованная формула состоит из нескольких частей.

Последний элемент в скобках представляет собой константу массива: {1,2,3,4,5}. Следует учитывать, что в Excel константы массива не заключаются в скобки автоматически. Это необходимо сделать вручную. Кроме того, не забывайте, что после добавления константы в формулу массива требуется нажать сочетание клавиш CTRL+SHIFT+ВВОД, чтобы ввести эту формулу.

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

=СУММА(A1*1,B1*2,C1*3,D1*4,E1*5)

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

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

=СУММА({3,4,5,6,7}*{1,2,3,4,5})

Для этого скопируйте функцию, выделите пустую ячейку в книге, вставьте формулу в строку формул, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД. Вы получите тот же результат, что и в предыдущем упражнении, где использовалась формула массива =СУММА(A1:E1*{1,2,3,4,5}).

Элементы, которые можно использовать в константах

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

Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1,2,A1:D4} или {1,2,СУММА(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки.

Именование констант массива

Возможно, лучший способ использовать константы массива \endash присвоить им имена. Именованные константы проще использовать, они позволяют частично скрыть сложность формул массива от начинающих пользователей. Чтобы присвоить имя константе массива, а затем использовать ее в качестве формулы, выполните следующие действия:

1. На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя.
   Откроется диалоговое окно Присвоить имя.
2. В поле Имя введите Квартал1.
3. В поле Объект ссылки введите следующую константу (не забудьте ввести скобки вручную):

={"Январь","Февраль","Март"}

Содержимое этого диалогового окна должно иметь следующий вид:

1. Нажмите кнопку ОК.
2. В рабочем листе выделите строку из трех пустых ячеек.
3. Введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=Квартал1

Будет отображен следующий результат:

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

Устранение проблем, связанных с константами массива

Если константы массива не действуют, проверьте наличие перечисленных ниже причин.

• Возможно, некоторые элементы разделены неверным знаком. Если запятая или точка с запятой опущена или указана в неверном месте, создание константы может завершиться неудачей либо может быть выведено предупреждение.
• Возможно, выделен диапазон ячеек, не соответствующий числу элементов в константе. Например, если выделен столбец из шести ячеек для использования в константе с пятью ячейками, в пустой ячейке будет выведено значение ошибки «#N/A». Наоборот, если выделено слишком мало ячеек, значения, не имеющие соответствующей ячейки, будут пропущены.

Использование констант массива

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

Умножение каждого из элементов массива

1. Выделите блок пустых ячеек из четырех столбцов и трех строк.
2. Введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

={1,2,3,4;5,6,7,8;9,10,11,12}*2

Возведение в квадрат элементов массива

• Выделите блок пустых ячеек из четырех столбцов и трех строк.
• Введите указанную ниже формулу массива, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

Можно также ввести следующую формулу массива, где используется оператор возведения в степень (^):

={1,2,3,4;5,6,7,8;9,10,11,12}^2

Транспонирование одномерной строки

1. Выделите столбец из пяти пустых ячеек.
2. Введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=ТРАНСП({1,2,3,4,5})

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

Транспонирование одномерного столбца

1. Выделите строку из пяти пустых ячеек.
2. Введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=ТРАНСП({1;2;3;4;5})

Даже если была введена вертикальная константа массива, функция ТРАНСП преобразует константу массива в строку.

Транспонирование двумерного массива

1. Выделите блок ячеек из трех столбцов и четырех строк.
2. Введите следующую константу, а затем нажмите клавиши CTRL+SHIFT+ВВОД:

=ТРАНСП({1,2,3,4;5,6,7,8;9,10,11,12})

Функция ТРАНСП преобразует каждую из строк в последовательность столбцов.

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

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

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

Создайте два листа с использованием данных этого раздела.

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

Завершенный лист должен иметь следующий вид:

1. Назовите первый лист Данные, а второй пустой лист — Массивы.

Создание массивов и констант массива на основе существующих значений

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

Создание массива на основе существующих значений

1. В книге примера выберите лист Массивы.
2. Выделите диапазон ячеек C1—E3.
3. Введите в строке формулы указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=Данные!E1:G3

Будет отображен следующий результат:

Формула будет ссылаться на значения, хранящиеся в диапазоне ячеек E1 \endash G3 на листе «Данные». Альтернатива использованию данной формулы массива с несколькими ячейками \endash вставить отдельную формулу в каждую ячейку листа «Массивы» указанным ниже образом.

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

Создание константы массива на основе существующих значений

1. На листе Массивы выделите ячейки C1—E3.
2. Нажмите клавишу F2, чтобы переключиться в режим правки.
3. Нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива.
4. Нажмите клавиши CTRL+SHIFT+ВВОД, чтобы ввести константу массива в качестве формулы массива.

Формула массива =Данные!E1:G3 будет заменена следующей константой массива:

={1,2,3;5,6,7;9,10,11}

Связь между листами «Данные» и «Массивы» будет разорвана, а формула массива будет заменена константой массива.

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

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

• На листе Данные введите указанную ниже формулу в ячейке C7, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=СУММ(ДЛСТР(C1:C5))

В ячейке C7 появится значение47.

В данном случае функция ДЛСТР возвращает длину каждой текстовой строки в каждой из ячеек диапазона. Затем функция СУММА складывает эти значения и выводит полученный результат в ячейке C7, которая содержит формулу.

Поиск n наименьших значений в диапазоне

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

1. На листе Данные выделите ячейки A12—A14.

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

1. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=НАИМЕНЬШИЙ(A1:A10,{1;2;3})

В ячейках A12—A14 появятся значения 400, 475и 500 соответственно.

В этой формуле используется константа массива, при помощи которой три раза выполняется функция НАИМЕНЬШИЙ, в результате чего возвращаются три элемента массива, находящегося в ячейках A1:A10, которые имеют наименьшие значения (1, 2 и 3 по возрастанию). Чтобы найти дополнительные значения, следует добавить аргументы в константу и соответствующее число ячеек результата к диапазону A12:A14. С этой формулой также используются дополнительные функции, например СУММА или СРЗНАЧ. Например:

=СУММ(НАИМЕНЬШИЙ(A1:A10,{1;2;3}))

=СРЗНАЧ(НАИМЕНЬШИЙ(A1:A10,{1;2;3}))

Поиск n наибольших значений в диапазоне

Чтобы найти наибольшие значения в диапазоне, следует заменить функцию НАИМЕНЬШИЙ функцией НАИБОЛЬШИЙ. Кроме того, в следующем примере используются функции СТРОКА и ДВССЫЛ.

1. На листе Данные выделите ячейки A12—A14.
2. Нажмите клавишу DELETE, чтобы удалить существующую формулу, не отменяя выделение ячеек.
3. В строке формул введите указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=НАИБОЛЬШИЙ(A1:A10,СТРОКА(ДВССЫЛ("1:3")))

В ячейках A12—A14 появятся значения 3200, 2700и 2000 соответственно.

На этом этапе может быть полезно ознакомиться с функциями СТРОКА и ДВССЫЛ. При помощи функции СТРОКА можно создать массив последовательных целых чисел. Например, выделите пустой столбец из 10 ячеек в книге примера, введите указанную ниже формулу массива в ячейках A1:A10, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

=СТРОКА(1:10)

В результате выполнения этой формулы будет создан столбец, содержащий 10 последовательных целочисленных значений. Чтобы понять, где может возникнуть проблема, вставьте строку над диапазоном, содержащим формулу массива (то есть над строкой 1). Ссылки на строки будут изменены и в результате выполнения формулы вы получите числа от 2 до 11. Чтобы избежать возникновения этой проблемы, следует добавить в формулу функцию ДВССЫЛ.

=СТРОКА(ДВССЫЛ("1:10"))

В функции ДВССЫЛ в качестве аргументов используются текстовые строки (именно поэтому диапазон 1:10 заключен в двойные кавычки). Текстовые значения не изменяются при вставке строк или перемещении формулы массива. В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений.

Разберем формулу, использованную ранее, функции ДВССЫЛ в качестве аргументов, можно использовать — =НАИБОЛЬШИЙ(A1:A10,СТРОКА(ДВССЫЛ("1:3"))) — начиная с внутренних скобок и переходя к внешним. Функция ДВССЫЛ возвращает набор текстовых значений, в данном случае значений с 1 по 3 включительно. В свою очередь, функция СТРОКА создает вертикальный массив из трех ячеек. Функция НАИБОЛЬШИЙ трижды выполняет оценку значений, находящихся в диапазоне ячеек A1:A10, по одному разу для каждой из ссылок, возвращенных функцией СТРОКА. В вертикальный массив из трех ячеек возвращаются значения 3200, 2700 и 2000. Если нужно найти дополнительные значения, расширьте диапазон ячеек в функции ДВССЫЛ.

Эту формулу можно использовать и с другими функциями, такими как СУММ и СРЗНАЧ.

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

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

• На рабочем листе Данные очистите существующую формулу в ячейке C7, введите в этой ячейке указанную ниже формулу, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

=ИНДЕКС(C1:C5,ПОИСКПОЗ(МАКС(ДЛСТР(C1:C5)),ДЛСТР(C1:C5),0),1)

В ячейку C7 будет подставлено значениеперепрыгнул.

Рассмотрим эту формулу, начиная с элементов в скобках. Функция ДЛСТР возвращает длину каждого из элементов в диапазоне ячеек C1:C5. Функция МАКС вычисляет наибольшее значение среди этих элементов, соответствующее строке текста максимальной длины, которая находится в ячейке C3.

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

(МАКС(ДЛСТР(C1:C5))

из следующего массива:

ДЛСТР(C1:C5)

Аргумент типа соответствия равен 0. Тип соответствия может иметь значение 1, 0 или -1. Если для аргумента задано значение 1, функция ПОИСКПОЗ возвращает наибольшее значение, которое не превышает искомое значение. Если для аргумента задано значение 0, функция ПОИСКПОЗ возвращает первое значение, равное искомому. Если для аргумента задано значение -1, функция ПОИСКПОЗ ищет наименьшее из значений, которые превышают искомое значение или равны ему. Если тип соответствия не указан, предполагается, что он равен 1.

Наконец, функция ИНДЕКС имеет следующие документы: массив, номер строки и столбца в этом массиве. Массив образуется диапазоном ячеек C1:C5, функция ПОИСКПОЗ задает адрес ячейки, а последний аргумент (1) указывает, что значение должно быть найдено в первом столбце массива.

Использование более сложных формул массива

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

Суммирование диапазона, который содержит значения ошибки

При попытке просуммировать диапазон, содержащий значения ошибки (например, #N/A) функция СУММА в Excel не работает. В следующем ниже примере демонстрируется, как просуммировать значения в диапазоне с именем «Данные», который содержит ошибки.

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),"",Данные))

В результате выполнения этой формулы создается новый массив, содержащий исходные значения за вычетом значений ошибки. Разберем эту функцию, начиная с внутренних скобок и переходя к внешним. Функция ОШИБКА выполняет поиск ошибок в указанном диапазоне ячеек (Данные). Функция ЕСЛИ возвращает конкретное значение, если при проверке заданного условия получено значение ИСТИНА, и другое значение, если получено значение ЛОЖЬ. В данном случае для всех значений ошибки возвращаются пустые строки (""), поскольку условие выполнено (значение ИСТИНА), а для остальных ячеек указанного диапазона (Данные) возвращаются содержащиеся в них значения, так как условие не выполнено (значение ЛОЖЬ), то есть они не содержат значения ошибки. При помощи функции СУММА вычисляется итоговое значение по отфильтрованному массиву.

Подсчет количества значений ошибки в диапазоне

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

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),1,0))

В результате выполнения этой формулы создается массив, в котором для ячеек со значениями ошибки указывается значение 1, а для других ячеек \endash значение 0. Для получения аналогичного результата указанную формулу можно упростить, удалив третий аргумент функции ЕСЛИ следующим образом:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные),1))

Если этот аргумент не задан и ячейка не содержит значения ошибки, функция ЕСЛИ возвращает значение ЛОЖЬ. Формулу можно упростить еще больше:

=СУММ(ЕСЛИ(ЕОШИБКА(Данные)*1))

Такой вариант формулы допустим, поскольку ИСТИНА*1=1 и ЛОЖЬ*1=0.

Суммирование значений с учетом условий

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

=СУММ(ЕСЛИ(Продажи>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив положительных значений и ложных значений. Функция СУММА будет игнорировать ложные значения, поскольку 0+0=0. Используемый в этой формуле диапазон ячеек может включать любое количество строк и столбцов.

Можно также выполнить суммирование значений с учетом нескольких условий. Например, в следующей формуле массива вычисления выполняются по значениям, которые больше 0 и не меньше 5:

=СУММ((Продажи>0)*(Продажи<=5)*(Продажи))

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

Доступно создание формул массива с использованием условия типа ИЛИ. Например, можно выполнить сложение значений, которые меньше 5 и больше 15:

=СУММ(ЕСЛИ((Продажи<5)+(Продажи>15),Продажи))

Функция ЕСЛИ возвращает все значения меньше 5 и больше 15, которые затем передаются в функцию СУММ.

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

Подсчет среднего значения с исключением нулей

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

=СРЗНАЧ(ЕСЛИ(Продажи<>0,Продажи))

В результате выполнения функции ЕСЛИ создается массив значений, не равных 0, которые затем передаются в функцию СРЗНАЧ.

Подсчет количества различающихся ячеек в двух диапазонах

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

=СУММ(ЕСЛИ(МоиДанные=ДругиеДанные,0,1))

В результате выполнения формулы создается массив, имеющий такой же размер, что и сравниваемые диапазоны. Функция ЕСЛИ заполняет массив значениями 0 и 1 (0 для ячеек с различными значениями, 1 \endash для ячеек с одинаковыми значениями). Затем функция СУММА возвращает сумму значений в массиве.

Эту формулу можно упростить следующим образом:

=СУММ(1*(МоиДанные<>ДругиеДанные))

Подобно формуле, подсчитывающей количество значений ошибки в диапазоне, эта формула работает благодаря тому, что ИСТИНА*1=1, а ЛОЖЬ*1=0.

Поиск позиции максимального значения в диапазоне

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

=МИН(ЕСЛИ(Данные=МАКС(Данные),СТРОКА(Данные),""))

В результате выполнения функции ЕСЛИ создается новый массив, соответствующий диапазону с именем "Данные". Если соответствующая ячейка содержит максимальное значение в диапазоне, массив будет содержать номер этой строки. В обратном случае массив содержит пустую строку (""). Функция МИН использует полученный массив в качестве своего второго аргумента и возвращает наименьшее значение, соответствующее номеру строки с максимальным значением в диапазоне с именем "Данные". Если диапазон "Данные" содержит одинаковые максимальные значения, формула возвращает строку первого значения.

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

=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные),СТРОКА(Данные),"")),СТОЛБЕЦ(Данные))

Загрузка...