Разное

Массивы в экселе: Создание формулы массива — Служба поддержки Office

Содержание

Создание формулы массива — Служба поддержки Office

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

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

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

Продавец

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

Число продано

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

Итоги продаж

Зуева

Седан

5

2200

=C2:C11*D2:D11

Купе

4

1800

Егоров

Седан

6

2300

Купе

8

1700

Еременко

Седан

3

2000

Купе

1

1600

Климов

Седан

9

2150

Купе

5

1950

Шашков

Седан

6

2250

Купе

8

2000

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

  1. В книге с примерами выберем ячейки E2-E11. Эти ячейки будут содержать результаты.

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

И под всегда подразумеваем 100 процентов времени.

  1. Введите эту формулу. Чтобы ввести формулу в ячейку, просто начните вводить текст (нажмите знак равно), и формула появится в последней выбранной ячейке. Вы также можете ввести формулу в формулу:

    =C2:C11*D2:D11

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

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

  1. В книге с примерами щелкните ячейку B13.

  2. Введите эту формулу, используя один из способов из шага 2 выше:

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

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

Формула перемножет значения в диапазонах ячеев C2:C11 и D2:D11, а затем добавляет результаты для вычисления общего итога.

Формулы массива в Excel — от А до Я

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

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

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

  • узнаете все о формулах массива;
  • увидите множество примеров таких формул;
  • сможете начать активно их применять на практике.

Поехали!

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

Формула массива (array formula) — это такая формула, в процессе вычисления которой создается один или несколько массивов. При этом не обязательно как результат вычисления, но и как промежуточный этап.

Будьте на 100% уверены: если формула

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

— это формула массива.

Слишком очевидно и все еще непонятно? Не беда — читаем далее.

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

Массивы в Excel

Массив (array) — это фиксированный набор элементов с фиксированным порядком. Иными словами, все элементы массива, включая их положение в нем, являются его неотъемлемой частью, их порядок и количество нельзя изменить.

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) — это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

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

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

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна — это нарушило бы само его определение.

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

Нельзя изменить часть массива.
Ошибка Excel при попытке нарушить целостность массива

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

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

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

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

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

  • числовой массив (массив чисел)
  • строковый массив (текстовые значения)
  • булевый массив (значения ИСТИНА-ЛОЖЬ)
  • смешанного типа

По изменяемости элементов —

  • массив констант,
  • вычисляемый массив

Методы создания массивов

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

Массив констант, созданный вручную

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

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

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

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

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

Создаем именованный массив в Excel

Это позволит больше не вводить его вручную, а обращаться к нему по его имени. Более того, Microsoft Excel помогает при их вводе всплывающими подсказками.

Используем именованный массив
С помощью функций

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

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

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

Массивы в Excel, полученные с помощью математических операторов
С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения
С помощью других массивов

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

Но можно создать массив и по принципу наоборот — на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

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

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

Ctrl+Shift+Enter

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

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

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

Однако, есть ряд функций, называемых агрегирующими. Если им на вход подается диапазон или массив, они возвращают единственное результирующее значение. Самая популярная — функция СУММ. Но есть и множество других.

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

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

  • учитывают весь массив
  • выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ — эти функции поиска используются наиболее часто.

Формулы массива — примеры формул

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

Приступаем к практическим шагам:

Учимся формулам массива 1/4:

Как создать алфавит в Excel

Билл Джелен. Динамические массивы в Excel

Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.

Билл Джелен – основатель сайта MrExcel.com и автор множества книг о Excel. Сайт бесплатно отвечает на более чем 30 000 вопросов в год. В моем блоге представлены три книги Джелена:

Скачать заметку в формате Word или pdf, примеры в формате Excel

Содержание

  1. Начало работы
  2. Функция СОРТ
  3. Функция СОРТПО
  4. Функция ФИЛЬТР
  5. Функция УНИК
  6. Комбинирование функций
  7. Функция ПОСЛЕД
  8. Функция СЛМАССИВ
  9. Почему формулы массива (Ctrl+Shift+Enter) такие жесткие: неявное пересечение
  10. Функция ПРОСМОТРX и динамические массивы
  11. Обычные функции, использующие динамические массивы
  12. Динамические массивы и функции кубов

Введение

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

О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны только в Office 365. Я думаю, что парадигма покупки бессрочной лицензии на Office каждые три или шесть лет устарела, и рекомендую переходить на подписку.

Как организована эта книга. В главе 1 вы узнаете о концепции формулы, распространяющейся на соседние ячейки. Вы увидите, как непустая ячейка может блокировать разлив массива, и как это исправить. Вы услышите о неявном пересечении и о том, как нотация @ может решить эту проблему. Главы 2–8 посвящены каждой из шести новых функций и их комбинациям. Глава 9 основана на превосходном видео Джо Макдэйда. Из этой главы вы узнаете термины, которые использует команда Excel Calc: Подъем, Трансляция, Попарный подъем, Усечение массива и Неявное пересечение. В главе 10 рассматриваются два способа, с помощью которых динамические массивы делают новую функцию ПРОСМОТРX более мощной. В 11-й – приведены 24 примера объединения динамических массивов с рядом обычных функций Excel. В 12-й главе показано, как динамические массивы могут работать с  функциями кубов.

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

Глава 1. Начало работы

Формулы теперь могу разливаться

В этой главе рассматривается новая формула =A2:A20, ошибка #ПЕРЕНОС! и новый неявный оператор пересечения @.

Начнем с базовой формулы массива. Перейдите в ячейку Е3. Наберите =A2:C10. В более ранних версиях Excel вам пришлось бы включить этот диапазон в качестве аргумента какой-нибудь функции, или использовать формулу массива, одновременно нажав Ctrl+Shift+Enter.

Рис. 1. Формула указывает на диапазон ячеек

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

Рис. 2. Одна формула вернула множество значений

Ячейка E4 содержит текст Central, и, хотя строка формул показывает формулу для этой ячейки, она отображается серым цветом. Давайте проверим с помощью VBA, что содержится в ячейках Е3 и Е4?

Рис. 3. VBA подтверждает, что в ячейке Е4 не формула

VBA показывает, что в ячейке Е3 – формула, а в Е4 – нет. Также в Excel можно ввести формулу =ЕФОРМУЛА(E4). Она вернет ЛОЖЬ. И еще одна проверка. Выберете диапазон D1:h30, и пройдите по меню Главная –> Найти и заменить –> Формулы. Будет выделена только ячейка E3.

Один из первых вопросов на YouTube в ответ на мои первые видео с динамическими массивами был: можно ли вы копировать и вставлять значения? Да! Выберите диапазон E3:G11, нажмите Ctrl+C, кликните правой кнопкой мыши на выбранную новую ячейку и выберите Специальная вставка –> Значения.

Что происходит, если формула не может пролиться?

Что произойдет, если ячейка, куда должен разлиться диапазон, будет занята?

Рис. 4. Как Excel справится с занятой ячейкой?

Excel вернет ошибку #ПЕРЕНОС! Excel сообщает, что не может вернуть массив целиком. Поэтому не вернет ни одного результата. Если выбрать контекстное меню слева от ячейки с ошибкой, можно  выделить мешающие ячейки. Возможно, их получится перенести в другое место листа.

Рис. 5. Ошибка #ПЕРЕНОС! и ее контекстное меню

Как только вы очистите ячейки, мешающие размещению массива, он автоматически разольется.

Существует несколько типов ошибки #ПЕРЕНОС! Та, что выше, называется Диапазон для переноса данных не пустой. Другие ошибки:

  • Неопределенный размер. Вы не можете использовать волатильные функции, например, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
  • Выходит за пределы листа. Вы не можете ввести функцию =СОРТ(C:C) в ячейке E2.
  • Табличная формула. Вы не можете использовать функции динамического массива внутри Таблицы.
  • Не хватает памяти. Вам следует ссылаться на диапазон меньшего размера.
  • Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
  • Неопознанная ошибка. Excel не может распознать ошибку.
Если ваша формула указывает на Таблицу, динамический массив будет расширяться при добавлении новых строк в Таблицу

Рис. 6. Динамический массив «отслеживает» Таблицу

Сравните с рис. 2. Вы преобразовали диапазон А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 изменилась на =Таблица1. Теперь, если вы добавите еще одну строку в Таблицу (А11:С11), формула в ячейке Е3 не изменится, а динамический массив автоматически расширится еще одной строкой (Е12:G12).

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

Использование нового типа ссылок на массив: E3#

