Советы и лайфхаки

Массивы в экселе – Работа с массивами функций в Excel

Формулы массива в Excel - введение

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

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

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

В чем преимущества использования этих формул? Огласите списочек Я бы выделил следующие:

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

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

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

Первый пример, пока простенький.

У нас есть два диапазона. Мы хотим получить сумму их произведений. Это простой пример, который я обычно реализую следующим образом: добавляю третий столбец (или строчку), получаю произведения и суммирую весь столбец

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

=СУММ()

Затем определяем аргументы – а это для нас сумма массива, который сам по себе  является произведением двух массивов, т.е.

=СУММ(массив1*массив2)

И последний, завершающий штрих, без которого Excel огрызнется «сам дурак!» - надо нажать в режиме редактирования CTRL+Shift+Enter.

Есть нюанс – вообще-то есть функция СУММПРОИЗВ, которая призвана давать сумму произведений массивов. Так что прежде чем использовать ФМ, надо посмотреть, может нам помогут функции СУММЕСЛИ и ей подобные?

Пример второй, сложноватый.

Найти сумму 5го, 6го и 7го наибольших элементов набора данных. Как это делается без ФМ: таблица сортируется по убыванию, берется дополнительный столбец, там проставляются порядковые номера и через функцию СУММЕСЛИ вытаскиваем сумму. Ну или еще каким-нибудь способом, которым это можно сделать. Не сильно проще.

Формула массива делает все без дополнительных телодвижений:

=СУММ(НАИБОЛЬШИЙ(B3:B25;{5;6;7}))

Понятно? Нет? Ну, давайте по шагам:

  1. Функция НАИБОЛЬШИЙ ищет те элементы, которые заданы (5,6 и 7-й) по порядку убывания. Результатом будет три числа, т.е. отдельный массив.
  2. Функция СУММ суммирует этот массив.

Не буду говорить за всех, я чаще всего использую в ФМ следующие функции:

ЕСЛИ

ИНДЕКС

ПОИСКПОЗ

СТРОКА (СТОЛБЕЦ)

НАИБОЛЬШИЙ (НАИМЕНЬШИЙ)

СУММ

И(ИЛИ)

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

  1. Для нахождения в какой-либо таблице ВСЕХ(!) элементов, отвечающих заданному условию.

Кто уже просматривал мой «Ускоренный практикум», помнит, что с помощью ФМ я выстраивал первую пятерку игроков, ранжируя по определенному признаку. Т.е., у меня например есть таблица банков, где один из признаков – количество банкоматов. В принципе, самое простое решение – использовать функцию

НАИБОЛЬШИЙ, чтобы вытащить первую пятерку (десятку, двадцатку) значений по показателю банкоматов и затем с помощью функции ВПР найти соответствующие им названия банков. Но что делать, если значения повторяются? (Забудем на время про сводную таблицу.) Т.е., Альфабанк (не путать с реальным названием ) имеет одно и то же количество банкоматов, что и Бетабанк. ВПР в этом случае найдет только первого, того, кто в таблице стоит повыше. Т.е. будет в пятерке банков два Альфабанка.

  1. Для автоматической сортировки и фильтрации элементов.

Для решения такой же задачи стандартными средствами потребовалось бы все время пользоваться сортировкой или сводной таблицей. И в этом случае ссылаться на эту таблицу было бы не то что сложно, но немного муторно. А у меня например, есть в списке тех отчетов, что я составляю, рейтинг банков. Он составляется на ежемесячной основе и содержит 10-20 показателей – кредитный портфель, депозитный портфель, ROE, ROA и т.д. И по форме отчета есть 10-20 таблиц, которые являются основой для диаграмм, соответственно должны быть отсортированы по убыванию.

Само собой, мне, как и каждому экселисту, лень было делать эти сортировки, поэтому я просто настроил ФМ и теперь просто завожу данные, а они уже сами сортируются и выделяются нужными цветами.

