Относительные и абсолютные ссылки. Функции. Лабораторная работа№3
Приемы обработки числовой информации в среде Excel
Задание 1
1) Запустите приложение MS Excel. В заголовке текущего окна появится название табличного документа «Книга1». Дайте ему имя «Отметки», выполнив команду Файл /Сохранить как … (в свою рабочую папку).
2) Введите в ячейки А1 и А2 заголовок таблицы, заполнив ячейки своими данными. Выполните объединение ячеек а) A1:H1 и б) A2:H2
3) Введите текстовые значения в ячейки А3:А8 и В3:H3.
4) Заполните отметками диапазон ячеек B4:G8. Подумайте, как выставляется годовая отметка.
5) В ячейку Н4 введите формулу =МИН(F4;G4).
6) Скопируйте формулу из ячейки H4 в ячейки диапазона H5:H8. Алгоритм копирования:
a) Выберите ячейку H4, содержащую копируемую формулу.
b) Установите указатель на рамку выделения и нажмите кнопку мыши.
c) Для копирования ячейки перетащите выделение, удерживая нажатой кнопку мыши, в ячейки блока H5:H8.
7) В ячейку F9 введите текст «Средний балл:», объедините ячейки блока F9:G9.
8) В ячейку H9 введите формулу =СРЗНАЧ(H4:H8); установите формат ячейки Числовой / Число десятичных знаков возьмите равным 1.
9) Выполните форматирование данных в ячейках и границ (см. образец).
| A
| B
| C
| D
| E
| F
| G
| H
|
| ОТМЕТКИ
|
| за 2012/2013 учебный год
|
| Предмет
| I
| II
| III
| IV
| За год
| Экзамен
| Итоговая
|
| Русский язык
|
|
|
|
|
|
| 4
|
| Литература
|
|
|
|
|
|
|
|
| Алгебра
|
|
|
|
|
|
|
|
| Геометрия
|
|
|
|
|
|
|
|
| Информатика
|
|
|
|
|
|
|
|
|
|
|
|
|
| Средний балл:
| 4,4
|
10) Постройте диаграмму успеваемости по предметам
a) Выделите ячейки A3:E8.
b) На панели инструментов щелкните по значку диаграммы
c) Следуя указаниям мастера постройте гистограмму.
11) Постройте диаграмму успеваемости по предметам в первой и четвертой четверти:
a) Удерживая CTRL выделите столбцы А3:А8, В3:В8, Е3:Е8.
b) С помощью инструмента постройте гистограмму.
12) Постройте круговую диаграмму итоговой успеваемости по предметам.
Задание 2
Относительные и абсолютные ссылки. Функции.
Если при изменении положения формулы (при копировании и распространении) автоматически меняются ссылки на ячейки относительно исходной, то такие ссылки называются относительными.
Относительная ссылкаиспользуется в формуле для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке.
Относительные ссылки имеют следующий вид: А1, В1 и т.п..
I.Подготовьте таблицу по образцу, поместив ее в ячейках A1 – E3.
В ячейке В3 находится цена за единицу товара.
В ячейке C3 - формула =B3 * C2 (цена за единицу товара умножить на количество).
| A
| B
| C
| D
| E
|
| Наименование товара
| Количество
|
| Мороженное
|
|
|
|
|
| Стоимость (в руб.)
| 12,70
| =B3 * C2
|
|
|
С помощью маркера заполнения распространите формулу вправо для получения стоимости товара за 3 и 4 единицы. Сравните свой результат с приведенным ниже.
| A
| B
| C
| D
| E
|
| Наименование товара
| Количество
|
| Мороженное
|
|
|
|
|
| Стоимость (в руб.)
| 12,70
| 25,40
| 76,20
| 304,80
|
Можно заметить, что вычисленная по формуле стоимость товара за три единицы неверна. Если выделить ячейку D3, то в Строке формул появится формула C3*D2, а должна быть формула B3*D2.
В результате распространения формулы вправо изменились и ссылки. А в нашем примере необходимо было каждый раз количество товара умножать на цену за единицу, то есть на содержимое ячейки B3.
В таких случаях, составляя формулу, применяют абсолютные ссылки. При перемещении или копировании формулы абсолютные ссылки не изменяются, ячейка фиксируется. В то время как относительные ссылки, с которыми мы работали до сих пор, автоматически обновляются в зависимости от нового положения.
Абсолютные ссылки имеют вид: $F$9; $C$45. Для фиксации координат применяется знак $.
Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке C3 - формула =$B$3 * C2.
Абсолютная ссылкаиспользуется для указания адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки.
Абсолютные ссылки имеют вид: $А$5, $F$5, $G$3 и т.п.
Примечание
Для того, чтобы относительную ссылку преобразовать в абсолютную, достаточно после ввода ссылки нажать клавишу F4 – и знаки доллара появятся автоматически.
Задание 3
Подготовьте таблицу для начисления пени в соответствии с образцом.
| (1) Оплата коммунальных услуг задержана на
|
| дней
|
| Вид оплаты
| Начисленная сумма
| Пени
| Всего к оплате
| (2) Квартплата
|
|
|
| Газ
|
|
|
| Электричество
|
|
|
| Телефон
|
|
|
|
| | | | | | |
Пенивысчитывается по формуле – 1% от начисленной суммы за каждый задержанный день.
Всего к оплате считается как сумма начисления плюс пени.
Добавьте в таблицу строку для подсчета итоговых показателей:
всего начислено, всего пени, всего к оплате.
Задание 4
1) Выполните ввод исходных данных по образцу.
2) Введите соответствующие смыслу формулы в ячейки E4 и F4 (для вычисления налога используйте абсолютную ссылку В$11).
3) Скопируйте вниз формулы: из ячейки E4 в блок ячеек E5:E9; из ячейки F4 в блок ячеек F5:F9 (по стрелке).
4) Введите по смыслу формулы в ячейки F13, E16, E19.
5) Отформатируйте числа в столбце F, оставив две цифры в дроби. (Формат, Ячейки, Числовой …)
6) Постройте диаграмму на отдельном листе для блока F4:F9.
7) Выполните сортировку данных по столбцу F.
| A
| B
| C
| D
| E
| F
|
| Прибыль по предприятиям
|
| Название
предприятия
| Доход (р.)
| Налог (р.)
|
| апрель
| май
| июнь
| квартал
| за квартал
| 4
| ОТЗ
|
|
|
|
|
|
| Петрозаводскмаш
|
|
|
|
|
|
| Северянка
|
|
|
|
|
|
| БОП
|
|
|
|
|
|
| Славмо
|
|
|
|
|
|
| Хлебозавод
|
|
|
|
|
|
|
|
| Налог (в %)
|
|
|
|
|
|
| Сумма налогов
за квартал (р.)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Максимальный до-
ход за квартал (р.)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Средний доход
за квартал (р.)
|
|
|
|
|
|
|
|
|
|
|
|
Задание 5.
Создайте ЭТ “Доставка груза” по образцу:
| A
| B
| C
| D
| E
| F
|
| НАРЯД-ЗАДАНИЕ НА ДОСТАВКУ ГРУЗА
|
| Номер
заявки
| Наименование
груза
| Стоимость груза (р.)
| Расстояние
км
| N
этажа
| Доставка
р.
|
|
|
| Диван
|
|
|
|
| 5
|
| Книжный шкаф
|
|
|
|
|
|
| Холодильник
|
|
|
|
|
|
| Мягкая мебель
|
|
|
|
|
|
| Стол
|
|
|
|
|
| ВСЕГО
|
|
| Наценка (%) зависит от стоимости груза
|
| |
| Наценка (р./км) зависит от расстояния
|
|
| Наценка (р./этаж) зависит от N этажа
| 2,5
| | | | | | | | | |
Пояснение.
Формула в ячейке F4 вводится с учетом того, что стоимость доставки груза складывается из следующих трех величин (наценок):
а) 5% от стоимости груза,
б) от расстояния (3 р./км), в) номера этажа (2,5 р./этаж).
Задание 6
Получите таблицу умножения (см. образец).
Примечание. Для первой строки и столбца А используйтеЗаполнение рядов чисел. Формулу, введенную в ячейку В2, скопируйте вначале вниз по столбцу (по стрелке), а затем, выделив этот столбец, скопируйте формулу из ячеек этого столбца вправо.
Таблица умножения
| A
| B
| C
| D
| E
| F
| G
| H
| I
| J
|
|
|
|
|
|
|
|
|
|
|
| 2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
В ячейку В2 следует ввести формулу: =$A2*B$1
|