Уокенбах Формулы в Excel

Определение допустимых значений диапазона

Иногда возникает следующая задача: необходимо проконтролировать содержимое списка на предмет допустимости значений в нем. Например, список всех допустимых чисел хранится в диапазоне с именем Master. При импортировании списка чисел в диапазон с именем MyList требуется убедиться, что все числа допустимы, путем сравнения элементов в импортируемом списке с элементами в списке Master.

Приведенная ниже формула массива возвращает значение ИСТИНА,если каждый элемент диапазона с именем MyList найден в диапазоне с именем Master. Оба этих диапазона должны состоять из одного столбца в независимости от числа строк.

(=EHfl(nOMCKnO3(HCTMHA;EHfl(nOHCKnO3(MyList;Master;0));0))}

Также можно определить количество недопустимых значений. Иными словами, если нужно подсчитать количество элементов в MyList, которые отсутствуют в Master, можно воспользоваться следующей формулой:

{=CyMM(l*EHfl(nOHCKnO3(MyList;Master;0)))}

А для определения первого недопустимого элемента в MyList можно успешно использовать формулу:

(=MHflEKC(MyList;nOMCKnO3(MCTMHA;EHfl(nOHCKnO3(MyList;Master;0));0))}

Определение суммы цифр числа

Для вычисления суммы цифр в положительном целом числе, которое хранится в ячейке А1, можно применить формулу:

{=СУММ(ПСТР(А1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(А1)));1)*1)}

Например, если ячейка А1 содержит значение 409, приведенная выше формула возвратит значение 13 (4+0+9=13).

А чтобы понять, как работает эта формула, рассмотрим функцию СТРОКА в формуле:

{=СТРОКА(ДВССЫЛ("1:"&ДЛСТР(А1)))}

Эта формула возвращает массив последовательных целых чисел, начинающийся с единицы и заканчивающийся количеством цифр в числе, находящемся в ячейке А1. Например, если ячейка А1 содержит значение 409, функция ДЛСТР возвратит значение 3, функцией СТРОКА будет сгенерирован массив:

Подробная информация об использовании функции двссыл приведена в главе14.

Затем полученный массив используется в качестве второго аргумента функции ПСТР. Если в формулу с ПСТР подставить полученные в описанном примере значения, формула упростится и будет иметь вид:

П С Т Р ( 4 0 9 ; ( 1 ; 2 ; 3 } ; 1 ) * 1 Эта формула генерирует массив, состоящий из трех элементов:

{4;0;9}

Теперь общая формула с функцией СУММ еще более упростится ибудет иметь вид:

=СУММ({4;0;9})

Результат вычислений равен13.

Функция ПСТР возвращаеттекстовую строку. Для преобразованиятекста в число элементы массива, находящиеся в аргументе функции ПСТР, умножаются на 1. Альтернативный путь — использование функции ЗНАЧЕН, которая также преобразует строку текста, отображающую число, в числовое значение.

Заметьте, чтоописанная выше формула не работает с отрицательными значениями, так как знак минус не является цифрой. Ниже приводится формула, которая исправляет этот недостаток. Дляэтого применяется функция ABS, которая возвращает абсолютное значение числа. На рис. 15.4 представлен рабочий лист, содержащий вячейке В 2 следующую формулу:

{=СУММ(ЗНАЧЕН(ПСТР(ABS(A2);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ABS(A2))));!)))}

Формула вячейке В2 была скопирована в другие ячейки столбца В для вычисления суммы чисел, находящихся встолбцеА.

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

На рис. 15.5представлен рабочий лист, который иллюстрирует проблему всех электронных таблиц — ошибку округления.

Как видно из рисунка, общая сумма в ячейке Е5 не является точной суммой чисел в столбце Е.Значения в столбце Е используют числовой формат, в котором отображается две цифры после запятой. Действительные значения имеют больше знаков после запятой, которые не отображаются. Таким образом, в ячейках находятся значения, округленные до двух десятичных разрядов после запятой. Результирующее влияние погрешностей округления может привести к общему неточному результату. Общее точное значение составляет $168,320997, отображаемое — $168,32.

Формула массива создает новый массив, который состоит из округленных значений в столбце Е:

{=СУММ(ОКРУГЛ(Е2:Е4;2))}

Эта формула возвращает правильную величину суммы округленных значений — $168,31. Для устранения таких ошибок округления можно ввести в ячейки столбца Еуже округленные

значения спомощи функцииОКРУГЛ. Этот подход не требует создания формулы массива.

В главе 10 содержится подробная информация о функциях Excel, осуществляющих округление.

Суммирование каждого n-го значения вмассиве

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

Обратимся к данным рис. 15.6. Значения хранятся в диапазоне В2 : В20 с именем Data, a значение п задается в ячейке D6, названной п.

Рис. 15.6. Формуламассива возвращает сумму каждого п-го значенияв диапазоне

Для суммирования каждого л-го элемента диапазона воспользуемся формулой:

{ = С У М М ( Е С Л И ( О С Т А Т ( С Т Р О К А ( Д В С С Ы Л ( " 1 : " & Ч С Т Р О К ( D a t a ) ) ) - l ; n ) = 0 ; D a

Эта формула создает массив последовательных целых чисел, а функция ОС ТАТ использует его в качестве первого аргумента. Второй аргумент функции ОС ТАТ — значение п. Функция ОС ТАТ создает другой массив, состоящий из остатков деления каждого последовательного целого числа на п. При равенстве элемента этого массива 0 (это значит, что деление прошло без остатка) происходит суммирование соответствующих значений в диапазоне Data.

Эта формула выдает сообщение об ошибке, если принять значение п равным 0. Улучшенная формула массива включает функцию ЕСЛИдля успешной работы прип=0:

{=ЕСЛИ(п=0;0;СУММ(ЕСЛИ(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(data)))- l;n)=0;data;"")))}

Эта формула выполняется только в ситуации, когда диапазон Data состоит из одного столбца значений. Она не работает, если диапазон состоит из более чем одного столбца или даже одной строки.

Для создания формулы, работающей с горизонтальным диапазоном, необходимо транспонировать массив целых чисел, генерируемых функцией СТРОКА. Модифицированная формула массива, предназначенная для обработки горизонтального диапазона Data, имеет вид:

{=ЕСЛИ(п=0;0;СУММ(ЕСЛИ(ОСТАТ(ТРАНСП(СТРОКА(ДВССЫЛ( " 1 : " & Ч С Т Р О К ( D a t a ) ) ) )

- l ; n ) = 0 ; D a t a ; " " ) ) ) }

Исключение нечисловых символов из текстовой строки

Приведенная ниже формула проводит извлечение числа из текстовой строки. Например, при применении формулы к текстовой строке, содержащей текст ABC145Z, возвращается значение 145.

{=ПСТР(А1;ПОИСКПОЗ(0;(ЕОШИБКА(ПСТР(А1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(А1)));1) *1)*1);0);ДЛСТР(А1)-

СУММ((ЕОШИБКА(ПСТР(А1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(А1)));1)*1)*1)))}

Эта формула работает только с непрерывными цифровыми последовательностями. Например, формула не даст правильного ответа при применениик текстовой последовательности X45Z99.

Определение ближайшего значения вдиапазоне

Пусть ячейка с произвольным числом имеет название Target. Для определения ближайшего к нему числа из массива Data воспользуемся формулой массива:

{=MHflEKC(Data;nOMCKnO3(HAMMEHbmPm(ABS(Target-Data);1);ABS(Target- Data) ;0) ) }

Если в массиве существует два числа, ближайших к Target,формула возвратит значение первого такого числа. На рис. 15.7 представлен при-

=СМЕЩ(А1;СЧЕТЗ(А:А) - 1;0)

Эта формула использует функцию СЧЕТЗдля подсчета количества непустых ячеек в столбце А. Это значение (минус 1) используется в качестве второго аргумента функции СМЕЩ. Например, пусть последнее значение было введено в строку с номером 100. Функция СЧЕТЗ возвратит значение 100, а функция СМЕЩ — значение в ячейке, находящейся на 99 строк ниже ячейкиА1.

Однако столбец А вполне может содержать хотя бы одну пустую ячейку внутри диапазона. Это приведет к тому, что предыдущая формула будет выполняться неправильно. Дело в том, что функция СЧЕТЗ не подсчитывает пустые ячейки.

Приведенная ниже формула возвращает содержимое последней непустой ячейки в первых 500 строках столбца А:

{=ИНДЕКС(А1:А500;МАКС(СТРОКА(А1:А500)*(А1:А500<>"")))}

Естественно, что эту формулу можно модифицировать для работы с любым другим столбцом. Для этого вместо ссылок на столбец А введите ссылки на любой другой столбец. Если количество строк превышает 500, замените в формуле число 500 на большее. Однако, чем меньше будет это число, тем больше будет скорость вычислений.

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

Возвращение последнего значения встроке

Аналогично можно найти значение последней непустой ячейки в строке. Для этого воспользуемся формулой:

{=ИНДЕКС (1:1;МАКС (СТОЛБЕЦ (1:1) *(1:1<>||И))) }

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

Упорядочение данных с помощью формулы массива

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

Использование для этой цели стандартной функции Excel РАНГ может приводить не совсем к тем результатам, которые ожидаются. Например, если два значения находятся на третьем месте, функция РАНГ присваивает каждому из них значение, равное 3. Однако может возникнуть желание определить их ранг как средний относительно других рангов. Иными словами, ранг 3,5 в этой функциидля обоих значений связан с третьим местом.

{=СУММ(1* (B2<=Sales.) ) -(СУММ(1* (B2=Sales) ) -1) /2} Эта же формула скопирована в нижниеячейки.

Каждый ранг вычисляется отдельной формулой массива, а не формулой массива, введенной в весь диапазон ячеек.

Puc. 15.8. Оценкаданныхс помощифункции РАНГи формулы массива

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

Создание динамических перекрестных таблиц

Перекрестные таблицы представляют обрабатываемые данные в двух измерениях. Рассмотрим данные примера, представленного на рис. 15.9.

Puc. 15.9. Использованиеформулмассива для суммирования данных в динамическихперекрестных таблицах

На этом рабочем листе представлен простейший список общих издержек. В столбцах содержится информацияо дате (Date), статье издержек (Category) и общей сумме издержек(Amount).

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

{=СУММ(($E3=Date)*(F$2=Category)*Amount)}

Эта формула скопирована и в остальные 14 ячеек таблицы. Формула отображает общие итоги каждого дня по категориям.

Она суммирует значения в диапазоне Amount в том случае, если названия строки и столбца в сводной таблице совпадают с данными в соответствующих диапазонах Date и Category. Это достигается путем умножения двух булевых (логических) выражений на Amount. Если оба булевых выражения — ИСТИНА,то результат равен Amount. Если хотя бы одно из булевых выражения — ЛОЖЬ,результат равен 0.

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

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

Работа сформулами массива, возвращающими несколько значений

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

Извлечение положительных значений из диапазона

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

используется формула массива:

ных значений

Извлечение непустых ячеек издиапазона

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

{ =ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ (ЕСЛИ ( D a t a o " " ; С Т Р О К А (ДВССЬШСЧ: "&4CTPOK(Data) ) ) ) ; СТРОКА(ДВССЫЛ("1:"&4CTP0K(Data)))));"";ИНДЕКС(Data;

НАИМЕНЬШИЙ (ЕСЛИ ( D a t a o 1 1 " ; СТРОКА (ДВССЬШСЧ: "&4CTP0K(Data) ) ) ) ; СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(Data))))))}

Изменение напротивоположный порядка следования элементов вдиапазоне

Ниже представленная формула массива работает с одномерным вертикальным массивом, имеющим имя Data. Она расположена в диапазоне, имеющем такой же размер, что и Data. Для возвращения массива с противоположным поряд-

ком следования элементов, нежели в Data, используется

Предположим, существует рабочий лист с одномерным вертикальным диапазоном с именем Data. Для осуществления сортировки значений массива по убыванию воспользуемся формулой массива:

{=НАИБОЛЫ11ИЙ (Data; СТРОКА (ДВССЬШ("1: "&ЧСТРОК (Data) ) )•)}

Она должна вводиться в такой же по размеру диапазон, что и Data, и производить сортировку только числовых значений.

Для сортировки значений Data по возрастанию применяется формула: { НАИМЕНЬШИЙ (Data; СТРОКА (ДВССЬШСЧ: "&ЧСТРОК (Data) ) ) ) }

Эти формулы полезны в том случае, когда нужно производить немедленную сортировку введенных данных. Дляэтого вначале присвойте диапазону, в который вводятся данные, имя Data.Затем введите формулу массива в другой диапазон с таким же количеством строк, что и Data.

Для ячеек в Data, не имеющих значения, формула массива будет выдавать сообщение об ошибке #ЧИСЛО!. Этот факт может раздражать при вводе данных. Улучшенная версия формулы, исключающая появление сообщений об ошибке, имеет вид:

{=ЕСЛИ(ЕОШ(НАИБОЛЫ11ИЙ(БаЬа;СТРОКА(ДВССЫЛСЧ: "&ЧСТРОК (Data) ) ) ) ) ; " " ; НАИБОЛЬШИЙ (Data; СТРОКА (ДВССЬШСЧ: "&ЧСТРОК (Data) ) ) ) ) }

ние об ошибке #ЧИСЛО!. Нарис. 15.12представлен пример применения формулы массива. Диапазон А2 :А20 имеет имя Data, а формула массива введена в диапазон С2 : С20.

Отображение календаря вдиапазоне

На рис. 15.13 представлен календарь, отображаемый в диапазоне ячеек..

Рабочий лист имеет дваопределенных имени: т (для месяцев), у (для лет). Единая формула массива вводится во все 42 ячейки, отображающие числа календаря. Диапазон В6 :Н И содержит формулу массива:

{=ЕСЛИ(МЕСЯЦ(ДАТА(у;т;1))оМЕСЯЦ(ДАТА(у;т;1)-(ДЕНЬНЕД(ДАТА(у;т;1))- 1)+{0:1:2:3:4:5}*7+{1;2;3;4;5;б;7}-1);"";ДАТА(у;т;1)- (ДЕНЬНЕД(ДАТА(у;т;1) )-1)+{0:1:2:3:4:5}*7 + {1;2;3;4;5;6;7}-1)}

Рис. 15.13. Отображение календаря с помощью одной формулы массива

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

{=ДАТА(у;т;1)-(РАБДЕНЬ(ДАТА(у;т;1))- 1)+{0:7:14:21:28:35}+{0;1;2;3;4;5;6}>

В главе 14можно найти дополнительную информацию о массиве констант.

Эта версия формулы отображает дни из предыдущего и следующего месяцев. Функция ЕСЛИ в первоначальной формуле проверяет каждую дату на предмет принадлежности к текущему месяцу. Если дата не принадлежит к текущему месяцу, возвращается пустая строка.

Возвращение массива спомощью VBA-функций

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

Например, автор этих строк убил несколько часов в бесплодных попытках создать формулу массива, возвращающую отсортированный список текстовых данных. Хотя подобная операция успешно проводилась с помощью формулы массива для числовых величин (см. "Динамическая сортировка величин в диапазоне"), произвести то же самое для текста оказалось довольно проблематично.

Представленная формула работает, но только в том случае, если диапазон Data не содержит повторяющихся значений:

{=ИНДЕКС(БаЬа;П0ИСКП03(СТР0КА(ДВССЫЛ(и1:"&ЧСТРОК(Data)));СЧЕТЕСЛИ(Data ; " < = " & D a t a ) ; 0 ) ) }

Поэтому создана пользовательская VB А-функция, названная SORTED:

Function SORTED(rng, Optional ascending) As Variant Dim SortedData() As Variant

Dim CellCount As Long

Dim Temp As Variant, i As Long, j As Long CellCount = rng.Count

ReDim SortedData(1 To CellCount)

If IsMissing(ascending) Then ascending = True

If rng.Columns.Count > 1 Then

SORTED =CVErr(xlErrValue)

Exit Function

End If

For i = 1 To CellCount

SortedData(i) = rng(i)

If TypeName(SortedData(i)) = "Empty" _

Then SortedData(i) = ""

Next i

On Error Resume Next

For i = 1 To CellCount

For j = i + 1 To CellCount