Эксель формула массива: что такое, формула, как сделать / Skillbox Media

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

Пример формулы массива Содержание скрыть 1 Что такое формула массива 2 Матрицы в Excel 2.1 Разница между матрицами и диапазонами в Excel 2.2 Размеры матриц 2.3 Типы матриц 2.4 Способы создания матриц 2.4.1 Матрица констант, созданная вручную 2.4. 2 Массив именованных констант 2.4.3 Использование функций 2.4.4 Использование математических операторов 2.4.5 Использование операторов сравнения 2.4.6 Использование других матриц 2.4.7 Использование комбинаций этих методов 2.5 Просмотр матрицы на листе 3 Взаимодействие функций Excel с массивами 4 Формулы массива — примеры формул

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

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

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

Идем!

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

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

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

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

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

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

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

Массивы в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Из типов элементов они содержат:
По типу данных —

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

Из-за изменчивости элементов —

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

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

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

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

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

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

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

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

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

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

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

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

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

С помощью функций

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

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

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

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

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

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

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

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

С помощью операторов сравнения

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

Массив в Excel с использованием операторов сравнения

С помощью других массивов

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

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

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

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

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

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

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

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

Ctrl + Shift + Enter

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

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

Ошибка ND, когда размер диапазона и массива не совпадает

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

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

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

В разделе математических функций к ним также относятся PRODUCT, SUM.PRODUCT, SUMIF, SUMIF.

Большинство статистических функций по своей природе принимают диапазон или массив в качестве входных данных и возвращают одно число. Самые популярные: AVERAGE, MIN, MAX, COUNT, COUNT, COUNTIF.

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

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

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

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

Как активировать формулы массива в таблицах Excel — схема скобок — Crast.net

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

Каковы преимущества создания формул массива в моих таблицах Excel?

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

Экономьте время на других формулах

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

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

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

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

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

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

ТРАНСПОНИРОВАНИЕ

Функция транспонирования позволяет изменять или вращать ячейки без создания дубликатов. Для этого используйте формулу «=ТРАНСП(A1:B4)», выполнив следующие действия. Сначала выберите несколько пустых ячеек, затем введите «= TRANSPOSE (». Введите диапазон ячеек, которые вы хотите транспонировать, а затем нажмите «ctrl + Shift + Enter».

TREND

Функция тренда возвращает линейные значения. Это подходит прямая по методу наименьших квадратов. Эта функция возвращает значения вдоль этой строки для указанного вами массива «new_x». Чтобы использовать его, введите «= TREND (». Выберите все известные значения «y» и нажмите F4. Разделите аргументы запятой. Выберите известные значения «x» и нажмите F4. Выберите первое значение «x». и нажмите «ввод»

ЧАСТОТЫ

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

Какую процедуру следует выполнить, чтобы ввести формулу массива в таблицы Excel?

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

Из Excel на сотовом телефоне

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

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

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

Как рассчитать части формулы массива в таблицах Excel?

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

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

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

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

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

=ЕСЛИ(A1=1,1,0)
=ЕСЛИ(A1:A100=1,1,0)

Ctrl + Shift + Enter Version (Для более старых версий Excel):

{=ЕСЛИ( A1:A100=1,1,0)}

В этой статье показано, как использовать формулы массива в Excel, но нажмите здесь, если вы хотите узнать, как использовать массивы / ФОРМУЛА МАССИВА в Google Таблицах.

Динамические массивы и формулы CSE (Ctrl + Shift + Enter)

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

Пример формулы динамического массива выглядит так: =A1:A100

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

Пример формулы СПП выглядит следующим образом: {=A1:A100 }

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

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

См. Колонку

• = A2: A1001
• {= A2: A1001}

. См. :V1

{=B1:V1}

Sum / Multiply columns

• =A3:A1001+B3:B1001
• {=A3:A1001+B3:B1001}
• =A3:A1001*B3:B1001
• {=A3:A1001*B3:B1001}

Функция ЕСЛИ с массивом

• =ЕСЛИ(B3:B1001>0,6;»Успешно»,»Неудачно»)
• {=ЕСЛИ(B3:B1001>0,6,»Успешно»,»Неудачно» «)}

Combine first and last name

• =B3:B1001&», «&A3:A1001
• {=B3:B1001&», «&A3:A1001}

Sum entire tables of данные

• =A3:B1001+D3:E1001+G3:h2001
• {=A3:B1001+D3:E1001+G3:h2001}

Извлечение данных с другого листа

• =List!A3:B1001
• {=List!A3:B1001}

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

Обращение к столбцу с помощью формулы массива

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

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

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

Задача: Показать список имен в другом столбце

Логика: В столбце C обратиться к списку имен, которые находятся в столбце A с формулой массива

Формула: Формула ниже, вводится в синюю ячейку (C2), для этого примера

=A2:A1001
{=A2:A1001}

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

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

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

Задача: Показать список дат в другой строке

Логика: В строке 3 обратиться к списку дат, которые находятся в строке 1 с формулой массива

Формула: Формула ниже, вводится в синюю ячейку (B3), для этого примера

=B1:V1
{=B1:V1}

Автор SpreadsheetClass.com

Как суммировать и умножать столбцы в Excel

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

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

В этом примере показано, как использовать формулу массива для расширения формул сложения и умножения.

Столбцы A и B представляют собой списки чисел… а столбцы D и E добавляют/умножают эти числа, расширяя формулы сложения и умножения вниз по столбцу с помощью массива.

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

Задача: Применить формулы сложения и умножения ко всему столбцу

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

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

=A3:A1001+B3:B1001 (введено в ячейку D3)
{=A3:A1001+B3:B1001}

=A3:A1001*B3:B1001 E) введено в ячейку3 (En

6 E)

{=A3:A1001*B3:B1001}

Использование массива для расширения функции ЕСЛИ

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

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

На приведенном ниже примере в столбцах A и B находится список имен учащихся и их оценок. Формула в столбце C использует функцию ЕСЛИ для отображения «Успешно», если оценка учащегося выше 60% (0,6), и отображает «Неудовлетворительно», если оценка не выше 60%. Но самое важное, что следует отметить в этом примере, это то, что функция ЕСЛИ была применена к столбцу с помощью формулы массива.

Задача: Применить формулу ЕСЛИ ко всему столбцу

Логика: Превратить формулу ЕСЛИ в формулу массива и указать весь столбец как диапазон

Формула: Формула ниже, вводится в синюю ячейку (C3), для этого примера

=ЕСЛИ(B3:B1001>0,6,»Удачно»,»Неудачно»)
{=ЕСЛИ(B3:B1001>0,6,»Удачно»,»Неудачно») «)}

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

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

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