Как вы захотите сослаться на массив E3:G12, но не знаете, какого он размера, добавьте оператор разлитого диапазона (#) после ячейки, содержащей формулу массива.

Например, =E3 вернет East, =E3# вернет весь массив, формула которого хранится в E3. Неофициально это называется ссылочной нотацией массива. Такая нотация поддерживается только при ссылке внутри одной книги.

Что такое неявное пересечение

Если вы введете [email protected]:C11 в любой ячейке в строках со 2 по 10, формула вернет значение из столбца С той строки, в которой вы ввели формулу. Знак @ известен как неявный оператор пересечения.

Рис. 7. Используйте нотацию @, когда вам нужно неявное пересечение.

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

  • В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах. Поскольку искомое значение может встретиться в нескольких строках обрабатываемого двумерного массива, оба способа получают на выходе отфильтрованный двумерный массив. Способы формирования отфильтрованных массивов — разные: первый способ использует функцию ArrAutofilterEx второй способ — функцию …

  • К примеру, есть у вас несколько десятков (или сотен) текстовых файлов с подобным содержимым: (количество файлов, и количество строк данных в каждом файле не ограничено) 1c04;1J0-698-151-G;1 комплект тормозных накладок;1J0698151G;1J0698151G;5;1 1c04;1H0698151A;Тормозные колодки;1H0698151A;1H0698151A;1;1 1c04;1K0-698-151-B;Тормозные колодки;1K0698151B;1K0698151B;2;1 А надо из всего этого…

  • Сортировка двумерного массива по нулевому столбцу Public Function CoolSort(SourceArr As Variant) As Variant ‘ сортировка двумерного массива по нулевому столбцу Dim Check As Boolean, iCount As Integer, jCount As Integer, nCount As Integer ReDim tmpArr(UBound(SourceArr, 2)) As Variant Do Until Check Check = True For iCount = LBound(SourceArr, 1) To UBound(SourceArr,…

  • Надстройка предназначена для облегчения импорта данных в Excel из текстовых файлов с разделителями (например, из CSV) Пока во вложении — обычный файл Excel с нужными макросами, надстройку выложу позже В надстройке применена функция получения ссылки на заданную пользователем ячейку. Основой для надстройки послужила функция загрузки CSV файла в двумерный массив Использовать функции…

  • Надстройка с несколькими пользовательскими функциями вычислительной математики. Надстройка предназначена для тех пользователей, кто хочет: интерполировать табличные данные; находить корни уравнения; вычислять производные; численно интегрировать; аппроксимировать экспериментальные данные   Внимание: 21 марта 2020 года выложена новая версия надстройки. Во вложении к статье — надстройка…

  • Функция TextFile2Array предназначена для преобразования файла CSV в двумерный массив Очень часто при работе с текстовыми файлами (и, в частности, с файлами CSV) приходится их загружать на лист Excel, предварительно производя фильтрацию данных в этом файле. Чтобы упростить весь процесс — от выбора файла CSV в диалоговом окне, до разбиения загруженного из файла текста в двумерный массив, и была…

  • Функция Array2XML формирует из исходной таблицы объект типа DOMDocument, который можно выгрузить в XML-файл одной строкой кода (метод Save) Sub XMLExport() Dim Заголовок As Range, Данные As Range Set Заголовок = Range(«a1:f1») Set Данные = Range([A2], Range(«A» & Rows.Count).End(xlUp)).Resize(, Заголовок.Columns.Count)   arrHeaders = Application….

  • Если у вас есть таблица Excel, в которой, в определённом столбце, через запятую перечислены значения (или диапазоны значений), а вы хотите получить аналогичную таблицу, но чтобы в каждой строке было только одно значение, — то вам на помощь придёт функция ExtendArray. (пример работы функции можно увидеть на прикреплённом изображении) В своей работе ExtendArray использует функцию…

  • Функция предназначена для получения двумерного массива данных из видимых строк листа Excel.   Например, нас интересует массив, начиная со второй строки, шириной 5 столбцов. Если все строки на листе — видимые, то все делается просто, одной строкой кода: arr = Range(Range(«a2»), Range(«a» & Rows.Count).End(xlUp)).Resize(, 5).Value   А если на листе включен…

  • Sub ПримерИспользования() ‘ задаём полный путь к обрабатываемому файлу ПутьКФайлу$ = ThisWorkbook.Path & «\» & «Contract.XLS»   Application.ScreenUpdating = False ‘ отключаем обновление экрана arr = LoadArrayFromWorkbook(ПутьКФайлу$, «a2», 30) ‘ загружаем данные ‘ выводим результаты в окно Immediate Debug.Print «…

  • Функция RandomRowsFromArray предназначена для выборки из двумерного массива случайных строк. К примеру, исходный массив (таблица) имеет размер 1000*20 (1000 строк, и 20 столбцов) Нам требуется выбрать из этой таблицы, случайным образом, 50 строк (получив, таким образом, таблицу размерами 50*20) Кроме того, необходимо, чтобы при каждом новом запуске макроса, в выборку попадали новые строки….

  • Функция принимает в качестве параметра arr двумерный массив, и разбивает его на несколько массивов, группируя строки по значению столбца SplitColumn& Сколько есть уникальных значений в столбце SplitColumn&, удовлетворяющих маске Mask$, — столько двумерных массивов будет возвращено функцией в виде коллекции Например, если есть исходный массив размерами 100*5, в котором во втором столбце…

  • Программа служит для преобразования тиковых значений котировок в свечи необходимого периода Исходный файл в формате .csv получается в результате выгрузки (экспорта) котировок с сайта www.finam.ru (Фьючерсы ФОРТС), и содержит большое количество строк (записей) — около 1 миллиона за 1 день. В исходном файле CSV имеется таблица  со следующими столбцами: <TICKER> <PER> <DATE…

  • Программа предназначена для анализа ожидаемой доходности различных портфелей (рассматриваются всевозможные комбинации проектов) При помощи формул типа =СЛЧИС() на листах с характеристиками проектов генерируются различные комбинации исходных значений, на основании которых формулами рассчитывается доходность проекта. Методом Монте-Карло формируются массивы значений (по одному массиву для…

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

  • Функция предназначена для сохранения двумерного массива в файл формата XLS Sub SaveArray(ByVal Arr, ByVal ColumnNames, ByVal DocName$) ‘ Получает двумерный массив Arr с данными, и массив заголовков столбцов ColumnNames. ‘ Создаёт новый файл в подпапке СФОРМИРОВАННЫЕ ДОКУМЕНТЫ с именем DocName$ On Error Resume Next   ‘ создаём подпапку (там же, где текущий файл Excel)…

  • Формула массива на Excel для Mac



    Мне нужно позвонить LINEST в Excel 2011 для Mac, я бы сказал — Это была детективная история. Моя лень, мое любопытство — все было здесь! У меня было домашнее задание из университетского курса по использованию LINEST.

    Я даже перестал пытаться сделать это на MAC, потому что после гугла я не мог найти никакого решения, как это сделать. (Я перепробовал все комбинации Fn Ctrl Alt Cmd Return !)

    Наконец-то я нашел решение здесь, на stack overflow! Я хочу упомянуть об этом еще раз, так как это было долгое время, которое я потратил на поиски решения. Я провел 4 дня вне дома, чтобы использовать только windows PC, где это легко, как пирог- F2 , CTRL + SHIFT + ENTER .

    Если вы хотите проверить, говорю ли я правду — перейдите к HELP в Excel для Mac, введите формулу массива, найдите LINEST и посмотрите на комбинацию решений!

    Решение: выберите диапазон, нажмите CONTROL + U , а затем нажмите + RETURN .

    Вот ссылка на оригинальный пост: Excel формулы, дающие ошибку #VALUE! при переносе на Mac

    excel macos array-formulas excel-2011
    Поделиться Источник Ievgenii     28 октября 2014 в 09:52

    5 ответов


    • Excel: Формула Массива Вычисляется Один Раз

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

    • Excel формула для столбца последнего отрицательного значения в диапазоне (без формул массива)

      У меня есть формула Excel, чтобы найти номер столбца последней ячейки с отрицательным значением в строке. Проблема в том, что это формула массива, а инструмент, который мне нужно использовать для создания электронной таблицы (Apache POI SXSSF), не поддерживает формулы массива. Значения в строке…



    26

    Похоже, это не работает в Mac Excel 2016. После небольшого копания, похоже, что комбинация клавиш для ввода формулы массива изменилась с + RETURN на CTRL + SHIFT + RETURN .

    Поделиться damien_papworth     05 ноября 2015 в 17:59



    7


    Выберите диапазон, нажмите CONTROL + U , а затем нажмите + RETURN .


    Поделиться Ievgenii     28 октября 2014 в 09:53



    1

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

    Введите формулу в виде формулы массива Изображение + SHIFT + RETURN или CONTROL + SHIFT + RETURN

    Поделиться sscc     21 апреля 2016 в 05:43


    • Excel формула массива UDF, возвращающая 0

      У меня есть формула массива следующим образом: Public Function fafsa(inputRange As range, desks As range) As Integer Dim vArr() As Integer Dim i As Long ReDim vArr(1 To inputRange.Cells.Count, 1 To 1) i = 1 For i = 1 To inputRange.Cells.Count vArr(i, 1) = 1 Next fasfa = vArr End Function При…

    • #DIV/0! Excel Mac 2011 и IFERROR

      Я пытаюсь решить ошибку #DIV/0! , возникающую, когда формула дает результат 0 . Я использую Excel 2011 для Mac. Вот оригинальная формула: =SUM((AX11025*1)/AV11025) А вот модифицированная формула для устранения ошибки #DIV/0 : =IFERROR((SUM(AX11025*1)/AV11025),SUM(AX425*1), 0)



    1

    CTRL+SHIFT+ENTER, ФОРМУЛА МАССИВА EXCEL 2016 MAC. Так что я прибываю в игру поздно, но, может быть, кто-то еще придет. Это почти свело меня с ума. Независимо от того, что я искал в Google, я ничего не нашел. Что бы я ни пытался сделать, никакого решения, казалось, не предвиделось. Довольно давно я переключился на Excel 2016, и сегодня мне нужно было сделать несколько формул массива. Также сидя на MacBook Pro 15 Touch Bar 2016. Не то чтобы это действительно имело значение, но все же, поскольку решение было опубликовано на Youtube в 2013 году. Причина, по которой, во всяком случае для меня, ничего не работало, заключается в Mac OS, клавиша управления по умолчанию, во всяком случае для меня, настроена на управление управлением полетом, что, по крайней мере для меня, отключило кнопку управления в Excel. Чтобы включить ключ для фактического управления функциями в Excel, вам нужно перейти в Системные настройки > управление полетом и отключить ярлыки для управления полетом. Итак, давайте посмотрим, как долго продлится это решение. Наверное, вернусь к началу после перерыва на кофе. Всего хорошего!

    Поделиться Bubbi von Beijer     10 марта 2020 в 11:35



    -2

    1. Выберите нужный диапазон ячеек
    2. Нажмите Fn + F2 или CONTROL + U
    3. Вставьте в свой массив значение
    4. Нажмите кнопку COMMAND ( ⌘ ) + SHIFT + RETURN

    Поделиться Wild Woz     06 июня 2017 в 10:37


    Похожие вопросы:


    ROW() в Формуле Excel для Mac 2011

    У меня есть файл XLS с несколькими листами с именами 1, 2, 3, 4 и т. д. Теперь мне нужна формула, которая приведет, например, к =’1′!M16, где имя листа, на который я ссылаюсь, основано на текущей…


    Excel формула массива с подстановочным знаком

    У меня есть формула массива, которая ссылается на выпадающую ячейку ($AG$7), чтобы определить, какие ячейки следует оценивать. Это хорошо работает, однако мне нужно включить дополнительный элемент в…


    excel формула массива для делимости

    Может ли существовать одна формула (возможно, формула массива), которая может указать, делится ли число в F1 на любое из чисел в ячейках (A1:D9)? С уважением.


    Excel: Формула Массива Вычисляется Один Раз

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


    Excel формула для столбца последнего отрицательного значения в диапазоне (без формул массива)

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


    Excel формула массива UDF, возвращающая 0

    У меня есть формула массива следующим образом: Public Function fafsa(inputRange As range, desks As range) As Integer Dim vArr() As Integer Dim i As Long ReDim vArr(1 To inputRange.Cells.Count, 1 To…


    #DIV/0! Excel Mac 2011 и IFERROR

    Я пытаюсь решить ошибку #DIV/0! , возникающую, когда формула дает результат 0 . Я использую Excel 2011 для Mac. Вот оригинальная формула: =SUM((AX11025*1)/AV11025) А вот модифицированная формула для…


    Excel формула, необходимая для поиска массива 2-D в отдельных листах

    Существует ли формула в Excel, которая будет выполнять поиск массива 2-D в отдельных листах внутри одной книги? У меня есть три таблицы, отформатированные одинаково с разными данными в таблицах….


    Excel Формула Массива

    Ранее я создал формулу массива Excel, такую как: X13: =SUM(IF($F13>h23:R13,1,0))+IF(F13>F9,1,0)+SUM(IF($F13>T13:U13,1,0))+IF(T13>U13,1,0) При применении этой формулы вниз по столбцу и…


    Преобразование MAC адресов (Excel)

    есть ли формула в Excel, которая может преобразовать адрес MAC из формата 11:22:33:44:55:ab в 1122-3344-55ab ? Спасибо!

    Все про массивы в VBA читать в пошаговом руководстве по Excel

    Автор Дмитрий Якушев На чтение 24 мин. Просмотров 53.7k.

    Дональд Кнут

    Список настолько же силен, как и его самое слабое звено

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

    Краткое руководство по массивам VBA

    ЗадачаСтатический
    массив
    Динамический
    массив
    ОбъявлениеDim arr(0 To 5) As
    Long
    Dim arr() As Long
    Dim arr As Variant
    Установить размер Dim arr(0 To 5) As
    Long
    ReDim arr(0 To 5)As
    Variant
    Увеличить размер
    (сохранить
    существующие
    данные)
    Только
    динамический
    ReDim Preserve arr(0 To 6)
    Установить
    значения
    arr(1) = 22 arr(1) = 22
    Получить значенияtotal = arr(1) total = arr(1)
    Первая позицияLBound(arr) LBound(arr)
    Последняя позицияUbound(arr) Ubound(arr)
    Читать все записи (1D)For i = LBound(arr) To UBound(arr)
    Next i
    Or
    For i = LBound(arr,1) To UBound(arr,1)
    Next i
    For i = LBound(arr) To UBound(arr)
    Next i
    Or
    For i = LBound(arr,1) To UBound(arr,1)
    Next i

    Читать все записи (2D)
    For i = LBound(arr,1) To UBound(arr,1)
      For j = LBound(arr,2) To UBound(arr,2)
      Next j
    Next i
    For i = LBound(arr,1) To UBound(arr,1)
      For j = LBound(arr,2) To UBound(arr,2)
      Next j
    Next i

    Читать все записи
    Dim item As Variant
    For Each item In arr
    Next item
    Dim item As Variant
    For Each item In arr
    Next item
    Перейти на SubSub MySub(ByRef arr() As String) Sub MySub(ByRef arr() As String)
    Возврат из функцииFunction GetArray() 
    As Long()
        Dim arr(0 To 5) As
    Long
        GetArray = arr
    End Function
    Function GetArray() 
    As Long()
        Dim arr() As Long
        GetArray = arr
    End Function
    Получить от
    функции
    Только
    динамический
    Dim arr() As Long 
    Arr = GetArray()
    Стереть массивErase arr
    *Сбрасывает все
    значения по
    умолчанию
    Erase arr
    *Удаляет массив
    Строка в массивТолько
    динамический
    Dim arr As Variant
    arr = Split(«James:Earl:Jones»,»:»)
    Массив в строкуDim sName As String
    sName = Join(arr, «:»)
    Dim sName As String
    sName = Join(arr, «:»)
    Заполните
    значениями
    Только
    динамический
    Dim arr As Variant
    arr = Array(«John», «Hazel», «Fred»)
    Диапазон в массивТолько
    динамический
    Dim arr As Variant
    arr = Range(«A1:D2»)
    Массив в диапазонТак же, как в
    динамическом
    Dim arr As Variant
    Range(«A5:D6») = arr

    Введение

    В этой статье подробно рассматриваются массивы на языке программирования Excel VBA. Она охватывает важные моменты, такие как:

    • Зачем вам массивы
    • Когда вы должны их использовать
    • Два типа массивов
    • Использование более одного измерения
    • Объявление массивов
    • Добавление значений
    • Просмотр всех предметов
    • Супер эффективный способ чтения Range в массив

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

    Быстрые заметки

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

    Массивы и циклы идут рука об руку. Наиболее распространенными циклами, которые вы используете с массивами, являются циклы For i и For Each.

    Что такое массивы и зачем они нужны?

    Массив VBA — это тип переменной. Используется для хранения списков данных одного типа. Примером может быть сохранение списка стран или списка итогов за неделю.

    В VBA обычная переменная может хранить только одно значение за раз.

    В следующем примере показана переменная, используемая для хранения оценок ученика.

    ' Может хранить только 1 значение за раз
    Dim Student1 As Integer
    Student1 = 55
    

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

    В следующем примере у нас есть оценки пяти студентов

    Мы собираемся прочитать эти отметки и записать их в Immediate Window.

    Примечание. Функция Debug.Print записывает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G).

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

    Public Sub StudentMarks()
    
        With ThisWorkbook.Worksheets("Лист1")
    
            ' Объявите переменную для каждого студента
            Dim Student1 As Integer
            Dim Student2 As Integer
            Dim Student3 As Integer
            Dim Student4 As Integer
            Dim Student5 As Integer
    
            ' Читайте оценки студентов из ячейки
            Student1 = .Range("C2").Offset(1)
            Student2 = .Range("C2").Offset(2)
            Student3 = .Range("C2").Offset(3)
            Student4 = .Range("C2").Offset(4)
            Student5 = .Range("C2").Offset(5)
    
            ' Печать студенческих оценок
            Debug.Print "Оценки студентов"
            Debug.Print Student1
            Debug.Print Student2
            Debug.Print Student3
            Debug.Print Student4
            Debug.Print Student5
    
        End With
    
    End Sub
    

    Ниже приведен вывод из примера

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

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

    Public Sub StudentMarksArr()
    
        With ThisWorkbook.Worksheets("Лист1")
    
            ' Объявите массив для хранения оценок для 5 студентов
            Dim Students(1 To 5) As Integer
    
            ' Читайте оценки учеников из ячеек C3: C7 в массив
            Dim i As Integer
            For i = 1 To 5
                Students(i) = .Range("C2").Offset(i)
            Next i
    
            ' Распечатывать оценки студентов из массива
            Debug.Print "Оценки студентов"
            For i = LBound(Students) To UBound(Students)
                Debug.Print Students(i)
            Next i
    
        End With
    
    End Sub
    

    Преимущество этого кода в том, что он будет работать для любого количества студентов. Если нам нужно изменить этот код для работы с 1000 студентами, нам нужно всего лишь изменить (от 1 до 5) на (от 1 до 1000) в декларации. В предыдущем примере нам нужно было добавить примерно пять тысяч строк кода.

    Давайте проведем быстрое сравнение переменных и массивов. Сначала мы сравним процесс объявления.

     ' Объявляем переменные
            Dim Student As Integer
            Dim Country As String
    
      ' Объявляем массивы
            Dim Students(1 To 3) As Integer
            Dim Countries(1 To 3) As String
    

    Далее мы сравниваем присвоение значения

        ' присвоить значение переменной
            Student1 = .Cells(1, 1) 
    
        ' присваивать значение первому элементу в массиве
            Students(1) = .Cells(1, 1)
    

    Наконец, мы смотрим на запись значений

      ' Вывести значение переменной
            Debug.Print Student1
    
      ' Вывести значение первого студента в массиве
            Debug.Print Students(1)
    

    Как видите, использование переменных и массивов очень похоже.

    Важным является тот факт, что массивы используют индекс (также называемый нижним индексом) для доступа к каждому элементу. Это означает, что мы можем легко получить доступ ко всем элементам в массиве, используя цикл For.

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

    Типы массивов VBA

    В VBA есть два типа массивов:

    1. Статический — массив фиксированного размера.
    2. Динамический — массив, в котором размер задается во время выполнения

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

    Объявление массива

    Статический массив объявляется следующим образом

    Public Sub DecArrayStatic()
    
        ' Создать массив с местоположениями 0,1,2,3
        Dim arrMarks1(0 To 3) As Long
    
        ' По умолчанию от 0 до 3, то есть местоположения 0,1,2,3
        Dim arrMarks2(3) As Long
    
        ' Создать массив с местоположениями 1,2,3,4,5
        Dim arrMarks1(1 To 5) As Long
    
        ' Создать массив с местоположениями 2,3,4 'Это редко используется
        Dim arrMarks3(2 To 4) As Long
    
    End Sub
    

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

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

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

    Public Sub DecArrayDynamic()
    
        ' Объявить динамический массив
        Dim arrMarks() As Long
    
        ' Установите размер массива, когда вы будете готовы
        ReDim arrMarks(0 To 5)
    
    End Sub
    

    Динамический массив не выделяется, пока вы не используете оператор ReDim. Преимущество в том, что вы можете подождать, пока не узнаете количество элементов, прежде чем устанавливать размер массива. Со статическим массивом вы должны указать размер заранее.

    Присвоение значений массиву

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

    Public Sub AssignValue()
    
        ' Объявить массив с местоположениями 0,1,2,3
        Dim arrMarks(0 To 3) As Long
    
        ' Установите значение позиции 0
        arrMarks(0) = 5
    
        ' становите значение позиции 3
        arrMarks(3) = 46
    
        ' Это ошибка, так как нет местоположения 4
        arrMarks(4) = 99
    
    End Sub
    

    Номер места называется индексом. Последняя строка в примере выдаст ошибку «Индекс вне диапазона», так как в примере массива нет местоположения 4.

    Использование функций Array и Split

    Вы можете использовать функцию Array для заполнения массива списком элементов. Вы должны объявить массив как тип Variant. Следующий код показывает, как использовать эту функцию.

      Dim arr1 As Variant
        arr1 = Array("Апельсин", "Персик","Груша")
    
      Dim arr2 As Variant
        arr2 = Array(5, 6, 7, 8, 12)
    

    Массив, созданный функцией Array, начнется с нулевого индекса, если вы не используете Option Base 1 в верхней части вашего модуля. Затем он начнется с первого индекса. В программировании, как правило, считается плохой практикой иметь ваши реальные данные в коде. Однако иногда это полезно, когда вам нужно быстро протестировать некоторый код. Функция Split используется для разделения строки на массив на основе разделителя. Разделитель — это символ, такой как запятая или пробел, который разделяет элементы.

    Следующий код разделит строку на массив из трех элементов.

     Dim s As String
        s = "Красный,Желтый,Зеленый,Синий"
    
        Dim arr() As String
        arr = Split(s, ",")
    

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

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

    Использование цикла For обеспечивает быстрый доступ ко всем элементам массива. Вот где сила использования массивов становится очевидной. Мы можем читать массивы с десятью значениями или десятью тысячами значений, используя те же несколько строк кода. В VBA есть две функции: LBound и UBound. Эти функции возвращают самый маленький и самый большой индекс в массиве. В массиве arrMarks (от 0 до 3) LBound вернет 0, а UBound вернет 3.

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

    Public Sub ArrayLoops()
    
        ' Объявить массив
        Dim arrMarks(0 To 5) As Long
    
        ' Заполните массив случайными числами
        Dim i As Long
        For i = LBound(arrMarks) To UBound(arrMarks)
            arrMarks(i) = 5 * Rnd
        Next i
    
        ' Распечатайте значения в массиве
        Debug.Print "Место нахождения", "Значение"
        For i = LBound(arrMarks) To UBound(arrMarks)
            Debug.Print i, arrMarks(i)
        Next i
    
    End Sub
    

    Функции LBound и UBound очень полезны. Их использование означает, что наши циклы будут работать правильно с любым размером массива. Реальное преимущество заключается в том, что если размер массива изменяется, нам не нужно менять код для печати значений. Цикл будет работать для массива любого размера, пока вы используете эти функции.

    Использование цикла For Each

    Вы можете использовать цикл For Each с массивами. Важно помнить, что он доступен только для чтения. Это означает, что вы не можете изменить значение в массиве.

    В следующем коде значение метки изменяется, но оно не меняет значение в массиве.

     For Each mark In arrMarks
            ' Не изменит значение массива
            mark = 5 * Rnd
        Next mark
    

    Цикл For Each отлично подходит для чтения массива. Как видите, лучше писать специально для двумерного массива.

      Dim mark As Variant
        For Each mark In arrMarks
            Debug.Print mark
        Next mark
    

    Использование Erase

    Функция Erase может использоваться для массивов, но она работает по-разному в зависимости от типа массива.

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

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

    Давайте рассмотрим пример статического массива. Этот пример аналогичен примеру ArrayLoops в последнем разделе с одним отличием — мы используем Erase после установки значений. Когда значение будет распечатано, все они будут равны нулю.

    Public Sub EraseStatic()
    
        ' Объявить массив
        Dim arrMarks(0 To 3) As Long
    
        ' Заполните массив случайными числами
        Dim i As Long
        For i = LBound(arrMarks) To UBound(arrMarks)
            arrMarks(i) = 5 * Rnd
        Next i
    
        ' ВСЕ ЗНАЧЕНИЯ УСТАНОВЛЕНЫ НА НОЛЬ
        Erase arrMarks
    
        ' Распечатайте значения - там все теперь ноль
        Debug.Print "Место нахождения", "Значение"
        For i = LBound(arrMarks) To UBound(arrMarks)
            Debug.Print i, arrMarks(i)
        Next i
    
    End Sub
    

    Теперь мы попробуем тот же пример с динамикой. После того, как мы используем Erase, все места в массиве были удалены. Нам нужно использовать ReDim, если мы хотим использовать массив снова.

    Если мы попытаемся получить доступ к членам этого массива, мы получим ошибку «Индекс вне диапазона».

    Public Sub EraseDynamic()
    
        ' Объявить массив
        Dim arrMarks() As Long
        ReDim arrMarks(0 To 3)
    
        ' Заполните массив случайными числами
        Dim i As Long
        For i = LBound(arrMarks) To UBound(arrMarks)
            arrMarks(i) = 5 * Rnd
        Next i
    
        ' arrMarks теперь освобожден. Места не существуют.
        Erase arrMarks
    
    End Sub
    

    ReDim с Preserve

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

    В следующем примере второй оператор ReDim создаст совершенно новый массив. Исходный массив и его содержимое будут удалены.

    Sub UsingRedim()
    
        Dim arr() As String
        
        ' Установить массив в слоты от 0 до 2
        ReDim arr(0 To 2)
        arr(0) = "Яблоко"
        
        ' Массив с яблоком теперь удален
        ReDim arr(0 To 3)
    
    End Sub
    

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

    Когда мы используем Redim Preserve, новый массив должен начинаться с того же начального размера, например мы не можем сохранить от (0 до 2) до (от 1 до 3) или до (от 2 до 10), поскольку они являются различными начальными размерами.

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

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

    Sub UsingRedimPreserve()
    
        Dim arr() As String
        
        ' Установить массив в слоты от 0 до 1
        ReDim arr(0 To 2)
        arr(0) = "Яблоко"
        arr(1) = "Апельсин"
        arr(2) = "Груша"
        
        ' Изменение размера и сохранение исходного содержимого
        ReDim Preserve arr(0 To 5)
    
    End Sub
    

    Из приведенных ниже снимков экрана видно, что исходное содержимое массива было «сохранено».

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

    Использование Preserve с 2-мерными массивами

    Preserve работает только с верхней границей массива.

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

    Sub Preserve2D()
    
        Dim arr() As Long
        
        ' Установите начальный размер
        ReDim arr(1 To 2, 1 To 5)
        
        ' Изменить размер верхнего измерения
        ReDim Preserve arr(1 To 2, 1 To 10)
    
    End Sub
    

    Если мы попытаемся использовать Preserve на нижней границе, мы получим ошибку «Индекс вне диапазона».

    В следующем коде мы используем Preserve для первого измерения. Запуск этого кода приведет к ошибке «Индекс вне диапазона»:

    Sub Preserve2DError()
    
        Dim arr() As Long
        
        ' Установите начальный размер
        ReDim arr(1 To 2, 1 To 5)
        
        ' Ошибка «Вне диапазона»
        ReDim Preserve arr(1 To 5, 1 To 5)
    
    End Sub
    

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

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

    Sub Preserve2DRange()
    
        Dim arr As Variant
        
        ' Назначить диапазон массиву
        arr = Sheet1.Range("A1:A5").Value
        
        ' Preserve будет работать только на верхней границе
        ReDim Preserve arr(1 To 5, 1 To 7)
    
    End Sub
    

    Сортировка массива

    В VBA нет функции для сортировки массива. Мы можем отсортировать ячейки листа, но это медленно, если данных много.

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

    Sub QuickSort(arr As Variant, first As Long, last As Long)
      
      Dim vCentreVal As Variant, vTemp As Variant
      
      Dim lTempLow As Long
      Dim lTempHi As Long
      lTempLow = first
      lTempHi = last
      
      vCentreVal = arr((first + last) / 2)
      Do While lTempLow <= lTempHi
      
        Do While arr(lTempLow) < vCentreVal And lTempLow < last
          lTempLow = lTempLow + 1
        Loop
        
        Do While vCentreVal < arr(lTempHi) And lTempHi > first
          lTempHi = lTempHi - 1
        Loop
        
        If lTempLow <= lTempHi Then
        
            ' Поменять значения
            vTemp = arr(lTempLow)
    
            arr(lTempLow) = arr(lTempHi)
            arr(lTempHi) = vTemp
          
            ' Перейти к следующим позициям
            lTempLow = lTempLow + 1
            lTempHi = lTempHi - 1
          
        End If
        
      Loop
      
      If first < lTempHi Then QuickSort arr, first, lTempHi
      If lTempLow < last Then QuickSort arr, lTempLow, last
      
    End Sub
    

    Вы можете использовать эту функцию так:

    Sub TestSort()
    
        ' Создать временный массив
        Dim arr() As Variant
        arr = Array("Банан", "Дыня", "Персик", "Слива", "Яблоко")
      
        ' Сортировать массив
        QuickSort arr, LBound(arr), UBound(arr)
    
        ' Печать массива в Immediate Window(Ctrl + G)
        Dim i As Long
        For i = LBound(arr) To UBound(arr)
            Debug.Print arr(i)
        Next i
    
    End Sub
    

    Передача массива в Sub или функцию

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

    Переход к процедуре с использованием ByRef означает, что вы передаете ссылку на массив. Таким образом, если вы измените массив в процедуре, он будет изменен, когда вы вернетесь.
     
    Примечание. Когда вы используете массив в качестве параметра, он не может использовать ByVal, он должен использовать ByRef. Вы можете передать массив с помощью ByVal, сделав параметр вариантом.

    ' Передает массив в функцию
    Public Sub PassToProc()
        Dim arr(0 To 5) As String
        ' Передать массив в функцию
        UseArray arr
    End Sub
    
    Public Function UseArray(ByRef arr() As String)
        ' Использовать массив
        Debug.Print UBound(arr)
    End Function
    

    Возвращение массива из функции

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

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

    Следующие примеры показывают это:

    Public Sub TestArray()
    
        ' Объявить динамический массив - не выделен
        Dim arr() As String
        ' Возврат нового массива
        arr = GetArray
    
    End Sub
    
    Public Function GetArray() As String()
    
        ' Создать и выделить новый массив
        Dim arr(0 To 5) As String
        ' Возвращаемый массив
        GetArray = arr
    
    End Function
    

    Двумерные массивы

    Массивы, на которые мы смотрели до сих пор, были одномерными. Это означает, что массивы представляют собой один список элементов.

    Двумерный массив — это список списков. Если вы думаете об одной строке электронной таблицы как об одном измерении, то более одного столбца является двухмерным. На самом деле электронная таблица является эквивалентом двумерного массива. Он имеет два измерения — строки и столбцы.

    Следует отметить одну маленькую вещь: Excel обрабатывает одномерный массив как строку, если вы записываете его в электронную таблицу. Другими словами, массив arr (от 1 до 5) эквивалентен arr (от 1 до 1, от 1 до 5) при записи значений в электронную таблицу.

    На следующем рисунке показаны две группы данных. Первый — это одномерный массив, а второй — двухмерный.

    Чтобы получить доступ к элементу в первом наборе данных (одномерном), все, что вам нужно сделать, это дать строку, например. 1,2, 3 или 4.

    Для второго набора данных (двумерного) вам нужно указать строку И столбец. Таким образом, вы можете думать, что 1-мерное — это несколько столбцов, а одна строка и двухмерное — это несколько строк и несколько столбцов.

    Примечание. В массиве может быть более двух измерений. Это редко требуется. Если вы решаете проблему с помощью 3+-мерного массива, то, вероятно, есть лучший способ сделать это.

    Вы объявляете двумерный массив следующим образом:

    Dim ArrayMarks(0 To 2, 0 To 3) As Long
    

    В следующем примере создается случайное значение для каждого элемента в массиве и печатается значение в Immediate Window.

    Public Sub TwoDimArray()
    
        ' Объявить двумерный массив
        Dim arrMarks(0 To 3, 0 To 2) As String
    
        ' Заполните массив текстом, состоящим из значений i и j
        Dim i As Long, j As Long
        For i = LBound(arrMarks) To UBound(arrMarks)
            For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
                arrMarks(i, j) = CStr(i) &amp; ":" &amp; CStr(j)
            Next j
        Next i
    
        ' Вывести значения в массиве в Immediate Window
        Debug.Print "i", "j", "Знаечние"
        For i = LBound(arrMarks) To UBound(arrMarks)
            For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
                Debug.Print i, j, arrMarks(i, j)
            Next j
        Next i
    
    End Sub
    

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

    Результат примера выглядит следующим образом:

    Этот макрос работает следующим образом:

    • Входит в цикл i
    • i установлен на 0
    • цикл Enters j
    • j установлен на 0
    • j установлен в 1
    • j установлен на 2
    • Выход из цикла j
    • i установлен в 1
    • j установлен на 0
    • j установлен в 1
    • j установлен на 2
    • И так до тех пор, пока i = 3 и j = 2

    Заметьте, что LBound и UBound имеют второй аргумент 2. Это указывает, что это верхняя или нижняя граница второго измерения. Это начальное и конечное местоположение для j. Значение по умолчанию 1, поэтому нам не нужно указывать его для цикла i.

    Использование цикла For Each

    Использование For Each лучше использовать при чтении из массива.
    Давайте возьмем код сверху, который выписывает двумерный массив.

     ' Для цикла For необходимо два цикла
        Debug.Print "i", "j", "Значение"
        For i = LBound(arrMarks) To UBound(arrMarks)
            For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
                Debug.Print i, j, arrMarks(i, j)
            Next j
        Next i
    

    Теперь давайте перепишем его, используя цикл For Each. Как видите, нам нужен только один цикл, и поэтому гораздо проще написать:

     ' Использование For Each требует только одного цикла
        Debug.Print "Значение"
        Dim mark As Variant
        For Each mark In arrMarks
            Debug.Print mark
        Next mark
    

    Использование цикла For Each дает нам массив только в одном порядке — от LBound до UBound. В большинстве случаев это все, что вам нужно.

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

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

    Public Sub ReadToArray()
    
        ' Объявить динамический массив
        Dim StudentMarks As Variant
    
        ' Считать значения в массив из первой строки
        StudentMarks = Range("A1:Z1").Value
    
        ' Запишите значения обратно в третий ряд
        Range("A3:Z3").Value = StudentMarks
    
    End Sub
    

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

    В следующем примере будут считаны примеры данных студента ниже из C3: E6 Лист1 и распечатаны в Immediate Window.

    Public Sub ReadAndDisplay()
    
        ' Получить диапазон
        Dim rg As Range
        Set rg = ThisWorkbook.Worksheets("Лист1").Range("C3:E6")
    
        ' Создать динамический массив
        Dim StudentMarks As Variant
    
        ' Считать значения в массив из листа 1
        StudentMarks = rg.Value
    
        ' Вывести значения массива
        Debug.Print "i", "j", "Значение"
        Dim i As Long, j As Long
        For i = LBound(StudentMarks) To UBound(StudentMarks)
            For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2)
                Debug.Print i, j, StudentMarks(i, j)
            Next j
        Next i
    
    End Sub
    

    Как видите, первое измерение (доступное через i) массива — это строка, а второе — столбец. Чтобы продемонстрировать это, взглянем на значение 44 в Е4 данных образца. Это значение находится в строке 2 столбца 3 наших данных. Вы можете видеть, что 44 хранится в массиве в StudentMarks (2,3).

    Как заставить ваши макросы работать на суперскорости

    Если ваши макросы работают очень медленно, этот раздел будет очень полезным. Особенно, если вы имеете дело с большими объемами данных. В VBA это держится в секрете.

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

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

    1. Скопируйте данные из ячеек в массив.
    2. Измените данные в массиве.
    3. Скопируйте обновленные данные из массива обратно в ячейки.

    Например, следующий код будет намного быстрее, чем код ниже:

    Public Sub ReadToArray()
    
        ' Считать значения в массив из первой строки
        Dim StudentMarks  As Variant
        StudentMarks = Range("A1:Z20000").Value
    
        Dim i As Long
        For i = LBound(StudentMarks) To UBound(StudentMarks)
            ' Обновление отметок здесь
            StudentMarks(i, 1) = StudentMarks(i, 1) * 2
            '...
        Next i
    
        ' Запишите новые значения обратно на лист
        Range("A1:Z20000").Value = StudentMarks
    
    End Sub
    
    Sub UsingCellsToUpdate()
        
        Dim c As Variant
        For Each c In Range("A1:Z20000")
            c.Value = ' Обновите значения здесь
        Next c
        
    End Sub
    

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

    ' Назначение - быстрее
    Range("A1:A10").Value = Range("B1:B10").Value
    
    ' Копировать Вставить - медленнее
    Range("B1:B1").Copy Destination:=Range("A1:A10")
    

    Заключение

    Ниже приводится краткое изложение основных моментов этой статьи.

    1. Массивы — это эффективный способ хранения списка элементов одного типа.
    2. Вы можете получить доступ к элементу массива напрямую, используя номер местоположения, который известен как индекс.
    3. Распространенная ошибка «Индекс вне диапазона» вызвана доступом к несуществующему местоположению.
    4. Существует два типа массивов: статический и динамический.
    5. Статический используется, когда размер массива всегда одинаков.
    6. Динамические массивы позволяют вам определять размер массива во время выполнения.
    7. LBound и UBound обеспечивают безопасный способ поиска самых маленьких и самых больших подписок массива.
    8. Основной массив является одномерным. Есть еще многомерные массивы.
    9. Чтобы только передать массив в процедуру, используйте ByRef. Вы делаете это так: ByRef arr() as long.
    10. Вы можете вернуть массив из функции, но массив, которому он назначен, не должен быть выделен в данный момент.
    11. Рабочий лист с его строками и столбцами по сути является двумерным массивом.
    12. Вы можете читать непосредственно из диапазона листа в двухмерный массив всего за одну строку кода.
    13. Вы также можете записать из двумерного массива в диапазон всего за одну строку кода.

    Тема 1. Операции с массивами и матрицами в MS Excel

    

    Тема 1. Операции с массивами и матрицами в MS Excel  Тема 1. Операции с массивами и матрицами в MS Excel

    1.1. Операции с массивами

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

    Адрес_первой_ячейки : Адрес_последней_ячейки.

    Трехмерные диапазоны создаются в одноименных ячейках нескольких смежных рабочих листов. Адресная ссылка на такой диапазон имеет формат:

    ’Имя_первого_рабочего_листа : Имя_последнего_рабочего_листа’ ! Адрес_первой_ячейки : Адрес_последней_ячейки

       
    например,            = ‘Лист1:Лист2’!$A$1:$B$4
    Если массив содержит данные арифметического типа, то с таким
    массивом  можно выполнять арифметические операции такие, как:
    -операции, в которых в качестве операндов участвуют массив
    и единственная  переменная, например умножение элементов массива
    на число;
    -операции, в которых в качестве операндов участвует двумерный
    массив и одномерный массив, например, почленно — построчное умножение;
    > -операции, в которых участвуют массивы одинаковой размерности.
    Для умножения (деления, сложения или вычисления функции) каждого
    элемента массива на число следует выполнить следующие действия:
    1.Ввести значения элементов массива в ячейки рабочего листа.
    2.Выделить область для размещения результата операции, ее
    размерность должна быть такой же, как размерность исходного массива.
    3.В строку формул ввести формулу, например:
                     

    = A1:B2*3 или = sin(A1:B2)
     и т.п.
    4.Указать, что производится операция над массивом, нажав комбинацию клавиш
    Ctrl+Shift+Enter.

    Пример 1. Требуется умножить элементы двумерного массива размерности 2 х 2 на число 3.

    Решение: Введите в ячейки диапазона значения элементов массива (см. рис.1).1. Выделите диапазон ячеек такой же размерности, в который будет помещаться результат операции.2. Введите в выделенный диапазон формулу в формате: = адрес_начальной_ячейки_диапазона: адрес_конечной_ячейки * адрес_второго_ операнда3. Нажмите комбинацию клавиш Ctrl + Shift + Enter. В ячейках выделенного диапазона появится результат.
    Рисунок 1

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

    Excel Формула массива | Exceljet

    Формула массива выполняет операцию с несколькими значениями вместо одного значения. Конечным результатом формулы массива может быть либо один элемент, либо массив элементов, в зависимости от того, как построена формула. Для правильной работы многие формулы массива нужно вводить с помощью Ctrl + Shift + ввод . При вводе формулы таким образом вы увидите формулу, заключенную в фигурные скобки {} в строке формул. Не вводите фигурные скобки вручную, иначе формула не будет работать!

    Что такое массив?

    Массив — это коллекция из более чем одного элемента.Массивы в Excel заключаются в фигурные скобки. Например, {1; 2; 3} или {«красный», «синий», «зеленый»}. Причина, по которой массивы так распространены в Excel, заключается в том, что они напрямую сопоставляются с диапазонами ячеек. Вертикальные диапазоны представлены в виде массивов, в которых используются точки с запятой, например {100; 125; 150}. Горизонтальные диапазоны представлены в виде массивов с запятыми, например {«маленький», «средний», «большой»}. В двумерном диапазоне будут использоваться как точки с запятой, так и запятые.

    Примеры

    Формулы массива довольно трудны для понимания из-за сложной и сложной терминологии.Но сами формулы массива могут быть очень простыми. Например, эта формула массива проверяет диапазон A1: A5 на значение «a»:

    .
     

    Операция с массивом — это сравнение каждой ячейки в A1: A5 со строкой «a». Поскольку сравнение работает с несколькими значениями, функция ИЛИ возвращает несколько результатов:

     
     = ИЛИ ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ}) 

    Если какой-либо элемент в результирующем массиве имеет значение ИСТИНА, функция ИЛИ возвращает ИСТИНА.

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

    .
     

    Функция LEN получает несколько значений в диапазоне B2: B11 и возвращает несколько результатов в массиве, подобном этому внутри SUM:

    .
     
     = СУММ ({3; 3; 5; 4; 5; 4; 6; 5; 4; 4}) 

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

    Специальный синтаксис

    Во всех версиях Excel, кроме Excel 365, для правильной работы многие формулы массива необходимо вводить особым образом.Вместо ввода с помощью клавиши «Enter» их нужно вводить с помощью Control + Shift + Enter . Иногда вы увидите Control + Shift + Enter, сокращенно «CSE», как в «формуле CSE». Введенная таким образом формула будет заключена в фигурные скобки с обеих сторон:

     

    Эти фигурные скобки автоматически отображаются в Excel. Убедитесь, что вы не вводите фигурные скобки вручную!

    Не все формулы массива нужно вводить с помощью Control + Shift + Enter.Некоторые функции, такие как SUMPRODUCT, изначально запрограммированы для обработки операций с массивами и обычно не требуют Control + Shift + Enter. Например, обе формулы ниже представляют собой формулы массива, которые возвращают один и тот же результат, но только для версии SUM требуется Control + Shift + Enter:

     

    Excel 365

    В Excel 365 формулы массива являются собственными и не требуют Ctrl + Shift + Enter. Общие сведения см. В разделе Формулы динамических массивов в Excel.

    Формулы и функции массивов в Excel

    В этом руководстве вы узнаете, что такое формула массива Excel, как правильно ввести ее в свои рабочие листы и как использовать константы массива и функции массива.

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

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

    Что такое массив в Excel?

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

    Например, если вы поместите свой еженедельный список покупок в формат массива Excel, он будет выглядеть так:

    {«молоко», «яйца», «масло», «кукурузные хлопья»}

    Затем, если вы выберете ячейки с A1 по D1, введите указанный выше массив со знаком равенства (=) в строке формул и нажмите CTRL + SHIFT + ENTER, вы получите следующий результат:

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

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

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

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

    Формулы массива доступны во всех версиях Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и ниже.

    И теперь, похоже, для вас самое подходящее время для создания вашей первой формулы массива.

    Простой пример формулы массива Excel

    Предположим, у вас есть товары в столбце B, их цены в столбце C, и вы хотите подсчитать общую сумму всех продаж.

    Конечно, ничто не мешает вам сначала вычислить промежуточные итоги в каждой строке с помощью чего-то столь же простого, как = B2 * C2 , а затем суммировать эти значения:

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

    1. Выберите пустую ячейку и введите в нее следующую формулу:

      = СУММ (B2: B6 * C2: C6)

    2. Нажмите сочетание клавиш CTRL + SHIFT + ENTER, чтобы заполнить формулу массива.

      Как только вы это сделаете, Microsoft Excel заключит формулу в {фигурные скобки}, которые визуально указывают на формулу массива.

      Формула умножает значения в каждой отдельной строке указанного массива (ячейки с B2 по C6), складывает промежуточные итоги и выводит общий итог:

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

    Зачем использовать формулы массива в Excel?
    Формулы массива

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

    Как ввести формулу массива в Excel (Ctrl + Shift + Enter)

    Как вы уже знаете, сочетание трех клавиш CTRL + SHIFT + ENTER — это волшебное прикосновение, которое превращает обычную формулу в формулу массива.

    При вводе формулы массива в Excel следует помнить о 4 важных моментах:

    1. После того, как вы закончили вводить формулу и одновременно нажали клавиши CTRL SHIFT ENTER, Excel автоматически заключает формулу между {фигурными скобками}. Когда вы выбираете такую ​​ячейку (я), вы можете видеть фигурные скобки в строке формул, что дает вам подсказку о том, что там есть формула массива.
    2. Ввести фигурные скобки вокруг формулы вручную не получится. Вы должны нажать сочетание клавиш Ctrl + Shift + Enter, чтобы заполнить формулу массива.
    3. Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают, и вы должны снова нажимать Ctrl + Shift + Enter, чтобы сохранить изменения.
    4. Если вы забудете нажать Ctrl + Shift + Enter, ваша формула будет вести себя как обычная формула и обрабатывать только первые значения в указанном массиве (ах).

    Поскольку все формулы массива Excel требуют нажатия Ctrl + Shift + Enter, их иногда называют формулами CSE .

    Используйте клавишу F9 для оценки частей формулы массива

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

    В приведенном выше примере, чтобы увидеть промежуточные итоги всех продуктов, выберите B2: B6 * C2: C6, нажмите F9 и получите следующий результат.

    Примечание. Обратите внимание, что вы должны выбрать какую-то часть формулы перед нажатием F9, иначе клавиша F9 просто заменит вашу формулу вычисленным значением (ями).

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

    Формула массива

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

    Существует несколько функций массива Excel, которые предназначены для возврата массивов с несколькими ячейками, например TRANSPOSE, TREND, FREQUENCY, LINEST и т. Д.

    Другие функции, такие как SUM, AVERAGE, AGGREGATE, MAX, MIN, могут вычислять выражения массива при вводе в одну ячейку с помощью Ctrl + Shift + Enter.

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

    Пример 1. Формула массива с одной ячейкой

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

    Обычно вы добавляете дополнительный столбец, скажем, столбец D, который вычисляет изменение продаж для каждого продукта с использованием формулы типа = C2-B2 , а затем находите максимальное значение в этом дополнительном столбце = MAX (D: D ) .

    Формула массива не требует дополнительного столбца, поскольку она отлично сохраняет промежуточные результаты в памяти. Итак, вы просто вводите следующую формулу и нажимаете Ctrl + Shift + Enter:

    = МАКС (C2: C6-B2: B6)

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

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

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

    = B2: B6 * C2: C6 * 0.1

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

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

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

    1. Выберите пустой диапазон ячеек, в который вы хотите вывести транспонированную таблицу. Поскольку мы преобразуем строки в столбцы, убедитесь, что выбрано такое же количество строк и столбцов, какое в исходной таблице есть столбцы и строки, соответственно. В этом примере мы выбираем 6 столбцов и 4 строки.
    2. Нажмите F2, чтобы войти в режим редактирования.
    3. Введите функцию массива = TRANSPOSE (массив) и нажмите Ctrl + Shift + Enter. В нашем примере формула = TRANSPOSE ($ A $ 1: $ D $ 6) .

      Результат будет выглядеть примерно так:

    Вот как вы используете функцию массива в Excel. Чтобы изучить основы TRANSPOSE, ознакомьтесь с этим руководством: Как транспонировать в Excel — преобразовывать строки в столбцы.

    Как работать с формулами массива с несколькими ячейками

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

    1. Выберите диапазон ячеек, в который вы хотите вывести результаты до ввода формулы.
    2. Чтобы удалить формулу массива с несколькими ячейками, выберите все ячейки, содержащие ее, и нажмите клавишу DELETE, или выберите всю формулу в строке формул, нажмите клавишу DELETE, а затем нажмите Ctrl + Shift + Enter.
    3. Вы не можете редактировать или перемещать содержимое отдельной ячейки в формуле массива, а также не можете вставлять новые или удалять существующие ячейки из формулы массива с несколькими ячейками. Каждый раз, когда вы пытаетесь это сделать, Microsoft Excel выдает предупреждение « Вы не можете изменить часть массива ».
    4. Чтобы сжать формулу массива, т.е. чтобы применить ее к меньшему количеству ячеек, вам нужно сначала удалить существующую формулу, а затем ввести новую.
    5. В разверните формулу массива, то есть примените ее к большему количеству ячеек, выберите все ячейки, содержащие текущую формулу, плюс пустые ячейки, где вы хотите ее разместить, нажмите F2, чтобы переключиться в режим редактирования, скорректируйте ссылки в формуле и нажмите Ctrl + Shift + Enter, чтобы обновить его.
    6. Вы не можете использовать формулы массива с несколькими ячейками в таблицах Excel.
    7. Вы должны ввести формулу массива с несколькими ячейками в диапазоне ячеек того же размера, что и результирующий массив, возвращаемый формулой. Если формула массива Excel создает массив, превышающий выбранный диапазон, лишние значения не будут отображаться на листе. Если массив, возвращаемый формулой, меньше выбранного диапазона, в дополнительных ячейках появятся ошибки # N / A.

    Если ваша формула может возвращать массив с переменным числом элементов, введите его в диапазоне, равном или превышающем максимальный массив, возвращаемый формулой, и заключите формулу в функцию ЕСЛИОШИБКА, как показано в этом примере.

    Константы массива Excel

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

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

    Существует 3 типа констант массива:

    1. Константа горизонтального массива

    Константа горизонтального массива находится в строке.Чтобы создать константу массива строк, введите значения, разделенные запятыми, и заключите их в фигурные скобки, например {1,2,3,4}.

    Примечание. При создании константы массива необходимо вручную ввести открывающую и закрывающую фигурные скобки.

    Чтобы ввести горизонтальный массив в электронную таблицу, выберите соответствующее количество пустых ячеек в строке, введите формулу = {1,2,3,4} в строке формул и нажмите Ctrl + Shift + Enter. Результат будет примерно таким:

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

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

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

    = {11; 22; 33; 44}

    3. Постоянная двумерного массива

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

    = {"а", "б", "в"; 1, 2, 3}

    Работа с константами массива Excel

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

    1. Элементы константы массива

      Константа массива может содержать числа, текстовые значения, логические значения (ИСТИНА и ЛОЖЬ) и значения ошибок, разделенные запятыми или точками с запятой.

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

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

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

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

      • Перейдите на вкладку Формулы> группу Определенные имена и щелкните Определить имя . Либо нажмите Ctrl + F3 и щелкните New .
      • Введите имя в поле Имя
      • В поле Относится к введите элементы константы массива, заключенные в фигурные скобки с предшествующим знаком равенства (=).Например:

        = {«Вс», «Пн», «Вт», «Ср», «Чт», «Пт», «Сб»}

      • Щелкните OK, чтобы сохранить именованный массив и закрыть окно.

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

      Результат должен быть примерно таким:

    3. Предотвращение ошибок

      Если константа массива работает неправильно, проверьте наличие следующих проблем:

      • Разделите элементы константы массива правильным символом — запятой в горизонтальных константах массива и точкой с запятой в вертикальных.
      • Выбран диапазон ячеек, который точно соответствует количеству элементов в константе массива. Если вы выберете больше ячеек, каждая дополнительная ячейка будет иметь ошибку # N / A. Если вы выберете меньше ячеек, будет вставлена ​​только часть массива.

    Использование констант массива в формулах Excel

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

    Пример 1. Сумма N наибольших / наименьших чисел в диапазоне

    Вы начинаете с создания константы вертикального массива, содержащей столько чисел, сколько вы хотите суммировать.Например, если вы хотите сложить 3 наименьших или наибольших числа в диапазоне, константа массива будет {1,2,3}.

    Затем вы берете функцию LARGE или SMALL, указываете весь диапазон ячеек в первом параметре и включаете константу массива во второй. Наконец, вставьте его в функцию SUM, например:

    Суммируйте 3 наибольших числа: = СУММ (НАИБОЛЬШИЙ (диапазон, {1,2,3}))

    Суммируйте 3 наименьших числа: = СУММ (МАЛЫЙ (диапазон, {1,2,3}))

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

    Аналогичным образом вы можете вычислить среднее из N наименьших или наибольших значений в диапазоне:

    Среднее значение трех верхних чисел: = СРЕДНИЙ (БОЛЬШОЙ (диапазон, {1,2,3}))

    Среднее значение трех нижних чисел: = СРЕДНЕЕ (МАЛЕНЬКОЕ (диапазон, {1,2,3}))

    Пример 2.Формула массива для подсчета ячеек с несколькими условиями

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

    Самый простой способ — использовать формулу СЧЁТЕСЛИМН с несколькими условиями. Однако, если вы хотите включить много продуктов, ваша формула СЧЁТЕСЛИМН может стать слишком большой. Чтобы сделать его более компактным, вы можете использовать СЧЁТЕСЛИМН вместе с СУММ и включить константу массива в один или несколько аргументов, например:

    = СУММ (СЧЁТЕСЛИ (диапазон1, «критерий1», диапазон2, {«критерий1», «критерий2»}))

    Реальная формула может выглядеть следующим образом:

    = СУММ (СЧЁТЕСЛИ (B2: B9, «вылазка», C2: C9, {«яблоки», «лимоны»}))

    Наш образец массива состоит всего из двух элементов, так как цель — продемонстрировать подход.В настоящие формулы массива вы можете включать столько элементов, сколько требует ваша бизнес-логика, при условии, что общая длина формулы не превышает 8192 символа в Excel 2019 — 2007 (1024 символа в Excel 2003 и ниже) и ваш компьютер достаточно мощный. достаточно для обработки больших массивов. Пожалуйста, ознакомьтесь с ограничениями формул массива для получения более подробной информации.

    А вот расширенный пример формулы массива, который находит сумму всех совпадающих значений в таблице: СУММ и ВПР с константой массива.

    Операторы AND и OR в формулах массива Excel

    Оператор массива сообщает формуле, как вы хотите обрабатывать массивы — с помощью логики И или ИЛИ.

    • Оператор AND — это звездочка (*), которая является символом умножения. Он указывает Excel вернуть значение ИСТИНА, если ВСЕ условия оцениваются как ИСТИНА.
    • Оператор ИЛИ — это знак плюса (+). Он возвращает ИСТИНА, если ЛЮБОЕ из условий в данном выражении оценивается как ИСТИНА.
    Формула массива с оператором И

    В этом примере мы находим сумму продаж, где продавец — Майк И продукт — Яблоки :

    = СУММ ((A2: A9 = «Майк») * (B2: B9 = «Яблоки») * (C2: C9))

    или

    = СУММ (ЕСЛИ (((A2: A9 = «Майк») * (B2: B9 = «Яблоки»)), (C2: C9)))

    Технически, эта формула умножает элементы трех массивов на одинаковые позиции.Первые два массива представлены значениями ИСТИНА и ЛОЖЬ, которые являются результатом сравнения A2: A9 с «Майк» и B2: B9 с «Яблоками». Третий массив содержит цифры продаж из диапазона C2: C9. Как и любая математическая операция , умножение преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. И поскольку умножение на 0 всегда дает ноль, результирующий массив имеет 0, когда одно или оба условия не выполняются. Если оба условия выполняются, соответствующий элемент из третьего массива получает в окончательный массив (например,грамм. 1 * 1 * C2 = 10). Итак, результатом умножения будет такой массив: {10; 0; 0; 30; 0; 0; 0; 0}. Наконец, функция СУММ складывает элементы массива и возвращает результат 40.

    Формула массива Excel с оператором ИЛИ

    Следующая формула массива с оператором ИЛИ (+) суммирует все продажи, где продавец — Майк, ИЛИ продукт — Яблоки:

    = СУММ (ЕСЛИ (((A2: A9 = «Майк») + (B2: B9 = «Яблоки»)), (C2: C9)))

    В этой формуле вы складываете элементы первых двух массивов (которые являются условиями, которые вы хотите проверить) и получаете ИСТИНА (> 0), если хотя бы одно условие оценивается как ИСТИНА; ЛОЖЬ (0), когда все условия оцениваются как ЛОЖЬ.Затем IF проверяет, больше ли результат сложения 0, и если да, SUM складывает соответствующий элемент третьего массива (C2: C9).

    Наконечник. В современных версиях Excel нет необходимости использовать формулу массива для такого рода задач — простая формула СУММЕСЛИМН отлично справляется с ними. Тем не менее, операторы AND и OR в формулах массива могут оказаться полезными в более сложных сценариях, не говоря уже об очень хорошей гимнастике ума 🙂

    Двойной унарный оператор в формулах массива Excel

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

    Двойное тире, которое технически называется двойным унарным оператором , используется для преобразования нечисловых логических значений (ИСТИНА / ЛОЖЬ), возвращаемых некоторыми выражениями, в 1 и 0, которые может понять функция массива.

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

    Следующая формула подойдет:

    = СУММ (- (МЕСЯЦ (A2: A10) = 1))

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

    Если вас интересует другой месяц, замените 1 на соответствующее число. Например, 2 означает февраль, 3 означает март и так далее. Чтобы сделать формулу более гибкой, вы можете указать номер месяца в какой-либо ячейке, как показано на скриншоте:

    А теперь давайте проанализируем, как работает эта формула массива. Функция МЕСЯЦ возвращает месяц каждой даты в ячейках с A2 по A10, представленный порядковым номером, который дает массив {2; 1; 4; 2; 12; 1; 2; 12; 1}.

    После этого каждый элемент массива сравнивается со значением в ячейке D1, которое в этом примере имеет номер 1. Результатом этого сравнения является массив логических значений ИСТИНА и ЛОЖЬ. Как вы помните, вы можете выбрать определенную часть формулы массива и нажать F9, чтобы увидеть, что означает эта часть:

    Наконец, вы должны преобразовать эти логические значения в единицы и нули, которые может понять функция СУММ. А для этого нужен двойной унарный оператор.Первый унарный переводит ИСТИНА / ЛОЖЬ в -1/0 соответственно. Вторая унарная инвертирует значения, то есть меняет знак, превращая их в +1 и 0, что большинство функций Excel могут понимать и с которыми могут работать. Если вы удалите двойной унарный из приведенной выше формулы, это не сработает.

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

    Вас также может заинтересовать

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

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

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

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

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

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

    Заполнение формулы массива в Excel

    Формулы массива также часто называют формулами CSE (Ctrl + Shift + Enter), потому что вместо простого нажатия Enter для получения результата вам нужно нажать Ctrl + Shift + Enter для завершения формула и произвести результат.

    Почему используются формулы массива?

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

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

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

    Формулы массива обладают следующими преимуществами:

    1. Формулы массива обеспечивают согласованность, точность и точность вычисляемых формул и значений, применяемых к диапазону ячеек.
    2. Формулы массива сокращают потери времени на непродуктивные операции за счет применения формулы непосредственно к диапазону ячеек, и их не нужно применять отдельно.
    3. Формулы массива гарантируют отсутствие избыточности и безопасность для пользователей, поскольку компонент формулы массива с несколькими ячейками не может быть заменен каким-либо другим значением, а формула массива ячеек целиком должна быть выбрана для изменения или безвозвратного удаления. Кроме того, в качестве дополнительной меры предосторожности вы должны одновременно нажать CSE для изменения формулы.
    4. Формулы массива уменьшают размер файлов за счет использования одной формулы, а не различных подобных промежуточных формул.
    5. Формулы массива уменьшают вероятность получения ошибочных результатов при неправильном применении формул.
      Например, когда необходимо вычислить сумму рабочих часов для каждого квартала месяцев, к массиву можно применить одну формулу суммы (формула массива с несколькими ячейками) вместо применения 4 различных формул массива.

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

    Синтаксис формулы Excel очень общий и основан на различных встроенных функциях.Как и другие формулы, формула массива начинается со знака равенства (=). Ключевое различие между общей арифметической формулой и арифметической формулой массива заключается в том, что при использовании формулы массива вы нажимаете Ctrl + Shift + Enter, чтобы получить результат введенной формулы, тогда как в первом случае для получения результата используется Enter. Когда это будет сделано, Excel автоматически заключит формулу массива в фигурные скобки. Ручной ввод фигурных скобок преобразует ее в текстовую строку, как в случае с другими компьютерными языками, такими как C, и просто не будет работать и выполнять функцию.

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

    Например: Чтобы вычислить сумму рабочих часов разных сотрудников и наградить лучшего сотрудника с наибольшим количеством рабочих часов, выполняется следующим образом:
    {= D3: D12 + E3: E12 + F3: F12 + G3: G12}
    , что то же самое, что и
    = СУММ (D3: G3), примененное к h4, = СУММ (D4: G4), примененное к h5, ……., = СУММ (D12: G12), примененное к h22.

    Это формула массива с несколькими ячейками.

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

    1. Формула массива всегда начинается со знака равенства.
    2. После того, как формула массива введена и применена к диапазону ячеек, будь то одна ячейка или несколько ячеек, ее функциональность может быть применена только при одновременном вводе Ctrl + Shift + Enter для получения результата. Это используется, чтобы распознать его как формулу массива и сохранить в своей памяти, автоматически добавляя фигурные скобки к формулам. Если вручную ввести фигурные скобки, Excel будет рассматривать ее как текстовую строку, которая не будет выполнять необходимую функцию.
    3. При работе с формулами с несколькими ячейками всегда применяйте результат, выбирая «диапазон» ячеек, к которым необходимо применить формулы массива.
    4. Отдельное содержимое отдельной ячейки в формуле массива нельзя изменить или изменить. Если попытаться сделать это, Excel отобразит сообщение о том, что вы не можете изменить часть массива, так как при вводе формулы массива Excel обрабатывает ее как данные, связанные друг с другом и нуждающиеся в коллективном изменении.
    5. Ссылаясь на вышеупомянутый пункт, отдельная ячейка не может быть удалена, и весь массив необходимо удалить, выбрав весь массив и нажав клавишу «Удалить». Чтобы удалить любую формулу массива, выберите всю формулу (например, = D3: D12 + E3: E12 + F3: F12 + G3: G12), нажмите Delete, а затем нажмите Ctrl + Shift + Enter.

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

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

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

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

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

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

      Например:
      Чтобы рассчитать общее количество рабочих часов всех сотрудников вместе и получить результат в ячейке h23, используется формула массива:
      = СУММ (D3: D12, E3: E12, F3: F12, G3: G12)

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

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

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

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

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

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

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

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

      Например:
      Чтобы рассчитать рабочее время каждого сотрудника в течение года и получить результат в диапазоне ячеек от h4: h22, используется формула массива:
      {= D3: D12 + E3: E12 + F3: F12 + G3 : G12}

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

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

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

    Редактирование формулы массива одиночных ячеек

    Для редактирования формулы массива одиночных ячеек необходимо последовательно выполнить следующие шаги:

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

    Формула отредактирована.

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

    Для редактирования формулы массива с несколькими ячейками необходимо последовательно выполнить следующие шаги:

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

    Формула отредактирована.

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

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

    Для перемещения формулы массива необходимо последовательно выполнить следующие шаги:

    1. Выберите диапазон ячеек, которые необходимо переместить.
    2. Нажмите Ctrl + X на клавиатуре.
    3. Выберите новое расположение ячеек.
    4. Нажмите Ctrl + V на клавиатуре, чтобы переместить их в новое желаемое положение.

    Удаление формулы массива

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

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

    Полное удаление формулы массива (без сохранения результата формулы массива)

    Для полного удаления формулы массива необходимо последовательно выполнить следующие шаги:

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

    Желаемая функция полного удаления выполнена.

    Удаление формулы массива (сохранение результата формулы массива)

    Следующие шаги необходимо выполнить последовательно, чтобы удалить формулу массива с сохранением результатов формулы массива:

    1. Скопируйте формулу и затем вставьте ее в ту же ячейку, используя параметр «Вставить значения», полученный путем щелчка правой кнопкой мыши по нужной ячейке или на вкладке «Главная» в разделе «Буфер обмена», а затем нажмите «Удалить», чтобы удалить значения.

    OR

    1. Выберите ячейку или диапазон ячеек, содержащих формулу, которую необходимо удалить.
    2. На вкладке «Главная» в разделе «Редактирование» нажмите «Найти и выбрать», а затем нажмите «Перейти».
    3. Щелкните Текущий массив.
    4. На вкладке «Главная» в группе «Буфер обмена» щелкните «Копировать».
    5. На вкладке «Главная» в группе «Буфер обмена» щелкните стрелку под надписью «Вставить» и выберите «Вставить значения».
    6. Выберите диапазон ячеек и нажмите удалить.

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

    Константы массива — это значения, добавленные в фигурные скобки, чтобы упростить совместную работу со связанными значениями путем присвоения имени константе массива, а также их совместной обработки. Они коллективно используют константы, а также диапазон значений. При использовании формулы массива введите открывающую скобку, введите желаемые значения и, наконец, введите закрывающую скобку. Например: = PRODUCT (A1: E1 * {1,2,3,4,5})

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

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

    Давайте решим следующую задачу на примере:
    Если нам нужно ввести пять значений, а именно 10, 20, 30, 40 и 50, в один столбец A последовательно, следующие шаги, которые необходимо принять:

    1. Выберите ячейки, в которые необходимо ввести значения.
    2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках.Если значение является строкой, поместите текст в двойные кавычки («).
      Разделяйте значения констант точкой с запятой, а не запятыми.
    3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы сохранить значения в столбце.
      Например: = {10; 20; 30; 40; 50}

      Это также называется одномерным вертикальным постоянным массивом.

    Использование константы массива для ввода значений в строку

    Давайте решим следующую задачу на примере:
    Если нам нужно ввести пять значений, а именно 10, 20, 30, 40 и 50 в одну строку 1 последовательно, необходимо выполнить следующие шаги:

    1. Выберите ячейки, в которые необходимо ввести значения.
    2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках. Если значение является строкой, поместите текст в двойные кавычки («).
      Теперь значения констант разделяйте запятыми, а не точкой с запятой.
    3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы значения в строке содержались.
      Например: = {10; 20; 30; 40; 50}

      Это также называется одномерным горизонтальным массивом констант.

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

    Давайте решим следующую задачу на примере:
    Если нам нужно ввести двадцать пять значений, а именно, 10, 20, 30, 40,50…..240,250 последовательно, в нескольких строках и столбцах, составляющих пять строк и столбцов, необходимо выполнить следующие шаги:

    1. Выберите ячейки, в которые необходимо ввести значения.
    2. Введите знак равенства и желаемые постоянные значения, которые должны содержаться в ячейках. Если значение является строкой, поместите текст в двойные кавычки («).
      Разделите значения констант, которые должны содержаться в строках, запятыми и точками с запятой, при этом значения констант должны содержаться в столбцах.
    3. Нажмите Ctrl + Shift + Enter на клавиатуре, чтобы значения в строке содержались.
      Например: = {10,20,30,40,50; 60,70,80,90,100; 110,120,130,140,150; 160,170,180,190,200; 210,220,230,240,250}

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

    Следует помнить

    1. Знак равенства (=) необходимо добавлять в начало каждой формулы массива Excel.
    2. Скобки нельзя установить вручную при работе с формулой массива.Их можно ввести только вручную при работе с константами массива.
    3. При вводе констант массива можно использовать числа и текст. Однако при работе с константами массива, содержащими текст, вводите его в двойные кавычки («»).

    3 шага к работе с формулами массива в Excel

    Формулы массива в Excel — отличный инструмент для выполнения вычислений временных рядов или исследования чувствительности к входным переменным.

    Массив — это группа ячеек, которую можно рассматривать как один объект.Есть два типа массивов: одномерные (значения в строках ИЛИ столбцы) и двумерные (значения в строках И столбцах).

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

    Некоторые вычисления намного проще выполнять с массивами или формулами массива. Например: суммирование «n» наибольших или наименьших чисел из диапазона.

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

    Массивы

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

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

    1. Используйте Ctrl + Shift + Enter

    Самым основным компонентом работы с формулами массива является то, что вы должны вводить их, используя Ctrl + Shift + Enter (или для краткости CSE). Процесс создания формулы массива выглядит следующим образом:

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

    2.Используйте имена для ссылки на массивы

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

    3. Выберите массивы с помощью сочетания клавиш

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

    Выделив любую ячейку в массиве, просто одновременно нажмите «Ctrl» и «/» (косая черта), и будет выбран весь массив. Затем просто нажмите клавишу F2 или отредактируйте формулу в строке формул. (Не забудьте нажать Ctrl + Shift + Enter, когда закончите!)

    Наличие большого количества массивов большого размера может замедлить ваши вычисления. Обычно это заметно только на очень больших таблицах.

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

    динамических массивов в Excel: восемь формул, которые необходимо знать

    Этот учебник по динамическим массивам в Excel s подходит для пользователей Excel для Microsoft 365.

    Цель

    Изучите НОВЫЕ функции Microsoft Dynamic Array в Excel и эффективно используйте их для решения проблем.

    Объяснение динамических массивов в Excel
    Формулы динамического массива

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

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

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

    Функция Цель
    ФИЛЬТР Отфильтровать данные и вернуть соответствующие записи
    RANDARRAY Сгенерировать массив случайных чисел
    Сгенерировать массив последовательных чисел
    SORT Сортировать диапазон по столбцу
    SORTBY Сортировать диапазон по другому диапазону или массиву
    UNIQUE Извлечь уникальные значения из списка или диапазона
    для замены XL
    XMATCH Современная замена функции ПОИСКПОЗ

    В этом посте мы рассмотрим пример каждой из них.

    Видеоурок

    ФИЛЬТР

    Функция ФИЛЬТР Excel фильтрует данные на основе заданных вами критериев. Он возвращает все совпадающие записи и, если не может найти никаких записей, возвращает значение, указанное в формуле.

    = ФИЛЬТР (массив; включить, [if_empty])

    В приведенном ниже примере Excel использует функцию ФИЛЬТР для поиска в массиве B4: E23 диапазона ячеек « Block» и извлекает значение, расположенное в ячейке G4 (восток).Это наш первый критерий. Затем он просматривает диапазон ячеек « Exam» и извлекает значение, расположенное в G7 (на английском языке). Две кавычки указывают Excel ничего не отображать, если он не может найти совпадений.

    Это результат:

    RANDARRAY

    Функция СЛУЧАЙНЫЙ СЛУЧАЙ генерирует случайные числа. Размер массива определяется аргументом строк и столбцов. Вы также можете выбрать диапазон, в который будет попадать число, и если вы хотите, чтобы они были десятичными или целыми числами (целыми числами).Это похоже на более мощную версию функции RANDBETWEEN.

    = RANDARRAY ([строки], [столбцы], [min], [max], [integer])

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

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

    В этом примере Excel использовал функцию RANDARRAY для вывода таблицы случайных чисел в шесть строк в высоту и шесть столбцов в ширину со значениями от 10 до 50.Аргумент ИСТИНА указывает Excel выводить целые (целые) числа, а не десятичные.

    ПОСЛЕДОВАТЕЛЬНОСТЬ

    Функция ПОСЛЕДОВАТЕЛЬНОСТЬ генерирует список последовательных чисел. Массив может быть одномерным или двумерным, и вы можете указать начальное значение и значение шага. Если определен только аргумент rows, Excel выведет список значений, начиная с единицы по умолчанию.

    = ПОСЛЕДОВАТЕЛЬНОСТЬ (строки, [столбцы], [начало], [шаг])

    В этом примере Excel использовал функцию ПОСЛЕДОВАТЕЛЬНОСТЬ только с аргументом строк для вывода списка, который начинается с единицы.

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

    В этом примере Excel использовал функцию ПОСЛЕДОВАТЕЛЬНОСТЬ для вывода списка последовательных чисел в десять строк в высоту и восемь столбцов в ширину, начиная с десяти и увеличиваясь с шагом в пять.

    СОРТ

    Функция СОРТИРОВКА в Excel сортирует содержимое диапазона, используя один или несколько столбцов.

    = СОРТИРОВАТЬ (массив, [sort_index], [sort_order], [by_col])

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

    В этом примере Excel использовал функцию SORT для сортировки меток в левой таблице в порядке убывания и вывода результатов в таблицу справа.

    СОРТ ищет в массиве B4: E23 столбец 4 и сортирует результаты в порядке убывания.

    Если аргумент [sort_order] не указан, сортировка по умолчанию выполняется в порядке возрастания.

    СОРТБИ

    Функция СОРТИРОВАТЬ в Excel сортирует содержимое диапазона на основе значений из другого диапазона. Вы можете выполнять несколько сортировок одних и тех же данных.

    = СОРТИРОВАТЬ (массив, по_массиву, [сортировка_порядка], [массив / порядок],…)

    В этом примере Excel использует функцию SORTBY для сортировки информации в таблице слева по метке в порядке убывания.Он выводит результаты в таблицу справа.

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

    УНИКАЛЬНЫЙ

    Функция UNIQUE возвращает список уникальных значений из диапазона.

    = УНИКАЛЬНЫЙ (массив; [by_col]; [точно_once])

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

    В этом примере Excel использует функцию UNIQUE для возврата списка всех уникальных элементов в горизонтальном диапазоне.

    XLOOKUP

    Функция XLOOKUP является современной заменой таких функций, как VLOOKUP, INDEX и MATCH. Он может выполнять поиск в вертикальном и горизонтальном диапазонах и поддерживает приблизительные и точные совпадения.

    = XLOOKUP (поиск, поиск_массив, возврат_массив, [not_found], [match_mode], [search_mode])

    В приведенном ниже примере Excel использовал функцию XLOOKUP для поиска приложения, указанного в ячейке h5 в диапазоне ячеек приложения, и возврата соответствующего результата из столбца «Доход».

    XMATCH

    Функция XMATCH выполняет поиск и возвращает позицию в вертикальном или горизонтальном диапазонах. Это последняя версия функции ПОИСКПОЗ и поддерживает приблизительное и точное соответствие.

    = XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

    В этом примере Excel использует функцию XMATCH для поиска значения в ячейке F3 в именованном диапазоне приложения. Он выполняет точное соответствие слову «Slack», и начинает поиск с первого элемента в списке.

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

    Дополнительные бесплатные руководства по Excel от Simon Sez IT. Взгляните на наш ресурсный центр Excel .

    Другие классы Excel, которые могут вам понравиться:

    Чтобы изучить Excel с Simon Sez IT. Взгляните на доступные курсы Excel .

    Excel Формула массива МЕДИАНА ЕСЛИ

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

    Основы

    Синтаксис и аргументы формулы MEDIAN IF следующие:

    {= МЕДИАНА (ЕСЛИ (логический_тест, значение_если_ истинное значение, значение_если_ ложь)}

    Цели каждой части формулы:

    • Функция МЕДИАНА находит среднее значение из набора чисел.
    • Функция ЕСЛИ позволяет нам устанавливать условия для значений, которые мы хотим исследовать.
    • Формула массива позволяет функции ЕСЛИ проверять несколько условий в одной ячейке. Когда условие выполнено, формула массива определяет, какие данные будет проверять функция МЕДИАНА, чтобы найти среднюю сумму вознаграждения.

    Чтобы заполнить формулу массива, введите формулу (без фигурных скобок), затем одновременно нажмите клавиши Control, Shift и Enter. Это завершит формулу массива в Excel и вставит фигурные скобки в начало и конец формулы. Не вводите в формулу фигурные скобки. Формулы массива, которые создаются путем ввода Control, Shift и Enter, также известны как формулы CSE.

    Пример

    Формула MEDIAN IF полезна для расчета средних сумм грантов / стипендий, присуждаемых студентам, отвечающим различным критериям проживания, жилья и предполагаемого семейного вклада (EFC), которые необходимы для заполнения шаблона калькулятора чистой цены, который оценивает чистые затраты для студентов.

    Метод

    В этом примере мы работаем с двумя листами в нашей книге Excel:

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

    Рисунок 1.Рабочий лист набора данных (отображается только часть набора данных)

    Рис. 2. Сводная таблица — сетка калькулятора чистой цены

    Для начала используйте формулу массива MEDIAN IF, чтобы найти среднюю сумму вознаграждения для студентов, которые проживают в округе, живут на территории кампуса и имеют EFC равное 0 долларов США (см. Красную рамку на рис. 2, это ячейка B4). Светло-синие, темно-синие и фиолетовые поля являются критериями формулы.

    В ячейке B4 сводной таблицы введите следующую формулу:

    Следующие ссылки на столбцы и ячейки представляют наши критерии:

    Для студентов, для которых выполняются вышеуказанные критерии, среднее значение берется из следующего столбца:

    • Dataset! $ E: $ E — студенческий грант / размер стипендии

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

    Чтобы завершить формулу массива, одновременно нажмите клавиши Control, Shift и Enter (фигурные скобки будут автоматически вставлены, чтобы заключить формулу).

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

    В описательной форме эта формула говорит: «Возвратите медианное значение суммы гранта / стипендии для студентов, чей статус проживания соответствует статусу проживания, выбранному в сводной сетке, И чей жилищный статус соответствует жилищному статусу, выбранному в сводной сетке, И чей EFC Диапазон соответствует диапазону EFC, выбранному в итоговой сетке.”

    Общие советы по формулам массива

    Формулы массива могут занимать больше времени для вычисления и увеличения размера файла больше, чем формулы без массива. Чтобы решить эти проблемы, попробуйте сделать следующее:

    • Копируйте и вставляйте формулы массива в новые ячейки небольшими разделами за раз (например, если вам нужно скопировать формулу массива в общей сложности на 800 ячеек, разделите процесс на части, чтобы вы скопировали формулу в 200 ячеек за один раз). время).
    • После вставки формулы массива в ячейки скопируйте результаты и вставьте special: values ​​в то же место.Это приведет к удалению формул, так что они больше не увеличивают размер файла и скорость обработки.

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

    • {= МАКС (ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)}
    • {= МИН (ЕСЛИ (логический_тест, значение_если_ истинное значение, значение_если_ ложь)}

    Просмотреть следующий совет

    Формулы массива Excel — обзор, единичный вывод, вывод массива

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

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

    Краткое описание:
    • Формулы массива позволяют использовать стандартные формулы Excel для нескольких ячеек одновременно. В отличие от стандартных формул, которые принимают отдельные ячейки в качестве входных данных, формулы массивов принимают целые массивы в качестве входных данных.
    • Excel использует следующий формат для ссылки на массивы: Первая ячейка: Последняя ячейка.
    • В отличие от стандартных формул ячеек, которые можно использовать простым нажатием клавиши Enter / Return, формулы массива в Excel используются одновременным нажатием клавиш Control / Command, Shift и Enter.

    Типы формул массива

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

    1. Формулы для одного выходного массива

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

    2. Формулы массива выходных массивов

    Они принимают один массив данных или несколько массивов данных в качестве входных и выдают массив данных в качестве выходных данных.

    Что такое массив в Microsoft Excel?

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

    Первая ячейка: Последняя ячейка

    Ссылка на ячейку желтой ячейки на изображении ниже — B5. Ссылки на ячейки зеленого массива и красного массива — это D1: D10 и A12: F14 соответственно.

    Общие сведения о формулах массива в Microsoft Excel

    В отличие от стандартных формул ячеек, которые можно использовать простым нажатием клавиши Enter / Return, формулы массива в Excel используются нажатием клавиш Control / Command, Shift и Enter клавиши одновременно.Если формула успешно распознается как формула массива, Excel автоматически помещает формулу в фигурные / закрученные скобки {} .

    Формулы единого выходного массива — иллюстративные примеры

    Расчет средних значений

    Мы можем найти среднее значение массива, введя в Excel следующее «= среднее (первая ячейка: последняя ячейка)» и нажав клавишу Control / Command, Shift и Enter одновременно. В этом случае мы можем найти среднюю зарплату, получаемую работниками компании ABC.

    Расчет условных средних

    Мы можем найти условные средние, используя формулы массива.

    Формулы для выходных массивов — иллюстративный пример

    Расчет сумм по массиву

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

    2. После создания столбца Total Pay мы выбираем массив, в котором должны отображаться выходные данные.

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

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

    5. Нам нужно одновременно нажать клавиши Control / Command, Shift и Enter.

    Дополнительные ресурсы

    CFI предлагает программу сертификации финансового моделирования и оценки (FMVA) ™ Стать сертифицированным аналитиком финансового моделирования и оценки (FMVA) ® для тех, кто хочет вывести свою карьеру на новый уровень. Чтобы продолжить изучение и развитие своей базы знаний, ознакомьтесь с дополнительными соответствующими ресурсами ниже:

    • Рекомендации по моделированию в Excel Рекомендации по моделированию в Excel Следующие рекомендации по моделированию в Excel позволяют пользователю обеспечить наиболее чистый и удобный опыт моделирования.

    Добавить комментарий

    Ваш адрес email не будет опубликован.