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

Формула массива в Excel | Эффеком ✅

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

Формула массива: что это и для чего нужно

Массивы данных представляют собой генеральную совокупность величин, которые были объединены в цельную группу.

Формула суммы массива присутствует и в Эксел, и в Гугл Таблицах. Это формулы -инструменты для работы с составляющими массива.

Использование этой функции в обоих редакторах преследует следующие цели:

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

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

Типы и состав формул в Экселе

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

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

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

Типы и состав формул в Гугл Таблицах

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

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

В Гугл Таблице пользователь также может задать наименование табличному диапазону. Это упрощает вычислительные операции. Функции в сервисе от Гугл аналогичны Экселю.

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

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

1. Первая категория – формулы, исполняющие множество вычислений ради одного ответа.
2. Вторая категория – формулы, исполняющие множество вычислений.

В первом случае ввод формулы осуществляется по следующему алгоритму:

1. Выбирается табличная ячейка, в которую надо ввести формулу.
2. Вводится соответствующая формула. В случае массивов синтаксис точно такой же, как и при стандартных функциях. В начале текста всегда проставляется символ «Равно».
3. Нажимается «Ввод» (клавиша на клавиатуре – «Enter»).

Во втором случае ввод производится по похожей схеме:

1. Пользователь выбирает диапазон табличных ячеек, куда будет вводиться формула.
2. Вводится синтаксис. В самом начале также проставляется равенство.
3. Нажимается «Ввод».

В некоторых версиях Экселя для активации формулы нужно зажать другую комбинацию кнопок на клавиатуре – «Ctrl», «Enter», «Shift».

Формула массива в Гугл таблицах

ArrayFormula в Гугл Sheets – это прямой аналог рассматриваемой формулы массива в Эксел. Формула вычисления массива в облачных Гугл Таблицах, по аналогии с Экселем, начинается с символа «Равно».

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

Формула массива может иметь два разных формата. Так, формула, отраженная в формате «1; 2», считается «вертикальной». Единица – элемент, расположенный сверху, двойка – элемент нижний. Формат «1/2» — «горизонтальный», т. е. обе цифры – это правый и левый элемент соответственно.

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

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

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

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

Формулы массива в Excel — ОфисГуру

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 30.

05.2016

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

Содержание

1. Без формулы массива
2. С формулой массива
3. Клавиша F9

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

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

1. Первым делом мы рассчитаем развитие для каждого студента.
2. Затем при помощи функции МАКС (MAX) мы найдём наибольшее значение в столбце Progress.

С формулой массива

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

1. Мы уже знаем, что определить прогресс первого студента нам поможет формула, показанная на рисунке ниже.
2. Чтобы из значений прогресса всех студентов найти максимальное, мы добавляем сюда функцию МАКС (MAX) и заменяем C2 на C2:C6, а B2 на B2:B6.
3. Завершаем редактирование формулы нажатием Ctrl+Shift+Enter.

Замечание: Формула массива в строке формул заключена в фигурные скобки {}. Не пытайтесь ввести их вручную, скобки исчезнут при попытке отредактировать формулу.

Пояснение:

• Диапазон данных (массив констант) хранится в памяти Excel, не в диапазоне ячеек. Массив констант можно представить в таком виде: {19;33;63;48;13}.
• Функция
  МАКС (MAX) использует этот массив как аргумент и возвращает результат 63.

Клавиша F9

При работе с формулами массива можно использовать режим просмотра содержимого массива констант.

1. В формуле выделите C2:C6-B2:B6, как показано на рисунке ниже.
2. Нажмите F9.

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/functions/array-formulas.html
Перевел: Антон Андронов
Правила перепечатки

Еще больше уроков по Microsoft Excel

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

Adblock
detector

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

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

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

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

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

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

Более ранние версии Excel требовали от пользователей нажатия

Ctrl + Shift + Enter для создания функции массива, что приводило к названию функций CSE (Ctrl, Shift, Escape), хотя это больше не относится к Excel 365.