На приведенном ниже примере в столбце A указаны имена, а в столбце B — фамилии.

Используя функцию формулы массива с оператором «&», эти имена объединяются в столбец D.

Задача: Объединить имя и фамилию в формате «Фамилия, Имя»

Логика: Объединить столбцы A и B по горизонтали с запятой и пробелом между ними, используя формулу массива и оператор «&»

Формула: Формула ниже вводится в синюю ячейку (D3), для этот пример

=B3:B1001&», «&A3:A1001
{=B3:B1001&», «&A3:A1001}

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

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

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

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

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

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

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

Формула: Приведенная ниже формула вводится в синюю ячейку (J3). :E1001+G3:h2001}

Этот контент был первоначально создан и написан SpreadsheetClass.com

Извлечение данных с другого листа с помощью формулы массива

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

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

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

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

Помните, что если в имени вкладки есть пробел, вы должны ввести апострофы до и после имени вкладки (пример: «Имя вкладки»!A3:B). Однако в этом примере имя вкладки, которое мы будем использовать, называется «Список», поэтому нам не нужны апострофы.

Задача: Отобразить данные, находящиеся в столбцах A и B на вкладке с именем «Список», в столбцах A и B на новой/другой вкладке

Логика: В новой вкладке, отображать данные из столбцов A и B из вкладки «Список», обращаясь к данным массивом

Формула: Приведенная ниже формула вводится в синюю ячейку (A3), для этого примера

=Список!A3:B1001
{=Список!A3:B1001}

6 Источник данные из вкладки «Список»:

Отдельная вкладка, отображающая ссылочные данные/массив:

Вспышка: проверьте свои знания

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

Нажмите зеленую кнопку «Печать» ниже, чтобы напечатать всю эту статью.

Вопрос №1

Какая из следующих формул относится к столбцу данных?

1. =A12:Z12
2. =G1:G1000
3. =B1

Вопрос № 2

Какая из следующих формул относится к ряду данных?

1. =J3
2. =U1:U1000
3. =A9:Z9

Вопрос № 3

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

=ЕСЛИ(D1=100,»Идеально»,»Не идеально»)

1. Верно
2. Ложно

Вопрос #4

Какая из следующих формул относится к другому диапазону формул?

1. =A1:Z1000
2. =Значения!A3:B1000

Вопрос №5

Какие из следующих формул относятся к диапазону с несколькими столбцами? (Выберите все подходящие варианты)

1. =Значения!A:B
2. =A:A
3. =C:B
4. =List!G:G
5. =Y:Z

Вопрос № 6

Какая из следующих формул будет применять формулу умножения ко всему столбцу?

1. =A3:AxB3:B
2. =A3:A*B3:B
3. =A3:A+B3:B
4. =A3:A(B3:B)

Вопрос #7

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

1. True
2. False

Answers to the questions above:

Question 1: 2

Question 2: 3

Question 3: 1

Question 4: 2

Вопрос 5: 1, 3, 5

Вопрос 6: 2

Вопрос 7: 1

Excel MEDIAN Array Formula

A MEDIAN Array Formula число средних значений массива в Excel которые соответствуют определенным критериям. Формула массива выполняет операцию над несколькими значениями вместо одного значения. В этой формуле массива мы по существу фильтруем набор данных с помощью формулы, чтобы найти медиану только тех значений, которые соответствуют нашим условиям. Эту формулу можно использовать для заполнения Калькулятора чистых цен Совета колледжей.

Основы

Синтаксис и аргументы формулы МЕДИАНА ЕСЛИ следующие:

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

Назначение каждой части формулы: 9004 функция находит среднее значение из набора чисел

Функция ЕСЛИ позволяет нам установить условия для значений, которые мы хотим проверить.

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

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

Пример

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

Метод

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

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

Figure 1. Dataset Worksheet (only a portion of the dataset is shown)

Figure 2. Summary Worksheet — Net Price Calculator Grid

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

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

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

из следующего столбца:

• Набор данных!

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

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

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

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