В этом уроке приведены практические примеры использования формул массива в Excel. Это самый минимум из того, что с их помощью можно сделать. Надеюсь, что большая часть этих примеров, обязательно пригодится для решения Ваших повседневных задач.
Подсчет количества знаков в диапазоне ячеек
На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.
В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функция СУММ — суммирует эти значения.
Наибольшие и наименьшие значения диапазона в Excel
Следующая формула возвращает 3 наибольших значения диапазона A1:D6
Чтобы возвратить другое количество наибольших значений, достаточно изменить массив констант. Например, следующая формула возвращает уже 6 наибольших значений того же диапазона:
Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.
Формула массива, представленная на рисунке ниже, позволяет подсчитать количество различий в двух диапазонах:
Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны — единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функция СУММ суммирует значения данного массива и возвращает результат.
Необходимо, чтобы оба сравниваемых диапазона имели одинаковый размер и ориентацию.
Вспомним предыдущий пример и попробуем усложнить задачу. К примеру, требуется сравнить диапазоны в Excel, которые имеют одинаковый размер, но разную ориентацию — один горизонтальный, а другой вертикальный. В этом случае на помощь придет функция ТРАНСП, которая позволяет транспонировать массив. Теперь формула из прошлого примера, немножко усложнится:
Транспонировать массив в Excel — значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.
На рисунке ниже представлены товары, цена которых указана в евро, а также их количество и итоговая стоимость в рублях. В ячейке D9 отображается общая сумма всего заказа.
Если изменить форматирование в диапазоне D4:D8, то становится видно, что значения в этих ячейках не округлены, а всего лишь визуально отформатированы. Соответственно, мы не можем быть уверенны в том, что сумма в ячейке D9 является точной.
В Excel существует, как минимум, два способа исправить эту погрешность.
- Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:
- Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.
Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности.
Как видите, сумма до и после округления немного отличается. В нашем случае это всего лишь одна копейка.
На рисунке ниже представлен фрагмент таблицы продаж, в которой 1231 позиция. Наша задача посчитать максимальную продажу, которую осуществил заданный продавец.
Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:
В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет — ЛОЖЬ. В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция. Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:
Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.
Данная формула позволяет вывести 5 наибольших продаж указанного продавца:
Итак, в данном уроке мы рассмотрели несколько интересных примеров применения формул массива в Excel. Надеюсь, что они были для Вас полезны и обязательно пригодятся в будущем. Если желаете получить еще больше информации о массивах, читайте следующие статьи:
- Знакомство с формулами массива в Excel
- Многоячеечные формулы массива в Excel
- Одноячеечные формулы массива в Excel
- Массивы констант в Excel
- Редактирование формул массива в Excel
- Подходы к редактированию формул массива в Excel
Урок подготовлен для Вас командой сайта office-guru.ru Автор: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel
