Как пользоваться формулами в excel - Учим Эксель

Как в кабинете

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

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

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

В Excel массив — это спектр ячеек. Ниже приведены несколько примеров. Массивы в Excel бывают двухмерные и одномерные. Одномерные в свою очередь делятся на горизонтальные и вертикальные.

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

Когда мы говорим о формулах массива, мы подразумеваем, что это обычная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ. ), но незначительно модифицированная, чтоб принять в качестве входных данных массив либо набор массивов. Это то, что лежит в базе формул массива и делает его настолько массивным.

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

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

формула массива минимум

Результатом в этом случае будет адресок ячейки с минимальным значением в данном спектре. Как вы сможете узреть, при обновлении данных в спектре, итог тоже изменяется. Подобного эффекта можно также достигнуть при помощи обыкновенной формулы =АДРЕС(ПОИСКПОЗ(МИН(A1:A9);A1:A9;0);1). Но ж мы лишь начали исследование, в предстоящем вы обнаружите, что некие вещи можно созодать лишь при помощи формулы массива, или с огромным количеством обыденных формул.

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

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

Интересно почитать:  Средняя взвешенная формула excel

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

(До этого чем мы пойдем далее, удостоверьтесь, что при вводе формул массива, вы вводите Ctrl + Shift + Enter, а не обыденный Enter, как при обыденных формулах).

Массив констант в формулах массивов

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

Горизонтальный массив констант

Горизонтальный массив констант вводиться как последовательность чисел, разбитых точкой с запятой (;), заключенных в фигурные скобки. К примеру: <1;2;3;4;5>. Горизонтальные массивы могут быть применены в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

горизонтальный массив

Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения делятся двоеточием (:) и также заключаются в фигурные скобки. К примеру: <1:2:3:4:5>.

вертикальный массив

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

Оператор массива докладывает формуле, какую операцию нужно совершить над массивами, предоставленными в качестве массива. К тому же, вы сможете применять операторы И (другой вариант написания — *) и ИЛИ другой вариант написания — +).

Оператор массива И

Оператор И возвращает значение ИСТИНА в вариантах, когда все условия выражения возвращают значение ИСТИНА. Пример ниже указывает внедрение оператора массива И (*) меж массивами:

формулы массивов в excel

Оператор массива ИЛИ

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из критерий выражения возвращает значение ИСТИНА. Пример ниже указывает внедрение оператора массива ИЛИ (+) меж массивами:

формулы массивов в excel

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

Спектр массива вводиться буквально также, как и рядовая формула (к примеру, A1:A10). Их не непременно сходу же заключать в скобки (К примеру, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) либо (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сходу установить скобки в формулах.

Интересно почитать:  Excel формулы в сводных таблицах

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

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

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

Скажем, у вас есть набор данных в ячейках D2:D10 и вы желаете отсортировать их в порядке возрастания. Вы уже наверняка додумались, что нам пригодиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое меньшее значение и данного спектра. Нам также пригодиться спектр, где мы будем создавать вычисления.

Рядовая функция НАИМЕНЬШИЙ для одной ячейки смотрится последующим образом =НАИМЕНЬШИЙ(D2:D10;1). Таковая формула возвратит нам меньшее значение спектра D2:D10. Но нам нужно скопировать эту функцию во все другие ячейки и внести конфигурации во 2-ой аргумент, чтоб получить отсортированный перечень. Для начала выделим спектр, в котором мы желаем узреть перечень, потом вводим формулу в первую ячейку и нажимаем Ctrl + Shift + Enter. Формула будет скопирована на весь спектр, результатом станет отсортированный перечень.

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

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

Представим, мы желаем узнать имя менеджера с большими продажами. Вот где находится настоящая мощь формул массивов. Ели бы мы употребляли обыденные формулы, нам пригодилось бы столько же строк, сколько менеджеров, если не больше. Но мы можем создать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем тут — это сравниваем сумму продаж определенного менеджера с суммой продаж наибольшего менеджера. Если условие поистине, возвращает номер строчки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с большим показателем продаж, в неприятном случае ворачивается пустота. При помощи функции МАКС мы находим строчку, где происходит крайнее вхождение имени, а потом при помощи функции СМЕЩ возвращаем имя из данной нам строчки.

Интересно почитать:  Excel формула получить данные сводной таблицы

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

Объединение данных по наиболее чем одному условию

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

макс формула массива

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

Сейчас попытайтесь сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книжки 2)Реализации >= 500. Задумайтесь минуту.

Мы можем достигнуть этого при помощи формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книжки»))+((D2:D10>=500));D2:D10;0)). Так что все-таки мы тут сделали? Мы проверили три условия — 1-ые два были скомбинированы при помощи оператора И (*) и третье было добавлено при помощи оператора ИЛИ (+). В итоге вышла структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из 4-ого столбца, когда оно воспринимает значение ИСТИНА и 0, если ЛОЖЬ. Итог, естественно, представляет собой сумму продаж отвечающим всем трем, обозначенным выше, условиям.

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

Ссылка на основную публикацию
Adblock
detector