Примером может быть функция для получения суммы всех продаж свыше 100 долларов США в заданный день. Или определить длину (в цифрах) пяти разных чисел, хранящихся в пяти ячейках.

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

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

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

Как определить и создать массив в Excel

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

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

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

.

 =B4:B8 * D4:D8 

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

Если мы предположим, что фиксированная стоимость доставки составляет 1,50 доллара США за фунт, мы можем рассчитать нашу колонку доставки с помощью аналогичной формулы, помещенной в ячейку F4:

 =C4:C8 * B11 

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

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

 =E4:E8 + F4:F8 

И снова простая формула массива превратила пять формул в одну.

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

Управление условиями в массивах Excel

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

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

Для новых тарифов на доставку стандартная доставка будет стоить 1,00 доллара США за фунт

, а доставка большего веса будет 1,75 доллара США за фунт . Транспортировка большого веса распространяется на все, что превышает семь фунтов .

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

 =ЕСЛИ(C4:C8 < 7, C4:C8*B11, C4:C8*B12) 

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

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

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

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

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

Пример: Усреднение N-х чисел

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

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

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

 =СРЗНАЧ(ЕСЛИ(МОДА(СТРОКА(B2:B16)-2,D19)=0,B2:B16,"")) 

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

Освоение формул массива в Excel имеет важное значение

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

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

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

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

Формула массива — это формула, которая может выполнять вычисления для нескольких значений одновременно. Формулы массива часто используются для таких функций Excel, как фильтрация и условное форматирование. Чтобы использовать формулу массива, вы должны сначала выбрать диапазон ячеек, которые вы хотите вычислить. Затем введите формулу в первую ячейку диапазона и нажмите CTRL + SHIFT + ENTER. Это сообщит Excel, что ваша формула является формулой массива, и применит формулу ко всем ячейкам в диапазоне.

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

Преимущества использования формул массива в Excel

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

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

Советы по устранению неполадок с формулами массива в Excel

Если у вас возникли проблемы с работой формулы массива, вы можете попробовать несколько способов. Во-первых, убедитесь, что вы выбрали правильный диапазон ячеек. Формулы массива работают только с диапазонами ячеек, а не с отдельными ячейками. Во-вторых, проверьте, заключена ли ваша формула в фигурные скобки {}. Если это не так, нажмите CTRL + SHIFT + ENTER, чтобы добавить их. Наконец, убедитесь, что все ячейки в диапазоне содержат данные, которые можно использовать в расчетах. Например, все ячейки в диапазоне должны содержать числовые данные, если вы используете функцию СУММ.

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

Формулы массива также могут сделать ваши электронные таблицы Excel более надежными и устойчивыми к ошибкам.

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

1. Выберите диапазон ячеек, который вы хотите рассчитать.

2. Введите формулу в первую ячейку диапазона.

3. Нажмите CTRL+SHIFT+ВВОД. Excel добавит фигурные скобки {} в формулу, и она будет применена ко всем ячейкам в диапазоне.

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

Пример 1. Основные математические операции

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

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

1. Выберите диапазон ячеек, который вы хотите добавить.

2. Введите в первую ячейку следующую формулу: =СУММ(A1:A5)

3. Нажмите CTRL + SHIFT + ENTER. Excel добавит фигурные скобки {} в формулу, и она будет применена ко всем ячейкам в диапазоне.

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

Пример 2: Статистические функции

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

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

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

1. Нахождение суммы диапазона ячеек: =СУММ(A1:A10)
2. Нахождение среднего диапазона ячеек: =СРЗНАЧ(A1:A10)
3. Подсчет количества ячеек в диапазоне, соответствующих определенному критерию: =СЧЁТЕСЛИ(A1:A10”>=5″)
4. Проверка данных в диапазоне ячеек: =ISNUMBER(A1:A10)

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

Советы по эффективному использованию формул массива

При использовании формул массива необходимо помнить о нескольких вещах:

1. Убедитесь, что формула массива введена в ячейку правильно.