Рекомендации и примеры по использованию формул массива

Чтобы в полной мере использовать возможности Excel, необходимо уметь работать с формулами массива, которые позволяют выполнять вычисления, недоступные при помощи других формул. В основе этой статьи лежит серия заметок «Уверенный пользователь Excel», составленных Колином Уилкоксом (Colin Wilcox) по материалам глав 14 и 15 книги Формулы в Excel 2002 (на английском языке), которая была написана Джоном Уокенбахом (John Walkenbach), Excel MVP. Дополнительные сведения о других книгах Джона см. на его странице, посвященной книгам (на английском языке).

В этой статье


Сведения о формулах массива

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

Зачем нужны формулы массива

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

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

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

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

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

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

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

А теперь проверьте, как это действует!

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

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

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

ПоказатьКопирование данных рабочего листа примера

  • Создайте пустую книгу или лист.
  • Выделите пример в разделе справки.

 Примечание.   Не выделяйте заголовок строки или столбца.

Выделение примера в справке.

Выделение примера в справке.
  • Нажмите сочетание клавиш CTRL+C
  • На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.

Продавец Тип автомобиля Число проданных единиц Цена единицы Итоги продаж
Кравцов седан 5 2 200  
  купе 4 1 800  
Игольников седан 6 2 300  
  купе 8 1 700  
Мазурова седан 3 2 000  
  купе 1 1 600  
Шевцова седан 9 2 150  
  купе 5 1 950  
Полев седан 6 2 250  
  купе 8 2 000  

  1. Для применения форматирования листа назначения воспользуйтесь кнопкой Параметры вставкиЗначок кнопки, которая отображается рядом.
  2. Чтобы умножить значения в массиве (диапазон ячеек C2 – D11), выделите ячейки от E2 до E11, а затем введите в строке формул следующую формулу:

=C2:C11*D2:D11

  1. Нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

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


Данные примера


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

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

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

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

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

В дополнение к этому использование формул массива несет следующие преимущества:

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

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

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

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

=C2*D2
=C3*D3

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

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

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

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

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

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

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

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

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

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

Томин седан 6 2 500
  купе 7 1 900
Вонг седан 4 2 200
  купе 3 2 000
Янов седан 8 2 300
  купе 8 2 100

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

Данные примера


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

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

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

К началу страницы К началу страницы

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

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

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

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

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

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

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

Как и формулы массива, константы массива можно использовать с любыми встроенными функциями 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. Для применения форматирования листа назначения воспользуйтесь кнопкой Параметры вставкиКнопка параметров вставки, которая отображается рядом.

3 4 5 6 7

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

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

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


Формула массива с константой массива


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

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

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


Синтаксис формулы массива с константой массива

Выноска 1 Функция
Выноска 2 Сохраненный массив
Выноска 3 Оператор
Выноска 4 Константа массива

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

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

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

  1. На вкладке Формулы в группе Присвоенные имена выберите команду Присвоить имя.

Откроется диалоговое окно Присвоение имени.

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

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

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

Диалоговое окно «Изменение имени» с формулой

  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.
400   быстрый   1 2 3 4
1 200   рыжий лис   5 6 7 8
3 200   перепрыгнул   9 10 11 12
475   через ленивого   13 14 15 16
500   пользователя          
2 000              
600              
1 700              
800              
2 700              
  1. Завершенный лист должен иметь следующий вид.

  2. Заполненные данные примера

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

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

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

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

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

=Данные!E1:G3

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

Результаты на листе «Массивы»

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


=Данные!E1 =Данные!F1 =Данные!G1
=Данные!E2 =Данные!F2 =Данные!G2
=Данные!E3 =Данные!F3 =Данные!G3

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

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

  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, по одному разу для каждой из ссылок, возвращенных функцией СТРОКА. В вертикальный массив из трех ячеек возвращаются значения 3 200, 2 700 и 2 000. Если нужно найти дополнительные значения, увеличьте диапазон в функции ДВССЫЛ.

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

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

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

  • На рабочем листе Данные очистите существующую формулу в ячейке 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) указывает, что значение должно быть найдено в первом столбце массива.

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

К началу страницы К началу страницы

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

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

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

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

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

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

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

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

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

В результате выполнения этой формулы создается массив, в котором для ячеек со значениями ошибки указывается значение 1, а для других ячеек – значение 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 – для ячеек с одинаковыми значениями). Затем функция СУММА возвращает сумму значений в массиве.

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

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

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

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

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

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

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

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

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

К началу страницы К началу страницы

 
 
Применимо к:
Excel 2007