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

Функция СУММЕСЛИ суммирует значения ячеек, специфицированных заданным критерием. Синтаксис: =СУММЕСЛИ
(Проверяемый_Интервал; Критерий; Сумм_интервал), где Проверяемый_Интервал - это интервал ячеек, для каждой из которых происходит проверка простого Критерия. Критерий - это критерий в форме числа, выражения или текста, действие которого распространяется на Проверяемый_Интервал. Например, критерий может быть выражен как 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 реализует метод решения подзадачи 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.

Вопросы для самопроверки

7.1. Какое понятие является более общим в Ехсеl: формула или функция?

7.2. Какое значение возвращает формула = -(-2)^2+(-2)^2?

7.3. Какой из операторов % или * имеет более высокий приоритет?

7.4. Сколько форматов ссылок имеется в Ехсеl?

7.5. Ударом по какой клавише производится очистка выделенной ячейки с формулой?

7.6. Оказывает ли влияние перемещение влияющих и зависимых ячеек на правильность вычислений по формулам Ехсеl?

7.7. Происходит ли изменение операндов при перемещении только зависимых ячеек?

7.8. Происходит ли изменение операндов в зависимых ячейках при перемещении только влияющих ячеек?

7.9. Происходит ли изменение операндов при копировании формулы, содержащей только абсолютные ссылки?

7.10. Происходит ли изменение операндов при копировании формулы, содержащей в качестве ссылки имя, заданное по умолчанию?

7.11. Происходит ли изменение операндов при копировании формулы, содержащей только относительные ссылки?

7.12. Происходит ли изменение операндов при копировании формулы, содержащей только смешанные ссылки?

7.13. Можно ли внешние фигурные скобки в формулах массивов набирать с клавиатуры?

7.14. Следует ли фигурные скобки в массивах констант набирать с клавиатуры?

7.15. Какая команда используется для ввода формулы массива?

7.16. Ячейка А1 содержит число 16. Какое значение возвращаетфункция

=A$1*ЕСЛИ(A$1< 10; 10%; 1) *ЕСЛИ(ИЛИ(A$1> =10; A$1< 20); 20%; 1)?

7.17. В ячейках А1, А2, А3, А4 находятся числа 0, 4, 5, 0 соответственно. В диапазон В1: В4 введена формула массива {=ЕСЛИ(A1: A4> > ТРАНСП({5; 2; 3; 4}); A1: A4; 9)}. Какое значение отобразится в ячейке В4?

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

7.19. В ячейках А1, А2, В1, В2 находятся соответственно числа 1, 2, 3, 4. В ячейку С3 введена формула =А$1+$В1. Какая формула возникнет в ячейке С4 при копировании в нее содержимого ячейки С3? Предполагается, что все остальные ячейки рабочего листа являются пустыми.

7.20. В ячейках А1 и В2 находятся соответственно числа 1, 2. В ячейку С3 введена формула =А$1+ИМЯ_В2, где ИМЯ_В2 – имя ячейки В2, заданное по умолчанию. Какая формула возникнет в ячейке C4 при копировании в нее содержимого ячейки С3? Предполагается, что все остальные ячейки рабочего листа являются пустыми.

Контрольные вопросы

7.21. В ячейках А1, А2, А3 находятся числа 3, 4, 5 соответственно. В ячейку В1 введена формула =ЕСЛИ(РАНГ(5; A1: A3; 1)< =1; СРЗНАЧ(1; 2; 3); МИН(1; 8; 7)). Какое значение в ней отобразится?

7.22. Ячейка А1 содержит число 18. Какое значение возвращает функция =A$1*ЕСЛИ(A$1< 10; 1%; 1)*ЕСЛИ(И(A$1> =10; A$1< 20); 2%; 1)?

7.23. В ячейках А1, А2, А3, А4 находятся текстовые значения а, б, а, в соответственно. В диапазоне В1: В4 находятся числовые значения 1, 2, 3, 4 соответственно. Какое значение возвращает формула =ЕСЛИ(СУММ-ЕСЛИ(A1: A4; " а"; B1: B4)> =4; МАКС(B1: B4); СРЗНАЧ(1; 9))?

7.24. Ячейка А1 содержит число 10. Какое значение возвращает функция =A1*(ЕСЛИ(A$1< 10; 10%)+ ЕСЛИ(И(A1> =10; A1< 20); 20%))?

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

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

