Работа в excel с массивами данных: Работа с массивами функций в Excel

Формула массива в Excel. Массивы данных и значений.

просмотрено: 1

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

Доброе время суток всем!  В текущем уроке мы рассмотрим работу с таким удобным инструментом Excel, как массивы и формулы массивов. Что же это такое и как с этим работать? Если говорить официально, то массив это диапазон, содержащий данные одного типа. Массив обрабатывается как одно целое. Именно поэтому поменять формулу только для части массива невозможно.  Почему это удобно и как с этим работать? Давайте разберемся на практике. Прежде всего стоит заметить, что те ячейки с определенной структурой и границами, которые мы гордо называем таблицами – это не таблицы. Почему? Потому что данные в таблице связаны между собой. Точно так же, как номер телефона в телефонной книге привязан к определенному абоненту. Для простоты, чтобы проще различать таблицы и диапазоны между собой, в русской версии Excel настоящие таблицы называются умными.

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

Диапазоны могут хранить данные разного типа. В Excel это могут быть числа, текст, логические данные, дата, время, ссылки на объекты. Вектор ячеек, в котором находятся данные произвольного типа, одинакового или нет – неважно, называют списком. А вот любой диапазон, в том числе и вектор, содержащий данные одного типа, называют массивом. В чем его польза и преимущество? В том, что если тип данных одинаковый, то для них можно использовать одинаковые формулы. Массив можно рассматривать как единое целое, состоящее из отдельных элементов. Поэтому любую формулу можно применять сразу ко всему массиву, не используя ее по отдельности для каждой ячейки. Именно такая формула, связанная с массивом в целом, а не с отдельной его частью, называется формула массива. Приведем примеры использования формул массива для решения практичеких задач.

Простой пример использования массива для получения общего итога.

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

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

Однако давайте внимательно посмотрим на итоговую формулу. В скобках указан диапазон, значения ячеек которого просуммированы. Что же находится в этих ячейках? Очевидно, что результаты умножения ячеек в текущей строке из столбцов «В» и «С» с ценой и количеством соответственно.

А теперь подумаем вот о чем. Диапазон с ценами – это массив? Безусловно, там же только числа. Аналогично и диапазон с количеством также является массивом. А если это массивы, то вместо того, чтобы попарно перемножать значения цены и находящегося в той же строки количества,  можно применить Функцию «СУММ» непосредственно к произведению этих массивов!

В результате вместо трех действий – написание формулы, копирование ее вниз, применение новой формулы – мы использовали только одну формулу с тем же результатом. Мы сделали ту же работу, но гораздо быстрее. 

Обратите внимание на фигурные скобки в строке формул. Они говорят о том, что перед нами не просто формула, а формула массива. Для ее ввода одновременно нажимаем комбинацию клавиш «Ctrl + Shift + Enter». Если просто нажать»Enter» то программа решит, что мы вводим обычную формулу. Она просто не поймет, как можно в ней ОДНОВРЕМЕННО перемножать несколько ячеек и даст ошибку. Поэтому будьте внимательны!

Применение массива для подсчета отработанных часов.

Рассмотрим на примере, как используется формула массива в типичной для бухгалтеров ситуации  по начислению заработной платы. Мы применяем для начисления почасовую оплату труда. Раньше, что греха таить, всем ставили одинаковые часы. Главное, чтобы сотрудник на работу пришел, а 8 отработанных часов в табеле будут в любом случае.

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

В качестве варианта можно поступить таким образом. Вначале извлечь и просуммировать только часы. Затем извлечь и просуммировать минуты. Минуты превратить в часы, поделив полученное значение на 60. Наконец, сложить начальную сумму часов с суммой часов, полученных из минут. Все хорошо, но только пока вы высчитываете часы максимум для десяти человек. А если их хотя бы 100? Причем в реальности вариантов гораздо больше. Это и срок доставки, когда мы оплачиваем за каждый час работы курьера. Это и время перевозок. Примеров много. Как же быть? На помощь опять приходит формула массива. В ней мы сделаем все этапы, перечисленные выше, вместе, применив для расчета функции ЧАС, МИНУТЫ И СУММ.