Тонкости использования ФМ:

  1. Бывает, приходится использовать константы, типа {1;2;3;4;5}. Это проще, чем где-то его набивать и вставлять ссылку. Но надо учитывать, что если числа разделяются точкой с запятой, то это горизонтальный массив. А если двоеточием – {1:2:3:4:5} – то вертикальный.
  2. ФМ может быть применена как к ячейке, так и к диапазону. Соответственно, на выходе будет диапазон. Менять формулу в этом случае можно только во всем диапазоне. Как вводится и меняется формула – выделяется диапазон, и забивается формула, завершается CTRL+Shift+Enter.
  3. Отличительным признаком ФМ являются фигурные скобки. Они видны, пока не перейдете в режим редактирования ячейки. Как-то непривычно порой разбирая формулы, видеть, что там где должна быть ссылка на ячейку, стоит диапазон – ЕСЛИ(А1:А5>0;1;0). Меня это сильно удивляло, как так .

Кто еще считает, что можно нормально обойтись и без ФМ, напишите в комментариях к статье, сможете ли реализовать расчет цены остатков на складе по методу ФИФО/ЛИФО.

Эксель Практик
«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

excelpractic.ru

Знакомство с формулами массива в Excel

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

Одномерные и двухмерные массивы

В Excel различают одномерные и двухмерные массивы. Одномерный массив, его еще называют вектором, может храниться в одной строке (горизонтальный массив) …

…или в одном столбце (вертикальный массив).

Двухмерный массив хранится в диапазоне ячеек, его еще называют матрицей.

В отличие от языка программирования VBA, Excel не поддерживает трехмерные массивы.

Размерность массива

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

Примеры приведены для русифицированной версии Excel.

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

Чтобы вставить такой массив в Excel, необходимо сначала выделить строку, состоящую из 5 ячеек, затем ввести формулу массива и нажать комбинацию клавиш Ctrl+Shift+Enter.

  1. Элементы в одномерном вертикальном массиве разделяются двоеточием (:). Например, на рисунке ниже представлен одномерный вертикальный массив, который состоит из 4 элементов.Если ввести его на рабочий лист, получим следующий результат:

  2. В двухмерных массивах для разделения элементов в строке используется точка с запятой, а для разделения строк – двоеточие. На рисунке ниже представлен массив, который состоит из 3 строк и 5 столбцов.Если ввести данный массив в Excel, результат будет таким:

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

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

В качестве заполнителя отсутствующего элемента в массиве можно использовать значение #Н/Д (нет данных). К примеру, следующая запись будет синтаксически верной:

Введя данный массив на рабочий лист Excel, получим следующее:

Одноячеечные и многоячеечные формулы массива

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

Во втором случае формула вводится в одну ячейку и возвращает только одно значение:

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Редактирование формул массива в Excel

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

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

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

Ctrl+Shift+Enter.

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

  1. Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.
  2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.
  3. Нельзя перемещать ячейки, которые входят в формулу массива. Зато можно переместить весь массив.
  4. Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.
  5. Нельзя использовать многоячеечные формулы массива в таблицах, созданных с помощью команды Таблица.

Как видите, все перечисленные выше правила подчеркивают, что массив – это одно целое. Если не выполнить, хотя бы одно из вышеперечисленных правил, Excel не даст отредактировать массив и выдаст следующее предупреждение:

Выделение массива в Excel

Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:

  1. Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ.
  2. С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву:А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.

    Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.

    Текущий массив будет выделен:

  3. При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию.

Как удалить формулу массива

Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.

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

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

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

  1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.
  2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.
  3. Внесите необходимые корректировки в формулу:
  4. А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.

