Формулу массива можно вводить в одну ячейку (а не в диапазон ячеек). Такие формулы обрабатывают массивы, хранящиеся либо в диапазоне ячеек, либо в памяти Excel. В этом разделе представлены некоторые дополнительные примеры одноячеечных формул массива.
Суммирование в диапазоне, содержащем ошибки
Многие пользователи Excel знают, что функция СУММ не работает, если просуммировать диапазон ячеек, содержащих сообщения об ошибках (например, #ДЕЛ/0! или #Н/Д). Рисунок иллюстрирует такой случай. Формула СУММ в ячейке C11 возвращает сообщение об ошибке, так как в диапазоне ячеек С4:C10, которые она складывает, содержатся сообщения об ошибках.
Следующая формула массива возвращает сумму значений, даже если входной диапазон содержит сообщения об ошибке.
{=СУММ(ЕСЛИОШИБКА(С4:С10;“”))}
Эта формула создает новый массив, включающий значения диапазона, но без сообщений об ошибке. Функция ЕСЛИ заменяет сообщения об ошибке на пустые значения. Затем функция СУММ складывает элементы “отфильтрованного” таким образом массива. Аналогичную методику можно использовать и для других функций, например МИН и МАКС.
В данном примере функция СУММ обрабатывает следующий внутренний массив, хранящийся памяти:
{8;20;12;“”;“”;5;10}
Возникают случаи, когда вместо ЕСЛИОШИБКА необходимо использовать другие функции. Дело в том, что ЕСЛИОШИБКА возвращает значение ИСТИНА при любых значениях ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!). Функция ЕОШ возвращает значение ИСТИНА при любых значениях ошибки, кроме #Н/Д, а функция ЕНД – только при значениях ошибки #Н/Д.
Новинка Excel 2010
Функция АГРЕГАТ предоставляет еще один способ суммирования диапазона, содержащего ошибочные значения:
=АГРЕГАТ(9,2,С4:C10)
Первый аргумент содержит значение 9 – код функции СУММ. Второй аргумент приказывает функции АГРЕГАТ игнорировать ошибки.
Подсчет количества ошибок в диапазоне
Представленная ниже формула массива подобна формуле предыдущего примера, за исключением того, что она возвращает количество ошибок в диапазоне с именем Данные.
{=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1;0))}
Эта формула создает массив, состоящий из единиц (если соответствующая ячейка содержит сообщение об ошибке) и нулей (если в ячейке отсутствует сообщение об ошибке).
Можно немного упростить формулу, удалив третий аргумент функции ЕСЛИ. В результате, если условие не выполняется (ячейка не содержит сообщение об ошибке), функция ЕСЛИ будет возвращать значение ЛОЖЬ. Усовершенствованная формула возвращает такой же результат, что и предыдущая, но в ней нет третьего аргумента функции ЕСЛИ.
{=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1))}
В действительности можно еще больше упростить эту формулу:
{=СУММ(ЕОШИБКА(Данные)*1)}
В этой версии формулы используются такие равенства:
ИСТИНА*1=1
И
ЛОЖЬ*1=0
Суммирование n наибольших значений в диапазоне
Следующая формула возвращает значение суммы десяти наибольших значений в диапазоне с именем Данные:
{=СУММ(НАИБОЛЬШИЙ(Данные;СТРОКА(ДВССЫЛ(“1:10”))))}
Функция НАИБОЛЬШИЙ выполняется десять раз, причем каждый раз с другим вторым аргументом (1, 2, 3… 10). Результаты этих вычислений хранятся в новом массиве, который служит аргументом для функции суммирования.
Чтобы просуммировать другое количество значений, необходимо заменить число 10 в аргументе функции ДВССЫЛ на другое значение.
Если количество суммируемых ячеек находится в ячейке С17, используйте оператор конкатенации для создания адреса диапазона для функции ДВССЫЛ.
{=СУММ(НАИБОЛЬШИЙ(Данные;СТРОКА(ДВССЫЛ(“1:”&С17))))}
Для суммирования n наименьших значений в диапазоне следует заменить функцию НАИБОЛЬШИЙ функцией НАИМЕНЬШИЙ.
Вычисление среднего без учета нулевых значений
На рисунке показан рабочий лист, на котором вычисляется средний объем продаж. В ячейку В14 помещена следующая формула:
=СРЗНАЧ(Данные)
Эта формула вычисляет среднее значение именованного диапазона Данные. Два продавца отсутствовали на работе некоторое время. В результате представленная выше формула неточно отображает средний уровень продаж торговых представителей.
Примечание
Функция СРЗНАЧ не учитывает пустые ячейки, зато принимает в расчет ячейки, содержащие нулевые значения.
Следующая формула массива возвращает среднее значение в диапазоне и при этом исключает ячейки, содержащие нулевые значения:
{=СРЗНАЧ(ЕСЛИ(Данные<>0;Данные))}
Эта формула создает новый массив, состоящий исключительно из ненулевых значений. Функция СРЗНАЧ принимает этот массив в качестве своего аргумента. Тот же результат можно получить при использовании обычной формулы (не формулы массива).
=СУММ(Данные)/СЧЁТЕСЛИ(Данные;“<>0”)
Вначале рассчитывается сумма всех значений ячеек в диапазоне. Затем эта сумма делится на количество ненулевых ячеек диапазона, полученное с помощью функции СЧЁТЕСЛИ.
Поиск значения в диапазоне
Для того чтобы определить, присутствует ли определенное значение в диапазоне ячеек, необходимо выбрать команду Главная→Редактирование→Найти и выделить Найти, после чего вручную задать параметры поиска. Можно поступить другим образом и решить эту задачу автоматически с помощью формулы массива.
На рисунке показан рабочий лист со списком имен в диапазоне А3:Е22, которому присвоено имя СписокИмен. Формула массива в ячейке D1 ищет имя, введенное в ячейке С1, которой присвоено имя Имя. Если заданное имя присутствует в списке имен, формула выводит текст Найдено. В противном случае она отображает фразу Не найдено.
Формула в ячейке D1 имеет следующий вид:
{=ЕСЛИ(ИЛИ(Имя=СписокИмен);“Найдено”;“Не найдено”)}
Эта формула сравнивает Имя с каждым элементом в диапазоне СписокИмен. Она создает новый массив, который состоит из логических значений ИСТИНА или ЛОЖЬ. Функция ИЛИ возвращает значение ИСТИНА, если хотя бы одно значение в новом массиве – ИСТИНА. Функция ЕСЛИ использует этот результат для определения возвращаемого сообщения.
Более простой вариант этой формулы представлен ниже. Эта формула возвращает значение ИСТИНА, если имя найдено, в противном случае – ЛОЖЬ.
{=ИЛИ(Имя=СписокИмен)}
Еще один подход предполагает использование функции СЧЁТЕСЛИ:
=ЕСЛИ(СЧЁТЕСЛИ(СписокИмен;Имя)>0;“Найдено”;“Не найдено”)
Подсчет отличающихся значений в двух диапазонах
Представленная ниже формула массива сравнивает соответствующие значения в двух диапазонах (имеющих имена МоиДанные и ТвоиДанные) и возвращает количество различных элементов в этих двух диапазонах. Если содержимое двух диапазонов идентично, формула возвращает нуль.
{=СУММ(ЕСЛИ(МоиДанные=ТвоиДанные;0;1))}
Два диапазона должны иметь одинаковые размер и ориентацию.
Данная формула создает новый массив такого же размера, как и сравниваемые диапазоны. Функция ЕСЛИ заполняет этот новый массив нулями и единицами (нуль – если есть различие, единица – если нет различий в соответствующих ячейках). Функция СУММ возвращает сумму значений этого массива.
Тот же результат можно получить, применив более простую формулу:
{=СУММ(1*(МоиДанные<>ТвоиДанные))}
Эта версия формулы базируется на следующих равенствах:
ИСТИНА*1=1
и
ЛОЖЬ*1=0
Местоположение максимального значения диапазона
Для определения номера строки, в которой находится максимальное значение одномерного вертикального массива с именем Данные, можно воспользоваться следующей формулой:
{=МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);“”))}
Функция ЕСЛИ создает новый массив, соответствующий диапазону Данные. Если соответствующая ячейка содержит максимальное значение диапазона Данные, то в новый массив включается номер ее строки. В противном случае массив содержит пустую текстовую строку. Функция МИН использует этот массив как второй аргумент и возвращает минимальное значение, которое соответствует номеру строки, в которой находится максимальное значение диапазона Данные.
Если диапазон Данные содержит несколько ячеек с максимальным значением, то возвращается номер первой такой ячейки.
Представленная ниже формула массива подобна предыдущей, но она возвращает действительный адрес ячейки с максимальным значением в диапазоне Данные. В формуле используется функция АДРЕС, которая имеет два аргумента: номер строки и номер столбца.
{=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);“”));
СТОЛБЕЦ(Данные))}
Если диапазон Данные занимает больше одного столбца, следующая формула массива вернет правильный адрес:
{=АДРЕС(МИН(ЕСЛИ(Данные=МАКС(Данные);СТРОКА(Данные);“”));
МИН(ЕСЛИ(Данные=МАКС(Данные);СТОЛБЕЦ(Данные);“”)))}
Поиск № строки, в которой находится n-е значение, совпадающее с заданным
Приведенная ниже формула ищет в одномерном вертикальном диапазоне с именем Данные значения, которые совпадают с содержимым ячейки Значение, и возвращает номер строки n-го совпадающего элемента.
{=НАИМЕНЬШИЙ(ЕСЛИ(Данные=Значение;СТРОКА(Данные);“”);n)}
Функция ЕСЛИ создает новый массив, состоящий из номеров строк, в которых значения из диапазона Данные совпадают со значением Значение. Если значение из Данные не совпадает со значением Значение, то новый массив содержит пустую текстовую строку. Функция НАИМЕНЬШИЙ обрабатывает этот новый массив и возвращает n-й наименьший номер столбца.
Если n превышает количество значений в диапазоне Данные, равных Значение, или значение Значение не найдено, формула возвращает ошибку #ЧИСЛО!.
Получение самого длинного текста в диапазоне
Следующая формула отображает содержимое ячейки диапазона Данные, в которой находится наибольшее количество символов. Если самый длинный текст содержится в нескольких ячейках, то возвращается содержимое первой такой ячейки.
{=ИНДЕКС(Данные;ПОИСКПОЗ(МАКС(ДЛСТР(Данные));ДЛСТР(Данные);ЛОЖЬ);1)}
Эта формула работает с двумя массивами, каждый из которых имеет длину соответствующего элемента из диапазона Данные. Функция МАКС находит наибольшее значение, которое соответствует элементу с самым длинным текстом. Функция ПОИСКПОЗ вычисляет смещение ячейки с максимальной длиной. Функция ИНДЕКС возвращает содержимое ячейки, в которой находится текст с наибольшим количеством символов. Эта формула возвращает правильный результат только в том случае, если диапазон Данные состоит из одного столбца.
Определение допустимых значений диапазона
Перед вами может возникнуть следующая задача: проконтролировать содержимое списка на предмет допустимости в нем значений. Предположим, что список всех допустимых чисел хранится в диапазоне с именем Основа. При импорте списка чисел в диапазон с именем МойСписок нужно убедиться, что все числа допустимы. Для этого необходимо сравнить элементы в импортируемом списке с элементами в списке Основа.
Приведенная ниже формула массива возвращает значение ИСТИНА, если каждый элемент диапазона с именем МойСписок найден в диапазоне с именем Основа. Оба этих диапазона должны состоять из одного столбца (это не зависит от числа строк).
{=ЕНД(ПОИСКПОЗ(ИСТИНА;ЕНД(ПОИСКПОЗ(МойСписок;Основа;0));0))}
Можно также определить количество недопустимых значений. Иными словами, если требуется подсчитать количество элементов в МойСписок, которые отсутствуют в Основа, воспользуйтесь следующей формулой:
{=СУММ(1*ЕНД(ПОИСКПОЗ(МоиСписок;Основа;0)))}
Для определения первого недопустимого элемента в МойСписок используйте приведенную ниже формулу.
{=ИНДЕКС(МойСписок;ПОИСКПОЗ(ИСТИНА;
ЕНД(ПОИСКПОЗ(МойСписок;Основа;0));0))}
Вычисление суммы цифр числа
Для вычисления суммы цифр в положительном целом числе, которое хранится в ячейке А1, можно использовать следующую формулу:
{=СУММ(ПСТР(А1;СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(А1)));1)*1)}
Например, если ячейка А1 содержит значение 409, приведенная выше формула вернет значение 13 (4+0+9=13).
Чтобы понять, как работает эта формула, рассмотрим функцию СТРОКА в следующей формуле:
{=СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(А1)))}
Эта формула возвращает массив последовательных целых чисел, который начинается с единицы и заканчивается количеством цифр в числе, находящемся в ячейке А1. Например, если ячейка А1 содержит значение 409, функция ДЛСТР вернет значение 3, а функцией СТРОКА будет сгенерирован следующий массив:
{1;2;3}
Полученный массив впоследствии используется в качестве второго аргумента функции ПСТР. Если в формулу с ПСТР подставить полученные в описанном примере значения, она будет упрощена:
{ПСТР(409;{1;2;3};1)*1}
Эта формула генерирует массив, состоящий из трех элементов:
{4;0;9}
В результате общая формула с функцией СУММ станет более простой и будет иметь следующий вид:
=СУММ({4;0;9})
Результат вычислений равен 13.
Примечание
Функция ПСТР возвращает текстовую строку, поэтому при преобразовании текста в число элементы массива, находящиеся в аргументе функции ПСТР, умножаются на единицу. Альтернативный путь – использование функции ЗНАЧЕН, которая также преобразует строку текста, отображающую число, в числовое значение.
Заметьте, что описанная выше формула не работает с отрицательными значениями, так как знак “минус” не является цифрой. Ниже приводится формула, в которой исправлен данный недостаток. Для этого используется функция ABS, которая возвращает абсолютное значение числа. На рисунке представлен рабочий лист, содержащий в ячейке В4 следующую формулу:
{=СУММ(ЗНАЧЕН(ПСТР(ABS(А4);СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(ABS(А4))));1)))}
Формула в ячейке В4 была скопирована в другие ячейки столбца В с целью вычислить суммы цифр в числах, находящихся в столбце А.
Суммирование округленных значений
На рисунке ниже показан рабочий лист, который демонстрирует характерную проблему всех электронных таблиц – ошибку округления. Как видно, общая сумма в ячейке Е7 не является точной суммой чисел в столбце Е. Значения в столбце Е используют числовой формат, в котором отображаются две цифры после запятой. Фактические значения имеют больше знаков после запятой, и некоторые из них не отображаются. Результирующее влияние погрешностей округления может привести к общему неточному результату. Общее значение составляет $168,320997, отображаемое – $168,32.
Следующая формула массива создает новый массив, который состоит из значений столбца Е, округленных до двух десятичных знаков:
{=СУММ(ОКРУГЛ(Е4:Е6;2))}
Эта формула возвращает правильную величину суммы округленных значений – $168,31.
Для устранения таких ошибок округления можно также ввести в ячейки столбца Е уже округленные значения с помощью функции ОКРУГЛ. Этот подход не требует создания формулы массива.
Суммирование каждого n-го значения в массиве
Предположим, что существует диапазон значений, в котором необходимо определить сумму каждого третьего значения: первого, четвертого, седьмого и т.д. Один из способов решения этой задачи – внесение адреса ячеек в формулу суммирования. Однако лучшим решением будет использование формулы массива.
Обратимся к данным рисунка. Значения хранятся в диапазоне В4:В22 с именем Данные156, а значение n задается в ячейке D4, названной n.
Для суммирования каждого n-го элемента диапазона используется следующая формула:
{=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(ДВССЫЛ(“1:”&
ЧСТРОК(Данные156)))-1;n)=0;Данные156;“”))}
Эта формула создает массив последовательных целых чисел. Функция ОСТАТ принимает его в качестве первого аргумента. Второй аргумент функции ОСТАТ – значение n. Функция ОСТАТ создает другой массив, состоящий из остатков деления каждого последовательного целого числа на n. При равенстве элемента этого массива нулю (это значит, что деление прошло без остатка) происходит суммирование соответствующих значений в диапазоне Данные156.
Если принять значение n равным нулю, приведенная выше формула возвращает сообщение об ошибке. Улучшенная формула массива включает функцию ЕСЛИ для успешной работы при n=0.
{=ЕСЛИ(n=0;0;СУММ(ЕСЛИ(ОСТАТ(СТРОКА(ДВССЫЛ(“1:”
&ЧСТРОК(Данные156)))-1;n)=0;Данные156;“”)))}
Эта формула работоспособна только в том случае, когда диапазон Данные состоит из одного столбца значений. Она не работает, если диапазон состоит из более чем одного столбца или даже одной строки.
Приведенная выше формула всегда включает первый элемент диапазона. К примеру, если n=5, будут суммироваться следующие элементы: 1, 6, 11 и 16. Рассмотрим несколько модифицированную версию формулы, которая начинает суммирование именно с n-го элемента. Когда n=5, формула суммирует следующие элементы: 5, 10 и 15.
{=ЕСЛИ(n=0;0;СУММ(ЕСЛИ(ОСТАТ(СТРОКА(ДВССЫЛ(“1:”
&ЧСТРОК(Данные156)))-n;n)=0;Данные156;“”)))}
Для создания формулы, работающей с горизонтальным диапазоном, необходимо транспонировать массив целых чисел, генерируемых функцией СТРОКА. Модифицированная формула массива, предназначенная для обработки горизонтального диапазона Данные, имеет следующий вид:
{=ЕСЛИ(n=0;0;СУММ(ЕСЛИ(ОСТАТ(ТРАНСП(СТРОКА(ДВССЫЛ(“1:”
&ЧСТРОК(Данные))))-1;n)=0;Данные;“”)))}
Удаление нечисловых символов из текстовой строки
Приведенная ниже формула извлекает числа из текстовой строки. Например, если применить формулу к строке, содержащей текст ABC145Z, будет возвращено значение 145.
{=ПСТР(А1;ПОИСКПОЗ(0;(ЕОШИБКА(ПСТР(А1;
СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(А1)));1)*1)*1);0);
ДЛСТР(А1)-СУММ((ЕОШИБКА(ПСТР(А1;
СТРОКА(ДВССЫЛ(“1:”&ДЛСТР(А1)));1)*1)*1)))}
Эта формула работает только с одним внедренным числом. К примеру, она даст неправильный результат при работе со строкой X45Z99 (вернет 45Z9).
Проверка правильности вычисления формул
Чтобы лучше понять, как выполняется конкретная сложная формула массивов, обратитесь к специальному средству отладки формул. Для этого выделите ячейку с формулой и выберите команду Формулы→Зависимости формул→Вычислить формулу. На экране откроется диалоговое окно Вычисление формулы, показанное ниже.
Щелкните на кнопке Вычислить несколько раз, и увидите промежуточные результаты вычисления формулы. Это подобно замедленному, пошаговому воспроизведению процесса вычисления.
Поиск ближайшего значения в диапазоне
Пусть ячейка с произвольным числом имеет имя Цель157. Для определения ближайшего к нему числа из массива Данные157 воспользуемся формулой массива.
{=ИНДЕКС(Данные157;ПОИСКПОЗ(НАИМЕНЬШИЙ(ABS
(Цель157-Данные157);1);ABS(Цель157-Данные157);0))}
Если в массиве существует два числа, ближайших к Цель157, то формула вернет значение первого из них. На рисунке представлен пример использования этой формулы.
В этом примере массив Данные157 находится в диапазоне А4:А22, значение Цель157 составляет 45. Формула массива находится в ячейке D5 и возвращает ближайшее к Цель157 значение, равное 48.
Получение последнего значения в столбце
Предположим, что существует рабочий лист, в столбец А которого периодически вносятся новые данные, и нам необходимо отобразить последнее введенное значение. Если столбец А не содержит пустых ячеек, данную задачу можно решить сравнительно просто, не прибегая к использованию формул массива.
=СМЕЩ(А1;СЧЁТЗ(А:А)-1;0)
В этой формуле функция СЧЁТЗ используется для подсчета количества непустых ячеек в столбце А. Полученное значение (за вычетом единицы) используется в качестве второго аргумента функции СМЕЩ. Например, пусть последнее значение было введено в строку с номером 100. Функция СЧЁТЗ вернет значение 100, а функция СМЕЩ – значение в ячейке, находящейся на 99 строк ниже ячейки А1.
В то же время столбец А вполне может содержать хотя бы одну пустую ячейку внутри диапазона. Это приведет к тому, что предыдущая формула будет выполняться неправильно. Дело в том, что функция СЧЁТЗ не подсчитывает пустые ячейки.
Приведенная ниже формула массива возвращает содержимое последней непустой ячейки столбца А.
{=ИНДЕКС(А:А;МАКС(СТРОКА(А:А)*(А:А<>“”)))}
Естественно, что эту формулу можно модифицировать для работы с любым другим столбцом. Для этого вместо ссылок на столбец А введите ссылки на любой другой столбец.
Предупреждение
Эту формулу нельзя размещать в том столбце, который она обрабатывает. Игнорирование этого правила приводит к циклической ссылке. Однако можно немного модифицировать формулу. Например, разместите формулу в первой строке и измените ссылки так, чтобы они начинались со второй строки.
Получение последнего значения в строке
Следующая формула массива аналогична предыдущей, однако возвращает последнюю непустую ячейку строки (в данном случае строки 1):
{=ИНДЕКС(1:1;МАКС(СТОЛБЕЦ(1:1)*(1:1<>“”)))}
Для использования этой формулы в другой строке замените ссылку 1:1.
Упорядочение данных с помощью формулы массива
Часто приходится вычислять последовательность значений в диапазоне данных. Предположим, существует рабочий лист с информацией о годовых объемах продаж, совершенных разными менеджерами. Нас интересует, какое место по объему продаж занимает каждый менеджер.
Использование для этой цели стандартной функции РАНГ может привести к неожиданным результатам. Например, если два значения находятся на третьем месте, функция РАНГ присваивает каждому из них значение, равное трем. Однако может возникнуть необходимость определить их ранг как средний относительно других рангов. Иными словами, ранг 3,5 в этой функции для обоих значений связан с третьим местом.
На рисунке представлен рабочий лист, в котором применяются два метода оценки значений, находящихся в столбце В (диапазон Продажи). В первом методе (столбец С) используется функция РАНГ. В столбце D с помощью формулы массива вычисляется ранг.
В ячейке D5 находится формула массива.
{=СУММ(1*(В5<=Продажи))-(СУММ(1*(В5=Продажи))-1)/2}
Эта же формула скопирована в нижние ячейки.
Примечание
Каждый ранг вычисляется с помощью отдельной формулы массива, а не формулы массива, введенной в весь диапазон ячеек.
Формулы массива работают следующим образом. Вначале вычисляется количество значений, больших анализируемого значения. Затем определяется количество равных значений минус один. В заключение из первого количества вычитается половина второго.
Новинка Excel 2010
Новая функция РАНГ.РВ во многих случаях устраняет необходимость применения формулы массива для решения подобных задач. В частности, она возвращает те же ранги, что и формула массива в столбце D на рисунке выше. Для этого нужно скопировать в ячейки столбца D формулу =РАНГ.РВ(В5, Продажи).