7.27. Какой оператор следует использовать для конкатенации текстовых строк?

7.28. В ячейках А1, А2, В1, В2 находятся соответственно числа 1, 2, 3, 4. В ячейку С3 введена формула =А$1+$В1. Какая формула возникнет в ячейке D3 при копировании в нее содержимого ячейки С3? Предполагается, что все остальные ячейки рабочего листа являются пустыми.

7.29. В ячейках А1 и В2 находятся соответственно числа 1 и 4. В ячейку С3 введена формула =А1+В2. Какое значение возникнет в ячейке D3 при перемещении блока В2: С3 вправо на одну ячейку? Предполагается, что все остальные ячейки рабочего листа являются пустыми.

Задания для лабораторных работ (для всех вариантов)

1. Постановка задачи: Для рабочих, занятых на двух видах работ, введена почасовая система оплаты труда, зависящая от вида выполняемых работ. Кроме основного заработка, начисляемого исходя из общего объема отработанного времени, определены премиальные, начисляемые как процент от основного заработка за вычетом удержаний. Предполагается, что удержания с премиальных меньше удержаний с основного заработка. Соответствующие числовые значения и прочие сведения указаны ниже в таблицах входных данных. Определить суммы к выплате, получаемые из кассы каждым рабочим.

Примечание: Задача носит учебный характер и может не соответствовать реальной ситуации.

2. Постановка задачи: Имеются данные об объемах реализации трех товаров по трем различным ценам в одном магазине за второй квартал года (числовые значения см. ниже). Требуется найти:

· Ежемесячную выручку от реализации каждого товара.

· Итоговую выручку от реализации всех товаров за каждый месяц и в целом за квартал, а также каждого товара в целом за квартал.

· Среднюю выручку от реализации всех товаров за каждый месяц и среднюю выручку от реализации каждого товара за квартал.

· Среднюю суммарную выручку по всем товарам за квартал.

· Среднюю ежемесячную выручку по всем товарам за квартал.

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

· Максимальную и минимальную выручку от реализации каждого товара за квартал.

· Процент месячной выручки от реализации всех товаров в общей суммарной выручке магазина за квартал.

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

Кроме этого, требуется ранжировать товары по объему полученной от их реализации выручки в каждом месяце и в целом за квартал (естественно, лучшим считается тот товар, от реализации которого возникла максимальная выручка).

Входные данные:

Блоку с числовыми значениями цен назначьте имя Цена, б локу с числовыми значениями объемов реализации назначьте имя Объем_реализации. Используйте эти имена в соответствующей формуле массива при вычислении выручки от реализации каждого товара в каждом месяце квартала. По полученным данным постройте какие-либо гистограмму и круговую диаграмму.

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

4. Решите предыдущую задачу в ситуации, когда исходные четыре числа представлены не массивом ссылок, а массивом констант.

5. Используя понятие главной формулы и смешанные ссылки обоих форматов, составить двухмерную таблицу значений функции f(x, y) при условии, что независимые переменные х и у принимают значения от 1 до 10 с шагом 1. Значения переменной х разместить в столбце, значения переменной у – в строке, значения функции f(x, y) – на пересечении соответствующих строк и столбцов. Решить задачу также и с использованием формул массивов.

Вариант	f(x, y)	Вариант	f(x, y)

6. Выбрав отрезок и шаг табуляции, создать ряды данных для двух произвольных непрерывных функций. Построить их графики на одной диаграмме.

7. Придумать какую-либо разрывную функцию с разрывом первого рода в одной точке ее области определения. Для этой функции создать ряд данных и построить ее график.

8. Используя функцию СЦЕПИТЬ, сцепить слово месяц со списком автозаполнения, состоящим из названий месяцев. После создания нового списка, скопируйте только значения полученного блока формул, применяя 1) команду Правка / Специальная вставка и 2) процедуру D& D_ПКМ …è ….

9. Составить таблицу истинности функции И для случая двух аргументов.

10. Введите в некоторую ячейку произвольное число. В другую ячейку введите формулу, возвращающую значение ИСТИНА, если число принадлежит интервалу (-6, 6). В противном случае эта формула должна возвратить значение ЛОЖЬ.

