Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Что такое формула массива

 

Вы, наверное, часто слышали или читали на форумах словосочетание "формула массива". Но не все знают что это такое и тем более, как пользоваться. Прошу не путать формулу массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
Большинство уже наверняка знакомо с функцией СУММ(SUMM). Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5). Получим сумму этих чисел - 15. Все просто и понятно. И вроде бы - функция уже работает с массивами - ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1). Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и... Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами. Ему надо явно указать, что мы хотим это сделать и результат вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш - Ctrl+Shift+Enter. Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки - {=СУММ(A1:A5+1)}. Если Вы после ввода их увидели - значит все сделано верно и формула массива введена правильно. И результат будет 20.

Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке {=СУММ(A1:A5+1)} и ни о какой формуле и суммировании речи быть уже не может.

Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5). Теперь мы изменили функцию и ввели её как формулу массива: {=СУММ(A1:A5+1)}. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1) =СУММ(2;3;4;5;6)

Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
{=МИН(ЕСЛИ(A1:A10<>0;A1:A10))}то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет - 1.


Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП(TRANSPOSE). Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10):

  • выделяете диапазон ячеек(D1:M3), равный по количеству ячеек исходному диапазону значений(A1:C10), которые необходимо транспонировать;
  • вписываете функцию ТРАНСП;
  • в качестве аргумента указываете ссылку на исходный диапазон значений: =ТРАНСП(A1:C10);
  • завершаете ввод функции сочетанием клавиш Ctrl+Shift+Enter.

В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции(D1:M3) должно быть равно числу столбцов в исходном диапазоне(A1:C10), а число столбцов - числу строк. Если указать меньше - не все значения будут транспонированы. Если больше - то все лишние ячейки будут заполнены значениями #Н/Д

Какие особенности подобного применения функций массива:

  • во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать - так надо;
  • ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности - только все вместе. В противном случае просто получите сообщение "Нельзя изменять часть массива!". Бывает очень удобно иногда в целях защиты формул от изменений.

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

  • ввод формулы завершается сочетанием клавиш Ctrl+Shift+Enter;
  • Если формулу массива записать сразу в несколько ячеек, то формула будет одна для всех ячеек и вернет для каждой ячейки свой результат

Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 10 коммент.
  1. Юрий:

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

  2. Вадим:

    Хочу сказать ОГРОМНОЕ спасибо автору. Всё расписано очень подробно и сразу все стало понятно)

  3. Наталия:

    Спасибо. Понятно, доступно. Все проделала. Получилось.

  4. Елена:

    Спасибо большое за помощь-очень доступно объяснили!

  5. Как перетащить формулу массива на другие строки?
    Формула у меня такая, но соответственно в фигурных скобках.

    =СУММ((отчеты!H5:H5555="гонорары")*(отчеты!F5:F5555))
    При перетаскивании приходится менять слово "гонорары"на другие статьи затрат сметы, это понятно, но почему меняется диапозон ячеек? Получается при копировании так:
    =СУММ((отчеты!H6:H5556="прочие_расходы")*(отчеты!F6:F5556))

  6. Allock:

    Формула смещается из-за указания относительных ссылок на ячейки.

    Необходимо закрепить суммируемый диапазон. Попробуйте так:
    =СУММ((отчеты!H$5:H$5555="гонорары")*(отчеты!F$5:F$5555)).

    А в целом в данном случае кажется более удобным использование СУММЕСЛИ.

  7. Ольга:

    Есть такой вопросик.. Мне надо, чтобы при протягивании формулы сумм диапазон суммируемых ячеек уменьшался на одну, например, я указала сумму рядом стоящих 10-ти ячеек, протягиваю формулу вниз, а она суммирует уже первых 9, потом 8 и так далее... Мне почему-то кажется, что мне поможет формула массива. Но я не знаю, как её применить. Помогите, пожалуйста! =)

  8. Ольга, формула массива ничем не поможет. А вот знание того, что такое относительные и абсолютные ссылки на ячейки - очень даже. Вы используете относительные, а нужны абсолютные. Можно заменить в нужных формулах ссылки относительные на абсолютные: Как поменять в формулах относительные ссылки на абсолютные и наоборот?

  9. Алексей:

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

  10. Алексей:

    Большое спасибо. Очень полезная информация.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти