Функции массива в excel: Создание формулы массива

Содержание

Создание формулы массива — Служба поддержки 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

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

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

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

И всегда означает 100 % времени.

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

=C2:C11*D2:D11
Нажмите клавиши CTRL+SHIFT+ВВОД.

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

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

=СУММ(C2:C11*D2:D11)
Нажмите клавиши CTRL+SHIFT+ВВОД.

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

— Excel

MS Office 2007: Microsoft Excel

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

Одна из наиболее интересных (и наиболее мощных) возможностей Excel — допустимость использования массивов в формулах.

Массив — это набор элементов, которые могут обрабатываться как единая группа или каждый в отдельности. В Excel массивы могут быть одно- или двухмерными. Измерения массивов непосредственно соответствуют строкам и столбцам. Например, одномерный массив может быть группой ячеек, которые размещены в одной строке (горизонтальный массив) или в одном столбце (вертикальный массив). Двухмерный массив размещается в нескольких строках и столбцах (рис.94). Трехмерных массивов Excel не поддерживает.

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

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

На рис.95 показан простой рабочий лист, на котором вычисляются

объемы продаж некоторых товаров. Чтобы вычислить объем продаж каждого конкретного товара (значения в столбце D), обычно используется формула умножения количества проданного товара (столбец С) и цены товара (столбец В). Например, в ячейке D2 будет формула =В2*С2, которая затем копируется на все оставшиеся ячейки столбца D. В данном случае получим пять отдельных формул в столбце D.

Другой способ вычисления пяти значений в столбце D — использование одной формулы массива. Эта формула займет диапазон D2:D5 и вернет сразу все пять искомых значений.

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

Выделить диапазон, в котором должен содержаться результат. В нашем примере это диапазон D2:D5.
Ввести формулу =В2:В5*С2:С5. Поставьте равно и выделите диапазон В2:В5, поставьте значок умножить и выделите диапазон С2:С5.
Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы ввести формулу как формулу массива (обычно для завершения ввода формулы следует нажать клавишу Enter).

Формула будет введена сразу во все пять выделенных ячеек. Если вы посмотрите в строку формул, то там будет представлена формула {=В2:В5*С2:С5}, т.е. Excel произвел попарное умножение элементов массивов В2:В5 и С2:С5 и создал новый массив стоимостей D2:D5.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl+Shift+Enter (при удержании нажатых клавиш Shift и Ctrl производят нажатие клавиши Enter). Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока (Проверьте!). Excel заключает формулу в фигурные скобки, показывая тем самым, что это формула массива. Эти скобки нельзя набирать вручную (формула будет воспринята как текст).

Задача 5.9. Формулы массивов для отдельных ячеек

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

{=СУММ(В2:В5*С2:С5)}.

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

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

Формула, как и предыдущая, работает с двумя массивами, хранящимися в диапазонах В2:В5 и С2:С7. Формула перемножает соответствующие значения из этих массивов виртуальный массив, который существует только в памяти компьютера. Затем функция СУММ обрабатывает этот новый массив и возвращает сумму его значений.

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

Задача 5.10. Именованные массивы

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

Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные в таблицу (рис.97).

Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2:В5) и третьим (С2:С5) столбцами таблицы последовательно и присвойте имена во вкладке Формулы-присвоить Имя. Диапазону В2:В5 присвойте имя Приход, а диапазону С2:С5 — имя Расход.

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

Вводим в диапазон D2:D5 формулу массива.

Выделим блок D2: D5. В этом блоке активна ячейка D2.
Наберем знак равенства =.
Нажмем функциональную клавишу F3. Появится окно Вставка имени. Выберем имя Приход и щелкнем Ок. Формула примет вид: =Приход.
Наберем знак минус (-).
Вновь нажмем клавишу F3. В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ок. Формула примет вид: =Приход-Расход.
Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}.

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

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12 и повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9: С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы.

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

Изменение формулы массива. Попытайтесь очистить одну из ячеек, занятую формулой. Например, выделите ячейку D9 и нажмите клавишу Del. В этом случае появляется сообщение «Нельзя изменять часть массива», откуда следует, что удалить блок можно только целиком.

Чтобы отредактировать формулу массива, необходимо выделить все ячейки массива, активизировать строку формул и удалить фигурные скобки. По окончании редактирования формулы, следует нажать комбинацию клавиш Shift+Ctrl+Enter, чтобы внести изменения. Теперь содержимое всех ячеек массива изменится в соответствии с внесенными изменениями. (Попробуйте, например, ввести формулу {Приход-Расход-1} , потом отменить это.)

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

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

Коррекция формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год — 1996, приход — 240, расход 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, поступили бы просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13 : В16-С13 : С16} — образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

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

Во вкладке Формулы-Диспетчер имен выделить Приход, и внизу окна в Диапазон изменить диапазон значений с В1:С5 на В1:С6. Для его изменения удалить имеющийся в окне диапазон и, перейдя на лист, выделить новый диапазон В1:С6. Точно также изменить диапазон для Расхода. После изменений диалоговое окно закрыть.
Выделить D2: D6, нажать клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажать клавиши Shift+ Ctrl+Enter.

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

Выделить D8 :D13 и нажать клавишу F2 для редактирования формулы.
Изменить в формуле ссылки на диапазоны ячеек с В8:В12 на В8:В13 и с С8: С12 на С8:С13. Нажать сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока. Теперь необходимо удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменить поименованные блоки (в результате в ячейке D6 результат отобразится как #Н/Д — недоступно). Выделить блок с формулой массива, нажать клавишу F2. Ввод закончить клавишами Ctrl+Enter.

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

Очистить последнюю строку таблицы. Выделить блок D2: D5, нажать клавишу F2, нажать клавиши Shift+Ctrl+Enter.

Аналогично поступить со второй таблицей:

Выделить диапазон с формулой массива.
Нажать комбинацию клавиш Ctrl+Enter, чтобы преобразовать формулу массива в обычную.
Удалить последнюю строчку таблицы.
Выделить диапазон для формулы массива, D9: D12.
Нажать F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удаленной строки. Для корректировки ссылок в формуле можно уменьшить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменяться в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 (В13 изменить на В12 и С13 на С12).
Закончить изменения клавишами Shift+Ctrl+Enter.

Задача 5.11. Создание массивов на основе значений ячеек диапазона.

На рис.98 показан рабочий лист, содержащий данные в диапазоне A1: С4.

Диапазон С8:F11 содержит массив, созданный на основе этих данных с помощью формулы {=А1:С4}

Массив в диапазоне С8 :F11 связан со значениями диапазона А1:С4.

Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве.

Задача 5.12. Транспонирование массива.

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

Допустим, имеется двумерный массив ячеек (рис.99), который необходимо транспонировать.

Для размещения транспонированного массива следует выделить диапазон ячеек (рис. 100). Поскольку исходный массив ячеек состоял из 8 строк на 2 столбца, то необходимо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. Далее в выделенном диапазоне ввести функцию транспонирования =ТРАНСП(, где в качестве аргумента функции является наш массив ячеек А1:В8. Заканчиваем ввод формулы комбинацией клавиш Ctrl+Shift+Enter и получаем «перевернутый массив» в качестве результата (рис. 101).

Задача 5.13. Таблица умножения.

Если вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке была таблица умножения вот такого вида (рис. 102).

При помощи формул массива она вся делается в одно движение:

ввести два диапазона чисел от 1 до 10 в строке 1 и столбце А;
выделить диапазон В2:К11;
ввести формулу =А2:A11*В1:К1;
закончить ввод формулы комбинацией клавиш Ctrl+Shift+Enter, чтобы Excel воспринял ее как формулу массива. Результат представлен ниже (рис. 103).

Задача 5.14. Выборочное суммирование.

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику (рис. 104).

В данном случае формула массива синхронно пробегает по всем элементам диапазонов СЗ:С21 и ВЗ:В21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5.

Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Можно применить и другой способ:

Нам необходимо просуммировать числа из диапазона D3:D21 (диапазон суммирования) при выполнении нескольких (двух) условий. Для каждой строки в случае одновременного равенства числа из диапазона СЗ:С21 числу G8, и числа из диапазона ВЗ:В21 числу G9, соответствующее число из диапазона суммирования будет суммироваться.

Для этого применим функцию СУММЕСЯИМН.

=СУММЕСЛИМН(D3:D21; СЗ:С21; G8; ВЗ:В21; G9)

На открывшейся форме СУММЕСЯИМН в окне Диапазон суммирования выбрать диапазон для суммирования (D3:D21). Для прописания условий в окне Диапазон_условия1 необходимо выбрать диапазон первого условия СЗ:С21, который равен критерию G8 — окно Условие1, в окне Диапазон_условия2 ввести диапазон второго условия ВЗ:В21, который должен равняться G9 — окно Условие2.

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

Задача 5.15.

Вернемся к задаче обработки данных метеостанции (Задача 5.5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:Н14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводится формула =ЕСЛИ(И(ВЗ>20; в3<80) ; 1; 0) и копируется в остальные ячейки блока F3:h24. В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 — выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 — в противном случае. Для подсчета суммы нормальных месяцев вводим в ячейку F25 формулу =СУММ (F3:F14) и копируем ее в блок G25:Н25. Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ (ЕСЛИ (ВЗ : В14>0;ЕСЛИ (ВЗ :В14<80; 1; 0) ; 0) ) } и скопируем ее в С25:D25. (Таким образом, компьютер, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае — проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае — 0). Формула {=СУММ (ЕСЛИ (И(ВЗ>20;ВЗ<80) ;1;0) ) } не приведет к успеху, так как функция И не является дистрибутивной.

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

Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу {=СУММ(ЕСЛИ(ВЗ:В14>20;ЕСЛИ(ВЗ:В14<80; ВЗ : В14; 0) ; 0) ) } и скопируйте ее в С26:D26. В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26, выберите значок Автосумма). Вы получите блок, показанный на рис. 105.

Задача 5.16.

В блоке A1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение. На новом листе запишите любую последовательность чисел в блок A1:А10.

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

{=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(A1:А10)-1; «возрастающая»; «не является возрастающей»)}

Разбор этой формулы:

А2:А10-А1:А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) — образует блок, состоящий из первых разностей элементов исходного блока;
ЕСЛИ (А2 : А10-А1: А9>0; 1; 0) — составляет блок из индикаторов положительных первых разностей;
СУММ (ЕСЛИ (А2 :А10-А1 :А9>0; 1; 0) ) — считает количество ненулевых элементов в блоке индикаторов;
СЧЕТ (A1: А10)-1 — рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;
и, наконец, если количество ненулевых элементов в блоке индикаторов равно размеру блока индикаторов, то последовательность возрастающая, иначе — нет.

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

Функции ссылок и массивов. — Функции листа. Функции ссылок и массивов — Функции Excel — Статьи об Excel

Функция	Function	id	Подкатегория	Описание
АДРЕС	ADDRESS	60267	—	Возвращает ссылку в виде текста на отдельную ячейку листа
ВПР	VLOOKUP	60150	Извлечения значений из таблиц	Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце
ВЫБОР	CHOOSE	60148	Извлечения значений из таблиц	Выбирает значение из списка значений по индексу
ГИПЕРССЫЛКА	HYPERLINK	60407	—	Создает ссылку, открывающую документ, находящийся на жестком диске, сервере, сети или в Интернете
ГПР	HLOOKUP	60149	Извлечения значений из таблиц	Ищет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке
ДВССЫЛ	INDIRECT	60196	—	Определяет ссылку, заданную текстовым значением
ДРВ	RTD	—	—	Извлекает данные реального времени из программ, поддерживающих автоматизацию COM
ИНДЕКС	INDEX	60077	Извлечения значений из таблиц	По индексу получает значение из ссылки или массива
ОБЛАСТИ	AREAS	60123	Измерения диапазонов	Определяет количество областей в ссылке
ПОИСКПОЗ	MATCH	60112	Извлечения значений из таблиц	Ищет значения в ссылке или массиве
ПРОСМОТР	LOOKUP	60076	Извлечения значений из таблиц	Ищет значения в векторе или массиве
СМЕЩ	OFFSET	60126	—	Определяет смещение ссылки относительно заданной ссылки
СТОЛБЕЦ	COLUMN	60057	Измерения диапазонов	Определяет номер столбца, на который указывает ссылка
СТРОКА	ROW	60056	Измерения диапазонов	Определяет номер строки, определяемой ссылкой
ТРАНСП	TRANSPOSE	60131	—	Выдает транспонированный массив
ЧИСЛСТОЛБ	COLUMNS	60125	Измерения диапазонов	Определяет количество столбцов в массиве или ссылке
ЧСТРОК	ROWS	60124	Измерения диапазонов	Определяет количество строк в ссылке

Функции работы с массивами в MS Excel

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

Рассмотрим часто используемые функции и их назначение.

Функция СТОЛБЕЦ

Описание: Возвращает номер столбца, на который указывает ссылка.

Синтаксис: СТОЛБЕЦ(ссылка)

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

Функция СТРОКА

Описание: Возвращает номер строки, на который указывает ссылка.

Синтаксис: СТРОКА(ссылка)

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

Функция ЧИСЛСТОЛБ

Описание: Возвращает количество столбцов в ссылке или массиве.

Синтаксис: ЧИСЛСТОЛБ (ссылка)

Ссылка – массив или ссылка на диапазон, в котором определяется количество столбцов.

Функция ЧСТРОК

Описание: Возвращает количество строк в ссылке или массиве.

Синтаксис: ЧСТРОК (ссылка)

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

Функция ОБЛАСТИ

Описание: Возвращает количество областей в ссылке. Область — это интервал смежных ячеек или отдельная ячейка.

Синтаксис: ЧИСЛСТОЛБ (ссылка)

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

Пример:

=ОБЛАСТИ(B2:D4) — количество областей в диапазоне (1)

=ОБЛАСТИ((B2:D4;E5;F6:I9)) — количество областей в диапазоне (3)

=ОБЛАСТИ(B2:D4 B2) — количество областей в диапазоне (1)

Функция ИНДЕКС

Описание: Возвращает значение элемента таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.), заданного номером строки и номером столбца.

Синтаксис: ИНДЕКС(массив;номер_строки;номер_столбца)

Массив — диапазон ячеек или массив констант.

Номер_строки — номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Замечания:

— Если массив содержит только одну строку или один столбец, аргумент «номер_строки» (или, соответственно, «номер_столбца») не является обязательным.

— Если массив занимает больше одной строки и больше одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».

Пример:

=ИНДЕКС(A2:B3;2;2) Значение ячейки на пересечении второй строки и второго столбца в диапазоне

=ИНДЕКС(A2:B3;2;1) Значение ячейки на пересечении второй строки и первого столбца в диапазоне.

11) Ms Excel: функции, формулы массива.

Функция – это инструмент, который позволяет:

-упрощать формулы

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

-выполнять вычисления по условию

Функции (аргументы вводятся в круглых скобках через «;»)

В качестве аргументов функций можно использовать: имена =СУММ(Продажи), формулы =КОРЕНЬ(А1^2 + А2^2), другие функции =SIN(РАДИАНЫ(В9)).

Список функций листа (по категориям, Фрагмент 4):

— Инженерные функции

— Логические функции

— Математические и тригонометрические функции

— Пользовательские функции, устанавливаемые с помощью надстроек

— Статистические функции

— Текстовые функции

— Финансовые функции

— Функции даты и времени

— Функции для работы с базами данных

— Функции кубов

— Функции поиска и ссылки

— Функции предыдущих версий

— Функции проверки свойств и значений.

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

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

Ввод формулы массива завершается нажатием клавиши Ctrl+Shift+Enter.

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

Вывод списка формул для работы с массивами (формулы/ссылки и массивы)

Ввод списка формул

ПРИМЕР. Решение систем линейных уравнений (Фрагмент 4)

3х + 6у = 9

2х + 0,54у = 4

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

А * Х = В, где