11. Введите в три ячейки произвольные числа. В четвертую ячейку введите формулу, возвращающую значение ИСТИНА, если каждое из чисел является отрицательным. В противном случае эта формула должна возвратить значение ЛОЖЬ. Задачу решить двумя способами: 1) используя функцию И, 2) без применения логических функций, учитывая, что нечетное число отрицательных чисел отрицательно тогда и только тогда, когда их произведение отрицательно; в этом случае результирующими значениями должны быть числа 1 или 0 вместо логических значений ИСТИНА или ЛОЖЬ.

12. Введите в три ячейки произвольные числа. В четвертую ячейку введите формулу, возвращающую значение ИСТИНА, если хотя бы одно из чисел является отрицательным. В противном случае эта формула должна возвратить значение ЛОЖЬ.

13. Следующие индивидуальные задания сначала выполните устно, а затем проверьте правильность результата решением соответствующей задачи в Excel. Ввод формул реализуйте двумя способами: с использованием Мастера функций и клавиатурным набором. При вводе формул с клавиатуры следуйте правилам:

· сначала вводится имя функции (имена функций можно вводить прописными буквами),

· затем вводятся круглые скобки в парах (т.е. ()),

· затем - разделители полей (т.е. ;) в требуемом по синтаксису каждой функции количестве,

· затем заполняются поля (т.е. промежутки между разделителями),

· для вложенных функций выполняются аналогичные действия,

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

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

14. Постановка задачи: Предположим, что рабочий лист содержит в ячейках B2: B7 фактические выпуски (в тыс. руб.) продукции цехов некоторого предприятия за полугодие. Ячейки C2: C7 содержат плановые показатели выпуска продукции по цехам за то же время. Требуется записать формулы для проверки соответствия плану фактических выпусков продукции каждого цеха, генерируя в ячейках D2: D7 тексты сообщений План превышен на или План недовыполнен на. В ячейках Е2: Е7 – указать соответствующее превышение фактических выпусков над плановыми показателями или недовыполнение плана. В ячейках F2: F7 – указать, на сколько ежемесячные фактические выпуски превышают соответствующие плановые показатели (+ отвечает превышению факта над планом, - отвечает недовыполнению плана). Рассчитать итоговые величины за полугодие по цехам:

· Число цехов, превысивших план.

· Число цехов, недовыполнивших план.

· Общую сумму превышения факта над планом по цехам, перевыполнившим план.

· Общую сумму недовыполнения плана по цехам, не выполнившим плановые нормативы.

· Итоговую сумму Факт минус План по предприятию.

Основную таблицу построить по образцу:

По графам Факт и План постройте 1)две гистограммы и расположите их на одной диаграмме, 2) какую-либо одну круговую диаграмму. Поэкспериментируйте с построением различных типов диаграмм, используя Мастер диаграмм.

Ответы на вопросы

