• Число месяцев, в течение которых расходы превышали бюджет.

• Общую сумму превышения расходов над бюджетом (только по тем месяцам, когда это происходило).

• Общую сумму резерва (только по тем месяцам, когда расходы были меньше бюджетных поступлений).

• Cуммарные расходы минус суммарный бюджет за полугодие.

Решение достигается в рамках построения таблицы и введения в ее ячейки формул, показанных ниже:

Справка

Функция ABS

Функция ABS возвращает модуль (абсолютную величину) числа. Синтаксис: ABS(Число), где Число - это действительное число, абсолютную величину которого требуется найти.

Иллюстративные примеры

1. Если ячейка A1 содержит отрицательное число -16, то функция =КОРЕНЬ(ABS(A1)) возвращает 4.

2. Функция =ABS(-2) возвращает 2.

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

Из этих формул только одна {=СУММ((B2:B7> C2:C7)*1)} требует специального пояснения. Чтобы понять, как «работают» все остальные, нужно обратиться к предыдущему материалу и приведенной ниже Справке. Формула массива B2:B7>C2:C7 (а это именно вложенная формула, хотя перед ней и нет знака равенства: во вложенных формулах и функциях знак равенства опускается) возвращает одно из двух значений ИСТИНА или ЛОЖЬ в зависимости от соотношения между числами, находящимися в соответствующих позициях столбцов B2:B7 и C2:C7 таблицы. Умножение на единицу после вложенной логической формулы (B2:B7> C2:C7) требуется для того, чтобы Ехсеl преобразовал логические константы ИСТИНА и ЛОЖЬ соответственно в 1 и 0. Функция массива {=СУММ(…)} суммирует эти единицы и нули, а результат суммирования показывает, сколько раз в формуле (B2:B7> C2:C7) реализуются правильные неравенства, т.е. в нашей задаче - число месяцев, в течение которых расходы превышали бюджет.

Справка

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию. Синтаксис: =СЧЁТЕСЛИ(Интервал;Критерий), где Интервал - это интервал, в котором нужно подсчитать число ячеек, удовлетворяющих простому Критерию. Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, Критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Иллюстративные примеры

1. Пусть ячейки A3:A6 содержат текстовые значения "яблоки", "апельсины", "персики", "яблоки" соответственно. Функция =СЧЁТЕСЛИ(A3:A6;"яблоки") возвращает значение 2, поскольку текст "яблоки" встречается в диапазоне A3:A6 два раза.

2. Пусть ячейки B3:B6 содержат числа 32, 54, 75, 86 соответственно. Функция =СЧЁТЕСЛИ(B3:B6;">55") возвращает значение 2, поскольку в диапазоне B3:B6 имеются два числа 75 и 86, превышающие число 55.

Функция СУММЕСЛИ

Функция СУММЕСЛИ суммирует значения ячеек, специфицированных заданным критерием. Синтаксис: =СУММЕСЛИ (Проверяемый_Интервал;Критерий;Сумм_интервал), где Проверяемый_Интервал - это интервал ячеек, для каждой из которых происходит проверка простого Критерия. Критерий - это критерий в форме числа, выражения или текста, действие которого распространяется на Проверяемый_Интервал. Например, критерий может быть выражен как 32, "32", ">32", "яблоки". Сумм_интервал - это тот диапазон ячеек, значения которых учитываются при суммировании, если Критерий в соответствующих позициях Проверяемого_Интервала вырабатывает логическое значение ИСТИНА, и значения которых не учитываются при суммировании в противном случае. Если Сумм_интервал опущен, то суммируются ячейки в аргументе Проверяемый_Интервал.

Иллюстративный пример

Пусть ячейки A1:A4 содержат следующие величины стоимости для четырех домов: 100 т.р., 200 т.р., 300 т.р., 400 т.р. соответственно, а ячейки B1:B4 содержат соответствующие величины комиссионных при продаже домов: 7000 руб., 14000 руб., 21 000 руб., 28 000 руб. Функция =СУММЕСЛИ(A1:A4;">160 т.р.";B1:B4) вычисляет сумму комиссионных для тех домов, стоимость которых превышает 160 т.р. Она возвращает значение равное 63 000 руб.

Пример 7.25. Постановка задачи: В ячейках А1, А2, А3 находятся текстовые значения М, С, М соответственно. В ячейку В1 введена формула =ЕСЛИ(СЧЁТЕСЛИ(A1:A3;"М")<=1;МАКС(3;0;7);СРЗНАЧ(1;15)). Какое значение в ней отобразится?

Решение: Поскольку в ячейках А1, А2, А3 текст М встречается два раза, то вложенная функция СЧЁТЕСЛИ(A1:A3;"М") возвращает значение 2. Поскольку 2>1 – истинное неравенство, то вложенная функция СЧЁТЕСЛИ(A1:A3;"М")<=1 возвращает значение ЛОЖЬ. Тогда функция ЕСЛИ возвращает значение, соответствующее своему третьему аргументу, т.е. вычисляемое вложенной функцией СРЗНАЧ(1;15). Среднее арифметическое двух чисел 1 и 15 равно 8. Следовательно, в ячейке В1 отобразится значение 8.

Пример 7.26. Постановка задачи: В ячейках А1, А2, А3, А4 находятся текстовые значения Cаша, Маша, Даша, Маша соответственно. В диапазоне В1:В4 находятся числовые значения 6, 2, 9, 4 соответственно. Какое отображаемое значение возникает в ячейке D1, если в диапазон С1:D1 вводится формула массива {=ЕСЛИ(СУММЕСЛИ(A1:A4; А4;B1:B4)<={1;6};МАКС(B1:B4);СРЗНАЧ(B1:B4))}?

Решение: В ячейке А4 находится текст Маша. Функция СУММЕСЛИ(A1:A4;А4;B1:B4) суммирует те значения диапазона B1:B4, для которых значения диапазона A1:A4 совпадают со значением ячейки А4, т.е. с текстом Маша. Такое совпадение реализуется для ячеек А2 и А4. Поэтому нужно найти сумму чисел в ячейках В2 и В4. Промежуточный результат этого этапа решения задачи равен (2+4) = 6. Далее, поскольку нас интересует результат, возникающий в ячейке D1 диапазона С1:D1, надо провести анализ лишь второго неравенства в группе неравенств СУММЕСЛИ(A1:A4;А4;B1:B4)<={1;6}, т.е. выяснить справедливость неравенства 6<=6, которое, очевидно, является истинным. Поэтому внешняя функция ЕСЛИ возвращает значение, вычисляемое вложенной функцией МАКС(B1:B4), а та в свою очередь возвращает максимальное значение конечного числового множества 6, 2, 9, 4, т.е. число 9. Следовательно, в ячейке D1 отобразится значение 9.

Пример 7.27. Цель: Разобраться как «работает» функция ВПР и в каких случаях следует вводить формулы массивов в отдельные ячейки.

Постановка задачи: Два цеха производят продукцию трех видов А, Б, В, причем цех 1 производит продукцию видов А и Б, а цех 2 – видов Б и В. Каждый вид продукции выпускается в двух вариантах соответствующих артикулов: продукции вида А соответствуют артикулы А1 и А2, продукции вида Б – артикулы Б1 и Б2, продукции вида В – артикулы В1 и В2. Имеется информация о количестве продукции, произведенной каждым цехом за январь и февраль. Известны также издержки, приходящиеся на единицу продукции каждого артикула. Подзадача 1: Определить общие издержки по продукции каждого артикула, выпущенной каждым цехом в январе и феврале. Подзадача 2: Определить также

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

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

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

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

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

• количество продукции артикула Б2, изготовленное обоими цехами за два месяца,

• количество продукции артикула Б2, изготовленное цехом 2 за два месяца,

• долю продукции артикула Б2, изготовленной цехом 2 за два месяца, в общем объеме продукции этого артикула, изготовленной обоими цехами за два месяца,