ЧАС – извлекает из указанного времени значение часа. В частности, формула =ЧАС(«10:52») даст результат 10.

МИНУТЫ – аналогична предыдущей, но извлекает соответственно минуты. Таким образом, формула =МИНУТЫ(«10:52») даст результат 52. Кстати, обратите внимание на кавычки в формуле. Они нужны, так как время указано явно, а не получено из ячейки.

СУММ – суммирует данные.

В итоге для данного примера получаем комбинацию:

{=СУММ(ЧАС(D2:D6))+СУММ(МИНУТЫ(D2:D6))/60}

Не забываем про нажатие в конце сочетания клавиш «Ctrl + Shift + Enter».

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

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

В следующем примере мы с помощью формулы массива получим среднюю, минимальную и максимальную цену по каждому товару. Причем, если среднюю цену можно найти с помощью встроенной функции СРЗНАЧЕСЛИ, то функций МИНЕСЛИ и МАКСЕСЛИ не предусмотрено.

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

{=СРЗНАЧ(ЕСЛИ($A$2:$A$17=F2;$B$2:$B$17))}

Формула расшифровывается так: если в диапазоне $A$2:$A$17 значение очередной ячейки совпадает с значением в ячейке F2, то для соответствующих   ячеек из диапазона $B$2:$B$17 вычислить среднее значение. Excel загрузит таблицу, уберет из нее все строки, которые не содержат нужное нам наименование. После этого по оставшимся строкам в цене – а $B$2:$B$17 это диапазон с ценами – вычисляется   среднее значение.

Если теперь отфильтровать наименования, выбрав только «Стол» и вычислить среднее значение по отображаемым ячейкам с помощью стандартных функций СРЗНАЧ или СРЗНАЧЕСЛИ, то закономерно выходит тот же результат.

Это значит, что формула написана верно. Поменяв в ней СРЗНАЧ на МИН, мы можем найти минимальное значение цены для выбранного товара, поменяв на МАКС – максимальное значение цены, ну и так далее.

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

 С помощью фактически ОДНОЙ ФОРМУЛЫ получили несколько РАЗНЫХ ВАРИАНТОВ итоговых значений. Это и минимум, и максимум, и среднее. Удобно? Конечно!

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

Выбор данных с помощью ВПР с учетом двух и более критериев.

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

Необходимо найти цену на хлеб в марте. Можно было бы применить сразу ВПР, но – позиций с хлебом несколько, как и позиций с мартом. Как быть? На помощь снова приходит формула массива.

Еще раз обращаю внимание на схожесть формулы с той, которую разбирали в предыдущей части. Конструкция ЕСЛИ($A$2:$A$19=F2;$B$2:$D$19) позволила отсечь строки в диапазоне ;$B$2:$D$19 , у которых месяц совпадал с указанным в ячейке F2, то есть – мартом. Из оставшихся данных был создан виртуальный диапазон, в котором и сработала функция ВПР. Формулу можно было бы и усложнить, например, получив цену по хлебу не только в определенном месяце, но и по определенном городе.

Заключение.

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

Однако не стоит забывать, что такие формулы применяют тогда, когда надо стразу применить один и тот же расчет к множеству данных ОДНОГО типа. Если значения в диапазоне разного типа, то формула может и не сработать. В то же время — это мощное оружие в руках пользователя, и применять его для решения мелких задач все равно что из пушки стрелять по воробьям. Кроме этого, не забывайте, что для ввода формул массива обязательно требуется комбинация «Ctrl + Shift + Enter».

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

На этом наш урок завершен. Всем хорошего дня и успехов в работе! Если вас заинтересовал материал, то встретимся на занятиях в нашем учебном центре для еще более углубленного обучения возможностям Excel на практике.

Loginom — анализ за границами возможностей Excel

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

• Ограниченный массив данных
• Низкая производительность на больших данных
• Автоматизация выполнения цепочек действий
• Затрудненное переиспользование
• Сложность реализации API
• Продвинутая аналитика с помощью Loginom

«Excel повсюду. Пожалуй, это наиболее популярная программа за всю историю существования бизнес-приложений. Поэтому не будет большим преувеличением сказать, что все мы живем в мире Excel», – Michael Alexander, Microsoft Excel 2019 «Bible»

Эта цитата наиболее точно отражает реалии современного бизнеса. За долгие годы своего существования программа глубоко проникла на рынок. При помощи Excel пытаются решать чуть ли не все задачи бизнес-аналитики: от расчета KPI и визуализации до моделирования и прогнозирования. Пользователи любят продукт за интуитивный интерфейс и гибкость в решении разнообразных задач.

Однако Excel имеет несколько узких мест:

1. Ограничение по количеству обрабатываемых данных
2. Низкая производительность на больших объемах
3. Сложности при автоматизации выполнения последовательностей действий
4. Неудобство переиспользования сценариев обработки
5. Сложность реализации API (программного интерфейса приложения)

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

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

Рассмотрим подробнее те ограничения, с которыми сталкиваются пользователи Excel и варианты их преодоления при помощи Loginom.

Ограниченный массив данных

Все чаще аналитики вынуждены оперировать большими массивами данных. Это становится проблемой при работе в Excel, т.к. лист Excel может содержать не более 1 048 576 строк.

Может показаться, что миллион записей — это достаточный объем, который покрывает 99% потребностей пользователей. Однако на сегодня предприятиями генерируются такие большие массивы данных, что практически каждый аналитик сталкивается с этим ограничением.

Например, по данным годового отчета 2021г. X5 Retail Group, в день в среднестатистическом магазине «Пятерочка» продается ~140 тыс. единиц товара. То есть в Excel, невозможно проанализировать больше одной недели работы обычного магазина.

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

Ошибка обработки данных в Excel

Конечно, есть обходные пути решения:

• Использование надстроек и дополнительного ПО
• Распределение информации по нескольким листам или книгам
• Разделение данных на входе по нескольким файлам

Однако их применение влечет за собой следующие издержки:

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

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

Низкая производительность на больших данных

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

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

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

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

Автоматизация выполнения цепочек действий

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

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

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

Сценарий обработки в Loginom

Благодаря визуальному проектированию сложная аналитика выполняется в Loginom без кодирования. В сценарии можно реализовать любую последовательность действий. А в случае, когда стандартных средств недостаточно для решения бизнес-задач, есть возможность подключения готовых библиотек компонентов.

Затрудненное переиспользование

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

Для решения этой проблемы необходимо подключить личную книгу макросов в Excel — Personal.xlsb, которая будет автоматически открываться в фоновом режиме при каждом запуске программы. Помещенные в нее макросы будут доступны для всех книг на данном ПК. Однако при открытии Excel-файла на другом компьютере пользователь не сможет получить доступ к макросам.

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

Создание и переиспользование подмодели в Loginom

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

Сложность реализации API

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

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

Обмен данными при помощи стандартных API решает данную проблему простым и элегантным способом. В Loginom можно двумя щелчками опубликовать веб-сервисы с поддержкой REST JSON или SOAP XML. Использование API позволяет исключить любые ручные операции при передаче данных, что в свою очередь, ликвидирует риски искажения и порчи информации в результате человеческих ошибок.

Благодаря API доступ к данным может быть организован не только внутри одной компании, но и между разными организациями. Например, компания-партнер может получить необходимую информацию напрямую через веб-сервис без пересылки Excel-файлов.

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

Продвинутая аналитика с помощью Loginom

Excel по-прежнему остается одной из самых популярных программ для решения разнообразных задач. Это универсальное средство работы с небольшим объемом данных. Данный инструмент знаком практически каждому, и им действительно удобно пользоваться.

Тем не менее аналитики регулярно сталкиваются с ограничениями Excel. В этих случаях необходим новый инструмент. Применение платформы Loginom для решения задач продвинутой аналитики поможет преодолеть узкие места Excel:

• Обрабатывать большие объемы данных
• Реализовать сложную логику без программирования
• Обеспечить высокую скорость обработки
• Предоставить возможность переиспользования наработок
• Публиковать веб-сервисы

Резюмируя, можно сказать, что внедрение Loginom — шаг к современной и технологичной работе с данными.