1.1. 5. 1.2. Правка. 1.3. Укажите. 1.4. Нет. 1.5. Да. 1.6. Отображение списка имен зависит от того, в каком режиме находится Ехсеl. 1.7. Одному экрану. 1.8. Эта ячейка находится на пересечении крайнего правого столбца и крайней нижней строки, в которые когда-либо вводились данные на рабочем листе в одном сеансе работы в Ехсеl. 2.1. 2. 2.2. 3. 2.3. Да, всегда. 2.4. 12. 2.5. Общий. 2.6. Ее левой границе. 2.7. Да, всегда. 2.8. Нет, никогда. 2.9. Да, в ряде случаев. 2.10. Ехсеl сам осуществляет перенос целых слов внутри ячейки, настраивая ее высоту. 3.1. Для их последующей обработки. 3.2. На пересечении бордюров строк и столбцов листа.
3.3. < Ctrl>. 3.4. < Shift>. 3.5. Выделяет два листа: предыдущий и следующий. 3.6. К выделению диапазона. 4.1. 2. 4.2. 5. 4.3. Нет. 4.4. Выравнивание / Отображение / Переносить по словам. 4.5. < Alt> + < Enter>.
4.6. < Tab>. 4.7. Да, так бывает всегда. 4.8. Формат / Ячейки. 4.9. < Esc>. 4.10. < Delete>. 4.11. < Ctrl>. 4.12. Правка/Специальная вставка.
4.13. < Shift>. 4.14. < Ctrl> + < Shift>. 5.1. число нуль. 5.2. по правому краю. 5.3. 1 января 1900 г. 5.4. Формат/Ячейка. 5.5. < F2>. 5.6. пробел. 5.7. Выделить ячейку, вызвать диалоговое окно Формат ячейки и посмотреть, что отображается на вкладке Число. 5.8. 1, 4665. 5.9. Имя ячейки. 5.10. 1, 47. 6.1. Сервис /Параметры. 6.2. < Ctrl>. 6.3. Правка/Заполнить. 6.4. Правка/Заполнить/ Прогрессия. Да. Нет. 6.5. Да. Они будут уничтожены.
6.6. Нужно выделить ячейки, содержащие начальные члены ряда. 6.7. Нет. Да. 6.8. Да. 6.9. 1. 6.10. Как разность между вторым и первым членами (порядок определяется направлением перемещения маркера заполнения). 6.11. Вверх или влево. Вниз или вправо. 6.12. 2-я Глава Параграф1.
6.13. 1я Глава Параграф2. 6.14. 1я Глава Параграф0. 7.1. Формула.
7.2. 8. 7.3. %. 7.4. 4. 7.5. < Delete>. 7.6. Нет. 7.7. Нет. 7.8. Да. 7.9. Нет.
7.10. Нет. 7.11. Да. 7.12. Иногда Да, иногда Нет. 7.13. Нет. 7.14. Да.
7.15. < Ctrl> + < Shift> + < Enter>. 7.16. 3, 2. 7.17. 0. 7.18. 2, 5. 7.19. =A$1+$B1. 7.20. =A$1+ ИМЯ_В2.

Вопросы к зачету

1. Базовые понятия среды Excel: меню, панели инструментов, назначение, структура и типы отображений строки состояния и строки формул, типы отображений экранного курсора мыши.

2. Элементарные приемы перемещения по экрану, листу и открытой книге Excel. Перемещение с помощью «горячих» клавиш.

3. Основные средства и команды управления данными. Описание команды Формат / Ячейки.

4. Характеристика типов меню, команд и основных форматов данных, применяемых в Excel.

5. Методы и приемы выделения ячеек, смежных и несмежных диапазонов, строк, столбцов и листов открытой книги Excel. Способы выделения с помощью «горячих» клавиш.

6. Типы данных и константные категории Excel. Правила работы с текстовыми и числовыми данными.

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

8. Методы перемещения и копирования содержимого ячейки. Копирование в строке формул. Копирование форматов. Характеристика команды Правка / Специальная вставка.

9. Одношаговый ввод значения или формулы в диапазон ячеек. Распределение многосимвольного текста по выделенной области.

10. Процедура добавления ячеек. Команда Вставка / Ячейки.

11. Очистка и удаление частей таблицы. Перемещение и копирование частей таблицы.

12. Общие правила работы с текстом, числами, датами и временем.

13. Понятие и правила автозаполнения. Характеристика команды Правка / Заполнить / Прогрессия.

14. Основные понятия, связанные с формулами и функциями: Формула, Синтаксис формулы, операнд, оператор, функция, аргумент, вложенная функция, приоритет операторов. Описание Мастера функций.

15. Применение операторов и порядок выполнения действий в формулах. Различие операторов Унарный_минус и Вычитание. Адресные операторы Ехсеl.

16. Арифметические и текстовый операторы. Операторы сравнения.

17. Изменения, происходящие в формулах при перемещении влияющих и зависимых ячеек.

18. Относительные, абсолютные и смешанные ссылки. Понятия зависимой и влияющей ячеек. Роль клавиши < F4>.

19. Изменения, происходящие в формулах при копировании зависимых ячеек.

20. Перенастройка формул при копировании и перемещении блоков с формулами.

21. Амперсант и конкатенация. Характеристика функции Сцепить.

22. Способы скрытия “лишних” данных. Понятие скрытого формата.

23. Ввод формулы, ссылающейся на другой лист.

24. Методы вычисления значений функций двух переменных в конечном числе точек.

25. Абсолютные имена. Различие команд Вставка / Имя / Присвоить и Вставка / Имя / Создать. Возможность использования имен в формулах в качестве ссылок на (именованные) ячейки и диапазоны.