• общие издержки по изготовлению продукции артикула Б2 цехом 2 за два месяца,

• долю издержек цеха 2 по изготовлению продукции артикула Б2 за два месяца в общих издержках по изготовлению продукции этого артикула обоими цехами за два месяца,

• долю издержек цеха 2 по изготовлению продукции артикула Б2 за два месяца в общих издержках по изготовлению продукции вида Б обоими цехами за два месяца.

Результат решения подзадачи 1:

Предварительные действия:

1. В ячейки диапазона А13:А18 введен текст янв, и им назначен скрытый формат (<Ctrl > + <1> / Число / Все форматы / Выбор скрытого формата ;;; / <ОК>).

2. В ячейки диапазона А20:А24 введен текст фев, и им назначен скрытый формат.

3. В ячейки диапазонов В13:В14 и В20:В21 введен текст цех 1, и им назначен скрытый формат.

4. В ячейки диапазонов В16:В18 и В23:В24 введен текст цех 2, и им назначен скрытый формат.

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

5. Столбцы G, H, I, J, K, L скрыты: в них содержатся иные варианты решения подзадачи 1, которые будут обсуждаться после основного варианта, соответствующего столбцу F.

Обсуждение решения подзадачи 1:

В диапазонах А12:Е24 и М12:N17 содержатся исходные данные задачи. Результат решения подзадачи 1 содержится в диапазоне F12:F24. С математической точки зрения обе подзадачи решаются элементарно. Так, например, чтобы найти общие издержки при выпуске цехом 2 продукции вида Б артикула Б1 нужно перемножить соответствующие объем выпуска (который введен в ячейку Е15) на издержки, приходящиеся на единицу продукции этого артикула (которые содержатся в ячейке N14). Чтобы получить этот частный результат, мы могли бы в ячейку F15 ввести формулу = Е15*N14 и аналогичным образом поступить при нахождении всех остальных частных результатов решения подзадачи 1. Но тогда нам потребовалось бы в диапазон F12:F24 ввести 13, хотя и однотипных, но различных формул. Excel позволяет автоматизировать этот процесс вводом в ячейку F12 одной главной формулы =ВПР(D12;$M$12:$N$17;2;0)*E12 с последующим ее копированием в остальные ячейки диапазона F12:F24 (так что, например, в ячейке F13 «лежит» формула =ВПР(D13;$M$12:$N$17; 2;0)* *E13, в ячейке F14 «лежит» формула =ВПР(D14;$M$12:$N$17; 2;0)*E14 и т.д.). Функция ВПР, содержащаяся в ячейке F12, “работает” следующим образом: она находит точное соответствие значения ячейки D12 (т.е. текста А1) такому же значению первого столбца диапазона $M$12:$N$17 и возвращает соответствующее значение второго столбца этого диапазона (в данном случае – значение ячейки N12, т.е. издержки, приходящиеся на выпуск единицы продукции артикула А1). Аналогичным образом эта функция “работает” и в остальных ячейках диапазона F13:F24. Более подробная информация о синтаксисе функции содержится ниже в Справке.

Справка

Функция ВПР (“Вертикальный ПРосмотр”)

Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного (аргументом Номер_столбца) столбца таблицы. Синтаксис: ВПР(Искомое_значение; Инфо_таблица;Номер_столбца;Интервальный_просмотр), где:

• Искомое_значение - это значение, которое должно быть найдено в первом столбце Инфо_таблицы (Искомое_значение может быть значением, ссылкой или текстовой строкой);

• Инфо_таблица - это таблица с информацией, в которой ищутся данные(в качестве аргумента Инфо_таблица можно использовать ссылку на интервал или имя интервала, например, БазаДанных или Список);

• Номер_столбца - это номер столбца в Инфо_таблице, в котором должно быть найдено соответствующее значение (если Номер_столбца равен 1, то возвращается значение из первого столбца аргумента Инфо_таблица; если Номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее. Если Номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе Инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!);