Другие материалы по теме:

Экосистема Loginom: что пользователю нужно знать о платформе

Loginom-ускорители: как сделать за полчаса аналитику, на которую обычно уходит месяц

Три условия превращения аналитика в Citizen Data Scientist

Массивы и векторы Excel

Этот материал о массивах и векторах включает три понятия:

1. Структуры Excel, способные хранить данные, чаще всего массив ячеек на рабочем листе (по ссылке, имени или константе)
2. математических элементов массивов операций и
3. матричная линейная алгебра массив операций

0. Массивы

Массив можно описать как группу элементов, с которыми можно работать как по отдельности, так и вместе. Ориентация массива классифицируется по его размерности (например, ориентация столбца или ориентация строки, обсуждаемая далее в разделе 1). В Excel эту концепцию лучше всего иллюстрирует пример, см. рисунок 1 (рабочий лист: массивы WS), где «группа элементов» означает непрерывный диапазон. В частности, массив может быть:

• Скаляр — одна ячейка
• Массив столбцов (вектор-столбец) — диапазон B2:B7 заштрихован зеленым
• Массив строк (вектор-строка) — диапазон B9:E9 заштрихован синим цветом
• Многомерный массив — диапазон B11:D14 серый

Выражения «скаляр», «вектор-строка» и «вектор-столбец» являются общими для прикладного программного обеспечения MatLab (матричная лаборатория).

Рис. 1. Столбец, строка и многомерный массив — CollArr (6R x 1C), RowArr (1R x 4C) и MDArr (4R x 3C) 12 элементов

1. Массивы — понятия

Размерность

Размер массива определяется количеством строк и количество столбцов. По соглашению размерность всегда указывается сначала строками, а затем столбцами, в духе стиля ссылок R1C1 и порядка аргументов строк и столбцов в таких функциях, как INDEX и OFFSET.

Excel использует [ mR x nR ] во время операций изменения размера выделения (

m и n являются общими для MatLab). MatLab использует обозначение m x n , где m — это количество строк (первое измерение), а n — количество столбцов (второе измерение). Таким образом, [2R x 3C] представляет собой массив из 2 строк на 3 столбца.

Массив является непрерывным, то есть прямоугольным/квадратным, и на массив рабочего листа можно ссылаться верхней левой ячейкой и нижней правой ячейкой с оператором диапазона, см. B11:DF14 на рисунке 1. Некоторые функции возвращают массив (например, функции ТРАНСП и МУМНОЖ).

Размеры (из рисунка 1):

• Скаляр: Размер 1 x 1 (на рисунке не показан)
• Вектор-столбец: с именем ColArr , размерность 6 x 1, количество элементов 6
• Вектор-строка: с именем RowArr , размерность 1 x 4, количество элементов 4
• Многомерный массив: двумерный массив с именем MDArr , размерность 4 x 3, количество элементов 12

В математике массивы на рисунке 1 могут быть представлены в виде:

$$\text{ColArr}=\begin{bmatrix} r1 \\ r2 \\ r3 \\ r4 \\ r5 \\ r6 \end{bmatrix}, \qquad \text{RowArr}=\begin{bmatrix} c1 & c2 & c3 & c4 \end{bmatrix} \qquad \text{MDArr}=\begin{bmatrix} r1c1 & r1c2 & r1c3 \\ r2c1 & r2c2 & r2c3 \\ r3c1 & r3c2 & r3c3 \\ r4c1 & r4c2 & r4c3 \end{bmatrix}$$

, где [ ] представляет массив.

Диспетчер имен фигурные скобки «{}»

В Excel есть два представления массивов:

1. изображение рабочего листа на рисунке 1 и
2. изображений памяти компьютера, таких как поле Value и Refers To диспетчера имен (рис. 2)

Рис. 2: Диспетчер имен — с подробной информацией о массивах строк, столбцов и многомерных массивов (1) и константе массива (2)

Поле значений

Три массива на рисунке 1 равны сохраненные массивы связаны и помечены на рисунке 2

