Вычисления с помощью мастера функций

 

1 Добавьте в книгу Лабораторные новый лист и назовите его Вычисления1. Внесите информацию об успеваемости и посещаемости студентов (рис. 3.9). Выполните статистическую обработку данных: рассчитайте количество пропущенных занятий, средний балл, его максимальное и минимальное значение, среднюю успеваемость.

 

 

Рис. 3.9. Исходные данные об успеваемости студентов

 

Выполнение:

1.1 Текст комментариев Проп. зан. и Ср. балл разбейте на две строки с помощью клавиатурной комбинации Alt + Enter. Объедините ячейки Q1:Q2, R1:R2, K15:Q15, K16:Q16, K17:Q17.

 

1.2 Использование статистических функций: в ячейку Q3 с помощью мастера функций (меню Вставка / Функция или кнопка ) введите формулу: =СЧЁТЕСЛИ(В3:Р3;"=н") (категория функции СЧЁТЕСЛИ - Статистические). При вводе второго аргумента функции (в поле «Условие») кавычки можно не указывать; они будут добавлены автоматически. Эта функция позволяет найти число ячеек из данного диапазона, содержимое которых удовлетворяет указанному условию (в данном случае равно «н»). Аналогичную формулу введите в ячейку В12: =СЧЁТЕСЛИ(В3:В11;"=н"). В ячейку В13 введите формулу =СЧЁТЕСЛИ(В3:В11;">0") (формула позволяет найти количество ячеек из диапазона В3:В11, содержащих положительные числа.

 

1.3 В ячейку R3 с помощью мастера функций введите формулу =СРЗНАЧ(В3:Р3) (СРЗНАЧ - статистическая функция, позволяющая найти среднее арифметическое чисел из указанного диапазона; при этом ячейки, не содержащие чисел, игнорируются).

 

1.4 В ячейку R15 с помощью мастера функций введите формулу =МАКС(R3:R11), в ячейку R16 - формулу =МИН(R3:R11) (статистические функции МАКС и МИН вычисляют соответственно максимальное и минимальное числовое значение в указанном диапазоне ячеек). В ячейку R17 введите формулу = СРЗНАЧ(R3:R11).

 

1.5 С помощью маркера заполнения скопируйте введенные формулы в остальные ячейки соответствующих строк (столбцов).

 

1.6 Для нахождения общего количества отсутствующих (Q12) и опрошенных (Q13) воспользуйтесь автосуммированием.

 

1.7 Проверьте, что суммирование данных по столбцу Q и по строке 12 приводит к одному и тому же результату.

 

1.8 Выделите требуемые результирующие значения полужирным шрифтом. В итоге таблица примет вид (рис. 3.10).

 

 

 

Рис. 3.10. Итоговая таблица успеваемости

 

 

2 На новом листе Вычисления2 создайте таблицу с результатами сдачи вступительных экзаменов (рис. 3.11). Вычислите средний балл для каждого из абитуриентов и укажите, каких из них можно зачислить. Рассчитайте, сколько человек зачислено и не зачислено.

 

 

Рис. 3.11. Данные о результатах сдачи вступительных экзаменов

 

Указания:

 

2.1 Использование логических функций: в ячейку F3 с помощью мастера функций введите формулу: =ЕСЛИ(E3>=$D$1;"ДА";"НЕТ") (категория функции ЕСЛИ - «логические»). Скопируйте полученную формулу во все ячейки столбца F с помощью маркера заполнения (при этом адрес ячейки D1 не будет изменен благодаря использованию абсолютной адресации).

 

2.2 В ячейку F12 введите формулу: =СЧЁТЕСЛИ(F3:F11;"=ДА") (эта формула находит количество ячеек в столбце F, содержащих строку «ДА»). Аналогично введите формулу в ячейку F13. Таблица примет вид (рис. 3.12).

 

 

Рис. 3.12. Итоговая таблица о зачислении

 

2.3 Протестируйте созданную таблицу, изменяя значения проходного балла и экзаменационных оценок.

 

3 Постройте таблицу (табл. 3.4) (начните, например, со строки 15). Рассчитайте стоимость покупки с учетом 10 % скидки, которая назначается, если покупка состоит более, чем из 5 наименований товаров или стоимость покупки превышает K рублей. Значение K и данные, помеченные «*», задайте произвольно. В формуле используйте логические функции.

 

 

Таблица 3.4

Расчет стоимости купленных товаров

 

№	Покупатель	Количество наименований купленных товаров	Стоимость покупки	Стоимость покупки с учетом скидки
	Власов	*	*
	Горбунков	*	*
	Доронин	*	*
	Захарова	*	*
	Иванов	*	*
	Кузнецов	*	*
		ЗначениеК:	*

 

Указания:

• В данном случае аргументом функции ЕСЛИ будет логическая функция ИЛИ. Аргументами же функции ИЛИ будут оба условия, при которых назначается скидка: ИЛИ(С16>5;D16>$D$22), где С16 - первое значение столбца «Количество наименований купленных товаров», D16 - первое значение столбца «Стоимость покупки», D22 - ячейка, в которой находится значение K. Эту формулу следует записать в поле Логическое_выражение функции ЕСЛИ. В поле Значение_если_истина надо ввести значение, которое возвращается, если логическое выражение имеет значение истина. В нашем случае это стоимость покупки со скидкой, равной 10 %, т.е. 0,9*D16. В поле Значение_если_ложь вводится значение, которое возвращается, если логическое выражение имеет значение ложь. В данном случае это стоимость покупки без скидки, т.е. D16. Таким образом, формула расчета стоимости со скидкой будет иметь вид: =ЕСЛИ(ИЛИ(С16>5;D16>$D$22);0,9*D16;D16). Далее следует скопировать эту формулу во все ячейки столбца «Стоимость покупки с учетом скидки».

 

4 Создайте таблицу (табл. 3.5). Поставьте отметку о зачислении в баскетбольную секцию, если туда принимают детей не старше 13 лет и ростом не менее 160 см. Если условия соблюдаются, то в отметке о зачислении напишите «да», иначе «нет». В записи формул используйте логические функции.

 

 

Таблица 3.5

Результаты зачисления в секцию

 

№	Фамилия	Возраст	Рост	Отметка о зачислении
	Власов
	Горбунков
	Доронин
	Захарова
	Иванов
	Кузнецов
	Морозов