• Аргумент Интервальный_просмотр является логической константой (который в случае значения ИСТИНА можно заменить единицей, а в случае значения ЛОЖЬ - нулем). Если Интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента Инфо_таблица должны быть обязательно расположены в возрастающем порядке, например, в таком: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат (Сортировку по возрастанию можно выполнить с помощью команд Данные / Сортировка / Установить переключатель По Возрастанию / <ОК>). Если Интервальный_просмотр имеет значение ЛОЖЬ, то Инфо_таблица не обязана быть отсортированной по возрастанию.

При использовании функции ВПР нужно иметь ввиду следующие обстоятельства:

1. Значения в первом столбце аргумента Инфо_таблица могут быть текстами, числами или логическими значениями. При этом регистр не учитывается (т. е. строчные и заглавные буквы не различаются).

2. Аргумент Интервальный_просмотр определяет, нужно ли, чтобы функция ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение. Другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем Искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

3. Если ВПР не может найти искомое_значение и аргумент Интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.

4. Если Искомое_значение меньше, чем наименьшее значение в первом столбце аргумента Инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.

5. Если функция ВПР не может найти Искомое_значение и Интервальный_просмотр имеет значение ЛОЖЬ, то функция ВПР возвращает значение ошибки #Н/Д (не доступно).

Иллюстративные примеры

На приведенной части рабочего листа

диапазон A4:C12 имеет имя Показатели. Ниже рассмотрены некоторые возможные варианты “работы” функции ВПР на указанном диапазоне:

1. Функция = ВПР(1;Показатели;1;ИСТИНА) возвращает значение 0,946 из ячейки А10 первого столбца (крайнего левого в Инфо_таблице, которая в данном случае есть ни что иное, как диапазон A4:C12) диапазона с именем Показатели, поскольку число 0,946 является наибольшим в этом столбце, не превышающим единицы (отметим, что: первый столбец отсортирован по возрастанию; аргумент Искомое_значение равен 1; аргумент Номер_столбца также равен 1).

2. Функция = ВПР(1;Показатели;2) возвращает значение 2,17 из ячейки В10 второго столбца диапазона Показатели (отметим, что аргумент Интервальный_просмотр опущен), поскольку число 0,946 является наибольшим, не превышающим единицы, в первом столбце (отметим, что возвращаемое значение “лежит” в той ячейке второго столбца Инфо_таблицы, которая “соседствует” с ячейкой с числом 0,946 ее крайнего левого столбца).

3. Функция = ВПР(1;Показатели;3;ИСТИНА) возвращает значение 100 из ячейки С10 третьего столбца Инфо_таблицы.

4. Функция = ВПР(0,746;Показатели;3;ЛОЖЬ) возвращает значение 200 из ячейки С8 третьего столбца Инфо_таблицы, поскольку функция ВПР ищет и находит точное совпадение аргумента Искомое_значение, равного 0,746, с числом 0,746 в первом столбце Инфо_таблицы в ячейке А8.

5. Функция = ВПР(0,1;Показатели;2;ИСТИНА) возвращает значение ошибки #Н/Д (не доступно), поскольку 0,1 меньше, чем наименьшее значение 0,457 в столбце A.

6. Функция = ВПР(2;Показатели;2;ИСТИНА) возвращает значение 1,71.

Другие возможные (в том числе и неправильные) варианты решения подзадачи 1 представлены в двух следующих таблицах:

Вариант	Ячейка или диапазон	Формула	Тип формулы
вариант 2	G12:G24	{=ВПР(D12:D24;$M$12:$N$17;2;0)*E12:E24}	формула массива
вариант 3	$H$12	=СУММ((D12=$M$12:$M$17)*$E$12:$E$24*$N$12:$N$17)	главная формула
вариант 4	$I$12	=СУММ((D12=$M$12:$M$17)*E12*$N$12:$N$17)	главная формула
вариант 5	J12	{=СУММ((D12=$M$12:$M$17)*E12*$N$12:$N$17)}	формула массива как главная формула
вариант 6	$K$12	=СУММ(ЕСЛИ(D12=$M$12:$M$17;E12*$N$12:$N$17))	главная формула
вариант 7	L12	{=СУММ(ЕСЛИ(D12=$M$12:$M$17;E12*$N$12:$N$17))}	формула массива как главная формула

