Задание 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» запись в