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

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

Подсчет количества знаков в диапазоне ячеек

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

В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функция СУММ – суммирует эти значения.

Наибольшие и наименьшие значения диапазона в Excel

Следующая формула возвращает 3 наибольших значения диапазона A1:D6

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

Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.

Подсчет количества отличий двух диапазонов в Excel

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

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

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

Транспонирование массива в Excel

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

Транспонировать массив в Excel – значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.

Суммирование округленных значений в Excel

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

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

В Excel существует, как минимум, два способа исправить эту погрешность.

1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:
2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.

Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности.

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

Наибольшее или наименьшее значение по условию

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

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

В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет – ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:

Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.

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

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

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

• Знакомство с формулами массива в Excel
• Многоячеечные формулы массива в Excel
• Одноячеечные формулы массива в Excel
• Массивы констант в Excel
• Редактирование формул массива в Excel
• Подходы к редактированию формул массива в Excel