Практическая работа 2.
Относительная и абсолютная адресация. Статистические функции (МИН, МАКС, СРЗНАЧ).
Выполнив задания этой темы, вы научитесь:
a. Выполнять операции по копированию, перемещению и автозаполнению отдельных ячеек и диапазонов.
b. Различать виды ссылок (абсолютная, относительная, смешанная)
c. Определять вид ссылки, необходимой для использования в расчетах.
d. Использовать в расчетах встроенные математические и статистические функции Excel.
Упражнение 1: Работа с диапазонами.
Технология выполнения задания:
1. Создайте таблицу следующего вида:
2. Номера позиций введите, используя маркер заполнения.
3. Вставьте текущую дату, выполнив следующие действия:
· Установите курсор в ячейку G2.
· На ленте Формулы в группе Библиотека функций щелкните по кнопке Дата и время;
· в появившемся диалоговом окне выберите функцию СЕГОДНЯ. Нажмите ОК.
4. В ячейку С3 внесите число – курс доллара;
5. Введите, используя абсолютную адресацию, в ячейку D5 формулу для вычисления цены товара в рублях (=C5*$C$3), затем за маркер заполнения скопируйте ее до ячейки D14. Обратите внимание! При автозаполнении адрес ячейки С3 не меняется, т. к. установлена абсолютная ссылка.
Для создания абсолютной ссылки удобно используйте клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот. Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
6. В ячейку G5 введите формулу (=C5*F5), затем за маркер заполнения скопируйте ее до ячейки G14.
7. Используя автосуммирование, вычислите «Итого» в ячейке G15.
8. В диапазонах С5:D14 и G5:G15 установите формат с разделителями. Для этого:
· выделите диапазоны ячеек С5:D14 и G5:G15;
· на ленте Главная выберите группу Число – Числовые форматы: Числовой – Число десятичных знаков: 0, в окне Разделителя групп разрядов ( ) установите флажок. Нажмите ОК.
9. Присвойте ячейкам С3, D5:D14 и G5:G15 денежный формат Обозначение: р.
10. Нанесите сетку таблицы там, где это необходимо и выполните заливку заголовка таблицы: вкладка Главная на ленте, группа Шрифт.
11. Подсчитайте среднее, максимальное и минимальное значения с использованием функций СРЗНАЧ, МАКС и МИН. Для этого выполните следующие действия:
· щелкните мышью по ячейке С15;
· в Библиотеке функций выберите кнопку Другие функции – Статистические – функция СРЗНАЧ и подтвердите диапазон ячеек C5:C14. В ячейке С15 появится формула =СРЗНАЧ(C5:C14).
12. Аналогично функции задаются и в ячейках С16 и С17.
Образец выполнения упражнения 1 в режиме отображения формул
Самостоятельная работа 1. История борьбы с огнедышащим драконом
В пещере у реки поселился огнедышащий дракон.
Всех, кто пытался его прогнать, он прогонял сам, полыхая на них огнем. Количество полыханий зависело от того, на кого надо полыхать. На царевича дракон полыхал 5 раз, на королевича — 4 раза, на простого рыцаря — 3.
За первые сто лет дракона пытались прогнать 2 царевича, 3 королевича и 5 простых рыцарей. За второе столетие на него покушались 3 царевича, 2 королевича и 7 простых рыцарей. За третий век дракона беспокоили 7 царевичей, 5 королевичей и 6 простых рыцарей. За следующее столетие дракону пришлось иметь дело с 3 царевичами, 6 королевичами и 10 простыми рыцарями. После чего дракона в конце концов оставили в покое и объявили гору, на которой он жил, заповедником для охраны редких видов животных.
Построить электронную таблицу, из которой будет видно: сколько человек пытались прогнать дракона за каждое из столетий в отдельности и за все 4 века вместе; сколько среди них было царевичей, сколько королевичей и сколько простых рыцарей; сколько раз дракону пришлось полыхать на них огнем в течение каждого века и за все 4 столетия вместе; сколько полыханий досталось царевичам, сколько королевичам и сколько простым рыцарям.
Решение
Прежде всего необходимо продумать структуру таблицы и разместить в ней имеющуюся информацию. В приведенном ниже решении информация о царевичах, королевичах и рыцарях занесена в строки, а столбцы содержат сведения о сражениях по векам. Нижняя строка и последние два столбца содержат итоговую информацию согласно условию задачи. Информация о полыханиях, приходящимся на одного царевича, королевича, рыцаря, вынесена отдельно. Это связано с тем, что при изменении этих данных достаточно будет изменить их в указанных ячейках, не изменяя при этом всех формул.
Таблица с результатами расчётов
Таблица в режиме отображения формул 
Сохраните работу как «История борьбы с огнедышащим драконом».
Самостоятельная работа 2.
Во время каникул ребята отправились путешествовать на разных видах транспорта.
Коля проплыл 50 км на пароходе, проехал 40 км на поезде и пролекм на самолете. Вася проплыл на пароходе 100 км, проехал на поезде 20 км и пролетел на самолете 60 км. Толя пролетел на самолете 200 км, проехал поездом 10 км и проплыл на пароходе 25 км. Маша проехала на поезде 30 км, пролетела на самолете 100 км и проплыла на пароходе 60 км.
Задание
1. Построить на основе вышеперечисленных данных электронную таблицу.
2. Добавить к таблице столбец, в котором будет отображаться общее количество километров, которое проехал каждый из ребят.
3. Вычислить общее количество километров, которое ребята проехали на поезде, пролетели на самолете и проплыли на пароходе (на каждом виде транспорта по отдельности).
4. Вычислить суммарное количество километров, которое дети проехали в сумме.
5. Определить максимальное и минимальное количество километров, которое дети проехали на поезде.
6. Определить среднее количество километров, которое дети проплыли на пароходе.
7. Внести в таблицу следующие изменения: Коля проехал на поезде 150 км, а Вася пролетел на самолете 200 км и выделить другим цветом ячейки, в которых произошли изменения.
8. Отсортировать таблицу по убыванию количества километров.
9. Сохраните работу как «Каникулы»