А — квадратная матрица коэффициентов при неизвестных,

В — вектор свободных членов,

Х — вектор неизвестных.

Тогда решение можно записать как

Х = А-1* В, где

А-1 — обратная матрица коэффициентов.

Следовательно, решение системы линейных уравнений можно выполнить в два шага:

— сначала обратить матрицу коэффициентов,

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

— Среди встроенных функций Excel имеются функции, выполняющие:

— МОБР(«аргумент») — обращение матрицы, заданной «аргументом»,

— МУМНОЖ(«аргумент 1»; «аргумент 2») — перемножение матриц «аргумент 1» на «аргумент 2».

РЕШЕНИЕ

1. Ввести в ячейки рабочей области числа — коэффициенты при неизвестных (область B45:C46) и свободные члены (область E45:E46)

2. Выделить область под обращенную матрицу коэффициентов (область B49:C50)

3. Ввести в выделенную область формулу массива {=МУМНОЖ(B49:C50;E45:E46)}

4. Выделить область под вектор решения (область E49:E50)

5. Ввести в выделенную область формулу массива {=МУМНОЖ(B49:C50;E45:E46)}

12) Ms Excel: форматирование и оформление эт.

Форматирование предназначено для изменения внешнего вида данных на рабочем листе.

Информация о формате записывается в ячейке.

По умолчанию все ячейки рабочей области имеют общий формат:

-шрифт Arial 10

-числовой формат основной

-выравнивание по горизонтали, в зависимости от типа данных.

Основы форматирования ячеек

(главное/формат ячеек)

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

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

1) Общий

2) Числовой, опции: число десятичных разрядов, разделитель тысяч, отрицательные числа

3) Денежный, опции: число десятичных разрядов, знак денежной единицы, отрицательные числа

4) Финансовый, денежные единицы выровнены по вертикали

5) Дата, 12 форматов дат

6) Время, 8 форматов времени

7) Процентный, опции: число десятичных разрядов

8) Дробный, 9 дробных форматов отображения чисел

9) Экспоненциальный, опции: количество десятичных разрядов

10) Текстовый, число как текст

11) Дополнительный, почтовый индекс, номер телефона, табельный номер, индекс

12) (все форматы) позволяет создать пользовательские форматы

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

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

Форматирование применяется к определенной группе ячеек, исключение составляет форматирование с помощью команды (главная/форматировать как таблицу)

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

Настройка формата вызывается по команде (главная/Shift и формат)

Есть возможность проводить условное форматирование ячеек, в зависимости от их содержания (главное/ условное форматирование)

Стили

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

Стиль сохраняется вместе с рабочей книгой.

Художественное оформление работы.

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

Функция ПРОСМОТРX в Excel | Компьютерные курсы Среда 31

Долгожданная функция ПРОСМОТРX (XLOOKUP) стала доступна пользователям Microsoft Excel.

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

Функцию сразу же окрестили новой версией ВПР.

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

Давайте разберемся, в чем суть функции. Начнем с синтаксиса:

ПРОСМОТРX(искомое_значение; просматриваемый_массив;
возвращаемый_массив; [если_ничего_не_найдено];
[режим_сопоставления]; [режим_поиска])

искомое значение — то, что мы хотим найти в массиве данных;
просматриваемый массив — строка или столбец, в которых мы будем искать наше значение. Сразу отличие от ВПР: функция ПРОСМОТРX работает и в вертикальных, и в горизонтальных таблицах;
возвращаемый массив — строка или столбец, из которых мы возьмем результат. Важное отличие в том, что теперь возвращаемый массив (в отличие от ВПР/ГПР) может располагаться слева от просматриваемого массива;
если ничего не найдено — [необязательный элемент функции]. Не обнаружив искомое значение в просматриваемом массиве Excel вернет нам ошибку #Н/Д. Если такой вариант нам не подходит, то вместо стандартной ошибки мы можем вывести что-то свое. Например: «не найдено» или «» — если мы хотим видеть пустую ячейку;
режим сопоставления — [необязательный элемент функции]. По умолчанию функция производит точное сопоставление (в ВПР для этого нам нужно было ставить 0). Теперь можно выбирать один из вариантов:

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

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

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

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

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

Терминология

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

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

Пример 1. Классика жанра — товарный чек

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

выделяем ячейку С7
вводим с клавиатуры =СУММ(
выделяем диапазон B2:B5
вводим знак умножения (звездочка)
выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:
чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Пример 2. Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
вводим функцию транспонирования =ТРАНСП(
в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

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

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

Для редактирования формулы массива необходимо выделить весь диапазон (A10:h21 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

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

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

выделяем диапазон B2:K11
вводим формулу =A2:A11*B1:K1
жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

Формула массива для извлечения непустых ячеек из диапазона
Формула массива для извлечения уникальных ячеек из диапазона
Формула массива для извлечения данных из списка (многоразовый ВПР)

Как узнать, является ли данная формула формулой массива? Что вообще она означает?

На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу «обрабатывает». Подтверждение формулы с помощью сочетания клавиш «Ctrl + Shitf + Enter» – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).

Примеры формул массива и отличие от обычных формул в Excel

Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.

К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к «функциям массива» (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.

Когда формула является формулой массива, а когда обычной?

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

{23;-32;15;7} – это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.

Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ({23;-32;15;7}) – они идентичны:

Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.

Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:

=ЕСЛИ($A$1>0;»больше»;»меньше»)

Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):

{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}

В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:

А так выглядят эти значения в массиве:

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

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

=ИНДЕКС({ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА};2)

Тоже самое что и:

Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив {«больше», «меньше», «больше», «больше»}. Схематически вторую таблицу можно отобразить так:

Так же ее можно прочитать функцией:

=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)

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

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

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

Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».

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

Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения «-32». Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:

Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив {ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА}. Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив {23; 0; 15; 7}. Затем эта таблица передается в качестве аргумента функции =СУММ({23; 0; 15; 7}), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.

Ни одна из описанных выше таблиц, в этом случае, не будет создана. Только одна ячейка диапазона будет проверена (ячейка в той же строке, в которой находится функция). В нашем случае 15>0 значит, как первый аргумент функция ЕСЛИ получит логическое значение ИСТИНА. Затем ВЕСЬ диапазон A1:A4 будет передан функции СУММ, и в результате функция возвращает значение равное 13 (23-32+15+7). Если бы в ячейке вместо значения 15 было число меньше нуля, функция ЕСЛИ в качестве аргумента получила бы значение ЛОЖЬ, и, следовательно, только значение нуля было бы передано функции СУММ. Наша функция СУММ в результате также вернет значение равное нулю.

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

При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?

Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.

Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить «Ctr+Shift+Enter». Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.

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

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

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

Нажмите клавишу F9 (или «Пересчет» в правом верхнем углу меню «Формулы»), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:

— запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом — «;» (точкой с запятой).

Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).

Нажмите кнопку F9, и вы получите массив результатов вычислений, как показано ниже:

То есть, созданный в памяти компьютера массив:

{ИСТИНА:ЛОЖЬ:ИСТИНА:ИСТИНА}

Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):

Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:

То есть, созданный в памяти компьютера массив:

{23

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 для выполнения вычислений и функций, которые не могут быть выполнены с использованием формул, не являющихся массивами.Они применяются к диапазону ячеек и полезны, повышая согласованность и сокращая создание похожих формул с одинаковой функциональностью.

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

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

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

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

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

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

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

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

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

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

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

Формулы массива обеспечивают согласованность, точность и точность вычисляемых формул и значений, применяемых к диапазону ячеек.
Формулы массива сокращают потери времени на непродуктивные операции, применяя формулу непосредственно к диапазону ячеек, и их не нужно применять отдельно.
Формулы массива гарантируют отсутствие избыточности и безопасность для пользователей, поскольку компонент формулы массива с несколькими ячейками не может быть заменен каким-либо другим значением, а формула массива ячеек целиком должна быть выбрана для изменения или безвозвратного удаления. Кроме того, в качестве дополнительной меры предосторожности вы должны одновременно нажать CSE для изменения формулы.
Формулы массива уменьшают размер файлов за счет использования одной формулы, а не различных подобных промежуточных формул.
Формулы массива уменьшают вероятность получения ошибочных результатов при неправильном применении формул.
Например, когда необходимо вычислить сумму рабочих часов для каждого квартала месяцев, к массиву можно применить одну формулу суммы (формула массива с несколькими ячейками) вместо применения 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.

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

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

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

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

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

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

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

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

Вам нужно щелкнуть по ячейке, в которую вы хотите ввести формулу массива.
Начните формулу массива со знака равенства, следуйте стандартному синтаксису формулы и при необходимости используйте математические операторы или встроенные функции в формуле Excel.
Например:
Чтобы рассчитать общее количество рабочих часов всех сотрудников вместе и получить результат в ячейке h23, используется формула массива:
= СУММ (D3: D12, E3: E12, F3: F12, G3: G12)
Нажмите Ctrl + Shift + Enter, чтобы получить желаемый результат.Это сделано для того, чтобы Excel автоматически вставлял фигурные скобки в формулу и распознавал ее как формулу массива.

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

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

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

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

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

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

Вам нужно щелкнуть по диапазону ячеек, в который вы хотите ввести формулу массива.
Начните формулу массива со знака равенства, следуйте стандартному синтаксису формулы и при необходимости используйте математические операторы или встроенные функции в формуле Excel.
Например:
Чтобы рассчитать рабочее время каждого сотрудника в течение года и получить результат в диапазоне ячеек от h4: h22, используется формула массива:
{= D3: D12 + E3: E12 + F3: F12 + G3 : G12}
Нажмите Ctrl + Shift + Enter, чтобы получить желаемый результат. Это сделано для того, чтобы Excel автоматически вставлял фигурные скобки в формулу и распознавал ее как формулу массива.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ИЛИ

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

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

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

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

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

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

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

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

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

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

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

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

Выберите ячейки, в которые необходимо ввести значения.
Введите знак равенства и желаемые значения констант, которые должны содержаться в ячейках. Если значение является строкой, поместите текст в двойные кавычки («).
Разделите значения констант, которые должны содержаться в строках, запятыми и точками с запятой, при этом значения констант должны содержаться в столбцах.
Нажмите 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}
Его также называют двумерным массивом констант, поскольку он состоит как из строк, так и из столбцов.

Что следует помнить

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

формул массива в Excel | Обучение Подключение

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

Рассмотрим пример рабочего листа ниже:

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

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

В этом примере требуется, чтобы вы умножили один столбец значений на другой и сложили все эти результаты вместе. Имея это в виду, введите «= СУММ (B2: B6 * C2: C6)» в строку формул:

Хотя каждый массив обрабатывается как один аргумент в формуле, формула в ее текущей форме приведет к ошибке #VALUE.Это связано с тем, что Excel не распознает массивы в качестве аргументов формулы СУММ.

Исправьте это, щелкнув внутри строки формул и нажав Ctrl + Shift + Enter. Это действие добавит фигурные скобки ({}) к обоим концам формулы:

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

Теперь предположим, что вам нужно рассчитать индивидуальную конечную цену за единицу для каждого продукта, чтобы она включала налог с продаж в размере 5%.Выберите ячейки D2 — D6:

В строке формул введите «= C2: C6 * 1.05» и нажмите Ctrl + Shift + Enter:

Как видите, каждое значение в первом массиве (C2: C6) было вычислено в соответствии с формулой, и результат заносится в соответствующее совпадающее место во втором массиве (D2: D6).

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

Сохраните изменения, внесенные в текущую книгу, и закройте Microsoft Excel 2013.

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

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

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

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

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

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

Массивы

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

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

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

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

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

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

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

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

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

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

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

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

Новые функции Excel: формулы динамических массивов и диапазоны разлива

Итог: Узнайте о новых функциях и формулах динамического массива, которые в конечном итоге заменят формулы массива Ctrl + Shift + Enter.

Уровень квалификации: Начинающий

Видеоуроки

Скачать файл Excel

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

Пример динамических массивов.xlsx (151,5 КБ)

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

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

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

До свидания Ctrl + Shift + Enter

Цель этой новой функции — в конечном итоге заменить формулы массива, которые мы вводим с помощью Ctrl + Shift + Enter (CSE). Не волнуйся, это будет долгое прощание.

Формулы

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

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

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

Давайте посмотрим, насколько это будет проще с новой функцией динамического массива UNIQUE.

УНИКАЛЬНАЯ функция в Excel

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

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

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

Функция UNIQUE также имеет дополнительные необязательные аргументы:

[by_col] — Позволяет сравнивать по строкам или столбцам, когда массив состоит из нескольких столбцов. Значение по умолчанию — False, для сравнения по строкам.
[plays_once] — позволяет возвращать только те значения, которые встречаются в массиве (диапазоне) один раз. Это отличный вариант. Значение по умолчанию — False, чтобы возвращать все уникальные значения.Установите для него значение True, чтобы возвращать значения, которые встречаются только один раз.

Вот справочная страница по функции UNIQUE, чтобы узнать о ней больше.

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

Диапазон разлива

Привыкайте к термину «разлив» для Excel.

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

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

Excel теперь сделает всю эту работу за нас!

Когда выбрана любая ячейка в диапазоне разлива, вокруг диапазона появляется синяя линия. Что произойдет, если что-то блокирует зону разлива?

#SPILL Ошибка

Если в диапазоне разлива уже есть данные, будет возвращена ошибка #SPILL.Это указывает на то, что диапазон, в котором необходимо распределить результаты, не является полностью пустым.

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

Функция сортировки

SORT — еще одна новая и очень полезная функция. Это выводит отсортированный список массива (диапазона), указанного в первом аргументе функции.

SORT имеет дополнительные необязательные аргументы для [sort_index], [sort_order], [by_col]. Вот справочная страница по функции СОРТИРОВКА, чтобы узнать больше.

Обозначение разлива

— Ссылка на разлив

В приведенном выше примере вы заметите, что я использовал C4 # в аргументе массива для функции SORT.

Это называется разливом Ref. Это позволяет нам создать ссылку на весь диапазон разлива, поместив # (хэштег или символ решетки) после адреса первой ячейки в диапазоне разлива.

Есть несколько способов создать разлив ref:

Введите или выберите первую ячейку в диапазоне разлива, чтобы создать ссылку на эту ячейку. Затем введите # после него. Вы увидите, что вокруг диапазона разлива появится ограничивающая рамка.
Другой способ — выделить все ячейки в диапазоне разлива. Ссылка на разлив будет создана автоматически.
Быстрое сочетание клавиш для этого — выбрать первую ячейку в диапазоне разлива, затем нажать Ctrl + Shift + Стрелка вниз , чтобы выделить все ячейки.Это также автоматически создает ссылку на разлив.

Использование для разлива Refs

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

Вы также можете использовать их для обычных формул, если хотите выполнить вычисление (SUM, COUNT и т. Д.) Или поиск (VLOOKUP, INDEX, MATCH) в диапазоне разлива.

Мы даже можем использовать их для именованных диапазонов или проверки данных (пример ниже).Как я упоминал ранее, количество разливов и диапазоны разливов — это термины, которые мы будем часто использовать в будущем с Excel.

Объединение функций динамического массива

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

Отлично подходит для источника списка проверки данных (раскрывающегося списка).

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

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

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

В приведенном ниже примере я использовал формулу SORT (UNIQUE ()) для создания списка уникальных посетителей из столбца B и вывода его в столбец H. Затем я использую столбец H в качестве источника раскрывающегося списка для имени клиента. .