26. Достоинства и недостатки использования имен в формулах.

27. Формулы массивов. Диапазон массива. Массивы констант. Методы изменения формулы массивов и очистки диапазона массива от формулы

28. Изменение диапазона массива при сохранении «старой» формулы массива (или при ее незначительной подправке).

29. Логические функции.

30. Синтаксис функций ЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИ, РАНГ, СРЗНАЧ. Отличие функций СУММЕСЛИ и СЧЁТЕСЛИ от функций СУММ(ЕСЛИ(…)) и СЧЁТ(ЕСЛИ(…)).

Литература

1. Карлберг К. Excel 5 для Windows в вопросах и ответах. С.-Пб.: BHV, 1995.

2. Попов А. Excel: Практическое руководство. М.: ДЕСС КОМ, 2000.

3. Гарнаев А. MS Excel и VBA. Использование в экономике и финансах. С.-Пб.: BHV, 1999.

4. Лавренов С.М. Excel: Сборник примеров и задач. М.: Финансы и статистика, 2000.

5. Додж М., Кината К., Стинсон К. Эффективная работа с Excel для Windows. С.-Пб.: BHV, 1997.

6. Комягин В.Б., Коцюбинский А.О. Excel for Windows в примерах. М.: Нолидж, 1996.

7. Долголаптев В.Г. Работа в Excel для Windows 95 на примерах М.: Бином, 1995.

8. Лукасевич И.Я. Анализ финансовых операций. М.: ЮНИТИ, 1999.

9. Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. М.:. Филинъ, 1999.

10. Курицкий Б. Поиск оптимальных решений средствами Excel в примерах. С.-П.: BHV, 1997.

11. Шориков А.Ф., Тюлюкин В.А., Иванов В.М. Основы информатики и введение в специальность. Екатеринбург., УрГЭУ, 2000.

* Кроме режима Готово в Excel используются также режимы Укажите, Правка и Ввод. Отметим, что в режиме Ввода константных значений в поле имени отображается адрес активной ячейки. При этом «карман» поля имени закрыт. В режиме Ввода формулы в поле имени также отображается адрес активной ячейки, но «карман» можно открыть и увидеть в нем список последних десяти использованных функций Ехсеl.

* Эта команда активизируется только при подключенном принтере.

* Эта команда активизируется только при подключенном принтере

* Пункты 2 и 3 можно поменять местами, т.е. сначала подвести курсор к маркеру заполнения так, чтобы он принял вид, а затем нажать клавишу < Shift> (при этом форма курсора меняется на).

* Пункты 2 и 3 можно поменять местами, т.е. сначала подвести курсор к маркеру заполнения так, чтобы он принял вид, а затем нажать клавишу < Shift> (при этом форма курсора меняется на).

* Чтобы использовать числа с большим количеством разрядов, следует использовать встроенный экспоненциальный числовой (экспоненциальное представление числа) или пользовательский формат.

* В текстовой строке «числа» находятся в крайних левой и правой позициях

* Вместо введения знака = с клавиатуры можно нажать кнопку < Изменить формулу> в строке формул или кнопку < Вставка функции> на панели инструментов Стандартная. При этом знак равенства вставляется автоматически.

* О смысле введения знака $ в формулу будет сказано ниже

* Предполагается, что копирование сопровождается вставкой формулы в новую позицию

** т.е. одинаковые буквенные и числовые обозначения

* Символ & находится на клавише с цифрой 7 основной клавиатуры

* Поскольку пользовательские таблицы содержат по три столбца, то и сами метки должны быть отделены одна от другой соответствующим числом пустых столбцов Ехсеl. Следовательно, при их выделении нужно удерживать клавишу < Ctrl>.

* Точно также производится перемещение целых листов, но в этом случае не нужно удерживать клавишу < Ctrl>.

* Символ возведения в степень ^ находится на клавише с цифрой 6 основной клавиатуры

* Более точные определения будут обсуждаться ниже

* которые не надо набирать с клавиатуры: они возникают автоматически при фиксации ввода формулы массива

* Этот тип диаграмм чаще всего используется при построении графиков функций

* Вместо команд Вставка / Имя / Присвоить удобнее использовать горячие клавиши < Ctrl> + < F3>