Формулы массива

Для поиска частичного максимума ли минимума, а также частичные подсчет и суммирование сложных условий могут быть выполнены с помощью формул массива. Такие формулы состоят из внешней функции СУММ, МАКС или МИН (собственно выполняющих их необходимую частичную операцию) и одной или нескольких вложенных функций ЕСЛИ, создающих виртуальный массив, над которыми эти операции выполняются.

Формула массива является мощным вычислительным элементом Excel. В отличие от обычных (скалярных) формул, вычисляющих значение только для одной ячейки, формулы массива вычисляют значения как для одной ячейки, так и для нескольких ячеек одновременно. Формулы массива заключены в фигурные скобки {}. Завершение ввода формулы массива осуществляется не простым нажатием клавиши Enter, а нажатием комбинации клавиш Ctrl+Shift+Enter, после чего формула автоматически «обрамляется» фигурными скобками.

Допустим, требуется вычислить сумму абсолютных значений чисел, содержащихся в диапазоне А1:А5. Если не использовать формулу массива, то единственный способ решения этой задачи состоит в том, чтобы в соседнем диапазоне, например, В1:В5, с помощью обычной функции ABS создать абсолютные значения и потом просуммировать их, например, в ячейке С1 с помощью функции СУММ.

Для того, чтобы ускорить процесс вычислений с помощью единственной формулы воспользуемся формулой массива {=СУММ(ABS(A1:A5))}.

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

С учетом вышеизложенного, формулы массива, реализующие рассматриваемые задачи частичных операций могут быть записаны в следующем виде:

Рассмотрим использование формул массива на примере поиска минимальной выручки торговой точки, превышающую заданную величину, например 1000 р.

Исходные данные для решения задачи содержатся в диапазоне A1:B10 рабочего листа Excel. Размер выручки записан в ячейках диапазона В2:В10. Если для нахождения величины минимальной выручки ввести в ячейку С2 функцию =МИН(B2:B10) – вы найдете самую минимальную выручку 850 р., которая не удовлетворяет заданному условию – меньше 1000р. Для того, чтобы «отсечь» лишние значения и сформировать виртуальный диапазон, в котором отсутствуют компоненты, неудовлетворяющие заданному условию включим в функцию МИН функцию ЕСЛИ, задающую условия отбора.

{=МИН(ЕСЛИ(В2:В10>1000; В2:В10))}

Функция ЕСЛИ в части условия будет поэлементно сравнивать значения в диапазоне В2:В10 c заданным критерием отбора >1000 и формировать виртуальный массив из которого будут исключены элементы неудовлетворяющие заданному критерию, т.е элементы 900р. и 850р..

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