Для этого мы можем просто использовать spill ref для ссылки на диапазон разлива (h5 #).Добавляя хэштег в конец, мы сообщаем Excel, что нам нужен весь диапазон разлива, а не только ячейка h5.

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

Функция фильтра

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

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

Нажмите, чтобы увеличить

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

Формулы динамических массивов

уже скоро!

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

На данный момент есть 7 новых функций динамического массива:

Фильтр — позволяет фильтровать диапазон данных на основе определенных вами критериев.
RandArray — возвращает массив случайных чисел.
Последовательность — позволяет создать список последовательных чисел в массиве, например 1, 2, 3, 4.
Сортировка — сортирует содержимое диапазона или массива.
SortBy — сортировка по значениям в соответствующем диапазоне или массиве.
Уникальный — возвращает список уникальных значений в списке или диапазоне.
Single — возвращает одно значение на пересечении строки или столбца ячейки. Обновление: функция Single была удалена из Excel, и вместо этого теперь используется символ @ для обратной совместимости.

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

Существующие функции могут пролиться

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

Это решение использует функцию ЧАСТОТА. В более старых версиях Excel мы должны вводить эту формулу с помощью Ctrl + Shift + Enter и сначала выбирать диапазон ячеек для вывода. Мне пришлось добавить дополнительные строки, чтобы можно было разместить до 10 ящиков.

Однако с новой функцией разлива эту настройку можно упростить. Мне не нужны все лишние строки с максимальным номером, начинающимся с ячейки C16.Я также мог бы использовать ссылку на разлив для E11 #, чтобы ссылаться на динамический диапазон в именованном диапазоне для источника моей диаграммы. В настоящее время мы не можем использовать spill refs непосредственно в графиках, но я предполагаю, что это будет исправлено.

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

Электронная книга о динамических массивах

Еще один отличный ресурс, чтобы узнать все об этих функциях до их выпуска, — это новая электронная книга Билла Джелен.Он называется Excel Dynamic Arrays: Straight to the Point , и его ~~можно загрузить совершенно бесплатно прямо сейчас в течение ограниченного времени~~ . Промо-период закончился, но это все еще отличный ресурс по очень разумной цене.

Щелкните здесь, чтобы получить электронную книгу

Заключение

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

Тем не менее, скоро появятся

динамических массивов.И я надеюсь, что вы так же взволнованы этим обновлением, как и я.

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

Вы, наверное, слышали, как я сказал: «Всегда есть миллион способов решить одну и ту же проблему в Excel…» Эта новая функция, вероятно, умножает это число еще на миллион, оставляя нам много нового для изучения и изучения в Excel. .🙂

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

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

Как суммировать данные электронной таблицы с помощью формул массива Excel

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

Например, менеджерам может быть интересно узнать, что в прошлом месяце продажи выросли на 10%. Но они были бы очарованы, узнав, что продажи Widgets Пэта Смита в Северо-Западном регионе удвоились в прошлом месяце, в то время как продажи всех остальных людей резко упали.

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

Знакомство с данными

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

Я назвал каждый столбец данных меткой, показанной в заголовке таблицы. Для этого я выбрал всю таблицу, за исключением строки «Итого», затем в группе «Формулы , определенное имя » выбрал Создать из выделенного ; убедился, что Top Row проверен; а потом выбрал ОК .

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

Введение в формулы массива, пример 1

Давайте начнем рассмотрение этих данных с суммирования продаж Джилл и Джо. Ячейки J3 и J4 выполняют эту сводку с помощью формул массива.

Вот формула для показанной ячейки:

J3 : {= СУММ (ЕСЛИ (Продавец = $ I3, Итого, 0))}

Обратите внимание на фигурные скобки, окружающие эту формулу.Вы НЕ вводите эти символы при вводе формулы. Вместо этого вы вводите формулу в массив.

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

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

Для иллюстрации, часть формулы «Продавец = $ I3» создает временный массив, подобный этому: {FALSE; ПРАВДА; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; FALSE} Здесь второй элемент в массиве — ИСТИНА. То есть вторая ячейка диапазона данных «Продавец» — ячейка A3 — содержит текст «Джо».

Формула говорит, что везде, где появляется ИСТИНА, возвращать соответствующее значение из столбца Итого. Следовательно, инструкция…
= IF (Продавец = $ I3, Total, 0)
… возвращает временный массив: {0; 12600; 0; 0; 0; 5060; 0; 1980; 0; 1540; 0; 4500; 0; 0}

Наконец, функция СУММ возвращает сумму этого временного массива: 25 680.

Вторая формула аналогична:

J3 : {= СУММ (ЕСЛИ (Продавец = $ I4, Итого, 0))}

Вы можете скопировать эту формулу из ячейки I3 или ввести ее для практики. Опять же, если вы введете его, НЕ вводите фигурные скобки вручную. Вместо этого, когда вы вводите формулу с помощью Ctrl-Shift Enter, фигурные скобки появятся автоматически.

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

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

Пример 2

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

Эта формула массива немного длиннее:

J9 : {= СУММ (ЕСЛИ ((Продавец = $ I9) * (Prod = 8 J $), Итого, 0))}

Здесь мы умножаем первый массив (Продавец = 19 долларов США) на второй массив (Prod = J8) . Это возвращает массив, содержащий ИСТИНА, только если обе соответствующие ячейки в массиве ИСТИНА.

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

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

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

Таким образом, вы сначала копируете ячейку J9 в ячейку J10, а затем копируете диапазон J9: J10 в диапазон K9: K10.

Формулы в строке 11 и в столбце L представляют собой просто формулы СУММ для столбцов и строк соответственно, как показано здесь:

J11 : = СУММ (J9: J10)
L9 : = СУММ (J9: K9)

Для удобства в следующих примерах я назову диапазон вычислений выше и слева от СУММ рабочим диапазоном .

Пример 3

Мы можем глубже изучить данные, добавив дополнительный аргумент. Здесь мы вводим метку «Головные уборы» в ячейку M14, чтобы отчитываться только о продажах головных уборов.

Формулы массива для этого примера немного длиннее, но следуют тому же шаблону, что и раньше:

J16 : {= SUM (IF ((Продавец = $ I16) * (Регион = 15 J $) * (Prod = 14 $ M $), Итого, 0))}

Скопируйте эту формулу в рабочий диапазон J16: L17.

Пример 4

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

Например, эта формула возвращает наибольшую цену за шляпы, проданные Джо:

J22 : {= MAX (IF ((Продавец = $ I22) * (Prod = 21 J $), Total / Qty, 0))}

Скопируйте эту формулу в рабочий диапазон.

Пример 5

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

Формула:

J28 : {= СУММ (ЕСЛИ ((Продавец = 28 долларов США) * (Прод = 27 долларов США), Кол-во, 0))}

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

Пример 6

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

Вот один из способов сделать это:

P4 : {= СРЕДНЕЕ (ЕСЛИ ((Продавец = $ O4) * (Прод = 3 доллара США), Итого / Кол-во, ””))}

Обратите внимание на пустую строку в последнем аргументе этой формулы. Если бы мы использовали ноль, функция СРЗНАЧ включила бы все эти нули в расчет своего среднего значения, что нам не нужно. Вместо этого, используя пустую строку, мы заставляем функцию AVERAGE игнорировать ячейки, которые не удовлетворяют нашим критериям.В качестве альтернативы мы могли бы использовать FALSE, например:

P4 : {= СРЕДНЕЕ (ЕСЛИ ((Продавец = $ O4) * (Продукт = 3 доллара США), Итого / Кол-во, ЛОЖЬ))}

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

Пример 7

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

Ключевая формула:

P10 : {= СУММ (ЕСЛИ ((Продавец = $ O10) * (Prod = P $ 9), Total, «»))
/ SUM (IF ((Продавец = $ O10) * (Prod = P $ 9) , Кол-во, “”))}

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

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

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

Пример 8

На этом экране отображаются наши результаты. Первая формула просто:

P16 : = J28 * P10

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

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

Пример 9

В примере 2 я сказал, что умножение двух тестов создает отношение И. То есть результат ИСТИНА, только если все умножаемые вами значения также ИСТИННЫ.

Точно так же суммирование двух результатов создает отношение ИЛИ.

Для иллюстрации предположим, что менеджер по продажам думает об объединении Северного и Западного регионов.Поэтому ему нужен отчет, в котором эти регионы рассматриваются как один регион.

Вот первая формула:

P24 : {= СУММ (ЕСЛИ ((Продавец = 24 доллара США) * ((Регион = 22 доллара США) + (Регион = 23 доллара США)), Итого, 0))}

Здесь, если Продавец — Джо, а если Регион — Север или Запад, массив возвращает Итого; в противном случае возвращается 0. Как обычно, функция СУММ возвращает сумму массива.

Обратите внимание на этот кусок в формуле: (Region = P $ 22) + (Region = P $ 23)

Это наша замена функции ИЛИ.Поскольку регион никогда не может быть ОБЕИМ севером и западом, мы получаем ИСТИНА, если Регион равен любому из них; в противном случае получаем ложь.

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

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

Формулы расширения массива

Формулы массива можно удивительным образом расширить. В частности, функции Excel ВЫБОР и СМЕЩЕНИЕ предлагают некоторые варианты, о которых следует помнить.

Для иллюстрации рассмотрим следующую формулу:

D20: {= SUM (IF (Продавец = 20 австралийских долларов, итого, 0))}

Предположим, вы хотите расширить эту формулу. Предположим, что иногда вы хотите вернуть Итого, когда Продавец равен метке в ячейке A20, а в других случаях вы хотите вернуть Итого, когда Регион равен метке в ячейке B20.Вы хотите указать используемый тест, введя значение 1 (для продавца) или 2 (для региона) в ячейку C20.

Чтобы настроить эту сводку, вы можете использовать функцию ВЫБРАТЬ двумя разными способами:

D20 : {= СУММ (ЕСЛИ (ВЫБРАТЬ (20 долларов США, Продавец = 20 долларов США, регион = 20 долларов США), Итого, 0))}

E20 : {= ВЫБРАТЬ (20 долларов США, СУММ (ЕСЛИ (Продавец = 20 австралийских долларов, Итого, 0)),
СУММ (ЕСЛИ (Регион = 20 долларов США, Итого, 0)))}

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

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

Точно так же функция СМЕЩЕНИЕ позволяет вам выбирать разные строки или столбцы для проверки или суммирования. Например:

C21 : {= СУММ (ЕСЛИ (СМЕЩЕНИЕ (Продавец, 0, $ A22) = $ B22, Итого, 0))}

Здесь, если значение в ячейке A22 равно 1, функция СМЕЩЕНИЕ заставляет формулу сравнивать метку в ячейке B22 с первым столбцом справа от продавца или столбцом Prod в примере.Если значение в ячейке A22 равно 2, функция СМЕЩЕНИЕ сравнивает метку со вторым столбцом, который в данном примере является столбцом «Регион».

Обновление

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

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

Формулы массива 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® Certification карьера на новый уровень. Чтобы продолжить изучение и развитие своей базы знаний, ознакомьтесь с дополнительными соответствующими ресурсами ниже:

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