Вариант 2 реализует метод решения подзадачи 1, аналогичный рассмотренному выше, но в нем используется формула массива. В вариантах 3, 4 и 6 показаны примеры неверного решения подзадачи 1. Внимательно проанализируйте формулы, приведенные в последней таблице для этих вариантов, и попробуйте найти ответ на вопрос, почему они приводят к неправильному результату. Обратите внимание на два обстоятельства: на размерности диапазонов, которые входят в эти формулы, а также на то, что Excel пытается обрабатывать эти диапазоны как формулы массивов, но, поскольку формулы введены как обычные “ячеечные” формулы, останавливается на первом шаге. Наоборот, формулы массивов, реализующие варианты 5 и 7, приводят к верному результату. Их также имеет смысл внимательно проанализировать. По-существу, они дают более простой способ решения подзадачи 1 по сравнению с основным вариантом, в котором применяется функция ВПР. Обратим внимание на то, что в вариантах 4 и 5, 6 и 7 используются попарно одинаковые формулы, но в вариантах 4 и 6 они введены как обычные “ячеечные” формулы, а в вариантах 5 и 7 они введены, хотя и в отдельные ячейки, но как формулы массивов, причем последние являются главными формулами соответственно для диапазонов J12:J24 и L12:L24.

Разберем для примера, как «работает» формула {=СУММ(ЕСЛИ(D12=$M$12:$M$17;E12*$N$12:$N$17))}. В ней значение ячейки D12 сравнивается с каждым значением диапазона $M$12:$M$17. В тех случаях, когда имеет место совпадение, функция ЕСЛИ возвращает соответствующее значение из диапазона $N$12:$N$17, умноженное на значение ячейки Е12, в противном случае возвращается значение 0 (НУЛЬ). Функция СУММ, введенная в ячейку L12 как формула массива, суммирует все эти нулевые и ненулевые значения (в данном случае имеется только одно ненулевое значение). Результат, как видно из предпоследнего рисунка, оказывается правильным.

Решение подзадачи 2 приведено в таблицах, расположенных ниже по тексту.

В диапазон С28:С32 введена формула массива {=B28:B32/ A28:A32}, по которой рассчитываются соответствующие издержки, приходящиеся на единицу продукции, как частное от деления общих издержек на суммарный объем произведенной продукции.

Обсудим для примера, как «работает» формула {=СУММ(($A12:$A24="фев")*($B12:$B24="ЦЕХ1")*($C12:$C24="Б")*F12:F24)}, которая (хотя и является формулой массива) вводится в одну ячейку, а не в диапазон, и по которой рассчитываются общие издержки по изготовлению продукции вида Б цехом 1 в феврале. Напомним, что в диапазоне А19:А24 находится текст фев, для ячеек которого функция $A12:$A24="фев" возвращает значение 1 (ИСТИНА). Для остальных ячеек А12:А18 эта функция возвращает значение 0 (ЛОЖЬ).Аналогично «работают» две другие вложенные функции $B12:$B24="ЦЕХ1" и $C12:$C24="Б". Поэтому для тех ячеек трех диапазонов А12:А24, B12:B24 и C12:C24, для которых эти три функции одновременно возвращают значение, равное 1, функция СУММ суммирует соответствующие значения ячеек диапазона F12:F24. Остальные значения этого диапазона умножаются на НУЛЬ и потому не вносят вклада в общий итог.

На остальные вопросы подзадачи 2 ответ содержится в следующих двух таблицах:

Вопрос: Почему отображаемые значения ячеек I36 и I43 одинаковы, а отображаемые значения ячеек I38 и I45 различны?

Совет: Обдумайте формулы, которые содержатся в ячейках диапазона I35:I45. Особое внимание обратите на ячейки I38 и I45.