Частичные операции

В Excelесть термин –частичная сумма, которым обозначается операция суммирования чисел из заданного диапазона, но «не всех подряд», а только тех, которые удовлетворяют определенному условию. Например, функцииСУММЕСЛИи СЧЕТЕСЛИ.

Рассмотрим частичную операцию суммирования на примере подсчета суммы комиссионных для стоимости имущества более 250 000 руб.

Рисунок 17 – Частичная операция суммирования

В ячейку В6введена функцияСУММЕСЛИ, аргументы которой показаны в диалоговом окне на рисунке

Рисунок 18 – Диалоговое окно СУММЕСЛИ

В поле Диапазонзадан диапазонА2:А5в котором содержатся данные о стоимости имущества. ФункцияСУММЕСЛИсравнивает каждое значение из диапазона вычисляемых ячеекА2:А6с критерием отбора>250000, заданным в полеКритерий. Ячейки в полеДиапазон_суммированиясуммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. ЕслиДиапазон_суммированияопущен, то суммируются ячейки в аргументе «диапазон».

В нашем случае в поле Диапазон_суммированияприсутствуют фактические ячейки для суммирования – значения комиссионных. ФункцияСУММЕСЛИпросуммировала только те значения комиссионных (21 000р. + 28 000р.), для которых стоимость имущества превышает250 000р.

Microsoft Excelпредлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа появлений текстовой строки или числа в пределах диапазона ячеек, можно воспользоваться функциейСЧЁТЕСЛИ.

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

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

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

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

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

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

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

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

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

подсчет	{=СУММ(ЕСЛИ(Усл; 1; 0))}	суммирует единицы
суммирование	{=СУММ(ЕСЛИ(Усл; А; 0))}	суммирует отобранные элементы
максимальный	{=МАКС(ЕСЛИ(Усл; А))}	находит наибольшее среди отобранных
минимальный	{=МИН(ЕСЛИ(Усл; А))}	находит наименьшее среди отобранных

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

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

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

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

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