Изменение размеров формулы массива

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

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Массивы | Понятный Excel

     Добрый день уважаемый читатель!      В этой статье я хочу рассмотреть еще один вариант использования функции ВПР в Экселе для подстановки значения. Для тех, кто видит мои статьи впервые и не знает о других возможностях этой функции, можете почитать о них отдельно, это и возможность поиска с учетом регистра значений, и поиск данных с […]

     Добрый день уважаемый читатель!    В статье я хочу вам рассказать о возможности произвести поиск по нескольким критериям. Не думаю, что стоит объяснять всю полезность этого варианта поиска, так как возможность найти данные, которые соответствуют нескольким условиям, частенько бывает очень полезно.      Я уже раннее описывал разные варианты поиска, это и возможность поиска с […]

     Добрый день уважаемый читатель!      В этой статьи я хочу показать и рассказать вам, как производить поиск данных, когда регистр имеет значение. Ранее, на сайте я уже описывал много интересных возможностей функции ВПР, это и интервальный просмотр данных, и поиск по нескольким листам и просто описывал, как улучшить возможности работы этой функции. Отдельно я […]

      Добрый день, уважаемый читатель TopExcel.ru!       Продолжая раскрывать тайны использования функций в Excel, я предлагаю вашему вниманию возможность, произвести выбор значений в Excel, когда возвращается не только первое найденное значение по условию, а абсолютно все найденные значения. Я уже раннее описывал разные возможности выбора значений с помощью функции ВПР, это и интервальный просмотр значений, […]

      Добрый день уважаемый пользователь Excel!       Продолжая серию статей о возможностях работы с функцией ВПР в Excel, рассмотрим в этой статье, как работает ВПР с интервальным просмотром данных. Очень удобно эту возможность использовать, когда существует несколько условий для формирования расчётов. Часто такая необходимость встречается в работе экономистов, нормировщиков всех направлений, специалистов отдела кадров, работникам […]

      Добрый день уважаемый пользователь Excel!      Я хочу продолжить описание работы с массивами данных, и сейчас рассмотрим способы улучшения работоспособности функции ВПР. Для чего это собственно нужно, расширять и улучшать функционал работы функции ВПР в Excel, просто достаточно часто возникает необходимость собрать все доступные вхождение условий, а не только первое, которое встретится. […]

     Добрый день!     Сегодня я хочу расширить границы использования функции ВПР и научить вас использовать эту функцию, что бы произвести поиск значений в Excel по нескольким листам вашего рабочего файла.      Как вы знаете или еще не знаете, я напоминаю, в чистом виде функция ВПР производит поиск только в одной таблице, а о том, […]

     Добрый день уважаемый читатель!      В этой статье я хочу снова вспомнить о могуществе и пользе функции ВПР и покажу 4 способа поиска данных в таблице Excel при помощи других функций и их комбинаций с несколькими условиями. Очень и очень много действий можно выполнять с помощью этой функции, но, тем не менее, она обладает […]

       Доброго времени суток друзья!      В этой статье, я хочу научить вас использовать для поиска и извлечения данных функцию ВПР в Excel при проведении вычислений или анализе. Я уже ранее описывал саму функцию, ее синтаксис, как и где она применяется и варианты ее использования в работе. Так что, прочитав обзор о работе функции ВПР, […]

     Приветствую вас на сайте TopExcel!     Темой этой статьи я хочу продолжить рассмотрения функционала раздела «Ссылки и массивы», а именно как работает функция СТОЛБЕЦ в Excel. Совсем недавно я рассматривал сестру этой функции – это функция СТРОКА. Общий принцип работы для этих функций и остается одинаковый, разнятся только координаты и направления, исходя из […]

topexcel.ru

Формулы массива Excel. - EXCEL-OFFICE.RU

Формулы массива Excel. - EXCEL-OFFICE.RU Формулы в Excel Формулы массива Excel.       В Excel   можно писать формулы не только в отдельные ячейки, но сразу в диапазоне ячеек. Эти формулы называют в Excel «формулы массива».
      Массив в Excel – это любое количество ячеек с данными (кроме одной), расположенных в строках, столбцах, диапазонах. Можно сказать, массив в Excel – это диапазон ячеек. Что такое диапазон, как его выделять, читайте в статье «Что такое диапазон в Excel» здесь. 
      Здесь рассмотрим, как ввести формулу массива в Excel, чем отличаются простые формулы от формул массива. Разберем примеры формулы массива в Excel. Начнем с простого. У нас такая таблица.
      Первый пример.
Чтобы сложить эти числа, достаточно поставить автосумму. Получится такая формула.    =СУММ(A1:A6) Результат получился = 21.
      Теперь нам нужно прибавить к каждому числу в столбце А число 2. Затем, полученные суммы сложить. Чтобы так посчитать простой формулой, нам нужно куда-то внести число 2, м.б. в столбец В, затем сложить все по строкам, затем автосуммой сложить числа в столбце – это займет очень много времени и сил.
      Вот здесь нам поможет формула массива.
В ячейку А7 пишем такую формулу.      =СУММ(A1:A6+2)
      Внимание!
      Как ввести формулу массива в Excel.
Теперь, чтобы ввести эту формулу, как формулу массива, нажимаем НЕ «Enter», а сочетание клавиш – «Ctrl» + «Shift» + «Enter».
      Формула получится такая.  {=СУММ(А1:А6+2)} 
Вся формула стоит в фигурных скобках – это значит, что это формулу массива. Excel понимает как нужно считать, когда видит фигурные скобки. А именно - к числу 1+2, к 2+2, к 3+2, к 4+2, к 5+2, к 6+2. Затем, получившиеся суммы сложит. Получится результат = 33.
      Корректировка формулы массива в Excel.
Корректируем формулу массива, как обычную формулу, НО, для ее ввода нажимаем НЕ «Enter», а сочетание клавиш – «Ctrl» + «Shift» + «Enter».
      Второй пример.
Если нам нужно не прибавить к числам столбца А число 2, а умножить числа столбца А на цифру 2, то в формуле так и пишем. Получится такая формула.    {=СУММ(A1:A6*2)}    Результат получится = 42.
      Третий пример.
Формулу массива можно установить не только в одной ячейке, но и в диапазоне ячеек. Например, нам нужно умножить числа столбца А на числа строки 1. Как написать такую формулу подробно описано в статье «Как сделать таблицу умножения в Excel» тут.
      Четвертый пример.
Нам нужно сложить данные не из всех ячеек столбца, а из каждой второй или третьей, т.д. ячейки. Здесь нам поможет формула массива. Смотрите статью «Как в Excel сложить каждую вторую ячейку, строку».      Пятый пример.
Формулой массива можно проводить сложные операции в таблице, например, сортировать по условию, используя различные функции. Этот способ удобен тем, что при изменении данных в столбце, автоматически изменятся данные сортировки. Подробнее о такой формуле массива читайте в статье «Сортировка в Excel формулой» здесь.
      Шестой пример.     
С помощью формулы массива можно удалить из списка, столбца пустые ячейки. Смотри этот способ в статье «Как удалить пустые ячейки в Excel».
      Седьмой пример.
Мы видим, что много вариантов применения формулы массива. Рассмотрим последний пример, который поможет написать алфавит. Этот алфавит пригодится для раскрывающегося списка, когда фамилии распределяются по буквам. Подробнее об этом смотрите в статье «Связанный выпадающий список в Excel по алфавиту».
      В Excel есть несколько способов быстро узнать или посчитать сумму ячеек. Узнать сумму в Excel можно без установки формулы. Есть несколько способов установки и применения формул суммы, вычитания, т.д. в Excel. Несколько способов мы рассмотрели в статье "Сумма в Excel".

www.excel-office.ru

Работа с массивами в Excel 🚩 Создание, изменение

Сегодня мы будем рассматривать массивы в Microsoft Excel и операции с ними. По сути это те же таблицы, но не всегда. Чтобы более подробно разобраться в этом мы подготовили для вас статью, где постарались расписать, как создаются массивы, как работать с большими массивами данных и прочее.

Работаем с массивами

Массив данных – это некоторые значения, которые разбросаны по строкам, а также столбцам. Таковыми можно считать матрицы, а также векторы. Чтобы выполнять какие-либо вычисления с массивами важно правильно указывать формулу:

  1. Выделяются все нужные ячейки.
  2. Вписывается нужная формула.
  3. Далее следует перейти в строку, где вводятся формулы и нажать Ctrl + Shift + Enter, затем формула автоматически окажется в скобках.

Вот так, по сути, и происходит создание массива в Экселе.

Как откорректировать содержимое массива

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

1. Сначала нужно закрыть появляющееся окно, кликнув на кнопку «ОК».

2. Там, где располагается строка формул есть группа значков, и среди них можно увидеть крестик. Нужно кликнуть на него либо, как вариант, использовать клавишу Esc.

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

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

  • Когда все будет отредактировано, следует нажать Ctrl+Shift+Esc.

