Практическое занятие 3. Работа с встроенными функциями.

 

Задание 1.

 

Ввести расчетную таблицу (рис. 116) и рассчитать комиссионные от продаж.

При продаже товаров на сумму до 150 тыс. руб. менеджер получает комиссионные в размере 5% от объема продаж, а при продаже на сумму более 150 т.р. – 6% от объема продаж. При выполнении задания присвоить имена ячейкам В2, В3, В4 и ввести их в соответствующие формулы.

 

 

Рис. 116

Задание 2.

Ввести расчетную таблицу (рис. 117) и определить оценку при решении теста при условии, что более 25 правильных ответов оценивается в 5 баллов, более 20 – 4 балла, менее 20 – 3 балла.

 

 

Рис. 117

 

Скорректировать расчетную формулу, дополнив ее условием для оценки в 2 балла, если количество правильных ответов меньше или равно15.

 

Задание 3.

Ввести таблицу расчета (рис. 118) и выполнить указания, приведенные в задании

Рис. 118

 

Добавить справа столбец с именем Количество и столбец с именем Стоимость. В столбец Количество ввести произвольные значения как больше 5, так и менее 5 шт. Для расчета стоимости как произведения цены на количество использовать функцию ЕСЛИ.

 

Задание 4.

 

Создать в Excelисходную таблицу (рис. 119) и таблицу результатов ее обработки (рис. 120).

 

Рис. 119.

 

 

Рис. 120.

 

В ячейку G4 ввести формулу для расчета максимального количества осадков (функция МАКС) за три года. В ячейку G5 ввести формулу для расчета минимального количества осадков за три года (функция МИН). Функции МАКС и МИН содержатся вразделе статистических функций. В ячейку G6 ввести формулу для расчета суммарного количества осадков за три года (функция СУММ). Функция СУММ содержится в разделе математических функций. В ячейку G7 ввести формулу для расчета среднего количества осадков за три года (суммарное количество осадков разделить на общее число месяцев).

В ячейку G8 ввести формулу для расчета количества засушливых месяцев за три года (в засушливом месяце осадков меньше 10 мм). Для расчета количества засушливых месяцев за три года использовать формулу СЧЕТЕСЛИ (интервал, критерий). Критерием является условие: < 10.

Функция СЧЕТЕСЛИ содержится в разделе математических функций.

Для расчета количества месяцев в пределах >20 и <80 и вне нормы <10 и >100 нужно создать две вспомогательные таблицы.

 

Рис. 121.

 

Первая таблица показана на рис. 121. В ячейку таблицы В18 ввести логическую функцию ЕСЛИ (И(B6>20;B6<80);1;0).

Функция имеет следующий смысл: Логическое условие И объединяет два неравенства ( B6>20 и B6<80), которые должны выполняться одновременно. Если они оба выполняются, то в ячейку заносится значение 1. В противном случае - 0.

Скопировать содержимое ячейки В18 в остальные ячейки области В18:D29. В ячейку G9 ввести формулу СЧЁТЕСЛИ(B18:D29;"=1"). Будет подсчитываться количество ячеек, в которых значение равно 1 (т.е. количество месяцев, когда осадки в пределах >20 и <80).

Самостоятельно создать вторую вспомогательную таблицу, ввести в нее необходимую логическую функцию для определения месяцев вне нормы (условие <10 и >100). Эти условия должны объединяться логическим условием или. ВячейкуG10 ввести формулу аналогично ячейке G9 с ссылкой на вторую вспомогательную таблицу.

 

Практическое занятие 4. Работа со списками

 

Создать в Excel следующую таблицу (рис. 122).

Рис. 122

 

В таблице используются следующие единицы измерения: Период - земные годы, Расстояние - среднее расстояние от Солнца в млн. км, Диаметр - тыс. км, Масса - *10²⁴ кг.

Задание 1.

произвести сортировку данных по алфавиту в первом столбце таблицы;

произвести сортировку данных в таблице по возрастанию диаметра.

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

Задание 2.

сиспользованием автофильтра осуществить поиск планет, начинающихся на букву «С» или «Ю» с массой менее 600 * 10²⁴ кг;

сиспользованием автофильтра осуществить поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 4*10²⁴ кг;

сиспользованием автофильтра осуществить поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3 * 10²⁴ кг до 500 * 10²⁴ кг, а также не более 2 спутников;

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

Задание 3.

сиспользованием усиленного фильтра осуществить поиск планет, имеющих период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км;

сиспользованием усиленного фильтра осуществить поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих один спутник.

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

Задание 4.

с использованием формы удалить и вставить информацию по планете Нептун;

с использованием формы осуществить поиск планеты, имеющей период обращения 11,86 земных лет;

вставить форму поиска указанной в предыдущем пункте планеты в документ Word.