• В массивах Excel используются открывающие «{» и закрывающие «}» фигурные скобки
• Массивы строятся построчно
• Отдельные элементы разделяются запятой «,»
• Конец строки отмечен точкой с запятой «;» кроме последней строки
• Эти форматы не применяются к скаляру

В записи массива Excel:

1. Имя: ColArr ; Значение: {"r1";"r2";"r3";"r4";"r5";"r6"}
2. Имя: RowArr ; Значение: {"c1","c2","c3","c4"}
3. Имя: MDArr ; Значение: {"r1c1","r1c2","r1c3";"r2c1","r2c2","r2c3";"r3c1","r3c2","r3c3";"r4c1","r4c2","r4c3" "}

Поле «Refers To»

Массив ArrConst , помеченный на рисунке 2, представляет собой

именованную константу массива . Он имеет размерность 2 x 2 и используется в рабочем листе Named Array Const на рисунке 3.

Рис. 3: Константа именованного массива — именованная ArrConst размером 2 x 2

В нотации массива Excel константа именованного массива имеет:

1. Имя: ArrConst ;
2. Значение: {...} фигурные скобки с горизонтальным многоточием;
3. Относится к: {"A",1;"XFD",1048576}

Чтобы ввести массив , выберите диапазон 2 x 2, введите =ArrConst в строке формул, затем нажмите Control + Shift + Enter, чтобы завершить формулу.

Константа именованного массива

• может содержать числа, текст, логические значения (ИСТИНА и ЛОЖЬ) и значения ошибок (например, #Н/Д!)
• весь текст должен быть заключен в двойные кавычки «»
• не может содержать другие массивы, формулы или функции
• проценты должны быть десятичными или текстовыми (0,10 или «10%)»

Аргументы функции фигурные скобки «{}»

Значения массива отображаются в диалоговом окне Аргументы функции справа от имени аргумента (рис. 4). Отображается не более 36 символов.

Рис. 4: Аргументы функции — с деталями вектора столбца CollArr из рис. 2, продемонстрированного с помощью функции COUNTA

Формула F2 F9 фигурные скобки «{}»

Вы можете проверить значения в массиве (или ссылке) с последовательностью F2 F9. На рисунке 5 ячейка F2 имеет формулу =COUNTA(ColArr) . Для проверки значений в массиве

1. переключитесь в режим редактирования, нажав F2
2. выберите ссылку ColArr 913)

Рис. 5: Формула с аргументом массива — вектор-столбец ColArr, в режиме редактирования (F2) Нажмите F9 для отладки части формулы, т.е. просмотр массива

2. Массивы — поэлементные операторы

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

• Формула массива завершается нажатием последовательности клавиш CONTROL + SHIFT + ENTER, часто называемой аббревиатурой 9.
  0007 CSE формула
• Существует два различных типа операций с массивами.
  1. Поэлементно и
  2. Матрица (на основе линейной алгебры)
• В этом модуле рассматривается только поэлементно
• (обычно) Каждый массив должен иметь одинаковую величину и направление. Другими словами, размерность имени (исключая скалярные массивы 1 x 1)
• Вы не можете редактировать или удалять часть массива. Вы должны выбрать текущий массив - используйте Ctrl + / ярлык или Главная > Редактирование > Найти и выбрать > Перейти к специальному > Текущий массив

Пример — Excel Online #1

Из «элементного» рабочего листа на рис. 6. В примере используются два вектора-строки размерности (1 x 3) каждый. Имена диапазонов массивов и значений: x = [1,2,3] и

y = [4,5,6] , они появляются в строках 4 и 5 на рисунке 6

WS1: поэлементный рабочий лист демонстрирует:

1. Дополнение
2. Вычитание
3. Умножение
4. Подразделение
   
5. Скалярное сложение
6. Скалярное вычитание
7. Скалярное умножение
8. Скалярное деление
9. Скалярное возведение в степень
10. Скалярная логическая

Рис. 6: Excel Online #1 — WS1: операции с массивами поэлементно (от 1 до 4) и поэлементно по скалярной константе (от 5 до 10), WS2: — Единицы x Цена для примера продаж. WS3: - 100 записей без IF-фильтра WS4: NPV с использованием номеров справочника VisiCalc и примера xlf Carrot Washer

WS2: Рабочий лист U x P >> Sales демонстрирует небольшую базу данных о количестве единиц и данных о ценах. Пользователь должен:

1. Рассчитать общий объем продаж с использованием обычных формул ячеек и функции СУММ
2. Рассчитайте общий объем продаж, используя формулы CSE для продаж, а затем SUM
3. Расчет общего объема продаж с использованием формул СПП в последней ячейке
4. Повторите шаг 3, используя функцию СУММПРОИЗВ Excel

Каждый вектор имеет размер 6 x 1.

WS3: рабочий лист из 100 записей без фильтра IF использует базу данных из 100 записей для демонстрации векторных логических формул CSE. Требуется:

1. Сумма продуктов A, B и C для Продавец = "Вонг"
2. Сумма продуктов A, B и C для Продавец = "Вонг" И Месяц = ​​"Сентябрь"
3. Сумма продукта А для Продавец = "Вонг" И Продукт А >= 10 И Продукт А <= 30

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

t} \end{equation}$$, где начальный денежный поток в нулевое время и будущие денежные потоки во время \(t\) обозначаются \(C_t\) в течение \(n\) периодов. \(k\) — периодическая ставка дисконтирования. 9(СТРОКА(1:6) - 1)) возвращает 17 427,61

