Почувствовать настоящие возможности формул массива можно при использовании формул массива, возвращающих одно значение. Такие примеры рассматриваются в настоящем разделе.
Подсчет количества символов в диапазоне
Предположим, что существует диапазон ячеек, содержащих текст.
При традиционном подсчете количества символов в ячейках вначале создайте формулу, подобную приведенной ниже.
=ДЛСТР(А1)
Далее скопируйте ее вниз для вычисления количества символов в каждой ячейке. Затем, используя формулу СУММ, вычислите сумму промежуточных значений.
Формула массива выполняет всю работу сразу, без использования промежуточных формул.
{=СУММ(ДЛСТР(А1:А14))}
В этой формуле функция ДЛСТР используется для создания массива (в памяти), который содержит количество знаков в каждой ячейке диапазона. В нашем случае новый массив имеет следующий вид:
{11;12;11;8;11;16;12;10;9;10;9;14;13;11}
Формулу массива теперь можно представить проще:
=СУММ({11;12;11;8;11;16;12;10;9;10;9;14;13;11})
Сложение трех наименьших элементов диапазона
Представленная ниже формула возвращает сумму трех наименьших значений в диапазоне с именем Числа.
{=СУММ(НАИМЕНЬШИЙ(Числа;{1;2;3}))}
В этой формуле используется массив констант в качестве второго аргумента функции НАИМЕНЬШИЙ. Функция генерирует новый массив, который состоит из трех наименьших значений диапазона. Затем массив обрабатывается функцией СУММ, которая возвращает сумму значений массива.
На рисунке ниже показан пример массива Числа, находящийся в диапазоне А1:А10. Функция НАИМЕНЬШИЙ выполняется три раза, каждый раз с новым вторым аргументом. В первый раз этот аргумент имеет значение 1, и функция возвращает -5. Во второй раз аргументом является 2, и функция возвращает нуль (второе по величине значение в диапазоне). В третий раз аргументом является 3, и функция возвращает двойку.
Функция СУММ получает следующий массив:
{-5;0;2}
Формула возвращает сумму элементов этого массива (-3).
Подсчет количества текстовых ячеек в диапазоне
На первый взгляд, для решения этой задачи лучше всего подходит функция СЧЁТЕСЛИ, но это не так. Функция СЧЁТЕСЛИ проверяет не тип значения, а условие вхождения в заданный интервал, поэтому для данной задачи она неприменима.
Функция ЕСЛИ используется в примере этого раздела для того, чтобы проверить, является ли содержимое ячейки текстом. В результате формируется новый массив, состоящий из нулей и единиц в зависимости от того, является ячейка текстовой или нет. Следует отметить, что новый массив имеет тот же размер и ориентацию, что и исходный. Затем новый массив передается функции СУММ, которая возвращает сумму элементов в массиве. Таким образом, формула предоставляет результат – количество текстовых ячеек в диапазоне.
{=СУММ(ЕСЛИ(ЕТЕКСТ(А1:D5);1;0))}
Результат использования описанной выше формулы вводится в ячейку С7, а массив, созданный функцией ЕСЛИ, имеет следующий вид:
{0;1;1;1:1;0;0;0:1;0;0;0:1;0;0;0:1;0;0;0}
Обратите внимание, что этот массив содержит пять строк из трех элементов (те же размерности, что и в диапазоне).
К такому же результату приведет использование следующей формулы:
{=СУММ(ЕТЕКСТ(A1:D5)*1)}
В этой формуле не используется функция ЕСЛИ; вместо этого используется тот факт, что
ИСТИНА*1=1
И
ЛОЖЬ*1=0
Значения ИСТИНА и ЛОЖЬ в формулах массивов
Когда массив возвращает булево значение (ИСТИНА или ЛОЖЬ), вы должны преобразовать его в число. Функция СУММ игнорирует булевы значения, но с ними можно выполнять обычные математические операции. В Excel эквивалентом значения ИСТИНА является 1, а эквивалентом значения ЛОЖЬ – 0. Преобразование булевых значений в числа гарантирует правильную их трактовку функцией СУММ.
Для преобразования булевых значений в числа без изменений самих этих значений можно использовать следующие три математические операции:
• умножение на единицу: (х*1 = х);
• добавление нуля: (х+0 = х);
• применение двойного знака “минус”: (—х = х).
Применение любого из этих операторов к булеву значению “приказывает” программе преобразовать его в число. Все следующие формулы вернут один и тот же результат.
(=СУММ(ЕТЕКСТ(A1:D5)*1)}
(=СУММ(ЕТЕКСТ(A1:D5)+0)}
(=СУММ(—ЕТЕКСТ(A1:D5))}
Не существует идеального способа преобразования булева значения в число, поэтому выбирайте метод, который больше подходит в данной ситуации. В то же время помните обо всех трех методах, поскольку эти конструкции вы можете встретить на рабочих листах других пользователей.
Устранение промежуточных формул
Одним из главных достоинств использования формул массива является возможность устранения в электронной таблице промежуточных формул. Это делает рабочие таблицы менее громоздкими и исключает необходимость отображения несущественных вычислений. На рисунке показан рабочий лист с баллами, набранными на первом и втором этапах тестирования. Столбец D содержит разницу между баллами в столбцах В и С. Ячейка D18 отображает формулу, вычисляющую среднее значение в столбце D.
=СРЗНАЧ(D2:D15)
Использование формулы массива позволяет исключить промежуточные вычисления. Ниже представлена формула, вычисляющая среднюю разницу баллов без использования значений в столбце D.
{=СРЗНАЧ(С2:С15-В2:В15)}
В этой формуле используются два массива, элементы которых хранятся в двух диапазонах (В2:В15 и С2:С15). Формула создает новый массив, состоящий из разности соответствующих элементов исходных массивов. Новый массив хранится только в памяти Excel, а не в ячейках электронной таблицы. Функция СРЗНАЧ получает этот новый массив в качестве аргумента. Новый массив состоит из следующих элементов:
{11;15;-6;1;19;2;0;7;15;1;8;23;21;-11}
Следовательно, в развернутом виде формула принимает следующий вид:
=СРЗНАЧ({11;15;-6;1;19;2;0;7;15;1;8;23;21;-11})
На основе данных этого примера можно вычислить и другие значения, используя формулы массива. Например, следующая формула возвращает наибольшую разницу (характеризующую наиболее значительное улучшение успеваемости). Эта разница равна 23 и соответствует результатам тестирования студента Сергей.
{=МАКС(С2:С15-В2:В15)}
Приведенная ниже формула возвращает наименьшую разницу (характеризующую ухудшение успеваемости). Она составляет -11 и соответствует результатам тестирования студента Альфред.
{=МИН(С2:С15-В2:В15)}
Использование массива вместо диапазона ссылок
Некоторые функции в качестве аргумента используют диапазон ссылок. Если в формуле используется такая функция, можно заменить диапазон ссылок на массив констант (особенно в том случае, если значения в диапазоне ссылок не изменяются).
Исключение составляют функции работы с базами данных (например, БДСУММ). Эти функции в качестве обязательного аргумента получают критерий, находящийся в диапазоне ячеек. К сожалению, использовать массив констант вместо ссылки на диапазон условий нельзя.
На рисунке показан рабочий лист, в котором используется таблица преобразования для отображения слова, соответствующего числу. Например, числу 9 в таблице преобразования (диапазон D1:E10) соответствует значение Девять. Формула, возвращающая это значение, находится в ячейке С1.
=ВПР(В1;Dl:Е10;2;ЛОЖЬ)
Вместо таблицы преобразований часто применяют двухмерный массив. Приведенная ниже формула возвращает тот же результат, что и предыдущая, но не требует наличия таблицы преобразований:
=ВПР(В1;{1;“Один”:2;“Два”:3;“Три”:4;“Четыре”:5;“Пять”:6;“Шесть”:7;“Семь”:8;“Восемь”:
9;“Девять”:10;“Десять”};2;ЛОЖЬ)