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

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

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

В этой статье

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

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

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

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

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

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

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

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

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

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

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

  • Суммирование всех n-х значений в диапазоне значений.

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

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

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

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

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

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

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

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

  1. Откройте новую пустую книгу.

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

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

    1. Создайте пустую книгу или лист.

    2. Выделите пример в разделе справки.

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

      Выделение примера в справке
      Выделение примера в справке
    3. Нажмите клавиши CTRL+C.

    4. На листе выделите ячейку A1 и нажмите клавиши CTRL+V.

Продавец

Тип автомобиля

Число проданных единиц

Цена за единицу

Итоги продаж

Ильина

Седан

17

2200

Купе

16

1800

Егоров

Седан

18

2300

Купе

8

1700

Еременко

Седан

15

2000

Купе

1

1600

Климов

Седан

9

2150

Купе

17

1950

Шашков

Седан

18

2250

Купе

8

2000

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

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

    =C2:C11*D2:D11

  3. Нажмите клавиши CTRL+SHIFT+ВВОД.

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

Маслов

Седан

18

2500

Купе

7

35000

Авдеев

Седан

16

2200

Купе

15

2000

Глазков

Седан

8

2300

Купе

8

2100.

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

15

16

17

18

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}).

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

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

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

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

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

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

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

  2. В поле Имя введите Квартал1.

  3. В поле Диапазон введите следующую константу (не забудьте ввести скобки вручную):

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

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

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

  4. Нажмите кнопку ОК.

  5. На листе выделите строку из трех пустых ячеек.

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

    =Квартал1

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

Именованный массив, введенный в виде формулы

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

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

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

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

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

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

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

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

  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

15

16

1 200

этих мягких

17

18

7

8

3200

французских булок,

9

10

11

120

475

да выпей

13

14

15

16

500

чаю

2000

600

1700

800

2700

  1. Готовый лист должен выглядеть примерно так:

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

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

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

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

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

  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, чтобы преобразовать ссылки на ячейки в значения. Excel преобразует значения в константу массива.

  4. Нажмите клавиши CTRL+SHIFT+ВВОД, чтобы ввести константу массива в качестве формулы массива.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При попытке просуммировать диапазон, содержащий значения ошибки (например, #Н/Д), функция СУММ в 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.

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

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

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

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

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

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

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

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

Были ли сведения полезными?

Отлично! Что-то еще?

Что мы могли бы улучшить?

Спасибо за ваш отзыв!

×