Чтобы удалить формулу:

  • Выделяются все ячейки с результатом. Далее нужно нажать клавишу Delete.

  • Формула исчезнет и можно будет указывать любую другую.

Функции массивов

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

Затем нужно указать аргументы. Далее будет совершен ввод, следует установить курсор мыши в строку формул и нажать Ctrl+Shift+Enter.

СУММ

Это самая часто используемая функция. Благодаря ей можно складывать отдельные ячейки или даже целые массивы.

ТРАНСП

Благодаря этой функции пользователь может менять строки и столбцы матрицы местами. Синтаксис: =ТРАНСП(массив)

Оператор МОБР

Эта функция предоставляет возможность производить расчет обратной матрицы. Важно учесть, что ее можно вычислить только, если в ней содержится одинаковое количество столбцов и строк, а определитель не равняется нулю. Синтаксис: =МОБР(массив).

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

Заключение

Теперь вы знаете, как в Excel сделать массив данных, каким образом вообще происходит работа с массивами Excel и обработка массивов данных. Кстати, довольно часто при работе с ними пользователь забывает нажимать Ctrl + Shift + Enter. Внимательно изучите все, что описано выше и запомните: если верно ввести функцию, она решит задачи и примеры любой сложности.

Это может быть интересно:

tehno-bum.ru

Формула массива Excel. Как пользоваться?

Промежуточные итоги в Excel Специальная вставка в Excel

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

Какие преимущества можно получить при использовании формул массива?

Когда не помогают другие возможности Excel, приходиться прибегать к формулам массива (например, транспонирование формулой).

Итак, начнем с самих массивов. Объясняя простым языком, массив представляет собой набор данных, обрабатываемых в Excel одним параметром (аргументом).

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

Пример 1. Формула массива Excel

Имеются два диапазона чисел. Нам требуется получить сумму произведений диапазонов. Обычный пример можно реализовать так: добавляется дополнительный, третий по счету столбец (либо строчка), после чего получается произведение и делается суммирование всего столбца.

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

=СУММ()

После этого определим аргументы, представляющие сумму этого массива в виде произведения из двух массивов, таким образом:

=СУММ(Диапазон1*Диапазон2)

В нашем случае

=СУММ(A1:A10*B1:B10)

В завершении, нажимаем одновременно клавиши в следующей комбинации: CTRL+Shift+Enter

Нажатие этого сочетания и означает запуск Формулы массива! Конечная формула будет заключена в фигурные скобки {}

{=СУММ(A1:A10*B1:B10)}

Нюанс. Имеется специальная функция, =СУММПРОИЗВ(), предназначенная для получения суммы сразу всех произведений в массиве.

Пример №2. Практическое применение

Требуется определить сумму 1-го, 3-го и 10-го наибольших элементов из набора данных (диапазона B1:B10). Не прибегая к ФМ это делается так: сортируем по убыванию таблицу, воспользуемся дополнительным столбцом, проставляем в нем порядковые номера и извлекаем сумму при помощи функции =СУММЕСЛИ().

Но формулой массива все делается проще:

=СУММ(НАИБОЛЬШИЙ(B1:B10;{1;3;10}))

1. Здесь функция =НАИБОЛЬШИЙ() осуществляет поиск по убыванию элементов, которые были заданы (в нашем случае это 10, 8 и 1) в диапазоне данных B1:B10

2. Суммирование данного массива происходит с использованием известной функции =СУММ().

3. CTRL+Shift+Enter, не забываем

Удобно, я считаю.

Пример файла

И напоследок еще раз обозначу  условия использования формул массива

— завершаем ввод формулы комбинацией клавиш вида Ctrl+Shift+Enter;

— где произведен ввод формулы массива, нельзя вносить изменения в ячейки диапазона по отдельности — делать это можно только одновременно со всеми ячейками, иначе получим уведомление вида «Нельзя изменить эту часть массива». Для примера с транспонирование формулой

 

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

Промежуточные итоги в Excel Специальная вставка в Excel

excelworks.ru

Отправить ответ

avatar
  Подписаться  
Уведомление о