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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула массива с несколькими ячейками

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

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

  1. Скопируйте всю таблицу, приведенную ниже, и вставьте ее на пустой лист книги Excel, начиная с ячейки A1.

    Продавец
     

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

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

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

    Итоги
    продаж

    Зуева

    Седан

    5

    33 000

    Купе

    4

    37 000

    Егоров

    Седан

    6

    24 000

    Купе

    8

    21 000

    Еременко

    Седан

    3

    29 000

    Купе

    1

    31 000

    Климов

    Седан

    9

    24 000

    Купе

    5

    37 000

    Шашков

    Седан

    6

    33 000

    Купе

    8

    31 000

    Формула (общий итог)

    Общий итог

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

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

  2. Чтобы просмотреть итоги продаж автомобилей типа "купе" и "седан" для каждого продавца, выберите диапазон E2:E11, введите формулу =C2:C11*D2:D11, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

  3. Чтобы просмотреть итоги всех продаж, выберите ячейку F11, введите формулу =СУММ(C2:C11*D2:D11), а затем нажмите клавиши CTRL+SHIFT+ВВОД.

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

В приложении Excel откройте лист "Лист1" и выделите ячейки E2:E11. Нажмите клавишу F2 и введите формулу =C2:C11*D2:D11 в текущую ячейку E2. При нажатии клавиши ВВОД формула будет введена только в ячейку E2 и отобразится значение 165 000. А теперь, введя формулу, нажмите сочетание клавиш CTRL+SHIFT+ВВОД, а не клавишу ВВОД. В таком случае результат отобразится в ячейках E2:E11. Обратите внимание: формула в строке формул отображается в виде {=C2:C11*D2:D11}, указывая на то, что это формула массива (как показано в таблице ниже).

При нажатии клавиш CTRL+SHIFT+ВВОД в приложении Excel формула заключается в фигурные скобки ({ }), а экземпляр формулы подставляется в каждую ячейку выделенного диапазона. Эта операция выполняется очень быстро, поэтому вы сразу увидите в столбце E итоговую сумму продаж каждого из типов машин для каждого из продавцов. При выделении ячеек E2, E3, E4 и т. д. будет отображаться одна и та же формула — {=C2:C11*D2:D11}.

Итоговые суммы в столбце E, вычисленные с помощью формулы массива

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

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

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

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

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

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

  • Согласованность    Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.

  • Безопасность    Компонент формулы массива с несколькими ячейками нельзя переписать. Например, щелкните ячейку E3 и нажмите клавишу DELETE. Вам придется выделить весь диапазон ячеек (E2–E11) и изменить формулу для всех ячеек либо оставить массив без изменений. Помимо этого потребуется нажать сочетание клавиш CTRL+SHIFT+ВВОД, чтобы подтвердить изменение формулы, что еще больше повышает уровень безопасности.

  • Меньший размер файлов    Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в книге для вычисления результатов в столбце E применяется всего лишь одна формула массива. Если бы вы прибегли к обычным формулам (таким как =C2*D2, C3*D3, C4*D4 и т. д.), вам понадобилось бы 11 разных формул для получения таких же результатов.

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

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

Формулы массива — это эффективный способ создания сложных формул. Формула массива =СУММ(C2:C11*D2:D11) эквивалентна формуле =СУММ(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

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

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

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

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

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

  • Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала.

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

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

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

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

Мы добавили на лист несколько дополнительных строк с данными о продажах. Это строки 12–17. Теперь нам необходимо обновить формулы массива так, чтобы они охватывали эти дополнительные строки.

Выполните эти действия в классической версии приложения Excel (для этого скачайте книгу на компьютер).

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

  1. Скопируйте всю таблицу и вставьте ее на лист Excel, начиная с ячейки A1.

    Продавец
     

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

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

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

    Итоги
    продаж

    Зуева

    Седан

    5

    33 000

    165 000

    Купе

    4

    37 000

    148 000

    Егоров

    Седан

    6

    24 000

    144 000

    Купе

    8

    21 000

    168 000

    Еременко

    Седан

    3

    29 000

    87 000

    Купе

    1

    31 000

    31 000

    Климов

    Седан

    9

    24 000

    216 000

    Купе

    5

    37 000

    185 000

    Шашков

    Седан

    6

    33 000

    198 000

    Купе

    8

    31 000

    248 000

    Новиков

    Седан

    2

    27 000

    Купе

    3

    30 000

    Корепин

    Седан

    4

    22 000

    Купе

    1

    41 000

    Вронский

    Седан

    5

    32 000

    Купе

    3

    36 000

    Общий итог

  2. Выберите ячейку E18, введите формулу общего итога =СУММ(C2:C17*D2:D17) в ячейку A20, а затем нажмите клавиши CTRL+SHIFT+ВВОД.
    Общий итог составит 2 131 000 рублей.

  3. Выделите диапазон ячеек, содержащий текущую формулу массива (E2:E11), а также пустые ячейки (E12:E17), расположенные рядом с новыми данными. Другими словами, выделите ячейки E2:E17.

  4. Нажмите клавишу F2, чтобы переключиться в режим правки.

  5. В строке формул измените значение C11 на C17, измените значение D11 на D17, а затем нажмите клавиши CTRL+SHIFT+ВВОД.
    Формула в ячейках E2–E11 будет обновлена, при этом экземпляры новой формулы будут вставлены в новые ячейки, E12–E17.

  6. Введите формулу массива =СУММ(C2:C17*D2:D17) в ячейку F17, так чтобы она ссылалась на ячейки в строках 2–17, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы завершить ввод формулы массива.
    Новый общий итог составит 2 131 000 рублей.

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

Несмотря на все преимущества формул массива, у них имеются свои недостатки.

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

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

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

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

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

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

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

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

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

Так выглядит массив из одной строки: {1;2;3;4}. Массив из одного столбца: {1:2:3:4}. А вот массив из двух строк и четырех столбцов: {1;2;3;4:5;6;7;8}. В двухстрочном массиве первая строка представлена 1, 2, 3 и 4, а вторая строка — 5, 6, 7 и 8. Строки разделены двоеточием между записями 4 и 5.

Как и формулы массива, константы массива можно использовать с большинством встроенных функций 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 в ячейку B1, 5 — в C1, 6 — в D1 и 7 — в E1.

  3. В ячейке 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. Введите указанную ниже формулу, а затем нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

    =Квартал1

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

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

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

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

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

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

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

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

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

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

  1. Создайте новый лист и выделите блок пустых ячеек из четырех столбцов и трех строк.

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

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

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

  1. Выделите блок пустых ячеек из четырех столбцов и трех строк.

  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. На листе Excel выберите диапазон ячеек C8:E10, а затем введите такую формулу:

    ={10;20;30:40;50;60:70;80;90}

    Не забудьте ввести { (открывающую фигурную скобку) перед числом 10 и} (закрывающую фигурную скобку) после числа 90, так как вы создаете массив чисел.

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

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Выделите диапазон ячеек C1–E3.

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

    =C8:E10

    В ячейках C1–E3 отобразится массив ячеек 3x3 с теми же значениями, что и в ячейках C8–E10.

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

  1. Выделите диапазон ячеек C1:C3 и нажмите клавишу F2, чтобы перейти в режим правки.
    Формула массива остается без изменений: =C8:E10.

  2. Нажмите клавишу F9, чтобы преобразовать ссылки на ячейки в значения. Значения будут преобразованы в константы массива. Теперь формула будет выглядеть так (значения из массива C8:E10): ={10;20;30:40;50;60:70;80;90}

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

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

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

  1. Скопируйте всю таблицу и вставьте ее на лист, начиная с ячейки A1.

  2. Выберите ячейку A9 и нажмите клавиши CTRL+SHIFT+ВВОД, чтобы увидеть общее количество символов в диапазоне ячеек A2:A6 (67).

  3. Выберите ячейку A12 и нажмите клавиши CTRL+SHIFT+ВВОД, чтобы увидеть содержимое ячейки с наибольшим количеством символов в диапазоне A2:A6 (ячейка A6).

Данные

Это

группа ячеек,

собранных вместе,

чтобы составить

единое предложение.

Общее количество символов в диапазоне ячеек A2:A6

=СУММ(ДЛСТР(A2:A6))

Содержимое ячейки с наибольшим количеством символов (A6)

=ИНДЕКС(A2:A6;ПОИСКПОЗ(МАКС(ДЛСТР(A2:A6));ДЛСТР(A2:A6);0);1)

Приведенная ниже формула используется в ячейке A9 для подсчета общего количества символов (67) в ячейках A2–A6.

=СУММ(ДЛСТР(A2:A6))

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

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

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

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

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

    =НАИМЕНЬШИЙ(A5:A14;{1:2:3})

В ячейках A16–A18 отобразятся значения 400, 475 и 500 соответственно.

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

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

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

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

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

  1. Выделите ячейки A1–A3.

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

    =НАИБОЛЬШИЙ(A5:A14;СТРОКА(ДВССЫЛ("1:3")))

В ячейках A1–A3 отобразятся значения 3200, 2700 и 2000 соответственно.

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

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

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

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

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

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

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

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

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

=ИНДЕКС(A6:A9;ПОИСКПОЗ(МАКС(ДЛСТР(A6:A9));ДЛСТР(A6:A9);0);1)

В ячейке A16 отобразится текст "набор ячеек, которые".

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

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

(МАКС(ДЛСТР( A6 : A9 ))

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

ДЛСТР( A6:A9 )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Обзор формул

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

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

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

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×