Практическая работа №3.

Тема: Относительные, абсолютные и смешанные ссылки в электронных таблицах

Выполнив задания данной темы, вы:

·  научитесь производить расчеты при помощи относительных, абсолютных и смешанных ссылок

Теоретические сведения к работе

О ссылках в формулах

Ссылка указывает на ячейку или диапазон ячеек листа и передает в Microsoft Excel сведения о расположении значений или данных, которые требуется использовать в формуле. При помощи ссылок можно использовать в одной формуле данные, находящиеся в разных частях листа, а также использовать в нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями.

Стиль ссылок A1

По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки.

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

Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр.

НЕ нашли? Не то? Что вы ищете?

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

Технология выполнения задания:

Задание 1. Создать таблицу для решения задачи:

Агентство по грузоперевозкам «Летучий голландец» предоставляет услуги по перевозке грузов по различным маршрутам. Данные о маршрутах, выполненных в течение недели, по каждому водителю приведены на рис. 1. Справочные данные о технических характеристиках автомобилей и протяженность маршрутов приведены на рис. 2.

1. Построить таблицы по приведенным данным.

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

3. Организовать межтабличные связи для автоматического формирования ведомости расхода топлива за неделю.

4. Сформировать и заполнить ведомость расхода горючего каждым водителем за неделю (рис. 3)

5. Результаты расчета количества израсходованного топлива за неделю представить в графическом виде.

Описание алгоритма решения задачи.

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Летучий голландец»

3. Лист 1 переименовать в лист с названием Данные о маршрутах.

4. На рабочем листе Данные о маршрутах создать таблицу о выполненных маршрутах (рис. 1).

5. Для проверки Ф. И.О. водителей введите эти данные в столбец К. Выделите столбец D и выберете в строке меню команды Данные\Проверка. В окне Проверка вводимых значений выберете вкладку Параметры. Задайте следующие параметры: Тип данных - Список; Источник - =$K$3:$K$9.

6. Выделите ячейки А2:J11 и выберете в строке меню команды Вставка\Имя\Присвоить. В окне Присвоение имени в поле Имя введите Данные о маршрутах и нажмите ОК.

Сведения о выполненных маршрутах

№ п/п

Ф. И.О. водителя

Марка автомобиля

№ рейса

Выполнено рейсов, шт.

Протяженность рейсов, км

Расход топлива на 100 км, л

Израсходовано топлива, л

Грузоподъемность, т

Вес перевезенного груза, т

1

Соловьев В. В.

КАМАЗ

А112

4

2

Михайлов С. С.

ЗИЛ

С431

3

3

Кузнецов Я. Я.

МАЗ

А112

5

4

Иванов К. К.

МАЗ

М023

7

5

Сидоров А. А.

ЗИЛ

В447

2

6

Волков Д. Д.

КАМАЗ

С431

8

7

Быков Л. Л.

КАМАЗ

В447

4

ИТОГО

х

х

х

В СРЕДНЕМ

х

х

х

7. Лист 2 переименовать в лист с названием Справочная информация.

8. На рабочем листе Справочная информация создать таблицы, в которых будут содержаться технические характеристики автомобилей и данные о протяженности выполняемых рейсов с использованием проверки номера рейса и марки автомобиля.

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

Технические характеристики автомобилей

Протяженность рейсов

 

№ п/п

Марка автомобиля

Расход топлива на 100 км, л

Грузоподъемность, т

№ п/п

№ рейса

Протяженность рейсов, км

1

ЗИЛ

42

7

1

А112

420

2

КАМАЗ

45

16

2

В447

310

3

МАЗ

53

12

3

М023

225

4

С431

250

10. На рабочем листе Данные о маршрутах заполнить таблицу «Сведения о выполненных маршрутах».

Протяженность рейсов:

в ячейку F3 ввести формулу =E3*'Справочная информация'! H3

в ячейку F4 ввести формулу =E4*'Справочная информация'! H6

в ячейку F5 ввести формулу =E5*'Справочная информация'! H3

в ячейку F6 ввести формулу =E6*'Справочная информация'! H5

в ячейку F7 ввести формулу =E7*'Справочная информация'! H4

в ячейку F8 ввести формулу =E8*'Справочная информация'! H6

в ячейку F9 ввести формулу =E9*'Справочная информация'! H4

в ячейку F10 ввести формулу =СУММ (F3:F9)

в ячейку F11 ввести формулу =СРЗНАЧ (F3:F9).

Расход топлива на 100 км

в ячейку G3 ввести формулу ='Справочная информация'! C4

в ячейку G4 ввести формулу ='Справочная информация'! C3

в ячейку G5 ввести формулу ='Справочная информация'! C5

в ячейку G6 ввести формулу ='Справочная информация'! C5

в ячейку G7 ввести формулу ='Справочная информация'! C3

