Задание 2. Табличный процессор MS Excel

2.1. Постановка задачи

1. Создать в MS Excel следующую таблицу и заполнить ее данными:

Ведомость учета расхода бензина в автохозяйстве

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

Номер

Гараж

Пробег, км

Фактический расход бензина, л

Норма расхода бензина л/100 км

Расход бензина по норме, л

Перерасход (экономия) бензина, л

ЗИЛ-130

НВ 12-34

1

1730

670

37,5


ГАЗ-66

ТР 18-26

2

1850

640

33,4


ЗИЛ-130

АВ 19-13

2

2010

720

37,5


Итого:


2. Рассчитать следующие характеристики

1) расход бензина по норме и перерасход (экономию) бензина для каждого автомобиля;

2) суммарные значения фактического расхода бензина, расхода бензина по норме, перерасход (экономию) бензина для всего автохозяйства (функция СУММ), среднее значение пробега для всех автомобилей (функция СРЗНАЧ);

3) суммарные значения фактического расхода бензина для каждой марки автомобиля (функция СУММЕСЛИ);

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

3. С помощью фильтрации получить следующую информацию:

1) марку автомобиля, для которого норма расхода бензина составляет 33,4л / 100 км;

2) номер автомобиля, имеющего максимальный фактический расход бензина;

3) номер автомобиля ЗИЛ-130, имеющего пробег от 1000 до 2000 км;

4) номера автомобилей ЗИЛ-130 или ГАЗ-66, имеющих пробег более 1500 км.

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

2.2. Создание таблицы и заполнение ее исходными данными

В объединение диапазона ячеек А1 – Н1 введем заголовок таблицы «Ведомость учета расхода бензина в автохозяйстве».

В ячейки А3 – Н3 введем заголовки столбцов таблицы: «Марка автомобиля», «Номер», «Гараж», «Пробег, км», «Фактический расход бензина, л», «Норма расхода бензина л/100 км», «Расход бензина по норме, л», «Перерасход (экономия) бензина, л».

Заполним таблицу исходными данными, после чего она примет вид:

Ведомость учета расхода бензина в автохозяйстве


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

Номер

Гараж

Пробег, км

Фактический расход бензина, л

Норма расхода бензина л/100 км

Расход бензина по норме, л

Перерасход (экономия) бензина, л

ЗИЛ-130

НВ 12-34

1

1730

670

37,5

 

 

ГАЗ-66

ТР 18-26

2

1850

640

33,4

 

 

ЗИЛ-130

АВ 19-13

2

2010

720

37,5

 

 

ЗИЛ-130

ПЕ 43-08

1

1543

660

35,2

 

 

ЗИЛ-130

РН 64-98

2

2340

695

35

 

 

ГАЗ-66

РО 53-61

1

1900

876

33,4

 

 

ГАЗ-66

ТИ 76-09

2

950

943

33,4

 

 

ЗИЛ-130

БЛ 23-34

1

743

695

37,5

 

 

ГАЗ-66

БЮ 98-09

1

256

930

33,4

 

 

ЗИЛ-130

ШГ 87-23

2

1389

670

37,5

 

 


2.3. Расчет суммарных значений фактического расхода и перерасхода бензина и среднего значения пробега

Для вычисления Расхода бензина по норме для каждой строки нужно Пробег разделить на 100 и умножить на Норму расхода бензина л/100 км. В результате для первой строки данных таблицы получим следующую формулу вычисления Расхода бензина по норме.

В результате формула для расчета Расхода бензина по норме для первого автомобиля будет следующая: «D4/100*F4». Аналогичная формула будет для всех остальных строк. Поэтому, созданную формулу в ячейке G4 скопируем во все ячейки диапазона G5 – G13. Ссылки на ячейки, используемые в формулах, будут обновляться автоматически.

Для расчета Перерасхода (экономии) бензина, нужно из значения Расход бензина по норме вычесть Фактический расход бензина. Например, для первой строки данных в ячейку H4 нужно ввести формулу «=G4-E4». Расчет Перерасхода бензина для остальных автомобилей аналогичен.

Для расчета суммарных значений фактического расхода бензина сначала объединим ячейки A15 – D15 и введем текст «Итого:». Зададим выравнивание по правому краю и полужирное начертание.

Перейдем в ячейку Е15 и нажмем кнопку Автосумма на панели инструментов. При помощи появившегося прямоугольника выделения диапазона выделим диапазон ячеек, данные из которых нужно просуммировать.

Аналогичным образом вычислим суммарные значение Расхода бензина по норме и Перерасход (экономию) бензина.

Теперь вычислим среднее значение пробега для всех автомобилей. Сначала в объединение ячеек А16 – С16 введем поясняющий текст «Среднее значение пробега».

Перейдем в ячейку D16, на панели инструментов нажмем стрелочку, расположенную справа от кнопки Автосумма. Из открывшегося списка выберем пункт «Среднее». Укажем диапазон D4 – D13.

После проведения описанных выше действий, таблица примет вид:

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

Номер

Гараж

Пробег, км

Фактический расход бензина, л

Норма расхода бензина л/100 км

Расход бензина по норме, л

Перерасход (экономия) бензина, л

ЗИЛ-130

НВ 12-34

1

1730

670

37,5

648,75

-21,25

ГАЗ-66

ТР 18-26

2

1850

640

33,4

617,9

-22,1

ЗИЛ-130

АВ 19-13

2

2010

720

37,5

753,75

33,75

ЗИЛ-130

ПЕ 43-08

1

1543

660

35,2

543,136

-116,864

ЗИЛ-130

РН 64-98

2

2340

695

35

819

124

ГАЗ-66

РО 53-61

1

1900

876

33,4

634,6

-241,4

ГАЗ-66

ТИ 76-09

2

950

943

33,4

317,3

-625,7

ЗИЛ-130

БЛ 23-34

1

743

695

37,5

278,625

-416,375

ГАЗ-66

БЮ 98-09

1

256

930

33,4

85,504

-844,496

ЗИЛ-130

ШГ 87-23

2

1389

670

37,5

520,875

-149,125

 

 

 

 

 

 

 

 

Итого:


7499

 

5219,44

-2279,56

Среднее значение пробега:


1471,1

 

 

 

 


2.4. Расчет суммарных значений фактического расхода бензина для каждой марки автомобиля

Введем поясняющую «Суммарный расход бензина для ГАЗ-66» запись в