• Платформа разработки: Excel 2016 (64-разрядная версия) Office 365 профессиональный плюс в Windows 10
• Сопутствующий материал: Добавление ряда имен относительных смещений в диспетчер имен
• O'Connor I, (2015) Преобразовать нижнюю треугольную таблицу в полную матрицу. Включает обсуждение размерности массива/матрицы и индексации элементов
• О'Коннор I, (2017) Функции Excel с аргументами массива. Включает массив (CSE) и ввод формул
• О'Коннор I, (2015) Константа вектора в массив. Включает код VBA для текстового вектора
• Пересмотрено: Суббота, 25 февраля 2023 г., 10:12 [по восточному времени Австралии (AET)]

Как построить формулу массива в Excel 2010

Формула массива — это специальная формула, которая работает с диапазоном значений в Excel 2010. Когда вы создаете формулу массива на листе, вы нажимаете Ctrl+Shift+Enter чтобы вставить формулу массива в диапазон массива.

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

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

 =А4*Р4 

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

 ={А4:А7*Р4:Р7} 

Эта формула массива умножает каждую почасовую ставку в массиве 4 x 1 в диапазоне A4:A7 на каждый отработанный час в массиве 4 x 1 в диапазоне R4:R7. Эта же формула вводится во все ячейки диапазона массива (R10:R13), как только вы заполняете формулу в активной ячейке R10. Чтобы увидеть, как это делается, выполните шаги, необходимые для построения этой формулы массива:

1. Сделайте ячейку R10 активной, затем выберите диапазон массива R10:R13 и введите = (знак равенства), чтобы запустить формулу массива.

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

2. Выберите диапазон A4:A7, который содержит почасовую ставку для каждого сотрудника, введите * (звездочка для умножения), а затем выберите диапазон R4:R7, который содержит общее количество часов, отработанных в течение первого периода оплаты.

3. Нажмите Ctrl+Shift+Enter, чтобы вставить формулу массива в диапазон массива.

   Excel вставляет фигурные скобки вокруг формулы и копирует формулу массива {=A4:A7*R4:R7} в каждую из ячеек в диапазоне массива R10:R13.

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

На рисунке ниже показана таблица заработной платы за февраль после выполнения всех формул массива в трех диапазонах: R10:R13, AI10:AI13 и AJ10:AJ13. Во второй диапазон ячеек, AI10:AI13, была введена следующая формула массива для расчета почасовой оплаты труда за второй период оплаты в феврале:

Таблица почасовой оплаты труда после ввода всех трех формул массива.

 {=А4:А7*АИ4:АИ7} 

Следующая формула массива была введена в третий диапазон ячеек, AJ10:AJ13, для расчета общей заработной платы, выплаченной каждому сотруднику в феврале 2010 г.:

 {=R10:R13+AI10:AI13} 

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