в ячейку G8 ввести формулу ='Справочная информация'! C4

в ячейку G9 ввести формулу ='Справочная информация'! C4

в ячейку G10 ввести формулу =СУММ (G3:G9)

в ячейку G11 ввести формулу =СРЗНАЧ (G3:G9)

Израсходовано топлива

в ячейку H3 ввести формулу =F3/100*G3. Размножить введенную в ячейка Н3 формулу для остальных ячеек (с Н4 по Н9) данного столбца.

в ячейку H10 ввести формулу =СУММ (H3:H9)

в ячейку H11 ввести формулу =СРЗНАЧ (H3:H9)

Грузоподъемность

в ячейку I3 ввести формулу ='Справочная информация'! D4

в ячейку I4 ввести формулу ='Справочная информация'! D3

в ячейку I5 ввести формулу ='Справочная информация'! D5

в ячейку I6 ввести формулу ='Справочная информация'! D5

в ячейку I7 ввести формулу ='Справочная информация'! D3

в ячейку I8 ввести формулу ='Справочная информация'! D4

в ячейку I9 ввести формулу ='Справочная информация'! D4

в ячейку I10 ввести формулу =СУММ (I3:I9)

в ячейку I11 ввести формулу =СРЗНАЧ (I3:I9)

Вес перевезенного груза

в ячейку J3 ввести формулу =I3*E3. Размножить введенную в ячейка J3 формулу для остальных ячеек (с J4 по J9) данного столбца.

в ячейку J10 ввести формулу =СУММ (J3:J9)

в ячейку J11 ввести формулу =СРЗНАЧ (J3:J9).

Сведения о выполненных маршрутах

№ п/п

Ф. И.О. водителя

Марка автомобиля

№ рейса

Выполнено рейсов, шт.

Протяженность рейсов, км

Расход топлива на 100 км, л

Израсходовано топлива, л

Грузоподъемность, т

Вес перевезенного груза, т

1

Соловьев В. В.

КАМАЗ

А112

4

1680

45

756

16

64

2

Михайлов С. С.

ЗИЛ

С431

3

750

42

315

7

21

3

Кузнецов Я. Я.

МАЗ

А112

5

2100

53

1113

12

60

4

Иванов К. К.

МАЗ

М023

7

1575

53

834.75

12

84

5

Сидоров А. А.

ЗИЛ

В447

2

620

42

260.4

7

14

6

Волков Д. Д.

КАМАЗ

С431

8

2000

45

900

16

128

7

Быков Л. Л.

КАМАЗ

В447

4

1240

45

558

16

64

ИТОГО

х

х

х

9965

325

4737.15

86

435

В СРЕДНЕМ

х

х

х

1423.57

46.43

676.74

12.29

62.14

11. Лист 3 переименовать в лист с названием Ведомость.

12. На рабочем листе Ведомость создать ведомость расхода горючего, в которых будут содержаться данные о выполненных рейсах и израсходованном топливе каждым водителем и заполнить ее (рис. 4).

Выполнено рейсов

в ячейку D10 ввести формулу ='Данные о маршрутах'! E3

в ячейку D11 ввести формулу ='Данные о маршрутах'! E4

в ячейку D12 ввести формулу ='Данные о маршрутах'! E5

в ячейку D13 ввести формулу ='Данные о маршрутах'! E6

в ячейку D14 ввести формулу ='Данные о маршрутах'! E7

в ячейку D15 ввести формулу ='Данные о маршрутах'! E8

в ячейку D16 ввести формулу ='Данные о маршрутах'! E8

в ячейку D17 ввести формулу =СУММ (D10:D16).

Израсходовано топлива

в ячейку E10 ввести формулу ='Данные о маршрутах'! H3

в ячейку E11 ввести формулу ='Данные о маршрутах'! H4

в ячейку E12 ввести формулу ='Данные о маршрутах'! H5

в ячейку E13 ввести формулу ='Данные о маршрутах'! H6

в ячейку E14 ввести формулу ='Данные о маршрутах'! H7

в ячейку E15 ввести формулу ='Данные о маршрутах'! H8

в ячейку E16 ввести формулу ='Данные о маршрутах'! H9

в ячейку E17 ввести формулу =СУММ (E10:E16).

Ведомость расхода горючего

Ф. И.О. водителя

№ рейса

Выполнено рейсов, шт.

Израсходовано топлива, л

Соловьев В. В.

А112

4

756

Михайлов С. С.

С431

3

315

Кузнецов Я. Я.

А112

5

1113

Иванов К. К.

М023

7

834.75

Сидоров А. А.

В447

2

260.4

Волков Д. Д.

С431

8

900

Быков Л. Л.

В447

4

558

ИТОГО

33

4737.15

Отчет о практической работе

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

Сделать вывод:

Архив с работами отправить преподавателю на электронный адрес:  *****@***ru

Выполнил студент______группы_________________________